[Home] [Help]
PACKAGE BODY: APPS.PO_VAL_LINES
Source
1 PACKAGE BODY PO_VAL_LINES AS
2 -- $Header: PO_VAL_LINES.plb 120.21 2006/11/15 00:16:09 vinokris noship $
3
4 c_ENTITY_TYPE_LINE CONSTANT VARCHAR2(30) := PO_VALIDATIONS.C_ENTITY_TYPE_LINE;
5
6 c_RATE CONSTANT VARCHAR2(30) := 'RATE';
7 c_FIXED_PRICE CONSTANT VARCHAR2(30) := 'FIXED PRICE';
8 c_STANDARD CONSTANT VARCHAR2(30) := 'STANDARD';
9 c_PLANNED CONSTANT VARCHAR2(30) := 'PLANNED';
10 c_TEMP_LABOR CONSTANT VARCHAR2(30) := 'TEMP LABOR';
11 c_BLANKET CONSTANT VARCHAR2(30) := 'BLANKET';
12 c_QUOTATION CONSTANT VARCHAR2(30) := 'QUOTATION';
13
14 -- <Complex Work R12 Start>
15 c_PREPAYMENT CONSTANT VARCHAR2(30) := 'PREPAYMENT';
16 c_LUMPSUM CONSTANT VARCHAR2(30) := 'LUMPSUM';
17 c_MILESTONE CONSTANT VARCHAR2(30) := 'MILESTONE';
18 c_ADVANCE CONSTANT VARCHAR2(30) := 'ADVANCE';
19 c_DELIVERY CONSTANT VARCHAR2(30) := 'DELIVERY';
20 -- <Complex Work R12 End>
21
22 c_NEW CONSTANT VARCHAR2(30) := 'NEW';
23
24 c_2_SOURCING CONSTANT VARCHAR2(30) := '2_SOURCING';
25
26 -- constants for columns:
27 c_COMMITTED_AMOUNT CONSTANT VARCHAR2(30) := 'COMMITTED_AMOUNT';
28 c_MIN_RELEASE_AMOUNT CONSTANT VARCHAR2(30) := 'MIN_RELEASE_AMOUNT';
29 c_QUANTITY CONSTANT VARCHAR2(30) := 'QUANTITY';
30 c_AMOUNT CONSTANT VARCHAR2(30) := 'AMOUNT';
31 c_LINE_NUM CONSTANT VARCHAR2(30) := 'LINE_NUM';
32 c_ITEM_ID CONSTANT VARCHAR2(30) := 'ITEM_ID';
33 c_START_DATE CONSTANT VARCHAR2(30) := 'START_DATE';
34 c_EXPIRATION_DATE CONSTANT VARCHAR2(30) := 'EXPIRATION_DATE';
35 c_UNIT_PRICE CONSTANT VARCHAR2(30) := 'UNIT_PRICE';
36 c_LIST_PRICE_PER_UNIT CONSTANT VARCHAR2(30) := 'LIST_PRICE_PER_UNIT';
37 c_MARKET_PRICE CONSTANT VARCHAR2(30) := 'MARKET_PRICE';
38 c_UNIT_MEAS_LOOKUP_CODE CONSTANT VARCHAR2(30) := 'UNIT_MEAS_LOOKUP_CODE';
39 c_ITEM_DESCRIPTION CONSTANT VARCHAR2(30) := 'ITEM_DESCRIPTION';
40 c_CATEGORY_ID CONSTANT VARCHAR2(30) := 'CATEGORY_ID';
41 c_JOB_ID CONSTANT VARCHAR2(30) := 'JOB_ID';
42 c_LINE_TYPE_ID CONSTANT VARCHAR2(30) := 'LINE_TYPE_ID';
43 c_SECONDARY_QUANTITY CONSTANT VARCHAR2(30) := 'SECONDARY_QUANTITY';
44 c_FROM_LINE_ID CONSTANT VARCHAR2(30) := 'FROM_LINE_ID';
45 c_RECOUPMENT_RATE VARCHAR(30) := 'RECOUPMENT_RATE'; -- Bug 5072189
46 c_RETAINAGE_RATE VARCHAR(30) := 'RETAINAGE_RATE'; -- Bug 5072189
47 c_PROGRESS_PAYMENT_RATE VARCHAR(30) := 'PROGRESS_PAYMENT_RATE'; -- Bug 5072189
48 c_MAX_RETAINAGE_AMOUNT VARCHAR(30) := 'MAX_RETAINAGE_AMOUNT'; -- Bug 5221843
49
50 -- The module base for this package.
51 D_PACKAGE_BASE CONSTANT VARCHAR2(50) :=
52 PO_LOG.get_package_base('PO_VAL_LINES');
53
54 -- The module base for the subprogram.
55 D_amt_agreed_ge_zero CONSTANT VARCHAR2(100) :=
56 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'amt_agreed_ge_zero');
57
58 -- The module base for the subprogram.
59 D_min_rel_amt_ge_zero CONSTANT VARCHAR2(100) :=
60 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'min_rel_amt_ge_zero');
61
62 -- The module base for the subprogram.
63 D_quantity_gt_zero CONSTANT VARCHAR2(100) :=
64 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'quantity_gt_zero');
65
66 -- The module base for the subprogram.
67 D_amount_gt_zero CONSTANT VARCHAR2(100) :=
68 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'amount_gt_zero');
69
70 -- The module base for the subprogram.
71 D_line_num_gt_zero CONSTANT VARCHAR2(100) :=
72 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'line_num_gt_zero');
73
74 D_otl_inv_start_date_change CONSTANT VARCHAR2(100) :=
75 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'otl_invalid_start_date_change');
76
77 D_otl_invalid_end_date_change CONSTANT VARCHAR2(100) :=
78 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'otl_invalid_end_date_change');
79
80 D_quantity_notif_change CONSTANT VARCHAR2(100) :=
81 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'quantity_notif_change');
82
83 D_unit_price_ge_zero CONSTANT VARCHAR2(100) :=
84 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'unit_price_ge_zero');
85
86 D_list_price_ge_zero CONSTANT VARCHAR2(100) :=
87 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'list_price_ge_zero');
88
89 D_market_price_ge_zero CONSTANT VARCHAR2(100) :=
90 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'market_price_ge_zero');
91
92 D_quantity_ge_quantity_enc CONSTANT VARCHAR2(100) :=
93 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'quantity_ge_quantity_enc');
94
95 D_amount_ge_timecard CONSTANT VARCHAR2(100) :=
96 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'amount_ge_timecard');
97
98 D_line_num_unique CONSTANT VARCHAR2(100) :=
99 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'line_num_unique');
100
101 D_vmi_asl_exists CONSTANT VARCHAR2(100) :=
102 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'vmi_asl_exists');
103
104 D_start_date_le_end_date CONSTANT VARCHAR2(100) :=
105 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'start_date_le_end_date');
106
107 D_validate_unit_price_change CONSTANT VARCHAR2(100) :=
108 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'validate_unit_price_change');
109
110 D_expiration_ge_blanket_start CONSTANT VARCHAR2(100) :=
111 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'expiration_ge_blanket_start');
112
113 D_expiration_le_blanket_end CONSTANT VARCHAR2(100) :=
114 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'expiration_le_blanket_end');
115
116 -- <Complex Work R12 Start>: Removed/added debug variables
117 D_quantity_ge_quantity_exec CONSTANT VARCHAR2(100) :=
118 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'quantity_ge_quantity_exec');
119 D_amount_ge_amount_exec CONSTANT VARCHAR2(100) :=
120 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'amount_ge_amount_exec');
121 D_price_ge_price_mstone_exec CONSTANT VARCHAR2(100) :=
122 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'price_ge_price_milestone_exec');
123 D_qty_ge_qty_milestone_exec CONSTANT VARCHAR2(100) :=
124 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'qty_ge_qty_milestone_exec');
125 -- Bug 5072189 Start
126 D_recoupment_rate_range_check CONSTANT VARCHAR2(100) :=
127 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'recoupment_rate_range_check');
128 D_retainage_rate_range_check CONSTANT VARCHAR2(100) :=
129 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'retainage_rate_range_check');
130 D_prog_pay_rate_range_check CONSTANT VARCHAR2(100) :=
131 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'prog_pay_rate_range_check');
132 -- Bug 5072189 End
133 D_max_retain_amt_ge_zero CONSTANT VARCHAR2(100) :=
134 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'max_retain_amt_ge_zero'); --Bug 5221843
135 D_max_retain_amt_ge_retained CONSTANT VARCHAR2(100) :=
136 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'max_retain_amt_ge_retained'); --Bug 5453079
137 -- Bug 5070210 Start
138 D_advance_amt_le_amt CONSTANT VARCHAR2(100) :=
139 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'advance_amt_le_amt');
140 -- Bug 5070210 End
141 -- <Complex Work R12 End>
142 D_unit_meas_not_null CONSTANT VARCHAR2(100) :=
143 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'unit_meas_not_null');
144 D_item_description_not_null CONSTANT VARCHAR2(100) :=
145 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'item_description_not_null');
146 D_category_id_not_null CONSTANT VARCHAR2(100) :=
147 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'category_id_not_null');
148 D_item_id_not_null CONSTANT VARCHAR2(100) :=
149 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'item_id_not_null');
150 D_temp_labor_job_id_not_null CONSTANT VARCHAR2(100) :=
151 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'temp_labor_job_id_not_null');
152 D_line_type_id_not_null CONSTANT VARCHAR2(100) :=
153 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'line_type_id_not_null');
154 D_temp_lbr_start_date_not_null CONSTANT VARCHAR2(100) :=
155 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'temp_lbr_start_date_not_null');
156 D_src_doc_line_not_null CONSTANT VARCHAR2(100) :=
157 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'src_doc_line_not_null');
158 -- The module base for the subprogram.
159 D_line_qtys_within_deviation CONSTANT VARCHAR2(100) :=
160 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'line_qtys_within_deviation');
161 D_line_sec_quantity_gt_zero CONSTANT VARCHAR2(100) :=
162 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'line_sec_quantity_gt_zero');
163 D_from_line_id_not_null CONSTANT VARCHAR2(100) :=
164 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'from_line_id_not_null');
165
166 -------------------------------------------------------------------------------
167 -- This procedure determines if Amount Agreed on blanket lines is greater
168 -- than or equal to zero. If not, return a failure.
169 -------------------------------------------------------------------------------
170 PROCEDURE amt_agreed_ge_zero(
171 p_line_id_tbl IN PO_TBL_NUMBER
172 , p_committed_amount_tbl IN PO_TBL_NUMBER
173 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
174 , x_result_type OUT NOCOPY VARCHAR2
175 )
176 IS
177 BEGIN
178
179 PO_VALIDATION_HELPER.greater_or_equal_zero(
180 p_calling_module => D_amt_agreed_ge_zero
181 , p_null_allowed_flag => PO_CORE_S.g_parameter_YES
182 , p_value_tbl => p_committed_amount_tbl
183 , p_entity_id_tbl => p_line_id_tbl
184 , p_entity_type => c_ENTITY_TYPE_LINE
185 , p_column_name => c_COMMITTED_AMOUNT
186 , p_message_name => PO_MESSAGE_S.PO_ALL_ENTER_VALUE_GE_ZERO
187 , x_results => x_results
188 , x_result_type => x_result_type
189 );
190
191 END amt_agreed_ge_zero;
192
193 -------------------------------------------------------------------------------
194 -- This procedure determines if Minimum Release Amount on blanket lines
195 -- is greater than or equal to zero. If not, return a failure.
196 -------------------------------------------------------------------------------
197 PROCEDURE min_rel_amt_ge_zero(
198 p_line_id_tbl IN PO_TBL_NUMBER
199 , p_min_release_amount_tbl IN PO_TBL_NUMBER
200 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
201 , x_result_type OUT NOCOPY VARCHAR2
202 )
203 IS
204 BEGIN
205
206 PO_VALIDATION_HELPER.greater_or_equal_zero(
207 p_calling_module => D_min_rel_amt_ge_zero
208 , p_null_allowed_flag => PO_CORE_S.g_parameter_YES
209 , p_value_tbl => p_min_release_amount_tbl
210 , p_entity_id_tbl => p_line_id_tbl
211 , p_entity_type => c_ENTITY_TYPE_LINE
212 , p_column_name => c_MIN_RELEASE_AMOUNT
213 , p_message_name => PO_MESSAGE_S.PO_ALL_ENTER_VALUE_GE_ZERO
214 , x_results => x_results
215 , x_result_type => x_result_type
216 );
217
218 END min_rel_amt_ge_zero;
219
220 -----------------------------------------------------------------------------
221 -- Validates that quantity is not null and greater than zero if it is not
222 -- a Rate or Fixed Price line.
223 -----------------------------------------------------------------------------
224 PROCEDURE quantity_gt_zero(
225 p_line_id_tbl IN PO_TBL_NUMBER
226 , p_quantity_tbl IN PO_TBL_NUMBER
227 , p_order_type_lookup_code_tbl IN PO_TBL_VARCHAR30
228 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
229 , x_result_type OUT NOCOPY VARCHAR2
230 )
231 IS
232 BEGIN
233
234 PO_VALIDATION_HELPER.gt_zero_order_type_filter(
235 p_calling_module => D_quantity_gt_zero
236 , p_value_tbl => p_quantity_tbl
237 , p_entity_id_tbl => p_line_id_tbl
238 , p_order_type_lookup_code_tbl => p_order_type_lookup_code_tbl
239 , p_check_quantity_types_flag => PO_CORE_S.g_parameter_YES
240 , p_entity_type => c_ENTITY_TYPE_LINE
241 , p_column_name => c_QUANTITY
242 , x_results => x_results
243 , x_result_type => x_result_type
244 );
245
246 END quantity_gt_zero;
247
248 -- <Complex Work R12 Start>
249 -- Consolidated quantity execution checks to improve efficiency and
250 -- to give only the most relevant error
251 -- Removed: quantity_ge_quantity_billed, quantity_ge_quantity_rcvd,
252 -- Added: quantity_ge_quantity_exec
253
254 -----------------------------------------------------------------------------
255 -- Validates that quantity is greater than or equal to the quantity
256 -- received or billed
257 -- This check is only performed if quantity is being reduced below the
258 -- current transaction quantity, since over-receiving is allowed.
259 -- <Complex Work R12>: Ignore qty milestones (there are other checks for those)
260 -----------------------------------------------------------------------------
261 PROCEDURE quantity_ge_quantity_exec(
262 p_line_id_tbl IN PO_TBL_NUMBER
263 , p_quantity_tbl IN PO_TBL_NUMBER
264 , x_result_set_id IN OUT NOCOPY NUMBER
265 , x_result_type OUT NOCOPY VARCHAR2
266 )
267 IS
268 d_mod CONSTANT VARCHAR2(100) := D_quantity_ge_quantity_exec;
269 BEGIN
270
271 IF PO_LOG.d_proc THEN
272 PO_LOG.proc_begin(d_mod,'p_line_id_tbl',p_line_id_tbl);
273 PO_LOG.proc_begin(d_mod,'p_quantity_tbl',p_quantity_tbl);
274 PO_LOG.proc_begin(d_mod,'x_result_set_id',x_result_set_id);
275 END IF;
276
277 IF (x_result_set_id IS NULL) THEN
278 x_result_set_id := PO_VALIDATIONS.next_result_set_id();
279 END IF;
280
281 FORALL i IN 1 .. p_line_id_tbl.COUNT
282 INSERT INTO PO_VALIDATION_RESULTS_GT
283 ( result_set_id
284 , entity_type
285 , entity_id
286 , column_name
287 , column_val
288 , message_name
289 , token1_name
290 , token1_value
291 )
292 SELECT
293 x_result_set_id
294 , c_ENTITY_TYPE_LINE
295 , p_line_id_tbl(i)
296 , c_QUANTITY
297 , TO_CHAR(p_quantity_tbl(i))
298 , (CASE
299 WHEN POLL_TOTAL.qty_bill_actuals = POLL_TOTAL.qty_executed
300 THEN PO_MESSAGE_S.PO_PO_QTY_ORD_LT_QTY_BILLED_NA
301 ELSE PO_MESSAGE_S.PO_PO_QTY_ORD_LT_QTY_RCVD_NA
302 END
303 )
304 , (CASE
305 WHEN POLL_TOTAL.qty_bill_actuals = POLL_TOTAL.qty_executed
306 THEN PO_MESSAGE_S.c_QTY_BILLED_token
307 ELSE PO_MESSAGE_S.c_QTY_RCVD_token
308 END
309 )
310 , (CASE
311 WHEN POLL_TOTAL.qty_bill_actuals = POLL_TOTAL.qty_executed
312 THEN TO_CHAR(POLL_TOTAL.qty_bill_actuals)
313 ELSE TO_CHAR(POLL_TOTAL.qty_recv_actuals)
314 END
315 )
316 FROM
317 ( SELECT
318 sum_qty_recv_actuals qty_recv_actuals,
319 sum_qty_bill_actuals qty_bill_actuals,
320 GREATEST(sum_qty_recv_actuals, sum_qty_bill_actuals) qty_executed
321 FROM
322 ( SELECT
323 NVL(SUM(
324 (CASE
325 WHEN PLL.shipment_type <> c_STANDARD THEN 0
326 ELSE NVL(PLL.quantity_received, 0)
327 END)), 0) sum_qty_recv_actuals,
328 NVL(SUM(
329 (CASE
330 WHEN PLL.shipment_type <> c_STANDARD THEN 0
331 ELSE GREATEST(NVL(PLL.quantity_billed, 0),
332 NVL(PLL.quantity_financed, 0))
333 END)), 0) sum_qty_bill_actuals
334 FROM PO_LINE_LOCATIONS_ALL PLL
335 WHERE PLL.po_line_id = p_line_id_tbl(i)
336 AND NVL(PLL.payment_type, c_DELIVERY) <> c_MILESTONE
337 )
338 ) POLL_TOTAL, PO_LINES_ALL POL
339 WHERE
340 POL.po_line_id = p_line_id_tbl(i)
341 AND p_quantity_tbl(i) IS NOT NULL
342 -- Quantity is being reduced below the current transaction quantity:
343 AND p_quantity_tbl(i) < POL.quantity
344 AND p_quantity_tbl(i) < POLL_TOTAL.qty_executed
345 ;
346
347 IF (SQL%ROWCOUNT > 0) THEN
348 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
349 ELSE
350 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
351 END IF;
352
353 IF PO_LOG.d_proc THEN
354 PO_VALIDATIONS.log_validation_results_gt(d_mod,9,x_result_set_id);
355 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
356 PO_LOG.proc_end(d_mod,'x_result_set_id',x_result_set_id);
357 END IF;
358
359 EXCEPTION
360 WHEN OTHERS THEN
361 IF PO_LOG.d_exc THEN
362 PO_LOG.exc(d_mod,0,NULL);
363 END IF;
364 RAISE;
365
366 END quantity_ge_quantity_exec;
367
368 -- Bug 5070210 Start
369 -- Bug 5446881: If the amount is null, we need to check against price * quantity
370 PROCEDURE advance_amt_le_amt(
371 p_line_id_tbl IN PO_TBL_NUMBER
372 , p_advance_tbl IN PO_TBL_NUMBER
373 , p_amount_tbl IN PO_TBL_NUMBER
374 , p_quantity_tbl IN PO_TBL_NUMBER
375 , p_price_tbl IN PO_TBL_NUMBER
376 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
377 , x_result_type OUT NOCOPY VARCHAR2
378 )
379 IS
380 d_mod CONSTANT VARCHAR2(100) := D_advance_amt_le_amt;
381 l_results_count NUMBER;
382 BEGIN
383
384 IF p_line_id_tbl IS not null
385 THEN
386 IF PO_LOG.d_proc THEN
387 PO_LOG.proc_begin(d_mod,'p_line_id_tbl',p_line_id_tbl);
388 PO_LOG.proc_begin(d_mod,'p_advance_tbl',p_advance_tbl);
389 PO_LOG.proc_begin(d_mod,'p_amount_tbl',p_amount_tbl);
390 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
391 END IF;
392
393
394 IF (x_results IS NULL) THEN
395 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
396 END IF;
397
398 l_results_count := x_results.result_type.COUNT;
399
400 FOR i IN 1 .. p_line_id_tbl.COUNT LOOP
401 IF (p_advance_tbl(i) > nvl(p_amount_tbl(i), p_quantity_tbl(i) * p_price_tbl(i))) THEN
402 x_results.add_result(
403 p_entity_type => c_ENTITY_TYPE_LINE
404 , p_entity_id => p_line_id_tbl(i)
405 , p_column_name => c_AMOUNT
406 , p_message_name => PO_MESSAGE_S.PO_ADVANCE_GT_LINE_AMOUNT
407 );
408 END IF;
409 END LOOP;
410
411 IF (l_results_count < x_results.result_type.COUNT) THEN
412 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
413 ELSE
414 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
415 END IF;
416 END IF;
417
418 END advance_amt_le_amt;
419
420 -- Bug 5446881 End
421 -- Bug 5070210 End
422
423 -- <Complex Work R12 End>
424
425 -----------------------------------------------------------------------------
426 -- Validates that quantity is greater than or equal to quantity encumbered.
427 -----------------------------------------------------------------------------
428 PROCEDURE quantity_ge_quantity_enc(
429 p_line_id_tbl IN PO_TBL_NUMBER
430 , p_quantity_tbl IN PO_TBL_NUMBER
431 , x_result_set_id IN OUT NOCOPY NUMBER
432 , x_result_type OUT NOCOPY VARCHAR2
433 )
434 IS
435 d_mod CONSTANT VARCHAR2(100) := D_quantity_ge_quantity_enc;
436 BEGIN
437
438 IF PO_LOG.d_proc THEN
439 PO_LOG.proc_begin(d_mod,'p_line_id_tbl',p_line_id_tbl);
440 PO_LOG.proc_begin(d_mod,'p_quantity_tbl',p_quantity_tbl);
441 PO_LOG.proc_begin(d_mod,'x_result_set_id',x_result_set_id);
442 END IF;
443
444 IF (x_result_set_id IS NULL) THEN
445 x_result_set_id := PO_VALIDATIONS.next_result_set_id();
446 END IF;
447
448 FORALL i IN 1 .. p_line_id_tbl.COUNT
449 INSERT INTO PO_VALIDATION_RESULTS_GT
450 ( result_set_id
451 , entity_type
452 , entity_id
453 , column_name
454 , column_val
455 , message_name
456 --PBWC Message Change Impact: Removing a token
457 , token1_name
458 , token1_value
459 --, token2_name
460 --, token2_value
461 )
462 SELECT
463 x_result_set_id
464 , c_ENTITY_TYPE_LINE
465 , p_line_id_tbl(i)
466 , c_QUANTITY
467 , TO_CHAR(p_quantity_tbl(i))
468 , PO_MESSAGE_S.PO_PO_QTY_ORD_LT_QTY_ENC_NA
469 --PBWC Message Change Impact: Removing a token
470 --, PO_MESSAGE_S.c_QTY_ORD_token
471 --, TO_CHAR(p_quantity_tbl(i))
472 , PO_MESSAGE_S.c_QTY_ENC_token
473 , TO_CHAR(DIST_TOTAL.quantity_encumbered)
474 FROM
475 ( SELECT NVL(SUM(POD.quantity_ordered),0) quantity_encumbered
476 FROM
477 PO_DISTRIBUTIONS_ALL POD
478 WHERE
479 POD.po_line_id = p_line_id_tbl(i)
480 AND POD.distribution_type IN (c_STANDARD,c_PLANNED)
481 AND POD.encumbered_flag = 'Y'
482 ) DIST_TOTAL
483 WHERE
484 p_quantity_tbl(i) IS NOT NULL
485 AND p_quantity_tbl(i) < DIST_TOTAL.quantity_encumbered
486 ;
487
488 IF (SQL%ROWCOUNT > 0) THEN
489 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
490 ELSE
491 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
492 END IF;
493
494 IF PO_LOG.d_proc THEN
495 PO_VALIDATIONS.log_validation_results_gt(d_mod,9,x_result_set_id);
496 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
497 PO_LOG.proc_end(d_mod,'x_result_set_id',x_result_set_id);
498 END IF;
499
500 EXCEPTION
501 WHEN OTHERS THEN
502 IF PO_LOG.d_exc THEN
503 PO_LOG.exc(d_mod,0,NULL);
504 END IF;
505 RAISE;
506
507 END quantity_ge_quantity_enc;
508
509 -----------------------------------------------------------------------------
510 -- Shows a warning if quantity is changed and notification controls
511 -- are enabled.
512 -----------------------------------------------------------------------------
513 PROCEDURE quantity_notif_change(
514 p_line_id_tbl IN PO_TBL_NUMBER
515 , p_quantity_tbl IN PO_TBL_NUMBER
516 , x_result_set_id IN OUT NOCOPY NUMBER
517 , x_result_type OUT NOCOPY VARCHAR2
518 )
519 IS
520 BEGIN
521
522 PO_VALIDATION_HELPER.amount_notif_ctrl_warning(
523 p_calling_module => D_quantity_notif_change
524 , p_line_id_tbl => p_line_id_tbl
525 , p_quantity_tbl => p_quantity_tbl
526 , p_column_name => c_QUANTITY
527 , p_message_name => PO_MESSAGE_S.PO_PO_NFC_QTY_CHANGE
528 , x_result_set_id => x_result_set_id
529 , x_result_type => x_result_type
530 );
531
532 END quantity_notif_change;
533
534
535 -----------------------------------------------------------------------------
536 -- Validates that amount is not null and greater than zero if the line is
537 -- Rate or Fixed Price.
538 -----------------------------------------------------------------------------
539 PROCEDURE amount_gt_zero(
540 p_line_id_tbl IN PO_TBL_NUMBER
541 , p_amount_tbl IN PO_TBL_NUMBER
542 , p_order_type_lookup_code_tbl IN PO_TBL_VARCHAR30
543 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
544 , x_result_type OUT NOCOPY VARCHAR2
545 )
546 IS
547 BEGIN
548
549 PO_VALIDATION_HELPER.gt_zero_order_type_filter(
550 p_calling_module => D_amount_gt_zero
551 , p_value_tbl => p_amount_tbl
552 , p_entity_id_tbl => p_line_id_tbl
553 , p_order_type_lookup_code_tbl => p_order_type_lookup_code_tbl
554 , p_check_quantity_types_flag => PO_CORE_S.g_parameter_NO
555 , p_entity_type => c_ENTITY_TYPE_LINE
556 , p_column_name => c_AMOUNT
557 , x_results => x_results
558 , x_result_type => x_result_type
559 );
560
561 END amount_gt_zero;
562
563 -- <Complex Work R12 Start>
564 -- Consolidated amount execution checks to improve efficiency and
565 -- to give only the most relevant error
566 -- Removed: amount_ge_amount_billed, amount_ge_amount_rcvd,
567 -- Added: amount_ge_amount_exec
568
569 -----------------------------------------------------------------------------
570 -- Validates that amount is greater than or equal to
571 -- amount billed and amount received.
572 -- This check is only performed if amount is being reduced below the
573 -- current transaction amount, since over-receiving is allowed.
574 -- <Complex Work R12>: Handle differing value bases and payment types
575 -----------------------------------------------------------------------------
576 PROCEDURE amount_ge_amount_exec(
577 p_line_id_tbl IN PO_TBL_NUMBER
578 , p_amount_tbl IN PO_TBL_NUMBER
579 , p_currency_code_tbl IN PO_TBL_VARCHAR30
580 , x_result_set_id IN OUT NOCOPY NUMBER
581 , x_result_type OUT NOCOPY VARCHAR2
582 )
583 IS
584 d_mod CONSTANT VARCHAR2(100) := D_amount_ge_amount_exec;
585 d_progress NUMBER;
586 l_precision NUMBER;
587 l_min_acct_unit NUMBER;
588 l_gt_key NUMBER;
589 BEGIN
590
591 d_progress := 0.0;
592
593 IF PO_LOG.d_proc THEN
594 PO_LOG.proc_begin(d_mod,'p_line_id_tbl',p_line_id_tbl);
595 PO_LOG.proc_begin(d_mod,'p_amount_tbl',p_amount_tbl);
596 PO_LOG.proc_begin(d_mod,'x_result_set_id',x_result_set_id);
597 END IF;
598
599 d_progress := 10.0;
600
601 IF (x_result_set_id IS NULL) THEN
602 x_result_set_id := PO_VALIDATIONS.next_result_set_id();
603 END IF;
604
605 d_progress := 20.0;
606
607 -- get currency info and put in GT table
608 l_gt_key := PO_CORE_S.get_session_gt_nextval();
609
610 d_progress := 30.0;
611
612 FORALL i IN 1 .. p_line_id_tbl.COUNT
613 INSERT INTO PO_SESSION_GT
614 ( key
615 , index_num1 -- po_line_id
616 , char1 -- currency_code
617 , num1 -- minimum_accountable_unit
618 , num2 -- precision
619 )
620 SELECT
621 l_gt_key
622 , p_line_id_tbl(i)
623 , p_currency_code_tbl(i)
624 , cur.minimum_accountable_unit
625 , cur.precision
626 FROM
627 fnd_currencies cur
628 WHERE
629 cur.currency_code = p_currency_code_tbl(i)
630 ;
631
632 d_progress := 40.0;
633
634 FORALL i IN 1 .. p_line_id_tbl.COUNT
635 INSERT INTO PO_VALIDATION_RESULTS_GT
636 ( result_set_id
637 , entity_type
638 , entity_id
639 , column_name
640 , column_val
641 , message_name
642 --PBWC Message Change Impact: Adding a token
643 , token1_name
644 , token1_value
645 )
646 SELECT
647 x_result_set_id
648 , c_ENTITY_TYPE_LINE
649 , p_line_id_tbl(i)
650 , c_AMOUNT
651 , TO_CHAR(p_amount_tbl(i))
652 , (CASE
653 WHEN POLL_TOTAL.amt_bill_actuals = POLL_TOTAL.amt_executed
654 THEN PO_MESSAGE_S.PO_PO_AMT_ORD_LT_AMT_BILLED_NA
655 ELSE PO_MESSAGE_S.PO_PO_AMT_ORD_LT_AMT_RCVD_NA
656 END
657 )
658 --PBWC Message Change Impact: Adding a token
659 , (CASE
660 WHEN POLL_TOTAL.amt_bill_actuals = POLL_TOTAL.amt_executed
661 THEN PO_MESSAGE_S.c_AMT_BILLED_TOKEN
662 ELSE PO_MESSAGE_S.c_AMT_RCVD_TOKEN
663 END
664 )
665 , (CASE
666 WHEN POLL_TOTAL.amt_bill_actuals = POLL_TOTAL.amt_executed
667 THEN TO_CHAR(POLL_TOTAL.amt_bill_actuals)
668 ELSE TO_CHAR(POLL_TOTAL.amt_recv_actuals)
669 END
670 )
671 --End PBWC Message Change Impact: Adding a token
672 FROM
673 ( SELECT
674 sum_amt_recv_actuals amt_recv_actuals,
675 sum_amt_bill_actuals amt_bill_actuals,
676 GREATEST(sum_amt_recv_actuals, sum_amt_bill_actuals) amt_executed
677 FROM
678 ( SELECT
679 NVL(SUM(
680 (CASE
681 WHEN PLL.shipment_type <> c_STANDARD
682 THEN 0
683 WHEN PLL.payment_type = c_RATE
684 THEN
685 CASE
686 WHEN gtt.num1 IS NOT NULL THEN
687 -- Round to minimum accountable unit.
688 ROUND(
689 NVL(PLL.quantity_received*PLL.price_override,0) / gtt.num1
690 ) * gtt.num1
691 ELSE
692 -- Round to currency precision.
693 ROUND( NVL(PLL.quantity_received*PLL.price_override,0)
694 , gtt.num2)
695 END
696 ELSE NVL(PLL.amount_received, 0)
697 END)), 0) sum_amt_recv_actuals,
698 NVL(SUM(
699 (CASE
700 WHEN PLL.shipment_type <> c_STANDARD
701 THEN 0
702 WHEN PLL.payment_type = c_RATE
703 THEN
704 CASE
705 WHEN gtt.num1 IS NOT NULL THEN
706 -- Round to minimum accountable unit.
707 ROUND(
708 NVL(PLL.quantity_billed*PLL.price_override,0) / gtt.num1
709 ) * gtt.num1
710 ELSE
711 -- Round to currency precision.
712 ROUND( NVL(PLL.quantity_billed*PLL.price_override,0)
713 , gtt.num2)
714 END
715 ELSE GREATEST(NVL(PLL.amount_billed, 0),
716 NVL(PLL.amount_financed, 0))
717 END)), 0) sum_amt_bill_actuals
718 FROM PO_LINE_LOCATIONS_ALL PLL
719 , PO_SESSION_GT GTT
720 WHERE PLL.po_line_id = p_line_id_tbl(i)
721 AND GTT.key = l_gt_key
722 AND GTT.index_num1(+) = PLL.po_line_id
723 )
724 ) POLL_TOTAL
725 , PO_LINES_ALL POL
726 WHERE
727 POL.po_line_id = p_line_id_tbl(i)
728 AND p_amount_tbl(i) IS NOT NULL
729 -- Amount is being reduced below the current transaction amount:
730 AND p_amount_tbl(i) < POL.amount
731 AND p_amount_tbl(i) < POLL_TOTAL.amt_executed
732 ;
733
734 d_progress := 50.0;
735
736 IF (SQL%ROWCOUNT > 0) THEN
737 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
738 ELSE
739 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
740 END IF;
741
742 d_progress := 60.0;
743
744 IF PO_LOG.d_proc THEN
745 PO_VALIDATIONS.log_validation_results_gt(d_mod,9,x_result_set_id);
746 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
747 PO_LOG.proc_end(d_mod,'x_result_set_id',x_result_set_id);
748 END IF;
749
750 d_progress := 70.0;
751
752 EXCEPTION
753 WHEN OTHERS THEN
754 IF PO_LOG.d_exc THEN
755 PO_LOG.exc(d_mod,d_progress,NULL);
756 END IF;
757 RAISE;
758
759 END amount_ge_amount_exec;
760
761 -- <Complex Work R12 End>
762
763
764 -----------------------------------------------------------------------------
765 -- Validates that amount is greater than or equal to the sum of amounts
766 -- on timecards against a Rate Based Standard PO line.
767 -- This check is only performed if amount is being reduced below the
768 -- current transaction amount.
769 -----------------------------------------------------------------------------
770 PROCEDURE amount_ge_timecard(
771 p_line_id_tbl IN PO_TBL_NUMBER
772 , p_amount_tbl IN PO_TBL_NUMBER
773 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
774 , x_result_type OUT NOCOPY VARCHAR2
775 )
776 IS
777 d_mod CONSTANT VARCHAR2(100) := D_amount_ge_timecard;
778
779 l_results_count NUMBER;
780 l_data_key NUMBER;
781
782 l_line_id_tbl PO_TBL_NUMBER;
783 l_amount_tbl PO_TBL_NUMBER;
784
785 l_timecard_amount_sum NUMBER;
786 l_return_status VARCHAR2(1);
787 BEGIN
788
789 IF PO_LOG.d_proc THEN
790 PO_LOG.proc_begin(d_mod,'p_line_id_tbl',p_line_id_tbl);
791 PO_LOG.proc_begin(d_mod,'p_amount_tbl',p_amount_tbl);
792 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
793 END IF;
794
795 IF (x_results IS NULL) THEN
796 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
797 END IF;
798
799 l_results_count := x_results.result_type.COUNT;
800
801 l_data_key := PO_CORE_S.get_session_gt_nextval();
802
803 FORALL i IN 1 .. p_line_id_tbl.COUNT
804 INSERT INTO PO_SESSION_GT
805 ( key
806 , num1
807 , num2
808 )
809 VALUES
810 ( l_data_key
811 , p_line_id_tbl(i)
812 , p_amount_tbl(i)
813 )
814 ;
815
816 SELECT
817 SES.num1
818 , SES.num2
819 BULK COLLECT INTO
820 l_line_id_tbl
821 , l_amount_tbl
822 FROM
823 PO_SESSION_GT SES
824 , PO_LINES_ALL LINE
825 , PO_HEADERS_ALL HEADER
826 WHERE
827 SES.key = l_data_key
828 AND LINE.po_line_id = SES.num1
829 AND HEADER.po_header_id = LINE.po_header_id
830 AND HEADER.type_lookup_code = c_STANDARD
831 AND LINE.order_type_lookup_code = c_RATE
832 AND SES.num2 < LINE.amount
833 ;
834
835 FOR i IN 1 .. l_line_id_tbl.COUNT LOOP
836
837 -- For Rate Based Standard PO lines where the amount has been decreased,
838 -- call the OTL API and identify the ones where the new amount is less than
839 -- the timecard sum.
840
841 PO_HXC_INTERFACE_PVT.get_timecard_amount(
842 p_api_version => 1.0
843 , x_return_status => l_return_status
844 , p_po_line_id => l_line_id_tbl(i)
845 , x_amount => l_timecard_amount_sum
846 );
847
848 IF (l_return_status <> 'S') THEN
849 RAISE FND_API.g_exc_unexpected_error;
850 END IF;
851
852 IF (l_amount_tbl(i) < l_timecard_amount_sum) THEN
853
854 x_results.add_result(
855 p_entity_type => c_ENTITY_TYPE_LINE
856 , p_entity_id => l_line_id_tbl(i)
857 , p_column_name => c_AMOUNT
858 , p_column_val => TO_CHAR(l_amount_tbl(i))
859 , p_message_name => PO_MESSAGE_S.PO_CHNG_OTL_INVALID_AMOUNT
860 , p_token1_name => PO_MESSAGE_S.c_TOTAL_AMT_token
861 , p_token1_value => l_timecard_amount_sum
862 );
863
864 END IF;
865
866 END LOOP;
867
868 IF (l_results_count < x_results.result_type.COUNT) THEN
869 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
870 ELSE
871 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
872 END IF;
873
874 IF PO_LOG.d_proc THEN
875 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
876 PO_LOG.log(PO_LOG.c_PROC_END,d_mod,NULL,'x_results',x_results);
877 END IF;
878
879 EXCEPTION
880 WHEN OTHERS THEN
881 IF PO_LOG.d_exc THEN
882 PO_LOG.exc(d_mod,0,NULL);
883 END IF;
884 RAISE;
885
886 END amount_ge_timecard;
887
888
889
890
891
892 -----------------------------------------------------------------------------
893 -- Validates that all the line numbers for a given header are
894 -- unique.
895 -----------------------------------------------------------------------------
896 -- Assumption:
897 -- All of the unposted line data will be passed in
898 -- to this routine in order to get accurate results.
899 PROCEDURE line_num_unique(
900 p_line_id_tbl IN PO_TBL_NUMBER
901 , p_header_id_tbl IN PO_TBL_NUMBER
902 , p_line_num_tbl IN PO_TBL_NUMBER
903 , x_result_set_id IN OUT NOCOPY NUMBER
904 , x_result_type OUT NOCOPY VARCHAR2
905 )
906 IS
907 BEGIN
908
909 PO_VALIDATION_HELPER.child_num_unique(
910 p_calling_module => D_line_num_unique
911 , p_entity_type => c_entity_type_LINE
912 , p_entity_id_tbl => p_line_id_tbl
913 , p_parent_id_tbl => p_header_id_tbl
914 , p_entity_num_tbl => p_line_num_tbl
915 , x_result_set_id => x_result_set_id
916 , x_result_type => x_result_type
917 );
918
919 END line_num_unique;
920
921 -----------------------------------------------------------------------------
922 -- Checks for null or non-positive line numbers.
923 -----------------------------------------------------------------------------
924
925 PROCEDURE line_num_gt_zero(
926 p_line_id_tbl IN PO_TBL_NUMBER
927 , p_line_num_tbl IN PO_TBL_NUMBER
928 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
929 , x_result_type OUT NOCOPY VARCHAR2
930 )
931 IS
932 BEGIN
933
934 PO_VALIDATION_HELPER.greater_than_zero(
935 p_calling_module => D_line_num_gt_zero
936 , p_null_allowed_flag => PO_CORE_S.g_parameter_NO
937 , p_value_tbl => p_line_num_tbl
938 , p_entity_id_tbl => p_line_id_tbl
939 , p_entity_type => c_ENTITY_TYPE_LINE
940 , p_column_name => c_LINE_NUM
941 , p_message_name => PO_MESSAGE_S.PO_ALL_ENTER_VALUE_GT_ZERO
942 , x_results => x_results
943 , x_result_type => x_result_type
944 );
945
946 END line_num_gt_zero;
947
948 ------------------------------------------------------------------------
949 -- Displays the warning 'PO_VMI_ASL_EXIST if all the following are true:
950 -- 1) Profile PO_VMI_DISPLAY_WARNING is 'Y'
951 -- 2) Item is not null
952 -- 3) Document is an SPO
953 -- 4) The item is set up for VMI in ASL
954 --
955 -- Where clauses derived from L_ITEM_CSR in PO_AUTOSOURCE_SV.get_asl_info
956 ------------------------------------------------------------------------
957 PROCEDURE vmi_asl_exists(
958 p_line_id_tbl IN PO_TBL_NUMBER
959 , p_type_lookup_code_tbl IN PO_TBL_VARCHAR30
960 , p_item_id_tbl IN PO_TBL_NUMBER
961 , p_org_id_tbl IN PO_TBL_NUMBER
962 , p_vendor_id_tbl IN PO_TBL_NUMBER
963 , p_vendor_site_id_tbl IN PO_TBL_NUMBER
964 , x_result_set_id IN OUT NOCOPY NUMBER
965 , x_result_type OUT NOCOPY VARCHAR2
966 )
967 IS
968 d_mod CONSTANT VARCHAR2(100) := D_vmi_asl_exists;
969
970 -- Flag indicating if profile PO_VMI_DISPLAY_WARNING is on
971 l_po_vmi_display_warning VARCHAR2(2000);
972 BEGIN
973
974 IF PO_LOG.d_proc THEN
975 PO_LOG.proc_begin(d_mod,'p_line_id_tbl',p_line_id_tbl);
976 PO_LOG.proc_begin(d_mod,'p_type_lookup_code_tbl',p_type_lookup_code_tbl);
977 PO_LOG.proc_begin(d_mod,'p_item_id_tbl',p_item_id_tbl);
978 PO_LOG.proc_begin(d_mod,'p_org_id_tbl',p_org_id_tbl);
979 PO_LOG.proc_begin(d_mod,'p_vendor_id_tbl',p_vendor_id_tbl);
980 PO_LOG.proc_begin(d_mod,'p_vendor_site_id_tbl',p_vendor_site_id_tbl);
981 PO_LOG.proc_begin(d_mod,'x_result_set_id',x_result_set_id);
982 END IF;
983
984 IF (x_result_set_id IS NULL) THEN
985 x_result_set_id := PO_VALIDATIONS.next_result_set_id();
986 END IF;
987
988 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
989
990 -- Get profile PO_VMI_DISPLAY_WARNING
991 fnd_profile.get(PO_PROFILES.PO_VMI_DISPLAY_WARNING,l_po_vmi_display_warning);
992
993 IF (l_po_vmi_display_warning = 'Y') THEN
994
995 FORALL i in 1 ..p_line_id_tbl.COUNT
996 INSERT INTO PO_VALIDATION_RESULTS_GT
997 ( result_set_id
998 , result_type
999 , entity_type
1000 , entity_id
1001 , column_name
1002 , column_val
1003 , message_name
1004 )
1005 SELECT
1006 x_result_set_id
1007 , PO_VALIDATIONS.c_result_type_WARNING
1008 , c_ENTITY_TYPE_LINE
1009 , p_line_id_tbl(i)
1010 , c_ITEM_ID
1011 , TO_CHAR(p_item_id_tbl(i))
1012 , PO_MESSAGE_S.PO_VMI_ASL_EXIST
1013 FROM
1014 PO_APPROVED_SUPPLIER_LIS_VAL_V PASL
1015 , PO_ASL_ATTRIBUTES PAA
1016 , PO_ASL_STATUS_RULES_V PASR
1017 WHERE
1018 -- item is not null
1019 p_item_id_tbl(i) IS NOT NULL
1020 -- Document is standard PO
1021 AND p_type_lookup_code_tbl(i) = c_STANDARD
1022
1023 --VMI is enabled
1024 AND paa.enable_vmi_flag = 'Y'
1025 AND pasl.item_id = p_item_id_tbl(i)
1026 AND pasl.vendor_id = p_vendor_id_tbl(i)
1027 AND nvl(pasl.vendor_site_id,-1) = nvl(p_vendor_site_id_tbl(i),-1)
1028 AND pasl.using_organization_id IN (p_org_id_tbl(i), -1)
1029 AND pasl.asl_id = paa.asl_id
1030 AND pasr.business_rule = c_2_SOURCING
1031 AND pasr.allow_action_flag = 'Y'
1032 AND pasr.status_id = pasl.asl_status_id
1033 AND paa.using_organization_id =
1034 (SELECT max(paa2.using_organization_id)
1035 FROM po_asl_attributes paa2
1036 WHERE paa2.asl_id = pasl.asl_id
1037 AND paa2.using_organization_id IN (-1, p_org_id_tbl(i)));
1038
1039 IF (SQL%ROWCOUNT > 0) THEN
1040 x_result_type := PO_VALIDATIONS.c_result_type_WARNING;
1041 END IF;
1042
1043 END IF;
1044
1045 IF PO_LOG.d_proc THEN
1046 PO_VALIDATIONS.log_validation_results_gt(d_mod,9,x_result_set_id);
1047 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
1048 PO_LOG.proc_end(d_mod,'x_result_set_id',x_result_set_id);
1049 END IF;
1050
1051 EXCEPTION
1052 WHEN OTHERS THEN
1053 IF PO_LOG.d_exc THEN
1054 PO_LOG.exc(d_mod,0,NULL);
1055 END IF;
1056 RAISE;
1057
1058 END vmi_asl_exists;
1059
1060
1061 ------------------------------------------------------------------------
1062 -- Validates that the start date is less than or equal to the end date.
1063 -- Shows error 'PO_SVC_ASSIGNMENT_DATES'.
1064 ------------------------------------------------------------------------
1065 PROCEDURE start_date_le_end_date(
1066 p_line_id_tbl IN PO_TBL_NUMBER
1067 , p_start_date_tbl IN PO_TBL_DATE
1068 , p_expiration_date_tbl IN PO_TBL_DATE
1069 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
1070 , x_result_type OUT NOCOPY VARCHAR2
1071 )
1072 IS
1073 BEGIN
1074
1075 PO_VALIDATION_HELPER.start_date_le_end_date(
1076 p_calling_module => D_start_date_le_end_date
1077 , p_start_date_tbl => p_start_date_tbl
1078 , p_end_date_tbl => p_expiration_date_tbl
1079 , p_entity_id_tbl => p_line_id_tbl
1080 , p_entity_type => c_ENTITY_TYPE_LINE
1081 , p_column_name => c_START_DATE
1082 , p_column_val_selector => NULL
1083 , p_message_name => PO_MESSAGE_S.PO_SVC_ASSIGNMENT_DATES
1084 , x_results => x_results
1085 , x_result_type => x_result_type
1086 );
1087
1088 END start_date_le_end_date;
1089
1090 ------------------------------------------------------------------------
1091 -- If the following is true:
1092 -- 1) Line has been saved
1093 -- 2) The new start date is greater than the existing start date
1094 -- 3) Document is an SPO
1095 -- 4) Line is rate based
1096 -- 5) Submitted or approved timecards exist for the line
1097 -- Then throw the error 'PO_CHNG_OTL_INVALID_START_DATE'.
1098 --
1099 ------------------------------------------------------------------------
1100 PROCEDURE otl_invalid_start_date_change(
1101 p_line_id_tbl IN PO_TBL_NUMBER
1102 , p_start_date_tbl IN PO_TBL_DATE
1103 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
1104 , x_result_type OUT NOCOPY VARCHAR2
1105 )
1106 IS
1107 BEGIN
1108
1109 PO_VALIDATION_HELPER.no_timecards_exist(
1110 p_calling_module => D_otl_inv_start_date_change
1111 , p_line_id_tbl => p_line_id_tbl
1112 , p_start_date_tbl => p_start_date_tbl
1113 , p_expiration_date_tbl => NULL
1114 , p_column_name => c_START_DATE
1115 , p_message_name => PO_MESSAGE_S.PO_CHNG_OTL_INVALID_START_DATE
1116 , x_results => x_results
1117 , x_result_type => x_result_type
1118 );
1119
1120 END otl_invalid_start_date_change;
1121
1122
1123 ------------------------------------------------------------------------
1124 -- If the following is true:
1125 -- 1) Line has been saved
1126 -- 2) The new end date is less than the existing end date
1127 -- 3) Document is an SPO
1128 -- 4) Line is rate based
1129 -- 5) Submitted or approved timecards exist for the line
1130 -- Then throw the error 'PO_CHNG_OTL_INVALID_END_DATE'.
1131 --
1132 ------------------------------------------------------------------------
1133 PROCEDURE otl_invalid_end_date_change(
1134 p_line_id_tbl IN PO_TBL_NUMBER
1135 , p_expiration_date_tbl IN PO_TBL_DATE
1136 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
1137 , x_result_type OUT NOCOPY VARCHAR2
1138 )
1139 IS
1140 BEGIN
1141
1142 PO_VALIDATION_HELPER.no_timecards_exist(
1143 p_calling_module => D_otl_invalid_end_date_change
1144 , p_line_id_tbl => p_line_id_tbl
1145 , p_start_date_tbl => NULL
1146 , p_expiration_date_tbl => p_expiration_date_tbl
1147 , p_column_name => c_EXPIRATION_DATE
1148 , p_message_name => PO_MESSAGE_S.PO_CHNG_OTL_INVALID_END_DATE
1149 , x_results => x_results
1150 , x_result_type => x_result_type
1151 );
1152
1153 END otl_invalid_end_date_change;
1154
1155
1156 -----------------------------------------------------------------------------
1157 -- Validates that the unit price is greater than or equal to zero
1158 -- for non-Fixed Price lines.
1159 -----------------------------------------------------------------------------
1160 PROCEDURE unit_price_ge_zero(
1161 p_line_id_tbl IN PO_TBL_NUMBER
1162 , p_unit_price_tbl IN PO_TBL_NUMBER
1163 , p_order_type_lookup_code_tbl IN PO_TBL_VARCHAR30
1164 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
1165 , x_result_type OUT NOCOPY VARCHAR2
1166 )
1167 IS
1168 d_mod CONSTANT VARCHAR2(100) := D_unit_price_ge_zero;
1169
1170 l_line_id_tbl PO_TBL_NUMBER;
1171 l_unit_price_tbl PO_TBL_NUMBER;
1172 l_input_size NUMBER;
1173 l_count NUMBER;
1174 BEGIN
1175
1176 IF PO_LOG.d_proc THEN
1177 PO_LOG.proc_begin(d_mod,'p_line_id_tbl',p_line_id_tbl);
1178 PO_LOG.proc_begin(d_mod,'p_unit_price_tbl',p_unit_price_tbl);
1179 PO_LOG.proc_begin(d_mod,'p_order_type_lookup_code_tbl',p_order_type_lookup_code_tbl);
1180 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
1181 END IF;
1182
1183 l_input_size := p_line_id_tbl.COUNT;
1184
1185 l_line_id_tbl := PO_TBL_NUMBER();
1186 l_line_id_tbl.extend(l_input_size);
1187 l_unit_price_tbl := PO_TBL_NUMBER();
1188 l_unit_price_tbl.extend(l_input_size);
1189
1190 l_count := 0;
1191
1192 FOR i IN 1 .. l_input_size LOOP
1193 IF (p_order_type_lookup_code_tbl(i) <> c_FIXED_PRICE) THEN
1194 l_count := l_count + 1;
1195 l_line_id_tbl(l_count) := p_line_id_tbl(i);
1196 l_unit_price_tbl(l_count) := p_unit_price_tbl(i);
1197 END IF;
1198 END LOOP;
1199
1200 l_line_id_tbl.trim(l_input_size-l_count);
1201 l_unit_price_tbl.trim(l_input_size-l_count);
1202
1203 PO_VALIDATION_HELPER.greater_or_equal_zero(
1204 p_calling_module => d_mod
1205 , p_null_allowed_flag => PO_CORE_S.g_parameter_NO
1206 , p_value_tbl => l_unit_price_tbl
1207 , p_entity_id_tbl => l_line_id_tbl
1208 , p_entity_type => c_ENTITY_TYPE_LINE
1209 , p_column_name => c_UNIT_PRICE
1210 , p_message_name => PO_MESSAGE_S.PO_ALL_ENTER_VALUE_GE_ZERO
1211 , x_results => x_results
1212 , x_result_type => x_result_type
1213 );
1214
1215 EXCEPTION
1216 WHEN OTHERS THEN
1217 IF PO_LOG.d_exc THEN
1218 PO_LOG.exc(d_mod,0,NULL);
1219 END IF;
1220 RAISE;
1221
1222 END unit_price_ge_zero;
1223
1224
1225 -----------------------------------------------------------------------------
1226 -- Validates that the list price per unit is greater than or equal to zero.
1227 -----------------------------------------------------------------------------
1228 PROCEDURE list_price_ge_zero(
1229 p_line_id_tbl IN PO_TBL_NUMBER
1230 , p_list_price_per_unit_tbl IN PO_TBL_NUMBER
1231 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
1232 , x_result_type OUT NOCOPY VARCHAR2
1233 )
1234 IS
1235 BEGIN
1236
1237 PO_VALIDATION_HELPER.greater_or_equal_zero(
1238 p_calling_module => D_list_price_ge_zero
1239 , p_null_allowed_flag => PO_CORE_S.g_parameter_YES
1240 , p_value_tbl => p_list_price_per_unit_tbl
1241 , p_entity_id_tbl => p_line_id_tbl
1242 , p_entity_type => c_ENTITY_TYPE_LINE
1243 , p_column_name => c_LIST_PRICE_PER_UNIT
1244 , p_message_name => PO_MESSAGE_S.PO_ALL_ENTER_VALUE_GE_ZERO
1245 , x_results => x_results
1246 , x_result_type => x_result_type
1247 );
1248
1249 END list_price_ge_zero;
1250
1251
1252 -----------------------------------------------------------------------------
1253 -- Validates that the market price is greater than or equal to zero.
1254 -----------------------------------------------------------------------------
1255 PROCEDURE market_price_ge_zero(
1256 p_line_id_tbl IN PO_TBL_NUMBER
1257 , p_market_price_tbl IN PO_TBL_NUMBER
1258 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
1259 , x_result_type OUT NOCOPY VARCHAR2
1260 )
1261 IS
1262 BEGIN
1263
1264 PO_VALIDATION_HELPER.greater_or_equal_zero(
1265 p_calling_module => D_market_price_ge_zero
1266 , p_null_allowed_flag => PO_CORE_S.g_parameter_YES
1267 , p_value_tbl => p_market_price_tbl
1268 , p_entity_id_tbl => p_line_id_tbl
1269 , p_entity_type => c_ENTITY_TYPE_LINE
1270 , p_column_name => c_MARKET_PRICE
1271 , p_message_name => PO_MESSAGE_S.PO_ALL_ENTER_VALUE_GE_ZERO
1272 , x_results => x_results
1273 , x_result_type => x_result_type
1274 );
1275
1276 END market_price_ge_zero;
1277
1278
1279
1280 -----------------------------------------------------------------------------
1281 -- Validates that the unit price may be changed, in that
1282 -- the price is not being used by another process that should
1283 -- prevent a price change.
1284 -----------------------------------------------------------------------------
1285 PROCEDURE validate_unit_price_change(
1286 p_line_id_tbl IN PO_TBL_NUMBER
1287 , p_unit_price_tbl IN PO_TBL_NUMBER
1288 , p_price_break_lookup_code_tbl IN PO_TBL_VARCHAR30
1289 , x_result_set_id IN OUT NOCOPY NUMBER
1290 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
1291 , x_result_type OUT NOCOPY VARCHAR2
1292 )
1293 IS
1294 d_mod CONSTANT VARCHAR2(100) := D_validate_unit_price_change;
1295
1296 l_data_key NUMBER;
1297 l_line_id_tbl PO_TBL_NUMBER;
1298 l_price_break_lookup_code_tbl PO_TBL_VARCHAR30;
1299 BEGIN
1300
1301 IF PO_LOG.d_proc THEN
1302 PO_LOG.proc_begin(d_mod,'p_line_id_tbl',p_line_id_tbl);
1303 PO_LOG.proc_begin(d_mod,'p_unit_price_tbl',p_unit_price_tbl);
1304 PO_LOG.proc_begin(d_mod,'p_price_break_lookup_code_tbl',p_price_break_lookup_code_tbl);
1305 PO_LOG.proc_begin(d_mod,'x_result_set_id',x_result_set_id);
1306 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
1307 END IF;
1308
1309 l_data_key := PO_CORE_S.get_session_gt_nextval();
1310
1311 FORALL i IN 1 .. p_line_id_tbl.COUNT
1312 INSERT INTO PO_SESSION_GT
1313 ( key
1314 , num1
1315 , num2
1316 , char1
1317 )
1318 VALUES
1319 ( l_data_key
1320 , p_line_id_tbl(i)
1321 , p_unit_price_tbl(i)
1322 , p_price_break_lookup_code_tbl(i)
1323 )
1324 ;
1325
1326
1327 SELECT
1328 SES.num1
1329 , SES.char1
1330 BULK COLLECT INTO
1331 l_line_id_tbl
1332 , l_price_break_lookup_code_tbl
1333 FROM
1334 PO_SESSION_GT SES
1335 , PO_LINES_ALL SAVED_LINE
1336 WHERE
1337 SES.key = l_data_key
1338 AND SAVED_LINE.po_line_id = SES.num1
1339 AND SAVED_LINE.order_type_lookup_code IN (c_QUANTITY, c_RATE)
1340 AND ( SES.num2 <> SAVED_LINE.unit_price
1341 OR (SES.num2 IS NULL AND SAVED_LINE.unit_price IS NOT NULL)
1342 OR (SES.num2 IS NOT NULL AND SAVED_LINE.unit_price IS NULL)
1343 )
1344 ;
1345
1346 IF (l_line_id_tbl.COUNT > 0) THEN
1347
1348 PO_VALIDATIONS.validate_unit_price_change(
1349 p_line_id_tbl => l_line_id_tbl
1350 , p_price_break_lookup_code_tbl => l_price_break_lookup_code_tbl
1351 , x_result_type => x_result_type
1352 , x_result_set_id => x_result_set_id
1353 , x_results => x_results
1354 );
1355
1356 ELSE
1357
1358 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1359
1360 END IF;
1361
1362
1363 IF PO_LOG.d_proc THEN
1364 PO_VALIDATIONS.log_validation_results_gt(d_mod,9,x_result_set_id);
1365 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
1366 PO_LOG.proc_end(d_mod,'x_result_set_id',x_result_set_id);
1367 PO_LOG.log(PO_LOG.c_PROC_END,d_mod,NULL,'x_results',x_results);
1368 END IF;
1369
1370 EXCEPTION
1371 WHEN OTHERS THEN
1372 IF PO_LOG.d_exc THEN
1373 PO_LOG.exc(d_mod,0,NULL);
1374 END IF;
1375 RAISE;
1376
1377 END validate_unit_price_change;
1378
1379
1380 -----------------------------------------------------------------------------
1381 -- Validates that the Expiration Date of the line
1382 -- is greater than or equal to the Effective From date
1383 -- of the Agreement.
1384 -- Agreements only.
1385 -----------------------------------------------------------------------------
1386 PROCEDURE expiration_ge_blanket_start(
1387 p_line_id_tbl IN PO_TBL_NUMBER
1388 , p_blanket_start_date_tbl IN PO_TBL_DATE
1389 , p_expiration_date_tbl IN PO_TBL_DATE
1390 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
1391 , x_result_type OUT NOCOPY VARCHAR2
1392 )
1393 IS
1394 BEGIN
1395
1396 PO_VALIDATION_HELPER.start_date_le_end_date(
1397 p_calling_module => D_expiration_ge_blanket_start
1398 , p_start_date_tbl => p_blanket_start_date_tbl
1399 , p_end_date_tbl => p_expiration_date_tbl
1400 , p_entity_id_tbl => p_line_id_tbl
1401 , p_entity_type => c_entity_type_LINE
1402 , p_column_name => c_EXPIRATION_DATE
1403 , p_column_val_selector => PO_VALIDATION_HELPER.c_END_DATE
1404 , p_message_name => PO_MESSAGE_S.POX_EXPIRATION_DATES
1405 , x_results => x_results
1406 , x_result_type => x_result_type
1407 );
1408
1409 END expiration_ge_blanket_start;
1410
1411
1412 -----------------------------------------------------------------------------
1413 -- Validates that the Expiration Date of the line
1414 -- is less than or equal to the Effective To date
1415 -- of the Agreement.
1416 -- Agreements only.
1417 -----------------------------------------------------------------------------
1418 PROCEDURE expiration_le_blanket_end(
1419 p_line_id_tbl IN PO_TBL_NUMBER
1420 , p_blanket_end_date_tbl IN PO_TBL_DATE
1421 , p_expiration_date_tbl IN PO_TBL_DATE
1422 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
1423 , x_result_type OUT NOCOPY VARCHAR2
1424 )
1425 IS
1426 BEGIN
1427
1428 PO_VALIDATION_HELPER.start_date_le_end_date(
1429 p_calling_module => D_expiration_le_blanket_end
1430 , p_start_date_tbl => p_expiration_date_tbl
1431 , p_end_date_tbl => p_blanket_end_date_tbl
1432 , p_entity_id_tbl => p_line_id_tbl
1433 , p_entity_type => c_entity_type_LINE
1434 , p_column_name => c_EXPIRATION_DATE
1435 , p_column_val_selector => PO_VALIDATION_HELPER.c_START_DATE
1436 , p_message_name => PO_MESSAGE_S.POX_EXPIRATION_DATES
1437 , x_results => x_results
1438 , x_result_type => x_result_type
1439 );
1440
1441 END expiration_le_blanket_end;
1442
1443
1444 -- <Complex Work R12 Start>
1445
1446 -- Bug 5072189 Start
1447 -------------------------------------------------------------------------
1448 -- The invoice close tolerance must be between 0 and 100, inclusive.
1449 -------------------------------------------------------------------------
1450 PROCEDURE recoupment_rate_range_check (
1451 p_line_id_tbl IN PO_TBL_NUMBER
1452 , p_recoupment_rate_tbl IN PO_TBL_NUMBER
1453 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
1454 , x_result_type OUT NOCOPY VARCHAR2
1455 )
1456 IS
1457 BEGIN
1458
1459 PO_VALIDATION_HELPER.within_percentage_range(
1460 p_calling_module => D_recoupment_rate_range_check
1461 , p_null_allowed_flag => PO_CORE_S.g_parameter_YES
1462 , p_value_tbl => p_recoupment_rate_tbl
1463 , p_entity_id_tbl => p_line_id_tbl
1464 , p_entity_type => c_ENTITY_TYPE_LINE
1465 , p_column_name => c_RECOUPMENT_RATE
1466 , p_message_name => PO_MESSAGE_S.PO_ALL_ENTER_PERCENT
1467 , x_results => x_results
1468 , x_result_type => x_result_type
1469 );
1470
1471 END recoupment_rate_range_check;
1472
1473 PROCEDURE retainage_rate_range_check (
1474 p_line_id_tbl IN PO_TBL_NUMBER
1475 , p_retainage_rate_tbl IN PO_TBL_NUMBER
1476 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
1477 , x_result_type OUT NOCOPY VARCHAR2
1478 )
1479 IS
1480 BEGIN
1481
1482 PO_VALIDATION_HELPER.within_percentage_range(
1483 p_calling_module => D_retainage_rate_range_check
1484 , p_null_allowed_flag => PO_CORE_S.g_parameter_YES
1485 , p_value_tbl => p_retainage_rate_tbl
1486 , p_entity_id_tbl => p_line_id_tbl
1487 , p_entity_type => c_ENTITY_TYPE_LINE
1488 , p_column_name => c_RETAINAGE_RATE
1489 , p_message_name => PO_MESSAGE_S.PO_ALL_ENTER_PERCENT
1490 , x_results => x_results
1491 , x_result_type => x_result_type
1492 );
1493
1494 END retainage_rate_range_check;
1495
1496 PROCEDURE prog_pay_rate_range_check (
1497 p_line_id_tbl IN PO_TBL_NUMBER
1498 , p_prog_pay_rate_tbl IN PO_TBL_NUMBER
1499 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
1500 , x_result_type OUT NOCOPY VARCHAR2
1501 )
1502 IS
1503 BEGIN
1504
1505 PO_VALIDATION_HELPER.within_percentage_range(
1506 p_calling_module => D_prog_pay_rate_range_check
1507 , p_null_allowed_flag => PO_CORE_S.g_parameter_YES
1508 , p_value_tbl => p_prog_pay_rate_tbl
1509 , p_entity_id_tbl => p_line_id_tbl
1510 , p_entity_type => c_ENTITY_TYPE_LINE
1511 , p_column_name => c_PROGRESS_PAYMENT_RATE
1512 , p_message_name => PO_MESSAGE_S.PO_ALL_ENTER_PERCENT
1513 , x_results => x_results
1514 , x_result_type => x_result_type
1515 );
1516
1517 END prog_pay_rate_range_check;
1518
1519 -- Bug 5072189 End
1520
1521 -----------------------------------------------------------------------------
1522 -- Validates that the line's quantity is greater than any of the
1523 -- quantity billed / quantity received of its quantity milestones.
1524 -- This check is only performed if quantity is being reduced below the
1525 -- current transaction quantity, since over-receiving is allowed.
1526 -----------------------------------------------------------------------------
1527 PROCEDURE qty_ge_qty_milestone_exec(
1528 p_line_id_tbl IN PO_TBL_NUMBER
1529 , p_quantity_tbl IN PO_TBL_NUMBER
1530 , x_result_set_id IN OUT NOCOPY NUMBER
1531 , x_result_type OUT NOCOPY VARCHAR2
1532 )
1533 IS
1534 d_mod CONSTANT VARCHAR2(100) := D_qty_ge_qty_milestone_exec;
1535 BEGIN
1536
1537 IF PO_LOG.d_proc THEN
1538 PO_LOG.proc_begin(d_mod,'p_line_id_tbl',p_line_id_tbl);
1539 PO_LOG.proc_begin(d_mod,'p_quantity_tbl',p_quantity_tbl);
1540 PO_LOG.proc_begin(d_mod,'x_result_set_id',x_result_set_id);
1541 END IF;
1542
1543 IF (x_result_set_id IS NULL) THEN
1544 x_result_set_id := PO_VALIDATIONS.next_result_set_id();
1545 END IF;
1546
1547 -- <Complex Work TODO>: Fill in the following messages
1548 -- PO_MESSAGE_S.PO_PO_QTY_LT_MILESTONE_BILL
1549 -- PO_MESSAGE_S.PO_PO_QTY_LT_MILESTONE_RECV
1550
1551 FORALL i IN 1 .. p_line_id_tbl.COUNT
1552 INSERT INTO PO_VALIDATION_RESULTS_GT
1553 ( result_set_id
1554 , entity_type
1555 , entity_id
1556 , column_name
1557 , column_val
1558 , message_name
1559 )
1560 SELECT
1561 x_result_set_id
1562 , c_ENTITY_TYPE_LINE
1563 , p_line_id_tbl(i)
1564 , c_QUANTITY
1565 , TO_CHAR(p_quantity_tbl(i))
1566 , (CASE
1567 WHEN POLL_TOTAL.max_qty_bill = POLL_TOTAL.max_qty_executed
1568 THEN 'CWPOTODOMESSAGE'
1569 ELSE 'CWPOTODOMESSAGE'
1570 END
1571 )
1572 FROM
1573 ( SELECT
1574 max_qty_recv,
1575 max_qty_bill,
1576 GREATEST(max_qty_recv, max_qty_bill) max_qty_executed
1577 FROM
1578 ( SELECT
1579 NVL(MAX(quantity_received), 0) max_qty_recv,
1580 NVL(MAX(quantity_billed), 0) max_qty_bill
1581 FROM PO_LINE_LOCATIONS_ALL PLL
1582 WHERE PLL.po_line_id = p_line_id_tbl(i)
1583 AND PLL.payment_type = c_MILESTONE
1584 AND PLL.value_basis = c_QUANTITY
1585 AND PLL.shipment_type = c_STANDARD
1586 )
1587 ) POLL_TOTAL, PO_LINES_ALL POL
1588 WHERE
1589 POL.po_line_id = p_line_id_tbl(i)
1590 AND p_quantity_tbl(i) IS NOT NULL
1591 AND p_quantity_tbl(i) < POL.quantity
1592 AND p_quantity_tbl(i) < POLL_TOTAL.max_qty_executed
1593 ;
1594
1595 IF (SQL%ROWCOUNT > 0) THEN
1596 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
1597 ELSE
1598 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1599 END IF;
1600
1601 IF PO_LOG.d_proc THEN
1602 PO_VALIDATIONS.log_validation_results_gt(d_mod,9,x_result_set_id);
1603 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
1604 PO_LOG.proc_end(d_mod,'x_result_set_id',x_result_set_id);
1605 END IF;
1606
1607 EXCEPTION
1608 WHEN OTHERS THEN
1609 IF PO_LOG.d_exc THEN
1610 PO_LOG.exc(d_mod,0,NULL);
1611 END IF;
1612 RAISE;
1613
1614 END qty_ge_qty_milestone_exec;
1615
1616 -----------------------------------------------------------------------------
1617 -- Validates that the line's price is greater than the sum of prices
1618 -- on milestones that have been received or billed.
1619 -- This check is only performed if price is being reduced below the
1620 -- current transaction price, since over-receiving is allowed.
1621 -----------------------------------------------------------------------------
1622 PROCEDURE price_ge_price_milestone_exec(
1623 p_line_id_tbl IN PO_TBL_NUMBER
1624 , p_price_tbl IN PO_TBL_NUMBER
1625 , x_result_set_id IN OUT NOCOPY NUMBER
1626 , x_result_type OUT NOCOPY VARCHAR2
1627 )
1628 IS
1629 d_mod CONSTANT VARCHAR2(100) := D_price_ge_price_mstone_exec;
1630 BEGIN
1631
1632 IF PO_LOG.d_proc THEN
1633 PO_LOG.proc_begin(d_mod,'p_line_id_tbl',p_line_id_tbl);
1634 PO_LOG.proc_begin(d_mod,'p_price_tbl',p_price_tbl);
1635 PO_LOG.proc_begin(d_mod,'x_result_set_id',x_result_set_id);
1636 END IF;
1637
1638 IF (x_result_set_id IS NULL) THEN
1639 x_result_set_id := PO_VALIDATIONS.next_result_set_id();
1640 END IF;
1641
1642 -- <Complex Work TODO>: Fill in the following messages
1643 -- PO_MESSAGE_S.PO_PO_QTY_LT_MILESTONE_BILL
1644
1645 FORALL i IN 1 .. p_line_id_tbl.COUNT
1646 INSERT INTO PO_VALIDATION_RESULTS_GT
1647 ( result_set_id
1648 , entity_type
1649 , entity_id
1650 , column_name
1651 , column_val
1652 , message_name
1653 )
1654 SELECT
1655 x_result_set_id
1656 , c_ENTITY_TYPE_LINE
1657 , p_line_id_tbl(i)
1658 , c_UNIT_PRICE
1659 , TO_CHAR(p_price_tbl(i))
1660 , 'CWPOTODOMESSAGE'
1661 FROM
1662 ( SELECT
1663 NVL(SUM(CASE
1664 WHEN PLL.quantity_received > 0
1665 THEN NVL(PLL.price_override, 0)
1666 WHEN PLL.quantity_billed > 0
1667 THEN NVL(PLL.price_override, 0)
1668 ELSE 0
1669 END),0) sum_price_executed
1670 FROM PO_LINE_LOCATIONS_ALL PLL
1671 WHERE PLL.po_line_id = p_line_id_tbl(i)
1672 AND PLL.payment_type = c_MILESTONE
1673 AND PLL.value_basis = c_QUANTITY
1674 AND PLL.shipment_type = c_STANDARD
1675 ) POLL_TOTAL, PO_LINES_ALL POL
1676 WHERE
1677 POL.po_line_id = p_line_id_tbl(i)
1678 AND p_price_tbl(i) IS NOT NULL
1679 AND p_price_tbl(i) < POL.unit_price
1680 AND p_price_tbl(i) < POLL_TOTAL.sum_price_executed
1681 ;
1682
1683 IF (SQL%ROWCOUNT > 0) THEN
1684 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
1685 ELSE
1686 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1687 END IF;
1688
1689 IF PO_LOG.d_proc THEN
1690 PO_VALIDATIONS.log_validation_results_gt(d_mod,9,x_result_set_id);
1691 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
1692 PO_LOG.proc_end(d_mod,'x_result_set_id',x_result_set_id);
1693 END IF;
1694
1695 EXCEPTION
1696 WHEN OTHERS THEN
1697 IF PO_LOG.d_exc THEN
1698 PO_LOG.exc(d_mod,0,NULL);
1699 END IF;
1700 RAISE;
1701
1702 END price_ge_price_milestone_exec;
1703
1704 -- <Complex Work R12 End>
1705
1706
1707 -------------------------------------------------------------------------------
1708 -- Ensures that the Unit of Measure is not null for non-FIXED PRICE lines.
1709 -------------------------------------------------------------------------------
1710 PROCEDURE unit_meas_not_null(
1711 p_line_id_tbl IN PO_TBL_NUMBER
1712 , p_unit_meas_lookup_code_tbl IN PO_TBL_VARCHAR30
1713 , p_order_type_lookup_code_tbl IN PO_TBL_VARCHAR30
1714 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
1715 , x_result_type OUT NOCOPY VARCHAR2
1716 )
1717 IS
1718 d_mod CONSTANT VARCHAR2(100) := D_unit_meas_not_null;
1719 l_results_count NUMBER;
1720 BEGIN
1721
1722 IF PO_LOG.d_proc THEN
1723 PO_LOG.proc_begin(d_mod,'p_line_id_tbl',p_line_id_tbl);
1724 PO_LOG.proc_begin(d_mod,'p_unit_meas_lookup_code_tbl',p_unit_meas_lookup_code_tbl);
1725 PO_LOG.proc_begin(d_mod,'p_order_type_lookup_code_tbl',p_order_type_lookup_code_tbl);
1726 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
1727 END IF;
1728
1729 IF (x_results IS NULL) THEN
1730 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
1731 END IF;
1732
1733 l_results_count := x_results.result_type.COUNT;
1734
1735 FOR i IN 1 .. p_line_id_tbl.COUNT LOOP
1736 IF ( p_order_type_lookup_code_tbl(i) <> c_FIXED_PRICE
1737 AND p_unit_meas_lookup_code_tbl(i) IS NULL
1738 )
1739 THEN
1740 x_results.add_result(
1741 p_entity_type => c_ENTITY_TYPE_LINE
1742 , p_entity_id => p_line_id_tbl(i)
1743 , p_column_name => c_UNIT_MEAS_LOOKUP_CODE
1744 , p_message_name => PO_MESSAGE_S.PO_ALL_NOT_NULL
1745 );
1746 END IF;
1747 END LOOP;
1748
1749 IF (l_results_count < x_results.result_type.COUNT) THEN
1750 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
1751 ELSE
1752 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1753 END IF;
1754
1755 IF PO_LOG.d_proc THEN
1756 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
1757 PO_LOG.log(PO_LOG.c_PROC_END,d_mod,NULL,'x_results',x_results);
1758 END IF;
1759
1760 EXCEPTION
1761 WHEN OTHERS THEN
1762 IF PO_LOG.d_exc THEN
1763 PO_LOG.exc(d_mod,0,NULL);
1764 END IF;
1765 RAISE;
1766
1767 END unit_meas_not_null;
1768
1769
1770 -------------------------------------------------------------------------------
1771 -- Ensures that the Item Description is not null.
1772 -------------------------------------------------------------------------------
1773 PROCEDURE item_description_not_null(
1774 p_line_id_tbl IN PO_TBL_NUMBER
1775 , p_item_description_tbl IN PO_TBL_VARCHAR2000
1776 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
1777 , x_result_type OUT NOCOPY VARCHAR2
1778 )
1779 IS
1780 BEGIN
1781
1782 PO_VALIDATION_HELPER.not_null(
1783 p_calling_module => D_item_description_not_null
1784 , p_value_tbl => PO_TYPE_CONVERTER.to_po_tbl_varchar4000(p_item_description_tbl)
1785 , p_entity_id_tbl => p_line_id_tbl
1786 , p_entity_type => c_entity_type_LINE
1787 , p_column_name => c_ITEM_DESCRIPTION
1788 , p_message_name => PO_MESSAGE_S.PO_ALL_NOT_NULL
1789 , x_results => x_results
1790 , x_result_type => x_result_type
1791 );
1792
1793 END item_description_not_null;
1794
1795
1796 -------------------------------------------------------------------------------
1797 -- Ensures that the Category is not null.
1798 -------------------------------------------------------------------------------
1799 PROCEDURE category_id_not_null(
1800 p_line_id_tbl IN PO_TBL_NUMBER
1801 , p_category_id_tbl IN PO_TBL_NUMBER
1802 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
1803 , x_result_type OUT NOCOPY VARCHAR2
1804 )
1805 IS
1806 BEGIN
1807
1808 PO_VALIDATION_HELPER.not_null(
1809 p_calling_module => D_category_id_not_null
1810 , p_value_tbl => PO_TYPE_CONVERTER.to_po_tbl_varchar4000(p_category_id_tbl)
1811 , p_entity_id_tbl => p_line_id_tbl
1812 , p_entity_type => c_entity_type_LINE
1813 , p_column_name => c_CATEGORY_ID
1814 , p_message_name => PO_MESSAGE_S.PO_ALL_NOT_NULL
1815 , x_results => x_results
1816 , x_result_type => x_result_type
1817 );
1818
1819 END category_id_not_null;
1820
1821
1822 ---------------------------------------------------------------------------
1823 -- If order_type_lookup_code is Quantity and outside_operation flag is 'Y',
1824 -- then the item_id cannot be null.
1825 ---------------------------------------------------------------------------
1826 PROCEDURE item_id_not_null(
1827 p_id_tbl IN PO_TBL_NUMBER
1828 , p_item_id_tbl IN PO_TBL_NUMBER
1829 , p_order_type_lookup_code_tbl IN PO_TBL_VARCHAR30
1830 , p_line_type_id_tbl IN PO_TBL_NUMBER
1831 , p_message_name IN VARCHAR2
1832 , x_result_set_id IN OUT NOCOPY NUMBER
1833 , x_result_type OUT NOCOPY VARCHAR2
1834 )
1835 IS
1836 d_mod CONSTANT VARCHAR2(100) := D_item_id_not_null;
1837
1838 l_id_tbl PO_TBL_NUMBER;
1839 l_line_type_id_tbl PO_TBL_NUMBER;
1840 l_input_size NUMBER;
1841 l_count NUMBER;
1842
1843 BEGIN
1844
1845 IF PO_LOG.d_proc THEN
1846 PO_LOG.proc_begin(d_mod,'p_id_tbl',p_id_tbl);
1847 PO_LOG.proc_begin(d_mod,'p_item_id_tbl',p_item_id_tbl);
1848 PO_LOG.proc_begin(d_mod,'p_order_type_lookup_code_tbl',p_order_type_lookup_code_tbl);
1849 PO_LOG.proc_begin(d_mod,'p_line_type_id_tbl',p_line_type_id_tbl);
1850 PO_LOG.proc_begin(d_mod,'p_message_name',p_message_name);
1851 PO_LOG.proc_begin(d_mod,'x_result_set_id',x_result_set_id);
1852 END IF;
1853
1854 IF (x_result_set_id IS NULL) THEN
1855 x_result_set_id := PO_VALIDATIONS.next_result_set_id();
1856 END IF;
1857
1858 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1859
1860 l_input_size := p_id_tbl.COUNT;
1861
1862 l_id_tbl := PO_TBL_NUMBER();
1863 l_id_tbl.extend(l_input_size);
1864 l_line_type_id_tbl := PO_TBL_NUMBER();
1865 l_line_type_id_tbl.extend(l_input_size);
1866
1867 l_count := 0;
1868
1869 FOR i IN 1 .. l_input_size LOOP
1870 IF ( p_item_id_tbl(i) IS NULL
1871 AND p_order_type_lookup_code_tbl(i) = c_QUANTITY
1872 AND p_line_type_id_tbl(i) IS NOT NULL
1873 )
1874 THEN
1875 l_count := l_count + 1;
1876 l_id_tbl(l_count) := p_id_tbl(i);
1877 l_line_type_id_tbl(l_count) := p_line_type_id_tbl(i);
1878 END IF;
1879 END LOOP;
1880
1881 IF (l_count > 0) THEN
1882
1883 l_id_tbl.trim(l_input_size-l_count);
1884 l_line_type_id_tbl.trim(l_input_size-l_count);
1885
1886 FORALL i IN 1 .. l_id_tbl.COUNT
1887 INSERT INTO PO_VALIDATION_RESULTS_GT
1888 ( result_set_id
1889 , entity_type
1890 , entity_id
1891 , column_name
1892 , message_name
1893 )
1894 SELECT
1895 x_result_set_id
1896 , c_ENTITY_TYPE_LINE
1897 , l_id_tbl(i)
1898 , c_ITEM_ID
1899 , p_message_name
1900 FROM
1901 PO_LINE_TYPES_B PLT
1902 WHERE
1903 PLT.line_type_id = l_line_type_id_tbl(i)
1904 AND PLT.outside_operation_flag = 'Y'
1905 ;
1906
1907 IF (SQL%ROWCOUNT > 0) THEN
1908 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
1909 END IF;
1910
1911 END IF;
1912
1913 IF PO_LOG.d_proc THEN
1914 PO_VALIDATIONS.log_validation_results_gt(d_mod,9,x_result_set_id);
1915 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
1916 PO_LOG.proc_end(d_mod,'x_result_set_id',x_result_set_id);
1917 END IF;
1918
1919 EXCEPTION
1920 WHEN OTHERS THEN
1921 IF PO_LOG.d_exc THEN
1922 PO_LOG.exc(d_mod,0,NULL);
1923 END IF;
1924 RAISE;
1925
1926 END item_id_not_null;
1927
1928
1929 -------------------------------------------------------------------------------
1930 -- Ensures that the Job is not null for TEMP LABOR lines.
1931 -------------------------------------------------------------------------------
1932 PROCEDURE temp_labor_job_id_not_null(
1933 p_line_id_tbl IN PO_TBL_NUMBER
1934 , p_job_id_tbl IN PO_TBL_NUMBER
1935 , p_purchase_basis_tbl IN PO_TBL_VARCHAR30
1936 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
1937 , x_result_type OUT NOCOPY VARCHAR2
1938 )
1939 IS
1940 d_mod CONSTANT VARCHAR2(100) := D_temp_labor_job_id_not_null;
1941 l_results_count NUMBER;
1942 BEGIN
1943
1944 IF PO_LOG.d_proc THEN
1945 PO_LOG.proc_begin(d_mod,'p_line_id_tbl',p_line_id_tbl);
1946 PO_LOG.proc_begin(d_mod,'p_job_id_tbl',p_job_id_tbl);
1947 PO_LOG.proc_begin(d_mod,'p_purchase_basis_tbl',p_purchase_basis_tbl);
1948 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
1949 END IF;
1950
1951 IF (x_results IS NULL) THEN
1952 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
1953 END IF;
1954
1955 l_results_count := x_results.result_type.COUNT;
1956
1957 FOR i IN 1 .. p_line_id_tbl.COUNT LOOP
1958 IF ( p_purchase_basis_tbl(i) = c_TEMP_LABOR
1959 AND p_job_id_tbl(i) IS NULL
1960 )
1961 THEN
1962 x_results.add_result(
1963 p_entity_type => c_ENTITY_TYPE_LINE
1964 , p_entity_id => p_line_id_tbl(i)
1965 , p_column_name => c_JOB_ID
1966 , p_message_name => PO_MESSAGE_S.PO_ALL_NOT_NULL
1967 );
1968 END IF;
1969 END LOOP;
1970
1971 IF (l_results_count < x_results.result_type.COUNT) THEN
1972 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
1973 ELSE
1974 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1975 END IF;
1976
1977 IF PO_LOG.d_proc THEN
1978 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
1979 PO_LOG.log(PO_LOG.c_PROC_END,d_mod,NULL,'x_results',x_results);
1980 END IF;
1981
1982 EXCEPTION
1983 WHEN OTHERS THEN
1984 IF PO_LOG.d_exc THEN
1985 PO_LOG.exc(d_mod,0,NULL);
1986 END IF;
1987 RAISE;
1988
1989 END temp_labor_job_id_not_null;
1990
1991 -------------------------------------------------------------------------------
1992 -- Ensures that the Source Doc Line is not null if the Source Doc is not null
1993 -- and the Source Doc is not a contract.
1994 -------------------------------------------------------------------------------
1995 PROCEDURE src_doc_line_not_null(
1996 p_line_id_tbl IN PO_TBL_NUMBER
1997 , p_from_header_id_tbl IN PO_TBL_NUMBER
1998 , p_from_line_id_tbl IN PO_TBL_NUMBER
1999 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
2000 , x_result_type OUT NOCOPY VARCHAR2
2001 )
2002 IS
2003 d_mod CONSTANT VARCHAR2(100) := D_src_doc_line_not_null;
2004 l_results_count NUMBER;
2005 BEGIN
2006
2007 IF (x_results IS NULL) THEN
2008 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
2009 END IF;
2010
2011 IF PO_LOG.d_proc THEN
2012 PO_LOG.proc_begin(d_mod,'p_line_id_tbl',p_line_id_tbl);
2013 PO_LOG.proc_begin(d_mod,'p_from_header_id_tbl',p_from_header_id_tbl);
2014 PO_LOG.proc_begin(d_mod,'p_from_line_id_tbl',p_from_line_id_tbl);
2015 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
2016 END IF;
2017
2018 l_results_count := x_results.result_type.COUNT;
2019
2020 FOR i IN 1 .. p_line_id_tbl.COUNT LOOP
2021 IF (p_from_line_id_tbl(i) IS NULL
2022 AND p_from_header_id_tbl(i) IS NOT NULL
2023 )
2024 THEN
2025 x_results.add_result(
2026 p_entity_type => c_ENTITY_TYPE_LINE
2027 , p_entity_id => p_line_id_tbl(i)
2028 , p_column_name => c_FROM_LINE_ID
2029 , p_message_name => PO_MESSAGE_S.PO_ALL_NOT_NULL
2030 );
2031 END IF;
2032 END LOOP;
2033
2034 IF (l_results_count < x_results.result_type.COUNT) THEN
2035 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
2036 ELSE
2037 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
2038 END IF;
2039
2040 IF PO_LOG.d_proc THEN
2041 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
2042 PO_LOG.log(PO_LOG.c_PROC_END,d_mod,NULL,'x_results',x_results);
2043 END IF;
2044
2045 EXCEPTION
2046 WHEN OTHERS THEN
2047 IF PO_LOG.d_exc THEN
2048 PO_LOG.exc(d_mod,0,NULL);
2049 END IF;
2050 RAISE;
2051 END src_doc_line_not_null;
2052
2053 -------------------------------------------------------------------------------
2054 -- Ensures that the Line Type is not null.
2055 -------------------------------------------------------------------------------
2056 PROCEDURE line_type_id_not_null(
2057 p_line_id_tbl IN PO_TBL_NUMBER
2058 , p_line_type_id_tbl IN PO_TBL_NUMBER
2059 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
2060 , x_result_type OUT NOCOPY VARCHAR2
2061 )
2062 IS
2063 BEGIN
2064
2065 PO_VALIDATION_HELPER.not_null(
2066 p_calling_module => D_line_type_id_not_null
2067 , p_value_tbl => PO_TYPE_CONVERTER.to_po_tbl_varchar4000(p_line_type_id_tbl)
2068 , p_entity_id_tbl => p_line_id_tbl
2069 , p_entity_type => c_entity_type_LINE
2070 , p_column_name => c_LINE_TYPE_ID
2071 , p_message_name => PO_MESSAGE_S.PO_ALL_NOT_NULL
2072 , x_results => x_results
2073 , x_result_type => x_result_type
2074 );
2075
2076 END line_type_id_not_null;
2077
2078 -------------------------------------------------------------------------------
2079 -- Ensures that the Start Date is not null for TEMP LABOR lines.
2080 -------------------------------------------------------------------------------
2081 PROCEDURE temp_lbr_start_date_not_null(
2082 p_line_id_tbl IN PO_TBL_NUMBER
2083 , p_start_date_tbl IN PO_TBL_DATE
2084 , p_purchase_basis_tbl IN PO_TBL_VARCHAR30
2085 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
2086 , x_result_type OUT NOCOPY VARCHAR2
2087 )
2088 IS
2089 d_mod CONSTANT VARCHAR2(100) := D_temp_lbr_start_date_not_null;
2090 l_results_count NUMBER;
2091 BEGIN
2092
2093 IF PO_LOG.d_proc THEN
2094 PO_LOG.proc_begin(d_mod,'p_line_id_tbl',p_line_id_tbl);
2095 PO_LOG.proc_begin(d_mod,'p_start_date_tbl',p_start_date_tbl);
2096 PO_LOG.proc_begin(d_mod,'p_purchase_basis_tbl',p_purchase_basis_tbl);
2097 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
2098 END IF;
2099
2100 IF (x_results IS NULL) THEN
2101 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
2102 END IF;
2103
2104 l_results_count := x_results.result_type.COUNT;
2105
2106 FOR i IN 1 .. p_line_id_tbl.COUNT LOOP
2107 IF ( p_purchase_basis_tbl(i) = c_TEMP_LABOR
2108 AND p_start_date_tbl(i) IS NULL
2109 )
2110 THEN
2111 x_results.add_result(
2112 p_entity_type => c_ENTITY_TYPE_LINE
2113 , p_entity_id => p_line_id_tbl(i)
2114 , p_column_name => c_START_DATE
2115 , p_message_name => PO_MESSAGE_S.PO_ALL_NOT_NULL
2116 );
2117 END IF;
2118 END LOOP;
2119
2120 IF (l_results_count < x_results.result_type.COUNT) THEN
2121 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
2122 ELSE
2123 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
2124 END IF;
2125
2126 IF PO_LOG.d_proc THEN
2127 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
2128 PO_LOG.log(PO_LOG.c_PROC_END,d_mod,NULL,'x_results',x_results);
2129 END IF;
2130
2131 EXCEPTION
2132 WHEN OTHERS THEN
2133 IF PO_LOG.d_exc THEN
2134 PO_LOG.exc(d_mod,0,NULL);
2135 END IF;
2136 RAISE;
2137
2138 END temp_lbr_start_date_not_null;
2139
2140 -----------------------------------------------------------------------------
2141 -- OPM Integration R12
2142 -- Validates that secondary quantity is not null and greater than zero for
2143 -- an opm item.
2144 -----------------------------------------------------------------------------
2145 PROCEDURE line_sec_quantity_gt_zero(
2146 p_line_id_tbl IN PO_TBL_NUMBER
2147 , p_item_id_tbl IN PO_TBL_NUMBER
2148 , p_sec_quantity_tbl IN PO_TBL_NUMBER
2149 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
2150 , x_result_type OUT NOCOPY VARCHAR2
2151 )
2152 IS
2153 d_mod CONSTANT VARCHAR2(100) := D_line_sec_quantity_gt_zero;
2154 l_inv_org_id_tbl PO_TBL_NUMBER;
2155 l_def_inv_org_id NUMBER;
2156 l_input_size NUMBER;
2157
2158 BEGIN
2159
2160 IF PO_LOG.d_proc THEN
2161 PO_LOG.proc_begin(d_mod,'p_line_id_tbl',p_line_id_tbl);
2162 PO_LOG.proc_begin(d_mod,'p_sec_quantity_tbl',p_sec_quantity_tbl);
2163 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
2164 END IF;
2165
2166 -- SQL What : Get the default inv org id
2167 -- SQL Why : To pass to the Opm Validations
2168 select inventory_organization_id
2169 into l_def_inv_org_id
2170 from financials_system_parameters;
2171
2172 l_input_size := p_line_id_tbl.COUNT;
2173
2174 l_inv_org_id_tbl := PO_TBL_NUMBER();
2175 l_inv_org_id_tbl.extend(l_input_size);
2176
2177 FOR i IN 1 .. p_line_id_tbl.COUNT LOOP
2178 l_inv_org_id_tbl(i) := l_def_inv_org_id;
2179 END LOOP;
2180
2181 PO_VALIDATION_HELPER.gt_zero_opm_filter(
2182 p_calling_module => D_line_sec_quantity_gt_zero
2183 , p_value_tbl => p_sec_quantity_tbl
2184 , p_entity_id_tbl => p_line_id_tbl
2185 , p_item_id_tbl => p_item_id_tbl
2186 , p_inv_org_id_tbl => l_inv_org_id_tbl
2187 , p_entity_type => c_ENTITY_TYPE_LINE
2188 , p_column_name => c_SECONDARY_QUANTITY
2189 , x_results => x_results
2190 , x_result_type => x_result_type
2191 );
2192
2193 EXCEPTION
2194 WHEN OTHERS THEN
2195 IF PO_LOG.d_exc THEN
2196 PO_LOG.exc(d_mod,0,NULL);
2197 END IF;
2198 RAISE;
2199
2200 END line_sec_quantity_gt_zero;
2201
2202 -----------------------------------------------------------------------------
2203 -- OPM Integration R12
2204 -- Validates secondary quantity and the quantity combination for
2205 -- an opm item
2206 -----------------------------------------------------------------------------
2207 PROCEDURE line_qtys_within_deviation (
2208 p_line_id_tbl IN PO_TBL_NUMBER
2209 , p_item_id_tbl IN PO_TBL_NUMBER
2210 , p_quantity_tbl IN PO_TBL_NUMBER
2211 , p_primary_uom_tbl IN PO_TBL_VARCHAR30
2212 , p_sec_quantity_tbl IN PO_TBL_NUMBER
2213 , p_secondary_uom_tbl IN PO_TBL_VARCHAR30
2214 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
2215 , x_result_type OUT NOCOPY VARCHAR2
2216 )
2217 IS
2218
2219 d_mod CONSTANT VARCHAR2(100) := D_line_qtys_within_deviation;
2220 l_inv_org_id_tbl PO_TBL_NUMBER;
2221 l_input_size NUMBER;
2222 l_def_inv_org_id NUMBER;
2223
2224 BEGIN
2225
2226 IF PO_LOG.d_proc THEN
2227 PO_LOG.proc_begin(d_mod,'p_line_id_tbl',p_line_id_tbl);
2228 PO_LOG.proc_begin(d_mod,'p_sec_quantity_tbl',p_sec_quantity_tbl);
2229 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
2230 END IF;
2231
2232 -- SQL What : Get the default inv org id
2233 -- SQL Why : To pass to the Opm Validations
2234 select inventory_organization_id
2235 into l_def_inv_org_id
2236 from financials_system_parameters;
2237
2238 l_input_size := p_line_id_tbl.COUNT;
2239
2240 l_inv_org_id_tbl := PO_TBL_NUMBER();
2241 l_inv_org_id_tbl.extend(l_input_size);
2242
2243 FOR i IN 1 .. p_line_id_tbl.COUNT LOOP
2244 l_inv_org_id_tbl(i) := l_def_inv_org_id;
2245 END LOOP;
2246
2247 PO_VALIDATION_HELPER.qtys_within_deviation (
2248 p_calling_module => D_line_qtys_within_deviation
2249 , p_entity_id_tbl => p_line_id_tbl
2250 , p_item_id_tbl => p_item_id_tbl
2251 , p_inv_org_id_tbl => l_inv_org_id_tbl
2252 , p_quantity_tbl => p_quantity_tbl
2253 , p_primary_uom_tbl => p_primary_uom_tbl
2254 , p_sec_quantity_tbl => p_sec_quantity_tbl
2255 , p_secondary_uom_tbl => p_secondary_uom_tbl
2256 , p_column_name => c_QUANTITY
2257 , x_results => x_results
2258 , x_result_type => x_result_type
2259 );
2260
2261 EXCEPTION
2262 WHEN OTHERS THEN
2263 IF PO_LOG.d_exc THEN
2264 PO_LOG.exc(d_mod,0,NULL);
2265 END IF;
2266 RAISE;
2267
2268 END line_qtys_within_deviation;
2269
2270 -----------------------------------------------------------------------------
2271 -- Validates that source doc line is not null if the source document is filled
2272 -- in and the source document is a blanket or quotation
2273 -----------------------------------------------------------------------------
2274 PROCEDURE from_line_id_not_null (
2275 p_line_id_tbl IN PO_TBL_NUMBER
2276 , p_from_header_id_tbl IN PO_TBL_NUMBER
2277 , p_from_line_id_tbl IN PO_TBL_NUMBER
2278 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
2279 , x_result_type OUT NOCOPY VARCHAR2
2280 )
2281 IS
2282
2283 d_mod CONSTANT VARCHAR2(100) := D_from_line_id_not_null;
2284 l_results_count NUMBER;
2285 l_src_doc_type_lookup_code VARCHAR2(30) := null;
2286 l_from_header_id NUMBER;
2287 BEGIN
2288
2289 IF PO_LOG.d_proc THEN
2290 PO_LOG.proc_begin(d_mod,'p_line_id_tbl',p_line_id_tbl);
2291 PO_LOG.proc_begin(d_mod,'p_from_header_id_tbl',p_from_header_id_tbl);
2292 PO_LOG.proc_begin(d_mod,'p_from_line_id_tbl',p_from_line_id_tbl);
2293 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
2294 END IF;
2295
2296 IF (x_results IS NULL) THEN
2297 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
2298 END IF;
2299
2300 l_results_count := x_results.result_type.COUNT;
2301
2302 FOR i IN 1 .. p_from_header_id_tbl.COUNT LOOP
2303
2304 l_from_header_id := p_from_header_id_tbl(i);
2305
2306 -- Only do validation if source document is chosen
2307 IF (l_from_header_id <> NULL)
2308 THEN
2309
2310 -- SQL What: Get type of source document
2311 -- SQL Why: To pass to validation
2312 SELECT type_lookup_code
2313 INTO l_src_doc_type_lookup_code
2314 FROM po_headers_all
2315 WHERE po_header_id = l_from_header_id;
2316
2317 -- Throw error if following is true:
2318 -- 1) Source doc is blanket or quotation
2319 -- 2) Source doc line is null
2320 IF ((l_src_doc_type_lookup_code = c_BLANKET
2321 OR l_src_doc_type_lookup_code = c_QUOTATION)
2322 AND p_from_line_id_tbl(i) IS NULL)
2323 THEN
2324 x_results.add_result(
2325 p_entity_type => c_ENTITY_TYPE_LINE
2326 , p_entity_id => p_line_id_tbl(i)
2327 , p_column_name => c_FROM_LINE_ID
2328 , p_message_name => PO_MESSAGE_S.PO_ALL_NOT_NULL
2329 );
2330 END IF; -- IF ((l_src_doc_type_lookup_code = c_BLANKET
2331
2332 END IF; -- IF (l_from_header_id <> NULL)
2333
2334 END LOOP;
2335
2336 IF (l_results_count < x_results.result_type.COUNT) THEN
2337 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
2338 ELSE
2339 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
2340 END IF;
2341
2342 IF PO_LOG.d_proc THEN
2343 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
2344 PO_LOG.log(PO_LOG.c_PROC_END,d_mod,NULL,'x_results',x_results);
2345 END IF;
2346
2347 EXCEPTION
2348 WHEN OTHERS THEN
2349 IF PO_LOG.d_exc THEN
2350 PO_LOG.exc(d_mod,0,NULL);
2351 END IF;
2352 RAISE;
2353
2354 END from_line_id_not_null;
2355
2356
2357 --Bug 5221843 START
2358 -------------------------------------------------------------------------------
2359 -- This procedure determines if Maximum Retainage Amount
2360 -- is greater than or equal to zero. If not, return a failure.
2361 -------------------------------------------------------------------------------
2362 PROCEDURE max_retain_amt_ge_zero(
2363 p_line_id_tbl IN PO_TBL_NUMBER
2364 , p_max_retain_amt_tbl IN PO_TBL_NUMBER
2365 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
2366 , x_result_type OUT NOCOPY VARCHAR2
2367 )
2368 IS
2369 BEGIN
2370
2371 PO_VALIDATION_HELPER.greater_or_equal_zero(
2372 p_calling_module => D_max_retain_amt_ge_zero
2373 , p_null_allowed_flag => PO_CORE_S.g_parameter_YES
2374 , p_value_tbl => p_max_retain_amt_tbl
2375 , p_entity_id_tbl => p_line_id_tbl
2376 , p_entity_type => c_ENTITY_TYPE_LINE
2377 , p_column_name => c_MAX_RETAINAGE_AMOUNT
2378 , p_message_name => PO_MESSAGE_S.PO_ALL_ENTER_VALUE_GE_ZERO
2379 , x_results => x_results
2380 , x_result_type => x_result_type
2381 );
2382
2383 END max_retain_amt_ge_zero;
2384
2385 --Bug 5221843 END
2386
2387 --Bug 5453079 START
2388 -------------------------------------------------------------------------------
2389 -- This procedure determines if Maximum Retainage Amount
2390 -- is greater than already retained amount. If not, return a failure.
2391 -------------------------------------------------------------------------------
2392 PROCEDURE max_retain_amt_ge_retained(
2393 p_line_id_tbl IN PO_TBL_NUMBER
2394 , p_max_retain_amt_tbl IN PO_TBL_NUMBER
2395 , x_result_set_id IN OUT NOCOPY NUMBER
2396 , x_result_type OUT NOCOPY VARCHAR2
2397 )
2398 IS
2399 d_mod CONSTANT VARCHAR2(100) := D_quantity_ge_quantity_enc;
2400
2401 BEGIN
2402
2403 IF PO_LOG.d_proc THEN
2404 PO_LOG.proc_begin(d_mod,'p_line_id_tbl',p_line_id_tbl);
2405 PO_LOG.proc_begin(d_mod,'p_max_retain_amt_tbl',p_max_retain_amt_tbl);
2406 PO_LOG.proc_begin(d_mod,'x_result_set_id',x_result_set_id);
2407 END IF;
2408
2409 IF (x_result_set_id IS NULL) THEN
2410 x_result_set_id := PO_VALIDATIONS.next_result_set_id();
2411 END IF;
2412
2413 FORALL i IN 1 .. p_line_id_tbl.COUNT
2414 INSERT INTO PO_VALIDATION_RESULTS_GT
2415 ( result_set_id
2416 , entity_type
2417 , entity_id
2418 , column_name
2419 , column_val
2420 , message_name
2421 , token1_name
2422 , token1_value
2423 , token2_name
2424 , token2_value
2425 )
2426 SELECT
2427 x_result_set_id
2428 , c_ENTITY_TYPE_LINE
2429 , p_line_id_tbl(i)
2430 , c_MAX_RETAINAGE_AMOUNT
2431 , TO_CHAR(p_max_retain_amt_tbl(i))
2432 , PO_MESSAGE_S.PO_MAX_RET_AMT_GE_RETAINED
2433 , PO_MESSAGE_S.c_MAX_RET_AMT_token
2434 , TO_CHAR(p_max_retain_amt_tbl(i))
2435 , PO_MESSAGE_S.c_AMT_RETAINED_token
2436 , TO_CHAR(LOCATIONS_TOTAL.amount_retained)
2437 FROM
2438 ( SELECT NVL(SUM(POLL.retainage_withheld_amount),0) amount_retained
2439 FROM
2440 PO_LINE_LOCATIONS_ALL POLL
2441 WHERE
2442 POLL.po_line_id = p_line_id_tbl(i)
2443 ) LOCATIONS_TOTAL
2444 WHERE
2445 p_max_retain_amt_tbl(i) < LOCATIONS_TOTAL.amount_retained
2446 ;
2447
2448 IF (SQL%ROWCOUNT > 0) THEN
2449 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
2450 ELSE
2451 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
2452 END IF;
2453
2454 IF PO_LOG.d_proc THEN
2455 PO_VALIDATIONS.log_validation_results_gt(d_mod,9,x_result_set_id);
2456 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
2457 PO_LOG.proc_end(d_mod,'x_result_set_id',x_result_set_id);
2458 END IF;
2459
2460 EXCEPTION
2461 WHEN OTHERS THEN
2462 IF PO_LOG.d_exc THEN
2463 PO_LOG.exc(d_mod,0,NULL);
2464 END IF;
2465 RAISE;
2466
2467 END max_retain_amt_ge_retained;
2468
2469 --Bug 5453079 END
2470
2471 END PO_VAL_LINES;