1 PACKAGE BODY PO_LINES_SV2 as
2 /* $Header: POXPOL2B.pls 120.1.12020000.3 2013/02/10 11:35:28 vegajula ship $ */
3
4 /*============================= PO_LINES_SV2 ===============================*/
5 /*===========================================================================
6
7 FUNCTION NAME: get_max_line_num()
8
9 ===========================================================================*/
10 FUNCTION get_max_line_num
11 (X_po_header_id NUMBER) return number is
12
13 x_max_line_num NUMBER;
14 X_Progress varchar2(3) := '';
15
16 BEGIN
17 X_Progress := '010';
18
19 SELECT nvl(max(line_num),0)
20 INTO X_max_line_num
21 FROM po_lines
22 WHERE po_header_id = X_po_header_id;
23
24 return(x_max_line_num);
25
26 EXCEPTION
27
28 WHEN OTHERS THEN
29 return(0);
30
31
32 END get_max_line_num;
33
34
35 /*===========================================================================
36
37 PROCEDURE NAME: update_line()
38 Moved to PO_LINES_SV11
39 ecso 3/19/97 for globalization
40
41 ===========================================================================*/
42
43 /*RETROACTIVE FPI START */
44 Procedure retroactive_change(p_po_line_id IN number) IS
45 X_progress VARCHAR2(3) := '';
46 x_user_id NUMBER := fnd_global.user_id;
47
48 /* Bug 12648504 */
49 l_line_id NUMBER;
50
51 BEGIN
52 X_progress := '010';
53
54 /* Bug 12648504 Start , try to get the lock on the record in PO_LINES before updating the
55 record . If system can't get the lock then it trhows an exception with sqlcode -54
56 */
57
58 SELECT po_line_id INTO l_line_id
59 FROM
60 PO_LINES
61 WHERE
62 po_line_id = p_po_line_id FOR UPDATE OF RETROACTIVE_DATE NOWAIT;
63
64 /* Bug 12648504 End */
65
66
67 update po_lines
68 set retroactive_date = sysdate,
69 last_update_date = sysdate,
70 last_updated_by = x_user_id
71 where po_line_id = p_po_line_id;
72 EXCEPTION
73 WHEN OTHERS THEN
74
75 /* Bug 12648504 Start ,Error code -54 means can't get lock on the record. */
76 IF SQLCODE=-54 THEN
77 RAISE;
78 END IF;
79 /* Bug 12648504 End */
80
81 po_message_s.sql_error('retroactive_change', X_progress, sqlcode);
82 raise;
83 END retroactive_change;
84 /*RETROACTIVE FPI END*/
85
86 -- <FPJ Retroactive START>
87 --------------------------------------------------------------------------------
88 --Start of Comments
89 --Name: retroactive_change
90 --Pre-reqs:
91 -- None.
92 --Modifies:
93 -- PO_LINE_LOCATIONS_ALL.retroactive_date.
94 --Locks:
95 -- None.
96 --Function:
97 -- This is the API which updates the column retroactive_date in po_line_locations
98 -- for Release ONLY with sysdate.
99 -- This procedure is called from PO_SHIPMENTS.price_override WHEN-VALIDATE-ITEM
100 -- trigger in the Enter Release form.
101 -- This will give the release shipment a different time with its corresponding
102 -- blanket agreement line, so that Approval Workflow will know this release had
103 -- some retroactive price change.
104 --Parameters:
105 --IN:
106 --p_line_location_id
107 -- the line_location_id for which the retroactive_Date needs to be updated.
108 --OUT:
109 -- None.
110 --Testing:
111 --
112 --End of Comments
113 -------------------------------------------------------------------------------
114 Procedure retro_change_shipment(p_line_location_id IN number) IS
115 l_progress VARCHAR2(3) := '';
116 l_user_id NUMBER := FND_GLOBAL.user_id;
117 BEGIN
118 l_progress := '010';
119
120 --Bug12931756, update retroactive_date directly to the same date as
121 --on the corresponding blanket agreement line. The approval workflow
122 --would not update the retroactive_date again
123 UPDATE po_line_locations pll
124 SET retroactive_date = (SELECT pl.retroactive_date
125 FROM po_lines_all pl
126 WHERE pl.po_line_id = pll.po_line_id),
127 --retroactive_date = SYSDATE,
128 last_update_date = SYSDATE,
129 last_updated_by = l_user_id
130 WHERE line_location_id = p_line_location_id;
131 EXCEPTION
132 WHEN OTHERS THEN
133 po_message_s.sql_error('retro_change_shipment', l_progress, sqlcode);
134 raise;
135 END retro_change_shipment;
136 -- <FPJ Retroactive END>
137
138
139 END PO_LINES_SV2;
[Home] [Help]