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