[Home] [Help]
PACKAGE BODY: APPS.ICX_CAT_POPULATE_PODOCS_PVT
Source
1 PACKAGE BODY ICX_CAT_POPULATE_PODOCS_PVT AS
2 /* $Header: ICXVPPDB.pls 120.17.12020000.2 2013/04/11 05:28:41 jiarsun ship $*/
3
4 --Will have the two cursors 1. BPA and Quote 2. GBPA
5 --for upgrade
6
7 --Will have the three cursors
8 --for online changes to BPA, Quote and GBPA.
9
10 -- Constants
11 G_PKG_NAME CONSTANT VARCHAR2(30):='ICX_CAT_POPULATE_PODOCS_PVT';
12
13 g_upgrade_last_run_date DATE;
14 g_key NUMBER;
15 g_current_cursor VARCHAR2(20) := 'GBPA_CSR';
16 g_start_rowid ROWID;
17 g_end_rowid ROWID;
18
19 ----------------------------------------------------------------------
20 ----------------- Begin of BPA specific Code ------------------------
21 ----------------------------------------------------------------------
22
23 PROCEDURE openBPACursor
24 ( p_key IN NUMBER ,
25 p_po_line_id IN NUMBER
26 )
27 IS
28 l_api_name CONSTANT VARCHAR2(30) := 'openBPACursor';
29 l_err_loc PLS_INTEGER;
30 l_bpa_csr ICX_CAT_POPULATE_ITEM_PVT.g_item_csr_type;
31 BEGIN
32 l_err_loc := 100;
33 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
34 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
35 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
36 'Processing cursor:' || l_api_name ||
37 ', p_key:' || p_key ||
38 ', p_po_line_id:' || p_po_line_id );
39 END IF;
40
41 l_err_loc := 150;
42 --First close the cursor
43 IF (l_bpa_csr%ISOPEN) THEN
44 l_err_loc := 200;
45 CLOSE l_bpa_csr;
46 END IF;
47
48 l_err_loc := 300;
49 --Comments on the cursor
50 --Move the outside operation flag of a line type to the main cursor from the
51 --status function due to the following reasons:
52 --1. PO has confirmed that the outside operation flag
53 -- of a line type cannot be changed once set
54 --2. The main cursor anyways joins with po_line_types_b
55 -- to eliminate the TEMP LABOR line
56 OPEN l_bpa_csr FOR
57 SELECT /*+ LEADING(doc) */
58 doc.*,
59 ctx.inventory_item_id ctx_inventory_item_id,
60 ctx.source_type ctx_source_type,
61 ctx.item_type ctx_item_type,
62 ctx.purchasing_org_id ctx_purchasing_org_id,
63 ctx.supplier_id ctx_supplier_id,
64 ctx.supplier_site_id ctx_supplier_site_id,
65 ctx.supplier_part_num ctx_supplier_part_num,
66 ctx.supplier_part_auxid ctx_supplier_part_auxid,
67 ctx.ip_category_id ctx_ip_category_id,
68 ctx.po_category_id ctx_po_category_id,
69 ctx.item_revision ctx_item_revision,
70 ROWIDTOCHAR(ctx.rowid) ctx_rowid
71 FROM (
72 SELECT NVL(pl.item_id, -2) inventory_item_id,
73 pl.po_line_id po_line_id,
74 NVL(pl.org_id, -2) org_id,
75 po_tlp.language language,
76 ph.type_lookup_code source_type,
77 DECODE(NVL(ph.global_agreement_flag, 'N'),
78 'Y', ga.purchasing_org_id, pl.org_id) purchasing_org_id,
79 pl.category_id po_category_id,
80 NVL(ph.vendor_id, -2) supplier_id,
81 NVL(pl.vendor_product_num, '##NULL##') supplier_part_num,
82 NVL(pl.supplier_part_auxid, '##NULL##') supplier_part_auxid,
83 DECODE(NVL(ph.global_agreement_flag, 'N'),
84 'Y', NVL(ga.vendor_site_id, -2),
85 NVL(ph.vendor_site_id, -2)) supplier_site_id,
86 pl.ip_category_id ip_category_id,
87 ic.category_name ip_category_name,
88 NVL(pl.item_revision, '-2') item_revision,
89 ph.po_header_id,
90 ph.CLM_DOCUMENT_NUMBER document_number,
91 pl.line_num,
92 UPPER(NVL(pl.allow_price_override_flag, 'N')) allow_price_override_flag,
93 pl.not_to_exceed_price,
94 pl.line_type_id,
95 pl.unit_meas_lookup_code,
96 DECODE(pltb.order_type_lookup_code, 'QUANTITY', pl.unit_price, to_number(null)) unit_price,
97 DECODE(pltb.order_type_lookup_code, 'QUANTITY', to_number(null), pl.amount) amount,
98 ph.currency_code,
99 ph.rate_type,
100 ph.rate_date,
101 ph.rate,
102 ph.agent_id buyer_id,
103 ph.vendor_contact_id supplier_contact_id,
104 NVL(pl.negotiated_by_preparer_flag, 'N') negotiated_by_preparer_flag,
105 pltb.order_type_lookup_code,
106 pv.vendor_name supplier,
107 ph.global_agreement_flag,
108 ph.approved_date,
109 NVL(ph.authorization_status, 'INCOMPLETE') authorization_status,
110 NVL(ph.frozen_flag, 'N') frozen_flag,
111 NVL(ph.cancel_flag, 'N') hdr_cancel_flag,
112 NVL(pl.cancel_flag, 'N') line_cancel_flag,
113 NVL(ph.closed_code, 'OPEN') hdr_closed_code,
114 NVL(pl.closed_code, 'OPEN') line_closed_code,
115 NVL(TRUNC(ph.end_date), TRUNC(SYSDATE + 1)) end_date,
116 NVL(TRUNC(pl.expiration_date), TRUNC(SYSDATE + 1)) expiration_date,
117 TRUNC(SYSDATE) system_date,
118 ph.created_by
119 FROM po_headers_all ph,
120 po_lines_all pl,
121 po_ga_org_assignments ga,
122 po_session_gt pogt,
123 po_attribute_values_tlp po_tlp,
124 po_line_types_b pltb,
125 icx_cat_categories_tl ic,
126 po_vendors pv
127 WHERE ph.po_header_id = pl.po_header_id
128 AND ph.type_lookup_code = 'BLANKET'
129 AND ph.po_header_id = ga.po_header_id (+)
130 AND ph.org_id = ga.organization_id (+)
131 AND pogt.key = p_key
132 AND pl.po_line_id = pogt.index_num1
133 AND ((pogt.char1 IS NULL)
134 OR
135 (pogt.char1 = 'Y' AND po_tlp.language = pogt.char2))
136 AND pl.po_line_id = po_tlp.po_line_id
137 AND pl.line_type_id = pltb.line_type_id
138 AND NVL(pltb.purchase_basis, 'NULL') <> 'TEMP LABOR'
139 AND NVL(pltb.outside_operation_flag, 'N') = 'N'
140 AND po_tlp.ip_category_id = ic.rt_category_id (+)
141 AND po_tlp.language = ic.language (+)
142 AND ph.vendor_id = pv.vendor_id(+)
143 AND pl.po_line_id >= p_po_line_id
144 ) doc,
145 icx_cat_items_ctx_hdrs_tlp ctx
146 WHERE doc.po_line_id = ctx.po_line_id (+)
147 AND doc.org_id = ctx.org_id (+)
148 AND doc.source_type = ctx.source_type (+)
149 AND doc.language = ctx.language (+)
150 ORDER BY doc.po_line_id;
151
152 l_err_loc := 600;
153 populateBPAs(l_bpa_csr, ICX_CAT_UTIL_PVT.g_online_const);
154
155 l_err_loc := 700;
156 CLOSE l_bpa_csr;
157 EXCEPTION
158 WHEN OTHERS THEN
159 ICX_CAT_UTIL_PVT.logUnexpectedException(
160 G_PKG_NAME, l_api_name,
161 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
162 RAISE;
163 END openBPACursor;
164
165 PROCEDURE openBPAOrgAssignmentCursor
166 ( p_key IN NUMBER ,
167 p_po_line_id IN NUMBER
168 )
169 IS
170 l_api_name CONSTANT VARCHAR2(30) := 'openBPAOrgAssignmentCursor';
171 l_err_loc PLS_INTEGER;
172 l_bpa_csr ICX_CAT_POPULATE_ITEM_PVT.g_item_csr_type;
173 BEGIN
174 l_err_loc := 100;
175 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
176 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
177 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
178 'Processing cursor:' || l_api_name ||
179 ', p_key:' || p_key ||
180 ', p_po_line_id:' || p_po_line_id );
181 END IF;
182
183 l_err_loc := 150;
184 --First close the cursor
185 IF (l_bpa_csr%ISOPEN) THEN
186 l_err_loc := 200;
187 CLOSE l_bpa_csr;
188 END IF;
189
190 l_err_loc := 300;
191 OPEN l_bpa_csr FOR
192 SELECT /*+ LEADING(doc) */
193 doc.*,
194 ctx.inventory_item_id ctx_inventory_item_id,
195 ctx.source_type ctx_source_type,
196 ctx.item_type ctx_item_type,
197 ctx.purchasing_org_id ctx_purchasing_org_id,
198 ctx.supplier_id ctx_supplier_id,
199 ctx.supplier_site_id ctx_supplier_site_id,
200 ctx.supplier_part_num ctx_supplier_part_num,
201 ctx.supplier_part_auxid ctx_supplier_part_auxid,
202 ctx.ip_category_id ctx_ip_category_id,
203 ctx.po_category_id ctx_po_category_id,
204 ctx.item_revision ctx_item_revision,
205 ROWIDTOCHAR(ctx.rowid) ctx_rowid
206 FROM (
207 SELECT NVL(pl.item_id, -2) inventory_item_id,
208 pl.po_line_id po_line_id,
209 NVL(pl.org_id, -2) org_id,
210 po_tlp.language language,
211 ph.type_lookup_code source_type,
212 DECODE(NVL(ph.global_agreement_flag, 'N'),
213 'Y', ga.purchasing_org_id, pl.org_id) purchasing_org_id,
214 pl.category_id po_category_id,
215 NVL(ph.vendor_id, -2) supplier_id,
216 NVL(pl.vendor_product_num, '##NULL##') supplier_part_num,
217 NVL(pl.supplier_part_auxid, '##NULL##') supplier_part_auxid,
218 DECODE(NVL(ph.global_agreement_flag, 'N'),
219 'Y', NVL(ga.vendor_site_id, -2),
220 NVL(ph.vendor_site_id, -2)) supplier_site_id,
221 pl.ip_category_id ip_category_id,
222 ic.category_name ip_category_name,
223 NVL(pl.item_revision, '-2') item_revision,
224 ph.po_header_id,
225 ph.CLM_DOCUMENT_NUMBER document_number,
226 pl.line_num,
227 UPPER(NVL(pl.allow_price_override_flag, 'N')) allow_price_override_flag,
228 pl.not_to_exceed_price,
229 pl.line_type_id,
230 pl.unit_meas_lookup_code,
231 DECODE(pltb.order_type_lookup_code, 'QUANTITY', pl.unit_price, to_number(null)) unit_price,
232 DECODE(pltb.order_type_lookup_code, 'QUANTITY', to_number(null), pl.amount) amount,
233 ph.currency_code,
234 ph.rate_type,
235 ph.rate_date,
236 ph.rate,
237 ph.agent_id buyer_id,
238 ph.vendor_contact_id supplier_contact_id,
239 NVL(pl.negotiated_by_preparer_flag, 'N') negotiated_by_preparer_flag,
240 pltb.order_type_lookup_code,
241 pv.vendor_name supplier,
242 ph.global_agreement_flag,
243 ph.approved_date,
244 NVL(ph.authorization_status, 'INCOMPLETE') authorization_status,
245 NVL(ph.frozen_flag, 'N') frozen_flag,
246 NVL(ph.cancel_flag, 'N') hdr_cancel_flag,
247 NVL(pl.cancel_flag, 'N') line_cancel_flag,
248 NVL(ph.closed_code, 'OPEN') hdr_closed_code,
249 NVL(pl.closed_code, 'OPEN') line_closed_code,
250 NVL(TRUNC(ph.end_date), TRUNC(SYSDATE + 1)) end_date,
251 NVL(TRUNC(pl.expiration_date), TRUNC(SYSDATE + 1)) expiration_date,
252 TRUNC(SYSDATE) system_date,
253 ph.created_by
254 FROM po_headers_all ph,
255 po_lines_all pl,
256 po_ga_org_assignments ga,
257 po_session_gt pogt,
258 po_attribute_values_tlp po_tlp,
259 po_line_types_b pltb,
260 icx_cat_categories_tl ic,
261 po_vendors pv
262 WHERE ph.po_header_id = pl.po_header_id
263 AND ph.type_lookup_code = 'BLANKET'
264 AND ph.po_header_id = ga.po_header_id (+)
265 AND ph.org_id = ga.organization_id (+)
266 AND pogt.key = p_key
267 AND ga.po_header_id = pogt.index_num1
268 AND ga.org_assignment_id = pogt.index_num2
269 AND pl.po_line_id = po_tlp.po_line_id
270 AND pl.line_type_id = pltb.line_type_id
271 AND NVL(pltb.purchase_basis, 'NULL') <> 'TEMP LABOR'
272 AND NVL(pltb.outside_operation_flag, 'N') = 'N'
273 AND po_tlp.ip_category_id = ic.rt_category_id (+)
274 AND po_tlp.language = ic.language (+)
275 AND ph.vendor_id = pv.vendor_id(+)
276 AND pl.po_line_id >= p_po_line_id
277 ) doc,
278 icx_cat_items_ctx_hdrs_tlp ctx
279 WHERE doc.po_line_id = ctx.po_line_id (+)
280 AND doc.org_id = ctx.org_id (+)
281 AND doc.source_type = ctx.source_type (+)
282 AND doc.language = ctx.language (+)
283 ORDER BY doc.po_line_id;
284
285 l_err_loc := 600;
286 populateBPAs(l_bpa_csr, ICX_CAT_UTIL_PVT.g_online_const);
287
288 l_err_loc := 700;
289 CLOSE l_bpa_csr;
290 EXCEPTION
291 WHEN OTHERS THEN
292 ICX_CAT_UTIL_PVT.logUnexpectedException(
293 G_PKG_NAME, l_api_name,
294 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
295 RAISE;
296 END openBPAOrgAssignmentCursor;
297
298 --l_bpa_csr, ICX_CAT_UTIL_PVT.g_upgrade_const
299 PROCEDURE populateBPAs
300 ( p_podocs_csr IN ICX_CAT_POPULATE_ITEM_PVT.g_item_csr_type ,
301 p_current_mode IN VARCHAR2
302 )
303 IS
304 l_api_name CONSTANT VARCHAR2(30) := 'populateBPAs';
305 l_err_loc PLS_INTEGER;
306 l_start_po_line_id NUMBER;
307 l_err_string VARCHAR2(4000);
308 l_batch_count PLS_INTEGER;
309 l_row_count PLS_INTEGER;
310 l_count PLS_INTEGER;
311 l_BPA_line_status_rec ICX_CAT_POPULATE_STATUS_PVT.g_BPA_line_status_rec_type;
312 l_BPA_line_status PLS_INTEGER;
313 l_current_ctx_item_rec ICX_CAT_POPULATE_ITEM_PVT.g_ctx_item_rec_type;
314 l_current_flag_rec ICX_CAT_POPULATE_ITEM_PVT.g_bpa_online_flag_rec_type;
315
316 ----- Start of declaring columns selected in the cursor -----
317 l_inv_item_id_tbl DBMS_SQL.NUMBER_TABLE;
318 l_po_line_id_tbl DBMS_SQL.NUMBER_TABLE;
319 l_org_id_tbl DBMS_SQL.NUMBER_TABLE;
320 l_language_tbl DBMS_SQL.VARCHAR2_TABLE;
321 l_source_type_tbl DBMS_SQL.VARCHAR2_TABLE;
322 l_purchasing_org_id_tbl DBMS_SQL.NUMBER_TABLE;
323 l_po_category_id_tbl DBMS_SQL.NUMBER_TABLE;
324 l_supplier_id_tbl DBMS_SQL.NUMBER_TABLE;
325 l_supplier_part_num_tbl DBMS_SQL.VARCHAR2_TABLE;
326 l_supplier_part_auxid_tbl DBMS_SQL.VARCHAR2_TABLE;
327 l_supplier_site_id_tbl DBMS_SQL.NUMBER_TABLE;
328 l_ip_category_id_tbl DBMS_SQL.NUMBER_TABLE;
329 l_ip_category_name_tbl DBMS_SQL.VARCHAR2_TABLE;
330 l_item_revision_tbl DBMS_SQL.VARCHAR2_TABLE;
331 l_po_header_id_tbl DBMS_SQL.NUMBER_TABLE;
332 l_document_number_tbl DBMS_SQL.VARCHAR2_TABLE;
333 l_line_num_tbl DBMS_SQL.NUMBER_TABLE;
334 l_allow_prc_override_flag_tbl DBMS_SQL.VARCHAR2_TABLE;
335 l_not_to_exceed_price_tbl DBMS_SQL.NUMBER_TABLE;
336 l_line_type_id_tbl DBMS_SQL.NUMBER_TABLE;
337 l_unit_meas_lookup_code_tbl DBMS_SQL.VARCHAR2_TABLE;
338 l_unit_price_tbl DBMS_SQL.NUMBER_TABLE;
339 l_amount_tbl DBMS_SQL.NUMBER_TABLE;
340 l_currency_code_tbl DBMS_SQL.VARCHAR2_TABLE;
341 l_rate_type_tbl DBMS_SQL.VARCHAR2_TABLE;
342 l_rate_date_tbl DBMS_SQL.DATE_TABLE;
343 l_rate_tbl DBMS_SQL.NUMBER_TABLE;
344 l_buyer_id_tbl DBMS_SQL.NUMBER_TABLE;
345 l_supplier_contact_id_tbl DBMS_SQL.NUMBER_TABLE;
346 l_negotiated_preparer_flag_tbl DBMS_SQL.VARCHAR2_TABLE;
347 l_order_type_lookup_code_tbl DBMS_SQL.VARCHAR2_TABLE;
348 l_supplier_tbl DBMS_SQL.VARCHAR2_TABLE;
349 l_global_agreement_flag_tbl DBMS_SQL.VARCHAR2_TABLE;
350 l_approved_date_tbl DBMS_SQL.DATE_TABLE;
351 l_authorization_status_tbl DBMS_SQL.VARCHAR2_TABLE;
352 l_frozen_flag_tbl DBMS_SQL.VARCHAR2_TABLE;
353 l_hdr_cancel_flag_tbl DBMS_SQL.VARCHAR2_TABLE;
354 l_line_cancel_flag_tbl DBMS_SQL.VARCHAR2_TABLE;
355 l_hdr_closed_code_tbl DBMS_SQL.VARCHAR2_TABLE;
356 l_line_closed_code_tbl DBMS_SQL.VARCHAR2_TABLE;
357 l_end_date_tbl DBMS_SQL.DATE_TABLE;
358 l_expiration_date_tbl DBMS_SQL.DATE_TABLE;
359 l_system_date_tbl DBMS_SQL.DATE_TABLE;
360 l_created_by_tbl DBMS_SQL.NUMBER_TABLE;
361 l_ctx_inventory_item_id_tbl DBMS_SQL.NUMBER_TABLE;
362 l_ctx_source_type_tbl DBMS_SQL.VARCHAR2_TABLE;
363 l_ctx_item_type_tbl DBMS_SQL.VARCHAR2_TABLE;
364 l_ctx_purchasing_org_id_tbl DBMS_SQL.NUMBER_TABLE;
365 l_ctx_supplier_id_tbl DBMS_SQL.NUMBER_TABLE;
366 l_ctx_supplier_site_id_tbl DBMS_SQL.NUMBER_TABLE;
367 l_ctx_supplier_part_num_tbl DBMS_SQL.VARCHAR2_TABLE;
368 l_ctx_supplier_part_auxid_tbl DBMS_SQL.VARCHAR2_TABLE;
369 l_ctx_ip_category_id_tbl DBMS_SQL.NUMBER_TABLE;
370 l_ctx_po_category_id_tbl DBMS_SQL.NUMBER_TABLE;
371 l_ctx_item_revision_tbl DBMS_SQL.VARCHAR2_TABLE;
372 l_ctx_rowid_tbl DBMS_SQL.UROWID_TABLE;
373 ------ End of declaring columns selected in the cursor ------
374
375 BEGIN
376 l_err_loc := 100;
377 l_batch_count := 0;
378 l_row_count := 0;
379 l_count := 0;
380 l_current_flag_rec := null;
381 LOOP
382 l_err_loc := 110;
383 l_inv_item_id_tbl.DELETE;
384 l_po_line_id_tbl.DELETE;
385 l_org_id_tbl.DELETE;
386 l_language_tbl.DELETE;
387 l_source_type_tbl.DELETE;
388 l_purchasing_org_id_tbl.DELETE;
389 l_po_category_id_tbl.DELETE;
390 l_supplier_id_tbl.DELETE;
391 l_supplier_part_num_tbl.DELETE;
392 l_supplier_part_auxid_tbl.DELETE;
393 l_supplier_site_id_tbl.DELETE;
394 l_ip_category_id_tbl.DELETE;
395 l_ip_category_name_tbl.DELETE;
396 l_item_revision_tbl.DELETE;
397 l_po_header_id_tbl.DELETE;
398 l_document_number_tbl.DELETE;
399 l_line_num_tbl.DELETE;
400 l_allow_prc_override_flag_tbl.DELETE;
401 l_not_to_exceed_price_tbl.DELETE;
402 l_line_type_id_tbl.DELETE;
403 l_unit_meas_lookup_code_tbl.DELETE;
404 l_unit_price_tbl.DELETE;
405 l_amount_tbl.DELETE;
406 l_currency_code_tbl.DELETE;
407 l_rate_type_tbl.DELETE;
408 l_rate_date_tbl.DELETE;
409 l_rate_tbl.DELETE;
410 l_buyer_id_tbl.DELETE;
411 l_supplier_contact_id_tbl.DELETE;
412 l_negotiated_preparer_flag_tbl.DELETE;
413 l_order_type_lookup_code_tbl.DELETE;
414 l_supplier_tbl.DELETE;
415 l_global_agreement_flag_tbl.DELETE;
416 l_approved_date_tbl.DELETE;
417 l_authorization_status_tbl.DELETE;
418 l_frozen_flag_tbl.DELETE;
419 l_hdr_cancel_flag_tbl.DELETE;
420 l_line_cancel_flag_tbl.DELETE;
421 l_hdr_closed_code_tbl.DELETE;
422 l_line_closed_code_tbl.DELETE;
423 l_end_date_tbl.DELETE;
424 l_expiration_date_tbl.DELETE;
425 l_system_date_tbl.DELETE;
426 l_created_by_tbl.DELETE;
427 l_ctx_inventory_item_id_tbl.DELETE;
428 l_ctx_source_type_tbl.DELETE;
429 l_ctx_item_type_tbl.DELETE;
430 l_ctx_purchasing_org_id_tbl.DELETE;
431 l_ctx_supplier_id_tbl.DELETE;
432 l_ctx_supplier_site_id_tbl.DELETE;
433 l_ctx_supplier_part_num_tbl.DELETE;
434 l_ctx_supplier_part_auxid_tbl.DELETE;
435 l_ctx_ip_category_id_tbl.DELETE;
436 l_ctx_po_category_id_tbl.DELETE;
437 l_ctx_item_revision_tbl.DELETE;
438 l_ctx_rowid_tbl.DELETE;
439
440 BEGIN
441 l_err_loc := 200;
442 FETCH p_podocs_csr BULK COLLECT INTO
443 l_inv_item_id_tbl,
444 l_po_line_id_tbl,
445 l_org_id_tbl,
446 l_language_tbl,
447 l_source_type_tbl,
448 l_purchasing_org_id_tbl,
449 l_po_category_id_tbl,
450 l_supplier_id_tbl,
451 l_supplier_part_num_tbl,
452 l_supplier_part_auxid_tbl,
453 l_supplier_site_id_tbl,
454 l_ip_category_id_tbl,
455 l_ip_category_name_tbl,
456 l_item_revision_tbl,
457 l_po_header_id_tbl,
458 l_document_number_tbl,
459 l_line_num_tbl,
460 l_allow_prc_override_flag_tbl,
461 l_not_to_exceed_price_tbl,
462 l_line_type_id_tbl,
463 l_unit_meas_lookup_code_tbl,
464 l_unit_price_tbl,
465 l_amount_tbl,
466 l_currency_code_tbl,
467 l_rate_type_tbl,
468 l_rate_date_tbl,
469 l_rate_tbl,
470 l_buyer_id_tbl,
471 l_supplier_contact_id_tbl,
472 l_negotiated_preparer_flag_tbl,
473 l_order_type_lookup_code_tbl,
474 l_supplier_tbl,
475 l_global_agreement_flag_tbl,
476 l_approved_date_tbl,
477 l_authorization_status_tbl,
478 l_frozen_flag_tbl,
479 l_hdr_cancel_flag_tbl,
480 l_line_cancel_flag_tbl,
481 l_hdr_closed_code_tbl,
482 l_line_closed_code_tbl,
483 l_end_date_tbl,
484 l_expiration_date_tbl,
485 l_system_date_tbl,
486 l_created_by_tbl,
487 l_ctx_inventory_item_id_tbl,
488 l_ctx_source_type_tbl,
489 l_ctx_item_type_tbl,
490 l_ctx_purchasing_org_id_tbl,
491 l_ctx_supplier_id_tbl,
492 l_ctx_supplier_site_id_tbl,
493 l_ctx_supplier_part_num_tbl,
494 l_ctx_supplier_part_auxid_tbl,
495 l_ctx_ip_category_id_tbl,
496 l_ctx_po_category_id_tbl,
497 l_ctx_item_revision_tbl,
498 l_ctx_rowid_tbl
499 LIMIT ICX_CAT_UTIL_PVT.g_batch_size;
500
501 l_err_loc := 300;
502 EXIT WHEN l_po_line_id_tbl.COUNT = 0;
503
504 l_err_loc := 400;
505 l_batch_count := l_batch_count + 1;
506
507 l_err_loc := 500;
508 l_count := l_po_line_id_tbl.COUNT;
509 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
510 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
511 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
512 'Num. of rows returned from the cursor:' || l_count);
513 END IF;
514
515 --Save the last po_line_id processed, so that re-open of cursor will start from the saved id.
516 l_start_po_line_id := l_po_line_id_tbl(l_count);
517
518 l_row_count := l_row_count + l_count;
519
520 FOR i in 1..l_po_line_id_tbl.COUNT LOOP
521 l_err_loc := 600;
522 --First get the status of the current BPA line
523 IF (p_current_mode = ICX_CAT_UTIL_PVT.g_upgrade_const AND
524 l_created_by_tbl(i) = ICX_CAT_UTIL_PVT.g_upgrade_user)
525 THEN
526 l_err_loc := 700;
527 -- The GBPAs created for bulkload items will not be in approved
528 -- status during upgrade, so treat them as valid during upgrade.
529 l_BPA_line_status := ICX_CAT_POPULATE_STATUS_PVT.VALID_FOR_POPULATE;
530 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
531 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
532 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
533 'bpa status is hard-coded to valid for' ||
534 ', p_current_mode:' || p_current_mode ||
535 ', l_created_by_tbl(i):' || l_created_by_tbl(i) );
536 END IF;
537 ELSE
538 l_err_loc := 800;
539 l_BPA_line_status_rec.approved_date := l_approved_date_tbl(i);
540 l_BPA_line_status_rec.authorization_status := l_authorization_status_tbl(i);
541 l_BPA_line_status_rec.frozen_flag := l_frozen_flag_tbl(i);
542 l_BPA_line_status_rec.hdr_cancel_flag := l_hdr_cancel_flag_tbl(i);
543 l_BPA_line_status_rec.line_cancel_flag := l_line_cancel_flag_tbl(i);
544 l_BPA_line_status_rec.hdr_closed_code := l_hdr_closed_code_tbl(i);
545 l_BPA_line_status_rec.line_closed_code := l_line_closed_code_tbl(i);
546 l_BPA_line_status_rec.end_date := l_end_date_tbl(i);
547 l_BPA_line_status_rec.expiration_date := l_expiration_date_tbl(i);
548 l_BPA_line_status_rec.system_date := l_system_date_tbl(i);
549
550 l_err_loc := 900;
551 l_BPA_line_status := ICX_CAT_POPULATE_STATUS_PVT.getBPALineStatus(l_BPA_line_status_rec);
552 END IF;
553
554 l_err_loc := 1000;
555 --bug 16374319 begin
556 IF (l_authorization_status_tbl(i) = 'APPROVED'
557 AND
558 (l_ctx_rowid_tbl(i) IS NOT NULL OR
559 l_BPA_line_status = ICX_CAT_POPULATE_STATUS_PVT.VALID_FOR_POPULATE))
560 --bug 16374319 end
561 THEN
562 l_err_loc := 1100;
563 l_current_ctx_item_rec.inventory_item_id := l_inv_item_id_tbl(i);
564 l_current_ctx_item_rec.po_line_id := l_po_line_id_tbl(i);
565 l_current_ctx_item_rec.req_template_name := TO_CHAR(ICX_CAT_UTIL_PVT.g_NULL_NUMBER);
566 l_current_ctx_item_rec.req_template_line_num := TO_NUMBER(ICX_CAT_UTIL_PVT.g_NULL_NUMBER);
567 l_current_ctx_item_rec.org_id := l_org_id_tbl(i);
568 l_current_ctx_item_rec.language := l_language_tbl(i);
569 l_current_ctx_item_rec.source_type := l_source_type_tbl(i);
570 l_current_ctx_item_rec.item_type := ICX_CAT_UTIL_PVT.g_purchase_item_type;
571 l_current_ctx_item_rec.purchasing_org_id := l_purchasing_org_id_tbl(i);
572 l_current_ctx_item_rec.owning_org_id := l_org_id_tbl(i);
573 l_current_ctx_item_rec.po_category_id := l_po_category_id_tbl(i);
574 l_current_ctx_item_rec.supplier_id := l_supplier_id_tbl(i);
575 l_current_ctx_item_rec.supplier_part_num := l_supplier_part_num_tbl(i);
576 l_current_ctx_item_rec.supplier_part_auxid := l_supplier_part_auxid_tbl(i);
577 l_current_ctx_item_rec.supplier_site_id := l_supplier_site_id_tbl(i);
578 l_current_ctx_item_rec.status := l_BPA_line_status;
579 l_current_ctx_item_rec.ip_category_id := l_ip_category_id_tbl(i);
580 l_current_ctx_item_rec.ip_category_name := l_ip_category_name_tbl(i);
581 l_current_ctx_item_rec.req_template_po_line_id := NULL;
582 l_current_ctx_item_rec.item_revision := l_item_revision_tbl(i);
583 l_current_ctx_item_rec.po_header_id := l_po_header_id_tbl(i);
584 l_current_ctx_item_rec.document_number := l_document_number_tbl(i);
585 l_current_ctx_item_rec.line_num := l_line_num_tbl(i);
586 l_current_ctx_item_rec.allow_price_override_flag := l_allow_prc_override_flag_tbl(i);
587 l_current_ctx_item_rec.not_to_exceed_price := l_not_to_exceed_price_tbl(i);
588 l_current_ctx_item_rec.line_type_id := l_line_type_id_tbl(i);
589 l_current_ctx_item_rec.unit_meas_lookup_code := l_unit_meas_lookup_code_tbl(i);
590 l_current_ctx_item_rec.suggested_quantity := NULL;
591 l_current_ctx_item_rec.unit_price := l_unit_price_tbl(i);
592 l_current_ctx_item_rec.amount := l_amount_tbl(i);
593 l_current_ctx_item_rec.currency_code := l_currency_code_tbl(i);
594 l_current_ctx_item_rec.rate_type := l_rate_type_tbl(i);
595 l_current_ctx_item_rec.rate_date := l_rate_date_tbl(i);
596 l_current_ctx_item_rec.rate := l_rate_tbl(i);
597 l_current_ctx_item_rec.buyer_id := l_buyer_id_tbl(i);
598 l_current_ctx_item_rec.supplier_contact_id := l_supplier_contact_id_tbl(i);
599 l_current_ctx_item_rec.rfq_required_flag := 'N';
600 l_current_ctx_item_rec.negotiated_by_preparer_flag := l_negotiated_preparer_flag_tbl(i);
601 l_current_ctx_item_rec.description := NULL;
602 l_current_ctx_item_rec.order_type_lookup_code := l_order_type_lookup_code_tbl(i);
603 l_current_ctx_item_rec.supplier := l_supplier_tbl(i);
604 l_current_ctx_item_rec.global_agreement_flag := l_global_agreement_flag_tbl(i);
605 l_current_ctx_item_rec.merged_source_type := 'SRC_DOC';
606 l_current_ctx_item_rec.ctx_inventory_item_id := l_ctx_inventory_item_id_tbl(i);
607 l_current_ctx_item_rec.ctx_source_type := l_ctx_source_type_tbl(i);
608 l_current_ctx_item_rec.ctx_item_type := l_ctx_item_type_tbl(i);
609 l_current_ctx_item_rec.ctx_purchasing_org_id := l_ctx_purchasing_org_id_tbl(i);
610 l_current_ctx_item_rec.ctx_supplier_id := l_ctx_supplier_id_tbl(i);
611 l_current_ctx_item_rec.ctx_supplier_site_id := l_ctx_supplier_site_id_tbl(i);
612 l_current_ctx_item_rec.ctx_supplier_part_num := l_ctx_supplier_part_num_tbl(i);
613 l_current_ctx_item_rec.ctx_supplier_part_auxid := l_ctx_supplier_part_auxid_tbl(i);
614 l_current_ctx_item_rec.ctx_ip_category_id := l_ctx_ip_category_id_tbl(i);
615 l_current_ctx_item_rec.ctx_po_category_id := l_ctx_po_category_id_tbl(i);
616 l_current_ctx_item_rec.ctx_item_revision := l_ctx_item_revision_tbl(i);
617 l_current_ctx_item_rec.ctx_rowid := l_ctx_rowid_tbl(i);
618
619 l_err_loc := 1300;
620 ICX_CAT_POPULATE_ITEM_PVT.processCurrentCtxItemRow(l_current_ctx_item_rec, g_current_cursor, p_current_mode);
621
622 l_err_loc := 1400;
623 ICX_CAT_POPULATE_ITEM_PVT.populateItemCtxTables('INLOOP', ICX_CAT_UTIL_PVT.g_BPACsr_const);
624 ELSE
625 IF (p_current_mode = ICX_CAT_UTIL_PVT.g_upgrade_const) THEN
626 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
627 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
628 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
629 'Row #:' || i ||
630 ', with po_line_id:' || l_po_line_id_tbl(i) ||', not processed' ||
631 ', l_approved_date_tbl:' || l_approved_date_tbl(i) ||
632 ', l_authorization_status_tbl:' || l_authorization_status_tbl(i) ||
633 ', l_frozen_flag_tbl:' || l_frozen_flag_tbl(i) ||
634 ', l_hdr_cancel_flag_tbl:' || l_hdr_cancel_flag_tbl(i) ||
635 ', l_line_cancel_flag_tbl:' || l_line_cancel_flag_tbl(i) ||
636 ', l_hdr_closed_code_tbl:' || l_hdr_closed_code_tbl(i) ||
637 ', l_line_closed_code_tbl:' || l_line_closed_code_tbl(i) ||
638 ', l_end_date_tbl:' || l_end_date_tbl(i) ||
639 ', l_expiration_date_tbl:' || l_expiration_date_tbl(i) ||
640 ', l_system_date_tbl:' || l_system_date_tbl(i) ||
641 ', status: ' || l_BPA_line_status);
642 END IF;
643 ELSE
644 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
645 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
646 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
647 'Row #:' || i ||
648 ', with po_line_id:' || l_po_line_id_tbl(i) ||', not processed' ||
649 ', l_approved_date_tbl:' || l_approved_date_tbl(i) ||
650 ', l_authorization_status_tbl:' || l_authorization_status_tbl(i) ||
651 ', l_frozen_flag_tbl:' || l_frozen_flag_tbl(i) ||
652 ', l_hdr_cancel_flag_tbl:' || l_hdr_cancel_flag_tbl(i) ||
653 ', l_line_cancel_flag_tbl:' || l_line_cancel_flag_tbl(i) ||
654 ', l_hdr_closed_code_tbl:' || l_hdr_closed_code_tbl(i) ||
655 ', l_line_closed_code_tbl:' || l_line_closed_code_tbl(i) ||
656 ', l_end_date_tbl:' || l_end_date_tbl(i) ||
657 ', l_expiration_date_tbl:' || l_expiration_date_tbl(i) ||
658 ', l_system_date_tbl:' || l_system_date_tbl(i) ||
659 ', status: ' || l_BPA_line_status);
660 END IF;
661 END IF;
662 END IF;
663 END LOOP; --FOR LOOP of l_po_line_id_tbl
664
665 l_err_loc := 1500;
666 EXIT WHEN l_po_line_id_tbl.COUNT < ICX_CAT_UTIL_PVT.g_batch_size;
667 EXCEPTION
668 WHEN ICX_CAT_UTIL_PVT.g_snap_shot_too_old THEN
669 l_err_string := 'ICX_CAT_POPULATE_PODOCS_PVT.populateBPAs' ||l_err_loc
670 ||', Total processeded batches:' ||l_batch_count
671 ||', Cursor will be reopened with po_line_id:' ||l_start_po_line_id;
672 IF (FND_API.To_Boolean(ICX_CAT_UTIL_PVT.g_COMMIT)) THEN
673 ICX_CAT_UTIL_PVT.logAndCommitSnapShotTooOld(g_pkg_name, l_api_name, l_err_string);
674 --Closing and reopen of cursor will be done by called procedures
675 l_err_loc := 1700;
676 IF (g_current_cursor = 'ORG_ASSIGNMENT_CSR') THEN
677 l_err_loc := 1720;
678 openBPAOrgAssignmentCursor(g_key, l_start_po_line_id);
679 ELSE
680 l_err_loc := 1740;
681 openBPACursor(g_key, l_start_po_line_id);
682 END IF;
683 ELSE
684 RAISE;
685 END IF;
686 END;
687 END LOOP; --Cursor loop
688
689 l_err_loc := 1800;
690 ICX_CAT_POPULATE_ITEM_PVT.populateItemCtxTables('OUTLOOP', ICX_CAT_UTIL_PVT.g_BPACsr_const);
691
692 l_err_loc := 1900;
693 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
694 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
695 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
696 'populateBPAs in mode:'|| p_current_mode ||' done; '||
697 'Total num. of batches processed:' ||l_batch_count ||
698 ', Total num. of rows processed:' ||l_row_count);
699 END IF;
700 EXCEPTION
701 WHEN OTHERS THEN
702 ICX_CAT_UTIL_PVT.logUnexpectedException(
703 G_PKG_NAME, l_api_name,
704 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
705 RAISE;
706 END populateBPAs;
707
708 ----------------------------------------------------------------------
709 ----------------- End of BPA specific Code -----------------------
710 ----------------- Begin of GBPA specific Code -----------------------
711 ----------------------------------------------------------------------
712
713 PROCEDURE openR12UpgradeGBPACursor
714 IS
715 l_api_name CONSTANT VARCHAR2(30) := 'openR12UpgradeGBPACursor';
716 l_err_loc PLS_INTEGER;
717 l_gbpa_csr ICX_CAT_POPULATE_ITEM_PVT.g_item_csr_type;
718 BEGIN
719 l_err_loc := 100;
720 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
721 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
722 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
723 'Processing cursor:' || l_api_name ||
724 ', g_upgrade_last_run_date:' || g_upgrade_last_run_date ||
725 ', g_start_rowid:' || g_start_rowid ||
726 ', g_end_rowid:' || g_end_rowid );
727 END IF;
728
729 l_err_loc := 150;
730 --First close the cursor
731 IF (l_gbpa_csr%ISOPEN) THEN
732 l_err_loc := 110;
733 CLOSE l_gbpa_csr;
734 END IF;
735
736 l_err_loc := 200;
737 --Open the GBPA cursor now
738 IF (g_upgrade_last_run_date) IS NULL THEN
739 l_err_loc := 300;
740 OPEN l_gbpa_csr FOR
741 SELECT /*+ LEADING(doc) use_nl(ctx) index(ctxIn, ICX_CAT_ITEMS_CTX_HDRS_TLP_PK) */
742 doc.*,
743 ctx.inventory_item_id ctx_inventory_item_id,
744 ctx.source_type ctx_source_type,
745 ctx.item_type ctx_item_type,
746 ctx.purchasing_org_id ctx_purchasing_org_id,
747 ctx.supplier_id ctx_supplier_id,
748 ctx.supplier_site_id ctx_supplier_site_id,
749 ctx.supplier_part_num ctx_supplier_part_num,
750 ctx.supplier_part_auxid ctx_supplier_part_auxid,
751 ctx.ip_category_id ctx_ip_category_id,
752 ctx.po_category_id ctx_po_category_id,
753 ctx.item_revision ctx_item_revision,
754 ROWIDTOCHAR(ctx.rowid) ctx_rowid
755 FROM
756 (
757 SELECT /*+ ROWID(ph) leading(ph,pv,t,pl,pltb,ctxIn) use_nl(pl,ctxIn,t) index(t,PO_GA_ORG_ASSIGN_U1)
758 index(ctxIn,ICX_CAT_ITEMS_CTX_HDRS_TLP_PK) NO_MERGE */
759 NVL(pl.item_id, -2) inventory_item_id,
760 pl.po_line_id po_line_id,
761 NVL(t.organization_id, -2) org_id,
762 ctxIn.language language,
763 'GLOBAL_BLANKET' source_type,
764 t.purchasing_org_id purchasing_org_id,
765 ctxIn.org_id owning_org_id,
766 ctxIn.po_category_id po_category_id,
767 ctxIn.supplier_id supplier_id,
768 ctxIn.supplier_part_num supplier_part_num,
769 ctxIn.supplier_part_auxid supplier_part_auxid,
770 t.vendor_site_id supplier_site_id,
771 ctxIn.ip_category_id ip_category_id,
772 ctxIn.ip_category_name ip_category_name,
773 NVL(pl.item_revision, '-2') item_revision,
774 ph.po_header_id,
775 ph.segment1 document_number,
776 pl.line_num,
777 UPPER(NVL(pl.allow_price_override_flag, 'N')) allow_price_override_flag,
778 pl.not_to_exceed_price,
779 pl.line_type_id,
780 pl.unit_meas_lookup_code,
781 DECODE(pltb.order_type_lookup_code, 'QUANTITY', pl.unit_price, to_number(null)) unit_price,
782 DECODE(pltb.order_type_lookup_code, 'QUANTITY', to_number(null), pl.amount) amount,
783 ph.currency_code,
784 ph.rate_type,
785 ph.rate_date,
786 ph.rate,
787 ph.agent_id buyer_id,
788 ph.vendor_contact_id supplier_contact_id,
789 NVL(pl.negotiated_by_preparer_flag, 'N') negotiated_by_preparer_flag,
790 pltb.order_type_lookup_code,
791 pv.vendor_name supplier,
792 ph.global_agreement_flag,
793 --For global agreement status
794 NVL(t.enabled_flag, 'N') enabled_flag
795 FROM po_ga_org_assignments t,
796 po_headers_all ph,
797 po_lines_all pl,
798 icx_cat_items_ctx_hdrs_tlp ctxIn,
799 po_vendors pv,
800 po_line_types_b pltb
801 WHERE ph.global_agreement_flag = 'Y'
802 AND ph.po_header_id = t.po_header_id
803 AND ph.org_id <> t.organization_id
804 AND t.po_header_id = pl.po_header_id
805 AND pl.po_line_id = ctxIn.po_line_id
806 AND pl.org_id = ctxIn.org_id
807 AND pl.line_type_id = pltb.line_type_id
808 AND ph.vendor_id = pv.vendor_id(+)
809 AND ph.rowid BETWEEN g_start_rowid AND g_end_rowid
810 ) doc,
811 icx_cat_items_ctx_hdrs_tlp ctx
812 WHERE doc.po_line_id = ctx.po_line_id (+)
813 AND doc.org_id = ctx.org_id (+)
814 AND doc.source_type = ctx.source_type (+)
815 AND doc.language = ctx.language (+);
816 ELSE
817 l_err_loc := 400;
818 OPEN l_gbpa_csr FOR
819 SELECT /*+ LEADING(doc) use_nl(ctx) index(ctxIn, ICX_CAT_ITEMS_CTX_HDRS_TLP_PK) */
820 doc.*,
821 ctx.inventory_item_id ctx_inventory_item_id,
822 ctx.source_type ctx_source_type,
823 ctx.item_type ctx_item_type,
824 ctx.purchasing_org_id ctx_purchasing_org_id,
825 ctx.supplier_id ctx_supplier_id,
826 ctx.supplier_site_id ctx_supplier_site_id,
827 ctx.supplier_part_num ctx_supplier_part_num,
828 ctx.supplier_part_auxid ctx_supplier_part_auxid,
829 ctx.ip_category_id ctx_ip_category_id,
830 ctx.po_category_id ctx_po_category_id,
831 ctx.item_revision ctx_item_revision,
832 ROWIDTOCHAR(ctx.rowid) ctx_rowid
833 FROM
834 (
835 SELECT /*+ ROWID(ph) leading(ph,pv,t,pl,pltb,ctxIn) use_nl(pl,ctxIn,t) index(t,PO_GA_ORG_ASSIGN_U1)
836 index(ctxIn,ICX_CAT_ITEMS_CTX_HDRS_TLP_PK) NO_MERGE */
837 NVL(pl.item_id, -2) inventory_item_id,
838 pl.po_line_id po_line_id,
839 NVL(t.organization_id, -2) org_id,
840 ctxIn.language language,
841 'GLOBAL_BLANKET' source_type,
842 t.purchasing_org_id purchasing_org_id,
843 ctxIn.org_id owning_org_id,
844 ctxIn.po_category_id po_category_id,
845 ctxIn.supplier_id supplier_id,
846 ctxIn.supplier_part_num supplier_part_num,
847 ctxIn.supplier_part_auxid supplier_part_auxid,
848 t.vendor_site_id supplier_site_id,
849 ctxIn.ip_category_id ip_category_id,
850 ctxIn.ip_category_name ip_category_name,
851 NVL(pl.item_revision, '-2') item_revision,
852 ph.po_header_id,
853 ph.segment1 document_number,
854 pl.line_num,
855 UPPER(NVL(pl.allow_price_override_flag, 'N')) allow_price_override_flag,
856 pl.not_to_exceed_price,
857 pl.line_type_id,
858 pl.unit_meas_lookup_code,
859 DECODE(pltb.order_type_lookup_code, 'QUANTITY', pl.unit_price, to_number(null)) unit_price,
860 DECODE(pltb.order_type_lookup_code, 'QUANTITY', to_number(null), pl.amount) amount,
861 ph.currency_code,
862 ph.rate_type,
863 ph.rate_date,
864 ph.rate,
865 ph.agent_id buyer_id,
866 ph.vendor_contact_id supplier_contact_id,
867 NVL(pl.negotiated_by_preparer_flag, 'N') negotiated_by_preparer_flag,
868 pltb.order_type_lookup_code,
869 pv.vendor_name supplier,
870 ph.global_agreement_flag,
871 --For global agreement status
872 NVL(t.enabled_flag, 'N') enabled_flag
873 FROM po_ga_org_assignments t,
874 po_headers_all ph,
875 po_lines_all pl,
876 icx_cat_items_ctx_hdrs_tlp ctxIn,
877 po_vendors pv,
878 po_line_types_b pltb
879 WHERE ph.global_agreement_flag = 'Y'
880 AND ph.po_header_id = t.po_header_id
881 AND ph.org_id <> t.organization_id
882 AND t.po_header_id = pl.po_header_id
883 AND pl.po_line_id = ctxIn.po_line_id
884 AND pl.org_id = ctxIn.org_id
885 AND pl.line_type_id = pltb.line_type_id
886 AND ph.vendor_id = pv.vendor_id(+)
887 AND ph.rowid BETWEEN g_start_rowid AND g_end_rowid
888 AND (ph.last_update_date > g_upgrade_last_run_date
889 OR pl.last_update_date > g_upgrade_last_run_date
890 OR t.last_update_date > g_upgrade_last_run_date)
891 ) doc,
892 icx_cat_items_ctx_hdrs_tlp ctx
893 WHERE doc.po_line_id = ctx.po_line_id (+)
894 AND doc.org_id = ctx.org_id (+)
895 AND doc.source_type = ctx.source_type (+)
896 AND doc.language = ctx.language (+);
897 END IF;
898
899 l_err_loc := 600;
900 populateGBPAs(l_gbpa_csr, ICX_CAT_UTIL_PVT.g_upgrade_const);
901
902 l_err_loc := 700;
903 CLOSE l_gbpa_csr;
904 EXCEPTION
905 WHEN OTHERS THEN
906 ICX_CAT_UTIL_PVT.logUnexpectedException(
907 G_PKG_NAME, l_api_name,
908 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
909 RAISE;
910 END openR12UpgradeGBPACursor;
911
912 PROCEDURE openGBPACursor
913 ( p_key IN NUMBER ,
914 p_po_line_id IN NUMBER
915 )
916 IS
917 l_api_name CONSTANT VARCHAR2(30) := 'openGBPACursor';
918 l_err_loc PLS_INTEGER;
919 l_gbpa_csr ICX_CAT_POPULATE_ITEM_PVT.g_item_csr_type;
920 BEGIN
921 l_err_loc := 100;
922 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
923 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
924 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
925 'Processing cursor:' || l_api_name ||
926 ', p_key:' || p_key ||
927 ', p_po_line_id:' || p_po_line_id );
928 END IF;
929
930 l_err_loc := 150;
931 --First close the cursor
932 IF (l_gbpa_csr%ISOPEN) THEN
933 l_err_loc := 200;
934 CLOSE l_gbpa_csr;
935 END IF;
936
937 l_err_loc := 300;
938 --Open the GBPA cursor now
939 OPEN l_gbpa_csr FOR
940 SELECT /*+ LEADING(doc) */
941 doc.*,
942 ctx.inventory_item_id ctx_inventory_item_id,
943 ctx.source_type ctx_source_type,
944 ctx.item_type ctx_item_type,
945 ctx.purchasing_org_id ctx_purchasing_org_id,
946 ctx.supplier_id ctx_supplier_id,
947 ctx.supplier_site_id ctx_supplier_site_id,
948 ctx.supplier_part_num ctx_supplier_part_num,
949 ctx.supplier_part_auxid ctx_supplier_part_auxid,
950 ctx.ip_category_id ctx_ip_category_id,
951 ctx.po_category_id ctx_po_category_id,
952 ctx.item_revision ctx_item_revision,
953 ROWIDTOCHAR(ctx.rowid) ctx_rowid
954 FROM
955 (
956 SELECT NVL(pl.item_id, -2) inventory_item_id,
957 pl.po_line_id po_line_id,
958 NVL(t.organization_id, -2) org_id,
959 ctxIn.language language,
960 'GLOBAL_BLANKET' source_type,
961 t.purchasing_org_id purchasing_org_id,
962 ctxIn.org_id owning_org_id,
963 ctxIn.po_category_id po_category_id,
964 ctxIn.supplier_id supplier_id,
965 ctxIn.supplier_part_num supplier_part_num,
966 ctxIn.supplier_part_auxid supplier_part_auxid,
967 t.vendor_site_id supplier_site_id,
968 ctxIn.ip_category_id ip_category_id,
969 ctxIn.ip_category_name ip_category_name,
970 NVL(pl.item_revision, '-2') item_revision,
971 ph.po_header_id,
972 ph.CLM_DOCUMENT_NUMBER document_number,
973 pl.line_num,
974 UPPER(NVL(pl.allow_price_override_flag, 'N')) allow_price_override_flag,
975 pl.not_to_exceed_price,
976 pl.line_type_id,
977 pl.unit_meas_lookup_code,
978 DECODE(pltb.order_type_lookup_code, 'QUANTITY', pl.unit_price, to_number(null)) unit_price,
979 DECODE(pltb.order_type_lookup_code, 'QUANTITY', to_number(null), pl.amount) amount,
980 ph.currency_code,
981 ph.rate_type,
982 ph.rate_date,
983 ph.rate,
984 ph.agent_id buyer_id,
985 ph.vendor_contact_id supplier_contact_id,
986 NVL(pl.negotiated_by_preparer_flag, 'N') negotiated_by_preparer_flag,
987 pltb.order_type_lookup_code,
988 pv.vendor_name supplier,
989 ph.global_agreement_flag,
990 NVL(t.enabled_flag, 'N') enabled_flag
991 FROM po_ga_org_assignments t,
992 po_headers_all ph,
993 po_lines_all pl,
994 po_session_gt pogt,
995 icx_cat_items_ctx_hdrs_tlp ctxIn,
996 po_vendors pv,
997 po_line_types_b pltb
998 WHERE ph.global_agreement_flag = 'Y'
999 AND ph.po_header_id = t.po_header_id
1000 AND ph.org_id <> t.organization_id
1001 AND ph.po_header_id = pl.po_header_id
1002 AND pogt.key = p_key
1003 AND NVL(pogt.char3, 'N') = 'Y'
1004 AND pl.po_line_id = pogt.index_num1
1005 AND ((pogt.char1 IS NULL)
1006 OR
1007 (pogt.char1 = 'Y' AND ctxIn.language = pogt.char2))
1008 AND pl.po_line_id = ctxIn.po_line_id
1009 AND pl.org_id = ctxIn.org_id
1010 AND pl.line_type_id = pltb.line_type_id
1011 AND ph.vendor_id = pv.vendor_id(+)
1012 AND pl.po_line_id >= p_po_line_id
1013 ) doc,
1014 icx_cat_items_ctx_hdrs_tlp ctx
1015 WHERE doc.po_line_id = ctx.po_line_id (+)
1016 AND doc.org_id = ctx.org_id (+)
1017 AND doc.source_type = ctx.source_type (+)
1018 AND doc.language = ctx.language (+)
1019 ORDER BY doc.po_line_id;
1020
1021 l_err_loc := 500;
1022 populateGBPAs(l_gbpa_csr, ICX_CAT_UTIL_PVT.g_online_const);
1023
1024 l_err_loc := 600;
1025 CLOSE l_gbpa_csr;
1026 EXCEPTION
1027 WHEN OTHERS THEN
1028 ICX_CAT_UTIL_PVT.logUnexpectedException(
1029 G_PKG_NAME, l_api_name,
1030 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
1031 RAISE;
1032 END openGBPACursor;
1033
1034 PROCEDURE openGBPAOrgAssignmentCursor
1035 ( p_key IN NUMBER ,
1036 p_po_line_id IN NUMBER
1037 )
1038 IS
1039 l_api_name CONSTANT VARCHAR2(30) := 'openGBPAOrgAssignmentCursor';
1040 l_err_loc PLS_INTEGER;
1041 l_gbpa_csr ICX_CAT_POPULATE_ITEM_PVT.g_item_csr_type;
1042 BEGIN
1043 l_err_loc := 100;
1044 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1045 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
1046 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1047 'Processing cursor:' || l_api_name ||
1048 ', p_key:' || p_key ||
1049 ', p_po_line_id:' || p_po_line_id );
1050 END IF;
1051
1052 l_err_loc := 150;
1053 --First close the cursor
1054 IF (l_gbpa_csr%ISOPEN) THEN
1055 l_err_loc := 200;
1056 CLOSE l_gbpa_csr;
1057 END IF;
1058
1059 l_err_loc := 300;
1060 --Open the GBPA cursor now
1061 OPEN l_gbpa_csr FOR
1062 SELECT /*+ LEADING(doc) */
1063 doc.*,
1064 ctx.inventory_item_id ctx_inventory_item_id,
1065 ctx.source_type ctx_source_type,
1066 ctx.item_type ctx_item_type,
1067 ctx.purchasing_org_id ctx_purchasing_org_id,
1068 ctx.supplier_id ctx_supplier_id,
1069 ctx.supplier_site_id ctx_supplier_site_id,
1070 ctx.supplier_part_num ctx_supplier_part_num,
1071 ctx.supplier_part_auxid ctx_supplier_part_auxid,
1072 ctx.ip_category_id ctx_ip_category_id,
1073 ctx.po_category_id ctx_po_category_id,
1074 ctx.item_revision ctx_item_revision,
1075 ROWIDTOCHAR(ctx.rowid) ctx_rowid
1076 FROM
1077 (
1078 SELECT NVL(pl.item_id, -2) inventory_item_id,
1079 pl.po_line_id po_line_id,
1080 NVL(t.organization_id, -2) org_id,
1081 ctxIn.language language,
1082 'GLOBAL_BLANKET' source_type,
1083 t.purchasing_org_id purchasing_org_id,
1084 ctxIn.org_id owning_org_id,
1085 ctxIn.po_category_id po_category_id,
1086 ctxIn.supplier_id supplier_id,
1087 ctxIn.supplier_part_num supplier_part_num,
1088 ctxIn.supplier_part_auxid supplier_part_auxid,
1089 t.vendor_site_id supplier_site_id,
1090 ctxIn.ip_category_id ip_category_id,
1091 ctxIn.ip_category_name ip_category_name,
1092 NVL(pl.item_revision, '-2') item_revision,
1093 ph.po_header_id,
1094 ph.CLM_DOCUMENT_NUMBER document_number,
1095 pl.line_num,
1096 UPPER(NVL(pl.allow_price_override_flag, 'N')) allow_price_override_flag,
1097 pl.not_to_exceed_price,
1098 pl.line_type_id,
1099 pl.unit_meas_lookup_code,
1100 DECODE(pltb.order_type_lookup_code, 'QUANTITY', pl.unit_price, to_number(null)) unit_price,
1101 DECODE(pltb.order_type_lookup_code, 'QUANTITY', to_number(null), pl.amount) amount,
1102 ph.currency_code,
1103 ph.rate_type,
1104 ph.rate_date,
1105 ph.rate,
1106 ph.agent_id buyer_id,
1107 ph.vendor_contact_id supplier_contact_id,
1108 NVL(pl.negotiated_by_preparer_flag, 'N') negotiated_by_preparer_flag,
1109 pltb.order_type_lookup_code,
1110 pv.vendor_name supplier,
1111 ph.global_agreement_flag,
1112 NVL(t.enabled_flag, 'N') enabled_flag
1113 FROM po_ga_org_assignments t,
1114 po_headers_all ph,
1115 po_lines_all pl,
1116 po_session_gt pogt,
1117 icx_cat_items_ctx_hdrs_tlp ctxIn,
1118 po_vendors pv,
1119 po_line_types_b pltb
1120 WHERE ph.global_agreement_flag = 'Y'
1121 AND ph.po_header_id = t.po_header_id
1122 AND ph.org_id <> t.organization_id
1123 AND t.po_header_id = pl.po_header_id
1124 AND pogt.key = p_key
1125 AND t.po_header_id = pogt.index_num1
1126 AND t.org_assignment_id = pogt.index_num2
1127 AND pl.po_line_id = ctxIn.po_line_id
1128 AND pl.org_id = ctxIn.org_id
1129 ANd pl.line_type_id = pltb.line_type_id
1130 AND ph.vendor_id = pv.vendor_id(+)
1131 AND pl.po_line_id >= p_po_line_id
1132 ) doc,
1133 icx_cat_items_ctx_hdrs_tlp ctx
1134 WHERE doc.po_line_id = ctx.po_line_id (+)
1135 AND doc.org_id = ctx.org_id (+)
1136 AND doc.source_type = ctx.source_type (+)
1137 AND doc.language = ctx.language (+)
1138 ORDER BY doc.po_line_id;
1139
1140 l_err_loc := 500;
1141 populateGBPAs(l_gbpa_csr, ICX_CAT_UTIL_PVT.g_online_const);
1142
1143 l_err_loc := 600;
1144 CLOSE l_gbpa_csr;
1145 EXCEPTION
1146 WHEN OTHERS THEN
1147 ICX_CAT_UTIL_PVT.logUnexpectedException(
1148 G_PKG_NAME, l_api_name,
1149 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
1150 RAISE;
1151 END openGBPAOrgAssignmentCursor;
1152
1153 --l_gbpa_csr, ICX_CAT_UTIL_PVT.g_upgrade_const
1154 PROCEDURE populateGBPAs
1155 ( p_podocs_csr IN ICX_CAT_POPULATE_ITEM_PVT.g_item_csr_type ,
1156 p_current_mode IN VARCHAR2
1157 )
1158 IS
1159 l_api_name CONSTANT VARCHAR2(30) := 'populateGBPAs';
1160 l_err_loc PLS_INTEGER;
1161 l_start_po_line_id NUMBER;
1162 l_err_string VARCHAR2(4000);
1163 l_batch_count PLS_INTEGER;
1164 l_row_count PLS_INTEGER;
1165 l_count PLS_INTEGER;
1166 l_GBPA_line_status PLS_INTEGER;
1167 l_current_ctx_item_rec ICX_CAT_POPULATE_ITEM_PVT.g_ctx_item_rec_type;
1168 l_current_flag_rec ICX_CAT_POPULATE_ITEM_PVT.g_bpa_online_flag_rec_type;
1169 l_authorization_status VARCHAR(25); --bug 16374319
1170
1171 ----- Start of declaring columns selected in the cursor -----
1172 l_inv_item_id_tbl DBMS_SQL.NUMBER_TABLE;
1173 l_po_line_id_tbl DBMS_SQL.NUMBER_TABLE;
1174 l_org_id_tbl DBMS_SQL.NUMBER_TABLE;
1175 l_language_tbl DBMS_SQL.VARCHAR2_TABLE;
1176 l_source_type_tbl DBMS_SQL.VARCHAR2_TABLE;
1177 l_purchasing_org_id_tbl DBMS_SQL.NUMBER_TABLE;
1178 l_owning_org_id_tbl DBMS_SQL.NUMBER_TABLE;
1179 l_po_category_id_tbl DBMS_SQL.NUMBER_TABLE;
1180 l_supplier_id_tbl DBMS_SQL.NUMBER_TABLE;
1181 l_supplier_part_num_tbl DBMS_SQL.VARCHAR2_TABLE;
1182 l_supplier_part_auxid_tbl DBMS_SQL.VARCHAR2_TABLE;
1183 l_supplier_site_id_tbl DBMS_SQL.NUMBER_TABLE;
1184 l_ip_category_id_tbl DBMS_SQL.NUMBER_TABLE;
1185 l_ip_category_name_tbl DBMS_SQL.VARCHAR2_TABLE;
1186 l_item_revision_tbl DBMS_SQL.VARCHAR2_TABLE;
1187 l_po_header_id_tbl DBMS_SQL.NUMBER_TABLE;
1188 l_document_number_tbl DBMS_SQL.VARCHAR2_TABLE;
1189 l_line_num_tbl DBMS_SQL.NUMBER_TABLE;
1190 l_allow_prc_override_flag_tbl DBMS_SQL.VARCHAR2_TABLE;
1191 l_not_to_exceed_price_tbl DBMS_SQL.NUMBER_TABLE;
1192 l_line_type_id_tbl DBMS_SQL.NUMBER_TABLE;
1193 l_unit_meas_lookup_code_tbl DBMS_SQL.VARCHAR2_TABLE;
1194 l_unit_price_tbl DBMS_SQL.NUMBER_TABLE;
1195 l_amount_tbl DBMS_SQL.NUMBER_TABLE;
1196 l_currency_code_tbl DBMS_SQL.VARCHAR2_TABLE;
1197 l_rate_type_tbl DBMS_SQL.VARCHAR2_TABLE;
1198 l_rate_date_tbl DBMS_SQL.DATE_TABLE;
1199 l_rate_tbl DBMS_SQL.NUMBER_TABLE;
1200 l_buyer_id_tbl DBMS_SQL.NUMBER_TABLE;
1201 l_supplier_contact_id_tbl DBMS_SQL.NUMBER_TABLE;
1202 l_negotiated_preparer_flag_tbl DBMS_SQL.VARCHAR2_TABLE;
1203 l_order_type_lookup_code_tbl DBMS_SQL.VARCHAR2_TABLE;
1204 l_supplier_tbl DBMS_SQL.VARCHAR2_TABLE;
1205 l_global_agreement_flag_tbl DBMS_SQL.VARCHAR2_TABLE;
1206 l_enabled_flag_tbl DBMS_SQL.VARCHAR2_TABLE;
1207 l_ctx_inventory_item_id_tbl DBMS_SQL.NUMBER_TABLE;
1208 l_ctx_source_type_tbl DBMS_SQL.VARCHAR2_TABLE;
1209 l_ctx_item_type_tbl DBMS_SQL.VARCHAR2_TABLE;
1210 l_ctx_purchasing_org_id_tbl DBMS_SQL.NUMBER_TABLE;
1211 l_ctx_supplier_id_tbl DBMS_SQL.NUMBER_TABLE;
1212 l_ctx_supplier_site_id_tbl DBMS_SQL.NUMBER_TABLE;
1213 l_ctx_supplier_part_num_tbl DBMS_SQL.VARCHAR2_TABLE;
1214 l_ctx_supplier_part_auxid_tbl DBMS_SQL.VARCHAR2_TABLE;
1215 l_ctx_ip_category_id_tbl DBMS_SQL.NUMBER_TABLE;
1216 l_ctx_po_category_id_tbl DBMS_SQL.NUMBER_TABLE;
1217 l_ctx_item_revision_tbl DBMS_SQL.VARCHAR2_TABLE;
1218 l_ctx_rowid_tbl DBMS_SQL.UROWID_TABLE;
1219 ------ End of declaring columns selected in the cursor ------
1220
1221 BEGIN
1222 l_err_loc := 100;
1223 l_batch_count := 0;
1224 l_row_count := 0;
1225 l_count := 0;
1226 l_current_flag_rec := null;
1227 LOOP
1228 l_err_loc := 200;
1229 l_inv_item_id_tbl.DELETE;
1230 l_po_line_id_tbl.DELETE;
1231 l_org_id_tbl.DELETE;
1232 l_language_tbl.DELETE;
1233 l_source_type_tbl.DELETE;
1234 l_purchasing_org_id_tbl.DELETE;
1235 l_owning_org_id_tbl.DELETE;
1236 l_po_category_id_tbl.DELETE;
1237 l_supplier_id_tbl.DELETE;
1238 l_supplier_part_num_tbl.DELETE;
1239 l_supplier_part_auxid_tbl.DELETE;
1240 l_supplier_site_id_tbl.DELETE;
1241 l_ip_category_id_tbl.DELETE;
1242 l_ip_category_name_tbl.DELETE;
1243 l_item_revision_tbl.DELETE;
1244 l_po_header_id_tbl.DELETE;
1245 l_document_number_tbl.DELETE;
1246 l_line_num_tbl.DELETE;
1247 l_allow_prc_override_flag_tbl.DELETE;
1248 l_not_to_exceed_price_tbl.DELETE;
1249 l_line_type_id_tbl.DELETE;
1250 l_unit_meas_lookup_code_tbl.DELETE;
1251 l_unit_price_tbl.DELETE;
1252 l_amount_tbl.DELETE;
1253 l_currency_code_tbl.DELETE;
1254 l_rate_type_tbl.DELETE;
1255 l_rate_date_tbl.DELETE;
1256 l_rate_tbl.DELETE;
1257 l_buyer_id_tbl.DELETE;
1258 l_supplier_contact_id_tbl.DELETE;
1259 l_negotiated_preparer_flag_tbl.DELETE;
1260 l_order_type_lookup_code_tbl.DELETE;
1261 l_supplier_tbl.DELETE;
1262 l_global_agreement_flag_tbl.DELETE;
1263 l_enabled_flag_tbl.DELETE;
1264 l_ctx_inventory_item_id_tbl.DELETE;
1265 l_ctx_source_type_tbl.DELETE;
1266 l_ctx_item_type_tbl.DELETE;
1267 l_ctx_purchasing_org_id_tbl.DELETE;
1268 l_ctx_supplier_id_tbl.DELETE;
1269 l_ctx_supplier_site_id_tbl.DELETE;
1270 l_ctx_supplier_part_num_tbl.DELETE;
1271 l_ctx_supplier_part_auxid_tbl.DELETE;
1272 l_ctx_ip_category_id_tbl.DELETE;
1273 l_ctx_po_category_id_tbl.DELETE;
1274 l_ctx_item_revision_tbl.DELETE;
1275 l_ctx_rowid_tbl.DELETE;
1276
1277 BEGIN
1278 l_err_loc := 300;
1279 FETCH p_podocs_csr BULK COLLECT INTO
1280 l_inv_item_id_tbl,
1281 l_po_line_id_tbl,
1282 l_org_id_tbl,
1283 l_language_tbl,
1284 l_source_type_tbl,
1285 l_purchasing_org_id_tbl,
1286 l_owning_org_id_tbl,
1287 l_po_category_id_tbl,
1288 l_supplier_id_tbl,
1289 l_supplier_part_num_tbl,
1290 l_supplier_part_auxid_tbl,
1291 l_supplier_site_id_tbl,
1292 l_ip_category_id_tbl,
1293 l_ip_category_name_tbl,
1294 l_item_revision_tbl,
1295 l_po_header_id_tbl,
1296 l_document_number_tbl,
1297 l_line_num_tbl,
1298 l_allow_prc_override_flag_tbl,
1299 l_not_to_exceed_price_tbl,
1300 l_line_type_id_tbl,
1301 l_unit_meas_lookup_code_tbl,
1302 l_unit_price_tbl,
1303 l_amount_tbl,
1304 l_currency_code_tbl,
1305 l_rate_type_tbl,
1306 l_rate_date_tbl,
1307 l_rate_tbl,
1308 l_buyer_id_tbl,
1309 l_supplier_contact_id_tbl,
1310 l_negotiated_preparer_flag_tbl,
1311 l_order_type_lookup_code_tbl,
1312 l_supplier_tbl,
1313 l_global_agreement_flag_tbl,
1314 l_enabled_flag_tbl,
1315 l_ctx_inventory_item_id_tbl,
1316 l_ctx_source_type_tbl,
1317 l_ctx_item_type_tbl,
1318 l_ctx_purchasing_org_id_tbl,
1319 l_ctx_supplier_id_tbl,
1320 l_ctx_supplier_site_id_tbl,
1321 l_ctx_supplier_part_num_tbl,
1322 l_ctx_supplier_part_auxid_tbl,
1323 l_ctx_ip_category_id_tbl,
1324 l_ctx_po_category_id_tbl,
1325 l_ctx_item_revision_tbl,
1326 l_ctx_rowid_tbl
1327 LIMIT ICX_CAT_UTIL_PVT.g_batch_size;
1328
1329 l_err_loc := 400;
1330 EXIT WHEN l_po_line_id_tbl.COUNT = 0;
1331
1332 l_err_loc := 500;
1333 l_batch_count := l_batch_count + 1;
1334
1335 l_err_loc := 600;
1336 l_count := l_po_line_id_tbl.COUNT;
1337 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1338 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
1339 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1340 'Num. of rows returned from the cursor:' || l_count);
1341 END IF;
1342 --Save the last po_line_id processed, so that re-open of cursor will start from the saved id.
1343 l_start_po_line_id := l_po_line_id_tbl(l_count);
1344
1345 l_row_count := l_row_count + l_count;
1346
1347 FOR i in 1..l_po_line_id_tbl.COUNT LOOP
1348 l_err_loc := 700;
1349 --First get the status of the current GBPA line
1350 l_GBPA_line_status := ICX_CAT_POPULATE_STATUS_PVT.getGlobalAgreementStatus(l_enabled_flag_tbl(i));
1351
1352 --bug 16374319 begin
1353 SELECT NVL(authorization_status, 'INCOMPLETE')
1354 INTO l_authorization_status
1355 FROM po_headers_all
1356 WHERE po_header_id = l_po_header_id_tbl(i);
1357
1358 l_err_loc := 800;
1359 IF (l_authorization_status = 'APPROVED'
1360 AND
1361 (l_ctx_rowid_tbl(i) IS NOT NULL OR
1362 l_GBPA_line_status = ICX_CAT_POPULATE_STATUS_PVT.VALID_FOR_POPULATE))
1363 --bug 16374319 end
1364 THEN
1365 l_err_loc := 900;
1366 l_current_ctx_item_rec.inventory_item_id := l_inv_item_id_tbl(i);
1367 l_current_ctx_item_rec.po_line_id := l_po_line_id_tbl(i);
1368 l_current_ctx_item_rec.req_template_name := TO_CHAR(ICX_CAT_UTIL_PVT.g_NULL_NUMBER);
1369 l_current_ctx_item_rec.req_template_line_num := TO_NUMBER(ICX_CAT_UTIL_PVT.g_NULL_NUMBER);
1370 l_current_ctx_item_rec.org_id := l_org_id_tbl(i);
1371 l_current_ctx_item_rec.language := l_language_tbl(i);
1372 l_current_ctx_item_rec.source_type := l_source_type_tbl(i);
1373 l_current_ctx_item_rec.item_type := ICX_CAT_UTIL_PVT.g_purchase_item_type;
1374 l_current_ctx_item_rec.purchasing_org_id := l_purchasing_org_id_tbl(i);
1375 l_current_ctx_item_rec.owning_org_id := l_owning_org_id_tbl(i);
1376 l_current_ctx_item_rec.po_category_id := l_po_category_id_tbl(i);
1377 l_current_ctx_item_rec.supplier_id := l_supplier_id_tbl(i);
1378 l_current_ctx_item_rec.supplier_part_num := l_supplier_part_num_tbl(i);
1379 l_current_ctx_item_rec.supplier_part_auxid := l_supplier_part_auxid_tbl(i);
1380 l_current_ctx_item_rec.supplier_site_id := l_supplier_site_id_tbl(i);
1381 l_current_ctx_item_rec.status := l_GBPA_line_status;
1382 l_current_ctx_item_rec.ip_category_id := l_ip_category_id_tbl(i);
1383 l_current_ctx_item_rec.ip_category_name := l_ip_category_name_tbl(i);
1384 l_current_ctx_item_rec.req_template_po_line_id := NULL;
1385 l_current_ctx_item_rec.item_revision := l_item_revision_tbl(i);
1386 l_current_ctx_item_rec.po_header_id := l_po_header_id_tbl(i);
1387 l_current_ctx_item_rec.document_number := l_document_number_tbl(i);
1388 l_current_ctx_item_rec.line_num := l_line_num_tbl(i);
1389 l_current_ctx_item_rec.allow_price_override_flag := l_allow_prc_override_flag_tbl(i);
1390 l_current_ctx_item_rec.not_to_exceed_price := l_not_to_exceed_price_tbl(i);
1391 l_current_ctx_item_rec.line_type_id := l_line_type_id_tbl(i);
1392 l_current_ctx_item_rec.unit_meas_lookup_code := l_unit_meas_lookup_code_tbl(i);
1393 l_current_ctx_item_rec.suggested_quantity := NULL;
1394 l_current_ctx_item_rec.unit_price := l_unit_price_tbl(i);
1395 l_current_ctx_item_rec.amount := l_amount_tbl(i);
1396 l_current_ctx_item_rec.currency_code := l_currency_code_tbl(i);
1397 l_current_ctx_item_rec.rate_type := l_rate_type_tbl(i);
1398 l_current_ctx_item_rec.rate_date := l_rate_date_tbl(i);
1399 l_current_ctx_item_rec.rate := l_rate_tbl(i);
1400 l_current_ctx_item_rec.buyer_id := l_buyer_id_tbl(i);
1401 l_current_ctx_item_rec.supplier_contact_id := l_supplier_contact_id_tbl(i);
1402 l_current_ctx_item_rec.rfq_required_flag := 'N';
1403 l_current_ctx_item_rec.negotiated_by_preparer_flag := l_negotiated_preparer_flag_tbl(i);
1404 l_current_ctx_item_rec.description := NULL;
1405 l_current_ctx_item_rec.order_type_lookup_code := l_order_type_lookup_code_tbl(i);
1406 l_current_ctx_item_rec.supplier := l_supplier_tbl(i);
1407 l_current_ctx_item_rec.global_agreement_flag := l_global_agreement_flag_tbl(i);
1408 l_current_ctx_item_rec.merged_source_type := 'SRC_DOC';
1409 l_current_ctx_item_rec.ctx_inventory_item_id := l_ctx_inventory_item_id_tbl(i);
1410 l_current_ctx_item_rec.ctx_source_type := l_ctx_source_type_tbl(i);
1411 l_current_ctx_item_rec.ctx_item_type := l_ctx_item_type_tbl(i);
1412 l_current_ctx_item_rec.ctx_purchasing_org_id := l_ctx_purchasing_org_id_tbl(i);
1413 l_current_ctx_item_rec.ctx_supplier_id := l_ctx_supplier_id_tbl(i);
1414 l_current_ctx_item_rec.ctx_supplier_site_id := l_ctx_supplier_site_id_tbl(i);
1415 l_current_ctx_item_rec.ctx_supplier_part_num := l_ctx_supplier_part_num_tbl(i);
1416 l_current_ctx_item_rec.ctx_supplier_part_auxid := l_ctx_supplier_part_auxid_tbl(i);
1417 l_current_ctx_item_rec.ctx_ip_category_id := l_ctx_ip_category_id_tbl(i);
1418 l_current_ctx_item_rec.ctx_po_category_id := l_ctx_po_category_id_tbl(i);
1419 l_current_ctx_item_rec.ctx_item_revision := l_ctx_item_revision_tbl(i);
1420 l_current_ctx_item_rec.ctx_rowid := l_ctx_rowid_tbl(i);
1421
1422 l_err_loc := 1300;
1423 ICX_CAT_POPULATE_ITEM_PVT.processCurrentCtxItemRow(l_current_ctx_item_rec, g_current_cursor, p_current_mode);
1424
1425 l_err_loc := 1400;
1426 ICX_CAT_POPULATE_ITEM_PVT.populateItemCtxTables('INLOOP', ICX_CAT_UTIL_PVT.g_GBPACsr_const);
1427 ELSE
1428 IF (p_current_mode = ICX_CAT_UTIL_PVT.g_upgrade_const) THEN
1429 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1430 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1431 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1432 'Row #:' || i ||
1433 ', with po_line_id:' || l_po_line_id_tbl(i) ||', not processed' ||
1434 ', l_enabled_flag_tbl:' || l_enabled_flag_tbl(i) ||
1435 ', status: ' || l_GBPA_line_status);
1436 END IF;
1437 ELSE
1438 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1439 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
1440 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1441 'Row #:' || i ||
1442 ', with po_line_id:' || l_po_line_id_tbl(i) ||', not processed' ||
1443 ', l_enabled_flag_tbl:' || l_enabled_flag_tbl(i) ||
1444 ', status: ' || l_GBPA_line_status);
1445 END IF;
1446 END IF;
1447 END IF;
1448 END LOOP; -- FOR LOOP of l_po_line_id_tbl
1449
1450 l_err_loc := 1500;
1451 EXIT WHEN l_po_line_id_tbl.COUNT < ICX_CAT_UTIL_PVT.g_batch_size;
1452 EXCEPTION
1453 WHEN ICX_CAT_UTIL_PVT.g_snap_shot_too_old THEN
1454 l_err_string := 'ICX_CAT_POPULATE_PODOCS_PVT.populateGBPAs' ||l_err_loc
1455 ||', Total processed batches:' ||l_batch_count
1456 ||', Cursor will be reopened with po_line_id:' ||l_start_po_line_id;
1457 IF (FND_API.To_Boolean(ICX_CAT_UTIL_PVT.g_COMMIT)) THEN
1458 ICX_CAT_UTIL_PVT.logAndCommitSnapShotTooOld(g_pkg_name, l_api_name, l_err_string);
1459 -- Closing and reopen of cursor will be done by called procedures
1460 IF (p_current_mode = ICX_CAT_UTIL_PVT.g_upgrade_const) THEN
1461 l_err_loc := 1600;
1462 openR12UpgradeGBPACursor;
1463 ELSE
1464 l_err_loc := 1700;
1465 IF (g_current_cursor = 'ORG_ASSIGNMENT_CSR') THEN
1466 l_err_loc := 1800;
1467 openGBPAOrgAssignmentCursor(g_key, l_start_po_line_id);
1468 ELSE
1469 l_err_loc := 1900;
1470 openGBPACursor(g_key, l_start_po_line_id);
1471 END IF;
1472 END IF;
1473 ELSE
1474 RAISE;
1475 END IF;
1476 END;
1477 END LOOP; -- Cursor loop
1478
1479 l_err_loc := 2000;
1480 ICX_CAT_POPULATE_ITEM_PVT.populateItemCtxTables('OUTLOOP', ICX_CAT_UTIL_PVT.g_GBPACsr_const);
1481
1482 l_err_loc := 2100;
1483 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1484 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
1485 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1486 'populateGBPAs in mode:'|| p_current_mode ||' done; '||
1487 'Total num. of batches processed:' ||l_batch_count ||
1488 ', Total num. of rows fetched:' ||l_row_count);
1489 END IF;
1490 EXCEPTION
1491 WHEN OTHERS THEN
1492 ICX_CAT_UTIL_PVT.logUnexpectedException(
1493 G_PKG_NAME, l_api_name,
1494 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
1495 RAISE;
1496 END populateGBPAs;
1497
1498 ----------------------------------------------------------------------
1499 ----------------- End of GBPA specific Code ----------------------
1500 ----------------- Start of BPA and GBPA Online API calls ------------
1501 ----------------------------------------------------------------------
1502
1503 PROCEDURE populateOnlineBlankets
1504 ( p_key IN NUMBER
1505 )
1506 IS
1507 l_api_name CONSTANT VARCHAR2(30) := 'populateOnlineBlankets';
1508 l_err_loc PLS_INTEGER;
1509 BEGIN
1510 l_err_loc := 100;
1511 -- Set the batch_size
1512 ICX_CAT_UTIL_PVT.setBatchSize;
1513
1514 l_err_loc := 200;
1515 -- Set the who columns
1516 ICX_CAT_UTIL_PVT.setWhoColumns(null);
1517
1518 l_err_loc := 250;
1519 g_current_cursor := 'GBPA_CSR';
1520
1521 l_err_loc := 300;
1522 g_key := p_key;
1523
1524 l_err_loc := 350;
1525 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1526 ICX_CAT_UTIL_PVT.logPOSessionGTData(p_key);
1527 END IF;
1528
1529 l_err_loc := 400;
1530 openBPACursor(p_key, 0);
1531
1532 l_err_loc := 500;
1533 openGBPACursor(p_key, 0);
1534
1535 l_err_loc := 600;
1536 g_metadataTblFormed := FALSE;
1537 g_CtxSqlForPODocsFormed := FALSE;
1538
1539 l_err_loc := 700;
1540 ICX_CAT_POPULATE_CTXSTRING_PVT.populateCtxCatgAtt(ICX_CAT_UTIL_PVT.g_who_columns_rec.internal_request_id);
1541
1542 l_err_loc := 800;
1543 EXCEPTION
1544 WHEN OTHERS THEN
1545 ICX_CAT_UTIL_PVT.logUnexpectedException(
1546 G_PKG_NAME, l_api_name,
1547 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
1548 RAISE;
1549 END populateOnlineBlankets;
1550
1551 PROCEDURE populateOnlineOrgAssgnmnts
1552 ( p_key IN NUMBER
1553 )
1554 IS
1555 l_api_name CONSTANT VARCHAR2(30) := 'populateOnlineOrgAssgnmnts';
1556 l_err_loc PLS_INTEGER;
1557 BEGIN
1558 l_err_loc := 100;
1559 -- Set the batch_size
1560 ICX_CAT_UTIL_PVT.setBatchSize;
1561
1562 l_err_loc := 200;
1563 -- Set the who columns
1564 ICX_CAT_UTIL_PVT.setWhoColumns(null);
1565
1566 l_err_loc := 300;
1567 g_current_cursor := 'ORG_ASSIGNMENT_CSR';
1568 g_key := p_key;
1569
1570 l_err_loc := 350;
1571 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1572 ICX_CAT_UTIL_PVT.logPOSessionGTData(p_key);
1573 END IF;
1574
1575 l_err_loc := 400;
1576 openBPAOrgAssignmentCursor(p_key, 0);
1577
1578 l_err_loc := 450;
1579 openGBPAOrgAssignmentCursor(p_key, 0);
1580
1581 l_err_loc := 500;
1582 g_metadataTblFormed := FALSE;
1583 g_CtxSqlForPODocsFormed := FALSE;
1584
1585 l_err_loc := 600;
1586 -- No need of to re-populate category attributes
1587 -- i.e. call ICX_CAT_POPULATE_CTXSTRING_PVT.populateCtxCatgAtt
1588 -- because the changes are only in the org assignments.
1589 -- ICX_CAT_POPULATE_CTXSTRING_PVT.populateCtxCatgAtt(ICX_CAT_UTIL_PVT.g_who_columns_rec.internal_request_id);
1590
1591 l_err_loc := 700;
1592 EXCEPTION
1593 WHEN OTHERS THEN
1594 ICX_CAT_UTIL_PVT.logUnexpectedException(
1595 G_PKG_NAME, l_api_name,
1596 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
1597 RAISE;
1598 END populateOnlineOrgAssgnmnts;
1599
1600 ----------------------------------------------------------------------
1601 -------------- End of BPA and GBPA Online API calls -----------------
1602 -------------- Start of BPA and Quote specific Code -----------------
1603 ----------------------------------------------------------------------
1604
1605 PROCEDURE openR12UpgradeBPAQuoteCursor
1606 IS
1607 l_api_name CONSTANT VARCHAR2(30) := 'openR12UpgradeBPAQuoteCursor';
1608 l_err_loc PLS_INTEGER;
1609 l_bpa_quote_csr ICX_CAT_POPULATE_ITEM_PVT.g_item_csr_type;
1610 BEGIN
1611 l_err_loc := 100;
1612 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1613 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
1614 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1615 'Processing cursor:' || l_api_name ||
1616 ', g_upgrade_last_run_date:' || g_upgrade_last_run_date ||
1617 ', g_start_rowid:' || g_start_rowid ||
1618 ', g_end_rowid:' || g_end_rowid );
1619 END IF;
1620
1621 l_err_loc := 150;
1622 --First close the cursor
1623 IF (l_bpa_quote_csr%ISOPEN) THEN
1624 l_err_loc := 200;
1625 CLOSE l_bpa_quote_csr;
1626 END IF;
1627
1628 l_err_loc := 300;
1629 --Comments on the cursor
1630 --Move the outside operation flag of a line type to the main cursor from the
1631 --status function due to the following reasons:
1632 --1. PO has confirmed that the outside operation flag
1633 -- of a line type cannot be changed once set
1634 --2. The main cursor anyways joins with po_line_types_b
1635 -- to eliminate the TEMP LABOR line
1636 IF (g_upgrade_last_run_date) IS NULL THEN
1637 l_err_loc := 400;
1638 OPEN l_bpa_quote_csr FOR
1639 SELECT /*+ LEADING(doc) use_nl_with_index(ctx,ICX_CAT_ITEMS_CTX_HDRS_TLP_PK) NO_EXPAND */
1640 doc.*,
1641 ctx.inventory_item_id ctx_inventory_item_id,
1642 ctx.source_type ctx_source_type,
1643 ctx.item_type ctx_item_type,
1644 ctx.purchasing_org_id ctx_purchasing_org_id,
1645 ctx.supplier_id ctx_supplier_id,
1646 ctx.supplier_site_id ctx_supplier_site_id,
1647 ctx.supplier_part_num ctx_supplier_part_num,
1648 ctx.supplier_part_auxid ctx_supplier_part_auxid,
1649 ctx.ip_category_id ctx_ip_category_id,
1650 ctx.po_category_id ctx_po_category_id,
1651 ctx.item_revision ctx_item_revision,
1652 ROWIDTOCHAR(ctx.rowid) ctx_rowid
1653 FROM (
1654 SELECT /*+ ROWID(poh) use_nl(pl,ga,po_tlp,ic) */
1655 NVL(pl.item_id, -2) inventory_item_id,
1656 pl.po_line_id po_line_id,
1657 NVL(pl.org_id, -2) org_id,
1658 po_tlp.language language,
1659 ph.type_lookup_code source_type,
1660 DECODE(NVL(ph.global_agreement_flag, 'N'),
1661 'Y', NVL(ga.purchasing_org_id, pl.org_id),
1662 NVL(pl.org_id, -2)) purchasing_org_id,
1663 pl.category_id po_category_id,
1664 NVL(ph.vendor_id, -2) supplier_id,
1665 NVL(pl.vendor_product_num, '##NULL##') supplier_part_num,
1666 NVL(pl.supplier_part_auxid, '##NULL##') supplier_part_auxid,
1667 DECODE(NVL(ph.global_agreement_flag, 'N'),
1668 'Y', NVL(ga.vendor_site_id, -2),
1669 NVL(ph.vendor_site_id, -2)) supplier_site_id,
1670 pl.ip_category_id ip_category_id,
1671 ic.category_name ip_category_name,
1672 NVL(pl.item_revision, '-2') item_revision,
1673 ph.po_header_id,
1674 ph.segment1 document_number,
1675 pl.line_num,
1676 UPPER(NVL(pl.allow_price_override_flag, 'N')) allow_price_override_flag,
1677 pl.not_to_exceed_price,
1678 pl.line_type_id,
1679 pl.unit_meas_lookup_code,
1680 DECODE(pltb.order_type_lookup_code, 'QUANTITY', pl.unit_price, to_number(null)) unit_price,
1681 DECODE(pltb.order_type_lookup_code, 'QUANTITY', to_number(null), pl.amount) amount,
1682 ph.currency_code,
1683 ph.rate_type,
1684 ph.rate_date,
1685 ph.rate,
1686 ph.agent_id buyer_id,
1687 ph.vendor_contact_id supplier_contact_id,
1688 DECODE(ph.type_lookup_code, 'QUOTATION', 'Y',
1689 NVL(pl.negotiated_by_preparer_flag, 'N')) negotiated_by_preparer_flag,
1690 pltb.order_type_lookup_code,
1691 pv.vendor_name supplier,
1692 ph.global_agreement_flag global_agreement_flag,
1693 --For Quote line status
1694 DECODE(ph.type_lookup_code, 'QUOTATION',
1695 ICX_CAT_POPULATE_STATUS_PVT.getQuoteLineStatus(pl.po_line_id),
1696 NULL) quote_status,
1697 --For blanket line status
1698 ph.approved_date,
1699 NVL(ph.authorization_status, 'INCOMPLETE') authorization_status,
1700 NVL(ph.frozen_flag, 'N') frozen_flag,
1701 NVL(ph.cancel_flag, 'N') hdr_cancel_flag,
1702 NVL(pl.cancel_flag, 'N') line_cancel_flag,
1703 NVL(ph.closed_code, 'OPEN') hdr_closed_code,
1704 NVL(pl.closed_code, 'OPEN') line_closed_code,
1705 NVL(TRUNC(ph.end_date), TRUNC(SYSDATE + 1)) end_date,
1706 NVL(TRUNC(pl.expiration_date), TRUNC(SYSDATE + 1)) expiration_date,
1707 TRUNC(SYSDATE) system_date,
1708 ph.created_by
1709 FROM po_headers_all ph,
1710 po_lines_all pl,
1711 po_ga_org_assignments ga,
1712 po_attribute_values_tlp po_tlp,
1713 po_line_types_b pltb,
1714 icx_cat_categories_tl ic,
1715 po_vendors pv
1716 WHERE ph.po_header_id = pl.po_header_id
1717 AND ph.type_lookup_code IN ('BLANKET', 'QUOTATION')
1718 AND ph.po_header_id = ga.po_header_id (+)
1719 AND ph.org_id = ga.organization_id (+)
1720 AND pl.po_line_id = po_tlp.po_line_id
1721 AND pl.line_type_id = pltb.line_type_id
1722 AND NVL(pltb.purchase_basis, 'NULL') <> 'TEMP LABOR'
1723 AND NVL(pltb.outside_operation_flag, 'N') = 'N'
1724 AND po_tlp.ip_category_id = ic.rt_category_id (+)
1725 AND po_tlp.language = ic.language (+)
1726 AND ph.vendor_id = pv.vendor_id(+)
1727 AND ph.rowid BETWEEN g_start_rowid AND g_end_rowid
1728 ) doc,
1729 icx_cat_items_ctx_hdrs_tlp ctx
1730 WHERE doc.po_line_id = ctx.po_line_id (+)
1731 AND doc.org_id = ctx.org_id (+)
1732 AND doc.source_type = ctx.source_type (+)
1733 AND doc.language = ctx.language (+)
1734 AND (doc.source_type = 'BLANKET'
1735 OR (ctx.rowid IS NOT NULL OR doc.quote_status = 0));
1736 ELSE
1737 l_err_loc := 500;
1738 OPEN l_bpa_quote_csr FOR
1739 SELECT /*+ LEADING(doc) use_nl_with_index(ctx,ICX_CAT_ITEMS_CTX_HDRS_TLP_PK) NO_EXPAND */
1740 doc.*,
1741 ctx.inventory_item_id ctx_inventory_item_id,
1742 ctx.source_type ctx_source_type,
1743 ctx.item_type ctx_item_type,
1744 ctx.purchasing_org_id ctx_purchasing_org_id,
1745 ctx.supplier_id ctx_supplier_id,
1746 ctx.supplier_site_id ctx_supplier_site_id,
1747 ctx.supplier_part_num ctx_supplier_part_num,
1748 ctx.supplier_part_auxid ctx_supplier_part_auxid,
1749 ctx.ip_category_id ctx_ip_category_id,
1750 ctx.po_category_id ctx_po_category_id,
1751 ctx.item_revision ctx_item_revision,
1752 ROWIDTOCHAR(ctx.rowid) ctx_rowid
1753 FROM (
1754 SELECT /*+ ROWID(poh) use_nl(pl,ga,po_tlp,ic) */
1755 NVL(pl.item_id, -2) inventory_item_id,
1756 pl.po_line_id po_line_id,
1757 NVL(pl.org_id, -2) org_id,
1758 po_tlp.language language,
1759 ph.type_lookup_code source_type,
1760 DECODE(NVL(ph.global_agreement_flag, 'N'),
1761 'Y', NVL(ga.purchasing_org_id, pl.org_id),
1762 NVL(pl.org_id, -2)) purchasing_org_id,
1763 pl.category_id po_category_id,
1764 NVL(ph.vendor_id, -2) supplier_id,
1765 NVL(pl.vendor_product_num, '##NULL##') supplier_part_num,
1766 NVL(pl.supplier_part_auxid, '##NULL##') supplier_part_auxid,
1767 DECODE(NVL(ph.global_agreement_flag, 'N'),
1768 'Y', NVL(ga.vendor_site_id, -2),
1769 NVL(ph.vendor_site_id, -2)) supplier_site_id,
1770 pl.ip_category_id ip_category_id,
1771 ic.category_name ip_category_name,
1772 NVL(pl.item_revision, '-2') item_revision,
1773 ph.po_header_id,
1774 ph.segment1 document_number,
1775 pl.line_num,
1776 UPPER(NVL(pl.allow_price_override_flag, 'N')) allow_price_override_flag,
1777 pl.not_to_exceed_price,
1778 pl.line_type_id,
1779 pl.unit_meas_lookup_code,
1780 DECODE(pltb.order_type_lookup_code, 'QUANTITY', pl.unit_price, to_number(null)) unit_price,
1781 DECODE(pltb.order_type_lookup_code, 'QUANTITY', to_number(null), pl.amount) amount,
1782 ph.currency_code,
1783 ph.rate_type,
1784 ph.rate_date,
1785 ph.rate,
1786 ph.agent_id buyer_id,
1787 ph.vendor_contact_id supplier_contact_id,
1788 DECODE(ph.type_lookup_code, 'QUOTATION', 'Y',
1789 NVL(pl.negotiated_by_preparer_flag, 'N')) negotiated_by_preparer_flag,
1790 pltb.order_type_lookup_code,
1791 pv.vendor_name supplier,
1792 ph.global_agreement_flag global_agreement_flag,
1793 --For Quote line status
1794 DECODE(ph.type_lookup_code, 'QUOTATION',
1795 ICX_CAT_POPULATE_STATUS_PVT.getQuoteLineStatus(pl.po_line_id),
1796 NULL) quote_status,
1797 --For blanket line status
1798 ph.approved_date,
1799 NVL(ph.authorization_status, 'INCOMPLETE') authorization_status,
1800 NVL(ph.frozen_flag, 'N') frozen_flag,
1801 NVL(ph.cancel_flag, 'N') hdr_cancel_flag,
1802 NVL(pl.cancel_flag, 'N') line_cancel_flag,
1803 NVL(ph.closed_code, 'OPEN') hdr_closed_code,
1804 NVL(pl.closed_code, 'OPEN') line_closed_code,
1805 NVL(TRUNC(ph.end_date), TRUNC(SYSDATE + 1)) end_date,
1806 NVL(TRUNC(pl.expiration_date), TRUNC(SYSDATE + 1)) expiration_date,
1807 TRUNC(SYSDATE) system_date,
1808 ph.created_by
1809 FROM po_headers_all ph,
1810 po_lines_all pl,
1811 po_ga_org_assignments ga,
1812 po_attribute_values_tlp po_tlp,
1813 po_line_types_b pltb,
1814 icx_cat_categories_tl ic,
1815 po_vendors pv
1816 WHERE ph.po_header_id = pl.po_header_id
1817 AND ph.type_lookup_code IN ('BLANKET', 'QUOTATION')
1818 AND ph.po_header_id = ga.po_header_id (+)
1819 AND ph.org_id = ga.organization_id (+)
1820 AND pl.po_line_id = po_tlp.po_line_id
1821 AND pl.line_type_id = pltb.line_type_id
1822 AND NVL(pltb.purchase_basis, 'NULL') <> 'TEMP LABOR'
1823 AND NVL(pltb.outside_operation_flag, 'N') = 'N'
1824 AND po_tlp.ip_category_id = ic.rt_category_id (+)
1825 AND po_tlp.language = ic.language (+)
1826 AND ph.vendor_id = pv.vendor_id(+)
1827 AND ph.rowid BETWEEN g_start_rowid AND g_end_rowid
1828 AND (ph.last_update_date > g_upgrade_last_run_date
1829 OR pl.last_update_date > g_upgrade_last_run_date
1830 OR ga.last_update_date > g_upgrade_last_run_date
1831 OR po_tlp.last_update_date > g_upgrade_last_run_date)
1832 ) doc,
1833 icx_cat_items_ctx_hdrs_tlp ctx
1834 WHERE doc.po_line_id = ctx.po_line_id (+)
1835 AND doc.org_id = ctx.org_id (+)
1836 AND doc.source_type = ctx.source_type (+)
1837 AND doc.language = ctx.language (+)
1838 AND (doc.source_type = 'BLANKET'
1839 OR (ctx.rowid IS NOT NULL OR doc.quote_status = 0));
1840 END IF;
1841
1842 l_err_loc := 700;
1843 populateBPAandQuotes(l_bpa_quote_csr, ICX_CAT_UTIL_PVT.g_upgrade_const);
1844
1845 l_err_loc := 800;
1846 CLOSE l_bpa_quote_csr;
1847 EXCEPTION
1848 WHEN OTHERS THEN
1849 ICX_CAT_UTIL_PVT.logUnexpectedException(
1850 G_PKG_NAME, l_api_name,
1851 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
1852 RAISE;
1853 END openR12UpgradeBPAQuoteCursor;
1854
1855 PROCEDURE populateBPAandQuotes
1856 ( p_podocs_csr IN ICX_CAT_POPULATE_ITEM_PVT.g_item_csr_type ,
1857 p_current_mode IN VARCHAR2
1858 )
1859 IS
1860 l_api_name CONSTANT VARCHAR2(30) := 'populateBPAandQuotes';
1861 l_err_loc PLS_INTEGER;
1862 l_err_string VARCHAR2(4000);
1863 l_batch_count PLS_INTEGER;
1864 l_row_count PLS_INTEGER;
1865 l_count PLS_INTEGER;
1866 l_BPA_line_status_rec ICX_CAT_POPULATE_STATUS_PVT.g_BPA_line_status_rec_type;
1867 l_podoc_status PLS_INTEGER;
1868 l_current_ctx_item_rec ICX_CAT_POPULATE_ITEM_PVT.g_ctx_item_rec_type;
1869
1870 ----- Start of declaring columns selected in the cursor -----
1871 l_inv_item_id_tbl DBMS_SQL.NUMBER_TABLE;
1872 l_po_line_id_tbl DBMS_SQL.NUMBER_TABLE;
1873 l_org_id_tbl DBMS_SQL.NUMBER_TABLE;
1874 l_language_tbl DBMS_SQL.VARCHAR2_TABLE;
1875 l_source_type_tbl DBMS_SQL.VARCHAR2_TABLE;
1876 l_purchasing_org_id_tbl DBMS_SQL.NUMBER_TABLE;
1877 l_po_category_id_tbl DBMS_SQL.NUMBER_TABLE;
1878 l_supplier_id_tbl DBMS_SQL.NUMBER_TABLE;
1879 l_supplier_part_num_tbl DBMS_SQL.VARCHAR2_TABLE;
1880 l_supplier_part_auxid_tbl DBMS_SQL.VARCHAR2_TABLE;
1881 l_supplier_site_id_tbl DBMS_SQL.NUMBER_TABLE;
1882 l_ip_category_id_tbl DBMS_SQL.NUMBER_TABLE;
1883 l_ip_category_name_tbl DBMS_SQL.VARCHAR2_TABLE;
1884 l_item_revision_tbl DBMS_SQL.VARCHAR2_TABLE;
1885 l_po_header_id_tbl DBMS_SQL.NUMBER_TABLE;
1886 l_document_number_tbl DBMS_SQL.VARCHAR2_TABLE;
1887 l_line_num_tbl DBMS_SQL.NUMBER_TABLE;
1888 l_allow_prc_override_flag_tbl DBMS_SQL.VARCHAR2_TABLE;
1889 l_not_to_exceed_price_tbl DBMS_SQL.NUMBER_TABLE;
1890 l_line_type_id_tbl DBMS_SQL.NUMBER_TABLE;
1891 l_unit_meas_lookup_code_tbl DBMS_SQL.VARCHAR2_TABLE;
1892 l_unit_price_tbl DBMS_SQL.NUMBER_TABLE;
1893 l_amount_tbl DBMS_SQL.NUMBER_TABLE;
1894 l_currency_code_tbl DBMS_SQL.VARCHAR2_TABLE;
1895 l_rate_type_tbl DBMS_SQL.VARCHAR2_TABLE;
1896 l_rate_date_tbl DBMS_SQL.DATE_TABLE;
1897 l_rate_tbl DBMS_SQL.NUMBER_TABLE;
1898 l_buyer_id_tbl DBMS_SQL.NUMBER_TABLE;
1899 l_supplier_contact_id_tbl DBMS_SQL.NUMBER_TABLE;
1900 l_negotiated_preparer_flag_tbl DBMS_SQL.VARCHAR2_TABLE;
1901 l_order_type_lookup_code_tbl DBMS_SQL.VARCHAR2_TABLE;
1902 l_supplier_tbl DBMS_SQL.VARCHAR2_TABLE;
1903 l_global_agreement_flag_tbl DBMS_SQL.VARCHAR2_TABLE;
1904 l_quote_status_tbl DBMS_SQL.VARCHAR2_TABLE;
1905 l_approved_date_tbl DBMS_SQL.DATE_TABLE;
1906 l_authorization_status_tbl DBMS_SQL.VARCHAR2_TABLE;
1907 l_frozen_flag_tbl DBMS_SQL.VARCHAR2_TABLE;
1908 l_hdr_cancel_flag_tbl DBMS_SQL.VARCHAR2_TABLE;
1909 l_line_cancel_flag_tbl DBMS_SQL.VARCHAR2_TABLE;
1910 l_hdr_closed_code_tbl DBMS_SQL.VARCHAR2_TABLE;
1911 l_line_closed_code_tbl DBMS_SQL.VARCHAR2_TABLE;
1912 l_end_date_tbl DBMS_SQL.DATE_TABLE;
1913 l_expiration_date_tbl DBMS_SQL.DATE_TABLE;
1914 l_system_date_tbl DBMS_SQL.DATE_TABLE;
1915 l_created_by_tbl DBMS_SQL.NUMBER_TABLE;
1916 l_ctx_inventory_item_id_tbl DBMS_SQL.NUMBER_TABLE;
1917 l_ctx_source_type_tbl DBMS_SQL.VARCHAR2_TABLE;
1918 l_ctx_item_type_tbl DBMS_SQL.VARCHAR2_TABLE;
1919 l_ctx_purchasing_org_id_tbl DBMS_SQL.NUMBER_TABLE;
1920 l_ctx_supplier_id_tbl DBMS_SQL.NUMBER_TABLE;
1921 l_ctx_supplier_site_id_tbl DBMS_SQL.NUMBER_TABLE;
1922 l_ctx_supplier_part_num_tbl DBMS_SQL.VARCHAR2_TABLE;
1923 l_ctx_supplier_part_auxid_tbl DBMS_SQL.VARCHAR2_TABLE;
1924 l_ctx_ip_category_id_tbl DBMS_SQL.NUMBER_TABLE;
1925 l_ctx_po_category_id_tbl DBMS_SQL.NUMBER_TABLE;
1926 l_ctx_item_revision_tbl DBMS_SQL.VARCHAR2_TABLE;
1927 l_ctx_rowid_tbl DBMS_SQL.UROWID_TABLE;
1928 ------ End of declaring columns selected in the cursor ------
1929
1930 BEGIN
1931 l_err_loc := 100;
1932 l_batch_count := 0;
1933 l_row_count := 0;
1934 l_count := 0;
1935 LOOP
1936 l_err_loc := 110;
1937 l_inv_item_id_tbl.DELETE;
1938 l_po_line_id_tbl.DELETE;
1939 l_org_id_tbl.DELETE;
1940 l_language_tbl.DELETE;
1941 l_source_type_tbl.DELETE;
1942 l_purchasing_org_id_tbl.DELETE;
1943 l_po_category_id_tbl.DELETE;
1944 l_supplier_id_tbl.DELETE;
1945 l_supplier_part_num_tbl.DELETE;
1946 l_supplier_part_auxid_tbl.DELETE;
1947 l_supplier_site_id_tbl.DELETE;
1948 l_ip_category_id_tbl.DELETE;
1949 l_ip_category_name_tbl.DELETE;
1950 l_item_revision_tbl.DELETE;
1951 l_po_header_id_tbl.DELETE;
1952 l_document_number_tbl.DELETE;
1953 l_line_num_tbl.DELETE;
1954 l_allow_prc_override_flag_tbl.DELETE;
1955 l_not_to_exceed_price_tbl.DELETE;
1956 l_line_type_id_tbl.DELETE;
1957 l_unit_meas_lookup_code_tbl.DELETE;
1958 l_unit_price_tbl.DELETE;
1959 l_amount_tbl.DELETE;
1960 l_currency_code_tbl.DELETE;
1961 l_rate_type_tbl.DELETE;
1962 l_rate_date_tbl.DELETE;
1963 l_rate_tbl.DELETE;
1964 l_buyer_id_tbl.DELETE;
1965 l_supplier_contact_id_tbl.DELETE;
1966 l_negotiated_preparer_flag_tbl.DELETE;
1967 l_order_type_lookup_code_tbl.DELETE;
1968 l_supplier_tbl.DELETE;
1969 l_global_agreement_flag_tbl.DELETE;
1970 l_quote_status_tbl.DELETE;
1971 l_approved_date_tbl.DELETE;
1972 l_authorization_status_tbl.DELETE;
1973 l_frozen_flag_tbl.DELETE;
1974 l_hdr_cancel_flag_tbl.DELETE;
1975 l_line_cancel_flag_tbl.DELETE;
1976 l_hdr_closed_code_tbl.DELETE;
1977 l_line_closed_code_tbl.DELETE;
1978 l_end_date_tbl.DELETE;
1979 l_expiration_date_tbl.DELETE;
1980 l_system_date_tbl.DELETE;
1981 l_created_by_tbl.DELETE;
1982 l_ctx_inventory_item_id_tbl.DELETE;
1983 l_ctx_source_type_tbl.DELETE;
1984 l_ctx_item_type_tbl.DELETE;
1985 l_ctx_purchasing_org_id_tbl.DELETE;
1986 l_ctx_supplier_id_tbl.DELETE;
1987 l_ctx_supplier_site_id_tbl.DELETE;
1988 l_ctx_supplier_part_num_tbl.DELETE;
1989 l_ctx_supplier_part_auxid_tbl.DELETE;
1990 l_ctx_ip_category_id_tbl.DELETE;
1991 l_ctx_po_category_id_tbl.DELETE;
1992 l_ctx_item_revision_tbl.DELETE;
1993 l_ctx_rowid_tbl.DELETE;
1994
1995 BEGIN
1996 l_err_loc := 200;
1997 FETCH p_podocs_csr BULK COLLECT INTO
1998 l_inv_item_id_tbl,
1999 l_po_line_id_tbl,
2000 l_org_id_tbl,
2001 l_language_tbl,
2002 l_source_type_tbl,
2003 l_purchasing_org_id_tbl,
2004 l_po_category_id_tbl,
2005 l_supplier_id_tbl,
2006 l_supplier_part_num_tbl,
2007 l_supplier_part_auxid_tbl,
2008 l_supplier_site_id_tbl,
2009 l_ip_category_id_tbl,
2010 l_ip_category_name_tbl,
2011 l_item_revision_tbl,
2012 l_po_header_id_tbl,
2013 l_document_number_tbl,
2014 l_line_num_tbl,
2015 l_allow_prc_override_flag_tbl,
2016 l_not_to_exceed_price_tbl,
2017 l_line_type_id_tbl,
2018 l_unit_meas_lookup_code_tbl,
2019 l_unit_price_tbl,
2020 l_amount_tbl,
2021 l_currency_code_tbl,
2022 l_rate_type_tbl,
2023 l_rate_date_tbl,
2024 l_rate_tbl,
2025 l_buyer_id_tbl,
2026 l_supplier_contact_id_tbl,
2027 l_negotiated_preparer_flag_tbl,
2028 l_order_type_lookup_code_tbl,
2029 l_supplier_tbl,
2030 l_global_agreement_flag_tbl,
2031 l_quote_status_tbl,
2032 l_approved_date_tbl,
2033 l_authorization_status_tbl,
2034 l_frozen_flag_tbl,
2035 l_hdr_cancel_flag_tbl,
2036 l_line_cancel_flag_tbl,
2037 l_hdr_closed_code_tbl,
2038 l_line_closed_code_tbl,
2039 l_end_date_tbl,
2040 l_expiration_date_tbl,
2041 l_system_date_tbl,
2042 l_created_by_tbl,
2043 l_ctx_inventory_item_id_tbl,
2044 l_ctx_source_type_tbl,
2045 l_ctx_item_type_tbl,
2046 l_ctx_purchasing_org_id_tbl,
2047 l_ctx_supplier_id_tbl,
2048 l_ctx_supplier_site_id_tbl,
2049 l_ctx_supplier_part_num_tbl,
2050 l_ctx_supplier_part_auxid_tbl,
2051 l_ctx_ip_category_id_tbl,
2052 l_ctx_po_category_id_tbl,
2053 l_ctx_item_revision_tbl,
2054 l_ctx_rowid_tbl
2055 LIMIT ICX_CAT_UTIL_PVT.g_batch_size;
2056
2057 l_err_loc := 300;
2058 EXIT WHEN l_po_line_id_tbl.COUNT = 0;
2059
2060 l_err_loc := 400;
2061 l_batch_count := l_batch_count + 1;
2062
2063 l_err_loc := 500;
2064 l_count := l_po_line_id_tbl.COUNT;
2065 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2066 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
2067 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
2068 'Num. of rows returned from the cursor:' || l_count);
2069 END IF;
2070
2071 l_row_count := l_row_count + l_count;
2072
2073 FOR i in 1..l_po_line_id_tbl.COUNT LOOP
2074 l_err_loc := 600;
2075 IF (l_source_type_tbl(i) = 'BLANKET') THEN
2076 --First get the status of the current BPA line
2077 IF (p_current_mode = ICX_CAT_UTIL_PVT.g_upgrade_const AND
2078 l_created_by_tbl(i) = ICX_CAT_UTIL_PVT.g_upgrade_user)
2079 THEN
2080 l_err_loc := 700;
2081 -- The GBPAs created for bulkload items will not be in approved
2082 -- status during upgrade, so treat them as valid during upgrade.
2083 l_podoc_status := ICX_CAT_POPULATE_STATUS_PVT.VALID_FOR_POPULATE;
2084 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2085 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
2086 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
2087 'bpa status is hard-coded to valid for' ||
2088 ', p_current_mode:' || p_current_mode ||
2089 ', l_created_by_tbl(i):' || l_created_by_tbl(i) );
2090 END IF;
2091 ELSE
2092 l_err_loc := 800;
2093 l_BPA_line_status_rec.approved_date := l_approved_date_tbl(i);
2094 l_BPA_line_status_rec.authorization_status := l_authorization_status_tbl(i);
2095 l_BPA_line_status_rec.frozen_flag := l_frozen_flag_tbl(i);
2096 l_BPA_line_status_rec.hdr_cancel_flag := l_hdr_cancel_flag_tbl(i);
2097 l_BPA_line_status_rec.line_cancel_flag := l_line_cancel_flag_tbl(i);
2098 l_BPA_line_status_rec.hdr_closed_code := l_hdr_closed_code_tbl(i);
2099 l_BPA_line_status_rec.line_closed_code := l_line_closed_code_tbl(i);
2100 l_BPA_line_status_rec.end_date := l_end_date_tbl(i);
2101 l_BPA_line_status_rec.expiration_date := l_expiration_date_tbl(i);
2102 l_BPA_line_status_rec.system_date := l_system_date_tbl(i);
2103
2104 l_err_loc := 900;
2105 l_podoc_status := ICX_CAT_POPULATE_STATUS_PVT.getBPALineStatus(l_BPA_line_status_rec);
2106 END IF;
2107 ELSE
2108 l_podoc_status := l_quote_status_tbl(i);
2109 END IF;
2110
2111 l_err_loc := 1000;
2112 IF (l_ctx_rowid_tbl(i) IS NOT NULL OR
2113 l_podoc_status = ICX_CAT_POPULATE_STATUS_PVT.VALID_FOR_POPULATE)
2114 THEN
2115 l_err_loc := 1100;
2116 l_current_ctx_item_rec.inventory_item_id := l_inv_item_id_tbl(i);
2117 l_current_ctx_item_rec.po_line_id := l_po_line_id_tbl(i);
2118 l_current_ctx_item_rec.req_template_name := TO_CHAR(ICX_CAT_UTIL_PVT.g_NULL_NUMBER);
2119 l_current_ctx_item_rec.req_template_line_num := TO_NUMBER(ICX_CAT_UTIL_PVT.g_NULL_NUMBER);
2120 l_current_ctx_item_rec.org_id := l_org_id_tbl(i);
2121 l_current_ctx_item_rec.language := l_language_tbl(i);
2122 l_current_ctx_item_rec.source_type := l_source_type_tbl(i);
2123 l_current_ctx_item_rec.item_type := ICX_CAT_UTIL_PVT.g_purchase_item_type;
2124 l_current_ctx_item_rec.purchasing_org_id := l_purchasing_org_id_tbl(i);
2125 l_current_ctx_item_rec.owning_org_id := l_org_id_tbl(i);
2126 l_current_ctx_item_rec.po_category_id := l_po_category_id_tbl(i);
2127 l_current_ctx_item_rec.supplier_id := l_supplier_id_tbl(i);
2128 l_current_ctx_item_rec.supplier_part_num := l_supplier_part_num_tbl(i);
2129 l_current_ctx_item_rec.supplier_part_auxid := l_supplier_part_auxid_tbl(i);
2130 l_current_ctx_item_rec.supplier_site_id := l_supplier_site_id_tbl(i);
2131 l_current_ctx_item_rec.status := l_podoc_status;
2132 l_current_ctx_item_rec.ip_category_id := l_ip_category_id_tbl(i);
2133 l_current_ctx_item_rec.ip_category_name := l_ip_category_name_tbl(i);
2134 l_current_ctx_item_rec.req_template_po_line_id := NULL;
2135 l_current_ctx_item_rec.item_revision := l_item_revision_tbl(i);
2136 l_current_ctx_item_rec.po_header_id := l_po_header_id_tbl(i);
2137 l_current_ctx_item_rec.document_number := l_document_number_tbl(i);
2138 l_current_ctx_item_rec.line_num := l_line_num_tbl(i);
2139 l_current_ctx_item_rec.allow_price_override_flag := l_allow_prc_override_flag_tbl(i);
2140 l_current_ctx_item_rec.not_to_exceed_price := l_not_to_exceed_price_tbl(i);
2141 l_current_ctx_item_rec.line_type_id := l_line_type_id_tbl(i);
2142 l_current_ctx_item_rec.unit_meas_lookup_code := l_unit_meas_lookup_code_tbl(i);
2143 l_current_ctx_item_rec.suggested_quantity := NULL;
2144 l_current_ctx_item_rec.unit_price := l_unit_price_tbl(i);
2145 l_current_ctx_item_rec.amount := l_amount_tbl(i);
2146 l_current_ctx_item_rec.currency_code := l_currency_code_tbl(i);
2147 l_current_ctx_item_rec.rate_type := l_rate_type_tbl(i);
2148 l_current_ctx_item_rec.rate_date := l_rate_date_tbl(i);
2149 l_current_ctx_item_rec.rate := l_rate_tbl(i);
2150 l_current_ctx_item_rec.buyer_id := l_buyer_id_tbl(i);
2151 l_current_ctx_item_rec.supplier_contact_id := l_supplier_contact_id_tbl(i);
2152 l_current_ctx_item_rec.rfq_required_flag := 'N';
2153 l_current_ctx_item_rec.negotiated_by_preparer_flag := l_negotiated_preparer_flag_tbl(i);
2154 l_current_ctx_item_rec.description := NULL;
2155 l_current_ctx_item_rec.order_type_lookup_code := l_order_type_lookup_code_tbl(i);
2156 l_current_ctx_item_rec.supplier := l_supplier_tbl(i);
2157 l_current_ctx_item_rec.global_agreement_flag := l_global_agreement_flag_tbl(i);
2158 l_current_ctx_item_rec.merged_source_type := 'SRC_DOC';
2159 l_current_ctx_item_rec.ctx_inventory_item_id := l_ctx_inventory_item_id_tbl(i);
2160 l_current_ctx_item_rec.ctx_source_type := l_ctx_source_type_tbl(i);
2161 l_current_ctx_item_rec.ctx_item_type := l_ctx_item_type_tbl(i);
2162 l_current_ctx_item_rec.ctx_purchasing_org_id := l_ctx_purchasing_org_id_tbl(i);
2163 l_current_ctx_item_rec.ctx_supplier_id := l_ctx_supplier_id_tbl(i);
2164 l_current_ctx_item_rec.ctx_supplier_site_id := l_ctx_supplier_site_id_tbl(i);
2165 l_current_ctx_item_rec.ctx_supplier_part_num := l_ctx_supplier_part_num_tbl(i);
2166 l_current_ctx_item_rec.ctx_supplier_part_auxid := l_ctx_supplier_part_auxid_tbl(i);
2167 l_current_ctx_item_rec.ctx_ip_category_id := l_ctx_ip_category_id_tbl(i);
2168 l_current_ctx_item_rec.ctx_po_category_id := l_ctx_po_category_id_tbl(i);
2169 l_current_ctx_item_rec.ctx_rowid := l_ctx_rowid_tbl(i);
2170
2171 l_err_loc := 1300;
2172 ICX_CAT_POPULATE_ITEM_PVT.processCurrentCtxItemRow(l_current_ctx_item_rec, null, p_current_mode);
2173
2174 l_err_loc := 1400;
2175 ICX_CAT_POPULATE_ITEM_PVT.populateItemCtxTables('INLOOP', ICX_CAT_UTIL_PVT.g_BPACsr_const);
2176 ELSE
2177 IF (p_current_mode = ICX_CAT_UTIL_PVT.g_upgrade_const) THEN
2178 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2179 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
2180 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
2181 'Row #:' || i ||
2182 ', with po_line_id:' || l_po_line_id_tbl(i) ||', not processed' ||
2183 ', l_source_type_tbl:' || l_source_type_tbl(i) ||
2184 ', l_approved_date_tbl:' || l_approved_date_tbl(i) ||
2185 ', l_authorization_status_tbl:' || l_authorization_status_tbl(i) ||
2186 ', l_frozen_flag_tbl:' || l_frozen_flag_tbl(i) ||
2187 ', l_hdr_cancel_flag_tbl:' || l_hdr_cancel_flag_tbl(i) ||
2188 ', l_line_cancel_flag_tbl:' || l_line_cancel_flag_tbl(i) ||
2189 ', l_hdr_closed_code_tbl:' || l_hdr_closed_code_tbl(i) ||
2190 ', l_line_closed_code_tbl:' || l_line_closed_code_tbl(i) ||
2191 ', l_end_date_tbl:' || l_end_date_tbl(i) ||
2192 ', l_expiration_date_tbl:' || l_expiration_date_tbl(i) ||
2193 ', l_system_date_tbl:' || l_system_date_tbl(i) ||
2194 ', status: ' || l_podoc_status);
2195 END IF;
2196 END IF;
2197 END IF;
2198 END LOOP; --FOR LOOP of l_po_line_id_tbl
2199
2200 l_err_loc := 1500;
2201 EXIT WHEN l_po_line_id_tbl.COUNT < ICX_CAT_UTIL_PVT.g_batch_size;
2202 EXCEPTION
2203 WHEN ICX_CAT_UTIL_PVT.g_snap_shot_too_old THEN
2204 l_err_string := ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name) || l_err_loc
2205 ||', Total processed batches:' ||l_batch_count
2206 ||', Cursor will be reopened;';
2207 IF (FND_API.To_Boolean(ICX_CAT_UTIL_PVT.g_COMMIT)) THEN
2208 ICX_CAT_UTIL_PVT.logAndCommitSnapShotTooOld(g_pkg_name, l_api_name, l_err_string);
2209 --Closing and reopen of cursor will be done by called procedures
2210 l_err_loc := 1600;
2211 openR12UpgradeBPAQuoteCursor;
2212 ELSE
2213 RAISE;
2214 END IF;
2215 END;
2216 END LOOP; --Cursor loop
2217
2218 l_err_loc := 1800;
2219 ICX_CAT_POPULATE_ITEM_PVT.populateItemCtxTables('OUTLOOP', ICX_CAT_UTIL_PVT.g_BPACsr_const);
2220
2221 l_err_loc := 1900;
2222 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2223 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
2224 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
2225 'in mode:'|| p_current_mode ||' done; '||
2226 'Total num. of batches processed:' ||l_batch_count ||
2227 ', Total num. of rows processed:' ||l_row_count);
2228 END IF;
2229 EXCEPTION
2230 WHEN OTHERS THEN
2231 ICX_CAT_UTIL_PVT.logUnexpectedException(
2232 G_PKG_NAME, l_api_name,
2233 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
2234 RAISE;
2235 END populateBPAandQuotes;
2236
2237 PROCEDURE upgradeR12PODocs
2238 ( p_upgrade_last_run_date IN DATE ,
2239 p_start_rowid IN ROWID ,
2240 p_end_rowid IN ROWID
2241 )
2242 IS
2243 l_api_name CONSTANT VARCHAR2(30) := 'upgradeR12PODocs';
2244 l_err_loc PLS_INTEGER;
2245 BEGIN
2246 l_err_loc := 100;
2247 g_upgrade_last_run_date := p_upgrade_last_run_date;
2248
2249 l_err_loc := 200;
2250 g_start_rowid := p_start_rowid;
2251 g_end_rowid := p_end_rowid;
2252
2253 l_err_loc := 300;
2254 openR12UpgradeBPAQuoteCursor;
2255
2256 l_err_loc := 400;
2257 openR12UpgradeGBPACursor;
2258
2259 l_err_loc := 500;
2260 EXCEPTION
2261 WHEN OTHERS THEN
2262 ICX_CAT_UTIL_PVT.logUnexpectedException(
2263 G_PKG_NAME, l_api_name,
2264 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
2265 ICX_CAT_UTIL_PVT.g_job_complete_date := NULL;
2266 ICX_CAT_UTIL_PVT.g_job_bpa_complete_date := NULL;
2267 ICX_CAT_UTIL_PVT.g_job_quote_complete_date := NULL;
2268 ICX_CAT_UTIL_PVT.g_job_current_status := ICX_CAT_UTIL_PVT.g_job_failed_status;
2269 RAISE;
2270 END upgradeR12PODocs;
2271
2272 ----------------------------------------------------------------------
2273 -------------- End of BPA and Quote specific Code -------------------
2274 -------------- Begin of Quotation specific Code ---------------------
2275 ----------------------------------------------------------------------
2276
2277 PROCEDURE openQuotesCursor
2278 ( p_key IN NUMBER ,
2279 p_po_line_id IN NUMBER
2280 )
2281 IS
2282 l_api_name CONSTANT VARCHAR2(30) := 'openQuotesCursor';
2283 l_err_loc PLS_INTEGER;
2284 l_quote_csr ICX_CAT_POPULATE_ITEM_PVT.g_item_csr_type;
2285 BEGIN
2286 l_err_loc := 100;
2287 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2288 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
2289 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
2290 'Processing cursor:' || l_api_name ||
2291 ', p_key:' || p_key ||
2292 ', p_po_line_id:' || p_po_line_id );
2293 END IF;
2294
2295 l_err_loc := 150;
2296 --First close the cursor
2297 IF (l_quote_csr%ISOPEN) THEN
2298 l_err_loc := 200;
2299 CLOSE l_quote_csr;
2300 END IF;
2301
2302 l_err_loc := 300;
2303 --Comments on the cursor
2304 --Move the outside operation flag of a line type to the main cursor from the
2305 --status function due to the following reasons:
2306 --1. PO has confirmed that the outside operation flag
2307 -- of a line type cannot be changed once set
2308 --2. The main cursor anyways joins with po_line_types_b
2309 -- to eliminate the TEMP LABOR line
2310 --3. Quote query uses an inline function because we check the existence of a price break at line level
2311 -- if header level approval_required_flag is Y.
2312 OPEN l_quote_csr FOR
2313 SELECT /*+ LEADING(doc) */
2314 doc.*,
2315 ctx.inventory_item_id ctx_inventory_item_id,
2316 ctx.source_type ctx_source_type,
2317 ctx.item_type ctx_item_type,
2318 ctx.purchasing_org_id ctx_purchasing_org_id,
2319 ctx.supplier_id ctx_supplier_id,
2320 ctx.supplier_site_id ctx_supplier_site_id,
2321 ctx.supplier_part_num ctx_supplier_part_num,
2322 ctx.supplier_part_auxid ctx_supplier_part_auxid,
2323 ctx.ip_category_id ctx_ip_category_id,
2324 ctx.po_category_id ctx_po_category_id,
2325 ctx.item_revision ctx_item_revision,
2326 ROWIDTOCHAR(ctx.rowid) ctx_rowid
2327 FROM (
2328 SELECT NVL(pl.item_id, -2) inventory_item_id,
2329 pl.po_line_id po_line_id,
2330 NVL(pl.org_id, -2) org_id,
2331 po_tlp.language language,
2332 ph.type_lookup_code source_type,
2333 NVL(pl.org_id, -2) purchasing_org_id,
2334 pl.category_id po_category_id,
2335 NVL(ph.vendor_id, -2) supplier_id,
2336 NVL(pl.vendor_product_num, '##NULL##') supplier_part_num,
2337 NVL(pl.supplier_part_auxid, '##NULL##') supplier_part_auxid,
2338 NVL(ph.vendor_site_id, -2) supplier_site_id,
2339 ICX_CAT_POPULATE_STATUS_PVT.getQuoteLineStatus(pl.po_line_id) status,
2340 pl.ip_category_id ip_category_id,
2341 ic.category_name ip_category_name,
2342 NVL(pl.item_revision, '-2') item_revision,
2343 ph.po_header_id,
2344 ph.segment1 document_number,
2345 pl.line_num,
2346 UPPER(NVL(pl.allow_price_override_flag, 'N')) allow_price_override_flag,
2347 pl.not_to_exceed_price,
2348 pl.line_type_id,
2349 pl.unit_meas_lookup_code,
2350 DECODE(pltb.order_type_lookup_code, 'QUANTITY', pl.unit_price, to_number(null)) unit_price,
2351 DECODE(pltb.order_type_lookup_code, 'QUANTITY', to_number(null), pl.amount) amount,
2352 ph.currency_code,
2353 ph.rate_type,
2354 ph.rate_date,
2355 ph.rate,
2356 ph.agent_id buyer_id,
2357 ph.vendor_contact_id supplier_contact_id,
2358 pltb.order_type_lookup_code,
2359 pv.vendor_name supplier
2360 FROM po_headers_all ph,
2361 po_lines_all pl,
2362 po_session_gt pogt,
2363 po_attribute_values_tlp po_tlp,
2364 po_line_types_b pltb,
2365 icx_cat_categories_tl ic,
2366 po_vendors pv
2367 WHERE ph.po_header_id = pl.po_header_id
2368 AND ph.type_lookup_code = 'QUOTATION'
2369 AND pogt.key = p_key
2370 AND pl.po_line_id = pogt.index_num1
2371 AND pl.po_line_id = po_tlp.po_line_id
2372 AND pl.line_type_id = pltb.line_type_id
2373 AND NVL(pltb.purchase_basis, 'NULL') <> 'TEMP LABOR'
2374 AND NVL(pltb.outside_operation_flag, 'N') = 'N'
2375 AND po_tlp.ip_category_id = ic.rt_category_id (+)
2376 AND po_tlp.language = ic.language (+)
2377 AND ph.vendor_id = pv.vendor_id(+)
2378 AND pl.po_line_id >= p_po_line_id
2379 ) doc,
2380 icx_cat_items_ctx_hdrs_tlp ctx
2381 WHERE doc.po_line_id = ctx.po_line_id (+)
2382 AND doc.org_id = ctx.org_id (+)
2383 AND doc.source_type = ctx.source_type (+)
2384 AND doc.language = ctx.language (+)
2385 -- AND (ctx.rowid IS NOT NULL OR doc.status = 0)
2386 ORDER BY doc.po_line_id;
2387
2388 l_err_loc := 500;
2389 populateQuotes(l_quote_csr, ICX_CAT_UTIL_PVT.g_online_const);
2390
2391 l_err_loc := 600;
2392 CLOSE l_quote_csr;
2393 EXCEPTION
2394 WHEN OTHERS THEN
2395 ICX_CAT_UTIL_PVT.logUnexpectedException(
2396 G_PKG_NAME, l_api_name,
2397 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
2398 RAISE;
2399 END openQuotesCursor;
2400
2401 -- l_quote_csr, ICX_CAT_UTIL_PVT.g_upgrade_const
2402 PROCEDURE populateQuotes
2403 ( p_podocs_csr IN ICX_CAT_POPULATE_ITEM_PVT.g_item_csr_type ,
2404 p_current_mode IN VARCHAR2
2405 )
2406 IS
2407 l_api_name CONSTANT VARCHAR2(30) := 'populateQuotes';
2408 l_err_loc PLS_INTEGER;
2409 l_start_po_line_id NUMBER;
2410 l_err_string VARCHAR2(4000);
2411 l_batch_count PLS_INTEGER;
2412 l_row_count PLS_INTEGER;
2413 l_count PLS_INTEGER;
2414 l_GBPA_line_status PLS_INTEGER;
2415 l_current_ctx_item_rec ICX_CAT_POPULATE_ITEM_PVT.g_ctx_item_rec_type;
2416
2417 ----- Start of declaring columns selected in the cursor -----
2418 l_inv_item_id_tbl DBMS_SQL.NUMBER_TABLE;
2419 l_po_line_id_tbl DBMS_SQL.NUMBER_TABLE;
2420 l_org_id_tbl DBMS_SQL.NUMBER_TABLE;
2421 l_language_tbl DBMS_SQL.VARCHAR2_TABLE;
2422 l_source_type_tbl DBMS_SQL.VARCHAR2_TABLE;
2423 l_purchasing_org_id_tbl DBMS_SQL.NUMBER_TABLE;
2424 l_po_category_id_tbl DBMS_SQL.NUMBER_TABLE;
2425 l_supplier_id_tbl DBMS_SQL.NUMBER_TABLE;
2426 l_supplier_part_num_tbl DBMS_SQL.VARCHAR2_TABLE;
2427 l_supplier_part_auxid_tbl DBMS_SQL.VARCHAR2_TABLE;
2428 l_supplier_site_id_tbl DBMS_SQL.NUMBER_TABLE;
2429 l_status_tbl DBMS_SQL.VARCHAR2_TABLE;
2430 l_ip_category_id_tbl DBMS_SQL.NUMBER_TABLE;
2431 l_ip_category_name_tbl DBMS_SQL.VARCHAR2_TABLE;
2432 l_item_revision_tbl DBMS_SQL.VARCHAR2_TABLE;
2433 l_po_header_id_tbl DBMS_SQL.NUMBER_TABLE;
2434 l_document_number_tbl DBMS_SQL.VARCHAR2_TABLE;
2435 l_line_num_tbl DBMS_SQL.NUMBER_TABLE;
2436 l_allow_prc_override_flag_tbl DBMS_SQL.VARCHAR2_TABLE;
2437 l_not_to_exceed_price_tbl DBMS_SQL.NUMBER_TABLE;
2438 l_line_type_id_tbl DBMS_SQL.NUMBER_TABLE;
2439 l_unit_meas_lookup_code_tbl DBMS_SQL.VARCHAR2_TABLE;
2440 l_unit_price_tbl DBMS_SQL.NUMBER_TABLE;
2441 l_amount_tbl DBMS_SQL.NUMBER_TABLE;
2442 l_currency_code_tbl DBMS_SQL.VARCHAR2_TABLE;
2443 l_rate_type_tbl DBMS_SQL.VARCHAR2_TABLE;
2444 l_rate_date_tbl DBMS_SQL.DATE_TABLE;
2445 l_rate_tbl DBMS_SQL.NUMBER_TABLE;
2446 l_buyer_id_tbl DBMS_SQL.NUMBER_TABLE;
2447 l_supplier_contact_id_tbl DBMS_SQL.NUMBER_TABLE;
2448 l_order_type_lookup_code_tbl DBMS_SQL.VARCHAR2_TABLE;
2449 l_supplier_tbl DBMS_SQL.VARCHAR2_TABLE;
2450 l_ctx_inventory_item_id_tbl DBMS_SQL.NUMBER_TABLE;
2451 l_ctx_source_type_tbl DBMS_SQL.VARCHAR2_TABLE;
2452 l_ctx_item_type_tbl DBMS_SQL.VARCHAR2_TABLE;
2453 l_ctx_purchasing_org_id_tbl DBMS_SQL.NUMBER_TABLE;
2454 l_ctx_supplier_id_tbl DBMS_SQL.NUMBER_TABLE;
2455 l_ctx_supplier_site_id_tbl DBMS_SQL.NUMBER_TABLE;
2456 l_ctx_supplier_part_num_tbl DBMS_SQL.VARCHAR2_TABLE;
2457 l_ctx_supplier_part_auxid_tbl DBMS_SQL.VARCHAR2_TABLE;
2458 l_ctx_ip_category_id_tbl DBMS_SQL.NUMBER_TABLE;
2459 l_ctx_po_category_id_tbl DBMS_SQL.NUMBER_TABLE;
2460 l_ctx_item_revision_tbl DBMS_SQL.VARCHAR2_TABLE;
2461 l_ctx_rowid_tbl DBMS_SQL.UROWID_TABLE;
2462 ------ End of declaring columns selected in the cursor ------
2463
2464 BEGIN
2465 l_err_loc := 100;
2466 l_batch_count := 0;
2467 l_row_count := 0;
2468 l_count := 0;
2469 LOOP
2470 l_err_loc := 200;
2471 l_inv_item_id_tbl.DELETE;
2472 l_po_line_id_tbl.DELETE;
2473 l_org_id_tbl.DELETE;
2474 l_language_tbl.DELETE;
2475 l_source_type_tbl.DELETE;
2476 l_purchasing_org_id_tbl.DELETE;
2477 l_po_category_id_tbl.DELETE;
2478 l_supplier_id_tbl.DELETE;
2479 l_supplier_part_num_tbl.DELETE;
2480 l_supplier_part_auxid_tbl.DELETE;
2481 l_supplier_site_id_tbl.DELETE;
2482 l_status_tbl.DELETE;
2483 l_ip_category_id_tbl.DELETE;
2484 l_ip_category_name_tbl.DELETE;
2485 l_item_revision_tbl.DELETE;
2486 l_po_header_id_tbl.DELETE;
2487 l_document_number_tbl.DELETE;
2488 l_line_num_tbl.DELETE;
2489 l_allow_prc_override_flag_tbl.DELETE;
2490 l_not_to_exceed_price_tbl.DELETE;
2491 l_line_type_id_tbl.DELETE;
2492 l_unit_meas_lookup_code_tbl.DELETE;
2493 l_unit_price_tbl.DELETE;
2494 l_amount_tbl.DELETE;
2495 l_currency_code_tbl.DELETE;
2496 l_rate_type_tbl.DELETE;
2497 l_rate_date_tbl.DELETE;
2498 l_rate_tbl.DELETE;
2499 l_buyer_id_tbl.DELETE;
2500 l_supplier_contact_id_tbl.DELETE;
2501 l_order_type_lookup_code_tbl.DELETE;
2502 l_supplier_tbl.DELETE;
2503 l_ctx_inventory_item_id_tbl.DELETE;
2504 l_ctx_source_type_tbl.DELETE;
2505 l_ctx_item_type_tbl.DELETE;
2506 l_ctx_purchasing_org_id_tbl.DELETE;
2507 l_ctx_supplier_id_tbl.DELETE;
2508 l_ctx_supplier_site_id_tbl.DELETE;
2509 l_ctx_supplier_part_num_tbl.DELETE;
2510 l_ctx_supplier_part_auxid_tbl.DELETE;
2511 l_ctx_ip_category_id_tbl.DELETE;
2512 l_ctx_po_category_id_tbl.DELETE;
2513 l_ctx_item_revision_tbl.DELETE;
2514 l_ctx_rowid_tbl.DELETE;
2515
2516 BEGIN
2517 l_err_loc := 300;
2518 FETCH p_podocs_csr BULK COLLECT INTO
2519 l_inv_item_id_tbl,
2520 l_po_line_id_tbl,
2521 l_org_id_tbl,
2522 l_language_tbl,
2523 l_source_type_tbl,
2524 l_purchasing_org_id_tbl,
2525 l_po_category_id_tbl,
2526 l_supplier_id_tbl,
2527 l_supplier_part_num_tbl,
2528 l_supplier_part_auxid_tbl,
2529 l_supplier_site_id_tbl,
2530 l_status_tbl,
2531 l_ip_category_id_tbl,
2532 l_ip_category_name_tbl,
2533 l_item_revision_tbl,
2534 l_po_header_id_tbl,
2535 l_document_number_tbl,
2536 l_line_num_tbl,
2537 l_allow_prc_override_flag_tbl,
2538 l_not_to_exceed_price_tbl,
2539 l_line_type_id_tbl,
2540 l_unit_meas_lookup_code_tbl,
2541 l_unit_price_tbl,
2542 l_amount_tbl,
2543 l_currency_code_tbl,
2544 l_rate_type_tbl,
2545 l_rate_date_tbl,
2546 l_rate_tbl,
2547 l_buyer_id_tbl,
2548 l_supplier_contact_id_tbl,
2549 l_order_type_lookup_code_tbl,
2550 l_supplier_tbl,
2551 l_ctx_inventory_item_id_tbl,
2552 l_ctx_source_type_tbl,
2553 l_ctx_item_type_tbl,
2554 l_ctx_purchasing_org_id_tbl,
2555 l_ctx_supplier_id_tbl,
2556 l_ctx_supplier_site_id_tbl,
2557 l_ctx_supplier_part_num_tbl,
2558 l_ctx_supplier_part_auxid_tbl,
2559 l_ctx_ip_category_id_tbl,
2560 l_ctx_po_category_id_tbl,
2561 l_ctx_item_revision_tbl,
2562 l_ctx_rowid_tbl
2563 LIMIT ICX_CAT_UTIL_PVT.g_batch_size;
2564 l_err_loc := 400;
2565
2566 EXIT WHEN l_po_line_id_tbl.COUNT = 0;
2567
2568 l_err_loc := 500;
2569 l_batch_count := l_batch_count + 1;
2570
2571 l_err_loc := 600;
2572 l_count := l_po_line_id_tbl.COUNT;
2573 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2574 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
2575 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
2576 'Num. of rows returned from the cursor:' || l_count);
2577 END IF;
2578
2579 --Save the last po_line_id processed, so that re-open of cursor will start from the saved id.
2580 l_start_po_line_id := l_po_line_id_tbl(l_count);
2581
2582 l_row_count := l_row_count + l_count;
2583
2584 FOR i in 1..l_po_line_id_tbl.COUNT LOOP
2585 l_err_loc := 700;
2586 IF (l_ctx_rowid_tbl(i) IS NOT NULL OR
2587 l_status_tbl(i) = ICX_CAT_POPULATE_STATUS_PVT.VALID_FOR_POPULATE)
2588 THEN
2589 l_err_loc := 800;
2590 l_current_ctx_item_rec.inventory_item_id := l_inv_item_id_tbl(i);
2591 l_current_ctx_item_rec.po_line_id := l_po_line_id_tbl(i);
2592 l_current_ctx_item_rec.req_template_name := TO_CHAR(ICX_CAT_UTIL_PVT.g_NULL_NUMBER);
2593 l_current_ctx_item_rec.req_template_line_num := TO_NUMBER(ICX_CAT_UTIL_PVT.g_NULL_NUMBER);
2594 l_current_ctx_item_rec.org_id := l_org_id_tbl(i);
2595 l_current_ctx_item_rec.language := l_language_tbl(i);
2596 l_current_ctx_item_rec.source_type := l_source_type_tbl(i);
2597 l_current_ctx_item_rec.item_type := ICX_CAT_UTIL_PVT.g_purchase_item_type;
2598 l_current_ctx_item_rec.purchasing_org_id := l_purchasing_org_id_tbl(i);
2599 l_current_ctx_item_rec.owning_org_id := l_org_id_tbl(i);
2600 l_current_ctx_item_rec.po_category_id := l_po_category_id_tbl(i);
2601 l_current_ctx_item_rec.supplier_id := l_supplier_id_tbl(i);
2602 l_current_ctx_item_rec.supplier_part_num := l_supplier_part_num_tbl(i);
2603 l_current_ctx_item_rec.supplier_part_auxid := l_supplier_part_auxid_tbl(i);
2604 l_current_ctx_item_rec.supplier_site_id := l_supplier_site_id_tbl(i);
2605 l_current_ctx_item_rec.status := l_status_tbl(i);
2606 l_current_ctx_item_rec.ip_category_id := l_ip_category_id_tbl(i);
2607 l_current_ctx_item_rec.ip_category_name := l_ip_category_name_tbl(i);
2608 l_current_ctx_item_rec.req_template_po_line_id := NULL;
2609 l_current_ctx_item_rec.item_revision := l_item_revision_tbl(i);
2610 l_current_ctx_item_rec.po_header_id := l_po_header_id_tbl(i);
2611 l_current_ctx_item_rec.document_number := l_document_number_tbl(i);
2612 l_current_ctx_item_rec.line_num := l_line_num_tbl(i);
2613 l_current_ctx_item_rec.allow_price_override_flag := l_allow_prc_override_flag_tbl(i);
2614 l_current_ctx_item_rec.not_to_exceed_price := l_not_to_exceed_price_tbl(i);
2615 l_current_ctx_item_rec.line_type_id := l_line_type_id_tbl(i);
2616 l_current_ctx_item_rec.unit_meas_lookup_code := l_unit_meas_lookup_code_tbl(i);
2617 l_current_ctx_item_rec.suggested_quantity := NULL;
2618 l_current_ctx_item_rec.unit_price := l_unit_price_tbl(i);
2619 l_current_ctx_item_rec.amount := l_amount_tbl(i);
2620 l_current_ctx_item_rec.currency_code := l_currency_code_tbl(i);
2621 l_current_ctx_item_rec.rate_type := l_rate_type_tbl(i);
2622 l_current_ctx_item_rec.rate_date := l_rate_date_tbl(i);
2623 l_current_ctx_item_rec.rate := l_rate_tbl(i);
2624 l_current_ctx_item_rec.buyer_id := l_buyer_id_tbl(i);
2625 l_current_ctx_item_rec.supplier_contact_id := l_supplier_contact_id_tbl(i);
2626 l_current_ctx_item_rec.rfq_required_flag := 'N';
2627 l_current_ctx_item_rec.negotiated_by_preparer_flag := 'Y';
2628 l_current_ctx_item_rec.description := NULL;
2629 l_current_ctx_item_rec.order_type_lookup_code := l_order_type_lookup_code_tbl(i);
2630 l_current_ctx_item_rec.supplier := l_supplier_tbl(i);
2631 l_current_ctx_item_rec.global_agreement_flag := 'N';
2632 l_current_ctx_item_rec.merged_source_type := 'SRC_DOC';
2633 l_current_ctx_item_rec.ctx_inventory_item_id := l_ctx_inventory_item_id_tbl(i);
2634 l_current_ctx_item_rec.ctx_source_type := l_ctx_source_type_tbl(i);
2635 l_current_ctx_item_rec.ctx_item_type := l_ctx_item_type_tbl(i);
2636 l_current_ctx_item_rec.ctx_purchasing_org_id := l_ctx_purchasing_org_id_tbl(i);
2637 l_current_ctx_item_rec.ctx_supplier_id := l_ctx_supplier_id_tbl(i);
2638 l_current_ctx_item_rec.ctx_supplier_site_id := l_ctx_supplier_site_id_tbl(i);
2639 l_current_ctx_item_rec.ctx_supplier_part_num := l_ctx_supplier_part_num_tbl(i);
2640 l_current_ctx_item_rec.ctx_supplier_part_auxid := l_ctx_supplier_part_auxid_tbl(i);
2641 l_current_ctx_item_rec.ctx_ip_category_id := l_ctx_ip_category_id_tbl(i);
2642 l_current_ctx_item_rec.ctx_po_category_id := l_ctx_po_category_id_tbl(i);
2643 l_current_ctx_item_rec.ctx_item_revision := l_ctx_item_revision_tbl(i);
2644 l_current_ctx_item_rec.ctx_rowid := l_ctx_rowid_tbl(i);
2645
2646 l_err_loc := 900;
2647 ICX_CAT_POPULATE_ITEM_PVT.processCurrentCtxItemRow(l_current_ctx_item_rec, null, p_current_mode);
2648
2649 l_err_loc := 1000;
2650 ICX_CAT_POPULATE_ITEM_PVT.populateItemCtxTables('INLOOP', ICX_CAT_UTIL_PVT.g_QuoteCsr_const);
2651 ELSE
2652 l_err_loc := 1100;
2653 IF (p_current_mode = ICX_CAT_UTIL_PVT.g_upgrade_const) THEN
2654 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2655 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
2656 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
2657 'Row #:' || i ||
2658 ', with po_line_id:' || l_po_line_id_tbl(i) ||', not processed' ||
2659 ', status: ' || l_status_tbl(i));
2660 END IF;
2661 ELSE
2662 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2663 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
2664 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
2665 'Row #:' || i ||
2666 ', with po_line_id:' || l_po_line_id_tbl(i) ||', not processed' ||
2667 ', status: ' || l_status_tbl(i));
2668 END IF;
2669 END IF;
2670 END IF;
2671 END LOOP; --FOR LOOP of l_po_line_id_tbl
2672
2673 l_err_loc := 1200;
2674 EXIT WHEN l_po_line_id_tbl.COUNT < ICX_CAT_UTIL_PVT.g_batch_size;
2675 EXCEPTION
2676 WHEN ICX_CAT_UTIL_PVT.g_snap_shot_too_old THEN
2677 l_err_string := 'ICX_CAT_POPULATE_PODOCS_PVT.populateQuotes' ||l_err_loc
2678 ||', Total processed batches:' ||l_batch_count
2679 ||', Cursor will be reopened with po_line_id:' ||l_start_po_line_id;
2680 IF (FND_API.To_Boolean(ICX_CAT_UTIL_PVT.g_COMMIT)) THEN
2681 ICX_CAT_UTIL_PVT.logAndCommitSnapShotTooOld(g_pkg_name, l_api_name, l_err_string);
2682 --Closing and reopen of cursor will be done by called procedures
2683 openQuotesCursor(g_key, l_start_po_line_id);
2684 ELSE
2685 RAISE;
2686 END IF;
2687 END;
2688 END LOOP; --Cursor loop
2689
2690 l_err_loc := 1500;
2691 ICX_CAT_POPULATE_ITEM_PVT.populateItemCtxTables('OUTLOOP', ICX_CAT_UTIL_PVT.g_QuoteCsr_const);
2692
2693 l_err_loc := 1600;
2694 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2695 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
2696 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
2697 'populateQuotes in mode:'|| p_current_mode ||' done; '||
2698 'Total num. of batches processed:' ||l_batch_count ||
2699 ', Total num. of rows processed:' ||l_row_count);
2700 END IF;
2701 EXCEPTION
2702 WHEN OTHERS THEN
2703 ICX_CAT_UTIL_PVT.logUnexpectedException(
2704 G_PKG_NAME, l_api_name,
2705 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
2706 RAISE;
2707 END populateQuotes;
2708
2709 PROCEDURE populateOnlineQuotes
2710 ( p_key IN NUMBER
2711 )
2712 IS
2713 l_api_name CONSTANT VARCHAR2(30) := 'populateOnlineQuotes';
2714 l_err_loc PLS_INTEGER;
2715 BEGIN
2716 l_err_loc := 100;
2717 -- Set the batch_size
2718 ICX_CAT_UTIL_PVT.setBatchSize;
2719
2720 l_err_loc := 200;
2721 -- Set the who columns
2722 ICX_CAT_UTIL_PVT.setWhoColumns(null);
2723
2724 l_err_loc := 300;
2725 g_key := p_key;
2726
2727 l_err_loc := 350;
2728 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2729 ICX_CAT_UTIL_PVT.logPOSessionGTData(p_key);
2730 END IF;
2731
2732 l_err_loc := 400;
2733 openQuotesCursor(p_key, 0);
2734
2735 l_err_loc := 500;
2736 g_metadataTblFormed := FALSE;
2737 g_CtxSqlForPODocsFormed := FALSE;
2738
2739 l_err_loc := 600;
2740 ICX_CAT_POPULATE_CTXSTRING_PVT.populateCtxCatgAtt(ICX_CAT_UTIL_PVT.g_who_columns_rec.internal_request_id);
2741
2742 l_err_loc := 700;
2743 EXCEPTION
2744 WHEN OTHERS THEN
2745 ICX_CAT_UTIL_PVT.logUnexpectedException(
2746 G_PKG_NAME, l_api_name,
2747 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
2748 RAISE;
2749 END populateOnlineQuotes;
2750
2751 ----------------------------------------------------------------------
2752 -------------- End of Quotation specific Code -----------------------
2753 ----------------------------------------------------------------------
2754
2755 PROCEDURE buildCtxSqlForPODocs
2756 ( p_special_ctx_sql_tbl IN OUT NOCOPY ICX_CAT_BUILD_CTX_SQL_PVT.g_ctx_sql_tbl_type ,
2757 p_regular_ctx_sql_tbl IN OUT NOCOPY ICX_CAT_BUILD_CTX_SQL_PVT.g_ctx_sql_tbl_type
2758 )
2759 IS
2760 l_api_name CONSTANT VARCHAR2(30) := 'buildCtxSqlForPODocs';
2761 l_err_loc PLS_INTEGER;
2762 BEGIN
2763 l_err_loc := 100;
2764 IF (NOT ICX_CAT_POPULATE_PODOCS_PVT.g_metadataTblFormed) THEN
2765 l_err_loc := 200;
2766 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2767 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
2768 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
2769 'about to call buildmetadatinfo');
2770 END IF;
2771
2772 l_err_loc := 200;
2773 ICX_CAT_BUILD_CTX_SQL_PVT.buildMetadataInfo
2774 (0, g_special_metadata_tbl, g_regular_nontl_metadata_tbl, g_regular_tl_metadata_tbl);
2775
2776 l_err_loc := 300;
2777 ICX_CAT_POPULATE_PODOCS_PVT.g_metadataTblFormed := TRUE;
2778 END IF;
2779
2780 l_err_loc := 400;
2781 IF (NOT ICX_CAT_POPULATE_PODOCS_PVT.g_CtxSqlForPODocsFormed) THEN
2782 l_err_loc := 500;
2783 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2784 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
2785 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
2786 'about to call buildctxsql');
2787 END IF;
2788
2789 l_err_loc := 600;
2790 ICX_CAT_BUILD_CTX_SQL_PVT.buildCtxSql
2791 (0, ICX_CAT_UTIL_PVT.g_PODoc_const, 'NOTROWID', g_special_metadata_tbl,
2792 g_regular_nontl_metadata_tbl, g_regular_tl_metadata_tbl,
2793 g_all_ctx_sql_tbl, g_special_ctx_sql_tbl, g_regular_ctx_sql_tbl);
2794 ICX_CAT_POPULATE_PODOCS_PVT.g_CtxSqlForPODocsFormed := TRUE;
2795 END IF;
2796
2797 l_err_loc := 700;
2798 p_special_ctx_sql_tbl := g_special_ctx_sql_tbl;
2799 p_regular_ctx_sql_tbl := g_regular_ctx_sql_tbl;
2800
2801 l_err_loc := 800;
2802
2803 EXCEPTION
2804 WHEN OTHERS THEN
2805 ICX_CAT_UTIL_PVT.logUnexpectedException(
2806 G_PKG_NAME, l_api_name,
2807 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
2808 RAISE;
2809 END buildCtxSqlForPODocs;
2810
2811
2812 END ICX_CAT_POPULATE_PODOCS_PVT;