[Home] [Help]
PACKAGE BODY: APPS.PO_RETROACTIVE_PRICING_PVT
Source
1 PACKAGE BODY PO_RETROACTIVE_PRICING_PVT AS
2 /*$Header: POXRPRIB.pls 120.18.12000000.2 2007/07/11 08:30:43 puppulur ship $*/
3
4 -- Global Variables
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PO_RETROACTIVE_PRICING_PVT';
6 G_BULK_LIMIT number := 1000;
7 g_log_mode VARCHAR2(240) :=
8 NVL(FND_PROFILE.VALUE('PO_SET_DEBUG_CONCURRENT_ON'),'N') ;
9 TYPE g_agreement_cur_type IS REF CURSOR;
10 TYPE num_table is table of number index by binary_integer;
11 TYPE char30_table is table of varchar2(30) index by binary_integer;
12 TYPE date_table is table of date index by binary_integer;
13
14 /* Global tables that are defined for inserting into the global temp
15 * table po_retroprice_gt.
16 */
17 g_row_id_table char30_table;
18 g_exclude_row_id_table char30_table;
19 g_new_price_table num_table;
20 g_po_header_id_table num_table;
21 g_po_release_id_table num_table;
22 g_auth_status_table char30_table;
23 g_archived_rev_num_table num_table;
24 g_po_auth_table char30_table;
25 g_rel_auth_table char30_table;
26
27 g_index number;
28 g_exclude_index number;
29
30 g_orig_org_id number;
31
32 g_log_head CONSTANT VARCHAR2(60) := 'po.plsql.' || g_pkg_name || '.';
33 g_sysdate DATE := sysdate;
34 g_user_id NUMBER := fnd_global.user_id;
35
36 -- <FPJ Retroactive Price>
37 g_communicate_update VARCHAR2(1) := 'N';
38
39
40 -- Debugging
41
42 g_debug_stmt BOOLEAN := PO_DEBUG.is_debug_stmt_on;
43 g_debug_unexp BOOLEAN := PO_DEBUG.is_debug_unexp_on;
44 -- Read the profile option that enables/disables the debug log
45 g_fnd_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
46
47 -- Bug 3231062
48 g_projects_11i10_installed CONSTANT VARCHAR2(1) :=
49 PA_PO_INTEGRATION.is_pjc_11i10_enabled;
50
51 -- bug2935437
52 /**
53 * Private Procedure: open_agreement_cur
54 * Modifies: x_cursor
55 * Effects: This procedure opens a cursor with dynamic sql embedded in string
56 * p_sql_str. Since the number of bind variables can be different
57 * based on the values of parameters p_item_from (to) and p_cat_from (to),
58 * the command for opening the cursor needs to be coded several times
59 * and one of them will be executed based on the number of bind variables
60 * Returns:
61 * x_cursor: cursor of an dynamic sql statement
62 */
63 PROCEDURE open_agreement_cur(p_sql_str IN VARCHAR2,
64 p_po_header_id IN NUMBER,
65 p_vendor_id IN NUMBER,
66 p_vendor_site_id IN NUMBER,
67 p_category_struct_id IN NUMBER,
68 p_ga_security IN VARCHAR2,
69 p_item_from IN VARCHAR2,
70 p_item_to IN VARCHAR2,
71 p_cat_from IN VARCHAR2,
72 p_cat_to IN VARCHAR2,
73 x_cursor IN OUT NOCOPY g_agreement_cur_type);
74 --
75
76 -- Bug 4080732 START: Forward declaration of the private function
77 FUNCTION is_inv_org_period_open
78 ( p_std_po_price_change IN VARCHAR2,
79 p_po_line_id IN NUMBER,
80 p_po_line_loc_id IN NUMBER
81 )
82 RETURN VARCHAR2;
83 -- Bug 4080732 END
84
85 /**
86 * Private Procedure: MassUpdate_Releases
87 * Modifies: Column price_override, retroactive_date in po_line_locations,
88 * Authorization_status, revision_num in po_headers and po_releases.
89 * Effects: Selects the agreements( blankets and contracts) as specified
90 * by the concurrent parameters and selects the execution docs
91 * refering these agreements for retroactive price updates.
92 * Get the new price based on the release/Std PO shipment values.
93 * If they are different, then update po_line_locations with the
94 * new price. In either case, update retoractive_date in
95 * po_line_locations with the retroactive_date in po_lines so that
96 * this shipment will not be picked up again unless blanket line
97 * is retroactively changed. Once all the releases are done, update
98 * po_headers or po_releases with the new revision number and set
99 * authorization_status to "Requires Reapproval" and initiate
100 * Workflow if the document was already in Approved state.
101 * Returns:
102 * x_return_status - FND_API.G_RET_STS_SUCCESS if control action succeeds
103 * FND_API.G_RET_STS_ERROR if control action fails
104 * FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
105 */
106 --------------------------------------------------------------------------------
107 --Start of Comments
108 --Name: MassUpdate_Releases
109 --Pre-reqs:
110 -- None.
111 --Modifies:
112 -- Column price_override, retroactive_date in po_line_locations,
113 -- Authorization_status, revision_num in po_headers and po_releases.
114 --Locks:
115 -- None.
116 --Function:
117 -- This API is called from the Approval Window or by the
118 -- Concurrent Program. This procedure will update all
119 -- the releases against Blanket Agreeements or Standard
120 -- POs against Global Agreements that have lines that
121 -- are retroactively changed.
122 -- Selects the blanket lines that have been retroactively changed
123 -- and selects all the releases againt BA (or std PO against GA).
124 -- Get the new price based on the release/Std PO shipment values.
125 -- If they are different, then update po_line_locations with the
126 -- new price. In either case, update retoractive_date in
127 -- po_line_locations with the retroactive_date in po_lines so that
128 -- this shipment will not be picked up again unless blanket line
129 -- is retroactively changed. Once all the releases are done, update
130 -- po_headers or po_releases with the new revision number and set
131 -- authorization_status to "Requires Reapproval" and initiate
132 -- Workflow if the document was already in Approved state.
133 --Parameters:
134 --IN:
135 --p_api_version
136 -- Version number of API that caller expects. It
137 -- should match the l_api_version defined in the
138 -- procedure (expected value : 1.0)
139 --p_validation_level
140 -- validation level api uses
141 --p_vendor_id
142 -- Site_id of the Supplier site selected by the user.
143 --p_po_header_id
144 -- Header_id of the Blanket/Global Agreement selected by user.
145 --p_category_struct_id
146 -- Purchasing Category structure Id
147 --p_category_from / p_category_to
148 -- Category Range that user selects to process retroactive changes
149 --p_item_num_from / p_item_num_to
150 -- Item Range that user selects to process retroactive changes
151 --p_date
152 -- All releases or Std PO created on or after this date must be changed.
153 --p_communicate_update
154 -- Communicate Price Updates to Supplier
155 --OUT:
156 --x_return_status
157 -- FND_API.G_RET_STS_SUCCESS if API succeeds
158 -- FND_API.G_RET_STS_ERROR if API fails
159 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
160 --Testing:
161 --
162 --End of Comments
163 --------------------------------------------------------------------------------
164
165 Procedure MassUpdate_Releases ( p_api_version IN NUMBER,
166 p_validation_level IN NUMBER,
167 p_vendor_id IN Number,
168 p_vendor_site_id IN Number,
169 p_po_header_id IN Number,
170 p_category_struct_id IN Number,
171 p_category_from IN Varchar2,
172 p_category_to IN Varchar2,
173 p_item_from IN Varchar2,
174 p_item_to IN Varchar2,
175 p_date IN Date,
176 -- <FPJ Retroactive Price>
177 p_communicate_update IN VARCHAR2 DEFAULT NULL,
178 x_return_status OUT NOCOPY VARCHAR2)
179
180 IS
181
182 --Bug 4176111: Add the paratemeter p_qp_license_on to conditionally enable logic to test retroactive_date
183 cursor select_open_releases(l_po_line_id number, l_retroactive_date date,p_date date, p_qp_license_on VARCHAR2 ) is
184 select poll.rowid,poll.line_location_id, poll.quantity,
185 poll.ship_to_organization_id, poll.ship_to_location_id,
186 poll.price_override, nvl(poll.need_by_date,sysdate),
187 por.po_release_id,
188 por.authorization_status, por.revision_num,
189 pora.revision_num
190 from po_line_locations poll,
191 po_releases_all por, -- <R12 MOAC>
192 po_releases_archive pora
193 where nvl(por.frozen_flag, 'N') = 'N'
194 and nvl(por.authorization_status, 'INCOMPLETE') IN
195 ('APPROVED', 'INCOMPLETE', 'REJECTED',
196 'REQUIRES REAPPROVAL')
197 and nvl(por.closed_code,'OPEN') IN ('OPEN','CLOSED',
198 'CLOSED FOR RECEIVING',
199 /* Bug 3334043: Releases that are closed by setting 'Invoice Close
200 * Tolerance to 100%' should also be picked up
201 */
202 'CLOSED FOR INVOICE')
203 and nvl(por.cancel_flag,'N') <> 'Y'
204 and nvl(por.consigned_consumption_flag,'N') ='N'
205 and ((poll.accrue_on_receipt_flag = 'Y' and
206 quantity_received =0 and quantity_billed =0)
207 OR
208 (poll.accrue_on_receipt_flag = 'N' and
209 quantity_billed = 0))
210 /* Bug 2725744. Added the condition to check for closed_code
211 * and cancel_flag flag for the release shipments.
212 */
213 and nvl(poll.closed_code,'OPEN') IN ('OPEN','CLOSED',
214 'CLOSED FOR RECEIVING',
215 /* Bug 3334043: Releases that are closed by setting 'Invoice Close
216 * Tolerance to 100%' should also be picked up
217 */
218 'CLOSED FOR INVOICE')
219 and nvl(poll.cancel_flag,'N') <> 'Y'
220 and por.po_release_id = poll.po_release_id
221 and poll.po_release_id is not null
222 and poll.po_line_id = l_po_line_id
223 --Bug 4176111: Only when the Advanced Pricing API is not enabled for PO, then use logic of comparing retroactive_date
224 --to rule out unneccesary release linelocations
225 and ( p_qp_license_on = 'Y' OR
226 ( p_qp_license_on = 'N' and nvl(poll.retroactive_date,sysdate) <> l_retroactive_date)) --<R12 GBPA Adv Pricing >
227 and nvl(poll.need_by_date,por.creation_date) >=
228 nvl(p_date,nvl(poll.need_by_date,por.creation_date))
229 AND POR.po_release_id = PORA.po_release_id (+)
230 AND PORA.latest_external_flag (+) = 'Y'
231 order by por.po_release_id
232 for update of poll.retroactive_date;
233
234 --Bug 4176111: Add the paratemeter p_qp_license_on to conditionally enable logic to test retroactive_date
235 cursor select_open_stdpo(l_po_line_id number, l_retroactive_date date,
236 p_date date, p_qp_license_on VARCHAR2) is
237 select pol.rowid,poll.line_location_id,pol.quantity,
238 poll.ship_to_organization_id, poll.ship_to_location_id,
239 poll.price_override, nvl(poll.need_by_date,sysdate),
240 poh.po_header_id,
241 poh.authorization_status, poh.revision_num,
242 poha.revision_num
243 from po_headers_all poh, po_lines_all pol, po_line_locations_all poll,
244 po_headers_archive_all poha,financials_system_params_all fsp
245 where pol.from_line_id = l_po_line_id
246 and poh.po_header_id = pol.po_header_id
247 and poh.org_id = fsp.org_id -- <R12 MOAC> -- Bug 3573266
248 and nvl(fsp.purch_encumbrance_flag,'N') = 'N' -- Bug 3573266
249 --Bug 4176111: Only when the Advanced Pricing API is not enabled for PO, then use logic of comparing retroactive_date
250 --to rule out unneccesary spo linelocations
251 and ( p_qp_license_on = 'Y' OR
252 ( p_qp_license_on = 'N' and nvl(poll.retroactive_date,sysdate) <> l_retroactive_date)) --<R12 GBPA Adv Pricing >
253 and poll.shipment_num = (select min(poll.shipment_num)
254 from po_line_locations_all polt
255 where polt.po_line_id=pol.po_line_id)
256 and nvl(poh.frozen_flag, 'N') = 'N'
257 and nvl(poh.authorization_status, 'INCOMPLETE') IN
258 ('APPROVED', 'INCOMPLETE', 'REJECTED',
259 'REQUIRES REAPPROVAL')
260 and nvl(poh.closed_code,'OPEN') IN ('OPEN','CLOSED',
261 'CLOSED FOR RECEIVING',
262 /* Bug 3334043: Std.POs that are closed by setting 'Invoice Close
263 * Tolerance to 100%' should also be picked up
264 */
265 'CLOSED FOR INVOICE')
266 and nvl(poh.cancel_flag,'N') <> 'Y'
267 and nvl(poh.consigned_consumption_flag,'N') ='N'
268 and pol.po_line_id = poll.po_line_id
269 and not exists (Select 'billed received shipments'
270 from po_line_locations_all poll1
271 where poll1.po_line_id = pol.po_line_id
272 and ((poll1.accrue_on_receipt_flag = 'Y' and
273 poll1.quantity_received <> 0)
274 or
275 poll1.quantity_billed <> 0))
276 /* Bug 2725744. Added the condition to check for closed_code
277 * and cancel_flag for the StdPO lines.
278 */
279 and nvl(pol.closed_code,'OPEN') IN ('OPEN','CLOSED',
280 'CLOSED FOR RECEIVING',
281 /* Bug 3334043: Std.POs that are closed by setting 'Invoice Close
282 * Tolerance to 100%' should also be picked up
283 */
284 'CLOSED FOR INVOICE')
285 and nvl(pol.cancel_flag,'N') <> 'Y'
286 and nvl(poll.need_by_date,poh.creation_date) >=
287 nvl(p_date,nvl(poll.need_by_date,poh.creation_date))
288 AND poh.po_header_id = poha.po_header_id (+)
289 AND poha.latest_external_flag (+) = 'Y'
290 AND pol.order_type_lookup_code not in ('RATE', 'FIXED PRICE') -- Bug 3524527
291 order by poh.po_header_id
292 for update of poll.retroactive_date;
293
294 -- <FPJ Retroactive Price START>
295 --Bug 4176111: Add the paratemeter p_qp_license_on to conditionally enable logic to test retroactive_date
296 cursor select_all_releases(l_po_line_id number, l_retroactive_date date,p_date date, p_qp_license_on VARCHAR2) is
297 select poll.rowid,poll.line_location_id, poll.quantity,
298 poll.ship_to_organization_id, poll.ship_to_location_id,
299 poll.price_override, nvl(poll.need_by_date,sysdate),
300 por.po_release_id,
301 por.authorization_status, por.revision_num,
302 pora.revision_num
303 from po_line_locations poll,
304 po_releases_all por, -- <R12 MOAC>
305 po_releases_archive pora
306 where nvl(por.frozen_flag, 'N') = 'N'
307 and nvl(por.authorization_status, 'INCOMPLETE') IN
308 ('APPROVED', 'INCOMPLETE', 'REJECTED',
309 'REQUIRES REAPPROVAL')
310 and nvl(por.closed_code,'OPEN') <> 'FINALLY CLOSED'
311 and nvl(por.cancel_flag,'N') <> 'Y'
312 /* Bug 2725744. Added the condition to check for closed_code
313 * and cancel_flag flag for the release shipments.
314 */
315 and nvl(poll.closed_code,'OPEN') <> 'FINALLY CLOSED'
316 and nvl(poll.cancel_flag,'N') <> 'Y'
317 and por.po_release_id = poll.po_release_id
318 and poll.po_release_id is not null
319 and poll.po_line_id = l_po_line_id
320 --Bug 4176111: Only when the Advanced Pricing API is not enabled for PO, then use logic of comparing retroactive_date
321 --to rule out unneccesary release linelocations
322 and ( p_qp_license_on = 'Y' OR
323 ( p_qp_license_on = 'N' and nvl(poll.retroactive_date,sysdate) <> l_retroactive_date)) --<R12 GBPA Adv Pricing >
324 and nvl(poll.need_by_date,por.creation_date) >=
325 nvl(p_date,nvl(poll.need_by_date,por.creation_date))
326 AND POR.po_release_id = PORA.po_release_id (+)
327 AND PORA.latest_external_flag (+) = 'Y'
328 order by por.po_release_id
329 for update of poll.retroactive_date;
330
331 --Bug 4176111: Add the paratemeter p_qp_license_on to conditionally enable logic to test retroactive_date
332 cursor select_all_stdpo(l_po_line_id number, l_retroactive_date date,
333 p_date date, p_qp_license_on VARCHAR2) is
334 select pol.rowid,poll.line_location_id,pol.quantity,
335 poll.ship_to_organization_id, poll.ship_to_location_id,
336 poll.price_override, nvl(poll.need_by_date,sysdate),
337 poh.po_header_id,
338 poh.authorization_status, poh.revision_num,
339 poha.revision_num
340 from po_headers_all poh, po_lines_all pol, po_line_locations_all poll,
341 po_headers_archive_all poha, po_document_types_all_b pdt,
342 financials_system_params_all fsp
343 where pol.from_line_id = l_po_line_id
344 and poh.po_header_id = pol.po_header_id
345 and poh.org_id = fsp.org_id -- <R12 MOAC> -- Bug 3573266
346 and nvl(fsp.purch_encumbrance_flag,'N') = 'N' -- Bug 3573266
347 and poh.org_id = pdt.org_id -- <R12 MOAC> -- Bug 3573266
348 and pdt.document_type_code = 'PO' -- Bug 3573266
349 and pdt.document_subtype = 'STANDARD' -- Bug 3573266
350 and (nvl(pdt.archive_external_revision_code,'PRINT') = 'APPROVE' -- Bug 3573266
351 or (not exists (Select 'billed received shipments' -- Bug 3565522
352 from po_line_locations_all poll1
353 where poll1.po_line_id = pol.po_line_id
354 and ((poll1.accrue_on_receipt_flag = 'Y' and
355 poll1.quantity_received <> 0)
356 or
357 poll1.quantity_billed <> 0))) )
358 --Bug 4176111: Only when the Advanced Pricing API is not enabled for PO, then use logic of comparing retroactive_date
359 --to rule out unneccesary spo linelocations
360 and ( p_qp_license_on = 'Y' OR
361 ( p_qp_license_on = 'N' and nvl(poll.retroactive_date,sysdate) <> l_retroactive_date)) --<R12 GBPA Adv Pricing >
362 and poll.shipment_num = (select min(poll.shipment_num)
363 from po_line_locations_all polt
364 where polt.po_line_id=pol.po_line_id)
365 and nvl(poh.frozen_flag, 'N') = 'N'
366 and nvl(poh.authorization_status, 'INCOMPLETE') IN
367 ('APPROVED', 'INCOMPLETE', 'REJECTED',
368 'REQUIRES REAPPROVAL')
369 and nvl(poh.closed_code,'OPEN') <> 'FINALLY CLOSED'
370 and nvl(poh.cancel_flag,'N') <> 'Y'
371 and pol.po_line_id = poll.po_line_id
372 /* Bug 2725744. Added the condition to check for closed_code
373 * and cancel_flag for the StdPO lines.
374 */
375 and nvl(pol.closed_code,'OPEN') <> 'FINALLY CLOSED'
376 and nvl(pol.cancel_flag,'N') <> 'Y'
377 and nvl(poll.need_by_date,poh.creation_date) >=
378 nvl(p_date,nvl(poll.need_by_date,poh.creation_date))
379 AND poh.po_header_id = poha.po_header_id (+)
380 AND poha.latest_external_flag (+) = 'Y'
381 AND pol.order_type_lookup_code not in ('RATE', 'FIXED PRICE') -- Bug 3524527
382 order by poh.po_header_id
383 for update of poll.retroactive_date;
384 -- <FPJ Retroactive Price END>
385
386 --<R12 GBPA Adv Pricing Start >
387
388 -- Cursor for open SPOs referencing to the CPA
389 -- SQL What: Select only open execution documents refering to a CPA
390 -- SQL Why : To be retro actively priced
391
392
393 cursor select_open_contract_exec_docs(l_po_header_id number, p_date date) is
394 SELECT
395 pol.rowid,
396 poll.line_location_id,
397 pol.quantity,
398 poll.ship_to_organization_id,
399 poll.ship_to_location_id,
400 poll.price_override,
401 nvl(poll.need_by_date,sysdate),
402 poh.po_header_id,
403 poh.authorization_status,
404 poh.revision_num,
405 poha.revision_num
406 FROM po_headers_all poh,
407 po_lines_all pol,
408 po_line_locations_all poll,
409 po_headers_archive_all poha,
410 financials_system_params_all fsp
411 WHERE pol.Contract_id = l_po_header_id
412 AND pol.from_header_id IS NULL
413 AND poh.po_header_id = pol.po_header_id
414 AND poh.org_id = fsp.org_id
415 AND nvl(fsp.purch_encumbrance_flag,'N') = 'N'
416 AND poll.shipment_num =
417 (
418 SELECT
419 min(poll.shipment_num)
420 FROM po_line_locations_all polt
421 WHERE polt.po_line_id=pol.po_line_id
422 )
423 AND nvl(poh.frozen_flag, 'N') = 'N'
424 AND nvl(poh.authorization_status, 'INCOMPLETE') IN ('APPROVED', 'INCOMPLETE', 'REJECTED', 'REQUIRES REAPPROVAL')
425 AND nvl(poh.closed_code,'OPEN') IN ('OPEN','CLOSED', 'CLOSED FOR RECEIVING', 'CLOSED FOR INVOICE')
426 AND nvl(poh.cancel_flag,'N') <> 'Y'
427 AND nvl(poh.consigned_consumption_flag,'N') ='N'
428 AND pol.po_line_id = poll.po_line_id
429 AND not exists
430 (
431 SELECT
432 'billed received shipments'
433 FROM po_line_locations_all poll1
434 WHERE poll1.po_line_id = pol.po_line_id
435 AND
436 (
437 (
438 poll1.accrue_on_receipt_flag = 'Y'
439 AND poll1.quantity_received <> 0
440 )
441 OR poll1.quantity_billed <> 0
442 )
443 )
444 AND nvl(pol.closed_code,'OPEN') IN ('OPEN','CLOSED', 'CLOSED FOR RECEIVING', 'CLOSED FOR INVOICE')
445 AND nvl(pol.cancel_flag,'N') <> 'Y'
446 AND nvl(poll.need_by_date,poh.creation_date) >= nvl(p_date,nvl(poll.need_by_date,poh.creation_date))
447 AND poh.po_header_id = poha.po_header_id (+)
448 AND poha.latest_external_flag (+) = 'Y'
449 AND pol.order_type_lookup_code not in ('RATE', 'FIXED PRICE')
450 ORDER BY poh.po_header_id for UPDATE of poll.retroactive_date;
451
452
453 -- Cursor for all SPOs referencing to the CPA
454 -- SQL What: Select all execution documents refering to a CPA
455 -- SQL Why : To be retro actively priced
456
457 cursor select_all_contract_exec_docs(l_po_header_id number, p_date date) is
458 SELECT
459 pol.rowid,
460 poll.line_location_id,
461 pol.quantity,
462 poll.ship_to_organization_id,
463 poll.ship_to_location_id,
464 poll.price_override,
465 nvl(poll.need_by_date,sysdate),
466 poh.po_header_id,
467 poh.authorization_status,
468 poh.revision_num,
469 poha.revision_num
470 FROM po_headers_all poh,
471 po_lines_all pol,
472 po_line_locations_all poll,
473 po_headers_archive_all poha,
474 po_document_types_all_b pdt,
475 financials_system_params_all fsp
476 WHERE pol.Contract_id = l_po_header_id
477 AND pol.from_header_id IS NULL
478 AND poh.po_header_id = pol.po_header_id
479 AND poh.org_id = fsp.org_id
480 AND nvl(fsp.purch_encumbrance_flag,'N') = 'N'
481 AND poh.org_id = pdt.org_id
482 AND pdt.document_type_code = 'PO'
483 AND pdt.document_subtype = 'STANDARD'
484 AND
485 (
486 nvl(pdt.archive_external_revision_code,'PRINT') = 'APPROVE'
487 OR
488 (
489 not exists
490 (
491 SELECT
492 'billed received shipments'
493 FROM po_line_locations_all poll1
494 WHERE poll1.po_line_id = pol.po_line_id
495 AND
496 (
497 (
498 poll1.accrue_on_receipt_flag = 'Y'
499 AND poll1.quantity_received <> 0
500 )
501 OR poll1.quantity_billed <> 0
502 )
503 )
504 )
505 )
506 AND poll.shipment_num =
507 (
508 SELECT
509 min(poll.shipment_num)
510 FROM po_line_locations_all polt
511 WHERE polt.po_line_id=pol.po_line_id
512 )
513 AND nvl(poh.frozen_flag, 'N') = 'N'
514 AND nvl(poh.authorization_status, 'INCOMPLETE') IN ('APPROVED', 'INCOMPLETE', 'REJECTED', 'REQUIRES REAPPROVAL')
515 AND nvl(poh.closed_code,'OPEN') <> 'FINALLY CLOSED'
516 AND nvl(poh.cancel_flag,'N') <> 'Y'
517 AND pol.po_line_id = poll.po_line_id
518 AND nvl(pol.closed_code,'OPEN') <> 'FINALLY CLOSED'
519 AND nvl(pol.cancel_flag,'N') <> 'Y'
520 AND nvl(poll.need_by_date,poh.creation_date) >= nvl(p_date,nvl(poll.need_by_date,poh.creation_date))
521 AND poh.po_header_id = poha.po_header_id (+)
522 AND poha.latest_external_flag (+) = 'Y'
523 AND pol.order_type_lookup_code not in ('RATE', 'FIXED PRICE')
524 ORDER BY poh.po_header_id for UPDATE
525 of poll.retroactive_date;
526
527 --<R12 GBPA Adv Pricing End >
528
529 cursor update_ship_price is
530 SELECT row_id, new_price
531 FROM po_retroprice_gt
532 WHERE po_release_id is not null;
533
534 cursor update_line_price is
535 SELECT row_id, new_price
536 FROM po_retroprice_gt
537 WHERE po_header_id is not null;
538
539
540 x_item_category_str varchar2(2000);
541 l_sql_str varchar2(3000);
542 l_sql_str1 varchar2(3000);
543 l_sql_str2 varchar2(3000);
544 l_sql_str3 varchar2(3000);
545 l_sql_str4 varchar2(3000);
546 l_sql_str5 varchar2(3000);
547 l_sql_str6 varchar2(3000);
548 l_sql_str7 varchar2(3000);
549 l_sql_str8 varchar2(3000);
550 l_sql_str9 varchar2(3000); --<R12 GBPA Adv Pricing >
551 --Bug 4176111: declare a dynamic query string for retroactive date
552 l_retro_date_clause varchar2(3000);
553
554 l_ga_security varchar2(1);
555 l_fnd_enabled varchar2(1);
556
557 l_agreement_cur g_agreement_cur_type;
558
559
560 l_po_line_id PO_LINES_ALL.PO_LINE_ID%TYPE;
561 l_retroactive_date PO_LINES_ALL.retroactive_date%TYPE;
562 l_global_agreement_flag PO_HEADERS_ALL.global_agreement_flag%TYPE;
563
564
565
566 l_api_version CONSTANT NUMBER := 1.0;
567 l_api_name CONSTANT VARCHAR2(30) := 'MassUpdate_Releases';
568 l_progress varchar2(3);
569
570 l_row_id_table char30_table;
571 l_po_line_loc_table num_table;
572 l_quantity_table num_table;
573 l_ship_to_org_id_table num_table;
574 l_ship_to_location_id_table num_table;
575 l_old_price_override_table num_table;
576 l_po_release_id_table num_table;
577 l_po_header_id_table num_table;
578 l_po_line_id_table num_table;
579 l_rev_num_table num_table;
580 l_archived_rev_num_table num_table;
581 l_retroactive_date_table date_table;
582 l_need_by_date_table date_table;
583 l_global_agreement_flag_table char30_table;
584 l_auth_status_table char30_table;
585
586 l_po_agreement_id_table num_table; --<R12 GBPA Adv Pricing >
587
588 l_temp_new_price_table num_table;
589 l_temp_row_id_table char30_table;
590 l_module VARCHAR2(100);
591 l_retroactive_update VARCHAR2(30) := 'NEVER';
592 l_tax_failure exception;
593 l_archive_mode_rel PO_DOCUMENT_TYPES.archive_external_revision_code%TYPE;
594 l_current_org_id NUMBER;
595 l_error_message VARCHAR2(2000);
596
597 --Bug 4176111: Variable to show if Adv Pricing API is enabled for PO
598 l_qp_license VARCHAR2(30) := NULL;
599 l_qp_license_on VARCHAR2(240) := NULL;
600
601 BEGIN
602 --Bug 4176111: Set the variable based on profile value for Adv Pricing API
603 FND_PROFILE.get('QP_LICENSED_FOR_PRODUCT',l_qp_license);
604 IF (l_qp_license IS NULL OR l_qp_license <> 'PO') THEN
605 l_qp_license_on := 'N';
606 l_retro_date_clause := 'and pol.retroactive_date is not null ';
607 ELSE
608 l_qp_license_on := 'Y';
609 l_retro_date_clause := '';
610 END IF;
611
612 -- Setup for writing the concurrent logs based on
613 -- the concurrent log Profile
614 IF g_log_mode = 'Y' THEN
615 po_debug.set_file_io(TRUE);
616 ELSE
617 po_debug.set_file_io(null);
618 END IF;
619
620 PO_DEBUG.put_line('Starting the Retroactive concurrent Program');
621
622 /* Logic :
623 * Get the GA function security to check whether the user
624 * has the function security for Global agreements set up.
625 * Fetch all the blanket lines that are
626 * retroactively changed and that have releases against them. For
627 * each blanket or Global agreement lines, fetch release shipments
628 * created against the line and update the shipments with the
629 * new price.
630 */
631
632 l_module := g_log_head||l_api_name||'.'||'000'||'.';
633
634
635 -- Standard call to check for call compatibility
636
637 IF (NOT FND_API.Compatible_API_Call(l_api_version
638 ,p_api_version
639 ,l_api_name
640 ,G_PKG_NAME))
641 THEN
642 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
643 END IF;
644
645 -- Initialize API return status to success
646
647 x_return_status := FND_API.G_RET_STS_SUCCESS;
648
649 IF g_debug_stmt then
650 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
651 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,l_module,
652 'Input Parameters are as below');
653 END IF;
654 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
655 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,l_module,
656 'Vendor id: ' || p_vendor_id);
657 END IF;
658 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
659 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,l_module,
660 'Vendor Site Id: ' || p_vendor_site_id);
661 END IF;
662 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
663 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,l_module,
664 'Agreement Id: ' || p_po_header_id);
665 END IF;
666 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
667 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,l_module,
668 'Purchasing Category Structure Id: ' ||
669 p_category_struct_id);
670 END IF;
671 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
672 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,l_module,
673 'Category From: ' || p_category_from);
674 END IF;
675 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
676 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,l_module,
677 'Category To : ' || p_category_to);
678 END IF;
679 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
680 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,l_module,
681 'Item From: ' || p_item_from);
682 END IF;
683 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
684 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,l_module,
685 'Item To: ' || p_item_to);
686 END IF;
687 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
688 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,l_module,
689 'Date: ' || p_date);
690 END IF;
691 end if; /*IF g_debug_stmt*/
692
693
694 -- <FPJ Retroactive START>
695 l_retroactive_update := Get_Retro_Mode;
696 -- <FPJ Retroactive END>
697
698 PO_DEBUG.put_line('Setting the Retroactive Pricing Mode from Profile');
699 PO_DEBUG.put_line('Retro Mode :' || l_retroactive_update);
700
701 IF (l_retroactive_update = 'NEVER') THEN
702 PO_DEBUG.put_line('Retroactive Profile is set to Never or Financials patchset is not at the right level');
703 RETURN;
704 END IF;
705
706 --<ECO 4905804>Removing MANAGE GLOBAL AGREEMENTS FUNCTION
707 --Since we are removing the manage global agreement function
708 --l_ga_security should by Y</ECO 4905804>
709
710 l_ga_security := 'Y';
711
712 l_current_org_id := PO_GA_PVT.get_current_org;
713 -- Bug 3574895. Retroactively updated Releases/Std POs were not getting
714 -- communicated to the supplier. Need to set this global
715 -- variable here so that we may be able to revert back to
716 -- the current org after the org is changed for processing
717 -- Global Agreements
718 g_orig_org_id := l_current_org_id;
719 IF g_debug_stmt then
720 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
721 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,l_module,
722 'GA function Security: ' ||l_ga_security);
723 END IF;
724 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
725 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,l_module,
726 'Current OU: ' ||l_current_org_id);
727 END IF;
728
729 end if;
730
731 -- <FPJ Retroactive Price START>
732
733 -- Set Communicate Updates Flag
734 IF (p_communicate_update = 'Y') THEN
735 g_communicate_update := 'Y';
736 ELSE
737 g_communicate_update := 'N';
738 END IF;
739
740 -- <FPJ Retroactive Price END>
741 -- Bug 3565522
742 -- Get the archive mode for releases to be used later in the code
743
744 l_archive_mode_rel := PO_DOCUMENT_ARCHIVE_PVT.get_archive_mode(
745 p_doc_type => 'RELEASE',
746 p_doc_subtype => 'BLANKET');
747
748
749 /* Sql to get the blanket lines that have been retroactively
750 * changed.
751 */
752 l_module := g_log_head||l_api_name||'.'||'010'||'.';
753 l_sql_str := 'select poh.po_header_id,pol.po_line_id, pol.retroactive_date, ' ||
754 'poh.global_agreement_flag ' ||
755 'from po_headers_all poh, ' ||
756 'po_lines pol, ' ||
757 'mtl_system_items msi, ' ||
758 'financials_system_params_all fsp, ' ; -- <R12 MOAC>
759 l_sql_str1 := 'mtl_categories mca ' ||
760 'where poh.type_lookup_code = ''BLANKET'' ' ||
761 --Bug 4176111: Use a dynamic query string to change the retroactive date clause
762 l_retro_date_clause || --<R12 GBPA Adv Pricing >
763 'and pol.po_header_id = ' ;
764 l_sql_str2 := 'nvl(:p_po_header_id, pol.po_header_id) ' ||
765 'and pol.po_header_id = poh.po_header_id ' ||
766 'and nvl(pol.price_break_lookup_code '||
767 ' ,''NON CUMULATIVE'') =''NON CUMULATIVE'' ' ;
768 l_sql_str3:= 'and poh.vendor_id = :p_vendor_id ' ||
769 'and poh.vendor_site_id = ' ||
770 'nvl(:p_vendor_site_id, poh.vendor_site_id) '
771 ||'and pol.org_id = poh.org_id '||
772 'and pol.org_id = fsp.org_id '; -- <R12 MOAC>
773 l_sql_Str4 := 'and nvl(poh.authorization_status,''INCOMPLETE'') = ''APPROVED'' ' ||
774 'and nvl(poh.frozen_flag, ''N'') = ''N'' ' ||
775 'and nvl(poh.consigned_consumption_flag,''N'') =''N'' ';
776 l_sql_str5 := 'and pol.item_id = msi.INVENTORY_ITEM_ID (+) ' ||
777 'AND nvl(msi.organization_id, ' ||
778 'fsp.inventory_organization_id)= ' ||
779 'fsp.inventory_organization_id ' ||
780 'AND mca.structure_id = ';
781 l_sql_str6 := 'TO_CHAR(:p_category_struct_id) ' ||
782 'and pol.category_id = mca.category_id (+) ' ||
783 ' and (((nvl(poh.global_agreement_flag,''N'') = ''N'') ';
784 l_sql_str7 := ' and exists ' ||
785 ' (select ''has releases'' from ' ||
786 ' po_line_locations poll where '||
787 ' poll.po_line_id = pol.po_line_id '||
788 ' and poll.po_release_id is not null)) ';
789 l_sql_str8 := ' OR ' ||
790 '((nvl(poh.global_agreement_flag,''N'') = ''Y'') '||
791 ' and ' ||
792 ' (:l_ga_security = ''Y'') '||
793 ' and exists '||
794 '(select ''has stdpo'' from po_lines_all pl where '||
795 ' pl.from_line_id = pol.po_line_id))) ' ;
796 --<R12 GBPA Adv Pricing Start>
797 l_sql_str9 := ' UNION ALL '||
798 'select poh.po_header_id,NULL, ' ||
799 'NULL, poh.global_agreement_flag ' ||
800 'from po_headers poh ' ||
801 'where poh.type_lookup_code = ''CONTRACT'' ' ||
802 'and poh.po_header_id = nvl(:p_po_header_id, poh.po_header_id) ' ||
803 'and poh.vendor_id = :p_vendor_id ' ||
804 'and poh.vendor_site_id = ' ||
805 'nvl(:p_vendor_site_id, poh.vendor_site_id) '||
806 'and nvl(poh.authorization_status,''INCOMPLETE'') = ''APPROVED'' ' ||
807 'and nvl(poh.frozen_flag, ''N'') = ''N'' ' ||
808 'and nvl(poh.consigned_consumption_flag,''N'') =''N'' '||
809 'and exists ' ||
810 ' ( SELECT ''has stdpo'' FROM po_lines_all pl '||
811 ' WHERE pl.contract_id = poh.po_header_id ) ';
812 --<R12 GBPA Adv Pricing End>
813
814
815 /* Dynamically build the item cursor*/
816
817 l_module := g_log_head||l_api_name||
818 '.'||'020'||'.';
819 PO_RETROACTIVE_PRICING_PVT.Build_Item_Cursor
820 ( p_cat_structure_id => p_category_struct_id
821 , p_cat_from => p_category_from
822 , p_cat_to => p_category_to
823 , p_item_from => p_item_from
824 , p_item_to => p_item_to
825 , x_item_cursor => x_item_category_str
826 );
827 l_sql_str := l_sql_str ||l_sql_str1||l_sql_str2||
828 l_sql_str3||l_sql_str4||l_sql_str5||
829 l_sql_str6||l_sql_str7 ||l_sql_str8 ||
830 x_item_category_str ||
831 l_sql_str9 ; --<R12 GBPA Adv Pricing >
832
833 --' order by pol.po_line_id '; --<R12 GBPA Adv Pricing >
834
835 l_module := g_log_head||l_api_name||'.'||'030'||'.';
836
837 IF g_debug_stmt then
838 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
839 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,l_sql_str);
840 END IF;
841 END IF;
842
843 -- bug2935437
844 -- call a procedure to do all var binding and l_agreement_cur opening
845
846 PO_DEBUG.put_line('Getting all the Agreements ');
847 open_agreement_cur(p_sql_str => l_sql_str,
848 p_po_header_id => p_po_header_id,
849 p_vendor_id => p_vendor_id,
850 p_vendor_site_id => p_vendor_site_id,
851 p_category_struct_id => p_category_struct_id,
852 p_ga_security => l_ga_security,
853 p_item_from => p_item_from,
854 p_item_to => p_item_to,
855 p_cat_from => p_category_from,
856 p_cat_to => p_category_to,
857 x_cursor => l_agreement_cur);
858
859
860 /*
861 OPEN l_blanket_line_cur FOR l_sql_str using p_po_header_id,
862 p_vendor_id, p_vendor_site_id,
863 p_category_struct_id,l_ga_security;
864 */
865
866 -- bug2935437 end
867
868 LOOP
869
870
871 --R12 GBPA Adv Pricing: Removed the 9.0 Database check
872 FETCH l_agreement_cur BULK COLLECT INTO
873 l_po_agreement_id_table,l_po_line_id_table,
874 l_retroactive_date_table, l_global_agreement_flag_table
875 LIMIT G_BULK_LIMIT;
876
877
878
879 IF l_po_agreement_id_table.COUNT = 0 THEN
880 l_error_message := 'Did not find any agreements to process. '||
881 'Make sure that the Cumulative Flag on the Blanket '||
882 'Price Breaks is set to OFF. Retro pricing does not '||
883 'work with cumulative price breaks.';
884 PO_DEBUG.put_line(l_error_message);
885 IF g_debug_stmt then
886 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
887 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module, l_error_message);
888 END IF;
889 END IF;
890 END IF;
891
892 if l_po_agreement_id_table.COUNT <> 0 then
893
894 for i in l_po_agreement_id_table.FIRST..l_po_agreement_id_table.LAST LOOP
895
896 IF g_debug_stmt then
897 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
898 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,l_module,
899 'l_po_agreement_id_table( ' || i || ')' ||
900 l_po_agreement_id_table(i));
901 END IF;
902 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
903 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,l_module,
904 'l_po_line_id_table( ' || i || ')' ||
905 l_po_line_id_table(i));
906 END IF;
907 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
908 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,l_module,
909 'l_retroactive_date_table( ' || i || ')' ||
910 l_retroactive_date_table(i));
911 END IF;
912 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
913 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,l_module,
914 'l_global_agreement_flag_table( ' || i ||
915 ')' || l_global_agreement_flag_table(i));
916 END IF;
917 end if;
918
919 l_module := g_log_head||l_api_name||'.'||'040'||'.';
920
921 g_index := 0;
922 g_exclude_index := 0;
923 g_exclude_row_id_table.delete;
924 g_row_id_table.delete;
925 g_new_price_table.delete;
926 g_po_header_id_table.delete;
927 g_po_release_id_table.delete;
928 g_archived_rev_num_table.delete;
929 g_auth_status_table.delete;
930
931 if l_po_line_id_table(i) IS NOT NULL then -- Blankets
932
933 if (l_global_agreement_flag_table(i) = 'Y') then
934
935 PO_DEBUG.put_line('Type of agreement being processes: Global Agreement');
936
937 IF g_debug_stmt then
938 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
939 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,l_module,
940 'Global Agreement ');
941 END IF;
942 end if;
943
944 PO_DEBUG.put_line('Get all Std POs referencing the GA');
945
946 -- <FPJ Retroactive Price START>
947 IF (l_retroactive_update = 'OPEN_RELEASES') THEN
948 OPEN select_open_stdpo(l_po_line_id_table(i),
949 l_retroactive_date_table(i),
950 p_date,
951 --Bug 4176111: Pass in the variable for Adv Pricing API
952 l_qp_license_on);
953 ELSE
954 OPEN select_all_stdpo(l_po_line_id_table(i),
955 l_retroactive_date_table(i),
956 p_date,
957 --Bug 4176111: Pass in the variable for Adv Pricing API
958 l_qp_license_on);
959 END IF; /* IF (l_retroactive_update = 'OPEN_RELEASES') */
960 -- <FPJ Retroactive Price END>
961
962 loop
963
964 -- <FPJ Retroactive Price START>
965 IF (l_retroactive_update = 'OPEN_RELEASES') THEN
966 FETCH select_open_stdpo BULK COLLECT INTO
967 l_row_id_table,
968 l_po_line_loc_table,
969 l_quantity_table,
970 l_ship_to_org_id_table,
971 l_ship_to_location_id_table,
972 l_old_price_override_table,
973 l_need_by_date_table,
974 l_po_header_id_table,
975 l_auth_status_table,
976 l_rev_num_table,
977 l_archived_rev_num_table
978 LIMIT G_BULK_LIMIT;
979 ELSE
980 FETCH select_all_stdpo BULK COLLECT INTO
981 l_row_id_table,
982 l_po_line_loc_table,
983 l_quantity_table,
984 l_ship_to_org_id_table,
985 l_ship_to_location_id_table,
986 l_old_price_override_table,
987 l_need_by_date_table,
988 l_po_header_id_table,
989 l_auth_status_table,
990 l_rev_num_table,
991 l_archived_rev_num_table
992 LIMIT G_BULK_LIMIT;
993 END IF; /* IF (l_retroactive_update = 'OPEN_RELEASES') */
994 -- <FPJ Retroactive Price END>
995
996 IF l_po_header_id_table.COUNT = 0 THEN
997 PO_DEBUG.put_line('Did not find any Std POs');
998 PO_DEBUG.put_line('Check for encumbrance setup and Archive mode in the PO creation OU');
999 PO_DEBUG.put_line('Retroactive Pricing is not supported in encumbered OUs ');
1000 PO_DEBUG.put_line('Retroactive Pricing is not supported in OU with archive set to communicate');
1001 END IF;
1002
1003 if l_po_header_id_table.COUNT <> 0 then
1004
1005 for j in l_po_header_id_table.FIRST..l_po_header_id_table.LAST LOOP
1006 IF g_debug_stmt then
1007 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1008 /* Bug 2716741.
1009 * All the values that were printed below
1010 * were using the index from the outer
1011 * loop. It was using i when the index
1012 * should be using j for the inner index.
1013 * This was causing a no_data_found error
1014 * when trying to write the log.
1015 */
1016 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1017 l_module,'l_row_id_table('
1018 || j || ')' ||
1019 l_row_id_table(j));
1020 END IF;
1021 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1022 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1023 l_module,'l_po_line_loc_table('
1024 || j || ')' ||
1025 l_po_line_loc_table(j));
1026 END IF;
1027 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1028 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1029 l_module,'l_quantity_table('
1030 || j || ')' ||
1031 l_quantity_table(j));
1032 END IF;
1033 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1034 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1035 l_module,'l_ship_to_org_id_table('
1036 || j || ')' ||
1037 l_ship_to_org_id_table(j));
1038 END IF;
1039 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1040 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1041 l_module,'l_ship_to_locn_id_table('
1042 || j || ')' ||
1043 l_ship_to_location_id_table(j));
1044 END IF;
1045 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1046 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1047 l_module,'l_old_price_table('
1048 || j || ')' ||
1049 l_old_price_override_table(j));
1050 END IF;
1051 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1052 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1053 l_module,'l_need_by_date_table('
1054 || j || ')' ||
1055 l_need_by_date_table(j));
1056 END IF;
1057 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1058 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1059 l_module,'l_po_header_id_table('
1060 || j || ')' ||
1061 l_po_header_id_table(j));
1062 END IF;
1063 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1064 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1065 l_module,'l_auth_status_table('
1066 || j || ')' ||
1067 l_auth_status_table(j));
1068 END IF;
1069 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1070 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1071 l_module,'l_rev_num_table('
1072 || j || ')' ||
1073 l_rev_num_table(j));
1074 END IF;
1075 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1076 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1077 l_module,'l_arch_rev_num_table('
1078 || j || ')' ||
1079 l_archived_rev_num_table(j));
1080 END IF;
1081 end if;
1082
1083
1084 l_module := g_log_head||l_api_name||
1085 '.'||'050'||'.';
1086
1087 --<R12 GBPA Adv Pricing Changed Call to use named parameters>
1088 Process_Price_Change
1089 (p_row_id => l_row_id_table(j),
1090 p_document_id => l_po_header_id_table(j),
1091 p_po_line_location_id => l_po_line_loc_table(j),
1092 p_retroactive_date => l_retroactive_date_table(i),
1093 p_quantity => l_quantity_table(j),
1094 p_ship_to_organization_id => l_ship_to_org_id_table(j),
1095 p_ship_to_location_id => l_ship_to_location_id_table(j),
1096 p_po_line_id => l_po_line_id_table(i),
1097 p_old_price_override => l_old_price_override_table(j),
1098 p_need_by_date => l_need_by_date_table(j),
1099 p_global_agreement_flag => l_global_agreement_flag_table(i),
1100 p_authorization_status => l_auth_status_table(j),
1101 p_rev_num => l_rev_num_table(j),
1102 p_archived_rev_num => l_archived_rev_num_table(j),
1103 p_contract_id => NULL
1104 );
1105
1106 end loop; /*l_po_header_id_table.FIRST.*/
1107 /* This retroactive_date is later used when
1108 * we run the Concurrent program again. We
1109 * will not be selecting these Std PO
1110 * shipments whose retroactive_date is
1111 * greater than the retroactive_date in
1112 * po_lines. This means that this PO
1113 * shipment was processed after the blanket
1114 * line was changed.
1115 */
1116 -- Bulk Update
1117 l_module := g_log_head||l_api_name||'.'||
1118 '060'||'.';
1119 /* Bug 2718565.
1120 * We need to update po_line_locations_all with
1121 * time stamp since we process the Std POs
1122 * against GA in other operating units.
1123 */
1124 FORALL processed_index in
1125 1..l_po_line_loc_table.COUNT
1126 UPDATE po_line_locations_all
1127 SET retroactive_date= l_retroactive_date_table(i),
1128 last_update_date = g_sysdate,
1129 last_updated_by = g_user_id
1130 WHERE line_location_id =
1131 l_po_line_loc_table(processed_index);
1132 end if; /* l_po_header_id_table.COUNT <> 0 */
1133 -- <FPJ Retroactive Price START>
1134 IF (l_retroactive_update = 'OPEN_RELEASES') THEN
1135 EXIT WHEN select_open_stdpo%NOTFOUND;
1136 ELSE
1137 EXIT WHEN select_all_stdpo%NOTFOUND;
1138 END IF; /* IF (l_retroactive_update = 'OPEN_RELEASES') */
1139 -- <FPJ Retroactive Price END>
1140
1141 end loop; /*select_stdpo */
1142
1143 -- <FPJ Retroactive Price START>
1144 IF (l_retroactive_update = 'OPEN_RELEASES') THEN
1145 CLOSE select_open_stdpo;
1146 ELSE
1147 CLOSE select_all_stdpo;
1148 END IF; /* IF (l_retroactive_update = 'OPEN_RELEASES') */
1149 -- <FPJ Retroactive Price END>
1150
1151 PO_DEBUG.put_line('Completed Processsing of Std POs ');
1152 PO_DEBUG.put_line('If price did not change - Check for encumbrance setup and
1153 Archive mode in the PO creation OU');
1154 PO_DEBUG.put_line('Retroactive Pricing is not supported in encumbered OUs ');
1155 PO_DEBUG.put_line('Retroactive Pricing of invoiced/Received releases is not supported in OU
1156 with archive set to communicate');
1157
1158 else
1159
1160 PO_DEBUG.put_line('Type of agreement being processed : Blanket Agreement');
1161
1162 IF g_debug_stmt THEN
1163 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1164 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,l_module,'Blanket Agreement');
1165 END IF;
1166 END IF;
1167
1168 -- Bug 3573266: Do not continue if the PO encumbrance in the current
1169 -- OU is ON
1170 IF (PO_CORE_S.is_encumbrance_on(p_doc_type => 'RELEASE',
1171 p_org_id => l_current_org_id))
1172 THEN
1173 PO_DEBUG.put_line('Retroactive Pricing is not supported in encumbered OUs ');
1174 IF g_debug_stmt THEN
1175 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1176 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,l_module,'Encumbrance ON');
1177 END IF;
1178 END IF;
1179
1180 EXIT; -- exit out of blanket line cursor loop
1181 END IF;
1182
1183 -- <FPJ Retroactive Price START>
1184 IF (l_retroactive_update = 'OPEN_RELEASES')OR
1185 (l_retroactive_update = 'ALL_RELEASES' AND
1186 l_archive_mode_rel <> 'APPROVE') -- Bug 3565522
1187 THEN
1188
1189 PO_DEBUG.put_line('Getting all open releases');
1190 PO_DEBUG.put_line('Profile is set to OPEN RELEASES or archive mode is set to communicate');
1191
1192 OPEN select_open_releases(l_po_line_id_table(i),
1193 l_retroactive_date_table(i),
1194 p_date,
1195 --Bug 4176111: Pass in the variable for Adv Pricing API
1196 l_qp_license_on);
1197 ELSE
1198 PO_DEBUG.put_line('Getting all releases including invoiced and received');
1199
1200 OPEN select_all_releases( l_po_line_id_table(i),
1201 l_retroactive_date_table(i),
1202 p_date,
1203 --Bug 4176111: Pass in the variable for Adv Pricing API
1204 l_qp_license_on);
1205 END IF; /* IF (l_retroactive_update = 'OPEN_RELEASES') */
1206 -- <FPJ Retroactive Price END>
1207 loop
1208
1209 -- <FPJ Retroactive Price START>
1210 IF (l_retroactive_update = 'OPEN_RELEASES')OR
1211 (l_retroactive_update = 'ALL_RELEASES' AND
1212 l_archive_mode_rel <> 'APPROVE') -- Bug 3565522
1213 THEN
1214 FETCH select_open_releases BULK COLLECT INTO
1215 l_row_id_table,
1216 l_po_line_loc_table,
1217 l_quantity_table,
1218 l_ship_to_org_id_table,
1219 l_ship_to_location_id_table,
1220 l_old_price_override_table,
1221 l_need_by_date_table,
1222 l_po_release_id_table,
1223 l_auth_status_table,
1224 l_rev_num_table,
1225 l_archived_rev_num_table
1226 LIMIT G_BULK_LIMIT;
1227 ELSE
1228 FETCH select_all_releases BULK COLLECT INTO
1229 l_row_id_table,
1230 l_po_line_loc_table,
1231 l_quantity_table,
1232 l_ship_to_org_id_table,
1233 l_ship_to_location_id_table,
1234 l_old_price_override_table,
1235 l_need_by_date_table,
1236 l_po_release_id_table,
1237 l_auth_status_table,
1238 l_rev_num_table,
1239 l_archived_rev_num_table
1240 LIMIT G_BULK_LIMIT;
1241 END IF; /* IF (l_retroactive_update = 'OPEN_RELEASES') */
1242 -- <FPJ Retroactive Price END>
1243
1244 if l_po_release_id_table.COUNT <> 0 then
1245
1246 for j in l_po_release_id_table.FIRST..l_po_release_id_table.LAST LOOP
1247 IF g_debug_stmt then
1248 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1249 /* Bug 2716741.
1250 * All the values that were printed below
1251 * were using the index from the outer
1252 * loop. It was using i when the index
1253 * should be using j for the inner index.
1254 * This was causing a no_data_found error
1255 * when trying to write the log.
1256 */
1257 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1258 l_module,'l_row_id_table('
1259 || j || ')' ||
1260 l_row_id_table(j));
1261 END IF;
1262 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1263 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1264 l_module,'l_po_line_loc_table('
1265 || j || ')' ||
1266 l_po_line_loc_table(j));
1267 END IF;
1268 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1269 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1270 l_module,'l_quantity_table('
1271 || j || ')' ||
1272 l_quantity_table(j));
1273 END IF;
1274 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1275 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1276 l_module,'l_ship_to_org_id_table('
1277 || j || ')' ||
1278 l_ship_to_org_id_table(j));
1279 END IF;
1280 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1281 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1282 l_module,'l_ship_to_locn_id_table('
1283 || j || ')' ||
1284 l_ship_to_location_id_table(j));
1285 END IF;
1286 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1287 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1288 l_module,'l_old_price_table('
1289 || j || ')' ||
1290 l_old_price_override_table(j));
1291 END IF;
1292 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1293 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1294 l_module,'l_need_by_date_table('
1295 || j || ')' ||
1296 l_need_by_date_table(j));
1297 END IF;
1298 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1299 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1300 l_module,'l_po_release_id_table('
1301 || j || ')' ||
1302 l_po_release_id_table(j));
1303 END IF;
1304 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1305 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1306 l_module,'l_auth_status_table('
1307 || j || ')' ||
1308 l_auth_status_table(j));
1309 END IF;
1310 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1311 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1312 l_module,'l_rev_num_table('
1313 || j || ')' ||
1314 l_rev_num_table(j));
1315 END IF;
1316 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1317 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1318 l_module,'l_arch_rev_num_table('
1319 || j || ')' ||
1320 l_archived_rev_num_table(j));
1321 END IF;
1322 end if;
1323
1324 l_module := g_log_head||l_api_name||'.'||
1325 '070'||'.';
1326 --<R12 GBPA Adv Pricing Changed Call to use named parameters>
1327
1328 Process_Price_Change
1329 (p_row_id => l_row_id_table(j),
1330 p_document_id => l_po_release_id_table(j),
1331 p_po_line_location_id => l_po_line_loc_table(j),
1332 p_retroactive_date => l_retroactive_date_table(i),
1333 p_quantity => l_quantity_table(j),
1334 p_ship_to_organization_id => l_ship_to_org_id_table(j),
1335 p_ship_to_location_id => l_ship_to_location_id_table(j),
1336 p_po_line_id => l_po_line_id_table(i),
1337 p_old_price_override => l_old_price_override_table(j),
1338 p_need_by_date => l_need_by_date_table(j),
1339 p_global_agreement_flag => l_global_agreement_flag_table(i),
1340 p_authorization_status => l_auth_status_table(j),
1341 p_rev_num => l_rev_num_table(j),
1342 p_archived_rev_num => l_archived_rev_num_table(j),
1343 p_contract_id => NULL
1344 );
1345
1346
1347 end loop; /*l_po_release_id_table.FIRST.*/
1348
1349
1350 l_module := g_log_head||l_api_name||'.'||
1351 '080'||'.';
1352 /* This retroactive_date is later used when
1353 * we run the Concurrent program again. We
1354 * will not be selecting these Std PO
1355 * shipments whose retroactive_date is
1356 * greater than the retroactive_date in
1357 * po_lines. This means that this PO
1358 * shipment was processed after the blanket
1359 * line was changed.
1360 */
1361 -- Bulk Update
1362 FORALL processed_index in
1363 1..l_po_line_loc_table.COUNT
1364 UPDATE po_line_locations
1365 SET retroactive_date= l_retroactive_date_table(i),
1366 last_update_date = g_sysdate,
1367 last_updated_by = g_user_id
1368 WHERE line_location_id =
1369 l_po_line_loc_table(processed_index);
1370
1371 -- Bug 3339149
1372 -- Remove the retroactive date for all the rows that
1373 -- were excluded for the invalid adj account
1374 FORALL exclude_index in
1375 1..g_exclude_row_id_table.COUNT
1376 UPDATE po_line_locations
1377 SET retroactive_date = null,
1378 last_update_date = g_sysdate,
1379 last_updated_by = g_user_id
1380 WHERE rowid = g_exclude_row_id_table(exclude_index);
1381
1382 end if; /* l_po_release_id_table.COUNT <> 0 */
1383
1384 -- <FPJ Retroactive Price START>
1385 IF (l_retroactive_update = 'OPEN_RELEASES')OR
1386 (l_retroactive_update = 'ALL_RELEASES' AND
1387 l_archive_mode_rel <> 'APPROVE') -- Bug 3565522
1388 THEN
1389 EXIT WHEN select_open_releases%NOTFOUND;
1390 ELSE
1391 EXIT WHEN select_all_releases%NOTFOUND;
1392 END IF; /* IF (l_retroactive_update = 'OPEN_RELEASES') */
1393 -- <FPJ Retroactive Price END>
1394
1395 end loop;/*select_releases*/
1396
1397 -- <FPJ Retroactive Price START>
1398 IF (l_retroactive_update = 'OPEN_RELEASES')OR
1399 (l_retroactive_update = 'ALL_RELEASES' AND
1400 l_archive_mode_rel <> 'APPROVE') -- Bug 3565522
1401 THEN
1402 CLOSE select_open_releases;
1403 ELSE
1404 CLOSE select_all_releases;
1405 END IF; /* IF (l_retroactive_update = 'OPEN_RELEASES') */
1406 -- <FPJ Retroactive Price END>
1407
1408 PO_DEBUG.put_line('Completed Processing blanket releases ');
1409
1410 end if; /*l_global_agreement_flag = 'Y' */
1411
1412 --<R12 GBPA Adv Pricing Start >
1413 else -- Contracts
1414
1415 IF g_debug_stmt THEN
1416 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1417 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,l_module,'contract Agreement');
1418 END IF;
1419 END IF;
1420
1421 IF (l_retroactive_update = 'OPEN_RELEASES') THEN
1422 OPEN select_open_contract_exec_docs(l_po_agreement_id_table(i),
1423 p_date);
1424 ELSE
1425 OPEN select_all_contract_exec_docs(l_po_agreement_id_table(i),
1426 p_date);
1427 END IF;
1428
1429 LOOP
1430
1431
1432 IF (l_retroactive_update = 'OPEN_RELEASES') THEN
1433 FETCH select_open_contract_exec_docs BULK COLLECT INTO
1434 l_row_id_table,
1435 l_po_line_loc_table,
1436 l_quantity_table,
1437 l_ship_to_org_id_table,
1438 l_ship_to_location_id_table,
1439 l_old_price_override_table,
1440 l_need_by_date_table,
1441 l_po_header_id_table,
1442 l_auth_status_table,
1443 l_rev_num_table,
1444 l_archived_rev_num_table
1445 LIMIT G_BULK_LIMIT;
1446 ELSE
1447 FETCH select_all_contract_exec_docs BULK COLLECT INTO
1448 l_row_id_table,
1449 l_po_line_loc_table,
1450 l_quantity_table,
1451 l_ship_to_org_id_table,
1452 l_ship_to_location_id_table,
1453 l_old_price_override_table,
1454 l_need_by_date_table,
1455 l_po_header_id_table,
1456 l_auth_status_table,
1457 l_rev_num_table,
1458 l_archived_rev_num_table
1459 LIMIT G_BULK_LIMIT;
1460 END IF;
1461
1462 IF l_po_header_id_table.COUNT = 0 THEN
1463 PO_DEBUG.put_line('Did not find any Std POs');
1464 PO_DEBUG.put_line('Check for encumbrance setup and Archive mode in the PO creation OU');
1465 PO_DEBUG.put_line('Retroactive Pricing is not supported in encumbered OUs ');
1466 PO_DEBUG.put_line('Retroactive Pricing is not supported in OU with archive set to communicate');
1467 END IF;
1468
1469 if l_po_header_id_table.COUNT <> 0 then
1470
1471 for j in l_po_header_id_table.FIRST..l_po_header_id_table.LAST LOOP
1472
1473 IF g_debug_stmt then
1474 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1475 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1476 l_module,'l_row_id_table(' || j || ')' || l_row_id_table(j));
1477 END IF;
1478 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1479 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1480 l_module,'l_po_line_loc_table(' || j || ')' || l_po_line_loc_table(j));
1481 END IF;
1482 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1483 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1484 l_module,'l_quantity_table(' || j || ')' || l_quantity_table(j));
1485 END IF;
1486 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1487 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1488 l_module,'l_ship_to_org_id_table(' || j || ')' || l_ship_to_org_id_table(j));
1489 END IF;
1490 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1491 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1492 l_module,'l_ship_to_locn_id_table(' || j || ')' || l_ship_to_location_id_table(j));
1493 END IF;
1494 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1495 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1496 l_module,'l_old_price_table(' || j || ')' || l_old_price_override_table(j));
1497 END IF;
1498 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1499 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1500 l_module,'l_need_by_date_table(' || j || ')' || l_need_by_date_table(j));
1501 END IF;
1502 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1503 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1504 l_module,'l_po_header_id_table(' || j || ')' || l_po_header_id_table(j));
1505 END IF;
1506 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1507 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1508 l_module,'l_auth_status_table(' || j || ')' || l_auth_status_table(j));
1509 END IF;
1510 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1511 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1512 l_module,'l_rev_num_table(' || j || ')' || l_rev_num_table(j));
1513 END IF;
1514 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1515 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1516 l_module,'l_arch_rev_num_table(' || j || ')' || l_archived_rev_num_table(j));
1517 END IF;
1518 end if;
1519
1520
1521 l_module := g_log_head||l_api_name||
1522 '.'||'084'||'.';
1523 --<R12 GBPA Adv Pricingi Changed Call to use named Parameters >
1524 Process_Price_Change
1525 (p_row_id => l_row_id_table(j),
1526 p_document_id => l_po_header_id_table(j),
1527 p_po_line_location_id => l_po_line_loc_table(j),
1528 p_retroactive_date => l_retroactive_date_table(i),
1529 p_quantity => l_quantity_table(j),
1530 p_ship_to_organization_id => l_ship_to_org_id_table(j),
1531 p_ship_to_location_id => l_ship_to_location_id_table(j),
1532 p_po_line_id => l_po_line_id_table(i),
1533 p_old_price_override => l_old_price_override_table(j),
1534 p_need_by_date => l_need_by_date_table(j),
1535 p_global_agreement_flag => l_global_agreement_flag_table(i),
1536 p_authorization_status => l_auth_status_table(j),
1537 p_rev_num => l_rev_num_table(j),
1538 p_archived_rev_num => l_archived_rev_num_table(j),
1539 p_contract_id => l_po_agreement_id_table(i)
1540 );
1541
1542 end loop; /*l_po_header_id_table.FIRST.*/
1543
1544 -- Bulk Update
1545 l_module := g_log_head||l_api_name||'.'||
1546 '088'||'.';
1547
1548 FORALL processed_index in
1549 1..l_po_line_loc_table.COUNT
1550 UPDATE po_line_locations_all
1551 SET retroactive_date= l_retroactive_date_table(i),
1552 last_update_date = g_sysdate,
1553 last_updated_by = g_user_id
1554 WHERE line_location_id =
1555 l_po_line_loc_table(processed_index);
1556 end if; /* l_po_header_id_table.COUNT <> 0 */
1557
1558 IF (l_retroactive_update = 'OPEN_RELEASES') THEN
1559 EXIT WHEN select_open_contract_exec_docs%NOTFOUND;
1560 ELSE
1561 EXIT WHEN select_all_contract_exec_docs%NOTFOUND;
1562 END IF; /* IF (l_retroactive_update = 'OPEN_RELEASES') */
1563
1564 end loop; /*select_stdpo */
1565
1566 IF (l_retroactive_update = 'OPEN_RELEASES') THEN
1567 CLOSE select_open_contract_exec_docs;
1568 ELSE
1569 CLOSE select_all_contract_exec_docs;
1570 END IF; /* IF (l_retroactive_update = 'OPEN_RELEASES') */
1571
1572 PO_DEBUG.put_line('Completed Processsing of Std POs ');
1573 PO_DEBUG.put_line('If price did not change - Check for encumbrance setup and
1574 Archive mode in the PO creation OU');
1575 PO_DEBUG.put_line('Retroactive Pricing is not supported in encumbered OUs ');
1576 PO_DEBUG.put_line('Retroactive Pricing of invoiced/Received releases is not supported in OU with archive set to communicate');
1577 --<R12 GBPA Adv Pricing End>
1578
1579 END IF; /* l_po_line_id_table(i) IS NOT NULL */
1580
1581 /* Insert the values in the temp table po_retroprice_gt
1582 * for each Blanket Line that we process.
1583 */
1584 l_module := g_log_head||l_api_name||'.'||
1585 '090'||'.';
1586 FORALL insert_index in 1..g_row_id_table.COUNT
1587 INSERT into po_retroprice_gt(
1588 row_id,
1589 new_price,
1590 po_header_id,
1591 po_release_id,
1592 archived_revision_num,
1593 authorization_status)
1594 VALUES(
1595 g_row_id_table(insert_index),
1596 g_new_price_table(insert_index),
1597 g_po_header_id_table(insert_index),
1598 g_po_release_id_table(insert_index),
1599 g_archived_rev_num_table(insert_index),
1600 g_auth_status_table(insert_index));
1601
1602
1603 end loop;/*l_po_line_id_table.FIRST */
1604
1605 end if;/*l_po_line_id_table.COUNT <> 0 */
1606
1607 exit when l_agreement_cur%notfound;
1608
1609 END LOOP; /* l_agreement_cur */
1610
1611 close l_agreement_cur;
1612
1613 /* Update PO shipments with the new Price */
1614 -- Bulk Select
1615 l_module := g_log_head||l_api_name||'.'||'100'||'.';
1616
1617 OPEN update_ship_price;
1618 LOOP
1619 fetch update_ship_price BULK COLLECT INTO
1620 l_temp_row_id_table,
1621 l_temp_new_price_table
1622 LIMIT G_BULK_LIMIT;
1623
1624 l_module := g_log_head||l_api_name||'.'||'110'||'.';
1625 if l_temp_row_id_table.COUNT <> 0 then
1626 FORALL price_update_index in 1..l_temp_row_id_table.COUNT
1627 UPDATE po_line_locations_all
1628 SET price_override =
1629 l_temp_new_price_table(price_update_index),
1630 calculate_tax_flag = 'Y',
1631 manual_price_change_flag = 'N', --<MANUAL PRICE OVERRIDE FPJ>
1632 last_update_date = g_sysdate,
1633 last_updated_by = g_user_id,
1634 --<R12 eTax Integration Start>
1635 tax_attribute_update_code =
1636 NVL(tax_attribute_update_code,'UPDATE')
1637 --<R12 eTax Integration End>
1638 WHERE rowid =
1639 l_temp_row_id_table(price_update_index);
1640 end if; /*l_temp_row_id_table.COUNT <> 0 */
1641 exit when update_ship_price%notfound;
1642
1643 END LOOP;
1644 CLOSE update_ship_price; /* 2857628 Close the cursor */
1645
1646 l_module := g_log_head||l_api_name||'.'||'120'||'.';
1647
1648 OPEN update_line_price;
1649 LOOP
1650 fetch update_line_price BULK COLLECT INTO
1651 l_temp_row_id_table,
1652 l_temp_new_price_table
1653 LIMIT G_BULK_LIMIT;
1654
1655 l_module := g_log_head||l_api_name||'.'||'110'||'.';
1656 if l_temp_row_id_table.COUNT <> 0 then
1657 FORALL price_update_index in 1..l_temp_row_id_table.COUNT
1658 UPDATE po_lines_all
1659 SET unit_price =
1660 l_temp_new_price_table(price_update_index),
1661 manual_price_change_flag = 'N', --<MANUAL PRICE OVERRIDE FPJ>
1662 last_update_date = g_sysdate,
1663 last_updated_by = g_user_id,
1664 --<R12 eTax Integration Start>
1665 tax_attribute_update_code =
1666 NVL(tax_attribute_update_code,'UPDATE')
1667 --<R12 eTax Integration End>
1668 WHERE rowid =
1669 l_temp_row_id_table(price_update_index);
1670
1671 FORALL price_update_index in 1..l_temp_row_id_table.COUNT
1672 UPDATE po_line_locations_all poll
1673 SET poll.price_override =
1674 l_temp_new_price_table(price_update_index),
1675 poll.calculate_tax_flag = 'Y',
1676 poll.last_update_date = g_sysdate,
1677 poll.last_updated_by = g_user_id,
1678 --<R12 eTax Integration Start>
1679 tax_attribute_update_code =
1680 NVL(tax_attribute_update_code,'UPDATE')
1681 --<R12 eTax Integration End>
1682 WHERE poll.po_line_id =
1683 (select pll.po_line_id
1684 from po_lines_all pll where
1685 rowid=l_temp_row_id_table(price_update_index));
1686
1687 end if; /*l_temp_row_id_table.COUNT <> 0 */
1688 exit when update_line_price%notfound;
1689
1690 END LOOP;
1691 CLOSE update_line_price; /* 2857628 Close the cursor */
1692
1693 l_module := g_log_head||l_api_name||'.'||'130'||'.';
1694 g_po_release_id_table.delete;
1695 g_po_header_id_table.delete;
1696
1697 PO_RETROACTIVE_PRICING_PVT.WrapUp_Standard_PO;
1698
1699
1700 l_module := g_log_head||l_api_name||'.'||'140'||'.';
1701
1702 PO_RETROACTIVE_PRICING_PVT.WrapUp_Releases;
1703
1704 COMMIT;
1705
1706 l_module := g_log_head||l_api_name||'.'||'150'||'.';
1707
1708 PO_RETROACTIVE_PRICING_PVT.Launch_PO_Approval;
1709
1710 l_module := g_log_head||l_api_name||'.'||'160'||'.';
1711 PO_RETROACTIVE_PRICING_PVT.Launch_REL_Approval;
1712
1713 PO_DEBUG.put_line('End of Retroactive Pricing Program');
1714
1715 EXCEPTION
1716 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1717 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1718 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
1719 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
1720 l_module,SQLERRM(SQLCODE));
1721 END IF;
1722 ROLLBACK;
1723 when l_tax_failure then
1724 x_return_status := FND_API.G_RET_STS_ERROR;
1725 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
1726 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
1727 l_module,SQLERRM(SQLCODE));
1728 END IF;
1729 ROLLBACK;
1730 when no_data_found then
1731 x_return_status := FND_API.G_RET_STS_ERROR;
1732 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
1733 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
1734 l_module,SQLERRM(SQLCODE));
1735 END IF;
1736 ROLLBACK;
1737 when others then
1738 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1739 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
1740 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
1741 l_module,SQLERRM(SQLCODE));
1742 END IF;
1743 /* Bug 2857628 START */
1744 if (select_open_releases%ISOPEN) then
1745 close select_open_releases;
1746 end if;
1747 if (select_open_stdpo%ISOPEN) then
1748 close select_open_stdpo;
1749 end if;
1750 /* Bug 2857628 END*/
1751
1752 -- <FPJ Retroactive Price START>
1753 IF (select_all_stdpo%ISOPEN) THEN
1754 CLOSE select_all_stdpo;
1755 END IF;
1756 IF (select_all_releases%ISOPEN) THEN
1757 CLOSE select_all_releases;
1758 END IF;
1759 -- <FPJ Retroactive Price END>
1760
1761 ROLLBACK;
1762
1763 END MASSUPDATE_RELEASES;
1764
1765
1766 /**
1767 * Private Procedure: Build_Item_Cursor
1768 * Effects: This procedure builds the item cursor statement.
1769 * This statement needs to be built at run time (dynamic SQL)
1770 * because of the dynamic nature of the System Item and
1771 * Category flexfields. This is called from massupdate_releases.
1772 * Returns: x_item_cursor - Sql string which contains the WHERE clause
1773 * to be used in getting the blanket line that is retroactively
1774 * changed.
1775 */
1776
1777
1778 PROCEDURE Build_Item_Cursor
1779 ( p_cat_structure_id IN NUMBER
1780 , p_cat_from IN VARCHAR2
1781 , p_cat_to IN VARCHAR2
1782 , p_item_from IN VARCHAR2
1783 , p_item_to IN VARCHAR2
1784 , x_item_cursor IN OUT NOCOPY VARCHAR2
1785 )
1786 IS
1787 l_flexfield_rec FND_FLEX_KEY_API.flexfield_type;
1788 l_structure_rec FND_FLEX_KEY_API.structure_type;
1789 l_segment_rec FND_FLEX_KEY_API.segment_type;
1790 l_segment_tbl FND_FLEX_KEY_API.segment_list;
1791 l_segment_number NUMBER;
1792 l_mstk_segs VARCHAR2(850);
1793 l_mcat_segs VARCHAR2(850);
1794 l_mcat_f VARCHAR2(2000);
1795 l_mcat_w1 VARCHAR2(2000);
1796 l_mcat_w2 VARCHAR2(2000);
1797 l_mstk_w VARCHAR2(2000);
1798 l_progress VARCHAR2(3);
1799 l_module VARCHAR2(100);
1800 l_api_name CONSTANT VARCHAR2(50) := 'Build_Item_Cursor';
1801 BEGIN
1802
1803 l_module := g_log_head||l_api_name||'.'||'000'||'.';
1804
1805 FND_FLEX_KEY_API.set_session_mode('customer_data');
1806
1807 -- retrieve system item concatenated flexfield
1808 l_mstk_segs := '';
1809 l_flexfield_rec := FND_FLEX_KEY_API.find_flexfield('INV', 'MSTK');
1810 l_structure_rec := FND_FLEX_KEY_API.find_structure(l_flexfield_rec, 101);
1811 FND_FLEX_KEY_API.get_segments
1812 ( flexfield => l_flexfield_rec
1813 , structure => l_structure_rec
1814 , nsegments => l_segment_number
1815 , segments => l_segment_tbl
1816 );
1817 FOR l_idx IN 1..l_segment_number LOOP
1818 l_segment_rec := FND_FLEX_KEY_API.find_segment
1819 ( l_flexfield_rec
1820 , l_structure_rec
1821 , l_segment_tbl(l_idx)
1822 );
1823 l_mstk_segs := l_mstk_segs ||'msi.'||l_segment_rec.column_name;
1824 IF l_idx < l_segment_number THEN
1825
1826 -- bug2935437
1827 -- single quotes around segment_separator are needed
1828
1829 l_mstk_segs := l_mstk_segs|| '||' || '''' ||
1830 l_structure_rec.segment_separator || '''' || '||';
1831 END IF;
1832 END LOOP;
1833
1834
1835 -- retrieve item category concatenated flexfield
1836 l_mcat_segs := '';
1837 l_flexfield_rec := FND_FLEX_KEY_API.find_flexfield('INV', 'MCAT');
1838 l_structure_rec := FND_FLEX_KEY_API.find_structure
1839 ( l_flexfield_rec
1840 , p_cat_structure_id
1841 );
1842 FND_FLEX_KEY_API.get_segments
1843 ( flexfield => l_flexfield_rec
1844 , structure => l_structure_rec
1845 , nsegments => l_segment_number
1846 , segments => l_segment_tbl
1847 );
1848 FOR l_idx IN 1..l_segment_number LOOP
1849 l_segment_rec := FND_FLEX_KEY_API.find_segment
1850 ( l_flexfield_rec
1851 , l_structure_rec
1852 , l_segment_tbl(l_idx)
1853 );
1854 l_mcat_segs := l_mcat_segs ||'mca.'||l_segment_rec.column_name;
1855 IF l_idx < l_segment_number THEN
1856 l_mcat_segs := l_mcat_segs||'||'||''''||
1857 l_structure_rec.segment_separator||''''||'||';
1858 END IF;
1859 END LOOP;
1860
1861
1862 -- bug2935437
1863 -- Use Bind variables instead of literals
1864
1865 IF p_item_from IS NOT NULL AND p_item_to IS NOT NULL THEN
1866 l_mstk_w := ' AND '||l_mstk_segs||' BETWEEN :p_item_from'||
1867 ' AND :p_item_to';
1868 ELSIF p_item_from IS NOT NULL AND p_item_to IS NULL THEN
1869 l_mstk_w := ' AND '||l_mstk_segs||' >= :p_item_from';
1870 ELSIF p_item_from IS NULL AND p_item_to IS NOT NULL THEN
1871 l_mstk_w := ' AND '||l_mstk_segs||' <= :p_item_to';
1872 ELSE
1873 l_mstk_w := NULL;
1874 END IF;
1875 IF p_cat_from IS NOT NULL AND p_cat_to IS NOT NULL THEN
1876 l_mcat_w2 := ' AND '||l_mcat_segs||' BETWEEN :p_cat_from'||
1877 ' AND :p_cat_to';
1878 ELSIF p_cat_from IS NOT NULL AND p_cat_to IS NULL THEN
1879 l_mcat_w2 := ' AND '||l_mcat_segs||' >= :p_cat_from';
1880 ELSIF p_cat_from IS NULL AND p_cat_to IS NOT NULL THEN
1881 l_mcat_w2 := ' AND '||l_mcat_segs||' <= :p_cat_to';
1882 ELSE
1883 l_mcat_f := NULL;
1884 l_mcat_w2 := NULL;
1885 END IF;
1886
1887 -- bug2935437 end
1888
1889 x_item_cursor := l_mstk_w || l_mcat_w2;
1890 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1891 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,l_module,'x_item_cursor: ' ||
1892 x_item_cursor);
1893 END IF;
1894 exception
1895 when others then
1896 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
1897 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
1898 l_module,SQLERRM(SQLCODE));
1899 END IF;
1900
1901 raise;
1902 end BUILD_ITEM_CURSOR;
1903
1904 /**
1905 * Private Procedure: WrapUp_Releases
1906 * Modifies: authorization_Status and revision_num in po_releases.
1907 * Effects: If any release shipment is updated with the new price, then
1908 * revision_num must be incremented and authorization_Status
1909 * has to be updated to Requires approval if the status is
1910 * Approved. This is called from massupdate_releases procedure.
1911 */
1912
1913 PROCEDURE WrapUp_Releases IS
1914
1915 l_global_arch_rev_num_table num_table;
1916 l_row_id_table char30_table;
1917
1918 l_progress varchar2(3);
1919 l_module VARCHAR2(100);
1920 l_api_name CONSTANT VARCHAR2(50) := 'WrapUp_Releases';
1921 x_tax_status VARCHAR2(10);
1922 l_encode varchar2(2000);
1923 l_error_message varchar2(2000);
1924 l_tax_failure exception;
1925
1926 -- <FPJ Retroactive START>
1927 l_consigned_flag_tbl po_tbl_varchar1;
1928 -- <FPJ Retroactive END>
1929 l_return_status VARCHAR(1); --<R12 eTax Integration>
1930 begin
1931
1932 -- Setup for writing the concurrent logs based on
1933 -- the concurrent log Profile
1934 IF g_log_mode = 'Y' THEN
1935 po_debug.set_file_io(TRUE);
1936 ELSE
1937 po_debug.set_file_io(null);
1938 END IF;
1939
1940 /* Increment Document Revision */
1941 -- Bulk Select
1942 l_module := g_log_head||l_api_name||'.'||'000'||'.';
1943 SELECT distinct po_release_id,
1944 nvl(authorization_status,'INCOMPLETE'),
1945 nvl(archived_revision_num,-999)
1946 BULK COLLECT INTO
1947 g_po_release_id_table,
1948 g_rel_auth_table,
1949 l_global_arch_rev_num_table
1950 FROM po_retroprice_gt prp
1951 WHERE prp.po_release_id is not null;
1952
1953 --
1954 -- Calculate Tax for updated Releases
1955 -- insert errors into debug log if any
1956 --
1957 IF (g_po_release_id_table.COUNT > 0) THEN
1958 FOR i IN g_po_release_id_table.first..g_po_release_id_table.LAST LOOP
1959 -- <R12 eTax Integration Start>
1960 l_return_status := NULL;
1961 IF g_debug_stmt then
1962 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1963 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,l_module,
1964 'Callin Tax for Release ID ' || g_po_release_id_table(i));
1965 END IF;
1966 END IF;
1967 po_tax_interface_pvt.calculate_tax(p_po_header_id => NULL,
1968 p_po_release_id => g_po_release_id_table(i),
1969 p_calling_program => 'PO_POXRPRIB_REL',
1970 x_return_status => l_return_status);
1971 IF g_debug_stmt then
1972 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1973 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,l_module,
1974 'Callin Tax : Return Status ' ||l_return_status);
1975 END IF;
1976 END IF;
1977 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1978 RAISE l_tax_failure;
1979 END IF;
1980 -- <R12 eTax Integration End>
1981 END LOOP;
1982 END IF;
1983
1984 -- Bulk Update
1985 /* Bug 2714259.
1986 * In addition to making authorization_status to Requires Reapproval,
1987 * we need to make the approved_flag in po_headers and the last
1988 * updated columns.
1989 */
1990 -- Bug 5168776 Update the Revised Date also
1991 l_module := g_log_head||l_api_name||'.'||'010'||'.';
1992 FORALL doc_update_index in 1..g_po_release_id_table.COUNT
1993 UPDATE po_releases por
1994 SET por.revision_num = decode(por.revision_num,
1995 l_global_arch_rev_num_table(doc_update_index),
1996 por.revision_num +1,por.revision_num),
1997 por.revised_date = decode(por.revision_num,
1998 l_global_arch_rev_num_table(doc_update_index),
1999 sysdate,por.revised_date),
2000 por.authorization_status = decode(por.authorization_status,
2001 'APPROVED', 'REQUIRES REAPPROVAL',
2002 por.authorization_status),
2003 por.approved_flag = decode(por.authorization_status,
2004 'APPROVED','R',por.approved_flag),
2005 por.last_update_date = g_sysdate,
2006 por.last_updated_by = g_user_id
2007 WHERE po_release_id = g_po_release_id_table(doc_update_index);
2008
2009
2010 /* Bug 2714259.
2011 * Update approved_flag to 'R', last_update_date and
2012 * last_updated_by columns in po_line_locations for which
2013 * the price has been updated .
2014 */
2015 l_module := g_log_head||l_api_name||'.'||'020'||'.';
2016 SELECT row_id
2017 BULK COLLECT INTO
2018 l_row_id_table
2019 FROM po_retroprice_gt prp
2020 WHERE prp.po_release_id is not null
2021 and nvl(authorization_status,'INCOMPLETE') = 'APPROVED';
2022
2023 l_module := g_log_head||l_api_name||'.'||'030'||'.';
2024 FORALL release_update_index in 1..l_row_id_table.COUNT
2025 UPDATE po_line_locations poll
2026 SET poll.approved_flag = 'R',
2027 poll.last_update_date = g_sysdate,
2028 poll.last_updated_by = g_user_id
2029 WHERE rowid = l_row_id_table(release_update_index);
2030
2031 exception
2032 when l_tax_failure then
2033 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
2034 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
2035 l_module,SQLERRM(SQLCODE));
2036 END IF;
2037 raise;
2038 when no_data_found then
2039 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
2040 /* No error since there need not be any rows in temp table */
2041 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
2042 l_module,SQLERRM(SQLCODE));
2043 END IF;
2044 when others then
2045 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
2046 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
2047 l_module,SQLERRM(SQLCODE));
2048 END IF;
2049 raise;
2050
2051 end WrapUp_Releases;
2052
2053
2054
2055 /**
2056 * Private Procedure: WrapUp_Standard_PO
2057 * Modifies: authorization_Status and revision_num in po_headers.
2058 * Effects: If any release shipment is updated with the new price, then
2059 * revision_num must be incremented and authorization_Status
2060 * has to be updated to Requires approval if the status is
2061 * Approved. This is called from massupdate_releases procedure.
2062 */
2063
2064 PROCEDURE WrapUp_Standard_PO IS
2065
2066 l_global_arch_rev_num_table num_table;
2067 l_row_id_table char30_table;
2068
2069 l_progress varchar2(3);
2070 l_module VARCHAR2(100);
2071 l_api_name CONSTANT VARCHAR2(50) := 'WrapUp_Standard_PO';
2072 x_tax_status VARCHAR2(10);
2073 l_encode varchar2(2000);
2074 l_error_message varchar2(2000);
2075 l_doc_org_id number;
2076 l_tax_failure exception;
2077 l_return_status VARCHAR(1); --<R12 eTax Integration>
2078 begin
2079 -- Setup for writing the concurrent logs based on
2080 -- the concurrent log Profile
2081 IF g_log_mode = 'Y' THEN
2082 po_debug.set_file_io(TRUE);
2083 ELSE
2084 po_debug.set_file_io(null);
2085 END IF;
2086
2087 /* Increment Document Revision */
2088
2089 -- Bulk Select
2090 l_module := g_log_head||l_api_name||'.'||'000'||'.';
2091 SELECT distinct po_header_id,
2092 nvl(authorization_status,'INCOMPLETE'),
2093 archived_revision_num
2094 BULK COLLECT INTO
2095 g_po_header_id_table,
2096 g_po_auth_table,
2097 l_global_arch_rev_num_table
2098 FROM po_retroprice_gt prp
2099 WHERE prp.po_header_id is not null;
2100
2101 --
2102 -- Calculate Tax for updated Releases
2103 -- insert errors into debug log if any
2104 --
2105 IF (g_po_header_id_table.COUNT > 0) THEN
2106 FOR i IN g_po_header_id_table.first..g_po_header_id_table.LAST LOOP
2107 -- <R12 eTax Integration Start>
2108 l_return_status := NULL;
2109 IF g_debug_stmt then
2110 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2111 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,l_module,
2112 'Callin Tax : PO HEADER ID ' ||g_po_header_id_table(i));
2113 END IF;
2114 END IF;
2115 po_tax_interface_pvt.calculate_tax(p_po_header_id => g_po_header_id_table(i),
2116 p_po_release_id => NULL,
2117 p_calling_program => 'PO_POXRPRIB_PO',
2118 x_return_status => l_return_status);
2119 IF g_debug_stmt then
2120 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2121 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,l_module,
2122 'Callin Tax : Return Status ' ||l_return_status);
2123 END IF;
2124 END IF;
2125 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2126 RAISE l_tax_failure;
2127 END IF;
2128 -- <R12 eTax Integration End>
2129 END LOOP;
2130 END IF;
2131
2132 -- Bulk Update
2133 /* Bug 2714259.
2134 * In addition to making authorization_status to Requires Reapproval,
2135 * we need to make the approved_flag in po_headers and the last updated
2136 * columns.
2137 */
2138 -- Bug 5168776 Update the Revised Date also
2139 FORALL doc_update_index in 1..g_po_header_id_table.COUNT
2140 UPDATE po_headers_all poh
2141 SET poh.revision_num = decode(poh.revision_num,
2142 l_global_arch_rev_num_table(doc_update_index),
2143 poh.revision_num +1,poh.revision_num),
2144 poh.revised_date = decode(poh.revision_num,
2145 l_global_arch_rev_num_table(doc_update_index),
2146 sysdate, poh.revised_date ),
2147 poh.authorization_status = decode(poh.authorization_status,
2148 'APPROVED', 'REQUIRES REAPPROVAL',
2149 poh.authorization_status),
2150 poh.approved_flag = decode(poh.authorization_status,
2151 'APPROVED','R',poh.approved_flag),
2152 poh.last_update_date = g_sysdate,
2153 poh.last_updated_by = g_user_id
2154 WHERE po_header_id = g_po_header_id_table(doc_update_index);
2155
2156 /* Bug 2714259.
2157 * Update approved_flag to 'R', last_update_date and
2158 * last_updated_by columns in po_line_locations for which
2159 * the price has been updated .
2160 */
2161 l_module := g_log_head||l_api_name||'.'||'020'||'.';
2162 SELECT row_id
2163 BULK COLLECT INTO
2164 l_row_id_table
2165 FROM po_retroprice_gt prp
2166 WHERE prp.po_header_id is not null
2167 and nvl(authorization_status,'INCOMPLETE') = 'APPROVED';
2168
2169 l_module := g_log_head||l_api_name||'.'||'030'||'.';
2170 FORALL ship_update_index in 1..l_row_id_table.COUNT
2171 UPDATE po_line_locations_all poll
2172 SET poll.approved_flag = 'R',
2173 poll.last_update_date = g_sysdate,
2174 poll.last_updated_by = g_user_id
2175 WHERE poll.po_line_id =
2176 (select pll.po_line_id
2177 from po_lines_all pll where
2178 rowid=l_row_id_table(ship_update_index));
2179
2180 exception
2181 when l_tax_failure then
2182 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
2183 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
2184 l_module,SQLERRM(SQLCODE));
2185 END IF;
2186 raise;
2187 when no_data_found then
2188 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
2189 /* No error since there need not be any rows in the temp table*/
2190 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
2191 l_module,SQLERRM(SQLCODE));
2192 END IF;
2193 when others then
2194 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
2195 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
2196 l_module,SQLERRM(SQLCODE));
2197 END IF;
2198 raise;
2199
2200 end WrapUp_Standard_PO;
2201
2202 /**
2203 * Private Procedure: Process_Price_Change
2204 * Modifies: updates the global variables with the release_id, revision_num
2205 * from the archive table, authorization_status, row_id of the
2206 * po_line_locations to be updated and the new price if it is different
2207 * from the old price.
2208 * Effects: Get the new price for the release shipment attributes and if
2209 * different update the global variables.This is called from
2210 * massupdate_releases procedure.
2211 */
2212
2213 PROCEDURE Process_Price_Change
2214 (p_row_id IN VARCHAR2,
2215 p_document_id IN NUMBER,
2216 p_po_line_location_id IN NUMBER,
2217 p_retroactive_date IN DATE,
2218 p_quantity IN NUMBER,
2219 p_ship_to_organization_id IN NUMBER,
2220 p_ship_to_location_id IN NUMBER,
2221 p_po_line_id IN NUMBER,
2222 p_old_price_override IN NUMBER,
2223 p_need_by_date IN DATE,
2224 p_global_agreement_flag IN VARCHAR2,
2225 p_authorization_status IN VARCHAR2,
2226 p_rev_num IN Number,
2227 p_archived_rev_num IN Number,
2228 p_contract_id IN NUMBER) IS --<R12 GBPA Adv Pricing >
2229
2230 l_new_price_override number;
2231 l_cumulative_flag boolean :=FALSE;
2232 l_progress VARCHAR2(3);
2233 l_module VARCHAR2(100);
2234 l_api_name CONSTANT VARCHAR2(50) := 'Process_Price_Change';
2235 l_error_message varchar2(2000);
2236 l_std_po_price_change VARCHAR2(1);
2237 l_retroactive_update VARCHAR2(30) := 'NEVER';
2238 l_po_line_id PO_LINES_ALL.po_line_id%TYPE;
2239
2240 --<R12 GBPA Adv Pricing Start>
2241 l_quantity PO_LINES.quantity%TYPE;
2242 l_ship_to_location_id PO_LINE_LOCATIONS.ship_to_location_id%TYPE;
2243 l_ship_to_org_id PO_LINE_LOCATIONS.ship_to_organization_id%TYPE;
2244 l_need_by_date PO_LINE_LOCATIONS.need_by_date%TYPE;
2245 l_from_line_id PO_LINES.from_line_id%TYPE;
2246 l_org_id po_lines.org_id%TYPE;
2247 l_contract_id po_lines.contract_id%TYPE;
2248 l_order_header_id po_lines.po_header_id%TYPE;
2249 l_order_line_id po_lines.po_line_id%TYPE;
2250 l_creation_date po_lines.creation_date%TYPE;
2251 l_item_id po_lines.item_id%TYPE;
2252 l_item_revision po_lines.item_revision%TYPE;
2253 l_category_id po_lines.category_id%TYPE;
2254 l_line_type_id po_lines.line_type_id%TYPE;
2255 l_vendor_product_num po_lines.vendor_product_num%TYPE;
2256 l_vendor_id po_headers.vendor_id%TYPE;
2257 l_vendor_site_id po_headers.vendor_site_id%TYPE;
2258 l_uom po_lines.unit_meas_lookup_code%TYPE;
2259 l_in_unit_price po_lines.unit_price%TYPE;
2260 l_base_unit_price po_lines.base_unit_price%TYPE;
2261 l_currency_code po_headers.currency_code%TYPE;
2262 l_return_status VARCHAR2(1);
2263
2264 x_base_unit_price NUMBER ;
2265 x_price_break_id NUMBER ;
2266 --<R12 GBPA Adv Pricing End>
2267
2268
2269
2270 begin
2271 -- Setup for writing the concurrent logs based on
2272 -- the concurrent log Profile
2273 IF g_log_mode = 'Y' THEN
2274 po_debug.set_file_io(TRUE);
2275 ELSE
2276 po_debug.set_file_io(null);
2277 END IF;
2278
2279
2280
2281 l_module := g_log_head||l_api_name||'.'||'000'||'.';
2282
2283 -- Bug 3339149 Start
2284
2285 IF p_po_line_id is NOT NULL THEN
2286
2287 IF p_global_agreement_flag = 'Y' THEN
2288
2289 select po_line_id
2290 into l_po_line_id
2291 from po_line_locations_all
2292 where line_location_id = p_po_line_location_id ;
2293
2294 l_std_po_price_change := 'Y';
2295
2296 ELSE
2297 l_std_po_price_change := 'N';
2298 END IF;
2299
2300 ELSE -- Contracts
2301
2302 l_std_po_price_change := 'Y';
2303 select po_line_id
2304 into l_po_line_id
2305 from po_line_locations_all
2306 where line_location_id = p_po_line_location_id ;
2307
2308
2309 END IF;
2310
2311
2312 l_retroactive_update := Get_Retro_Mode;
2313
2314 -- Bug 4080732 START
2315 -- For a consigned flow, check that the Inventory Org Period is open,
2316 -- before updating the price on the consumption advice. For this check,
2317 -- we get the Inv Org from the ship-to-org at the shipment level (for
2318 -- regular flows). And for Shared Procuremnet scenario, we use the
2319 -- logical inv org of the transaction flow.
2320 IF (l_retroactive_update = 'ALL_RELEASES') AND
2321 (is_inv_org_period_open(l_std_po_price_change,
2322 l_po_line_id,
2323 p_po_line_location_id) = 'N')
2324 THEN
2325 l_error_message := 'Can not retroactively update price on a consumption '||
2326 'advice, since the Inventory Org period is not open.';
2327
2328 IF g_debug_stmt then
2329 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
2330 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION, l_module, l_error_message);
2331 END IF;
2332 END IF;
2333
2334 PO_DEBUG.put_line(l_error_message);
2335
2336 g_exclude_index := g_exclude_index + 1;
2337 g_exclude_row_id_table(g_exclude_index) := p_row_id ;
2338
2339 RETURN;
2340
2341 END IF;
2342 -- Bug 4080732 END
2343
2344 IF l_retroactive_update = 'ALL_RELEASES'
2345 AND (Is_Adjustment_Account_Valid(l_std_po_price_change,
2346 l_po_line_id,
2347 p_po_line_location_id) = 'N')
2348 THEN
2349
2350 FND_MESSAGE.set_name('PO', 'PO_RETRO_PRICING_NOT_ALLOWED');
2351 l_error_message := FND_MESSAGE.get;
2352 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
2353 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,l_module,l_error_message);
2354 END IF;
2355 PO_DEBUG.put_line(l_error_message);
2356
2357 g_exclude_index := g_exclude_index + 1;
2358 g_exclude_row_id_table(g_exclude_index) := p_row_id ;
2359
2360 Return;
2361
2362 END IF;
2363 -- Bug 3339149 End
2364
2365 -- Bug 3231062 START
2366 IF (l_retroactive_update = 'ALL_RELEASES' AND
2367 (Is_Retro_Project_Allowed(l_std_po_price_change,
2368 l_po_line_id,
2369 p_po_line_location_id) = 'N'))
2370 THEN
2371 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
2372 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,l_module,
2373 'Can not update price since project 11i10 is not enabled');
2374 END IF;
2375 PO_DEBUG.put_line('Can not update price since project 11i10 is not enabled');
2376
2377 g_exclude_index := g_exclude_index + 1;
2378 g_exclude_row_id_table(g_exclude_index) := p_row_id ;
2379
2380 Return;
2381
2382 END IF; /*IF (l_retroactive_update = 'ALL_RELEASES' AND*/
2383 -- Bug 3231062 END
2384
2385
2386 --<R12 GBPA Adv Pricing Start>
2387 IF g_debug_stmt then
2388 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
2389 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION, l_module, 'intialising Get Price Break Call');
2390 END IF;
2391 END IF;
2392
2393 if (l_std_po_price_change = 'Y') then
2394 --Bug5469245: Use Tables Instead of Synonyms for global procurement support
2395 SELECT NVL(p_quantity,POL.quantity),
2396 POL.from_line_id,
2397 NVL(p_ship_to_location_id, PLL.ship_to_location_id),
2398 NVL(p_need_by_date, NVL(PLL.need_by_date, sysdate)),
2399 NVL(p_ship_to_organization_id,PLL.ship_to_organization_id),
2400 POL.org_id,
2401 POL.contract_id,
2402 POL.po_header_id,
2403 POL.po_line_id,
2404 POL.creation_date,
2405 POL.item_id,
2406 POL.item_revision,
2407 POL.category_id,
2408 POL.line_type_id,
2409 POL.vendor_product_num,
2410 POH.vendor_id,
2411 POH.vendor_site_id,
2412 POL.unit_meas_lookup_code,
2413 POL.base_unit_price,
2414 POH.currency_code
2415 INTO l_quantity,
2416 l_from_line_id,
2417 l_ship_to_location_id,
2418 l_need_by_date,
2419 l_ship_to_org_id,
2420 l_org_id,
2421 l_contract_id,
2422 l_order_header_id,
2423 l_order_line_id,
2424 l_creation_date,
2425 l_item_id,
2426 l_item_revision,
2427 l_category_id,
2428 l_line_type_id,
2429 l_vendor_product_num,
2430 l_vendor_id,
2431 l_vendor_site_id,
2432 l_uom,
2433 l_in_unit_price,
2434 l_currency_code
2435 FROM po_line_locations_all PLL, po_lines_all POL,
2436 po_headers_all POH
2437 WHERE PLL.line_location_id = p_po_line_location_id
2438 AND POL.po_line_id = PLL.po_line_id
2439 AND POH.po_header_id = POL.po_header_id;
2440
2441 IF g_debug_stmt then
2442 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
2443 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION, l_module, 'Call get price break API ');
2444 END IF;
2445 END IF;
2446
2447 PO_SOURCING2_SV.get_break_price
2448 ( p_api_version => 1.0
2449 , p_order_quantity => l_quantity
2450 , p_ship_to_org => l_ship_to_org_id
2451 , p_ship_to_loc => l_ship_to_location_id
2452 , p_po_line_id => l_from_line_id
2453 , p_cum_flag => FALSE
2454 , p_need_by_date => l_need_by_date
2455 , p_line_location_id => p_po_line_location_id
2456 , p_contract_id => l_contract_id
2457 , p_org_id => l_org_id
2458 , p_supplier_id => l_vendor_id
2459 , p_supplier_site_id => l_vendor_site_id
2460 , p_creation_date => l_creation_date
2461 , p_order_header_id => l_order_header_id
2462 , p_order_line_id => l_order_line_id
2463 , p_line_type_id => l_line_type_id
2464 , p_item_revision => l_item_revision
2465 , p_item_id => l_item_id
2466 , p_category_id => l_category_id
2467 , p_supplier_item_num => l_vendor_product_num
2468 , p_in_price => l_in_unit_price
2469 , p_uom => l_uom
2470 , p_currency_code => l_currency_code
2471 , x_base_unit_price => x_base_unit_price
2472 , x_price_break_id => x_price_break_id
2473 , x_price => l_new_price_override
2474 , x_return_status => l_return_status
2475 );
2476
2477 --<R12 GBPA Adv Pricing End>
2478
2479 ELSE
2480
2481 IF g_debug_stmt then
2482 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
2483 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION, l_module, 'Call get price break API:old call ');
2484 END IF;
2485 END IF;
2486
2487 l_new_price_override := po_sourcing2_sv.get_break_price(
2488 p_quantity,
2489 p_ship_to_organization_id,
2490 p_ship_to_location_id,
2491 p_po_line_id,
2492 l_cumulative_flag,
2493 p_need_by_date,
2494 p_po_line_location_id);
2495
2496 END IF; -- if (l_std_po_price_change = 'Y') then
2497
2498 IF g_debug_stmt then
2499 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2500 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
2501 l_module,'l_new_price_override'||
2502 l_new_price_override);
2503 END IF;
2504 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2505 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
2506 l_module,'p_old_price_override' ||
2507 p_old_price_override);
2508 END IF;
2509 end if;
2510
2511
2512
2513 if (l_new_price_override <> p_old_price_override) then
2514 l_module := g_log_head||l_api_name||'.'||'010'||'.';
2515 g_index := g_index + 1;
2516 g_row_id_table(g_index) := p_row_id ;
2517 g_new_price_table(g_index) := l_new_price_override ;
2518 g_auth_status_table(g_index) := p_authorization_status ;
2519 g_archived_rev_num_table(g_index) := p_archived_rev_num;
2520
2521 if (l_std_po_price_change = 'Y') then
2522 g_po_header_id_table(g_index) := p_document_id ;
2523 g_po_release_id_table(g_index) := null ;
2524 else
2525 g_po_release_id_table(g_index) := p_document_id ;
2526 g_po_header_id_table(g_index) := null ;
2527 end if; /*l_std_po_price_change= 'Y' */
2528
2529 end if; /* l_new_price_override <> l_old_price_override*/
2530
2531
2532 exception
2533 when others then
2534 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
2535 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
2536 l_module,SQLERRM(SQLCODE));
2537 END IF;
2538 raise;
2539
2540 end Process_Price_Change;
2541
2542
2543 /**
2544 * Private Procedure: Launch_PO_Approval
2545 * Modifies: Authorization_status of po_headers if the document was
2546 * already approved.
2547 * Effects: Get the po_header_id from the global temp table po_retroprice_gt
2548 * which has all the document ids that have been updated with
2549 * new price. If the document is in the approved state, then
2550 * call submission_check and if it is successful, initiate
2551 * approval. This is called from massupdate_releases procedure.
2552 */
2553
2554 PROCEDURE Launch_PO_Approval IS
2555 l_progress varchar2(3);
2556 l_module VARCHAR2(100);
2557 l_api_name CONSTANT VARCHAR2(50) := 'Launch_PO_Approval';
2558 l_msg_buf varchar2(2000);
2559 x_return_status varchar2(1);
2560 x_sub_check_status varchar2(1);
2561 x_msg_data varchar2(2000);
2562 x_online_report_id number;
2563 x_msg_count number;
2564 l_doc_org_id number;
2565 -- Bug 3318625
2566 l_consigned_flag PO_HEADERS_ALL.consigned_consumption_flag%TYPE;
2567 begin
2568
2569 l_module := g_log_head||l_api_name||'.'||'000'||'.';
2570 /* Bug 2707350.
2571 * Org context needs to be set for the submission check procedure.
2572 * Get the orig org_id and then for each document that will be sent
2573 * sent for submission check, get the org_id from po_headers_all.
2574 * Set the org context using this org_id and if submission check
2575 * is successful, initiate approval. When all documents are done
2576 * set the original org context.
2577 */
2578
2579 if (g_po_header_id_table.count > 0) then
2580 for i in g_po_header_id_table.first..g_po_header_id_table.LAST loop
2581
2582 -- Bug 3318625, Re-approve 'REQUIRES REAPPROVAL' Consumption Advices
2583 -- if (g_po_auth_table(i) ='APPROVED') then
2584 if (g_po_auth_table(i) in ('APPROVED', 'REQUIRES REAPPROVAL')) then
2585
2586 select org_id,
2587 NVL(consigned_consumption_flag, 'N') -- Bug 3318625
2588 into l_doc_org_id,
2589 l_consigned_flag -- Bug 3318625
2590 from po_headers_all
2591 where po_header_id = g_po_header_id_table(i);
2592
2593 -- Bug 3318625 START
2594 IF (g_po_auth_table(i) = 'APPROVED' OR
2595 (g_po_auth_table(i) = 'REQUIRES REAPPROVAL' AND
2596 l_consigned_flag = 'Y'))
2597 THEN
2598 -- Bug 3318625 END
2599
2600 PO_MOAC_UTILS_PVT.set_org_context(l_doc_org_id) ; -- <R12 MOAC>
2601
2602 PO_DOCUMENT_CHECKS_GRP.po_submission_check(
2603 p_api_version => 1.0,
2604 p_action_requested => 'DOC_SUBMISSION_CHECK',
2605 p_document_type => 'PO',
2606 p_document_subtype => 'STANDARD',
2607 p_document_id => g_po_header_id_table(i),
2608 x_return_status => x_return_status,
2609 x_sub_check_status => x_sub_check_status,
2610 x_msg_data => x_msg_data,
2611 x_online_report_id => x_online_report_id);
2612
2613 /* For FND_LOG level, using LEVEL_EXCEPTION since these
2614 * are really exception that happened but we are not
2615 * erroring out here but just logging it and then continue
2616 * trying to submit next document for approval.
2617 */
2618
2619 If (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
2620 l_msg_buf := null;
2621 l_msg_buf := FND_MSG_PUB.Get(p_msg_index => 1,
2622 p_encoded => 'F');
2623 l_msg_buf := 'Std PO ' ||g_po_header_id_table(i)||
2624 l_msg_buf;
2625 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
2626 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
2627 l_module,l_msg_buf);
2628 END IF;
2629 end if;
2630 If ((x_return_status = FND_API.G_RET_STS_SUCCESS) and
2631 (x_sub_check_status = FND_API.G_RET_STS_ERROR)) THEN
2632
2633 l_msg_buf := 'Std PO: ' ||g_po_header_id_table(i)||
2634 ' Online Report Id: '||x_online_report_id;
2635 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
2636 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
2637 l_module,l_msg_buf);
2638 END IF;
2639
2640 end if;
2641 l_module := g_log_head||l_api_name||'.'||'010'||'.';
2642
2643 If ((x_return_status = FND_API.G_RET_STS_SUCCESS) and
2644 (x_sub_check_status = FND_API.G_RET_STS_SUCCESS)) THEN
2645
2646 PO_RETROACTIVE_PRICING_PVT.Retroactive_Launch_Approval
2647 ( p_doc_id => g_po_header_id_table(i),
2648 p_doc_type => 'PO',
2649 p_doc_subtype => 'STANDARD');
2650 end if;
2651
2652 -- Bug 3318625
2653 END IF; /* if (g_po_auth_table(i) = 'APPROVED' OR */
2654
2655 end if; /*if (g_po_auth_table(i) in ('APPROVED', 'REQUIRES REAPPROVAL')) */
2656
2657 end loop; /* l_global_document_id_table.first */
2658 end if;/*g_po_header_id_table.count > 0 */
2659
2660 PO_MOAC_UTILS_PVT.set_org_context(g_orig_org_id) ; -- <R12 MOAC>
2661 exception
2662 when no_data_found then
2663 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
2664 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
2665 l_module,SQLERRM(SQLCODE));
2666 END IF;
2667 raise;
2668 when others then
2669 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
2670 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
2671 l_module,SQLERRM(SQLCODE));
2672 END IF;
2673 raise;
2674
2675 end Launch_PO_Approval;
2676
2677
2678 /**
2679 * Private Procedure: Launch_REL_Approval
2680 * Modifies: Authorization_status of po_releases if the document was
2681 * already approved.
2682 * Effects: Get the po_release_id from the global temp table po_retroprice_gt
2683 * which has all the document ids that have been updated with
2684 * new price. If the document is in the approved state, then
2685 * call submission_check and if it is successful, initiate
2686 * approval. This is called from massupdate_releases procedure.
2687 */
2688
2689 PROCEDURE Launch_REL_Approval IS
2690 l_progress varchar2(3);
2691 l_module VARCHAR2(100);
2692 l_api_name CONSTANT VARCHAR2(50) := 'Launch_REL_Approval';
2693 l_msg_buf varchar2(2000);
2694 x_return_status varchar2(1);
2695 x_sub_check_status varchar2(1);
2696 x_msg_data varchar2(2000);
2697 x_online_report_id number;
2698 -- Bug 3318625
2699 l_consigned_flag PO_RELEASES_ALL.consigned_consumption_flag%TYPE;
2700 begin
2701
2702 /* Get po_header_id for the documents that are in the Approved STate.
2703 * Call submission checks and initiate approval.
2704 */
2705 l_module := g_log_head||l_api_name||'.'||'000'||'.';
2706
2707 if (g_po_release_id_table.count > 0) then
2708 for i in g_po_release_id_table.first..g_po_release_id_table.LAST loop
2709
2710 -- Bug 3318625 START
2711 -- Re-approve 'REQUIRES REAPPROVAL' Consumption Advices
2712 -- if (g_rel_auth_table(i) ='APPROVED') then
2713 if (g_rel_auth_table(i) in ('APPROVED', 'REQUIRES REAPPROVAL')) then
2714
2715 select NVL(consigned_consumption_flag, 'N') -- Bug 3318625
2716 into l_consigned_flag
2717 from po_releases_all
2718 where po_release_id = g_po_release_id_table(i);
2719
2720 IF (g_rel_auth_table(i) = 'APPROVED' OR
2721 (g_rel_auth_table(i) = 'REQUIRES REAPPROVAL' AND
2722 l_consigned_flag = 'Y'))
2723 THEN
2724 -- Bug 3318625 END
2725
2726 PO_DOCUMENT_CHECKS_GRP.po_submission_check(
2727 p_api_version => 1.0,
2728 p_action_requested => 'DOC_SUBMISSION_CHECK',
2729 p_document_type => 'RELEASE',
2730 p_document_subtype => 'BLANKET',
2731 p_document_id => g_po_release_id_table(i),
2732 x_return_status => x_return_status,
2733 x_sub_check_status => x_sub_check_status,
2734 x_msg_data => x_msg_data,
2735 x_online_report_id => x_online_report_id);
2736
2737 /* For FND_LOG level, using LEVEL_EXCEPTION since these
2738 * are really exception that happened but we are not
2739 * erroring out here but just logging it and then continue
2740 * trying to submit next document for approval.
2741 */
2742 If (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
2743 l_msg_buf := null;
2744 l_msg_buf := FND_MSG_PUB.Get(p_msg_index => 1,
2745 p_encoded => 'F');
2746 l_msg_buf := 'Release ' ||g_po_release_id_table(i)||
2747 l_msg_buf;
2748 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
2749 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
2750 l_module,l_msg_buf);
2751 END IF;
2752 end if;
2753
2754 If ((x_return_status = FND_API.G_RET_STS_SUCCESS) and
2755 (x_sub_check_status = FND_API.G_RET_STS_ERROR)) THEN
2756
2757 l_msg_buf := 'Release ' ||g_po_release_id_table(i)||
2758 'Online Report Id '||x_online_report_id;
2759 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
2760 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
2761 l_module,l_msg_buf);
2762 END IF;
2763
2764 end if;
2765
2766 l_module := g_log_head||l_api_name||'.'||'010'||'.';
2767 If ((x_return_status = FND_API.G_RET_STS_SUCCESS) and
2768 (x_sub_check_status = FND_API.G_RET_STS_SUCCESS)) THEN
2769
2770 PO_RETROACTIVE_PRICING_PVT.Retroactive_Launch_Approval
2771 ( p_doc_id => g_po_release_id_table(i),
2772 p_doc_type => 'RELEASE',
2773 p_doc_subtype => 'BLANKET');
2774 end if;
2775
2776 -- Bug 3318625
2777 END IF; /* if (g_rel_auth_table(i) = 'APPROVED' OR */
2778
2779 end if; /*if (g_rel_auth_table(i) in ('APPROVED', 'REQUIRES REAPPROVAL')) */
2780
2781 end loop; /* l_global_document_id_table.first */
2782 end if;/*g_po_release_id_table.count > 0 */
2783 exception
2784 when others then
2785 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
2786 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
2787 l_module,SQLERRM(SQLCODE));
2788 END IF;
2789 raise;
2790
2791 end Launch_REL_Approval;
2792
2793 /**
2794 * Private Procedure: Retroactive_Launch_Approval
2795 * Modifies: Authorization_status of po_headers and po_releaes.
2796 * Effects: Get the default supplier communiation flags using
2797 * po_vendor_sites_sv.get_transmission_defaults and then
2798 * call start_wf_process by setting the correct values
2799 * for the supplier communication flags. This is called from
2800 * massupdate_releases procedure.
2801 */
2802
2803 Procedure Retroactive_Launch_Approval(
2804 p_doc_id IN Number,
2805 p_doc_type IN Varchar2,
2806 p_doc_subtype IN Varchar2) IS
2807
2808 l_workflow_process varchar2(40) := null;
2809 l_submitter_action varchar2(25);
2810 l_forward_to_id number;
2811 l_forward_from_id number;
2812 l_def_approval_path_id number;
2813 l_note varchar2(25);
2814 l_ItemType po_headers_all.WF_ITEM_TYPE%TYPE := null;
2815 l_ItemKey po_headers_all.WF_ITEM_Key%TYPE := null;
2816 l_seq_for_item_key varchar2(6) := null;
2817 l_action_orig_from varchar2(30) := 'RETRO'; --need to findout
2818 l_xmlsetup varchar2(1) := 'N';
2819 l_docnum po_headers_all.segment1%type;
2820 l_preparer_id po_headers.agent_id%type;
2821 l_default_method PO_VENDOR_SITES.SUPPLIER_NOTIF_METHOD%TYPE := null;
2822 l_email_address po_vendor_sites.email_Address%type := null;
2823 l_fax_number varchar2(30) := null; --Changed as part of Bug 5765243
2824 l_document_num po_headers.segment1%type;
2825 l_xml_flag varchar2(1) := 'N';
2826 l_email_flag varchar2(1) := 'N';
2827 l_fax_flag varchar2(1) := 'N';
2828 l_print_flag varchar2(1) := 'N';
2829 l_org_id number;
2830
2831
2832 l_create_sourcing_rule varchar2(30) := null;
2833 l_update_sourcing_rule varchar2(30) := null;
2834 l_rel_gen_method varchar2(30) := null;
2835 l_progress varchar2(3);
2836 l_module VARCHAR2(100);
2837 l_api_name CONSTANT VARCHAR2(50) := 'Retroactive_Launch_Approval';
2838
2839 begin
2840
2841
2842
2843 l_module := g_log_head||l_api_name||'.'||'000'||'.';
2844 /* Get the org context and set it since we will initiate approvals
2845 * for Std PO against Global Agreement in other operating units too.
2846 */
2847 If ((p_doc_type = 'PO') OR (p_doc_type = 'PA')) then
2848 SELECT poh.org_id
2849 into l_org_id
2850 FROM po_headers_all poh
2851 WHERE poh.po_header_id = p_doc_id;
2852 elsif (p_doc_type = 'RELEASE') then
2853 SELECT por.org_id
2854 into l_org_id
2855 FROM po_releases_all por
2856 WHERE por.po_release_id = p_doc_id;
2857 end if; /*If ((p_document_type = 'PO') OR (p_document_type = 'PA'))*/
2858
2859 PO_MOAC_UTILS_PVT.set_org_context(l_org_id) ; -- <R12 MOAC>
2860
2861 PO_VENDOR_SITES_SV.Get_Transmission_Defaults(
2862 p_document_id => p_doc_id,
2863 p_document_type => p_doc_type,
2864 p_document_subtype => p_doc_subtype,
2865 p_preparer_id => l_preparer_id,
2866 x_default_method => l_default_method,
2867 x_email_address => l_email_address,
2868 x_fax_number => l_fax_number,
2869 x_document_num => l_document_num);
2870
2871 If ((l_default_method = 'EMAIL') and
2872 (l_email_address is not null)) then
2873 l_email_flag := 'Y';
2874 elsif ((l_default_method = 'FAX') and (l_fax_number is not null)) then
2875 l_email_address := null;
2876
2877 l_fax_flag := 'Y';
2878 elsif l_default_method = 'PRINT' then
2879 l_email_address := null;
2880 l_fax_number := null;
2881
2882 l_print_flag := 'Y';
2883 else
2884 l_email_address := null;
2885 l_fax_number := null;
2886 end if;
2887
2888 l_module := g_log_head||l_api_name||'.'||'000'||'.';
2889
2890
2891 po_reqapproval_init1.start_wf_process
2892 ( ItemType => l_ItemType,
2893 ItemKey => l_ItemKey,
2894 WorkflowProcess => l_workflow_process,
2895 ActionOriginatedFrom => l_action_orig_from,
2896 DocumentID => p_doc_id,
2897 DocumentNumber => l_docnum,
2898 PreparerID => l_preparer_id,
2899 DocumentTypeCode => p_doc_type,
2900 DocumentSubtype => p_doc_subtype,
2901 SubmitterAction => l_submitter_action,
2902 forwardToID => l_forward_to_id,
2903 forwardFromID => l_forward_from_id,
2904 DefaultApprovalPathID => l_def_approval_path_id,
2905 Note => l_note,
2906 printFlag => l_print_flag,
2907 FaxFlag => l_fax_flag,
2908 FaxNumber => l_fax_number,
2909 EmailFlag => l_email_flag,
2910 EmailAddress => l_email_address,
2911 CreateSourcingRule => l_create_sourcing_rule,
2912 UpdateSourcingRule => l_update_sourcing_rule,
2913 ReleaseGenMethod => l_rel_gen_method,
2914 MassUpdateReleases => 'N',
2915 --Bug 3574895. Retroactively updated releases were not
2916 -- getting communicated to supplier
2917 CommunicatePriceChange => g_communicate_update,
2918 RetroactivePriceChange => 'Y');
2919
2920 exception
2921 when no_data_found then
2922 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
2923 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
2924 l_module,SQLERRM(SQLCODE));
2925 END IF;
2926 raise;
2927 when others then
2928 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
2929 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
2930 l_module,SQLERRM(SQLCODE));
2931 END IF;
2932 raise;
2933
2934 end Retroactive_Launch_Approval;
2935
2936
2937
2938 --<R12 GBPA Adv Pricing >
2939 --Changed the procedure name
2940 PROCEDURE open_agreement_cur(p_sql_str IN VARCHAR2,
2941 p_po_header_id IN NUMBER,
2942 p_vendor_id IN NUMBER,
2943 p_vendor_site_id IN NUMBER,
2944 p_category_struct_id IN NUMBER,
2945 p_ga_security IN VARCHAR2,
2946 p_item_from IN VARCHAR2,
2947 p_item_to IN VARCHAR2,
2948 p_cat_from IN VARCHAR2,
2949 p_cat_to IN VARCHAR2,
2950 x_cursor IN OUT NOCOPY g_agreement_cur_type) IS
2951
2952 TYPE bind_var_tbl_type IS TABLE OF VARCHAR2(800) INDEX BY BINARY_INTEGER;
2953
2954 l_bind_vars bind_var_tbl_type;
2955 l_current_index NUMBER := 1;
2956 l_num_bind_vars NUMBER := 5; -- number of bind variables known
2957
2958 l_module VARCHAR2(100);
2959 l_api_name CONSTANT VARCHAR2(50) := 'open_agreement_cur';
2960
2961 BEGIN
2962
2963 l_module := g_log_head||l_api_name||'.'||'000'||'.';
2964
2965 IF (p_item_from IS NOT NULL) THEN
2966 l_bind_vars(l_current_index) := p_item_from;
2967 l_current_index := l_current_index + 1;
2968 l_num_bind_vars := l_num_bind_vars + 1;
2969 END IF;
2970
2971 IF (p_item_to IS NOT NULL) THEN
2972 l_bind_vars(l_current_index) := p_item_to;
2973 l_current_index := l_current_index + 1;
2974 l_num_bind_vars := l_num_bind_vars + 1;
2975 END IF;
2976
2977 IF (p_cat_from IS NOT NULL) THEN
2978 l_bind_vars(l_current_index) := p_cat_from;
2979 l_current_index := l_current_index + 1;
2980 l_num_bind_vars := l_num_bind_vars + 1;
2981 END IF;
2982
2983 IF (p_cat_to IS NOT NULL) THEN
2984 l_bind_vars(l_current_index) := p_cat_to;
2985 l_current_index := l_current_index + 1;
2986 l_num_bind_vars := l_num_bind_vars + 1;
2987 END IF;
2988
2989 l_module := g_log_head||l_api_name||'.'||'010'||'.';
2990 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2991 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,l_module,
2992 'Ready to open x_cursor, num of binds = ' || l_num_bind_vars);
2993 END IF;
2994
2995 IF (l_num_bind_vars = 5) THEN
2996 OPEN x_cursor FOR p_sql_str USING p_po_header_id, p_vendor_id,
2997 p_vendor_site_id, p_category_struct_id,
2998 p_ga_security,
2999 p_po_header_id, p_vendor_id, p_vendor_site_id;
3000 ELSIF (l_num_bind_vars = 6) THEN
3001 OPEN x_cursor FOR p_sql_str USING p_po_header_id, p_vendor_id,
3002 p_vendor_site_id, p_category_struct_id,
3003 p_ga_security, l_bind_vars(1),
3004 p_po_header_id, p_vendor_id, p_vendor_site_id;
3005 ELSIF (l_num_bind_vars = 7) THEN
3006 OPEN x_cursor FOR p_sql_str USING p_po_header_id, p_vendor_id,
3007 p_vendor_site_id, p_category_struct_id,
3008 p_ga_security, l_bind_vars(1),
3009 l_bind_vars(2),
3010 p_po_header_id, p_vendor_id, p_vendor_site_id;
3011 ELSIF (l_num_bind_vars = 8) THEN
3012 OPEN x_cursor FOR p_sql_str USING p_po_header_id, p_vendor_id,
3013 p_vendor_site_id, p_category_struct_id,
3014 p_ga_security, l_bind_vars(1),
3015 l_bind_vars(2), l_bind_vars(3),
3016 p_po_header_id, p_vendor_id, p_vendor_site_id;
3017 ELSIF (l_num_bind_vars = 9) THEN
3018 OPEN x_cursor FOR p_sql_str USING p_po_header_id, p_vendor_id,
3019 p_vendor_site_id, p_category_struct_id,
3020 p_ga_security, l_bind_vars(1),
3021 l_bind_vars(2), l_bind_vars(3),
3022 l_bind_vars(4),
3023 p_po_header_id, p_vendor_id, p_vendor_site_id;
3024 END IF;
3025
3026 l_module := g_log_head||l_api_name||'.'||'020'||'.';
3027 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3028 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,l_module,
3029 'x_cursor is opened');
3030 END IF;
3031 EXCEPTION
3032 WHEN OTHERS THEN
3033 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
3034 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
3035 l_module,SQLERRM(SQLCODE));
3036 END IF;
3037 END open_agreement_cur;
3038
3039 -- bug2935437 end
3040
3041
3042 -- <FPJ Retroactive Price START>
3043 --------------------------------------------------------------------------------
3044 --Start of Comments
3045 --Name: Get_Retro_mode
3046 --Pre-reqs:
3047 -- None.
3048 --Modifies:
3049 -- None.
3050 --Locks:
3051 -- None.
3052 --Function:
3053 -- This function returns retroactive pricing mode.
3054 --Parameters:
3055 --IN:
3056 -- None.
3057 --RETURN:
3058 -- 'NEVER': Not Supported
3059 -- 'OPEN_RELEASES': Retroactive Pricing Update on Open Releases
3060 -- 'ALL_RELEASES': Retroactive Pricing Update on All Releases
3061 --Testing:
3062 --
3063 --End of Comments
3064 -------------------------------------------------------------------------------
3065 FUNCTION Get_Retro_Mode RETURN VARCHAR2 IS
3066 l_api_name CONSTANT varchar2(30) := 'Get_Retro_Mode';
3067 l_log_head CONSTANT VARCHAR2(100):= g_log_head || l_api_name;
3068 l_progress VARCHAR2(3);
3069 l_retroactive_update VARCHAR2(30) := 'NEVER';
3070 -- Bug 3614598
3071 l_ap_family_pack FND_PRODUCT_INSTALLATIONS.patch_level%TYPE;
3072
3073 BEGIN
3074
3075 l_progress := '000';
3076 IF g_debug_stmt THEN
3077 PO_DEBUG.debug_begin(l_log_head);
3078 END IF;
3079
3080 FND_PROFILE.get('PO_ALLOW_RETROPRICING_OF_PO',l_retroactive_update);
3081 IF (l_retroactive_update IS NULL) THEN
3082 l_retroactive_update := 'NEVER';
3083 END IF; /* IF (l_retroactive_update IS NULL) */
3084
3085 l_progress := '020';
3086 IF g_debug_stmt THEN
3087 PO_DEBUG.debug_var(l_log_head,l_progress,'l_retroactive_update', l_retroactive_update);
3088 END IF;
3089
3090 IF (l_retroactive_update = 'NEVER') THEN
3091 RETURN l_retroactive_update;
3092 END IF; /* IF (l_retroactive_update = 'NEVER') */
3093
3094 l_progress := '060';
3095 IF g_debug_stmt THEN
3096 PO_DEBUG.debug_var(l_log_head,l_progress,'l_retroactive_update', l_retroactive_update);
3097 END IF;
3098
3099 IF (l_retroactive_update = 'ALL_RELEASES') THEN
3100
3101 -- Bug 3614598 START
3102 -- Remove checking for inventory since it is now part of SCM
3103 -- Use AD_VERSION_UTIL.get_product_patch_level instead of direct query
3104 AD_VERSION_UTIL.get_product_patch_level
3105 ( p_appl_id => 200, -- AP
3106 p_patch_level => l_ap_family_pack
3107 );
3108
3109 l_progress := '080';
3110 IF g_debug_stmt THEN
3111 PO_DEBUG.debug_var(l_log_head,l_progress,'l_ap_family_pack', l_ap_family_pack);
3112 END IF;
3113
3114 IF (l_ap_family_pack < '11i.AP.L') THEN
3115 l_retroactive_update := 'OPEN_RELEASES';
3116 END IF; /* IF (l_ap_family_pack > '11i.AP.L') */
3117 -- Bug 3614598 END
3118
3119 END IF; /* IF (l_retroactive_update = 'ALL_RELEASES') */
3120
3121 l_progress := '100';
3122 IF g_debug_stmt THEN
3123 PO_DEBUG.debug_end(l_log_head);
3124 PO_DEBUG.debug_var(l_log_head,l_progress,'l_retroactive_update', l_retroactive_update);
3125 END IF;
3126
3127 return l_retroactive_update;
3128 EXCEPTION
3129 WHEN OTHERS THEN
3130 return 'NEVER';
3131
3132 END Get_Retro_Mode;
3133
3134 --------------------------------------------------------------------------------
3135 --Start of Comments
3136 --Name: Is_Retro_Update
3137 --Pre-reqs:
3138 -- None.
3139 --Modifies:
3140 -- None.
3141 --Locks:
3142 -- None.
3143 --Function:
3144 -- This function returns retroactive pricing status.
3145 --Parameters:
3146 --IN:
3147 --p_document_id
3148 -- The id of the document (po_header_id or po_release_id)
3149 --p_document_type
3150 -- The type of the document
3151 -- PO : Standard PO
3152 -- RELEASE : Release
3153 --RETURN:
3154 -- 'Y': Retroactive Pricing Update
3155 -- 'N': Not a Retroactive Pricing Update
3156 --Testing:
3157 --
3158 --End of Comments
3159 -------------------------------------------------------------------------------
3160 FUNCTION Is_Retro_Update(p_document_id IN NUMBER,
3161 p_document_type IN VARCHAR2)
3162 RETURN VARCHAR2
3163 IS
3164 l_retro_change VARCHAR2(1) := 'N';
3165 BEGIN
3166
3167 --<R12 GBPA Adv Pricing Start>
3168
3169 -- bug5358954
3170 -- Added the where clause
3171 -- 'poll.line_location_id = polla.line_location_id'
3172 -- in the following two sqls.
3173
3174
3175 IF (p_document_type = 'PO') THEN
3176 -- SQL What: Find out any retroactive pricing change for this PO
3177 -- by comparing the price in the latest revision
3178 SELECT 'Y'
3179 INTO l_retro_change
3180 FROM dual
3181 WHERE EXISTS (SELECT 'retroactive pricing changes'
3182 FROM po_line_locations poll,
3183 po_line_locations_archive polla
3184 WHERE poll.po_header_id = p_document_id
3185 AND poll.po_header_id =polla.po_header_id
3186 AND poll.line_location_id = polla.line_location_id
3187 AND polla.latest_external_flag = 'Y'
3188 AND poll.price_override <> polla.price_override);
3189 ELSE
3190 -- SQL What: Find out any retroactive pricing change for this Release
3191 -- by comparing the price in the latest revision
3192
3193 SELECT 'Y'
3194 INTO l_retro_change
3195 FROM dual
3196 WHERE EXISTS (SELECT 'retroactive pricing changes'
3197 FROM po_line_locations poll,
3198 po_line_locations_archive polla
3199 WHERE poll.po_release_id = p_document_id
3200 AND poll.po_header_id =polla.po_header_id
3201 AND poll.line_location_id = polla.line_location_id
3202 AND polla.latest_external_flag = 'Y'
3203 AND poll.price_override <> polla.price_override);
3204 END IF; /* IF (p_document_type = 'PO') */
3205
3206 --<R12 GBPA Adv Pricing End>
3207
3208
3209 return l_retro_change;
3210 EXCEPTION
3211 WHEN NO_DATA_FOUND THEN
3212 return 'N';
3213
3214 END Is_Retro_Update;
3215
3216 --------------------------------------------------------------------------------
3217 --Start of Comments
3218 --Name: Reset_Retro_Update
3219 --Pre-reqs:
3220 -- None.
3221 --Modifies:
3222 -- None.
3223 --Locks:
3224 -- None.
3225 --Function:
3226 -- This function resets retroactive_date.
3227 --Parameters:
3228 --IN:
3229 --p_document_id
3230 -- The id of the document (po_header_id or po_release_id)
3231 --p_document_type
3232 -- The type of the document
3233 -- PO : Standard PO
3234 -- RELEASE : Release
3235 --Testing:
3236 --
3237 --End of Comments
3238 -------------------------------------------------------------------------------
3239 PROCEDURE Reset_Retro_Update(p_document_id IN NUMBER,
3240 p_document_type IN VARCHAR2)
3241 IS
3242 PRAGMA AUTONOMOUS_TRANSACTION;
3243 -- Bug 3251646
3244 -- The autonomous transaction is required if the workflow function modifies
3245 -- records in main document entity tables.
3246
3247 l_api_name CONSTANT varchar2(30) := 'Reset_Retro_Update';
3248 l_log_head CONSTANT VARCHAR2(100):= g_log_head || l_api_name;
3249 l_progress VARCHAR2(3);
3250 l_user_id NUMBER := FND_GLOBAL.user_id;
3251 BEGIN
3252
3253 l_progress := '000';
3254 IF g_debug_stmt THEN
3255 PO_DEBUG.debug_begin(l_log_head);
3256 PO_DEBUG.debug_var(l_log_head,l_progress,'p_document_id', p_document_id);
3257 PO_DEBUG.debug_var(l_log_head,l_progress,'p_document_type', p_document_type);
3258 END IF;
3259
3260 IF (p_document_type = 'PO') THEN
3261 l_progress := '100';
3262 IF g_debug_stmt THEN
3263 PO_DEBUG.debug_stmt(l_log_head,l_progress, 'Reset retroactive_date for PO');
3264 END IF; /* IF g_debug_stmt */
3265
3266 -- SQL What: Reset retroactive_date for this PO
3267 -- SQL Why : For Standard PO, column po_lines.retroactive_date will
3268 -- be updated if any retroactive pricing changes. Reset it
3269 -- to NULL after processing retroactive pricing.
3270 UPDATE po_lines_all
3271 SET retroactive_date = NULL,
3272 last_update_date = SYSDATE,
3273 last_updated_by = l_user_id
3274 WHERE po_header_id = p_document_id;
3275 ELSE
3276 l_progress := '200';
3277 IF g_debug_stmt THEN
3278 PO_DEBUG.debug_stmt(l_log_head,l_progress, 'Reset retroactive_date for Release');
3279 END IF; /* IF g_debug_stmt */
3280
3281 -- SQL What: Find out any retroactive pricing change for this Release
3282 -- SQL Why : For Release, column po_line_locations.retroactive_date will
3283 -- be updated if any retroactive pricing changes, Reset it
3284 -- to the corresponding blanket line retroactive_date after
3285 -- processing retroactive pricing.
3286 UPDATE po_line_locations_all pll
3287 SET retroactive_date = (SELECT pl.retroactive_date
3288 FROM po_lines_all pl
3289 WHERE pl.po_line_id = pll.po_line_id),
3290 last_update_date = SYSDATE,
3291 last_updated_by = l_user_id
3292 WHERE pll.po_release_id = p_document_id;
3293 END IF; /* IF (p_document_type = 'PO') */
3294
3295 l_progress := '300';
3296 IF g_debug_stmt THEN
3297 PO_DEBUG.debug_end(l_log_head);
3298 END IF;
3299
3300 COMMIT; -- <Bug 3251646>
3301
3302 EXCEPTION
3303 WHEN OTHERS THEN
3304 IF g_debug_unexp THEN
3305 PO_DEBUG.debug_exc(l_log_head,l_progress);
3306 END IF;
3307
3308 RAISE;
3309
3310 END Reset_Retro_Update;
3311
3312 --------------------------------------------------------------------------------
3313 --Start of Comments
3314 --Name: Retro_Invoice_Release
3315 --Pre-reqs:
3316 -- None.
3317 --Modifies:
3318 -- PO_DISTRIBUTIONS_ALL.invoice_adjustment_flag.
3319 --Locks:
3320 -- None.
3321 --Function:
3322 -- This procedure updates invoice adjustment flag, and calls Costing
3323 -- and Inventory APIs. This is called from Approval workflow.
3324 --Parameters:
3325 --IN:
3326 --p_api_version
3327 -- Version number of API that caller expects. It
3328 -- should match the l_api_version defined in the
3329 -- procedure (expected value : 1.0)
3330 --p_document_id
3331 -- The id of the document (po_header_id or po_release_id)
3332 --p_document_type
3333 -- The type of the document
3334 -- PO : Standard PO
3335 -- RELEASE : Release
3336 --OUT:
3337 --x_return_status
3338 -- FND_API.G_RET_STS_SUCCESS if API succeeds
3339 -- FND_API.G_RET_STS_ERROR if API fails
3340 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
3341 --x_msg_count
3342 -- Number of Error messages
3343 --x_msg_data
3344 -- Contains error msg in case x_return_status returned
3345 -- FND_API.G_RET_STS_ERROR or FND_API.G_RET_STS_UNEXP_ERROR
3346 --Testing:
3347 --
3348 --End of Comments
3349 -------------------------------------------------------------------------------
3350
3351 PROCEDURE Retro_Invoice_Release(p_api_version IN NUMBER,
3352 p_document_id IN NUMBER,
3353 p_document_type IN VARCHAR2,
3354 x_return_status OUT NOCOPY VARCHAR2,
3355 x_msg_count OUT NOCOPY NUMBER,
3356 x_msg_data OUT NOCOPY VARCHAR2)
3357 IS
3358 PRAGMA AUTONOMOUS_TRANSACTION;
3359 -- Bug 3251646
3360 -- The autonomous transaction is required if the workflow function modifies
3361 -- records in main document entity tables.
3362
3363 l_api_name CONSTANT varchar2(30) := 'Retro_Invoice_Release';
3364 l_api_version CONSTANT NUMBER := 1.0;
3365 l_log_head CONSTANT VARCHAR2(100):= g_log_head || l_api_name;
3366 l_progress VARCHAR2(3);
3367 l_document_id NUMBER;
3368
3369 -- Bug 3314204, Pass Inventory API price in functional price
3370 l_base_curr_precision FND_CURRENCIES.precision%TYPE := 2;
3371
3372 CURSOR c_stdpo(p_po_header_id NUMBER) IS
3373 -- SQL What: Querying for standard PO which is not consigned
3374 -- SQL Why: Need to process retroactive price correction of
3375 -- invoices and receipt account adjustments
3376 -- SQL Join: po_header_id
3377 SELECT poll.po_header_id,
3378 poll.po_release_id,
3379 poll.po_line_id,
3380 poll.line_location_id,
3381 poll.quantity_billed,
3382 poll.price_override new_price,
3383 polla.price_override old_price
3384 FROM po_line_locations poll,
3385 po_line_locations_archive polla
3386 WHERE poll.po_header_id = p_po_header_id
3387 AND poll.po_release_id IS NULL
3388 AND ((poll.accrue_on_receipt_flag = 'Y' AND
3389 (poll.quantity_received > 0 OR
3390 poll.quantity_billed > 0)) OR
3391 NVL(poll.accrue_on_receipt_flag, 'N') = 'N') -- <Bug 3197792>
3392 AND poll.line_location_id = polla.line_location_id
3393 AND polla.latest_external_flag = 'Y'
3394 AND poll.price_override <> polla.price_override; -- Bug 3526448
3395
3396
3397 CURSOR c_release(p_po_release_id NUMBER) IS
3398 -- SQL What: Querying for Rlease which is not consigned
3399 -- SQL Why: Need to process retroactive price correction of
3400 -- invoices and receipt account adjustments
3401 -- SQL Join: po_release_id
3402 SELECT poll.po_header_id,
3403 poll.po_release_id,
3404 poll.po_line_id,
3405 poll.line_location_id,
3406 poll.quantity_billed,
3407 poll.price_override new_price,
3408 polla.price_override old_price
3409 FROM po_line_locations poll,
3410 po_line_locations_archive polla
3411 WHERE poll.po_release_id = p_po_release_id
3412 AND ((poll.accrue_on_receipt_flag = 'Y' AND
3413 (poll.quantity_received > 0 OR
3414 poll.quantity_billed > 0)) OR
3415 NVL(poll.accrue_on_receipt_flag, 'N') = 'N') -- <Bug 3197792>
3416 AND poll.line_location_id = polla.line_location_id
3417 AND polla.latest_external_flag = 'Y'
3418 AND poll.price_override <> polla.price_override; -- Bug 3526448
3419
3420 CURSOR c_consigned_stdpo(p_po_header_id NUMBER) IS
3421 -- SQL What: Querying for standard PO which is consigned
3422 -- SQL Why: Need to process retroactive price correction of
3423 -- invoices and receipt account adjustments
3424 -- SQL Join: po_header_id
3425 SELECT poh.po_header_id,
3426 to_number(NULL) po_release_id, --<Bug 3292429>
3427 pol.from_header_id, -- <Bug 3245719>
3428 poh.currency_code,
3429 poh.rate_type,
3430 poh.rate_date,
3431 poh.rate,
3432 pol.po_line_id,
3433 pol.item_id inventory_item_id,
3434 -- Bug 3393219, Consumption transaction owning org
3435 -- fsp.inventory_organization_id organization_id,
3436 pod.destination_organization_id organization_id,
3437 poll.line_location_id,
3438 poll.quantity_billed,
3439 pol.unit_meas_lookup_code transaction_uom,
3440 -- Bug 3314204, Pass Inventory API price in functional price
3441 -- Bug 3303148, Include Non-Recovery Tax
3442 -- Bug 3834275, added nvl to non recoverable tax
3443 round((round(poll.price_override * poll.quantity,
3444 l_base_curr_precision) +
3445 nvl(pod.nonrecoverable_tax,0)) * NVL(pod.rate, 1),
3446 l_base_curr_precision) / poll.quantity new_price,
3447 -- poll.price_override new_price,
3448 round((round(polla.price_override * poll.quantity,
3449 l_base_curr_precision) +
3450 nvl(poda.nonrecoverable_tax,0)) * NVL(pod.rate, 1),
3451 l_base_curr_precision) / poll.quantity old_price,
3452 -- polla.price_override old_price,
3453 poll.quantity transaction_quantity,
3454 round((round(poll.price_override * poll.quantity,
3455 l_base_curr_precision) +
3456 nvl(pod.nonrecoverable_tax,0)) * NVL(pod.rate, 1),
3457 l_base_curr_precision) / poll.quantity -
3458 round((round(polla.price_override * poll.quantity,
3459 l_base_curr_precision) +
3460 nvl(poda.nonrecoverable_tax,0)) * NVL(pod.rate, 1),
3461 l_base_curr_precision) / poll.quantity transaction_cost,
3462 -- poll.price_override - polla.price_override transaction_cost,
3463 pod.po_distribution_id,
3464 pod.project_id,
3465 pod.task_id,
3466 pod.accrual_account_id distribute_account_id
3467 FROM po_headers poh,
3468 po_lines_all pol, -- <R12 MOAC>
3469 -- Bug 3393219, Consumption transaction owning org
3470 -- financials_system_parameters fsp,
3471 po_line_locations_all poll, -- <R12 MOAC>
3472 po_line_locations_archive_all polla, -- <R12 MOAC>
3473 po_distributions_all pod, -- <R12 MOAC>
3474 -- Bug 3314204, 3303148
3475 po_distributions_archive_all poda -- <R12 MOAC>
3476 WHERE pol.po_header_id = p_po_header_id
3477 AND pol.po_header_id = poh.po_header_id
3478 AND pol.po_line_id = poll.po_line_id
3479 AND poll.line_location_id = pod.line_location_id
3480 AND poll.line_location_id = polla.line_location_id
3481 AND polla.latest_external_flag = 'Y'
3482 -- Bug 3314204, 3303148
3483 AND pod.po_distribution_id = poda.po_distribution_id
3484 AND poda.latest_external_flag = 'Y'
3485 AND poll.price_override <> polla.price_override; -- Bug 3526448
3486
3487 CURSOR c_consigned_release(p_po_release_id NUMBER) IS
3488 -- SQL What: Querying for Rlease which is consigned
3489 -- SQL Why: Need to process retroactive price correction of
3490 -- invoices and receipt account adjustments
3491 -- SQL Join: po_release_id
3492 SELECT to_number(NULL) po_header_id, --<Bug 3292429>
3493 por.po_release_id,
3494 poh.po_header_id from_header_id,
3495 poh.currency_code,
3496 poh.rate_type,
3497 poh.rate_date,
3498 poh.rate,
3499 pol.po_line_id,
3500 pol.item_id inventory_item_id,
3501 -- Bug 3393219, Consumption transaction owning org
3502 -- fsp.inventory_organization_id organization_id,
3503 pod.destination_organization_id organization_id,
3504 poll.line_location_id,
3505 poll.quantity_billed,
3506 pol.unit_meas_lookup_code transaction_uom,
3507 -- Bug 3314204, Pass Inventory API price in functional price
3508 -- Bug 3303148, Include Non-Recovery Tax
3509 -- Bug 3834275, added nvl to non recoverable tax
3510 round((round(poll.price_override * poll.quantity,
3511 l_base_curr_precision) +
3512 nvl(pod.nonrecoverable_tax,0)) * NVL(pod.rate, 1),
3513 l_base_curr_precision) / poll.quantity new_price,
3514 -- poll.price_override new_price,
3515 round((round(polla.price_override * poll.quantity,
3516 l_base_curr_precision) +
3517 nvl(poda.nonrecoverable_tax,0)) * NVL(pod.rate, 1),
3518 l_base_curr_precision) / poll.quantity old_price,
3519 -- polla.price_override old_price,
3520 poll.quantity transaction_quantity,
3521 round((round(poll.price_override * poll.quantity,
3522 l_base_curr_precision) +
3523 nvl(pod.nonrecoverable_tax,0)) * NVL(pod.rate, 1),
3524 l_base_curr_precision) / poll.quantity -
3525 round((round(polla.price_override * poll.quantity,
3526 l_base_curr_precision) +
3527 nvl(poda.nonrecoverable_tax,0)) * NVL(pod.rate, 1),
3528 l_base_curr_precision) / poll.quantity transaction_cost,
3529 -- poll.price_override - polla.price_override transaction_cost,
3530 pod.po_distribution_id,
3531 pod.project_id,
3532 pod.task_id,
3533 pod.accrual_account_id distribute_account_id
3534 FROM po_releases por,
3535 po_headers_all poh, -- <R12 MOAC>
3536 po_lines pol,
3537 -- Bug 3393219, Consumption transaction owning org
3538 -- financials_system_parameters fsp,
3539 po_line_locations_all poll, -- <R12 MOAC>
3540 po_line_locations_archive_all polla, -- <R12 MOAC>
3541 po_distributions_all pod, -- <R12 MOAC>
3542 -- Bug 3314204, 3303148
3543 po_distributions_archive poda
3544 WHERE por.po_release_id = p_po_release_id
3545 AND por.po_release_id = poll.po_release_id
3546 AND poll.po_header_id = poh.po_header_id
3547 AND poll.po_line_id = pol.po_line_id
3548 AND poll.line_location_id = pod.line_location_id
3549 AND poll.line_location_id = polla.line_location_id
3550 AND polla.latest_external_flag = 'Y'
3551 -- Bug 3314204, 3303148
3552 AND pod.po_distribution_id = poda.po_distribution_id
3553 AND poda.latest_external_flag = 'Y'
3554 AND poll.price_override <> polla.price_override; -- Bug 3526448
3555
3556
3557 l_po_header_ids_tbl po_tbl_number;
3558 l_po_release_ids_tbl po_tbl_number;
3559 l_from_header_ids_tbl po_tbl_number;
3560 l_currency_codes_tbl po_tbl_varchar30;
3561 l_rate_types_tbl po_tbl_varchar30;
3562 l_rate_dates_tbl po_tbl_date;
3563 l_rates_tbl po_tbl_number;
3564 l_po_line_ids_tbl po_tbl_number;
3565 l_inventory_item_ids_tbl po_tbl_number;
3566 l_organization_ids_tbl po_tbl_number;
3567 l_line_location_ids_tbl po_tbl_number;
3568 l_quantity_billeds_tbl po_tbl_number;
3569 l_transaction_uoms_tbl po_tbl_varchar30;
3570 l_new_prices_tbl po_tbl_number;
3571 l_old_prices_tbl po_tbl_number;
3572 l_transaction_quantitys_tbl po_tbl_number;
3573 l_transaction_costs_tbl po_tbl_number;
3574 l_distribution_ids_tbl po_tbl_number;
3575 l_project_ids_tbl po_tbl_number;
3576 l_task_ids_tbl po_tbl_number;
3577 l_dist_account_ids_tbl po_tbl_number;
3578
3579 l_primary_quantity PO_LINES.quantity%TYPE;
3580 l_primary_uom PO_LINES.unit_meas_lookup_code%TYPE;
3581 l_uom_code MTL_UNITS_OF_MEASURE.uom_code%TYPE;
3582
3583 l_consigned_flag PO_HEADERS.consigned_consumption_flag%TYPE;
3584 l_return_status varchar2(1);
3585 l_msg_data varchar2(2000);
3586 l_msg_count number;
3587
3588 -- To call Inventory API
3589 l_mtl_trx_rec INV_LOGICAL_TRANSACTION_GLOBAL.mtl_trx_rec_type;
3590 l_mtl_trx_tbl INV_LOGICAL_TRANSACTION_GLOBAL.mtl_trx_tbl_type;
3591 l_mtl_index BINARY_INTEGER := 1;
3592
3593 l_fnd_enabled varchar2(1);
3594
3595
3596 BEGIN
3597
3598 l_progress := '000';
3599 SAVEPOINT RETRO_INVOICE_SP;
3600
3601 -- Setup for writing the concurrent logs based on
3602 -- the concurrent log Profile
3603 IF g_log_mode = 'Y' THEN
3604 po_debug.set_file_io(TRUE);
3605 ELSE
3606 po_debug.set_file_io(null);
3607 END IF;
3608
3609 x_return_status := FND_API.G_RET_STS_SUCCESS;
3610 x_msg_count := 0;
3611 x_msg_data := NULL;
3612
3613 IF g_debug_stmt THEN
3614 PO_DEBUG.debug_begin(l_log_head);
3615 PO_DEBUG.debug_var(l_log_head,l_progress,'p_document_id', p_document_id);
3616 PO_DEBUG.debug_var(l_log_head,l_progress,'p_document_type', p_document_type);
3617 END IF;
3618
3619 -- Standard call to check for call compatibility
3620 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
3621 THEN
3622 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3623 END IF;
3624
3625 -- Bug 3314204, Pass Inventory API price in functional price
3626 SELECT nvl(FND.precision, 2)
3627 INTO l_base_curr_precision
3628 FROM FND_CURRENCIES FND,
3629 FINANCIALS_SYSTEM_PARAMETERS FSP,
3630 GL_SETS_OF_BOOKS GSB
3631 WHERE FSP.set_of_books_id = GSB.set_of_books_id AND
3632 FND.currency_code = GSB.currency_code;
3633
3634 l_progress := '010';
3635 IF g_debug_stmt THEN
3636 PO_DEBUG.debug_var(l_log_head,l_progress,
3637 'l_base_curr_precision', l_base_curr_precision);
3638 PO_DEBUG.debug_stmt(l_log_head,l_progress,
3639 'Check Consigned Consumption flag');
3640 END IF; /* IF g_debug_stmt */
3641
3642 l_progress := '020';
3643 IF (p_document_type = 'PO') THEN
3644 SELECT NVL(consigned_consumption_flag, 'N')
3645 INTO l_consigned_flag
3646 FROM PO_HEADERS
3647 WHERE po_header_id = p_document_id;
3648 ELSE
3649 SELECT NVL(consigned_consumption_flag, 'N')
3650 INTO l_consigned_flag
3651 FROM PO_RELEASES
3652 WHERE po_release_id = p_document_id;
3653 END IF; /* IF (p_document_type = 'PO') */
3654
3655 l_progress := '030';
3656 IF g_debug_stmt THEN
3657 PO_DEBUG.debug_var(l_log_head,l_progress,'l_consigned_flag', l_consigned_flag);
3658 END IF; /* IF g_debug_stmt */
3659
3660 IF (l_consigned_flag = 'N') THEN
3661 -- For standard POs and PO releases(not consigned),
3662 -- call the new accounting events API
3663 l_progress := '040';
3664 IF g_debug_stmt THEN
3665 PO_DEBUG.debug_stmt(l_log_head,l_progress,
3666 'Open Cursor for Not Consigned PO/RELEASE');
3667 END IF; /* IF g_debug_stmt */
3668
3669 l_progress := '050';
3670 IF (p_document_type = 'PO') THEN
3671 OPEN c_stdpo(p_document_id);
3672 ELSE
3673 OPEN c_release(p_document_id);
3674 END IF; /* IF (p_document_type = 'PO') */
3675
3676 l_progress := '060';
3677 IF g_debug_stmt THEN
3678 PO_DEBUG.debug_stmt(l_log_head,l_progress, 'Fetch from Cursor');
3679 END IF; /* IF g_debug_stmt */
3680
3681 LOOP
3682 IF (p_document_type = 'PO') THEN
3683 FETCH c_stdpo BULK COLLECT INTO
3684 l_po_header_ids_tbl,
3685 l_po_release_ids_tbl,
3686 l_po_line_ids_tbl,
3687 l_line_location_ids_tbl,
3688 l_quantity_billeds_tbl,
3689 l_new_prices_tbl,
3690 l_old_prices_tbl
3691 LIMIT G_BULK_LIMIT;
3692 ELSE
3693 FETCH c_release BULK COLLECT INTO
3694 l_po_header_ids_tbl,
3695 l_po_release_ids_tbl,
3696 l_po_line_ids_tbl,
3697 l_line_location_ids_tbl,
3698 l_quantity_billeds_tbl,
3699 l_new_prices_tbl,
3700 l_old_prices_tbl
3701 LIMIT G_BULK_LIMIT;
3702 END IF; /* IF (p_document_type = 'PO') */
3703
3704 l_progress := '070';
3705 IF l_po_header_ids_tbl.COUNT > 0 THEN
3706 IF g_debug_stmt THEN
3707 PO_DEBUG.debug_var(l_log_head,l_progress,'l_po_header_ids_tbl', l_po_header_ids_tbl);
3708 PO_DEBUG.debug_var(l_log_head,l_progress,'l_po_release_ids_tbl', l_po_release_ids_tbl);
3709 PO_DEBUG.debug_var(l_log_head,l_progress,'l_po_line_ids_tbl', l_po_line_ids_tbl);
3710 PO_DEBUG.debug_var(l_log_head,l_progress,'l_line_location_ids_tbl', l_line_location_ids_tbl);
3711 PO_DEBUG.debug_var(l_log_head,l_progress,'l_quantity_billeds_tbl', l_quantity_billeds_tbl);
3712 PO_DEBUG.debug_var(l_log_head,l_progress,'l_new_prices_tbl', l_new_prices_tbl);
3713 PO_DEBUG.debug_var(l_log_head,l_progress,'l_old_prices_tbl', l_old_prices_tbl);
3714 PO_DEBUG.debug_stmt(l_log_head,l_progress,
3715 'Before updating the invoice_adjustment_flag to R');
3716 END IF; /* IF g_debug_stmt */
3717
3718 l_progress := '080';
3719 FORALL i in 1..l_line_location_ids_tbl.COUNT
3720 UPDATE po_distributions_all
3721 SET invoice_adjustment_flag = 'R'
3722 WHERE line_location_id = l_line_location_ids_tbl(i)
3723 AND l_quantity_billeds_tbl(i) > 0;
3724
3725 l_progress := '090';
3726 IF g_debug_stmt THEN
3727 PO_DEBUG.debug_stmt(l_log_head,l_progress,
3728 'updated the invoice_adjustment_flag to R -- Rowcount: ' || SQL%ROWCOUNT);
3729 PO_DEBUG.debug_stmt(l_log_head,l_progress,
3730 'Before Calling Create_AccountingEvents()');
3731 END IF; /* IF g_debug_stmt */
3732
3733 FOR i IN l_po_header_ids_tbl.FIRST..l_po_header_ids_tbl.LAST LOOP
3734
3735 l_progress := '100';
3736 IF g_debug_stmt THEN
3737 PO_DEBUG.debug_stmt(l_log_head,l_progress,
3738 'Call RCV_AccrualAccounting_GRP.Create_AccountingEvents()');
3739 END IF;
3740 PO_DEBUG.put_line('Call RCV_AccrualAccounting_GRP.Create_AccountingEvents');
3741
3742 RCV_AccrualAccounting_GRP.Create_AccountingEvents(
3743 p_api_version => 1.0,
3744 p_source_type => 'RETROPRICE',
3745 p_po_header_id => l_po_header_ids_tbl(i),
3746 p_po_release_id => l_po_release_ids_tbl(i),
3747 p_po_line_id => l_po_line_ids_tbl(i),
3748 p_po_line_location_id=> l_line_location_ids_tbl(i),
3749 p_old_po_price => l_old_prices_tbl(i),
3750 p_new_po_price => l_new_prices_tbl(i),
3751 x_return_status => l_return_status,
3752 x_msg_count => l_msg_count,
3753 x_msg_data => l_msg_data);
3754
3755 l_progress := '105';
3756 IF g_debug_stmt THEN
3757 PO_DEBUG.debug_var(l_log_head,l_progress,'l_return_status', l_return_status);
3758 PO_DEBUG.debug_var(l_log_head,l_progress,'x_msg_count', l_msg_count);
3759 PO_DEBUG.debug_var(l_log_head,l_progress,'x_msg_data', l_msg_data);
3760 END IF; /* IF g_debug_stmt */
3761
3762 PO_DEBUG.put_line('Return status : ' || l_return_status);
3763 PO_DEBUG.put_line('Message Count: ' || l_msg_count);
3764 PO_DEBUG.put_line('Message data : '|| l_msg_data);
3765
3766 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) Then
3767 IF (l_return_status = FND_API.G_RET_STS_ERROR) Then
3768 RAISE FND_API.G_EXC_ERROR;
3769 ELSE
3770 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3771 END IF; /* IF (l_return_status = FND_API.G_RET_STS_ERROR) */
3772 END IF; /* IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) */
3773
3774 /** INVCONV rseshadr - call OPM API for process organizations **/
3775 l_progress := '106';
3776 IF g_debug_stmt THEN
3777 PO_DEBUG.debug_stmt(l_log_head,l_progress,
3778 'Call GMF_Rcv_Accounting_Pkg.Create_Adjust_Txns()');
3779 END IF;
3780 PO_DEBUG.put_line('Call GMF_Rcv_Accounting_Pkg.Create_Adjust_Txns');
3781
3782 GMF_Rcv_Accounting_Pkg.Create_Adjust_Txns(
3783 p_api_version => 1.0,
3784 p_init_msg_list => FND_API.G_FALSE,
3785 p_commit => FND_API.G_FALSE,
3786 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3787 p_po_header_id => l_po_header_ids_tbl(i),
3788 p_po_release_id => l_po_release_ids_tbl(i),
3789 p_po_line_id => l_po_line_ids_tbl(i),
3790 p_po_line_location_id=> l_line_location_ids_tbl(i),
3791 p_old_po_price => l_old_prices_tbl(i),
3792 p_new_po_price => l_new_prices_tbl(i),
3793 x_return_status => l_return_status,
3794 x_msg_count => l_msg_count,
3795 x_msg_data => l_msg_data);
3796
3797 l_progress := '108';
3798 IF g_debug_stmt THEN
3799 PO_DEBUG.debug_var(l_log_head,l_progress,'l_return_status', l_return_status);
3800 PO_DEBUG.debug_var(l_log_head,l_progress,'x_msg_count', l_msg_count);
3801 PO_DEBUG.debug_var(l_log_head,l_progress,'x_msg_data', l_msg_data);
3802 END IF; /* IF g_debug_stmt */
3803
3804 PO_DEBUG.put_line('Return status : ' || l_return_status);
3805 PO_DEBUG.put_line('Message Count: ' || l_msg_count);
3806 PO_DEBUG.put_line('Message data : '|| l_msg_data);
3807
3808 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) Then
3809 IF (l_return_status = FND_API.G_RET_STS_ERROR) Then
3810 RAISE FND_API.G_EXC_ERROR;
3811 ELSE
3812 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3813 END IF; /* IF (l_return_status = FND_API.G_RET_STS_ERROR) */
3814 END IF; /* IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) */
3815 /** INVCONV rseshadr - end of changes **/
3816
3817 END LOOP;
3818
3819 l_progress := '110';
3820 IF g_debug_stmt THEN
3821 PO_DEBUG.debug_stmt(l_log_head,l_progress,
3822 'After Calling Create_AccountingEvents()');
3823 END IF; /* IF g_debug_stmt */
3824
3825 END IF; /* IF l_po_header_ids_tbl.COUNT > 0 */
3826
3827 l_progress := '120';
3828 IF g_debug_stmt THEN
3829 PO_DEBUG.debug_stmt(l_log_head,l_progress, 'Check EXIT condition');
3830 END IF; /* IF g_debug_stmt */
3831
3832 IF (p_document_type = 'PO') THEN
3833 EXIT WHEN c_stdpo%NOTFOUND;
3834 ELSE
3835 EXIT WHEN c_release%NOTFOUND;
3836 END IF; /* IF (p_document_type = 'PO') */
3837
3838 l_progress := '125';
3839 IF g_debug_stmt THEN
3840 PO_DEBUG.debug_stmt(l_log_head,l_progress, 'Clear PL/SQL tables');
3841 END IF; /* IF g_debug_stmt */
3842
3843 l_po_header_ids_tbl.DELETE;
3844 l_po_release_ids_tbl.DELETE;
3845 l_po_line_ids_tbl.DELETE;
3846 l_line_location_ids_tbl.DELETE;
3847 l_quantity_billeds_tbl.DELETE;
3848 l_new_prices_tbl.DELETE;
3849 l_old_prices_tbl.DELETE;
3850
3851 END LOOP; /* c_stdpo / c_release */
3852
3853 l_progress := '130';
3854 IF g_debug_stmt THEN
3855 PO_DEBUG.debug_stmt(l_log_head,l_progress, 'Close Cursor');
3856 END IF; /* IF g_debug_stmt */
3857
3858 IF (p_document_type = 'PO') THEN
3859 CLOSE c_stdpo;
3860 ELSE
3861 CLOSE c_release;
3862 END IF; /* IF (p_document_type = 'PO') */
3863
3864 ELSE /* IF (l_consigned_flag = 'N') */
3865 -- For consumption advices, call the Inventory API
3866
3867 l_progress := '140';
3868 IF g_debug_stmt THEN
3869 PO_DEBUG.debug_stmt(l_log_head,l_progress,
3870 'Open Cursor for Consigned PO/RELEASE');
3871 END IF; /* IF g_debug_stmt */
3872
3873 l_progress := '150';
3874 IF (p_document_type = 'PO') THEN
3875 OPEN c_consigned_stdpo(p_document_id);
3876 ELSE
3877 OPEN c_consigned_release(p_document_id);
3878 END IF; /* IF (p_document_type = 'PO') */
3879
3880 l_progress := '160';
3881 IF g_debug_stmt THEN
3882 PO_DEBUG.debug_stmt(l_log_head,l_progress, 'Fetch from Cursor');
3883 END IF; /* IF g_debug_stmt */
3884
3885 LOOP
3886 l_mtl_index := 1;
3887
3888 IF (p_document_type = 'PO') THEN
3889 FETCH c_consigned_stdpo BULK COLLECT INTO
3890 l_po_header_ids_tbl,
3891 l_po_release_ids_tbl,
3892 l_from_header_ids_tbl,
3893 l_currency_codes_tbl,
3894 l_rate_types_tbl,
3895 l_rate_dates_tbl,
3896 l_rates_tbl,
3897 l_po_line_ids_tbl,
3898 l_inventory_item_ids_tbl,
3899 l_organization_ids_tbl,
3900 l_line_location_ids_tbl,
3901 l_quantity_billeds_tbl,
3902 l_transaction_uoms_tbl,
3903 l_new_prices_tbl,
3904 l_old_prices_tbl,
3905 l_transaction_quantitys_tbl,
3906 l_transaction_costs_tbl,
3907 l_distribution_ids_tbl,
3908 l_project_ids_tbl,
3909 l_task_ids_tbl,
3910 l_dist_account_ids_tbl
3911 LIMIT G_BULK_LIMIT;
3912 ELSE
3913 FETCH c_consigned_release BULK COLLECT INTO
3914 l_po_header_ids_tbl,
3915 l_po_release_ids_tbl,
3916 l_from_header_ids_tbl,
3917 l_currency_codes_tbl,
3918 l_rate_types_tbl,
3919 l_rate_dates_tbl,
3920 l_rates_tbl,
3921 l_po_line_ids_tbl,
3922 l_inventory_item_ids_tbl,
3923 l_organization_ids_tbl,
3924 l_line_location_ids_tbl,
3925 l_quantity_billeds_tbl,
3926 l_transaction_uoms_tbl,
3927 l_new_prices_tbl,
3928 l_old_prices_tbl,
3929 l_transaction_quantitys_tbl,
3930 l_transaction_costs_tbl,
3931 l_distribution_ids_tbl,
3932 l_project_ids_tbl,
3933 l_task_ids_tbl,
3934 l_dist_account_ids_tbl
3935 LIMIT G_BULK_LIMIT;
3936 END IF; /* IF (p_document_type = 'PO') */
3937
3938 l_progress := '170';
3939 IF l_po_header_ids_tbl.COUNT > 0 THEN
3940 IF g_debug_stmt THEN
3941 PO_DEBUG.debug_var(l_log_head,l_progress,'l_po_header_ids_tbl', l_po_header_ids_tbl);
3942 PO_DEBUG.debug_var(l_log_head,l_progress,'l_po_release_ids_tbl', l_po_release_ids_tbl);
3943 PO_DEBUG.debug_var(l_log_head,l_progress,'l_from_header_ids_tbl', l_from_header_ids_tbl);
3944 PO_DEBUG.debug_var(l_log_head,l_progress,'l_currency_codes_tbl', l_currency_codes_tbl);
3945 PO_DEBUG.debug_var(l_log_head,l_progress,'l_rate_types_tbl', l_rate_types_tbl);
3946 PO_DEBUG.debug_var(l_log_head,l_progress,'l_rate_dates_tbl', l_rate_dates_tbl);
3947 PO_DEBUG.debug_var(l_log_head,l_progress,'l_rates_tbl', l_rates_tbl);
3948 PO_DEBUG.debug_var(l_log_head,l_progress,'l_po_line_ids_tbl', l_po_line_ids_tbl);
3949 PO_DEBUG.debug_var(l_log_head,l_progress,'l_inventory_item_ids_tbl', l_inventory_item_ids_tbl);
3950 PO_DEBUG.debug_var(l_log_head,l_progress,'l_organization_ids_tbl', l_organization_ids_tbl);
3951 PO_DEBUG.debug_var(l_log_head,l_progress,'l_line_location_ids_tbl', l_line_location_ids_tbl);
3952 PO_DEBUG.debug_var(l_log_head,l_progress,'l_quantity_billeds_tbl', l_quantity_billeds_tbl);
3953 PO_DEBUG.debug_var(l_log_head,l_progress,'l_transaction_uoms_tbl', l_transaction_uoms_tbl);
3954 PO_DEBUG.debug_var(l_log_head,l_progress,'l_new_prices_tbl', l_new_prices_tbl);
3955 PO_DEBUG.debug_var(l_log_head,l_progress,'l_old_prices_tbl', l_old_prices_tbl);
3956 PO_DEBUG.debug_var(l_log_head,l_progress,'l_transaction_quantitys_tbl', l_transaction_quantitys_tbl);
3957 PO_DEBUG.debug_var(l_log_head,l_progress,'l_transaction_costs_tbl', l_transaction_costs_tbl);
3958 PO_DEBUG.debug_var(l_log_head,l_progress,'l_distribution_ids_tbl', l_distribution_ids_tbl);
3959 PO_DEBUG.debug_var(l_log_head,l_progress,'l_project_ids_tbl', l_project_ids_tbl);
3960 PO_DEBUG.debug_var(l_log_head,l_progress,'l_task_ids_tbl', l_task_ids_tbl);
3961 PO_DEBUG.debug_var(l_log_head,l_progress,'l_dist_account_ids_tbl', l_dist_account_ids_tbl);
3962 PO_DEBUG.debug_stmt(l_log_head,l_progress,
3963 'Before updating the invoice_adjustment_flag to R');
3964 END IF; /* IF g_debug_stmt */
3965
3966 l_progress := '180';
3967 FORALL i in 1..l_distribution_ids_tbl.COUNT
3968 UPDATE po_distributions
3969 SET invoice_adjustment_flag = 'R'
3970 WHERE po_distribution_id = l_distribution_ids_tbl(i)
3971 AND l_quantity_billeds_tbl(i) > 0;
3972
3973 l_progress := '200';
3974 IF g_debug_stmt THEN
3975 PO_DEBUG.debug_stmt(l_log_head,l_progress,
3976 'updated the invoice_adjustment_flag to R -- Rowcount: ' || SQL%ROWCOUNT);
3977 PO_DEBUG.debug_stmt(l_log_head,l_progress, 'Before Calling Inventory API');
3978 END IF; /* IF g_debug_stmt */
3979
3980 FOR i IN l_po_header_ids_tbl.FIRST..l_po_header_ids_tbl.LAST LOOP
3981
3982 l_progress := '205';
3983 IF g_debug_stmt THEN
3984 PO_DEBUG.debug_stmt(l_log_head,l_progress, 'Prepare Record before Call Inventory API');
3985 END IF; /* IF g_debug_stmt */
3986
3987 -- calculate Primary Quantity and UOM
3988 RCV_QUANTITIES_S.get_primary_qty_uom (
3989 x_transaction_qty => l_transaction_quantitys_tbl(i),
3990 x_transaction_uom => l_transaction_uoms_tbl(i),
3991 x_item_id => l_inventory_item_ids_tbl(i),
3992 x_organization_id => l_organization_ids_tbl(i),
3993 x_primary_qty => l_primary_quantity,
3994 x_primary_uom => l_primary_uom);
3995
3996 l_progress := '210';
3997 IF g_debug_stmt THEN
3998 PO_DEBUG.debug_var(l_log_head,l_progress,'l_primary_quantity', l_primary_quantity);
3999 PO_DEBUG.debug_var(l_log_head,l_progress,'l_primary_uom', l_primary_uom);
4000 PO_DEBUG.debug_stmt(l_log_head,l_progress, 'Get UOM code');
4001 END IF; /* IF g_debug_stmt */
4002
4003 BEGIN
4004 -- INV expects the uom_code whereas PO stores unit_of_measure.
4005 SELECT mum.uom_code
4006 INTO l_uom_code
4007 FROM mtl_units_of_measure mum
4008 WHERE mum.unit_of_measure = l_transaction_uoms_tbl(i);
4009 EXCEPTION
4010 WHEN OTHERS THEN
4011 l_uom_code := NULL;
4012 END;
4013
4014 l_progress := '220';
4015 IF g_debug_stmt THEN
4016 PO_DEBUG.debug_var(l_log_head,l_progress,'l_uom_code', l_uom_code);
4017 END IF; /* IF g_debug_stmt */
4018
4019 l_mtl_trx_rec.organization_id := l_organization_ids_tbl(i);
4020 l_progress := '225';
4021 l_mtl_trx_rec.inventory_item_id := l_inventory_item_ids_tbl(i);
4022 l_progress := '230';
4023 l_mtl_trx_rec.transaction_type_id := 20; -- Retroactive Price Update
4024 l_progress := '235';
4025 l_mtl_trx_rec.transaction_action_id := 25; -- Retroactive Price Update
4026 l_progress := '240';
4027 l_mtl_trx_rec.transaction_source_type_id:= 1; -- PO
4028 l_progress := '245';
4029 l_mtl_trx_rec.transaction_source_id := l_from_header_ids_tbl(i);
4030 l_progress := '250';
4031 l_mtl_trx_rec.transaction_quantity := l_transaction_quantitys_tbl(i);
4032 l_progress := '255';
4033 l_mtl_trx_rec.transaction_uom := l_uom_code;
4034 l_progress := '260';
4035 l_mtl_trx_rec.primary_quantity := l_primary_quantity;
4036 l_progress := '265';
4037 l_mtl_trx_rec.transaction_date := SYSDATE;
4038 l_progress := '270';
4039 l_mtl_trx_rec.distribution_account_id := l_dist_account_ids_tbl(i);
4040 l_progress := '275';
4041 l_mtl_trx_rec.transaction_cost := l_transaction_costs_tbl(i);
4042 l_progress := '280';
4043 l_mtl_trx_rec.currency_code := l_currency_codes_tbl(i);
4044 l_progress := '285';
4045 l_mtl_trx_rec.currency_conversion_rate:= l_rates_tbl(i);
4046 l_progress := '290';
4047 l_mtl_trx_rec.currency_conversion_type:= l_rate_types_tbl(i);
4048 l_progress := '295';
4049 l_mtl_trx_rec.currency_conversion_date:= l_rate_dates_tbl(i);
4050 l_progress := '300';
4051 l_mtl_trx_rec.project_id := l_project_ids_tbl(i);
4052 l_progress := '305';
4053 l_mtl_trx_rec.task_id := l_task_ids_tbl(i);
4054 l_progress := '310';
4055 l_mtl_trx_rec.consumption_release_id := l_po_release_ids_tbl(i);
4056 l_progress := '315';
4057 l_mtl_trx_rec.consumption_po_header_id:= l_po_header_ids_tbl(i);
4058 l_progress := '320';
4059 l_mtl_trx_rec.old_po_price := l_old_prices_tbl(i);
4060 l_progress := '325';
4061 l_mtl_trx_rec.new_po_price := l_new_prices_tbl(i);
4062 l_progress := '330';
4063 l_mtl_trx_rec.parent_transaction_flag := NULL; -- Not parent transaction
4064 l_progress := '335';
4065 l_mtl_trx_rec.po_distribution_id := l_distribution_ids_tbl(i); -- bug5112228
4066
4067
4068 l_progress := '340';
4069 l_mtl_trx_tbl(l_mtl_index) := l_mtl_trx_rec;
4070 l_mtl_index := l_mtl_index + 1;
4071
4072 END LOOP;
4073
4074 l_progress := '350';
4075 IF g_debug_stmt THEN
4076 PO_DEBUG.debug_stmt(l_log_head,l_progress, 'Prepare Record Done');
4077 END IF; /* IF g_debug_stmt */
4078
4079 IF (l_mtl_index > 0) THEN
4080 l_progress := '360';
4081 IF g_debug_stmt THEN
4082 PO_DEBUG.debug_stmt(l_log_head,l_progress, 'Call Inventory API');
4083 END IF; /* IF g_debug_stmt */
4084 PO_DEBUG.put_line('Call Inventory API');
4085
4086 INV_LOGICAL_TRANSACTIONS_PUB.create_logical_transactions(
4087 p_api_version_number => 1.0,
4088 p_init_msg_lst => FND_API.G_FALSE,
4089 p_validation_flag => FND_API.G_TRUE,
4090 p_trx_flow_header_id => NULL,
4091 p_defer_logical_transactions=> NULL,
4092 p_logical_trx_type_code => 4,
4093 p_exploded_flag => 2,
4094 p_mtl_trx_tbl => l_mtl_trx_tbl,
4095 x_return_status => l_return_status,
4096 x_msg_count => l_msg_count,
4097 x_msg_data => l_msg_data);
4098
4099 l_progress := '370';
4100 IF g_debug_stmt THEN
4101 PO_DEBUG.debug_var(l_log_head,l_progress,'l_return_status', l_return_status);
4102 PO_DEBUG.debug_var(l_log_head,l_progress,'x_msg_count', x_msg_count);
4103 PO_DEBUG.debug_var(l_log_head,l_progress,'x_msg_data', x_msg_data);
4104 END IF; /* IF g_debug_stmt */
4105
4106 PO_DEBUG.put_line('Return status : ' || l_return_status);
4107 PO_DEBUG.put_line('Message Count: ' || l_msg_count);
4108 PO_DEBUG.put_line('Message data : '|| l_msg_data);
4109
4110 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) Then
4111 IF (l_return_status = FND_API.G_RET_STS_ERROR) Then
4112 RAISE FND_API.G_EXC_ERROR;
4113 ELSE
4114 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4115 END IF; /* IF (l_return_status = FND_API.G_RET_STS_ERROR) */
4116 END IF; /* IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) */
4117
4118 END IF; /* IF (l_mtl_index > 0) */
4119
4120 l_progress := '380';
4121 IF g_debug_stmt THEN
4122 PO_DEBUG.debug_stmt(l_log_head,l_progress, 'After Call Inventory API');
4123 END IF; /* IF g_debug_stmt */
4124
4125 END IF; /* IF l_po_header_ids_tbl.COUNT > 0 */
4126
4127 l_progress := '390';
4128 IF g_debug_stmt THEN
4129 PO_DEBUG.debug_stmt(l_log_head,l_progress, 'Check EXIT condition');
4130 END IF; /* IF g_debug_stmt */
4131
4132 IF (p_document_type = 'PO') THEN
4133 EXIT WHEN c_consigned_stdpo%NOTFOUND;
4134 ELSE
4135 EXIT WHEN c_consigned_release%NOTFOUND;
4136 END IF; /* IF (p_document_type = 'PO') */
4137
4138 l_progress := '400';
4139 IF g_debug_stmt THEN
4140 PO_DEBUG.debug_stmt(l_log_head,l_progress, 'Clear PL/SQL tables');
4141 END IF; /* IF g_debug_stmt */
4142
4143 l_po_header_ids_tbl.DELETE;
4144 l_po_release_ids_tbl.DELETE;
4145 l_from_header_ids_tbl.DELETE;
4146 l_currency_codes_tbl.DELETE;
4147 l_rate_types_tbl.DELETE;
4148 l_rate_dates_tbl.DELETE;
4149 l_rates_tbl.DELETE;
4150 l_po_line_ids_tbl.DELETE;
4151 l_inventory_item_ids_tbl.DELETE;
4152 l_organization_ids_tbl.DELETE;
4153 l_line_location_ids_tbl.DELETE;
4154 l_quantity_billeds_tbl.DELETE;
4155 l_transaction_uoms_tbl.DELETE;
4156 l_new_prices_tbl.DELETE;
4157 l_old_prices_tbl.DELETE;
4158 l_transaction_quantitys_tbl.DELETE;
4159 l_transaction_costs_tbl.DELETE;
4160 l_distribution_ids_tbl.DELETE;
4161 l_project_ids_tbl.DELETE;
4162 l_task_ids_tbl.DELETE;
4163 l_dist_account_ids_tbl.DELETE;
4164 l_mtl_trx_tbl.DELETE;
4165
4166 END LOOP; /* c_consigned_stdpo / c_consigned_release */
4167
4168 l_progress := '410';
4169 IF g_debug_stmt THEN
4170 PO_DEBUG.debug_stmt(l_log_head,l_progress, 'Close Cursor');
4171 END IF; /* IF g_debug_stmt */
4172
4173 IF (p_document_type = 'PO') THEN
4174 CLOSE c_consigned_stdpo;
4175 ELSE
4176 CLOSE c_consigned_release;
4177 END IF; /* IF (p_document_type = 'PO') */
4178
4179 END IF; /* IF (l_consigned_flag = 'N') */
4180
4181 l_progress := '430';
4182 IF g_debug_stmt THEN
4183 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
4184 PO_DEBUG.debug_end(l_log_head);
4185 END IF;
4186
4187 COMMIT; -- <Bug 3251646>
4188
4189 EXCEPTION
4190 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4191 ROLLBACK TO RETRO_INVOICE_SP;
4192 x_msg_data := FND_MSG_PUB.GET(p_msg_index => FND_MSG_PUB.G_LAST,
4193 p_encoded => 'F');
4194 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4195
4196 IF g_debug_unexp THEN
4197 PO_DEBUG.debug_exc(l_log_head,l_progress);
4198 END IF;
4199
4200 IF (c_stdpo%ISOPEN) THEN
4201 CLOSE c_stdpo;
4202 END IF;
4203 IF (c_release%ISOPEN) THEN
4204 CLOSE c_release;
4205 END IF;
4206 IF (c_consigned_stdpo%ISOPEN) THEN
4207 CLOSE c_consigned_stdpo;
4208 END IF;
4209 IF (c_consigned_release%ISOPEN) THEN
4210 CLOSE c_consigned_release;
4211 END IF;
4212 WHEN FND_API.G_EXC_ERROR THEN
4213 ROLLBACK TO RETRO_INVOICE_SP;
4214 x_msg_data := FND_MSG_PUB.GET(p_msg_index => FND_MSG_PUB.G_LAST,
4215 p_encoded => 'F');
4216 x_return_status := FND_API.G_RET_STS_ERROR;
4217
4218 IF g_debug_unexp THEN
4219 PO_DEBUG.debug_exc(l_log_head,l_progress);
4220 END IF;
4221
4222 IF (c_stdpo%ISOPEN) THEN
4223 CLOSE c_stdpo;
4224 END IF;
4225 IF (c_release%ISOPEN) THEN
4226 CLOSE c_release;
4227 END IF;
4228 IF (c_consigned_stdpo%ISOPEN) THEN
4229 CLOSE c_consigned_stdpo;
4230 END IF;
4231 IF (c_consigned_release%ISOPEN) THEN
4232 CLOSE c_consigned_release;
4233 END IF;
4234 WHEN OTHERS THEN
4235 ROLLBACK TO RETRO_INVOICE_SP;
4236 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
4237 FND_MSG_PUB.add_exc_msg(G_PKG_NAME, l_api_name);
4238 END IF;
4239
4240 IF g_debug_unexp THEN
4241 PO_DEBUG.debug_exc(l_log_head,l_progress);
4242 END IF;
4243
4244 x_msg_data := FND_MSG_PUB.GET(p_msg_index => FND_MSG_PUB.G_LAST,
4245 p_encoded => 'F');
4246 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4247
4248 IF (c_stdpo%ISOPEN) THEN
4249 CLOSE c_stdpo;
4250 END IF;
4251 IF (c_release%ISOPEN) THEN
4252 CLOSE c_release;
4253 END IF;
4254 IF (c_consigned_stdpo%ISOPEN) THEN
4255 CLOSE c_consigned_stdpo;
4256 END IF;
4257 IF (c_consigned_release%ISOPEN) THEN
4258 CLOSE c_consigned_release;
4259 END IF;
4260
4261 END Retro_Invoice_Release;
4262 -- <FPJ Retroactive Price END>
4263
4264 --------------------------------------------------------------------------------
4265 --Start of Comments :Bug 3231062
4266 --Name: Is_Retro_Project_Allowed
4267 --Pre-reqs:
4268 -- None.
4269 --Modifies:
4270 -- None.
4271 --Locks:
4272 -- None.
4273 --Function:
4274 -- This function returns true if retroactive pricing update allow on line with
4275 -- project reference.
4276 -- If the profile option value is set to 'All Releases' and if the Archival
4277 -- mode for the Operating Unit is set to 'Approve', you can update the PO Price
4278 -- even if they were received and/or invoiced, except if there is project
4279 -- information associated with any of the distributions, and PA 11.5.10 is not
4280 -- installed.
4281 --Note:
4282 -- Removed after 11iX
4283 --Parameters:
4284 --IN:
4285 --p_std_po_price_change
4286 --p_po_line_id
4287 --p_po_line_loc_id
4288 --RETURN:
4289 -- 'Y': Retroactive pricing update is allowed
4290 -- 'N': Retroactive pricing update is not allowed
4291 --Testing:
4292 --
4293 --End of Comments
4294 -------------------------------------------------------------------------------
4295 FUNCTION Is_Retro_Project_Allowed(p_std_po_price_change IN VARCHAR2,
4296 p_po_line_id IN NUMBER,
4297 p_po_line_loc_id IN NUMBER
4298 )
4299 RETURN VARCHAR2
4300 IS
4301
4302 l_retro_proj_allowed VARCHAR2(1) := 'Y';
4303 l_module VARCHAR2(100);
4304 l_api_name CONSTANT VARCHAR2(50) := 'Is_Retro_Project_Allowed';
4305
4306 BEGIN
4307
4308 l_module := g_log_head||l_api_name||'.'||'000'||'.';
4309
4310 IF g_debug_stmt then
4311 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4312 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,l_module,
4313 'Project Check');
4314 END IF;
4315 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4316 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,l_module,
4317 'Price Change on std PO :' || p_std_po_price_change);
4318 END IF;
4319 END IF;
4320
4321 IF g_projects_11i10_installed = 'Y' THEN
4322 IF g_debug_stmt THEN
4323 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4324 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,l_module,
4325 'Project 11i10 Enabled');
4326 END IF;
4327 END IF;
4328 RETURN 'Y';
4329 END IF;
4330
4331 IF p_std_po_price_change = 'Y' THEN
4332 BEGIN
4333 -- SQL What: Returns Y if there are any shipment which has project information
4334 -- and is received/invoiced, 0 otherwise.
4335 -- SQL Why: To prevent retro price changes if there are such shipments.
4336 SELECT 'N'
4337 INTO l_retro_proj_allowed
4338 FROM DUAL
4339 WHERE EXISTS (SELECT 'has project information'
4340 FROM PO_LINE_LOCATIONS_ALL pll,
4341 PO_DISTRIBUTIONS_ALL pod
4342 WHERE pll.po_line_id = p_po_line_id
4343 AND pod.line_location_id = pll.line_location_id
4344 AND ((NVL(pll.quantity_received,0) > 0 AND
4345 NVL(pll.accrue_on_receipt_flag,'N') = 'Y') OR
4346 NVL(pll.quantity_billed,0) > 0)
4347 AND pod.project_id IS NOT NULL);
4348 EXCEPTION
4349 WHEN NO_DATA_FOUND THEN
4350 l_retro_proj_allowed := 'Y';
4351 END;
4352 ELSE
4353 BEGIN
4354 -- SQL What: Returns Y if there are any shipment which has project information
4355 -- and is received/invoiced, 0 otherwise.
4356 -- SQL Why: To prevent retro price changes if there are such shipments.
4357 SELECT 'N'
4358 INTO l_retro_proj_allowed
4359 FROM DUAL
4360 WHERE EXISTS (SELECT 'has project information'
4361 FROM PO_LINE_LOCATIONS_ALL pll,
4362 PO_DISTRIBUTIONS_ALL pod
4363 WHERE pll.line_location_id = p_po_line_loc_id
4364 AND pod.line_location_id = pll.line_location_id
4365 AND ((NVL(pll.quantity_received,0) > 0 AND
4366 NVL(pll.accrue_on_receipt_flag,'N') = 'Y') OR
4367 NVL(pll.quantity_billed,0) > 0)
4368 AND pod.project_id IS NOT NULL);
4369 EXCEPTION
4370 WHEN NO_DATA_FOUND THEN
4371 l_retro_proj_allowed := 'Y';
4372 END;
4373 END IF; /*IF p_std_po_price_change = 'Y'*/
4374
4375 IF g_debug_stmt THEN
4376 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4377 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,l_module,
4378 'Retro Projcet Allowed: '|| l_retro_proj_allowed);
4379 END IF;
4380 END IF;
4381
4382 RETURN l_retro_proj_allowed;
4383
4384 EXCEPTION
4385 WHEN OTHERS THEN
4386 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
4387 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
4388 l_module,SQLERRM(SQLCODE));
4389 END IF;
4390
4391 RETURN 'N';
4392 END Is_Retro_Project_Allowed;
4393
4394 --------------------------------------------------------------------------------
4395 --Start of Comments :Bug 3339149
4396 --Name: Is_Adjustment_Account_Valid
4397 --Pre-reqs:
4398 -- None.
4399 --Modifies:
4400 -- None.
4401 --Locks:
4402 -- None.
4403 --Function:
4404 -- This function returns true if the adjustment account exists and is valid
4405 --Parameters:
4406 --IN:
4407 --p_std_po_price_change
4408 --p_po_line_id
4409 --p_po_line_loc_id
4410 --RETURN:
4411 -- 'Y': Adjustment account is valid
4412 -- 'N': Adjustment Account does not exist or is not valid
4413 --Testing:
4414 --
4415 --End of Comments
4416 -------------------------------------------------------------------------------
4417 FUNCTION Is_Adjustment_Account_Valid(p_std_po_price_change IN VARCHAR2,
4418 p_po_line_id IN NUMBER,
4419 p_po_line_loc_id IN NUMBER
4420 )
4421 RETURN VARCHAR2
4422 IS
4423
4424 l_account_valid varchar2(1) := 'Y';
4425 l_retroprice_adj_account_id number;
4426 l_ship_to_organization_id PO_LINE_LOCATIONS.ship_to_organization_id%TYPE;
4427 l_qty_received PO_LINE_LOCATIONS.quantity_received%TYPE;
4428 l_accrue_flag PO_LINE_LOCATIONS.accrue_on_receipt_flag%TYPE;
4429 l_qty_billed PO_LINE_LOCATIONS.quantity_billed%TYPE;
4430 -- Bug 3541961
4431 l_consigned_flag PO_HEADERS.consigned_consumption_flag%TYPE;
4432 l_org_id PO_HEADERS.org_id%TYPE;
4433 l_trans_flow_header_id PO_LINE_LOCATIONS.transaction_flow_header_id%TYPE;
4434 l_logical_inv_org_id number;
4435
4436 l_module varchar2(100);
4437 l_api_name CONSTANT VARCHAR2(50) := 'Is_Adjustment_Account_Valid';
4438
4439 cursor c_std_po_shipments is
4440 select NVL(pll.quantity_received,0),
4441 NVL(pll.accrue_on_receipt_flag,'N'),
4442 NVL(pll.quantity_billed,0),
4443 -- Bug 3541961
4444 NVL(poh.consigned_consumption_flag,'N'),
4445 pll.ship_to_organization_id,
4446 poh.org_id,
4447 pll.transaction_flow_header_id -- Bug 3880758
4448 from po_line_locations_all pll,
4449 -- Bug 3541961
4450 po_headers_all poh
4451 where pll.po_line_id = p_po_line_id
4452 -- Bug 3541961
4453 and pll.po_header_id = poh.po_header_id;
4454
4455 cursor c_rel_shipments is
4456 select NVL(pll.quantity_received,0),
4457 NVL(pll.accrue_on_receipt_flag,'N'),
4458 NVL(pll.quantity_billed,0),
4459 NVL(por.consigned_consumption_flag,'N'),
4460 pll.ship_to_organization_id,
4461 por.org_id,
4462 null -- Bug 3880758
4463 from po_line_locations_all pll,
4464 -- Bug 3541961
4465 po_releases_all por
4466 where pll.line_location_id = p_po_line_loc_id
4467 -- Bug 3541961
4468 and pll.po_release_id = por.po_release_id;
4469
4470 BEGIN
4471
4472 l_module := g_log_head||l_api_name||'.'||'000'||'.';
4473
4474 IF g_debug_stmt then
4475 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4476 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,l_module,
4477 'Adj Account validity check');
4478 END IF;
4479 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4480 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,l_module,
4481 'Price Change on std PO :' || p_std_po_price_change);
4482 END IF;
4483 END IF;
4484
4485 IF p_std_po_price_change = 'Y' THEN
4486 OPEN c_std_po_shipments;
4487 ELSE
4488 OPEN c_rel_shipments;
4489 END IF;
4490
4491 LOOP
4492
4493 IF p_std_po_price_change = 'Y' THEN
4494 FETCH c_std_po_shipments
4495 INTO l_qty_received,
4496 l_accrue_flag,
4497 l_qty_billed,
4498 l_consigned_flag, -- Bug 3541961
4499 l_ship_to_organization_id,
4500 l_org_id, -- Bug 3610693
4501 l_trans_flow_header_id; -- Bug 3880758
4502 EXIT when c_std_po_shipments%NOTFOUND;
4503 ELSE
4504 FETCH c_rel_shipments
4505 INTO l_qty_received,
4506 l_accrue_flag,
4507 l_qty_billed,
4508 l_consigned_flag, -- Bug 3541961
4509 l_ship_to_organization_id,
4510 l_org_id, -- Bug 3610693
4511 l_trans_flow_header_id; -- Bug 3880758
4512 EXIT when c_rel_shipments%NOTFOUND;
4513 END IF;
4514
4515 -- Bug 3880758 Start
4516 -- If a transaction flow exists we take the account from rcv
4517 -- parameters of the logical inventory org defined for the
4518 -- transaction flow
4519 IF l_trans_flow_header_id is null THEN
4520
4521 IF g_debug_stmt then
4522 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4523 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,l_module,
4524 'Adj Account from ship to org of PO');
4525 END IF;
4526 END IF;
4527
4528 select retroprice_adj_account_id
4529 into l_retroprice_adj_account_id
4530 from rcv_parameters
4531 where organization_id = l_ship_to_organization_id;
4532
4533 ELSE
4534 -- Get the logical inventory org defined for the
4535 -- transaction flow
4536 l_logical_inv_org_id := PO_SHARED_PROC_PVT.get_logical_inv_org_id(
4537 p_transaction_flow_header_id => l_trans_flow_header_id );
4538
4539 IF g_debug_stmt then
4540 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4541 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,l_module,
4542 'Adj Account from logical inv org of start OU');
4543 END IF;
4544 END IF;
4545
4546 select retroprice_adj_account_id
4547 into l_retroprice_adj_account_id
4548 from rcv_parameters
4549 where organization_id = l_logical_inv_org_id;
4550
4551 END IF;
4552 -- Bug 3880758 End
4553
4554 IF (((l_qty_received > 0) AND (l_accrue_flag = 'Y'))
4555 OR (l_qty_billed > 0)
4556 -- Bug 3541961
4557 OR (l_consigned_flag = 'Y')) THEN
4558
4559 IF l_retroprice_adj_account_id is null THEN
4560
4561 l_account_valid := 'N';
4562 exit;
4563
4564 ELSE
4565
4566 Begin
4567 SELECT distinct 'Y'
4568 INTO l_account_valid
4569 FROM gl_code_combinations gcc,
4570 gl_sets_of_books sob,
4571 financials_system_params_all fsp
4572 WHERE gcc.code_combination_id = l_retroprice_adj_account_id
4573 AND gcc.enabled_flag = 'Y'
4574 AND trunc(SYSDATE) BETWEEN
4575 trunc(nvl(start_date_active,SYSDATE - 1) )
4576 AND
4577 trunc(nvl (end_date_active,SYSDATE + 1) )
4578 AND gcc.detail_posting_allowed_flag = 'Y'
4579 AND gcc.summary_flag = 'N'
4580 AND gcc.chart_of_accounts_id = sob.chart_of_accounts_id
4581 AND fsp.org_id = l_org_id -- Bug 3610693
4582 AND sob.set_of_books_id = fsp.set_of_books_id;
4583 Exception
4584 When no_data_found then
4585 l_account_valid := 'N';
4586 exit;
4587 End;
4588
4589 END IF; -- End of account null
4590
4591 END IF; -- End of partially received/invoiced
4592
4593 END LOOP;
4594
4595 IF p_std_po_price_change = 'Y' THEN
4596 CLOSE c_std_po_shipments;
4597 ELSE
4598 CLOSE c_rel_shipments;
4599 END IF;
4600
4601 IF g_debug_stmt then
4602 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4603 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,l_module,
4604 'Adj Account validity flag: '|| l_account_valid);
4605 END IF;
4606 END IF;
4607
4608 Return l_account_valid;
4609
4610 Exception
4611 When Others then
4612 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
4613 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
4614 l_module,SQLERRM(SQLCODE));
4615 END IF;
4616
4617 IF (c_std_po_shipments%ISOPEN) THEN
4618 CLOSE c_std_po_shipments;
4619 END IF;
4620
4621 IF (c_rel_shipments%ISOPEN) THEN
4622 CLOSE c_rel_shipments;
4623 END IF;
4624
4625 Return 'N';
4626 END;
4627
4628 --------------------------------------------------------------------------------
4629 --Start of Comments: Bug 4080732
4630 --Name: is_inv_org_period_open
4631 --Pre-reqs:
4632 -- None.
4633 --Modifies:
4634 -- None.
4635 --Locks:
4636 -- None.
4637 --Function:
4638 -- This function returns true if the Inventory accounting period is open for
4639 -- all ship-to-org's at the shipment level for a Consumption Advice.
4640 -- For Standard PO's, this function is called at the line level. So it
4641 -- checks all PO shipments in a loop.
4642 -- For Blanket releases, this function is called at shipment level. So we
4643 -- do not need a loop.
4644 --
4645 -- If the document is not a Consumption Advice, this function returns 'Y'.
4646 --
4647 -- Note on geting the Inv Org:
4648 -- ---------------------------
4649 -- For this check, we get the Inv Org from the ship-to-org at the shipment
4650 -- level (for regular flows). And for Shared Procuremnet scenario, we use the
4651 -- logical inv org of the transaction flow.
4652 --
4653 --Parameters:
4654 --IN:
4655 -- p_std_po_price_change
4656 -- p_po_line_id
4657 -- p_po_line_loc_id
4658 --RETURN:
4659 -- 'Y': If the Inventory accounting period is open for all ship-to-org's at the
4660 -- shipment level. Also returns 'Y' is the document is not a Consumption
4661 -- Advice.
4662 -- 'N': If even for one ship-to-org of the Consumption Advice, the Inventory
4663 -- period is not open.
4664 --Testing:
4665 --
4666 --End of Comments
4667 -------------------------------------------------------------------------------
4668 FUNCTION is_inv_org_period_open
4669 ( p_std_po_price_change IN VARCHAR2,
4670 p_po_line_id IN NUMBER,
4671 p_po_line_loc_id IN NUMBER
4672 )
4673 RETURN VARCHAR2
4674 IS
4675
4676 l_is_inv_org_period_open VARCHAR2(1) := 'Y';
4677 l_consigned_flag PO_HEADERS.consigned_consumption_flag%TYPE;
4678 l_trans_flow_header_id PO_LINE_LOCATIONS.transaction_flow_header_id%TYPE;
4679 l_ship_to_organization_id PO_LINE_LOCATIONS.ship_to_organization_id%TYPE;
4680 l_logical_inv_org_id MTL_TRANSACTION_FLOW_LINES.from_organization_id%TYPE;
4681 l_inv_org_id_period_check HR_ALL_ORGANIZATION_UNITS.organization_id%TYPE;
4682
4683 l_module VARCHAR2(100);
4684 l_api_name CONSTANT VARCHAR2(50) := 'is_inv_org_period_open';
4685 l_count NUMBER;
4686
4687 CURSOR STD_PO_SHIPMENTS_CSR IS
4688 SELECT NVL(poh.consigned_consumption_flag, 'N'),
4689 pll.ship_to_organization_id,
4690 pll.transaction_flow_header_id
4691 FROM po_line_locations_all pll,
4692 po_headers_all poh
4693 WHERE pll.po_line_id = p_po_line_id
4694 AND pll.po_header_id = poh.po_header_id;
4695
4696 CURSOR REL_SHIPMENTS_CSR IS
4697 SELECT NVL(por.consigned_consumption_flag, 'N'),
4698 pll.ship_to_organization_id,
4699 NULL
4700 FROM po_line_locations_all pll,
4701 po_releases_all por
4702 WHERE pll.line_location_id = p_po_line_loc_id
4703 AND pll.po_release_id = por.po_release_id;
4704
4705 BEGIN
4706
4707 l_module := g_log_head || l_api_name || '.' || '000' || '.';
4708
4709 IF g_debug_stmt THEN
4710 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4711 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
4712 'Inventory Org Open Period Check');
4713 END IF;
4714 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4715 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
4716 'Is price to be changed on Std PO ? : ' || p_std_po_price_change);
4717 END IF;
4718 END IF;
4719
4720 IF p_std_po_price_change = 'Y' THEN
4721 OPEN STD_PO_SHIPMENTS_CSR;
4722 ELSE
4723 OPEN REL_SHIPMENTS_CSR;
4724 END IF;
4725
4726 l_count := 1;
4727 LOOP
4728
4729 IF p_std_po_price_change = 'Y' THEN
4730 FETCH STD_PO_SHIPMENTS_CSR
4731 INTO l_consigned_flag,
4732 l_ship_to_organization_id,
4733 l_trans_flow_header_id;
4734
4735 EXIT when STD_PO_SHIPMENTS_CSR%NOTFOUND;
4736 ELSE
4737 FETCH REL_SHIPMENTS_CSR
4738 INTO l_consigned_flag,
4739 l_ship_to_organization_id,
4740 l_trans_flow_header_id;
4741
4742 EXIT when REL_SHIPMENTS_CSR%NOTFOUND;
4743 END IF;
4744
4745 IF g_debug_stmt THEN
4746 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4747 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
4748 'count='||l_count||
4749 ', consigned_flag='||l_consigned_flag||
4750 ', ship_to_org_id='||l_ship_to_organization_id||
4751 ', trans_flow_header_id='||l_trans_flow_header_id);
4752 END IF;
4753 END IF;
4754
4755 -- If it is not a consigned flow, there is no need of checking the status
4756 -- of Inventory Org Period. So exit the loop with return value 'Y'.
4757 IF (l_consigned_flag = 'N') THEN
4758 l_is_inv_org_period_open := 'Y';
4759 EXIT;
4760 END IF;
4761
4762 -- If a transaction flow exists we use the logical inventory org defined
4763 -- for the transaction flow to check the Opne Inventory period.
4764 IF l_trans_flow_header_id IS NULL THEN
4765
4766 l_inv_org_id_period_check := l_ship_to_organization_id;
4767
4768 IF g_debug_stmt THEN
4769 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4770 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
4771 'Open Period Check for ship-to-org');
4772 END IF;
4773 END IF;
4774
4775 ELSE
4776 -- Get the logical inventory org defined for the transaction flow
4777 l_logical_inv_org_id := PO_SHARED_PROC_PVT.get_logical_inv_org_id(
4778 p_transaction_flow_header_id => l_trans_flow_header_id );
4779
4780 l_inv_org_id_period_check := l_logical_inv_org_id;
4781
4782 IF g_debug_stmt THEN
4783 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4784 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
4785 'Open Period Check for logical inv org of start OU:'||
4786 l_logical_inv_org_id);
4787 END IF;
4788 END IF;
4789
4790 END IF; -- IF l_trans_flow_header_id IS NULL
4791
4792 IF l_inv_org_id_period_check IS NULL THEN
4793 l_is_inv_org_period_open := 'N';
4794 EXIT;
4795 ELSE
4796 -- Call the API to check Inventory Org Open Period
4797 -- The SOB_ID parameter can be null for Inventory open period check,
4798 -- because it is not used inside this API when APP_NAME is 'INV'
4799 IF (PO_DATES_S.val_open_period(
4800 x_trx_date => sysdate, -- IN DATE
4801 x_sob_id => NULL, -- IN NUMBER,
4802 x_app_name => 'INV', -- IN VARCHAR2
4803 x_org_id => l_inv_org_id_period_check) -- IN NUMBER
4804 = FALSE ) THEN
4805
4806 l_is_inv_org_period_open := 'N';
4807
4808 IF g_debug_stmt THEN
4809 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4810 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
4811 'Result from PO_DATES_S.val_open_period is FALSE');
4812 END IF;
4813 END IF;
4814
4815 EXIT;
4816 END IF;
4817 END IF; -- IF l_inv_org_id_period_check IS NULL
4818 END LOOP;
4819
4820 IF (p_std_po_price_change = 'Y') THEN
4821 CLOSE STD_PO_SHIPMENTS_CSR;
4822 ELSE
4823 CLOSE REL_SHIPMENTS_CSR;
4824 END IF;
4825
4826 IF g_debug_stmt THEN
4827 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
4828 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
4829 'Returning: Inventory Org Period Open='||
4830 l_is_inv_org_period_open);
4831 END IF;
4832 END IF;
4833
4834 RETURN l_is_inv_org_period_open;
4835
4836 EXCEPTION
4837 WHEN OTHERS THEN
4838 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
4839 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION, l_module, SQLERRM(SQLCODE));
4840 END IF;
4841
4842 IF (STD_PO_SHIPMENTS_CSR%ISOPEN) THEN
4843 CLOSE STD_PO_SHIPMENTS_CSR;
4844 END IF;
4845
4846 IF (REL_SHIPMENTS_CSR%ISOPEN) THEN
4847 CLOSE REL_SHIPMENTS_CSR;
4848 END IF;
4849
4850 RETURN 'N';
4851 END is_inv_org_period_open;
4852
4853 END PO_RETROACTIVE_PRICING_PVT;