[Home] [Help]
PACKAGE BODY: APPS.PO_CHANGE_API1_S
Source
1 PACKAGE BODY PO_CHANGE_API1_S AS
2 /* $Header: POXCHN1B.pls 120.10 2011/12/27 09:16:23 pamandav ship $*/
3
4 g_pkg_name CONSTANT VARCHAR2(50) := 'PO_CHANGE_API1_S';
5
6 g_sysdate DATE := SYSDATE;
7 g_user_id NUMBER := fnd_global.user_id;
8 g_login_id NUMBER := fnd_global.login_id;
9
10 FUNCTION record_acceptance
11 (
12 X_PO_NUMBER VARCHAR2,
13 X_RELEASE_NUMBER NUMBER,
14 X_REVISION_NUMBER NUMBER,
15 X_ACTION VARCHAR2,
16 X_ACTION_DATE DATE,
17 X_EMPLOYEE_ID NUMBER,
18 X_ACCEPTED_FLAG VARCHAR2,
19 X_ACCEPTANCE_LOOKUP_CODE VARCHAR2,
20 X_NOTE CLOB,
24 p_org_id IN NUMBER
21 X_INTERFACE_TYPE VARCHAR2,
22 X_TRANSACTION_ID NUMBER,
23 VERSION VARCHAR2,
25 ) RETURN NUMBER IS
26
27 l_po_header_id NUMBER := NULL;
28 l_po_release_id NUMBER := NULL;
29 l_current_revision NUMBER := NULL;
30 l_doc_type VARCHAR2(30);
31 l_doc_subtype VARCHAR2(30);
32 l_INTERFACE_TYPE VARCHAR2(25);
33 l_TRANSACTION_ID NUMBER := NULL;
34 l_count NUMBER := NULL;
35 l_result NUMBER := 1;
36 l_employee_id NUMBER := NULL;
37
38 -- Bug 2850566
39 l_rowid ROWID;
40 l_Last_Update_Login PO_ACCEPTANCES.last_update_login%TYPE;
41 l_Last_Update_Date PO_ACCEPTANCES.last_update_date%TYPE;
42 l_Last_Updated_By PO_ACCEPTANCES.last_updated_by%TYPE;
43 l_acc_po_header_id PO_HEADERS_ALL.po_header_id%TYPE;
44 l_acceptance_id PO_ACCEPTANCES.acceptance_id%TYPE;
45 -- End of Bug 2850566
46 l_org_id PO_HEADERS_ALL.org_id%type := p_org_id;
47 CURSOR c_po_header IS
48 select PO_HEADER_ID
49 from PO_HEADERS
50 where segment1 = X_PO_NUMBER
51 and type_lookup_code in ('STANDARD', 'BLANKET', 'CONTRACT', 'PLANNED');
52
53 CURSOR c_po_release IS
54 select po_release_id
55 from po_releases
56 where po_header_id = l_po_header_id
57 and release_num = X_RELEASE_NUMBER;
58
59 BEGIN
60
61 IF (X_TRANSACTION_ID is null) then
62 select PO_INTERFACE_ERRORS_S.nextval
63 into l_TRANSACTION_ID from sys.dual;
64 else
65 l_TRANSACTION_ID := X_TRANSACTION_ID;
66 end if;
67
68 l_INTERFACE_TYPE := nvl(X_INTERFACE_TYPE, 'CHANGEPO');
69
70 --<Bug#4581621 Start>
71 PO_MOAC_UTILS_PVT.validate_orgid_pub_api(x_org_id => l_org_id);
72 PO_MOAC_UTILS_PVT.set_policy_context('S',l_org_id);
73 --<Bug#4581621 End>
74
75 l_result := PO_CHANGE_API1_S.check_mandatory_params(
76 X_PO_NUMBER,
77 X_REVISION_NUMBER,
78 VERSION,
79 l_INTERFACE_TYPE,
80 l_transaction_id);
81 if (l_result = 0) then
82 return l_result;
83 end if;
84
85 if (X_ACCEPTED_FLAG not in ('Y','N') or
86 X_ACCEPTED_FLAG is null) then
87 PO_CHANGE_API1_S.insert_error(l_interface_type,
88 l_transaction_id,
89 'X_ACCEPTED_FLAG',
90 NULL,
91 'PO_CHNG_INVALID_ACCEPTED_FLAG',
92 NULL,
93 NULL);
94 return 0;
95 end if;
96
97 if (X_ACCEPTANCE_LOOKUP_CODE is null) then
98 PO_CHANGE_API1_S.insert_error(l_interface_type,
99 l_transaction_id,
100 'X_ACCEPTANCE_LOOKUP_CODE',
101 NULL,
102 'PO_ALL_CNL_PARAM_NULL',
103 NULL,
104 NULL);
105 return 0;
106 else
107 select count(*)
108 into l_count
109 from PO_LOOKUP_CODES
110 where lookup_type = 'ACCEPTANCE TYPE'
111 and lookup_code = X_ACCEPTANCE_LOOKUP_CODE;
112
113 if (l_count = 0) then
114 PO_CHANGE_API1_S.insert_error(l_interface_type,
115 l_transaction_id,
116 'X_ACCEPTANCE_LOOKUP_CODE',
117 NULL,
118 'PO_CHNG_INVALID_ACC_LK_CODE',
119 NULL,
120 NULL);
121 return 0;
122 end if;
123 end if;
124
125 OPEN c_po_header;
126 FETCH c_po_header INTO l_po_header_id;
127
128 if (c_po_header%NOTFOUND or l_po_header_id is null) then
129 PO_CHANGE_API1_S.insert_error(l_interface_type,
130 l_transaction_id,
131 'PO_HEADER_ID',
132 'PO_HEADERS',
133 'PO_NOPOFOUND',
134 NULL,
135 NULL);
136 return 0;
137 end if;
138 CLOSE c_po_header;
139
140 IF (X_RELEASE_NUMBER is not null) THEN
141 OPEN c_po_release;
142 FETCH c_po_release INTO l_po_release_id;
143
144 if (c_po_release%NOTFOUND or
145 l_po_release_id is null) then
146 PO_CHANGE_API1_S.insert_error(l_interface_type,
147 l_transaction_id,
148 'PO_RELEASE_ID',
149 'PO_RELEASES',
150 'PO_CHNG_INVALID_RELEASE_NUM',
151 NULL,
152 NULL);
153 return 0;
154 end if;
155 CLOSE c_po_release;
156
157 l_employee_id := X_employee_id;
158
159 l_result := PO_CHANGE_API1_S.validate_acceptance(
160 null,
161 l_po_release_id,
162 l_employee_id,
163 X_REVISION_NUMBER,
164 l_current_revision,
165 l_interface_type,
166 l_transaction_id);
167
168 ELSE
169 l_employee_id := X_employee_id; --Bug12638303
170
171 l_result := PO_CHANGE_API1_S.validate_acceptance(
172 l_po_header_id,
173 null,
174 l_employee_id,
175 X_REVISION_NUMBER,
176 l_current_revision,
177 l_interface_type,
178 l_transaction_id);
179 END IF;
180
181 if (l_result = 0) then
182 return l_result;
183 else
184
185 -- Bug 2850566 RBAIRRAJ
186 -- Calling the Acceptances row handler to insert into the PO_ACCEPTANCES table
187 -- instead of writing an Insert statement.
188
189 IF l_po_release_id IS NULL THEN
190 l_acc_po_header_id := l_po_header_id;
191 ELSE
192 l_acc_po_header_id := NULL;
193 END IF;
194
195 PO_ACCEPTANCES_INS_PVT.insert_row(
196 x_rowid => l_rowid,
197 x_acceptance_id => l_acceptance_id,
198 x_Last_Update_Date => l_Last_Update_Date,
199 x_Last_Updated_By => l_Last_Updated_By,
200 x_Last_Update_Login => l_Last_Update_Login,
201 p_creation_date => g_sysdate,
202 p_created_by => g_user_id,
203 p_po_header_id => l_acc_po_header_id,
204 p_po_release_id => l_Po_Release_Id,
205 p_action => nvl(X_ACTION, 'ACCPO'),
206 p_action_date => nvl(X_ACTION_DATE, g_sysdate),
207 p_employee_id => l_employee_id,
208 p_revision_num => X_REVISION_NUMBER,
209 p_accepted_flag => X_ACCEPTED_FLAG,
210 p_acceptance_lookup_code => X_ACCEPTANCE_LOOKUP_CODE,
211 p_note => X_NOTE);
212
213 -- End of Bug 2850566 RBAIRRAJ
214
215 /*Bug # 5597797 The below code is modified to update the acceptance required flag to 'N' only if the a_accepted_flag is 'Y'.
216 This is in sync with the functionality of the Enter PO --> Tools --> Acceptances form.*/
217
218 IF (X_REVISION_NUMBER = l_current_revision) then
219 IF x_accepted_flag = 'Y' THEN
220 IF (l_po_release_id is null) then
221 update PO_HEADERS
222 /* Changed ACCEPTANCE_REQUIRED_FLAG value from null to N*/
223 set ACCEPTANCE_REQUIRED_FLAG = 'N', -- bug 4721255
224 ACCEPTANCE_DUE_DATE = null,
225 last_update_date = g_sysdate,
226 last_updated_by = g_user_id
227 where PO_HEADER_ID = l_po_header_id;
228 ELSE
229 update PO_RELEASES
230 set ACCEPTANCE_REQUIRED_FLAG = 'N', -- bug 4721255
231 ACCEPTANCE_DUE_DATE = null,
232 last_update_date = g_sysdate,
233 last_updated_by = g_user_id
234 where PO_RELEASE_ID = l_po_release_id;
235 END IF;
236 END IF;
237 END IF;
238
239 END IF;
240 -- End Bug # 5597797
241 return l_result;
242
243 EXCEPTION
244 WHEN OTHERS THEN
245 return 0;
246 END record_acceptance;
247
248 FUNCTION validate_acceptance
249 ( X_po_header_id IN NUMBER,
250 X_po_release_id IN NUMBER,
251 X_employee_id IN OUT NOCOPY NUMBER,
252 X_revision_num IN NUMBER,
253 X_current_revision IN OUT NOCOPY NUMBER,
254 X_interface_type IN VARCHAR2,
255 X_transaction_id IN NUMBER
256 ) RETURN NUMBER IS
257
258 l_cancel_flag VARCHAR2(1);
259 l_status VARCHAR2(25);
260 l_closed_code VARCHAR2(25);
261 l_count NUMBER := 0;
262 l_result NUMBER := 1;
263
264 BEGIN
265
266 IF (X_po_release_id is not null) then
267 select nvl(closed_code, 'OPEN'),
268 nvl(authorization_status, 'INCOMPLETE'),
269 nvl(cancel_flag, 'N'),
270 revision_num,
271 nvl(X_employee_id, agent_id)
272 into l_closed_code,
273 l_status,
274 l_cancel_flag,
275 X_current_revision,
276 X_employee_id
277 from po_releases
278 where po_release_id = X_po_release_id;
279
280 ELSIF (X_po_header_id is not null) then
281 select nvl(closed_code, 'OPEN'),
282 nvl(authorization_status, 'INCOMPLETE'),
283 nvl(cancel_flag, 'N'),
284 revision_num,
285 nvl(X_employee_id, agent_id)
286 into l_closed_code,
287 l_status,
288 l_cancel_flag,
289 X_current_revision,
290 X_employee_id
291 from po_headers
292 where po_header_id = X_po_header_id;
293 END IF;
294
295 if (l_closed_code = 'FINALLY CLOSED') then
296 l_result := 0;
297 PO_CHANGE_API1_S.insert_error(X_interface_type,
298 X_transaction_id,
299 'CLOSED_CODE',
300 'PO_HEADERS',
301 'PO_ALL_DOC_CANNOT_BE_OPENED',
302 NULL,
303 NULL);
304 end if;
305
306 -- Bug#4156064: allow changing of PO with incomplete status also
307 if (l_status not in ('APPROVED', 'REQUIRES REAPPROVAL', 'INCOMPLETE')) then
308 l_result := 0;
309 PO_CHANGE_API1_S.insert_error(X_interface_type,
310 X_transaction_id,
311 'AUTHORIZATION_STATUS',
312 'PO_HEADERS',
313 'PO_ALL_DOC_CANNOT_BE_OPENED',
314 NULL,
315 NULL);
316 end if;
317
318 if (l_cancel_flag = 'Y') then
319 l_result := 0;
320 PO_CHANGE_API1_S.insert_error(X_interface_type,
321 X_transaction_id,
322 'CANCEL_FLAG',
323 'PO_HEADERS',
324 'PO_ALL_DOC_CANNOT_BE_OPENED',
325 NULL,
326 NULL);
327 end if;
328
329 /* Bug 9029360 : Accepting revisions of older versions should not be permitted.
330 Changing condtion to check whether passed revision_num is not same as that of
331 current_revision, then insert error. */
332
333 if (X_revision_num <> X_current_revision) then
334 l_result := 0;
335 PO_CHANGE_API1_S.insert_error(X_interface_type,
336 X_transaction_id,
337 'X_REVISION_NUMBER',
338 NULL,
339 'PO_CHNG_REVISION_NOT_MATCH',
340 NULL,
341 NULL);
342 end if;
343
344 select count(*)
345 into l_count
346 from per_people_f
347 where person_id = X_employee_id
348 and trunc(g_sysdate) between effective_start_date
349 and nvl(effective_end_date, g_sysdate+1);
350
351 if (l_count = 0) then
352 l_result := 0;
353 PO_CHANGE_API1_S.insert_error( X_interface_type,
354 X_transaction_id,
355 'X_EMPLOYEE_ID',
356 NULL,
357 'PO_CHNG_NOT_VALID_EMPLOYEE',
358 NULL,
359 NULL);
360 end if;
361
362 return l_result;
363
364 EXCEPTION
365 WHEN OTHERS THEN
366 return 0;
367 END validate_acceptance;
368
369 FUNCTION check_mandatory_params
370 (
371 X_PO_NUMBER VARCHAR2,
372 X_REVISION_NUMBER NUMBER,
373 VERSION VARCHAR2,
374 X_INTERFACE_TYPE VARCHAR2,
375 X_TRANSACTION_ID NUMBER
376 ) RETURN NUMBER IS
377
378 BEGIN
379
380 if (X_PO_NUMBER is null) then
381 PO_CHANGE_API1_S.insert_error(X_INTERFACE_TYPE,
382 X_TRANSACTION_ID,
383 'PO_NUMBER',
384 NULL,
385 'PO_ALL_CNL_PARAM_NULL',
386 NULL,
387 NULL);
388 return 0;
389 end if;
390
391 if (X_REVISION_NUMBER is null) then
392 PO_CHANGE_API1_S.insert_error(X_INTERFACE_TYPE,
393 X_TRANSACTION_ID,
394 'REVISION_NUM',
395 NULL,
396 'PO_ALL_CNL_PARAM_NULL',
397 NULL,
398 NULL);
399 return 0;
400 end if;
401
402 if (VERSION is null) then
403 PO_CHANGE_API1_S.insert_error(X_INTERFACE_TYPE,
404 X_TRANSACTION_ID,
405 'VERSION',
406 NULL,
407 'PO_ALL_CNL_PARAM_NULL',
408 NULL,
409 NULL);
410 return 0;
411 elsif (VERSION <> '1.0') then
412 PO_CHANGE_API1_S.insert_error(X_INTERFACE_TYPE,
413 X_TRANSACTION_ID,
414 'VERSION',
415 NULL,
416 'PO_CHNG_INVALID_VERSION',
417 NULL,
418 NULL);
419 return 0;
420 end if;
421
422 return 1;
423
424 EXCEPTION
425 WHEN OTHERS THEN
426 return 0;
427 END check_mandatory_params;
428
429 PROCEDURE insert_error
430 ( X_INTERFACE_TYPE IN VARCHAR2,
431 X_transaction_id IN NUMBER,
432 X_column_name IN VARCHAR2,
433 X_TABLE_NAME IN VARCHAR2,
434 X_MESSAGE_NAME IN VARCHAR2,
435 X_token_name IN VARCHAR2,
436 X_token_value IN VARCHAR2
437 ) IS
438
439 pragma AUTONOMOUS_TRANSACTION;
440 l_error_message VARCHAR2(2000) := NULL;
441
442 BEGIN
443 FND_MESSAGE.set_name('PO', X_MESSAGE_NAME);
444
445 if (X_token_name is not null) then
446 FND_MESSAGE.set_token(X_token_name, X_token_value);
447 end if;
448
449 l_error_message := FND_MESSAGE.get;
450
451 insert into PO_INTERFACE_ERRORS (
452 INTERFACE_TYPE,
453 INTERFACE_TRANSACTION_ID,
454 COLUMN_NAME,
455 ERROR_MESSAGE,
456 PROCESSING_DATE,
457 ERROR_MESSAGE_NAME,
458 TABLE_NAME,
459 CREATION_DATE,
460 CREATED_BY,
461 LAST_UPDATE_DATE,
462 LAST_UPDATED_BY,
463 LAST_UPDATE_LOGIN,
464 REQUEST_ID,
465 PROGRAM_APPLICATION_ID,
466 PROGRAM_ID,
467 PROGRAM_UPDATE_DATE )
468 values (
469 X_INTERFACE_TYPE,
470 X_transaction_id,
471 X_column_name,
472 l_error_message,
473 g_sysdate,
474 X_MESSAGE_NAME,
475 X_TABLE_NAME,
476 g_sysdate,
477 g_user_id,
478 g_sysdate,
479 g_user_id,
480 g_login_id,
481 NULL,
482 201,
483 NULL,
484 NULL );
485
486 commit;
487
488 EXCEPTION
489 WHEN OTHERS THEN
490 raise;
491 END insert_error;
492
493 -- <PO_CHANGE_API FPJ>
494 -- In file version 115.10, removed the X_INTERFACE_TYPE and X_TRANSACTION_ID
495 -- parameters from UPDATE_PO and added an X_API_ERRORS parameter, because
496 -- the PO Change API will no longer write error messages to the
497 -- PO_INTERFACE_ERRORS table. Instead, it will return all of the errors
498 -- in the x_api_errors object.
499
500 FUNCTION update_po
501 (
502 X_PO_NUMBER VARCHAR2,
503 X_RELEASE_NUMBER NUMBER,
504 X_REVISION_NUMBER NUMBER,
505 X_LINE_NUMBER NUMBER,
506 X_SHIPMENT_NUMBER NUMBER,
507 NEW_QUANTITY NUMBER,
508 NEW_PRICE NUMBER,
509 NEW_PROMISED_DATE DATE,
510 NEW_NEED_BY_DATE DATE,
511 LAUNCH_APPROVALS_FLAG VARCHAR2,
512 UPDATE_SOURCE VARCHAR2,
513 VERSION VARCHAR2,
514 X_OVERRIDE_DATE DATE := NULL,
515 X_API_ERRORS OUT NOCOPY PO_API_ERRORS_REC_TYPE,
516 p_BUYER_NAME VARCHAR2 default NULL, /* Bug:2986718 */
517 -- <INVCONV R12 START>
518 p_secondary_quantity NUMBER ,
519 p_preferred_grade VARCHAR2,
520 -- <INVCONV R12 END>
521 p_org_id IN NUMBER
522 ) RETURN NUMBER IS
523
524 l_api_version CONSTANT NUMBER := 1.0;
525 l_api_name CONSTANT VARCHAR2(50) := 'UPDATE_PO';
526
527 l_error_message PO_INTERFACE_ERRORS.ERROR_MESSAGE%TYPE;
528
529 l_result NUMBER := 1;
530 l_return_status VARCHAR2(1);
531 l_org_id PO_HEADERS_ALL.org_id%type := p_org_id;
532 BEGIN
533
534 IF NOT FND_API.Compatible_API_CALL ( l_api_version,
535 TO_NUMBER(VERSION,99.999),
536 l_api_name,
537 g_pkg_name)
538 THEN
539 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
540 END IF;
541
542 FND_MSG_PUB.initialize;
543
544 --<Bug#4581621 Start>
545 PO_MOAC_UTILS_PVT.validate_orgid_pub_api(x_org_id => l_org_id);
546 PO_MOAC_UTILS_PVT.set_policy_context('S',l_org_id);
547 --<Bug#4581621 End>
548
549 PO_DOCUMENT_UPDATE_GRP.update_document(
550 X_PO_NUMBER,
551 X_RELEASE_NUMBER,
552 X_REVISION_NUMBER,
553 X_LINE_NUMBER,
554 X_SHIPMENT_NUMBER,
555 NEW_QUANTITY,
556 NEW_PRICE,
557 NEW_PROMISED_DATE,
558 NEW_NEED_BY_DATE,
559 LAUNCH_APPROVALS_FLAG,
560 UPDATE_SOURCE,
561 X_OVERRIDE_DATE,
562 2.0, -- Version <PO_CHANGE_API FPJ>
563 l_result,
564 x_api_errors, -- <PO_CHANGE_API FPJ>
565 p_BUYER_NAME, /* Bug:2986718 */
566 p_secondary_quantity, -- <INVCONV R12>
567 p_preferred_grade -- <INVCONV R12>
568 );
569
570 RETURN l_result;
571
572 EXCEPTION
573 -- <PO_CHANGE_API FPJ START>
574 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
575 -- Add the errors on the API message list to x_api_errors.
576 PO_DOCUMENT_UPDATE_PVT.add_message_list_errors (
577 p_api_errors => x_api_errors,
578 x_return_status => l_return_status
579 );
580 RETURN 0;
581 WHEN OTHERS THEN
582 -- Add the unexpected error to the API message list.
583 PO_DEBUG.handle_unexp_error ( p_pkg_name => g_pkg_name,
584 p_proc_name => l_api_name );
585 -- Add the errors on the API message list to x_api_errors.
586 PO_DOCUMENT_UPDATE_PVT.add_message_list_errors (
587 p_api_errors => x_api_errors,
588 x_return_status => l_return_status
589 );
590 RETURN 0;
591 -- <PO_CHANGE_API FPJ END>
592 END update_po;
593
594 END PO_CHANGE_API1_S;