[Home] [Help]
PACKAGE BODY: APPS.PO_CONTROL_ACTION_VALIDATIONS
Source
1 PACKAGE BODY po_control_action_validations AS
2 /* $Header: PO_CONTROL_ACTION_VALIDATIONS.plb 120.1.12020000.3 2013/04/10 09:09:16 jozhong noship $ */
3
4
5 -- The module base for this package.
6 g_debug_stmt CONSTANT BOOLEAN := (PO_DEBUG.is_debug_stmt_on And (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL));
7 g_debug_unexp CONSTANT BOOLEAN := (PO_DEBUG.is_debug_unexp_on AND (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL));
8 g_pkg_name CONSTANT varchar2(50) := PO_LOG.get_package_base('PO_CONTROL_ACTION_VALIDATIONS');
9
10
11 --------------------------------------------------------------
12 -- Common Validation Subroutines Constants
13 --------------------------------------------------------------
14
15 c_can_qty_rec_grt_ord CONSTANT VARCHAR2(30) := 'C_CAN_QTY_REC_GRT_ORD';
16 c_can_qty_bill_grt_ord CONSTANT VARCHAR2(30) := 'C_CAN_QTY_BILL_GRT_ORD';
17 c_can_qty_del_grt_ord CONSTANT VARCHAR2(30) := 'C_CAN_QTY_DELL_GRT_ORD';
18 c_can_qty_rec_not_del CONSTANT VARCHAR2(30) := 'C_CAN_QTY_REC_NOT_DEL';
19 c_can_qty_bill_grt_rec CONSTANT VARCHAR2(30) := 'C_CAN_QTY_BILL_GRT_REC';
20 c_can_qty_bill_grt_del CONSTANT VARCHAR2(30) := 'C_CAN_QTY_BILL_GRT_DEL';
21 c_can_ship_dist_diff_qty CONSTANT VARCHAR2(30) := 'C_CAN_SHIP_DIST_DIFF_QTY';
22 c_can_line_ship_diff_qty CONSTANT VARCHAR2(30) := 'C_CAN_LINE_SHIP_DIFF_QTY';
23 c_can_lcm_match_option_chk CONSTANT VARCHAR2(30) := 'C_CAN_LCM_MATCH_OPTION_CHK';
24 c_can_lcm_dest_type_chk CONSTANT VARCHAR2(30) := 'C_CAN_LCM_DEST_TYPE_CHK';
25 c_can_line_ship_diff_price CONSTANT VARCHAR2(30) := 'C_CAN_LINE_SHIP_DIFF_PRICE';
26 c_can_line_price_grt_limit CONSTANT VARCHAR2(30) := 'C_CAN_LINE_PRICE_GRT_LIMIT';
27
28
29 c_can_invalid_budget_acct_flex CONSTANT VARCHAR2(30) := 'C_CAN_INVALID_BUDGET_ACCT_FLEX';
30 c_can_invalid_charge_acct_flex CONSTANT VARCHAR2(30) := 'C_CAN_INVALID_CHARGE_ACCT_FLEX';
31
32 c_can_with_pending_rcv_trx CONSTANT VARCHAR2(30) := 'C_CAN_WITH_PENDING_RCV_TRX';
33 c_can_with_asn CONSTANT VARCHAR2(30) := 'C_CAN_WITH_ASN';
34
35
36 --------------------------------------------------------------
37 -- Cancel Planned PO Header Validation Subroutine Constants
38 --------------------------------------------------------------
39 c_can_po_pa_with_open_rel CONSTANT VARCHAR2(30) := 'C_CAN_PO_PA_WITH_OPEN_REL';
40
41
42 --------------------------------------------------------------
43 -- Cancel Blanket agreement Header Validation Subroutine Constants
44 --------------------------------------------------------------
45 c_can_ga_with_open_std_ref CONSTANT VARCHAR2(30) := 'C_CAN_GA_WITH_OPEN_STD_REF';
46
47
48 --------------------------------------------------------------
49 -- Cancel Contract Agreement Validation Subroutine Constants
50 --------------------------------------------------------------
51 c_can_cga_with_open_std_ref CONSTANT VARCHAR2(30) := 'C_CAN_CGA_WITH_OPEN_STD_REF';
52
53
54 --------------------------------------------------------------
55 -- Cancel Custom Validation Subroutine Constants
56 --------------------------------------------------------------
57 c_can_custom_validation CONSTANT VARCHAR2(30) := 'C_CAN_CUSTOM_VALIDATION';
58
59
60 -- Business Rule Validation set for Cancel Action
61 cancel_validation_set CONSTANT PO_TBL_VARCHAR2000 := PO_TBL_VARCHAR2000(
62 c_can_ship_dist_diff_qty
63 ,c_can_line_ship_diff_qty
64 ,c_can_line_ship_diff_price
65 ,c_can_lcm_match_option_chk
66 ,c_can_lcm_dest_type_chk
67 ,c_can_line_price_grt_limit
68 ,c_can_qty_rec_grt_ord
69 ,c_can_qty_bill_grt_ord
70 ,c_can_qty_del_grt_ord
71 ,c_can_with_pending_rcv_trx
72 ,c_can_qty_rec_not_del
73 ,c_can_invalid_budget_acct_flex
74 ,c_can_invalid_charge_acct_flex
75 ,c_can_qty_bill_grt_rec
76 ,c_can_qty_bill_grt_del
77 ,c_can_with_asn
78 ,c_can_po_pa_with_open_rel
79 ,c_can_ga_with_open_std_ref
80 ,c_can_cga_with_open_std_ref
81 ,c_can_custom_validation);
82
83
84 --------------------------------------------------------------------------------
85 --Start of Comments
86 --Name: qty_rec_grt_ord_chk
87
88 --Function:
89 -- Validates If there are any Uncancelled shipments that have been received more
90 -- than they ordered (Fully Received) , the PO/Release Header/Line/Shipment cannot be cancelled
91 --
92 --Parameters:
93 --IN:
94 -- p_online_report_id
95 -- p_key
96 -- p_login_id
97 -- p_user_id
98 -- p_sequence
99
100 --IN OUT:
101
102 -- OUT:
103 -- x_return_status
104 -- FND_API.G_RET_STS_SUCCESS if procedure succeeds
105 -- FND_API.G_RET_STS_ERROR if procedure fails
106 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
107
108 -- x_msg_data
109
110 --End of Comments
111 --------------------------------------------------------------------------------
112 PROCEDURE qty_rec_grt_ord_chk(
113 p_online_report_id IN NUMBER,
114 p_key IN po_session_gt.key%TYPE,
115 p_user_id IN po_lines.last_updated_by%TYPE,
116 p_login_id IN po_lines.last_update_login%TYPE ,
117 p_sequence IN OUT NOCOPY NUMBER,
118 x_return_status OUT NOCOPY VARCHAR2,
119 x_msg_data OUT NOCOPY VARCHAR2)
120 IS
121
122 d_api_name CONSTANT VARCHAR2(30) := 'qty_rec_grt_ord_chk.';
123 d_module CONSTANT VARCHAR2(100) := g_pkg_name || d_api_name;
124
125 l_progress VARCHAR2(3) := '000' ;
126 l_line_token VARCHAR2(20);
127 l_ship_token VARCHAR2(20);
128 l_amt_token VARCHAR2(20);
129 l_qty_token VARCHAR2(20);
130
131
132 BEGIN
133 IF g_debug_stmt THEN
134 PO_DEBUG.debug_begin(d_module);
135 PO_DEBUG.debug_var(d_module,l_progress,'p_login_id',p_login_id);
136 PO_DEBUG.debug_var(d_module,l_progress,'p_user_id',p_user_id);
137 PO_DEBUG.debug_var(d_module,l_progress,'p_key',p_key);
138 PO_DEBUG.debug_var(d_module,l_progress,'p_online_report_id',p_online_report_id);
139 END IF;
140
141 x_return_status := FND_API.g_ret_sts_success;
142 x_msg_data:=NULL;
143
144 l_progress := '001';
145 l_line_token := fnd_message.get_string('PO', 'PO_ZMVOR_LINE');
146 l_ship_token := fnd_message.get_string('PO', 'PO_ZMVOR_SHIPMENT');
147 l_amt_token := fnd_message.get_string('PO', 'PO_WF_NOTIF_AMOUNT');
148 l_qty_token := fnd_message.get_string('PO', 'PO_WF_NOTIF_QUANTITY');
149
150
151 -- Gt Table Columns Mapping
152 -- num1 - entity_id
153 -- char1 - document_type
154 -- char2 - document_subtype
155 -- char3 - entity_level
156 -- char4 - doc_id
157 -- char5 - process_entity_flag
158 -- date1 - entity_action_date
159 -- index_num1 - lowestentityid
160 --lowestentityid :
161 -- shipmentid in case of document type= PO/Releases and subtype=Planned/
162 -- Standard at any entity level
163 -- Lineid in case of BPA and GBPA at any entity level
164 -- Headerid in case of CPA
165 INSERT INTO PO_ONLINE_REPORT_TEXT
166 (ONLINE_REPORT_ID,
167 LAST_UPDATE_LOGIN,
168 LAST_UPDATED_BY,
169 LAST_UPDATE_DATE,
170 CREATED_BY,
171 CREATION_DATE,
172 LINE_NUM,
173 SHIPMENT_NUM,
174 DISTRIBUTION_NUM,
175 SEQUENCE,
176 TEXT_LINE,
177 transaction_id,
178 transaction_type)
179 (SELECT
180 p_online_report_id,
181 p_login_id,
182 p_user_id,
183 SYSDATE,
184 p_user_id,
185 SYSDATE,
186 POL.LINE_NUM,
187 poll.SHIPMENT_NUM,
188 0,
189 p_sequence + ROWNUM,
190 PO_CORE_S.get_translated_text
191 ('PO_CAN_SHIP_REC_GRT_ORD',
192 'LINE_SHIP_DIST_NUM', l_line_token||pol.LINE_NUM||','||l_ship_token||poll.SHIPMENT_NUM,
193 'AMT_QTY_TOKEN', DECODE(poll.matching_basis, 'AMOUNT',l_amt_token,l_qty_token),
194 'QTY1', DECODE(poll.matching_basis, 'AMOUNT', Nvl(poll.amount_received, 0), Nvl(poll.quantity_received, 0)),
195 'QTY2', DECODE(poll.matching_basis, 'AMOUNT', Nvl(poll.amount, 0), Nvl(poll.quantity, 0)) ,
196 'DOC_NUM',gt.char6),
197 gt.num1,
198 gt.char3
199 FROM
200 po_line_locations poll,
201 po_lines pol ,
202 po_session_gt gt
203 WHERE gt.key=p_key
204 AND poll.line_location_id = gt.index_num1 -- lowestentityid i.e.
205 AND poll.po_line_id = pol.po_line_id
206 AND gt.char1 <> po_document_cancel_pvt.c_doc_type_PA
207 AND gt.char2 <> po_document_cancel_pvt.c_doc_subtype_PLANNED
208 AND ((POLL.matching_basis ='QUANTITY'
209 AND round(nvl(POLL.quantity_received,0),5) >0
210 AND round(nvl(POLL.quantity_received,0),5) > = round(nvl(POLL.quantity,0),5))
211 OR
212 (POLL.matching_basis ='AMOUNT'
213 AND round(nvl(POLL.amount_received,0),5) >0
214 AND round(nvl(POLL.amount_received,0),5) >= round(nvl(POLL.amount,0),5)))
215 );
216
217
218 p_sequence := P_SEQUENCE + SQL%ROWCOUNT;
219
220 IF g_debug_stmt THEN
221 PO_DEBUG.debug_end(d_module);
222 END IF ;
223
224 EXCEPTION
225 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
226 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
227 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
228
229 WHEN FND_API.G_EXC_ERROR THEN
230 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
231 x_return_status := FND_API.G_RET_STS_ERROR;
232
233 WHEN OTHERS THEN
234 IF (G_DEBUG_UNEXP) THEN
235 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
236 d_module || '.OTHERS_EXCEPTION', 'EXCEPTION: LOCATION IS '
237 || l_progress || ' SQL CODE IS '||sqlcode);
238 END IF;
239
240 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
241 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
242
243 END qty_rec_grt_ord_chk;
244
245 --------------------------------------------------------------------------------
246 -- Start of Comments
247 --
248 --Name: line_price_chk
249 --
250 -- Function: Validates if the Line Price is not exceeding the Price Limit
251 --
252 --Parameters:
253 --IN:
254 -- p_online_report_id
255 -- p_key
256 -- p_login_id
257 -- p_user_id
258 -- p_sequence
259 --IN OUT:
260
261 -- OUT:
262 -- x_return_status
263 -- FND_API.G_RET_STS_SUCCESS if procedure succeeds
264 -- FND_API.G_RET_STS_ERROR if procedure fails
265 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
266
267 -- x_msg_data
268 --
269 -- End of Comments
270 --------------------------------------------------------------------------------
271 PROCEDURE line_price_chk(
272 p_online_report_id IN NUMBER,
273 p_key IN po_session_gt.key%TYPE,
274 p_user_id IN po_lines.last_updated_by%TYPE,
275 p_login_id IN po_lines.last_update_login%TYPE ,
276 p_sequence IN OUT NOCOPY NUMBER,
277 x_return_status OUT NOCOPY VARCHAR2,
278 x_msg_data OUT NOCOPY VARCHAR2)
279 IS
280
281 d_api_name CONSTANT VARCHAR2(30) := 'line_price_chk.';
282 d_module CONSTANT VARCHAR2(100) := g_pkg_name || d_api_name;
283
284 l_progress VARCHAR2(3) := '000' ;
285 l_line_token VARCHAR2(20);
286 l_amt_token VARCHAR2(20);
287 l_price_token VARCHAR2(20);
288
289
290 BEGIN
291
292
293 IF g_debug_stmt THEN
294 PO_DEBUG.debug_begin(d_module);
295 PO_DEBUG.debug_var(d_module,l_progress,'p_login_id',p_login_id);
296 PO_DEBUG.debug_var(d_module,l_progress,'p_user_id',p_user_id);
297 PO_DEBUG.debug_var(d_module,l_progress,'p_key',p_key);
298 PO_DEBUG.debug_var(d_module,l_progress,'p_online_report_id',p_online_report_id);
299 END IF;
300
301
302 x_return_status := FND_API.g_ret_sts_success;
303 x_msg_data:=NULL;
304
305 l_progress := '001';
306 l_line_token := fnd_message.get_string('PO', 'PO_ZMVOR_LINE');
307 l_amt_token := fnd_message.get_string('PO', 'PO_WF_NOTIF_AMOUNT');
308 l_price_token := fnd_message.get_string('PO', 'PO_WF_NOTIF_UNIT_PRICE');
309
310
311
312 --Gt Table Columns Mapping
313 --num1 - entity_id
314 --char1 - document_type
315 --char2 - document_subtype
316 --char3 - entity_level
317 --char4 - doc_id
318 --char5 - process_entity_flag
319 --date1 - entity_action_date
320 --index_num1 - lowestentityid
321 --lowestentityid :
322 -- shipmentid in case of document type= PO/Releases and
323 -- subtype= Planned/Standard at any entity level
324 -- Lineid in case of BPA and GBPA at any entity level
325 -- Headerid in case of CPA
326
327 INSERT INTO PO_ONLINE_REPORT_TEXT(
328 ONLINE_REPORT_ID,
329 LAST_UPDATE_LOGIN,
330 LAST_UPDATED_BY,
331 LAST_UPDATE_DATE,
332 CREATED_BY,
333 CREATION_DATE,
334 LINE_NUM,
335 SHIPMENT_NUM,
336 DISTRIBUTION_NUM,
337 SEQUENCE,
338 TEXT_LINE,
339 transaction_id,
340 transaction_type)
341 (SELECT
342 p_online_report_id,
343 p_login_id,
344 p_user_id,
345 SYSDATE,
346 p_user_id,
347 SYSDATE,
348 POL.LINE_NUM,
349 0,
350 0,
351 p_sequence + ROWNUM,
352 PO_CORE_S.get_translated_text(
353 'PO_CAN_PRICE_LIMIT_LT_PRICE',
354 'LINE_SHIP_DIST_NUM',
355 l_line_token||pol.LINE_NUM,
356 'AMT_PRICE_TOKEN',
357 DECODE(pol.amount, NULL,l_price_token,l_amt_token),
358 'PRICE1',
359 DECODE(pol.amount, NULL,pol.unit_price,pol.amount),
360 'PRICE2',
361 pol.not_to_exceed_price,
362 'DOC_NUM',gt.char6),
363 gt.num1,
364 gt.char3
365 FROM
366 po_lines pol,
367 po_session_gt gt
368 WHERE
369 gt.key = p_key
370 AND gt.char1 = po_document_cancel_pvt.c_doc_type_PA
371 AND gt.char2 <>po_document_cancel_pvt.c_doc_subtype_contract
372 AND pol.po_line_id=gt.index_num1
373 AND nvl(POL.allow_price_override_flag, 'N') = 'Y'
374 AND POL.not_to_exceed_price IS NOT NULL
375 AND ((POL.unit_price IS NOT NULL and POL.not_to_exceed_price < POL.unit_price)
376 or
377 (POL.amount IS NOT NULL and POL.not_to_exceed_price < POL.amount)));
378
379
380
381 p_sequence := P_SEQUENCE + SQL%ROWCOUNT;
382
383 IF g_debug_stmt THEN
384 PO_DEBUG.debug_end(d_module);
385 END IF;
386
387 EXCEPTION
388 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
389 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
390 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
391
392 WHEN FND_API.G_EXC_ERROR THEN
393 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
394 x_return_status := FND_API.G_RET_STS_ERROR;
395
396 WHEN OTHERS THEN
397 IF (G_DEBUG_UNEXP) THEN
398 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
399 d_module || '.OTHERS_EXCEPTION', 'EXCEPTION: LOCATION IS '
400 || l_progress || ' SQL CODE IS '||sqlcode);
401 END IF;
402
403 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
404 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
405
406 END line_price_chk;
407
408
409 --------------------------------------------------------------------------------
410 --Start of Comments
411 --
412 --Name: line_ship_qty_chk
413 --
414 -- Function:
415 -- Validates if the Quantities/Amounts match between Line and Shipments
416 -- if it doesnot match,then the PO/Release Header/Line cannot be cancelled
417 --
418 --Parameters:
419 --IN:
420 -- p_online_report_id
421 -- p_key
422 -- p_login_id
423 -- p_user_id
424 -- p_sequence
425 --IN OUT:
426
427 -- OUT:
428 -- x_return_status
429 -- FND_API.G_RET_STS_SUCCESS if procedure succeeds
430 -- FND_API.G_RET_STS_ERROR if procedure fails
431 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
432
433 -- x_msg_data
434 --
435 --End of Comments
436 --------------------------------------------------------------------------------
437 PROCEDURE line_ship_qty_chk(
438 p_online_report_id IN NUMBER,
439 p_key IN po_session_gt.key%TYPE,
440 p_user_id IN po_lines.last_updated_by%TYPE,
441 p_login_id IN po_lines.last_update_login%TYPE ,
442 p_sequence IN OUT NOCOPY NUMBER,
443 x_return_status OUT NOCOPY VARCHAR2,
444 x_msg_data OUT NOCOPY VARCHAR2)
445 IS
446 d_api_name CONSTANT VARCHAR2(30) := 'line_ship_qty_chk.';
447 d_module CONSTANT VARCHAR2(100) := g_pkg_name || d_api_name;
448 l_progress VARCHAR2(3) := '000';
449 l_line_token VARCHAR2(20);
450 l_amt_token VARCHAR2(20);
451 l_qty_token VARCHAR2(20);
452
453
454 BEGIN
455 IF g_debug_stmt THEN
456 PO_DEBUG.debug_begin(d_module);
457 PO_DEBUG.debug_var(d_module,l_progress,'p_login_id',p_login_id);
458 PO_DEBUG.debug_var(d_module,l_progress,'p_user_id',p_user_id);
459 PO_DEBUG.debug_var(d_module,l_progress,'p_key',p_key);
460 PO_DEBUG.debug_var(d_module,l_progress,'p_online_report_id',p_online_report_id);
461 END IF;
462
463 x_return_status := FND_API.g_ret_sts_success;
464 x_msg_data:=NULL;
465
466 l_progress := '001';
467 l_line_token := fnd_message.get_string('PO', 'PO_ZMVOR_LINE');
468 l_amt_token := fnd_message.get_string('PO', 'PO_WF_NOTIF_AMOUNT');
469 l_qty_token := fnd_message.get_string('PO', 'PO_WF_NOTIF_QUANTITY');
470
471 --Gt Table Columns Mapping
472 --num1 - entity_id
473 --char1 - document_type
474 --char2 - document_subtype
475 --char3 - entity_level
476 --char4 - doc_id
477 --char5 - process_entity_flag
478 --date1 - entity_action_date
479 --index_num1 - lowestentityid
480 --lowestentityid :
481 -- shipmentid in case of document type= PO/Releases and
482 -- subtype= Planned/Standard at any entity level
483 -- Lineid in case of BPA and GBPA at any entity level
484 -- Headerid in case of CPA
485
486 INSERT INTO PO_ONLINE_REPORT_TEXT(
487 ONLINE_REPORT_ID,
488 LAST_UPDATE_LOGIN,
489 LAST_UPDATED_BY,
490 LAST_UPDATE_DATE,
491 CREATED_BY,
492 CREATION_DATE,
493 LINE_NUM,
494 SHIPMENT_NUM,
495 DISTRIBUTION_NUM,
496 SEQUENCE,
497 TEXT_LINE,
498 transaction_id,
499 transaction_type)
500 (SELECT
501 p_online_report_id,
502 p_login_id,
503 p_user_id,
504 SYSDATE,
505 p_user_id,
506 SYSDATE,
507 POL.LINE_NUM,
508 0,
509 0,
510 p_sequence + ROWNUM,
511 PO_CORE_S.get_translated_text(
512 'PO_CAN_PO_LINE_NE_SHIP_AMT',
513 'LINE_SHIP_DIST_NUM',
514 l_line_token||pol.LINE_NUM,
515 'AMT_QTY_TOKEN',
516 DECODE(pol.amount, NULL,l_qty_token,l_amt_token),
517 'QTY1',
518 DECODE(pol.amount, null, pol.quantity,pol.amount),
519 'QTY2',
520 DECODE(
521 pol.amount,
522 null,
523 (SELECT Sum(poll.quantity - nvl(poll.quantity_cancelled,0))
524 FROM po_line_locations poll
525 WHERE poll.po_line_id = pol.po_line_id ),
526 (SELECT sum(poll.amount - nvl(poll.amount_cancelled,0))
527 FROM po_line_locations poll
528 WHERE poll.po_line_id = pol.po_line_id )),
529 'DOC_NUM',(SELECT segment1 FROM po_headers WHERE po_header_id=pol.po_header_id)),
530 (SELECT gt.num1
531 FROM po_session_gt gt
532 WHERE gt.KEY=p_key
533 AND gt.num1 IN
534 (SELECT pol1.po_line_id
535 FROM po_lines pol1
536 WHERE pol1.po_line_id=pol.po_line_id
537 AND gt.char3=po_document_cancel_pvt.c_entity_level_LINE
538 UNION ALL
539 SELECT pol1.po_header_id
540 FROM po_lines pol1
541 WHERE pol1.po_line_id=pol.po_line_id
542 AND gt.char3=po_document_cancel_pvt.c_entity_level_HEADER)),
543 (SELECT gt.char3
544 FROM po_session_gt gt
545 WHERE
546 gt.KEY=p_key
547 AND gt.num1 IN
548 (SELECT pol1.po_line_id
549 FROM po_lines pol1
550 WHERE pol1.po_line_id=pol.po_line_id
551 AND gt.char3=po_document_cancel_pvt.c_entity_level_LINE
552 UNION ALL
553 SELECT pol1.po_header_id
554 FROM po_lines pol1
555 WHERE pol1.po_line_id=pol.po_line_id
556 AND gt.char3=po_document_cancel_pvt.c_entity_level_HEADER))
557
558 FROM
559 po_lines pol
560 WHERE
561 pol.po_line_id IN
562 (SELECT DISTINCT po_line_id
563 FROM po_line_locations,
564 po_session_gt gt
565 WHERE gt.KEY=p_key
566 AND line_location_id= gt.index_num1 -- lowestentityid)
567 AND gt.char1 = po_document_cancel_pvt.c_doc_type_PO
568 AND gt.char3<> po_document_cancel_pvt.c_entity_level_SHIPMENT)
569
570 AND ((POL.quantity IS NOT null
571 AND pol.quantity <> (SELECT Sum(poll.quantity - nvl(poll.quantity_cancelled,0))
572 FROM po_line_locations poll
573 WHERE poll.po_line_id = pol.po_line_id))
574 OR
575 (POL.amount IS NOT null
576 AND pol.amount <> (SELECT sum(poll.amount- nvl(poll.amount_cancelled,0))
577 FROM po_line_locations poll
578 WHERE poll.po_line_id = pol.po_line_id )))
579 );
580
581
582 p_sequence := P_SEQUENCE + SQL%ROWCOUNT;
583
584 IF g_debug_stmt THEN
585 PO_DEBUG.debug_end(d_module);
586 END IF;
587
588 EXCEPTION
589 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
590 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
591 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
592
593 WHEN FND_API.G_EXC_ERROR THEN
594 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
595 x_return_status := FND_API.G_RET_STS_ERROR;
596
597 WHEN OTHERS THEN
598 IF (G_DEBUG_UNEXP) THEN
599 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
600 d_module || '.OTHERS_EXCEPTION', 'EXCEPTION: LOCATION IS '
601 || l_progress || ' SQL CODE IS '||sqlcode);
602 END IF;
603
604 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
605 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
606
607 END line_ship_qty_chk;
608
609
610 --------------------------------------------------------------------------------
611 --Start of Comments
612 --Name: line_ship_price_chk
613 --
614 -- Function:
615 -- Validates if the Unit Price/Price_override matches between Line and Shipments
616 -- if it doesnot match,then the PO/Release Header/Line cannot be cancelled
617 --
618 --Parameters:
619 --IN:
620 -- p_online_report_id
621 -- p_key
622 -- p_login_id
623 -- p_user_id
624 -- p_sequence
625
626 --IN OUT:
627
628 -- OUT:
629 -- x_return_status
630 -- FND_API.G_RET_STS_SUCCESS if procedure succeeds
631 -- FND_API.G_RET_STS_ERROR if procedure fails
632 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
633
634 -- x_msg_data
635
636 --End of Comments
637 --------------------------------------------------------------------------------
638
639 PROCEDURE line_ship_price_chk(
640 p_online_report_id IN NUMBER,
641 p_key IN po_session_gt.key%TYPE,
642 p_user_id IN po_lines.last_updated_by%TYPE,
643 p_login_id IN po_lines.last_update_login%TYPE ,
644 p_sequence IN OUT NOCOPY NUMBER,
645 x_return_status OUT NOCOPY VARCHAR2,
646 x_msg_data OUT NOCOPY VARCHAR2)
647 IS
648
649 d_api_name CONSTANT VARCHAR2(30) := 'line_ship_price_chk';
650 d_module CONSTANT VARCHAR2(100) := g_pkg_name || d_api_name;
651 l_progress VARCHAR2(3) := '000';
652
653 l_line_token VARCHAR2(20);
654 l_ship_token VARCHAR2(20);
655 l_amt_token VARCHAR2(20);
656 l_qty_token VARCHAR2(20);
657
658
659 BEGIN
660 IF g_debug_stmt THEN
661 PO_DEBUG.debug_begin(d_module);
662 PO_DEBUG.debug_var(d_module,l_progress,'p_login_id',p_login_id);
663 PO_DEBUG.debug_var(d_module,l_progress,'p_user_id',p_user_id);
664 PO_DEBUG.debug_var(d_module,l_progress,'p_key',p_key);
665 PO_DEBUG.debug_var(d_module,l_progress,'p_online_report_id',p_online_report_id);
666 END IF;
667
668 x_return_status := FND_API.g_ret_sts_success;
669 x_msg_data:=NULL;
670
671 l_progress := '001';
672 l_line_token := fnd_message.get_string('PO', 'PO_ZMVOR_LINE');
673 l_ship_token := fnd_message.get_string('PO', 'PO_ZMVOR_SHIPMENT');
674 l_amt_token := fnd_message.get_string('PO', 'PO_WF_NOTIF_AMOUNT');
675 l_qty_token := fnd_message.get_string('PO', 'PO_WF_NOTIF_QUANTITY');
676
677 --Gt Table Columns Mapping
678 --num1 - entity_id
679 --char1 - document_type
680 --char2 - document_subtype
681 --char3 - entity_level
682 --char4 - doc_id
683 --char5 - process_entity_flag
684 --date1 - entity_action_date
685 --index_num1 - lowestentityid
686 --lowestentityid :
687 -- shipmentid in case of document type= PO/Releases and
688 -- subtype= Planned/Standard at any entity level
689 -- Lineid in case of BPA and GBPA at any entity level
690 -- Headerid in case of CPA
691
692 INSERT INTO PO_ONLINE_REPORT_TEXT(
693 ONLINE_REPORT_ID,
694 LAST_UPDATE_LOGIN,
695 LAST_UPDATED_BY,
696 LAST_UPDATE_DATE,
697 CREATED_BY,
698 CREATION_DATE,
699 LINE_NUM,
700 SHIPMENT_NUM,
701 DISTRIBUTION_NUM,
702 SEQUENCE,
703 TEXT_LINE,
704 transaction_id,
705 transaction_type)
706 (SELECT
707 p_online_report_id,
708 p_login_id,
709 p_user_id,
710 SYSDATE,
711 p_user_id,
712 SYSDATE,
713 POL.LINE_NUM,
714 poll.SHIPMENT_NUM,
715 0,
716 p_sequence + ROWNUM,
717 PO_CORE_S.get_translated_text(
718 'PO_CAN_SHIP_PRICE_NE_LINE',
719 'LINE_SHIP_DIST_NUM',
720 l_line_token||pol.LINE_NUM||','||l_ship_token||poll.SHIPMENT_NUM,
721 'PRICE1',
722 pol.unit_price,
723 'PRICE2',
724 poll.price_override,
725 'DOC_NUM',
726 gt.char6),
727 gt.num1,
728 gt.char3
729 FROM
730 po_line_locations poll,
731 po_lines pol ,
732 po_session_gt gt
733 WHERE
734 gt.key=p_key
735 AND poll.line_location_id = gt.index_num1 -- lowestentityid i.e.
736 AND poll.po_line_id = pol.po_line_id
737 AND gt.char1 <> po_document_cancel_pvt.c_doc_type_PA
738 AND pol.unit_price <> poll.price_override
739 AND poll.shipment_type in ('STANDARD','PLANNED')
740 AND po_control_action_validations.is_complex_work_po(poll.po_header_id) ='N'
741 );
742
743 p_sequence := P_SEQUENCE + SQL%ROWCOUNT;
744 IF g_debug_stmt THEN
745 PO_DEBUG.debug_end(d_module);
746 END IF;
747
748
749 EXCEPTION
750 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
751 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
752 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
753
754 WHEN FND_API.G_EXC_ERROR THEN
755 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
756 x_return_status := FND_API.G_RET_STS_ERROR;
757
758 WHEN OTHERS THEN
759 IF (G_DEBUG_UNEXP) THEN
760 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
761 d_module || '.OTHERS_EXCEPTION', 'EXCEPTION: LOCATION IS '
762 || l_progress || ' SQL CODE IS '||sqlcode);
763 END IF;
764
765 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
766 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
767
768
769 END line_ship_price_chk;
770
771 --------------------------------------------------------------------------------
772 --Start of Comments
773 --Name:ship_dist_qty_chk
774 --
775 -- Function:
776 -- Validates if the Quantities/Amounts match between Shipments and Distributions
777 -- if it doesnot match,then the PO/Release Header/Line/Shipment cannot be cancelled
778 --
779 --Parameters:
780 --IN:
781 -- p_online_report_id
782 -- p_key
783 -- p_login_id
784 -- p_user_id
785 -- p_sequence
786
787 --IN OUT:
788
789 -- OUT:
790 -- x_return_status
791 -- FND_API.G_RET_STS_SUCCESS if procedure succeeds
792 -- FND_API.G_RET_STS_ERROR if procedure fails
793 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
794
795 -- x_msg_data
796
797 --End of Comments
798 --------------------------------------------------------------------------------
799
800 PROCEDURE ship_dist_qty_chk(
801 p_online_report_id IN NUMBER,
802 p_key IN po_session_gt.key%TYPE,
803 p_user_id IN po_lines.last_updated_by%TYPE,
804 p_login_id IN po_lines.last_update_login%TYPE ,
805 p_sequence IN OUT NOCOPY NUMBER,
806 x_return_status OUT NOCOPY VARCHAR2,
807 x_msg_data OUT NOCOPY VARCHAR2)
808 IS
809
810 d_api_name CONSTANT VARCHAR2(30) := 'ship_dist_qty_chk.';
811 d_module CONSTANT VARCHAR2(100) := g_pkg_name||d_api_name;
812 l_progress VARCHAR2(3) := '000';
813
814 l_line_token VARCHAR2(20);
815 l_ship_token VARCHAR2(20);
816 l_amt_token VARCHAR2(20);
817 l_qty_token VARCHAR2(20);
818
819
820 BEGIN
821
822
823 IF g_debug_stmt THEN
824 PO_DEBUG.debug_begin(d_module);
825 PO_DEBUG.debug_var(d_module,l_progress,'p_login_id',p_login_id);
826 PO_DEBUG.debug_var(d_module,l_progress,'p_user_id',p_user_id);
827 PO_DEBUG.debug_var(d_module,l_progress,'p_key',p_key);
828 PO_DEBUG.debug_var(d_module,l_progress,'p_online_report_id',p_online_report_id);
829 END IF;
830
831
832
833 x_return_status := FND_API.g_ret_sts_success;
834 x_msg_data:=NULL;
835
836 l_progress := '001';
837 l_line_token := fnd_message.get_string('PO', 'PO_ZMVOR_LINE');
838 l_ship_token := fnd_message.get_string('PO', 'PO_ZMVOR_SHIPMENT');
839 l_amt_token := fnd_message.get_string('PO', 'PO_WF_NOTIF_AMOUNT');
840 l_qty_token := fnd_message.get_string('PO', 'PO_WF_NOTIF_QUANTITY');
841
842
843
844 --Gt Table Columns Mapping
845 --num1 - entity_id
846 --char1 - document_type
847 --char2 - document_subtype
848 --char3 - entity_level
849 --char4 - doc_id
850 --char5 - process_entity_flag
851 --date1 - entity_action_date
852 --index_num1 - lowestentityid
853 --lowestentityid :
854 -- shipmentid in case of document type= PO/Releases and
855 -- subtype= Planned/Standard at any entity level
856 -- Lineid in case of BPA and GBPA at any entity level
857 -- Headerid in case of CPA
858
859
860
861 INSERT INTO PO_ONLINE_REPORT_TEXT(
862 ONLINE_REPORT_ID,
863 LAST_UPDATE_LOGIN,
864 LAST_UPDATED_BY,
865 LAST_UPDATE_DATE,
866 CREATED_BY,
867 CREATION_DATE,
868 LINE_NUM,
869 SHIPMENT_NUM,
870 DISTRIBUTION_NUM,
871 SEQUENCE,
872 TEXT_LINE,
873 transaction_id,
874 transaction_type)
875 (SELECT
876 p_online_report_id,
877 p_login_id,
878 p_user_id,
879 SYSDATE,
880 p_user_id,
881 SYSDATE,
882 POL.LINE_NUM,
883 poll.SHIPMENT_NUM,
884 0,
885 p_sequence + ROWNUM,
886 PO_CORE_S.get_translated_text(
887 'PO_CAN_PO_SHIP_NE_DIST_AMT',
888 'LINE_SHIP_DIST_NUM',
889 l_line_token||pol.LINE_NUM||','||l_ship_token||poll.SHIPMENT_NUM,
890 'AMT_QTY_TOKEN',
891 DECODE(poll.matching_basis, 'AMOUNT',l_amt_token,l_qty_token),
892 'QTY1',
893 DECODE(poll.matching_basis, 'AMOUNT', Nvl(poll.amount, 0), Nvl(poll.quantity, 0)),
894 'QTY2',
895 DECODE(
896 poll.matching_basis,
897 'AMOUNT',
898 (SELECT sum(POD2.amount_ordered)
899 FROM PO_DISTRIBUTIONS_ALL POD2
900 WHERE POD2.line_location_id = poll.line_location_id ),
901 (SELECT sum(POD2.quantity_ordered)
902 FROM PO_DISTRIBUTIONS_ALL POD2
903 WHERE POD2.line_location_id = poll.line_location_id )),
904 'DOC_NUM',
905 gt.char6
906 ),
907 gt.num1,
908 gt.char3
909 FROM
910 po_line_locations poll,
911 po_lines pol ,
912 po_session_gt gt
913 WHERE
914 gt.key=p_key
915 AND poll.line_location_id = gt.index_num1 -- lowestentityid i.e.
916 AND poll.po_line_id = pol.po_line_id
917 AND gt.char1 <> po_document_cancel_pvt.c_doc_type_PA
918 AND ((POLL.quantity IS NOT null
919 AND POLL.quantity <> (SELECT sum(POD2.quantity_ordered)
920 FROM PO_DISTRIBUTIONS_ALL POD2
921 WHERE POD2.line_location_id = poll.line_location_id ))
922 OR
923 (POLL.amount IS NOT null
924 AND POLL.amount <> (SELECT sum(POD2.amount_ordered)
925 FROM PO_DISTRIBUTIONS_ALL POD2
926 WHERE POD2.line_location_id = poll.line_location_id )))
927 );
928
929
930 p_sequence := P_SEQUENCE + SQL%ROWCOUNT;
931
932 IF g_debug_stmt THEN
933 PO_DEBUG.debug_end(d_module);
934 END IF;
935
936
937 EXCEPTION
938 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
939 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
940 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
941
942 WHEN FND_API.G_EXC_ERROR THEN
943 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
944 x_return_status := FND_API.G_RET_STS_ERROR;
945
946 WHEN OTHERS THEN
947 IF (G_DEBUG_UNEXP) THEN
948 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
949 d_module || '.OTHERS_EXCEPTION', 'EXCEPTION: LOCATION IS '
950 || l_progress || ' SQL CODE IS '||sqlcode);
951 END IF;
952
953 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
954 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
955
956 END ship_dist_qty_chk;
957
958 --------------------------------------------------------------------------------
959 --Start of Comments
960 --Name:lcm_match_option_chk
961 --
962 -- Function:
963 -- Validates if the document is LCM enabled then its shipment
964 -- must have the invoice match option as 'Receipt'
965 -- If the validation fails, teh documnet cannot be cancelled
966 --
967 --
968 --Parameters:
969 --IN:
970 -- p_online_report_id
971 -- p_key
972 -- p_login_id
973 -- p_user_id
974 -- p_sequence
975
976 --IN OUT:
977
978 -- OUT:
979 -- x_return_status
980 -- FND_API.G_RET_STS_SUCCESS if procedure succeeds
981 -- FND_API.G_RET_STS_ERROR if procedure fails
982 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
983
984 -- x_msg_data
985
986 --End of Comments
987 --------------------------------------------------------------------------------
988
989 PROCEDURE lcm_match_option_chk(
990 p_online_report_id IN NUMBER,
991 p_key IN po_session_gt.key%TYPE,
992 p_user_id IN po_lines.last_updated_by%TYPE,
993 p_login_id IN po_lines.last_update_login%TYPE ,
994 p_sequence IN OUT NOCOPY NUMBER,
995 x_return_status OUT NOCOPY VARCHAR2,
996 x_msg_data OUT NOCOPY VARCHAR2)
997 IS
998
999 d_api_name CONSTANT VARCHAR2(30) := 'lcm_match_option_chk.';
1000 d_module CONSTANT VARCHAR2(100) := g_pkg_name||d_api_name;
1001 l_progress VARCHAR2(3) := '000';
1002
1003
1004 l_line_token VARCHAR2(20);
1005 l_ship_token VARCHAR2(20);
1006 l_amt_token VARCHAR2(20);
1007 l_qty_token VARCHAR2(20);
1008
1009
1010 BEGIN
1011
1012
1013 IF g_debug_stmt THEN
1014 PO_DEBUG.debug_begin(d_module);
1015 PO_DEBUG.debug_var(d_module,l_progress,'p_login_id',p_login_id);
1016 PO_DEBUG.debug_var(d_module,l_progress,'p_user_id',p_user_id);
1017 PO_DEBUG.debug_var(d_module,l_progress,'p_key',p_key);
1018 PO_DEBUG.debug_var(d_module,l_progress,'p_online_report_id',p_online_report_id);
1019 END IF;
1020
1021
1022
1023 x_return_status := FND_API.g_ret_sts_success;
1024 x_msg_data:=NULL;
1025
1026 l_progress := '001';
1027 l_line_token := fnd_message.get_string('PO', 'PO_ZMVOR_LINE');
1028 l_ship_token := fnd_message.get_string('PO', 'PO_ZMVOR_SHIPMENT');
1029 l_amt_token := fnd_message.get_string('PO', 'PO_WF_NOTIF_AMOUNT');
1030 l_qty_token := fnd_message.get_string('PO', 'PO_WF_NOTIF_QUANTITY');
1031
1032
1033
1034 --Gt Table Columns Mapping
1035 --num1 - entity_id
1036 --char1 - document_type
1037 --char2 - document_subtype
1038 --char3 - entity_level
1039 --char4 - doc_id
1040 --char5 - process_entity_flag
1041 --date1 - entity_action_date
1042 --index_num1 - lowestentityid
1043 --lowestentityid :
1044 -- shipmentid in case of document type= PO/Releases and
1045 -- subtype= Planned/Standard at any entity level
1046 -- Lineid in case of BPA and GBPA at any entity level
1047 -- Headerid in case of CPA
1048
1049
1050
1051 INSERT INTO PO_ONLINE_REPORT_TEXT(
1052 ONLINE_REPORT_ID,
1053 LAST_UPDATE_LOGIN,
1054 LAST_UPDATED_BY,
1055 LAST_UPDATE_DATE,
1056 CREATED_BY,
1057 CREATION_DATE,
1058 LINE_NUM,
1059 SHIPMENT_NUM,
1060 DISTRIBUTION_NUM,
1061 SEQUENCE,
1062 TEXT_LINE,
1063 transaction_id,
1064 transaction_type)
1065 (SELECT
1066 p_online_report_id,
1067 p_login_id,
1068 p_user_id,
1069 SYSDATE,
1070 p_user_id,
1071 SYSDATE,
1072 POL.LINE_NUM,
1073 poll.SHIPMENT_NUM,
1074 0,
1075 p_sequence + ROWNUM,
1076 PO_CORE_S.get_translated_text(
1077 'PO_CAN_SHIP_INV_MATCH_NE_R',
1078 'LINE_SHIP_DIST_NUM',
1079 l_line_token||pol.LINE_NUM||','||l_ship_token||poll.SHIPMENT_NUM,
1080 'DOC_NUM',
1081 gt.char6
1082 ),
1083 gt.num1,
1084 gt.char3
1085 FROM
1086 po_line_locations poll,
1087 po_lines pol ,
1088 po_session_gt gt
1089 WHERE
1090 gt.key=p_key
1091 AND poll.line_location_id = gt.index_num1 -- i.e lowestentityid .
1092 AND poll.po_line_id = pol.po_line_id
1093 AND gt.char1 <> po_document_cancel_pvt.c_doc_type_PA
1094 AND Nvl(poll.LCM_FLAG,'N') = 'Y'
1095 AND Nvl(poll.match_option,'P') <> 'R');
1096
1097
1098 p_sequence := P_SEQUENCE + SQL%ROWCOUNT;
1099
1100 IF g_debug_stmt THEN
1101 PO_DEBUG.debug_end(d_module);
1102 END IF;
1103
1104
1105 EXCEPTION
1106 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1107 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
1108 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1109
1110 WHEN FND_API.G_EXC_ERROR THEN
1111 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
1112 x_return_status := FND_API.G_RET_STS_ERROR;
1113
1114 WHEN OTHERS THEN
1115 IF (G_DEBUG_UNEXP) THEN
1116 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
1117 d_module || '.OTHERS_EXCEPTION', 'EXCEPTION: LOCATION IS '
1118 || l_progress || ' SQL CODE IS '||sqlcode);
1119 END IF;
1120
1121 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
1122 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1123
1124
1125 END lcm_match_option_chk;
1126
1127 --------------------------------------------------------------------------------
1128 --Start of Comments
1129 --Name:lcm_dest_type_chk
1130 --
1131 -- Function:
1132 -- Validates if the document is LCM enabled then distribution
1133 -- must have the destination type as 'Inventory'
1134 -- If the validation fails, teh documnet cannot be cancelled
1135 --
1136 --Parameters:
1137 --IN:
1138 -- p_online_report_id
1139 -- p_key
1140 -- p_login_id
1141 -- p_user_id
1142 -- p_sequence
1143
1144 --IN OUT:
1145
1146 -- OUT:
1147 -- x_return_status
1148 -- FND_API.G_RET_STS_SUCCESS if procedure succeeds
1149 -- FND_API.G_RET_STS_ERROR if procedure fails
1150 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
1151
1152 -- x_msg_data
1153
1154 --End of Comments
1155 --------------------------------------------------------------------------------
1156
1157 PROCEDURE lcm_dest_type_chk(
1158 p_online_report_id IN NUMBER,
1159 p_key IN po_session_gt.key%TYPE,
1160 p_user_id IN po_lines.last_updated_by%TYPE,
1161 p_login_id IN po_lines.last_update_login%TYPE ,
1162 p_sequence IN OUT NOCOPY NUMBER,
1163 x_return_status OUT NOCOPY VARCHAR2,
1164 x_msg_data OUT NOCOPY VARCHAR2)
1165 IS
1166
1167 d_api_name CONSTANT VARCHAR2(30) := 'lcm_dest_type_chk.';
1168 d_module CONSTANT VARCHAR2(100) := g_pkg_name||d_api_name;
1169 l_progress VARCHAR2(3) := '000';
1170
1171
1172 l_line_token VARCHAR2(20);
1173 l_dist_token VARCHAR2(20);
1174 l_ship_token VARCHAR2(20);
1175 l_amt_token VARCHAR2(20);
1176 l_qty_token VARCHAR2(20);
1177
1178
1179 BEGIN
1180
1181
1182 IF g_debug_stmt THEN
1183 PO_DEBUG.debug_begin(d_module);
1184 PO_DEBUG.debug_var(d_module,l_progress,'p_login_id',p_login_id);
1185 PO_DEBUG.debug_var(d_module,l_progress,'p_user_id',p_user_id);
1186 PO_DEBUG.debug_var(d_module,l_progress,'p_key',p_key);
1187 PO_DEBUG.debug_var(d_module,l_progress,'p_online_report_id',p_online_report_id);
1188 END IF;
1189
1190
1191
1192 x_return_status := FND_API.g_ret_sts_success;
1193 x_msg_data:=NULL;
1194
1195 l_progress := '001';
1196 l_line_token := fnd_message.get_string('PO', 'PO_ZMVOR_LINE');
1197 l_ship_token := fnd_message.get_string('PO', 'PO_ZMVOR_SHIPMENT');
1198 l_dist_token := fnd_message.get_string('PO', 'PO_ZMVOR_DISTRIBUTION');
1199 l_amt_token := fnd_message.get_string('PO', 'PO_WF_NOTIF_AMOUNT');
1200 l_qty_token := fnd_message.get_string('PO', 'PO_WF_NOTIF_QUANTITY');
1201
1202
1203
1204 --Gt Table Columns Mapping
1205 --num1 - entity_id
1206 --char1 - document_type
1207 --char2 - document_subtype
1208 --char3 - entity_level
1209 --char4 - doc_id
1210 --char5 - process_entity_flag
1211 --date1 - entity_action_date
1212 --index_num1 - lowestentityid
1213 --lowestentityid :
1214 -- shipmentid in case of document type= PO/Releases and
1215 -- subtype= Planned/Standard at any entity level
1216 -- Lineid in case of BPA and GBPA at any entity level
1217 -- Headerid in case of CPA
1218
1219 INSERT INTO PO_ONLINE_REPORT_TEXT(
1220 ONLINE_REPORT_ID,
1221 LAST_UPDATE_LOGIN,
1222 LAST_UPDATED_BY,
1223 LAST_UPDATE_DATE,
1224 CREATED_BY,
1225 CREATION_DATE,
1226 LINE_NUM,
1227 SHIPMENT_NUM,
1228 DISTRIBUTION_NUM,
1229 SEQUENCE,
1230 TEXT_LINE,
1231 transaction_id,
1232 transaction_type)
1233 (SELECT
1234 p_online_report_id,
1235 p_login_id,
1236 p_user_id,
1237 SYSDATE,
1238 p_user_id,
1239 SYSDATE,
1240 POL.LINE_NUM,
1241 poll.SHIPMENT_NUM,
1242 pod.distribution_num,
1243 p_sequence + ROWNUM,
1244 PO_CORE_S.get_translated_text(
1245 'PO_CAN_DIST_DEST_TYPE_NE_I',
1246 'LINE_SHIP_DIST_NUM',
1247 l_line_token||pol.LINE_NUM||','||l_ship_token||poll.SHIPMENT_NUM||','||l_dist_token||pod.distribution_num,
1248 'DOC_NUM',
1249 gt.char6
1250 ),
1251 gt.num1,
1252 gt.char3
1253 FROM
1254 po_distributions_all pod,
1255 po_line_locations poll,
1256 po_lines pol ,
1257 po_session_gt gt
1258 WHERE
1259 gt.key=p_key
1260 AND poll.line_location_id = gt.index_num1 -- i.e lowestentityid .
1261 AND poll.po_line_id = pol.po_line_id
1262 AND poll.line_location_id=pod.line_location_id
1263 AND gt.char1 <> po_document_cancel_pvt.c_doc_type_PA
1264 AND Nvl(poll.LCM_FLAG,'N') = 'Y'
1265 AND pod.DESTINATION_TYPE_CODE <> 'INVENTORY');
1266
1267
1268 p_sequence := P_SEQUENCE + SQL%ROWCOUNT;
1269
1270 IF g_debug_stmt THEN
1271 PO_DEBUG.debug_end(d_module);
1272 END IF;
1273
1274
1275 EXCEPTION
1276 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1277 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
1278 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1279
1280 WHEN FND_API.G_EXC_ERROR THEN
1281 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
1282 x_return_status := FND_API.G_RET_STS_ERROR;
1283
1284 WHEN OTHERS THEN
1285 IF (G_DEBUG_UNEXP) THEN
1286 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
1287 d_module || '.OTHERS_EXCEPTION', 'EXCEPTION: LOCATION IS '
1288 || l_progress || ' SQL CODE IS '||sqlcode);
1289 END IF;
1290
1291 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
1292 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1293
1294 END lcm_dest_type_chk;
1295
1296
1297
1298 --------------------------------------------------------------------------------
1299 --Start of Comments
1300 --Name:qty_del_grt_ord_chk
1301 --
1302 -- Function:
1303 -- Validates If there are any Uncancelled shipments distributions that have
1304 -- been delivered more than they ordered (Fully Received),the PO/Release
1305 -- Header/Line/Shipment cannot be cancelled
1306 --
1307
1308 --Parameters:
1309 --IN:
1310 -- p_online_report_id
1311 -- p_key
1312 -- p_login_id
1313 -- p_user_id
1314 -- p_sequence
1315
1316 --IN OUT:
1317
1318 -- OUT:
1319 -- x_return_status
1320 -- FND_API.G_RET_STS_SUCCESS if procedure succeeds
1321 -- FND_API.G_RET_STS_ERROR if procedure fails
1322 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
1323
1324 -- x_msg_data
1325
1326 --End of Comments
1327 --------------------------------------------------------------------------------
1328
1329 PROCEDURE qty_del_grt_ord_chk(
1330 p_online_report_id IN NUMBER,
1331 p_key IN po_session_gt.key%TYPE,
1332 p_user_id IN po_lines.last_updated_by%TYPE,
1333 p_login_id IN po_lines.last_update_login%TYPE ,
1334 p_sequence IN OUT NOCOPY NUMBER,
1335 x_return_status OUT NOCOPY VARCHAR2,
1336 x_msg_data OUT NOCOPY VARCHAR2)
1337
1338 IS
1339
1340 d_api_name CONSTANT VARCHAR2(30) := 'qty_del_grt_ord_chk.';
1341 d_module CONSTANT VARCHAR2(100) := g_pkg_name||d_api_name;
1342 l_progress VARCHAR2(3) := '000';
1343
1344 l_line_token VARCHAR2(20);
1345 l_ship_token VARCHAR2(20);
1346 l_dist_token VARCHAR2(15);
1347 l_amt_token VARCHAR2(20);
1348 l_qty_token VARCHAR2(20);
1349
1350
1351 BEGIN
1352
1353 IF g_debug_stmt THEN
1354 PO_DEBUG.debug_begin(d_module);
1355 PO_DEBUG.debug_var(d_module,l_progress,'p_login_id',p_login_id);
1356 PO_DEBUG.debug_var(d_module,l_progress,'p_user_id',p_user_id);
1357 PO_DEBUG.debug_var(d_module,l_progress,'p_key',p_key);
1358 PO_DEBUG.debug_var(d_module,l_progress,'p_online_report_id',p_online_report_id);
1359 END IF;
1360
1361
1362
1363 x_return_status := FND_API.g_ret_sts_success;
1364 x_msg_data:=NULL;
1365 l_line_token := fnd_message.get_string('PO', 'PO_ZMVOR_LINE');
1366 l_ship_token := fnd_message.get_string('PO', 'PO_ZMVOR_SHIPMENT');
1367 l_dist_token := fnd_message.get_string('PO', 'PO_ZMVOR_DISTRIBUTION');
1368 l_amt_token := fnd_message.get_string('PO', 'PO_WF_NOTIF_AMOUNT');
1369 l_qty_token := fnd_message.get_string('PO', 'PO_WF_NOTIF_QUANTITY');
1370
1371 l_progress:= '001';
1372
1373
1374
1375 --Gt Table Columns Mapping
1376 --num1 - entity_id
1377 --char1 - document_type
1378 --char2 - document_subtype
1379 --char3 - entity_level
1380 --char4 - doc_id
1381 --char5 - process_entity_flag
1382 --date1 - entity_action_date
1383 --index_num1 - lowestentityid
1384 --lowestentityid :
1385 -- shipmentid in case of document type= PO/Releases and
1386 -- subtype= Planned/Standard at any entity level
1387 -- Lineid in case of BPA and GBPA at any entity level
1388 -- Headerid in case of CPA
1389
1390 --Bug15869000 : At the distribution level, quantity delivered can be
1391 --equal to the quantity ordered for cancel to go through (If it clears
1392 --all the other checks wrt line and shipment level). So removed the '='
1393 --condition while doing quantity checks at the distributions level.
1394 INSERT INTO PO_ONLINE_REPORT_TEXT(
1395 ONLINE_REPORT_ID,
1396 LAST_UPDATE_LOGIN,
1397 LAST_UPDATED_BY,
1398 LAST_UPDATE_DATE,
1399 CREATED_BY,
1400 CREATION_DATE,
1401 LINE_NUM,
1402 SHIPMENT_NUM,
1403 DISTRIBUTION_NUM,
1404 SEQUENCE,
1405 TEXT_LINE,
1406 transaction_id,
1407 transaction_type)
1408 (SELECT p_online_report_id,
1409 p_login_id,
1410 p_user_id,
1411 SYSDATE,
1412 p_user_id,
1413 SYSDATE,
1414 POL.LINE_NUM,
1415 poll.SHIPMENT_NUM,
1416 pod.DISTRIBUTION_NUM,
1417 p_sequence + ROWNUM,
1418 PO_CORE_S.get_translated_text
1419 ('PO_CAN_DIST_DEL_GRT_ORD'
1420 , 'LINE_SHIP_DIST_NUM', l_line_token||pol.LINE_NUM||','||l_ship_token||poll.SHIPMENT_NUM||','||l_dist_token||pod.DISTRIBUTION_NUM
1421 , 'AMT_QTY_TOKEN', DECODE(poll.matching_basis, 'AMOUNT',l_amt_token,l_qty_token)
1422 , 'QTY1', DECODE(poll.matching_basis, 'AMOUNT',Round(Nvl(pod.amount_delivered, 0), 5), Round(Nvl(pod.quantity_delivered, 0), 5))
1423 , 'QTY2', DECODE(poll.matching_basis, 'AMOUNT',Round(Nvl(pod.amount_ordered, 0), 5), Round(Nvl(pod.quantity_ordered, 0), 5))
1424 , 'DOC_NUM', gt.char6
1425 ),
1426 gt.num1,
1427 gt.char3
1428 FROM
1429 po_distributions pod,
1430 po_line_locations poll,
1431 po_lines pol,
1432 po_session_gt gt
1433 WHERE
1434 gt.key=p_key
1435 AND pod.line_location_id = gt.index_num1
1436 AND pod.line_location_id = poll.line_location_id
1437 AND pol.po_line_id = poll.po_line_id
1438 AND gt.char1 <> po_document_cancel_pvt.c_doc_type_PA
1439 AND gt.char2 <> po_document_cancel_pvt.c_doc_subtype_PLANNED
1440 AND ((POLL.matching_basis ='QUANTITY'
1441 AND nvl(pod.quantity_delivered,0)> 0
1442 AND round(nvl(pod.quantity_delivered,0),5) > round(nvl(pod.quantity_ordered,0),5)) --Bug15869000
1443 OR
1444 (POLL.matching_basis ='AMOUNT'
1445 AND nvl(pod.amount_delivered,0)> 0
1446 AND round(nvl(pod.amount_delivered,0),5) > round(nvl(pod.amount_ordered,0),5))) ); --Bug15869000
1447
1448
1449 p_sequence := P_SEQUENCE + SQL%ROWCOUNT;
1450
1451 IF g_debug_stmt THEN
1452 PO_DEBUG.debug_end(d_module);
1453 END IF;
1454
1455 EXCEPTION
1456 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1457 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
1458 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1459
1460 WHEN FND_API.G_EXC_ERROR THEN
1461 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
1462 x_return_status := FND_API.G_RET_STS_ERROR;
1463
1464 WHEN OTHERS THEN
1465 IF (G_DEBUG_UNEXP) THEN
1466 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
1467 d_module || '.OTHERS_EXCEPTION', 'EXCEPTION: LOCATION IS '
1468 || l_progress || ' SQL CODE IS '||sqlcode);
1469 END IF;
1470
1471 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
1472 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1473
1474 END qty_del_grt_ord_chk;
1475
1476 --------------------------------------------------------------------------------
1477 --Start of Comments
1478 --Name:qty_bill_grt_ord_chk
1479 --
1480 -- Function:
1481 -- Validates If there are any Uncancelled shipments distributions that
1482 -- have been billed more than they ordered (Fully Invoiced) ,
1483 -- the PO/Release Header/Line/Shipment cannot be cancelled
1484 --
1485
1486 --Parameters:
1487 --IN:
1488 -- p_online_report_id
1489 -- p_key
1490 -- p_login_id
1491 -- p_user_id
1492 -- p_sequence
1493
1494 --IN OUT:
1495
1496 -- OUT:
1497 -- x_return_status
1498 -- FND_API.G_RET_STS_SUCCESS if procedure succeeds
1499 -- FND_API.G_RET_STS_ERROR if procedure fails
1500 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
1501
1502 -- x_msg_data
1503
1504 --End of Comments
1505 --------------------------------------------------------------------------------
1506
1507 PROCEDURE qty_bill_grt_ord_chk(
1508 p_online_report_id IN NUMBER,
1509 p_key IN po_session_gt.key%TYPE,
1510 p_user_id IN po_lines.last_updated_by%TYPE,
1511 p_login_id IN po_lines.last_update_login%TYPE ,
1512 p_sequence IN OUT NOCOPY NUMBER,
1513 x_return_status OUT NOCOPY VARCHAR2,
1514 x_msg_data OUT NOCOPY VARCHAR2)
1515
1516 IS
1517
1518 d_api_name CONSTANT VARCHAR2(30) := 'qty_bill_grt_ord_chk.';
1519 d_module CONSTANT VARCHAR2(100) := g_pkg_name||d_api_name;
1520 l_progress VARCHAR2(3) := '000';
1521
1522 l_line_token VARCHAR2(20);
1523 l_ship_token VARCHAR2(20);
1524 l_dist_token VARCHAR2(20);
1525 l_amt_token VARCHAR2(20);
1526 l_qty_token VARCHAR2(20);
1527
1528
1529 BEGIN
1530
1531
1532 IF g_debug_stmt THEN
1533 PO_DEBUG.debug_begin(d_module);
1534 PO_DEBUG.debug_var(d_module,l_progress,'p_login_id',p_login_id);
1535 PO_DEBUG.debug_var(d_module,l_progress,'p_user_id',p_user_id);
1536 PO_DEBUG.debug_var(d_module,l_progress,'p_key',p_key);
1537 PO_DEBUG.debug_var(d_module,l_progress,'p_online_report_id',p_online_report_id);
1538 END IF;
1539
1540
1541
1542 x_return_status := FND_API.g_ret_sts_success;
1543 x_msg_data:=NULL;
1544 l_line_token := fnd_message.get_string('PO', 'PO_ZMVOR_LINE');
1545 l_ship_token := fnd_message.get_string('PO', 'PO_ZMVOR_SHIPMENT');
1546 l_dist_token := fnd_message.get_string('PO', 'PO_ZMVOR_DISTRIBUTION');
1547 l_amt_token := fnd_message.get_string('PO', 'PO_WF_NOTIF_AMOUNT');
1548 l_qty_token := fnd_message.get_string('PO', 'PO_WF_NOTIF_QUANTITY');
1549
1550 l_progress:= '001';
1551
1552
1553 --Gt Table Columns Mapping
1554 -- num1 - entity_id
1555 -- char1 - document_type
1556 -- char2 - document_subtype
1557 -- char3 - entity_level
1558 -- char4 - doc_id
1559 -- char5 - process_entity_flag
1560 -- date1 - entity_action_date
1561 -- index_num1 - lowestentityid
1562 --lowestentityid :
1563 -- shipmentid in case of document type= PO/Releases and
1564 -- subtype= Planned/Standard at any entity level
1565 -- Lineid in case of BPA and GBPA at any entity level
1566 -- Headerid in case of CPA
1567
1568 --Bug15869000 : At the distribution level, quantity billed can be
1569 --equal to the quantity ordered for cancel to go through (If it clears
1570 --all the other checks wrt line and shipment level). So removed the '='
1571 --condition while doing quantity checks at the distributions level.
1572 INSERT INTO PO_ONLINE_REPORT_TEXT(
1573 ONLINE_REPORT_ID,
1574 LAST_UPDATE_LOGIN,
1575 LAST_UPDATED_BY,
1576 LAST_UPDATE_DATE,
1577 CREATED_BY,
1578 CREATION_DATE,
1579 LINE_NUM,
1580 SHIPMENT_NUM,
1581 DISTRIBUTION_NUM,
1582 SEQUENCE,
1583 TEXT_LINE,
1584 transaction_id,
1585 transaction_type)
1586 (SELECT p_online_report_id,
1587 p_login_id,
1588 p_user_id,
1589 SYSDATE,
1590 p_user_id,
1591 SYSDATE,
1592 POL.LINE_NUM,
1593 poll.SHIPMENT_NUM,
1594 pod.DISTRIBUTION_NUM,
1595 p_sequence + ROWNUM,
1596 PO_CORE_S.get_translated_text
1597 ('PO_CAN_DIST_BILL_GRT_ORD'
1598 , 'LINE_SHIP_DIST_NUM', l_line_token||pol.LINE_NUM||','||l_ship_token||poll.SHIPMENT_NUM||','||l_dist_token||pod.DISTRIBUTION_NUM
1599 , 'AMT_QTY_TOKEN', DECODE(poll.matching_basis, 'AMOUNT',l_amt_token,l_qty_token)
1600 , 'QTY1', DECODE(poll.matching_basis, 'AMOUNT',
1601 Round(Nvl(DECODE(POD.distribution_type,
1602 'PREPAYMENT', POD.amount_financed,
1603 POD.amount_billed), 0), 5),
1604 Round(Nvl(DECODE(POD.distribution_type,
1605 'PREPAYMENT',
1606 POD.quantity_financed,
1607 POD.quantity_billed), 0), 5))
1608 , 'QTY2', DECODE(poll.matching_basis, 'AMOUNT',
1609 Round(Nvl(pod.amount_ordered, 0), 5),
1610 Round(Nvl(pod.quantity_ordered, 0), 5))
1611 , 'DOC_NUM', gt.char6
1612 ),
1613 gt.num1,
1614 gt.char3
1615 FROM
1616 po_distributions pod,
1617 po_line_locations poll,
1618 po_lines pol,
1619 po_session_gt gt
1620 WHERE
1621 gt.key=p_key
1622 AND pod.line_location_id = gt.index_num1
1623 AND pod.line_location_id = poll.line_location_id
1624 AND pol.po_line_id = poll.po_line_id
1625 AND gt.char1 <> po_document_cancel_pvt.c_doc_type_PA
1626 AND gt.char2 <> po_document_cancel_pvt.c_doc_subtype_PLANNED
1627 AND ((POLL.matching_basis ='QUANTITY'
1628 AND nvl(DECODE(POD.distribution_type,
1629 'PREPAYMENT',
1630 POD.quantity_financed,
1631 POD.quantity_billed
1632 ),
1633 0) >0
1634 AND Round(nvl(DECODE(POD.distribution_type,
1635 'PREPAYMENT',
1636 POD.quantity_financed,
1637 POD.quantity_billed
1638 ),
1639 0),
1640 5) > round(nvl(pod.quantity_ordered,0),5)) --Bug15869000
1641 OR
1642 (POLL.matching_basis ='AMOUNT'
1643 AND nvl(DECODE(POD.distribution_type,
1644 'PREPAYMENT',
1645 POD.amount_financed,
1646 POD.amount_billed
1647 ),
1648 0) >0
1649 AND Round(nvl(DECODE(POD.distribution_type,
1650 'PREPAYMENT',
1651 POD.amount_financed,
1652 POD.amount_billed
1653 ),
1654 0),
1655 5) > round(nvl(pod.amount_ordered,0),5)))); --Bug15869000
1656
1657 p_sequence := P_SEQUENCE + SQL%ROWCOUNT;
1658
1659 IF g_debug_stmt THEN
1660 PO_DEBUG.debug_end(d_module);
1661 END IF;
1662
1663 EXCEPTION
1664 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1665 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
1666 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1667
1668 WHEN FND_API.G_EXC_ERROR THEN
1669 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
1670 x_return_status := FND_API.G_RET_STS_ERROR;
1671
1672 WHEN OTHERS THEN
1673 IF (G_DEBUG_UNEXP) THEN
1674 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
1675 d_module || '.OTHERS_EXCEPTION', 'EXCEPTION: LOCATION IS '
1676 || l_progress || ' SQL CODE IS '||sqlcode);
1677 END IF;
1678
1679 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
1680 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1681
1682 END qty_bill_grt_ord_chk;
1683
1684 --------------------------------------------------------------------------------
1685 --Start of Comments
1686 --Name:qty_bill_grt_rec_chk
1687 --
1688 -- Function:
1689 -- Validates If there are any Uncancelled shipments that have been billed
1690 -- more than they are received ,then cancel action is not allowed on the entity
1691 --
1692
1693 --Parameters:
1694 --IN:
1695 -- p_online_report_id
1696 -- p_key
1697 -- p_login_id
1698 -- p_user_id
1699 -- p_sequence
1700
1701 --IN OUT:
1702
1703 -- OUT:
1704 -- x_return_status
1705 -- FND_API.G_RET_STS_SUCCESS if procedure succeeds
1706 -- FND_API.G_RET_STS_ERROR if procedure fails
1707 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
1708
1709 -- x_msg_data
1710
1711 --End of Comments
1712 --------------------------------------------------------------------------------
1713
1714 PROCEDURE qty_bill_grt_rec_chk(
1715 p_online_report_id IN NUMBER,
1716 p_key IN po_session_gt.key%TYPE,
1717 p_user_id IN po_lines.last_updated_by%TYPE,
1718 p_login_id IN po_lines.last_update_login%TYPE ,
1719 p_sequence IN OUT NOCOPY NUMBER,
1720 x_return_status OUT NOCOPY VARCHAR2,
1721 x_msg_data OUT NOCOPY VARCHAR2)
1722
1723 IS
1724
1725 d_api_name CONSTANT VARCHAR2(30) := 'qty_bill_grt_rec_chk.';
1726 d_module CONSTANT VARCHAR2(100) := g_pkg_name||d_api_name;
1727 l_progress VARCHAR2(3) := '000';
1728 l_line_token VARCHAR2(20);
1729 l_ship_token VARCHAR2(20);
1730 l_amt_token VARCHAR2(20);
1731 l_qty_token VARCHAR2(20);
1732
1733
1734 BEGIN
1735
1736
1737 IF g_debug_stmt THEN
1738 PO_DEBUG.debug_begin(d_module);
1739 PO_DEBUG.debug_var(d_module,l_progress,'p_login_id',p_login_id);
1740 PO_DEBUG.debug_var(d_module,l_progress,'p_user_id',p_user_id);
1741 PO_DEBUG.debug_var(d_module,l_progress,'p_key',p_key);
1742 PO_DEBUG.debug_var(d_module,l_progress,'p_online_report_id',p_online_report_id);
1743 END IF;
1744
1745
1746
1747 x_return_status := FND_API.g_ret_sts_success;
1748 x_msg_data:=NULL;
1749 l_line_token := fnd_message.get_string('PO', 'PO_ZMVOR_LINE');
1750 l_ship_token := fnd_message.get_string('PO', 'PO_ZMVOR_SHIPMENT');
1751 l_amt_token := fnd_message.get_string('PO', 'PO_WF_NOTIF_AMOUNT');
1752 l_qty_token := fnd_message.get_string('PO', 'PO_WF_NOTIF_QUANTITY');
1753 l_progress:= '001';
1754
1755
1756
1757 --Gt Table Columns Mapping
1758 -- num1 - entity_id
1759 -- char1 - document_type
1760 -- char2 - document_subtype
1761 -- char3 - entity_level
1762 -- char4 - doc_id
1763 -- char5 - process_entity_flag
1764 -- date1 - entity_action_date
1765 -- index_num1 - lowestentityid
1766 --lowestentityid :
1767 -- shipmentid in case of document type= PO/Releases and
1768 -- subtype= Planned/Standard at any entity level
1769 -- Lineid in case of BPA and GBPA at any entity level
1770 -- Headerid in case of CPA
1771
1772
1773 INSERT INTO PO_ONLINE_REPORT_TEXT(
1774 ONLINE_REPORT_ID,
1775 LAST_UPDATE_LOGIN,
1776 LAST_UPDATED_BY,
1777 LAST_UPDATE_DATE,
1778 CREATED_BY,
1779 CREATION_DATE,
1780 LINE_NUM,
1781 SHIPMENT_NUM,
1782 DISTRIBUTION_NUM,
1783 SEQUENCE,
1784 TEXT_LINE,
1785 transaction_id,
1786 transaction_type)
1787 (SELECT
1788 p_online_report_id,
1789 p_login_id,
1790 p_user_id,
1791 SYSDATE,
1792 p_user_id,
1793 SYSDATE,
1794 POL.LINE_NUM,
1795 poll.SHIPMENT_NUM,
1796 0,
1797 p_sequence + ROWNUM,
1798 PO_CORE_S.get_translated_text
1799 ('PO_CAN_SHIP_BILL_GRT_REC'
1800 , 'LINE_SHIP_DIST_NUM', l_line_token||pol.LINE_NUM||','||l_ship_token||poll.SHIPMENT_NUM
1801 , 'AMT_QTY_TOKEN', DECODE(poll.matching_basis, 'AMOUNT',l_amt_token,l_qty_token)
1802 , 'QTY1', DECODE(poll.matching_basis, 'AMOUNT',
1803 Round(Nvl(DECODE(POLL.shipment_type, 'PREPAYMENT',
1804 poll.amount_financed, poll.amount_billed), 0), 5),
1805 Round(Nvl(DECODE(POLL.shipment_type, 'PREPAYMENT',
1806 poll.quantity_financed, poll.quantity_billed), 0), 5))
1807 , 'QTY2', DECODE(poll.matching_basis, 'AMOUNT',
1808 Round(Nvl(poll.amount_received, 0), 5),
1809 Round(Nvl(poll.quantity_received, 0), 5))
1810 , 'DOC_NUM', gt.char6
1811 ),
1812 gt.num1,
1813 gt.char3
1814 FROM
1815 po_line_locations poll,
1816 po_lines pol,
1817 po_session_gt gt
1818 WHERE gt.key=p_key
1819 AND poll.line_location_id = gt.index_num1
1820 AND pol.po_line_id = poll.po_line_id
1821 AND nvl(POLL.receipt_required_flag, 'Y')<> 'N'
1822 AND gt.char1 <> po_document_cancel_pvt.c_doc_type_PA
1823 AND gt.char2 <> po_document_cancel_pvt.c_doc_subtype_PLANNED
1824 AND ((POLL.matching_basis ='QUANTITY'
1825 AND nvl(DECODE(poll.shipment_type,
1826 'PREPAYMENT',
1827 poll.quantity_financed,
1828 poll.quantity_billed),
1829 0)>0
1830 AND Round(nvl(DECODE(poll.shipment_type,
1831 'PREPAYMENT',
1832 poll.quantity_financed,
1833 poll.quantity_billed),
1834 0),
1835 5) > round(nvl(poll.quantity_received,0),5)) --bug#15971932
1836 OR
1837 (POLL.matching_basis ='AMOUNT'
1838 AND nvl(DECODE(poll.shipment_type,
1839 'PREPAYMENT',
1840 poll.amount_financed,
1841 poll.amount_billed),
1842 0)>0
1843 AND Round(nvl(DECODE(poll.shipment_type,
1844 'PREPAYMENT',
1845 poll.amount_financed,
1846 poll.amount_billed),
1847 0),
1848 5) > round(nvl(poll.amount_received,0),5)))); --bug#15971932
1849
1850
1851
1852 p_sequence := P_SEQUENCE + SQL%ROWCOUNT;
1853
1854 IF g_debug_stmt THEN
1855 PO_DEBUG.debug_end(d_module);
1856 END IF;
1857
1858 EXCEPTION
1859 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1860 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
1861 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1862
1863 WHEN FND_API.G_EXC_ERROR THEN
1864 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
1865 x_return_status := FND_API.G_RET_STS_ERROR;
1866
1867 WHEN OTHERS THEN
1868 IF (G_DEBUG_UNEXP) THEN
1869 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
1870 d_module || '.OTHERS_EXCEPTION', 'EXCEPTION: LOCATION IS '
1871 || l_progress || ' SQL CODE IS '||sqlcode);
1872 END IF;
1873
1874 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
1875 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1876
1877 END qty_bill_grt_rec_chk;
1878
1879 --------------------------------------------------------------------------------
1880 --Start of Comments
1881 --Name:qty_bill_grt_del_chk
1882 --
1883 -- Function:
1884 -- Validates If there are any Uncancelled shipments distributions that have
1885 -- been billed more than they are delivered ,then cancel action is not allowed
1886 -- on the entity
1887
1888 --Parameters:
1889 --IN:
1890 -- p_online_report_id
1891 -- p_key
1892 -- p_login_id
1893 -- p_user_id
1894 -- p_sequence
1895
1896 --IN OUT:
1897
1898 -- OUT:
1899 -- x_return_status
1900 -- FND_API.G_RET_STS_SUCCESS if procedure succeeds
1901 -- FND_API.G_RET_STS_ERROR if procedure fails
1902 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
1903
1904 -- x_msg_data
1905
1906 --End of Comments
1907 --------------------------------------------------------------------------------
1908
1909 PROCEDURE qty_bill_grt_del_chk(
1910 p_online_report_id IN NUMBER,
1911 p_key IN po_session_gt.key%TYPE,
1912 p_user_id IN po_lines.last_updated_by%TYPE,
1913 p_login_id IN po_lines.last_update_login%TYPE ,
1914 p_sequence IN OUT NOCOPY NUMBER,
1915 x_return_status OUT NOCOPY VARCHAR2,
1916 x_msg_data OUT NOCOPY VARCHAR2)
1917
1918 IS
1919
1920 d_api_name CONSTANT VARCHAR2(30) := 'qty_bill_grt_del_chk.';
1921 d_module CONSTANT VARCHAR2(100) := g_pkg_name||d_api_name;
1922 l_progress VARCHAR2(3) := '000';
1923
1924 l_line_token VARCHAR2(20);
1925 l_ship_token VARCHAR2(20);
1926 l_dist_token VARCHAR2(20);
1927 l_amt_token VARCHAR2(20);
1928 l_qty_token VARCHAR2(20);
1929 BEGIN
1930
1931 IF g_debug_stmt THEN
1932 PO_DEBUG.debug_begin(d_module);
1933 PO_DEBUG.debug_var(d_module,l_progress,'p_login_id',p_login_id);
1934 PO_DEBUG.debug_var(d_module,l_progress,'p_user_id',p_user_id);
1935 PO_DEBUG.debug_var(d_module,l_progress,'p_key',p_key);
1936 PO_DEBUG.debug_var(d_module,l_progress,'p_online_report_id',p_online_report_id);
1937 END IF;
1938
1939
1940
1941 x_return_status := FND_API.g_ret_sts_success;
1942 x_msg_data:=NULL;
1943
1944 l_line_token := fnd_message.get_string('PO', 'PO_ZMVOR_LINE');
1945 l_ship_token := fnd_message.get_string('PO', 'PO_ZMVOR_SHIPMENT');
1946 l_dist_token := fnd_message.get_string('PO', 'PO_ZMVOR_DISTRIBUTION');
1947 l_amt_token := fnd_message.get_string('PO', 'PO_WF_NOTIF_AMOUNT');
1948 l_qty_token := fnd_message.get_string('PO', 'PO_WF_NOTIF_QUANTITY');
1949
1950 l_progress:= '001';
1951
1952
1953 --Gt Table Columns Mapping
1954 -- num1 - entity_id
1955 -- char1 - document_type
1956 -- char2 - document_subtype
1957 -- char3 - entity_level
1958 -- char4 - doc_id
1959 -- char5 - process_entity_flag
1960 -- date1 - entity_action_date
1961 -- index_num1 - lowestentityid
1962 --lowestentityid :
1963 -- shipmentid in case of document type= PO/Releases and
1964 -- subtype= Planned/Standard at any entity level
1965 -- Lineid in case of BPA and GBPA at any entity level
1966 -- Headerid in case of CPA
1967
1968
1969 INSERT INTO PO_ONLINE_REPORT_TEXT(
1970 ONLINE_REPORT_ID,
1971 LAST_UPDATE_LOGIN,
1972 LAST_UPDATED_BY,
1973 LAST_UPDATE_DATE,
1974 CREATED_BY,
1975 CREATION_DATE,
1976 LINE_NUM,
1977 SHIPMENT_NUM,
1978 DISTRIBUTION_NUM,
1979 SEQUENCE,
1980 TEXT_LINE,
1981 transaction_id,
1982 transaction_type)
1983 (SELECT p_online_report_id,
1984 p_login_id,
1985 p_user_id,
1986 SYSDATE,
1987 p_user_id,
1988 SYSDATE,
1989 POL.LINE_NUM,
1990 poll.SHIPMENT_NUM,
1991 pod.DISTRIBUTION_NUM,
1992 p_sequence + ROWNUM,
1993 PO_CORE_S.get_translated_text
1994 ('PO_CAN_DIST_BILL_GRT_DEL'
1995 ,'LINE_SHIP_DIST_NUM',l_line_token||pol.LINE_NUM||','||l_ship_token||poll.SHIPMENT_NUM||','||l_dist_token||pod.DISTRIBUTION_NUM
1996 ,'AMT_QTY_TOKEN',DECODE(poll.matching_basis, 'AMOUNT',l_amt_token,l_qty_token)
1997 ,'QTY1',DECODE(poll.matching_basis, 'AMOUNT',
1998 Round(Nvl(DECODE(POD.distribution_type, 'PREPAYMENT', POD.amount_financed, POD.amount_billed), 0), 5),
1999 Round(Nvl(DECODE(POD.distribution_type, 'PREPAYMENT', POD.quantity_financed, POD.quantity_billed), 0), 5))
2000 , 'QTY2',DECODE(poll.matching_basis, 'AMOUNT',Round(Nvl(pod.amount_delivered, 0), 5), Round(Nvl(pod.quantity_delivered, 0), 5))
2001 , 'DOC_NUM', gt.char6
2002 ),
2003 gt.num1,
2004 gt.char3
2005 FROM
2006 po_distributions pod,
2007 po_line_locations poll,
2008 po_lines pol,
2009 po_session_gt gt
2010 WHERE
2011 gt.key=p_key
2012 AND pod.line_location_id = gt.index_num1
2013 AND pod.line_location_id = poll.line_location_id
2014 AND pol.po_line_id = poll.po_line_id
2015 AND nvl(POLL.receipt_required_flag, 'Y')<> 'N'
2016 AND gt.char1 <> po_document_cancel_pvt.c_doc_type_PA
2017 AND gt.char2 <> po_document_cancel_pvt.c_doc_subtype_PLANNED
2018 AND ((POLL.matching_basis ='QUANTITY'
2019 AND nvl(DECODE(pod.distribution_type,
2020 'PREPAYMENT',
2021 pod.quantity_financed,
2022 pod.quantity_billed)
2023 ,0) >0
2024 AND Round(nvl(DECODE(pod.distribution_type,
2025 'PREPAYMENT',
2026 pod.quantity_financed,
2027 pod.quantity_billed)
2028 ,0)
2029 ,5)> round(nvl(pod.quantity_delivered,0),5))
2030 OR
2031 (POLL.matching_basis ='AMOUNT'
2032 AND nvl(DECODE(pod.distribution_type,
2033 'PREPAYMENT',
2034 pod.quantity_financed,
2035 pod.quantity_billed)
2036 ,0) >0
2037 AND Round(nvl(DECODE(pod.distribution_type,
2038 'PREPAYMENT',
2039 pod.amount_financed,
2040 pod.amount_billed)
2041 ,0)
2042 , 5) > round(nvl(pod.amount_delivered,0),5))) );
2043
2044
2045
2046 p_sequence := P_SEQUENCE + SQL%ROWCOUNT;
2047
2048
2049 IF g_debug_stmt THEN
2050 PO_DEBUG.debug_end(d_module);
2051 END IF;
2052
2053 EXCEPTION
2054 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2055 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
2056 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2057
2058 WHEN FND_API.G_EXC_ERROR THEN
2059 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
2060 x_return_status := FND_API.G_RET_STS_ERROR;
2061
2062 WHEN OTHERS THEN
2063 IF (G_DEBUG_UNEXP) THEN
2064 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
2065 d_module || '.OTHERS_EXCEPTION', 'EXCEPTION: LOCATION IS '
2066 || l_progress || ' SQL CODE IS '||sqlcode);
2067 END IF;
2068
2069 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
2070 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2071
2072
2073 END qty_bill_grt_del_chk;
2074
2075 --------------------------------------------------------------------------------
2076 --Start of Comments
2077 --Name:qty_rec_not_del_chk
2078 --
2079 -- Function:
2080 -- Validates If there is anything that is received but not delivered the
2081 -- PO cannot be cancelled
2082
2083 --Parameters:
2084 --IN:
2085 -- p_online_report_id
2086 -- p_key
2087 -- p_login_id
2088 -- p_user_id
2089 -- p_sequence
2090
2091 --IN OUT:
2092
2093 -- OUT:
2094 -- x_return_status
2095 -- FND_API.G_RET_STS_SUCCESS if procedure succeeds
2096 -- FND_API.G_RET_STS_ERROR if procedure fails
2097 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
2098
2099 -- x_msg_data
2100
2101 --End of Comments
2102 --------------------------------------------------------------------------------
2103
2104 PROCEDURE qty_rec_not_del_chk(
2105 p_online_report_id IN NUMBER,
2106 p_key IN po_session_gt.key%TYPE,
2107 p_user_id IN po_lines.last_updated_by%TYPE,
2108 p_login_id IN po_lines.last_update_login%TYPE ,
2109 p_sequence IN OUT NOCOPY NUMBER,
2110 x_return_status OUT NOCOPY VARCHAR2,
2111 x_msg_data OUT NOCOPY VARCHAR2)
2112
2113 IS
2114
2115 d_api_name CONSTANT VARCHAR2(30) := 'qty_rec_not_del_chk.';
2116 d_module CONSTANT VARCHAR2(100) := g_pkg_name||d_api_name;
2117 l_progress VARCHAR2(3) := '000';
2118
2119
2120 l_line_token VARCHAR2(20);
2121 l_ship_token VARCHAR2(20);
2122 l_dist_token VARCHAR2(20);
2123 l_amt_token VARCHAR2(20);
2124 l_qty_token VARCHAR2(20);
2125
2126
2127 BEGIN
2128
2129
2130 IF g_debug_stmt THEN
2131 PO_DEBUG.debug_begin(d_module);
2132 PO_DEBUG.debug_var(d_module,l_progress,'p_login_id',p_login_id);
2133 PO_DEBUG.debug_var(d_module,l_progress,'p_user_id',p_user_id);
2134 PO_DEBUG.debug_var(d_module,l_progress,'p_key',p_key);
2135 PO_DEBUG.debug_var(d_module,l_progress,'p_online_report_id',p_online_report_id);
2136 END IF;
2137
2138
2139
2140 x_return_status := FND_API.g_ret_sts_success;
2141 x_msg_data:=NULL;
2142
2143 l_line_token := fnd_message.get_string('PO', 'PO_ZMVOR_LINE');
2144 l_ship_token := fnd_message.get_string('PO', 'PO_ZMVOR_SHIPMENT');
2145 l_dist_token := fnd_message.get_string('PO', 'PO_ZMVOR_DISTRIBUTION');
2146 l_amt_token := fnd_message.get_string('PO', 'PO_WF_NOTIF_AMOUNT');
2147 l_qty_token := fnd_message.get_string('PO', 'PO_WF_NOTIF_QUANTITY');
2148
2149 l_progress:= '001';
2150
2151
2152 --Gt Table Columns Mapping
2153 -- num1 - entity_id
2154 -- char1 - document_type
2155 -- char2 - document_subtype
2156 -- char3 - entity_level
2157 -- char4 - doc_id
2158 -- char5 - process_entity_flag
2159 -- date1 - entity_action_date
2160 -- index_num1 - lowestentityid
2161 --lowestentityid :
2162 -- shipmentid in case of document type= PO/Releases and
2163 -- subtype= Planned/Standard at any entity level
2164 -- Lineid in case of BPA and GBPA at any entity level
2165 -- Headerid in case of CPA
2166
2167
2168 INSERT INTO po_online_report_text(
2169 ONLINE_REPORT_ID,
2170 LAST_UPDATE_LOGIN,
2171 LAST_UPDATED_BY,
2172 LAST_UPDATE_DATE,
2173 CREATED_BY,
2174 CREATION_DATE,
2175 LINE_NUM,
2176 SHIPMENT_NUM,
2177 DISTRIBUTION_NUM,
2178 SEQUENCE,
2179 TEXT_LINE,
2180 transaction_id,
2181 transaction_type)
2182 (SELECT
2183 p_online_report_id,
2184 p_login_id,
2185 p_user_id,
2186 SYSDATE,
2187 p_user_id,
2188 SYSDATE,
2189 POL.LINE_NUM,
2190 poll.SHIPMENT_NUM,
2191 null,
2192 p_sequence + ROWNUM,
2193 PO_CORE_S.get_translated_text(
2194 'PO_CAN_SHIP_REC_NOT_DEL'
2195 , 'LINE_SHIP_DIST_NUM', l_line_token||pol.LINE_NUM||','||l_ship_token||poll.SHIPMENT_NUM
2196 , 'AMT_QTY_TOKEN', DECODE(poll.matching_basis, 'AMOUNT',l_amt_token,l_qty_token)
2197 , 'QTY1', DECODE(poll.matching_basis, 'AMOUNT',
2198 Nvl(poll.amount_received, 0),
2199 Nvl(poll.quantity_received, 0))
2200 , 'QTY2', (SELECT Sum(Decode(poll.matching_basis, 'AMOUNT',
2201 Nvl(pod.amount_delivered, 0),
2202 Nvl(pod.quantity_delivered, 0)))
2203 FROM po_distributions_all pod
2204 WHERE pod.line_location_id=poll.line_location_id)
2205 , 'DOC_NUM', gt.char6
2206 ),
2207 gt.num1,
2208 gt.char3
2209 FROM
2210 po_line_locations poll,
2211 po_lines pol,
2212 po_session_gt gt
2213
2214 WHERE gt.KEY =p_key
2215 AND poll.line_location_id =gt.index_num1
2216 AND pol.po_line_id = poll.po_line_id
2217 AND gt.char1 <> po_document_cancel_pvt.c_doc_type_PA
2218 AND gt.char2 <> po_document_cancel_pvt.c_doc_subtype_PLANNED
2219 AND ((poll.matching_basis = 'AMOUNT'
2220 AND nvl(poll.amount_received, 0) <>(SELECT Sum(Nvl(amount_delivered, 0))
2221 FROM po_distributions_all
2222 WHERE line_location_id=poll.line_location_id))
2223 OR (poll.matching_basis <>'AMOUNT'
2224 AND Nvl(poll.quantity_received, 0) <> (SELECT Sum(Nvl(quantity_delivered, 0))
2225 FROM po_distributions_all
2226 WHERE line_location_id=poll.line_location_id))));
2227
2228
2229 p_sequence := P_SEQUENCE + SQL%ROWCOUNT;
2230
2231 IF g_debug_stmt THEN
2232 PO_DEBUG.debug_end(d_module);
2233 END IF;
2234
2235 EXCEPTION
2236 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2237 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
2238 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2239
2240 WHEN FND_API.G_EXC_ERROR THEN
2241 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
2242 x_return_status := FND_API.G_RET_STS_ERROR;
2243
2244 WHEN OTHERS THEN
2245 IF (G_DEBUG_UNEXP) THEN
2246 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
2247 d_module || '.OTHERS_EXCEPTION', 'EXCEPTION: LOCATION IS '
2248 || l_progress || ' SQL CODE IS '||sqlcode);
2249 END IF;
2250
2251 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
2252 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2253
2254
2255 END qty_rec_not_del_chk;
2256
2257 --------------------------------------------------------------------------------
2258 --Start of Comments
2259 --Name:pending_rcv_trx_chk
2260 --
2261 -- Function:
2262 -- Validates If there are any receiving transctions in the receiving interface
2263 -- that have not been processes for the current entity ,
2264 -- then the entity ccannot be canceleld
2265
2266
2267 --Parameters:
2268 --IN:
2269 -- p_online_report_id
2270 -- p_key
2271 -- p_login_id
2272 -- p_user_id
2273 -- p_sequence
2274
2275 --IN OUT:
2276
2277 -- OUT:
2278 -- x_return_status
2279 -- FND_API.G_RET_STS_SUCCESS if procedure succeeds
2280 -- FND_API.G_RET_STS_ERROR if procedure fails
2281 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
2282
2283 -- x_msg_data
2284
2285 --End of Comments
2286 --------------------------------------------------------------------------------
2287
2288 PROCEDURE pending_rcv_trx_chk(
2289 p_online_report_id IN NUMBER,
2290 p_key IN po_session_gt.key%TYPE,
2291 p_user_id IN po_lines.last_updated_by%TYPE,
2292 p_login_id IN po_lines.last_update_login%TYPE ,
2293 p_sequence IN OUT NOCOPY NUMBER,
2294 x_return_status OUT NOCOPY VARCHAR2,
2295 x_msg_data OUT NOCOPY VARCHAR2)
2296
2297 IS
2298
2299 d_api_name CONSTANT VARCHAR2(30) := 'pending_rcv_trx_chk.';
2300 d_module CONSTANT VARCHAR2(100) := g_pkg_name||d_api_name;
2301 l_progress VARCHAR2(3) := '000';
2302
2303 BEGIN
2304
2305
2306 IF g_debug_stmt THEN
2307 PO_DEBUG.debug_begin(d_module);
2308 PO_DEBUG.debug_var(d_module,l_progress,'p_login_id',p_login_id);
2309 PO_DEBUG.debug_var(d_module,l_progress,'p_user_id',p_user_id);
2310 PO_DEBUG.debug_var(d_module,l_progress,'p_key',p_key);
2311 PO_DEBUG.debug_var(d_module,l_progress,'p_online_report_id',p_online_report_id);
2312 END IF;
2313
2314
2315
2316 x_return_status := FND_API.g_ret_sts_success;
2317 x_msg_data:=NULL;
2318
2319 l_progress:= '001';
2320
2321 --Gt Table Columns Mapping
2322 -- num1 - entity_id
2323 -- char1 - document_type
2324 -- char2 - document_subtype
2325 -- char3 - entity_level
2326 -- char4 - doc_id
2327 -- char5 - process_entity_flag
2328 -- date1 - entity_action_date
2329 -- index_num1 - lowestentityid
2330 --lowestentityid :
2331 -- shipmentid in case of document type= PO/Releases and
2332 -- subtype= Planned/Standard at any entity level
2333 -- Lineid in case of BPA and GBPA at any entity level
2334 -- Headerid in case of CPA
2335
2336
2337 INSERT INTO po_online_report_text(
2338 ONLINE_REPORT_ID,
2339 LAST_UPDATE_LOGIN,
2340 LAST_UPDATED_BY,
2341 LAST_UPDATE_DATE,
2342 CREATED_BY,
2343 CREATION_DATE,
2344 LINE_NUM,
2345 SHIPMENT_NUM,
2346 DISTRIBUTION_NUM,
2347 SEQUENCE,
2348 TEXT_LINE,
2349 transaction_id,
2350 transaction_type)
2351 (SELECT
2352 p_online_report_id,
2353 p_login_id,
2354 p_user_id,
2355 SYSDATE,
2356 p_user_id,
2357 SYSDATE,
2358 POL.LINE_NUM,
2359 poll.SHIPMENT_NUM,
2360 0,
2361 p_sequence + ROWNUM,
2362 PO_CORE_S.get_translated_text
2363 ('PO_CAN_SHIP_WITH_RCV_TRX'
2364 , 'LINE_NUM', pol.LINE_NUM
2365 , 'SHIP_NUM', poll.SHIPMENT_NUM
2366 , 'DOC_NUM', gt.char6
2367 ),
2368 gt.num1,
2369 gt.char3
2370 FROM
2371 po_line_locations poll,
2372 po_lines pol,
2373 po_session_gt gt
2374 WHERE
2375 gt.key=p_key
2376 AND poll.line_location_id =gt.index_num1
2377 AND pol.po_line_id = poll.po_line_id
2378 AND gt.char1 <> po_document_cancel_pvt.c_doc_type_PA
2379 AND gt.char2 <> po_document_cancel_pvt.c_doc_subtype_PLANNED
2380 AND EXISTS
2381 (SELECT 'Pending Transaction'
2382 FROM RCV_TRANSACTIONS_INTERFACE RTI
2383 WHERE RTI.processing_status_code = 'PENDING'
2384 AND RTI.po_line_location_id =poll.line_location_id));
2385
2386
2387 p_sequence := P_SEQUENCE + SQL%ROWCOUNT;
2388
2389 IF g_debug_stmt THEN
2390 PO_DEBUG.debug_end(d_module);
2391 END IF;
2392
2393 EXCEPTION
2394 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2395 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
2396 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2397
2398 WHEN FND_API.G_EXC_ERROR THEN
2399 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
2400 x_return_status := FND_API.G_RET_STS_ERROR;
2401
2402 WHEN OTHERS THEN
2403 IF (G_DEBUG_UNEXP) THEN
2404 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
2405 d_module || '.OTHERS_EXCEPTION', 'EXCEPTION: LOCATION IS '
2406 || l_progress || ' SQL CODE IS '||sqlcode);
2407 END IF;
2408
2409 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
2410 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2411
2412 END pending_rcv_trx_chk;
2413
2414
2415 --------------------------------------------------------------------------------
2416 --Start of Comments
2417 --Name:pending_asn_chk
2418 --
2419 -- Function:
2420 -- Validates If there are any ASN that have not been fully received for the
2421 -- shipments,then cancel action is not allowed on the entity
2422
2423 --Parameters:
2424 --IN:
2425 -- p_online_report_id
2426 -- p_key
2427 -- p_login_id
2428 -- p_user_id
2429 -- p_sequence
2430
2431 --IN OUT:
2432
2433 -- OUT:
2434 -- x_return_status
2435 -- FND_API.G_RET_STS_SUCCESS if procedure succeeds
2436 -- FND_API.G_RET_STS_ERROR if procedure fails
2437 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
2438
2439 -- x_msg_data
2440
2441 --End of Comments
2442 --------------------------------------------------------------------------------
2443
2444 PROCEDURE pending_asn_chk(
2445 p_online_report_id IN NUMBER,
2446 p_key IN po_session_gt.key%TYPE,
2447 p_user_id IN po_lines.last_updated_by%TYPE,
2448 p_login_id IN po_lines.last_update_login%TYPE ,
2449 p_sequence IN OUT NOCOPY NUMBER,
2450 x_return_status OUT NOCOPY VARCHAR2,
2451 x_msg_data OUT NOCOPY VARCHAR2)
2452
2453 IS
2454
2455 d_api_name CONSTANT VARCHAR2(30) := 'pending_asn_chk.';
2456 d_module CONSTANT VARCHAR2(100) := g_pkg_name||d_api_name;
2457 l_progress VARCHAR2(3) := '000';
2458
2459
2460 BEGIN
2461
2462
2463 IF g_debug_stmt THEN
2464 PO_DEBUG.debug_begin(d_module);
2465 PO_DEBUG.debug_var(d_module,l_progress,'p_login_id',p_login_id);
2466 PO_DEBUG.debug_var(d_module,l_progress,'p_user_id',p_user_id);
2467 PO_DEBUG.debug_var(d_module,l_progress,'p_key',p_key);
2468 PO_DEBUG.debug_var(d_module,l_progress,'p_online_report_id',p_online_report_id);
2469 END IF;
2470
2471
2472
2473 x_return_status := FND_API.g_ret_sts_success;
2474 x_msg_data:=NULL;
2475
2476 l_progress:= '001';
2477
2478 --Gt Table Columns Mapping
2479 -- num1 - entity_id
2480 -- char1 - document_type
2481 -- char2 - document_subtype
2482 -- char3 - entity_level
2483 -- char4 - doc_id
2484 -- char5 - process_entity_flag
2485 -- date1 - entity_action_date
2486 -- index_num1 - lowestentityid
2487 --lowestentityid :
2488 -- shipmentid in case of document type= PO/Releases and
2489 -- subtype= Planned/Standard at any entity level
2490 -- Lineid in case of BPA and GBPA at any entity level
2491 -- Headerid in case of CPA
2492
2493
2494 INSERT INTO po_online_report_text(
2495 ONLINE_REPORT_ID,
2496 LAST_UPDATE_LOGIN,
2497 LAST_UPDATED_BY,
2498 LAST_UPDATE_DATE,
2499 CREATED_BY,
2500 CREATION_DATE,
2501 LINE_NUM,
2502 SHIPMENT_NUM,
2503 DISTRIBUTION_NUM,
2504 SEQUENCE,
2505 TEXT_LINE,
2506 transaction_id,
2507 transaction_type)
2508 (SELECT p_online_report_id,
2509 p_login_id,
2510 p_user_id,
2511 SYSDATE,
2512 p_user_id,
2513 SYSDATE,
2514 POL.LINE_NUM,
2515 poll.SHIPMENT_NUM,
2516 0,
2517 p_sequence + ROWNUM,
2518 PO_CORE_S.get_translated_text
2519 ('PO_CAN_SHIP_WITH_ASN'
2520 , 'LINE_NUM', pol.LINE_NUM
2521 , 'SHIP_NUM', poll.SHIPMENT_NUM
2522 , 'DOC_NUM', gt.char6
2523 ),
2524 gt.num1,
2525 gt.char3
2526 FROM
2527 po_line_locations poll,
2528 po_lines pol,
2529 po_session_gt gt
2530 WHERE gt.key=p_key
2531 AND poll.line_location_id =gt.index_num1
2532 AND pol.po_line_id = poll.po_line_id
2533 AND gt.char1 <> po_document_cancel_pvt.c_doc_type_PA
2534 AND gt.char2 <> po_document_cancel_pvt.c_doc_subtype_PLANNED
2535 AND POLL.payment_type IS NULL
2536 AND EXISTS
2537 (SELECT 'ASN outstanding'
2538 FROM RCV_SHIPMENT_LINES RSL
2539 WHERE RSL.po_line_location_id = poll.line_location_id
2540 AND NVL(RSL.quantity_shipped,0) > NVL(RSL.quantity_received,0)
2541 AND NVL(RSL.ASN_LINE_FLAG,'N') = 'Y'
2542 AND NVL(RSL.SHIPMENT_LINE_STATUS_CODE,'EXPECTED') <> 'CANCELLED'));
2543
2544
2545 p_sequence := P_SEQUENCE + SQL%ROWCOUNT;
2546
2547
2548 IF g_debug_stmt THEN
2549 PO_DEBUG.debug_end(d_module);
2550 END IF;
2551
2552 EXCEPTION
2553 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2554 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
2555 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2556
2557 WHEN FND_API.G_EXC_ERROR THEN
2558 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
2559 x_return_status := FND_API.G_RET_STS_ERROR;
2560
2561 WHEN OTHERS THEN
2562 IF (G_DEBUG_UNEXP) THEN
2563 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
2564 d_module || '.OTHERS_EXCEPTION', 'EXCEPTION: LOCATION IS '
2565 || l_progress || ' SQL CODE IS '||sqlcode);
2566 END IF;
2567
2568 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
2569 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2570
2571 END pending_asn_chk;
2572
2573 --------------------------------------------------------------------------------
2574 --Start of Comments
2575 --Name:invalid_budget_acct_chk
2576 --
2577 -- Function:
2578 -- Validates Validates If encumbrance is on and the budget account is invalid,
2579 -- then cancel action is not allowed on the entity
2580
2581 --Parameters:
2582 --IN:
2583 -- p_online_report_id
2584 -- p_action_date
2585 -- p_key
2586 -- p_login_id
2587 -- p_user_id
2588 -- p_sequence
2589
2590 --IN OUT:
2591
2592 -- OUT:
2593 -- x_return_status
2594 -- FND_API.G_RET_STS_SUCCESS if procedure succeeds
2595 -- FND_API.G_RET_STS_ERROR if procedure fails
2596 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
2597
2598 -- x_msg_data
2599
2600 --End of Comments
2601 --------------------------------------------------------------------------------
2602
2603 PROCEDURE invalid_budget_acct_chk(
2604 p_online_report_id IN NUMBER,
2605 p_action_date IN DATE,
2606 p_key IN po_session_gt.key%TYPE,
2607 p_user_id IN po_lines.last_updated_by%TYPE,
2608 p_login_id IN po_lines.last_update_login%TYPE ,
2609 p_sequence IN OUT NOCOPY NUMBER,
2610 x_return_status OUT NOCOPY VARCHAR2,
2611 x_msg_data OUT NOCOPY VARCHAR2)
2612
2613 IS
2614
2615 d_api_name CONSTANT VARCHAR2(30) := 'invalid_budget_acct_chk.';
2616 d_module CONSTANT VARCHAR2(100) := g_pkg_name||d_api_name;
2617 l_progress VARCHAR2(3) := '000';
2618
2619
2620
2621 BEGIN
2622
2623
2624 IF g_debug_stmt THEN
2625 PO_DEBUG.debug_begin(d_module);
2626 PO_DEBUG.debug_var(d_module,l_progress,'p_login_id',p_login_id);
2627 PO_DEBUG.debug_var(d_module,l_progress,'p_user_id',p_user_id);
2628 PO_DEBUG.debug_var(d_module,l_progress,'p_key',p_key);
2629 PO_DEBUG.debug_var(d_module,l_progress,'p_online_report_id',p_online_report_id);
2630 END IF;
2631
2632
2633
2634 x_return_status := FND_API.g_ret_sts_success;
2635 x_msg_data:=NULL;
2636
2637 l_progress:= '001';
2638
2639 --Gt Table Columns Mapping
2640 -- num1 - entity_id
2641 -- char1 - document_type
2642 -- char2 - document_subtype
2643 -- char3 - entity_level
2644 -- char4 - doc_id
2645 -- char5 - process_entity_flag
2646 -- date1 - entity_action_date
2647 -- index_num1 - lowestentityid
2648 --lowestentityid :
2649 -- shipmentid in case of document type= PO/Releases and
2650 -- subtype= Planned/Standard at any entity level
2651 -- Lineid in case of BPA and GBPA at any entity level
2652 -- Headerid in case of CPA
2653
2654 --Bug 15913701. p_action_date is already a date variable.
2655 --Wrapping this inside to_date is giving unexpected results and query is
2656 --unnecessarily inserting values. Removed the to_date wrap around p_action_date.
2657
2658 INSERT INTO po_online_report_text(
2659 ONLINE_REPORT_ID,
2660 LAST_UPDATE_LOGIN,
2661 LAST_UPDATED_BY,
2662 LAST_UPDATE_DATE,
2663 CREATED_BY,
2664 CREATION_DATE,
2665 LINE_NUM,
2666 SHIPMENT_NUM,
2667 DISTRIBUTION_NUM,
2668 SEQUENCE,
2669 TEXT_LINE,
2670 transaction_id,
2671 transaction_type)
2672 (SELECT
2673 p_online_report_id,
2674 p_login_id,
2675 p_user_id,
2676 SYSDATE,
2677 p_user_id,
2678 SYSDATE,
2679 POL.LINE_NUM,
2680 poll.SHIPMENT_NUM,
2681 pod.distribution_num,
2682 p_sequence + ROWNUM,
2683 PO_CORE_S.get_translated_text
2684 ('PO_CAN_DIST_INV_BUDGET_ACCT'
2685 , 'LINE_NUM', pol.LINE_NUM
2686 , 'SHIP_NUM', poll.SHIPMENT_NUM
2687 , 'DIST_NUM', pod.distribution_num
2688 , 'DOC_NUM', gt.char6
2689 ),
2690 gt.num1,
2691 gt.char3
2692 FROM
2693 po_distributions pod,
2694 po_line_locations poll,
2695 po_lines pol,
2696 po_session_gt gt,
2697 FINANCIALS_SYSTEM_PARAMETERS FSP,
2698 gl_code_combinations gcc
2699 WHERE gt.key=p_key
2700 AND pod.line_location_id=gt.index_num1
2701 AND POD.line_location_id = POLL.line_location_id
2702 AND POL.po_line_id = POLL.po_line_id
2703 AND POLL.shipment_type in ('STANDARD', 'PLANNED','PREPAYMENT')
2704 AND GCC.code_combination_id = POD.BUDGET_ACCOUNT_ID
2705 AND gt.char1 <> po_document_cancel_pvt.c_doc_type_PA
2706 AND fsp.purch_encumbrance_flag = 'Y'
2707 AND (GCC.enabled_flag <> 'Y' OR
2708 nvl(p_action_date,trunc(sysdate)) not between
2709 nvl(GCC.start_date_active, nvl(p_action_date,trunc(sysdate)-1))
2710 AND NVL(GCC.end_date_active, nvl(p_action_date,trunc(sysdate)+1))));
2711
2712
2713 p_sequence := P_SEQUENCE + SQL%ROWCOUNT;
2714
2715
2716 IF g_debug_stmt THEN
2717 PO_DEBUG.debug_end(d_module);
2718 END IF;
2719
2720
2721 EXCEPTION
2722 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2723 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
2724 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2725
2726 WHEN FND_API.G_EXC_ERROR THEN
2727 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
2728 x_return_status := FND_API.G_RET_STS_ERROR;
2729
2730 WHEN OTHERS THEN
2731 IF (G_DEBUG_UNEXP) THEN
2732 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
2733 d_module || '.OTHERS_EXCEPTION', 'EXCEPTION: LOCATION IS '
2734 || l_progress || ' SQL CODE IS '||sqlcode);
2735 END IF;
2736
2737 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
2738 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2739
2740
2741 END invalid_budget_acct_chk;
2742
2743 --------------------------------------------------------------------------------
2744 --Start of Comments
2745 --Name:invalid_charge_acct_chk
2746 --
2747 -- Function:
2748 -- Validates Validates If the charge account is invalid,
2749 -- then cancel action is not allowed on the entity
2750
2751 --Parameters:
2752 --IN:
2753 -- p_online_report_id
2754 -- p_key
2755 -- p_login_id
2756 -- p_user_id
2757 -- p_sequence
2758
2759 --IN OUT:
2760
2761 -- OUT:
2762 -- x_return_status
2763 -- FND_API.G_RET_STS_SUCCESS if procedure succeeds
2764 -- FND_API.G_RET_STS_ERROR if procedure fails
2765 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
2766
2767 -- x_msg_data
2768
2769 --End of Comments
2770 --------------------------------------------------------------------------------
2771
2772 PROCEDURE invalid_charge_acct_chk(
2773 p_online_report_id IN NUMBER,
2774 p_action_date IN DATE,
2775 p_key IN po_session_gt.key%TYPE,
2776 p_user_id IN po_lines.last_updated_by%TYPE,
2777 p_login_id IN po_lines.last_update_login%TYPE ,
2778 p_sequence IN OUT NOCOPY NUMBER,
2779 x_return_status OUT NOCOPY VARCHAR2,
2780 x_msg_data OUT NOCOPY VARCHAR2)
2781
2782 IS
2783
2784 d_api_name CONSTANT VARCHAR2(30) := 'invalid_charge_acct_chk.';
2785 d_module CONSTANT VARCHAR2(100) := g_pkg_name||d_api_name;
2786 l_progress VARCHAR2(3) := '000';
2787
2788
2789 BEGIN
2790
2791
2792 IF g_debug_stmt THEN
2793 PO_DEBUG.debug_begin(d_module);
2794 PO_DEBUG.debug_var(d_module,l_progress,'p_login_id',p_login_id);
2795 PO_DEBUG.debug_var(d_module,l_progress,'p_user_id',p_user_id);
2796 PO_DEBUG.debug_var(d_module,l_progress,'p_key',p_key);
2797 PO_DEBUG.debug_var(d_module,l_progress,'p_online_report_id',p_online_report_id);
2798 END IF;
2799
2800
2801
2802 x_return_status := FND_API.g_ret_sts_success;
2803 x_msg_data:=NULL;
2804
2805 l_progress:= '001';
2806
2807 --Gt Table Columns Mapping
2808 -- num1 - entity_id
2809 -- char1 - document_type
2810 -- char2 - document_subtype
2811 -- char3 - entity_level
2812 -- char4 - doc_id
2813 -- char5 - process_entity_flag
2814 -- date1 - entity_action_date
2815 -- index_num1 - lowestentityid
2816 --lowestentityid :
2817 -- shipmentid in case of document type= PO/Releases and
2818 -- subtype= Planned/Standard at any entity level
2819 -- Lineid in case of BPA and GBPA at any entity level
2820 -- Headerid in case of CPA
2821
2822 --Bug 15913701. p_action_date is already a date variable.
2823 --Wrapping this inside to_date is giving unexpected results and query is
2824 --unnecessarily inserting values. Removed the to_date wrap around p_action_date.
2825
2826 INSERT INTO po_online_report_text(
2827 ONLINE_REPORT_ID,
2828 LAST_UPDATE_LOGIN,
2829 LAST_UPDATED_BY,
2830 LAST_UPDATE_DATE,
2831 CREATED_BY,
2832 CREATION_DATE,
2833 LINE_NUM,
2834 SHIPMENT_NUM,
2835 DISTRIBUTION_NUM,
2836 SEQUENCE,
2837 TEXT_LINE,
2838 transaction_id,
2839 transaction_type)
2840 (SELECT
2841 p_online_report_id,
2842 p_login_id,
2843 p_user_id,
2844 SYSDATE,
2845 p_user_id,
2846 SYSDATE,
2847 POL.LINE_NUM,
2848 poll.SHIPMENT_NUM,
2849 pod.distribution_num,
2850 p_sequence + ROWNUM,
2851 PO_CORE_S.get_translated_text
2852 ('PO_CAN_DIST_INV_CHARGE_ACCT'
2853 , 'LINE_NUM', pol.LINE_NUM
2854 , 'SHIP_NUM', poll.SHIPMENT_NUM
2855 , 'DIST_NUM', pod.distribution_num
2856 , 'DOC_NUM', gt.char6
2857
2858 ),
2859 gt.num1,
2860 gt.char3
2861 FROM
2862 po_distributions pod,
2863 po_line_locations poll,
2864 po_lines pol,
2865 gl_code_combinations gcc,
2866 po_session_gt gt
2867 WHERE gt.key=p_key
2868 AND pod.line_location_id=gt.index_num1
2869 AND POD.line_location_id = POLL.line_location_id
2870 AND POL.po_line_id = POLL.po_line_id
2871 AND GCC.code_combination_id = POD.code_combination_id
2872 AND gt.char1 <> po_document_cancel_pvt.c_doc_type_PA
2873 AND (Nvl(GCC.enabled_flag,'N') <> 'Y' OR
2874 nvl(p_action_date,trunc(sysdate)) not between
2875 nvl(GCC.start_date_active, nvl(p_action_date,trunc(sysdate)-1))
2876 AND NVL(GCC.end_date_active, nvl(p_action_date,trunc(sysdate)+1))));
2877
2878
2879
2880 p_sequence := P_SEQUENCE + SQL%ROWCOUNT;
2881
2882
2883 IF g_debug_stmt THEN
2884 PO_DEBUG.debug_end(d_module);
2885 END IF;
2886
2887
2888 EXCEPTION
2889 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2890 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
2891 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2892
2893 WHEN FND_API.G_EXC_ERROR THEN
2894 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
2895 x_return_status := FND_API.G_RET_STS_ERROR;
2896
2897 WHEN OTHERS THEN
2898 IF (G_DEBUG_UNEXP) THEN
2899 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
2900 d_module || '.OTHERS_EXCEPTION', 'EXCEPTION: LOCATION IS '
2901 || l_progress || ' SQL CODE IS '||sqlcode);
2902 END IF;
2903
2904 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
2905 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2906
2907 END invalid_charge_acct_chk;
2908
2909 --------------------------------------------------------------------------------
2910 --Start of Comments
2911 --Name:ga_with_open_std_ref_chk
2912 --
2913 -- Function:
2914 -- Validates If GBPA has Uncancelled Open Standard PO reference,
2915 -- then the GBPA cannot be cancelled
2916
2917 --Parameters:
2918 --IN:
2919 -- p_online_report_id
2920 -- p_key
2921 -- p_login_id
2922 -- p_user_id
2923 -- p_sequence
2924
2925 --IN OUT:
2926
2927 -- OUT:
2928 -- x_return_status
2929 -- FND_API.G_RET_STS_SUCCESS if procedure succeeds
2930 -- FND_API.G_RET_STS_ERROR if procedure fails
2931 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
2932
2933 -- x_msg_data
2934
2935 --End of Comments
2936 --------------------------------------------------------------------------------
2937
2938 PROCEDURE ga_with_open_std_ref_chk(
2939 p_online_report_id IN NUMBER,
2940 p_key IN po_session_gt.key%TYPE,
2941 p_user_id IN po_lines.last_updated_by%TYPE,
2942 p_login_id IN po_lines.last_update_login%TYPE ,
2943 p_sequence IN OUT NOCOPY NUMBER,
2944 x_return_status OUT NOCOPY VARCHAR2,
2945 x_msg_data OUT NOCOPY VARCHAR2)
2946
2947 IS
2948
2949 d_api_name CONSTANT VARCHAR2(30) := 'ga_with_open_std_ref_chk';
2950 d_module CONSTANT VARCHAR2(100) := g_pkg_name||d_api_name;
2951 l_progress VARCHAR2(3) := '000';
2952
2953
2954 BEGIN
2955
2956
2957 IF g_debug_stmt THEN
2958 PO_DEBUG.debug_begin(d_module);
2959 PO_DEBUG.debug_var(d_module,l_progress,'p_login_id',p_login_id);
2960 PO_DEBUG.debug_var(d_module,l_progress,'p_user_id',p_user_id);
2961 PO_DEBUG.debug_var(d_module,l_progress,'p_key',p_key);
2962 PO_DEBUG.debug_var(d_module,l_progress,'p_online_report_id',p_online_report_id);
2963 END IF;
2964
2965
2966
2967 x_return_status := FND_API.g_ret_sts_success;
2968 x_msg_data:=NULL;
2969
2970 l_progress:= '001';
2971
2972 -- Gt Table Columns Mapping
2973 -- num1 - entity_id
2974 -- char1 - document_type
2975 -- char2 - document_subtype
2976 -- char3 - entity_level
2977 -- char4 - doc_id
2978 -- char5 - process_entity_flag
2979 -- date1 - entity_action_date
2980 -- index_num1 - lowestentityid
2981 --lowestentityid :
2982 -- shipmentid in case of document type= PO/Releases and
2983 -- subtype= Planned/Standard at any entity level
2984 -- Lineid in case of BPA and GBPA at any entity level
2985 -- Headerid in case of CPA
2986
2987
2988 INSERT INTO po_online_report_text(
2989 ONLINE_REPORT_ID,
2990 LAST_UPDATE_LOGIN,
2991 LAST_UPDATED_BY,
2992 LAST_UPDATE_DATE,
2993 CREATED_BY,
2994 CREATION_DATE,
2995 LINE_NUM,
2996 SHIPMENT_NUM,
2997 DISTRIBUTION_NUM,
2998 SEQUENCE,
2999 TEXT_LINE,
3000 transaction_id,
3001 transaction_type)
3002 (SELECT
3003 p_online_report_id,
3004 p_login_id,
3005 p_user_id,
3006 SYSDATE,
3007 p_user_id,
3008 SYSDATE,
3009 POL.LINE_NUM,
3010 0,
3011 0,
3012 p_sequence + ROWNUM,
3013 PO_CORE_S.get_translated_text
3014 ('PO_CAN_GA_WITH_OPEN_STD_REF'
3015 , 'LINE_NUM', pol.LINE_NUM
3016 , 'DOC_NUM', gt.char6),
3017
3018 gt.num1,
3019 gt.char3
3020 FROM
3021 po_lines pol,
3022 po_headers poh,
3023 po_session_gt gt
3024 WHERE gt.key=p_key
3025 AND poh.po_header_id=pol.po_header_id
3026 AND pol.po_line_id =gt.index_num1
3027 AND gt.char1 = po_document_cancel_pvt.c_doc_type_PA
3028 AND poh.global_agreement_flag = 'Y'
3029 AND EXISTS
3030 (SELECT 'Uncancelled std PO lines ref this ga line Exist'
3031 FROM po_lines POL1
3032 WHERE POL1.from_line_id = POL.po_line_id
3033 AND nvl(POL1.cancel_flag,'N') = 'N'
3034 AND nvl(POL1.closed_code, 'OPEN') <> 'FINALLY CLOSED'));
3035
3036
3037 p_sequence := P_SEQUENCE + SQL%ROWCOUNT;
3038
3039 IF g_debug_stmt THEN
3040 PO_DEBUG.debug_end(d_module);
3041 END IF;
3042
3043
3044 EXCEPTION
3045 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3046 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
3047 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3048
3049 WHEN FND_API.G_EXC_ERROR THEN
3050 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
3051 x_return_status := FND_API.G_RET_STS_ERROR;
3052
3053 WHEN OTHERS THEN
3054 IF (G_DEBUG_UNEXP) THEN
3055 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
3056 d_module || '.OTHERS_EXCEPTION', 'EXCEPTION: LOCATION IS '
3057 || l_progress || ' SQL CODE IS '||sqlcode);
3058 END IF;
3059
3060 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
3061 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3062
3063
3064 END ga_with_open_std_ref_chk;
3065
3066 --------------------------------------------------------------------------------
3067 --Start of Comments
3068 --Name:po_pa_WITH_OPEN_REL_chk
3069 --
3070 -- Function:
3071 -- Validates If Blanket PA/Planned PO has Uncancelled Open Releases,
3072 -- then the BPA/PPO cannot be cancelled
3073
3074 --Parameters:
3075 --IN:
3076 -- p_online_report_id
3077 -- p_key
3078 -- p_login_id
3079 -- p_user_id
3080 -- p_sequence
3081
3082 --IN OUT:
3083
3084 -- OUT:
3085 -- x_return_status
3086 -- FND_API.G_RET_STS_SUCCESS if procedure succeeds
3087 -- FND_API.G_RET_STS_ERROR if procedure fails
3088 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
3089
3090 -- x_return_msg
3091
3092 --End of Comments
3093 --------------------------------------------------------------------------------
3094
3095 PROCEDURE po_pa_WITH_OPEN_REL_chk(
3096 p_online_report_id IN NUMBER,
3097 p_key IN po_session_gt.key%TYPE,
3098 p_user_id IN po_lines.last_updated_by%TYPE,
3099 p_login_id IN po_lines.last_update_login%TYPE ,
3100 p_sequence IN OUT NOCOPY NUMBER,
3101 x_return_status OUT NOCOPY VARCHAR2,
3102 x_msg_data OUT NOCOPY VARCHAR2)
3103
3104 IS
3105
3106 d_api_name CONSTANT VARCHAR2(30) := 'po_pa_WITH_OPEN_REL_chk.';
3107 d_module CONSTANT VARCHAR2(100) := g_pkg_name||d_api_name;
3108 l_progress VARCHAR2(3) := '000';
3109 l_ship_token VARCHAR2(20); -- bug 16525950
3110
3111
3112 BEGIN
3113 IF g_debug_stmt THEN
3114 PO_DEBUG.debug_begin(d_module);
3115 PO_DEBUG.debug_var(d_module,l_progress,'p_login_id',p_login_id);
3116 PO_DEBUG.debug_var(d_module,l_progress,'p_user_id',p_user_id);
3117 PO_DEBUG.debug_var(d_module,l_progress,'p_key',p_key);
3118 PO_DEBUG.debug_var(d_module,l_progress,'p_online_report_id',p_online_report_id);
3119 END IF;
3120
3121 x_return_status := FND_API.g_ret_sts_success;
3122 x_msg_data:=NULL;
3123
3124 l_progress:= '001';
3125 -- bug 16525950
3126 l_ship_token := fnd_message.get_string('PO', 'PO_ZMVOR_SHIPMENT');
3127
3128 -- Gt Table Columns Mapping
3129 -- num1 - entity_id
3130 -- char1 - document_type
3131 -- char2 - document_subtype
3132 -- char3 - entity_level
3133 -- char4 - doc_id
3134 -- char5 - process_entity_flag
3135 -- date1 - entity_action_date
3136 -- index_num1 - lowestentityid
3137 -- lowestentityid :
3138 -- shipmentid in case of document type= PO/Releases and
3139 -- subtype= Planned/Standard at any entity level
3140 -- Lineid in case of BPA and GBPA at any entity level
3141 -- Headerid in case of CPA
3142
3143 -- Bug 16174863: Add condition in the select statement for
3144 -- po_pa_WITH_OPEN_REL_chk() to restrict the document type as BPA or planned PO.
3145
3146 INSERT INTO po_online_report_text(
3147 ONLINE_REPORT_ID,
3148 LAST_UPDATE_LOGIN,
3149 LAST_UPDATED_BY,
3150 LAST_UPDATE_DATE,
3151 CREATED_BY,
3152 CREATION_DATE,
3153 LINE_NUM,
3154 SHIPMENT_NUM,
3155 DISTRIBUTION_NUM,
3156 SEQUENCE,
3157 TEXT_LINE,
3158 transaction_id,
3159 transaction_type)
3160 (SELECT
3161 p_online_report_id,
3162 p_login_id,
3163 p_user_id,
3164 SYSDATE,
3165 p_user_id,
3166 SYSDATE,
3167 POL.LINE_NUM,
3168 0,
3169 0,
3170 p_sequence + ROWNUM,
3171 PO_CORE_S.get_translated_text
3172 ('PO_CAN_PA_WITH_OPEN_REL'
3173 , 'LINE_NUM', pol.LINE_NUM
3174 , 'DOC_NUM',gt.char6),
3175
3176 gt.num1,
3177 gt.char3
3178 FROM po_lines pol,
3179 po_session_gt gt
3180 WHERE gt.key=p_key
3181 AND pol.po_line_id =gt.index_num1
3182 -- bug 16174863
3183 AND gt.char1 = po_document_cancel_pvt.c_doc_type_PA
3184 AND EXISTS
3185 (SELECT 'Uncancelled Releases Exist'
3186 FROM PO_LINE_LOCATIONS PLL
3187 WHERE PLL.po_line_id = POL.po_line_id
3188 AND PLL.shipment_type in ('BLANKET')
3189 AND nvl(PLL.cancel_flag,'N') = 'N'
3190 AND nvl(PLL.closed_code, 'OPEN') <> 'FINALLY CLOSED'
3191 UNION
3192 SELECT 'Uncancelled Releases Exist'
3193 FROM po_releases por
3194 WHERE POR.po_header_id = pol.po_header_id
3195 -- bug 16590732: Do this check if it is Header level cancel
3196 AND gt.char3 = po_document_cancel_pvt.c_entity_level_header
3197 AND nvl(POR.cancel_flag,'N') = 'N'
3198 AND nvl(POR.closed_code,'OPEN') <> 'FINALLY CLOSED')
3199 );
3200 -- bug 16525950: seperately validate for Planned PO
3201 -- as index_num1 is Shipment id in this case
3202 INSERT INTO po_online_report_text(
3203 ONLINE_REPORT_ID,
3204 LAST_UPDATE_LOGIN,
3205 LAST_UPDATED_BY,
3206 LAST_UPDATE_DATE,
3207 CREATED_BY,
3208 CREATION_DATE,
3209 LINE_NUM,
3210 SHIPMENT_NUM,
3211 DISTRIBUTION_NUM,
3212 SEQUENCE,
3213 TEXT_LINE,
3214 transaction_id,
3215 transaction_type)
3216 (SELECT
3217 p_online_report_id,
3218 p_login_id,
3219 p_user_id,
3220 SYSDATE,
3221 p_user_id,
3222 SYSDATE,
3223 POL.LINE_NUM,
3224 POLL.SHIPMENT_NUM,
3225 0,
3226 p_sequence + ROWNUM,
3227 -- bug 16525950 : Constructing the token value to display shipment number also
3228 PO_CORE_S.get_translated_text
3229 ('PO_CAN_PA_WITH_OPEN_REL'
3230 , 'LINE_NUM', pol.LINE_NUM || l_ship_token || poll.SHIPMENT_NUM
3231 , 'DOC_NUM',gt.char6),
3232
3233 gt.num1,
3234 gt.char3
3235 FROM
3236 po_lines pol,
3237 po_line_locations poll,
3238 po_session_gt gt
3239 WHERE gt.key=p_key
3240 AND poll.LINE_LOCATION_ID =gt.index_num1
3241 AND pol.po_line_id =poll.po_line_id
3242 AND gt.char2 = po_document_cancel_pvt.c_doc_subtype_PLANNED
3243 AND EXISTS(
3244 SELECT 'Uncancelled Releases Exist'
3245 FROM PO_LINE_LOCATIONS PLL
3246 WHERE
3247 PLL.SOURCE_SHIPMENT_ID = POLL.LINE_LOCATION_ID
3248 AND PLL.shipment_type in ('SCHEDULED')
3249 AND nvl(PLL.cancel_flag,'N') = 'N'
3250 AND nvl(PLL.closed_code, 'OPEN') <> 'FINALLY CLOSED'
3251 UNION
3252 SELECT 'Uncancelled Releases Exist'
3253 FROM po_releases por
3254 WHERE POR.po_header_id = pol.po_header_id
3255 -- bug 16590732: Do this check if it is Header level cancel
3256 AND gt.char3 = po_document_cancel_pvt.c_entity_level_header
3257 AND nvl(POR.cancel_flag,'N') = 'N'
3258 AND nvl(POR.closed_code,'OPEN') <> 'FINALLY CLOSED')
3259 );
3260 p_sequence := P_SEQUENCE + SQL%ROWCOUNT;
3261
3262 IF g_debug_stmt THEN
3263 PO_DEBUG.debug_end(d_module);
3264 END IF;
3265
3266 EXCEPTION
3267 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3268 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
3269 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3270
3271 WHEN FND_API.G_EXC_ERROR THEN
3272 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
3273 x_return_status := FND_API.G_RET_STS_ERROR;
3274
3275 WHEN OTHERS THEN
3276 IF (G_DEBUG_UNEXP) THEN
3277 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
3278 d_module || '.OTHERS_EXCEPTION', 'EXCEPTION: LOCATION IS '
3279 || l_progress || ' SQL CODE IS '||sqlcode);
3280 END IF;
3281
3282 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
3283 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3284
3285
3286 END po_pa_WITH_OPEN_REL_chk;
3287
3288
3289 --------------------------------------------------------------------------------
3290 --Start of Comments
3291 --Name:cga_with_open_std_ref_chk
3292 --
3293 -- Function:
3294 -- Validates If CPA has Uncancelled Open Standard PO reference,
3295 -- then the CPA cannot be cancelled
3296
3297 --Parameters:
3298 --IN:
3299 -- p_online_report_id
3300 -- p_key
3301 -- p_login_id
3302 -- p_user_id
3303 -- p_sequence
3304
3305 --IN OUT:
3306
3307 -- OUT:
3308 -- x_return_status
3309 -- FND_API.G_RET_STS_SUCCESS if procedure succeeds
3310 -- FND_API.G_RET_STS_ERROR if procedure fails
3311 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
3312
3313 -- x_return_msg
3314
3315 --End of Comments
3316 --------------------------------------------------------------------------------
3317
3318 PROCEDURE cga_with_open_std_ref_chk(
3319 p_online_report_id IN NUMBER,
3320 p_key IN po_session_gt.key%TYPE,
3321 p_user_id IN po_lines.last_updated_by%TYPE,
3322 p_login_id IN po_lines.last_update_login%TYPE ,
3323 p_sequence IN OUT NOCOPY NUMBER,
3324 x_return_status OUT NOCOPY VARCHAR2,
3325 x_msg_data OUT NOCOPY VARCHAR2)
3326 IS
3327
3328 d_api_name CONSTANT VARCHAR2(30) := 'cga_with_open_std_ref_chk.';
3329 d_module CONSTANT VARCHAR2(100) := g_pkg_name||d_api_name;
3330 l_progress VARCHAR2(3) := '000';
3331
3332 BEGIN
3333
3334 IF g_debug_stmt THEN
3335 PO_DEBUG.debug_begin(d_module);
3336 PO_DEBUG.debug_var(d_module,l_progress,'p_login_id',p_login_id);
3337 PO_DEBUG.debug_var(d_module,l_progress,'p_user_id',p_user_id);
3338 PO_DEBUG.debug_var(d_module,l_progress,'p_key',p_key);
3339 PO_DEBUG.debug_var(d_module,l_progress,'p_online_report_id',p_online_report_id);
3340 END IF;
3341
3342
3343
3344 x_return_status := FND_API.g_ret_sts_success;
3345 x_msg_data:=NULL;
3346
3347 l_progress:= '001';
3348
3349 --Gt Table Columns Mapping
3350 -- num1 - entity_id
3351 -- char1 - document_type
3352 -- char2 - document_subtype
3353 -- char3 - entity_level
3354 -- char4 - doc_id
3355 -- char5 - process_entity_flag
3356 -- date1 - entity_action_date
3357 -- index_num1 - lowestentityid
3358 --lowestentityid :
3359 -- shipmentid in case of document type= PO/Releases and
3360 -- subtype= Planned/Standard at any entity level
3361 -- Lineid in case of BPA and GBPA at any entity level
3362 -- Headerid in case of CPA
3363
3364
3365 INSERT INTO po_online_report_text(
3366 ONLINE_REPORT_ID,
3367 LAST_UPDATE_LOGIN,
3368 LAST_UPDATED_BY,
3369 LAST_UPDATE_DATE,
3370 CREATED_BY,
3371 CREATION_DATE,
3372 LINE_NUM,
3373 SHIPMENT_NUM,
3374 DISTRIBUTION_NUM,
3375 SEQUENCE,
3376 TEXT_LINE,
3377 transaction_id,
3378 transaction_type)
3379 (SELECT p_online_report_id,
3380 p_login_id,
3381 p_user_id,
3382 SYSDATE,
3383 p_user_id,
3384 SYSDATE,
3385 gt.num1,
3386 0,
3387 0,
3388 p_sequence + ROWNUM,
3389 PO_CORE_S.get_translated_text
3390 ('PO_CAN_CGA_WITH_OPEN_STD_REF'
3391 , 'DOC_NUM',gt.char6),
3392
3393 gt.num1,
3394 gt.char3
3395 FROM
3396 po_session_gt gt
3397 WHERE gt.key=p_key
3398 AND gt.char1 = po_document_cancel_pvt.c_doc_type_PA
3399 AND EXISTS
3400 (SELECT 'Has open std Po lines ref this contract'
3401 FROM po_lines POL
3402 WHERE POL.contract_id = gt.index_num1
3403 AND NVL(POL.cancel_flag, 'N') = 'N'
3404 AND NVL(POL.closed_code, 'OPEN') <> 'FINALLY CLOSED') );
3405
3406 p_sequence := P_SEQUENCE + SQL%ROWCOUNT;
3407
3408 IF g_debug_stmt THEN
3409 PO_DEBUG.debug_end(d_module);
3410 END IF;
3411
3412 EXCEPTION
3413 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3414 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
3415 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3416
3417 WHEN FND_API.G_EXC_ERROR THEN
3418 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
3419 x_return_status := FND_API.G_RET_STS_ERROR;
3420
3421 WHEN OTHERS THEN
3422 IF (G_DEBUG_UNEXP) THEN
3423 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
3424 d_module || '.OTHERS_EXCEPTION', 'EXCEPTION: LOCATION IS '
3425 || l_progress || ' SQL CODE IS '||sqlcode);
3426 END IF;
3427
3428 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
3429 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3430
3431 END cga_with_open_std_ref_chk;
3432
3433
3434
3435 --------------------------------------------------------------------------------
3436 --Start of Comments
3437 --Name: cancel_custom_validation
3438 --
3439 -- Function:
3440 -- This routine is a Handle to support custom validations
3441 --Parameters:
3442 --IN:
3443 -- p_online_report_id
3444 -- p_key
3445 -- p_login_id
3446 -- p_user_id
3447 -- p_sequence
3448
3449 --IN OUT:
3450
3451 -- OUT:
3452 -- x_return_status
3453 -- FND_API.G_RET_STS_SUCCESS if procedure succeeds
3454 -- FND_API.G_RET_STS_ERROR if procedure fails
3455 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
3456
3457 -- x_msg_data
3458
3459 --End of Comments
3460 --------------------------------------------------------------------------------
3461
3462 PROCEDURE cancel_custom_validation(
3463 p_online_report_id IN NUMBER,
3464 p_key IN po_session_gt.key%TYPE,
3465 p_user_id IN po_lines.last_updated_by%TYPE,
3466 p_login_id IN po_lines.last_update_login%TYPE ,
3467 p_sequence IN OUT NOCOPY NUMBER,
3468 x_return_status OUT NOCOPY VARCHAR2,
3469 x_msg_data OUT NOCOPY VARCHAR2)
3470 IS
3471
3472 d_api_name CONSTANT VARCHAR2(30) := 'cancel_custom_validation.';
3473 d_module CONSTANT VARCHAR2(100) := g_pkg_name||d_api_name;
3474
3475 l_progress VARCHAR2(3) := '000';
3476
3477
3478 BEGIN
3479
3480 x_return_status := FND_API.G_RET_STS_SUCCESS;
3481
3482 IF g_debug_stmt THEN
3483 PO_DEBUG.debug_begin(d_module);
3484 PO_DEBUG.debug_var(d_module,l_progress,'p_key',p_key);
3485 PO_DEBUG.debug_var(d_module,l_progress,'p_online_report_id',p_online_report_id);
3486 PO_DEBUG.debug_var(d_module,l_progress,'p_user_id',p_user_id);
3487 PO_DEBUG.debug_var(d_module,l_progress,'p_login_id',p_login_id);
3488 PO_DEBUG.debug_var(d_module,l_progress,'p_sequence',p_sequence);
3489 END IF;
3490
3491
3492
3493 IF g_debug_stmt THEN
3494 PO_DEBUG.debug_end(d_module);
3495 END IF;
3496
3497 EXCEPTION
3498 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3499 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
3500 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3501
3502 WHEN FND_API.G_EXC_ERROR THEN
3503 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
3504 x_return_status := FND_API.G_RET_STS_ERROR;
3505
3506 WHEN OTHERS THEN
3507 IF (G_DEBUG_UNEXP) THEN
3508 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
3509 d_module || '.OTHERS_EXCEPTION', 'EXCEPTION: LOCATION IS '
3510 || l_progress || ' SQL CODE IS '||sqlcode);
3511 END IF;
3512
3513 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
3514 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3515 END cancel_custom_validation;
3516
3517
3518
3519 --------------------------------------------------------------------------------
3520 --Start of Comments
3521 --Name:
3522 --
3523 --Modifies: p_key to a new key in po_session_gt [see the explanation below]
3524 -- Function:
3525 -- Cancel action be performed at any of the three levels[Header/Line/Shipment]
3526 -- for a docuemnt
3527 -- But All Business Rules Valdiations are with the :
3528 -- * shipments in case of document type= PO/Releases and subtype= Planned/Standard
3529 -- * Lines in case of BPA and GBPA
3530 -- * Header in case of CPA
3531 -- Ex: On cancelling a PO Header, all its shipments are validated against
3532 -- the business rules,similarly, if we cancel a PO line, all its shipments
3533 -- are validated, For BPA header, its lines are used for validation, so on.
3534 -- So in order to avoid multiple if else conditions like,If entity level=Header,
3535 -- then join with po_header_id, else for entity_level=LINE
3536 -- then join with po_line_id, and so on,fetching all the lowest entities for
3537 -- the entity being cancelled based on the entity_level and updating in po_session_gt.
3538 --
3539 -- The entire entity record was already inserted in po_session_gt with l_old_key,
3540 -- but as the lowest entity for a document can be multiple,
3541 -- ex: there can be more than one shipment for a PO, so not updating the
3542 -- existing records in po_session_gt,
3543 -- rather inserting new records[with key=l_new_key],which will be used further
3544 -- for validations.
3545 -- Following is the entity record and columns in po_session_gt mapping:
3546 -- entity_id - num1
3547 -- document_type - char1
3548 -- document_subtype - char2
3549 -- entity_level - char3
3550 -- doc_id - char4
3551 -- process_entity_flag - char5
3552 -- entity_action_date - date1
3553 -- lowestentityid - index_num1
3554
3555 --Parameters:
3556 --IN:
3557 -- p_entity_rec_tbl
3558 -- p_key
3559 --
3560
3561 --IN OUT:
3562
3563 -- OUT:
3564 -- x_return_status
3565 -- FND_API.G_RET_STS_SUCCESS if procedure succeeds
3566 -- FND_API.G_RET_STS_ERROR if procedure fails
3567 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
3568
3569 -- x_return_msg
3570
3571 --End of Comments
3572 --------------------------------------------------------------------------------
3573
3574 PROCEDURE update_gt_with_low_entity(
3575 p_entity_rec_tbl IN po_document_action_pvt.entity_dtl_rec_type_tbl,
3576 p_key IN OUT NOCOPY po_session_gt.key%TYPE,
3577 x_return_status OUT NOCOPY VARCHAR2,
3578 x_msg_data OUT NOCOPY VARCHAR2)
3579 IS
3580
3581 d_api_name CONSTANT VARCHAR2(30) := 'update_gt_with_low_entity.';
3582 d_module CONSTANT VARCHAR2(100) := g_pkg_name||d_api_name;
3583 l_progress VARCHAR2(3):= '000';
3584
3585 l_old_key po_session_gt.key%TYPE;
3586 l_new_key po_session_gt.key%TYPE;
3587
3588 BEGIN
3589
3590 x_return_status := FND_API.G_RET_STS_SUCCESS;
3591 x_msg_data :=NULL;
3592 l_progress:= '001';
3593
3594
3595
3596 IF g_debug_stmt THEN
3597 PO_DEBUG.debug_begin(d_module);
3598 PO_DEBUG.debug_var(d_module,l_progress,'p_key',p_key);
3599 END IF;
3600
3601
3602 l_new_key := PO_CORE_S.get_session_gt_nextval;
3603 l_old_key := p_key;
3604 l_progress :='002';
3605
3606 IF g_debug_stmt THEN
3607 PO_DEBUG.debug_var(d_module,l_progress,'l_old_key',l_old_key);
3608 PO_DEBUG.debug_var(d_module,l_progress,'l_new_key',l_new_key);
3609 END IF;
3610
3611
3612
3613 -- Lowest entity for SPO/PPO and Releases that will be used for validations
3614 -- is Shipments .Get all the shipments for the SPO/PPO and Releases for
3615 -- entity level =shipment/line/header
3616 INSERT INTO PO_session_gt gt(
3617 key,
3618 index_num1,
3619 num1,
3620 char1,
3621 char2,
3622 char3,
3623 char4,
3624 char5,
3625 char6,
3626 date1)
3627 (SELECT
3628 l_new_key,
3629 line_location_id,
3630 num1,
3631 char1,
3632 char2,
3633 char3,
3634 char4,
3635 'Y',
3636 Decode (
3637 pgt.char1,
3638 po_document_cancel_pvt.c_doc_type_RELEASE,
3639 (SELECT poh.segment1||'-'|| por.release_num
3640 FROM po_releases por,
3641 po_headers poh
3642 WHERE por.po_release_id=poll.po_release_id
3643 AND por.po_header_id=poh.po_header_id),
3644 (SELECT segment1
3645 FROM po_headers
3646 WHERE po_header_id=poll.po_header_id)
3647 ),
3648 date1
3649 FROM po_line_locations poll,
3650 po_session_gt pgt
3651 WHERE pgt.key = l_old_key
3652 AND nvl(poll.cancel_flag,'N') = 'N'
3653 AND nvl(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
3654 AND ( ( pgt.char3 = po_document_cancel_pvt.c_entity_level_shipment
3655 AND pgt.num1 = line_location_id )
3656 OR( pgt.char3 = po_document_cancel_pvt.c_entity_level_line
3657 AND pgt.char1 <> po_document_cancel_pvt.c_doc_type_PA
3658 -- bug 16525950 : consider only the PO shipment and not release
3659 -- In case of PPO, the release shipment will also be considered
3660 -- if we do not add this condition, and as this is for level =Line
3661 -- it is not applicable for releases
3662 AND poll.po_release_id IS NULL
3663 AND pgt.num1 = po_line_id )
3664 OR(pgt.char1 = po_document_cancel_pvt.c_doc_type_PO
3665 AND pgt.char3 = po_document_cancel_pvt.c_entity_level_header
3666 -- bug 16525950
3667 AND poll.po_release_id IS NULL
3668 AND pgt.num1 = po_header_id )
3669 OR (pgt.char1 = po_document_cancel_pvt.c_doc_type_RELEASE
3670 AND pgt.char3 = po_document_cancel_pvt.c_entity_level_header
3671 AND pgt.num1 = po_release_id ) ));
3672
3673
3674
3675 -- Lowest entity for BPA /GBPA that will be used for validations is Lines
3676 -- Get all the Lines for the BPA /GBPA for entity level =line/header
3677
3678 INSERT INTO po_session_gt gt(
3679 key,
3680 num1,
3681 index_num1,
3682 char1,
3683 char2,
3684 char3,
3685 char4,
3686 char5,
3687 char6,
3688 date1)
3689 (SELECT
3690 l_new_key,
3691 num1,
3692 po_line_id,
3693 char1,
3694 char2,
3695 char3,
3696 char4,
3697 'Y',
3698 (SELECT segment1
3699 FROM po_headers
3700 WHERE po_header_id=pol.po_header_id
3701 ),
3702 date1
3703 FROM
3704 po_lines pol,
3705 po_session_gt pgt
3706 WHERE pgt.key = l_old_key
3707 AND nvl(pol.cancel_flag,'N') = 'N'
3708 AND nvl(pol.closed_code, 'OPEN') <> 'FINALLY CLOSED'
3709 AND (( pgt.char3 = po_document_cancel_pvt.c_entity_level_line
3710 AND pgt.char1 = po_document_cancel_pvt.c_doc_type_PA
3711 AND pgt.num1 = po_line_id )
3712 OR(pgt.char1 = po_document_cancel_pvt.c_doc_type_PA
3713 AND pgt.char3 = po_document_cancel_pvt.c_entity_level_header
3714 AND pgt.num1 = pol.po_header_id ) ));
3715
3716
3717
3718 -- Lowest entity for CPA that will be used for validations is CPA Header
3719 -- Get all the Header id for the CPA for entity level =header
3720
3721 INSERT INTO po_session_gt gt(
3722 key,
3723 num1,
3724 index_num1,
3725 char1,
3726 char2,
3727 char3,
3728 char4,
3729 char5,
3730 char6,
3731 date1)
3732 (SELECT
3733 l_new_key,
3734 num1,
3735 num1,
3736 char1,
3737 char2,
3738 char3,
3739 char4,
3740 'Y',
3741 (SELECT segment1
3742 FROM po_headers
3743 WHERE po_header_id=num1
3744 ),
3745 date1
3746 FROM po_session_gt pgt
3747 WHERE pgt.key = l_old_key
3748 AND pgt.char1 = po_document_cancel_pvt.c_doc_type_PA
3749 AND pgt.char2 = po_document_cancel_pvt.c_doc_subtype_contract);
3750
3751
3752 -- Return the new key
3753 p_key :=l_new_key;
3754
3755
3756 IF g_debug_stmt THEN
3757 PO_DEBUG.debug_end(d_module);
3758 END IF;
3759
3760
3761 EXCEPTION
3762
3763 WHEN FND_API.g_exc_error THEN
3764 x_return_status := FND_API.g_ret_sts_error;
3765 WHEN FND_API.g_exc_unexpected_error THEN
3766 x_return_status := FND_API.g_ret_sts_unexp_error;
3767 WHEN OTHERS THEN
3768 x_return_status := FND_API.g_ret_sts_unexp_error;
3769
3770 END update_gt_with_low_entity;
3771
3772 --------------------------------------------------------------------------------
3773 --Start of Comments
3774 --Name: validate_set
3775 --
3776 -- Function:
3777 -- This routine executes the business rule validations for cancel action
3778 --Parameters:
3779 --IN:
3780 -- p_validation_set
3781 -- p_online_report_id
3782 -- p_action_date
3783 -- p_login_id
3784 -- p_user_id
3785 -- p_sequence
3786 -- p_key
3787
3788 --IN OUT:
3789
3790 -- OUT:
3791 -- x_return_status
3792 -- FND_API.G_RET_STS_SUCCESS if procedure succeeds
3793 -- FND_API.G_RET_STS_ERROR if procedure fails
3794 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
3795
3796 -- x_return_msg
3797
3798 --End of Comments
3799 --------------------------------------------------------------------------------
3800
3801 PROCEDURE validate_set(
3802 p_validation_set IN PO_TBL_VARCHAR2000,
3803 p_online_report_id IN NUMBER,
3804 p_action_date IN DATE,
3805 p_login_id IN po_lines.last_update_login%TYPE,
3806 p_user_id IN po_lines.last_updated_by%TYPE,
3807 p_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
3808 p_key IN po_session_gt.key%TYPE,
3809 x_return_status OUT NOCOPY VARCHAR2,
3810 x_msg_data OUT NOCOPY VARCHAR2)
3811
3812 IS
3813
3814 d_api_name CONSTANT VARCHAR2(30) := 'validate_set.';
3815 d_module CONSTANT VARCHAR2(100) := g_pkg_name||d_api_name;
3816 l_progress VARCHAR2(3):= '000';
3817 l_val VARCHAR2(2000);
3818
3819 BEGIN
3820
3821 x_return_status := FND_API.G_RET_STS_SUCCESS;
3822 x_msg_data :=NULL;
3823 l_progress:= '001';
3824
3825
3826 IF g_debug_stmt THEN
3827 PO_DEBUG.debug_begin(d_module);
3828 PO_DEBUG.debug_var(d_module,l_progress,'p_key',p_key);
3829 PO_DEBUG.debug_var(d_module,l_progress,'p_action_date',p_action_date);
3830 PO_DEBUG.debug_var(d_module,l_progress,'p_online_report_id',p_online_report_id);
3831 END IF;
3832
3833
3834 l_progress := '002';
3835 FOR i IN 1 .. p_validation_set.COUNT
3836 LOOP
3837 l_val := p_validation_set(i);
3838
3839 BEGIN
3840 CASE l_val
3841
3842 -- If there are any Uncancelled shipments that have quantity/amount
3843 -- not matching to sum of its ditributions, then the PO/Release
3844 -- Header/Line/Shipment cannot be cancelled
3845 WHEN c_can_ship_dist_diff_qty THEN
3846 ship_dist_qty_chk(
3847 p_online_report_id =>p_online_report_id,
3848 p_key => p_key,
3849 p_user_id =>p_user_id,
3850 p_login_id =>p_login_id,
3851 p_sequence =>p_sequence,
3852 x_return_status =>x_return_status,
3853 x_msg_data =>x_msg_data);
3854
3855 -- If there are any Uncancelled lines that have quanity/amount
3856 -- not matching to sum of its shipments, then the PO/Release
3857 -- Header/Line cannot be cancelled
3858 WHEN c_can_line_ship_diff_qty THEN
3859 line_ship_qty_chk(
3860 p_online_report_id =>p_online_report_id,
3861 p_key => p_key,
3862 p_user_id =>p_user_id,
3863 p_login_id =>p_login_id,
3864 p_sequence =>p_sequence,
3865 x_return_status =>x_return_status,
3866 x_msg_data =>x_msg_data);
3867
3868 -- If there are any Uncancelled sipments that have unit price
3869 -- not matching to price of its line, then the PO/Release
3870 -- Header/Line/Shipment cannot be cancelled
3871 WHEN c_can_line_ship_diff_price THEN
3872 line_ship_price_chk(
3873 p_online_report_id =>p_online_report_id,
3874 p_key => p_key,
3875 p_user_id =>p_user_id,
3876 p_login_id =>p_login_id,
3877 p_sequence =>p_sequence,
3878 x_return_status =>x_return_status,
3879 x_msg_data =>x_msg_data);
3880
3881 -- If there are any Uncancelled Line that have unit price exceeding
3882 -- the price limit
3883 WHEN c_can_line_price_grt_limit THEN
3884 line_price_chk(
3885 p_online_report_id =>p_online_report_id,
3886 p_key => p_key,
3887 p_user_id =>p_user_id,
3888 p_login_id =>p_login_id,
3889 p_sequence =>p_sequence,
3890 x_return_status =>x_return_status,
3891 x_msg_data =>x_msg_data);
3892
3893 -- if the document is LCM enabled then its shipment
3894 -- must have the invoice match option as 'Receipt'
3895 -- If the validation fails, teh documnet cannot be cancelled
3896 WHEN c_can_lcm_match_option_chk THEN
3897 lcm_match_option_chk(
3898 p_online_report_id =>p_online_report_id,
3899 p_key => p_key,
3900 p_user_id =>p_user_id,
3901 p_login_id =>p_login_id,
3902 p_sequence =>p_sequence,
3903 x_return_status =>x_return_status,
3904 x_msg_data =>x_msg_data);
3905
3906 -- if the document is LCM enabled then its Ditsribution
3907 -- must have the destination type as 'Inventory'
3908 -- If the validation fails, teh documnet cannot be cancelled
3909 WHEN c_can_lcm_dest_type_chk THEN
3910 lcm_dest_type_chk(
3911 p_online_report_id =>p_online_report_id,
3912 p_key => p_key,
3913 p_user_id =>p_user_id,
3914 p_login_id =>p_login_id,
3915 p_sequence =>p_sequence,
3916 x_return_status =>x_return_status,
3917 x_msg_data =>x_msg_data);
3918
3919 -- If there are any Uncancelled shipments that have been received
3920 -- more than they ordered (Fully Received) , the PO/Release
3921 -- Header/Line/Shipment cannot be cancelled
3922 WHEN c_can_qty_rec_grt_ord THEN
3923 qty_rec_grt_ord_chk(
3924 p_online_report_id =>p_online_report_id,
3925 p_key => p_key,
3926 p_user_id =>p_user_id,
3927 p_login_id =>p_login_id,
3928 p_sequence =>p_sequence,
3929 x_return_status =>x_return_status,
3930 x_msg_data =>x_msg_data);
3931
3932 -- If there are any Uncancelled shipments distributions that have
3933 -- been billed more than they ordered (Fully Invoiced) ,
3934 -- the PO/Release Header/Line/Shipment cannot be cancelled
3935 WHEN c_can_qty_bill_grt_ord THEN
3936 qty_bill_grt_ord_chk(
3937 p_online_report_id =>p_online_report_id,
3938 p_key => p_key,
3939 p_user_id =>p_user_id,
3940 p_login_id =>p_login_id ,
3941 p_sequence =>p_sequence,
3942 x_return_status =>x_return_status,
3943 x_msg_data =>x_msg_data);
3944
3945 -- If there are any Uncancelled shipments distributions that have
3946 -- been delivered more than they ordered (Fully Received),
3947 --the PO/Release Header/Line/Shipment cannot be cancelled
3948 WHEN c_can_qty_del_grt_ord THEN
3949 qty_del_grt_ord_chk(
3950 p_online_report_id =>p_online_report_id,
3951 p_key => p_key,
3952 p_user_id =>p_user_id,
3953 p_login_id =>p_login_id ,
3954 p_sequence =>p_sequence,
3955 x_return_status =>x_return_status,
3956 x_msg_data =>x_msg_data);
3957
3958 -- If there are any Uncancelled shipments that have been billed more
3959 -- than they are received ,then cancel action is not allowed on
3960 -- the entity
3961 WHEN c_can_qty_bill_grt_rec THEN
3962 qty_bill_grt_rec_chk(
3963 p_online_report_id =>p_online_report_id,
3964 p_key => p_key,
3965 p_user_id =>p_user_id,
3966 p_login_id =>p_login_id ,
3967 p_sequence =>p_sequence,
3968 x_return_status =>x_return_status,
3969 x_msg_data =>x_msg_data);
3970
3971 -- If there are any Uncancelled shipments distributions that have been
3972 -- billed more than they are delivered ,then cancel action is not
3973 -- allowed on the entity
3974 WHEN c_can_qty_bill_grt_del THEN
3975 qty_bill_grt_del_chk(
3976 p_online_report_id =>p_online_report_id,
3977 p_key => p_key,
3978 p_user_id =>p_user_id,
3979 p_login_id =>p_login_id ,
3980 p_sequence =>p_sequence,
3981 x_return_status =>x_return_status,
3982 x_msg_data =>x_msg_data);
3983
3984 -- If there is anything that is received but not delivered the PO
3985 -- cannot be cancelled
3986 WHEN c_can_qty_rec_not_del THEN
3987 qty_rec_not_del_chk (
3988 p_online_report_id =>p_online_report_id,
3989 p_key => p_key,
3990 p_user_id =>p_user_id,
3991 p_login_id =>p_login_id ,
3992 p_sequence =>p_sequence,
3993 x_return_status =>x_return_status,
3994 x_msg_data =>x_msg_data);
3995
3996 -- If there are any receiving transctions in the receiving interface
3997 -- that have not been processes for the current entity , then the
3998 -- entity ccannot be canceleld
3999 WHEN c_can_with_pending_rcv_trx THEN
4000 pending_rcv_trx_chk(
4001 p_online_report_id =>p_online_report_id,
4002 p_key => p_key,
4003 p_user_id =>p_user_id,
4004 p_login_id =>p_login_id ,
4005 p_sequence =>p_sequence,
4006 x_return_status =>x_return_status,
4007 x_msg_data =>x_msg_data);
4008
4009
4010 -- If there are any ASN that have not been fully received for the
4011 -- shipments,then cancel action is not allowed on the entity
4012 WHEN c_can_with_asn THEN
4013 pending_asn_chk(
4014 p_online_report_id =>p_online_report_id,
4015 p_key => p_key,
4016 p_user_id =>p_user_id,
4017 p_login_id =>p_login_id ,
4018 p_sequence =>p_sequence,
4019 x_return_status =>x_return_status,
4020 x_msg_data =>x_msg_data);
4021
4022 -- If encumbrance is on, then If the budget account is invalid,
4023 -- then cancel action is not allowed on the entity
4024 WHEN c_can_invalid_budget_acct_flex THEN
4025 invalid_budget_acct_chk(
4026 p_online_report_id =>p_online_report_id,
4027 p_key => p_key,
4028 p_action_date =>p_action_date,
4029 p_user_id =>p_user_id,
4030 p_login_id =>p_login_id ,
4031 p_sequence =>p_sequence,
4032 x_return_status =>x_return_status,
4033 x_msg_data =>x_msg_data);
4034
4035
4036 -- If the charge account is invalid, then cancel action is not
4037 -- allowed on the entity
4038 WHEN c_can_invalid_charge_acct_flex THEN
4039 invalid_charge_acct_chk(
4040 p_online_report_id =>p_online_report_id,
4041 p_key => p_key,
4042 p_action_date =>p_action_date,
4043 p_user_id =>p_user_id,
4044 p_login_id =>p_login_id ,
4045 p_sequence =>p_sequence,
4046 x_return_status =>x_return_status,
4047 x_msg_data =>x_msg_data);
4048
4049 -- If GBPA has Uncancelled Open Standard PO reference,
4050 -- then the GBPA cannot be cancelled
4051 WHEN c_can_ga_with_open_std_ref THEN
4052 ga_with_open_std_ref_chk(
4053 p_online_report_id =>p_online_report_id,
4054 p_key => p_key,
4055 p_user_id =>p_user_id,
4056 p_login_id =>p_login_id ,
4057 p_sequence =>p_sequence,
4058 x_return_status =>x_return_status,
4059 x_msg_data =>x_msg_data);
4060
4061 -- If Blanket PA/Planned PO has Uncancelled Open Releases,
4062 -- then the BPA/PPO cannot be cancelled
4063 WHEN c_can_po_pa_with_open_rel THEN
4064 po_pa_WITH_OPEN_REL_chk(
4065 p_online_report_id =>p_online_report_id,
4066 p_key => p_key,
4067 p_user_id =>p_user_id,
4068 p_login_id =>p_login_id ,
4069 p_sequence =>p_sequence,
4070 x_return_status =>x_return_status,
4071 x_msg_data =>x_msg_data);
4072
4073
4074 -- If CPA has Uncancelled Open Standard PO reference,
4075 -- then the CPA cannot be cancelled
4076 WHEN c_can_cga_with_open_std_ref THEN
4077 cga_with_open_std_ref_chk(
4078 p_online_report_id =>p_online_report_id,
4079 p_key => p_key,
4080 p_user_id =>p_user_id,
4081 p_login_id =>p_login_id ,
4082 p_sequence =>p_sequence,
4083 x_return_status =>x_return_status,
4084 x_msg_data =>x_msg_data);
4085
4086 -- If there are any custom validations, until they pass,
4087 -- the document can not be cancelled
4088 WHEN c_can_custom_validation THEN
4089 cancel_custom_validation(
4090 p_online_report_id =>p_online_report_id,
4091 p_key => p_key,
4092 p_user_id =>p_user_id,
4093 p_login_id =>p_login_id ,
4094 p_sequence =>p_sequence,
4095 x_return_status =>x_return_status,
4096 x_msg_data =>x_msg_data);
4097
4098 ELSE
4099 IF g_debug_stmt THEN
4100 PO_DEBUG.debug_var(d_module,l_progress,'Invalid identifier in validation set',l_val);
4101 END IF;
4102 RAISE CASE_NOT_FOUND;
4103 END CASE;
4104
4105 EXCEPTION
4106 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4107 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
4108 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4109
4110 WHEN FND_API.G_EXC_ERROR THEN
4111 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
4112 x_return_status := FND_API.G_RET_STS_ERROR;
4113
4114 WHEN OTHERS THEN
4115 IF (G_DEBUG_UNEXP) THEN
4116 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
4117 d_module || '.OTHERS_EXCEPTION', 'EXCEPTION: LOCATION IS '
4118 || l_progress || ' SQL CODE IS '||sqlcode);
4119 END IF;
4120
4121 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
4122 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4123
4124 END;
4125
4126 END LOOP;
4127
4128 IF g_debug_stmt THEN
4129 PO_DEBUG.debug_end(d_module);
4130 END IF;
4131
4132 END validate_set;
4133
4134 --------------------------------------------------------------------------------
4135 --Start of Comments
4136 --Name: po_cancel_action_checks
4137 --
4138 -- Function:
4139 -- Performs the following. Business Rule Valdiations to allow cancel action
4140 -- 1. If there are any Uncancelled shipments that have been received
4141 -- more than they ordered (Fully Received),
4142 -- the PO/Release Header/Line/Shipment cannot be cancelled
4143 -- 2. If there are any Uncancelled shipments distributions that have
4144 -- been billed more than they ordered (Fully Invoiced) ,
4145 -- the PO/Release Header/Line/Shipment cannot be cancelled
4146 -- 3. If there are any Uncancelled shipments distributions that have
4147 -- been delivered more than they ordered (Fully Received),
4148 -- the PO/Release Header/Line/Shipment cannot be cancelled
4149 -- 4. If there are any receiving transctions in the receiving interface
4150 -- that have not been processes for the current entity , then
4151 -- the entity ccannot be canceleld
4152 -- 5. If there is anything that is received but not delivered the PO
4153 -- cannot be cancelled
4154 -- 6. If encumbrance is on, then if the budget account is invalid, then
4155 -- cancel action is not allowed on the entity
4156 -- 7. If the charge account is invalid, thenn cancel action is not
4157 -- allowed on the entity
4158 -- 8. If there are any Uncancelled shipments that have been billed more
4159 -- than they are received ,then cancel action is not allowed on
4160 -- the entity
4161 -- 9. If there are any Uncancelled shipments distributions that have
4162 -- been billed more than they are delivered ,then cancel action is
4163 -- not allowed on the entity
4164 -- 10. If there are any ASN that have not been fully received for the
4165 -- shipments,then cancel action is not allowed on the entity
4166 -- 11. If Blanket PA/Planned PO has Uncancelled Open Releases, then the
4167 -- BPA/PPO cannot be cancelled
4168 -- 12. If GBPA has Uncancelled Open Standard PO reference, then the GBPA
4169 -- cannot be cancelled
4170 -- 12. If CPA has Uncancelled Open Standard PO reference, then the CPA
4171 -- cannot be cancelled
4172 -- 14. If there are any custom validations, until they pass, the document
4173 -- can not be cancelled
4174 --
4175 --If any of the above validation fails,it inserts the error in po-onlie_report_text
4176
4177 --Parameters:
4178 --IN:
4179 -- p_entity_rec_tbl
4180 -- p_action_date
4181 -- p_key
4182 -- p_user_id
4183 -- p_login_id
4184 -- p_sequence
4185 -- p_online_report_id
4186
4187 --IN OUT:
4188
4189 -- OUT:
4190 -- x_return_status
4191 -- FND_API.G_RET_STS_SUCCESS if procedure succeeds
4192 -- FND_API.G_RET_STS_ERROR if procedure fails
4193 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
4194
4195 -- x_return_msg
4196
4197 --End of Comments
4198 --------------------------------------------------------------------------------
4199
4200 PROCEDURE po_cancel_action_checks(
4201 p_entity_rec_tbl IN po_document_action_pvt.entity_dtl_rec_type_tbl,
4202 p_action_date IN DATE,
4203 p_key IN po_session_gt.key%TYPE,
4204 p_login_id IN po_lines.last_update_login%TYPE,
4205 p_user_id IN po_lines.last_updated_by%TYPE,
4206 p_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
4207 p_online_report_id IN NUMBER,
4208 x_return_status OUT NOCOPY VARCHAR2,
4209 x_return_msg OUT NOCOPY VARCHAR2)
4210 IS
4211
4212 d_api_name CONSTANT VARCHAR2(30) := 'po_cancel_action_checks';
4213 d_module CONSTANT VARCHAR2(100) := g_pkg_name || d_api_name;
4214 l_progress VARCHAR2(3) := '000';
4215
4216
4217 BEGIN
4218
4219 x_return_status := FND_API.G_RET_STS_SUCCESS;
4220 x_return_msg :=NULL;
4221
4222 IF g_debug_stmt THEN
4223 PO_DEBUG.debug_begin(d_module);
4224 PO_DEBUG.debug_var(d_module,l_progress,'p_key',p_key);
4225 PO_DEBUG.debug_var(d_module,l_progress,'p_action_date',p_action_date);
4226 PO_DEBUG.debug_var(d_module,l_progress,'p_online_report_id',p_online_report_id);
4227 END IF;
4228
4229
4230 l_progress := '001';
4231
4232 validate_set(
4233 p_validation_set => cancel_validation_set,
4234 p_online_report_id => p_online_report_id,
4235 p_action_date => p_action_date,
4236 p_key =>p_key,
4237 p_user_id =>p_user_id,
4238 p_login_id =>p_login_id,
4239 p_sequence =>p_sequence,
4240 x_return_status => x_return_status,
4241 x_msg_data => x_return_msg);
4242
4243
4244
4245 IF (x_return_status = FND_API.g_ret_sts_error) THEN
4246 RAISE FND_API.g_exc_error;
4247 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
4248 RAISE FND_API.g_exc_unexpected_error;
4249 END IF;
4250
4251 IF g_debug_stmt THEN
4252 PO_DEBUG.debug_var(d_module,l_progress,'x_return_status',x_return_status);
4253 PO_DEBUG.debug_end(d_module);
4254 END IF;
4255
4256
4257 EXCEPTION
4258 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4259 x_return_msg := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
4260 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4261
4262 WHEN FND_API.G_EXC_ERROR THEN
4263 x_return_msg := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
4264 x_return_status := FND_API.G_RET_STS_ERROR;
4265
4266 WHEN OTHERS THEN
4267 IF (G_DEBUG_UNEXP) THEN
4268 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
4269 d_module || '.OTHERS_EXCEPTION', 'EXCEPTION: LOCATION IS '
4270 || l_progress || ' SQL CODE IS '||sqlcode);
4271 END IF;
4272
4273 x_return_msg := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
4274 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4275
4276 END po_cancel_action_checks;
4277
4278 --------------------------------------------------------------------------------
4279 --Start of Comments
4280 --Name: val_doc_security
4281 --
4282 -- Function:
4283 -- This is wrapper function on PO_REQS_CONTROL_SV.val_doc_security.
4284 -- PO_REQS_CONTROL_SV.val_doc_security returns boolean , so cannot be used
4285 -- in sql statmemt
4286 -- So creating a wrapper on it, that will return 'Y'/'N'.
4287
4288 --Parameters:
4289 --IN:
4290 -- p_doc_agent_id
4291 -- p_agent_id
4292 -- p_doc_type
4293 -- p_doc_subtype
4294
4295 -- Returns
4296 -- 'Y' >>TRUE
4297 -- 'N' >> FALSE
4298
4299 --End of Comments
4300 --------------------------------------------------------------------------------
4301
4302
4303 FUNCTION val_doc_security(
4304 p_doc_agent_id IN NUMBER,
4305 p_agent_id IN NUMBER,
4306 p_doc_type IN VARCHAR2,
4307 p_doc_subtype IN VARCHAR2)
4308 RETURN VARCHAR2
4309 IS
4310
4311 d_api_name CONSTANT VARCHAR2(30) := 'val_doc_security.';
4312 d_module CONSTANT VARCHAR2(100) := g_pkg_name || d_api_name;
4313 l_progress VARCHAR2(3) := '000';
4314
4315
4316 BEGIN
4317 IF g_debug_stmt THEN
4318 PO_DEBUG.debug_begin(d_module);
4319 PO_DEBUG.debug_var(d_module,l_progress,'p_agent_id',p_agent_id);
4320 PO_DEBUG.debug_var(d_module,l_progress,'p_doc_agent_id',p_doc_agent_id);
4321 PO_DEBUG.debug_var(d_module,l_progress,'p_doc_type',p_doc_type);
4322 PO_DEBUG.debug_var(d_module,l_progress,'p_doc_subtype',p_doc_subtype);
4323 END IF;
4324
4325
4326 IF PO_REQS_CONTROL_SV.val_doc_security(
4327 x_doc_agent_id => p_doc_agent_id,
4328 x_agent_id => p_agent_id,
4329 x_doc_type => p_doc_type,
4330 x_doc_subtype => p_doc_subtype) THEN
4331
4332 RETURN 'Y';
4333 ELSE
4334 RETURN 'N';
4335 END IF;
4336 END;
4337
4338 --------------------------------------------------------------------------------
4339 --Start of Comments
4340 --Name: is_complex_work_po
4341 --
4342 -- Function:
4343 -- This is wrapper function on PO_COMPLEX_WORK_PVT.is_complex_work_
4344 -- PO_COMPLEX_WORK_PVT.is_complex_work_po returns boolean,so cannot be used
4345 -- in sql statmemt
4346 -- So creating a wrapper on it, that will return 'Y'/'N'.
4347
4348 --Parameters:
4349 --IN:
4350 -- p_doc_id
4351
4352 -- Returns
4353 -- 'Y' >>TRUE
4354 -- 'N' >> FALSE
4355
4356 --End of Comments
4357 --------------------------------------------------------------------------------
4358
4359 FUNCTION is_complex_work_po(
4360 p_doc_id IN NUMBER)
4361 RETURN VARCHAR2
4362 IS
4363
4364 d_api_name CONSTANT VARCHAR2(30) := 'is_complex_work_po.';
4365 d_module CONSTANT VARCHAR2(100) := g_pkg_name || d_api_name;
4366 l_progress VARCHAR2(3) := '000';
4367
4368 BEGIN
4369
4370 IF g_debug_stmt THEN
4371 PO_DEBUG.debug_begin(d_module);
4372 PO_DEBUG.debug_var(d_module,l_progress,'p_doc_id',p_doc_id);
4373 END IF;
4374
4375 IF PO_COMPLEX_WORK_PVT.is_complex_work_po(
4376 p_po_header_id => p_doc_id) THEN
4377
4378 RETURN 'Y';
4379 ELSE
4380 RETURN 'N';
4381 END IF;
4382
4383 END;
4384
4385 --------------------------------------------------------------------------------
4386 --Start of Comments
4387 --Name: val_doc_state_check
4388 --
4389 -- Function:
4390 -- Checks if p_agent_id has the access and security clearance to modify
4391 -- or act upon the document
4392
4393 --Parameters:
4394 --IN:
4395 -- p_entity_rec_tbl
4396 -- p_online_report_id
4397 -- p_user_id
4398 -- p_login_id
4399 -- p_sequence
4400 -- p_agent_id
4401 -- p_key
4402
4403 --IN OUT:
4404
4405 -- OUT:
4406 -- x_return_status
4407 -- FND_API.G_RET_STS_SUCCESS if procedure succeeds
4408 -- FND_API.G_RET_STS_ERROR if procedure fails
4409 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
4410
4411 -- x_return_msg
4412
4413 --End of Comments
4414 --------------------------------------------------------------------------------
4415
4416 PROCEDURE val_security_check(
4417 p_entity_rec_tbl IN po_document_action_pvt.entity_dtl_rec_type_tbl,
4418 p_online_report_id IN NUMBER,
4419 p_key IN po_session_gt.key%TYPE,
4420 p_user_id IN po_lines.last_updated_by%TYPE,
4421 p_login_id IN po_lines.last_update_login%TYPE,
4422 p_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
4423 p_agent_id IN PO_HEADERS.agent_id%TYPE,
4424 x_return_status OUT NOCOPY VARCHAR2,
4425 x_return_msg OUT NOCOPY VARCHAR2)
4426
4427
4428
4429 IS
4430
4431 d_api_name CONSTANT VARCHAR2(30) := 'val_security_check.';
4432 d_module CONSTANT VARCHAR2(100) := g_pkg_name || d_api_name;
4433 l_progress VARCHAR2(3) := '000';
4434 l_flag BOOLEAN := FALSE ;
4435
4436
4437
4438 BEGIN
4439
4440 x_return_status := FND_API.G_RET_STS_SUCCESS;
4441 x_return_msg :=NULL;
4442
4443
4444 IF g_debug_stmt THEN
4445 PO_DEBUG.debug_begin(d_module);
4446 PO_DEBUG.debug_var(d_module,l_progress,'p_key',p_key);
4447 PO_DEBUG.debug_var(d_module,l_progress,'p_agent_id',p_agent_id);
4448 PO_DEBUG.debug_var(d_module,l_progress,'p_online_report_id',p_online_report_id);
4449 PO_DEBUG.debug_var(d_module,l_progress,'p_sequence',p_sequence);
4450 END IF;
4451
4452 l_progress :='001';
4453
4454 -- Validate agent security for SPO/PPO/BPA/CPA
4455 INSERT INTO PO_online_report_text(
4456 ONLINE_REPORT_ID,
4457 LAST_UPDATE_LOGIN,
4458 LAST_UPDATED_BY,
4459 LAST_UPDATE_DATE,
4460 CREATED_BY,
4461 CREATION_DATE,
4462 LINE_NUM,
4463 SHIPMENT_NUM,
4464 DISTRIBUTION_NUM,
4465 SEQUENCE,
4466 TEXT_LINE,
4467 transaction_id,
4468 transaction_type)
4469 (SELECT
4470 p_online_report_id,
4471 p_login_id,
4472 p_user_id,
4473 SYSDATE,
4474 p_user_id,
4475 SYSDATE,
4476 0,
4477 0,
4478 0,
4479 p_sequence + ROWNUM,
4480 PO_CORE_S.get_translated_text('PO_CAN_CONTROL_SECURITY_FAILED',
4481 'DOC_NUM', gt.char6),
4482 gt.num1,
4483 gt.char3
4484 FROM po_headers poh,
4485 po_session_gt gt
4486 WHERE gt.key=p_key
4487 AND poh.po_header_id = gt.char4
4488 AND gt.char1 <> po_document_cancel_pvt.c_doc_type_RELEASE
4489 AND (po_control_action_validations.val_doc_security(
4490 poh.agent_id,
4491 p_agent_id,
4492 gt.char1,
4493 gt.char2) <>'Y' )) ;
4494
4495
4496
4497 p_sequence :=p_sequence+SQL%ROWCOUNT;
4498
4499 l_progress :='002';
4500
4501 IF g_debug_stmt THEN
4502 PO_DEBUG.debug_var(d_module,l_progress,'p_sequence',p_sequence);
4503 END IF;
4504
4505
4506 -- Validate agent security for Releases
4507
4508 INSERT INTO PO_online_report_text(
4509 ONLINE_REPORT_ID,
4510 LAST_UPDATE_LOGIN,
4511 LAST_UPDATED_BY,
4512 LAST_UPDATE_DATE,
4513 CREATED_BY,
4514 CREATION_DATE,
4515 LINE_NUM,
4516 SHIPMENT_NUM,
4517 DISTRIBUTION_NUM,
4518 SEQUENCE,
4519 TEXT_LINE,
4520 transaction_id,
4521 transaction_type)
4522 (SELECT
4523 p_online_report_id,
4524 p_login_id,
4525 p_user_id,
4526 SYSDATE,
4527 p_user_id,
4528 SYSDATE,
4529 0,
4530 0,
4531 0,
4532 p_sequence + ROWNUM,
4533 PO_CORE_S.get_translated_text('PO_CAN_CONTROL_SECURITY_FAILED',
4534 'DOC_NUM',gt.char6),
4535 gt.num1,
4536 gt.char3
4537 FROM
4538 po_releases prh,
4539 po_session_gt gt
4540 WHERE gt.key=p_key
4541 AND prh.po_release_id = gt.char4
4542 AND gt.char1 = po_document_cancel_pvt.c_doc_type_RELEASE
4543 AND(po_control_action_validations.val_doc_security(
4544 prh.agent_id,
4545 p_agent_id,
4546 gt.char1,
4547 gt.char2) <>'Y' )) ;
4548
4549 p_sequence :=p_sequence+SQL%ROWCOUNT;
4550
4551 l_progress :='003';
4552
4553 IF g_debug_stmt THEN
4554 PO_DEBUG.debug_var(d_module,l_progress,'p_sequence',p_sequence);
4555 PO_DEBUG.debug_end(d_module);
4556 END IF;
4557
4558
4559
4560 EXCEPTION
4561 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4562 x_return_msg := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
4563 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4564
4565 WHEN FND_API.G_EXC_ERROR THEN
4566 x_return_msg := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
4567 x_return_status := FND_API.G_RET_STS_ERROR;
4568
4569 WHEN OTHERS THEN
4570 IF (G_DEBUG_UNEXP) THEN
4571 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
4572 d_module || '.OTHERS_EXCEPTION', 'EXCEPTION: LOCATION IS '
4573 || l_progress || ' SQL CODE IS '||sqlcode);
4574 END IF;
4575
4576 x_return_msg := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
4577 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4578
4579 END val_security_check;
4580
4581 --------------------------------------------------------------------------------
4582 --Start of Comments
4583 --Name: val_doc_state_check
4584 --
4585 -- Function:
4586 -- 1. Validates the document state,in other words action is valid for the
4587 -- current document state if the caller is CANCEL API
4588 -- - Document should be atleast once approved
4589 -- - Document should be in APPROVED/REJECTED/REQUIRES-REAPPROVAL status
4590 -- - Document should not be Finally Closed/Hold
4591 -- 2. Validates if the document has not been changed since its last revision.
4592 -- If any of the above validation fails,it inserts the error in
4593 -- po_onlie_report_text
4594
4595 --Parameters:
4596 --IN:
4597 -- p_entity_rec_tbl
4598 -- p_online_report_id
4599 -- p_user_id
4600 -- p_login_id
4601 -- p_sequence
4602 -- p_source
4603 -- p_key
4604
4605 --IN OUT:
4606
4607 -- OUT:
4608 -- x_return_status
4609 -- FND_API.G_RET_STS_SUCCESS if procedure succeeds
4610 -- FND_API.G_RET_STS_ERROR if procedure fails
4611 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
4612
4613 -- x_return_msg
4614
4615 --End of Comments
4616 --------------------------------------------------------------------------------
4617
4618 PROCEDURE val_doc_state_check(
4619 p_entity_rec_tbl IN po_document_action_pvt.entity_dtl_rec_type_tbl,
4620 p_online_report_id IN NUMBER,
4621 p_agent_id IN PO_HEADERS.agent_id%TYPE,
4622 p_user_id IN po_lines.last_updated_by%TYPE,
4623 p_login_id IN po_lines.last_update_login%TYPE,
4624 p_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
4625 p_source IN VARCHAR2 DEFAULT NULL,
4626 p_key IN po_session_gt.key%TYPE,
4627 x_return_status OUT NOCOPY VARCHAR2,
4628 x_return_msg OUT NOCOPY VARCHAR2)
4629 IS
4630
4631 d_api_name CONSTANT VARCHAR2(30) := 'val_doc_state_check.';
4632 d_module CONSTANT VARCHAR2(100) := g_pkg_name || d_api_name;
4633 l_progress VARCHAR2(3) := '000';
4634
4635
4636 l_allowable_actions_tbl PO_Document_Control_PVT.g_lookup_code_tbl_type;
4637 l_displayed_field_tbl PO_Document_Control_PVT.g_displayed_field_tbl_type;
4638 l_action PO_LOOKUP_CODES.lookup_code%TYPE;
4639
4640 l_doc_subtype PO_DOCUMENT_TYPES.document_subtype%TYPE;
4641 l_doc_type PO_DOCUMENT_TYPES.document_type_code%TYPE;
4642
4643 l_doc_line_loc_id NUMBER;
4644 l_doc_line_id NUMBER;
4645 l_doc_id NUMBER;
4646
4647 l_current_entity_changed VARCHAR2(1);
4648 l_action_ok BOOLEAN;
4649
4650 BEGIN
4651
4652
4653 x_return_status := FND_API.G_RET_STS_SUCCESS;
4654 x_return_msg :=NULL;
4655
4656 IF g_debug_stmt THEN
4657 PO_DEBUG.debug_begin(d_module);
4658 PO_DEBUG.debug_var(d_module,l_progress,'p_key',p_key);
4659 PO_DEBUG.debug_var(d_module,l_progress,'p_user_id',p_user_id);
4660 PO_DEBUG.debug_var(d_module,l_progress,'p_login_id',p_login_id);
4661 PO_DEBUG.debug_var(d_module,l_progress,'p_online_report_id',p_online_report_id);
4662 PO_DEBUG.debug_var(d_module,l_progress,'p_source',p_source);
4663 END IF;
4664
4665
4666 l_action :='CANCEL';
4667 l_progress :='001';
4668
4669 -- For each entity
4670 FOR i IN 1..p_entity_rec_tbl.Count LOOP
4671
4672 l_doc_subtype := p_entity_rec_tbl(i).document_subtype;
4673 l_doc_type := p_entity_rec_tbl(i).document_type;
4674 l_doc_id := p_entity_rec_tbl(i).doc_id;
4675
4676 -- If the calling mode is cancel api, then only validate the applicability
4677 -- of cancel action on the current entity state, other wise, it will
4678 -- already be validated
4679 IF nvl(p_source,'NULL') NOT IN(PO_DOCUMENT_CANCEL_PVT.c_HTML_CONTROL_ACTION,
4680 PO_DOCUMENT_CANCEL_PVT.c_FORM_CONTROL_ACTION)
4681 THEN
4682
4683 l_action_ok := FALSE;
4684
4685 IF p_entity_rec_tbl(i).entity_level=po_document_cancel_pvt.c_entity_level_HEADER THEN
4686 l_doc_line_id := NULL;
4687 l_doc_line_loc_id := NULL;
4688
4689 IF g_debug_stmt THEN
4690 PO_DEBUG.debug_var(d_module,l_progress,'l_doc_subtype',l_doc_subtype);
4691 PO_DEBUG.debug_var(d_module,l_progress,'l_doc_id',l_doc_id);
4692 PO_DEBUG.debug_var(d_module,l_progress,'l_doc_type',l_doc_type);
4693 END IF;
4694
4695 IF l_doc_type =po_document_cancel_pvt.c_doc_type_RELEASE THEN
4696 l_progress :='002';
4697 l_action := 'CANCEL REL';
4698
4699 PO_Document_Control_PVT.get_rel_header_actions(
4700 p_doc_subtype => l_doc_subtype,
4701 p_doc_id => l_doc_id,
4702 p_agent_id => p_agent_id,
4703 x_lookup_code_tbl => l_allowable_actions_tbl,
4704 x_displayed_field_tbl => l_displayed_field_tbl,
4705 x_return_status => x_return_status);
4706 ELSE
4707 l_progress :='003';
4708 l_action := 'CANCEL PO';
4709 PO_Document_Control_PVT.get_header_actions(
4710 p_doc_subtype => l_doc_subtype,
4711 p_doc_id => l_doc_id,
4712 p_agent_id => p_agent_id,
4713 x_lookup_code_tbl => l_allowable_actions_tbl,
4714 x_displayed_field_tbl => l_displayed_field_tbl,
4715 x_return_status => x_return_status);
4716 END IF;
4717
4718 ELSIF p_entity_rec_tbl(i).entity_level=po_document_cancel_pvt.c_entity_level_LINE THEN
4719 l_doc_line_id := p_entity_rec_tbl(i).entity_id;
4720 l_doc_line_loc_id := NULL;
4721
4722 l_progress :='004';
4723 l_action := 'CANCEL PO LINE';
4724
4725 PO_Document_Control_PVT.get_line_actions(
4726 p_doc_subtype => l_doc_subtype,
4727 p_doc_line_id => l_doc_line_id,
4728 p_agent_id => p_agent_id,
4729 x_lookup_code_tbl => l_allowable_actions_tbl,
4730 x_displayed_field_tbl => l_displayed_field_tbl,
4731 x_return_status => x_return_status);
4732
4733
4734
4735 ELSIF p_entity_rec_tbl(i).entity_level=po_document_cancel_pvt.c_entity_level_SHIPMENT THEN
4736 l_doc_line_id := NULL;
4737 l_doc_line_loc_id :=p_entity_rec_tbl(i).entity_id;
4738
4739 IF l_doc_type =po_document_cancel_pvt.c_doc_type_RELEASE THEN
4740 l_progress :='005';
4741 l_action := 'CANCEL REL SHIPMENT';
4742
4743 PO_Document_Control_PVT.get_rel_shipment_actions(
4744 p_doc_subtype => l_doc_subtype,
4745 p_doc_line_loc_id => l_doc_line_loc_id,
4746 p_agent_id => p_agent_id,
4747 x_lookup_code_tbl => l_allowable_actions_tbl,
4748 x_displayed_field_tbl => l_displayed_field_tbl,
4749 x_return_status => x_return_status);
4750 ELSE
4751 l_action := 'CANCEL PO SHIPMENT';
4752 l_progress :='006';
4753 PO_Document_Control_PVT.get_shipment_actions(
4754 p_doc_type => l_doc_type,
4755 p_doc_subtype => l_doc_subtype,
4756 p_doc_line_loc_id => l_doc_line_loc_id,
4757 p_agent_id => p_agent_id,
4758 x_lookup_code_tbl => l_allowable_actions_tbl,
4759 x_displayed_field_tbl => l_displayed_field_tbl,
4760 x_return_status => x_return_status);
4761 END IF; -- l_doc_type =po_document_cancel_pvt.c_doc_type_RELEASE
4762
4763 END IF;-- p_entity_rec_tbl(i).entity_level=c_entity_level_HEADER
4764
4765
4766
4767
4768 IF (x_return_status = FND_API.g_ret_sts_error) THEN
4769 l_action_ok := FALSE;
4770 x_return_status := FND_API.g_ret_sts_success;
4771 l_progress :='007';
4772 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
4773 RAISE FND_API.g_exc_unexpected_error;
4774 END IF;
4775
4776
4777 IF g_debug_stmt THEN
4778 PO_DEBUG.debug_var(d_module,l_progress,'l_action',l_action);
4779 PO_DEBUG.debug_var(d_module,l_progress,'p_agent_id',p_agent_id);
4780 PO_DEBUG.debug_var(d_module,l_progress,'l_allowable_actions_tbl.count',l_allowable_actions_tbl.count);
4781 PO_DEBUG.debug_var(d_module,l_progress,'l_displayed_field_tbl.count',l_displayed_field_tbl.count);
4782 END IF;
4783
4784
4785 l_progress :='007';
4786
4787 -- If any valid actions are returned for this entity
4788 IF l_allowable_actions_tbl.Count >0 THEN
4789 l_progress :='008';
4790 -- Loop through allowable actions to see if this action is in the set
4791 FOR i IN l_allowable_actions_tbl.first..l_allowable_actions_tbl.last
4792 LOOP
4793 IF (l_action = l_allowable_actions_tbl(i)) THEN
4794 l_action_ok := TRUE;
4795 EXIT;
4796 END IF;
4797 END LOOP;
4798 END IF ;
4799
4800 IF g_debug_stmt THEN
4801 PO_DEBUG.debug_var(d_module,l_progress,'l_action_ok',l_action_ok);
4802 END IF;
4803
4804 l_progress :='009';
4805 -- If not in the set, insert error
4806 IF NOT l_action_ok THEN
4807
4808 INSERT INTO po_online_report_text(
4809 ONLINE_REPORT_ID,
4810 LAST_UPDATE_LOGIN,
4811 LAST_UPDATED_BY,
4812 LAST_UPDATE_DATE,
4813 CREATED_BY,
4814 CREATION_DATE,
4815 LINE_NUM,
4816 SHIPMENT_NUM,
4817 DISTRIBUTION_NUM,
4818 SEQUENCE,
4819 TEXT_LINE,
4820 transaction_id,
4821 transaction_type) VALUES
4822 (p_online_report_id,
4823 p_login_id,
4824 p_user_id,
4825 SYSDATE,
4826 p_user_id,
4827 SYSDATE,
4828 0,
4829 0,
4830 0,
4831 p_sequence + 1,
4832 PO_CORE_S.get_translated_text(
4833 'PO_CAN_CONTROL_INVALID_ACTION',
4834 'DOC_NUM',
4835 Decode(p_entity_rec_tbl(i).document_type,
4836 po_document_cancel_pvt.c_doc_type_RELEASE,
4837 (SELECT poh.segment1||'-'|| por.release_num
4838 FROM po_releases por,
4839 po_headers poh
4840 WHERE por.po_release_id=p_entity_rec_tbl(i).doc_id
4841 AND por.po_header_id=poh.po_header_id),
4842 (SELECT segment1
4843 FROM po_headers
4844 WHERE po_header_id=p_entity_rec_tbl(i).doc_id)
4845 ),
4846 'ACTION',
4847 'CANCEL',
4848 'ENTITY_LEVEL',
4849 p_entity_rec_tbl(i).entity_level),
4850
4851 p_entity_rec_tbl(i).entity_id,
4852 p_entity_rec_tbl(i).entity_level);
4853
4854 p_sequence := P_SEQUENCE + 1;
4855 END IF; -- NOT l_action_ok
4856
4857
4858 END IF; --- p_source =c_cancel_api
4859
4860
4861 l_progress :='010';
4862
4863 END LOOP;
4864
4865 IF g_debug_stmt THEN
4866 PO_DEBUG.debug_end(d_module);
4867 END IF ;
4868
4869 EXCEPTION
4870 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4871 x_return_msg := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
4872 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4873
4874 WHEN FND_API.G_EXC_ERROR THEN
4875 x_return_msg := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
4876 x_return_status := FND_API.G_RET_STS_ERROR;
4877
4878 WHEN OTHERS THEN
4879 IF (G_DEBUG_UNEXP) THEN
4880 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
4881 d_module || '.OTHERS_EXCEPTION', 'EXCEPTION: LOCATION IS '
4882 || l_progress || ' SQL CODE IS '||sqlcode);
4883 END IF;
4884
4885 x_return_msg := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
4886 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4887
4888 END val_doc_state_check;
4889
4890 --------------------------------------------------------------------------------
4891 --Start of Comments
4892 --Name: revert_pending_changes
4893
4894 --Function:
4895 -- Reverts the non archived changes[only vital columns] in the base tables
4896 -- If the user i/p flag "Revert_change_flag" is set to Y.
4897 -- Following columns are reverted :
4898 -- Po Distributions : Amount Ordered and Quantity Ordered
4899 -- Po Shipments : Amount,Quantity,Price Override,Need By date and Promised Date
4900 -- Po Lines : Amount,Quantity and Unit Price
4901
4902
4903 --Parameters:
4904 --IN:
4905 -- p_revert_chg_flag
4906 -- p_online_report_id
4907 -- p_user_id
4908 -- p_login_id
4909 -- p_key
4910
4911 --IN OUT :
4912 --OUT :
4913 -- x_msg_data
4914 -- x_return_status -
4915 -- FND_API.G_RET_STS_SUCCESS if cancel action succeeds
4916 -- FND_API.G_RET_STS_ERROR if cancel action fails
4917 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
4918 --
4919 --End of Comments
4920 --------------------------------------------------------------------------------
4921
4922
4923 PROCEDURE revert_pending_changes(
4924 p_api_version IN NUMBER,
4925 p_init_msg_list IN VARCHAR2,
4926 p_revert_chg_flag IN VARCHAR2,
4927 p_online_report_id IN NUMBER,
4928 p_user_id IN po_lines.last_updated_by%TYPE,
4929 p_login_id IN po_lines.last_update_login%TYPE,
4930 p_key IN po_session_gt.key%TYPE,
4931 x_return_status OUT NOCOPY VARCHAR2,
4932 x_msg_data OUT NOCOPY VARCHAR2)
4933 IS
4934
4935 d_api_name CONSTANT VARCHAR2(30) := 'revert_pending_changes';
4936 d_api_version CONSTANT NUMBER := 1.0;
4937 d_module CONSTANT VARCHAR2(100) := g_pkg_name || d_api_name;
4938
4939 l_progress VARCHAR2(3) := '000' ;
4940 l_line_loc_id_tbl po_tbl_number;
4941 l_line_id_tbl po_tbl_number;
4942 l_sequence po_online_report_text.sequence%TYPE;
4943 l_line_token VARCHAR2(20);
4944 l_ship_token VARCHAR2(20);
4945 l_amt_token VARCHAR2(20);
4946 l_qty_token VARCHAR2(20);
4947 l_doc_token VARCHAR2(20);
4948 l_to_token VARCHAR2(20);
4949
4950
4951
4952 BEGIN
4953
4954 -- Start standard API initialization
4955 IF FND_API.to_boolean(p_init_msg_list) THEN
4956 FND_MSG_PUB.initialize;
4957 END IF;
4958
4959 IF NOT FND_API.compatible_api_call(d_api_version, p_api_version,
4960 d_api_name, g_pkg_name) THEN
4961 RAISE FND_API.g_exc_unexpected_error;
4962 END IF;
4963
4964 l_line_token := fnd_message.get_string('PO', 'PO_ZMVOR_LINE');
4965 l_ship_token := fnd_message.get_string('PO', 'PO_ZMVOR_SHIPMENT');
4966 l_amt_token := fnd_message.get_string('PO', 'PO_WF_NOTIF_AMOUNT');
4967 l_qty_token := fnd_message.get_string('PO', 'PO_WF_NOTIF_QUANTITY');
4968 l_doc_token := fnd_message.get_string('PO', 'PO_DOCUMENT_LABEL');
4969 l_to_token := fnd_message.get_string('PO', 'PO_WF_NOTIF_TO');
4970
4971
4972 IF g_debug_stmt THEN
4973 PO_DEBUG.debug_begin(d_module);
4974 PO_DEBUG.debug_var(d_module,l_progress,'p_revert_chg_flag',p_revert_chg_flag);
4975 PO_DEBUG.debug_var(d_module,l_progress,'p_online_report_id',p_online_report_id);
4976 PO_DEBUG.debug_var(d_module,l_progress,'p_user_id',p_user_id);
4977 PO_DEBUG.debug_var(d_module,l_progress,'p_login_id',p_login_id);
4978 PO_DEBUG.debug_var(d_module,l_progress,'p_key',p_key);
4979 END IF;
4980
4981 x_return_status := FND_API.G_RET_STS_SUCCESS;
4982
4983 IF p_revert_chg_flag ='Y' THEN
4984
4985 SELECT Nvl(Max(sequence) ,0)
4986 INTO l_sequence
4987 FROM PO_ONLINE_REPORT_TEXT
4988 WHERE online_report_id=p_online_report_id;
4989
4990 IF g_debug_stmt THEN
4991 PO_DEBUG.debug_var(d_module,l_progress,'l_sequence',l_sequence);
4992 END IF;
4993
4994 -- Reverting back the distributions amount_ordered and quantity_ordered
4995 UPDATE po_distributions_all pod
4996 SET (amount_ordered ,
4997 quantity_ordered )=
4998 (SELECT
4999 amount_ordered,
5000 quantity_ordered
5001 FROM po_distributions_archive_all poad
5002 WHERE poad.po_distribution_id =pod.po_distribution_id
5003 AND poad.latest_external_flag ='Y')
5004 WHERE pod.line_location_id IN
5005 (SELECT poll.line_location_id
5006 FROM po_session_gt gt,
5007 po_line_locations poll,
5008 po_line_locations_archive_all poall
5009 WHERE gt.key=p_key
5010 AND gt.char1 <> PO_Document_Cancel_PVT.c_doc_type_PA
5011 AND nvl(gt.char5,'Y') <> 'N'
5012 AND poall.line_location_id =poll.line_location_id
5013 AND poall.latest_external_flag ='Y'
5014 AND (Nvl(poll.price_override,0) <> Nvl(poall.price_override,0)
5015 OR Nvl(poll.quantity,0) <> Nvl(poall.quantity,0)
5016 OR Nvl(poll.amount,0) <> Nvl(poall.amount,0)
5017 OR Nvl(poll.promised_date,sysdate) <> Nvl(poall.promised_date,sysdate)
5018 OR Nvl(poll.need_by_date,sysdate) <> Nvl(poall.need_by_date,sysdate))
5019 AND (( poll.line_location_id=gt.num1
5020 AND gt.char3=PO_Document_Cancel_PVT.c_entity_level_SHIPMENT)
5021 OR (poll.po_line_id=gt.num1
5022 AND gt.char3=PO_Document_Cancel_PVT.c_entity_level_LINE)
5023 OR (poll.po_header_id=gt.num1
5024 AND gt.char1<>PO_Document_Cancel_PVT.c_doc_type_RELEASE
5025 AND gt.char3=PO_Document_Cancel_PVT.c_entity_level_HEADER)
5026 OR (poll.po_release_id=gt.num1
5027 AND gt.char1=PO_Document_Cancel_PVT.c_doc_type_RELEASE
5028 AND gt.char3=PO_Document_Cancel_PVT.c_entity_level_HEADER)
5029
5030 ))
5031
5032 RETURNING line_location_id,po_line_id
5033 BULK COLLECT INTO l_line_loc_id_tbl,l_line_id_tbl;
5034
5035 IF g_debug_stmt THEN
5036 PO_DEBUG.debug_var(d_module,l_progress,'Updated distributions -Line Loc Count',l_line_loc_id_tbl.count);
5037 PO_DEBUG.debug_var(d_module,l_progress,'Updated distributions-Line Count',l_line_id_tbl.count);
5038 END IF;
5039
5040 -- Reverting back the Lines Quantity and amount
5041 UPDATE po_line_locations poll
5042 SET (price_override,
5043 quantity,
5044 amount,
5045 need_by_date,
5046 promised_date) =
5047 (SELECT
5048 price_override,
5049 quantity,
5050 amount,
5051 need_by_date,
5052 promised_date
5053 FROM
5054 po_line_locations_archive_all poall
5055 WHERE poall.line_location_id =poll.line_location_id
5056 AND poall.latest_external_flag ='Y')
5057 WHERE line_location_id IN
5058 (SELECT *
5059 FROM TABLE (CAST (l_line_loc_id_tbl AS po_tbl_number)));
5060
5061
5062 -- Reverting back the Lines Quantity and amount
5063 UPDATE po_lines pol
5064 SET (amount , quantity )=
5065 (SELECT SUM(DECODE(
5066 POLL.amount,
5067 NULL,
5068 --Quantity or Amount Line Locations
5069 ((NVL(poll.quantity,0) - NVL(poll.quantity_cancelled,0))
5070 * POLL.price_override),
5071 -- Fixed Price or Rate Line Locations
5072 (NVL(poll.amount, 0) - NVL(poll.amount_cancelled,0))
5073 )),
5074 SUM(NVL(poll.quantity,0)
5075 - NVL(poll.quantity_cancelled, 0))
5076
5077
5078 FROM po_line_locations POLL
5079 WHERE poll.po_line_id = pol.po_line_id)
5080 WHERE po_line_id IN (SELECT *
5081 FROM TABLE (CAST (l_line_id_tbl AS po_tbl_number)));
5082
5083 -- Bug 14580278 :Updating Price for Non Complex SPO Lines
5084
5085 UPDATE po_lines pol
5086 SET pol.unit_price =
5087 (SELECT unit_price
5088 FROM po_lines_archive_all
5089 WHERE po_line_id = pol.po_line_id
5090 AND latest_external_flag='Y')
5091 WHERE po_line_id IN (SELECT po_line_id
5092 FROM po_line_locations poll
5093 WHERE poll.payment_type IS NULL
5094 AND line_location_id IN
5095 (SELECT *
5096 FROM TABLE (CAST (l_line_loc_id_tbl AS po_tbl_number)))
5097 );
5098
5099
5100
5101 -- Updating Price for Complex PO Line with Milestone pay items
5102 UPDATE po_lines pol
5103 SET pol.unit_price =
5104 (SELECT SUM(price_override)
5105 FROM po_line_locations
5106 WHERE po_line_id = pol.po_line_id)
5107 WHERE pol.order_type_lookup_code IN ('QUANTITY', 'AMOUNT')
5108 AND po_line_id IN (SELECT po_line_id
5109 FROM po_line_locations poll
5110 WHERE poll.payment_type IS NOT NULL
5111 AND line_location_id IN
5112 (SELECT *
5113 FROM TABLE (CAST (l_line_loc_id_tbl AS po_tbl_number)))
5114 );
5115
5116 --Gt Table Columns Mapping
5117 --num1 - entity_id
5118 --char1 - document_type
5119 --char2 - document_subtype
5120 --char3 - entity_level
5121 --char4 - doc_id
5122 --char5 - process_entity_flag
5123 --date1 - entity_action_date
5124 --index_num1 - lowestentityid
5125 --lowestentityid :
5126 -- shipmentid in case of document type= PO/Releases and
5127 -- subtype= Planned/Standard at any entity level
5128 -- Lineid in case of BPA and GBPA at any entity level
5129 -- Headerid in case of CPA
5130
5131
5132 INSERT INTO PO_ONLINE_REPORT_TEXT(
5133 ONLINE_REPORT_ID,
5134 LAST_UPDATE_LOGIN,
5135 LAST_UPDATED_BY,
5136 LAST_UPDATE_DATE,
5137 CREATED_BY,
5138 CREATION_DATE,
5139 LINE_NUM,
5140 SHIPMENT_NUM,
5141 DISTRIBUTION_NUM,
5142 SEQUENCE,
5143 TEXT_LINE,
5144 message_type,
5145 transaction_id,
5146 transaction_type)
5147 (SELECT
5148 p_online_report_id,
5149 p_login_id,
5150 p_user_id,
5151 SYSDATE,
5152 p_user_id,
5153 SYSDATE,
5154 POL.LINE_NUM,
5155 poll.SHIPMENT_NUM,
5156 0,
5157 l_sequence + ROWNUM,
5158 PO_CORE_S.get_translated_text(
5159 'PO_CHANGED_CANT_CANCEL_INFO',
5160 'DOC_LINE_SHIP_DIST_NUM',l_doc_token||gt.char6||','|| l_line_token||pol.LINE_NUM||','||l_ship_token||poll.SHIPMENT_NUM,
5161 'PRICE_TOKEN',poll.price_override||l_to_token || (SELECT price_override
5162 FROM po_line_locations_archive_all
5163 WHERE latest_external_flag ='Y'
5164 AND line_location_id =poll.line_location_id),
5165 'AMT_QTY_TOKEN', DECODE(poll.amount,NULL, 'AMOUNT',l_amt_token,l_qty_token),
5166 'QTY_AMT',Decode(poll.amount,NULL,
5167 poll.quantity||l_to_token || (SELECT quantity
5168 FROM po_line_locations_archive_all
5169 WHERE latest_external_flag ='Y'
5170 AND line_location_id =poll.line_location_id),
5171 poll.amount||l_to_token || (SELECT amount
5172 FROM po_line_locations_archive_all
5173 WHERE latest_external_flag ='Y'
5174 AND line_location_id =poll.line_location_id)
5175 ),
5176 'NEED_BY_PRM_DATE', Decode(poll.promised_date,NULL,
5177 poll.need_by_date||l_to_token || (SELECT need_by_date
5178 FROM po_line_locations_archive_all
5179 WHERE latest_external_flag ='Y'
5180 AND line_location_id =poll.line_location_id),
5181 poll.promised_date||l_to_token || (SELECT promised_date
5182 FROM po_line_locations_archive_all
5183 WHERE latest_external_flag ='Y'
5184 AND line_location_id =poll.line_location_id)
5185
5186
5187
5188 )),
5189 'I',
5190 gt.num1,
5191 gt.char3
5192 FROM
5193 po_line_locations poll,
5194 po_lines pol,
5195 po_session_gt gt
5196 WHERE
5197 gt.key=p_key
5198 AND gt.char1 <> PO_Document_Cancel_PVT.c_doc_type_PA
5199 AND poll.po_line_id = pol.po_line_id
5200 AND poll.line_location_id IN
5201 (SELECT line_location_id
5202 FROM TABLE (CAST (l_line_loc_id_tbl AS po_tbl_number)))
5203 AND ( (poll.line_location_id=gt.num1
5204 AND gt.char3=PO_Document_Cancel_PVT.c_entity_level_SHIPMENT)
5205 OR(poll.po_line_id=gt.num1
5206 AND gt.char3=PO_Document_Cancel_PVT.c_entity_level_LINE)
5207 OR(poll.po_header_id=gt.num1
5208 AND gt.char1<>PO_Document_Cancel_PVT.c_doc_type_RELEASE
5209 AND gt.char3=PO_Document_Cancel_PVT.c_entity_level_HEADER)
5210 OR (poll.po_release_id=gt.num1
5211 AND gt.char1=PO_Document_Cancel_PVT.c_doc_type_RELEASE
5212 AND gt.char3=PO_Document_Cancel_PVT.c_entity_level_HEADER)
5213
5214 ));
5215
5216
5217
5218
5219 END IF;
5220
5221 EXCEPTION
5222 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5223 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
5224 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5225 FND_MSG_PUB.add_exc_msg(g_pkg_name, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
5226 WHEN FND_API.G_EXC_ERROR THEN
5227 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
5228 x_return_status := FND_API.G_RET_STS_ERROR;
5229 FND_MSG_PUB.add_exc_msg(g_pkg_name, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
5230 WHEN OTHERS THEN
5231 IF (G_DEBUG_UNEXP) THEN
5232 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
5233 d_module || '.OTHERS_EXCEPTION', 'EXCEPTION: LOCATION IS '
5234 || l_progress || ' SQL CODE IS '||sqlcode);
5235 END IF;
5236
5237 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
5238 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5239 FND_MSG_PUB.add_exc_msg(g_pkg_name, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
5240
5241 END revert_pending_changes;
5242
5243
5244 --------------------------------------------------------------------------------
5245 --Start of Comments
5246 --Name: check_revert_pending_changes
5247
5248 -- Function:
5249 -- Checks if there are any non-approved changes in the base tables
5250 -- and reverts it to sync teh base table with latest revision of archive.
5251 -- If the user inputs the rervert change flag as 'Y' and archive exists
5252 -- then reverts the non archived changes[only vital columns] in the base tables
5253 -- If user inputs the rervert change flag as 'Y' and archive doesnot exist
5254 -- then throw an error as Cannot revert Changes
5255 -- If User inputs the rervert change flag as 'N',then throw an error
5256 -- asking user to revert the changes before canceling the document
5257
5258 --Parameters:
5259 --IN:
5260 -- p_revert_chg_flag
5261 -- p_source
5262 -- p_key
5263 -- p_user_id
5264 -- p_login_id
5265 -- p_sequence ,
5266 -- p_online_report_id
5267
5268 --IN OUT:
5269
5270 -- OUT:
5271 -- x_return_status
5272 -- FND_API.G_RET_STS_SUCCESS if procedure succeeds
5273 -- FND_API.G_RET_STS_ERROR if procedure fails
5274 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
5275
5276 -- x_return_msg
5277
5278 --End of Comments
5279 --------------------------------------------------------------------------------
5280 PROCEDURE check_revert_pending_changes(
5281 p_revert_chg_flag IN VARCHAR2,
5282 p_online_report_id IN NUMBER,
5283 p_user_id IN po_lines.last_updated_by%TYPE,
5284 p_login_id IN po_lines.last_update_login%TYPE,
5285 p_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
5286 p_source IN VARCHAR2 DEFAULT NULL,
5287 p_low_level_key IN po_session_gt.key%TYPE,
5288 p_entity_level_key IN po_session_gt.key%TYPE,
5289 p_po_enc_flag IN FINANCIALS_SYSTEM_PARAMETERS.purch_encumbrance_flag%TYPE,
5290 x_return_status OUT NOCOPY VARCHAR2,
5291 x_return_msg OUT NOCOPY VARCHAR2)
5292 IS
5293
5294 d_api_name CONSTANT VARCHAR2(30) := 'check_revert_pending_changes.';
5295 d_module CONSTANT VARCHAR2(100) := g_pkg_name || d_api_name;
5296
5297 l_progress VARCHAR2(3) := '000' ;
5298 p_line_loc_id_tbl po_tbl_number;
5299 p_line_id_tbl po_tbl_number;
5300 l_line_token VARCHAR2(20);
5301 l_ship_token VARCHAR2(20);
5302 l_amt_token VARCHAR2(20);
5303 l_qty_token VARCHAR2(20);
5304 l_doc_token VARCHAR2(20);
5305 l_to_token VARCHAR2(20);
5306
5307
5308
5309 BEGIN
5310
5311 l_line_token := fnd_message.get_string('PO', 'PO_ZMVOR_LINE');
5312 l_ship_token := fnd_message.get_string('PO', 'PO_ZMVOR_SHIPMENT');
5313 l_amt_token := fnd_message.get_string('PO', 'PO_WF_NOTIF_AMOUNT');
5314 l_qty_token := fnd_message.get_string('PO', 'PO_WF_NOTIF_QUANTITY');
5315 l_doc_token := fnd_message.get_string('PO', 'PO_DOCUMENT_LABEL');
5316 l_to_token := fnd_message.get_string('PO', 'PO_WF_NOTIF_TO');
5317
5318
5319
5320
5321
5322 IF p_revert_chg_flag ='Y' THEN
5323
5324 INSERT INTO PO_ONLINE_REPORT_TEXT(
5325 ONLINE_REPORT_ID,
5326 LAST_UPDATE_LOGIN,
5327 LAST_UPDATED_BY,
5328 LAST_UPDATE_DATE,
5329 CREATED_BY,
5330 CREATION_DATE,
5331 LINE_NUM,
5332 SHIPMENT_NUM,
5333 DISTRIBUTION_NUM,
5334 SEQUENCE,
5335 TEXT_LINE,
5336 message_type,
5337 transaction_id,
5338 transaction_type)
5339 (SELECT
5340 p_online_report_id,
5341 p_login_id,
5342 p_user_id,
5343 SYSDATE,
5344 p_user_id,
5345 SYSDATE,
5346 POL.LINE_NUM,
5347 poll.SHIPMENT_NUM,
5348 0,
5349 p_sequence + ROWNUM,
5350 PO_CORE_S.get_translated_text(
5351 'PO_CANT_REVERT_PENDING_CHG',
5352 'DOC_LINE_SHIP_DIST_NUM',l_doc_token||gt.char6||','|| l_line_token||pol.LINE_NUM||','||l_ship_token||poll.SHIPMENT_NUM
5353 ),
5354 'E',
5355 gt.num1,
5356 gt.char3
5357 FROM
5358 po_distributions_all pod,
5359 po_line_locations poll,
5360 po_lines pol ,
5361 po_session_gt gt
5362 WHERE
5363 gt.key=p_low_level_key
5364 AND pod.line_location_id=poll.line_location_id
5365 AND poll.line_location_id = gt.index_num1 -- lowestentityid i.e.
5366 AND poll.po_line_id = pol.po_line_id
5367 AND gt.char1 <> po_document_cancel_pvt.c_doc_type_PA
5368 AND Nvl(poll.approved_flag,'N')<>'Y'
5369 -- <13503748: Edit without unreserve ER START>
5370 -- Throw an error if the encumbered flag at PO distributions is N for
5371 -- encumbered enabled environment
5372 AND (p_po_enc_flag = 'Y' AND pod.encumbered_flag = 'N')
5373 -- <13503748: Edit without unreserve ER END>
5374 AND (p_po_enc_flag ='Y'
5375 OR NOT EXISTS (SELECT 'exists archive'
5376 FROM po_distributions_archive_all
5377 WHERE po_distribution_id =pod.po_distribution_id)
5378 )
5379
5380 );
5381
5382 l_progress := '002';
5383
5384 -- Revert the pending changes on the docuemnt before proceeding for the
5385 -- Cancel action
5386 revert_pending_changes(
5387 p_api_version=> 1.0,
5388 p_init_msg_list=>FND_API.G_FALSE,
5389 p_revert_chg_flag => p_revert_chg_flag,
5390 p_online_report_id => p_online_report_id,
5391 p_user_id => p_user_id,
5392 p_login_id => p_login_id,
5393 p_key => p_entity_level_key ,
5394 x_return_status => x_return_status,
5395 x_msg_data => x_return_msg);
5396
5397
5398 IF (x_return_status = FND_API.g_ret_sts_error) THEN
5399 RAISE FND_API.g_exc_error;
5400 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
5401 RAISE FND_API.g_exc_unexpected_error;
5402 END IF;
5403
5404
5405
5406 ELSE
5407
5408 --Gt Table Columns Mapping
5409 --num1 - entity_id
5410 --char1 - document_type
5411 --char2 - document_subtype
5412 --char3 - entity_level
5413 --char4 - doc_id
5414 --char5 - process_entity_flag
5415 --date1 - entity_action_date
5416 --index_num1 - lowestentityid
5417 --lowestentityid :
5418 -- shipmentid in case of document type= PO/Releases and
5419 -- subtype= Planned/Standard at any entity level
5420 -- Lineid in case of BPA and GBPA at any entity level
5421 -- Headerid in case of CPA
5422
5423
5424 INSERT INTO PO_ONLINE_REPORT_TEXT(
5425 ONLINE_REPORT_ID,
5426 LAST_UPDATE_LOGIN,
5427 LAST_UPDATED_BY,
5428 LAST_UPDATE_DATE,
5429 CREATED_BY,
5430 CREATION_DATE,
5431 LINE_NUM,
5432 SHIPMENT_NUM,
5433 DISTRIBUTION_NUM,
5434 SEQUENCE,
5435 TEXT_LINE,
5436 message_type,
5437 transaction_id,
5438 transaction_type)
5439 (SELECT
5440 p_online_report_id,
5441 p_login_id,
5442 p_user_id,
5443 SYSDATE,
5444 p_user_id,
5445 SYSDATE,
5446 POL.LINE_NUM,
5447 poll.SHIPMENT_NUM,
5448 0,
5449 p_sequence + ROWNUM,
5450 PO_CORE_S.get_translated_text(
5451 'PO_CHANGED_CANT_CANCEL_ERR',
5452 'DOC_LINE_SHIP_DIST_NUM',l_doc_token||gt.char6||','|| l_line_token||pol.LINE_NUM||','||l_ship_token||poll.SHIPMENT_NUM,
5453 'PRICE_TOKEN', (SELECT price_override
5454 FROM po_line_locations_archive_all
5455 WHERE latest_external_flag ='Y'
5456 AND line_location_id =poll.line_location_id)||l_to_token || poll.price_override,
5457 'AMT_QTY_TOKEN', DECODE(poll.amount,NULL, 'AMOUNT',l_amt_token,l_qty_token),
5458 'QTY_AMT',Decode(poll.amount,NULL,
5459 (SELECT quantity
5460 FROM po_line_locations_archive_all
5461 WHERE latest_external_flag ='Y'
5462 AND line_location_id =poll.line_location_id) ||l_to_token || poll.quantity,
5463 (SELECT amount
5464 FROM po_line_locations_archive_all
5465 WHERE latest_external_flag ='Y'
5466 AND line_location_id =poll.line_location_id) ||l_to_token || poll.amount
5467 ),
5468 'NEED_BY_PRM_DATE', Decode(poll.promised_date,NULL,
5469 (SELECT need_by_date
5470 FROM po_line_locations_archive_all
5471 WHERE latest_external_flag ='Y'
5472 AND line_location_id =poll.line_location_id)||l_to_token||poll.need_by_date ,
5473 (SELECT promised_date
5474 FROM po_line_locations_archive_all
5475 WHERE latest_external_flag ='Y'
5476 AND line_location_id =poll.line_location_id)||l_to_token||poll.promised_date
5477
5478
5479
5480 )),
5481 'E',
5482 gt.num1,
5483 gt.char3
5484 FROM
5485 po_distributions_all pod,
5486 po_line_locations poll,
5487 po_lines pol ,
5488 po_session_gt gt
5489 WHERE
5490 gt.key=p_low_level_key
5491 AND pod.line_location_id=poll.line_location_id
5492 AND poll.line_location_id = gt.index_num1 -- lowestentityid i.e.
5493 AND poll.po_line_id = pol.po_line_id
5494 AND gt.char1 <> po_document_cancel_pvt.c_doc_type_PA
5495 AND ((NOT EXISTS (SELECT 'exists archive'
5496 FROM po_distributions_archive_all
5497 WHERE po_distribution_id =pod.po_distribution_id)
5498 AND Nvl(poll.approved_flag,'N')<>'Y')
5499 OR EXISTS (SELECT 'change exists'
5500 FROM po_line_locations_archive_all poall
5501 WHERE poall.line_location_id =poll.line_location_id
5502 AND poall.latest_external_flag ='Y'
5503 AND (Nvl(poll.price_override,0) <> Nvl(poall.price_override,0)
5504 OR Nvl(poll.quantity,0) <> Nvl(poall.quantity,0)
5505 OR Nvl(poll.amount,0) <> Nvl(poall.amount,0)
5506 OR Nvl(poll.promised_date,sysdate) <> Nvl(poall.promised_date,sysdate)
5507 OR Nvl(poll.need_by_date,sysdate) <> Nvl(poall.need_by_date,sysdate))
5508
5509 )
5510 )
5511 );
5512
5513 END IF;
5514
5515 IF g_debug_stmt THEN
5516 PO_DEBUG.debug_end(d_module);
5517 END IF ;
5518
5519
5520 EXCEPTION
5521 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5522 x_return_msg := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
5523 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5524
5525 WHEN FND_API.G_EXC_ERROR THEN
5526 x_return_msg := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
5527 x_return_status := FND_API.G_RET_STS_ERROR;
5528
5529 WHEN OTHERS THEN
5530 IF (G_DEBUG_UNEXP) THEN
5531 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
5532 d_module || '.OTHERS_EXCEPTION', 'EXCEPTION: LOCATION IS '
5533 || l_progress || ' SQL CODE IS '||sqlcode);
5534 END IF;
5535
5536 x_return_msg := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
5537 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5538
5539 END check_revert_pending_changes;
5540
5541 --------------------------------------------------------------------------------
5542 --Start of Comments
5543 --Name: check_cancel_reqs_flag
5544
5545 -- Function:
5546 -- Compares x_cancel_reqs_flag to the current OU's purchasing options.
5547 -- If the current OU option is Always cancel, then x_cancel_reqs_flag is set
5548 -- to 'Y'.
5549 -- If the current OU option is Never cancel, then x_cancel_reqs_flag is set
5550 -- to 'N'. Otherwise, x_cancel_reqs_flag is not modified.
5551 -- A warning message is appended to the API message list if the caller passed
5552 -- in a value for x_cancel_reqs_flag, and this was overwritten because it
5553 -- conflicted with the current OU's purchasing options.
5554 --
5555
5556 --Parameters:
5557 --IN:
5558 -- p_user_id
5559 -- p_login_id
5560 -- p_sequence ,
5561 -- p_online_report_id
5562 -- p_doc_type
5563 -- p_doc_id
5564 -- p_po_encumbrance_flag
5565 -- p_req_encumbrance_flag
5566
5567 --IN OUT:
5568
5569 -- OUT:
5570 -- x_return_status
5571 -- FND_API.G_RET_STS_SUCCESS if procedure succeeds
5572 -- FND_API.G_RET_STS_ERROR if procedure fails
5573 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
5574
5575 -- x_cancel_reqs_flag:
5576 -- A 'Y' or 'N' indicating that cancelling backing reqs when PO's are
5577 -- cancelled is desired.
5578
5579 -- x_msg_data
5580
5581 --End of Comments
5582 --------------------------------------------------------------------------------
5583
5584 PROCEDURE check_cancel_reqs_flag(
5585 x_return_status OUT NOCOPY VARCHAR2,
5586 x_msg_data OUT NOCOPY VARCHAR2,
5587 p_user_id IN po_lines.last_updated_by%TYPE,
5588 p_login_id IN po_lines.last_update_login%TYPE,
5589 p_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
5590 x_cancel_reqs_flag IN OUT NOCOPY VARCHAR2,
5591 p_online_report_id IN NUMBER,
5592 p_doc_type IN VARCHAR2,
5593 p_doc_id IN NUMBER,
5594 p_po_encumbrance_flag IN VARCHAR2,
5595 p_req_encumbrance_flag IN VARCHAR2)
5596 IS
5597
5598 d_api_name CONSTANT VARCHAR2(30) := 'check_cancel_reqs_flag.';
5599 d_module CONSTANT VARCHAR2(100) := g_pkg_name||d_api_name;
5600 l_cancel_reqs_sys_val
5601 PO_SYSTEM_PARAMETERS_ALL.cancel_reqs_on_po_cancel_flag%TYPE;
5602 l_show_warning BOOLEAN := FALSE;
5603 l_progress VARCHAR2(3);
5604 l_cancel_reqs_flag VARCHAR2(1);
5605
5606 BEGIN
5607 l_progress := '000';
5608
5609 IF g_debug_stmt THEN
5610 PO_DEBUG.debug_begin(d_module);
5611 PO_DEBUG.debug_var(d_module,l_progress,'p_sequence',p_sequence);
5612 PO_DEBUG.debug_var(d_module,l_progress,'x_cancel_reqs_flag',x_cancel_reqs_flag);
5613 PO_DEBUG.debug_var(d_module,l_progress,'p_online_report_id',p_online_report_id);
5614 PO_DEBUG.debug_var(d_module,l_progress,'p_doc_type',p_doc_type);
5615 PO_DEBUG.debug_var(d_module,l_progress,'p_doc_id',p_doc_id);
5616 PO_DEBUG.debug_var(d_module,l_progress,'p_po_encumbrance_flag',p_po_encumbrance_flag);
5617 PO_DEBUG.debug_var(d_module,l_progress,'p_req_encumbrance_flag',p_req_encumbrance_flag);
5618 PO_DEBUG.debug_var(d_module,l_progress,'l_show_warning',l_show_warning);
5619
5620 END IF;
5621
5622 x_return_status := FND_API.G_RET_STS_SUCCESS;
5623 l_cancel_reqs_flag:=x_cancel_reqs_flag;
5624 x_msg_data :=NULL;
5625
5626 l_progress := '001';
5627
5628 SELECT cancel_reqs_on_po_cancel_flag
5629 INTO l_cancel_reqs_sys_val
5630 FROM po_system_parameters;
5631
5632 IF g_debug_stmt THEN
5633 PO_DEBUG.debug_var(d_module,l_progress,'l_cancel_reqs_sys_val',l_cancel_reqs_sys_val);
5634 END IF;
5635
5636
5637 IF (l_cancel_reqs_sys_val = 'A' AND
5638 NVL(x_cancel_reqs_flag, 'X') <> 'Y') THEN
5639
5640 l_progress := '002';
5641 l_show_warning:=TRUE;
5642 x_cancel_reqs_flag := 'Y';
5643
5644 ELSIF(l_cancel_reqs_sys_val = 'N' AND
5645 NVL(x_cancel_reqs_flag, 'X') <> 'N') THEN
5646
5647 l_progress := '003';
5648 l_show_warning:=TRUE;
5649 x_cancel_reqs_flag := 'N';
5650 END IF;
5651
5652 IF g_debug_stmt THEN
5653 PO_DEBUG.debug_var(d_module,l_progress,'l_show_warning',l_show_warning);
5654 PO_DEBUG.debug_var(d_module,l_progress,'x_cancel_reqs_flag',x_cancel_reqs_flag);
5655 END IF;
5656
5657 IF l_show_warning AND x_cancel_reqs_flag IS NOT NULL THEN
5658 INSERT INTO po_online_report_text(
5659 ONLINE_REPORT_ID,
5660 LAST_UPDATE_LOGIN,
5661 LAST_UPDATED_BY,
5662 LAST_UPDATE_DATE,
5663 CREATED_BY,
5664 CREATION_DATE,
5665 LINE_NUM,
5666 SHIPMENT_NUM,
5667 DISTRIBUTION_NUM,
5668 SEQUENCE,
5669 TEXT_LINE,
5670 transaction_id,
5671 transaction_type) VALUES
5672 (p_online_report_id,
5673 p_login_id,
5674 p_user_id,
5675 SYSDATE,
5676 p_user_id,
5677 SYSDATE,
5678 0,
5679 0,
5680 0,
5681 p_sequence + 1,
5682 PO_CORE_S.get_translated_text('PO_INVALID_CANCEL_REQS_FLAG',
5683 'USER_VALUE',l_cancel_reqs_flag,
5684 'SYSTEM_VALUE',l_cancel_reqs_sys_val),
5685
5686 0,
5687 0
5688 );
5689
5690 p_sequence :=p_sequence+1;
5691 END IF;
5692
5693
5694 IF (x_cancel_reqs_flag ='Y'
5695 AND p_po_encumbrance_flag = 'Y'
5696 AND p_req_encumbrance_flag='Y')
5697 THEN
5698 PO_Document_Cancel_PVT.val_cancel_backing_reqs(
5699 p_api_version => 1.0,
5700 p_init_msg_list => FND_API.G_FALSE,
5701 x_return_status => x_return_status,
5702 p_doc_type => p_doc_type,
5703 p_doc_id => p_doc_id );
5704
5705 IF (x_return_status = FND_API.g_ret_sts_error) THEN
5706 -- Cannot cancel backing reqs, so reset to 'N'
5707 x_cancel_reqs_flag := 'N';
5708 x_return_status := FND_API.g_ret_sts_success;
5709
5710 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
5711 RAISE FND_API.g_exc_unexpected_error;
5712 END IF;
5713 END IF;
5714
5715 IF g_debug_stmt THEN
5716 PO_DEBUG.debug_end(d_module);
5717 END IF ;
5718
5719 EXCEPTION
5720 WHEN NO_DATA_FOUND THEN
5721 NULL;
5722
5723 WHEN OTHERS THEN
5724 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5725
5726 IF (G_DEBUG_UNEXP) THEN
5727 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
5728 d_module || '.OTHERS_EXCEPTION', 'EXCEPTION: LOCATION IS '
5729 || l_progress || ' SQL CODE IS '||sqlcode);
5730 END IF;
5731
5732 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
5733
5734
5735 END check_cancel_reqs_flag;
5736
5737 --------------------------------------------------------------------------------
5738 --Start of Comments
5739 --Name: validate_doc_params
5740
5741 -- Function:1.Validates the entity id against the entity level :The entity id
5742 -- should be a valid id at that level.
5743 -- - If the entity level is SHIPMENT, then the entity_id should a
5744 -- valid line_location_id in po_line_locations
5745 -- - If the entity level is LINE, then the entity_id should a
5746 -- valid po_line_id in po_lines
5747 -- - If the entity level is HEADER, then the entity_id should a
5748 -- valid po_header_id/po_release_id in po_headers/po_releases
5749 -- 2. Validate the parameter doc_id
5750 -- - doc_id should always be po_header_id
5751 -- 3. Validate doc_type and doc_subtype combination
5752 -- ex: doc_type=PO and doc_subtype=STANADARD/PLANNED is valid
5753 -- but doc_type=PO and doc_subtype=CONTRACT/BLANKET is invalid
5754 -- If any of the above validation fails, it inserts the error in
5755 -- po-onlie_report_text
5756 --
5757
5758 --Parameters:
5759 --IN:
5760 -- p_entity_rec_tbl
5761 -- p_online_report_id
5762 -- p_key
5763 -- p_user_id
5764 -- p_login_id
5765 -- p_sequence ,
5766
5767 --IN OUT:
5768
5769 -- OUT:
5770 -- x_return_status
5771 -- FND_API.G_RET_STS_SUCCESS if procedure succeeds
5772 -- FND_API.G_RET_STS_ERROR if procedure fails
5773 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
5774
5775 -- x_return_msg
5776
5777 --End of Comments
5778 --------------------------------------------------------------------------------
5779
5780 PROCEDURE validate_doc_params(
5781 p_entity_rec_tbl IN po_document_action_pvt.entity_dtl_rec_type_tbl,
5782 p_online_report_id IN NUMBER,
5783 p_key IN po_session_gt.key%TYPE,
5784 p_user_id IN po_lines.last_updated_by%TYPE,
5785 p_login_id IN po_lines.last_update_login%TYPE,
5786 p_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
5787 x_return_status OUT NOCOPY VARCHAR2,
5788 x_return_msg OUT NOCOPY VARCHAR2)
5789 IS
5790
5791 d_api_name CONSTANT VARCHAR2(30) := 'validate_doc_params.';
5792 d_module CONSTANT VARCHAR2(100) := g_pkg_name||d_api_name;
5793
5794 l_progress VARCHAR2(3) := '000';
5795 l_org_count NUMBER;
5796
5797
5798 BEGIN
5799
5800 x_return_status := FND_API.G_RET_STS_SUCCESS;
5801 x_return_msg :=NULL;
5802 l_org_count :=0;
5803
5804
5805 IF g_debug_stmt THEN
5806 PO_DEBUG.debug_begin(d_module);
5807 PO_DEBUG.debug_var(d_module,l_progress,'p_key',p_key);
5808 PO_DEBUG.debug_var(d_module,l_progress,'p_user_id',p_user_id);
5809 PO_DEBUG.debug_var(d_module,l_progress,'p_login_id',p_login_id);
5810 PO_DEBUG.debug_var(d_module,l_progress,'p_online_report_id',p_online_report_id);
5811 END IF;
5812
5813 l_progress := '001';
5814
5815 -- Validate doc_type and doc_subtype combination
5816 INSERT INTO PO_ONLINE_REPORT_TEXT(
5817 ONLINE_REPORT_ID,
5818 LAST_UPDATE_LOGIN,
5819 LAST_UPDATED_BY,
5820 LAST_UPDATE_DATE,
5821 CREATED_BY,
5822 CREATION_DATE,
5823 LINE_NUM,
5824 SHIPMENT_NUM,
5825 DISTRIBUTION_NUM,
5826 SEQUENCE,
5827 TEXT_LINE,
5828 transaction_id,
5829 transaction_type)
5830 (SELECT
5831 p_online_report_id,
5832 p_login_id,
5833 p_user_id,
5834 SYSDATE,
5835 p_user_id,
5836 SYSDATE,
5837 0,
5838 0,
5839 0,
5840 p_sequence + ROWNUM,
5841 PO_CORE_S.get_translated_text
5842 ('PO_INVALID_DOC_TYPE_SUBTYPE'
5843 , 'TYPE', gt.char1
5844 , 'SUBTYPE', gt.char2
5845 ),
5846 gt.num1,
5847 gt.char3
5848 FROM
5849 po_session_gt gt
5850 WHERE gt.key=p_key
5851 AND (gt.char1 IS NULL
5852 OR gt.char2 IS NULL
5853 OR gt.char1 NOT IN (po_document_cancel_pvt.c_doc_type_PO,
5854 po_document_cancel_pvt.c_doc_type_PA,
5855 po_document_cancel_pvt.c_doc_type_RELEASE)
5856 OR (gt.char1=po_document_cancel_pvt.c_doc_type_PO
5857 AND gt.char2 NOT IN(po_document_cancel_pvt.c_doc_subtype_STANDARD,
5858 po_document_cancel_pvt.c_doc_subtype_PLANNED))
5859 OR (gt.char1=po_document_cancel_pvt.c_doc_type_PA
5860 AND gt.char2 NOT IN (po_document_cancel_pvt.c_doc_subtype_BLANKET,
5861 po_document_cancel_pvt.c_doc_subtype_contract))
5862 OR (gt.char1=po_document_cancel_pvt.c_doc_type_RELEASE
5863 AND gt.char2 NOT IN (po_document_cancel_pvt.c_doc_subtype_BLANKET,
5864 po_document_cancel_pvt.c_doc_subtype_SCHEDULED))
5865 ));
5866
5867 p_sequence := P_SEQUENCE + SQL%ROWCOUNT;
5868 l_progress := '002';
5869
5870 -- Validate : If the entity level is SHIPMENT,
5871 -- then the entity_id should a valid line_location_id in po_line_locations
5872 INSERT INTO PO_ONLINE_REPORT_TEXT(
5873 ONLINE_REPORT_ID,
5874 LAST_UPDATE_LOGIN,
5875 LAST_UPDATED_BY,
5876 LAST_UPDATE_DATE,
5877 CREATED_BY,
5878 CREATION_DATE,
5879 LINE_NUM,
5880 SHIPMENT_NUM,
5881 DISTRIBUTION_NUM,
5882 SEQUENCE,
5883 TEXT_LINE,
5884 transaction_id,
5885 transaction_type)
5886 (SELECT
5887 p_online_report_id,
5888 p_login_id,
5889 p_user_id,
5890 SYSDATE,
5891 p_user_id,
5892 SYSDATE,
5893 0,
5894 0,
5895 0,
5896 p_sequence + ROWNUM,
5897 PO_CORE_S.get_translated_text
5898 ('PO_INVALID_DOC_IDS',
5899 'DOC_ID',
5900 gt.num1),
5901 gt.num1,
5902 gt.char3
5903 FROM
5904 po_session_gt gt
5905 WHERE gt.key=p_key
5906 AND gt.char3 =po_document_cancel_pvt.c_entity_level_SHIPMENT
5907 AND NOT EXISTS (SELECT '1'
5908 FROM po_line_locations poll
5909 WHERE poll.line_location_id = gt.num1
5910 ));
5911
5912
5913 p_sequence := P_SEQUENCE + SQL%ROWCOUNT;
5914 l_progress := '003';
5915
5916 -- Validate : If the entity level is LINE,
5917 -- then the entity_id should a valid po_line_id in po_lines
5918 INSERT INTO PO_ONLINE_REPORT_TEXT(
5919 ONLINE_REPORT_ID,
5920 LAST_UPDATE_LOGIN,
5921 LAST_UPDATED_BY,
5922 LAST_UPDATE_DATE,
5923 CREATED_BY,
5924 CREATION_DATE,
5925 LINE_NUM,
5926 SHIPMENT_NUM,
5927 DISTRIBUTION_NUM,
5928 SEQUENCE,
5929 TEXT_LINE,
5930 transaction_id,
5931 transaction_type)
5932 (SELECT
5933 p_online_report_id,
5934 p_login_id,
5935 p_user_id,
5936 SYSDATE,
5937 p_user_id,
5938 SYSDATE,
5939 0,
5940 0,
5941 0,
5942 p_sequence + ROWNUM,
5943 PO_CORE_S.get_translated_text
5944 ('PO_INVALID_DOC_IDS',
5945 'DOC_ID',
5946 gt.num1),
5947 gt.num1,
5948 gt.char3
5949 FROM
5950 po_session_gt gt
5951 WHERE gt.key=p_key
5952 AND gt.char3 =po_document_cancel_pvt.c_entity_level_LINE
5953 AND NOT EXISTS(SELECT '1'
5954 FROM po_lines pol
5955 WHERE pol.po_line_id = gt.num1
5956 ));
5957
5958
5959
5960 p_sequence := P_SEQUENCE + SQL%ROWCOUNT;
5961 l_progress := '004';
5962
5963 -- Validate : If the entity level is HEADER and docuemnt type is PO/PA,
5964 -- then the entity_id should a valid po_header_id in po_headers
5965 INSERT INTO PO_ONLINE_REPORT_TEXT(
5966 ONLINE_REPORT_ID,
5967 LAST_UPDATE_LOGIN,
5968 LAST_UPDATED_BY,
5969 LAST_UPDATE_DATE,
5970 CREATED_BY,
5971 CREATION_DATE,
5972 LINE_NUM,
5973 SHIPMENT_NUM,
5974 DISTRIBUTION_NUM,
5975 SEQUENCE,
5976 TEXT_LINE,
5977 transaction_id,
5978 transaction_type)
5979 (SELECT p_online_report_id,
5980 p_login_id,
5981 p_user_id,
5982 SYSDATE,
5983 p_user_id,
5984 SYSDATE,
5985 0,
5986 0,
5987 0,
5988 p_sequence + ROWNUM,
5989 PO_CORE_S.get_translated_text
5990 ('PO_INVALID_DOC_IDS',
5991 'DOC_ID',
5992 gt.num1),
5993 gt.num1,
5994 gt.char3
5995 FROM
5996 po_session_gt gt
5997 WHERE gt.key=p_key
5998 AND gt.char3 =po_document_cancel_pvt.c_entity_level_HEADER
5999 AND gt.char1<>po_document_cancel_pvt.c_doc_type_RELEASE
6000 AND NOT EXISTS (SELECT '1'
6001 FROM po_headers poh
6002 WHERE poh.po_header_id = gt.num1
6003 ));
6004
6005 p_sequence := P_SEQUENCE + SQL%ROWCOUNT;
6006 l_progress := '005';
6007
6008 -- Validate : If the entity level is HEADER and docuemnt type is RELEASE,
6009 -- then the entity_id should a valid po_release_id in po_releases
6010 INSERT INTO PO_ONLINE_REPORT_TEXT(
6011 ONLINE_REPORT_ID,
6012 LAST_UPDATE_LOGIN,
6013 LAST_UPDATED_BY,
6014 LAST_UPDATE_DATE,
6015 CREATED_BY,
6016 CREATION_DATE,
6017 LINE_NUM,
6018 SHIPMENT_NUM,
6019 DISTRIBUTION_NUM,
6020 SEQUENCE,
6021 TEXT_LINE,
6022 transaction_id,
6023 transaction_type)
6024 (SELECT
6025 p_online_report_id,
6026 p_login_id,
6027 p_user_id,
6028 SYSDATE,
6029 p_user_id,
6030 SYSDATE,
6031 0,
6032 0,
6033 0,
6034 p_sequence + ROWNUM,
6035 PO_CORE_S.get_translated_text
6036 ('PO_INVALID_DOC_IDS',
6037 'DOC_ID',
6038 gt.num1),
6039 gt.num1,
6040 gt.char3
6041 FROM
6042 po_session_gt gt
6043 WHERE gt.key=p_key
6044 AND gt.char3 =po_document_cancel_pvt.c_entity_level_HEADER
6045 AND gt.char1 =po_document_cancel_pvt.c_doc_type_RELEASE
6046 AND NOT EXISTS (SELECT '1'
6047 FROM po_releases poh
6048 WHERE poh.po_release_id = gt.num1 ));
6049
6050
6051
6052 p_sequence := P_SEQUENCE + SQL%ROWCOUNT;
6053 l_progress := '006';
6054
6055 -- Validate :doc_id parametr should be a valid po_header_id in po_headers
6056 -- or valid po_release_id in po_releases
6057
6058 INSERT INTO PO_ONLINE_REPORT_TEXT(
6059 ONLINE_REPORT_ID,
6060 LAST_UPDATE_LOGIN,
6061 LAST_UPDATED_BY,
6062 LAST_UPDATE_DATE,
6063 CREATED_BY,
6064 CREATION_DATE,
6065 LINE_NUM,
6066 SHIPMENT_NUM,
6067 DISTRIBUTION_NUM,
6068 SEQUENCE,
6069 TEXT_LINE,
6070 transaction_id,
6071 transaction_type)
6072 (SELECT
6073 p_online_report_id,
6074 p_login_id,
6075 p_user_id,
6076 SYSDATE,
6077 p_user_id,
6078 SYSDATE,
6079 0,
6080 0,
6081 0,
6082 p_sequence + ROWNUM,
6083 PO_CORE_S.get_translated_text
6084 ('PO_INVALID_DOC_IDS',
6085 'DOC_ID',
6086 gt.char4),
6087 gt.num1,
6088 gt.char3
6089 FROM
6090 po_session_gt gt
6091 WHERE gt.key=p_key
6092 AND GT.char3 <> PO_Document_Cancel_PVT.c_entity_level_HEADER
6093 AND NOT EXISTS (SELECT '1'
6094 FROM po_headers poh
6095 WHERE poh.po_header_id = gt.char4
6096 UNION ALL
6097 SELECT '1'
6098 FROM po_releases prh
6099 WHERE prh.po_release_id = gt.char4));--validate doc_id
6100
6101
6102 p_sequence := P_SEQUENCE + SQL%ROWCOUNT;
6103 l_progress := '006';
6104
6105
6106 -- Validate : All documents should belong to same OU
6107 SELECT Count(DISTINCT OPERATING_UNIT)
6108 INTO l_org_count
6109 FROM po_headers,
6110 org_organization_definitions ood
6111 WHERE ood.organization_id=org_id
6112 AND po_header_id IN
6113 (SELECT pol.po_header_id
6114 FROM po_lines pol,
6115 po_session_gt gt
6116 WHERE gt.KEY=p_key
6117 AND gt.num1=pol.po_line_id
6118 AND gt.char3=PO_Document_Cancel_PVT.c_entity_level_LINE
6119 UNION ALL
6120 SELECT poll.po_header_id
6121 FROM po_line_locations poll,
6122 po_session_gt gt
6123 WHERE gt.KEY=p_key
6124 AND gt.num1=poll.line_location_id
6125 AND gt.char3=PO_Document_Cancel_PVT.c_entity_level_SHIPMENT
6126 UNION ALL
6127 SELECT gt.num1
6128 FROM po_session_gt gt
6129 WHERE gt.KEY=p_key
6130 AND gt.char3=PO_Document_Cancel_PVT.c_entity_level_HEADER
6131 ) ;
6132
6133 IF l_org_count>1 THEN
6134
6135 INSERT INTO PO_ONLINE_REPORT_TEXT(
6136 ONLINE_REPORT_ID,
6137 LAST_UPDATE_LOGIN,
6138 LAST_UPDATED_BY,
6139 LAST_UPDATE_DATE,
6140 CREATED_BY,
6141 CREATION_DATE,
6142 LINE_NUM,
6143 SHIPMENT_NUM,
6144 DISTRIBUTION_NUM,
6145 SEQUENCE,
6146 TEXT_LINE,
6147 transaction_id,
6148 transaction_type)
6149 VALUES
6150 (p_online_report_id,
6151 p_login_id,
6152 p_user_id,
6153 SYSDATE,
6154 p_user_id,
6155 SYSDATE,
6156 0,
6157 0,
6158 0,
6159 p_sequence + 1,
6160 PO_CORE_S.get_translated_text('PO_CAN_DIFF_OU_DOCS'),
6161 0,
6162 0
6163 );
6164
6165 END IF;
6166
6167 p_sequence := P_SEQUENCE + SQL%ROWCOUNT;
6168
6169 IF g_debug_stmt THEN
6170 PO_DEBUG.debug_end(d_module);
6171 END IF ;
6172
6173 EXCEPTION
6174 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6175 x_return_msg := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
6176 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6177
6178 WHEN FND_API.G_EXC_ERROR THEN
6179 x_return_msg := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
6180 x_return_status := FND_API.G_RET_STS_ERROR;
6181
6182 WHEN OTHERS THEN
6183 IF (G_DEBUG_UNEXP) THEN
6184 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
6185 d_module || '.OTHERS_EXCEPTION', 'EXCEPTION: LOCATION IS '
6186 || l_progress || ' SQL CODE IS '||sqlcode);
6187 END IF;
6188
6189 x_return_msg := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,P_ENCODED => 'F');
6190 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6191
6192 END validate_doc_params;
6193
6194
6195 --------------------------------------------------------------------------------
6196 --Start of Comments
6197 --Name: validate_cancel_action_params
6198
6199 -- Function:1.Validated the entity id against the entity level :The entity id
6200 -- should be a valid id at that level.
6201 -- - If the entity level is SHIPMENT, then the entity_id should a
6202 -- valid line_location_id in po_line_locations
6203 -- - If the entity level is LINE, then the entity_id should a valid
6204 -- po_line_id in po_lines
6205 -- - If the entity level is HEADER, then the entity_id should a
6206 -- valid po_header_id/po_release_id in po_headers/po_releases
6207 -- 2. Validate the parameter doc_id
6208 -- - doc_id should always be po_header_id
6209 -- 3. Validate doc_type and doc_subtype combination
6210 -- ex: doc_type=PO and doc_subtype=STANADARD/PLANNED is valid
6211 -- but doc_type=PO and doc_subtype=CONTRACT/BLANKET is invalid
6212 -- 4. Valdiates the action_date gainst the open GL period in enc is
6213 -- enabled/cbc accounting date if cbc is enabled
6214 -- 5. Validate the Cancel reqs falg against the Purchasing Option OU
6215 -- If any of the above validation fails, it inserts the error in
6216 -- po-onlie_report_text
6217 --
6218
6219 --Parameters:
6220 --IN:
6221 -- p_da_call_rec
6222 -- p_online_report_id
6223 -- p_key
6224 -- p_user_id
6225 -- p_login_id
6226 -- p_po_enc_flag
6227 -- p_req_enc_flag
6228 -- p_sequence
6229
6230
6231 --IN OUT:
6232
6233 -- OUT:
6234 -- x_return_status
6235 -- FND_API.G_RET_STS_SUCCESS if procedure succeeds
6236 -- FND_API.G_RET_STS_ERROR if procedure fails
6237 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
6238
6239 -- x_return_msg
6240
6241 --End of Comments
6242 --------------------------------------------------------------------------------
6243
6244
6245 PROCEDURE validate_cancel_action_params(
6246 p_da_call_rec IN OUT NOCOPY po_document_action_pvt.DOC_ACTION_CALL_TBL_REC_TYPE,
6247 p_online_report_id IN NUMBER,
6248 p_key IN po_session_gt.key%TYPE,
6249 p_user_id IN po_lines.last_updated_by%TYPE,
6250 p_login_id IN po_lines.last_update_login%TYPE,
6251 p_po_enc_flag IN FINANCIALS_SYSTEM_PARAMETERS.purch_encumbrance_flag%TYPE,
6252 p_req_enc_flag IN FINANCIALS_SYSTEM_PARAMETERS.req_encumbrance_flag%TYPE,
6253 p_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
6254 x_return_status OUT NOCOPY VARCHAR2,
6255 x_return_msg OUT NOCOPY VARCHAR2)
6256
6257
6258 IS
6259
6260 d_api_name CONSTANT VARCHAR2(30) := 'validate_cancel_action_params.';
6261 d_module CONSTANT VARCHAR2(100) := g_pkg_name||d_api_name;
6262
6263 l_progress VARCHAR2(3) := '000';
6264
6265 l_entity_rec_tbl po_document_action_pvt.entity_dtl_rec_type_tbl;
6266 l_cbc_enabled VARCHAR2(1);
6267 l_action_date DATE;
6268 id_count NUMBER :=0;
6269 l_doc_id_tbl po_tbl_number :=PO_TBL_NUMBER();
6270 l_source VARCHAR2(50);
6271
6272
6273 BEGIN
6274
6275 IF g_debug_stmt THEN
6276 PO_DEBUG.debug_begin(d_module);
6277 PO_DEBUG.debug_var(d_module,l_progress,'p_key',p_key);
6278 PO_DEBUG.debug_var(d_module,l_progress,'p_online_report_id',p_online_report_id);
6279 PO_DEBUG.debug_var(d_module,l_progress,'p_req_enc_flag',p_req_enc_flag);
6280 PO_DEBUG.debug_var(d_module,l_progress,'p_po_enc_flag',p_po_enc_flag);
6281 END IF;
6282
6283 l_progress := '001';
6284
6285 x_return_status := FND_API.G_RET_STS_SUCCESS;
6286 x_return_msg:=NULL;
6287
6288 l_entity_rec_tbl :=p_da_call_rec.entity_dtl_record_tbl;
6289 l_action_date :=p_da_call_rec.action_date;
6290 l_source := p_da_call_rec.caller;
6291
6292 l_progress := '002';
6293
6294 IF g_debug_stmt THEN
6295 PO_DEBUG.debug_var(d_module,l_progress,'l_action_date',l_action_date);
6296 END IF;
6297
6298 l_progress := '003';
6299
6300 --validate entity_id,doc_id, doc_type, doc_subtype
6301 validate_doc_params(p_entity_rec_tbl => l_entity_rec_tbl,
6302 p_online_report_id =>p_online_report_id,
6303 p_key =>p_key,
6304 p_user_id =>p_user_id,
6305 p_login_id => p_login_id,
6306 p_sequence =>p_sequence,
6307 x_return_status => x_return_status,
6308 x_return_msg =>x_return_msg );
6309
6310 IF (x_return_status = FND_API.g_ret_sts_error) THEN
6311 RAISE FND_API.g_exc_error;
6312 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
6313 RAISE FND_API.g_exc_unexpected_error;
6314 END IF;
6315
6316 l_progress := '004';
6317
6318 -- for each entity id in the entity record table
6319 FOR i IN 1..l_entity_rec_tbl.Count LOOP
6320 -- Action date is validated at document level
6321 -- (i.e.doc_type,doc_subtype and doc_id).There can be entities belonging
6322 -- to same doc_id[ ex: 2 shipemnts from same PO],
6323 -- hence, checking if the action_date is already validated for that
6324 IF NOT l_doc_id_tbl.EXISTS(l_entity_rec_tbl(i).doc_id) THEN
6325
6326 IF nvl(l_source,'NULL') NOT IN (PO_DOCUMENT_CANCEL_PVT.c_HTML_CONTROL_ACTION,
6327 PO_DOCUMENT_CANCEL_PVT.c_FORM_CONTROL_ACTION)
6328 THEN
6329
6330 -- Initialize the action date
6331 -- If x_action_date is NULL, then sets it to a valid CBC accounting
6332 -- date if CBC is enabled. Otherwise, sets it to the current system date.
6333 PO_DOCUMENT_CONTROL_PVT.init_action_date(
6334 p_api_version => 1.0,
6335 p_init_msg_list => FND_API.G_FALSE,
6336 x_return_status => x_return_status,
6337 p_doc_type => l_entity_rec_tbl(i).document_type,
6338 p_doc_subtype => l_entity_rec_tbl(i).document_subtype,
6339 p_doc_id => l_entity_rec_tbl(i).doc_id,
6340 x_action_date => l_action_date,
6341 x_cbc_enabled => l_cbc_enabled);
6342
6343
6344 IF (x_return_status = FND_API.g_ret_sts_error) THEN
6345 RAISE FND_API.g_exc_error;
6346 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
6347 RAISE FND_API.g_exc_unexpected_error;
6348 END IF;
6349
6350 END IF; --nvl(l_source,'NULL') NOT IN (PO_DOCUMENT_CANCEL_PVT.c_HTML_CONTROL_ACTION,PO_DOCUMENT_CANCEL_PVT.c_FORM_CONTROL_ACTION)
6351
6352 l_progress := '005';
6353
6354 IF g_debug_stmt THEN
6355 PO_DEBUG.debug_var(d_module,l_progress,'l_action_date',l_action_date);
6356 PO_DEBUG.debug_var(d_module,l_progress,'l_cbc_enabled',l_cbc_enabled);
6357 END IF;
6358
6359
6360 -- Validate the action date
6361 -- If encumbrance is on, checks that l_action_date lies in an open GL period
6362 -- Also checks that action_date is a valid CBC accounting date if cbc is enabled
6363 PO_DOCUMENT_CONTROL_PVT.val_action_date(
6364 p_api_version => 1.0,
6365 p_init_msg_list => FND_API.G_FALSE,
6366 x_return_status => x_return_status,
6367 p_doc_type => l_entity_rec_tbl(i).document_type,
6368 p_doc_subtype => l_entity_rec_tbl(i).document_subtype,
6369 p_doc_id => l_entity_rec_tbl(i).doc_id,
6370 p_action => 'CANCEL',
6371 p_action_date => l_action_date,
6372 p_cbc_enabled => l_cbc_enabled,
6373 p_po_encumbrance_flag => p_po_enc_flag,
6374 p_req_encumbrance_flag => p_req_enc_flag );
6375
6376 l_progress := '006';
6377
6378
6379 IF g_debug_stmt THEN
6380 PO_DEBUG.debug_var(d_module,l_progress,'x_return_status',x_return_status);
6381 END IF;
6382
6383 -- If the valdation fails then insert error into online report text
6384 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
6385
6386 INSERT INTO po_online_report_text(
6387 ONLINE_REPORT_ID,
6388 LAST_UPDATE_LOGIN,
6389 LAST_UPDATED_BY,
6390 LAST_UPDATE_DATE,
6391 CREATED_BY,
6392 CREATION_DATE,
6393 LINE_NUM,
6394 SHIPMENT_NUM,
6395 DISTRIBUTION_NUM,
6396 SEQUENCE,
6397 TEXT_LINE,
6398 transaction_id,
6399 transaction_type)
6400 (SELECT
6401 p_online_report_id,
6402 p_login_id,
6403 p_user_id,
6404 SYSDATE,
6405 p_user_id,
6406 SYSDATE,
6407 0,
6408 0,
6409 0,
6410 p_sequence + ROWNUM,
6411 PO_CORE_S.get_translated_text('PO_ACTION_DATE_INVALID',
6412 'DOC_NUM',
6413 gt.char6,
6414 'ACTION_DATE',
6415 l_action_date),
6416 gt.num1,
6417 gt.char3
6418 FROM
6419 po_session_gt gt
6420 WHERE gt.key=p_key
6421 AND gt.char4 =l_entity_rec_tbl(i).doc_id
6422 );
6423
6424 p_sequence := P_SEQUENCE + SQL%ROWCOUNT;
6425
6426 END IF;
6427
6428 l_progress := '007';
6429
6430 l_doc_id_tbl.extend;
6431 id_count:=id_count+1;
6432 l_doc_id_tbl(id_count):=l_entity_rec_tbl(i).doc_id;
6433
6434 UPDATE po_session_gt
6435 SET date1= l_action_date
6436 WHERE KEY=p_key
6437 AND char4= l_entity_rec_tbl(i).doc_id;
6438
6439 IF g_debug_stmt THEN
6440 PO_DEBUG.debug_var(d_module,l_progress,'update row count',SQL%ROWCOUNT);
6441 END IF;
6442
6443 l_progress := '008';
6444
6445 -- Validate CancelReqs flag with the current OU's purchasing options.
6446 check_cancel_reqs_flag(
6447 p_online_report_id =>p_online_report_id,
6448 x_cancel_reqs_flag =>p_da_call_rec.cancel_reqs_flag,
6449 p_doc_type => l_entity_rec_tbl(i).document_type,
6450 p_doc_id => l_entity_rec_tbl(i).doc_id,
6451 p_user_id =>p_user_id,
6452 p_login_id => p_login_id,
6453 p_sequence =>p_sequence,
6454 p_po_encumbrance_flag => p_po_enc_flag,
6455 p_req_encumbrance_flag => p_req_enc_flag,
6456 x_return_status => x_return_status,
6457 x_msg_data =>x_return_msg );
6458
6459
6460
6461 END IF;
6462 END LOOP;
6463
6464
6465
6466 IF g_debug_stmt THEN
6467 PO_DEBUG.debug_end(d_module);
6468 END IF ;
6469
6470 EXCEPTION
6471 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6472 x_return_msg := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,
6473 P_ENCODED => 'F');
6474 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6475
6476 WHEN FND_API.G_EXC_ERROR THEN
6477 x_return_msg := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,
6478 P_ENCODED => 'F');
6479 x_return_status := FND_API.G_RET_STS_ERROR;
6480
6481 WHEN OTHERS THEN
6482 IF (G_DEBUG_UNEXP) THEN
6483 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
6484 d_module || '.OTHERS_EXCEPTION', 'EXCEPTION: LOCATION IS '
6485 || l_progress || ' SQL CODE IS '||sqlcode);
6486 END IF;
6487
6488
6489 x_return_msg := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,
6490 P_ENCODED => 'F');
6491 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6492
6493
6494 END validate_cancel_action_params;
6495
6496
6497 --------------------------------------------------------------------------------
6498 --Start of Comments
6499 --Name: mark_errored_record
6500
6501 -- Modifies:process_entity_flag for each entity being canceled in the entity
6502 -- record which will be used to identify records eligible for futher
6503 -- processing
6504 --
6505 --Effects:1.Updates the process entity flag=N if for that entity id there is
6506 -- an entry in po_online_report_text table
6507 -- 2.Deletes the data from session gt table, which was used for validations
6508 --
6509
6510 --Parameters:
6511 --IN:
6512 -- p_da_call_rec
6513 -- p_key
6514 -- p_online_report_id
6515 -- p_entity_rec_tbl
6516
6517 --IN OUT:
6518
6519 -- OUT:
6520 -- x_return_status
6521 -- FND_API.G_RET_STS_SUCCESS if procedure succeeds
6522 -- FND_API.G_RET_STS_ERROR if procedure fails
6523 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
6524
6525 -- x_return_msg
6526
6527 --End of Comments
6528 --------------------------------------------------------------------------------
6529
6530 PROCEDURE mark_errored_record(
6531 p_key IN po_session_gt.key%TYPE,
6532 p_online_report_id IN NUMBER,
6533 p_entity_rec_tbl IN OUT NOCOPY po_document_action_pvt.entity_dtl_rec_type_tbl,
6534 x_return_status OUT NOCOPY VARCHAR2,
6535 x_return_msg OUT NOCOPY VARCHAR2,
6536 x_return_code OUT NOCOPY VARCHAR2)
6537
6538
6539 IS
6540
6541 d_api_name CONSTANT VARCHAR2(30) := 'mark_errored_record.';
6542 d_module CONSTANT VARCHAR2(100) := g_pkg_name || d_api_name;
6543 l_progress VARCHAR2(3) := '000' ;
6544
6545 l_count NUMBER;
6546
6547 BEGIN
6548
6549
6550 IF g_debug_stmt THEN
6551 PO_DEBUG.debug_begin(d_module);
6552 PO_DEBUG.debug_var(d_module,l_progress,'p_key',p_key);
6553 PO_DEBUG.debug_var(d_module,l_progress,'p_online_report_id',p_online_report_id);
6554 END IF;
6555
6556 l_progress := '001';
6557
6558 x_return_status := FND_API.G_RET_STS_SUCCESS;
6559 x_return_msg:=NULL;
6560 x_return_code :='S';
6561
6562
6563 -- Upadate char5[column corresponding to process_entity_flag in entity record
6564 -- table] in po_session_gt to 'N'
6565 -- If there is an entry in po_online_report_text for the entity_id and
6566 -- entity_level combination
6567 -- Columns mapping :
6568 -- entity_id in entity record = num1 in session gt = transaction_id in
6569 -- po_online_report_text
6570 -- entity_level in entity record = char3 in session gt = transaction_type in
6571 -- po_online_report_text
6572 -- process_entity_flag in entity record char5 in session gt
6573
6574
6575 UPDATE po_session_gt
6576 SET char5 ='N'
6577 WHERE KEY=p_key
6578 AND EXISTS(SELECT 'error record exists'
6579 FROM po_online_report_text
6580 WHERE transaction_id=num1
6581 AND transaction_type=char3
6582 AND Nvl(message_type,'E') = 'E'
6583 AND online_report_id =p_online_report_id);
6584
6585
6586 l_count :=SQL%ROWCOUNT;
6587
6588 IF g_debug_stmt THEN
6589 PO_DEBUG.debug_var(d_module,l_progress,'records updated in po_session_gt',l_count);
6590 END IF;
6591
6592 IF l_count>0 THEN
6593 x_return_code :='F';
6594 END IF;
6595 IF g_debug_stmt THEN
6596 PO_DEBUG.debug_var(d_module,l_progress,'x_return_code',x_return_code);
6597 END IF;
6598
6599 l_progress := '002';
6600
6601 -- Bulk select from po_session_gt back into entity record so that the
6602 -- corresponding process_entity_flag gets updated in entity record
6603 -- doc_id,document_type,document_subtype,entity_id,entity_level,
6604 -- process_entity_flag,recreate_demand_flag
6605
6606 SELECT DISTINCT char4,
6607 char1,
6608 char2,
6609 num1,
6610 char3,
6611 date1,
6612 char5,
6613 'N'
6614 BULK COLLECT INTO
6615 p_entity_rec_tbl
6616 FROM
6617 po_session_gt
6618 WHERE KEY=p_key
6619 ORDER BY char4;
6620
6621 l_count :=SQL%ROWCOUNT;
6622
6623 IF g_debug_stmt THEN
6624 PO_DEBUG.debug_var(d_module,l_progress,'records updated into p_entity_rec_tbl',l_count);
6625 PO_DEBUG.debug_end(d_module);
6626 END IF ;
6627
6628 EXCEPTION
6629 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6630 x_return_msg := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,
6631 P_ENCODED => 'F');
6632 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6633
6634 WHEN FND_API.G_EXC_ERROR THEN
6635 x_return_msg := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,
6636 P_ENCODED => 'F');
6637 x_return_status := FND_API.G_RET_STS_ERROR;
6638
6639 WHEN OTHERS THEN
6640 IF (G_DEBUG_UNEXP) THEN
6641 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
6642 d_module || '.OTHERS_EXCEPTION', 'EXCEPTION: LOCATION IS '
6643 || l_progress || ' SQL CODE IS '||sqlcode);
6644 END IF;
6645
6646
6647 x_return_msg := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,
6648 P_ENCODED => 'F');
6649 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6650
6651
6652 END mark_errored_record;
6653
6654 --------------------------------------------------------------------------------
6655 --Start of Comments
6656 --Name: validate_cancel_action
6657
6658 --Requires: p_da_call_rec to be initialized with the proper data
6659 --Modifies:1.process_entity_flag for each entity being canceled in the entity record
6660 -- which will be used to identify records eligible for futher processing
6661 -- 2.online_report_id column in p_da_call_rec which will be used later
6662 -- to report errors.
6663 --
6664 --Effects: Validates the document for Cancel Action and insert the error in
6665 -- online_eport_text table.
6666 -- Validation includes -
6667 -- 1. Validating the input parameters
6668 -- - entity id against the entity level :The entity id should be a
6669 -- valid id at that level.
6670 -- - Initializes the action date and validate it based on the
6671 -- encumbrance enabled/cbc enabled flags
6672 -- 2. Validates if the current user has access and security clearance
6673 -- to modfiy/act upon the entiy being canceled.
6674 -- 3. Validates the document state if the caller is CANCEL API
6675 -- - Document should be atleast once approved
6676 -- - Document should be in APPROVED/REJECTED/REQUIRES-REAPPROVAL
6677 -- status
6678 -- - Document should not be Finally Closed/Hold
6679 -- 4. Validates if the document has not been changed since its last
6680 -- revision.
6681 -- 5. Business Rule Valdiations to allow cancel action
6682 -- If any of the above validation fails, it inserts the error in
6683 -- po-onlie_report_text
6684 --
6685 --
6686
6687 --Parameters:
6688 --IN:
6689 -- p_da_call_rec
6690 -- p_key
6691 -- p_user_id
6692 -- p_login_id
6693 -- p_po_enc_flag
6694 -- p_req_enc_flag
6695
6696 --IN OUT:
6697
6698 -- OUT:
6699 -- x_return_status
6700 -- FND_API.G_RET_STS_SUCCESS if procedure succeeds
6701 -- FND_API.G_RET_STS_ERROR if procedure fails
6702 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
6703
6704 -- x_msg_data
6705
6706 --End of Comments
6707 --------------------------------------------------------------------------------
6708
6709 PROCEDURE validate_cancel_action(
6710 p_da_call_rec IN OUT NOCOPY po_document_action_pvt.DOC_ACTION_CALL_TBL_REC_TYPE,
6711 p_key IN po_session_gt.key%TYPE,
6712 p_user_id IN po_lines.last_updated_by%TYPE,
6713 p_login_id IN po_lines.last_update_login%TYPE,
6714 p_po_enc_flag IN FINANCIALS_SYSTEM_PARAMETERS.purch_encumbrance_flag%TYPE,
6715 p_req_enc_flag IN FINANCIALS_SYSTEM_PARAMETERS.req_encumbrance_flag%TYPE,
6716 x_return_status OUT NOCOPY VARCHAR2,
6717 x_msg_data OUT NOCOPY VARCHAR2,
6718 x_return_code OUT NOCOPY VARCHAR2)
6719 IS
6720
6721 l_online_report_id NUMBER;
6722 l_temp_key po_session_gt.key%TYPE;
6723 l_key po_session_gt.key%TYPE;
6724
6725
6726
6727 l_agent_id PO_HEADERS.agent_id%TYPE := FND_GLOBAL.employee_id;
6728 l_sequence po_online_report_text.sequence%TYPE ;
6729
6730 d_api_name CONSTANT VARCHAR2(30) := 'validate_cancel_action';
6731 d_module CONSTANT VARCHAR2(100) := g_pkg_name || d_api_name;
6732 l_progress VARCHAR2(3) := '000' ;
6733
6734
6735 BEGIN
6736
6737 IF g_debug_stmt THEN
6738 PO_DEBUG.debug_begin(d_module);
6739 PO_DEBUG.debug_var(d_module,l_progress,'l_agent_id',l_agent_id);
6740 PO_DEBUG.debug_var(d_module,l_progress,'p_key',p_key);
6741 PO_DEBUG.debug_var(d_module,l_progress,'p_login_id',p_login_id);
6742 PO_DEBUG.debug_var(d_module,l_progress,'p_user_id',p_user_id);
6743 PO_DEBUG.debug_var(d_module,l_progress,'p_po_enc_flag',p_po_enc_flag);
6744 PO_DEBUG.debug_var(d_module,l_progress,'p_req_enc_flag',p_req_enc_flag);
6745 END IF;
6746
6747
6748 l_key :=p_key;
6749 x_return_status := FND_API.g_ret_sts_success;
6750 x_msg_data:=NULL;
6751 x_return_code :='S';
6752 l_sequence := 0;
6753
6754 l_progress := '001';
6755
6756 --Get the unique id to be used for this document
6757 SELECT PO_ONLINE_REPORT_TEXT_S.nextval
6758 INTO l_online_report_id
6759 FROM sys.dual;
6760
6761
6762 l_progress := '002';
6763
6764
6765 -- Update the l_online_report_id into the record
6766 p_da_call_rec.online_report_id :=l_online_report_id;
6767
6768 IF g_debug_stmt THEN
6769 PO_DEBUG.debug_var(d_module,l_progress,'l_online_report_id',l_online_report_id);
6770 END IF;
6771
6772
6773 -- Validate the input parameters like entity ids, action date
6774 validate_cancel_action_params(
6775 p_da_call_rec => p_da_call_rec,
6776 p_online_report_id => l_online_report_id,
6777 p_key => l_key,
6778 p_user_id => p_user_id,
6779 p_login_id => p_login_id,
6780 p_po_enc_flag => p_po_enc_flag,
6781 p_req_enc_flag => p_req_enc_flag,
6782 p_sequence => l_sequence,
6783 x_return_status => x_return_status,
6784 x_return_msg => x_msg_data);
6785
6786
6787
6788
6789 IF (x_return_status = FND_API.g_ret_sts_error) THEN
6790 RAISE FND_API.g_exc_error;
6791 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
6792 RAISE FND_API.g_exc_unexpected_error;
6793 END IF;
6794
6795
6796 l_progress := '004';
6797
6798
6799 -- Ensure that we are not using a NULL agent ID.
6800 IF (l_agent_id IS NULL) THEN
6801 l_agent_id := -1;
6802 END IF;
6803
6804
6805 -- Check if this agent has security clearance for the documents being cancelled
6806 val_security_check(
6807 p_entity_rec_tbl => p_da_call_rec.entity_dtl_record_tbl,
6808 p_online_report_id => l_online_report_id,
6809 p_key => l_key,
6810 p_user_id => p_user_id,
6811 p_login_id => p_login_id,
6812 p_sequence => l_sequence,
6813 p_agent_id => l_agent_id,
6814 x_return_status => x_return_status,
6815 x_return_msg => x_msg_data);
6816
6817
6818
6819 IF (x_return_status = FND_API.g_ret_sts_error) THEN
6820 RAISE FND_API.g_exc_error;
6821 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
6822 RAISE FND_API.g_exc_unexpected_error;
6823 END IF;
6824
6825
6826 l_progress := '005';
6827
6828 -- Validate the document state
6829 val_doc_state_check(
6830 p_entity_rec_tbl => p_da_call_rec.entity_dtl_record_tbl,
6831 p_key => l_key,
6832 p_agent_id => l_agent_id,
6833 p_online_report_id => l_online_report_id,
6834 p_user_id => p_user_id,
6835 p_login_id => p_login_id,
6836 p_sequence => l_sequence,
6837 p_source => p_da_call_rec.caller,
6838 x_return_status => x_return_status,
6839 x_return_msg => x_msg_data);
6840
6841
6842
6843 IF (x_return_status = FND_API.g_ret_sts_error) THEN
6844 RAISE FND_API.g_exc_error;
6845 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
6846 RAISE FND_API.g_exc_unexpected_error;
6847 END IF;
6848
6849 l_progress := '006';
6850 l_temp_key :=l_key;
6851
6852 -- For Cancel , all the business rules are to be valdiated on the lowest
6853 -- level entity of any docuemnt
6854 -- ex:On cancelling PO header, we need to validate the rules for all
6855 -- its shipments
6856 -- On cancelling PO Line, we need to validate the rules for all
6857 -- its shipments
6858 -- On Cancelling Blanket header, we need to validate all its line ,
6859 -- and so on..
6860 -- So, updating the session gt with the lowest level enity for each
6861 -- entity being cancelled,which will be used for business rule valdiations
6862 update_gt_with_low_entity(
6863 p_entity_rec_tbl=>p_da_call_rec.entity_dtl_record_tbl,
6864 p_key =>l_temp_key,
6865 x_return_status =>x_return_status,
6866 x_msg_data =>x_msg_data);
6867
6868
6869 IF (x_return_status = FND_API.g_ret_sts_error) THEN
6870 RAISE FND_API.g_exc_error;
6871 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
6872 RAISE FND_API.g_exc_unexpected_error;
6873 END IF;
6874
6875 IF g_debug_stmt THEN
6876 PO_DEBUG.debug_var(d_module,l_progress,'l_key',l_key);
6877 PO_DEBUG.debug_var(d_module,l_progress,'l_temp_key',l_temp_key);
6878 END IF;
6879
6880 l_progress := '007';
6881
6882 -- Lock all the docuemnts being cancelled
6883 PO_DOCUMENT_LOCK_GRP.lock_document (
6884 p_online_report_id =>l_online_report_id,
6885 p_api_version =>1.0,
6886 p_init_msg_list =>FND_API.G_FALSE,
6887 po_sesiongt_key => l_key,
6888 p_user_id => p_user_id,
6889 p_login_id =>p_login_id,
6890 x_return_status =>x_return_status);
6891
6892 IF (x_return_status = FND_API.g_ret_sts_error) THEN
6893 RAISE FND_API.g_exc_error;
6894 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
6895 RAISE FND_API.g_exc_unexpected_error;
6896 END IF;
6897
6898
6899 l_progress := '008';
6900
6901 --check for any change in its current revision with that of in the Archive
6902 -- If there are pending changes, revert it based on user input flag
6903 check_revert_pending_changes(
6904 p_revert_chg_flag => p_da_call_rec.revert_pending_chg_flag,
6905 p_online_report_id => l_online_report_id,
6906 p_user_id => p_user_id,
6907 p_login_id => p_login_id,
6908 p_sequence => l_sequence,
6909 p_source => p_da_call_rec.caller,
6910 p_low_level_key => l_temp_key ,
6911 p_po_enc_flag => p_po_enc_flag,
6912 p_entity_level_key => l_key ,
6913 x_return_status => x_return_status,
6914 x_return_msg => x_msg_data);
6915
6916
6917 IF (x_return_status = FND_API.g_ret_sts_error) THEN
6918 RAISE FND_API.g_exc_error;
6919 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
6920 RAISE FND_API.g_exc_unexpected_error;
6921 END IF;
6922
6923
6924 l_progress := '009';
6925
6926
6927 -- Preform all business validations needed for cancelling the docuemnt
6928 po_cancel_action_checks(
6929 p_entity_rec_tbl => p_da_call_rec.entity_dtl_record_tbl,
6930 p_action_date => p_da_call_rec.action_date,
6931 p_key => l_temp_key,
6932 p_online_report_id => l_online_report_id,
6933 p_user_id => p_user_id,
6934 p_login_id => p_login_id,
6935 p_sequence => l_sequence,
6936 x_return_status => x_return_status,
6937 x_return_msg => x_msg_data);
6938
6939
6940 IF (x_return_status = FND_API.g_ret_sts_error) THEN
6941 RAISE FND_API.g_exc_error;
6942 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
6943 RAISE FND_API.g_exc_unexpected_error;
6944 END IF;
6945
6946 l_progress := '010';
6947
6948 DELETE FROM po_session_gt WHERE KEY=l_temp_key;
6949
6950 l_progress :='011';
6951
6952 --Update the process_entity_flag to N in p_da_call_rec
6953 --for the entities which didnt pass the validations
6954 mark_errored_record(
6955 p_key => l_key,
6956 p_online_report_id => l_online_report_id,
6957 p_entity_rec_tbl => p_da_call_rec.entity_dtl_record_tbl,
6958 x_return_status => x_return_status,
6959 x_return_msg => x_msg_data,
6960 x_return_code => x_return_code);
6961
6962 IF g_debug_stmt THEN
6963 PO_DEBUG.debug_end(d_module);
6964 END IF ;
6965
6966
6967 EXCEPTION
6968 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6969 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,
6970 P_ENCODED => 'F');
6971 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6972 IF (G_DEBUG_UNEXP) THEN
6973 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
6974 d_module || '.UNEXPECTED_EXCEPTION', 'EXCEPTION: LOCATION IS '
6975 || l_progress || ' SQL CODE IS '||sqlcode);
6976 END IF;
6977
6978
6979 WHEN FND_API.G_EXC_ERROR THEN
6980 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,
6981 P_ENCODED => 'F');
6982 x_return_status := FND_API.G_RET_STS_ERROR;
6983
6984 IF (G_DEBUG_UNEXP) THEN
6985 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
6986 d_module || '.ERROR', 'ERROR: LOCATION IS '
6987 || l_progress || ' SQL CODE IS '||sqlcode);
6988 END IF;
6989
6990 WHEN OTHERS THEN
6991 IF (G_DEBUG_UNEXP) THEN
6992 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
6993 d_module || '.OTHERS_EXCEPTION', 'EXCEPTION: LOCATION IS '
6994 || l_progress || ' SQL CODE IS '||sqlcode);
6995 END IF;
6996
6997
6998 x_msg_data := FND_MSG_PUB.GET(P_MSG_INDEX => FND_MSG_PUB.G_LAST,
6999 P_ENCODED => 'F');
7000 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
7001
7002 END validate_cancel_action;
7003
7004
7005
7006
7007 END PO_CONTROL_ACTION_VALIDATIONS;