1 PACKAGE BODY po_val_distributions2 AS
2 -- $Header: PO_VAL_DISTRIBUTIONS2.plb 120.20.12020000.2 2013/02/10 14:17:09 vegajula ship $
3 c_entity_type_distribution CONSTANT VARCHAR2(30) := PO_VALIDATIONS.c_entity_type_DISTRIBUTION;
4 -- The module base for this package.
5 d_package_base CONSTANT VARCHAR2(50) := po_log.get_package_base('PO_VAL_DISTRIBUTIONS2');
6
7 -- The module base for the subprogram.
8 d_amount_ordered CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'AMOUNT_ORDERED');
9 d_quantity_ordered CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'QUANTITY_ORDERED');
10 d_destination_org_id CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'DESTINATION_ORG_ID');
11 d_deliver_to_location_id CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'DELIVER_TO_LOCATION_ID');
12 d_deliver_to_person_id CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'DELIVER_TO_PERSON_ID');
13 d_destination_type_code CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'DESTINATION_TYPE_CODE');
14 d_destination_subinv CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'DESTINATION_SUBINV');
15 d_wip_entity_id CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'WIP_ENTITY_ID');
16 d_prevent_encumberance_flag CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'PREVENT_ENCUMBERANCE_FLAG');
17 d_charge_account_id CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'CHARGE_ACCOUNT_ID');
18 d_budget_account_id CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'BUDGET_ACCOUNT_ID');
19 d_account_id CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'ACCOUNT_ID');
20 d_project_acct_context CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'PROJECT_ACCT_CONTEXT');
21 d_project_info CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'PROJECT_INFO');
22 d_tax_recovery_override_flag CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'TAX_RECOVERY_OVERRIDE_FLAG');
23 -- ACRN proj
24 d_ACRN_value_valid CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'ACRN_VALUE_VALID');
25 -- CLM Partial Funding Changes
26 d_partial_funded_flag CONSTANT VARCHAR2(100) := po_log.get_subprogram_base(d_package_base, 'PARTIAL_FUNDED_FLAG');
27 -- Indicates that the calling program is PDOI.
28 c_program_pdoi CONSTANT VARCHAR2(10) := 'PDOI';
29 -- The application name of PO.
30 c_po CONSTANT VARCHAR2(2) := 'PO';
31
32 -----------------------------------------------------------
33 -- Validation Logic:
34 -- If order_type_lookup_code is RATE or FIXED PRICE,
35 -- Quantity_ordered must be null or 0.
36 -- If order_type_code is other than RATE or FIXED PRICE,
37 -- Quantity_ordered must not be null and be greater than 0
38 -----------------------------------------------------------
39 PROCEDURE amount_ordered(
40 p_id_tbl IN po_tbl_number,
41 p_amount_ordered_tbl IN po_tbl_number,
42 p_order_type_code_tbl IN po_tbl_varchar30,
43 p_distribution_type_tbl IN po_tbl_varchar30, -- PDOI for Complex PO Project
44 x_results IN OUT NOCOPY po_validation_results_type,
45 x_result_type OUT NOCOPY VARCHAR2)
46 IS
47 d_mod CONSTANT VARCHAR2(100) := d_amount_ordered;
48 BEGIN
49 IF (x_results IS NULL) THEN
50 x_results := po_validation_results_type.new_instance();
51 END IF;
52
53 x_result_type := po_validations.c_result_type_success;
54
55 IF po_log.d_proc THEN
56 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
57 po_log.proc_begin(d_mod, 'p_amount_ordered_tbl', p_amount_ordered_tbl);
58 po_log.proc_begin(d_mod, 'p_order_type_code_tbl', p_order_type_code_tbl);
59 po_log.proc_begin(d_mod, 'p_distribution_type_tbl', p_distribution_type_tbl); -- PDOI for Complex PO Project
60 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
61 END IF;
62
63 FOR i IN 1 .. p_id_tbl.COUNT LOOP
64 IF Nvl(p_distribution_type_tbl(i),'STANDARD') <> 'PREPAYMENT' THEN -- PDOI for Complex PO Project
65 IF (p_order_type_code_tbl(i) IN('RATE', 'FIXED PRICE')) THEN
66 IF (NVL(p_amount_ordered_tbl(i), 0) <= 0) THEN
67 x_results.add_result(p_entity_type => c_entity_type_distribution,
68 p_entity_id => p_id_tbl(i),
69 p_column_name => 'AMOUNT_ORDERED',
70 p_column_val => p_amount_ordered_tbl(i),
71 p_message_name => 'PO_PDOI_SVC_MUST_AMT');
72 x_result_type := po_validations.c_result_type_failure;
73 END IF;
74 ELSE
75 IF (NVL(p_amount_ordered_tbl(i), 0) <> 0) THEN
76 x_results.add_result(p_entity_type => c_entity_type_distribution,
77 p_entity_id => p_id_tbl(i),
78 p_column_name => 'AMOUNT_ORDERED',
79 p_column_val => p_amount_ordered_tbl(i),
80 p_message_name => 'PO_SVC_NO_AMT');
81 x_result_type := po_validations.c_result_type_failure;
82 END IF;
83 END IF;
84 END IF;
85 END LOOP;
86
87 IF po_log.d_proc THEN
88 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
89 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
90 END IF;
91 EXCEPTION
92 WHEN OTHERS THEN
93 IF po_log.d_exc THEN
94 po_log.exc(d_mod, 0, NULL);
95 END IF;
96
97 RAISE;
98 END amount_ordered;
99
100 -----------------------------------------------------------
101 -- Validation Logic:
102 -- If order_type_lookup_code is RATE or FIXED PRICE,
103 -- Quantity_ordered must be null or 0.
104 -- If order_type_code is other than RATE or FIXED PRICE,
105 -- Quantity_ordered must not be null and be greater than 0
106 -----------------------------------------------------------
107 PROCEDURE quantity_ordered(
108 p_id_tbl IN po_tbl_number,
109 p_quantity_ordered_tbl IN po_tbl_number,
110 p_order_type_code_tbl IN po_tbl_varchar30,
111 p_distribution_type_tbl IN po_tbl_varchar30, -- PDOI for Complex PO Project
112 x_results IN OUT NOCOPY po_validation_results_type,
113 x_result_type OUT NOCOPY VARCHAR2)
114 IS
115 d_mod CONSTANT VARCHAR2(100) := d_quantity_ordered;
116 BEGIN
117 IF (x_results IS NULL) THEN
118 x_results := po_validation_results_type.new_instance();
119 END IF;
120
121 x_result_type := po_validations.c_result_type_success;
122
123 IF po_log.d_proc THEN
124 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
125 po_log.proc_begin(d_mod, 'p_quantity_ordered_tbl', p_quantity_ordered_tbl);
126 po_log.proc_begin(d_mod, 'p_order_type_code_tbl', p_order_type_code_tbl);
127 po_log.proc_begin(d_mod, 'p_distribution_type_tbl', p_distribution_type_tbl); -- PDOI for Complex PO Project
128 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
129 END IF;
130
131 FOR i IN 1 .. p_id_tbl.COUNT LOOP
132 IF Nvl(p_distribution_type_tbl(i),'STANDARD') <> 'PREPAYMENT' THEN -- PDOI for Complex PO Project
133 IF (p_order_type_code_tbl(i) IN('RATE', 'FIXED PRICE')) THEN
134 IF (NVL(p_quantity_ordered_tbl(i), 0) <> 0) THEN
135 x_results.add_result(p_entity_type => c_entity_type_distribution,
136 p_entity_id => p_id_tbl(i),
137 p_column_name => 'QUANTITY_ORDERED',
138 p_column_val => p_quantity_ordered_tbl(i),
139 p_message_name => 'PO_SVC_NO_QTY');
140 x_result_type := po_validations.c_result_type_failure;
141 END IF;
142 ELSE
143 IF (NVL(p_quantity_ordered_tbl(i), 0) <= 0) THEN
144 x_results.add_result(p_entity_type => c_entity_type_distribution,
145 p_entity_id => p_id_tbl(i),
146 p_column_name => 'QUANTITY_ORDERED',
147 p_column_val => p_quantity_ordered_tbl(i),
148 p_message_name => 'PO_PDOI_INVALID_QTY');
149 x_result_type := po_validations.c_result_type_failure;
150 END IF;
151 END IF;
152 END IF;
153 END LOOP;
154
155 IF po_log.d_proc THEN
156 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
157 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
158 END IF;
159 EXCEPTION
160 WHEN OTHERS THEN
161 IF po_log.d_exc THEN
162 po_log.exc(d_mod, 0, NULL);
163 END IF;
164
165 RAISE;
166 END quantity_ordered;
167
168 -----------------------------------------------------------
169 -- Validation Logic:
170 -- Should be the same as ship_to_organization_id.
171 -----------------------------------------------------------
172 PROCEDURE destination_org_id(
173 p_id_tbl IN po_tbl_number,
174 p_dest_org_id_tbl IN po_tbl_number,
175 p_ship_to_org_id_tbl IN po_tbl_number,
176 x_results IN OUT NOCOPY po_validation_results_type,
177 x_result_type OUT NOCOPY VARCHAR2)
178 IS
179 d_mod CONSTANT VARCHAR2(100) := d_destination_org_id;
180 BEGIN
181 IF (x_results IS NULL) THEN
182 x_results := po_validation_results_type.new_instance();
183 END IF;
184
185 x_result_type := po_validations.c_result_type_success;
186
187 IF po_log.d_proc THEN
188 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
189 po_log.proc_begin(d_mod, 'p_dest_org_id_tbl', p_dest_org_id_tbl);
190 po_log.proc_begin(d_mod, 'p_ship_to_org_id_tbl', p_ship_to_org_id_tbl);
191 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
192 END IF;
193
194 FOR i IN 1 .. p_id_tbl.COUNT LOOP
195 IF (NVL(p_dest_org_id_tbl(i), -11) <> NVL(p_ship_to_org_id_tbl(i), -99)) THEN
196 x_results.add_result(p_entity_type => c_entity_type_distribution,
197 p_entity_id => p_id_tbl(i),
198 p_column_name => 'DESTINATION_ORG_ID',
199 p_column_val => p_dest_org_id_tbl(i),
200 p_message_name => 'PO_PDOI_INVALID_DEST_ORG',
201 p_token1_name => 'DESTINATION_ORGANIZATION',
202 p_token1_value => p_dest_org_id_tbl(i));
203 x_result_type := po_validations.c_result_type_failure;
204 END IF;
205 END LOOP;
206
207 IF po_log.d_proc THEN
208 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
209 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
210 END IF;
211 EXCEPTION
212 WHEN OTHERS THEN
213 IF po_log.d_exc THEN
214 po_log.exc(d_mod, 0, NULL);
215 END IF;
216
217 RAISE;
218 END destination_org_id;
219
220 -----------------------------------------------------------
221 -- Validation Logic:
222 -- If deliver_to_location_id is not null,
223 -- then validate against hr_locations based on ship_to_organization_id
224 -----------------------------------------------------------
225 PROCEDURE deliver_to_location_id(
226 p_id_tbl IN po_tbl_number,
227 p_deliver_to_location_id_tbl IN po_tbl_number,
228 p_ship_to_org_id_tbl IN po_tbl_number,
229 x_result_set_id IN OUT NOCOPY NUMBER,
230 x_result_type OUT NOCOPY VARCHAR2)
231 IS
232 d_mod CONSTANT VARCHAR2(100) := d_deliver_to_location_id;
233 BEGIN
234 IF x_result_set_id IS NULL THEN
235 x_result_set_id := po_validations.next_result_set_id();
236 END IF;
237
238 x_result_type := po_validations.c_result_type_success;
239
240 IF po_log.d_proc THEN
241 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
242 po_log.proc_begin(d_mod, 'p_ship_to_org_id_tbl', p_ship_to_org_id_tbl);
243 po_log.proc_begin(d_mod, 'p_deliver_to_location_id_tbl', p_deliver_to_location_id_tbl);
244 po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
245 END IF;
246
247 FORALL i IN 1 .. p_id_tbl.COUNT
248 INSERT INTO po_validation_results_gt
249 (result_set_id,
250 result_type,
251 entity_type,
252 entity_id,
253 message_name,
254 column_name,
255 column_val,
256 token1_name,
257 token1_value)
258 SELECT x_result_set_id,
259 po_validations.c_result_type_failure,
260 c_entity_type_distribution,
261 p_id_tbl(i),
262 'PO_PDOI_INVALID_DEL_LOCATION',
263 'DELIVER_TO_LOCATION_ID',
264 p_deliver_to_location_id_tbl(i),
265 'DELIVER_TO_LOCATION_ID',
266 p_deliver_to_location_id_tbl(i)
267 FROM DUAL
268 WHERE p_deliver_to_location_id_tbl(i) IS NOT NULL
269 AND NOT EXISTS(
270 SELECT 1
271 FROM hr_locations
272 WHERE NVL(inventory_organization_id, p_ship_to_org_id_tbl(i)) = p_ship_to_org_id_tbl(i)
273 AND NVL(inactive_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
274 AND location_id = p_deliver_to_location_id_tbl(i))
275 AND NOT EXISTS(
276 SELECT 1
277 FROM hz_locations
278 WHERE NVL(address_expiration_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
279 AND location_id = p_deliver_to_location_id_tbl(i));
280
281 IF (SQL%ROWCOUNT > 0) THEN
282 x_result_type := po_validations.c_result_type_failure;
283 END IF;
284
285 IF po_log.d_proc THEN
286 po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
287 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
288 po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
289 END IF;
290 EXCEPTION
291 WHEN OTHERS THEN
292 IF po_log.d_exc THEN
293 po_log.exc(d_mod, 0, NULL);
294 END IF;
295
296 RAISE;
297 END deliver_to_location_id;
298
299 -----------------------------------------------------------
300 -- Validation Logic:
301 -- If deliver_to_person_id is not null, then validate against hr_employees_current_v
302 -----------------------------------------------------------
303 PROCEDURE deliver_to_person_id(
304 p_id_tbl IN po_tbl_number,
305 p_deliver_to_person_id_tbl IN po_tbl_number,
306 x_result_set_id IN OUT NOCOPY NUMBER,
307 x_result_type OUT NOCOPY VARCHAR2)
308 IS
309 d_mod CONSTANT VARCHAR2(100) := d_deliver_to_person_id;
310 d_position NUMBER;
311
312 l_fsp_business_group_id NUMBER := NULL;
313 l_cwk_profile_value VARCHAR2(1) := NULL;
314 l_assignment_type VARCHAR2(1) := NULL;
315
316 l_index_tbl DBMS_SQL.number_table;
317 BEGIN
318 d_position := 0;
319
320 IF x_result_set_id IS NULL THEN
321 x_result_set_id := po_validations.next_result_set_id();
322 END IF;
323
324 x_result_type := po_validations.c_result_type_success;
325
326 IF po_log.d_proc THEN
327 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
328 po_log.proc_begin(d_mod, 'p_deliver_to_person_id_tbl', p_deliver_to_person_id_tbl);
329 po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
330 END IF;
331
332 d_position := 10;
333
334 -- l_index_tbl is used to skip the validation if
335 -- deliver_to_person_id is empty
336 FOR i IN 1..p_id_tbl.COUNT LOOP
337 IF (p_deliver_to_person_id_tbl(i) IS NOT NULL) THEN
338 l_index_tbl(i) := i;
339 END IF;
340 END LOOP;
341
342 SELECT FSP.BUSINESS_GROUP_ID
343 INTO l_fsp_business_group_id
344 FROM FINANCIALS_SYSTEM_PARAMETERS FSP;
345
346 l_cwk_profile_value := nvl(fnd_profile.value('HR_TREAT_CWK_AS_EMP'), 'N');
347 IF (l_cwk_profile_value = 'Y') THEN
348 l_assignment_type := 'C';
349 ELSE
350 l_assignment_type := 'E';
351 END IF;
352
353 IF (PO_LOG.d_stmt) THEN
354 PO_LOG.stmt(d_mod, d_position, 'l_fsp_business_group_id', l_fsp_business_group_id);
355 PO_LOG.stmt(d_mod, d_position, 'l_assignment_type', l_assignment_type);
356 END IF;
357
358 d_position := 20;
359
360 -- bug 5454379: add hint to use index PER_PEOPLE_F_PK
361 -- which is more selective
362 FORALL i IN INDICES OF l_index_tbl
363 INSERT INTO po_validation_results_gt
364 (result_set_id,
365 result_type,
366 entity_type,
367 entity_id,
368 message_name,
369 column_name,
370 column_val,
371 token1_name,
372 token1_value)
373 SELECT x_result_set_id,
374 po_validations.c_result_type_failure,
375 c_entity_type_distribution,
376 p_id_tbl(i),
377 'PO_PDOI_INVALID_DEL_PERSON',
378 'DELIVER_TO_PERSON_ID',
379 p_deliver_to_person_id_tbl(i),
380 'DELIVER_TO_PERSON',
381 p_deliver_to_person_id_tbl(i)
382 FROM DUAL
383 WHERE NOT EXISTS(
384 SELECT
385 /*+ INDEX(P PER_PEOPLE_F_PK) */
386 1
387 FROM PER_PEOPLE_F P,
388 PER_ASSIGNMENTS_F A
389 WHERE P.person_id = p_deliver_to_person_id_tbl(i)
390 AND A.person_id = P.person_id
391 AND A.primary_flag = 'Y'
392 AND TRUNC(SYSDATE) BETWEEN P.effective_start_date AND P.effective_end_date
393 AND TRUNC(SYSDATE) BETWEEN A.effective_start_date AND A.effective_end_date
394 AND (NVL(current_employee_flag,'N') = 'Y'
395 OR NVL(current_npw_flag,'N') = 'Y')
396 AND Decode(hr_general.get_xbg_profile,'Y',p.business_group_id,
397 l_fsp_business_group_id) = p.business_group_id /* 9034751 */ AND A.assignment_type IN ('E',l_assignment_type));
398
399 IF (SQL%ROWCOUNT > 0) THEN
400 x_result_type := po_validations.c_result_type_failure;
401 END IF;
402
403 IF po_log.d_proc THEN
404 po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
405 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
406 po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
407 END IF;
408 EXCEPTION
409 WHEN OTHERS THEN
410 IF po_log.d_exc THEN
411 po_log.exc(d_mod, 0, NULL);
412 END IF;
413
414 RAISE;
415 END deliver_to_person_id;
416
417 -----------------------------------------------------------
418 -- Validation Logic:
419 -- If not null, validate destination_type_code based on item_status,
420 -- accrue_on_receipt_flag, transaction_flow_header_id;
421 --
422 -- Validation Business Rules
423 -- item status
424 -- 'O' = outside processing item
425 -- - destination type must be SHOP FLOOR
426 -- 'E' = item stockable in the org
427 -- - destination type cannot be SHOP FLOOR
428 -- 'D' = item defined but not stockable in org
429 -- - destination type must be EXPENSE
430 -- null = item not defined in org
431 --
432 -- accrue on receipt
433 -- 'N' - destination type must be expense
434 -- 'Y' - if expense_accrual = PERIOD END
435 -- then destination type code cannot be EXPENSE
436 -- Cannot be INVENTORY if item_id is null.
437 -- If SHIKYU item, then dest type code must be INVENTORY.
438 -----------------------------------------------------------
439 PROCEDURE destination_type_code(
440 p_id_tbl IN po_tbl_number,
441 p_dest_type_code_tbl IN po_tbl_varchar30,
442 p_ship_to_org_id_tbl IN po_tbl_number,
443 p_item_id_tbl IN po_tbl_number,
444 p_txn_flow_header_id_tbl IN po_tbl_number,
445 p_accrue_on_receipt_flag_tbl IN po_tbl_varchar1,
446 p_value_basis_tbl IN po_tbl_varchar30,
447 p_purchase_basis_tbl IN po_tbl_varchar30, --bug 7644072
448 p_expense_accrual_code IN po_system_parameters.expense_accrual_code%TYPE,
449 p_loc_outsourced_assembly_tbl IN po_tbl_number,
450 x_result_set_id IN OUT NOCOPY NUMBER,
451 x_results IN OUT NOCOPY po_validation_results_type,
452 x_result_type OUT NOCOPY VARCHAR2)
453 IS
454
455 d_mod CONSTANT VARCHAR2(100) := d_destination_type_code;
456 d_position NUMBER;
457
458 -- key of temp table used to identify the derived result
459 l_key po_session_gt.key%TYPE;
460 l_num_list_tbl DBMS_SQL.NUMBER_TABLE;
461
462 -- tables to store the derived result
463 l_index_tbl PO_TBL_NUMBER;
464 l_result_tbl PO_TBL_VARCHAR1;
465 l_item_status_tbl PO_TBL_VARCHAR1 := PO_TBL_VARCHAR1();
466
467 BEGIN
468
469 d_position := 0;
470
471 IF x_result_set_id IS NULL THEN
472 x_result_set_id := po_validations.next_result_set_id();
473 END IF;
474
475 IF (x_results IS NULL) THEN
476 x_results := po_validation_results_type.new_instance();
477 END IF;
478
479 x_result_type := po_validations.c_result_type_success;
480
481 d_position := 10;
482
483 IF po_log.d_proc THEN
484 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
485 po_log.proc_begin(d_mod, 'p_dest_type_code_tbl', p_dest_type_code_tbl);
486 po_log.proc_begin(d_mod, 'p_ship_to_org_id_tbl', p_ship_to_org_id_tbl);
487 po_log.proc_begin(d_mod, 'p_item_id_tbl', p_item_id_tbl);
488 po_log.proc_begin(d_mod, 'p_txn_flow_header_id_tbl', p_txn_flow_header_id_tbl);
489 po_log.proc_begin(d_mod, 'p_accrue_on_receipt_flag_tbl', p_accrue_on_receipt_flag_tbl);
490 po_log.proc_begin(d_mod, 'p_expense_accrual_code', p_expense_accrual_code);
491 po_log.proc_begin(d_mod, 'p_loc_outsourced_assembly_tbl', p_loc_outsourced_assembly_tbl);
492 po_log.proc_begin(d_mod, 'p_value_basis_tbl', p_value_basis_tbl);
493 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
494 po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
495 END IF;
496
497 d_position := 20;
498
499 FOR i IN 1 .. p_id_tbl.COUNT LOOP
500 -- If dest type code is INVENTORY, item_id must be NULL.
501 IF (p_dest_type_code_tbl(i) = 'INVENTORY' AND p_item_id_tbl(i) IS NULL) THEN
502 x_results.add_result(p_entity_type => c_entity_type_distribution,
503 p_entity_id => p_id_tbl(i),
504 p_column_name => 'DESTINATION_TYPE_CODE',
505 p_column_val => p_dest_type_code_tbl(i),
506 p_message_name => 'PO_PDOI_INVALID_DEST_TYPE',
507 p_token1_name => 'DESTINATION_TYPE',
508 p_token1_value => p_dest_type_code_tbl(i));
509 x_result_type := po_validations.c_result_type_failure;
510 END IF;
511 -- If SHIKYU item, destination type must be INVENTORY.
512 IF (p_dest_type_code_tbl(i) <> 'INVENTORY' AND p_loc_outsourced_assembly_tbl(i) = 1) THEN
513 x_results.add_result(p_entity_type => c_entity_type_distribution,
514 p_entity_id => p_id_tbl(i),
515 p_column_name => 'DESTINATION_TYPE_CODE',
516 p_column_val => p_dest_type_code_tbl(i),
517 p_message_name => 'PO_PDOI_SHIKYU_DEST_TYPE');
518 x_result_type := po_validations.c_result_type_failure;
519 END IF;
520 END LOOP;
521
522 d_position := 30;
523
524 -- assign a new key used in temporary table
525 l_key := PO_CORE_S.get_session_gt_nextval;
526
527 -- initialize table containing the row number
528 PO_PDOI_UTL.generate_ordered_num_list
529 (
530 p_size => p_id_tbl.COUNT,
531 x_num_list => l_num_list_tbl
532 );
533
534 d_position := 40;
535
536 FORALL i IN 1..l_num_list_tbl.COUNT
537 INSERT INTO po_session_gt(key, num1, char1)
538 SELECT l_key,
539 l_num_list_tbl(i),
540 decode(msi.outside_operation_flag,'Y','O', decode(msi.stock_enabled_flag,'Y','E','D'))
541 FROM mtl_system_items msi
542 WHERE p_dest_type_code_tbl(i) IS NOT NULL
543 AND msi.organization_id = p_ship_to_org_id_tbl(i)
544 AND msi.inventory_item_id = p_item_id_tbl(i);
545
546 d_position := 50;
547
548 DELETE FROM po_session_gt
549 WHERE key = l_key
550 RETURNING num1, char1
551 BULK COLLECT INTO l_index_tbl, l_result_tbl;
552
553 d_position := 60;
554
555 l_item_status_tbl.extend(p_id_tbl.COUNT);
556
557 FOR i IN 1..l_index_tbl.COUNT
558 LOOP
559 IF (PO_LOG.d_stmt) THEN
560 PO_LOG.stmt(d_mod, d_position, 'index', l_index_tbl(i));
561 PO_LOG.stmt(d_mod, d_position, 'new item_status', l_result_tbl(i));
562 END IF;
563
564 l_item_status_tbl(l_index_tbl(i)) := l_result_tbl(i);
565 END LOOP;
566
567 -- For entries without item_id, defautl item_status to 'D'
568 FOR i IN 1..p_id_tbl.COUNT LOOP
569 IF (p_item_id_tbl(i) IS NULL) THEN
570 l_item_status_tbl(i) := 'D';
571 END IF;
572 END LOOP;
573
574 IF po_log.d_proc THEN
575 po_log.proc_begin(d_mod, 'l_item_status_tbl', l_item_status_tbl);
576 END IF;
577
578 d_position := 70;
579
580 FORALL i IN 1 .. p_id_tbl.COUNT
581 INSERT INTO po_validation_results_gt
582 (result_set_id,
583 result_type,
584 entity_type,
585 entity_id,
586 message_name,
587 column_name,
588 column_val,
589 token1_name,
590 token1_value)
591 SELECT x_result_set_id,
592 po_validations.c_result_type_failure,
593 c_entity_type_distribution,
594 p_id_tbl(i),
595 'PO_PDOI_INVALID_DEST_TYPE',
596 'DESTINATION_TYPE_CODE',
597 p_dest_type_code_tbl(i),
598 'DESTINATION_TYPE',
599 p_dest_type_code_tbl(i)
600 FROM DUAL
601 WHERE p_dest_type_code_tbl(i) IS NOT NULL
602 AND NOT EXISTS(SELECT 1
603 FROM po_lookup_codes
604 WHERE lookup_type = 'DESTINATION TYPE'
605 AND ((nvl(l_item_status_tbl(i),'D') = 'D'
606 /* AND lookup_code = 'EXPENSE') commented and added below 7644072*/
607 AND lookup_code <> 'INVENTORY') -- bug 7644072
608 OR (nvl(l_item_status_tbl(i),'D') = 'E'
609 AND lookup_code <> 'SHOP FLOOR')
610 OR (nvl(l_item_status_tbl(i),'D') = 'O'
611 AND lookup_code = 'SHOP FLOOR')
612 /* commenting the below and adding new conditions bug 7644072
613 OR (p_value_basis_tbl(i) = 'FIXED PRICE' -- EAM Integration Enhancement R12
614 AND lookup_code = 'SHOP FLOOR')*/
615 OR (p_value_basis_tbl(i) = 'FIXED PRICE' -- EAM Integration Enhancement R12
616 AND p_purchase_basis_tbl(i) = 'TEMP LABOR' --bug7644072
617 AND lookup_code = 'EXPENSE') --bug7644072
618 )
619 AND ((nvl(p_accrue_on_receipt_flag_tbl(i),'N') = 'N' AND lookup_code = 'EXPENSE')
620 OR p_txn_flow_header_id_tbl(i) is NOT NULL
621 OR (nvl(p_accrue_on_receipt_flag_tbl(i),'N') = 'Y'
622 AND ((p_expense_accrual_code = 'PERIOD END' AND lookup_code <> 'EXPENSE')
623 OR p_expense_accrual_code <> 'PERIOD END')
624 )
625 )
626 AND lookup_code= p_dest_type_code_tbl(i));
627
628 IF (SQL%ROWCOUNT > 0) THEN
629 x_result_type := po_validations.c_result_type_failure;
630 END IF;
631
632 d_position := 80;
633
634 IF po_log.d_proc THEN
635 po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
636 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
637 po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
638 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
639 END IF;
640 EXCEPTION
641 WHEN OTHERS THEN
642 IF po_log.d_exc THEN
643 po_log.exc(d_mod, d_position, NULL);
644 END IF;
645
646 RAISE;
647 END destination_type_code;
648
649 -----------------------------------------------------------
650 -- Validation Logic:
651 -- If destination_type_code is INVENTORY and destination_subinventiry is not null,
652 -- validate the destination_subinventory against mtl_secondary_inventories based on
653 -- ship_to_organization_id and item_id.
654 -- If destination_type_code is SHOP FLOOR and EXPENSE, the value has to be NULL.
655 -- Need to validate that SHIKYU item can only have asset subinventory (inventory_asset = 1).
656 -----------------------------------------------------------
657 PROCEDURE destination_subinv(
658 p_id_tbl IN po_tbl_number,
659 p_destination_subinv_tbl IN po_tbl_varchar30,
660 p_dest_type_code_tbl IN po_tbl_varchar30,
661 p_item_id_tbl IN po_tbl_number,
662 p_ship_to_org_id_tbl IN po_tbl_number,
663 p_loc_outsourced_assembly_tbl IN po_tbl_number,
664 x_result_set_id IN OUT NOCOPY NUMBER,
665 x_results IN OUT NOCOPY po_validation_results_type,
666 x_result_type OUT NOCOPY VARCHAR2)
667 IS
668 d_mod CONSTANT VARCHAR2(100) := d_destination_subinv;
669 BEGIN
670 IF x_result_set_id IS NULL THEN
671 x_result_set_id := po_validations.next_result_set_id();
672 END IF;
673
674 IF (x_results IS NULL) THEN
675 x_results := po_validation_results_type.new_instance();
676 END IF;
677
678 x_result_type := po_validations.c_result_type_success;
679
680 IF po_log.d_proc THEN
681 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
682 po_log.proc_begin(d_mod, 'p_destination_subinv_tbl', p_destination_subinv_tbl);
683 po_log.proc_begin(d_mod, 'p_dest_type_code_tbl', p_dest_type_code_tbl);
684 po_log.proc_begin(d_mod, 'p_item_id_tbl', p_item_id_tbl);
685 po_log.proc_begin(d_mod, 'p_ship_to_org_id_tbl', p_ship_to_org_id_tbl);
686 po_log.proc_begin(d_mod, 'p_loc_outsourced_assembly', p_loc_outsourced_assembly_tbl);
687 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
688 po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
689 END IF;
690
691 FOR i IN 1 .. p_id_tbl.COUNT LOOP
692 IF (p_dest_type_code_tbl(i) IN('SHOP FLOOR', 'EXPENSE') AND p_destination_subinv_tbl(i) IS NOT NULL) THEN
693 x_results.add_result(p_entity_type => c_entity_type_distribution,
694 p_entity_id => p_id_tbl(i),
695 p_column_name => 'DESTINATION_SUBINVENTORY',
696 p_column_val => p_destination_subinv_tbl(i),
697 p_message_name => 'PO_PDOI_INVALID_DEST_SUBINV');
698 x_result_type := po_validations.c_result_type_failure;
699 END IF;
700 END LOOP;
701
702 FORALL i IN 1 .. p_id_tbl.COUNT
703 INSERT INTO po_validation_results_gt
704 (result_set_id,
705 result_type,
706 entity_type,
707 entity_id,
708 message_name,
709 column_name,
710 column_val,
711 token1_name,
712 token1_value)
713 SELECT x_result_set_id,
714 po_validations.c_result_type_failure,
715 c_entity_type_distribution,
716 p_id_tbl(i),
717 'PO_PDOI_INVALID_DEST_SUBINV',
718 'DESTINATION_SUBINVENTORY',
719 p_destination_subinv_tbl(i),
720 'DESTINATION_SUBINVENTORY',
721 p_destination_subinv_tbl(i)
722 FROM DUAL
723 WHERE p_dest_type_code_tbl(i) = 'INVENTORY'
724 AND p_destination_subinv_tbl(i) IS NOT NULL
725 AND NOT EXISTS(
726 SELECT 1
727 FROM mtl_secondary_inventories msub
728 WHERE msub.organization_id = NVL(p_ship_to_org_id_tbl(i), msub.organization_id)
729 AND NVL(msub.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
730 AND ( p_item_id_tbl(i) IS NULL
731 OR ( p_item_id_tbl(i) IS NOT NULL
732 AND EXISTS(
733 SELECT NULL
734 FROM mtl_system_items msi
735 WHERE msi.organization_id = NVL(p_ship_to_org_id_tbl(i), msi.organization_id)
736 AND msi.inventory_item_id = p_item_id_tbl(i)
737 AND ( msi.restrict_subinventories_code = 2
738 OR ( msi.restrict_subinventories_code = 1
739 AND EXISTS(
740 SELECT NULL
741 FROM mtl_item_sub_inventories mis
742 WHERE mis.organization_id =
743 NVL(p_ship_to_org_id_tbl(i), mis.organization_id)
744 AND mis.inventory_item_id = msi.inventory_item_id
745 AND mis.secondary_inventory = msub.secondary_inventory_name))))))
746 AND msub.secondary_inventory_name = p_destination_subinv_tbl(i));
747
748 IF (SQL%ROWCOUNT > 0) THEN
749 x_result_type := po_validations.c_result_type_failure;
750 END IF;
751
752 -- Need to validate that SHIKYU item can only have asset subinventory (inventory_asset = 1).
753 FORALL i IN 1 .. p_id_tbl.COUNT
754 INSERT INTO po_validation_results_gt
755 (result_set_id,
756 result_type,
757 entity_type,
758 entity_id,
759 message_name,
760 column_name,
761 column_val,
762 token1_name,
763 token1_value)
764 SELECT x_result_set_id,
765 po_validations.c_result_type_failure,
766 c_entity_type_distribution,
767 p_id_tbl(i),
768 'PO_PDOI_SHIKYU_DEST_SUBINV',
769 'DESTINATION_SUBINVENTORY',
770 p_destination_subinv_tbl(i),
771 'DESTINATION_SUBINVENTORY',
772 p_destination_subinv_tbl(i)
773 FROM DUAL
774 WHERE p_destination_subinv_tbl(i) IS NOT NULL
775 AND p_dest_type_code_tbl(i) = 'INVENTORY'
776 AND p_loc_outsourced_assembly_tbl(i) = 1 /* SHIKYU item */
777 AND EXISTS(
778 SELECT 1
779 FROM mtl_secondary_inventories msub
780 WHERE msub.organization_id = NVL(p_ship_to_org_id_tbl(i), msub.organization_id)
781 AND NVL(msub.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
782 AND msub.asset_inventory = 2 /* Not asset subinventory */
783 AND msub.secondary_inventory_name = p_destination_subinv_tbl(i));
784
785 IF (SQL%ROWCOUNT > 0) THEN
786 x_result_type := po_validations.c_result_type_failure;
787 END IF;
788
789 IF po_log.d_proc THEN
790 po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
791 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
792 po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
793 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
794 END IF;
795 EXCEPTION
796 WHEN OTHERS THEN
797 IF po_log.d_exc THEN
798 po_log.exc(d_mod, 0, NULL);
799 END IF;
800
801 RAISE;
802 END destination_subinv;
803
804 -----------------------------------------------------------
805 -- Validation Logic:
806 -- If destination_type_code is SHOP FLOOR,
807 -- If wip_entity_id is null,
808 -- ERR: 'PO_PDOI_COLUMN_NOT_NULL'
809 -- Else
810 -- Validate against wip_repetitive_schedules/
811 -- wip_discrete_jobs depending on the value of
812 -- wip_repetitive_schedule_id. (If the
813 -- destination_type_code = 'SHOP FLOOR', then if
814 -- WIP_REPETITIVE_SCHEDULE_ID is not null then the
815 -- record must be a repetitive schedule. If
816 -- WIP_REPETITIVE_SCHEDULE_ID is NULL, then it
817 -- must be a discrete job)
818 -----------------------------------------------------------
819 PROCEDURE wip_entity_id(
820 p_id_tbl IN po_tbl_number,
821 p_wip_entity_id_tbl IN po_tbl_number,
822 p_wip_rep_schedule_id_tbl IN po_tbl_number,
823 p_dest_type_code_tbl IN po_tbl_varchar30,
824 p_destination_org_id_tbl IN po_tbl_number,
825 x_result_set_id IN OUT NOCOPY NUMBER,
826 x_results IN OUT NOCOPY po_validation_results_type,
827 x_result_type OUT NOCOPY VARCHAR2)
828 IS
829 d_mod CONSTANT VARCHAR2(100) := d_wip_entity_id;
830 BEGIN
831 IF x_result_set_id IS NULL THEN
832 x_result_set_id := po_validations.next_result_set_id();
833 END IF;
834
835 IF (x_results IS NULL) THEN
836 x_results := po_validation_results_type.new_instance();
837 END IF;
838
839 x_result_type := po_validations.c_result_type_success;
840
841 IF po_log.d_proc THEN
842 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
843 po_log.proc_begin(d_mod, 'p_wip_entity_id_tbl', p_wip_entity_id_tbl);
844 po_log.proc_begin(d_mod, 'p_wip_rep_schedule_id_tbl', p_wip_rep_schedule_id_tbl);
845 po_log.proc_begin(d_mod, 'p_dest_type_code_tbl', p_dest_type_code_tbl);
846 po_log.proc_begin(d_mod, 'p_destination_org_id_tbl', p_destination_org_id_tbl);
847 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
848 po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
849 END IF;
850
851 FOR i IN 1 .. p_id_tbl.COUNT LOOP
852 IF (p_dest_type_code_tbl(i) = 'SHOP FLOOR' AND p_wip_entity_id_tbl(i) IS NULL) THEN
853 x_results.add_result(p_entity_type => c_entity_type_distribution,
854 p_entity_id => p_id_tbl(i),
855 p_column_name => 'WIP_ENTITY_ID',
856 p_column_val => p_wip_entity_id_tbl(i),
857 p_message_name => 'PO_PDOI_COLUMN_NOT_NULL',
858 p_token1_name => 'COLUMN_NAME',
859 p_token1_value => 'WIP_ENTITY_ID');
860 x_result_type := po_validations.c_result_type_failure;
861 END IF;
862 END LOOP;
863
864 FORALL i IN 1 .. p_id_tbl.COUNT
865 INSERT INTO po_validation_results_gt
866 (result_set_id,
867 result_type,
868 entity_type,
869 entity_id,
870 message_name,
871 column_name,
872 column_val,
873 token1_name,
874 token1_value)
875 SELECT x_result_set_id,
876 po_validations.c_result_type_failure,
877 c_entity_type_distribution,
878 p_id_tbl(i),
879 'PO_PDOI_INVALID_WIP_SCHED',
880 'WIP_REPETITIVE_SCHEDULE_ID',
881 p_wip_rep_schedule_id_tbl(i),
882 'WIP_REPETITIVE_SCHEDULE_ID',
883 p_wip_rep_schedule_id_tbl(i)
884 FROM DUAL
885 WHERE p_dest_type_code_tbl(i) = 'SHOP FLOOR'
886 AND p_wip_entity_id_tbl(i) IS NOT NULL
887 AND p_wip_rep_schedule_id_tbl(i) IS NOT NULL
888 AND NOT EXISTS(
889 SELECT 1
890 FROM wip_repetitive_schedules wrs
891 WHERE wrs.organization_id = p_destination_org_id_tbl(i)
892 AND wrs.wip_entity_id = p_wip_entity_id_tbl(i)
893 AND wrs.repetitive_schedule_id = p_wip_rep_schedule_id_tbl(i)
894 AND wrs.status_type IN(3, 4, 6));
895
896 IF (SQL%ROWCOUNT > 0) THEN
897 x_result_type := po_validations.c_result_type_failure;
898 END IF;
899
900 FORALL i IN 1 .. p_id_tbl.COUNT
901 INSERT INTO po_validation_results_gt
902 (result_set_id,
903 result_type,
904 entity_type,
905 entity_id,
906 message_name,
907 column_name,
908 column_val,
909 token1_name,
910 token1_value)
911 SELECT x_result_set_id,
912 po_validations.c_result_type_failure,
913 c_entity_type_distribution,
914 p_id_tbl(i),
915 'PO_PDOI_INVALID_WIP_ENTITY',
916 'WIP_ENTITY_ID',
917 p_wip_entity_id_tbl(i),
918 'WIP_ENTITY_ID',
919 p_wip_entity_id_tbl(i)
920 FROM DUAL
921 WHERE p_dest_type_code_tbl(i) = 'SHOP FLOOR'
922 AND p_wip_entity_id_tbl(i) IS NOT NULL
923 AND p_wip_rep_schedule_id_tbl(i) IS NULL
924 AND NOT EXISTS(
925 SELECT 1
926 FROM wip_discrete_jobs wdj
927 WHERE wdj.organization_id = p_destination_org_id_tbl(i)
928 AND wdj.wip_entity_id = p_wip_entity_id_tbl(i)
929 AND wdj.status_type IN(3, 4, 6));
930
931 IF (SQL%ROWCOUNT > 0) THEN
932 x_result_type := po_validations.c_result_type_failure;
933 END IF;
934
935 IF po_log.d_proc THEN
936 po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
937 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
938 po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
939 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
940 END IF;
941 EXCEPTION
942 WHEN OTHERS THEN
943 IF po_log.d_exc THEN
944 po_log.exc(d_mod, 0, NULL);
945 END IF;
946
947 RAISE;
948 END wip_entity_id;
949
950 -----------------------------------------------------------
951 -- Validation Logic:
952 -- The value needs to be 'Y' if the destination_type_code is 'SHOP FLOOR'.
953 -- For other destination type, the value has to be 'N'.
954 -----------------------------------------------------------
955 PROCEDURE prevent_encumbrance_flag(
956 p_id_tbl IN po_tbl_number,
957 p_prevent_encum_flag_tbl IN po_tbl_varchar1,
958 p_dest_type_code_tbl IN po_tbl_varchar30,
959 p_distribution_type_tbl IN po_tbl_varchar30, -- PDOI for Complex PO Project
960 p_wip_entity_id_tbl IN po_tbl_number,
961 x_results IN OUT NOCOPY po_validation_results_type,
962 x_result_type OUT NOCOPY VARCHAR2)
963 IS
964 d_mod CONSTANT VARCHAR2(100) := d_prevent_encumberance_flag;
965 l_wip_entity_type NUMBER;
966 BEGIN
967 IF (x_results IS NULL) THEN
968 x_results := po_validation_results_type.new_instance();
969 END IF;
970
971 x_result_type := po_validations.c_result_type_success;
972
973 IF po_log.d_proc THEN
974 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
975 po_log.proc_begin(d_mod, 'p_prevent_encum_flag_tbl', p_prevent_encum_flag_tbl);
976 po_log.proc_begin(d_mod, 'p_dest_type_code_tbl', p_dest_type_code_tbl);
977 po_log.proc_begin(d_mod, 'p_distribution_type_tbl', p_distribution_type_tbl); -- PDOI for Complex PO Project
978 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
979 END IF;
980
981 /* For Encumbrance Project - To enable Encumbrance for Destination type - Shop Floor and WIP entity type - EAM
982 Retriving entity_type and Setting the prevent_encumbrance_flag to 'Y' if destination type = shop floor
983 and wip_entity_type <> 6 ( '6' is for EAM jobs) */
984
985 FOR i IN 1 .. p_id_tbl.COUNT LOOP
986
987 IF(p_dest_type_code_tbl(i) = 'SHOP FLOOR') then
988 BEGIN
989 select entity_type
990 into l_wip_entity_type
991 from wip_entities
992 where wip_entity_id = p_wip_entity_id_tbl(i) ;
993 exception
994 when others then
995 null;
996 END;
997 END IF;
998
999 IF ( (p_dest_type_code_tbl(i) = 'SHOP FLOOR' AND p_prevent_encum_flag_tbl(i) = 'N' AND l_wip_entity_type <> 6 ) /* Condition added for Encumbrance Project */
1000 OR (p_dest_type_code_tbl(i) <> 'SHOP FLOOR' AND p_prevent_encum_flag_tbl(i) = 'Y'
1001 AND p_distribution_type_tbl(i) <> 'PREPAYMENT')) THEN -- PDOI for Complex PO Project
1002 x_results.add_result(p_entity_type => c_entity_type_distribution,
1003 p_entity_id => p_id_tbl(i),
1004 p_column_name => 'PREVENT_ENCUMBERANCE_FLAG',
1005 p_column_val => p_prevent_encum_flag_tbl(i),
1006 p_message_name => 'PO_PDOI_INV_PREV_ENCUM_FLAG');
1007 x_result_type := po_validations.c_result_type_failure;
1008 END IF;
1009 END LOOP;
1010
1011 IF po_log.d_proc THEN
1012 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
1013 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
1014 END IF;
1015 EXCEPTION
1016 WHEN OTHERS THEN
1017 IF po_log.d_exc THEN
1018 po_log.exc(d_mod, 0, NULL);
1019 END IF;
1020
1021 RAISE;
1022 END prevent_encumbrance_flag;
1023
1024 -----------------------------------------------------------
1025 -- Validation Logic:
1026 -- The charge_account_id can not be null.
1027 -- If it is not null, it must be a valid id in gl_code_combinations.
1028 -----------------------------------------------------------
1029 PROCEDURE charge_account_id(
1030 p_id_tbl IN po_tbl_number,
1031 p_charge_account_id_tbl IN po_tbl_number,
1032 p_gl_date_tbl IN po_tbl_date,
1033 p_chart_of_account_id IN NUMBER,
1034 x_result_set_id IN OUT NOCOPY NUMBER,
1035 x_result_type OUT NOCOPY VARCHAR2)
1036 IS
1037 d_mod CONSTANT VARCHAR2(100) := d_charge_account_id;
1038 BEGIN
1039 IF x_result_set_id IS NULL THEN
1040 x_result_set_id := po_validations.next_result_set_id();
1041 END IF;
1042
1043 x_result_type := po_validations.c_result_type_success;
1044
1045 IF po_log.d_proc THEN
1046 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
1047 po_log.proc_begin(d_mod, 'p_charge_account_id_tbl', p_charge_account_id_tbl);
1048 po_log.proc_begin(d_mod, 'p_gl_date_tbl', p_gl_date_tbl);
1049 po_log.proc_begin(d_mod, 'p_chart_of_account_id', p_chart_of_account_id);
1050 po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
1051 END IF;
1052
1053 FORALL i IN 1 .. p_id_tbl.COUNT
1054 INSERT INTO po_validation_results_gt
1055 (result_set_id,
1056 result_type,
1057 entity_type,
1058 entity_id,
1059 message_name,
1060 column_name,
1061 column_val,
1062 token1_name,
1063 token1_value)
1064 SELECT x_result_set_id,
1065 po_validations.c_result_type_failure,
1066 c_entity_type_distribution,
1067 p_id_tbl(i),
1068 decode(p_charge_account_id_tbl(i), NULL, 'PO_PDOI_NO_CHG_ACCT', 'PO_PDOI_INVALID_CHG_ACCOUNT'),
1069 'CHARGE_ACCOUNT_ID',
1070 p_charge_account_id_tbl(i),
1071 decode(p_charge_account_id_tbl(i), NULL, NULL, 'CHARGE_ACCOUNT'),
1072 decode(p_charge_account_id_tbl(i), NULL, NULL, p_charge_account_id_tbl(i))
1073 FROM DUAL
1074 WHERE (p_charge_account_id_tbl(i) IS NULL OR
1075 (p_charge_account_id_tbl(i) IS NOT NULL AND
1076 NOT EXISTS(
1077 SELECT NULL
1078 FROM gl_code_combinations gcc
1079 WHERE gcc.code_combination_id = p_charge_account_id_tbl(i)
1080 AND gcc.enabled_flag = 'Y'
1081 AND TRUNC(NVL(p_gl_date_tbl(i), SYSDATE)) BETWEEN TRUNC(NVL(start_date_active,
1082 NVL(p_gl_date_tbl(i), SYSDATE)))
1083 AND TRUNC(NVL(end_date_active,
1084 NVL(p_gl_date_tbl(i), SYSDATE)))
1085 AND gcc.detail_posting_allowed_flag = 'Y'
1086 AND gcc.chart_of_accounts_id = p_chart_of_account_id
1087 AND gcc.summary_flag = 'N')));
1088
1089 IF (SQL%ROWCOUNT > 0) THEN
1090 x_result_type := po_validations.c_result_type_failure;
1091 END IF;
1092
1093 IF po_log.d_proc THEN
1094 po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
1095 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
1096 po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
1097 END IF;
1098 EXCEPTION
1099 WHEN OTHERS THEN
1100 IF po_log.d_exc THEN
1101 po_log.exc(d_mod, 0, NULL);
1102 END IF;
1103
1104 RAISE;
1105 END charge_account_id;
1106
1107 -----------------------------------------------------------
1108 -- Validation Logic:
1109 -- 1. If po_encumbrance_flag is Y, and destination_type_code is not 'SHOP FLOOR',
1110 -- the budget_account_id can not be null.
1111 -- 2. If the account_id is not null, it must be a valid id in gl_code_combinations.
1112 -----------------------------------------------------------
1113 PROCEDURE budget_account_id(
1114 p_id_tbl IN po_tbl_number,
1115 p_budget_account_id_tbl IN po_tbl_number,
1116 p_gl_date_tbl IN po_tbl_date,
1117 p_dest_type_code_tbl IN po_tbl_varchar30,
1118 p_distribution_type_tbl IN po_tbl_varchar30, -- PDOI for Complex PO Project
1119 p_chart_of_account_id IN NUMBER,
1120 p_po_encumberance_flag IN VARCHAR2,
1121 p_wip_entity_id_tbl IN po_tbl_number,
1122 x_result_set_id IN OUT NOCOPY NUMBER,
1123 x_result_type OUT NOCOPY VARCHAR2)
1124 IS
1125 d_mod CONSTANT VARCHAR2(100) := d_budget_account_id;
1126 l_wip_entity_type number := NULL ;
1127 BEGIN
1128 IF x_result_set_id IS NULL THEN
1129 x_result_set_id := po_validations.next_result_set_id();
1130 END IF;
1131
1132 x_result_type := po_validations.c_result_type_success;
1133
1134 IF po_log.d_proc THEN
1135 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
1136 po_log.proc_begin(d_mod, 'p_budget_account_id_tbl', p_budget_account_id_tbl);
1137 po_log.proc_begin(d_mod, 'p_gl_date_tbl', p_gl_date_tbl);
1138 po_log.proc_begin(d_mod, 'p_dest_type_code_tbl', p_dest_type_code_tbl);
1139 po_log.proc_begin(d_mod, 'p_distribution_type_tbl', p_distribution_type_tbl); -- PDOI for Complex PO Project
1140 po_log.proc_begin(d_mod, 'p_chart_of_account_id', p_chart_of_account_id);
1141 po_log.proc_begin(d_mod, 'p_po_encumberance_flag', p_po_encumberance_flag);
1142 po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
1143 END IF;
1144
1145 -- bug 4899825: add checking on destination_type_code when
1146 -- budget_account_id is empty; If destination_
1147 -- type_code is 'SHOP FLOOR', budget_account_id
1148 -- could be empty even when encumbrance is enabled
1149 FORALL i IN 1 .. p_id_tbl.COUNT
1150 INSERT INTO po_validation_results_gt
1151 (result_set_id,
1152 result_type,
1153 entity_type,
1154 entity_id,
1155 message_name,
1156 column_name,
1157 column_val,
1158 token1_name,
1159 token1_value)
1160 SELECT x_result_set_id,
1161 po_validations.c_result_type_failure,
1162 c_entity_type_distribution,
1163 p_id_tbl(i),
1164 'PO_PDOI_INVALID_BUDGET_ACCT',
1165 'BUDGET_ACCOUNT_ID',
1166 p_budget_account_id_tbl(i),
1167 'BUDGET_ACCOUNT',
1168 p_budget_account_id_tbl(i)
1169 FROM DUAL
1170 WHERE (p_po_encumberance_flag = 'Y' AND
1171 (p_dest_type_code_tbl(i) <> 'SHOP FLOOR' AND p_distribution_type_tbl(i) <> 'PREPAYMENT') AND
1172 -- PDOI for Complex PO Project
1173 (p_dest_type_code_tbl(i) <> 'SHOP FLOOR' OR (p_dest_type_code_tbl(i) = 'SHOP FLOOR'
1174 AND (SELECT entity_type from wip_entities where wip_entity_id = p_wip_entity_id_tbl(i)) = 6)) /* Encumbrance Project */
1175 AND p_budget_account_id_tbl(i) IS NULL)
1176 OR ( p_budget_account_id_tbl(i) IS NOT NULL
1177 AND NOT EXISTS(
1178 SELECT NULL
1179 FROM gl_code_combinations gcc
1180 WHERE gcc.code_combination_id = p_budget_account_id_tbl(i)
1181 AND gcc.enabled_flag = 'Y'
1182 AND TRUNC(NVL(p_gl_date_tbl(i), SYSDATE)) BETWEEN TRUNC(NVL(start_date_active,
1183 NVL(p_gl_date_tbl(i), SYSDATE)))
1184 AND TRUNC(NVL(end_date_active,
1185 NVL(p_gl_date_tbl(i), SYSDATE)))
1186 AND gcc.detail_posting_allowed_flag = 'Y'
1187 AND gcc.chart_of_accounts_id = p_chart_of_account_id
1188 AND gcc.summary_flag = 'N'));
1189
1190 IF (SQL%ROWCOUNT > 0) THEN
1191 x_result_type := po_validations.c_result_type_failure;
1192 END IF;
1193
1194 IF po_log.d_proc THEN
1195 po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
1196 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
1197 po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
1198 END IF;
1199 EXCEPTION
1200 WHEN OTHERS THEN
1201 IF po_log.d_exc THEN
1202 po_log.exc(d_mod, 0, NULL);
1203 END IF;
1204
1205 RAISE;
1206 END budget_account_id;
1207
1208 ----------------------------------------------------------------------------------
1209 -- Validation Logic:
1210 -- If the account_id is not null, it must be a valid id in gl_code_combinations.
1211 -- Used to validate accrual account id and variance account id.
1212 ----------------------------------------------------------------------------------
1213 PROCEDURE account_id(
1214 p_id_tbl IN po_tbl_number,
1215 p_account_id_tbl IN po_tbl_number,
1216 p_gl_date_tbl IN po_tbl_date,
1217 p_chart_of_account_id IN NUMBER,
1218 p_message_name IN VARCHAR2,
1219 p_column_name IN VARCHAR2,
1220 p_token_name IN VARCHAR2,
1221 x_result_set_id IN OUT NOCOPY NUMBER,
1222 x_result_type OUT NOCOPY VARCHAR2)
1223 IS
1224 d_mod CONSTANT VARCHAR2(100) := d_account_id;
1225 BEGIN
1226 IF x_result_set_id IS NULL THEN
1227 x_result_set_id := po_validations.next_result_set_id();
1228 END IF;
1229
1230 x_result_type := po_validations.c_result_type_success;
1231
1232 IF po_log.d_proc THEN
1233 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
1234 po_log.proc_begin(d_mod, 'p_account_id_tbl', p_account_id_tbl);
1235 po_log.proc_begin(d_mod, 'p_gl_date_tbl', p_gl_date_tbl);
1236 po_log.proc_begin(d_mod, 'p_chart_of_account_id', p_chart_of_account_id);
1237 po_log.proc_begin(d_mod, 'p_message_name', p_message_name);
1238 po_log.proc_begin(d_mod, 'p_column_name', p_column_name);
1239 po_log.proc_begin(d_mod, 'p_token_name', p_token_name);
1240 po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
1241 END IF;
1242
1243 FORALL i IN 1 .. p_id_tbl.COUNT
1244 INSERT INTO po_validation_results_gt
1245 (result_set_id,
1246 result_type,
1247 entity_type,
1248 entity_id,
1249 message_name,
1250 column_name,
1251 column_val,
1252 token1_name,
1253 token1_value)
1254 SELECT x_result_set_id,
1255 po_validations.c_result_type_failure,
1256 c_entity_type_distribution,
1257 p_id_tbl(i),
1258 p_message_name,
1259 p_column_name,
1260 p_account_id_tbl(i),
1261 p_token_name,
1262 p_account_id_tbl(i)
1263 FROM DUAL
1264 WHERE p_account_id_tbl(i) IS NOT NULL
1265 AND NOT EXISTS(
1266 SELECT NULL
1267 FROM gl_code_combinations gcc
1268 WHERE gcc.code_combination_id = p_account_id_tbl(i)
1269 AND gcc.enabled_flag = 'Y'
1270 AND TRUNC(NVL(p_gl_date_tbl(i), SYSDATE)) BETWEEN TRUNC(NVL(start_date_active,
1271 NVL(p_gl_date_tbl(i), SYSDATE)))
1272 AND TRUNC(NVL(end_date_active,
1273 NVL(p_gl_date_tbl(i), SYSDATE)))
1274 AND gcc.detail_posting_allowed_flag = 'Y'
1275 AND gcc.chart_of_accounts_id = p_chart_of_account_id
1276 AND gcc.summary_flag = 'N');
1277
1278 IF (SQL%ROWCOUNT > 0) THEN
1279 x_result_type := po_validations.c_result_type_failure;
1280 END IF;
1281
1282 IF po_log.d_proc THEN
1283 po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
1284 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
1285 po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
1286 END IF;
1287 EXCEPTION
1288 WHEN OTHERS THEN
1289 IF po_log.d_exc THEN
1290 po_log.exc(d_mod, 0, NULL);
1291 END IF;
1292
1293 RAISE;
1294 END account_id;
1295
1296 -----------------------------------------------------------
1297 -- Validation Logic:
1298 -- Project_accounting_context must be 'Y' if the values of project_id, task_id, expenditure_type,
1299 -- expenditure_organization_id are all not null.
1300 -----------------------------------------------------------
1301 PROCEDURE project_acct_context(
1302 p_id_tbl IN po_tbl_number,
1303 p_project_acct_ctx_tbl IN po_tbl_varchar30,
1304 p_project_id_tbl IN po_tbl_number,
1305 p_task_id_tbl IN po_tbl_number,
1306 p_exp_type_tbl IN po_tbl_varchar30,
1307 p_exp_org_id_tbl IN po_tbl_number,
1308 x_results IN OUT NOCOPY po_validation_results_type,
1309 x_result_type OUT NOCOPY VARCHAR2)
1310 IS
1311 d_mod CONSTANT VARCHAR2(100) := d_project_acct_context;
1312 BEGIN
1313 IF (x_results IS NULL) THEN
1314 x_results := po_validation_results_type.new_instance();
1315 END IF;
1316
1317 x_result_type := po_validations.c_result_type_success;
1318
1319 IF po_log.d_proc THEN
1320 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
1321 po_log.proc_begin(d_mod, 'p_project_acct_ctx_tbl', p_project_acct_ctx_tbl);
1322 po_log.proc_begin(d_mod, 'p_project_id_tbl', p_project_id_tbl);
1323 po_log.proc_begin(d_mod, 'p_task_id_tbl', p_task_id_tbl);
1324 po_log.proc_begin(d_mod, 'p_exp_type_tbl', p_exp_type_tbl);
1325 po_log.proc_begin(d_mod, 'p_exp_org_id_tbl', p_exp_org_id_tbl);
1326 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
1327 END IF;
1328
1329 FOR i IN 1 .. p_id_tbl.COUNT LOOP
1330 IF ( (p_project_acct_ctx_tbl(i) IS NULL OR p_project_acct_ctx_tbl(i) = 'N')
1331 AND p_project_id_tbl(i) IS NOT NULL
1332 AND p_task_id_tbl(i) IS NOT NULL
1333 AND p_exp_type_tbl(i) IS NOT NULL
1334 AND p_exp_org_id_tbl(i) IS NOT NULL) THEN
1335 x_results.add_result(p_entity_type => c_entity_type_distribution,
1336 p_entity_id => p_id_tbl(i),
1337 p_column_name => 'PROJECT_ACCOUNT_CONTEXT',
1338 p_column_val => p_project_acct_ctx_tbl(i),
1339 p_message_name => 'PO_PDOI_PROJECT_ACCT_CONTEXT');
1340 x_result_type := po_validations.c_result_type_failure;
1341 END IF;
1342 END LOOP;
1343
1344 IF po_log.d_proc THEN
1345 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
1346 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
1347 END IF;
1348 EXCEPTION
1349 WHEN OTHERS THEN
1350 IF po_log.d_exc THEN
1351 po_log.exc(d_mod, 0, NULL);
1352 END IF;
1353
1354 RAISE;
1355 END project_acct_context;
1356
1357 -----------------------------------------------------------
1358 -- Validation Logic:
1359 -- If project_accounting_context is 'Y',
1360 -- If destination_type_code is EXPENSE,
1361 -- a. validate project_id, task_id and expenditure_item_date against
1362 -- pa_projects_expend_v/pa_tasks_expends_v
1363 -- b. validate expenditure_type against pa_expenditure_type_expend_v
1364 -- c. validate expenditure_organization_id against pa_organizations_expends_v
1365 -- d. if all the above validations passed, call pa_transactions_pub.validate_transaction()
1366 -- to validate project information.
1367 -- Else if destination_type_code is INVENTORY
1368 -- a. call po_project_details_sv.validate_proj_references_wrp() to validate PJM project
1369 -- b. validate expenditure_type against pa_expenditure_types if not null
1370 -- c. validate expenditure_organization_id against per_organzaition_units.
1371 -----------------------------------------------------------
1372 PROCEDURE project_info(
1373 p_id_tbl IN po_tbl_number,
1374 p_project_acct_ctx_tbl IN po_tbl_varchar30,
1375 p_dest_type_code_tbl IN po_tbl_varchar30,
1376 p_project_id_tbl IN po_tbl_number,
1377 p_task_id_tbl IN po_tbl_number,
1378 p_expenditure_type_tbl IN po_tbl_varchar30,
1379 p_expenditure_org_id_tbl IN po_tbl_number,
1380 p_ship_to_org_id_tbl IN po_tbl_number,
1381 p_need_by_date_tbl IN po_tbl_date,
1382 p_promised_date_tbl IN po_tbl_date,
1383 p_expenditure_item_date_tbl IN po_tbl_date,
1384 p_ship_to_ou_id IN NUMBER,
1385 p_deliver_to_person_id_tbl IN po_tbl_number,
1386 p_agent_id_tbl IN po_tbl_number,
1387 p_txn_flow_header_id_tbl IN po_tbl_number,
1388 x_results IN OUT NOCOPY po_validation_results_type,
1389 x_result_type OUT NOCOPY VARCHAR2)
1390 IS
1391 d_mod CONSTANT VARCHAR2(100) := d_project_info;
1392 l_valid VARCHAR2(1);
1393 l_msg_name VARCHAR2(100); --<Bug 14662559>
1394 BEGIN
1395 IF (x_results IS NULL) THEN
1396 x_results := po_validation_results_type.new_instance();
1397 END IF;
1398
1399 x_result_type := po_validations.c_result_type_success;
1400
1401 IF po_log.d_proc THEN
1402 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
1403 po_log.proc_begin(d_mod, 'p_project_acct_ctx_tbl', p_project_acct_ctx_tbl);
1404 po_log.proc_begin(d_mod, 'p_dest_type_code_tbl', p_dest_type_code_tbl);
1405 po_log.proc_begin(d_mod, 'p_project_id_tbl', p_project_id_tbl);
1406 po_log.proc_begin(d_mod, 'p_task_id_tbl', p_task_id_tbl);
1407 po_log.proc_begin(d_mod, 'p_expenditure_type_tbl', p_expenditure_type_tbl);
1408 po_log.proc_begin(d_mod, 'p_expenditure_org_id_tbl', p_expenditure_org_id_tbl);
1409 po_log.proc_begin(d_mod, 'p_ship_to_org_id_tbl', p_ship_to_org_id_tbl);
1410 po_log.proc_begin(d_mod, 'p_need_by_date_tbl', p_need_by_date_tbl);
1411 po_log.proc_begin(d_mod, 'p_promised_date_tbl', p_promised_date_tbl);
1412 po_log.proc_begin(d_mod, 'p_expenditure_item_date_tbl', p_expenditure_item_date_tbl);
1413 po_log.proc_begin(d_mod, 'p_ship_to_ou_id', p_ship_to_ou_id);
1414 po_log.proc_begin(d_mod, 'p_deliver_to_person_id_tbl', p_deliver_to_person_id_tbl);
1415 po_log.proc_begin(d_mod, 'p_agent_id_tbl', p_agent_id_tbl);
1416 po_log.proc_begin(d_mod, 'p_txn_flow_header_id_tbl', p_txn_flow_header_id_tbl);
1417 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
1418 END IF;
1419
1420 FOR i IN 1 .. p_id_tbl.COUNT LOOP
1421 IF (p_project_acct_ctx_tbl(i) = 'Y') THEN
1422 po_pdoi_distributions_sv3.validate_project_info(p_dest_type_code_tbl(i),
1423 p_project_id_tbl(i),
1424 p_task_id_tbl(i),
1425 p_expenditure_type_tbl(i),
1426 p_expenditure_org_id_tbl(i),
1427 p_ship_to_org_id_tbl(i),
1428 p_need_by_date_tbl(i),
1429 p_promised_date_tbl(i),
1430 p_expenditure_item_date_tbl(i),
1431 p_ship_to_ou_id,
1432 NVL(p_deliver_to_person_id_tbl(i),p_agent_id_tbl(i)),
1433 l_valid,
1434 l_msg_name);
1435 --Bug 14662559: Show different error messages for different project validations.
1436
1437 IF (l_valid <> 'Y') THEN
1438 if l_msg_name = 'PO_PDOI_INVALID_EXPEND_TYPE' or l_msg_name = 'PO_PDOI_INVALID_EXPEND_ORG' then
1439 x_results.add_result(p_entity_type => c_entity_type_distribution,
1440 p_entity_id => p_id_tbl(i),
1441 p_column_name => 'PROJECT_ID',
1442 p_column_val => p_project_id_tbl(i),
1443 --Bug# 5117923: corrected the msg name
1444 p_message_name => l_msg_name);
1445 else
1446 x_results.add_result(p_entity_type => c_entity_type_distribution,
1447 p_entity_id => p_id_tbl(i),
1448 p_column_name => 'PROJECT_ID',
1449 p_column_val => p_project_id_tbl(i),
1450 --Bug# 5117923: corrected the msg name
1451 p_message_name => 'PO_PDOI_INVALID_PROJ_INFO',
1452 p_token1_name => 'PJM_ERROR_MSG',
1453 p_token1_value => FND_MESSAGE.GET_STRING('PA',l_msg_name)
1454 );
1455 end if;
1456
1457 x_result_type := po_validations.c_result_type_failure;
1458 END IF;
1459 END IF;
1460
1461 IF (p_dest_type_code_tbl(i) = 'EXPENSE' AND p_project_id_tbl(i) IS NOT NULL
1462 AND p_txn_flow_header_id_tbl(i) IS NOT NULL) THEN
1463 x_results.add_result(p_entity_type => c_entity_type_distribution,
1464 p_entity_id => p_id_tbl(i),
1465 p_column_name => 'PROJECT_ID',
1466 p_column_val => p_project_id_tbl(i),
1467 p_message_name => 'PO_CROSS_OU_PA_PROJECT_CHECK');
1468 x_result_type := po_validations.c_result_type_failure;
1469 END IF;
1470 END LOOP;
1471
1472 IF po_log.d_proc THEN
1473 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
1474 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
1475 END IF;
1476 EXCEPTION
1477 WHEN OTHERS THEN
1478 IF po_log.d_exc THEN
1479 po_log.exc(d_mod, 0, NULL);
1480 END IF;
1481
1482 RAISE;
1483 END project_info;
1484
1485 -----------------------------------------------------------
1486 -- Validation Logic:
1487 -- If tax_recovery_override_flag is 'Y' and
1488 -- p_allow_tax_rate_override is not 'Y', throw an error
1489 -----------------------------------------------------------
1490 PROCEDURE tax_recovery_override_flag(p_id_tbl IN po_tbl_number,
1491 p_recovery_override_flag_tbl IN po_tbl_varchar1,
1492 p_allow_tax_rate_override IN VARCHAR2,
1493 x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE,
1494 x_result_type OUT NOCOPY VARCHAR2)
1495 IS
1496 d_mod CONSTANT VARCHAR2(100) := d_tax_recovery_override_flag;
1497 BEGIN
1498 IF (x_results IS NULL) THEN
1499 x_results := po_validation_results_type.new_instance();
1500 END IF;
1501
1502 x_result_type := po_validations.c_result_type_success;
1503
1504 IF po_log.d_proc THEN
1505 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
1506 po_log.proc_begin(d_mod, 'p_recovery_override_flag_tbl', p_recovery_override_flag_tbl);
1507 po_log.proc_begin(d_mod, 'p_allow_tax_rate_override', p_allow_tax_rate_override);
1508 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
1509 END IF;
1510
1511 IF (p_allow_tax_rate_override <> 'Y') THEN
1512 FOR i IN 1 .. p_id_tbl.COUNT LOOP
1513 IF (p_recovery_override_flag_tbl(i) = 'Y') THEN
1514 x_results.add_result(p_entity_type => c_entity_type_distribution,
1515 p_entity_id => p_id_tbl(i),
1516 p_column_name => 'TAX_RECOVERY_OVERRIDE_FLAG',
1517 p_column_val => p_recovery_override_flag_tbl(i),
1518 p_message_name => 'PO_PDOI_NO_TAX_RATE_OVERRIDE');
1519 x_result_type := po_validations.c_result_type_failure;
1520 END IF;
1521 END LOOP;
1522 END IF;
1523
1524 IF po_log.d_proc THEN
1525 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
1526 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
1527 END IF;
1528 EXCEPTION
1529 WHEN OTHERS THEN
1530 IF po_log.d_exc THEN
1531 po_log.exc(d_mod, 0, NULL);
1532 END IF;
1533
1534 RAISE;
1535 END tax_recovery_override_flag;
1536
1537 -----------------------------------------------------------
1538 -- ACRN proj changes
1539 -- Validation Logic:
1540 -- If ACRN is enabled and po_account_helper.is_valid_ACRN
1541 -- returns an error then throw an error
1542 -----------------------------------------------------------
1543
1544 PROCEDURE ACRN_value_valid(
1545 p_dist_id_tbl IN PO_TBL_NUMBER
1546 , p_style_id_tbl IN PO_TBL_NUMBER
1547 , p_ACRN_tbl IN PO_TBL_VARCHAR30
1548 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
1549 , x_result_type OUT NOCOPY VARCHAR2
1550 )
1551 IS
1552 d_mod CONSTANT VARCHAR2(100) := d_ACRN_value_valid;
1553 l_is_ACRN_enabled VARCHAR2(1);
1554 l_org_id NUMBER;
1555 BEGIN
1556 IF PO_LOG.d_proc THEN
1557 PO_LOG.proc_begin(d_mod,'p_dist_id_tbl',p_dist_id_tbl);
1558 PO_LOG.proc_begin(d_mod,'p_style_id_tbl',p_style_id_tbl);
1559 PO_LOG.proc_begin(d_mod,'p_ACRN_tbl',p_ACRN_tbl);
1560 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
1561 END IF;
1562
1563 IF (x_results IS NULL) THEN
1564 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
1565 END IF;
1566
1567 x_result_type := po_validations.c_result_type_success;
1568
1569 FOR I IN 1..p_dist_id_tbl.COUNT LOOP
1570 -- CLM Controls Project changes
1571 BEGIN
1572 SELECT ORG_ID
1573 INTO l_org_id
1574 FROM PO_DISTRIBUTIONS_ALL
1575 WHERE PO_DISTRIBUTION_ID = p_dist_id_tbl(i);
1576 EXCEPTION
1577 WHEN NO_DATA_FOUND THEN
1578 l_org_id := NULL;
1579 END;
1580 l_is_ACRN_enabled := PO_PARTIAL_FUNDING_PKG.Is_ACRN_enabled(l_org_id);
1581
1582 IF(l_is_ACRN_enabled='Y' AND p_ACRN_tbl(i) IS NOT NULL)
1583 THEN
1584 DECLARE
1585 X_RETURN_STATUS VARCHAR2(1);
1586 X_RETURN_MESSAGE VARCHAR2(30);
1587 BEGIN
1588
1589 po_account_helper.is_valid_ACRN(p_ACRN_tbl(i), X_RETURN_STATUS, X_RETURN_MESSAGE);
1590
1591 IF(X_RETURN_STATUS = 'E')
1592 THEN
1593 x_results.add_result(
1594 p_entity_type => c_ENTITY_TYPE_DISTRIBUTION
1595 , p_entity_id => p_dist_id_tbl(i)
1596 , p_column_name => 'ACRN'
1597 , p_column_val => p_ACRN_tbl(i)
1598 , p_message_name => X_RETURN_MESSAGE
1599 );
1600 x_result_type := po_validations.c_result_type_failure;
1601
1602 END if;
1603
1604 END;
1605 END IF;
1606
1607 END LOOP;
1608
1609
1610 IF PO_LOG.d_proc THEN
1611 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
1612 PO_LOG.log(PO_LOG.c_PROC_END,d_mod,NULL,'x_results',x_results);
1613 END IF;
1614
1615 EXCEPTION
1616 WHEN OTHERS THEN
1617 IF PO_LOG.d_exc THEN
1618 PO_LOG.exc(d_mod,0,NULL);
1619 END IF;
1620 RAISE;
1621
1622 END ACRN_value_valid;
1623
1624 -- End ACRN proj
1625
1626 ----------------------------------------------------------------------------
1627 -- CLM Partial Funding Changes
1628 -- Validation Logic:
1629 -- 1. If Partial_funded_flag is Y and Encumbrance is not enabled for the
1630 -- respective Organization, throw an error.
1631 -- 2. Else If Partial_funded_flag is Y and the respective document is not
1632 -- CLM enabled, throw an error.
1633 -- 3. Else If Partial_funded_flag is Y and the Funded value is less than
1634 -- zero, throw an error.
1635 -- Note: If Funded value is greater than the Total Order value, a warning
1636 -- need to be thrown. But the tax amount is not yet calculated, this
1637 -- validation will be done in Post Processing.
1638 --------------------------------------------------------------------------
1639 PROCEDURE partial_funded_flag(p_id_tbl IN po_tbl_number,
1640 p_partial_funded_flag_tbl IN po_tbl_varchar1,
1641 p_funded_value_tbl IN po_tbl_number,
1642 p_style_id_tbl IN po_tbl_number,
1643 p_po_encumberance_flag IN VARCHAR2,
1644 x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE,
1645 x_result_type OUT NOCOPY VARCHAR2)
1646 IS
1647 d_mod CONSTANT VARCHAR2(100) := d_partial_funded_flag;
1648 style_id_exists NUMBER := 0;
1649 d_position NUMBER := 0;
1650 BEGIN
1651 IF (x_results IS NULL) THEN
1652 x_results := po_validation_results_type.new_instance();
1653 END IF;
1654
1655 x_result_type := po_validations.c_result_type_success;
1656
1657 IF po_log.d_proc THEN
1658 po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
1659 po_log.proc_begin(d_mod, 'p_partial_funded_flag_tbl', p_partial_funded_flag_tbl);
1660 po_log.proc_begin(d_mod, 'p_funded_value_tbl', p_funded_value_tbl);
1661 po_log.proc_begin(d_mod, 'p_style_id_tbl', p_style_id_tbl);
1662 po_log.proc_begin(d_mod, 'p_po_encumberance_flag', p_po_encumberance_flag);
1663 po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
1664 END IF;
1665
1666 d_position := 10;
1667
1668 FOR i IN 1 .. p_id_tbl.COUNT LOOP
1669 IF (p_partial_funded_flag_tbl(i) = 'Y' AND p_po_encumberance_flag = 'N') THEN
1670
1671 d_position := 20;
1672
1673 IF (PO_LOG.d_stmt) THEN
1674 PO_LOG.stmt(d_mod, d_position, 'Purchase order Encumbrance for this Organization is not enabled');
1675 END IF;
1676 x_results.add_result(p_entity_type => c_entity_type_distribution,
1677 p_entity_id => p_id_tbl(i),
1678 p_column_name => 'PARTIAL_FUNDED_FLAG',
1679 p_column_val => p_partial_funded_flag_tbl(i),
1680 p_message_name => 'PO_PDOI_PF_FLAG_ENC');
1681 x_result_type := po_validations.c_result_type_failure;
1682 ELSIF (p_partial_funded_flag_tbl(i) = 'Y' AND p_po_encumberance_flag = 'Y') THEN
1683
1684 d_position := 30;
1685
1686 IF (PO_LOG.d_stmt) THEN
1687 PO_LOG.stmt(d_mod, d_position, 'Check for CLM enabled Flag of Style Id');
1688 END IF;
1689
1690 BEGIN
1691 SELECT Count(1) INTO style_id_exists
1692 FROM po_doc_style_headers
1693 WHERE style_id = Nvl(p_style_id_tbl(i),-1)
1694 AND Nvl(clm_flag,'N') = 'Y';
1695 EXCEPTION
1696 WHEN NO_DATA_FOUND THEN
1697 IF po_log.d_proc THEN
1698 po_log.proc_begin(d_mod, 'CLM Flag is not enabled for the Style ID ', p_style_id_tbl(i));
1699 END IF;
1700 END;
1701
1702 IF (style_id_exists = 0) THEN
1703 d_position := 40;
1704 x_results.add_result(p_entity_type => c_entity_type_distribution,
1705 p_entity_id => p_id_tbl(i),
1706 p_column_name => 'PARTIAL_FUNDED_FLAG',
1707 p_column_val => p_partial_funded_flag_tbl(i),
1708 p_message_name => 'PO_PDOI_PF_FLAG_CLM');
1709 x_result_type := po_validations.c_result_type_failure;
1710 ELSIF (p_funded_value_tbl(i) < 0) THEN
1711 d_position := 50;
1712 IF po_log.d_proc THEN
1713 po_log.proc_begin(d_mod, 'Negative Funded Value ', p_funded_value_tbl(i));
1714 END IF;
1715 x_results.add_result(p_entity_type => c_entity_type_distribution,
1716 p_entity_id => p_id_tbl(i),
1717 p_column_name => 'PARTIAL_FUNDED_FLAG',
1718 p_column_val => p_partial_funded_flag_tbl(i),
1719 p_message_name => 'PO_PDOI_INVALID_FUND_VAL');
1720 x_result_type := po_validations.c_result_type_failure;
1721 END IF;
1722 END IF;
1723 END LOOP;
1724 IF po_log.d_proc THEN
1725 po_log.proc_end(d_mod, 'x_result_type', x_result_type);
1726 po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
1727 END IF;
1728 EXCEPTION
1729 WHEN OTHERS THEN
1730 IF po_log.d_exc THEN
1731 po_log.exc(d_mod, 0, NULL);
1732 END IF;
1733
1734 RAISE;
1735 END partial_funded_flag;
1736
1737 END po_val_distributions2;
[Home] [Help]