[Home] [Help]
PACKAGE BODY: APPS.PO_COPYDOC_SUB
Source
1 PACKAGE BODY po_copydoc_sub AS
2 /* $Header: POXCPSUB.pls 120.6.12010000.2 2008/09/15 12:24:57 lswamina ship $*/
3
4 -- Cursor definitions:
5
6 CURSOR po_line_cursor(x_po_header_id po_lines.po_header_id%TYPE) IS
7 SELECT *
8 FROM PO_LINES
9 WHERE po_header_id = x_po_header_id
10 ORDER BY line_num;
11
12 /* Bug# 3528563, We only need to consider the PRICE BREAKS while
13 * validating Blankets. Without the condition on the shipment_type
14 * it was also picking the Release while doing the validations since
15 * Blankets and Releases share the po_line_id in PO_LINES_ALL.
16 * This happened when Releases were entered for a BPA and the BPA
17 * had to be re-approved after being REJECTED.
18 * The same has to be done for Scheculed Relases too. */
19 CURSOR po_shipment_cursor(x_po_line_id po_line_locations.po_line_id%TYPE) IS
20 SELECT *
21 FROM PO_LINE_LOCATIONS
22 WHERE po_line_id = x_po_line_id
23 AND shipment_type not in ('BLANKET','SCHEDULED')
24 ORDER BY shipment_num;
25
26 CURSOR po_distribution_cursor(x_line_location_id po_distributions.line_location_id%TYPE) IS
27 SELECT *
28 FROM PO_DISTRIBUTIONS POD
29 WHERE POD.line_location_id = x_line_location_id
30 AND POD.distribution_type <> 'AGREEMENT' --<ENCUMBRANCE FPJ>
31 ORDER BY distribution_num;
32
33 --<ENCUMBRANCE FPJ: added new cursor for encumbered BPA dists>
34 CURSOR pa_distribution_cursor(x_po_header_id po_distributions.po_header_id%TYPE) IS
35 SELECT *
36 FROM PO_DISTRIBUTIONS POD
37 WHERE POD.po_header_id = x_po_header_id
38 AND POD.distribution_type = 'AGREEMENT'
39 ORDER BY distribution_num;
40
41 -- end of cursor definitions
42
43 -- private functions
44
45 PROCEDURE get_fsp_values(
46 x_ship_to_location_id IN OUT NOCOPY po_headers.ship_to_location_id%TYPE,
47 x_bill_to_location_id IN OUT NOCOPY po_headers.bill_to_location_id%TYPE,
48 x_ship_via_lookup_code IN OUT NOCOPY po_headers.ship_via_lookup_code%TYPE,
49 x_fob_lookup_code IN OUT NOCOPY po_headers.fob_lookup_code%TYPE,
50 x_freight_terms_lu_code IN OUT NOCOPY po_headers.freight_terms_lookup_code%TYPE,
51 x_terms_id IN OUT NOCOPY po_headers.terms_id%TYPE,
52 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
53 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE
54 );
55
56 PROCEDURE get_vendor_values(
57 x_vendor_id IN po_headers.vendor_id%TYPE,
58 x_ship_to_location_id IN OUT NOCOPY po_headers.ship_to_location_id%TYPE,
59 x_bill_to_location_id IN OUT NOCOPY po_headers.bill_to_location_id%TYPE,
60 x_ship_via_lookup_code IN OUT NOCOPY po_headers.ship_via_lookup_code%TYPE,
61 x_fob_lookup_code IN OUT NOCOPY po_headers.fob_lookup_code%TYPE,
62 x_freight_terms_lu_code IN OUT NOCOPY po_headers.freight_terms_lookup_code%TYPE,
63 x_terms_id IN OUT NOCOPY po_headers.terms_id%TYPE,
64 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
65 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE
66 );
67
68 PROCEDURE get_vendor_site_values(
69 x_vendor_id IN po_headers.vendor_id%TYPE,
70 x_vendor_site_id IN po_headers.vendor_site_id%TYPE,
71 x_ship_to_location_id IN OUT NOCOPY po_headers.ship_to_location_id%TYPE,
72 x_bill_to_location_id IN OUT NOCOPY po_headers.bill_to_location_id%TYPE,
73 x_ship_via_lookup_code IN OUT NOCOPY po_headers.ship_via_lookup_code%TYPE,
74 x_fob_lookup_code IN OUT NOCOPY po_headers.fob_lookup_code%TYPE,
75 x_freight_terms_lu_code IN OUT NOCOPY po_headers.freight_terms_lookup_code%TYPE,
76 x_terms_id IN OUT NOCOPY po_headers.terms_id%TYPE,
77 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
78 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE
79 );
80
81 PROCEDURE validate_buyer_id(
82 x_buyer_id IN OUT NOCOPY po_headers.agent_id%TYPE,
83 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
84 x_sequence IN OUT NOCOPY po_online_report_text.line_num%TYPE
85 );
86
87
88 PROCEDURE validate_vendor(
89 x_sob_id IN financials_system_parameters.set_of_books_id%TYPE,
90 x_inv_org_id IN financials_system_parameters.inventory_organization_id%TYPE,
91 x_vendor_id IN OUT NOCOPY po_headers.vendor_id%TYPE,
92 x_vendor_site_id IN OUT NOCOPY po_headers.vendor_site_id%TYPE,
93 x_vendor_contact_id IN OUT NOCOPY po_headers.vendor_contact_id%TYPE,
94 x_ship_to_location_id IN OUT NOCOPY po_headers.ship_to_location_id%TYPE,
95 x_bill_to_location_id IN OUT NOCOPY po_headers.bill_to_location_id%TYPE,
96 x_ship_via_lookup_code IN OUT NOCOPY po_headers.ship_via_lookup_code%TYPE,
97 x_fob_lookup_code IN OUT NOCOPY po_headers.fob_lookup_code%TYPE,
98 x_freight_terms_lu_code IN OUT NOCOPY po_headers.freight_terms_lookup_code%TYPE,
99 x_terms_id IN OUT NOCOPY po_headers.terms_id%TYPE,
100 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
101 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE
102 );
103
104 PROCEDURE validate_location_terms(
105 x_sob_id IN financials_system_parameters.set_of_books_id%TYPE,
106 x_ship_to_location_id IN OUT NOCOPY po_headers.ship_to_location_id%TYPE,
107 x_bill_to_location_id IN OUT NOCOPY po_headers.bill_to_location_id%TYPE,
108 x_fob_lookup_code IN OUT NOCOPY po_headers.fob_lookup_code%TYPE,
109 x_freight_terms_lu_code IN OUT NOCOPY po_headers.freight_terms_lookup_code%TYPE,
110 x_terms_id IN OUT NOCOPY po_headers.terms_id%TYPE,
111 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
112 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE
113 );
114
115 PROCEDURE validate_item(
116 x_item_id IN OUT NOCOPY po_lines.item_id%TYPE,
117 x_item_description IN OUT NOCOPY po_lines.item_description%TYPE,
118 x_item_revision IN OUT NOCOPY po_lines.item_revision%TYPE,
119 x_category_id IN OUT NOCOPY po_lines.category_id%TYPE,
120 x_line_type_id IN po_lines.line_type_id%TYPE,
121 x_inv_org_id IN financials_system_parameters.inventory_organization_id%TYPE,
122 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
123 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
124 x_line_num IN po_online_report_text.line_num%TYPE,
125 x_return_code OUT NOCOPY NUMBER
126 );
127
128 PROCEDURE validate_project_id(
129 x_project_id IN OUT NOCOPY po_distributions.project_id%TYPE,
130 -- <PO_PJM_VALIDATION FPI>
131 -- Removed the x_destination_type_code and x_ship_to_organization_id arguments.
132 -- Added NOCOPY to x_project_id and x_sequence.
133 -- x_destination_type_code IN po_distributions.destination_type_code%TYPE,
134 -- x_ship_to_organization_id IN NUMBER,
135 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
136 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
137 x_line_num IN po_online_report_text.line_num%TYPE,
138 x_shipment_num IN po_online_report_text.shipment_num%TYPE,
139 x_distribution_num IN po_online_report_text.distribution_num%TYPE
140 );
141
142 PROCEDURE validate_task_id(
143 x_task_id IN OUT NOCOPY NUMBER,
144 x_project_id IN NUMBER,
145 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
146 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
147 x_line_num IN po_online_report_text.line_num%TYPE,
148 x_shipment_num IN po_online_report_text.shipment_num%TYPE,
149 x_distribution_num IN po_online_report_text.distribution_num%TYPE
150 );
151
152 PROCEDURE validate_account_id(
153 x_account_id IN OUT NOCOPY NUMBER,
154 x_account_type IN VARCHAR2,
155 x_gl_date IN DATE,
156 x_sob_id IN financials_system_parameters.set_of_books_id%TYPE,
157 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
158 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
159 x_line_num IN po_online_report_text.line_num%TYPE,
160 x_shipment_num IN po_online_report_text.shipment_num%TYPE,
161 x_distribution_num IN po_online_report_text.distribution_num%TYPE
162 );
163
164 PROCEDURE validate_destination_type_code(
165 x_destination_type_code IN OUT NOCOPY po_distributions.destination_type_code%TYPE,
166 x_item_id IN po_lines.item_id%TYPE,
167 x_ship_org_id IN financials_system_parameters.inventory_organization_id%TYPE,
168 x_accrue_on_receipt_flag IN varchar2,
169 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
170 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
171 x_line_num IN po_online_report_text.line_num%TYPE,
172 x_shipment_num IN po_online_report_text.shipment_num%TYPE,
173 x_distribution_num IN po_online_report_text.distribution_num%TYPE,
174 x_line_location_id IN po_line_locations.line_location_id%TYPE, -- CONSIGNED FPI START
175 x_po_line_id IN po_lines.po_line_id%TYPE, -- Bug 3557910 Additional Input Parameter PO LINE ID
176 p_transaction_flow_header_id IN NUMBER --< Bug 3546252 >
177 );
178
179 PROCEDURE validate_deliver_to_person_id(
180 x_deliver_to_person_id IN OUT NOCOPY po_distributions.deliver_to_person_id%TYPE,
181 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
182 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
183 x_line_num IN po_online_report_text.line_num%TYPE,
184 x_shipment_num IN po_online_report_text.shipment_num%TYPE,
185 x_distribution_num IN po_online_report_text.distribution_num%TYPE
186 );
187
188 PROCEDURE validate_osp_data( x_wip_entity_id IN NUMBER,
189 x_wip_operation_seq_num IN NUMBER,
190 x_wip_resource_seq_num IN NUMBER,
191 x_wip_repetitive_schedule_id IN NUMBER,
192 x_wip_line_id IN NUMBER,
193 x_destination_organization_id IN NUMBER,
194 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
195 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
196 x_line_num IN po_online_report_text.line_num%TYPE,
197 x_shipment_num IN po_online_report_text.shipment_num%TYPE,
198 x_distribution_num IN po_online_report_text.distribution_num%TYPE);
199
200 PROCEDURE validate_deliver_to_loc_id(
201 x_deliver_to_location_id IN OUT NOCOPY po_distributions.deliver_to_location_id%TYPE,
202 x_ship_to_organization_id IN NUMBER,
203 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
204 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
205 x_line_num IN po_online_report_text.line_num%TYPE,
206 x_shipment_num IN po_online_report_text.shipment_num%TYPE,
207 x_distribution_num IN po_online_report_text.distribution_num%TYPE
208 );
209
210 PROCEDURE validate_dest_subinventory(
211 x_destination_subinventory IN OUT NOCOPY po_distributions.destination_subinventory%TYPE,
212 x_ship_to_organization_id IN NUMBER,
213 x_item_id IN po_lines.item_id%TYPE,
214 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
215 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
216 x_line_num IN po_online_report_text.line_num%TYPE,
217 x_shipment_num IN po_online_report_text.shipment_num%TYPE,
218 x_distribution_num IN po_online_report_text.distribution_num%TYPE
219 );
220
221 --<ENCUMBRANCE FPJ : removed procedure validate_gl_encumbered_date>
222 -- this is now added to regular submission check (POXVDCKB.pls 115.30)
223
224 PROCEDURE validate_contract_num(
225 p_contract_id IN PO_LINES_ALL.contract_id%TYPE, -- <GC FPJ>
226 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
227 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
228 x_line_num IN po_online_report_text.line_num%TYPE
229 );
230
231 --<Bug 2864544 mbhargav START>
232 --Procedure that validate that the referenced global agreement is not cancelled or
233 --finally closed
234 PROCEDURE validate_global_ref(
235 p_from_header_id IN po_headers_all.po_header_id%TYPE,
236 p_from_line_id IN po_lines_all.po_line_id%TYPE,
237 p_online_report_id IN po_online_report_text.online_report_id%TYPE,
238 p_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
239 p_line_num IN po_online_report_text.line_num%TYPE
240 );
241 --<Bug 2864544 mbhargav END>
242
243 -- SERVICES FPJ Start
244 -- Procedure to validate the temp labor Job on PO lines
245
246 PROCEDURE validate_job(
247 p_job_id IN po_lines.job_id%TYPE,
248 p_online_report_id IN po_online_report_text.online_report_id%TYPE,
249 p_line_num IN po_online_report_text.line_num%TYPE,
250 p_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE
251 );
252
253 -- SERVICES FPJ End
254
255 --< Shared Proc FPJ Start >
256 PROCEDURE validate_transaction_flow
257 (
258 p_ship_to_org_id IN NUMBER,
259 p_transaction_flow_header_id IN NUMBER,
260 p_item_category_id IN NUMBER,
261 p_online_report_id IN NUMBER,
262 p_line_num IN NUMBER,
263 p_shipment_num IN NUMBER,
264 p_item_id IN NUMBER, -- Bug 3433867
265 x_sequence IN OUT NOCOPY NUMBER
266 );
267
268 PROCEDURE validate_org_assignments
269 (
270 p_po_header_id IN NUMBER,
271 p_vendor_id IN NUMBER,
272 p_online_report_id IN NUMBER,
273 x_sequence IN OUT NOCOPY NUMBER
274 );
275
276 PROCEDURE populate_session_gt
277 (
278 x_return_status OUT NOCOPY VARCHAR2,
279 p_po_header_id IN NUMBER,
280 p_online_report_id IN NUMBER,
281 x_sequence IN OUT NOCOPY NUMBER,
282 x_key OUT NOCOPY NUMBER
283 );
284 --< Shared Proc FPJ End >
285
286
287 -- Bug 3488117: Added validation procedures for expenditure fields
288
289 PROCEDURE validate_exp_item_date(
290 x_project_id IN NUMBER,
291 x_task_id IN NUMBER,
292 x_exp_item_date IN OUT NOCOPY DATE,
293 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
294 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
295 x_line_num IN po_online_report_text.line_num%TYPE,
296 x_shipment_num IN po_online_report_text.shipment_num%TYPE,
297 x_distribution_num IN po_online_report_text.distribution_num%TYPE
298 );
299
300 PROCEDURE validate_exp_type(
301 x_project_id IN NUMBER,
302 x_exp_type IN OUT NOCOPY po_distributions.expenditure_type%TYPE,
303 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
304 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
305 x_line_num IN po_online_report_text.line_num%TYPE,
306 x_shipment_num IN po_online_report_text.shipment_num%TYPE,
307 x_distribution_num IN po_online_report_text.distribution_num%TYPE
308 );
309
310 PROCEDURE validate_exp_org(
311 x_org_id IN OUT NOCOPY NUMBER,
312 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
313 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
314 x_line_num IN po_online_report_text.line_num%TYPE,
315 x_shipment_num IN po_online_report_text.shipment_num%TYPE,
316 x_distribution_num IN po_online_report_text.distribution_num%TYPE
317 );
318
319 -- End Bug 3488117
320
321
322 -- end private function declarations
323
324 PROCEDURE get_fsp_values(
325 x_ship_to_location_id IN OUT NOCOPY po_headers.ship_to_location_id%TYPE,
326 x_bill_to_location_id IN OUT NOCOPY po_headers.bill_to_location_id%TYPE,
327 x_ship_via_lookup_code IN OUT NOCOPY po_headers.ship_via_lookup_code%TYPE,
328 x_fob_lookup_code IN OUT NOCOPY po_headers.fob_lookup_code%TYPE,
329 x_freight_terms_lu_code IN OUT NOCOPY po_headers.freight_terms_lookup_code%TYPE,
330 x_terms_id IN OUT NOCOPY po_headers.terms_id%TYPE,
331 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
332 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE
333 ) IS
334
335 x_progress VARCHAR2(4) := NULL;
336
337 BEGIN
338
339 x_progress := '001';
340
341 SELECT fsp.ship_to_location_id,
342 fsp.bill_to_location_id,
343 fsp.ship_via_lookup_code,
344 fsp.fob_lookup_code,
345 fsp.freight_terms_lookup_code,
346 fsp.terms_id
347 INTO x_ship_to_location_id,
348 x_bill_to_location_id,
349 x_ship_via_lookup_code,
350 x_fob_lookup_code,
351 x_freight_terms_lu_code,
352 x_terms_id
353 FROM FINANCIALS_SYSTEM_PARAMETERS fsp;
354
355 EXCEPTION
356 WHEN NO_DATA_FOUND THEN
357 x_ship_to_location_id := NULL;
358 x_bill_to_location_id := NULL;
359 x_ship_via_lookup_code := NULL;
360 x_fob_lookup_code := NULL;
361 x_freight_terms_lu_code := NULL;
362 x_terms_id := NULL;
363 fnd_message.set_name('PO', 'PO_MISSING_FSP_VALUES');
364 po_copydoc_s1.online_report(x_online_report_id,
365 x_sequence,
366 substr(fnd_message.get, 1, 240),
367 0, 0, 0);
368 WHEN OTHERS THEN
369 x_ship_to_location_id := NULL;
370 x_bill_to_location_id := NULL;
371 x_ship_via_lookup_code := NULL;
372 x_fob_lookup_code := NULL;
373 x_freight_terms_lu_code := NULL;
374 x_terms_id := NULL;
375 po_copydoc_s1.copydoc_sql_error('get_fsp_values', x_progress, sqlcode,
376 x_online_report_id,
377 x_sequence,
378 0, 0, 0);
379 END get_fsp_values;
380
381
382 PROCEDURE get_vendor_values(
383 x_vendor_id IN po_headers.vendor_id%TYPE,
384 x_ship_to_location_id IN OUT NOCOPY po_headers.ship_to_location_id%TYPE,
385 x_bill_to_location_id IN OUT NOCOPY po_headers.bill_to_location_id%TYPE,
386 x_ship_via_lookup_code IN OUT NOCOPY po_headers.ship_via_lookup_code%TYPE,
387 x_fob_lookup_code IN OUT NOCOPY po_headers.fob_lookup_code%TYPE,
388 x_freight_terms_lu_code IN OUT NOCOPY po_headers.freight_terms_lookup_code%TYPE,
389 x_terms_id IN OUT NOCOPY po_headers.terms_id%TYPE,
390 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
391 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE
392 ) IS
393
394 x_progress VARCHAR2(4) := NULL;
395
396 BEGIN
397
398 x_progress := '001';
399
400 -- Bug# 4546121:All columns that referred to the obsolete columns in po_vendors have
401 -- been nulled out.
402 SELECT null,
403 null,
404 null,
405 null,
406 null,
407 terms_id
408 INTO x_ship_to_location_id,
409 x_bill_to_location_id,
410 x_ship_via_lookup_code,
411 x_fob_lookup_code,
412 x_freight_terms_lu_code,
413 x_terms_id
414 FROM PO_VENDORS
415 WHERE vendor_id = x_vendor_id;
416
417 EXCEPTION
418 WHEN NO_DATA_FOUND THEN
419 get_fsp_values(x_ship_to_location_id,
420 x_bill_to_location_id,
421 x_ship_via_lookup_code,
422 x_fob_lookup_code,
423 x_freight_terms_lu_code,
424 x_terms_id,
425 x_online_report_id,
426 x_sequence);
427 WHEN OTHERS THEN
428 po_copydoc_s1.copydoc_sql_error('get_vendor_values', x_progress, sqlcode,
429 x_online_report_id,
430 x_sequence,
431 0, 0, 0);
432 get_fsp_values(x_ship_to_location_id,
433 x_bill_to_location_id,
434 x_ship_via_lookup_code,
435 x_fob_lookup_code,
436 x_freight_terms_lu_code,
437 x_terms_id,
438 x_online_report_id,
439 x_sequence);
440
441 END get_vendor_values;
442
443
444 PROCEDURE get_vendor_site_values(
445 x_vendor_id IN po_headers.vendor_id%TYPE,
446 x_vendor_site_id IN po_headers.vendor_site_id%TYPE,
447 x_ship_to_location_id IN OUT NOCOPY po_headers.ship_to_location_id%TYPE,
448 x_bill_to_location_id IN OUT NOCOPY po_headers.bill_to_location_id%TYPE,
449 x_ship_via_lookup_code IN OUT NOCOPY po_headers.ship_via_lookup_code%TYPE,
450 x_fob_lookup_code IN OUT NOCOPY po_headers.fob_lookup_code%TYPE,
451 x_freight_terms_lu_code IN OUT NOCOPY po_headers.freight_terms_lookup_code%TYPE,
452 x_terms_id IN OUT NOCOPY po_headers.terms_id%TYPE,
453 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
454 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE
455 ) IS
456
457 x_progress VARCHAR2(4) := NULL;
458
459 BEGIN
460
461 x_progress := '001';
462
463 SELECT ship_to_location_id,
464 bill_to_location_id,
465 ship_via_lookup_code,
466 fob_lookup_code,
467 freight_terms_lookup_code,
468 terms_id
469 INTO x_ship_to_location_id,
470 x_bill_to_location_id,
471 x_ship_via_lookup_code,
472 x_fob_lookup_code,
473 x_freight_terms_lu_code,
474 x_terms_id
475 FROM PO_VENDOR_SITES
476 WHERE vendor_id = x_vendor_id
477 AND vendor_site_id = x_vendor_site_id;
478
479 EXCEPTION
480 WHEN NO_DATA_FOUND THEN
481 get_vendor_values(x_vendor_id,
482 x_ship_to_location_id,
483 x_bill_to_location_id,
484 x_ship_via_lookup_code,
485 x_fob_lookup_code,
486 x_freight_terms_lu_code,
487 x_terms_id,
488 x_online_report_id,
489 x_sequence);
490 WHEN OTHERS THEN
491 po_copydoc_s1.copydoc_sql_error('get_vendor_site_values', x_progress, sqlcode,
492 x_online_report_id,
493 x_sequence,
494 0, 0, 0);
495 get_vendor_values(x_vendor_id,
496 x_ship_to_location_id,
497 x_bill_to_location_id,
498 x_ship_via_lookup_code,
499 x_fob_lookup_code,
500 x_freight_terms_lu_code,
501 x_terms_id,
502 x_online_report_id,
503 x_sequence);
504
505 END get_vendor_site_values;
506
507
508 PROCEDURE validate_buyer_id(
509 x_buyer_id IN OUT NOCOPY po_headers.agent_id%TYPE,
510 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
511 x_sequence IN OUT NOCOPY po_online_report_text.line_num%TYPE
512 ) IS
513 x_valid_flag VARCHAR2(2) := NULL;
514 x_progress VARCHAR2(4) := NULL;
515
516 BEGIN
517
518 -- This field has to be not NULL, because it's a mandatory field.
519 -- unless DB is corrupted.
520
521 IF (x_buyer_id IS NULL) THEN
522 fnd_message.set_name('PO', 'PO_PO_MISSING_BUYER_ID');
523 po_copydoc_s1.online_report(x_online_report_id,
524 x_sequence,
525 substr(fnd_message.get, 1, 240),
526 0, 0, 0);
527
528 ELSE
529 x_progress := '001';
530
531 SELECT distinct 'Y'
532 INTO x_valid_flag
533 FROM po_buyers_val_v
534 WHERE employee_id = (
535 SELECT agent_id FROM po_agents
536 WHERE sysdate between nvl(start_date_active, sysdate-1)
537 and nvl(end_date_active, sysdate+1)
538 AND agent_id = x_buyer_id);
539 END IF;
540
541 EXCEPTION
542 WHEN NO_DATA_FOUND THEN
543 x_buyer_id := NULL;
544 fnd_message.set_name('PO', 'PO_RI_INVALID_SUGGESTED_BUYER');
545 po_copydoc_s1.online_report(x_online_report_id,
546 x_sequence,
547 substr(fnd_message.get, 1, 240),
548 0, 0, 0);
549 WHEN OTHERS THEN
550 x_buyer_id := NULL;
551 po_copydoc_s1.copydoc_sql_error('validate_buyer_id', x_progress, sqlcode,
552 x_online_report_id,
553 x_sequence,
554 0, 0, 0);
555
556 END validate_buyer_id;
557
558 PROCEDURE validate_vendor(
559 x_sob_id IN financials_system_parameters.set_of_books_id%TYPE,
560 x_inv_org_id IN financials_system_parameters.inventory_organization_id%TYPE,
561 x_vendor_id IN OUT NOCOPY po_headers.vendor_id%TYPE,
562 x_vendor_site_id IN OUT NOCOPY po_headers.vendor_site_id%TYPE,
563 x_vendor_contact_id IN OUT NOCOPY po_headers.vendor_contact_id%TYPE,
564 x_ship_to_location_id IN OUT NOCOPY po_headers.ship_to_location_id%TYPE,
565 x_bill_to_location_id IN OUT NOCOPY po_headers.bill_to_location_id%TYPE,
566 x_ship_via_lookup_code IN OUT NOCOPY po_headers.ship_via_lookup_code%TYPE,
567 x_fob_lookup_code IN OUT NOCOPY po_headers.fob_lookup_code%TYPE,
568 x_freight_terms_lu_code IN OUT NOCOPY po_headers.freight_terms_lookup_code%TYPE,
569 x_terms_id IN OUT NOCOPY po_headers.terms_id%TYPE,
570 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
571 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE
572 ) IS
573
574 x_progress VARCHAR2(4) := NULL;
575 x_valid_flag VARCHAR2(2) := NULL;
576
577 BEGIN
578
579 x_progress := '001';
580 BEGIN
581 SELECT distinct 'Y'
582 INTO x_valid_flag
583 FROM PO_VENDORS
584 WHERE vendor_id = x_vendor_id
585 AND enabled_flag = 'Y'
586 AND SYSDATE BETWEEN nvl(start_date_active, SYSDATE-1)
587 AND nvl(end_date_active, SYSDATE+1);
588 EXCEPTION
589 WHEN NO_DATA_FOUND THEN
590 fnd_message.set_name('PO', 'PO_PDOI_INVALID_VENDOR');
591 fnd_message.set_token('VALUE', to_char(x_vendor_id), FALSE);
592 x_vendor_id := NULL;
593 po_copydoc_s1.online_report(x_online_report_id,
594 x_sequence,
595 substr(fnd_message.get, 1, 240),
596 0, 0, 0);
597 WHEN OTHERS THEN
598 x_vendor_id := NULL;
599 po_copydoc_s1.copydoc_sql_error('validate_vendor', x_progress, sqlcode,
600 x_online_report_id,
601 x_sequence,
602 0, 0, 0);
603 END;
604
605 IF (x_vendor_id IS NULL) THEN
606 x_vendor_site_id := NULL;
607 x_vendor_contact_id := NULL;
608 ELSE
609 x_progress := '002';
610 BEGIN
611 SELECT distinct 'Y'
612 INTO x_valid_flag
613 FROM PO_VENDOR_SITES
614 WHERE vendor_site_id = x_vendor_site_id
615 AND vendor_id = x_vendor_id
616 AND nvl(rfq_only_site_flag,'N') <> 'Y'
617 AND purchasing_site_flag = 'Y'
618 AND SYSDATE < nvl(inactive_date, SYSDATE + 1);
619 EXCEPTION
620 WHEN NO_DATA_FOUND THEN
621 fnd_message.set_name('PO', 'PO_PDOI_INVALID_VENDOR_SITE');
622 fnd_message.set_token('VALUE', to_char(x_vendor_site_id), FALSE);
623 x_vendor_site_id := NULL;
624 po_copydoc_s1.online_report(x_online_report_id,
625 x_sequence,
626 substr(fnd_message.get, 1, 240),
627 0, 0, 0);
628 WHEN OTHERS THEN
629 x_vendor_site_id := NULL;
630 po_copydoc_s1.copydoc_sql_error('validate_vendor', x_progress, sqlcode,
631 x_online_report_id,
632 x_sequence,
633 0, 0, 0);
634 END;
635 IF (x_vendor_site_id IS NULL) THEN
636 x_vendor_contact_id := NULL;
637 ELSE
638 -- It's ok to have vendor contact null, but not ok if it's invalid
639 IF (x_vendor_contact_id IS NOT NULL) THEN
640 x_progress := '003';
641 BEGIN
642 SELECT distinct 'Y'
643 INTO x_valid_flag
644 FROM PO_VENDOR_CONTACTS
645 WHERE vendor_contact_id = x_vendor_contact_id
646 AND vendor_site_id = x_vendor_site_id
647 AND SYSDATE < nvl(inactive_date, SYSDATE+1);
648 EXCEPTION
649 WHEN NO_DATA_FOUND THEN
650 fnd_message.set_name('PO', 'PO_PDOI_INVALID_VDR_CNTCT');
651 fnd_message.set_token('VALUE', to_char(x_vendor_contact_id), FALSE);
652 x_vendor_contact_id := NULL;
653 po_copydoc_s1.online_report(x_online_report_id,
654 x_sequence,
655 substr(fnd_message.get, 1, 240),
656 0, 0, 0);
657 WHEN OTHERS THEN
658 x_vendor_contact_id := NULL;
659 po_copydoc_s1.copydoc_sql_error('validate_vendor', x_progress, sqlcode,
660 x_online_report_id,
661 x_sequence,
662 0, 0, 0);
663 END;
664 END IF;
665 END IF;
666 END IF;
667
668 /* Get values for ship_to_location_id,
669 -- bill_to_location_id,
670 -- ship_via_lookup_code,
671 -- fob_lookup_code,
672 -- freight_terms_lookup_code,
673 -- and terms_id
674 bug 1673520 : dreddy
675 we do not need this as we dont care if the locations and terms on the
676 vendor/vendor site and fsp are valid or not at the time of copying
677
678 IF (x_vendor_id IS NULL) THEN
679 -- we get the system defaults.
680 get_fsp_values(x_ship_to_location_id,
681 x_bill_to_location_id,
682 x_ship_via_lookup_code,
683 x_fob_lookup_code,
684 x_freight_terms_lu_code,
685 x_terms_id,
686 x_online_report_id,
687 x_sequence);
688 ELSE
689 -- User provided a vendor_id and it's valid.
690 IF (x_vendor_site_id IS NULL) THEN
691 -- so we get the vendor values.
692 get_vendor_values(x_vendor_id,
693 x_ship_to_location_id,
694 x_bill_to_location_id,
695 x_ship_via_lookup_code,
696 x_fob_lookup_code,
697 x_freight_terms_lu_code,
698 x_terms_id,
699 x_online_report_id,
700 x_sequence);
701 ELSE
702 -- Use values from vendor site.
703 get_vendor_site_values(x_vendor_id,
704 x_vendor_site_id,
705 x_ship_to_location_id,
706 x_bill_to_location_id,
707 x_ship_via_lookup_code,
708 x_fob_lookup_code,
709 x_freight_terms_lu_code,
710 x_terms_id,
711 x_online_report_id,
712 x_sequence);
713 END IF;
714 END IF;
715 */
716
717 END validate_vendor;
718
719 PROCEDURE validate_location_terms(
720 x_sob_id IN financials_system_parameters.set_of_books_id%TYPE,
721 x_ship_to_location_id IN OUT NOCOPY po_headers.ship_to_location_id%TYPE,
722 x_bill_to_location_id IN OUT NOCOPY po_headers.bill_to_location_id%TYPE,
723 x_fob_lookup_code IN OUT NOCOPY po_headers.fob_lookup_code%TYPE,
724 x_freight_terms_lu_code IN OUT NOCOPY po_headers.freight_terms_lookup_code%TYPE,
725 x_terms_id IN OUT NOCOPY po_headers.terms_id%TYPE,
726 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
727 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE
728 ) IS
729
730 x_progress VARCHAR2(4) := NULL;
731 x_valid_flag VARCHAR2(2) := NULL;
732
733 BEGIN
734
735 IF (x_ship_to_location_id IS NOT NULL) THEN
736 x_progress := '004';
737 BEGIN
738 SELECT distinct 'Y'
739 INTO x_valid_flag
740 FROM PO_SHIP_TO_LOC_ORG_V
741 WHERE location_id = x_ship_to_location_id
742 AND (set_of_books_id IS NULL OR set_of_books_id = x_sob_id);
743 EXCEPTION
744 WHEN NO_DATA_FOUND THEN
745 x_ship_to_location_id := NULL;
746 fnd_message.set_name('PO', 'PO_PO_SHIP_LOCN_INVALID');
747 po_copydoc_s1.online_report(x_online_report_id,
748 x_sequence,
749 substr(fnd_message.get, 1, 240),
750 0, 0, 0);
751 WHEN OTHERS THEN
752 x_ship_to_location_id := NULL;
753 po_copydoc_s1.copydoc_sql_error('validate_location_terms', x_progress, sqlcode,
754 x_online_report_id,
755 x_sequence,
756 0, 0, 0);
757 END;
758 END IF;
759
760 IF (x_bill_to_location_id IS NOT NULL) THEN
761 x_progress := '005';
762 BEGIN
763 SELECT distinct 'Y'
764 INTO x_valid_flag
765 FROM HR_BILLING_LOCATIONS_PO_V
766 WHERE location_id = x_bill_to_location_id;
767 EXCEPTION
768 WHEN NO_DATA_FOUND THEN
769 fnd_message.set_name('PO', 'PO_PDOI_INVALID_BILL_LOC_ID');
770 fnd_message.set_token('VALUE', to_char(x_bill_to_location_id));
771 x_bill_to_location_id := NULL;
772 po_copydoc_s1.online_report(x_online_report_id,
773 x_sequence,
774 substr(fnd_message.get, 1, 240),
775 0, 0, 0);
776 WHEN OTHERS THEN
777 x_bill_to_location_id := NULL;
778 po_copydoc_s1.copydoc_sql_error('validate_location_terms', x_progress, sqlcode,
779 x_online_report_id,
780 x_sequence,
781 0, 0, 0);
782 END;
783 END IF;
784
785 IF (x_fob_lookup_code IS NOT NULL) THEN
786 x_progress := '007';
787 BEGIN
788 SELECT distinct 'Y'
789 INTO x_valid_flag
790 FROM PO_LOOKUP_CODES
791 WHERE lookup_type = 'FOB'
792 AND SYSDATE < nvl(inactive_date, SYSDATE+1)
793 AND lookup_code = x_fob_lookup_code;
794 EXCEPTION
795 WHEN NO_DATA_FOUND THEN
796 fnd_message.set_name('PO', 'PO_PDOI_INVALID_FOB');
797 fnd_message.set_token('VALUE', x_fob_lookup_code, FALSE);
798 x_fob_lookup_code := NULL;
799 po_copydoc_s1.online_report(x_online_report_id,
800 x_sequence,
801 substr(fnd_message.get, 1, 240),
802 0, 0, 0);
803 WHEN OTHERS THEN
804 x_fob_lookup_code := NULL;
805 po_copydoc_s1.copydoc_sql_error('validate_location_terms', x_progress, sqlcode,
806 x_online_report_id,
807 x_sequence,
808 0, 0, 0);
809 END;
810 END IF;
811
812 IF (x_freight_terms_lu_code IS NOT NULL) THEN
813 x_progress := '007';
814 BEGIN
815 SELECT distinct 'Y'
816 INTO x_valid_flag
817 FROM PO_LOOKUP_CODES
818 WHERE lookup_type = 'FREIGHT TERMS'
819 AND SYSDATE < nvl(inactive_date, SYSDATE+1)
820 AND lookup_code = x_freight_terms_lu_code;
821 EXCEPTION
822 WHEN NO_DATA_FOUND THEN
823 fnd_message.set_name('PO', 'PO_PDOI_INVALID_FREIGHT_TERMS');
824 fnd_message.set_token('VALUE', x_freight_terms_lu_code, FALSE);
825 x_freight_terms_lu_code := NULL;
826 po_copydoc_s1.online_report(x_online_report_id,
827 x_sequence,
828 substr(fnd_message.get, 1, 240),
829 0, 0, 0);
830 WHEN OTHERS THEN
831 x_freight_terms_lu_code := NULL;
832 po_copydoc_s1.copydoc_sql_error('validate_location_terms', x_progress, sqlcode,
833 x_online_report_id,
834 x_sequence,
835 0, 0, 0);
836 END;
837 END IF;
838
839 IF (x_terms_id IS NOT NULL) THEN
840 x_progress := '008';
841 BEGIN
842 SELECT distinct 'Y'
843 INTO x_valid_flag
844 FROM AP_TERMS_VAL_V
845 WHERE term_id = x_terms_id;
846 EXCEPTION
847 WHEN NO_DATA_FOUND THEN
848 fnd_message.set_name('PO', 'PO_PDOI_INVALID_PAY_TERMS');
849 fnd_message.set_token('VALUE', to_char(x_terms_id), FALSE);
850 x_terms_id := NULL;
851 po_copydoc_s1.online_report(x_online_report_id,
852 x_sequence,
853 substr(fnd_message.get, 1, 240),
854 0, 0, 0);
855 WHEN OTHERS THEN
856 x_terms_id := NULL;
857 po_copydoc_s1.copydoc_sql_error('validate_location_terms', x_progress, sqlcode,
858 x_online_report_id,
859 x_sequence,
860 0, 0, 0);
861 END;
862 END IF;
863
864 END validate_location_terms;
865
866
867 PROCEDURE validate_item(
868 x_item_id IN OUT NOCOPY po_lines.item_id%TYPE,
869 x_item_description IN OUT NOCOPY po_lines.item_description%TYPE,
870 x_item_revision IN OUT NOCOPY po_lines.item_revision%TYPE,
871 x_category_id IN OUT NOCOPY po_lines.category_id%TYPE,
872 x_line_type_id IN po_lines.line_type_id%TYPE,
873 x_inv_org_id IN financials_system_parameters.inventory_organization_id%TYPE,
874 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
875 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
876 x_line_num IN po_online_report_text.line_num%TYPE,
877 x_return_code OUT NOCOPY NUMBER
878 ) IS
879
880 x_progress VARCHAR2(4);
881 x_valid_flag VARCHAR2(2);
882 l_validate_flag mtl_category_sets_v.validate_flag%TYPE; --Bug# 3222657
883 l_category_set_id mtl_category_sets_v.category_set_id%TYPE; --Bug# 3222657
884 BEGIN
885
886 IF (x_item_id IS NOT NULL) THEN
887 x_progress := '001';
888 BEGIN
889 SELECT distinct 'Y'
890 INTO x_valid_flag
891 FROM MTL_SYSTEM_ITEMS
892 WHERE inventory_item_id = x_item_id
893 AND purchasing_enabled_flag = 'Y'
894 AND organization_id = x_inv_org_id;
895 EXCEPTION
896 WHEN NO_DATA_FOUND THEN
897 fnd_message.set_name('PO', 'PO_PDOI_ITEM_INVALID');
898 fnd_message.set_token('ITEM', to_char(x_item_id), FALSE);
899 x_item_id := NULL;
900 po_copydoc_s1.online_report(x_online_report_id,
901 x_sequence,
902 substr(fnd_message.get, 1, 240),
903 x_line_num, 0, 0);
904 x_return_code := -1;
905 RETURN;
906 WHEN OTHERS THEN
907 x_item_id := NULL;
908 po_copydoc_s1.copydoc_sql_error('validate_item', x_progress, sqlcode,
909 x_online_report_id,
910 x_sequence,
911 x_line_num, 0, 0);
912 RETURN;
913 x_return_code := -1;
914 END;
915
916 IF (x_item_id IS NOT NULL) THEN
917 -- Make sure if line_type is outside_operation, we have an
918 -- outside_operation item as well
919 x_progress := '002';
920 BEGIN
921 SELECT distinct 'Y'
922 INTO x_valid_flag
923 FROM MTL_SYSTEM_ITEMS MSI
924 WHERE MSI.inventory_item_id = x_item_id
925 AND MSI.organization_id = x_inv_org_id
926 AND (MSI.outside_operation_flag <> 'Y'
927 OR (MSI.outside_operation_flag = 'Y'
928 AND EXISTS (SELECT 'op line type'
929 FROM PO_LINE_TYPES PLT
930 WHERE PLT.line_type_id = x_line_type_id
931 AND PLT.outside_operation_flag = 'Y')
932 )
933 );
934 EXCEPTION
935 WHEN NO_DATA_FOUND THEN
936 x_item_id := NULL;
937 -- Bug 3488117: Truncate at 2000 characters instead of 240.
938 fnd_message.set_name('PO', 'PO_RI_LINE_TYPE_ITEM_MISMATCH');
939 po_copydoc_s1.online_report(x_online_report_id,
940 x_sequence,
941 substr(fnd_message.get, 1, 2000),
942 x_line_num, 0, 0);
943 x_return_code := -1;
944 RETURN;
945 WHEN OTHERS THEN
946 x_item_id := NULL;
947 po_copydoc_s1.copydoc_sql_error('validate_item', x_progress, sqlcode,
948 x_online_report_id,
949 x_sequence,
950 x_line_num, 0, 0);
951 x_return_code := -1;
952 RETURN;
953 END;
954
955 -- We have an item_id, so the line_type cannot be 'AMOUNT'
956 x_progress := '002';
957 BEGIN
958 SELECT distinct 'Y'
959 INTO x_valid_flag
960 FROM PO_LINE_TYPES
961 WHERE line_type_id = x_line_type_id
962 AND order_type_lookup_code <> 'AMOUNT';
963 EXCEPTION
964 WHEN NO_DATA_FOUND THEN
965 x_item_id := NULL;
966 fnd_message.set_name('PO', 'PO_PDOI_MISMATCH_ITEM_ITEMTYPE');
967 po_copydoc_s1.online_report(x_online_report_id,
968 x_sequence,
969 substr(fnd_message.get, 1, 240),
970 x_line_num, 0, 0);
971 x_return_code := -1;
972 RETURN;
973 WHEN OTHERS THEN
974 x_item_id := NULL;
975 po_copydoc_s1.copydoc_sql_error('validate_item', x_progress, sqlcode,
976 x_online_report_id,
977 x_sequence,
978 x_line_num, 0, 0);
979 x_return_code := -1;
980 RETURN;
981 END;
982
983 IF (x_item_revision IS NOT NULL) THEN
984 x_progress := '003';
985 BEGIN
986 SELECT distinct 'Y'
987 INTO x_valid_flag
988 FROM MTL_ITEM_REVISIONS_ORG_VAL_V
989 WHERE revision = x_item_revision
990 AND inventory_item_id = x_item_id;
991 EXCEPTION
992 WHEN NO_DATA_FOUND THEN
993 fnd_message.set_name('PO', 'PO_RI_INVALID_ITEM_REVISION');
994 fnd_message.set_token('VALUE', x_item_revision);
995 x_item_revision := NULL;
996 po_copydoc_s1.online_report(x_online_report_id,
997 x_sequence,
998 substr(fnd_message.get, 1, 240),
999 x_line_num, 0, 0);
1000 WHEN OTHERS THEN
1001 x_item_revision := NULL;
1002 po_copydoc_s1.copydoc_sql_error('validate_item', x_progress, sqlcode,
1003 x_online_report_id,
1004 x_sequence,
1005 x_line_num, 0, 0);
1006 END;
1007 END IF;
1008
1009 IF (x_category_id IS NULL) THEN
1010 x_progress := '004';
1011 BEGIN
1012 SELECT MIC.category_id
1013 INTO x_category_id
1014 FROM MTL_ITEM_CATEGORIES MIC,
1015 MTL_DEFAULT_SETS_VIEW MDSV
1016 WHERE MIC.inventory_item_id = x_item_id
1017 AND MIC.organization_id = x_inv_org_id
1018 AND MIC.category_set_id = MDSV.category_set_id
1019 AND MDSV.functional_area_id = 2;
1020 EXCEPTION
1021 WHEN NO_DATA_FOUND THEN
1022 fnd_message.set_name('PO', 'PO_PO_MISSING_CATEGORY');
1023 fnd_message.set_token('ITEM', to_char(x_item_id), FALSE);
1024 x_category_id := NULL;
1025 po_copydoc_s1.online_report(x_online_report_id,
1026 x_sequence,
1027 substr(fnd_message.get, 1, 240),
1028 x_line_num, 0, 0);
1029 x_return_code := -1;
1030 RETURN;
1031 WHEN OTHERS THEN
1032 x_category_id := NULL;
1033 po_copydoc_s1.copydoc_sql_error('validate_item', x_progress, sqlcode,
1034 x_online_report_id,
1035 x_sequence,
1036 x_line_num, 0, 0);
1037 x_return_code := -1;
1038 RETURN;
1039 END;
1040
1041 /* Bug #: 2179656
1042 ** Desc: Removed the check for validating that the PO line item belongs
1043 ** to the item category specified on the PO line. This was not allowing the
1044 ** users to reapprove the copied PO when the item category was changed but
1045 ** a non-copied PO could be approved even when the item category was changed
1046 ** for an item on the existing PO line. Keeping in sync the logic as per
1047 ** product management.
1048
1049 ELSE
1050 x_progress := '005';
1051 BEGIN
1052 SELECT distinct 'Y'
1053 INTO x_valid_flag
1054 FROM MTL_ITEM_CATEGORIES MIC,
1055 MTL_DEFAULT_SETS_VIEW MDSV
1056 WHERE MIC.category_id = x_category_id
1057 AND MIC.inventory_item_id = x_item_id
1058 AND MIC.organization_id = x_inv_org_id
1059 AND MIC.category_set_id = MDSV.category_set_id
1060 AND MDSV.functional_area_id = 2;
1061 EXCEPTION
1062 WHEN NO_DATA_FOUND THEN
1063 x_category_id := NULL;
1064 -- Bug 3488117: Truncate at 2000 characters instead of 240.
1065 fnd_message.set_name('PO', 'PO_RI_INVALID_CATEGORY_ID');
1066 po_copydoc_s1.online_report(x_online_report_id,
1067 x_sequence,
1068 substr(fnd_message.get, 1, 2000),
1069 x_line_num, 0, 0);
1070 x_return_code := -1;
1071 RETURN;
1072 WHEN OTHERS THEN
1073 x_category_id := NULL;
1074 po_copydoc_s1.copydoc_sql_error('validate_item', x_progress, sqlcode,
1075 x_online_report_id,
1076 x_sequence,
1077 x_line_num, 0, 0);
1078 x_return_code := -1;
1079 RETURN;
1080 END;
1081 ** End of Fix: Bug# 2179656 */
1082
1083 END IF;
1084 END IF;
1085 ELSE
1086 x_item_revision := NULL;
1087 IF (x_category_id IS NULL) THEN
1088 fnd_message.set_name('PO', 'PO_RI_CAT_ID_ITEM_DESC_MISSING');
1089 po_copydoc_s1.online_report(x_online_report_id,
1090 x_sequence,
1091 substr(fnd_message.get, 1, 240),
1092 x_line_num, 0, 0);
1093 x_return_code := -1;
1094 RETURN;
1095 ELSE
1096 x_progress := '006';
1097 BEGIN
1098
1099 -- Start Bug # 3222657
1100 /*
1101 Bug# 3222657, Commented out the following piece of code and fixed the Issue by
1102 validating on MTL_CATEGORY_SET_VALID_CATS only when the 'Enforce list of valid
1103 Categories' (validate_flag) is set to 'Y' for the Category Set.
1104
1105 SELECT distinct 'Y'
1106 INTO x_valid_flag
1107 FROM MTL_CATEGORY_SET_VALID_CATS MCSVC,
1108 MTL_DEFAULT_SETS_VIEW MDSV
1109 WHERE MCSVC.category_id = x_category_id
1110 AND MCSVC.category_set_id = MDSV.category_set_id
1111 AND MDSV.functional_area_id = 2;
1112 */
1113 Begin
1114 select validate_flag,
1115 category_set_id
1116 INTO l_validate_flag,
1117 l_category_set_id
1118 FROM MTL_DEFAULT_SETS_VIEW MDSV
1119 where MDSV.functional_area_id = 2;
1120 Exception
1121 when others then
1122 NULL;
1123 End;
1124
1125 IF l_validate_flag = 'Y' then
1126
1127 SELECT distinct 'Y'
1128 INTO x_valid_flag
1129 FROM MTL_CATEGORY_SET_VALID_CATS MCSVC,
1130 MTL_CATEGORIES_VL MCV
1131 WHERE MCSVC.category_id = x_category_id
1132 AND MCSVC.category_set_id = l_category_set_id
1133 AND MCV.category_id = MCSVC.category_id
1134 AND sysdate < nvl(mcv.disable_date, sysdate+1)
1135 AND mcv.enabled_flag = 'Y';
1136
1137 ELSE
1138
1139 SELECT distinct 'Y'
1140 INTO x_valid_flag
1141 FROM MTL_CATEGORIES_VL MCV
1142 WHERE MCV.category_id = x_category_id
1143 AND sysdate < nvl(mcv.disable_date, sysdate+1)
1144 AND mcv.enabled_flag = 'Y';
1145
1146 END IF;
1147 -- End Bug # 3222657
1148
1149 EXCEPTION
1150 WHEN NO_DATA_FOUND THEN
1151 x_category_id := NULL;
1152 fnd_message.set_name('PO', 'PO_RI_INVALID_CATEGORY_ID');
1153 po_copydoc_s1.online_report(x_online_report_id,
1154 x_sequence,
1155 substr(fnd_message.get, 1, 240),
1156 x_line_num, 0, 0);
1157 x_return_code := -1;
1158 RETURN;
1159 WHEN OTHERS THEN
1160 x_category_id := NULL;
1161 po_copydoc_s1.copydoc_sql_error('validate_item', x_progress, sqlcode,
1162 x_online_report_id,
1163 x_sequence,
1164 x_line_num, 0, 0);
1165 x_return_code := -1;
1166 RETURN;
1167 END;
1168 END IF;
1169 IF (x_item_description IS NULL) THEN
1170 fnd_message.set_name('PO', 'PO_RI_CAT_ID_ITEM_DESC_MISSING');
1171 po_copydoc_s1.online_report(x_online_report_id,
1172 x_sequence,
1173 substr(fnd_message.get, 1, 240),
1174 x_line_num, 0, 0);
1175 x_return_code := -1;
1176 RETURN;
1177 END IF;
1178 END IF;
1179
1180 x_return_code := 0;
1181
1182 EXCEPTION
1183 WHEN OTHERS THEN
1184 po_copydoc_s1.copydoc_sql_error('validate_item', x_progress, sqlcode,
1185 x_online_report_id,
1186 x_sequence,
1187 x_line_num, 0, 0);
1188 x_return_code := -1;
1189
1190 END validate_item;
1191
1192 PROCEDURE validate_account_id(
1193 x_account_id IN OUT NOCOPY NUMBER,
1194 x_account_type IN VARCHAR2,
1195 x_gl_date IN DATE,
1196 x_sob_id IN financials_system_parameters.set_of_books_id%TYPE,
1197 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
1198 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
1199 x_line_num IN po_online_report_text.line_num%TYPE,
1200 x_shipment_num IN po_online_report_text.shipment_num%TYPE,
1201 x_distribution_num IN po_online_report_text.distribution_num%TYPE
1202 )IS
1203 x_valid_flag VARCHAR2(2);
1204 x_progress VARCHAR2(4) := NULL;
1205
1206 BEGIN
1207
1208 x_progress := '001';
1209
1210 -- validation
1211 SELECT distinct 'Y'
1212 INTO x_valid_flag
1213 FROM gl_code_combinations gcc,
1214 gl_sets_of_books sob
1215 WHERE gcc.code_combination_id = x_account_id
1216 AND gcc.enabled_flag = 'Y'
1217 AND trunc(nvl(x_gl_date,SYSDATE)) BETWEEN
1218 trunc(nvl(start_date_active, nvl(x_gl_date,SYSDATE) ))
1219 AND
1220 trunc(nvl (end_date_active, nvl(x_gl_date,SYSDATE) ))
1221 AND gcc.detail_posting_allowed_flag = 'Y'
1222 AND gcc.summary_flag = 'N'
1223 AND gcc.chart_of_accounts_id = sob.chart_of_accounts_id
1224 AND sob.set_of_books_id = x_sob_id;
1225
1226
1227 EXCEPTION
1228 WHEN NO_DATA_FOUND THEN
1229 x_account_id := NULL;
1230 IF x_account_type = 'BUDGET' THEN
1231 fnd_message.set_name('PO', 'PO_RI_INVALID_BUDGET_ACC_ID');
1232 ELSIF x_account_type = 'ACCRUAL' THEN
1233 fnd_message.set_name('PO', 'PO_RI_INVALID_ACCRUAL_ACC_ID');
1234 ELSIF x_account_type = 'CHARGE' THEN
1235 fnd_message.set_name('PO', 'PO_RI_INVALID_CHARGE_ACC_ID');
1236 ELSIF x_account_type = 'VARIANCE' THEN
1237 fnd_message.set_name('PO', 'PO_RI_INVALID_VARIANCE_ACC_ID');
1238 END IF;
1239 po_copydoc_s1.online_report(x_online_report_id,
1240 x_sequence,
1241 substr(fnd_message.get, 1, 240),
1242 x_line_num, x_shipment_num, x_distribution_num);
1243
1244 WHEN OTHERS THEN
1245 x_account_id :=NULL;
1246 po_copydoc_s1.copydoc_sql_error('validate_account_id', x_progress, sqlcode,
1247 x_online_report_id,
1248 x_sequence,
1249 x_line_num, x_shipment_num, x_distribution_num);
1250 -- RAISE COPYDOC_DIST_FAILURE;
1251
1252 END validate_account_id;
1253
1254
1255 /* assume Oracle Project is installed */
1256
1257 /**
1258 * Private Procedure: validate_project_id
1259 * Requires: Destination type is EXPENSE.
1260 * Modifies: PO_ONLINE_REPORT_TEXT
1261 * Effects: Validates that the given project_id exists as a
1262 * chargeable PA project. If not, writes an error message to
1263 * PO_ONLINE_REPORT_TEXT.
1264 * Returns: none
1265 */
1266 PROCEDURE validate_project_id(
1267 x_project_id IN OUT NOCOPY po_distributions.project_id%TYPE,
1268 -- <PO_PJM_VALIDATION FPI>
1269 -- Removed the x_destination_type_code and x_ship_to_organization_id arguments.
1270 -- Added NOCOPY to x_project_id and x_sequence.
1271 -- x_destination_type_code IN po_distributions.destination_type_code%TYPE,
1272 -- x_ship_to_organization_id IN NUMBER,
1273 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
1274 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
1275 x_line_num IN po_online_report_text.line_num%TYPE,
1276 x_shipment_num IN po_online_report_text.shipment_num%TYPE,
1277 x_distribution_num IN po_online_report_text.distribution_num%TYPE
1278 )IS
1279 x_valid_flag VARCHAR2(2);
1280 x_progress VARCHAR2(4) := NULL;
1281
1282 BEGIN
1283
1284 IF (x_project_id IS NULL) THEN
1285 RETURN;
1286 END IF;
1287
1288 -- validation
1289
1290 -- <PO_PJM_VALIDATION FPI>
1291 -- Removed the IF statement and the ELSE clause, since now this procedure
1292 -- will only be called if the destination type is EXPENSE.
1293
1294 -- IF x_destination_type_code = 'EXPENSE' THEN
1295 x_progress := '001';
1296 SELECT distinct 'Y'
1297 INTO x_valid_flag
1298 FROM pa_projects_expend_v
1299 WHERE project_id=x_project_id;
1300
1301 -- <PO_PJM_VALIDATION FPI START>
1302 /**
1303 ELSE
1304 x_progress := '002';
1305 SELECT distinct 'Y'
1306 INTO x_valid_flag
1307 FROM mtl_project_v m,
1308 pjm_project_parameters p
1309 WHERE p.organization_id = x_ship_to_organization_id
1310 AND m.project_id = p.project_id
1311 AND m.project_id = x_project_id;
1312 END IF;
1313 **/
1314 -- <PO_PJM_VALIDATION FPI END>
1315
1316 EXCEPTION
1317 WHEN NO_DATA_FOUND THEN
1318 x_project_id := NULL;
1319 -- Bug 3488117: Truncate at 2000 characters instead of 240.
1320 fnd_message.set_name('PO', 'PO_RI_INVALID_PA_INFO');
1321 po_copydoc_s1.online_report(x_online_report_id,
1322 x_sequence,
1323 substr(fnd_message.get, 1, 2000),
1324 x_line_num, x_shipment_num, x_distribution_num);
1325
1326 WHEN OTHERS THEN
1327 x_project_id :=NULL;
1328 po_copydoc_s1.copydoc_sql_error('validate_project_id', x_progress, sqlcode,
1329 x_online_report_id,
1330 x_sequence,
1331 x_line_num, x_shipment_num, x_distribution_num);
1332
1333 -- RAISE COPYDOC_DIST_FAILURE;
1334
1335 END validate_project_id;
1336
1337 PROCEDURE validate_task_id(
1338 x_task_id IN OUT NOCOPY NUMBER,
1339 x_project_id IN NUMBER,
1340 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
1341 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
1342 x_line_num IN po_online_report_text.line_num%TYPE,
1343 x_shipment_num IN po_online_report_text.shipment_num%TYPE,
1344 x_distribution_num IN po_online_report_text.distribution_num%TYPE
1345 )IS
1346 x_valid_flag VARCHAR2(2);
1347 x_progress VARCHAR2(4) := NULL;
1348
1349 BEGIN
1350
1351 IF ( x_task_id IS NULL) THEN
1352 RETURN;
1353 END IF;
1354
1355 x_progress := '001';
1356
1357 -- validation
1358 select distinct 'Y'
1359 into x_valid_flag
1360 from pa_tasks_expend_v
1361 where project_id = x_project_id
1362 and task_id = x_task_id;
1363
1364 EXCEPTION
1365 WHEN NO_DATA_FOUND THEN
1366 x_task_id := NULL;
1367 -- Bug 3488177: Truncate at 2000 characters instead of 240.
1368 fnd_message.set_name('PO', 'PO_RI_INVALID_PA_INFO');
1369 po_copydoc_s1.online_report(x_online_report_id,
1370 x_sequence,
1371 substr(fnd_message.get, 1, 2000),
1372 x_line_num, x_shipment_num, x_distribution_num);
1373
1374 WHEN OTHERS THEN
1375 x_task_id :=NULL;
1376 po_copydoc_s1.copydoc_sql_error('validate_task_id', x_progress, sqlcode,
1377 x_online_report_id,
1378 x_sequence,
1379 x_line_num, x_shipment_num, x_distribution_num);
1380 -- RAISE COPYDOC_DIST_FAILURE;
1381
1382 END validate_task_id;
1383
1384 -- Start Bug 3488117: Add code to validate expenditure fields.
1385
1386 PROCEDURE validate_exp_item_date(
1387 x_project_id IN NUMBER,
1388 x_task_id IN NUMBER,
1389 x_exp_item_date IN OUT NOCOPY DATE,
1390 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
1391 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
1392 x_line_num IN po_online_report_text.line_num%TYPE,
1393 x_shipment_num IN po_online_report_text.shipment_num%TYPE,
1394 x_distribution_num IN po_online_report_text.distribution_num%TYPE
1395 )IS
1396 x_valid_flag VARCHAR2(2) := 'N';
1397 x_progress VARCHAR2(4) := NULL;
1398 BEGIN
1399 IF (x_exp_item_date is null) then
1400 RETURN;
1401 END IF;
1402
1403 x_progress := '001';
1404 select distinct 'Y'
1405 into x_valid_flag
1406 from pa_projects_all prj,
1407 pa_tasks tsk
1408 where
1409 prj.project_id = x_project_id
1410 and prj.project_id = tsk.project_id
1411 and tsk.task_id = x_task_id
1412 and x_exp_item_date between
1413 nvl(prj.start_date,x_exp_item_date)
1414 and nvl(prj.completion_date,x_exp_item_date)
1415 and x_exp_item_date between
1416 nvl(tsk.start_date,x_exp_item_date)
1417 and nvl(tsk.completion_date,x_exp_item_date);
1418
1419 EXCEPTION
1420 WHEN NO_DATA_FOUND THEN
1421 x_exp_item_date := NULL;
1422 fnd_message.set_name('PO', 'PO_RI_INVALID_PA_INFO');
1423 po_copydoc_s1.online_report(x_online_report_id,
1424 x_sequence,
1425 substr(fnd_message.get, 1, 2000),
1426 x_line_num, x_shipment_num, x_distribution_num);
1427
1428 WHEN OTHERS THEN
1429 x_exp_item_date := NULL;
1430 po_copydoc_s1.copydoc_sql_error('validate_exp_item_date', x_progress, sqlcode,
1431 x_online_report_id,
1432 x_sequence,
1433 x_line_num, x_shipment_num, x_distribution_num);
1434 END validate_exp_item_date;
1435
1436 PROCEDURE validate_exp_type(
1437 x_project_id IN NUMBER,
1438 x_exp_type IN OUT NOCOPY po_distributions.expenditure_type%TYPE,
1439 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
1440 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
1441 x_line_num IN po_online_report_text.line_num%TYPE,
1442 x_shipment_num IN po_online_report_text.shipment_num%TYPE,
1443 x_distribution_num IN po_online_report_text.distribution_num%TYPE
1444 )IS
1445 x_valid_flag VARCHAR2(2) := 'N';
1446 x_progress VARCHAR2(4) := NULL;
1447 BEGIN
1448 IF (x_exp_type is null) then
1449 RETURN;
1450 END IF;
1451
1452 x_progress := '001';
1453 SELECT
1454 'Y' into x_valid_flag
1455 FROM
1456 pa_expenditure_types_expend_v et
1457 WHERE
1458 system_linkage_function = 'VI' and
1459 (et.project_id = x_project_id or et.project_id is null) and
1460 trunc(sysdate) between nvl(et.expnd_typ_start_date_active, trunc(sysdate)) and
1461 nvl(et.expnd_typ_end_date_Active, trunc(sysdate))
1462 and et.expenditure_type = x_exp_type;
1463
1464 EXCEPTION
1465 WHEN NO_DATA_FOUND THEN
1466 x_exp_type := NULL;
1467 fnd_message.set_name('PO', 'PO_RI_INVALID_PA_INFO');
1468 po_copydoc_s1.online_report(x_online_report_id,
1469 x_sequence,
1470 substr(fnd_message.get, 1, 2000),
1471 x_line_num, x_shipment_num, x_distribution_num);
1472
1473 WHEN OTHERS THEN
1474 x_exp_type := NULL;
1475 po_copydoc_s1.copydoc_sql_error('validate_exp_type', x_progress, sqlcode,
1476 x_online_report_id,
1477 x_sequence,
1478 x_line_num, x_shipment_num, x_distribution_num);
1479 END validate_exp_type;
1480
1481 PROCEDURE validate_exp_org(
1482 x_org_id IN OUT NOCOPY NUMBER,
1483 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
1484 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
1485 x_line_num IN po_online_report_text.line_num%TYPE,
1486 x_shipment_num IN po_online_report_text.shipment_num%TYPE,
1487 x_distribution_num IN po_online_report_text.distribution_num%TYPE
1488 )IS
1489 x_valid_flag VARCHAR2(2) := 'N';
1490 x_progress VARCHAR2(4) := NULL;
1491 BEGIN
1492 IF (x_org_id is null) then
1493 RETURN;
1494 END IF;
1495
1496 x_progress := '001';
1497 select 'Y'
1498 into x_valid_flag
1499 from pa_organizations_expend_v pao
1500 where pao.active_flag = 'Y'
1501 and pao.organization_id = x_org_id ;
1502
1503 EXCEPTION
1504 WHEN NO_DATA_FOUND THEN
1505 x_org_id := NULL;
1506 fnd_message.set_name('PO', 'PO_RI_INVALID_PA_INFO');
1507 po_copydoc_s1.online_report(x_online_report_id,
1508 x_sequence,
1509 substr(fnd_message.get, 1, 2000),
1510 x_line_num, x_shipment_num, x_distribution_num);
1511
1512 WHEN OTHERS THEN
1513 x_org_id := NULL;
1514 po_copydoc_s1.copydoc_sql_error('validate_exp_org', x_progress, sqlcode,
1515 x_online_report_id,
1516 x_sequence,
1517 x_line_num, x_shipment_num, x_distribution_num);
1518 END validate_exp_org;
1519
1520 -- End Bug 3488117
1521
1522
1523 PROCEDURE validate_destination_type_code(
1524 x_destination_type_code IN OUT NOCOPY po_distributions.destination_type_code%TYPE,
1525 x_item_id IN po_lines.item_id%TYPE,
1526 x_ship_org_id IN financials_system_parameters.inventory_organization_id%TYPE,
1527 x_accrue_on_receipt_flag IN varchar2,
1528 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
1529 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
1530 x_line_num IN po_online_report_text.line_num%TYPE,
1531 x_shipment_num IN po_online_report_text.shipment_num%TYPE,
1532 x_distribution_num IN po_online_report_text.distribution_num%TYPE,
1533 x_line_location_id IN po_line_locations.line_location_id%TYPE, -- CONSIGNED FPI START
1534 x_po_line_id IN po_lines.po_line_id%TYPE, -- Bug 3557910
1535 p_transaction_flow_header_id IN NUMBER --< Bug 3546252 >
1536 ) IS
1537
1538 x_valid_flag VARCHAR2(2);
1539 /* x_expense_accrual_code po_system_parameters.expense_accrual_code%TYPE;Bug7351781*/
1540 x_item_status VARCHAR2(1);
1541 x_progress VARCHAR(4) := NULL;
1542 -- CONSIGNED FPI
1543 x_consigned_flag po_line_locations.consigned_flag%TYPE := NULL;
1544 -- Bug 3557910 Start
1545 x_eam_install_status VARCHAR2(10);
1546 x_eam_profile VARCHAR2(10);
1547 x_osp_line_flag VARCHAR2(10);
1548 -- END BUG 3557910
1549
1550 BEGIN
1551
1552 x_progress := '001';
1553
1554 /*SELECT expense_accrual_code
1555 INTO x_expense_accrual_code
1556 FROM po_system_parameters; Bug7351781 */
1557
1558 -- CONSIGNED FPI START
1559 -- Bug Fix for #2713973: COPY PO WITH CONSIGNED SHIPMENT LINE FAILS
1560 SELECT consigned_flag
1561 INTO x_consigned_flag
1562 FROM po_line_locations
1563 WHERE line_location_id = x_line_location_id;
1564 -- CONSIGNED FPI END
1565
1566 po_items_sv2.get_item_status(x_item_id, x_ship_org_id, x_item_status);
1567
1568 -- Business Rules
1569 -- item status
1570 -- 'O' = outside processing item
1571 -- - destination type must be SHOP FLOOR
1572 -- 'E' = item stockable in the org
1573 -- - destination type cannot be SHOP FLOOR
1574 -- 'D' = item defined but not stockable in org
1575 -- - destination type must be EXPENSE
1576 -- null = item not defined in org
1577 --
1578 -- accrual on receipt
1579 -- 'N' - destination type must be expense
1580 -- OR
1581 -- - the shipment is consigned
1582 --
1583 -- 'Y' - if expense_accrual = PERIOD END
1584 -- then destination type code cannot be EXPENSE
1585 -- OR
1586 -- - there is a transaction flow defined < Bug 3546252 >
1587 --
1588 -- Bug 3557910 Start
1589 -- If it is an eAM destination do that validations.
1590
1591 x_progress := '0151';
1592
1593 BEGIN
1594
1595 SELECT NVL(PLT.OUTSIDE_OPERATION_FLAG,'N')
1596 INTO x_osp_line_flag
1597 FROM PO_LINE_TYPES PLT, PO_LINES POL
1598 WHERE PLT.LINE_TYPE_ID = POL.LINE_TYPE_ID
1599 AND POL.PO_LINE_ID = x_po_line_id;
1600
1601 EXCEPTION
1602 WHEN NO_DATA_FOUND THEN
1603 x_osp_line_flag := NULL;
1604 END;
1605
1606 x_progress := '0152';
1607
1608 PO_SETUP_S1.GET_EAM_STARTUP(x_eam_install_status,x_eam_profile);
1609 /*Bug7351781 - matched the accrue on receipt flag with the corresponding destination type code*/
1610
1611 x_progress := '0153';
1612
1613 IF nvl(x_eam_install_status,'N') ='I' and
1614 nvl(x_eam_profile,'N')='Y'and
1615 nvl(x_osp_line_flag,'N') ='N' then
1616
1617 --< Bug 3546252 > When a transaction flow exists, no need to check the
1618 -- accrue_on_receipt_flag.
1619 select distinct 'Y' valid
1620 into x_valid_flag
1621 from po_lookup_codes
1622 where lookup_type = 'DESTINATION TYPE'
1623 and ( ( nvl(x_item_status,'D') = 'D'
1624 and lookup_code <> 'INVENTORY' )
1625 or ( nvl(x_item_status,'D') = 'E'
1626 and lookup_code <> 'SHOP FLOOR')
1627 or ( nvl(x_item_status,'D') = 'O'
1628 and lookup_code = 'SHOP FLOOR') )
1629 and ( ( nvl(x_consigned_flag,'N') = 'Y' and lookup_code = 'INVENTORY' )
1630 OR( p_transaction_flow_header_id IS NOT NULL ) --< Bug 3546252 >
1631 or( nvl(x_consigned_flag,'N') = 'N'
1632 and ( (nvl(x_accrue_on_receipt_flag,'Y') = 'N'
1633 and lookup_code ='EXPENSE')
1634 or ( nvl(x_accrue_on_receipt_flag,'Y') = 'Y'
1635 and lookup_code IN ('EXPENSE','INVENTORY','SHOP FLOOR') ) ) ) ) --bug7351781
1636 and lookup_code= x_destination_type_code;
1637
1638 ELSE
1639 -- END BUG 3557910
1640
1641 x_progress := '002';
1642
1643 -- CONSIGNED FPI
1644 -- Bug Fix for #2713973: COPY PO WITH CONSIGNED SHIPMENT LINE FAILS
1645 -- Added the condition for accrue_on_receipt_flag to be 'N' if
1646 -- consigned_flag is 'Y'
1647 --< Bug 3546252 > When a transaction flow exists, no need to check the
1648 -- accrue_on_receipt_flag.
1649 select distinct 'Y' valid
1650 into x_valid_flag
1651 from po_lookup_codes
1652 where lookup_type = 'DESTINATION TYPE'
1653 and ( ( nvl( x_item_status,'D') = 'D'
1654 and lookup_code = 'EXPENSE')
1655 or ( nvl( x_item_status,'D') = 'E'
1656 and lookup_code <> 'SHOP FLOOR')
1657 or ( nvl( x_item_status,'D') = 'O'
1658 and lookup_code = 'SHOP FLO-OR') )
1659 and ( ( nvl( x_accrue_on_receipt_flag,'Y') = 'N'
1660 and ( lookup_code = 'EXPENSE' or
1661 x_consigned_flag = 'Y') )
1662 OR (p_transaction_flow_header_id IS NOT NULL) --< Bug 3546252 >
1663 OR (nvl(x_accrue_on_receipt_flag,'Y') = 'N'
1664 and lookup_code ='EXPENSE')
1665 OR ( nvl(x_accrue_on_receipt_flag,'Y') = 'Y'
1666 and lookup_code IN ('EXPENSE','INVENTORY','SHOP FLOOR'))) --bug7351781
1667 and lookup_code= x_destination_type_code;
1668
1669 END IF; --Bug 3557910
1670 EXCEPTION
1671 WHEN NO_DATA_FOUND THEN
1672 x_destination_type_code := NULL;
1673 fnd_message.set_name('PO', 'PO_RI_INVALID_DEST_TYPE_CODE');
1674 po_copydoc_s1.online_report(x_online_report_id,
1675 x_sequence,
1676 substr(fnd_message.get, 1, 240),
1677 x_line_num, x_shipment_num, x_distribution_num);
1678
1679 WHEN OTHERS THEN
1680 x_destination_type_code := NULL;
1681 po_copydoc_s1.copydoc_sql_error('validate_destination_type_code', x_progress, sqlcode,
1682 x_online_report_id,
1683 x_sequence,
1684 x_line_num, x_shipment_num, x_distribution_num);
1685
1686 END validate_destination_type_code;
1687
1688 /* deliver_to_person = Requestor */
1689 PROCEDURE validate_deliver_to_person_id(
1690 x_deliver_to_person_id IN OUT NOCOPY po_distributions.deliver_to_person_id%TYPE,
1691 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
1692 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
1693 x_line_num IN po_online_report_text.line_num%TYPE,
1694 x_shipment_num IN po_online_report_text.shipment_num%TYPE,
1695 x_distribution_num IN po_online_report_text.distribution_num%TYPE
1696 )IS
1697 x_valid_flag VARCHAR2(2);
1698 x_progress VARCHAR2(4) := NULL;
1699
1700 BEGIN
1701
1702 -- deliver_to_person is optional
1703 IF (x_deliver_to_person_id IS NULL) THEN
1704 RETURN;
1705 END IF;
1706
1707 x_progress := '001';
1708
1709 -- validation
1710 --R12 CWK removed inactive date where clause
1711 SELECT distinct 'Y'
1712 INTO x_valid_flag
1713 FROM hr_employees_current_v
1714 WHERE employee_id = x_deliver_to_person_id;
1715
1716
1717 EXCEPTION
1718 WHEN NO_DATA_FOUND THEN
1719 x_deliver_to_person_id := NULL;
1720 fnd_message.set_name('PO', 'PO_PO_INVALID_DEL_TO_PERSON');
1721 po_copydoc_s1.online_report(x_online_report_id,
1722 x_sequence,
1723 substr(fnd_message.get, 1, 240),
1724 x_line_num, x_shipment_num, x_distribution_num);
1725
1726 WHEN OTHERS THEN
1727 x_deliver_to_person_id := NULL;
1728 po_copydoc_s1.copydoc_sql_error('validate_deliver_to_person_id', x_progress, sqlcode,
1729 x_online_report_id,
1730 x_sequence,
1731 x_line_num, x_shipment_num, x_distribution_num);
1732 -- RAISE COPYDOC_DIST_FAILURE;
1733
1734 END validate_deliver_to_person_id;
1735
1736
1737 PROCEDURE validate_osp_data
1738 (x_wip_entity_id IN NUMBER,
1739 x_wip_operation_seq_num IN NUMBER,
1740 x_wip_resource_seq_num IN NUMBER,
1741 x_wip_repetitive_schedule_id IN NUMBER,
1742 x_wip_line_id IN NUMBER,
1743 x_destination_organization_id IN NUMBER,
1744 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
1745 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
1746 x_line_num IN po_online_report_text.line_num%TYPE,
1747 x_shipment_num IN po_online_report_text.shipment_num%TYPE,
1748 x_distribution_num IN po_online_report_text.distribution_num%TYPE)
1749 IS
1750 x_count number;
1751 x_progress VARCHAR2(4) := '10';
1752 --Bug# 2090549 togeorge 11/05/2001
1753 --Modifed this procedure for adding eAM specific validations. And also
1754 --included the if conditions to check whether each value being validated
1755 --is not null, otherwise they would display error message even for a regular
1756 --PO (a non osp or the destination type is not 'SHOP FLOOR')
1757 x_entity_type number;
1758 BEGIN
1759
1760 -- is this a valid wip job
1761 x_count :=null;
1762 IF x_wip_entity_id is NOT NULL and x_wip_line_id IS NULL THEN
1763 begin
1764 select entity_type
1765 into x_entity_type
1766 from wip_entities
1767 where wip_entity_id=x_wip_entity_id;
1768 exception
1769 when others then
1770 null;
1771 end;
1772 if x_entity_type =6 then --6 stands for eAM work orders
1773 select count(*)
1774 into x_count
1775 from wip_entities we,
1776 wip_discrete_jobs wdj
1777 where we.wip_entity_id = wdj.wip_entity_id
1778 and we.entity_type = 6
1779 and wdj.status_type in (3,4,6);
1780 else
1781 select count(*) into x_count
1782 from wip_osp_jobs_val_v job, pa_tasks task
1783 where job.wip_entity_id = x_wip_entity_id
1784 and job.organization_id = x_destination_organization_id
1785 and task.task_id (+) = job.task_id;
1786 end if;
1787 if (x_count < 1) then
1788 -- register error
1789 fnd_message.set_name('PO', 'PO_PO_INVALID_JOB');
1790 po_copydoc_s1.online_report(x_online_report_id,
1791 x_sequence,
1792 substr(fnd_message.get, 1, 240),
1793 x_line_num, x_shipment_num,
1794 x_distribution_num);
1795 end if;
1796 end if;
1797
1798 x_count :=null;
1799 IF x_wip_line_id is NOT NULL THEN
1800 x_progress := '15';
1801 select count(*)
1802 into x_count
1803 from wip_osp_lines_val_v
1804 where line_id = x_wip_line_id
1805 and organization_id = x_destination_organization_id;
1806 if (x_count < 1) then
1807 -- register error
1808 fnd_message.set_name('PO', 'PO_PO_INVALID_JOB_LINE');
1809 po_copydoc_s1.online_report(x_online_report_id,
1810 x_sequence,
1811 substr(fnd_message.get, 1, 240),
1812 x_line_num, x_shipment_num,
1813 x_distribution_num);
1814 end if;
1815 END IF;
1816
1817 x_count :=null;
1818 x_progress := '20';
1819 -- is this a valid operation sequence
1820 -- Bug 3557910. The eAM operation may not be
1821 -- a standard operation. Hence we need to outer join BSO.
1822 if x_wip_operation_seq_num is not null then
1823 if x_entity_type =6 then --6 stands for eAM work orders
1824 select count(*)
1825 INTO x_count
1826 from WIP_OPERATIONS WO,
1827 BOM_STANDARD_OPERATIONS BSO
1828 WHERE WO.WIP_ENTITY_ID=x_wip_entity_id
1829 AND operation_seq_num=x_wip_operation_seq_num
1830 AND WO.STANDARD_OPERATION_ID = BSO.STANDARD_OPERATION_ID (+) --Bug 3557910 Added Outer JOIN
1831 AND nvl(BSO.OPERATION_TYPE,1) =1
1832 AND BSO.line_id is null;
1833
1834 else
1835 select count(*) into x_count
1836 from wip_osp_operations_val_v
1837 where operation_seq_num = x_wip_operation_seq_num
1838 and organization_id = x_destination_organization_id
1839 and ((wip_entity_id = x_wip_entity_id) and x_wip_line_id is null);
1840
1841 if (x_count < 1) then
1842 x_progress := '25';
1843 select count(*) into x_count
1844 from wip_osp_operations_val_v
1845 where operation_seq_num = x_wip_operation_seq_num
1846 and organization_id = x_destination_organization_id
1847 and repetitive_schedule_id = x_wip_repetitive_schedule_id;
1848 end if;
1849 end if; /* x_entity_type =6 */
1850 if (x_count < 1) then
1851 -- register error
1852 fnd_message.set_name('PO', 'PO_PO_INVALID_OPERATION_SEQ');
1853 po_copydoc_s1.online_report(x_online_report_id,
1854 x_sequence,
1855 substr(fnd_message.get, 1, 240),
1856 x_line_num, x_shipment_num,
1857 x_distribution_num);
1858 end if;
1859 end if; /*x_wip_operation_seq_num is not null */
1860
1861 x_progress := '30';
1862 -- is this a valid resource sequence
1863 x_count :=null;
1864 if x_wip_resource_seq_num is not null and x_entity_type <>6 then
1865
1866 select count(*) into x_count
1867 from wip_osp_resources_val_v
1868 where resource_seq_num = x_wip_resource_seq_num
1869 and organization_id = x_destination_organization_id
1870 and ((wip_entity_id = x_wip_entity_id and x_wip_line_id is null)
1871 or repetitive_schedule_id = x_wip_repetitive_schedule_id)
1872 and operation_seq_num = x_wip_operation_seq_num;
1873
1874 if (x_count < 1) then
1875 -- register error
1876 fnd_message.set_name('PO', 'PO_PO_INVALID_RESOURCE_SEQ');
1877 po_copydoc_s1.online_report(x_online_report_id,
1878 x_sequence,
1879 substr(fnd_message.get, 1, 240),
1880 x_line_num, x_shipment_num,
1881 x_distribution_num);
1882 end if;
1883 end if;
1884
1885 EXCEPTION
1886 WHEN OTHERS THEN
1887 po_copydoc_s1.copydoc_sql_error('PO_COPYDOC_SUB.validate_osp_data',
1888 x_progress, sqlcode,
1889 x_online_report_id,
1890 x_sequence,
1891 x_line_num,
1892 x_shipment_num,
1893 x_distribution_num);
1894 END;
1895
1896 /* Deliver To Location */
1897 PROCEDURE validate_deliver_to_loc_id(
1898 x_deliver_to_location_id IN OUT NOCOPY po_distributions.deliver_to_location_id%TYPE,
1899 x_ship_to_organization_id IN NUMBER,
1900 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
1901 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
1902 x_line_num IN po_online_report_text.line_num%TYPE,
1903 x_shipment_num IN po_online_report_text.shipment_num%TYPE,
1904 x_distribution_num IN po_online_report_text.distribution_num%TYPE
1905 )IS
1906 x_valid_flag VARCHAR2(2);
1907 x_progress VARCHAR2(4) := NULL;
1908
1909 BEGIN
1910
1911 -- deliver_to_location is optional
1912 IF (x_deliver_to_location_id IS NULL) THEN
1913 RETURN;
1914 END IF;
1915
1916 x_progress := '001';
1917
1918 -- validation
1919 -- bug 1942696 hr_location changes to reflect the new view
1920 begin
1921 SELECT distinct 'Y'
1922 INTO x_valid_flag
1923 FROM HR_LOCATIONS_ALL
1924 WHERE nvl(inventory_organization_id,x_ship_to_organization_id) = x_ship_to_organization_id
1925 AND nvl(inactive_date, trunc(sysdate + 1)) > trunc(sysdate)
1926 AND location_id = x_deliver_to_location_id;
1927
1928 exception
1929 when no_data_found then
1930 SELECT distinct 'Y'
1931 INTO x_valid_flag
1932 FROM HZ_LOCATIONS
1933 WHERE nvl(address_expiration_date, trunc(sysdate + 1)) > trunc(sysdate)
1934 AND location_id = x_deliver_to_location_id;
1935 end;
1936
1937 EXCEPTION
1938 WHEN NO_DATA_FOUND THEN
1939 x_deliver_to_location_id := NULL;
1940 fnd_message.set_name('PO', 'PO_RI_INVALID_DEL_LOC_ID_DEST');
1941 -- Bug 3488117: Changed trunc size from 240 to 2000.
1942 po_copydoc_s1.online_report(x_online_report_id,
1943 x_sequence,
1944 substr(fnd_message.get, 1, 2000),
1945 x_line_num, x_shipment_num, x_distribution_num);
1946
1947 WHEN OTHERS THEN
1948 x_deliver_to_location_id := NULL;
1949 po_copydoc_s1.copydoc_sql_error('validate_deliver_to_loc_id', x_progress, sqlcode,
1950 x_online_report_id,
1951 x_sequence,
1952 x_line_num, x_shipment_num, x_distribution_num);
1953 -- RAISE COPYDOC_DIST_FAILURE;
1954
1955 END validate_deliver_to_loc_id;
1956
1957 PROCEDURE validate_dest_subinventory(
1958 x_destination_subinventory IN OUT NOCOPY po_distributions.destination_subinventory%TYPE,
1959 x_ship_to_organization_id IN NUMBER,
1960 x_item_id IN po_lines.item_id%TYPE,
1961 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
1962 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
1963 x_line_num IN po_online_report_text.line_num%TYPE,
1964 x_shipment_num IN po_online_report_text.shipment_num%TYPE,
1965 x_distribution_num IN po_online_report_text.distribution_num%TYPE
1966 )IS
1967 x_valid_flag VARCHAR2(2);
1968 x_progress VARCHAR2(4) := NULL;
1969
1970 BEGIN
1971
1972 IF ( x_destination_subinventory IS NULL) THEN
1973 RETURN;
1974 END IF;
1975
1976 x_progress := '001';
1977
1978 -- validation
1979 select distinct 'Y'
1980 into x_valid_flag
1981 from mtl_secondary_inventories msub
1982 where msub.organization_id = nvl(x_ship_to_organization_id, msub.organization_id)
1983 and nvl(msub.disable_date, trunc(sysdate+1)) > trunc(sysdate)
1984 and (x_item_id is null
1985 or
1986 (x_item_id is not null
1987 and exists (select null
1988 from mtl_system_items msi
1989 where msi.organization_id = nvl(x_ship_to_organization_id, msi.organization_id)
1990 and msi.inventory_item_id = x_item_id
1991 and (msi.restrict_subinventories_code = 2
1992 or (msi.restrict_subinventories_code = 1
1993 and exists (select null
1994 from mtl_item_sub_inventories mis
1995 where mis.organization_id = nvl(x_ship_to_organization_id , mis.organization_id)
1996 and mis.inventory_item_id = msi.inventory_item_id
1997 and mis.secondary_inventory = msub.secondary_inventory_name))))))
1998 and msub.secondary_inventory_name = x_destination_subinventory;
1999
2000
2001 EXCEPTION
2002 WHEN NO_DATA_FOUND THEN
2003 x_destination_subinventory := NULL;
2004 fnd_message.set_name('PO', 'PO_RI_INVALID_DEST_SUBINV');
2005 po_copydoc_s1.online_report(x_online_report_id,
2006 x_sequence,
2007 substr(fnd_message.get, 1, 240),
2008 x_line_num, x_shipment_num, x_distribution_num);
2009
2010 WHEN OTHERS THEN
2011 x_destination_subinventory :=NULL;
2012 po_copydoc_s1.copydoc_sql_error('validate_dest_subinventory', x_progress, sqlcode,
2013 x_online_report_id,
2014 x_sequence,
2015 x_line_num, x_shipment_num, x_distribution_num);
2016
2017 -- RAISE COPYDOC_DIST_FAILURE;
2018
2019 END validate_dest_subinventory;
2020
2021
2022 PROCEDURE validate_contract_num(
2023 p_contract_id IN PO_LINES_ALL.contract_id%TYPE, -- <GC FPJ>
2024 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
2025 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
2026 x_line_num IN po_online_report_text.line_num%TYPE
2027 ) IS
2028 x_valid_flag VARCHAR2(2);
2029 x_progress VARCHAR2(4) := NULL;
2030
2031 l_contract_doc_num PO_HEADERS_ALL.segment1%TYPE; -- <GC FPJ>
2032
2033 BEGIN
2034
2035 IF (p_contract_id IS NULL) THEN -- <GC FPJ>
2036 RETURN;
2037 END IF;
2038
2039 x_progress := '001';
2040
2041 -- <GC FPJ START>
2042 -- Check frozen flag as we should not create a new po line referencing
2043 -- a frozen contract. Also, remove the check for enabled_flag as it is
2044 -- not used by PO. Moreover, effective date and approved flag check
2045 -- are removed since they are checked in regular submission check.
2046 -- The original query has been removed for clarity
2047
2048 -- SQL What: Given a contract, make sure that the contract is still valid
2049 -- SQL Why: This is part of the copy doc submission check
2050
2051 SELECT 'Y'
2052 INTO x_valid_flag
2053 FROM po_headers_all POH
2054 WHERE POH.po_header_id = p_contract_id
2055 AND POH.type_lookup_code = 'CONTRACT'
2056 AND POH.authorization_status = 'APPROVED'
2057 AND NVL(POH.closed_code, 'OPEN') = 'OPEN'
2058 AND NVL(POH.cancel_flag, 'N') <> 'Y'
2059 AND NVL(POH.frozen_flag, 'N') <> 'Y';
2060
2061 -- <GC FPJ END>
2062
2063 EXCEPTION
2064 WHEN NO_DATA_FOUND THEN
2065 -- <GC FPJ>
2066 -- When the error is displayed, we should should contract number
2067 l_contract_doc_num := PO_HEADERS_SV4.get_doc_num
2068 ( p_po_header_id => p_contract_id
2069 );
2070
2071 fnd_message.set_name('PO', 'PO_PO_INVALID_CONTRACT');
2072 fnd_message.set_token('VALUE', l_contract_doc_num, FALSE); -- <GC FPJ>
2073
2074 po_copydoc_s1.online_report(x_online_report_id,
2075 x_sequence,
2076 substr(fnd_message.get, 1, 240),
2077 x_line_num, 0, 0);
2078 WHEN OTHERS THEN
2079
2080 po_copydoc_s1.copydoc_sql_error('validate_contract_num', x_progress, sqlcode,
2081 x_online_report_id,
2082 x_sequence,
2083 x_line_num, 0, 0);
2084
2085 END validate_contract_num;
2086
2087 --<Bug 2864544 mbhargav START>
2088 --Private procedure that validate that the referenced global agreement is not cancelled or
2089 --finally closed
2090 PROCEDURE validate_global_ref(
2091 p_from_header_id IN po_headers_all.po_header_id%TYPE,
2092 p_from_line_id IN po_lines_all.po_line_id%TYPE,
2093 p_online_report_id IN po_online_report_text.online_report_id%TYPE,
2094 p_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
2095 p_line_num IN po_online_report_text.line_num%TYPE
2096 ) IS
2097 l_valid_flag VARCHAR2(2);
2098 l_progress VARCHAR2(4) := NULL;
2099
2100 BEGIN
2101
2102 IF (p_from_header_id is NULL or p_from_line_id IS NULL) THEN
2103 RETURN;
2104 END IF;
2105
2106 l_progress := '001';
2107
2108 SELECT 'Y'
2109 INTO l_valid_flag
2110 FROM po_headers_all
2111 WHERE po_header_id = p_from_header_id
2112 AND nvl(closed_code,'OPEN') <> 'FINALLY CLOSED'
2113 AND nvl(cancel_flag,'N') <> 'Y';
2114
2115 EXCEPTION
2116 WHEN NO_DATA_FOUND THEN
2117 fnd_message.set_name('PO', 'PO_SUB_CP_INVALID_GA_REF');
2118 po_copydoc_s1.online_report(p_online_report_id,
2119 p_sequence,
2120 substr(fnd_message.get, 1, 240),
2121 p_line_num, 0, 0);
2122 WHEN OTHERS THEN
2123 po_copydoc_s1.copydoc_sql_error('validate_global_ref', l_progress, sqlcode,
2124 p_online_report_id,
2125 p_sequence,
2126 p_line_num, 0, 0);
2127
2128 END validate_global_ref;
2129 --<Bug 2864544 mbhargav END>
2130
2131 --SERVICES FPJ Start
2132 ---------------------------------------------------------------------------------------------
2133 --Start of Comments
2134 --Name: validate_job
2135 --
2136 --Pre-reqs: None
2137 --
2138 --Modifies: None
2139 --
2140 --Locks: None
2141 --
2142 --Function: This procedure validates the job on copied po document
2143 --
2144 --
2145 --Parameters:
2146 --IN:
2147 -- p_job_id
2148 -- Job id on the copied PO line
2149 -- p_online_report_id
2150 -- If of the error report to show errors if any
2151 -- p_line_num
2152 -- PO Line which is being validated
2153 --IN OUT:
2154 -- p_sequence
2155 -- sequence of the online error report
2156 --
2157 --Testing: -
2158 --End of Comments
2159 -------------------------------------------------------------------------------------------------
2160 PROCEDURE validate_job(
2161 p_job_id IN po_lines.job_id%TYPE,
2162 p_online_report_id IN po_online_report_text.online_report_id%TYPE,
2163 p_line_num IN po_online_report_text.line_num%TYPE,
2164 p_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE
2165 ) IS
2166
2167 l_valid_flag VARCHAR2(2);
2168 l_progress VARCHAR2(4) := NULL;
2169 l_job_name per_jobs_vl.name%TYPE;
2170 l_bg_name per_business_groups_perf.name%TYPE;
2171 l_category mtl_categories_kfv.concatenated_segments%TYPE;
2172
2173 BEGIN
2174 l_progress := '010';
2175
2176 IF p_job_id is null THEN
2177 RETURN;
2178 END IF;
2179
2180 -- Sql What : Get the job name for the given job_id
2181 -- Sql Why : The Job name is used in the message token
2182
2183 l_progress := '020';
2184
2185 SELECT pj.name,
2186 pb.name
2187 INTO l_job_name,
2188 l_bg_name
2189 FROM per_jobs_vl pj,
2190 per_business_groups_perf pb
2191 WHERE pj.business_group_id = pb.business_group_id
2192 AND pj.job_id = p_job_id;
2193
2194
2195 -- Sql What : Check if the job exists in the Jobs table
2196 -- Sql Why : Check if the Job is valid in HR
2197 Begin
2198
2199 l_progress := '030';
2200
2201 SELECT 'Y'
2202 INTO l_valid_flag
2203 FROM per_jobs
2204 WHERE job_id = p_job_id
2205 AND sysdate between date_from and nvl(date_to,sysdate+1);
2206
2207 Exception
2208
2209 When no_data_found Then
2210
2211 IF nvl(hr_general.get_xbg_profile,'N') = 'Y' THEN
2212 fnd_message.set_name('PO', 'PO_SVC_JOB_INVALID_IN_HR_BG');
2213 fnd_message.set_token('JOB', l_job_name);
2214 fnd_message.set_token('BG', l_bg_name);
2215 ELSE
2216 fnd_message.set_name('PO', 'PO_SVC_JOB_INVALID_IN_HR');
2217 fnd_message.set_token('JOB', l_job_name);
2218 END IF;
2219
2220 po_copydoc_s1.online_report(p_online_report_id,
2221 p_sequence,
2222 substr(fnd_message.get, 1, 240),
2223 p_line_num, 0, 0);
2224 End;
2225
2226 -- Sql What : Check if the job exists in the Jobs table
2227 -- Sql Why : Check if the Job is valid in HR
2228 Begin
2229
2230 l_progress := '040';
2231
2232 SELECT mtl.concatenated_segments
2233 INTO l_category
2234 FROM po_job_associations pja,
2235 mtl_categories_kfv mtl
2236 WHERE pja.category_id = mtl.category_id
2237 AND pja.job_id = p_job_id;
2238
2239 SELECT 'Y'
2240 INTO l_valid_flag
2241 FROM po_job_associations
2242 WHERE job_id = p_job_id
2243 AND ( inactive_date is null
2244 OR trunc(sysdate) < trunc(inactive_date));
2245
2246 Exception
2247
2248 When no_data_found Then
2249 fnd_message.set_name('PO', 'PO_SVC_JOB_ASSOC_INVALID');
2250 fnd_message.set_token('JOB', l_job_name);
2251 fnd_message.set_token('CATEGORY', l_category);
2252 po_copydoc_s1.online_report(p_online_report_id,
2253 p_sequence,
2254 substr(fnd_message.get, 1, 240),
2255 p_line_num, 0, 0);
2256 End;
2257
2258 l_progress := '050';
2259
2260 EXCEPTION
2261 WHEN OTHERS THEN
2262 po_copydoc_s1.copydoc_sql_error('validate_job', l_progress, sqlcode,
2263 p_online_report_id,
2264 p_sequence,
2265 p_line_num, 0, 0);
2266 END;
2267
2268 -- SERVICES FPJ End
2269
2270 PROCEDURE submission_check_copydoc(
2271 x_po_header_id IN po_headers.po_header_id%TYPE,
2272 x_online_report_id IN NUMBER,
2273 x_sob_id IN financials_system_parameters.set_of_books_id%TYPE,
2274 x_inv_org_id IN financials_system_parameters.inventory_organization_id%TYPE) IS
2275
2276 x_po_header_record po_headers%ROWTYPE;
2277 x_po_line_record po_lines%ROWTYPE;
2278 x_po_shipment_record po_line_locations%ROWTYPE;
2279 x_po_distribution_record po_distributions%ROWTYPE;
2280
2281 x_line_num po_online_report_text.line_num%TYPE := NULL;
2282 x_shipment_num po_online_report_text.shipment_num%TYPE := NULL;
2283 x_distribution_num po_online_report_text.distribution_num%TYPE := NULL;
2284
2285 x_progress VARCHAR2(4);
2286 x_sequence po_online_report_text.sequence%TYPE := 1;
2287 x_return_code NUMBER := NULL;
2288
2289 --<ENCUMBRANCE FPJ START>
2290 l_encumbrance_on BOOLEAN;
2291 l_enc_type VARCHAR2(10);
2292 --<ENCUMBRANCE FPJ END>
2293
2294 -- <PO_PJM_VALIDATION FPI START>
2295 l_val_proj_result VARCHAR(1);
2296 l_val_proj_error_code VARCHAR2(80);
2297 -- <PO_PJM_VALIDATION FPI END>
2298
2299 l_return_status VARCHAR2(1); --< Bug 3265539 >
2300 l_ship_to_ou_id FINANCIALS_SYSTEM_PARAMS_ALL.org_id%TYPE; --< Bug 3265539 >
2301 l_is_complex_work_po BOOLEAN := FALSE; --Bug6314548
2302
2303 --<Bug 5332013 START>
2304 x_msg_application varchar2(30);
2305 x_msg_type varchar2(1);
2306 x_msg_token1 varchar2(30);
2307 x_msg_token2 varchar2(30);
2308 x_msg_token3 varchar2(30);
2309 x_msg_count number;
2310 x_msg_data varchar2(30);
2311 x_billable_flag varchar2(1);
2312 --<Bug 5332013 END>
2313
2314 BEGIN
2315
2316 x_progress := '001';
2317 -- Get header info and validate header info:
2318 -- - buyer
2319 -- - vendor, vendor_site, vendor_contact
2320 -- - ship-to-locatioin, bill-to-location
2321
2322 SELECT *
2323 INTO x_po_header_record
2324 FROM po_headers
2325 WHERE po_header_id = x_po_header_id;
2326
2327 /* Bug#6151431 ankusriv - FP for Bug#5871448 cvardia
2328 ** Desc : The below condition needs to be changed. We need to call Copy
2329 ** doc submission check only once when the PO is created and unapproved.
2330 **
2331 **
2332 ** Bug#2206125: kagarwal
2333 ** Desc: If the PO is for Re-approval we need not call the copy
2334 ** doc submission check - return with success.
2335 **
2336 ** if (nvl(x_po_header_record.approved_flag, 'N') = 'R') THEN
2337 ** RETURN;
2338 ** end if;
2339 */
2340
2341 if (x_po_header_record.approved_date IS NOT NULL) THEN
2342 Return;
2343 end if;
2344
2345
2346 --<ENCUMBRANCE FPJ start>
2347 IF (x_po_header_record.type_lookup_code = 'BLANKET') THEN
2348 l_enc_type := 'PA';
2349 ELSE
2350 l_enc_type := 'PO';
2351 END IF;
2352
2353 l_encumbrance_on := PO_CORE_S.is_encumbrance_on(
2354 p_doc_type => l_enc_type
2355 , p_org_id => NULL --defaults to current context
2356 );
2357 --<ENCUMBRANCE FPJ end>
2358
2359 -- ship-to-location and bill-to-location validation is included
2360 -- in the validate_vendor procedure
2361 validate_vendor(x_sob_id,
2362 x_inv_org_id,
2363 x_po_header_record.vendor_id,
2364 x_po_header_record.vendor_site_id,
2365 x_po_header_record.vendor_contact_id,
2366 x_po_header_record.ship_to_location_id,
2367 x_po_header_record.bill_to_location_id,
2368 x_po_header_record.ship_via_lookup_code,
2369 x_po_header_record.fob_lookup_code,
2370 x_po_header_record.freight_terms_lookup_code,
2371 x_po_header_record.terms_id,
2372 x_online_report_id,
2373 x_sequence);
2374
2375 /* the above procedure validates the location and terms on vendor. we need to
2376 validate these fields on the po header */
2377 validate_location_terms(x_sob_id,
2378 x_po_header_record.ship_to_location_id,
2379 x_po_header_record.bill_to_location_id,
2380 x_po_header_record.fob_lookup_code,
2381 x_po_header_record.freight_terms_lookup_code,
2382 x_po_header_record.terms_id,
2383 x_online_report_id,
2384 x_sequence);
2385
2386 validate_buyer_id(x_po_header_record.agent_id,
2387 x_online_report_id,
2388 x_sequence);
2389
2390 --< Shared Proc FPJ Start >
2391 IF (x_po_header_record.global_agreement_flag = 'Y') THEN
2392 validate_org_assignments
2393 (p_po_header_id => x_po_header_id,
2394 p_vendor_id => x_po_header_record.vendor_id,
2395 p_online_report_id => x_online_report_id,
2396 x_sequence => x_sequence);
2397 END IF;
2398 --< Shared Proc FPJ End >
2399 /*Bug 6314548 Check if the PO is complex PO.If so skip the check
2400 for validating destination_type_code for complex PO's */
2401 l_is_complex_work_po := PO_COMPLEX_WORK_PVT.is_complex_work_po(p_po_header_id => x_po_header_id);
2402
2403 --<ENCUMBRANCE FPJ START>
2404 IF nvl(x_po_header_record.encumbrance_required_flag, 'N') = 'Y' THEN
2405 --document is an encumbered BPA
2406 --the associated dist requires only a subset of the PO dist validations
2407
2408 OPEN pa_distribution_cursor(x_po_header_id);
2409 <<BPADISTS>>
2410 LOOP
2411 FETCH pa_distribution_cursor INTO x_po_distribution_record;
2412 EXIT BPADISTS WHEN pa_distribution_cursor%NOTFOUND;
2413
2414 -- Unlike PO, BPA distributions should NOT be validated on these fields:
2415 -- charge acct, accrual acct, variance acct IDs
2416 -- project/task information
2417 -- OSP information
2418 -- destination/deliver-to information
2419
2420 validate_account_id(
2421 x_account_id => x_po_distribution_record.budget_account_id
2422 , x_account_type => 'BUDGET'
2423 , x_gl_date => x_po_distribution_record.gl_encumbered_date
2424 , x_sob_id => x_sob_id
2425 , x_online_report_id => x_online_report_id
2426 , x_sequence => x_sequence
2427 , x_line_num => NULL
2428 , x_shipment_num => NULL
2429 , x_distribution_num => x_po_distribution_record.distribution_num
2430 );
2431
2432 END LOOP BPADISTS;
2433 CLOSE pa_distribution_cursor; --bug 3447914: close the cursor
2434
2435 ELSE
2436 --document is not an encumbered BPA
2437 --<ENCUMBRANCE FPJ END>
2438
2439 OPEN po_line_cursor(x_po_header_id);
2440 <<LINES>>
2441 LOOP
2442 FETCH po_line_cursor INTO x_po_line_record;
2443 EXIT LINES WHEN po_line_cursor%NOTFOUND;
2444
2445 x_line_num := x_po_line_record.line_num;
2446 -- Get each line and validate line info:
2447 -- - item
2448 -- - contract number
2449
2450 validate_item(x_po_line_record.item_id,
2451 x_po_line_record.item_description,
2452 x_po_line_record.item_revision,
2453 x_po_line_record.category_id,
2454 x_po_line_record.line_type_id,
2455 x_inv_org_id,
2456 x_online_report_id,
2457 x_sequence,
2458 x_line_num,
2459 x_return_code);
2460
2461 validate_contract_num(x_po_line_record.contract_id,
2462 x_online_report_id,
2463 x_sequence,
2464 x_line_num);
2465
2466 --<Bug 2864544 mbhargav START>
2467 --Call to validate that the referenced global agreement is not cancelled or
2468 --finally closed
2469 IF (x_po_line_record.from_header_id is NOT NULL or
2470 x_po_line_record.from_line_id is NOT NULL)
2471 THEN
2472 validate_global_ref(x_po_line_record.from_header_id,
2473 x_po_line_record.from_line_id,
2474 x_online_report_id,
2475 x_sequence,
2476 x_line_num);
2477 END IF;
2478 --<Bug 2864544 mbhargav END>
2479
2480 -- SERVICES FPJ Start
2481 -- Call the procedure to validate the HR job
2482
2483 validate_job(p_job_id => x_po_line_record.job_id,
2484 p_online_report_id => x_online_report_id,
2485 p_line_num => x_line_num,
2486 p_sequence => x_sequence );
2487
2488 -- SERVICES FPJ End
2489
2490 OPEN po_shipment_cursor(x_po_line_record.po_line_id);
2491 <<SHIPMENTS>>
2492 LOOP
2493 FETCH po_shipment_cursor INTO x_po_shipment_record;
2494 EXIT SHIPMENTS WHEN po_shipment_cursor%NOTFOUND;
2495
2496 x_shipment_num := x_po_shipment_record.shipment_num;
2497 -- Get each shipment and validate shipment info:
2498
2499 --< Shared Proc FPJ Start >
2500 IF (x_po_shipment_record.shipment_type = 'STANDARD') THEN
2501 validate_transaction_flow
2502 (p_ship_to_org_id => x_po_shipment_record.ship_to_organization_id,
2503 p_transaction_flow_header_id =>
2504 x_po_shipment_record.transaction_flow_header_id,
2505 p_item_category_id => x_po_line_record.category_id,
2506 p_online_report_id => x_online_report_id,
2507 p_line_num => x_line_num,
2508 p_shipment_num => x_shipment_num,
2509 p_item_id => x_po_line_record.item_id, -- Bug 3433867
2510 x_sequence => x_sequence);
2511 END IF;
2512 --< Shared Proc FPJ End >
2513
2514 --< Bug 3370735 Start >
2515 IF (x_po_header_record.type_lookup_code NOT IN ('BLANKET', 'RFQ'))
2516 THEN
2517 --< Bug 3265539 >
2518 -- Derive the operating unit associated with the ship-to org
2519 PO_CORE_S.get_inv_org_ou_id
2520 (x_return_status => l_return_status,
2521 p_inv_org_id => x_po_shipment_record.ship_to_organization_id,
2522 x_ou_id => l_ship_to_ou_id);
2523
2524 IF (l_return_status <> FND_API.g_ret_sts_success) THEN
2525 RAISE APP_EXCEPTION.application_exception;
2526 END IF;
2527 --< Bug 3265539 End >
2528
2529 END IF;
2530 --< Bug 3370735 End >
2531
2532 OPEN po_distribution_cursor(x_po_shipment_record.line_location_id);
2533 <<DISTRIBUTIONS>>
2534 LOOP
2535 FETCH po_distribution_cursor INTO x_po_distribution_record;
2536 EXIT DISTRIBUTIONS WHEN po_distribution_cursor%NOTFOUND;
2537
2538 x_distribution_num := x_po_distribution_record.distribution_num;
2539 -- Get each distribution and validate distribution info:
2540 -- - deliver-to-person
2541 -- - deliver-to-location, inventory_location
2542 -- - charge, accrual, budget, variance account
2543 -- - project id, task id
2544
2545 /* kagarwal: Check only when PO encumbrance is on */
2546
2547 IF ((x_po_distribution_record.budget_account_id IS NOT NULL)
2548 AND l_encumbrance_on) THEN
2549 validate_account_id(x_po_distribution_record.budget_account_id,
2550 'BUDGET',
2551 x_po_distribution_record.gl_encumbered_date,
2552 x_sob_id,
2553 x_online_report_id,
2554 x_sequence,
2555 x_line_num,
2556 x_shipment_num,
2557 x_distribution_num);
2558 END IF;
2559
2560 validate_account_id(x_po_distribution_record.code_combination_id,
2561 'CHARGE',
2562 x_po_distribution_record.gl_encumbered_date,
2563 x_sob_id,
2564 x_online_report_id,
2565 x_sequence,
2566 x_line_num,
2567 x_shipment_num,
2568 x_distribution_num);
2569
2570 validate_account_id(x_po_distribution_record.accrual_account_id,
2571 'ACCRUAL',
2572 x_po_distribution_record.gl_encumbered_date,
2573 x_sob_id,
2574 x_online_report_id,
2575 x_sequence,
2576 x_line_num,
2577 x_shipment_num,
2578 x_distribution_num);
2579
2580 validate_account_id(x_po_distribution_record.variance_account_id,
2581 'VARIANCE',
2582 x_po_distribution_record.gl_encumbered_date,
2583 x_sob_id,
2584 x_online_report_id,
2585 x_sequence,
2586 x_line_num,
2587 x_shipment_num,
2588 x_distribution_num);
2589
2590 -- <PO_PJM_VALIDATION FPI>
2591 -- Added the IF statement and ELSE clause. Removed the
2592 -- x_destination_type_code and x_ship_to_organized_id arguments
2593 -- from validate_project_id.
2594
2595 /*BUG6643377 Added the following IF condition to conditionally excecute the code depending on whether PO has a project reference or not*/
2596
2597 IF x_po_distribution_record.project_id IS NOT NULL THEN
2598
2599 IF (x_po_distribution_record.destination_type_code = 'EXPENSE') THEN
2600
2601 validate_project_id(x_po_distribution_record.project_id,
2602 -- x_po_distribution_record.destination_type_code,
2603 -- x_po_shipment_record.ship_to_organization_id,
2604 x_online_report_id,
2605 x_sequence,
2606 x_line_num,
2607 x_shipment_num,
2608 x_distribution_num);
2609
2610 validate_task_id(x_po_distribution_record.task_id,
2611 x_po_distribution_record.project_id,
2612 x_online_report_id,
2613 x_sequence,
2614 x_line_num,
2615 x_shipment_num,
2616 x_distribution_num);
2617
2618 -- Start Bug 3488117: Do expenditure date, type, and org validations.
2619 validate_exp_item_date(x_po_distribution_record.project_id,
2620 x_po_distribution_record.task_id,
2621 x_po_distribution_record.expenditure_item_date,
2622 x_online_report_id,
2623 x_sequence,
2624 x_line_num,
2625 x_shipment_num,
2626 x_distribution_num);
2627
2628 validate_exp_type(x_po_distribution_record.project_id,
2629 x_po_distribution_record.expenditure_type,
2630 x_online_report_id,
2631 x_sequence,
2632 x_line_num,
2633 x_shipment_num,
2634 x_distribution_num);
2635
2636 validate_exp_org(x_po_distribution_record.expenditure_organization_id,
2637 x_online_report_id,
2638 x_sequence,
2639 x_line_num,
2640 x_shipment_num,
2641 x_distribution_num);
2642 -- End Bug 3488117
2643
2644 /* Bug# 5332013, Calling the PA api to do the transaction control validation*/
2645 -- <Start Bug# 5332013>
2646 pa_transactions_pub.validate_transaction(X_project_id=>x_po_distribution_record.project_id
2647 , X_task_id => x_po_distribution_record.task_id
2648 , X_ei_date => x_po_distribution_record.expenditure_item_date
2649 , X_expenditure_type => x_po_distribution_record.expenditure_type
2650 , X_non_labor_resource => ''
2651 , X_person_id => x_po_distribution_record.deliver_to_person_id
2652 , X_quantity => ''
2653 , X_denom_currency_code => ''
2654 , X_acct_currency_code => ''
2655 , X_denom_raw_cost => ''
2656 , X_acct_raw_cost => ''
2657 , X_acct_rate_type => ''
2658 , X_acct_rate_date => ''
2659 , X_acct_exchange_rate => ''
2660 , X_transfer_ei => ''
2661 , X_incurred_by_org_id => x_po_distribution_record.expenditure_organization_id
2662 , X_nl_resource_org_id => ''
2663 , X_transaction_source => ''
2664 , X_calling_module => 'POXPOEPO'
2665 , X_vendor_id => ''
2666 , X_entered_by_user_id => ''
2667 , X_attribute_category => ''
2668 , X_attribute1 => ''
2669 , X_attribute2 => ''
2670 , X_attribute3 => ''
2671 , X_attribute4 => ''
2672 , X_attribute5 => ''
2673 , X_attribute6 => ''
2674 , X_attribute7 => ''
2675 , X_attribute8 => ''
2676 , X_attribute9 => ''
2677 , X_attribute10 => ''
2678 , X_attribute11 => ''
2679 , X_attribute12 => ''
2680 , X_attribute13 => ''
2681 , X_attribute14 => ''
2682 , X_attribute15 => ''
2683 , X_msg_application => X_msg_application
2684 , X_msg_type => X_msg_type
2685 , X_msg_token1 => X_msg_token1
2686 , X_msg_token2 => X_msg_token2
2687 , X_msg_token3 => X_msg_token3
2688 , X_msg_count => X_msg_count
2689 , X_msg_data => X_msg_data
2690 , X_billable_flag => X_billable_flag);
2691
2692 IF x_msg_type = 'E' and x_msg_data is not NULL THEN
2693 -- Write the message to the PO_ONLINE_REPORT_TEXT table as an error.
2694 po_copydoc_s1.online_report(x_online_report_id,
2695 x_sequence, x_msg_data,
2696 x_line_num, x_shipment_num, x_distribution_num,
2697 PO_COPYDOC_S1.G_ERROR_MESSAGE_TYPE);
2698 ELSIF x_msg_type = 'W' and x_msg_data is not NULL THEN
2699 -- Write the message to the PO_ONLINE_REPORT_TEXT table as a warning.
2700 po_copydoc_s1.online_report(x_online_report_id,
2701 x_sequence, x_msg_data,
2702 x_line_num, x_shipment_num, x_distribution_num,
2703 PO_COPYDOC_S1.G_WARNING_MESSAGE_TYPE);
2704 END IF;
2705 -- <End Bug# 5332013>
2706
2707 -- <PO_PJM_VALIDATION FPI START>
2708 ELSE
2709 --< Bug 3265539 Start >
2710 -- Call PO wrapper procedure to validate the PJM project
2711 PO_PROJECT_DETAILS_SV.validate_proj_references_wpr
2712 (p_inventory_org_id => x_po_shipment_record.ship_to_organization_id,
2713 p_operating_unit => l_ship_to_ou_id,
2714 p_project_id => x_po_distribution_record.project_id,
2715 p_task_id => x_po_distribution_record.task_id,
2716 p_date1 => x_po_shipment_record.need_by_date,
2717 p_date2 => x_po_shipment_record.promised_date,
2718 p_calling_function => 'POXCPSUB',
2719 x_error_code => l_val_proj_error_code,
2720 x_return_code => l_val_proj_result);
2721
2722 IF ( l_val_proj_result = PO_PROJECT_DETAILS_SV.pjm_validate_failure) THEN
2723 -- Write the message to the PO_ONLINE_REPORT_TEXT table as an error.
2724 po_copydoc_s1.online_report(x_online_report_id,
2725 x_sequence, FND_MESSAGE.get,
2726 x_line_num, x_shipment_num, x_distribution_num,
2727 PO_COPYDOC_S1.G_ERROR_MESSAGE_TYPE);
2728 ELSIF ( l_val_proj_result = PO_PROJECT_DETAILS_SV.pjm_validate_warning) THEN
2729 -- Write the message to the PO_ONLINE_REPORT_TEXT table as a warning.
2730 po_copydoc_s1.online_report(x_online_report_id,
2731 x_sequence, FND_MESSAGE.get,
2732 x_line_num, x_shipment_num, x_distribution_num,
2733 PO_COPYDOC_S1.G_WARNING_MESSAGE_TYPE);
2734 END IF;
2735 --< Bug 3265539 End >
2736
2737 END IF; /* destination type is 'EXPENSE' */
2738 -- <PO_PJM_VALIDATION FPI END>
2739 END IF; --BUG6643377
2740
2741 /*Bug6314548 Donot call this validation if PO is complex work PO */
2742 IF (NOT l_is_complex_work_po) then
2743 validate_destination_type_code(x_po_distribution_record.destination_type_code,
2744 x_po_line_record.item_id,
2745 x_po_shipment_record.ship_to_organization_id,
2746 nvl(x_po_distribution_record.accrue_on_receipt_flag,x_po_shipment_record.accrue_on_receipt_flag),
2747 x_online_report_id,
2748 x_sequence,
2749 x_line_num,
2750 x_shipment_num,
2751 X_distribution_num,
2752 x_po_shipment_record.line_location_id,
2753 x_po_line_record.po_line_id, ---Bug 3557910 Additional Input Parameter PO LINE ID
2754 x_po_shipment_record.transaction_flow_header_id); --< Bug 3546252 >
2755
2756 END IF; --Bug6314548
2757
2758 validate_deliver_to_loc_id(x_po_distribution_record.deliver_to_location_id,
2759 x_po_shipment_record.ship_to_organization_id,
2760 x_online_report_id,
2761 x_sequence,
2762 x_line_num,
2763 x_shipment_num,
2764 x_distribution_num);
2765
2766 validate_dest_subinventory(x_po_distribution_record.destination_subinventory,
2767 x_po_shipment_record.ship_to_organization_id,
2768 x_po_line_record.item_id,
2769 x_online_report_id,
2770 x_sequence,
2771 x_line_num,
2772 x_shipment_num,
2773 x_distribution_num);
2774
2775 validate_deliver_to_person_id(x_po_distribution_record.deliver_to_person_id,
2776 x_online_report_id,
2777 x_sequence,
2778 x_line_num,
2779 x_shipment_num,
2780 x_distribution_num);
2781
2782 -- Validate OSP data ( Bug: 2072545 )
2783 validate_osp_data(x_po_distribution_record.wip_entity_id,
2784 x_po_distribution_record.wip_operation_seq_num,
2785 x_po_distribution_record.wip_resource_seq_num,
2786 x_po_distribution_record.wip_repetitive_schedule_id,
2787 x_po_distribution_record.wip_line_id,
2788 x_po_distribution_record.destination_organization_id,
2789 x_online_report_id,
2790 x_sequence,
2791 x_line_num,
2792 x_shipment_num,
2793 x_distribution_num);
2794
2795 END LOOP DISTRIBUTIONS;
2796 CLOSE po_distribution_cursor;
2797
2798 END LOOP SHIPMENTS;
2799 CLOSE po_shipment_cursor;
2800
2801 END LOOP LINES;
2802 CLOSE po_line_cursor;
2803
2804 END IF; -- doc is encumbered BPA check <ENCUMBRANCE FPJ>
2805
2806 EXCEPTION
2807 WHEN OTHERS THEN
2808 po_copydoc_s1.copydoc_sql_error('submission_check_copydoc', x_progress, sqlcode,
2809 x_online_report_id,
2810 x_sequence,
2811 x_line_num,x_shipment_num,x_distribution_num);
2812
2813 END submission_check_copydoc;
2814
2815 --< Shared Proc FPJ Start >
2816 --------------------------------------------------------------------------------
2817 --Start of Comments
2818 --Name: validate_transaction_flow
2819 --Pre-reqs:
2820 -- None.
2821 --Modifies:
2822 -- PO_ONLINE_REPORT_TEXT
2823 --Locks:
2824 -- None.
2825 --Function:
2826 -- Validates the transaction flow if it is not NULL. If it is a valid
2827 -- flow with respect to the ship-to org, then validation passes. Otherwise,
2828 -- adds error to the online report.
2829 --Parameters:
2830 --IN:
2831 --p_ship_to_org_id
2832 --p_transaction_flow_header_id
2833 --p_item_category_id
2834 --p_online_report_id
2835 --p_line_num
2836 --p_shipment_num
2837 --p_item_id
2838 --IN OUT:
2839 --x_sequence
2840 -- PO_ONLINE_REPORT_TEXT.sequence.
2841 --End of Comments
2842 --------------------------------------------------------------------------------
2843 PROCEDURE validate_transaction_flow
2844 (
2845 p_ship_to_org_id IN NUMBER,
2846 p_transaction_flow_header_id IN NUMBER,
2847 p_item_category_id IN NUMBER,
2848 p_online_report_id IN NUMBER,
2849 p_line_num IN NUMBER,
2850 p_shipment_num IN NUMBER,
2851 p_item_id IN NUMBER, -- Bug 3433867
2852 x_sequence IN OUT NOCOPY NUMBER
2853 )
2854 IS
2855
2856 l_progress VARCHAR2(3);
2857 l_is_valid BOOLEAN;
2858 l_in_current_sob BOOLEAN;
2859 l_check_txn_flow BOOLEAN;
2860 l_return_status VARCHAR2(1);
2861
2862 l_transaction_flow_header_id
2863 PO_LINE_LOCATIONS_ALL.transaction_flow_header_id%TYPE;
2864
2865 BEGIN
2866 l_progress := '000';
2867
2868 IF (p_transaction_flow_header_id IS NOT NULL) THEN
2869
2870 l_progress := '010';
2871
2872 PO_SHARED_PROC_PVT.validate_ship_to_org
2873 (p_init_msg_list => FND_API.g_false,
2874 x_return_status => l_return_status,
2875 p_ship_to_org_id => p_ship_to_org_id,
2876 p_item_category_id => p_item_category_id,
2877 p_item_id => p_item_id, -- Bug 3433867
2878 x_is_valid => l_is_valid,
2879 x_in_current_sob => l_in_current_sob,
2880 x_check_txn_flow => l_check_txn_flow,
2881 x_transaction_flow_header_id => l_transaction_flow_header_id);
2882
2883 l_progress := '020';
2884
2885 IF (l_return_status <> FND_API.g_ret_sts_success) THEN
2886 FND_MESSAGE.set_encoded(encoded_message => FND_MSG_PUB.get);
2887 RAISE FND_API.g_exc_error;
2888 END IF;
2889
2890 l_progress := '030';
2891
2892 IF (NOT l_is_valid) OR
2893 (p_transaction_flow_header_id <>
2894 NVL(l_transaction_flow_header_id, -99))
2895 THEN
2896 FND_MESSAGE.set_name(application => 'PO',
2897 name => 'PO_COPY_DOC_INVALID_TXN_FLOW');
2898 RAISE FND_API.g_exc_error;
2899 END IF;
2900
2901 END IF; --< if txn flow header ID not null >
2902
2903 EXCEPTION
2904 WHEN FND_API.g_exc_error THEN
2905 PO_COPYDOC_S1.online_report
2906 (x_online_report_id => p_online_report_id,
2907 x_sequence => x_sequence,
2908 x_message => FND_MESSAGE.get,
2909 x_line_num => p_line_num,
2910 x_shipment_num => p_shipment_num,
2911 x_distribution_num => 0);
2912 WHEN OTHERS THEN
2913 PO_COPYDOC_S1.copydoc_sql_error
2914 (x_routine => 'PO_COPYDOC_SUB.validate_transaction_flow',
2915 x_progress => l_progress,
2916 x_sqlcode => SQLCODE,
2917 x_online_report_id => p_online_report_id,
2918 x_sequence => x_sequence,
2919 x_line_num => p_line_num,
2920 x_shipment_num => p_shipment_num,
2921 x_distribution_num => 0);
2922 END validate_transaction_flow;
2923
2924 --------------------------------------------------------------------------------
2925 --Start of Comments
2926 --Name: validate_org_assignments
2927 --Pre-reqs:
2928 -- None.
2929 --Modifies:
2930 -- PO_ONLINE_REPORT_TEXT
2931 -- PO_SESSION_GT
2932 -- PO_SESSION_GT_S
2933 --Locks:
2934 -- None.
2935 --Function:
2936 -- Validates all the enabled org assignments of the Global Agreement
2937 -- p_po_header_id. Checks if the Requesting Org, Purchasing Org, and
2938 -- Purchasing Site are still valid for all the enabled records. Inserts an
2939 -- error message to PO_ONLINE_REPORT_TEXT table under ID p_online_report_id
2940 -- for each check that fails.
2941 --Parameters:
2942 --IN:
2943 --p_po_header_id
2944 -- The PO header ID of the GA.
2945 --p_vendor_id
2946 -- The vendor ID of the GA header.
2947 --p_online_report_id
2948 -- The online report ID to write to if an error occurs.
2949 --IN OUT:
2950 --x_sequence
2951 -- The online report sequence, which gets incremented when an error is
2952 -- written to PO_ONLINE_REPORT_TEXT.
2953 --End of Comments
2954 --------------------------------------------------------------------------------
2955 PROCEDURE validate_org_assignments
2956 (
2957 p_po_header_id IN NUMBER,
2958 p_vendor_id IN NUMBER,
2959 p_online_report_id IN NUMBER,
2960 x_sequence IN OUT NOCOPY NUMBER
2961 )
2962 IS
2963
2964 l_progress VARCHAR2(3);
2965 l_return_status VARCHAR2(1);
2966 l_user_id NUMBER;
2967 l_login_id NUMBER;
2968 l_err_prefix VARCHAR2(256);
2969 l_populate_failed_exc EXCEPTION;
2970
2971 l_key PO_SESSION_GT.key%TYPE;
2972 l_text_line PO_ONLINE_REPORT_TEXT.text_line%TYPE;
2973 l_message_type PO_ONLINE_REPORT_TEXT.message_type%TYPE;
2974
2975 BEGIN
2976
2977 l_progress := '000';
2978
2979 -- Populate the global session table with all enabled org assignments for
2980 -- the current GA.
2981 populate_session_gt(x_return_status => l_return_status,
2982 p_po_header_id => p_po_header_id,
2983 p_online_report_id => p_online_report_id,
2984 x_sequence => x_sequence,
2985 x_key => l_key);
2986
2987 IF (l_return_status <> FND_API.g_ret_sts_success) THEN
2988 RAISE l_populate_failed_exc;
2989 END IF;
2990
2991 l_progress := '010';
2992
2993 l_user_id := FND_GLOBAL.user_id;
2994 l_login_id := FND_GLOBAL.login_id;
2995 l_message_type := PO_COPYDOC_S1.g_error_message_type;
2996 l_err_prefix := FND_MESSAGE.get_string(appin => 'PO',
2997 namein => 'PO_GA_ORG_ASSIGN_PREFIX');
2998
2999 --------------------------------------------------------------
3000 l_progress := '020';
3001
3002 -- Validate the Requesting Orgs for all enabled org assignments
3003 l_text_line := FND_MESSAGE.get_string
3004 (appin => 'PO',
3005 namein => 'PO_GA_INVALID_REQUESTING_ORG');
3006 INSERT INTO po_online_report_text
3007 (
3008 online_report_id,
3009 last_update_login,
3010 last_updated_by,
3011 last_update_date,
3012 created_by,
3013 creation_date,
3014 sequence,
3015 text_line,
3016 message_type
3017 )
3018 --SQL What: Check if the requesting orgs for enabled org assignments are
3019 -- still valid.
3020 --SQL Why: Insert an error for each invalid requesting org
3021 SELECT p_online_report_id,
3022 l_login_id,
3023 l_user_id,
3024 SYSDATE,
3025 l_user_id,
3026 SYSDATE,
3027 x_sequence + ROWNUM,
3028 SUBSTRB(l_err_prefix||psg.char1||l_text_line,1,2000),
3029 l_message_type
3030 FROM po_session_gt psg
3031 WHERE psg.key = l_key
3032 AND NOT EXISTS
3033 (SELECT 'is active ou'
3034 FROM hr_operating_units hou,
3035 financials_system_params_all fspa,
3036 po_system_parameters_all pspa
3037 WHERE hou.organization_id = psg.num1
3038 AND hou.organization_id = pspa.org_id
3039 AND pspa.org_id = fspa.org_id
3040 AND TRUNC(SYSDATE) BETWEEN TRUNC(hou.date_from)
3041 AND TRUNC(NVL(hou.date_to, SYSDATE+1))
3042 );
3043
3044 --Increment the x_sequence with number of errors reported in last query
3045 x_sequence := x_sequence + SQL%ROWCOUNT;
3046
3047 --------------------------------------------------------------
3048 l_progress := '030';
3049
3050 -- Validate the Purchasing Orgs for all enabled org assignments
3051 l_text_line := FND_MESSAGE.get_string
3052 (appin => 'PO',
3053 namein => 'PO_GA_INVALID_PURCHASING_ORG');
3054 INSERT INTO po_online_report_text
3055 (
3056 online_report_id,
3057 last_update_login,
3058 last_updated_by,
3059 last_update_date,
3060 created_by,
3061 creation_date,
3062 sequence,
3063 text_line,
3064 message_type
3065 )
3066 --SQL What: Check if the purchasing orgs for enabled org assignments are
3067 -- still valid.
3068 --SQL Why: Insert an error for each invalid purchasing org
3069 SELECT p_online_report_id,
3070 l_login_id,
3071 l_user_id,
3072 SYSDATE,
3073 l_user_id,
3074 SYSDATE,
3075 x_sequence + ROWNUM,
3076 SUBSTRB(l_err_prefix||psg.char1||l_text_line,1,2000),
3077 l_message_type
3078 FROM po_session_gt psg
3079 WHERE psg.key = l_key
3080 AND (NOT EXISTS
3081 (SELECT 'is active ou'
3082 FROM hr_operating_units hou,
3083 financials_system_params_all fspa,
3084 po_system_parameters_all pspa
3085 WHERE hou.organization_id = psg.num2
3086 AND hou.organization_id = pspa.org_id
3087 AND pspa.org_id = fspa.org_id
3088 AND TRUNC(SYSDATE) BETWEEN TRUNC(hou.date_from)
3089 AND TRUNC(NVL(hou.date_to, SYSDATE+1))
3090 )
3091 OR
3092 NOT EXISTS
3093 (SELECT 'encumbrance check'
3094 FROM financials_system_params_all fspa1,
3095 financials_system_params_all fspa2
3096 WHERE fspa1.org_id = psg.num1
3097 AND fspa2.org_id = psg.num2
3098 AND ( fspa1.org_id = fspa2.org_id
3099 OR
3100 ( NVL(fspa1.purch_encumbrance_flag,'N') = 'N'
3101 AND NVL(fspa1.req_encumbrance_flag,'N') = 'N'
3102 AND NVL(fspa2.purch_encumbrance_flag,'N') = 'N'
3103 AND NVL(fspa2.req_encumbrance_flag,'N') = 'N'
3104 )
3105 )
3106 )
3107 OR
3108 NOT EXISTS
3109 (SELECT 'Valid vendor site for POU'
3110 FROM po_vendor_sites_all pvsa
3111 WHERE pvsa.vendor_id = p_vendor_id
3112 AND pvsa.org_id = psg.num2
3113 AND pvsa.purchasing_site_flag = 'Y'
3114 AND NVL(pvsa.rfq_only_site_flag, 'N') = 'N'
3115 AND TRUNC(SYSDATE) <
3116 TRUNC(NVL(pvsa.inactive_date, SYSDATE+1))
3117 )
3118 );
3119
3120 --Increment the x_sequence with number of errors reported in last query
3121 x_sequence := x_sequence + SQL%ROWCOUNT;
3122
3123 --------------------------------------------------------------
3124 l_progress := '040';
3125
3126 -- Validate the Purchasing Sites for all enabled org assignments
3127 l_text_line := FND_MESSAGE.get_string
3128 (appin => 'PO',
3129 namein => 'PO_GA_INVALID_PURCHASING_SITE');
3130 INSERT INTO po_online_report_text
3131 (
3132 online_report_id,
3133 last_update_login,
3134 last_updated_by,
3135 last_update_date,
3136 created_by,
3137 creation_date,
3138 sequence,
3139 text_line,
3140 message_type
3141 )
3142 --SQL What: Check if the purchasing sites for enabled org assignments are
3143 -- still valid.
3144 --SQL Why: Insert an error for each invalid purchasing site
3145 SELECT p_online_report_id,
3146 l_login_id,
3147 l_user_id,
3148 SYSDATE,
3149 l_user_id,
3150 SYSDATE,
3151 x_sequence + ROWNUM,
3152 SUBSTRB(l_err_prefix||psg.char1||l_text_line,1,2000),
3153 l_message_type
3154 FROM po_session_gt psg
3155 WHERE psg.key = l_key
3156 AND NOT EXISTS
3157 (SELECT 'Valid vendor site'
3158 FROM po_vendor_sites_all pvsa
3159 WHERE pvsa.vendor_site_id = psg.num3
3160 AND pvsa.vendor_id = p_vendor_id
3161 AND pvsa.org_id = psg.num2
3162 AND pvsa.purchasing_site_flag = 'Y'
3163 AND NVL(pvsa.rfq_only_site_flag, 'N') = 'N'
3164 AND TRUNC(SYSDATE) < TRUNC(NVL(pvsa.inactive_date, SYSDATE+1))
3165 );
3166
3167 --Increment the x_sequence with number of errors reported in last query
3168 x_sequence := x_sequence + SQL%ROWCOUNT;
3169
3170 EXCEPTION
3171 WHEN l_populate_failed_exc THEN
3172 -- If caught, error already inserted, so do nothing here.
3173 NULL;
3174 WHEN OTHERS THEN
3175 PO_COPYDOC_S1.copydoc_sql_error
3176 (x_routine => 'PO_COPYDOC_SUB.validate_org_assignments',
3177 x_progress => l_progress,
3178 x_sqlcode => SQLCODE,
3179 x_online_report_id => p_online_report_id,
3180 x_sequence => x_sequence,
3181 x_line_num => 0,
3182 x_shipment_num => 0,
3183 x_distribution_num => 0);
3184 END validate_org_assignments;
3185
3186 --------------------------------------------------------------------------------
3187 --Start of Comments
3188 --Name: populate_session_gt
3189 --Pre-reqs:
3190 -- None.
3191 --Modifies:
3192 -- PO_SESSION_GT
3193 -- PO_SESSION_GT_S
3194 --Locks:
3195 -- None.
3196 --Function:
3197 -- Populates the global session table PO_SESSION_GT with all the org
3198 -- assignments of p_po_header_id that are enabled. The columns populated are:
3199 -- key -> x_key
3200 -- num1 -> Requesting Org ID
3201 -- num2 -> Purchasing Org ID
3202 -- num3 -> Vendor Site ID
3203 -- char1 -> Requesting Org Name
3204 --Parameters:
3205 --IN:
3206 --p_po_header_id
3207 -- The PO header ID of the GA.
3208 --p_online_report_id
3209 -- The online report ID to write to if an error occurs.
3210 --IN OUT:
3211 --x_sequence
3212 -- The online report sequence, which gets incremented when an error is
3213 -- written to PO_ONLINE_REPORT_TEXT.
3214 --OUT:
3215 --x_return_status
3216 -- FND_API.g_ret_sts_success - success
3217 -- FND_API.g_ret_sts_unexp_error - unexpected error occurs
3218 --x_key
3219 -- The next value in the PO_SESSION_GT_S sequence to be used as the key for
3220 -- all the data inserted.
3221 --End of Comments
3222 --------------------------------------------------------------------------------
3223 PROCEDURE populate_session_gt
3224 (
3225 x_return_status OUT NOCOPY VARCHAR2,
3226 p_po_header_id IN NUMBER,
3227 p_online_report_id IN NUMBER,
3228 x_sequence IN OUT NOCOPY NUMBER,
3229 x_key OUT NOCOPY NUMBER
3230 )
3231 IS
3232
3233 l_progress VARCHAR2(3);
3234
3235 BEGIN
3236
3237 l_progress := '000';
3238 x_return_status := FND_API.g_ret_sts_success;
3239
3240 -- Get the key for the global session table to use for this GA
3241 SELECT po_session_gt_s.nextval
3242 INTO x_key
3243 FROM DUAL;
3244
3245 l_progress := '010';
3246
3247 -- Populate the global session table with all enabled org assignments for
3248 -- the current GA.
3249 INSERT INTO po_session_gt
3250 (
3251 key,
3252 num1,
3253 num2,
3254 num3,
3255 char1
3256 )
3257 --SQL What: Get info for all enabled org assignments of this GA
3258 --SQL Why: Store info temporarily for org assignment submission checks
3259 SELECT x_key,
3260 pgoa.organization_id,
3261 pgoa.purchasing_org_id,
3262 pgoa.vendor_site_id,
3263 hout.name
3264 FROM po_ga_org_assignments pgoa,
3265 hr_all_organization_units_tl hout
3266 WHERE pgoa.po_header_id = p_po_header_id
3267 AND pgoa.organization_id = hout.organization_id
3268 AND hout.language = USERENV('LANG')
3269 AND pgoa.enabled_flag = 'Y';
3270
3271 IF (SQL%ROWCOUNT = 0) THEN
3272 -- Need to raise error if nothing gets inserted
3273 RAISE NO_DATA_FOUND;
3274 END IF;
3275
3276 EXCEPTION
3277 WHEN OTHERS THEN
3278 x_return_status := FND_API.g_ret_sts_unexp_error;
3279 PO_COPYDOC_S1.copydoc_sql_error
3280 (x_routine => 'PO_COPYDOC_SUB.populate_session_gt',
3281 x_progress => l_progress,
3282 x_sqlcode => SQLCODE,
3283 x_online_report_id => p_online_report_id,
3284 x_sequence => x_sequence,
3285 x_line_num => 0,
3286 x_shipment_num => 0,
3287 x_distribution_num => 0);
3288 END populate_session_gt;
3289
3290 --< Shared Proc FPJ End >
3291
3292 END po_copydoc_sub;