1 PACKAGE BODY PO_REQ_DOCUMENT_UPDATE_PVT AS
2 /* $Header: POXVCRQB.pls 120.5 2005/10/31 00:15:36 sjadhav noship $*/
3
4 --CONSTANTS
5
6 G_PKG_NAME CONSTANT varchar2(30) := 'PO_REQ_DOCUMENT_UPDATE_PVT';
7
8 c_log_head CONSTANT VARCHAR2(50) := 'po.plsql.'|| G_PKG_NAME || '.';
9
10 -- Read the profile option that enables/disables the debug log
11 g_fnd_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
12
13 -------------------------------------------------------------------------------
14 --Start of Comments
15 --Name: derive_dependent_fields
16 --Pre-reqs:
17 -- None.
18 --Modifies:
19 -- None.
20 --Locks:
21 -- None.
22 --Function:
23 -- Helper to update_requisition to derive dependent fields in Req Line/Distribution
24 -- The following fields in p_distributions record are derived
25 -- Distribution Quantity
26 -- Distribution Total
27 -- Distribution Taxes
28 --Parameters:
29 --IN:
30 --p_count
31 -- Specifies the number of entities in table IN parameters like p_header_id, p_release_id
32 -- All the table IN parameters are assumed to be of the same size
33 -- Other IN parameters are detailed in main procedure update_requisition
34 --OUT:
35 --x_return_status
36 -- Indicates API return status as 'S', 'E' or 'U'.
37 --x_req_status_rec
38 -- The various status fields would have the PO/Rel Line/Shipment status values
39 --End of Comments
40 -------------------------------------------------------------------------------
41
42 PROCEDURE derive_dependent_fields (
43 p_lines IN OUT NOCOPY PO_REQ_LINES_REC_TYPE,
44 p_distributions IN OUT NOCOPY PO_REQ_DISTRIBUTIONS_REC_TYPE,
45 p_update_source IN VARCHAR2,
46 x_return_status OUT NOCOPY VARCHAR2,
47 x_msg_count OUT NOCOPY NUMBER,
48 x_msg_data OUT NOCOPY VARCHAR2
49 ) IS
50
51 l_api_name CONSTANT VARCHAR(30) := 'DERIVE_DEPENDENT_FIELDS';
52 l_progress VARCHAR2(3) := '000';
53
54 l_sequence PO_TBL_NUMBER := PO_TBL_NUMBER();
55 l_dist_total_qty PO_TBL_NUMBER;
56 l_last_dist_index PO_TBL_NUMBER;
57 l_line_count NUMBER := p_lines.req_line_id.count;
58 l_key po_session_gt.key%type;
59
60 BEGIN
61
62 IF g_fnd_debug = 'Y' THEN
63 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
64 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, c_log_head || '.'||l_api_name||'.'
65 || l_progress, 'Entering Procedure '||l_api_name);
66 END IF;
67 END IF;
68
69 --Use sequence(i) to simulate i inside FORALL as direct reference to i not allowed
70 --Initialize sequence array to contain 1,2,3, ..., p_count
71 l_sequence.extend(l_line_count);
72 FOR i IN 1..l_line_count LOOP
73 l_sequence(i) := i;
74 END LOOP;
75
76 ----------------------------------------------------------------
77 -- PO_SESSION_GT column mapping
78 --
79 --<R12 eTax Integration> Removed num3, num4, num5 and code for
80 -- tax proration Tax will now be recalculated not prorated
81 --
82 -- num1 Req Distribution Quantity
83 -- num2 Req New Distribution Total
84 -- num9 Req Line Sequence
85 -- num10 Req Distribution ID
86 ----------------------------------------------------------------
87
88 select po_session_gt_s.nextval into l_key from dual;
89
90 -- Derive LineTotal and DistributionQuantity
91 FORALL i IN 1..l_line_count
92 INSERT
93 INTO po_session_gt
94 (key,
95 num9, --Req Line Sequence
96 num10, --Req Distribution ID
97 num1) --Req Distribution Quantity
98 select
99 l_key,
100 l_sequence(i),
101 rd.distribution_id,
102 --ReqDistributionQuantity: prorated from line
103 decode(p_lines.quantity(i), null, null,
104 rd.req_line_quantity * p_lines.quantity(i) / rl.quantity)
105 FROM po_requisition_lines rl, po_req_distributions rd
106 where rl.requisition_line_id = p_lines.req_line_id(i)
107 and rd.requisition_line_id = rl.requisition_line_id;
108
109 -- For each Req Line, Get
110 -- sum of new distribution quantities and last distributionID
111 select
112 sum(num1),
113 max(num10)
114 BULK COLLECT INTO
115 l_dist_total_qty,
116 l_last_dist_index
117 from po_session_gt
118 where key = l_key
119 group by num9
120 order by num9; --Group and Order by Req Line Sequence stored in num9
121
122 -- Add any proration remainder to the last distribution in the line
123 -- num1 Req Distribution Quantity
124 -- num10 Req Distribution ID
125 FORALL i IN 1..l_line_count
126 update po_session_gt
127 set num1 = num1 + (p_lines.quantity(i) - l_dist_total_qty(i))
128 where key = l_key and num10 = l_last_dist_index(i);
129
130 -- Derive Line Total
131 FORALL i IN 1..l_line_count
132 update po_session_gt
133 set
134 num2 --Req New Line Total
135 = (select
136 --New Line Total
137 decode(nvl(p_lines.unit_price(i), nvl(p_lines.quantity(i), p_lines.amount(i))),
138 null, null,
139 decode(plt.matching_basis, 'AMOUNT',
140 nvl(p_lines.amount(i), rl.amount),
141 nvl(p_lines.unit_price(i), rl.unit_price)
142 * nvl(p_lines.quantity(i), rl.quantity)))
143 FROM po_requisition_lines rl, po_req_distributions rd, po_line_types plt
144 where rl.requisition_line_id = p_lines.req_line_id(i)
145 and rd.requisition_line_id = rl.requisition_line_id
146 and rl.line_type_id = plt.line_type_id
147 and rd.distribution_id = num10)
148 WHERE key = l_key -- bug3551463
149 AND num9 = l_sequence(i); -- bug3551463
150
151 -- Select the derived fields from GT Table Into p_distributions Record
152 SELECT
153 num10, -- Req Distribution ID
154 num1, -- Req Distribution Quantity
155 num2 -- Req New Distribution Total
156 BULK COLLECT INTO
157 p_distributions.distribution_id,
158 p_distributions.quantity,
159 p_distributions.total
160 FROM po_session_gt
161 where key = l_key;
162
163 delete from po_session_gt where key = l_key;
164
165 x_return_status := FND_API.G_RET_STS_SUCCESS;
166
167 EXCEPTION
168 WHEN FND_API.G_EXC_ERROR THEN
169 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
170 x_return_status := FND_API.G_RET_STS_ERROR;
171 WHEN OTHERS THEN
172 FND_MSG_PUB.add_exc_msg(G_PKG_NAME, l_api_name || '.' || l_progress);
173 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
174 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
175
176 END derive_dependent_fields;
177
178 -------------------------------------------------------------------------------
179 --Start of Comments
180 --Name: update_requisition
181 --Pre-reqs:
182 -- None.
183 --Modifies:
184 -- Modifies the Requisition Line/Distribution
185 --Locks:
186 -- None.
187 --Function:
188 -- Updates the Requisition with changes to various fields on Req Line/Distribution
189 --Parameters:
190 --IN:
191 --p_api_version
192 -- Specifies API version.
193 --p_req_changes
194 -- Specifies changes in the Requisition at Line/Distribution level
195 --p_update_source
196 -- Source of update. Currently not used by the API. Created for future use.
197 --OUT:
198 --x_return_status
199 -- Indicates API return status as 'S', 'E' or 'U'.
200 --x_msg_count
201 -- The number of messages put into FND Message Stack by this API
202 --x_msg_data
203 -- First message put into FND Message Stack by this API
204 --Testing:
205 -- All the input table parameters should have the exact same length.
206 -- They may have null values at some indexes, but need to identify an entity uniquely
207 -- Call the API when only Requisition Exist, PO/Release Exist
208 -- and for all the combinations of attributes.
209 --End of Comments
210 -------------------------------------------------------------------------------
211
212 PROCEDURE update_requisition (
213 p_api_version IN NUMBER,
214 p_req_changes IN OUT NOCOPY PO_REQ_CHANGES_REC_TYPE,
215 p_update_source IN VARCHAR2,
216 x_return_status OUT NOCOPY VARCHAR2,
217 x_msg_count OUT NOCOPY NUMBER,
218 x_msg_data OUT NOCOPY VARCHAR2
219 ) IS
220
221 l_api_name CONSTANT VARCHAR(30) := 'UPDATE_REQUISITION';
222 l_api_version CONSTANT NUMBER := 1.0;
223 l_progress VARCHAR2(3) := '000';
224 l_req_status_rec PO_STATUS_REC_TYPE;
225 l_req_header_id po_tbl_number := po_tbl_number();
226 l_line_count NUMBER := p_req_changes.line_changes.req_line_id.count;
227 l_return_status VARCHAR2(1); --<eTax Integration R12>
228
229 BEGIN
230
231 IF g_fnd_debug = 'Y' THEN
232 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
233 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, c_log_head || '.'||l_api_name||'.'
234 || l_progress, 'Entering Procedure '||l_api_name);
235 END IF;
236 END IF;
237
238 -- Standard call to check for call compatibility
239 l_progress := '010';
240 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
241 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
242 END IF;
243
244 l_progress := '020';
245 -- Initialize Variables to call Req Status Check API
246 l_req_header_id.extend(l_line_count);
247 FOR i IN 1..l_line_count LOOP
248 l_req_header_id(i) := p_req_changes.req_header_id;
249 END LOOP;
250
251 l_progress := '030';
252 --Req Status Check API to Check if Requisition Header/Line Status allows update
253 --Lock the Header/Line/Distribution records if this Requisition is updatable
254 PO_REQ_DOCUMENT_CHECKS_PVT.req_status_check(
255 p_api_version => 1.0,
256 p_req_header_id => l_req_header_id,
257 p_req_line_id => p_req_changes.line_changes.req_line_id,
258 p_req_distribution_id => null,
259 p_mode => 'CHECK_UPDATEABLE',
260 p_lock_flag => 'Y',
261 x_req_status_rec => l_req_status_rec,
262 x_return_status => x_return_status,
263 x_msg_count => x_msg_count,
264 x_msg_data => x_msg_data);
265
266 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
267 return;
268 END IF;
269
270 l_progress := '040';
271 FOR i IN 1..l_line_count LOOP
272 IF l_req_status_rec.updatable_flag(i) <> 'Y' THEN
273 -- The Req Header/Line is not updatable, Error out
274 FND_MESSAGE.set_name('PO', 'PO_CANT_CHANGE_REQ');
275 FND_MSG_PUB.add;
276 RAISE FND_API.G_EXC_ERROR;
277 END IF;
278 END LOOP;
279
280 l_progress := '050';
281 --Derive Distribution Quantity and Tax values
282 PO_REQ_DOCUMENT_UPDATE_PVT.derive_dependent_fields(
283 p_lines => p_req_changes.line_changes,
284 p_distributions => p_req_changes.distribution_changes,
285 p_update_source => p_update_source,
286 x_return_status => x_return_status,
287 x_msg_count => x_msg_count,
288 x_msg_data => x_msg_data);
289
290 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
291 return;
292 END IF;
293
294 l_progress := '060';
295 --Apply Req Line Changes to Database in Bulk
296 FORALL i IN 1..l_line_count
297 update po_requisition_lines
298 set unit_price=
299 nvl(p_req_changes.line_changes.unit_price(i), unit_price),
300 currency_unit_price=
301 nvl(p_req_changes.line_changes.currency_unit_price(i), currency_unit_price),
302 quantity
303 =nvl(p_req_changes.line_changes.quantity(i), quantity),
304 secondary_quantity
305 =nvl(p_req_changes.line_changes.secondary_quantity(i), secondary_quantity),
306 need_by_date
307 =nvl(p_req_changes.line_changes.need_by_date(i), need_by_date),
308 deliver_to_location_id =
309 nvl(p_req_changes.line_changes.deliver_to_location_id(i), deliver_to_location_id),
310 assignment_start_date
311 =nvl(p_req_changes.line_changes.assignment_start_date(i), assignment_start_date),
312 assignment_end_date
313 =nvl(p_req_changes.line_changes.assignment_end_date(i), assignment_end_date),
314 amount =
315 nvl(p_req_changes.line_changes.amount(i), amount),
316 tax_attribute_update_code =
317 'UPDATE'
318 where requisition_line_id= p_req_changes.line_changes.req_line_id(i);
319
320 l_progress := '070';
321 --Apply Req Distribution Changes to Database in Bulk
322 FORALL i IN 1.. p_req_changes.distribution_changes.distribution_id.COUNT
323 update po_req_distributions
324 set req_line_quantity
325 =nvl(p_req_changes.distribution_changes.quantity(i), req_line_quantity)
326 where distribution_id= p_req_changes.distribution_changes.distribution_id(i);
327
328 --<eTax Integration R12 Start> Call Requisition Tax API for tax calculation
329 -- recoverable and non revoverable tax will get updated in distributions
330 -- table in the below call
331 l_return_status := NULL;
332 po_tax_interface_pvt.calculate_tax_requisition(
333 x_return_status => l_return_status,
334 p_requisition_header_id => p_req_changes.req_header_id,
335 p_calling_program => 'REQ_CHANGE');
336
337 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
338 FOR i IN 1..po_tax_interface_pvt.G_TAX_ERRORS_TBL.MESSAGE_TEXT.COUNT
339 LOOP
340 FND_MESSAGE.SET_NAME('PO','PO_CUSTOM_MSG');
341 FND_MESSAGE.SET_TOKEN('TRANSLATED_TOKEN',po_tax_interface_pvt.G_TAX_ERRORS_TBL.message_text(i));
342 FND_MSG_PUB.add;
343 END LOOP;
344 END IF;
345
346 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
347 RAISE FND_API.G_EXC_ERROR;
348 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
349 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
350 END IF;
351 -- <eTax Integration R12 End>
352
353 x_return_status := FND_API.G_RET_STS_SUCCESS;
354
355 l_progress := '080';
356
357 EXCEPTION
358 WHEN FND_API.G_EXC_ERROR THEN
359 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
360 x_return_status := FND_API.G_RET_STS_ERROR;
361 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
362 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
363 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
364 WHEN OTHERS THEN
365 FND_MSG_PUB.add_exc_msg(G_PKG_NAME, l_api_name || '.' || l_progress);
366 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
367 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
368
369 END update_requisition;
370
371 END PO_REQ_DOCUMENT_UPDATE_PVT;
[Home] [Help]