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