[Home] [Help]
PACKAGE BODY: APPS.PO_AUTOCREATE_UTIL_PVT
Source
1 PACKAGE BODY PO_AUTOCREATE_UTIL_PVT AS
2 /* $Header: POXVACUB.pls 120.0 2005/06/01 21:10:13 appldev noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PO_AUTOCREATE_UTIL_PVT';
5 G_MODULE_PREFIX CONSTANT VARCHAR2(40) := 'po.plsql.' || g_pkg_name || '.';
6 g_debug_stmt CONSTANT BOOLEAN := PO_DEBUG.is_debug_stmt_on;
7
8 /**************** PRIVATE PROCEDURES ****************/
9
10 -----------------------------------------------------------------------
11 --Start of Comments
12 --Name: add_to_builder_reqs_gt
13 --Pre-reqs: None
14 --Modifies: PO_SESSION_GT
15 --Locks:
16 -- None
17 --Function:
18 -- Insert rows to PO_SESSION_GT. This is done to track builder reqs on
19 -- the server side
20 --Parameters:
21 --IN:
22 --p_key
23 -- key in PO_SESSION_GT
24 --p_req_list
25 -- table containing requisition_line_id
26 --IN OUT:
27 --OUT:
28 --Returns:
29 --Notes:
30 --Testing:
31 --End of Comments
32 -----------------------------------------------------------------------
33 PROCEDURE add_to_builder_reqs_gt
34 ( p_key IN NUMBER,
35 p_req_list IN PO_TBL_NUMBER
36 ) IS
37
38 BEGIN
39
40 FORALL i IN 1..p_req_list.COUNT
41 INSERT INTO PO_SESSION_GT
42 ( key,
43 index_num1
44 )
45 VALUES
46 ( p_key,
47 p_req_list(i)
48 );
49 END add_to_builder_reqs_gt;
50
51
52
53 /**************** PUBLIC PROCEDURES ****************/
54
55 -----------------------------------------------------------------------
56 --Start of Comments
57 --Name: add_to_builder_reqs_gt
58 --Pre-reqs: None
59 --Modifies: PO_SESSION_GT
60 --Locks:
61 -- None
62 --Function:
63 -- Synchronize PO_SESSION_GT with builder reqs. This procedure
64 -- first cleans up the gt table and then insert into the table with the
65 -- rows passed in
66 --Parameters:
67 --IN:
68 --p_key
69 -- key in PO_SESSION_GT
70 --p_req_list
71 -- table containing requisition_line_id
72 --IN OUT:
73 --OUT:
74 --Returns:
75 --Notes:
76 --Testing:
77 --End of Comments
78 -----------------------------------------------------------------------
79 PROCEDURE synchronize_builder_reqs
80 ( p_key IN NUMBER,
81 p_req_list IN PO_TBL_NUMBER
82 ) IS
83
84 l_api_name CONSTANT VARCHAR2(30) := 'synchronize_builder_reqs';
85 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
86 G_MODULE_PREFIX || l_api_name || '.';
87 l_progress VARCHAR2(3);
88
89 BEGIN
90
91 l_progress := '000';
92
93 IF (g_debug_stmt) THEN
94 PO_DEBUG.debug_begin
95 ( p_log_head => l_module
96 );
97
98 PO_DEBUG.debug_var
99 ( p_log_head => l_module,
100 p_progress => l_progress,
101 p_name => 'p_key',
102 p_value => p_key
103 );
104
105 PO_DEBUG.debug_var
106 ( p_log_head => l_module,
107 p_progress => l_progress,
108 p_name => 'p_req_list.COUNT',
109 p_value => p_req_list.COUNT
110 );
111 END IF;
112
113 clear_builder_reqs_gt
114 ( p_key => p_key
115 );
116
117 add_to_builder_reqs_gt
118 ( p_key => p_key,
119 p_req_list => p_req_list
120 );
121
122 IF (g_debug_stmt) THEN
123 PO_DEBUG.debug_end
124 ( p_log_head => l_module
125 );
126 END IF;
127 END synchronize_builder_reqs;
128
129
130 -------------------------------------------------------------------------------
131 -------------------------------------------------------------------------------
132 --Start of Comments
133 --Name: add_req_lines_gt
134 --Pre-reqs:
135 -- None
136 --Modifies:
137 -- po_session_gt
138 --Locks:
139 -- None
140 --Function:
141 -- Adds the input table of numbers to the PO_SESSION_GT table and returns
142 -- the GT key for which the values were added.
143 --Parameters:
144 --IN:
145 --p_req_line_id_tbl
146 -- PO_TBL_NUMBER of Requisition Line IDs to add to the GT table
147 --Returns:
148 -- NUMBER representing the key value for which the input values were added
149 --Notes:
150 -- None
151 --Testing:
152 -- None
153 --End of Comments
154 -------------------------------------------------------------------------------
155 -------------------------------------------------------------------------------
156 FUNCTION add_req_lines_gt
157 (
158 p_req_line_id_tbl IN PO_TBL_NUMBER
159 )
160 RETURN NUMBER
161 IS
162 l_key NUMBER;
163
164 BEGIN
165
166 l_key := PO_CORE_S.get_session_gt_nextval;
167
168 FORALL i IN 1..p_req_line_id_tbl.COUNT
169
170 INSERT INTO po_session_gt
171 ( key
172 , index_num1
173 ) VALUES
174 ( l_key
175 , p_req_line_id_tbl(i)
176 );
177
178 return (l_key);
179
180 END add_req_lines_gt;
181
182 -----------------------------------------------------------------------
183 --Start of Comments
184 --Name: clear_builder_reqs_gt
185 --Pre-reqs: None
186 --Modifies: PO_SESSION_GT
187 --Locks:
188 -- None
189 --Function:
190 -- Remove all builder reqs from PO_SESSION_GT table
191 --Parameters:
192 --IN:
193 --p_key
194 -- key in PO_SESSION_GT
195 --IN OUT:
196 --OUT:
197 --Returns:
198 --Notes:
199 --Testing:
200 --End of Comments
201 -----------------------------------------------------------------------
202 PROCEDURE clear_builder_reqs_gt
203 (
204 p_key IN NUMBER
205 )
206 IS
207 BEGIN
208
209 DELETE FROM PO_SESSION_GT
210 WHERE key = p_key;
211
212 END clear_builder_reqs_gt;
213
214 -------------------------------------------------------------------------------
215 -------------------------------------------------------------------------------
216 --Start of Comments
217 --Name: get_and_lock_req_lines_in_pool
218 --Pre-reqs:
219 -- None
220 --Modifies:
221 -- None
222 --Locks:
223 -- po_requisition_lines_all
224 --Function:
225 -- Retrieves and locks all Requisition Lines that are specified in the input
226 -- nested table and are in the Req Pool.
227 --Parameters:
228 --IN:
229 --p_req_line_id_tbl
230 -- PO_TBL_NUMBER of Requisition Line IDs to add to the GT table
231 --p_lock_records
232 -- 'Y' or 'N' indicating whether to lock the records that are being retrieved
233 --OUT:
234 --x_req_line_id_in_pool_tbl
235 -- PO_TBL_NUMBER of Req Line IDs which exist in the input nested table
236 -- as well as the Req Pool
237 --x_records_locked
238 -- BOOLEAN indicating whether the records were successfully locked
239 --Notes:
240 -- None
241 --Testing:
242 -- None
243 --End of Comments
244 -------------------------------------------------------------------------------
245 -------------------------------------------------------------------------------
246 PROCEDURE get_and_lock_req_lines_in_pool
247 (
248 p_req_line_id_tbl IN PO_TBL_NUMBER
249 , p_lock_records IN VARCHAR2
250 , x_req_line_id_in_pool_tbl OUT NOCOPY PO_TBL_NUMBER
251 , x_records_locked OUT NOCOPY VARCHAR2
252 )
253 IS
254 l_key NUMBER;
255
256 BEGIN
257
258 x_records_locked := 'N';
259
260 -- Initialize GT Table ----------------------------------------------------
261
262 l_key := PO_AUTOCREATE_UTIL_PVT.add_req_lines_gt(p_req_line_id_tbl);
263
264
265 -- Query and Lock ---------------------------------------------------------
266
267 IF ( p_lock_records = 'Y' ) THEN
268
269 SELECT pool.requisition_line_id
270 BULK COLLECT INTO x_req_line_id_in_pool_tbl
271 FROM po_req_lines_in_pool_sec_v pool
272 WHERE pool.requisition_line_id IN ( SELECT selected.index_num1
273 FROM po_session_gt selected
274 WHERE selected.key = l_key
275 )
276 FOR UPDATE NOWAIT;
277
278 x_records_locked := 'Y';
279
280 -- Query Only -------------------------------------------------------------
281
282 ELSE
283
284 SELECT pool.requisition_line_id
285 BULK COLLECT INTO x_req_line_id_in_pool_tbl
286 FROM po_req_lines_in_pool_sec_v pool
287 WHERE pool.requisition_line_id IN ( SELECT selected.index_num1
288 FROM po_session_gt selected
289 WHERE selected.key = l_key
290 );
291
292 END IF;
293
294 -- Clean Up GT Table ------------------------------------------------------
295
296 -- Delete Requisition Lines which we previously added to the GT table.
297
298 clear_builder_reqs_gt(l_key);
299
300 EXCEPTION
301
302 WHEN OTHERS THEN
303
304 IF ( SQLCODE = -54 ) -- unable to lock records
305 THEN
306 x_records_locked := 'N';
307 ELSE
308 raise;
309 END IF;
310
311 END get_and_lock_req_lines_in_pool;
312
313
314 END PO_AUTOCREATE_UTIL_PVT;