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