1 PACKAGE BODY PO_VAL_DISTRIBUTIONS AS
2 -- $Header: PO_VAL_DISTRIBUTIONS.plb 120.23.12020000.10 2013/05/30 17:36:44 vpeddi ship $
3
4 c_ENTITY_TYPE_DISTRIBUTION CONSTANT VARCHAR2(30) := PO_VALIDATIONS.C_ENTITY_TYPE_DISTRIBUTION;
5 c_NEW CONSTANT VARCHAR2(3) := 'NEW';
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_PREPAYMENT CONSTANT VARCHAR2(30) := 'PREPAYMENT'; -- <Complex Work R12>
10 c_DEST_TYPE_SHOP_FLOOR CONSTANT VARCHAR2(30) := 'SHOP FLOOR';
11 c_DEST_TYPE_EXPENSE CONSTANT VARCHAR2(30) := 'EXPENSE';
12 c_DEST_TYPE_INVENTORY CONSTANT VARCHAR2(30) :='INVENTORY';
13
14 -- Constants for column names
15 c_DISTRIBUTION_NUM CONSTANT VARCHAR2(30) := 'DISTRIBUTION_NUM';
16 c_QUANTITY_ORDERED CONSTANT VARCHAR2(30) := 'QUANTITY_ORDERED';
17 c_AMOUNT_ORDERED CONSTANT VARCHAR2(30) := 'AMOUNT_ORDERED';
18 c_END_ITEM_UNIT_NUMBER CONSTANT VARCHAR2(30) := 'END_ITEM_UNIT_NUMBER';
19 c_WIP_ENTITY_ID CONSTANT VARCHAR2(30) := 'WIP_ENTITY_ID';
20 c_WIP_OPERATION_SEQ_NUM CONSTANT VARCHAR2(30) := 'WIP_OPERATION_SEQ_NUM';
21 c_WIP_RESOURCE_SEQ_NUM CONSTANT VARCHAR2(30) := 'WIP_RESOURCE_SEQ_NUM';
22 c_AMOUNT_TO_ENCUMBER CONSTANT VARCHAR2(30) := 'AMOUNT_TO_ENCUMBER';
23 c_BUDGET_ACCOUNT_ID CONSTANT VARCHAR2(30) := 'BUDGET_ACCOUNT_ID';
24 c_CODE_COMBINATION_ID CONSTANT VARCHAR2(30) := 'CODE_COMBINATION_ID';
25 c_GL_ENCUMBERED_DATE CONSTANT VARCHAR2(30) := 'GL_ENCUMBERED_DATE';
26 c_UNENCUMBERED_AMOUNT CONSTANT VARCHAR2(30) := 'UNENCUMBERED_AMOUNT';
27 c_AWARD_ID CONSTANT VARCHAR2(30) := 'AWARD_ID';
28
29 /* CLM Partial Funding Changes -- START */
30 c_FUNDED_VALUE CONSTANT VARCHAR2(30) := 'FUNDED_VALUE';
31 c_QUANTITY_FUNDED CONSTANT VARCHAR2(30) := 'c_QUANTITY_FUNDED';
32 c_AMOUNT_FUNDED CONSTANT VARCHAR2(30) := 'AMOUNT_FUNDED';
33 /* CLM Partial Funding Changes -- END */
34
35 --CLM PDOI Integration
36 c_CLM_OPTION_INDICATOR CONSTANT VARCHAR2(30) := 'CLM_OPTION_INDICATOR';
37 c_GROUP_LINE_ID VARCHAR2(30) := 'GROUP_LINE_ID';
38 c_CLM_DEFENCE_FUNDING VARCHAR2(30) := 'CLM_DEFENCE_FUNDING';
39 -- The module base for this package.
40 D_PACKAGE_BASE CONSTANT VARCHAR2(50) :=
41 PO_LOG.get_package_base('PO_VAL_DISTRIBUTIONS');
42
43 -- The module base for the subprogram.
44 D_dist_num_unique CONSTANT VARCHAR2(100) :=
45 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'dist_num_unique');
46
47 -- The module base for the subprogram.
48 D_dist_num_gt_zero CONSTANT VARCHAR2(100) :=
49 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'dist_num_gt_zero');
50
51 -- The module base for the subprogram.
52 D_quantity_gt_zero CONSTANT VARCHAR2(100) :=
53 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'quantity_gt_zero');
54
55 -- The module base for the subprogram.
56 D_amount_gt_zero CONSTANT VARCHAR2(100) :=
57 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'amount_gt_zero');
58
59 -- The module base for the subprogram.
60 D_check_fv_validations CONSTANT VARCHAR2(100) :=
61 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'check_fv_validations');
62
63 D_check_proj_rel_validations CONSTANT VARCHAR2(100) :=
64 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'check_proj_related_validations');
65
66 -- <Complex Work R12 Start>: Combine billed/del checks into exec checks
67 -- The module base for the subprogram.
68 D_quantity_ge_quantity_exec CONSTANT VARCHAR2(100) :=
69 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'quantity_ge_quantity_exec');
70
71 D_amount_ge_amount_exec CONSTANT VARCHAR2(100) :=
72 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'amount_ge_amount_exec');
73
74 -- <Complex Work R12 End>
75
76 D_pjm_unit_number_effective CONSTANT VARCHAR2(100) :=
77 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'pjm_unit_number_effective');
78
79 D_oop_enter_all_fields CONSTANT VARCHAR2(100) :=
80 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'oop_enter_all_fields');
81
82 D_amount_to_encumber_ge_zero CONSTANT VARCHAR2(100) :=
83 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'amount_to_encumber_ge_zero');
84 D_budget_account_id_not_null CONSTANT VARCHAR2(100) :=
85 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'budget_account_id_not_null');
86 D_gl_encumbered_date_not_null CONSTANT VARCHAR2(100) :=
87 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'gl_encumbered_date_not_null');
88 D_unencum_amt_le_amt_to_encum CONSTANT VARCHAR2(100) :=
89 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'unencum_amt_le_amt_to_encum');
90
91 /* CLM Partial Funding Changes -- START */
92 D_funded_value_ge_zero CONSTANT VARCHAR2(100) :=
93 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'funded_value_ge_zero');
94 D_funded_val_le_tot_order_val CONSTANT VARCHAR2(100) :=
95 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'funded_val_le_tot_order_val');
96 D_qty_funded_ge_exc_qty CONSTANT VARCHAR2(100) :=
97 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'qty_funded_ge_exc_qty');
98 D_amount_funded_ge_exc_amount CONSTANT VARCHAR2(100) :=
99 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'amount_funded_ge_exc_amount');
100 D_funded_value_gt_backing_req CONSTANT VARCHAR2(100) :=
101 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'funded_value_gt_backing_req');
102 /* CLM Partial Funding Changes -- END */
103
104 -- CLM-LnSc
105 D_funded_val_gt_req_funds_rmn CONSTANT VARCHAR2(100) :=
106 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'funded_val_gt_req_funds_rmn');
107
108 --CLM PDOI Integration
109 D_check_clin_slin_funding CONSTANT VARCHAR2(100) :=
110 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'check_clin_slin_funding');
111 D_validate_defence_funding CONSTANT VARCHAR2(100) :=
112 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'validate_defence_funding');
113 D_validate_option_distribution CONSTANT VARCHAR2(100) :=
114 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'validate_option_distribution');
115 D_check_dist_val_inc_for_par CONSTANT VARCHAR2(100) :=
116 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'check_dist_val_inc_for_par');
117
118 -----------------------------------------------------------------------------
119 -- Validates that all the distribution numbers for a given shipment are
120 -- unique.
121 -----------------------------------------------------------------------------
122 -- Assumption:
123 -- All of the unposted distribution data will be passed in
124 -- to this routine in order to get accurate results.
125 PROCEDURE dist_num_unique(
126 p_dist_id_tbl IN PO_TBL_NUMBER
127 , p_line_loc_id_tbl IN PO_TBL_NUMBER
128 , p_dist_num_tbl IN PO_TBL_NUMBER
129 , p_par_draft_id_tbl IN PO_TBL_VARCHAR4000 DEFAULT NULL --<PAR Project>
130 , p_draft_id_tbl IN PO_TBL_NUMBER --<PAR Project>
131 , x_result_set_id IN OUT NOCOPY NUMBER
132 , x_result_type OUT NOCOPY VARCHAR2
133 )
134 IS
135 BEGIN
136 PO_VALIDATION_HELPER.child_num_unique(
137 p_calling_module => D_dist_num_unique
138 , p_entity_type => c_entity_type_DISTRIBUTION
139 , p_entity_id_tbl => p_dist_id_tbl
140 , p_parent_id_tbl => p_line_loc_id_tbl
141 , p_entity_num_tbl => p_dist_num_tbl
142 , p_par_draft_id_tbl => p_par_draft_id_tbl -- <PAR Project>
143 , p_draft_id_tbl => p_draft_id_tbl --<PAR Project>
144 , x_result_set_id => x_result_set_id
145 , x_result_type => x_result_type
146 );
147
148 END dist_num_unique;
149
150
151 -----------------------------------------------------------------------------
152 -- Checks for null or non-positive distribution numbers.
153 -----------------------------------------------------------------------------
154 PROCEDURE dist_num_gt_zero(
155 p_dist_id_tbl IN PO_TBL_NUMBER
156 , p_dist_num_tbl IN PO_TBL_NUMBER
157 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
158 , x_result_type OUT NOCOPY VARCHAR2
159 )
160 IS
161 BEGIN
162
163 PO_VALIDATION_HELPER.greater_than_zero(
164 p_calling_module => D_dist_num_gt_zero
165 , p_null_allowed_flag => PO_CORE_S.g_parameter_NO
166 , p_value_tbl => p_dist_num_tbl
167 , p_entity_id_tbl => p_dist_id_tbl
168 , p_entity_type => c_ENTITY_TYPE_DISTRIBUTION
169 , p_column_name => c_DISTRIBUTION_NUM
170 , p_message_name => PO_MESSAGE_S.PO_ALL_ENTER_VALUE_GT_ZERO
171 , x_results => x_results
172 , x_result_type => x_result_type
173 );
174
175 END dist_num_gt_zero;
176
177
178 -----------------------------------------------------------------------------
179 -- Validates that quantity is not null and greater than zero if it is not
180 -- a Rate or Fixed Price line.
181 -----------------------------------------------------------------------------
182 PROCEDURE quantity_gt_zero(
183 p_dist_id_tbl IN PO_TBL_NUMBER
184 , p_qty_ordered_tbl IN PO_TBL_NUMBER
185 , p_value_basis_tbl IN PO_TBL_VARCHAR30 -- <Complex Work R12>
186 , p_cost_constraint_tbl IN PO_TBL_VARCHAR30
187 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
188 , x_result_type OUT NOCOPY VARCHAR2
189 , p_header_id_tbl IN PO_TBL_NUMBER --<Bug 15871378>
190 )
191 IS
192 l_input_size NUMBER;
193 l_clm_info_flag_tbl PO_TBL_VARCHAR1;
194 BEGIN
195
196 l_input_size := p_dist_id_tbl.COUNT;
197
198 l_clm_info_flag_tbl := PO_TBL_VARCHAR1();
199 l_clm_info_flag_tbl.extend(l_input_size);
200
201 FOR i IN 1 .. l_input_size LOOP
202 l_clm_info_flag_tbl(i) := 'N';
203 END LOOP;
204
205 PO_VALIDATION_HELPER.gt_zero_order_type_filter(
206 p_calling_module => D_quantity_gt_zero
207 , p_value_tbl => p_qty_ordered_tbl
208 , p_entity_id_tbl => p_dist_id_tbl
209 , p_order_type_lookup_code_tbl => p_value_basis_tbl -- <Complex Work R12>
210 , p_clm_info_flag_tbl => l_clm_info_flag_tbl
211 , p_cost_constraint_tbl => p_cost_constraint_tbl
212 , p_check_quantity_types_flag => PO_CORE_S.g_parameter_YES
213 , p_entity_type => c_ENTITY_TYPE_DISTRIBUTION
214 , p_column_name => c_QUANTITY_ORDERED
215 , x_results => x_results
216 , x_result_type => x_result_type
217 , p_header_id_tbl => p_header_id_tbl --<Bug 15871378>
218 );
219
220 END quantity_gt_zero;
221
222
223 -- <Complex Work R12 Start>
224 -- Combined quantity_ge_quantity_billed and quantity_ge_quantity_del into
225 -- quantity_ge_quantity_exec
226
227 -----------------------------------------------------------------------------
228 -- Validates that quantity is greater than or equal to quantity billed,
229 -- delivered and financed.
230 -- This check is only performed if quantity is being reduced below the
231 -- current transaction quantity, since over-billing/delivery is allowed.
232 -----------------------------------------------------------------------------
233 PROCEDURE quantity_ge_quantity_exec(
234 p_dist_id_tbl IN PO_TBL_NUMBER
235 , p_dist_type_tbl IN PO_TBL_VARCHAR30
236 , p_qty_ordered_tbl IN PO_TBL_NUMBER
237 , x_result_set_id IN OUT NOCOPY NUMBER
238 , x_result_type OUT NOCOPY VARCHAR2
239 )
240 IS
241 d_mod CONSTANT VARCHAR2(100) := D_quantity_ge_quantity_exec;
242 BEGIN
243
244 IF PO_LOG.d_proc THEN
245 PO_LOG.proc_begin(d_mod,'p_dist_id_tbl',p_dist_id_tbl);
246 PO_LOG.proc_begin(d_mod,'p_dist_type_tbl',p_dist_type_tbl);
247 PO_LOG.proc_begin(d_mod,'p_qty_ordered_tbl',p_qty_ordered_tbl);
248 PO_LOG.proc_begin(d_mod,'x_result_set_id',x_result_set_id);
249 END IF;
250
251 IF (x_result_set_id IS NULL) THEN
252 x_result_set_id := PO_VALIDATIONS.next_result_set_id();
253 END IF;
254
255 FORALL i IN 1 .. p_dist_id_tbl.COUNT
256 INSERT INTO PO_VALIDATION_RESULTS_GT
257 ( result_set_id
258 , entity_type
259 , entity_id
260 , column_name
261 , column_val
262 , message_name
263 --PBWC Message Change Impact: Adding a token
264 , token1_name
265 , token1_value
266 )
267 SELECT
268 x_result_set_id
269 , c_ENTITY_TYPE_DISTRIBUTION
270 , p_dist_id_tbl(i)
271 , C_QUANTITY_ORDERED
272 , TO_CHAR(p_qty_ordered_tbl(i))
273 , (CASE
274 WHEN NVL(POD.quantity_delivered, 0) >
275 GREATEST(NVL(POD.quantity_billed, 0), NVL(POD.quantity_financed, 0))
276 THEN PO_MESSAGE_S.PO_PO_QTY_ORD_LT_QTY_DEL_NA
277 ELSE PO_MESSAGE_S.PO_PO_QTY_ORD_LT_QTY_BILLED_NA
278 END
279 )
280 --PBWC Message Change Impact: Adding a token
281 , (CASE
282 WHEN NVL(POD.quantity_delivered, 0) >
283 GREATEST(NVL(POD.quantity_billed, 0), NVL(POD.quantity_financed, 0))
284 THEN PO_MESSAGE_S.c_QTY_DEL_token
285 ELSE PO_MESSAGE_S.c_QTY_BILLED_token
286 END
287 )
288 , (CASE
289 WHEN NVL(POD.quantity_delivered, 0) >
290 GREATEST(NVL(POD.quantity_billed, 0), NVL(POD.quantity_financed, 0))
291 THEN TO_CHAR(POD.quantity_delivered)
292 ELSE TO_CHAR(POD.quantity_billed)
293 END
294 )
295 FROM
296 PO_DISTRIBUTIONS_ALL POD
297 WHERE
298 POD.po_distribution_id = p_dist_id_tbl(i)
299 AND p_dist_type_tbl(i) IN (c_STANDARD, c_PREPAYMENT) -- <Complex Work R12>
300 -- Quantity is being reduced below the current transaction quantity:
301 AND p_qty_ordered_tbl(i) < POD.quantity_ordered
302 AND p_qty_ordered_tbl(i) < -- <Complex Work R12>
303 GREATEST(NVL(POD.quantity_delivered, 0),
304 NVL(POD.quantity_billed, 0),
305 NVL(POD.quantity_financed, 0))
306 ;
307
308 IF (SQL%ROWCOUNT > 0) THEN
309 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
310 ELSE
311 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
312 END IF;
313
314 IF PO_LOG.d_proc THEN
315 PO_VALIDATIONS.log_validation_results_gt(d_mod,9,x_result_set_id);
316 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
317 PO_LOG.proc_end(d_mod,'x_result_set_id',x_result_set_id);
318 END IF;
319
320 EXCEPTION
321 WHEN OTHERS THEN
322 IF PO_LOG.d_exc THEN
323 PO_LOG.exc(d_mod,0,NULL);
324 END IF;
325 RAISE;
326
327 END quantity_ge_quantity_exec;
328
329 -- <Complex Work R12 End>
330
331 -----------------------------------------------------------------------------
332 -- Validates that amount is not null and greater than zero if the line is
333 -- Rate or Fixed Price.
334 -----------------------------------------------------------------------------
335 PROCEDURE amount_gt_zero(
336 p_dist_id_tbl IN PO_TBL_NUMBER
337 , p_amt_ordered_tbl IN PO_TBL_NUMBER
338 , p_value_basis_tbl IN PO_TBL_VARCHAR30 -- <Complex Work R12>
339 , p_cost_constraint_tbl IN PO_TBL_VARCHAR30
340 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
341 , x_result_type OUT NOCOPY VARCHAR2
342 , p_header_id_tbl IN PO_TBL_NUMBER --<Bug 15871378>
343 )
344 IS
345 l_input_size NUMBER;
346 l_clm_info_flag_tbl PO_TBL_VARCHAR1;
347 BEGIN
348
349 l_input_size := p_dist_id_tbl.COUNT;
350
351 l_clm_info_flag_tbl := PO_TBL_VARCHAR1();
352 l_clm_info_flag_tbl.extend(l_input_size);
353
354 FOR i IN 1 .. l_input_size LOOP
355 l_clm_info_flag_tbl(i) := 'N';
356 END LOOP;
357
358 PO_VALIDATION_HELPER.gt_zero_order_type_filter(
359 p_calling_module => D_amount_gt_zero
360 , p_value_tbl => p_amt_ordered_tbl
361 , p_entity_id_tbl => p_dist_id_tbl
362 , p_order_type_lookup_code_tbl => p_value_basis_tbl -- <Complex Work R12>
363 , p_clm_info_flag_tbl => l_clm_info_flag_tbl
364 , p_cost_constraint_tbl => p_cost_constraint_tbl
365 , p_check_quantity_types_flag => PO_CORE_S.g_parameter_NO
366 , p_entity_type => c_ENTITY_TYPE_DISTRIBUTION
367 , p_column_name => c_AMOUNT_ORDERED
368 , x_results => x_results
369 , x_result_type => x_result_type
370 , p_header_id_tbl => p_header_id_tbl --<Bug 15871378>
371 );
372
373 END amount_gt_zero;
374
375 -- <Complex Work R12 Start>
376 -- Combined amount_ge_amount_billed and amount_ge_amount_del into
377 -- amount_ge_amount_exec
378
379 -----------------------------------------------------------------------------
380 -- Validates that amount is greater than or equal to
381 -- amount billed, amount financed, and amount delivered.
382 -- This check is only performed if amount is being reduced below the
383 -- current transaction amount, since over-delivery/billing is allowed.
384 -----------------------------------------------------------------------------
385 PROCEDURE amount_ge_amount_exec(
386 p_dist_id_tbl IN PO_TBL_NUMBER
387 , p_dist_type_tbl IN PO_TBL_VARCHAR30
388 , p_amt_ordered_tbl IN PO_TBL_NUMBER
389 , x_result_set_id IN OUT NOCOPY NUMBER
390 , x_result_type OUT NOCOPY VARCHAR2
391 )
392 IS
393 d_mod CONSTANT VARCHAR2(100) := D_amount_ge_amount_exec;
394 BEGIN
395
396 IF PO_LOG.d_proc THEN
397 PO_LOG.proc_begin(d_mod,'p_dist_id_tbl',p_dist_id_tbl);
398 PO_LOG.proc_begin(d_mod,'p_dist_type_tbl',p_dist_type_tbl);
399 PO_LOG.proc_begin(d_mod,'p_amt_ordered_tbl',p_amt_ordered_tbl);
400 PO_LOG.proc_begin(d_mod,'x_result_set_id',x_result_set_id);
401 END IF;
402
403 IF (x_result_set_id IS NULL) THEN
404 x_result_set_id := PO_VALIDATIONS.next_result_set_id();
405 END IF;
406
407 FORALL i IN 1 .. p_dist_id_tbl.COUNT
408 INSERT INTO PO_VALIDATION_RESULTS_GT
409 ( result_set_id
410 , entity_type
411 , entity_id
412 , column_name
413 , column_val
414 , message_name
415 --PBWC Message Change Impact: Adding a token
416 , token1_name
417 , token1_value
418 )
419 SELECT
420 x_result_set_id
421 , c_ENTITY_TYPE_DISTRIBUTION
422 , p_dist_id_tbl(i)
423 , C_AMOUNT_ORDERED
424 , TO_CHAR(p_amt_ordered_tbl(i))
425 , (CASE
426 WHEN NVL(POD.amount_delivered, 0) >
427 GREATEST(NVL(POD.amount_billed, 0), NVL(POD.amount_financed, 0))
428 THEN PO_MESSAGE_S.PO_PO_AMT_ORD_LT_AMT_DEL_NA
429 ELSE PO_MESSAGE_S.PO_PO_AMT_ORD_LT_AMT_BILLED_NA
430 END
431 )
432 --PBWC Message Change Impact: Adding a token
433 , (CASE
434 WHEN NVL(POD.amount_delivered, 0) >
435 GREATEST(NVL(POD.amount_billed, 0), NVL(POD.amount_financed, 0))
436 THEN PO_MESSAGE_S.c_AMT_DEL_token
437 ELSE PO_MESSAGE_S.c_AMT_BILLED_token
438 END
439 )
440 , (CASE
441 WHEN NVL(POD.amount_delivered, 0) >
442 GREATEST(NVL(POD.amount_billed, 0), NVL(POD.amount_financed, 0))
443 THEN TO_CHAR(POD.amount_delivered)
444 ELSE TO_CHAR(POD.amount_billed)
445 END
446 )
447 FROM
448 PO_DISTRIBUTIONS_ALL POD
449 WHERE
450 POD.po_distribution_id = p_dist_id_tbl(i)
451 AND p_dist_type_tbl(i) IN (c_STANDARD, c_PREPAYMENT) -- <Complex Work R12>
452 -- Amount is being reduced below the current transaction amount:
453 AND p_amt_ordered_tbl(i) < POD.amount_ordered
454 AND p_amt_ordered_tbl(i) < GREATEST(NVL(POD.amount_delivered, 0),
455 NVL(POD.amount_financed, 0),
456 NVL(POD.amount_billed, 0));
457
458 IF (SQL%ROWCOUNT > 0) THEN
459 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
460 ELSE
461 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
462 END IF;
463
464 IF PO_LOG.d_proc THEN
465 PO_VALIDATIONS.log_validation_results_gt(d_mod,9,x_result_set_id);
466 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
467 PO_LOG.proc_end(d_mod,'x_result_set_id',x_result_set_id);
468 END IF;
469
470 EXCEPTION
471 WHEN OTHERS THEN
472 IF PO_LOG.d_exc THEN
473 PO_LOG.exc(d_mod,0,NULL);
474 END IF;
475 RAISE;
476
477 END amount_ge_amount_exec;
478
479 -- <Complex Work R12 End>
480
481
482 ------------------------------------------------------------------------
483 -- Validates that if the item is unit number effective, then the unit number
484 -- on the distribution is not null.
485 --
486 -- Displays the warning 'UEFF-UNIT NUMBER REQUIRED' if all the following are true:
487 -- 1) Item on the line is unit number effective
488 -- 2) Unit number field at the distribution level is null
489 --
490 -- Where clauses derived from PJM_UNIT_EFF.UNIT_EFFECTIVE_ITEM
491 ------------------------------------------------------------------------
492 PROCEDURE pjm_unit_number_effective(
493 p_dist_id_tbl IN PO_TBL_NUMBER
494 , p_end_item_unit_number_tbl IN PO_TBL_VARCHAR30
495 , p_item_id_tbl IN PO_TBL_NUMBER
496 , p_ship_to_org_id_tbl IN PO_TBL_NUMBER
497 -- Bug# 4338241: Checking if it is inventory and PJM is installed
498 , p_destination_type_code_tbl IN PO_TBL_VARCHAR30
499 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
500 , x_result_type OUT NOCOPY VARCHAR2
501 )
502 IS
503 d_mod CONSTANT VARCHAR2(100) := D_pjm_unit_number_effective;
504
505 l_unit_number_effective VARCHAR2(1);
506 l_results_count NUMBER;
507 BEGIN
508
509 IF PO_LOG.d_proc THEN
510 PO_LOG.proc_begin(d_mod,'p_dist_id_tbl',p_dist_id_tbl);
511 PO_LOG.proc_begin(d_mod,'p_end_item_unit_number_tbl',p_end_item_unit_number_tbl);
512 PO_LOG.proc_begin(d_mod,'p_item_id_tbl',p_item_id_tbl);
513 PO_LOG.proc_begin(d_mod,'p_ship_to_org_id_tbl',p_ship_to_org_id_tbl);
514 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
515 END IF;
516
517 IF (x_results IS NULL) THEN
518 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
519 END IF;
520
521 l_results_count := x_results.result_type.COUNT;
522
523 FOR i IN 1 .. p_dist_id_tbl.COUNT LOOP
524
525 -- Bug# 4338241
526 -- Do this validation only if PJM is installed and
527 -- destination type is not EXPENSE
528 -- If the unit number field at the distributions level is null
529 -- Then check if the item and org are unit number effective
530
531 -- Bug 5193851
532 -- Changed the check po_core_s.get_product_install_status('PJM') = 'Y'
533 -- to po_core_s.get_product_install_status('PJM') = 'I'
534 IF (po_core_s.get_product_install_status('PJM') = 'I' AND
535 p_destination_type_code_tbl(i) <> c_DEST_TYPE_EXPENSE AND
536 p_end_item_unit_number_tbl(i) IS NULL) THEN
537
538 l_unit_number_effective :=
539 PO_PROJECT_DETAILS_SV.pjm_unit_eff_item(p_item_id_tbl(i),p_ship_to_org_id_tbl(i));
540
541 IF (l_unit_number_effective = 'Y') THEN
542
543 x_results.add_result(
544 p_entity_type => c_ENTITY_TYPE_DISTRIBUTION
545 , p_entity_id => p_dist_id_tbl(i)
546 , p_column_name => c_END_ITEM_UNIT_NUMBER
547 , p_message_name => PO_MESSAGE_S.PO_ALL_NOT_NULL -- Bug 5193851 - Changed the message
548 );
549
550 END IF;
551
552 END IF;
553
554 END LOOP;
555
556 IF (l_results_count < x_results.result_type.COUNT) THEN
557 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
558 ELSE
559 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
560 END IF;
561
562 IF PO_LOG.d_proc THEN
563 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
564 PO_LOG.log(PO_LOG.c_PROC_END,d_mod,NULL,'x_results',x_results);
565 END IF;
566
567 EXCEPTION
568 WHEN OTHERS THEN
569 IF PO_LOG.d_exc THEN
570 PO_LOG.exc(d_mod,0,NULL);
571 END IF;
572 RAISE;
573
574 END pjm_unit_number_effective;
575
576 PROCEDURE oop_enter_all_fields(
577 p_dist_id_tbl IN PO_TBL_NUMBER
578 , p_line_line_type_id_tbl IN PO_TBL_NUMBER
579 , p_wip_entity_id_tbl IN PO_TBL_NUMBER
580 , p_wip_line_id_tbl IN PO_TBL_NUMBER
581 , p_wip_operation_seq_num_tbl IN PO_TBL_NUMBER
582 , p_destination_type_code_tbl IN PO_TBL_VARCHAR30
583 , p_wip_resource_seq_num_tbl IN PO_TBL_NUMBER
584 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
585 , x_result_type OUT NOCOPY VARCHAR2
586 )
587 IS
588 d_mod CONSTANT VARCHAR2(100) := D_oop_enter_all_fields;
589
590 l_results_count NUMBER;
591 l_outside_operation_flag VARCHAR2(1);
592 BEGIN
593
594 IF PO_LOG.d_proc THEN
595 PO_LOG.proc_begin(d_mod,'p_dist_id_tbl',p_dist_id_tbl);
596 PO_LOG.proc_begin(d_mod,'p_line_line_type_id_tbl',p_line_line_type_id_tbl);
597 PO_LOG.proc_begin(d_mod,'p_wip_entity_id_tbl',p_wip_entity_id_tbl);
598 PO_LOG.proc_begin(d_mod,'p_wip_line_id_tbl',p_wip_line_id_tbl);
599 PO_LOG.proc_begin(d_mod,'p_wip_operation_seq_num_tbl',p_wip_operation_seq_num_tbl);
600 PO_LOG.proc_begin(d_mod,'p_destination_type_code_tbl',p_destination_type_code_tbl);
601 PO_LOG.proc_begin(d_mod,'p_wip_resource_seq_num_tbl',p_wip_resource_seq_num_tbl);
602 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
603 END IF;
604
605 IF (x_results IS NULL) THEN
606 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
607 END IF;
608
609 l_results_count := x_results.result_type.COUNT;
610
611 FOR i IN 1 .. p_dist_id_tbl.COUNT LOOP
612
613 -- Get outside operation flag
614 SELECT outside_operation_flag
615 INTO l_outside_operation_flag
616 FROM po_line_types
617 WHERE line_type_id = p_line_line_type_id_tbl(i);
618
619 IF (p_destination_type_code_tbl(i) = c_DEST_TYPE_SHOP_FLOOR)
620 THEN
621 IF (p_wip_entity_id_tbl(i) IS NULL
622 AND p_wip_line_id_tbl(i) IS NULL)
623 THEN
624 x_results.add_result(
625 p_entity_type => c_ENTITY_TYPE_DISTRIBUTION
626 , p_entity_id => p_dist_id_tbl(i)
627 , p_column_name => c_WIP_ENTITY_ID
628 , p_message_name => PO_MESSAGE_S.PO_OOP_ENTER_ALL_FIELDS
629 );
630 END IF;
631
632 IF (p_wip_operation_seq_num_tbl(i) IS NULL)
633 THEN
634 x_results.add_result(
635 p_entity_type => c_ENTITY_TYPE_DISTRIBUTION
636 , p_entity_id => p_dist_id_tbl(i)
637 , p_column_name => c_WIP_OPERATION_SEQ_NUM
638 , p_message_name => PO_MESSAGE_S.PO_OOP_ENTER_ALL_FIELDS
639 );
640 END IF;
641
642 -- BR says that if WIP line id is not null,
643 -- then assembly cannot be null.
644 --
645 -- Assembly is not stored in PO tables, but if one chooses
646 -- an assembly, then one must also choose a job. Therefore,
647 -- we check here if the job column is null instead.
648 IF (p_wip_line_id_tbl(i) IS NOT NULL
649 AND p_wip_entity_id_tbl(i) IS NULL)
650 THEN
651 x_results.add_result(
652 p_entity_type => c_ENTITY_TYPE_DISTRIBUTION
653 , p_entity_id => p_dist_id_tbl(i)
654 , p_column_name => c_WIP_ENTITY_ID
655 , p_message_name => PO_MESSAGE_S.PO_OOP_ENTER_ALL_FIELDS
656 );
657 END IF;
658
659 -- If OSP line, then also validate that resource sequence is not null
660 IF (l_outside_operation_flag = 'Y'
661 AND p_wip_resource_seq_num_tbl(i) IS NULL)
662 THEN
663 x_results.add_result(
664 p_entity_type => c_ENTITY_TYPE_DISTRIBUTION
665 , p_entity_id => p_dist_id_tbl(i)
666 , p_column_name => c_WIP_RESOURCE_SEQ_NUM
667 , p_message_name => PO_MESSAGE_S.PO_OOP_ENTER_ALL_FIELDS
668 );
669 END IF;
670 END IF; -- IF (p_destination_type_code_tbl(i) = c_DEST_TYPE_SHOP_FLOOR)
671
672 END LOOP;
673
674 IF (l_results_count < x_results.result_type.COUNT) THEN
675 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
676 ELSE
677 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
678 END IF;
679
680 IF PO_LOG.d_proc THEN
681 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
682 PO_LOG.log(PO_LOG.c_PROC_END,d_mod,NULL,'x_results',x_results);
683 END IF;
684
685 EXCEPTION
686 WHEN OTHERS THEN
687 IF PO_LOG.d_exc THEN
688 PO_LOG.exc(d_mod,0,NULL);
689 END IF;
690 RAISE;
691
692 END oop_enter_all_fields;
693
694
695 -------------------------------------------------------------------------
696 -- Check that unencumbered amount is less than or equal to amount to
697 -- encumber.
698 -- Agreements only.
699 -------------------------------------------------------------------------
700 PROCEDURE unencum_amt_le_amt_to_encum(
701 p_dist_id_tbl IN PO_TBL_NUMBER
702 , p_amount_to_encumber_tbl IN PO_TBL_NUMBER
703 , p_unencumbered_amount_tbl IN PO_TBL_NUMBER
704 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
705 , x_result_type OUT NOCOPY VARCHAR2
706 )
707 IS
708 BEGIN
709
710 PO_VALIDATION_HELPER.num1_less_or_equal_num2(
711 p_calling_module => D_unencum_amt_le_amt_to_encum
712 , p_num1_tbl => p_unencumbered_amount_tbl
713 , p_num2_tbl => p_amount_to_encumber_tbl
714 , p_entity_id_tbl => p_dist_id_tbl
715 , p_entity_type => c_entity_type_DISTRIBUTION
716 , p_column_name => c_AMOUNT_TO_ENCUMBER
717 , p_message_name => PO_MESSAGE_S.PO_AMT_TO_ENCUM_LT_UNENCUM
718 , x_results => x_results
719 , x_result_type => x_result_type
720 );
721
722 END unencum_amt_le_amt_to_encum;
723
724 -----------------------------------------------------------------------------
725 -- Checks that the Amount To Encumber is not null and >= 0.
726 -- Agreements only.
727 -----------------------------------------------------------------------------
728 PROCEDURE amount_to_encumber_ge_zero(
729 p_dist_id_tbl IN PO_TBL_NUMBER
730 , p_amount_to_encumber_tbl IN PO_TBL_NUMBER
731 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
732 , x_result_type OUT NOCOPY VARCHAR2
733 )
734 IS
735 BEGIN
736
737 PO_VALIDATION_HELPER.greater_or_equal_zero(
738 p_calling_module => D_amount_to_encumber_ge_zero
739 , p_null_allowed_flag => PO_CORE_S.g_parameter_NO
740 , p_value_tbl => p_amount_to_encumber_tbl
741 , p_entity_id_tbl => p_dist_id_tbl
742 , p_entity_type => c_ENTITY_TYPE_DISTRIBUTION
743 , p_column_name => c_AMOUNT_TO_ENCUMBER
744 , p_message_name => PO_MESSAGE_S.PO_ALL_ENTER_VALUE_GE_ZERO
745 , x_results => x_results
746 , x_result_type => x_result_type
747 );
748
749 END amount_to_encumber_ge_zero;
750
751
752 -------------------------------------------------------------------------------
753 -- Ensures that the Budget Account is not null.
754 -- Agreements only.
755 -------------------------------------------------------------------------------
756 PROCEDURE budget_account_id_not_null(
757 p_dist_id_tbl IN PO_TBL_NUMBER
758 , p_budget_account_id_tbl IN PO_TBL_NUMBER
759 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
760 , x_result_type OUT NOCOPY VARCHAR2
761 )
762 IS
763 BEGIN
764
765 PO_VALIDATION_HELPER.not_null(
766 p_calling_module => D_budget_account_id_not_null
767 , p_value_tbl => PO_TYPE_CONVERTER.to_po_tbl_varchar4000(p_budget_account_id_tbl)
768 , p_entity_id_tbl => p_dist_id_tbl
769 , p_entity_type => c_entity_type_DISTRIBUTION
770 , p_column_name => c_BUDGET_ACCOUNT_ID
771 , p_message_name => PO_MESSAGE_S.PO_ALL_NOT_NULL
772 , x_results => x_results
773 , x_result_type => x_result_type
774 );
775
776 END budget_account_id_not_null;
777
778
779 -------------------------------------------------------------------------------
780 -- Ensures that the GL Encumbered Date is not null.
781 -- Agreements only.
782 -------------------------------------------------------------------------------
783 PROCEDURE gl_encumbered_date_not_null(
784 p_dist_id_tbl IN PO_TBL_NUMBER
785 , p_gl_encumbered_date_tbl IN PO_TBL_DATE
786 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
787 , x_result_type OUT NOCOPY VARCHAR2
788 )
789 IS
790 BEGIN
791
792 PO_VALIDATION_HELPER.not_null(
793 p_calling_module => D_gl_encumbered_date_not_null
794 , p_value_tbl => PO_TYPE_CONVERTER.to_po_tbl_varchar4000(p_gl_encumbered_date_tbl)
795 , p_entity_id_tbl => p_dist_id_tbl
796 , p_entity_type => c_entity_type_DISTRIBUTION
797 , p_column_name => c_GL_ENCUMBERED_DATE
798 , p_message_name => PO_MESSAGE_S.PO_ALL_NOT_NULL
799 , x_results => x_results
800 , x_result_type => x_result_type
801 );
802
803 END gl_encumbered_date_not_null;
804
805 ------------------------------------------------------------------------
806 -- Validates that if PO encumbrance is on, the GL Date is not null and
807 -- is in an open period.
808 -- For both SPOs and BPAs.
809 ------------------------------------------------------------------------
810 PROCEDURE gl_enc_date_not_null_open(
811 p_dist_id_tbl IN PO_TBL_NUMBER
812 , p_org_id_tbl IN PO_TBL_NUMBER
813 , p_gl_encumbered_date_tbl IN PO_TBL_DATE
814 , p_dist_type_tbl IN PO_TBL_VARCHAR30 --Bug 14664343
815 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
816 , x_result_type OUT NOCOPY VARCHAR2
817 )
818 IS
819 l_sob_id FINANCIALS_SYSTEM_PARAMS_ALL.set_of_books_id%TYPE;
820 l_po_enc_flag FINANCIALS_SYSTEM_PARAMS_ALL.purch_encumbrance_flag%TYPE;
821 l_gl_enc_period_name PO_DISTRIBUTIONS_ALL.gl_encumbered_period_name %TYPE;
822 l_results_count NUMBER;
823 l_gl_encumbered_date DATE;
824 BEGIN
825
826 IF (x_results IS NULL) THEN
827 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
828 END IF;
829
830 l_results_count := x_results.result_type.COUNT;
831
832 FOR I IN 1..p_dist_id_tbl.COUNT LOOP
833 SELECT purch_encumbrance_flag, set_of_books_id
834 INTO l_po_enc_flag, l_sob_id
835 FROM financials_system_params_all
836 WHERE org_id = p_org_id_tbl(i);
837
838 IF (l_po_enc_flag = 'Y' AND p_dist_type_tbl(i) <> 'PREPAYMENT') THEN --Bug 14664343
839 -- GL Date Project: Start
840 -- If the Profile - PO:Validate GL Period is set to 'Redefault', try to
841 -- derive the valid GL Encumbered Date. If Valid GL Date is derived, then
842 -- skip raising any error message (the Valid GL Date will be derived once
843 -- again in JAVA layer, in postProcessDistribution), else raise an error.
844 IF Nvl(FND_PROFILE.VALUE('PO_VALIDATE_GL_PERIOD'),'Y') = 'R' THEN
845
846 l_gl_encumbered_date := p_gl_encumbered_date_tbl(i);
847 po_periods_sv.get_period_name(l_sob_id,
848 p_gl_encumbered_date_tbl(i),
849 l_gl_enc_period_name);
850 END IF;
851 -- GL Date Project: End
852 --14523678 If profile PO_VALIDATE_GL_PERIOD raise error if gldate is invalid or null
853 IF (Nvl(FND_PROFILE.VALUE('PO_VALIDATE_GL_PERIOD'),'Y') IN ( 'R','Y')) THEN
854
855 IF (p_gl_encumbered_date_tbl(i) IS NULL) THEN -- Error: GL Date is required
856 x_results.add_result(
857 p_entity_type => c_ENTITY_TYPE_DISTRIBUTION
858 , p_entity_id => p_dist_id_tbl(i)
859 , p_column_name => c_GL_ENCUMBERED_DATE
860 , p_column_val => NULL
861 , p_message_name => PO_MESSAGE_S.PO_ALL_NOT_NULL
862 );
863
864 ELSE -- Verify that GL Date is in an open period.
865 po_periods_sv.get_period_name(l_sob_id,
866 p_gl_encumbered_date_tbl(i),
867 l_gl_enc_period_name);
868
869 IF (l_gl_enc_period_name IS NULL) THEN
870 x_results.add_result(
871 p_entity_type => c_ENTITY_TYPE_DISTRIBUTION
872 , p_entity_id => p_dist_id_tbl(i)
873 , p_column_name => c_GL_ENCUMBERED_DATE
874 , p_column_val => TO_CHAR(p_gl_encumbered_date_tbl(i))
875 , p_message_name => PO_MESSAGE_S.PO_PO_ENTER_OPEN_GL_DATE
876 );
877 END IF;
878
879 END IF; -- p_gl_date_tbl(i) IS NULL
880 END IF; -- l_po_enc_flag = 'Y'
881 END IF;
882 END LOOP;
883
884
885 IF (l_results_count < x_results.result_type.COUNT) THEN
886 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
887 ELSE
888 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
889 END IF;
890
891 END gl_enc_date_not_null_open;
892
893 ------------------------------------------------------------------------
894 -- gms_data_valid
895 --
896 -- Checks with the PO/Grants interface functions whether or not the
897 -- award data on a distribution is valid.
898 --
899 ------------------------------------------------------------------------
900 PROCEDURE gms_data_valid(
901 p_dist_id_tbl IN PO_TBL_NUMBER
902 , p_project_id_tbl IN PO_TBL_NUMBER
903 , p_task_id_tbl IN PO_TBL_NUMBER
904 , p_award_number_tbl IN PO_TBL_VARCHAR2000
905 , p_expenditure_type_tbl IN PO_TBL_VARCHAR30
906 , p_expenditure_item_date_tbl IN PO_TBL_DATE
907 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
908 , x_result_type OUT NOCOPY VARCHAR2
909 )
910 IS
911 l_failure_dist_id_tbl PO_TBL_NUMBER;
912 l_failure_message_tbl PO_TBL_VARCHAR4000;
913 l_results_count NUMBER;
914
915 BEGIN
916
917 IF (x_results IS NULL) THEN
918 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
919 END IF;
920
921 l_results_count := x_results.result_type.COUNT;
922
923 IF (PO_GMS_INTEGRATION_PVT.is_gms_enabled()) THEN
924
925 PO_GMS_INTEGRATION_PVT.validate_award_data(
926 p_dist_id_tbl => p_dist_id_tbl
927 , p_project_id_tbl => p_project_id_tbl
928 , p_task_id_tbl => p_task_id_tbl
929 , p_award_number_tbl => p_award_number_tbl
930 , p_expenditure_type_tbl => p_expenditure_type_tbl
931 , p_expenditure_item_date_tbl => p_expenditure_item_date_tbl
932 , x_failure_dist_id_tbl => l_failure_dist_id_tbl
933 , x_failure_message_tbl => l_failure_message_tbl
934 );
935
936 IF (l_failure_dist_id_tbl IS NOT NULL) THEN
937 FOR i IN 1 .. l_failure_dist_id_tbl.COUNT LOOP
938 x_results.add_result(
939 p_entity_type => c_entity_type_DISTRIBUTION
940 , p_entity_id => l_failure_dist_id_tbl(i)
941 , p_column_name => c_AWARD_ID
942 , p_message_name => PO_MESSAGE_S.PO_WRAPPER_MESSAGE
943 , p_token1_name => PO_MESSAGE_S.c_MESSAGE_token
944 , p_token1_value => l_failure_message_tbl(i)
945 );
946 END LOOP;
947 END IF;
948
949 END IF; -- if is_gms_enabled
950
951 IF (l_results_count < x_results.result_type.COUNT) THEN
952 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
953 ELSE
954 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
955 END IF;
956
957 END gms_data_valid;
958
959 ------------------------------------------------------------------------
960 -- ECO 4059111
961 -- Performs the federal financial validations for distributions
962 -- For Standard POs only.
963 ------------------------------------------------------------------------
964 PROCEDURE check_fv_validations(
965 p_dist_id_tbl IN PO_TBL_NUMBER
966 , p_ccid_tbl IN PO_TBL_NUMBER
967 , p_org_id_tbl IN PO_TBL_NUMBER
968 , p_attribute1_tbl IN PO_TBL_VARCHAR2000
969 , p_attribute2_tbl IN PO_TBL_VARCHAR2000
970 , p_attribute3_tbl IN PO_TBL_VARCHAR2000
971 , p_attribute4_tbl IN PO_TBL_VARCHAR2000
972 , p_attribute5_tbl IN PO_TBL_VARCHAR2000
973 , p_attribute6_tbl IN PO_TBL_VARCHAR2000
974 , p_attribute7_tbl IN PO_TBL_VARCHAR2000
975 , p_attribute8_tbl IN PO_TBL_VARCHAR2000
976 , p_attribute9_tbl IN PO_TBL_VARCHAR2000
977 , p_attribute10_tbl IN PO_TBL_VARCHAR2000
978 , p_attribute11_tbl IN PO_TBL_VARCHAR2000
979 , p_attribute12_tbl IN PO_TBL_VARCHAR2000
980 , p_attribute13_tbl IN PO_TBL_VARCHAR2000
981 , p_attribute14_tbl IN PO_TBL_VARCHAR2000
982 , p_attribute15_tbl IN PO_TBL_VARCHAR2000
983 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
984 , x_result_type OUT NOCOPY VARCHAR2
985 )
986 IS
987 d_mod CONSTANT VARCHAR2(100) := D_check_fv_validations;
988
989 l_ledger_id FINANCIALS_SYSTEM_PARAMS_ALL.set_of_books_id%TYPE;
990 l_return_status VARCHAR2(1);
991 l_error_message VARCHAR2(2000);
992 l_results_count NUMBER;
993 l_result_type VARCHAR2(30);
994 BEGIN
995
996 IF PO_LOG.d_proc THEN
997 PO_LOG.proc_begin(d_mod,'p_dist_id_tbl',p_dist_id_tbl);
998 PO_LOG.proc_begin(d_mod,'p_ccid_tbl',p_ccid_tbl);
999 PO_LOG.proc_begin(d_mod,'p_org_id_tbl',p_org_id_tbl);
1000 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
1001 END IF;
1002
1003 IF (x_results IS NULL) THEN
1004 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
1005 END IF;
1006
1007 l_results_count := x_results.result_type.COUNT;
1008
1009 FOR i IN 1..p_dist_id_tbl.COUNT LOOP
1010
1011 IF FV_INSTALL.enabled(p_org_id_tbl(i)) THEN
1012
1013 SELECT set_of_books_id
1014 INTO l_ledger_id
1015 FROM financials_system_params_all
1016 WHERE org_id = p_org_id_tbl(i);
1017
1018 FV_PO_VALIDATE_GRP.CHECK_AGREEMENT_DATES(
1019 x_code_combination_id => p_ccid_tbl(i),
1020 x_org_id => p_org_id_tbl(i),
1021 x_ledger_id => l_ledger_id,
1022 x_called_from => 'PO',
1023 x_ATTRIBUTE1 => p_attribute1_tbl(i),
1024 x_ATTRIBUTE2 => p_attribute2_tbl(i),
1025 x_ATTRIBUTE3 => p_attribute3_tbl(i),
1026 x_ATTRIBUTE4 => p_attribute4_tbl(i),
1027 x_ATTRIBUTE5 => p_attribute5_tbl(i),
1028 x_ATTRIBUTE6 => p_attribute6_tbl(i),
1029 x_ATTRIBUTE7 => p_attribute7_tbl(i),
1030 x_ATTRIBUTE8 => p_attribute8_tbl(i),
1031 x_ATTRIBUTE9 => p_attribute9_tbl(i),
1032 x_ATTRIBUTE10 => p_attribute10_tbl(i),
1033 x_ATTRIBUTE11 => p_attribute11_tbl(i),
1034 x_ATTRIBUTE12 => p_attribute12_tbl(i),
1035 x_ATTRIBUTE13 => p_attribute13_tbl(i),
1036 x_ATTRIBUTE14 => p_attribute14_tbl(i),
1037 x_ATTRIBUTE15 => p_attribute15_tbl(i),
1038 x_status => l_return_status,
1039 x_message => l_error_message) ;
1040
1041
1042 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1043 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1044 l_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
1045 ELSE
1046 l_result_type := PO_VALIDATIONS.c_result_type_WARNING;
1047 END IF;
1048
1049 x_results.add_result(
1050 p_entity_type => c_entity_type_DISTRIBUTION
1051 , p_entity_id => p_dist_id_tbl(i)
1052 , p_column_name => c_CODE_COMBINATION_ID
1053 , p_column_val => NULL
1054 , p_result_type => l_result_type
1055 , p_message_name => l_error_message
1056 );
1057 END IF;
1058
1059 END IF; -- FV Enabled
1060 END LOOP;
1061
1062 IF (l_results_count < x_results.result_type.COUNT) THEN
1063
1064 x_result_type := PO_VALIDATIONS.c_result_type_WARNING;
1065
1066 FOR j IN 1..x_results.result_type.COUNT LOOP
1067 IF (x_results.result_type(j) = PO_VALIDATIONS.c_result_type_FAILURE)
1068 THEN
1069 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
1070 END IF;
1071 exit;
1072 END LOOP;
1073
1074 ELSE
1075 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1076 END IF;
1077
1078 IF PO_LOG.d_proc THEN
1079 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
1080 PO_LOG.log(PO_LOG.c_PROC_END,d_mod,NULL,'x_results',x_results);
1081 END IF;
1082
1083 EXCEPTION
1084 WHEN OTHERS THEN
1085 IF PO_LOG.d_exc THEN
1086 PO_LOG.exc(d_mod,0,NULL);
1087 END IF;
1088 RAISE;
1089
1090 END check_fv_validations;
1091
1092 /* CLM Partial Funding Changes -- START */
1093 ------------------------------------------------------------------------
1094 -- Validates whether the Funded Value is negative or not, when
1095 -- 1. Encumbrance is enabled and
1096 -- 2. Document is of CLM style.
1097 ------------------------------------------------------------------------
1098 PROCEDURE funded_value_ge_zero(
1099 p_dist_id_tbl IN PO_TBL_NUMBER
1100 , p_org_id_tbl IN PO_TBL_NUMBER
1101 , p_style_id_tbl IN PO_TBL_NUMBER
1102 , p_partial_funded_flag_tbl IN PO_TBL_VARCHAR1
1103 , p_funded_value_tbl IN PO_TBL_NUMBER
1104 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
1105 , x_result_type OUT NOCOPY VARCHAR2
1106 )
1107 IS
1108 d_mod CONSTANT VARCHAR2(100) := D_funded_value_ge_zero;
1109 l_po_enc_flag FINANCIALS_SYSTEM_PARAMS_ALL.purch_encumbrance_flag%TYPE;
1110 l_results_count NUMBER;
1111 l_is_clm_enabled VARCHAR2(1);
1112 BEGIN
1113
1114 IF PO_LOG.d_proc THEN
1115 PO_LOG.proc_begin(d_mod,'p_dist_id_tbl',p_dist_id_tbl);
1116 PO_LOG.proc_begin(d_mod,'p_org_id_tbl',p_org_id_tbl);
1117 PO_LOG.proc_begin(d_mod,'p_style_id_tbl',p_style_id_tbl);
1118 PO_LOG.proc_begin(d_mod,'p_partial_funded_flag_tbl',p_partial_funded_flag_tbl);
1119 PO_LOG.proc_begin(d_mod,'p_funded_value_tbl',p_funded_value_tbl);
1120 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
1121 END IF;
1122
1123 IF (x_results IS NULL) THEN
1124 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
1125 END IF;
1126
1127 l_results_count := x_results.result_type.COUNT;
1128
1129 FOR I IN 1..p_dist_id_tbl.COUNT LOOP
1130 SELECT purch_encumbrance_flag
1131 INTO l_po_enc_flag
1132 FROM financials_system_params_all
1133 WHERE org_id = p_org_id_tbl(i);
1134
1135 l_is_clm_enabled := po_doc_style_helper.get_clm_flag(p_style_id => p_style_id_tbl(i));
1136
1137 IF (l_po_enc_flag = 'Y' AND l_is_clm_enabled = 'Y' AND Nvl(p_funded_value_tbl(i),0) < 0) THEN
1138
1139 x_results.add_result(
1140 p_entity_type => c_ENTITY_TYPE_DISTRIBUTION
1141 , p_entity_id => p_dist_id_tbl(i)
1142 , p_column_name => c_FUNDED_VALUE
1143 , p_message_name => PO_MESSAGE_S.PO_PDOI_INVALID_FUND_VAL
1144 );
1145
1146 END IF;
1147
1148 END LOOP;
1149
1150 IF (l_results_count < x_results.result_type.COUNT) THEN
1151 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
1152 ELSE
1153 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1154 END IF;
1155
1156 IF PO_LOG.d_proc THEN
1157 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
1158 PO_LOG.log(PO_LOG.c_PROC_END,d_mod,NULL,'x_results',x_results);
1159 END IF;
1160
1161 EXCEPTION
1162 WHEN OTHERS THEN
1163 IF PO_LOG.d_exc THEN
1164 PO_LOG.exc(d_mod,0,NULL);
1165 END IF;
1166 RAISE;
1167
1168 END funded_value_ge_zero;
1169
1170
1171 ------------------------------------------------------------------------
1172 -- Validates whether the Funded Value is less than or equal to Total
1173 -- Order Value or not, when
1174 -- 1. Encumbrance is enabled and
1175 -- 2. Document is of CLM style.
1176 ------------------------------------------------------------------------
1177 PROCEDURE funded_val_le_tot_order_val(
1178 p_dist_id_tbl IN PO_TBL_NUMBER
1179 , p_org_id_tbl IN PO_TBL_NUMBER
1180 , p_style_id_tbl IN PO_TBL_NUMBER
1181 , p_partial_funded_flag_tbl IN PO_TBL_VARCHAR1
1182 , p_funded_value_tbl IN PO_TBL_NUMBER
1183 , p_quantity_ordered_tbl IN PO_TBL_NUMBER
1184 , p_amount_ordered_tbl IN PO_TBL_NUMBER
1185 , p_price_override_tbl IN PO_TBL_NUMBER
1186 , p_nonrecoverable_tax_tbl IN PO_TBL_NUMBER
1187 , p_rate_tbl IN PO_TBL_NUMBER
1188 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
1189 , x_result_type OUT NOCOPY VARCHAR2
1190 )
1191 IS
1192 d_mod CONSTANT VARCHAR2(100) := D_funded_val_le_tot_order_val;
1193 l_po_enc_flag FINANCIALS_SYSTEM_PARAMS_ALL.purch_encumbrance_flag%TYPE;
1194 l_results_count NUMBER;
1195 l_is_clm_enabled VARCHAR2(1);
1196 l_total_order_value NUMBER;
1197
1198 x_precision NUMBER := NULL;
1199 x_ext_precision NUMBER := NULL;
1200 x_min_acct_unit NUMBER := NULL;
1201 l_func_currency_code VARCHAR2(10);
1202
1203
1204 BEGIN
1205
1206 IF PO_LOG.d_proc THEN
1207 PO_LOG.proc_begin(d_mod,'p_dist_id_tbl',p_dist_id_tbl);
1208 PO_LOG.proc_begin(d_mod,'p_org_id_tbl',p_org_id_tbl);
1209 PO_LOG.proc_begin(d_mod,'p_style_id_tbl',p_style_id_tbl);
1210 PO_LOG.proc_begin(d_mod,'p_partial_funded_flag_tbl',p_partial_funded_flag_tbl);
1211 PO_LOG.proc_begin(d_mod,'p_funded_value_tbl',p_funded_value_tbl);
1212 PO_LOG.proc_begin(d_mod,'p_quantity_ordered_tbl',p_quantity_ordered_tbl);
1213 PO_LOG.proc_begin(d_mod,'p_amount_ordered_tbl',p_amount_ordered_tbl);
1214 PO_LOG.proc_begin(d_mod,'p_price_override_tbl',p_price_override_tbl);
1215 PO_LOG.proc_begin(d_mod,'p_nonrecoverable_tax_tbl',p_nonrecoverable_tax_tbl);
1216 PO_LOG.proc_begin(d_mod,'p_rate_tbl',p_rate_tbl);
1217 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
1218 END IF;
1219
1220 IF (x_results IS NULL) THEN
1221 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
1222 END IF;
1223
1224 l_results_count := x_results.result_type.COUNT;
1225
1226
1227 FOR I IN 1..p_dist_id_tbl.COUNT LOOP
1228 SELECT purch_encumbrance_flag
1229 INTO l_po_enc_flag
1230 FROM financials_system_params_all
1231 WHERE org_id = p_org_id_tbl(i);
1232
1233 IF l_func_currency_code IS NULL THEN
1234 -- 13943396 Get currency_code to extract the precision value
1235 SELECT glsob.currency_code
1236 INTO l_func_currency_code
1237 FROM gl_sets_of_books glsob,
1238 financials_system_params_all fsp
1239 WHERE glsob.set_of_books_id = fsp.set_of_books_id
1240 AND fsp.org_id = p_org_id_tbl(i);
1241
1242 --13943396 Extract the precision value to round the Funded value
1243 fnd_currency.get_info(l_func_currency_code,
1244 x_precision,
1245 x_ext_precision,
1246 x_min_acct_unit );
1247
1248 END IF;
1249
1250
1251 l_is_clm_enabled := po_doc_style_helper.get_clm_flag(p_style_id => p_style_id_tbl(i));
1252
1253 IF (l_po_enc_flag = 'Y' AND l_is_clm_enabled = 'Y') THEN
1254
1255 IF (p_quantity_ordered_tbl(i) IS NOT NULL) THEN
1256 -- 13943396 added precision for comparision
1257 l_total_order_value := Round( ((p_quantity_ordered_tbl(i) * p_price_override_tbl(i))
1258 + NVL(p_nonrecoverable_tax_tbl(i),0)) * NVL(p_rate_tbl(i), 1),x_precision);
1259
1260 IF (l_total_order_value < p_funded_value_tbl(i)) THEN
1261 x_results.add_result(
1262 p_entity_type => c_ENTITY_TYPE_DISTRIBUTION
1263 , p_entity_id => p_dist_id_tbl(i)
1264 , p_column_name => c_FUNDED_VALUE
1265 , p_message_name => PO_MESSAGE_S.PO_PDOI_INVALID_FUND_VAL_ENC
1266 );
1267 END IF;
1268
1269 ELSE
1270 -- 13943396 added precision for comparision
1271 l_total_order_value := Round((NVL(p_amount_ordered_tbl(i),0) + NVL(p_nonrecoverable_tax_tbl(i),0))
1272 * NVL(p_rate_tbl(i), 1),x_precision);
1273
1274 IF (l_total_order_value < p_funded_value_tbl(i)) THEN
1275 x_results.add_result(
1276 p_entity_type => c_ENTITY_TYPE_DISTRIBUTION
1277 , p_entity_id => p_dist_id_tbl(i)
1278 , p_column_name => c_FUNDED_VALUE
1279 , p_message_name => PO_MESSAGE_S.PO_PDOI_INVALID_FUND_VAL
1280 );
1281 END IF;
1282
1283 END IF;
1284
1285 END IF;
1286 END LOOP;
1287
1288 IF (l_results_count < x_results.result_type.COUNT) THEN
1289 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
1290 ELSE
1291 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1292 END IF;
1293
1294 IF PO_LOG.d_proc THEN
1295 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
1296 PO_LOG.log(PO_LOG.c_PROC_END,d_mod,NULL,'x_results',x_results);
1297 END IF;
1298
1299 EXCEPTION
1300 WHEN OTHERS THEN
1301 IF PO_LOG.d_exc THEN
1302 PO_LOG.exc(d_mod,0,NULL);
1303 END IF;
1304 RAISE;
1305
1306 END funded_val_le_tot_order_val;
1307
1308 ------------------------------------------------------------------------
1309 -- Validates whether the new Quantity Funded is greater than the
1310 -- Utilized Quantity or not, when
1311 -- 1. Encumbrance is enabled and
1312 -- 2. Document is of CLM style.
1313 ------------------------------------------------------------------------
1314 PROCEDURE qty_funded_ge_exc_qty(
1315 p_dist_id_tbl IN PO_TBL_NUMBER
1316 , p_org_id_tbl IN PO_TBL_NUMBER
1317 , p_style_id_tbl IN PO_TBL_NUMBER
1318 , p_quantity_funded_tbl IN PO_TBL_NUMBER
1319 , p_quantity_billed_tbl IN PO_TBL_NUMBER
1320 , p_quantity_delivered_tbl IN PO_TBL_NUMBER
1321 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
1322 , x_result_type OUT NOCOPY VARCHAR2
1323 )
1324 IS
1325 d_mod CONSTANT VARCHAR2(100) := D_qty_funded_ge_exc_qty;
1326 l_po_enc_flag FINANCIALS_SYSTEM_PARAMS_ALL.purch_encumbrance_flag%TYPE;
1327 l_results_count NUMBER;
1328 l_is_clm_enabled VARCHAR2(1);
1329 BEGIN
1330
1331 IF PO_LOG.d_proc THEN
1332 PO_LOG.proc_begin(d_mod,'p_dist_id_tbl',p_dist_id_tbl);
1333 PO_LOG.proc_begin(d_mod,'p_org_id_tbl',p_org_id_tbl);
1334 PO_LOG.proc_begin(d_mod,'p_style_id_tbl',p_style_id_tbl);
1335 PO_LOG.proc_begin(d_mod,'p_quantity_funded_tbl',p_quantity_funded_tbl);
1336 PO_LOG.proc_begin(d_mod,'p_quantity_billed_tbl',p_quantity_billed_tbl);
1337 PO_LOG.proc_begin(d_mod,'p_quantity_delivered_tbl',p_quantity_delivered_tbl);
1338 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
1339 END IF;
1340
1341 IF (x_results IS NULL) THEN
1342 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
1343 END IF;
1344
1345 l_results_count := x_results.result_type.COUNT;
1346
1347 FOR I IN 1..p_dist_id_tbl.COUNT LOOP
1348 SELECT purch_encumbrance_flag
1349 INTO l_po_enc_flag
1350 FROM financials_system_params_all
1351 WHERE org_id = p_org_id_tbl(i);
1352
1353 l_is_clm_enabled := po_doc_style_helper.get_clm_flag(p_style_id => p_style_id_tbl(i));
1354
1355 IF (l_po_enc_flag = 'Y' AND l_is_clm_enabled = 'Y' AND
1356 (NVL(p_quantity_billed_tbl(i),0) > 0 OR NVL(p_quantity_delivered_tbl(i),0) >0)) THEN
1357
1358 IF (NVL(p_quantity_billed_tbl(i),0) > NVL(p_quantity_funded_tbl(i),0)) THEN
1359
1360 x_results.add_result(
1361 p_entity_type => c_ENTITY_TYPE_DISTRIBUTION
1362 , p_entity_id => p_dist_id_tbl(i)
1363 , p_column_name => c_QUANTITY_FUNDED
1364 , p_message_name => PO_MESSAGE_S.PO_PF_QTY_FUND_LT_QTY_BIL_NA
1365 );
1366
1367 ELSIF (NVL(p_quantity_delivered_tbl(i),0) > NVL(p_quantity_funded_tbl(i),0)) THEN
1368
1369 x_results.add_result(
1370 p_entity_type => c_ENTITY_TYPE_DISTRIBUTION
1371 , p_entity_id => p_dist_id_tbl(i)
1372 , p_column_name => c_QUANTITY_FUNDED
1373 , p_message_name => PO_MESSAGE_S.PO_PF_QTY_FUND_LT_QTY_DEL_NA
1374 );
1375
1376 END IF;
1377
1378 END IF;
1379
1380 END LOOP;
1381
1382 IF (l_results_count < x_results.result_type.COUNT) THEN
1383 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
1384 ELSE
1385 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1386 END IF;
1387
1388 IF PO_LOG.d_proc THEN
1389 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
1390 PO_LOG.log(PO_LOG.c_PROC_END,d_mod,NULL,'x_results',x_results);
1391 END IF;
1392
1393 EXCEPTION
1394 WHEN OTHERS THEN
1395 IF PO_LOG.d_exc THEN
1396 PO_LOG.exc(d_mod,0,NULL);
1397 END IF;
1398 RAISE;
1399
1400 END qty_funded_ge_exc_qty;
1401
1402 ------------------------------------------------------------------------
1403 -- Validates whether the new Amount Funded is greater than the
1404 -- Utilized Amount or not, when
1405 -- 1. Encumbrance is enabled and
1406 -- 2. Document is of CLM style.
1407 ------------------------------------------------------------------------
1408 PROCEDURE amount_funded_ge_exc_amount(
1409 p_dist_id_tbl IN PO_TBL_NUMBER
1410 , p_org_id_tbl IN PO_TBL_NUMBER
1411 , p_style_id_tbl IN PO_TBL_NUMBER
1412 , p_amount_funded_tbl IN PO_TBL_NUMBER
1413 , p_amount_billed_tbl IN PO_TBL_NUMBER
1414 , p_amount_delivered_tbl IN PO_TBL_NUMBER
1415 , p_line_matching_basis_tbl IN PO_TBL_VARCHAR30 --bug 13773450
1416 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
1417 , x_result_type OUT NOCOPY VARCHAR2
1418 )
1419 IS
1420 d_mod CONSTANT VARCHAR2(100) := D_amount_funded_ge_exc_amount;
1421 l_po_enc_flag FINANCIALS_SYSTEM_PARAMS_ALL.purch_encumbrance_flag%TYPE;
1422 l_results_count NUMBER;
1423 l_is_clm_enabled VARCHAR2(1);
1424 BEGIN
1425
1426 IF PO_LOG.d_proc THEN
1427 PO_LOG.proc_begin(d_mod,'p_dist_id_tbl',p_dist_id_tbl);
1428 PO_LOG.proc_begin(d_mod,'p_org_id_tbl',p_org_id_tbl);
1429 PO_LOG.proc_begin(d_mod,'p_style_id_tbl',p_style_id_tbl);
1430 PO_LOG.proc_begin(d_mod,'p_amount_funded_tbl',p_amount_funded_tbl);
1431 PO_LOG.proc_begin(d_mod,'p_amount_billed_tbl',p_amount_billed_tbl);
1432 PO_LOG.proc_begin(d_mod,'p_amount_delivered_tbl',p_amount_delivered_tbl);
1433 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
1434 END IF;
1435
1436 IF (x_results IS NULL) THEN
1437 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
1438 END IF;
1439
1440 l_results_count := x_results.result_type.COUNT;
1441
1442 FOR I IN 1..p_dist_id_tbl.COUNT LOOP
1443 SELECT purch_encumbrance_flag
1444 INTO l_po_enc_flag
1445 FROM financials_system_params_all
1446 WHERE org_id = p_org_id_tbl(i);
1447
1448 l_is_clm_enabled := po_doc_style_helper.get_clm_flag(p_style_id => p_style_id_tbl(i));
1449
1450 IF (l_po_enc_flag = 'Y' AND l_is_clm_enabled = 'Y'
1451 --bug 13773450: for lines having matching basis as quantity no need to check these conditions.
1452 AND p_line_matching_basis_tbl(i) <> 'QUANTITY'
1453 AND (NVL(p_amount_billed_tbl(i),0) > 0 OR NVL(p_amount_delivered_tbl(i),0) >0)) THEN
1454
1455 IF (NVL(p_amount_billed_tbl(i),0) > NVL(p_amount_funded_tbl(i),0)) THEN
1456
1457 x_results.add_result(
1458 p_entity_type => c_ENTITY_TYPE_DISTRIBUTION
1459 , p_entity_id => p_dist_id_tbl(i)
1460 , p_column_name => c_AMOUNT_FUNDED
1461 , p_message_name => PO_MESSAGE_S.PO_PF_AMT_FUND_LT_AMT_BIL_NA
1462 );
1463
1464 ELSIF (NVL(p_amount_delivered_tbl(i),0) > NVL(p_amount_funded_tbl(i),0)) THEN
1465
1466 x_results.add_result(
1467 p_entity_type => c_ENTITY_TYPE_DISTRIBUTION
1468 , p_entity_id => p_dist_id_tbl(i)
1469 , p_column_name => c_AMOUNT_FUNDED
1470 , p_message_name => PO_MESSAGE_S.PO_PF_AMT_FUND_LT_AMT_DEL_NA
1471 );
1472
1473 END IF;
1474
1475 END IF;
1476
1477 END LOOP;
1478
1479 IF (l_results_count < x_results.result_type.COUNT) THEN
1480 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
1481 ELSE
1482 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1483 END IF;
1484
1485 IF PO_LOG.d_proc THEN
1486 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
1487 PO_LOG.log(PO_LOG.c_PROC_END,d_mod,NULL,'x_results',x_results);
1488 END IF;
1489
1490 EXCEPTION
1491 WHEN OTHERS THEN
1492 IF PO_LOG.d_exc THEN
1493 PO_LOG.exc(d_mod,0,NULL);
1494 END IF;
1495 RAISE;
1496
1497 END amount_funded_ge_exc_amount;
1498
1499 ------------------------------------------------------------------------
1500 -- Validates whether the Funded Value of the PO distribution is greater
1501 -- than its corresponding Requisition distribution or not, when
1502 -- 1. Encumbrance is enabled and
1503 -- 2. Document is of CLM style.
1504 ------------------------------------------------------------------------
1505 PROCEDURE funded_value_gt_backing_req(
1506 p_dist_id_tbl IN PO_TBL_NUMBER
1507 , p_org_id_tbl IN PO_TBL_NUMBER
1508 , p_style_id_tbl IN PO_TBL_NUMBER
1509 , p_funded_value_tbl IN PO_TBL_NUMBER
1510 , p_req_distribution_id_tbl IN PO_TBL_NUMBER
1511 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
1512 , x_result_type OUT NOCOPY VARCHAR2
1513 )
1514 IS
1515 d_mod CONSTANT VARCHAR2(100) := D_funded_value_gt_backing_req;
1516 l_po_enc_flag FINANCIALS_SYSTEM_PARAMS_ALL.purch_encumbrance_flag%TYPE;
1517 l_results_count NUMBER;
1518 l_is_clm_enabled VARCHAR2(1);
1519 l_backing_req_fund_value NUMBER;
1520 BEGIN
1521
1522 IF PO_LOG.d_proc THEN
1523 PO_LOG.proc_begin(d_mod,'p_dist_id_tbl',p_dist_id_tbl);
1524 PO_LOG.proc_begin(d_mod,'p_org_id_tbl',p_org_id_tbl);
1525 PO_LOG.proc_begin(d_mod,'p_style_id_tbl',p_style_id_tbl);
1526 PO_LOG.proc_begin(d_mod,'p_funded_value_tbl',p_funded_value_tbl);
1527 PO_LOG.proc_begin(d_mod,'p_req_distribution_id_tbl',p_req_distribution_id_tbl);
1528 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
1529 END IF;
1530
1531 IF (x_results IS NULL) THEN
1532 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
1533 END IF;
1534
1535 l_results_count := x_results.result_type.COUNT;
1536
1537 FOR I IN 1..p_dist_id_tbl.COUNT LOOP
1538 SELECT purch_encumbrance_flag
1539 INTO l_po_enc_flag
1540 FROM financials_system_params_all
1541 WHERE org_id = p_org_id_tbl(i);
1542
1543 l_is_clm_enabled := po_doc_style_helper.get_clm_flag(p_style_id => p_style_id_tbl(i));
1544
1545 IF (l_po_enc_flag = 'Y' AND l_is_clm_enabled = 'Y' AND
1546 p_req_distribution_id_tbl(i) IS NOT NULL) THEN
1547
1548 SELECT NVL(funded_value,0)
1549 INTO l_backing_req_fund_value
1550 FROM po_req_distributions_all
1551 WHERE distribution_id = p_req_distribution_id_tbl(i);
1552
1553 IF (l_backing_req_fund_value < p_funded_value_tbl(i)) THEN
1554
1555 x_results.add_result(
1556 p_entity_type => c_ENTITY_TYPE_DISTRIBUTION
1557 , p_entity_id => p_dist_id_tbl(i)
1558 , p_column_name => c_FUNDED_VALUE
1559 , p_message_name => PO_MESSAGE_S.PO_PF_FUND_VAL_GT_BACK_REQ
1560 );
1561
1562 END IF;
1563
1564 END IF;
1565
1566 END LOOP;
1567
1568 IF (l_results_count < x_results.result_type.COUNT) THEN
1569 x_result_type := PO_VALIDATIONS.c_result_type_WARNING;
1570 ELSE
1571 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1572 END IF;
1573
1574 IF PO_LOG.d_proc THEN
1575 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
1576 PO_LOG.log(PO_LOG.c_PROC_END,d_mod,NULL,'x_results',x_results);
1577 END IF;
1578
1579 EXCEPTION
1580 WHEN OTHERS THEN
1581 IF PO_LOG.d_exc THEN
1582 PO_LOG.exc(d_mod,0,NULL);
1583 END IF;
1584 RAISE;
1585
1586 END funded_value_gt_backing_req;
1587 /* CLM Partial Funding Changes -- END */
1588
1589 ------------------------------------------------------------------------
1590 -- Bug 5442682
1591 -- Performs required field validations on project related fields
1592 -- Following business rules apply (given project is filled in) :
1593 -- 1. Task is required irrespective of destination type
1594 -- 2. For expense lines, expenditure org and expenditure item
1595 -- date are required
1596 -- 3. If gms is installed and award is required for the project,
1597 -- expenditure type is required if award is filled in
1598 -- 4. If gms is not installed or if award is not required,
1599 -- expenditure item type is required.
1600 ------------------------------------------------------------------------
1601 PROCEDURE check_proj_related_validations(
1602 p_dist_id_tbl IN PO_TBL_NUMBER
1603 , p_dest_type_code_tbl IN PO_TBL_VARCHAR30
1604 , p_project_id_tbl IN PO_TBL_NUMBER
1605 , p_task_id_tbl IN PO_TBL_NUMBER
1606 , p_award_id_tbl IN PO_TBL_NUMBER
1607 , p_expenditure_type_tbl IN PO_TBL_VARCHAR30
1608 , p_expenditure_org_id_tbl IN PO_TBL_NUMBER
1609 , p_expenditure_item_date_tbl IN PO_TBL_DATE
1610 , p_ship_to_org_id_tbl IN PO_TBL_NUMBER
1611 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
1612 , x_result_type OUT NOCOPY VARCHAR2
1613 )
1614 IS
1615 d_mod CONSTANT VARCHAR2(100) := D_check_proj_rel_validations;
1616 l_error_message VARCHAR2(2000);
1617 l_award_required_flag VARCHAR2(2);
1618 l_expenditure_type_reqd BOOLEAN := FALSE;
1619 l_results_count NUMBER;
1620 x_project_reference_enabled NUMBER;
1621 x_project_control_level NUMBER;
1622 BEGIN
1623 IF PO_LOG.d_proc THEN
1624 PO_LOG.proc_begin(d_mod,'p_dist_id_tbl',p_dist_id_tbl);
1625 PO_LOG.proc_begin(d_mod,'p_dest_type_code_tbl',p_dest_type_code_tbl);
1626 PO_LOG.proc_begin(d_mod,'p_project_id_tbl',p_project_id_tbl);
1627 PO_LOG.proc_begin(d_mod,'p_task_id_tbl',p_task_id_tbl);
1628 PO_LOG.proc_begin(d_mod,'p_award_id_tbl',p_award_id_tbl);
1629 PO_LOG.proc_begin(d_mod,'p_expenditure_type_tbl',p_expenditure_type_tbl);
1630 PO_LOG.proc_begin(d_mod,'p_expenditure_org_id_tbl',p_expenditure_org_id_tbl);
1631 PO_LOG.proc_begin(d_mod,'p_expenditure_item_date_tbl',p_expenditure_item_date_tbl);
1632 PO_LOG.proc_begin(d_mod,'p_ship_to_org_id_tbl',p_ship_to_org_id_tbl);
1633 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
1634 END IF;
1635
1636 IF (x_results IS NULL) THEN
1637 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
1638 END IF;
1639
1640 l_results_count := x_results.result_type.COUNT;
1641
1642
1643 FOR i IN 1..p_dist_id_tbl.COUNT LOOP
1644
1645 po_core_s4.get_mtl_parameters (p_ship_to_org_id_tbl(i),
1646 NULL,
1647 x_project_reference_enabled,
1648 x_project_control_level);
1649
1650 PO_LOG.proc_begin(d_mod,'x_project_reference_enabled',x_project_reference_enabled);
1651 PO_LOG.proc_begin(d_mod,'x_project_control_level',x_project_control_level);
1652 -- Bug 7558385
1653 -- Need to check for PJM Parameters before making Task as mandatory.
1654 IF (p_project_id_tbl(i) IS NOT NULL) AND
1655 (NOT((x_project_reference_enabled = 1 ) and
1656 (x_project_control_level = 1 ) and
1657 (p_dest_type_code_tbl(i) in (c_DEST_TYPE_INVENTORY, c_DEST_TYPE_SHOP_FLOOR))
1658 ))
1659 THEN
1660 -- Task is required irrespective of destination type
1661 IF (p_task_id_tbl(i) IS NULL) THEN
1662 x_results.add_result(
1663 p_entity_type => c_entity_type_DISTRIBUTION
1664 , p_entity_id => p_dist_id_tbl(i)
1665 , p_column_name => 'TASK_ID'
1666 , p_column_val => NULL
1667 , p_message_name => PO_MESSAGE_S.PO_ALL_NOT_NULL
1668 );
1669 END IF;
1670
1671 IF (p_dest_type_code_tbl(i) = c_DEST_TYPE_EXPENSE) THEN
1672
1673 IF (PO_GMS_INTEGRATION_PVT.is_gms_enabled()) THEN
1674
1675 -- Award field is rendered if destination type is expense and grants is enabled
1676 -- Check if award is required for the project
1677 PO_GMS_INTEGRATION_PVT.is_award_required_for_project
1678 (p_project_id => p_project_id_tbl(i)
1679 , x_award_required_flag => l_award_required_flag);
1680
1681 -- If award is required, expenditure item type is required if award is filled in
1682 IF (l_award_required_flag = 'Y') THEN
1683 IF ( p_award_id_tbl(i) IS NOT NULL AND p_expenditure_type_tbl(i) IS NULL) THEN
1684 l_expenditure_type_reqd := TRUE;
1685 END IF; -- award id null check
1686 ELSIF (p_expenditure_type_tbl(i) IS NULL) THEN
1687 -- If award is not required for the project, the expenditure type is required
1688 l_expenditure_type_reqd := TRUE;
1689 END IF; --award required check
1690 ELSIF (p_expenditure_type_tbl(i) IS NULL) THEN
1691 -- If grants is not enabled, expenditure item type is required
1692 l_expenditure_type_reqd := TRUE;
1693 END IF; -- grants enabled check
1694
1695 IF (l_expenditure_type_reqd) THEN
1696 x_results.add_result(
1697 p_entity_type => c_entity_type_DISTRIBUTION
1698 , p_entity_id => p_dist_id_tbl(i)
1699 , p_column_name => 'EXPENDITURE_TYPE'
1700 , p_column_val => NULL
1701 , p_message_name => PO_MESSAGE_S.PO_ALL_NOT_NULL
1702 );
1703 END IF; --expenditure type validation check
1704
1705 IF (p_expenditure_org_id_tbl(i) IS NULL) THEN
1706 x_results.add_result(
1707 p_entity_type => c_entity_type_DISTRIBUTION
1708 , p_entity_id => p_dist_id_tbl(i)
1709 , p_column_name => 'EXPENDITURE_ORGANIZATION_ID'
1710 , p_column_val => NULL
1711 , p_message_name => PO_MESSAGE_S.PO_ALL_NOT_NULL
1712 );
1713 END IF;
1714
1715 IF (p_expenditure_item_date_tbl(i) IS NULL) THEN
1716 x_results.add_result(
1717 p_entity_type => c_entity_type_DISTRIBUTION
1718 , p_entity_id => p_dist_id_tbl(i)
1719 , p_column_name => 'EXPENDITURE_ITEM_DATE'
1720 , p_column_val => NULL
1721 , p_message_name => PO_MESSAGE_S.PO_ALL_NOT_NULL
1722 );
1723 END IF;
1724 END IF; -- destination type check
1725 END IF;
1726 END LOOP;
1727
1728 IF (l_results_count < x_results.result_type.COUNT) THEN
1729 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
1730 ELSE
1731 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1732 END IF;
1733
1734 IF po_log.d_proc THEN
1735 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
1736 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
1737 END IF;
1738
1739 EXCEPTION
1740 WHEN OTHERS THEN
1741 IF po_log.d_exc THEN
1742 po_log.exc(d_mod, 0, NULL);
1743 END IF;
1744
1745 RAISE;
1746
1747 END check_proj_related_validations;
1748
1749 -- CLM-LnSc-st
1750 -----------------------------------------------------------------------------
1751 -- Validates if Total Funded Value of Distributions is greater than the Funds
1752 -- Remaining on the PR they refer to.
1753 -----------------------------------------------------------------------------
1754 PROCEDURE funded_val_gt_req_funds_rmn(
1755 p_dist_id_tbl IN PO_TBL_NUMBER,
1756 p_dist_type_tbl IN PO_TBL_VARCHAR30,
1757 p_org_id_tbl IN PO_TBL_NUMBER,
1758 p_style_id_tbl IN PO_TBL_NUMBER,
1759 p_draft_id_tbl IN PO_TBL_NUMBER,
1760 p_funded_value_tbl IN PO_TBL_NUMBER,
1761 p_enc_amount_tbl IN PO_TBL_NUMBER,
1762 p_req_dist_id_tbl IN PO_TBL_NUMBER,
1763 x_result_set_id IN OUT NOCOPY NUMBER,
1764 x_result_type OUT NOCOPY VARCHAR2)
1765 IS
1766 d_mod CONSTANT VARCHAR2(100) := D_funded_val_gt_req_funds_rmn;
1767
1768 l_data_key NUMBER;
1769
1770 BEGIN
1771
1772 IF PO_LOG.d_proc THEN
1773 PO_LOG.proc_begin(d_mod);
1774 END IF;
1775
1776 IF (x_result_set_id IS NULL) THEN
1777 x_result_set_id := PO_VALIDATIONS.next_result_set_id();
1778 END IF;
1779
1780 ----------------------------------------------------------------------
1781 -- In order to check that the total funded value of Distributions
1782 -- is less than the Funds remaining on the backing Req, we first we need to
1783 -- construct a view of the intended Document.In order to do this, we need to get
1784 -- all of the in-memory data into a temp database table, so that
1785 -- we can run the business logic of the validation on this.
1786 --
1787 -- The session temp table will be used with the following mapping:
1788
1789 -- Distributions
1790 --
1791 -- index_num1 distribution_id
1792 -- index_num2 req_distribution_id
1793 -- num1 funded_value (latest value passed into the Validation routine)
1794 -- num2 funded_value_saved (already existing(saved in db),if any, funded_value for this Dist)
1795 --
1796 -- A view of the in-memory and stored data will be constructed
1797 -- as follows:
1798 -- 1. Gather the in-memory data(distribution_id,req_distribution_id,funded_value)
1799 -- and the stored data(PO_DISTRIBUTIONS_MERGE_V.funded_value) into the temp table.
1800 -- Valid Distributions are CLM-enabled, STANDARD type,
1801 -- having a Req reference, and when PO and Req Encumbrance is On
1802 -- 2. Compare the temp table data with Funds remaining on Req as follows:
1803 -- For each distinct Req being referred to by the Distributions, record error If
1804 -- For Base Documeny:
1805 -- Funds Remaining for Req + Total Funds already saved in db for these Dists
1806 -- < Total Funds now required by these Dists
1807 -- For Modification:
1808 -- Funds Remaining for Req + Total +ve Change in Funds already saved in db for these Dists
1809 -- < Total +ve Change in Funds now required by these Dists
1810 ----------------------------------------------------------------------
1811
1812 -- Put the stored data for these ids into the temp table.
1813 l_data_key := PO_CORE_S.get_session_gt_nextval();
1814
1815 FORALL i IN 1 .. p_dist_id_tbl.COUNT
1816 INSERT INTO PO_SESSION_GT(
1817 KEY,
1818 index_num1,
1819 index_num2,
1820 num1,
1821 num2)
1822 SELECT
1823 l_data_key,
1824 p_dist_id_tbl(i),
1825 p_req_dist_id_tbl(i),
1826 Decode(Nvl(p_draft_id_tbl(i), -1), -1, p_funded_value_tbl(i),
1827 Decode(Sign(Nvl(p_funded_value_tbl(i),0) - Nvl(p_enc_amount_tbl(i),0)), -1, 0,
1828 Nvl(p_funded_value_tbl(i),0) - Nvl(p_enc_amount_tbl(i), 0))),
1829 (SELECT Decode(Nvl(p_draft_id_tbl(i), -1), -1, funded_value,
1830 Decode(Sign(change_in_funded_value), -1, 0, change_in_funded_value))
1831 FROM po_distributions_merge_v
1832 WHERE po_distribution_id = p_dist_id_tbl(i)
1833 AND draft_id = Nvl(p_draft_id_tbl(i), -1))
1834 FROM dual
1835 WHERE p_req_dist_id_tbl(i) IS NOT NULL
1836 AND p_dist_type_tbl(i) = c_STANDARD
1837 AND PO_DOC_STYLE_HELPER.get_clm_flag(p_style_id => p_style_id_tbl(i)) = 'Y'
1838 AND EXISTS(
1839 SELECT NULL
1840 FROM FINANCIALS_SYSTEM_PARAMS_ALL
1841 WHERE org_id = p_org_id_tbl(i)
1842 AND purch_encumbrance_flag = 'Y'
1843 AND req_encumbrance_flag = 'Y');
1844
1845 INSERT INTO PO_VALIDATION_RESULTS_GT(
1846 result_set_id,
1847 entity_type,
1848 entity_id,
1849 column_name,
1850 message_name)
1851 SELECT
1852 x_result_set_id,
1853 c_ENTITY_TYPE_DISTRIBUTION,
1854 GT.index_num1, -- po_distribution_id
1855 c_FUNDED_VALUE,
1856 PO_MESSAGE_S.PO_FUNDED_VAL_GT_REQ_FUNDS_RMN
1857 FROM
1858 PO_SESSION_GT GT
1859 WHERE
1860 GT.key = l_data_key
1861 AND EXISTS(
1862 SELECT NULL
1863 FROM PO_SESSION_GT GT2,
1864 PO_CLMREQ_DIST_DETAILS_V PRD
1865 WHERE PRD.distribution_id = GT2.index_num2 -- JOIN
1866 AND GT2.key = l_data_key
1867 AND GT2.index_num2 = GT.index_num2 -- req_distribution_id
1868 GROUP BY
1869 GT2.index_num2,
1870 PRD.funds_remaining
1871 HAVING PRD.funds_remaining + Sum(Nvl(GT2.num2,0)) < Sum(Nvl(GT2.num1,0)));
1872
1873 IF (SQL%ROWCOUNT > 0) THEN
1874 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
1875 ELSE
1876 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1877 END IF;
1878
1879 IF PO_LOG.d_proc THEN
1880 PO_VALIDATIONS.log_validation_results_gt(d_mod,9,x_result_set_id);
1881 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
1882 PO_LOG.proc_end(d_mod,'x_result_set_id',x_result_set_id);
1883 END IF;
1884
1885 EXCEPTION
1886 WHEN OTHERS THEN
1887 IF PO_LOG.d_exc THEN
1888 PO_LOG.exc(d_mod,0,NULL);
1889 END IF;
1890 RAISE;
1891
1892 END funded_val_gt_req_funds_rmn;
1893 -- CLM-LnSc-end
1894
1895 --<PAR Project>
1896 -------------------------------------------------------------------------------
1897 --Start of Comments
1898 --Pre-reqs: None.
1899 --Procedure Name: check_dist_values_inc_for_par.
1900 --Locks: None.
1901 --Function:
1902 -- Validates if Total Funded Value of Distributions is greater than the Funds
1903 -- Remaining on the PR they refer to.
1904 --Parameters:
1905 --IN:
1906 --p_dist_id_tbl
1907 --p_draft_id_tbl
1908 --p_draft_type_tbl
1909 --p_quantity_ordered_tbl
1910 --p_amt_ordered_tbl
1911 --p_old_qty_ordered_tbl
1912 --OUT:
1913 --x_result_type
1914 -- Provides a summary of the validation results.
1915 -- VARCHAR2(30)
1916 --x_results
1917 -- The results of the validations.
1918 --End of Comments
1919 -------------------------------------------------------------------------------
1920 PROCEDURE check_dist_values_inc_for_par(
1921 p_dist_id_tbl IN PO_TBL_NUMBER,
1922 p_draft_id_tbl IN PO_TBL_NUMBER,
1923 p_draft_type_tbl IN PO_TBL_VARCHAR30,
1924 p_quantity_ordered_tbl IN PO_TBL_NUMBER,
1925 p_amt_ordered_tbl IN PO_TBL_NUMBER,
1926 p_old_qty_ordered_tbl IN PO_TBL_NUMBER,
1927 p_change_status_tbl IN PO_TBL_VARCHAR30,
1928 x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE ,
1929 x_result_type OUT NOCOPY VARCHAR2)
1930
1931 IS
1932 d_mod CONSTANT VARCHAR2(100) := D_check_dist_val_inc_for_par;
1933 l_draft_type VARCHAR2(10);
1934 l_results_count NUMBER;
1935
1936
1937 BEGIN
1938
1939 IF PO_LOG.d_proc THEN
1940 PO_LOG.proc_begin(d_mod);
1941 END IF;
1942
1943 IF (x_results IS NULL) THEN
1944 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
1945 END IF;
1946
1947 l_results_count := x_results.result_type.COUNT;
1948
1949 FOR i IN 1..p_dist_id_tbl.Count() LOOP
1950
1951 IF PO_LOG.d_proc THEN
1952 PO_LOG.stmt(d_mod,10,'p_dist_id_tbl(i) is ',p_dist_id_tbl(i));
1953 PO_LOG.stmt(d_mod,20,'p_draft_type_tbl(i) is ',p_draft_type_tbl(i));
1954 END IF;
1955
1956 --Validation has to skipped for new lines in PAR
1957 IF ((p_draft_type_tbl(i) = 'PAR' AND p_change_status_tbl(i)='NEW') OR
1958 p_draft_type_tbl(i) <>'PAR' OR p_draft_type_tbl(i) IS NULL ) THEN
1959 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1960 RETURN;
1961 END IF;
1962
1963
1964 IF PO_LOG.d_proc THEN
1965 PO_LOG.stmt(d_mod,30,'p_quantity_ordered_tbl(i) ',p_quantity_ordered_tbl(i));
1966 PO_LOG.stmt(d_mod,40,'p_old_qty_ordered_tbl(i) ',p_old_qty_ordered_tbl(i));
1967 END IF;
1968
1969 --Compare the old and new qauntities and thorw an error if there is an increase
1970 IF p_quantity_ordered_tbl(i) > p_old_qty_ordered_tbl(i) THEN
1971
1972 IF PO_LOG.d_proc THEN
1973 PO_LOG.stmt(d_mod,50,'Qty/Amount is increased at distributions on a PAR ');
1974 END IF;
1975
1976 x_results.add_result(
1977 p_entity_type => c_ENTITY_TYPE_DISTRIBUTION
1978 , p_entity_id => p_dist_id_tbl(i)
1979 , p_column_name => c_AMOUNT_ORDERED
1980 , p_message_name => 'PO_DIST_AMOUNT_INC_ON_PAR'
1981 );
1982
1983 END IF;
1984 END LOOP;
1985
1986 IF (l_results_count < x_results.result_type.COUNT) THEN
1987 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
1988 ELSE
1989 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1990 END IF;
1991
1992 END check_dist_values_inc_for_par;
1993
1994
1995
1996 --CLM PDOI Integration
1997 /*------------------------------------------------------------------------------
1998 This procedure validates:
1999 1. Proper clin slin funding
2000 2. If funding is at slin level, then there should not be any distribution
2001 against its clin
2002 -------------------------------------------------------------------------------*/
2003
2004 PROCEDURE check_clin_slin_funding(
2005 p_intf_dist_id_tbl IN PO_TBL_NUMBER,
2006 --p_dist_id_tbl IN PO_TBL_NUMBER,
2007 x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE,
2008 x_result_type OUT NOCOPY VARCHAR2)
2009 IS
2010
2011 d_mod CONSTANT VARCHAR2(100) := D_check_clin_slin_funding;
2012 l_lines_count NUMBER := 0;
2013 l_results_count NUMBER :=0;
2014
2015 BEGIN
2016
2017 -- skip the validation for non-CLM documents
2018 IF NOT PO_CLM_CLO_UTIL.Isclminstalled THEN
2019 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
2020 RETURN;
2021 END IF;
2022
2023 IF PO_LOG.d_proc THEN
2024 PO_LOG.proc_begin(d_mod,'p_id_tbl',p_intf_dist_id_tbl);
2025 END IF;
2026
2027 --loop through each distribution that corresponds to a slin
2028 FOR i IN 1..p_intf_dist_id_tbl.COUNT LOOP
2029
2030 SELECT Count(pdi.interface_distribution_id)
2031 INTO l_lines_count
2032 FROM po_distributions_interface pdi, po_lines_interface pli
2033 WHERE pdi.interface_distribution_id = p_intf_dist_id_tbl(i)
2034 AND pdi.interface_line_id = PLI.interface_line_id
2035 AND PLI.group_line_id IS NOT NULL
2036 AND EXISTS ( --distribution against clin
2037 SELECT interface_distribution_id
2038 FROM po_distributions_interface
2039 WHERE interface_line_id = PLI.group_line_id
2040 );
2041 IF l_lines_count > 0 THEN
2042 x_results.add_result(
2043 p_entity_type => c_ENTITY_TYPE_DISTRIBUTION
2044 , p_entity_id => p_intf_dist_id_tbl(i)
2045 , p_column_name => c_GROUP_LINE_ID
2046 , p_message_name => 'PO_FUNDING_INVALID'
2047 );
2048 END IF;
2049
2050 l_results_count := l_results_count + l_lines_count;
2051
2052 END LOOP;
2053
2054 IF l_results_count > 0 THEN
2055 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
2056 ELSE
2057 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
2058 END IF;
2059
2060 IF PO_LOG.d_proc THEN
2061 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
2062 END IF;
2063 EXCEPTION
2064 WHEN OTHERS THEN
2065 IF PO_LOG.d_exc THEN
2066 PO_LOG.exc(d_mod,0,NULL);
2067 END IF;
2068 RAISE;
2069
2070 END check_clin_slin_funding;
2071
2072
2073
2074 PROCEDURE validate_defence_funding(
2075 p_intf_dist_id_tbl IN PO_TBL_NUMBER,
2076 p_clm_defence_funding_tbl IN PO_TBL_VARCHAR30,
2077 x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE,
2078 x_result_type OUT NOCOPY VARCHAR2)
2079 IS
2080
2081 d_mod CONSTANT VARCHAR2(100) := D_validate_defence_funding;
2082 l_lines_count NUMBER := 0;
2083 l_results_count NUMBER :=0;
2084 is_defence_funding_valid VARCHAR2(1) := NULL;
2085
2086 BEGIN
2087
2088 -- skip the validation for non-CLM documents
2089 IF NOT PO_CLM_CLO_UTIL.Isclminstalled THEN
2090 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
2091 RETURN;
2092 END IF;
2093
2094 IF PO_LOG.d_proc THEN
2095 PO_LOG.proc_begin(d_mod,'p_id_tbl',p_intf_dist_id_tbl);
2096 END IF;
2097
2098 IF (x_results IS NULL) THEN
2099 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
2100 END IF;
2101 --loop through each distribution that corresponds to a slin
2102 FOR i IN 1..p_intf_dist_id_tbl.COUNT
2103 LOOP
2104 IF p_clm_defence_funding_tbl(i) IS null
2105 THEN
2106 x_results.add_result(
2107 p_entity_type => c_ENTITY_TYPE_DISTRIBUTION
2108 , p_entity_id => p_intf_dist_id_tbl(i)
2109 , p_column_name => c_CLM_DEFENCE_FUNDING
2110 , p_message_name => PO_MESSAGE_S.PO_ALL_NOT_NULL
2111 );
2112 l_results_count := l_results_count + l_lines_count;
2113 ELSE
2114 BEGIN
2115 SELECT 'Y' INTO is_defence_funding_valid
2116 FROM dual
2117 WHERE EXISTS ( SELECT 1 FROM po_lookup_codes
2118 WHERE LOOKUP_TYPE = 'PO_FUND_INDICATOR'
2119 AND lookup_code = p_clm_defence_funding_tbl(i)
2120 );
2121 EXCEPTION WHEN No_Data_Found
2122 THEN
2123 x_results.add_result(
2124 p_entity_type => c_ENTITY_TYPE_DISTRIBUTION
2125 , p_entity_id => p_intf_dist_id_tbl(i)
2126 , p_column_name => c_CLM_DEFENCE_FUNDING
2127 , p_message_name => 'PO_DEFENCE_FUNDING_INVALID'
2128 );
2129 l_results_count := l_results_count + 1;
2130 END;
2131 END IF;
2132 END LOOP;
2133 IF l_results_count > 0 THEN
2134 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
2135 ELSE
2136 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
2137 END IF;
2138 IF PO_LOG.d_proc THEN
2139 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
2140 END IF;
2141 EXCEPTION
2142 WHEN OTHERS THEN
2143 IF PO_LOG.d_exc THEN
2144 PO_LOG.exc(d_mod,0,NULL);
2145 END IF;
2146 RAISE;
2147
2148 END validate_defence_funding;
2149
2150
2151
2152 PROCEDURE validate_option_distribution(
2153 p_intf_dist_id_tbl IN PO_TBL_NUMBER,
2154 x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE,
2155 x_result_type OUT NOCOPY VARCHAR2)
2156 IS
2157
2158 d_mod CONSTANT VARCHAR2(100) := D_validate_option_distribution;
2159 l_intf_dist_id_tbl PO_TBL_NUMBER;
2160 l_data_key NUMBER;
2161
2162 BEGIN
2163
2164 -- skip the validation for non-CLM documents
2165 IF NOT PO_CLM_CLO_UTIL.Isclminstalled THEN
2166 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
2167 RETURN;
2168 END IF;
2169
2170 IF PO_LOG.d_proc THEN
2171 PO_LOG.proc_begin(d_mod,'p_id_tbl',p_intf_dist_id_tbl);
2172 END IF;
2173
2174 IF (x_results IS NULL) THEN
2175 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
2176 END IF;
2177
2178 -- Put the stored data for these ids into the temp table.
2179 l_data_key := PO_CORE_S.get_session_gt_nextval();
2180
2181 FORALL i IN 1 .. p_intf_dist_id_tbl.COUNT
2182 INSERT INTO PO_SESSION_GT(
2183 KEY,
2184 num1)
2185 SELECT
2186 l_data_key,
2187 pdi.interface_distribution_id
2188 FROM po_distributions_interface pdi,
2189 po_lines_interface PLI
2190 WHERE pdi.interface_distribution_id = p_intf_dist_id_tbl(i)
2191 AND pdi.interface_line_id = PLI.interface_line_id
2192 AND Nvl(PLI.clm_option_indicator,'*') = 'O'
2193 AND Nvl(PLI.clm_exercised_flag,'N') <> 'Y';
2194
2195 DELETE FROM po_session_gt
2196 WHERE key = l_data_key
2197 RETURNING num1 BULK COLLECT INTO l_intf_dist_id_tbl;
2198
2199 FOR i IN 1..l_intf_dist_id_tbl.Count
2200 LOOP
2201 x_results.add_result(
2202 p_entity_type => c_ENTITY_TYPE_DISTRIBUTION
2203 , p_entity_id => l_intf_dist_id_tbl(i)
2204 , p_column_name => c_CLM_OPTION_INDICATOR
2205 , p_message_name => 'PO_OPTION_DIST_INVALID'
2206 );
2207 END LOOP;
2208
2209 IF l_intf_dist_id_tbl.count > 0 THEN
2210 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
2211 ELSE
2212 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
2213 END IF;
2214 IF PO_LOG.d_proc THEN
2215 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
2216 END IF;
2217 EXCEPTION
2218 WHEN OTHERS THEN
2219 IF PO_LOG.d_exc THEN
2220 PO_LOG.exc(d_mod,0,NULL);
2221 END IF;
2222 RAISE;
2223
2224 END validate_option_distribution;
2225
2226
2227 END PO_VAL_DISTRIBUTIONS;
[Home] [Help]