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