[Home] [Help]
PACKAGE BODY: APPS.PO_PDOI_DISTRIBUTIONS_SV3
Source
1 PACKAGE BODY PO_PDOI_DISTRIBUTIONS_SV3 AS
2 /* $Header: POXPIDVB.pls 120.3.12020000.2 2013/02/11 01:36:32 vegajula ship $ */
3
4 /*================================================================
5
6 PROCEDURE NAME: validate_po_dist()
7
8 ==================================================================*/
9
10 /**
11 * Private Procedure: validate_po_dist
12 * Requires: none
13 * Modifies: PO_INTERFACE_ERRORS
14 * Effects: Validates the given PO distribution information. Writes
15 * any validation errors to the PO_INTERFACE_ERRORS table.
16 * Returns: none
17 */
18 PROCEDURE validate_po_dist(x_interface_header_id in NUMBER,
19 x_interface_line_id in NUMBER,
20 x_interface_distribution_id in NUMBER,
21 x_po_distribution_id IN NUMBER,
22 x_charge_account_id IN NUMBER,
23 x_destination_organization_id IN NUMBER,
24 x_sob_id IN NUMBER,
25 x_item_id IN NUMBER,
26 x_ship_to_organization_id IN NUMBER,
27 x_deliver_to_person_id IN NUMBER,
28 x_deliver_to_location_id IN NUMBER,
29 x_header_processable_flag in out NOCOPY varchar2,
30 x_quantity_ordered IN NUMBER,
31 x_distribution_num IN NUMBER,
32 x_quantity_delivered IN NUMBER,
33 x_quantity_billed IN NUMBER,
34 x_quantity_cancelled IN NUMBER,
35 x_destination_type_code IN VARCHAR2,
36 x_accrue_on_receipt_flag IN VARCHAR2,
37 p_transaction_flow_header_id IN NUMBER, --<Shared Proc FPJ>
38 x_destination_subinventory IN VARCHAR2,
39 x_wip_entity_id IN NUMBER,
40 x_wip_repetitive_schedule_id IN NUMBER,
41 x_prevent_encumbrance_flag IN VARCHAR2,
42 x_budget_account_id IN NUMBER,
43 x_accrual_account_id IN NUMBER,
44 x_variance_account_id IN NUMBER,
45 -- Bug 2137906 fixed. added ussgl_transaction_code.
46 x_ussgl_transaction_code IN VARCHAR2,
47 x_gl_date IN DATE,
48 x_chart_of_accounts_id IN NUMBER,
49 x_project_account_context IN VARCHAR2,
50 x_project_id IN NUMBER,
51 x_task_id IN NUMBER,
52 x_expenditure_type IN VARCHAR2,
53 x_expenditure_organization_id IN NUMBER,
54 p_order_type_lookup_code IN VARCHAR2, --<SERVICES FPJ>
55 p_amount IN NUMBER, --<SERVICES FPJ>
56 -- <PO_PJM_VALIDATION FPI START>
57 x_need_by_date IN DATE,
58 x_promised_date IN DATE,
59 x_expenditure_item_date IN DATE, --Bug 2892199
60 -- <PO_PJM_VALIDATION FPI END>
61 p_ship_to_ou_id IN NUMBER --< Bug 3265539 >
62 )
63 IS
64
65 X_progress varchar2(3) := NULL;
66 x_valid varchar2(1) := NULL;
67 x_item_status varchar2(2);
68 x_enc_flag varchar2(1);
69 x_temp_val BOOLEAN ;
70 x_msg_name varchar2(100); -- bug 14662559
71
72 BEGIN
73
74 x_progress := '010';
75 if x_po_distribution_id is null then
76 po_interface_errors_sv1.handle_interface_errors(
77 'PO_DOCS_OPEN_INTERFACE',
78 'FATAL',
79 null,
80 x_interface_header_id,
81 x_interface_line_id,
82 --need to create this message
83 'PO_PDOI_NO_DIST_ID',
84 'PO_DISTRIBUTIONS_INTERFACE',
85 'PO_DISTRIBUTION_ID' ,
86 null,null,null,null,null,null,
87 null,null,null,null,null,null,
88 x_header_processable_flag, x_interface_distribution_id);
89 end if;
90
91 x_progress := '020';
92
93 if x_charge_account_id is null then
94 po_interface_errors_sv1.handle_interface_errors(
95 'PO_DOCS_OPEN_INTERFACE',
96 'FATAL',
97 null,
98 x_interface_header_id,
99 x_interface_line_id,
100 --need to create this message
101 'PO_PDOI_NO_CHG_ACCT',
102 'PO_DISTRIBUTIONS_INTERFACE',
103 'CHARGE_ACCOUNT_ID' ,
104 null,null,null,null,null,null,
105 null,null,null,null,null,null,
106 x_header_processable_flag, x_interface_distribution_id);
107 end if;
108
109 x_progress := '030';
110
111 --<SERVICES FPJ START>
112 IF (p_order_type_lookup_code IN ('RATE', 'FIXED PRICE')) THEN
113 IF (NVL(p_amount, 0) <= 0) THEN
114 PO_INTERFACE_ERRORS_SV1.handle_interface_errors(
115 X_interface_type => 'PO_DOCS_OPEN_INTERFACE',
116 X_Error_type => 'FATAL',
117 X_Batch_id => NULL,
118 X_Interface_Header_Id => X_interface_header_id,
119 X_Interface_Line_id => X_interface_line_id,
120 X_Error_message_name => 'PO_PDOI_SVC_MUST_AMT',
121 X_Table_name => 'PO_DISTRIBUTIONS_INTERFACE',
122 X_Column_name => 'AMOUNT',
123 X_TokenName1 => NULL,
124 X_TokenName2 => NULL,
125 X_TokenName3 => NULL,
126 X_TokenName4 => NULL,
127 X_TokenName5 => NULL,
128 X_TokenName6 => NULL,
129 X_TokenValue1 => NULL,
130 X_TokenValue2 => NULL,
131 X_TokenValue3 => NULL,
132 X_TokenValue4 => NULL,
133 X_TokenValue5 => NULL,
134 X_TokenValue6 => NULL,
135 X_header_processable_flag => x_header_processable_flag);
136 END IF;
137
138 IF (NVL(x_quantity_ordered, 0) <> 0) THEN
139 PO_INTERFACE_ERRORS_SV1.handle_interface_errors(
140 X_interface_type => 'PO_DOCS_OPEN_INTERFACE',
141 X_Error_type => 'FATAL',
142 X_Batch_id => NULL,
143 X_Interface_Header_Id => X_interface_header_id,
144 X_Interface_Line_id => X_interface_line_id,
145 X_Error_message_name => 'PO_SVC_NO_QTY',
146 X_Table_name => 'PO_DISTRIBUTIONS_INTERFACE',
147 X_Column_name => 'QUANTITY_ORDERED',
148 X_TokenName1 => NULL,
149 X_TokenName2 => NULL,
150 X_TokenName3 => NULL,
151 X_TokenName4 => NULL,
152 X_TokenName5 => NULL,
153 X_TokenName6 => NULL,
154 X_TokenValue1 => NULL,
155 X_TokenValue2 => NULL,
156 X_TokenValue3 => NULL,
157 X_TokenValue4 => NULL,
158 X_TokenValue5 => NULL,
159 X_TokenValue6 => NULL,
160 X_header_processable_flag => x_header_processable_flag);
161 END IF; --IF (NVL(x_quantity_ordered, 0) <> 0)
162
163 ELSE --if p_order_type_lookup_code not in ('RATE', 'FIXED PRICE')
164 --<SERVICES FPJ END>
165 if (x_quantity_ordered <= 0) OR (x_quantity_ordered is null) then
166 po_interface_errors_sv1.handle_interface_errors(
167 'PO_DOCS_OPEN_INTERFACE',
168 'FATAL',
169 null,
170 x_interface_header_id,
171 x_interface_line_id,
172 --need to create this message
173 'PO_PDOI_INVALID_QTY',
174 'PO_DISTRIBUTIONS_INTERFACE',
175 'QUANTITY_ORDERED' ,
176 'QUANTITY_ORDERED',null,null,null,null,null,
177 x_QUANTITY_ORDERED,null,null,null,null,null,
178 x_header_processable_flag, x_interface_distribution_id);
179 end if;
180
181 --<SERVICES FPJ START>
182 IF (NVL(p_amount,0) <> 0) THEN
183 PO_INTERFACE_ERRORS_SV1.handle_interface_errors(
184 X_interface_type => 'PO_DOCS_OPEN_INTERFACE',
185 X_Error_type => 'FATAL',
186 X_Batch_id => NULL,
187 X_Interface_Header_Id => X_interface_header_id,
188 X_Interface_Line_id => X_interface_line_id,
189 X_Error_message_name => 'PO_SVC_NO_AMT',
190 X_Table_name => 'PO_DISTRIBUTIONS_INTERFACE',
191 X_Column_name => 'AMOUNT_ORDERED',
192 X_TokenName1 => NULL,
193 X_TokenName2 => NULL,
194 X_TokenName3 => NULL,
195 X_TokenName4 => NULL,
196 X_TokenName5 => NULL,
197 X_TokenName6 => NULL,
198 X_TokenValue1 => NULL,
199 X_TokenValue2 => NULL,
200 X_TokenValue3 => NULL,
201 X_TokenValue4 => NULL,
202 X_TokenValue5 => NULL,
203 X_TokenValue6 => NULL,
204 X_header_processable_flag => x_header_processable_flag);
205 END IF;
206 END IF; --IF (p_order_type_lookup_code IN ('RATE', 'FIXED PRICE')
207 --<SERVICES FPJ END>
208
209 x_progress := '040';
210
211 if (x_distribution_num is null) then
212 po_interface_errors_sv1.handle_interface_errors(
213 'PO_DOCS_OPEN_INTERFACE',
214 'FATAL',
215 null,
216 x_interface_header_id,
217 x_interface_line_id,
218 --need to create this message
219 'PO_PDOI_NO_DIST_NUM',
220 'PO_DISTRIBUTIONS_INTERFACE',
221 'DISTRIBUTION_NUM' ,
222 null,null,null,null,null,null,
223 null,null,null,null,null,null,
224 x_header_processable_flag, x_interface_distribution_id);
225 end if;
226
227 x_progress := '060';
228
229 -- Fixed Bug 2681256 draising
230 -- if (x_quantity_delivered <> 0) OR (x_quantity_delivered is not null) then
231
232 if nvl(x_quantity_delivered,0) <> 0 then
233 po_interface_errors_sv1.handle_interface_errors(
234 'PO_DOCS_OPEN_INTERFACE',
235 'FATAL',
236 null,
237 x_interface_header_id,
238 x_interface_line_id,
239 --need to create this message
240 'PO_PDOI_INVALID_QTY_DEL',
241 'PO_DISTRIBUTIONS_INTERFACE',
242 'QUANTITY_DELIVERED' ,
243 'QUANTITY_DELIVERED',null,null,null,null,null,
244 x_QUANTITY_DELIVERED,null,null,null,null,null,
245 x_header_processable_flag, x_interface_distribution_id);
246 end if;
247
248 x_progress := '070';
249
250 -- if (x_quantity_billed <> 0) OR (x_quantity_billed is not null) then
251 if nvl(x_quantity_billed,0) <> 0 then
252 po_interface_errors_sv1.handle_interface_errors(
253 'PO_DOCS_OPEN_INTERFACE',
254 'FATAL',
255 null,
256 x_interface_header_id,
257 x_interface_line_id,
258 --need to create this message
259 'PO_PDOI_INVALID_QTY_BILL',
260 'PO_DISTRIBUTIONS_INTERFACE',
261 'QUANTITY_BILLED' ,
262 'QUANTITY_BILLED',null,null,null,null,null,
263 x_QUANTITY_BILLED,null,null,null,null,null,
264 x_header_processable_flag, x_interface_distribution_id);
265 end if;
266
267 x_progress := '080';
268
269 -- if (x_quantity_cancelled <> 0) OR (x_quantity_cancelled is not null) then
270
271 if nvl(x_quantity_cancelled,0) <> 0 then
272 po_interface_errors_sv1.handle_interface_errors(
273 'PO_DOCS_OPEN_INTERFACE',
274 'FATAL',
275 null,
276 x_interface_header_id,
277 x_interface_line_id,
278 --need to create this message
279 'PO_PDOI_INVALID_QTY_CANCELLED',
280 'PO_DISTRIBUTIONS_INTERFACE',
281 'QUANTITY_CANCELLED' ,
282 'QUANTITY_CANCELLED',null,null,null,null,null,
283 x_QUANTITY_CANCELLED,null,null,null,null,null,
284 x_header_processable_flag, x_interface_distribution_id);
285 end if;
286
287 x_progress := '090';
288
289 --< Shared Proc FPJ Start>
290 -- The only validation needed for destination org is to ensure it is the same
291 -- as the Ship-to org, which has already been validated.
292 --
293 --< Bug 3022783 Start >
294 -- Destination org must be equal to ship-to org.
295 IF NVL(x_destination_organization_id, -11) <>
296 NVL(x_ship_to_organization_id, -99)
297 THEN
298 po_interface_errors_sv1.handle_interface_errors(
299 'PO_DOCS_OPEN_INTERFACE',
300 'FATAL',
301 null,
302 x_interface_header_id,
303 x_interface_line_id,
304 'PO_PDOI_INVALID_DEST_ORG',
305 'PO_DISTRIBUTIONS_INTERFACE',
306 'DESTINATION_ORGANIZATION_ID' ,
307 'DESTINATION_ORGANIZATION',null,null,null,null,null,
308 x_DESTINATION_ORGANIZATION_ID,null,null,null,null,null,
309 x_header_processable_flag, x_interface_distribution_id);
310 end if;
311 --< Bug 3022783 End >
312 --< Shared Proc FPJ End >
313
314 x_progress := '100';
315
316 if x_destination_type_code is not null then
317 po_items_sv2.get_item_status(x_item_id,
318 x_ship_to_organization_id,
319 x_item_status );
320 x_valid := validate_destination_type_code(x_destination_type_code, x_item_status,
321 x_accrue_on_receipt_flag, p_transaction_flow_header_id); --<Shared Proc FPJ>
322 if x_valid <> 'Y' then
323 po_interface_errors_sv1.handle_interface_errors(
324 'PO_DOCS_OPEN_INTERFACE',
325 'FATAL',
326 null,
327 x_interface_header_id,
328 x_interface_line_id,
329 --need to create this message
330 'PO_PDOI_INVALID_DEST_TYPE',
331 'PO_DISTRIBUTIONS_INTERFACE',
332 'DESTINATION_TYPE_CODE' ,
333 'DESTINATION_TYPE',null,null,null,null,null,
334 x_DESTINATION_TYPE_CODE,null,null,null,null,null,
335 x_header_processable_flag, x_interface_distribution_id);
336 end if;
337 end if;
338
339 x_progress := '110';
340
341 if x_deliver_to_location_id is not null then
342 x_valid := validate_deliver_to_loc_id(x_deliver_to_location_id, x_ship_to_organization_id);
343 if x_valid <> 'Y' then
344 po_interface_errors_sv1.handle_interface_errors(
345 'PO_DOCS_OPEN_INTERFACE',
346 'FATAL',
347 null,
348 x_interface_header_id,
349 x_interface_line_id,
350 --need to create this message
351 'PO_PDOI_INVALID_DEL_LOCATION',
352 'PO_DISTRIBUTIONS_INTERFACE',
353 'DELIVER_TO_LOCATION_ID' ,
354 'DELIVER_TO_LOCATION',null,null,null,null,null,
355 x_DELIVER_TO_LOCATION_ID,null,null,null,null,null,
356 x_header_processable_flag, x_interface_distribution_id);
357 end if;
358 end if;
359
360 x_progress := '115';
361
362 if x_deliver_to_person_id is not null then
363 x_valid := validate_deliver_to_person_id(x_deliver_to_person_id);
364 if x_valid <> 'Y' then
365 po_interface_errors_sv1.handle_interface_errors(
366 'PO_DOCS_OPEN_INTERFACE',
367 'FATAL',
368 null,
369 x_interface_header_id,
370 x_interface_line_id,
371 --need to create this message
372 'PO_PDOI_INVALID_DEL_PERSON',
373 'PO_DISTRIBUTIONS_INTERFACE',
374 'DELIVER_TO_PERSON_ID' ,
375 'DELIVER_TO_PERSON',null,null,null,null,null,
376 x_DELIVER_TO_PERSON_ID,null,null,null,null,null,
377 x_header_processable_flag, x_interface_distribution_id);
378 end if;
379 end if;
380
381 x_progress := '120';
382
383 IF (x_item_id is NULL) AND ( x_destination_type_code = 'INVENTORY' ) THEN
384 po_interface_errors_sv1.handle_interface_errors(
385 'PO_DOCS_OPEN_INTERFACE',
386 'FATAL',
387 null,
388 x_interface_header_id,
389 x_interface_line_id,
390 --need to create this message
391 'PO_PDOI_INVALID_DEST_TYPE',
392 'PO_DISTRIBUTIONS_INTERFACE',
393 'DESTINATION_TYPE_CODE' ,
394 'DESTINATION_TYPE',null,null,null,null,null,
395 x_DESTINATION_TYPE_CODE,null,null,null,null,null,
396 x_header_processable_flag, x_interface_distribution_id);
397 END IF;
398
399 x_progress := '130';
400
401 if (x_destination_TYPE_CODE = 'INVENTORY') and (x_destination_subinventory is not null) then
402 x_valid := validate_dest_subinventory(x_destination_subinventory, x_ship_to_organization_id, x_item_id);
403 if x_valid <> 'Y' then
404 po_interface_errors_sv1.handle_interface_errors(
405 'PO_DOCS_OPEN_INTERFACE',
406 'FATAL',
407 null,
408 x_interface_header_id,
409 x_interface_line_id,
410 --need to create this message
411 'PO_PDOI_INVALID_DEST_SUBINV',
412 'PO_DISTRIBUTIONS_INTERFACE',
413 'DESTINATION_SUBINVENTORY' ,
414 'DESTINATION_SUBINVENTORY',null,null,null,null,null,
415 x_DESTINATION_SUBINVENTORY,null,null,null,null,null,
416 x_header_processable_flag, x_interface_distribution_id);
417 end if;
418 END IF;
419
420 x_progress := '140';
421
422 if (x_destination_type_code in ('SHOP FLOOR','EXPENSE')) and (x_destination_subinventory is not null) then
423 po_interface_errors_sv1.handle_interface_errors(
424 'PO_DOCS_OPEN_INTERFACE',
425 'FATAL',
426 null,
427 x_interface_header_id,
428 x_interface_line_id,
429 --need to create this message
430 'PO_PDOI_INVALID_DEST_SUBINV',
431 'PO_DISTRIBUTIONS_INTERFACE',
432 'DESTINATION_SUBINVENTORY' ,
433 'DESTINATION_SUBINVENTORY',null,null,null,null,null,
434 x_DESTINATION_SUBINVENTORY,null,null,null,null,null,
435 x_header_processable_flag, x_interface_distribution_id);
436
437 end if;
438
439 x_progress := '150';
440
441 /* Bug 3083961 x_wip_repetitive_schedule_id can be null. So check only x_wip_entity_id. Fail the
442 Distribution if it is null and x_destination_type_code is SHOP FLOOR */
443
444 if (x_destination_type_code = 'SHOP FLOOR') then
445 if (x_wip_entity_id is not null) then
446 x_valid := validate_wip(x_wip_entity_id, x_destination_organization_id, x_wip_repetitive_schedule_id);
447 if x_valid <> 'Y' then
448 if x_wip_repetitive_schedule_id is not null then
449 po_interface_errors_sv1.handle_interface_errors(
450 'PO_DOCS_OPEN_INTERFACE',
451 'FATAL',
452 null,
453 x_interface_header_id,
454 x_interface_line_id,
455 --need to create this message
456 'PO_PDOI_INVALID_WIP_SCHED',
457 'PO_DISTRIBUTIONS_INTERFACE',
458 'WIP_REPETITIVE_SCHEDULE_ID' ,
459 'WIP_REPETITIVE_SCHEDULE_ID',null,null,null,null,null,
460 x_WIP_REPETITIVE_SCHEDULE_ID,null,null,null,null,null,
461 x_header_processable_flag, x_interface_distribution_id);
462 else
463 /* Bug 3083961 */
464 po_interface_errors_sv1.handle_interface_errors(
465 'PO_DOCS_OPEN_INTERFACE',
466 'FATAL',
467 null,
468 x_interface_header_id,
469 x_interface_line_id,
470 'PO_PDOI_INVALID_WIP_ENTITY',
471 'PO_DISTRIBUTIONS_INTERFACE',
472 'WIP_ENTITY_ID',
473 'WIP_ENTITY',null,null,null,null,null,
474 x_wip_entity_id,null,null,null,null,null,
475 x_header_processable_flag, x_interface_distribution_id);
476 end if;
477 end if; /* x_valid */
478 else -- x_wip_entity_id is null
479 /* Bug 3083961 */
480 po_interface_errors_sv1.handle_interface_errors(
481 'PO_DOCS_OPEN_INTERFACE',
482 'FATAL',
483 null,
484 X_interface_header_id,
485 X_interface_line_id,
486 'PO_PDOI_COLUMN_NOT_NULL',
487 'PO_DISTRIBUTIONS_INTERFACE',
488 'WIP_ENTITY_ID',
489 'COLUMN_NAME',
490 null,null,null,null,null,
491 'WIP_ENTITY_ID',
492 null,null, null,null,null,
493 X_header_processable_flag);
494 end if;
495 end if;
496
497 x_progress := '160';
498
499 IF x_destination_type_code = 'SHOP FLOOR' THEN
500
501 if x_Prevent_Encumbrance_Flag = 'N' then
502 po_interface_errors_sv1.handle_interface_errors(
503 'PO_DOCS_OPEN_INTERFACE',
504 'FATAL',
505 null,
506 x_interface_header_id,
507 x_interface_line_id,
508 --need to create this message
509 'PO_PDOI_INV_PREV_ENCUM_FLAG',
510 'PO_DISTRIBUTIONS_INTERFACE',
511 'PREVENT_ENCUMBRANCE_FLAG' ,
512 null,null,null,null,null,null,
513 null,null,null,null,null,null,
514 x_header_processable_flag, x_interface_distribution_id);
515 end if;
516 else --other dest
517 if x_Prevent_Encumbrance_Flag = 'Y' then
518 po_interface_errors_sv1.handle_interface_errors(
519 'PO_DOCS_OPEN_INTERFACE',
520 'FATAL',
521 null,
522 x_interface_header_id,
523 x_interface_line_id,
524 --need to create this message
525 'PO_PDOI_INV_PREV_ENCUM_FLAG',
526 'PO_DISTRIBUTIONS_INTERFACE',
527 'PREVENT_ENCUMBRANCE_FLAG' ,
528 null,null,null,null,null,null,
529 null,null,null,null,null,null,
530 x_header_processable_flag, x_interface_distribution_id);
531 end if;
532 END IF;
533
534 x_progress := '180';
535
536 if x_charge_account_id is not null then
537 x_valid := validate_account(x_charge_account_id, x_gl_date, x_chart_of_accounts_id);
538 if x_valid <> 'Y' then
539 po_interface_errors_sv1.handle_interface_errors(
540 'PO_DOCS_OPEN_INTERFACE',
541 'FATAL',
542 null,
543 x_interface_header_id,
544 x_interface_line_id,
545 --need to create this message
546 'PO_PDOI_INVALID_CHG_ACCOUNT',
547 'PO_DISTRIBUTIONS_INTERFACE',
548 'CHARGE_ACCOUNT_ID' ,
549 'CHARGE_ACCOUNT',null,null,null,null,null,
550 x_CHARGE_ACCOUNT_ID,null,null,null,null,null,
551 x_header_processable_flag, x_interface_distribution_id);
552 end if;
553 end if;
554
555 x_progress := '190';
556
557 if x_budget_account_id is not null then
558 x_valid := validate_account(x_budget_account_id, x_gl_date, x_chart_of_accounts_id);
559 if x_valid <> 'Y' then
560 po_interface_errors_sv1.handle_interface_errors(
561 'PO_DOCS_OPEN_INTERFACE',
562 'FATAL',
563 null,
564 x_interface_header_id,
565 x_interface_line_id,
566 --need to create this message
567 'PO_PDOI_INVALID_BUDGET_ACCT',
568 'PO_DISTRIBUTIONS_INTERFACE',
569 'BUDGET_ACCOUNT_ID' ,
570 'BUDGET_ACCOUNT',null,null,null,null,null,
571 x_BUDGET_ACCOUNT_ID,null,null,null,null,null,
572 x_header_processable_flag, x_interface_distribution_id);
573
574 end if;
575
576 else
577
578 /* Bug 2098833 */
579 /* If budget account is null and encumbrance is on, then it is an error */
580
581 select nvl(purch_encumbrance_flag,'N')
582 into x_enc_flag
583 from financials_system_parameters ;
584
585 if (x_enc_flag = 'Y') then
586
587 po_interface_errors_sv1.handle_interface_errors(
588 'PO_DOCS_OPEN_INTERFACE',
589 'FATAL',
590 null,
591 x_interface_header_id,
592 x_interface_line_id,
593 'PO_PDOI_INVALID_BUDGET_ACCT',
594 'PO_DISTRIBUTIONS_INTERFACE',
595 'BUDGET_ACCOUNT_ID' ,
596 'BUDGET_ACCOUNT',null,null,null,null,null,
597 NULL,null,null,null,null,null,
598 x_header_processable_flag, x_interface_distribution_id);
599 end if;
600
601 end if;
602
603 x_progress := '200';
604
605 if x_variance_account_id is not null then
606 x_valid := validate_account(x_variance_account_id, x_gl_date, x_chart_of_accounts_id);
607 if x_valid <> 'Y' then
608 po_interface_errors_sv1.handle_interface_errors(
609 'PO_DOCS_OPEN_INTERFACE',
610 'FATAL',
611 null,
612 x_interface_header_id,
613 x_interface_line_id,
614 --need to create this message
615 'PO_PDOI_INVALID_VAR_ACCT',
616 'PO_DISTRIBUTIONS_INTERFACE',
617 'VARIANCE_ACCOUNT_ID' ,
618 'VARIANCE_ACCOUNT',null,null,null,null,null,
619 x_VARIANCE_ACCOUNT_ID,null,null,null,null,null,
620 x_header_processable_flag, x_interface_distribution_id);
621 end if;
622 end if;
623
624 x_progress := '210';
625
626 if x_accrual_account_id is not null then
627 x_valid := validate_account(x_accrual_account_id, x_gl_date, x_chart_of_accounts_id);
628 if x_valid <> 'Y' then
629 po_interface_errors_sv1.handle_interface_errors(
630 'PO_DOCS_OPEN_INTERFACE',
631 'FATAL',
632 null,
633 x_interface_header_id,
634 x_interface_line_id,
635 --need to create this message
636 'PO_PDOI_INVALID_ACCRUAL_ACCT',
637 'PO_DISTRIBUTIONS_INTERFACE',
638 'ACCRUAL_ACCOUNT_ID' ,
639 'ACCRUAL_ACCOUNT',null,null,null,null,null,
640 x_accrual_account_id,null,null,null,null,null,
641 x_header_processable_flag, x_interface_distribution_id);
642 end if;
643 end if;
644
645 x_progress := '220';
646
647 if (x_project_account_context = 'N' or x_project_account_context is null)
648 and (x_project_id is not null) and (x_task_id is not null)
649 and (x_expenditure_type is not null)
650 and (x_expenditure_organization_id is not null) then
651 po_interface_errors_sv1.handle_interface_errors(
652 'PO_DOCS_OPEN_INTERFACE',
653 'FATAL',
654 null,
655 x_interface_header_id,
656 x_interface_line_id,
657 --need to create this message
658 'PO_PDOI_PROJ_ACCT_CONTEXT',
659 'PO_DISTRIBUTIONS_INTERFACE',
660 'PROJECT_ACCOUNT_CONTEXT' ,
661 null,null,null,null,null,null,
662 null,null,null,null,null,null,
663 x_header_processable_flag, x_interface_distribution_id);
664 end if;
665
666 x_progress := '230';
667
668 if (x_project_account_context = 'Y') then
669
670 -- <PO_PJM_VALIDATION FPI>
671 -- Make sure that there is no message on the stack.
672 fnd_message.clear;
673
674 validate_project_info
675 (x_destination_type_code,
676 x_project_id,
677 x_task_id,
678 x_expenditure_type,
679 x_expenditure_organization_id,
680 -- <PO_PJM_VALIDATION FPI> added 3 parameters:
681 x_ship_to_organization_id,
682 x_need_by_date,
683 x_promised_date,
684 x_expenditure_item_date,
685 p_ship_to_ou_id, --< Bug 3265539 >
686 x_deliver_to_person_id,--<Bug 3793395>
687 x_valid, --<Bug 14662559>
688 x_msg_name --<Bug 14662559>
689 );
690 -- Bug 2892199 Added expenditure_item_date
691 if x_valid <> 'Y' then
692 po_interface_errors_sv1.handle_interface_errors(
693 'PO_DOCS_OPEN_INTERFACE',
694 'FATAL',
695 null,
696 x_interface_header_id,
697 x_interface_line_id,
698 --need to create this message
699 'PO_PDOI_INVALID_PROJ_INFO',
700 'PO_DISTRIBUTIONS_INTERFACE',
701 'PROJECT_ID' ,
702 /*null,*/ 'PJM_ERROR_MSG', -- <PO_PJM_VALIDATION FPI>
703 null,null,null,null,null,
704 -- Pass in the PJM error message, if one exists
705 /*null,*/ NVL(fnd_message.get,''), -- <PO_PJM_VALIDATION FPI>
706 null,null,null,null,null,
707 x_header_processable_flag, x_interface_distribution_id);
708 end if;
709 end if;
710
711 -- Bug 3379488 Start: When there is a transaction flow between POU and ROU
712 -- and exists a project information on the distribution for expense
713 -- destination, prevent creation of the PO
714
715 x_progress := '235';
716 IF (x_destination_type_code = 'EXPENSE')
717 AND (p_transaction_flow_header_id IS NOT NULL)
718 AND (x_project_id IS NOT NULL) THEN
719 PO_INTERFACE_ERRORS_SV1.handle_interface_errors(
720 x_interface_type => 'PO_DOCS_OPEN_INTERFACE',
721 x_error_type => 'FATAL',
722 x_batch_id => null,
723 x_interface_header_id => x_interface_header_id,
724 x_interface_line_id => x_interface_line_id,
725 x_error_message_name => 'PO_CROSS_OU_PA_PROJECT_CHECK',
726 x_table_name => 'PO_DISTRIBUTIONS_INTERFACE',
727 x_column_name => 'PROJECT_ID',
728 x_tokenname1 => NULL,
729 x_tokenname2 => NULL,
730 x_tokenname3 => NULL,
731 x_tokenname4 => NULL,
732 x_tokenname5 => NULL,
733 x_tokenname6 => NULL,
734 x_tokenvalue1 => NULL,
735 x_tokenvalue2 => NULL,
736 x_tokenvalue3 => NULL,
737 x_tokenvalue4 => NULL,
738 x_tokenvalue5 => NULL,
739 x_tokenvalue6 => NULL,
740 x_header_processable_flag=> x_header_processable_flag,
741 x_interface_dist_id => x_interface_distribution_id);
742 END IF;
743
744 -- Bug 3379488 End
745
746 x_progress := '240';
747
748
749 EXCEPTION
750 WHEN others THEN
751 po_message_s.sql_error('validate_po_dist', X_progress, sqlcode);
752 raise;
753
754 END validate_po_dist;
755
756 /*================================================================
757
758 FUNCTION NAME: validate_destination_type_code()
759
760 ==================================================================*/
761
762 FUNCTION validate_destination_type_code(
763 x_destination_type_code IN varchar2,
764 x_item_status in varchar2,
765 x_accrue_on_receipt_flag IN varchar2,
766 p_transaction_flow_header_id IN NUMBER --<Shared Proc FPJ>
767 ) RETURN VARCHAR2 IS
768
769 x_valid_flag VARCHAR2(2);
770 x_expense_accrual_code po_system_parameters.expense_accrual_code%TYPE;
771 x_progress VARCHAR(4) := NULL;
772
773 BEGIN
774
775 x_progress := '001';
776
777 SELECT expense_accrual_code
778 INTO x_expense_accrual_code
779 FROM po_system_parameters;
780
781 -- Business Rules
782 -- item status
783 -- 'O' = outside processing item
784 -- - destination type must be SHOP FLOOR
785 -- 'E' = item stockable in the org
786 -- - destination type cannot be SHOP FLOOR
787 -- 'D' = item defined but not stockable in org
788 -- - destination type must be EXPENSE
789 -- null = item not defined in org
790 --
791 -- accrual on receipt
792 -- 'N' - destination type must be expense
793 -- 'Y' - if expense_accrual = PERIOD END
794 -- then destination type code cannot be EXPENSE
795 --
796
797 x_progress := '002';
798
799 select distinct 'Y' valid
800 into x_valid_flag
801 from po_lookup_codes
802 where lookup_type = 'DESTINATION TYPE'
803 and ( ( nvl( x_item_status,'D') = 'D'
804 and lookup_code = 'EXPENSE')
805 or ( nvl( x_item_status,'D') = 'E'
806 and lookup_code <> 'SHOP FLOOR')
807 or ( nvl( x_item_status,'D') = 'O'
808 and lookup_code = 'SHOP FLOOR') )
809 and ( ( nvl( x_accrue_on_receipt_flag,'Y') = 'N'
810 and lookup_code = 'EXPENSE')
811 OR p_transaction_flow_header_id is NOT NULL --<Shared Proc FPJ>
812 or (nvl( x_accrue_on_receipt_flag,'Y') = 'Y'
813 and (( x_expense_accrual_code = 'PERIOD END'
814 and lookup_code <> 'EXPENSE')
815 or x_expense_accrual_code <> 'PERIOD END')))
816 and lookup_code= x_destination_type_code;
817
818 return x_valid_flag;
819
820 EXCEPTION
821 WHEN NO_DATA_FOUND then
822 x_valid_flag := 'N';
823 return x_valid_flag;
824 WHEN OTHERS THEN
825 x_valid_flag := 'N';
826 return x_valid_flag;
827 po_message_s.sql_error('validate_destination_type_code',X_progress, sqlcode);
828 RAISE;
829
830 END validate_destination_type_code;
831
832 /*================================================================
833
834 FUNCTION NAME: validate_deliver_to_person_id()
835
836 ==================================================================*/
837
838 FUNCTION validate_deliver_to_person_id(
839 x_deliver_to_person_id IN NUMBER
840 ) RETURN VARCHAR2 IS
841 x_valid_flag VARCHAR2(2);
842 x_progress VARCHAR2(4) := NULL;
843
844 BEGIN
845 x_progress := '001';
846
847 -- validation
848 -- R12 CWK: removed where clause based on inactive_date
849 -- as it would taken care in the view itself.
850 SELECT distinct 'Y'
851 INTO x_valid_flag
852 FROM hr_employees_current_v
853 WHERE employee_id = x_deliver_to_person_id;
854
855 return x_valid_flag;
856
857 EXCEPTION
858 WHEN NO_DATA_FOUND then
859 x_valid_flag := 'N';
860 return x_valid_flag;
861 WHEN OTHERS THEN
862 x_valid_flag := 'N';
863 return x_valid_flag;
864 po_message_s.sql_error('validate_deliver_to_person_id',X_progress, sqlcode);
865 RAISE;
866
867 END validate_deliver_to_person_id;
868
869 /*================================================================
870
871 FUNCTION NAME: validate_deliver_to_loc_id()
872
873 ==================================================================*/
874
875 FUNCTION validate_deliver_to_loc_id(
876 x_deliver_to_location_id IN varchar2,
877 x_ship_to_organization_id IN NUMBER
878 ) RETURN VARCHAR2 IS
879
880 x_valid_flag VARCHAR2(2);
881 x_progress VARCHAR2(4) := NULL;
882
883 BEGIN
884
885 x_progress := '001';
886 --Bug# 1942696 togeorge 08/16/2001
887 --HR removes hz_locations from hr_locations; added exception
888 Begin
889 -- validation
890 SELECT distinct 'Y'
891 INTO x_valid_flag
892 FROM HR_LOCATIONS
893 WHERE nvl(inventory_organization_id,x_ship_to_organization_id) = x_ship_to_organization_id
894 AND nvl(inactive_date, trunc(sysdate + 1)) > trunc(sysdate)
895 AND location_id = x_deliver_to_location_id;
896
897 return x_valid_flag;
898 exception
899 WHEN NO_DATA_FOUND then
900 SELECT distinct 'Y'
901 INTO x_valid_flag
902 FROM HZ_LOCATIONS
903 WHERE nvl(address_expiration_date, trunc(sysdate + 1)) > trunc(sysdate)
904 AND location_id = x_deliver_to_location_id;
905
906 return x_valid_flag;
907 end;
908 --
909 EXCEPTION
910 WHEN NO_DATA_FOUND then
911 x_valid_flag := 'N';
912 return x_valid_flag;
913 WHEN OTHERS THEN
914 x_valid_flag := 'N';
915 return x_valid_flag;
916 po_message_s.sql_error('validate_deliver_to_loc_id',X_progress, sqlcode);
917 RAISE;
918
919 END validate_deliver_to_loc_id;
920
921 /*================================================================
922
923 FUNCTION NAME: validate_dest_subinventory()
924
925 ==================================================================*/
926
927 FUNCTION validate_dest_subinventory(
928 x_destination_subinventory IN varchar2,
929 x_ship_to_organization_id IN NUMBER,
930 x_item_id IN NUMBER
931 ) RETURN VARCHAR2 IS
932
933 x_valid_flag VARCHAR2(2);
934 x_progress VARCHAR2(4) := NULL;
935
936 BEGIN
937
938 x_progress := '001';
939
940 -- validation
941 select distinct 'Y'
942 into x_valid_flag
943 from mtl_secondary_inventories msub
944 where msub.organization_id = nvl(x_ship_to_organization_id, msub.organization_id)
945 and nvl(msub.disable_date, trunc(sysdate+1)) > trunc(sysdate)
946 and (x_item_id is null
947 or
948 (x_item_id is not null
949 and exists (select null
950 from mtl_system_items msi
951 where msi.organization_id = nvl(x_ship_to_organization_id, msi.organization_id)
952 and msi.inventory_item_id = x_item_id
953 and (msi.restrict_subinventories_code = 2
954 or (msi.restrict_subinventories_code = 1
955 and exists (select null
956 from mtl_item_sub_inventories mis
957 where mis.organization_id = nvl(x_ship_to_organization_id , mis.organization_id)
958 and mis.inventory_item_id = msi.inventory_item_id
959 and mis.secondary_inventory = msub.secondary_inventory_name))))))
960 and msub.secondary_inventory_name = x_destination_subinventory;
961
962 return x_valid_flag;
963
964 EXCEPTION
965 WHEN NO_DATA_FOUND then
966 x_valid_flag := 'N';
967 return x_valid_flag;
968 WHEN OTHERS THEN
969 x_valid_flag := 'N';
970 return x_valid_flag;
971 po_message_s.sql_error('validate_dest_subinventory',X_progress, sqlcode);
972 RAISE;
973
974 END validate_dest_subinventory;
975
976 /*================================================================
977
978 FUNCTION NAME: validate_org()
979
980 ==================================================================*/
981
982 FUNCTION validate_org(x_org_id in NUMBER, x_sob_id in NUMBER)
983 RETURN VARCHAR2 IS
984
985 x_valid VARCHAR2(2);
986 x_progress VARCHAR2(4) := NULL;
987
988 BEGIN
989
990 x_progress := '001';
991
992 SELECT distinct 'Y' INTO x_valid
993 FROM org_organization_definitions ood
994 WHERE x_org_id = ood.organization_id
995 AND ood.set_of_books_id = x_sob_id;
996
997 return x_valid;
998
999 EXCEPTION
1000 WHEN NO_DATA_FOUND then
1001 x_valid := 'N';
1002 return x_valid;
1003 WHEN OTHERS THEN
1004 x_valid := 'N';
1005 return x_valid;
1006 po_message_s.sql_error('validate_org',X_progress, sqlcode);
1007 RAISE;
1008
1009 END validate_org;
1010
1011 /*================================================================
1012
1013 FUNCTION NAME: validate_wip()
1014
1015 ==================================================================*/
1016
1017 FUNCTION validate_wip(x_wip_entity_id in NUMBER, x_destination_organization_id in NUMBER, x_wip_repetitive_schedule_id in NUMBER) RETURN VARCHAR2 IS
1018
1019 x_valid VARCHAR2(2);
1020 x_valid1 VARCHAR2(2);
1021 x_valid2 VARCHAR2(2);
1022 x_progress VARCHAR2(4) := NULL;
1023
1024 BEGIN
1025
1026 x_progress := '001';
1027
1028 /* If the destination_type_code = 'SHOP FLOOR', then if */
1029 /* WIP_REPETITIVE_SCHEDULE_ID is not null then the record must be a */
1030 /* repetitive_schedule. If WIP_REPETITIVE_SCHEDULE_ID is NULL, then it */
1031 /* must be a discrete job */
1032 -- Bug 3083961. If x_wip_repetitive_schedule_id is not null check in wip_repetitive_schedules.
1033 -- else check in wip_discrete_jobs
1034
1035 if x_wip_repetitive_schedule_id is not null then
1036 begin
1037 SELECT distinct 'Y' into x_valid
1038 FROM wip_repetitive_schedules wrs
1039 WHERE wrs.organization_id=x_destination_organization_id
1040 AND wrs.wip_entity_id = x_wip_entity_id
1041 AND wrs.repetitive_schedule_id =
1042 x_wip_repetitive_schedule_id
1043 AND wrs.status_type IN (3,4,6)
1044 AND x_wip_repetitive_schedule_id is not null;
1045 exception
1046 WHEN NO_DATA_FOUND then
1047 x_valid := 'N';
1048 return x_valid;
1049 end;
1050 else
1051 begin
1052 SELECT distinct 'Y' into x_valid
1053 FROM wip_discrete_jobs wdj
1054 WHERE wdj.organization_id=x_destination_organization_id
1055 AND wdj.wip_entity_id = x_wip_entity_id
1056 AND wdj.status_type IN (3,4,6)
1057 AND x_wip_repetitive_schedule_id is NULL;
1058 exception
1059 WHEN NO_DATA_FOUND then
1060 x_valid := 'N';
1061 return x_valid;
1062 end;
1063 end if;
1064
1065 return x_valid;
1066
1067 EXCEPTION
1068 WHEN NO_DATA_FOUND then
1069 x_valid := 'N';
1070 return x_valid;
1071 WHEN OTHERS THEN
1072 x_valid := 'N';
1073 return x_valid;
1074 po_message_s.sql_error('validate_wip',X_progress, sqlcode);
1075 RAISE;
1076
1077 END validate_wip;
1078
1079 /*================================================================
1080
1081 FUNCTION NAME: validate_account()
1082
1083 ==================================================================*/
1084
1085 FUNCTION validate_account(x_account_id in NUMBER, x_gl_date in date, x_chart_of_accounts_id in NUMBER) RETURN VARCHAR2 IS
1086
1087 x_valid VARCHAR2(2);
1088 x_progress VARCHAR2(4) := NULL;
1089
1090 BEGIN
1091
1092 x_progress := '001';
1093
1094 SELECT distinct 'Y' into x_valid
1095 FROM gl_code_combinations gcc
1096 WHERE gcc.code_combination_id = x_account_id
1097 AND gcc.enabled_flag = 'Y'
1098 AND trunc(nvl(x_gl_date,SYSDATE)) BETWEEN
1099 trunc(nvl(start_date_active,
1100 nvl(x_gl_date,SYSDATE) ))
1101 AND
1102 trunc(nvl (end_date_active,
1103 nvl(x_gl_date,SYSDATE) ))
1104 AND gcc.detail_posting_allowed_flag = 'Y'
1105 AND gcc.chart_of_accounts_id=
1106 x_chart_of_accounts_id
1107 AND gcc.summary_flag = 'N';
1108
1109 return x_valid;
1110
1111 EXCEPTION
1112 WHEN NO_DATA_FOUND then
1113 x_valid := 'N';
1114 return x_valid;
1115 WHEN OTHERS THEN
1116 x_valid := 'N';
1117 return x_valid;
1118 po_message_s.sql_error('validate_account',X_progress, sqlcode);
1119 RAISE;
1120
1121 END validate_account;
1122
1123 /*================================================================
1124
1125 FUNCTION NAME: validate_project_info()
1126
1127 ==================================================================*/
1128
1129 /**
1130 * Private Procedure: validate_project_info
1131 * Requires: none
1132 * Modifies: concurrent program log
1133 * Effects: Calls the PJM validation API with the given project, task,
1134 * etc. Writes validation warnings to the concurrent program log.
1135 * Returns:
1136 * 'N' if validation failed. This distribution becomes invalid.
1137 * 'Y' if the validation result is success or warning. Processing should
1138 * continue on this distribution.
1139 */
1140 PROCEDURE validate_project_info
1141 (
1142 x_destination_type_code IN VARCHAR2,
1143 x_project_id IN NUMBER,
1144 x_task_id IN NUMBER,
1145 x_expenditure_type IN VARCHAR2,
1146 x_expenditure_organization_id IN NUMBER ,
1147 -- <PO_PJM_VALIDATION FPI START>
1148 x_ship_to_organization_id IN NUMBER,
1149 x_need_by_date IN DATE,
1150 x_promised_date IN DATE,
1151 x_expenditure_item_date IN DATE, -- Bug 2892199
1152 -- <PO_PJM_VALIDATION FPI END>
1153 p_ship_to_ou_id IN NUMBER, --< Bug 3265539 >
1154 p_deliver_to_person_id IN NUMBER, --<Bug 3793395>
1155 x_valid OUT NOCOPY VARCHAR2, --<Bug 14662559>
1156 x_msg_name OUT NOCOPY VARCHAR2 --<Bug 14662559>
1157 ) IS
1158
1159 --x_valid VARCHAR2(2); --<Bug 14662559>
1160 x_valid1 VARCHAR2(2);
1161 x_valid2 VARCHAR2(2);
1162 x_valid3 VARCHAR2(2);
1163 x_progress VARCHAR2(4) := NULL;
1164
1165 -- <PO_PJM_VALIDATION FPI START>
1166 l_val_proj_result VARCHAR(1);
1167 l_val_proj_error_code VARCHAR2(80);
1168 -- <PO_PJM_VALIDATION FPI END>
1169
1170 --<Bug 3793395 mbhargav START>
1171 x_msg_application varchar2(30);
1172 x_msg_type varchar2(1);
1173 x_msg_token1 varchar2(30);
1174 x_msg_token2 varchar2(30);
1175 x_msg_token3 varchar2(30);
1176 x_msg_count number;
1177 x_msg_data varchar2(30);
1178 x_billable_flag varchar2(1);
1179 --<Bug 3793395 mbhargav END>
1180
1181 BEGIN
1182
1183 x_progress := '001';
1184
1185 /* Bug: 1786105 For all the following three validation select, the table/views:
1186 mtl_projects_v
1187 pa_expenditure_types
1188 per_organization_units
1189 needs to be replaced with
1190 pa_projects_expend_v
1191 pa_expenditure_types_expend_v
1192 pa_organizations_expend_v
1193 or else the projects validation would fail.
1194 */
1195 /* Bug 2892199 Added expenditure item date validation */
1196 IF x_destination_type_code = 'EXPENSE' then
1197 --bug 14662559: set 3 different message name for these 3 validation.
1198 begin
1199 SELECT 'Y' into x_valid1
1200 FROM pa_projects_expend_v pap,
1201 pa_tasks_expend_v pat
1202 WHERE pap.project_id = x_project_id
1203 AND pap.project_id = pat.project_id
1204 AND pat.task_id = x_task_id
1205 AND pat.chargeable_flag = 'Y'
1206 AND x_expenditure_item_date BETWEEN
1207 nvl(pap.start_date,x_expenditure_item_date)
1208 AND nvl(pap.completion_date,x_expenditure_item_date)
1209 AND x_expenditure_item_date BETWEEN
1210 nvl(pat.start_date,x_expenditure_item_date)
1211 AND nvl(pat.completion_date,x_expenditure_item_date) ;
1212 exception
1213 when no_data_found then
1214 x_valid1 := 'N';
1215 end;
1216
1217 begin
1218 SELECT 'Y' into x_valid2
1219 FROM pa_expenditure_types_expend_v pet
1220 WHERE pet.expenditure_type = x_expenditure_type
1221 AND pet.system_linkage_function = 'VI';
1222 exception
1223 when no_data_found then
1224 x_valid2 := 'N';
1225 end;
1226
1227 begin
1228 SELECT 'Y' into x_valid3
1229 FROM pa_organizations_expend_v pou
1230 WHERE pou.organization_id=x_expenditure_organization_id;
1231 exception
1232 when no_data_found then
1233 x_valid3 := 'N';
1234 end;
1235
1236 if x_valid1 = 'Y' and x_valid2 = 'Y' and x_valid3 = 'Y' then
1237
1238 --<Bug 3793395 mbhargav START>
1239 --Call the PA API to validate project related information
1240 pa_transactions_pub.validate_transaction( X_project_id => x_project_id
1241 , X_task_id => x_task_id
1242 , X_ei_date => x_expenditure_item_date
1243 , X_expenditure_type => x_expenditure_type
1244 , X_non_labor_resource => ''
1245 , X_person_id => p_deliver_to_person_id
1246 , X_quantity => ''
1247 , X_denom_currency_code => ''
1248 , X_acct_currency_code => ''
1249 , X_denom_raw_cost => ''
1250 , X_acct_raw_cost => ''
1251 , X_acct_rate_type => ''
1252 , X_acct_rate_date => ''
1253 , X_acct_exchange_rate => ''
1254 , X_transfer_ei => ''
1255 , X_incurred_by_org_id => x_expenditure_organization_id
1256 , X_nl_resource_org_id => ''
1257 , X_transaction_source => ''
1258 , X_calling_module => 'POXPOEPO'
1259 , X_vendor_id => ''
1260 , X_entered_by_user_id => ''
1261 , X_attribute_category => ''
1262 , X_attribute1 => ''
1263 , X_attribute2 => ''
1264 , X_attribute3 => ''
1265 , X_attribute4 => ''
1266 , X_attribute5 => ''
1267 , X_attribute6 => ''
1268 , X_attribute7 => ''
1269 , X_attribute8 => ''
1270 , X_attribute9 => ''
1271 , X_attribute10 => ''
1272 , X_attribute11 => ''
1273 , X_attribute12 => ''
1274 , X_attribute13 => ''
1275 , X_attribute14 => ''
1276 , X_attribute15 => ''
1277 , X_msg_application => X_msg_application
1278 , X_msg_type => X_msg_type
1279 , X_msg_token1 => X_msg_token1
1280 , X_msg_token2 => X_msg_token2
1281 , X_msg_token3 => X_msg_token3
1282 , X_msg_count => X_msg_count
1283 , X_msg_data => X_msg_data
1284 , X_billable_flag => X_billable_flag);
1285
1286 x_msg_name := x_msg_data; --<Bug 14662559>
1287 IF x_msg_type = 'E' and x_msg_data is not NULL THEN
1288 --Project related info causes error. Stop processing
1289 FND_FILE.put_line(FND_FILE.LOG, x_msg_data);
1290 x_valid := 'N';
1291 ELSIF x_msg_type = 'W' and x_msg_data is not NULL THEN
1292 /* Write the warning to the concurrent program log and then */
1293 /* set x_valid to 'Y' to allow processing to continue.*/
1294 FND_FILE.put_line(FND_FILE.LOG, x_msg_data);
1295 x_valid := 'Y';
1296 ELSE
1297 x_valid := 'Y';
1298 END IF;
1299 --<Bug 3793395 mbhargav END>
1300
1301 else
1302 x_valid := 'N';
1303 --bug 14662559: set 3 different message name for these 3 validation.
1304 if x_valid2 = 'N' then
1305 x_msg_name := 'PO_PDOI_INVALID_EXPEND_TYPE';
1306 elsif x_valid3 = 'N' then
1307 x_msg_name := 'PO_PDOI_INVALID_EXPEND_ORG';
1308 elsif x_valid1 = 'N' then
1309 x_msg_name := 'PA_EXP_TASK_EFF';
1310 end if;
1311 end if;
1312 else
1313 -- <PO_PJM_VALIDATION FPI START>
1314 --< Bug 3265539 Start >
1315 -- Call PO wrapper procedure to validate the PJM project
1316 PO_PROJECT_DETAILS_SV.validate_proj_references_wpr
1317 (p_inventory_org_id => x_ship_to_organization_id,
1318 p_operating_unit => p_ship_to_ou_id,
1319 p_project_id => x_project_id,
1320 p_task_id => x_task_id,
1321 p_date1 => x_need_by_date,
1322 p_date2 => x_promised_date,
1323 p_calling_function => 'PDOI',
1324 x_error_code => l_val_proj_error_code,
1325 x_return_code => l_val_proj_result);
1326
1327 IF (l_val_proj_result = PO_PROJECT_DETAILS_SV.pjm_validate_failure) THEN
1328 x_valid1 := 'N';
1329 ELSIF (l_val_proj_result = PO_PROJECT_DETAILS_SV.pjm_validate_warning) THEN
1330 /* Write the warning to the concurrent program log and then */
1331 /* set x_valid to 'Y' to allow processing to continue.*/
1332 FND_FILE.put_line(FND_FILE.LOG, FND_MESSAGE.get);
1333 x_valid1 := 'Y';
1334 ELSE /* Success */
1335 x_valid1 := 'Y';
1336 END IF;
1337 --< Bug 3265539 End >
1338 -- <PO_PJM_VALIDATION FPI END>
1339 IF x_destination_type_code = 'INVENTORY' then
1340
1341 /* bug 9412338/9831707 The expenditure_type was being validated with
1342 respect to the pa_expenditure_types, However it should be validated
1343 with respect to pa_expend_typ_sys_links.Because of this the project
1344 validations were failing.Added the table pa_expend_typ_sys_links to the
1345 from clause ,join condition in the where clause.System_linkage_function
1346 should be validated with the value in pa_expend_typ_sys_links table.*/
1347
1348 SELECT 'Y' into x_valid2
1349 FROM sys.dual
1350 WHERE x_expenditure_type IS NULL
1351 OR EXISTS( SELECT 'Valid Expenditure Type'
1352 FROM pa_expenditure_types pet,
1353 pa_expend_typ_sys_links sl -- Bug 9412338/9831707
1354 WHERE pet.expenditure_type = x_expenditure_type
1355 AND pet.expenditure_type = sl.expenditure_type --bug9412338/9831707
1356 AND sl.system_linkage_function = 'VI' --bug9412338/9831707
1357 );
1358
1359 /* Bug # 1609762
1360 When the Destination Type is INVENTORY then the expenditure Org can
1361 be NULL. */
1362
1363 SELECT 'Y' into x_valid3
1364 FROM sys.dual
1365 WHERE x_expenditure_organization_id IS NULL
1366 OR EXISTS( SELECT 'Valid Expenditure Org'
1367 FROM per_organization_units pou
1368 WHERE pou.organization_id=
1369 x_expenditure_organization_id
1370 );
1371
1372 if x_valid1 = 'Y' and x_valid2 = 'Y' and x_valid3 = 'Y' then
1373 x_valid := 'Y';
1374 else
1375 x_valid := 'N';
1376 end if;
1377 end if;
1378 end if;
1379
1380 --return x_valid;
1381
1382 EXCEPTION
1383 WHEN NO_DATA_FOUND then
1384 x_valid := 'N';
1385 --return x_valid;
1386 WHEN OTHERS THEN
1387 x_valid := 'N';
1388 --return x_valid;
1389 po_message_s.sql_error('validate_project_info',X_progress, sqlcode);
1390 RAISE;
1391
1392 end validate_project_info;
1393
1394 END PO_PDOI_DISTRIBUTIONS_SV3;