[Home] [Help]
PACKAGE BODY: APPS.PO_VAL_DISTRIBUTIONS
Source
1 PACKAGE BODY PO_VAL_DISTRIBUTIONS AS
2 -- $Header: PO_VAL_DISTRIBUTIONS.plb 120.16.12010000.2 2008/12/08 10:43:23 cvardia 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 -- The module base for this package.
30 D_PACKAGE_BASE CONSTANT VARCHAR2(50) :=
31 PO_LOG.get_package_base('PO_VAL_DISTRIBUTIONS');
32
33 -- The module base for the subprogram.
34 D_dist_num_unique CONSTANT VARCHAR2(100) :=
35 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'dist_num_unique');
36
37 -- The module base for the subprogram.
38 D_dist_num_gt_zero CONSTANT VARCHAR2(100) :=
39 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'dist_num_gt_zero');
40
41 -- The module base for the subprogram.
42 D_quantity_gt_zero CONSTANT VARCHAR2(100) :=
43 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'quantity_gt_zero');
44
45 -- The module base for the subprogram.
46 D_amount_gt_zero CONSTANT VARCHAR2(100) :=
47 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'amount_gt_zero');
48
49 -- The module base for the subprogram.
50 D_check_fv_validations CONSTANT VARCHAR2(100) :=
51 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'check_fv_validations');
52
53 D_check_proj_rel_validations CONSTANT VARCHAR2(100) :=
54 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'check_proj_related_validations');
55
56 -- <Complex Work R12 Start>: Combine billed/del checks into exec checks
57 -- The module base for the subprogram.
58 D_quantity_ge_quantity_exec CONSTANT VARCHAR2(100) :=
59 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'quantity_ge_quantity_exec');
60
61 D_amount_ge_amount_exec CONSTANT VARCHAR2(100) :=
62 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'amount_ge_amount_exec');
63
64 -- <Complex Work R12 End>
65
66 D_pjm_unit_number_effective CONSTANT VARCHAR2(100) :=
67 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'pjm_unit_number_effective');
68
69 D_oop_enter_all_fields CONSTANT VARCHAR2(100) :=
70 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'oop_enter_all_fields');
71
72 D_amount_to_encumber_ge_zero CONSTANT VARCHAR2(100) :=
73 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'amount_to_encumber_ge_zero');
74 D_budget_account_id_not_null CONSTANT VARCHAR2(100) :=
75 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'budget_account_id_not_null');
76 D_gl_encumbered_date_not_null CONSTANT VARCHAR2(100) :=
77 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'gl_encumbered_date_not_null');
78 D_unencum_amt_le_amt_to_encum CONSTANT VARCHAR2(100) :=
79 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'unencum_amt_le_amt_to_encum');
80
81 -----------------------------------------------------------------------------
82 -- Validates that all the distribution numbers for a given shipment are
83 -- unique.
84 -----------------------------------------------------------------------------
85 -- Assumption:
86 -- All of the unposted distribution data will be passed in
87 -- to this routine in order to get accurate results.
88 PROCEDURE dist_num_unique(
89 p_dist_id_tbl IN PO_TBL_NUMBER
90 , p_line_loc_id_tbl IN PO_TBL_NUMBER
91 , p_dist_num_tbl IN PO_TBL_NUMBER
92 , x_result_set_id IN OUT NOCOPY NUMBER
93 , x_result_type OUT NOCOPY VARCHAR2
94 )
95 IS
96 BEGIN
97 PO_VALIDATION_HELPER.child_num_unique(
98 p_calling_module => D_dist_num_unique
99 , p_entity_type => c_entity_type_DISTRIBUTION
100 , p_entity_id_tbl => p_dist_id_tbl
101 , p_parent_id_tbl => p_line_loc_id_tbl
102 , p_entity_num_tbl => p_dist_num_tbl
103 , x_result_set_id => x_result_set_id
104 , x_result_type => x_result_type
105 );
106
107 END dist_num_unique;
108
109
110 -----------------------------------------------------------------------------
111 -- Checks for null or non-positive distribution numbers.
112 -----------------------------------------------------------------------------
113 PROCEDURE dist_num_gt_zero(
114 p_dist_id_tbl IN PO_TBL_NUMBER
115 , p_dist_num_tbl IN PO_TBL_NUMBER
116 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
117 , x_result_type OUT NOCOPY VARCHAR2
118 )
119 IS
120 BEGIN
121
122 PO_VALIDATION_HELPER.greater_than_zero(
123 p_calling_module => D_dist_num_gt_zero
124 , p_null_allowed_flag => PO_CORE_S.g_parameter_NO
125 , p_value_tbl => p_dist_num_tbl
126 , p_entity_id_tbl => p_dist_id_tbl
127 , p_entity_type => c_ENTITY_TYPE_DISTRIBUTION
128 , p_column_name => c_DISTRIBUTION_NUM
129 , p_message_name => PO_MESSAGE_S.PO_ALL_ENTER_VALUE_GT_ZERO
130 , x_results => x_results
131 , x_result_type => x_result_type
132 );
133
134 END dist_num_gt_zero;
135
136
137 -----------------------------------------------------------------------------
138 -- Validates that quantity is not null and greater than zero if it is not
139 -- a Rate or Fixed Price line.
140 -----------------------------------------------------------------------------
141 PROCEDURE quantity_gt_zero(
142 p_dist_id_tbl IN PO_TBL_NUMBER
143 , p_qty_ordered_tbl IN PO_TBL_NUMBER
144 , p_value_basis_tbl IN PO_TBL_VARCHAR30 -- <Complex Work R12>
145 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
146 , x_result_type OUT NOCOPY VARCHAR2
147 )
148 IS
149 BEGIN
150
151 PO_VALIDATION_HELPER.gt_zero_order_type_filter(
152 p_calling_module => D_quantity_gt_zero
153 , p_value_tbl => p_qty_ordered_tbl
154 , p_entity_id_tbl => p_dist_id_tbl
155 , p_order_type_lookup_code_tbl => p_value_basis_tbl -- <Complex Work R12>
156 , p_check_quantity_types_flag => PO_CORE_S.g_parameter_YES
157 , p_entity_type => c_ENTITY_TYPE_DISTRIBUTION
158 , p_column_name => c_QUANTITY_ORDERED
159 , x_results => x_results
160 , x_result_type => x_result_type
161 );
162
163 END quantity_gt_zero;
164
165
166 -- <Complex Work R12 Start>
167 -- Combined quantity_ge_quantity_billed and quantity_ge_quantity_del into
168 -- quantity_ge_quantity_exec
169
170 -----------------------------------------------------------------------------
171 -- Validates that quantity is greater than or equal to quantity billed,
172 -- delivered and financed.
173 -- This check is only performed if quantity is being reduced below the
174 -- current transaction quantity, since over-billing/delivery is allowed.
175 -----------------------------------------------------------------------------
176 PROCEDURE quantity_ge_quantity_exec(
177 p_dist_id_tbl IN PO_TBL_NUMBER
178 , p_dist_type_tbl IN PO_TBL_VARCHAR30
179 , p_qty_ordered_tbl IN PO_TBL_NUMBER
180 , x_result_set_id IN OUT NOCOPY NUMBER
181 , x_result_type OUT NOCOPY VARCHAR2
182 )
183 IS
184 d_mod CONSTANT VARCHAR2(100) := D_quantity_ge_quantity_exec;
185 BEGIN
186
187 IF PO_LOG.d_proc THEN
188 PO_LOG.proc_begin(d_mod,'p_dist_id_tbl',p_dist_id_tbl);
189 PO_LOG.proc_begin(d_mod,'p_dist_type_tbl',p_dist_type_tbl);
190 PO_LOG.proc_begin(d_mod,'p_qty_ordered_tbl',p_qty_ordered_tbl);
191 PO_LOG.proc_begin(d_mod,'x_result_set_id',x_result_set_id);
192 END IF;
193
194 IF (x_result_set_id IS NULL) THEN
195 x_result_set_id := PO_VALIDATIONS.next_result_set_id();
196 END IF;
197
198 FORALL i IN 1 .. p_dist_id_tbl.COUNT
199 INSERT INTO PO_VALIDATION_RESULTS_GT
200 ( result_set_id
201 , entity_type
202 , entity_id
203 , column_name
204 , column_val
205 , message_name
206 --PBWC Message Change Impact: Adding a token
207 , token1_name
208 , token1_value
209 )
210 SELECT
211 x_result_set_id
212 , c_ENTITY_TYPE_DISTRIBUTION
213 , p_dist_id_tbl(i)
214 , C_QUANTITY_ORDERED
215 , TO_CHAR(p_qty_ordered_tbl(i))
216 , (CASE
217 WHEN NVL(POD.quantity_delivered, 0) >
218 GREATEST(NVL(POD.quantity_billed, 0), NVL(POD.quantity_financed, 0))
219 THEN PO_MESSAGE_S.PO_PO_QTY_ORD_LT_QTY_DEL_NA
220 ELSE PO_MESSAGE_S.PO_PO_QTY_ORD_LT_QTY_BILLED_NA
221 END
222 )
223 --PBWC Message Change Impact: Adding a token
224 , (CASE
225 WHEN NVL(POD.quantity_delivered, 0) >
226 GREATEST(NVL(POD.quantity_billed, 0), NVL(POD.quantity_financed, 0))
227 THEN PO_MESSAGE_S.c_QTY_DEL_token
228 ELSE PO_MESSAGE_S.c_QTY_BILLED_token
229 END
230 )
231 , (CASE
232 WHEN NVL(POD.quantity_delivered, 0) >
233 GREATEST(NVL(POD.quantity_billed, 0), NVL(POD.quantity_financed, 0))
234 THEN TO_CHAR(POD.quantity_delivered)
235 ELSE TO_CHAR(POD.quantity_billed)
236 END
237 )
238 FROM
239 PO_DISTRIBUTIONS_ALL POD
240 WHERE
241 POD.po_distribution_id = p_dist_id_tbl(i)
242 AND p_dist_type_tbl(i) IN (c_STANDARD, c_PREPAYMENT) -- <Complex Work R12>
243 -- Quantity is being reduced below the current transaction quantity:
244 AND p_qty_ordered_tbl(i) < POD.quantity_ordered
245 AND p_qty_ordered_tbl(i) < -- <Complex Work R12>
246 GREATEST(NVL(POD.quantity_delivered, 0),
247 NVL(POD.quantity_billed, 0),
248 NVL(POD.quantity_financed, 0))
249 ;
250
251 IF (SQL%ROWCOUNT > 0) THEN
252 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
253 ELSE
254 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
255 END IF;
256
257 IF PO_LOG.d_proc THEN
258 PO_VALIDATIONS.log_validation_results_gt(d_mod,9,x_result_set_id);
259 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
260 PO_LOG.proc_end(d_mod,'x_result_set_id',x_result_set_id);
261 END IF;
262
263 EXCEPTION
264 WHEN OTHERS THEN
265 IF PO_LOG.d_exc THEN
266 PO_LOG.exc(d_mod,0,NULL);
267 END IF;
268 RAISE;
269
270 END quantity_ge_quantity_exec;
271
272 -- <Complex Work R12 End>
273
274 -----------------------------------------------------------------------------
275 -- Validates that amount is not null and greater than zero if the line is
276 -- Rate or Fixed Price.
277 -----------------------------------------------------------------------------
278 PROCEDURE amount_gt_zero(
279 p_dist_id_tbl IN PO_TBL_NUMBER
280 , p_amt_ordered_tbl IN PO_TBL_NUMBER
281 , p_value_basis_tbl IN PO_TBL_VARCHAR30 -- <Complex Work R12>
282 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
283 , x_result_type OUT NOCOPY VARCHAR2
284 )
285 IS
286 BEGIN
287
288 PO_VALIDATION_HELPER.gt_zero_order_type_filter(
289 p_calling_module => D_amount_gt_zero
290 , p_value_tbl => p_amt_ordered_tbl
291 , p_entity_id_tbl => p_dist_id_tbl
292 , p_order_type_lookup_code_tbl => p_value_basis_tbl -- <Complex Work R12>
293 , p_check_quantity_types_flag => PO_CORE_S.g_parameter_NO
294 , p_entity_type => c_ENTITY_TYPE_DISTRIBUTION
295 , p_column_name => c_AMOUNT_ORDERED
296 , x_results => x_results
297 , x_result_type => x_result_type
298 );
299
300 END amount_gt_zero;
301
302 -- <Complex Work R12 Start>
303 -- Combined amount_ge_amount_billed and amount_ge_amount_del into
304 -- amount_ge_amount_exec
305
306 -----------------------------------------------------------------------------
307 -- Validates that amount is greater than or equal to
308 -- amount billed, amount financed, and amount delivered.
309 -- This check is only performed if amount is being reduced below the
310 -- current transaction amount, since over-delivery/billing is allowed.
311 -----------------------------------------------------------------------------
312 PROCEDURE amount_ge_amount_exec(
313 p_dist_id_tbl IN PO_TBL_NUMBER
314 , p_dist_type_tbl IN PO_TBL_VARCHAR30
315 , p_amt_ordered_tbl IN PO_TBL_NUMBER
316 , x_result_set_id IN OUT NOCOPY NUMBER
317 , x_result_type OUT NOCOPY VARCHAR2
318 )
319 IS
320 d_mod CONSTANT VARCHAR2(100) := D_amount_ge_amount_exec;
321 BEGIN
322
323 IF PO_LOG.d_proc THEN
324 PO_LOG.proc_begin(d_mod,'p_dist_id_tbl',p_dist_id_tbl);
325 PO_LOG.proc_begin(d_mod,'p_dist_type_tbl',p_dist_type_tbl);
326 PO_LOG.proc_begin(d_mod,'p_amt_ordered_tbl',p_amt_ordered_tbl);
327 PO_LOG.proc_begin(d_mod,'x_result_set_id',x_result_set_id);
328 END IF;
329
330 IF (x_result_set_id IS NULL) THEN
331 x_result_set_id := PO_VALIDATIONS.next_result_set_id();
332 END IF;
333
334 FORALL i IN 1 .. p_dist_id_tbl.COUNT
335 INSERT INTO PO_VALIDATION_RESULTS_GT
336 ( result_set_id
337 , entity_type
338 , entity_id
339 , column_name
340 , column_val
341 , message_name
342 --PBWC Message Change Impact: Adding a token
343 , token1_name
344 , token1_value
345 )
346 SELECT
347 x_result_set_id
348 , c_ENTITY_TYPE_DISTRIBUTION
349 , p_dist_id_tbl(i)
350 , C_AMOUNT_ORDERED
351 , TO_CHAR(p_amt_ordered_tbl(i))
352 , (CASE
353 WHEN NVL(POD.amount_delivered, 0) >
354 GREATEST(NVL(POD.amount_billed, 0), NVL(POD.amount_financed, 0))
355 THEN PO_MESSAGE_S.PO_PO_AMT_ORD_LT_AMT_DEL_NA
356 ELSE PO_MESSAGE_S.PO_PO_AMT_ORD_LT_AMT_BILLED_NA
357 END
358 )
359 --PBWC Message Change Impact: Adding a token
360 , (CASE
361 WHEN NVL(POD.amount_delivered, 0) >
362 GREATEST(NVL(POD.amount_billed, 0), NVL(POD.amount_financed, 0))
363 THEN PO_MESSAGE_S.c_AMT_DEL_token
364 ELSE PO_MESSAGE_S.c_AMT_BILLED_token
365 END
366 )
367 , (CASE
368 WHEN NVL(POD.amount_delivered, 0) >
369 GREATEST(NVL(POD.amount_billed, 0), NVL(POD.amount_financed, 0))
370 THEN TO_CHAR(POD.amount_delivered)
371 ELSE TO_CHAR(POD.amount_billed)
372 END
373 )
374 FROM
375 PO_DISTRIBUTIONS_ALL POD
376 WHERE
377 POD.po_distribution_id = p_dist_id_tbl(i)
378 AND p_dist_type_tbl(i) IN (c_STANDARD, c_PREPAYMENT) -- <Complex Work R12>
379 -- Amount is being reduced below the current transaction amount:
380 AND p_amt_ordered_tbl(i) < POD.amount_ordered
381 AND p_amt_ordered_tbl(i) < GREATEST(NVL(POD.amount_delivered, 0),
382 NVL(POD.amount_financed, 0),
383 NVL(POD.amount_billed, 0));
384
385 IF (SQL%ROWCOUNT > 0) THEN
386 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
387 ELSE
388 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
389 END IF;
390
391 IF PO_LOG.d_proc THEN
392 PO_VALIDATIONS.log_validation_results_gt(d_mod,9,x_result_set_id);
393 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
394 PO_LOG.proc_end(d_mod,'x_result_set_id',x_result_set_id);
395 END IF;
396
397 EXCEPTION
398 WHEN OTHERS THEN
399 IF PO_LOG.d_exc THEN
400 PO_LOG.exc(d_mod,0,NULL);
401 END IF;
402 RAISE;
403
404 END amount_ge_amount_exec;
405
406 -- <Complex Work R12 End>
407
408 ------------------------------------------------------------------------
409 -- Validates that if the item is unit number effective, then the unit number
410 -- on the distribution is not null.
411 --
412 -- Displays the warning 'UEFF-UNIT NUMBER REQUIRED' if all the following are true:
413 -- 1) Item on the line is unit number effective
414 -- 2) Unit number field at the distribution level is null
415 --
416 -- Where clauses derived from PJM_UNIT_EFF.UNIT_EFFECTIVE_ITEM
417 ------------------------------------------------------------------------
418 PROCEDURE pjm_unit_number_effective(
419 p_dist_id_tbl IN PO_TBL_NUMBER
420 , p_end_item_unit_number_tbl IN PO_TBL_VARCHAR30
421 , p_item_id_tbl IN PO_TBL_NUMBER
422 , p_ship_to_org_id_tbl IN PO_TBL_NUMBER
423 -- Bug# 4338241: Checking if it is inventory and PJM is installed
424 , p_destination_type_code_tbl IN PO_TBL_VARCHAR30
425 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
426 , x_result_type OUT NOCOPY VARCHAR2
427 )
428 IS
429 d_mod CONSTANT VARCHAR2(100) := D_pjm_unit_number_effective;
430
431 l_unit_number_effective VARCHAR2(1);
432 l_results_count NUMBER;
433 BEGIN
434
435 IF PO_LOG.d_proc THEN
436 PO_LOG.proc_begin(d_mod,'p_dist_id_tbl',p_dist_id_tbl);
437 PO_LOG.proc_begin(d_mod,'p_end_item_unit_number_tbl',p_end_item_unit_number_tbl);
438 PO_LOG.proc_begin(d_mod,'p_item_id_tbl',p_item_id_tbl);
439 PO_LOG.proc_begin(d_mod,'p_ship_to_org_id_tbl',p_ship_to_org_id_tbl);
440 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
441 END IF;
442
443 IF (x_results IS NULL) THEN
444 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
445 END IF;
446
447 l_results_count := x_results.result_type.COUNT;
448
449 FOR i IN 1 .. p_dist_id_tbl.COUNT LOOP
450
451 -- Bug# 4338241
452 -- Do this validation only if PJM is installed and
453 -- destination type is not EXPENSE
454 -- If the unit number field at the distributions level is null
455 -- Then check if the item and org are unit number effective
456
457 -- Bug 5193851
458 -- Changed the check po_core_s.get_product_install_status('PJM') = 'Y'
459 -- to po_core_s.get_product_install_status('PJM') = 'I'
460 IF (po_core_s.get_product_install_status('PJM') = 'I' AND
461 p_destination_type_code_tbl(i) <> c_DEST_TYPE_EXPENSE AND
462 p_end_item_unit_number_tbl(i) IS NULL) THEN
463
464 l_unit_number_effective :=
465 PO_PROJECT_DETAILS_SV.pjm_unit_eff_item(p_item_id_tbl(i),p_ship_to_org_id_tbl(i));
466
467 IF (l_unit_number_effective = 'Y') THEN
468
469 x_results.add_result(
470 p_entity_type => c_ENTITY_TYPE_DISTRIBUTION
471 , p_entity_id => p_dist_id_tbl(i)
472 , p_column_name => c_END_ITEM_UNIT_NUMBER
473 , p_message_name => PO_MESSAGE_S.PO_ALL_NOT_NULL -- Bug 5193851 - Changed the message
474 );
475
476 END IF;
477
478 END IF;
479
480 END LOOP;
481
482 IF (l_results_count < x_results.result_type.COUNT) THEN
483 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
484 ELSE
485 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
486 END IF;
487
488 IF PO_LOG.d_proc THEN
489 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
490 PO_LOG.log(PO_LOG.c_PROC_END,d_mod,NULL,'x_results',x_results);
491 END IF;
492
493 EXCEPTION
494 WHEN OTHERS THEN
495 IF PO_LOG.d_exc THEN
496 PO_LOG.exc(d_mod,0,NULL);
497 END IF;
498 RAISE;
499
500 END pjm_unit_number_effective;
501
502 PROCEDURE oop_enter_all_fields(
503 p_dist_id_tbl IN PO_TBL_NUMBER
504 , p_line_line_type_id_tbl IN PO_TBL_NUMBER
505 , p_wip_entity_id_tbl IN PO_TBL_NUMBER
506 , p_wip_line_id_tbl IN PO_TBL_NUMBER
507 , p_wip_operation_seq_num_tbl IN PO_TBL_NUMBER
508 , p_destination_type_code_tbl IN PO_TBL_VARCHAR30
509 , p_wip_resource_seq_num_tbl IN PO_TBL_NUMBER
510 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
511 , x_result_type OUT NOCOPY VARCHAR2
512 )
513 IS
514 d_mod CONSTANT VARCHAR2(100) := D_oop_enter_all_fields;
515
516 l_results_count NUMBER;
517 l_outside_operation_flag VARCHAR2(1);
518 BEGIN
519
520 IF PO_LOG.d_proc THEN
521 PO_LOG.proc_begin(d_mod,'p_dist_id_tbl',p_dist_id_tbl);
522 PO_LOG.proc_begin(d_mod,'p_line_line_type_id_tbl',p_line_line_type_id_tbl);
523 PO_LOG.proc_begin(d_mod,'p_wip_entity_id_tbl',p_wip_entity_id_tbl);
524 PO_LOG.proc_begin(d_mod,'p_wip_line_id_tbl',p_wip_line_id_tbl);
525 PO_LOG.proc_begin(d_mod,'p_wip_operation_seq_num_tbl',p_wip_operation_seq_num_tbl);
526 PO_LOG.proc_begin(d_mod,'p_destination_type_code_tbl',p_destination_type_code_tbl);
527 PO_LOG.proc_begin(d_mod,'p_wip_resource_seq_num_tbl',p_wip_resource_seq_num_tbl);
528 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
529 END IF;
530
531 IF (x_results IS NULL) THEN
532 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
533 END IF;
534
535 l_results_count := x_results.result_type.COUNT;
536
537 FOR i IN 1 .. p_dist_id_tbl.COUNT LOOP
538
539 -- Get outside operation flag
540 SELECT outside_operation_flag
541 INTO l_outside_operation_flag
542 FROM po_line_types
543 WHERE line_type_id = p_line_line_type_id_tbl(i);
544
545 IF (p_destination_type_code_tbl(i) = c_DEST_TYPE_SHOP_FLOOR)
546 THEN
547 IF (p_wip_entity_id_tbl(i) IS NULL
548 AND p_wip_line_id_tbl(i) IS NULL)
549 THEN
550 x_results.add_result(
551 p_entity_type => c_ENTITY_TYPE_DISTRIBUTION
552 , p_entity_id => p_dist_id_tbl(i)
553 , p_column_name => c_WIP_ENTITY_ID
554 , p_message_name => PO_MESSAGE_S.PO_OOP_ENTER_ALL_FIELDS
555 );
556 END IF;
557
558 IF (p_wip_operation_seq_num_tbl(i) IS NULL)
559 THEN
560 x_results.add_result(
561 p_entity_type => c_ENTITY_TYPE_DISTRIBUTION
562 , p_entity_id => p_dist_id_tbl(i)
563 , p_column_name => c_WIP_OPERATION_SEQ_NUM
564 , p_message_name => PO_MESSAGE_S.PO_OOP_ENTER_ALL_FIELDS
565 );
566 END IF;
567
568 -- BR says that if WIP line id is not null,
569 -- then assembly cannot be null.
570 --
571 -- Assembly is not stored in PO tables, but if one chooses
572 -- an assembly, then one must also choose a job. Therefore,
573 -- we check here if the job column is null instead.
574 IF (p_wip_line_id_tbl(i) IS NOT NULL
575 AND p_wip_entity_id_tbl(i) IS NULL)
576 THEN
577 x_results.add_result(
578 p_entity_type => c_ENTITY_TYPE_DISTRIBUTION
579 , p_entity_id => p_dist_id_tbl(i)
580 , p_column_name => c_WIP_ENTITY_ID
581 , p_message_name => PO_MESSAGE_S.PO_OOP_ENTER_ALL_FIELDS
582 );
583 END IF;
584
585 -- If OSP line, then also validate that resource sequence is not null
586 IF (l_outside_operation_flag = 'Y'
587 AND p_wip_resource_seq_num_tbl(i) IS NULL)
588 THEN
589 x_results.add_result(
590 p_entity_type => c_ENTITY_TYPE_DISTRIBUTION
591 , p_entity_id => p_dist_id_tbl(i)
592 , p_column_name => c_WIP_RESOURCE_SEQ_NUM
593 , p_message_name => PO_MESSAGE_S.PO_OOP_ENTER_ALL_FIELDS
594 );
595 END IF;
596 END IF; -- IF (p_destination_type_code_tbl(i) = c_DEST_TYPE_SHOP_FLOOR)
597
598 END LOOP;
599
600 IF (l_results_count < x_results.result_type.COUNT) THEN
601 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
602 ELSE
603 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
604 END IF;
605
606 IF PO_LOG.d_proc THEN
607 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
608 PO_LOG.log(PO_LOG.c_PROC_END,d_mod,NULL,'x_results',x_results);
609 END IF;
610
611 EXCEPTION
612 WHEN OTHERS THEN
613 IF PO_LOG.d_exc THEN
614 PO_LOG.exc(d_mod,0,NULL);
615 END IF;
616 RAISE;
617
618 END oop_enter_all_fields;
619
620
621 -------------------------------------------------------------------------
622 -- Check that unencumbered amount is less than or equal to amount to
623 -- encumber.
624 -- Agreements only.
625 -------------------------------------------------------------------------
626 PROCEDURE unencum_amt_le_amt_to_encum(
627 p_dist_id_tbl IN PO_TBL_NUMBER
628 , p_amount_to_encumber_tbl IN PO_TBL_NUMBER
629 , p_unencumbered_amount_tbl IN PO_TBL_NUMBER
630 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
631 , x_result_type OUT NOCOPY VARCHAR2
632 )
633 IS
634 BEGIN
635
636 PO_VALIDATION_HELPER.num1_less_or_equal_num2(
637 p_calling_module => D_unencum_amt_le_amt_to_encum
638 , p_num1_tbl => p_unencumbered_amount_tbl
639 , p_num2_tbl => p_amount_to_encumber_tbl
640 , p_entity_id_tbl => p_dist_id_tbl
641 , p_entity_type => c_entity_type_DISTRIBUTION
642 , p_column_name => c_AMOUNT_TO_ENCUMBER
643 , p_message_name => PO_MESSAGE_S.PO_AMT_TO_ENCUM_LT_UNENCUM
644 , x_results => x_results
645 , x_result_type => x_result_type
646 );
647
648 END unencum_amt_le_amt_to_encum;
649
650 -----------------------------------------------------------------------------
651 -- Checks that the Amount To Encumber is not null and >= 0.
652 -- Agreements only.
653 -----------------------------------------------------------------------------
654 PROCEDURE amount_to_encumber_ge_zero(
655 p_dist_id_tbl IN PO_TBL_NUMBER
656 , p_amount_to_encumber_tbl IN PO_TBL_NUMBER
657 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
658 , x_result_type OUT NOCOPY VARCHAR2
659 )
660 IS
661 BEGIN
662
663 PO_VALIDATION_HELPER.greater_or_equal_zero(
664 p_calling_module => D_amount_to_encumber_ge_zero
665 , p_null_allowed_flag => PO_CORE_S.g_parameter_NO
666 , p_value_tbl => p_amount_to_encumber_tbl
667 , p_entity_id_tbl => p_dist_id_tbl
668 , p_entity_type => c_ENTITY_TYPE_DISTRIBUTION
669 , p_column_name => c_AMOUNT_TO_ENCUMBER
670 , p_message_name => PO_MESSAGE_S.PO_ALL_ENTER_VALUE_GE_ZERO
671 , x_results => x_results
672 , x_result_type => x_result_type
673 );
674
675 END amount_to_encumber_ge_zero;
676
677
678 -------------------------------------------------------------------------------
679 -- Ensures that the Budget Account is not null.
680 -- Agreements only.
681 -------------------------------------------------------------------------------
682 PROCEDURE budget_account_id_not_null(
683 p_dist_id_tbl IN PO_TBL_NUMBER
684 , p_budget_account_id_tbl IN PO_TBL_NUMBER
685 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
686 , x_result_type OUT NOCOPY VARCHAR2
687 )
688 IS
689 BEGIN
690
691 PO_VALIDATION_HELPER.not_null(
692 p_calling_module => D_budget_account_id_not_null
693 , p_value_tbl => PO_TYPE_CONVERTER.to_po_tbl_varchar4000(p_budget_account_id_tbl)
694 , p_entity_id_tbl => p_dist_id_tbl
695 , p_entity_type => c_entity_type_DISTRIBUTION
696 , p_column_name => c_BUDGET_ACCOUNT_ID
697 , p_message_name => PO_MESSAGE_S.PO_ALL_NOT_NULL
698 , x_results => x_results
699 , x_result_type => x_result_type
700 );
701
702 END budget_account_id_not_null;
703
704
705 -------------------------------------------------------------------------------
706 -- Ensures that the GL Encumbered Date is not null.
707 -- Agreements only.
708 -------------------------------------------------------------------------------
709 PROCEDURE gl_encumbered_date_not_null(
710 p_dist_id_tbl IN PO_TBL_NUMBER
711 , p_gl_encumbered_date_tbl IN PO_TBL_DATE
712 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
713 , x_result_type OUT NOCOPY VARCHAR2
714 )
715 IS
716 BEGIN
717
718 PO_VALIDATION_HELPER.not_null(
719 p_calling_module => D_gl_encumbered_date_not_null
720 , p_value_tbl => PO_TYPE_CONVERTER.to_po_tbl_varchar4000(p_gl_encumbered_date_tbl)
721 , p_entity_id_tbl => p_dist_id_tbl
722 , p_entity_type => c_entity_type_DISTRIBUTION
723 , p_column_name => c_GL_ENCUMBERED_DATE
724 , p_message_name => PO_MESSAGE_S.PO_ALL_NOT_NULL
725 , x_results => x_results
726 , x_result_type => x_result_type
727 );
728
729 END gl_encumbered_date_not_null;
730
731 ------------------------------------------------------------------------
732 -- Validates that if PO encumbrance is on, the GL Date is not null and
733 -- is in an open period.
734 -- For both SPOs and BPAs.
735 ------------------------------------------------------------------------
736 PROCEDURE gl_enc_date_not_null_open(
737 p_dist_id_tbl IN PO_TBL_NUMBER
738 , p_org_id_tbl IN PO_TBL_NUMBER
739 , p_gl_encumbered_date_tbl IN PO_TBL_DATE
740 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
741 , x_result_type OUT NOCOPY VARCHAR2
742 )
743 IS
744 l_sob_id FINANCIALS_SYSTEM_PARAMS_ALL.set_of_books_id%TYPE;
745 l_po_enc_flag FINANCIALS_SYSTEM_PARAMS_ALL.purch_encumbrance_flag%TYPE;
746 l_gl_enc_period_name PO_DISTRIBUTIONS_ALL.gl_encumbered_period_name %TYPE;
747 l_results_count NUMBER;
748 BEGIN
749
750 IF (x_results IS NULL) THEN
751 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
752 END IF;
753
754 l_results_count := x_results.result_type.COUNT;
755
756 FOR I IN 1..p_dist_id_tbl.COUNT LOOP
757 SELECT purch_encumbrance_flag, set_of_books_id
758 INTO l_po_enc_flag, l_sob_id
759 FROM financials_system_params_all
760 WHERE org_id = p_org_id_tbl(i);
761
762 IF (l_po_enc_flag = 'Y') THEN
763
764 IF (p_gl_encumbered_date_tbl(i) IS NULL) THEN -- Error: GL Date is required
765 x_results.add_result(
766 p_entity_type => c_ENTITY_TYPE_DISTRIBUTION
767 , p_entity_id => p_dist_id_tbl(i)
768 , p_column_name => c_GL_ENCUMBERED_DATE
769 , p_column_val => NULL
770 , p_message_name => PO_MESSAGE_S.PO_ALL_NOT_NULL
771 );
772
773 ELSE -- Verify that GL Date is in an open period.
774 po_periods_sv.get_period_name(l_sob_id,
775 p_gl_encumbered_date_tbl(i),
776 l_gl_enc_period_name);
777
778 IF (l_gl_enc_period_name IS NULL) THEN
779 x_results.add_result(
780 p_entity_type => c_ENTITY_TYPE_DISTRIBUTION
781 , p_entity_id => p_dist_id_tbl(i)
782 , p_column_name => c_GL_ENCUMBERED_DATE
783 , p_column_val => TO_CHAR(p_gl_encumbered_date_tbl(i))
784 , p_message_name => PO_MESSAGE_S.PO_PO_ENTER_OPEN_GL_DATE
785 );
786 END IF;
787
788 END IF; -- p_gl_date_tbl(i) IS NULL
789 END IF; -- l_po_enc_flag = 'Y'
790 END LOOP;
791
792 IF (l_results_count < x_results.result_type.COUNT) THEN
793 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
794 ELSE
795 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
796 END IF;
797
798 END gl_enc_date_not_null_open;
799
800 ------------------------------------------------------------------------
801 -- gms_data_valid
802 --
803 -- Checks with the PO/Grants interface functions whether or not the
804 -- award data on a distribution is valid.
805 --
806 ------------------------------------------------------------------------
807 PROCEDURE gms_data_valid(
808 p_dist_id_tbl IN PO_TBL_NUMBER
809 , p_project_id_tbl IN PO_TBL_NUMBER
810 , p_task_id_tbl IN PO_TBL_NUMBER
811 , p_award_number_tbl IN PO_TBL_VARCHAR2000
812 , p_expenditure_type_tbl IN PO_TBL_VARCHAR30
813 , p_expenditure_item_date_tbl IN PO_TBL_DATE
814 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
815 , x_result_type OUT NOCOPY VARCHAR2
816 )
817 IS
818 l_failure_dist_id_tbl PO_TBL_NUMBER;
819 l_failure_message_tbl PO_TBL_VARCHAR4000;
820 l_results_count NUMBER;
821
822 BEGIN
823
824 IF (x_results IS NULL) THEN
825 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
826 END IF;
827
828 l_results_count := x_results.result_type.COUNT;
829
830 IF (PO_GMS_INTEGRATION_PVT.is_gms_enabled()) THEN
831
832 PO_GMS_INTEGRATION_PVT.validate_award_data(
833 p_dist_id_tbl => p_dist_id_tbl
834 , p_project_id_tbl => p_project_id_tbl
835 , p_task_id_tbl => p_task_id_tbl
836 , p_award_number_tbl => p_award_number_tbl
837 , p_expenditure_type_tbl => p_expenditure_type_tbl
838 , p_expenditure_item_date_tbl => p_expenditure_item_date_tbl
839 , x_failure_dist_id_tbl => l_failure_dist_id_tbl
840 , x_failure_message_tbl => l_failure_message_tbl
841 );
842
843 IF (l_failure_dist_id_tbl IS NOT NULL) THEN
844 FOR i IN 1 .. l_failure_dist_id_tbl.COUNT LOOP
845 x_results.add_result(
846 p_entity_type => c_entity_type_DISTRIBUTION
847 , p_entity_id => l_failure_dist_id_tbl(i)
848 , p_column_name => c_AWARD_ID
849 , p_message_name => PO_MESSAGE_S.PO_WRAPPER_MESSAGE
850 , p_token1_name => PO_MESSAGE_S.c_MESSAGE_token
851 , p_token1_value => l_failure_message_tbl(i)
852 );
853 END LOOP;
854 END IF;
855
856 END IF; -- if is_gms_enabled
857
858 IF (l_results_count < x_results.result_type.COUNT) THEN
859 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
860 ELSE
861 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
862 END IF;
863
864 END gms_data_valid;
865
866 ------------------------------------------------------------------------
867 -- ECO 4059111
868 -- Performs the federal financial validations for distributions
869 -- For Standard POs only.
870 ------------------------------------------------------------------------
871 PROCEDURE check_fv_validations(
872 p_dist_id_tbl IN PO_TBL_NUMBER
873 , p_ccid_tbl IN PO_TBL_NUMBER
874 , p_org_id_tbl IN PO_TBL_NUMBER
875 , p_attribute1_tbl IN PO_TBL_VARCHAR2000
876 , p_attribute2_tbl IN PO_TBL_VARCHAR2000
877 , p_attribute3_tbl IN PO_TBL_VARCHAR2000
878 , p_attribute4_tbl IN PO_TBL_VARCHAR2000
879 , p_attribute5_tbl IN PO_TBL_VARCHAR2000
880 , p_attribute6_tbl IN PO_TBL_VARCHAR2000
881 , p_attribute7_tbl IN PO_TBL_VARCHAR2000
882 , p_attribute8_tbl IN PO_TBL_VARCHAR2000
883 , p_attribute9_tbl IN PO_TBL_VARCHAR2000
884 , p_attribute10_tbl IN PO_TBL_VARCHAR2000
885 , p_attribute11_tbl IN PO_TBL_VARCHAR2000
886 , p_attribute12_tbl IN PO_TBL_VARCHAR2000
887 , p_attribute13_tbl IN PO_TBL_VARCHAR2000
888 , p_attribute14_tbl IN PO_TBL_VARCHAR2000
889 , p_attribute15_tbl IN PO_TBL_VARCHAR2000
890 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
891 , x_result_type OUT NOCOPY VARCHAR2
892 )
893 IS
894 d_mod CONSTANT VARCHAR2(100) := D_check_fv_validations;
895
896 l_ledger_id FINANCIALS_SYSTEM_PARAMS_ALL.set_of_books_id%TYPE;
897 l_return_status VARCHAR2(1);
898 l_error_message VARCHAR2(2000);
899 l_results_count NUMBER;
900 l_result_type VARCHAR2(30);
901 BEGIN
902
903 IF PO_LOG.d_proc THEN
904 PO_LOG.proc_begin(d_mod,'p_dist_id_tbl',p_dist_id_tbl);
905 PO_LOG.proc_begin(d_mod,'p_ccid_tbl',p_ccid_tbl);
906 PO_LOG.proc_begin(d_mod,'p_org_id_tbl',p_org_id_tbl);
907 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
908 END IF;
909
910 IF (x_results IS NULL) THEN
911 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
912 END IF;
913
914 l_results_count := x_results.result_type.COUNT;
915
916 FOR i IN 1..p_dist_id_tbl.COUNT LOOP
917
918 IF FV_INSTALL.enabled(p_org_id_tbl(i)) THEN
919
920 SELECT set_of_books_id
921 INTO l_ledger_id
922 FROM financials_system_params_all
923 WHERE org_id = p_org_id_tbl(i);
924
925 FV_PO_VALIDATE_GRP.CHECK_AGREEMENT_DATES(
926 x_code_combination_id => p_ccid_tbl(i),
927 x_org_id => p_org_id_tbl(i),
928 x_ledger_id => l_ledger_id,
929 x_called_from => 'PO',
930 x_ATTRIBUTE1 => p_attribute1_tbl(i),
931 x_ATTRIBUTE2 => p_attribute2_tbl(i),
932 x_ATTRIBUTE3 => p_attribute3_tbl(i),
933 x_ATTRIBUTE4 => p_attribute4_tbl(i),
934 x_ATTRIBUTE5 => p_attribute5_tbl(i),
935 x_ATTRIBUTE6 => p_attribute6_tbl(i),
936 x_ATTRIBUTE7 => p_attribute7_tbl(i),
937 x_ATTRIBUTE8 => p_attribute8_tbl(i),
938 x_ATTRIBUTE9 => p_attribute9_tbl(i),
939 x_ATTRIBUTE10 => p_attribute10_tbl(i),
940 x_ATTRIBUTE11 => p_attribute11_tbl(i),
941 x_ATTRIBUTE12 => p_attribute12_tbl(i),
942 x_ATTRIBUTE13 => p_attribute13_tbl(i),
943 x_ATTRIBUTE14 => p_attribute14_tbl(i),
944 x_ATTRIBUTE15 => p_attribute15_tbl(i),
945 x_status => l_return_status,
946 x_message => l_error_message) ;
947
948
949 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
950 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
951 l_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
952 ELSE
953 l_result_type := PO_VALIDATIONS.c_result_type_WARNING;
954 END IF;
955
956 x_results.add_result(
957 p_entity_type => c_entity_type_DISTRIBUTION
958 , p_entity_id => p_dist_id_tbl(i)
959 , p_column_name => c_CODE_COMBINATION_ID
960 , p_column_val => NULL
961 , p_result_type => l_result_type
962 , p_message_name => l_error_message
963 );
964 END IF;
965
966 END IF; -- FV Enabled
967 END LOOP;
968
969 IF (l_results_count < x_results.result_type.COUNT) THEN
970
971 x_result_type := PO_VALIDATIONS.c_result_type_WARNING;
972
973 FOR j IN 1..x_results.result_type.COUNT LOOP
974 IF (x_results.result_type(j) = PO_VALIDATIONS.c_result_type_FAILURE)
975 THEN
976 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
977 END IF;
978 exit;
979 END LOOP;
980
981 ELSE
982 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
983 END IF;
984
985 IF PO_LOG.d_proc THEN
986 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
987 PO_LOG.log(PO_LOG.c_PROC_END,d_mod,NULL,'x_results',x_results);
988 END IF;
989
990 EXCEPTION
991 WHEN OTHERS THEN
992 IF PO_LOG.d_exc THEN
993 PO_LOG.exc(d_mod,0,NULL);
994 END IF;
995 RAISE;
996
997 END check_fv_validations;
998
999 ------------------------------------------------------------------------
1000 -- Bug 5442682
1001 -- Performs required field validations on project related fields
1002 -- Following business rules apply (given project is filled in) :
1003 -- 1. Task is required irrespective of destination type
1004 -- 2. For expense lines, expenditure org and expenditure item
1005 -- date are required
1006 -- 3. If gms is installed and award is required for the project,
1007 -- expenditure type is required if award is filled in
1008 -- 4. If gms is not installed or if award is not required,
1009 -- expenditure item type is required.
1010 ------------------------------------------------------------------------
1011 PROCEDURE check_proj_related_validations(
1012 p_dist_id_tbl IN PO_TBL_NUMBER
1013 , p_dest_type_code_tbl IN PO_TBL_VARCHAR30
1014 , p_project_id_tbl IN PO_TBL_NUMBER
1015 , p_task_id_tbl IN PO_TBL_NUMBER
1016 , p_award_id_tbl IN PO_TBL_NUMBER
1017 , p_expenditure_type_tbl IN PO_TBL_VARCHAR30
1018 , p_expenditure_org_id_tbl IN PO_TBL_NUMBER
1019 , p_expenditure_item_date_tbl IN PO_TBL_DATE
1020 , p_ship_to_org_id_tbl IN PO_TBL_NUMBER
1021 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
1022 , x_result_type OUT NOCOPY VARCHAR2
1023 )
1024 IS
1025 d_mod CONSTANT VARCHAR2(100) := D_check_proj_rel_validations;
1026 l_error_message VARCHAR2(2000);
1027 l_award_required_flag VARCHAR2(2);
1028 l_expenditure_type_reqd BOOLEAN := FALSE;
1029 l_results_count NUMBER;
1030 x_project_reference_enabled NUMBER;
1031 x_project_control_level NUMBER;
1032 BEGIN
1033 IF PO_LOG.d_proc THEN
1034 PO_LOG.proc_begin(d_mod,'p_dist_id_tbl',p_dist_id_tbl);
1035 PO_LOG.proc_begin(d_mod,'p_dest_type_code_tbl',p_dest_type_code_tbl);
1036 PO_LOG.proc_begin(d_mod,'p_project_id_tbl',p_project_id_tbl);
1037 PO_LOG.proc_begin(d_mod,'p_task_id_tbl',p_task_id_tbl);
1038 PO_LOG.proc_begin(d_mod,'p_award_id_tbl',p_award_id_tbl);
1039 PO_LOG.proc_begin(d_mod,'p_expenditure_type_tbl',p_expenditure_type_tbl);
1040 PO_LOG.proc_begin(d_mod,'p_expenditure_org_id_tbl',p_expenditure_org_id_tbl);
1041 PO_LOG.proc_begin(d_mod,'p_expenditure_item_date_tbl',p_expenditure_item_date_tbl);
1042 PO_LOG.proc_begin(d_mod,'p_ship_to_org_id_tbl',p_ship_to_org_id_tbl);
1043 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
1044 END IF;
1045
1046 IF (x_results IS NULL) THEN
1047 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
1048 END IF;
1049
1050 l_results_count := x_results.result_type.COUNT;
1051
1052
1053 FOR i IN 1..p_dist_id_tbl.COUNT LOOP
1054
1055 po_core_s4.get_mtl_parameters (p_ship_to_org_id_tbl(i),
1056 NULL,
1057 x_project_reference_enabled,
1058 x_project_control_level);
1059
1060 PO_LOG.proc_begin(d_mod,'x_project_reference_enabled',x_project_reference_enabled);
1061 PO_LOG.proc_begin(d_mod,'x_project_control_level',x_project_control_level);
1062 -- Bug 7558385
1063 -- Need to check for PJM Parameters before making Task as mandatory.
1064 IF (p_project_id_tbl(i) IS NOT NULL) AND
1065 (NOT((x_project_reference_enabled = 1 ) and
1066 (x_project_control_level = 1 ) and
1067 (p_dest_type_code_tbl(i) in (c_DEST_TYPE_INVENTORY, c_DEST_TYPE_SHOP_FLOOR))
1068 ))
1069 THEN
1070 -- Task is required irrespective of destination type
1071 IF (p_task_id_tbl(i) IS NULL) THEN
1072 x_results.add_result(
1073 p_entity_type => c_entity_type_DISTRIBUTION
1074 , p_entity_id => p_dist_id_tbl(i)
1075 , p_column_name => 'TASK_ID'
1076 , p_column_val => NULL
1077 , p_message_name => PO_MESSAGE_S.PO_ALL_NOT_NULL
1078 );
1079 END IF;
1080
1081 IF (p_dest_type_code_tbl(i) = c_DEST_TYPE_EXPENSE) THEN
1082
1083 IF (PO_GMS_INTEGRATION_PVT.is_gms_enabled()) THEN
1084
1085 -- Award field is rendered if destination type is expense and grants is enabled
1086 -- Check if award is required for the project
1087 PO_GMS_INTEGRATION_PVT.is_award_required_for_project
1088 (p_project_id => p_project_id_tbl(i)
1089 , x_award_required_flag => l_award_required_flag);
1090
1091 -- If award is required, expenditure item type is required if award is filled in
1092 IF (l_award_required_flag = 'Y') THEN
1093 IF ( p_award_id_tbl(i) IS NOT NULL AND p_expenditure_type_tbl(i) IS NULL) THEN
1094 l_expenditure_type_reqd := TRUE;
1095 END IF; -- award id null check
1096 ELSIF (p_expenditure_type_tbl(i) IS NULL) THEN
1097 -- If award is not required for the project, the expenditure type is required
1098 l_expenditure_type_reqd := TRUE;
1099 END IF; --award required check
1100 ELSIF (p_expenditure_type_tbl(i) IS NULL) THEN
1101 -- If grants is not enabled, expenditure item type is required
1102 l_expenditure_type_reqd := TRUE;
1103 END IF; -- grants enabled check
1104
1105 IF (l_expenditure_type_reqd) THEN
1106 x_results.add_result(
1107 p_entity_type => c_entity_type_DISTRIBUTION
1108 , p_entity_id => p_dist_id_tbl(i)
1109 , p_column_name => 'EXPENDITURE_TYPE'
1110 , p_column_val => NULL
1111 , p_message_name => PO_MESSAGE_S.PO_ALL_NOT_NULL
1112 );
1113 END IF; --expenditure type validation check
1114
1115 IF (p_expenditure_org_id_tbl(i) IS NULL) THEN
1116 x_results.add_result(
1117 p_entity_type => c_entity_type_DISTRIBUTION
1118 , p_entity_id => p_dist_id_tbl(i)
1119 , p_column_name => 'EXPENDITURE_ORGANIZATION_ID'
1120 , p_column_val => NULL
1121 , p_message_name => PO_MESSAGE_S.PO_ALL_NOT_NULL
1122 );
1123 END IF;
1124
1125 IF (p_expenditure_item_date_tbl(i) IS NULL) THEN
1126 x_results.add_result(
1127 p_entity_type => c_entity_type_DISTRIBUTION
1128 , p_entity_id => p_dist_id_tbl(i)
1129 , p_column_name => 'EXPENDITURE_ITEM_DATE'
1130 , p_column_val => NULL
1131 , p_message_name => PO_MESSAGE_S.PO_ALL_NOT_NULL
1132 );
1133 END IF;
1134 END IF; -- destination type check
1135 END IF;
1136 END LOOP;
1137
1138 IF (l_results_count < x_results.result_type.COUNT) THEN
1139 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
1140 ELSE
1141 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1142 END IF;
1143
1144 IF po_log.d_proc THEN
1145 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
1146 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
1147 END IF;
1148
1149 EXCEPTION
1150 WHEN OTHERS THEN
1151 IF po_log.d_exc THEN
1152 po_log.exc(d_mod, 0, NULL);
1153 END IF;
1154
1155 RAISE;
1156
1157 END check_proj_related_validations;
1158
1159 END PO_VAL_DISTRIBUTIONS;