[Home] [Help]
PACKAGE BODY: APPS.ICX_CAT_POPULATE_REQTMPL_PVT
Source
1 PACKAGE BODY ICX_CAT_POPULATE_REQTMPL_PVT AS
2 /* $Header: ICXVPPRB.pls 120.10 2006/10/06 02:43:55 rwidjaja noship $*/
3
4 -- Constants
5 G_PKG_NAME CONSTANT VARCHAR2(30) :='ICX_CAT_POPULATE_REQTMPL_PVT';
6 g_upgrade_last_run_date DATE;
7 g_key NUMBER;
8 g_start_rowid ROWID;
9 g_end_rowid ROWID;
10
11 PROCEDURE openR12UpgradeReqTmpltsCursor
12 IS
13 l_api_name CONSTANT VARCHAR2(30) := 'openR12UpgradeReqTmpltsCursor';
14 l_err_loc PLS_INTEGER;
15 l_reqTemplate_csr ICX_CAT_POPULATE_ITEM_PVT.g_item_csr_type;
16 BEGIN
17 l_err_loc := 100;
18 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
19 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
20 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
21 'Processing cursor:' || l_api_name ||
22 ', g_upgrade_last_run_date:' || g_upgrade_last_run_date ||
23 ', g_start_rowid:' || g_start_rowid ||
24 ', g_end_rowid:' || g_end_rowid );
25 END IF;
26
27 l_err_loc := 150;
28 --First close the cursor
29 IF (l_reqTemplate_csr%ISOPEN) THEN
30 l_err_loc := 200;
31 CLOSE l_reqTemplate_csr;
32 END IF;
33
34 --options: 1. Do a not exists (only work the first time and not during delta)
35 --2. (Have a internal_request_id check in ctx_hdrs) Will work for both first time and delta. Current extractor process.
36 --Changes would be to add the internal_request_id clause only after seeing the snaphot too old error.
37
38 l_err_loc := 300;
39 IF (g_upgrade_last_run_date) IS NULL THEN
40 l_err_loc := 400;
41 OPEN l_reqTemplate_csr FOR
42 SELECT /*+ LEADING(doc) use_nl(ctx) */
43 doc.*,
44 ctx.inventory_item_id ctx_inventory_item_id,
45 ctx.source_type ctx_source_type,
46 ctx.item_type ctx_item_type,
47 ctx.purchasing_org_id ctx_purchasing_org_id,
48 ctx.supplier_id ctx_supplier_id,
49 ctx.supplier_site_id ctx_supplier_site_id,
50 ctx.supplier_part_num ctx_supplier_part_num,
51 ctx.supplier_part_auxid ctx_supplier_part_auxid,
52 ctx.ip_category_id ctx_ip_category_id,
53 ctx.po_category_id ctx_po_category_id,
54 ctx.item_revision ctx_item_revision,
55 ROWIDTOCHAR(ctx.rowid) ctx_rowid
56 FROM
57 (
58 SELECT /*+ ROWID(prl) use_nl(prh,ph,pl,po_tlp,ic) */
59 NVL(prl.item_id, -2) inventory_item_id,
60 prl.express_name req_template_name,
61 prl.sequence_num req_template_line_num,
62 NVL(prl.org_id, -2) org_id,
63 po_tlp.language language,
64 DECODE(prl.source_type_code, 'VENDOR', 'TEMPLATE', 'INTERNAL_TEMPLATE') source_type,
65 DECODE(prl.source_type_code, 'VENDOR', 'PURCHASE', 'INTERNAL') item_type,
66 NVL(prl.org_id, -2) purchasing_org_id,
67 prl.category_id po_category_id,
68 NVL(prl.suggested_vendor_id, -2) supplier_id,
69 NVL(prl.suggested_vendor_product_code, '##NULL##') supplier_part_num,
70 NVL(prl.suggested_vendor_site_id, -2) supplier_site_id,
71 prl.ip_category_id,
72 ic.category_name ip_category_name,
73 -- For template line status
74 prh.inactive_date,
75 --For blanket line status
76 prl.po_line_id,
77 prl.po_line_id req_template_po_line_id,
78 NVL(prl.item_revision, '-2'),
79 prl.po_header_id,
80 ph.segment1 document_number,
81 pl.line_num,
82 prl.line_type_id,
83 prl.unit_meas_lookup_code,
84 prl.suggested_quantity,
85 DECODE(pltb.order_type_lookup_code, 'QUANTITY', prl.unit_price, to_number(null)) unit_price,
86 DECODE(pltb.order_type_lookup_code, 'QUANTITY', to_number(null), prl.amount) amount,
87 ph.currency_code,
88 ph.rate_type,
89 ph.rate_date,
90 ph.rate rate,
91 prl.suggested_buyer_id buyer_id,
92 prl.suggested_vendor_contact_id supplier_contact_id,
93 prl.rfq_required_flag,
94 NVL(prl.negotiated_by_preparer_flag, 'N') negotiated_by_preparer_flag,
95 pltb.order_type_lookup_code,
96 pv.vendor_name supplier,
97 ph.global_agreement_flag,
98 ph.approved_date,
99 NVL(ph.authorization_status, 'INCOMPLETE') authorization_status,
100 NVL(ph.frozen_flag, 'N') frozen_flag,
101 NVL(ph.cancel_flag, 'N') hdr_cancel_flag,
102 NVL(pl.cancel_flag, 'N') line_cancel_flag,
103 NVL(ph.closed_code, 'OPEN') hdr_closed_code,
104 NVL(pl.closed_code, 'OPEN') line_closed_code,
105 NVL(TRUNC(ph.end_date), TRUNC(SYSDATE + 1)) end_date,
106 NVL(TRUNC(pl.expiration_date), TRUNC(SYSDATE+1)) expiration_date,
107 TRUNC(SYSDATE) system_date
108 FROM po_reqexpress_headers_all prh,
109 po_reqexpress_lines_all prl,
110 po_headers_all ph,
111 po_lines_all pl,
112 po_attribute_values_tlp po_tlp,
113 po_line_types_b pltb,
114 icx_cat_categories_tl ic,
115 po_vendors pv
116 WHERE prl.express_name = prh.express_name
117 AND prl.org_id = prh.org_id
118 AND prl.po_line_id = pl.po_line_id (+)
119 AND prl.po_header_id = pl.po_header_id (+)
120 AND prl.po_header_id = ph.po_header_id (+)
121 AND -2 = po_tlp.po_line_id
122 AND prl.express_name = po_tlp.req_template_name
123 AND prl.sequence_num = po_tlp.req_template_line_num
124 AND prl.org_id = po_tlp.org_id
125 AND prl.line_type_id = pltb.line_type_id
126 AND NVL(pltb.purchase_basis, 'NULL') <> 'TEMP LABOR'
127 AND po_tlp.ip_category_id = ic.rt_category_id (+)
128 AND po_tlp.language = ic.language (+)
129 AND prl.suggested_vendor_id = pv.vendor_id(+)
130 AND prl.rowid BETWEEN g_start_rowid and g_end_rowid
131 ) doc,
132 icx_cat_items_ctx_hdrs_tlp ctx
133 WHERE -2 = ctx.po_line_id (+)
134 AND doc.req_template_name = ctx.req_template_name (+)
135 AND doc.req_template_line_num = ctx.req_template_line_num (+)
136 AND doc.source_type = ctx.source_type (+)
137 AND doc.org_id = ctx.org_id (+)
138 AND doc.language = ctx.language (+);
139 ELSE
140 l_err_loc := 500;
141 OPEN l_reqTemplate_csr FOR
142 SELECT /*+ LEADING(doc) use_nl(ctx) */
143 doc.*,
144 ctx.inventory_item_id ctx_inventory_item_id,
145 ctx.source_type ctx_source_type,
146 ctx.item_type ctx_item_type,
147 ctx.purchasing_org_id ctx_purchasing_org_id,
148 ctx.supplier_id ctx_supplier_id,
149 ctx.supplier_site_id ctx_supplier_site_id,
150 ctx.supplier_part_num ctx_supplier_part_num,
151 ctx.supplier_part_auxid ctx_supplier_part_auxid,
152 ctx.ip_category_id ctx_ip_category_id,
153 ctx.po_category_id ctx_po_category_id,
154 ctx.item_revision ctx_item_revision,
155 ROWIDTOCHAR(ctx.rowid) ctx_rowid
156 FROM
157 (
158 SELECT /*+ ROWID(prl) use_nl(prh,ph,pl,po_tlp,ic) */
159 NVL(prl.item_id, -2) inventory_item_id,
160 prl.express_name req_template_name,
161 prl.sequence_num req_template_line_num,
162 NVL(prl.org_id, -2) org_id,
163 po_tlp.language language,
164 DECODE(prl.source_type_code, 'VENDOR', 'TEMPLATE', 'INTERNAL_TEMPLATE') source_type,
165 DECODE(prl.source_type_code, 'VENDOR', 'PURCHASE', 'INTERNAL') item_type,
166 NVL(prl.org_id, -2) purchasing_org_id,
167 prl.category_id po_category_id,
168 NVL(prl.suggested_vendor_id, -2) supplier_id,
169 NVL(prl.suggested_vendor_product_code, '##NULL##') supplier_part_num,
170 NVL(prl.suggested_vendor_site_id, -2) supplier_site_id,
171 prl.ip_category_id,
172 ic.category_name ip_category_name,
173 -- For template line status
174 prh.inactive_date,
175 --For blanket line status
176 prl.po_line_id,
177 prl.po_line_id req_template_po_line_id,
178 NVL(prl.item_revision, '-2'),
179 prl.po_header_id,
180 ph.segment1 document_number,
181 pl.line_num,
182 prl.line_type_id,
183 prl.unit_meas_lookup_code,
184 prl.suggested_quantity,
185 DECODE(pltb.order_type_lookup_code, 'QUANTITY', prl.unit_price, to_number(null)) unit_price,
186 DECODE(pltb.order_type_lookup_code, 'QUANTITY', to_number(null), prl.amount) amount,
187 ph.currency_code,
188 ph.rate_type,
189 ph.rate_date,
190 ph.rate rate,
191 prl.suggested_buyer_id buyer_id,
192 prl.suggested_vendor_contact_id supplier_contact_id,
193 prl.rfq_required_flag,
194 NVL(prl.negotiated_by_preparer_flag, 'N') negotiated_by_preparer_flag,
195 pltb.order_type_lookup_code,
196 pv.vendor_name supplier,
197 ph.global_agreement_flag,
198 ph.approved_date,
199 NVL(ph.authorization_status, 'INCOMPLETE') authorization_status,
200 NVL(ph.frozen_flag, 'N') frozen_flag,
201 NVL(ph.cancel_flag, 'N') hdr_cancel_flag,
202 NVL(pl.cancel_flag, 'N') line_cancel_flag,
203 NVL(ph.closed_code, 'OPEN') hdr_closed_code,
204 NVL(pl.closed_code, 'OPEN') line_closed_code,
205 NVL(TRUNC(ph.end_date), TRUNC(SYSDATE + 1)) end_date,
206 NVL(TRUNC(pl.expiration_date), TRUNC(SYSDATE+1)) expiration_date,
207 TRUNC(SYSDATE) system_date
208 FROM po_reqexpress_headers_all prh,
209 po_reqexpress_lines_all prl,
210 po_headers_all ph,
211 po_lines_all pl,
212 po_attribute_values_tlp po_tlp,
213 po_line_types_b pltb,
214 icx_cat_categories_tl ic,
215 po_vendors pv
216 WHERE prl.express_name = prh.express_name
217 AND prl.org_id = prh.org_id
218 AND prl.po_line_id = pl.po_line_id (+)
219 AND prl.po_header_id = pl.po_header_id (+)
220 AND prl.po_header_id = ph.po_header_id (+)
221 AND -2 = po_tlp.po_line_id
222 AND prl.express_name = po_tlp.req_template_name
223 AND prl.sequence_num = po_tlp.req_template_line_num
224 AND prl.org_id = po_tlp.org_id
225 AND prl.line_type_id = pltb.line_type_id
226 AND NVL(pltb.purchase_basis, 'NULL') <> 'TEMP LABOR'
227 AND po_tlp.ip_category_id = ic.rt_category_id (+)
228 AND po_tlp.language = ic.language (+)
229 AND prl.suggested_vendor_id = pv.vendor_id(+)
230 AND prl.rowid BETWEEN g_start_rowid and g_end_rowid
231 AND (ph.last_update_date > g_upgrade_last_run_date
232 OR pl.last_update_date > g_upgrade_last_run_date
233 OR prh.last_update_date > g_upgrade_last_run_date
234 OR prl.last_update_date > g_upgrade_last_run_date
235 OR po_tlp.last_update_date > g_upgrade_last_run_date)
236 ) doc,
237 icx_cat_items_ctx_hdrs_tlp ctx
238 WHERE -2 = ctx.po_line_id (+)
239 AND doc.req_template_name = ctx.req_template_name (+)
240 AND doc.req_template_line_num = ctx.req_template_line_num (+)
241 AND doc.source_type = ctx.source_type (+)
242 AND doc.org_id = ctx.org_id (+)
243 AND doc.language = ctx.language (+);
244 END IF;
245
246 l_err_loc := 700;
247 populateReqTemplates(l_reqTemplate_csr, ICX_CAT_UTIL_PVT.g_upgrade_const);
248
249 l_err_loc := 800;
250 CLOSE l_reqTemplate_csr;
251
252 l_err_loc := 900;
253 EXCEPTION
254 WHEN OTHERS THEN
255 ICX_CAT_UTIL_PVT.logUnexpectedException(
256 G_PKG_NAME, l_api_name,
257 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
258 RAISE;
259 END openR12UpgradeReqTmpltsCursor;
260
261 PROCEDURE openReqTmpltsCursor
262 ( p_key IN NUMBER
263 )
264 IS
265 l_api_name CONSTANT VARCHAR2(30) := 'openReqTmpltsCursor';
266 l_err_loc PLS_INTEGER;
267 l_reqTemplate_csr ICX_CAT_POPULATE_ITEM_PVT.g_item_csr_type;
268 BEGIN
269 l_err_loc := 100;
270 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
271 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
272 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
273 'Processing cursor:' || l_api_name ||
274 ', p_key:' || p_key );
275 END IF;
276
277 l_err_loc := 150;
278 --First close the cursor
279 IF (l_reqTemplate_csr%ISOPEN) THEN
280 l_err_loc := 200;
281 CLOSE l_reqTemplate_csr;
282 END IF;
283
284 l_err_loc := 300;
285 OPEN l_reqTemplate_csr FOR
286 SELECT /*+ LEADING(doc) */
287 doc.*,
288 ctx.inventory_item_id ctx_inventory_item_id,
289 ctx.source_type ctx_source_type,
290 ctx.item_type ctx_item_type,
291 ctx.purchasing_org_id ctx_purchasing_org_id,
292 ctx.supplier_id ctx_supplier_id,
293 ctx.supplier_site_id ctx_supplier_site_id,
294 ctx.supplier_part_num ctx_supplier_part_num,
295 ctx.supplier_part_auxid ctx_supplier_part_auxid,
296 ctx.ip_category_id ctx_ip_category_id,
297 ctx.po_category_id ctx_po_category_id,
298 ctx.item_revision ctx_item_revision,
299 ROWIDTOCHAR(ctx.rowid) ctx_rowid
300 FROM
301 (
302 SELECT NVL(prl.item_id, -2) inventory_item_id,
303 prl.express_name req_template_name,
304 prl.sequence_num req_template_line_num,
305 NVL(prl.org_id, -2) org_id,
306 po_tlp.language language,
307 DECODE(prl.source_type_code, 'VENDOR', 'TEMPLATE', 'INTERNAL_TEMPLATE') source_type,
308 DECODE(prl.source_type_code, 'VENDOR', 'PURCHASE', 'INTERNAL') item_type,
309 NVL(prl.org_id, -2) purchasing_org_id,
310 prl.category_id po_category_id,
311 NVL(prl.suggested_vendor_id, -2) supplier_id,
312 NVL(prl.suggested_vendor_product_code, '##NULL##') supplier_part_num,
313 NVL(prl.suggested_vendor_site_id, -2) supplier_site_id,
314 prl.ip_category_id,
315 ic.category_name ip_category_name,
316 prh.inactive_date,
317 prl.po_line_id,
318 prl.po_line_id req_template_po_line_id,
319 NVL(prl.item_revision, '-2'),
320 prl.po_header_id,
321 ph.segment1 document_number,
322 pl.line_num,
323 prl.line_type_id,
324 prl.unit_meas_lookup_code,
325 prl.suggested_quantity,
326 DECODE(pltb.order_type_lookup_code, 'QUANTITY', prl.unit_price, to_number(null)) unit_price,
327 DECODE(pltb.order_type_lookup_code, 'QUANTITY', to_number(null), prl.amount) amount,
328 ph.currency_code,
329 ph.rate_type,
330 ph.rate_date,
331 ph.rate rate,
332 prl.suggested_buyer_id buyer_id,
333 prl.suggested_vendor_contact_id supplier_contact_id,
334 prl.rfq_required_flag,
335 NVL(prl.negotiated_by_preparer_flag, 'N') negotiated_by_preparer_flag,
336 pltb.order_type_lookup_code,
337 pv.vendor_name supplier,
338 ph.global_agreement_flag,
339 ph.approved_date,
340 NVL(ph.authorization_status, 'INCOMPLETE') authorization_status,
341 NVL(ph.frozen_flag, 'N') frozen_flag,
342 NVL(ph.cancel_flag, 'N') hdr_cancel_flag,
343 NVL(pl.cancel_flag, 'N') line_cancel_flag,
344 NVL(ph.closed_code, 'OPEN') hdr_closed_code,
345 NVL(pl.closed_code, 'OPEN') line_closed_code,
346 NVL(TRUNC(ph.end_date), TRUNC(SYSDATE + 1)) end_date,
347 NVL(TRUNC(pl.expiration_date), TRUNC(SYSDATE+1)) expiration_date,
348 TRUNC(SYSDATE) system_date
349 FROM po_reqexpress_headers_all prh,
350 po_reqexpress_lines_all prl,
351 po_session_gt pogt,
352 po_headers_all ph,
353 po_lines_all pl,
354 po_attribute_values_tlp po_tlp,
355 po_line_types_b pltb,
356 icx_cat_categories_tl ic,
357 po_vendors pv
358 WHERE prl.express_name = prh.express_name
359 AND prl.org_id = prh.org_id
360 AND pogt.key = p_key
361 AND prl.express_name = pogt.index_char1
362 AND prl.sequence_num = pogt.index_num1
363 AND prl.org_id = pogt.index_num2
364 AND prl.po_line_id = pl.po_line_id (+)
365 AND prl.po_header_id = pl.po_header_id (+)
366 AND prl.po_header_id = ph.po_header_id (+)
367 AND prl.express_name = po_tlp.req_template_name
368 AND prl.sequence_num = po_tlp.req_template_line_num
369 AND prl.org_id = po_tlp.org_id
370 AND prl.line_type_id = pltb.line_type_id
371 AND NVL(pltb.purchase_basis, 'NULL') <> 'TEMP LABOR'
372 AND po_tlp.ip_category_id = ic.rt_category_id (+)
373 AND po_tlp.language = ic.language (+)
374 AND prl.suggested_vendor_id = pv.vendor_id(+)
375 ) doc,
376 icx_cat_items_ctx_hdrs_tlp ctx
377 WHERE -2 = ctx.po_line_id (+)
378 AND doc.req_template_name = ctx.req_template_name (+)
379 AND doc.req_template_line_num = ctx.req_template_line_num (+)
380 AND doc.source_type = ctx.source_type (+)
381 AND doc.org_id = ctx.org_id (+)
382 AND doc.language = ctx.language (+);
383
384 l_err_loc := 500;
385 populateReqTemplates(l_reqTemplate_csr, ICX_CAT_UTIL_PVT.g_online_const);
386
387 l_err_loc := 600;
388 CLOSE l_reqTemplate_csr;
389
390 l_err_loc := 700;
391 EXCEPTION
392 WHEN OTHERS THEN
393 ICX_CAT_UTIL_PVT.logUnexpectedException(
394 G_PKG_NAME, l_api_name,
395 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
396 RAISE;
397 END openReqTmpltsCursor;
398
399 -- l_reqTemplate_csr, ICX_CAT_UTIL_PVT.g_upgrade_const
400 PROCEDURE populateReqTemplates
401 ( p_reqTemplate_csr IN ICX_CAT_POPULATE_ITEM_PVT.g_item_csr_type ,
402 p_current_mode IN VARCHAR2
403 )
404 IS
405
406 l_api_name CONSTANT VARCHAR2(30) := 'populateReqTemplates';
407 l_err_loc PLS_INTEGER;
408 l_err_string VARCHAR2(4000);
409 l_batch_count PLS_INTEGER;
410 l_row_count PLS_INTEGER;
411 l_count PLS_INTEGER;
412 l_BPA_line_status_rec ICX_CAT_POPULATE_STATUS_PVT.g_BPA_line_status_rec_type;
413 l_ReqTmplt_line_status PLS_INTEGER;
414 l_current_ctx_item_rec ICX_CAT_POPULATE_ITEM_PVT.g_ctx_item_rec_type;
415
416 ----- Start of declaring columns selected in the cursor -----
417 l_inv_item_id_tbl DBMS_SQL.NUMBER_TABLE;
418 l_req_template_name_tbl DBMS_SQL.VARCHAR2_TABLE;
419 l_req_template_line_num_tbl DBMS_SQL.NUMBER_TABLE;
420 l_org_id_tbl DBMS_SQL.NUMBER_TABLE;
421 l_language_tbl DBMS_SQL.VARCHAR2_TABLE;
422 l_source_type_tbl DBMS_SQL.VARCHAR2_TABLE;
423 l_item_type_tbl DBMS_SQL.VARCHAR2_TABLE;
424 l_purchasing_org_id_tbl DBMS_SQL.NUMBER_TABLE;
425 l_po_category_id_tbl DBMS_SQL.NUMBER_TABLE;
426 l_supplier_id_tbl DBMS_SQL.NUMBER_TABLE;
427 l_supplier_part_num_tbl DBMS_SQL.VARCHAR2_TABLE;
428 l_supplier_site_id_tbl DBMS_SQL.NUMBER_TABLE;
429 l_ip_category_id_tbl DBMS_SQL.NUMBER_TABLE;
430 l_ip_category_name_tbl DBMS_SQL.VARCHAR2_TABLE;
431 l_inactive_date_tbl DBMS_SQL.DATE_TABLE;
432 l_po_line_id_tbl DBMS_SQL.NUMBER_TABLE;
433 l_req_template_po_line_id_tbl DBMS_SQL.NUMBER_TABLE;
434 l_item_revision_tbl DBMS_SQL.VARCHAR2_TABLE;
435 l_po_header_id_tbl DBMS_SQL.NUMBER_TABLE;
436 l_document_number_tbl DBMS_SQL.VARCHAR2_TABLE;
437 l_line_num_tbl DBMS_SQL.NUMBER_TABLE;
438 l_line_type_id_tbl DBMS_SQL.NUMBER_TABLE;
439 l_unit_meas_lookup_code_tbl DBMS_SQL.VARCHAR2_TABLE;
440 l_suggested_quantity_tbl DBMS_SQL.NUMBER_TABLE;
441 l_unit_price_tbl DBMS_SQL.NUMBER_TABLE;
442 l_amount_tbl DBMS_SQL.NUMBER_TABLE;
443 l_currency_code_tbl DBMS_SQL.VARCHAR2_TABLE;
444 l_rate_type_tbl DBMS_SQL.VARCHAR2_TABLE;
445 l_rate_date_tbl DBMS_SQL.DATE_TABLE;
446 l_rate_tbl DBMS_SQL.NUMBER_TABLE;
447 l_buyer_id_tbl DBMS_SQL.NUMBER_TABLE;
448 l_supplier_contact_id_tbl DBMS_SQL.NUMBER_TABLE;
449 l_rfq_required_flag_tbl DBMS_SQL.VARCHAR2_TABLE;
450 l_negotiated_preparer_flag_tbl DBMS_SQL.VARCHAR2_TABLE;
451 l_order_type_lookup_code_tbl DBMS_SQL.VARCHAR2_TABLE;
452 l_supplier_tbl DBMS_SQL.VARCHAR2_TABLE;
453 l_global_agreement_flag_tbl DBMS_SQL.VARCHAR2_TABLE;
454 l_approved_date_tbl DBMS_SQL.DATE_TABLE;
455 l_authorization_status_tbl DBMS_SQL.VARCHAR2_TABLE;
456 l_frozen_flag_tbl DBMS_SQL.VARCHAR2_TABLE;
457 l_hdr_cancel_flag_tbl DBMS_SQL.VARCHAR2_TABLE;
458 l_line_cancel_flag_tbl DBMS_SQL.VARCHAR2_TABLE;
459 l_hdr_closed_code_tbl DBMS_SQL.VARCHAR2_TABLE;
460 l_line_closed_code_tbl DBMS_SQL.VARCHAR2_TABLE;
461 l_end_date_tbl DBMS_SQL.DATE_TABLE;
462 l_expiration_date_tbl DBMS_SQL.DATE_TABLE;
463 l_system_date_tbl DBMS_SQL.DATE_TABLE;
464 l_ctx_inventory_item_id_tbl DBMS_SQL.NUMBER_TABLE;
465 l_ctx_source_type_tbl DBMS_SQL.VARCHAR2_TABLE;
466 l_ctx_item_type_tbl DBMS_SQL.VARCHAR2_TABLE;
467 l_ctx_purchasing_org_id_tbl DBMS_SQL.NUMBER_TABLE;
468 l_ctx_supplier_id_tbl DBMS_SQL.NUMBER_TABLE;
469 l_ctx_supplier_site_id_tbl DBMS_SQL.NUMBER_TABLE;
470 l_ctx_supplier_part_num_tbl DBMS_SQL.VARCHAR2_TABLE;
471 l_ctx_supplier_part_auxid_tbl DBMS_SQL.VARCHAR2_TABLE;
472 l_ctx_ip_category_id_tbl DBMS_SQL.NUMBER_TABLE;
473 l_ctx_po_category_id_tbl DBMS_SQL.NUMBER_TABLE;
474 l_ctx_item_revision_tbl DBMS_SQL.VARCHAR2_TABLE;
475 l_ctx_rowid_tbl DBMS_SQL.UROWID_TABLE;
476 ------ End of declaring columns selected in the cursor ------
477
478 BEGIN
479 l_err_loc := 100;
480 l_batch_count := 0;
481 l_row_count := 0;
482 l_count := 0;
483 LOOP
484 l_err_loc := 200;
485 l_inv_item_id_tbl.DELETE;
486 l_req_template_name_tbl.DELETE;
487 l_req_template_line_num_tbl.DELETE;
488 l_org_id_tbl.DELETE;
489 l_language_tbl.DELETE;
490 l_source_type_tbl.DELETE;
491 l_item_type_tbl.DELETE;
492 l_purchasing_org_id_tbl.DELETE;
493 l_po_category_id_tbl.DELETE;
494 l_supplier_id_tbl.DELETE;
495 l_supplier_part_num_tbl.DELETE;
496 l_supplier_site_id_tbl.DELETE;
497 l_ip_category_id_tbl.DELETE;
498 l_ip_category_name_tbl.DELETE;
499 l_inactive_date_tbl.DELETE;
500 l_po_line_id_tbl.DELETE;
501 l_req_template_po_line_id_tbl.DELETE;
502 l_item_revision_tbl.DELETE;
503 l_po_header_id_tbl.DELETE;
504 l_document_number_tbl.DELETE;
505 l_line_num_tbl.DELETE;
506 l_line_type_id_tbl.DELETE;
507 l_unit_meas_lookup_code_tbl.DELETE;
508 l_suggested_quantity_tbl.DELETE;
509 l_unit_price_tbl.DELETE;
510 l_amount_tbl.DELETE;
511 l_currency_code_tbl.DELETE;
512 l_rate_type_tbl.DELETE;
513 l_rate_date_tbl.DELETE;
514 l_rate_tbl.DELETE;
515 l_buyer_id_tbl.DELETE;
516 l_supplier_contact_id_tbl.DELETE;
517 l_rfq_required_flag_tbl.DELETE;
518 l_negotiated_preparer_flag_tbl.DELETE;
519 l_order_type_lookup_code_tbl.DELETE;
520 l_supplier_tbl.DELETE;
521 l_global_agreement_flag_tbl.DELETE;
522 l_approved_date_tbl.DELETE;
523 l_authorization_status_tbl.DELETE;
524 l_frozen_flag_tbl.DELETE;
525 l_hdr_cancel_flag_tbl.DELETE;
526 l_line_cancel_flag_tbl.DELETE;
527 l_hdr_closed_code_tbl.DELETE;
528 l_line_closed_code_tbl.DELETE;
529 l_end_date_tbl.DELETE;
530 l_expiration_date_tbl.DELETE;
531 l_system_date_tbl.DELETE;
532 l_ctx_inventory_item_id_tbl.DELETE;
533 l_ctx_source_type_tbl.DELETE;
534 l_ctx_item_type_tbl.DELETE;
535 l_ctx_purchasing_org_id_tbl.DELETE;
536 l_ctx_supplier_id_tbl.DELETE;
537 l_ctx_supplier_site_id_tbl.DELETE;
538 l_ctx_supplier_part_num_tbl.DELETE;
539 l_ctx_supplier_part_auxid_tbl.DELETE;
540 l_ctx_ip_category_id_tbl.DELETE;
541 l_ctx_po_category_id_tbl.DELETE;
542 l_ctx_item_revision_tbl.DELETE;
543 l_ctx_rowid_tbl.DELETE;
544
545 BEGIN
546 l_err_loc := 300;
547 FETCH p_reqTemplate_csr BULK COLLECT INTO
548 l_inv_item_id_tbl,
549 l_req_template_name_tbl,
550 l_req_template_line_num_tbl,
551 l_org_id_tbl,
552 l_language_tbl,
553 l_source_type_tbl,
554 l_item_type_tbl,
555 l_purchasing_org_id_tbl,
556 l_po_category_id_tbl,
557 l_supplier_id_tbl,
558 l_supplier_part_num_tbl,
559 l_supplier_site_id_tbl,
560 l_ip_category_id_tbl,
561 l_ip_category_name_tbl,
562 l_inactive_date_tbl,
563 l_po_line_id_tbl,
564 l_req_template_po_line_id_tbl,
565 l_item_revision_tbl,
566 l_po_header_id_tbl,
567 l_document_number_tbl,
568 l_line_num_tbl,
569 l_line_type_id_tbl,
570 l_unit_meas_lookup_code_tbl,
571 l_suggested_quantity_tbl,
572 l_unit_price_tbl,
573 l_amount_tbl,
574 l_currency_code_tbl,
575 l_rate_type_tbl,
576 l_rate_date_tbl,
577 l_rate_tbl,
578 l_buyer_id_tbl,
579 l_supplier_contact_id_tbl,
580 l_rfq_required_flag_tbl,
581 l_negotiated_preparer_flag_tbl,
582 l_order_type_lookup_code_tbl,
583 l_supplier_tbl,
584 l_global_agreement_flag_tbl,
585 l_approved_date_tbl,
586 l_authorization_status_tbl,
587 l_frozen_flag_tbl,
588 l_hdr_cancel_flag_tbl,
589 l_line_cancel_flag_tbl,
590 l_hdr_closed_code_tbl,
591 l_line_closed_code_tbl,
592 l_end_date_tbl,
593 l_expiration_date_tbl,
594 l_system_date_tbl,
595 l_ctx_inventory_item_id_tbl,
596 l_ctx_source_type_tbl,
597 l_ctx_item_type_tbl,
598 l_ctx_purchasing_org_id_tbl,
599 l_ctx_supplier_id_tbl,
600 l_ctx_supplier_site_id_tbl,
601 l_ctx_supplier_part_num_tbl,
602 l_ctx_supplier_part_auxid_tbl,
603 l_ctx_ip_category_id_tbl,
604 l_ctx_po_category_id_tbl,
605 l_ctx_item_revision_tbl,
606 l_ctx_rowid_tbl
607 LIMIT ICX_CAT_UTIL_PVT.g_batch_size;
608 l_err_loc := 400;
609 EXIT WHEN l_inv_item_id_tbl.COUNT = 0;
610
611 l_err_loc := 500;
612 l_batch_count := l_batch_count + 1;
613
614 l_err_loc := 600;
615 l_count := l_inv_item_id_tbl.COUNT;
616 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
617 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
618 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
619 'Num. of rows returned from the cursor:' || l_count);
620 END IF;
621 l_row_count := l_row_count + l_count;
622
623 l_err_loc := 700;
624 FOR i in 1..l_inv_item_id_tbl.COUNT LOOP
625 l_err_loc := 800;
626 --First get the status of the current BPA line
627 l_BPA_line_status_rec.approved_date := l_approved_date_tbl(i);
628 l_BPA_line_status_rec.authorization_status := l_authorization_status_tbl(i);
629 l_BPA_line_status_rec.frozen_flag := l_frozen_flag_tbl(i);
630 l_BPA_line_status_rec.hdr_cancel_flag := l_hdr_cancel_flag_tbl(i);
631 l_BPA_line_status_rec.line_cancel_flag := l_line_cancel_flag_tbl(i);
632 l_BPA_line_status_rec.hdr_closed_code := l_hdr_closed_code_tbl(i);
633 l_BPA_line_status_rec.line_closed_code := l_line_closed_code_tbl(i);
634 l_BPA_line_status_rec.end_date := l_end_date_tbl(i);
635 l_BPA_line_status_rec.expiration_date := l_expiration_date_tbl(i);
636 l_BPA_line_status_rec.system_date := l_system_date_tbl(i);
637
638 l_err_loc := 900;
639 l_ReqTmplt_line_status := ICX_CAT_POPULATE_STATUS_PVT.getTemplateLineStatus
640 (l_inactive_date_tbl(i), l_po_line_id_tbl(i), l_BPA_line_status_rec);
641
642 l_err_loc := 1000;
643 IF (l_ctx_rowid_tbl(i) IS NOT NULL OR
644 l_ReqTmplt_line_status = ICX_CAT_POPULATE_STATUS_PVT.VALID_FOR_POPULATE)
645 THEN
646 l_err_loc := 1100;
647 l_current_ctx_item_rec.inventory_item_id := l_inv_item_id_tbl(i);
648 l_current_ctx_item_rec.po_line_id := TO_NUMBER(ICX_CAT_UTIL_PVT.g_NULL_NUMBER);
649 l_current_ctx_item_rec.req_template_name := l_req_template_name_tbl(i);
650 l_current_ctx_item_rec.req_template_line_num := l_req_template_line_num_tbl(i);
651 l_current_ctx_item_rec.org_id := l_org_id_tbl(i);
652 l_current_ctx_item_rec.language := l_language_tbl(i);
653 l_current_ctx_item_rec.source_type := l_source_type_tbl(i);
654 l_current_ctx_item_rec.item_type := l_item_type_tbl(i);
655 l_current_ctx_item_rec.purchasing_org_id := l_purchasing_org_id_tbl(i);
656 l_current_ctx_item_rec.owning_org_id := l_org_id_tbl(i);
657 l_current_ctx_item_rec.po_category_id := l_po_category_id_tbl(i);
658 l_current_ctx_item_rec.supplier_id := l_supplier_id_tbl(i);
659 l_current_ctx_item_rec.supplier_part_num := l_supplier_part_num_tbl(i);
660 l_current_ctx_item_rec.supplier_part_auxid := '##NULL##';
661 l_current_ctx_item_rec.supplier_site_id := l_supplier_site_id_tbl(i);
662 l_current_ctx_item_rec.status := l_ReqTmplt_line_status;
663 l_current_ctx_item_rec.ip_category_id := l_ip_category_id_tbl(i);
664 l_current_ctx_item_rec.ip_category_name := l_ip_category_name_tbl(i);
665 l_current_ctx_item_rec.req_template_po_line_id := l_req_template_po_line_id_tbl(i);
666 l_current_ctx_item_rec.item_revision := l_item_revision_tbl(i);
667 l_current_ctx_item_rec.po_header_id := l_po_header_id_tbl(i);
668 l_current_ctx_item_rec.document_number := l_document_number_tbl(i);
669 l_current_ctx_item_rec.line_num := l_line_num_tbl(i);
670 l_current_ctx_item_rec.allow_price_override_flag := 'N';
671 l_current_ctx_item_rec.not_to_exceed_price := NULL;
672 l_current_ctx_item_rec.line_type_id := l_line_type_id_tbl(i);
673 l_current_ctx_item_rec.unit_meas_lookup_code := l_unit_meas_lookup_code_tbl(i);
674 l_current_ctx_item_rec.suggested_quantity := l_suggested_quantity_tbl(i);
675 l_current_ctx_item_rec.unit_price := l_unit_price_tbl(i);
676 l_current_ctx_item_rec.amount := l_amount_tbl(i);
677 l_current_ctx_item_rec.currency_code := l_currency_code_tbl(i);
678 l_current_ctx_item_rec.rate_type := l_rate_type_tbl(i);
679 l_current_ctx_item_rec.rate_date := l_rate_date_tbl(i);
680 l_current_ctx_item_rec.rate := l_rate_tbl(i);
681 l_current_ctx_item_rec.buyer_id := l_buyer_id_tbl(i);
682 l_current_ctx_item_rec.supplier_contact_id := l_supplier_contact_id_tbl(i);
683 l_current_ctx_item_rec.rfq_required_flag := l_rfq_required_flag_tbl(i);
684 l_current_ctx_item_rec.negotiated_by_preparer_flag := l_negotiated_preparer_flag_tbl(i);
685 l_current_ctx_item_rec.description := NULL;
686 l_current_ctx_item_rec.order_type_lookup_code := l_order_type_lookup_code_tbl(i);
687 l_current_ctx_item_rec.supplier := l_supplier_tbl(i);
688 l_current_ctx_item_rec.global_agreement_flag := l_global_agreement_flag_tbl(i);
689 l_current_ctx_item_rec.merged_source_type := 'REQ_TEMPLATE';
690 l_current_ctx_item_rec.ctx_inventory_item_id := l_ctx_inventory_item_id_tbl(i);
691 l_current_ctx_item_rec.ctx_source_type := l_ctx_source_type_tbl(i);
692 l_current_ctx_item_rec.ctx_item_type := l_ctx_item_type_tbl(i);
693 l_current_ctx_item_rec.ctx_purchasing_org_id := l_ctx_purchasing_org_id_tbl(i);
694 l_current_ctx_item_rec.ctx_supplier_id := l_ctx_supplier_id_tbl(i);
695 l_current_ctx_item_rec.ctx_supplier_site_id := l_ctx_supplier_site_id_tbl(i);
696 l_current_ctx_item_rec.ctx_supplier_part_num := l_ctx_supplier_part_num_tbl(i);
697 l_current_ctx_item_rec.ctx_supplier_part_auxid := l_ctx_supplier_part_auxid_tbl(i);
698 l_current_ctx_item_rec.ctx_ip_category_id := l_ctx_ip_category_id_tbl(i);
699 l_current_ctx_item_rec.ctx_po_category_id := l_ctx_po_category_id_tbl(i);
700 l_current_ctx_item_rec.ctx_item_revision := l_ctx_item_revision_tbl(i);
701 l_current_ctx_item_rec.ctx_rowid := l_ctx_rowid_tbl(i);
702
703 l_err_loc := 1200;
704 ICX_CAT_POPULATE_ITEM_PVT.processCurrentCtxItemRow(l_current_ctx_item_rec, null, p_current_mode);
705
706 l_err_loc := 1300;
707 ICX_CAT_POPULATE_ITEM_PVT.populateItemCtxTables('INLOOP', ICX_CAT_UTIL_PVT.g_ReqTemplateCsr_const);
708 ELSE
709 IF (p_current_mode = ICX_CAT_UTIL_PVT.g_upgrade_const) THEN
710 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
711 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
712 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
713 'Row #:' || i ||
714 ', with req_template_line:(' || l_req_template_name_tbl(i) ||
715 ', ' || l_req_template_line_num_tbl(i) ||
716 ', ' || l_org_id_tbl(i) ||'), not processed' ||
717 ', l_inactive_date_tbl:' || l_inactive_date_tbl(i) ||
718 ', l_po_line_id_tbl:' || l_po_line_id_tbl(i) ||
719 ', l_approved_date_tbl:' || l_approved_date_tbl(i) ||
720 ', l_authorization_status_tbl:' || l_authorization_status_tbl(i) ||
721 ', l_frozen_flag_tbl:' || l_frozen_flag_tbl(i) ||
722 ', l_hdr_cancel_flag_tbl:' || l_hdr_cancel_flag_tbl(i) ||
723 ', l_line_cancel_flag_tbl:' || l_line_cancel_flag_tbl(i) ||
724 ', l_hdr_closed_code_tbl:' || l_hdr_closed_code_tbl(i) ||
725 ', l_line_closed_code_tbl:' || l_line_closed_code_tbl(i) ||
726 ', l_end_date_tbl:' || l_end_date_tbl(i) ||
727 ', l_expiration_date_tbl:' || l_expiration_date_tbl(i) ||
728 ', l_system_date_tbl:' || l_system_date_tbl(i) ||
729 ', status: ' || l_ReqTmplt_line_status);
730 END IF;
731 ELSE
732 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
733 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
734 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
735 'Row #:' || i ||
736 ', with req_template_line:(' || l_req_template_name_tbl(i) ||
737 ', ' || l_req_template_line_num_tbl(i) ||
738 ', ' || l_org_id_tbl(i) ||'), not processed' ||
739 ', l_inactive_date_tbl:' || l_inactive_date_tbl(i) ||
740 ', l_po_line_id_tbl:' || l_po_line_id_tbl(i) ||
741 ', l_approved_date_tbl:' || l_approved_date_tbl(i) ||
742 ', l_authorization_status_tbl:' || l_authorization_status_tbl(i) ||
743 ', l_frozen_flag_tbl:' || l_frozen_flag_tbl(i) ||
744 ', l_hdr_cancel_flag_tbl:' || l_hdr_cancel_flag_tbl(i) ||
745 ', l_line_cancel_flag_tbl:' || l_line_cancel_flag_tbl(i) ||
746 ', l_hdr_closed_code_tbl:' || l_hdr_closed_code_tbl(i) ||
747 ', l_line_closed_code_tbl:' || l_line_closed_code_tbl(i) ||
748 ', l_end_date_tbl:' || l_end_date_tbl(i) ||
749 ', l_expiration_date_tbl:' || l_expiration_date_tbl(i) ||
750 ', l_system_date_tbl:' || l_system_date_tbl(i) ||
751 ', status: ' || l_ReqTmplt_line_status);
752 END IF;
753 END IF;
754 END IF;
755 END LOOP; --FOR LOOP of l_inv_item_id_tbl
756
757 l_err_loc := 1400;
758 EXIT WHEN l_inv_item_id_tbl.COUNT < ICX_CAT_UTIL_PVT.g_batch_size;
759 EXCEPTION
760 WHEN ICX_CAT_UTIL_PVT.g_snap_shot_too_old THEN
761 l_err_string := 'ICX_CAT_POPULATE_REQTMPL_PVT.populateReqTemplates' ||l_err_loc
762 ||', Total processeded batches:' ||l_batch_count
763 ||', Cursor will be reopened with TBD TBD:' ;
764 IF (FND_API.To_Boolean(ICX_CAT_UTIL_PVT.g_COMMIT)) THEN
765 ICX_CAT_UTIL_PVT.logAndCommitSnapShotTooOld(g_pkg_name, l_api_name, l_err_string);
766 --Closing and reopen of cursor will be done by called procedures
767 IF (p_current_mode = ICX_CAT_UTIL_PVT.g_upgrade_const) THEN
768 l_err_loc := 1500;
769 openR12UpgradeReqTmpltsCursor;
770 ELSE
771 l_err_loc := 1600;
772 openReqTmpltsCursor(g_key);
773 END IF;
774 ELSE
775 RAISE;
776 END IF;
777 END;
778 END LOOP; --Cursor loop
779
780 l_err_loc := 1700;
781 ICX_CAT_POPULATE_ITEM_PVT.populateItemCtxTables('OUTLOOP', ICX_CAT_UTIL_PVT.g_ReqTemplateCsr_const);
782
783 l_err_loc := 1800;
784 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
785 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
786 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
787 'populateReqTemplates done; '||
788 'Total num. of batches processed:' ||l_batch_count ||
789 ', Total num. of rows processed:' ||l_row_count);
790 END IF;
791 l_err_loc := 1900;
792 EXCEPTION
793 WHEN OTHERS THEN
794 ICX_CAT_UTIL_PVT.logUnexpectedException(
795 G_PKG_NAME, l_api_name,
796 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
797 RAISE;
798 END populateReqTemplates;
799
800 PROCEDURE upgradeR12ReqTemplates
801 ( p_upgrade_last_run_date IN DATE ,
802 p_start_rowid IN ROWID ,
803 p_end_rowid IN ROWID
804 )
805 IS
806 l_api_name CONSTANT VARCHAR2(30) := 'upgradeR12ReqTemplates';
807 l_err_loc PLS_INTEGER;
808 BEGIN
809 l_err_loc := 100;
810 g_upgrade_last_run_date := p_upgrade_last_run_date;
811
812 l_err_loc := 200;
813 g_start_rowid := p_start_rowid;
814 g_end_rowid := p_end_rowid;
815
816 l_err_loc := 300;
817 openR12UpgradeReqTmpltsCursor;
818
819 l_err_loc := 400;
820 EXCEPTION
821 WHEN OTHERS THEN
822 ICX_CAT_UTIL_PVT.logUnexpectedException(
823 G_PKG_NAME, l_api_name,
824 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
825 ICX_CAT_UTIL_PVT.g_job_reqtmplt_complete_date := NULL;
826 ICX_CAT_UTIL_PVT.g_job_complete_date := NULL;
827 ICX_CAT_UTIL_PVT.g_job_current_status := ICX_CAT_UTIL_PVT.g_job_failed_status;
828 RAISE;
829 END upgradeR12ReqTemplates;
830
831 PROCEDURE populateOnlineReqTemplates
832 ( p_key IN NUMBER
833 )
834 IS
835 l_api_name CONSTANT VARCHAR2(30) := 'populateOnlineReqTemplates';
836 l_err_loc PLS_INTEGER;
837 BEGIN
838 l_err_loc := 100;
839 -- Set the batch_size
840 ICX_CAT_UTIL_PVT.setBatchSize;
841
842 l_err_loc := 200;
843 -- Set the who columns
844 ICX_CAT_UTIL_PVT.setWhoColumns(null);
845
846 l_err_loc := 300;
847 g_key := p_key;
848
849 l_err_loc := 350;
850 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
851 ICX_CAT_UTIL_PVT.logPOSessionGTData(p_key);
852 END IF;
853
854 l_err_loc := 400;
855 openReqTmpltsCursor(p_key);
856
857 l_err_loc := 500;
858 ICX_CAT_POPULATE_CTXSTRING_PVT.populateCtxCatgAtt(ICX_CAT_UTIL_PVT.g_who_columns_rec.internal_request_id);
859
860 l_err_loc := 600;
861 g_metadataTblFormed := FALSE;
862 g_CtxSqlForPODocsFormed := FALSE;
863
864 l_err_loc := 700;
865 EXCEPTION
866 WHEN OTHERS THEN
867 ICX_CAT_UTIL_PVT.logUnexpectedException(
868 G_PKG_NAME, l_api_name,
869 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
870 RAISE;
871 END populateOnlineReqTemplates;
872
873 PROCEDURE buildCtxSqlForRTs
874 ( p_special_ctx_sql_tbl IN OUT NOCOPY ICX_CAT_BUILD_CTX_SQL_PVT.g_ctx_sql_tbl_type ,
875 p_regular_ctx_sql_tbl IN OUT NOCOPY ICX_CAT_BUILD_CTX_SQL_PVT.g_ctx_sql_tbl_type
876 )
877 IS
878 l_api_name CONSTANT VARCHAR2(30) := 'buildCtxSqlForRTs';
879 l_err_loc PLS_INTEGER;
880 BEGIN
881 l_err_loc := 100;
882 IF (NOT ICX_CAT_POPULATE_REQTMPL_PVT.g_metadataTblFormed) THEN
883 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
884 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
885 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
886 'about to call buildmetadatinfo');
887 END IF;
888
889 l_err_loc := 200;
890 ICX_CAT_BUILD_CTX_SQL_PVT.buildMetadataInfo
891 (0, g_special_metadata_tbl, g_regular_nontl_metadata_tbl, g_regular_tl_metadata_tbl);
892
893 l_err_loc := 300;
894 ICX_CAT_POPULATE_REQTMPL_PVT.g_metadataTblFormed := TRUE;
895 END IF;
896
897 l_err_loc := 400;
898 IF (NOT ICX_CAT_POPULATE_REQTMPL_PVT.g_CtxSqlForPODocsFormed) THEN
899 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
900 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
901 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
902 'about to call buildctxsql');
903 END IF;
904
905 l_err_loc := 500;
906 ICX_CAT_BUILD_CTX_SQL_PVT.buildCtxSql
907 (0, ICX_CAT_UTIL_PVT.g_ReqTemplateCsr_const, 'NOTROWID', g_special_metadata_tbl,
908 g_regular_nontl_metadata_tbl, g_regular_tl_metadata_tbl,
909 g_all_ctx_sql_tbl, g_special_ctx_sql_tbl, g_regular_ctx_sql_tbl);
910
911 l_err_loc := 600;
912 ICX_CAT_POPULATE_REQTMPL_PVT.g_CtxSqlForPODocsFormed := TRUE;
913 END IF;
914
915 l_err_loc := 700;
916 p_special_ctx_sql_tbl := g_special_ctx_sql_tbl;
917 p_regular_ctx_sql_tbl := g_regular_ctx_sql_tbl;
918
919 l_err_loc := 800;
920 EXCEPTION
921 WHEN OTHERS THEN
922 ICX_CAT_UTIL_PVT.logUnexpectedException(
923 G_PKG_NAME, l_api_name,
924 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
925 RAISE;
926 END buildCtxSqlForRTs;
927
928
929 END ICX_CAT_POPULATE_REQTMPL_PVT;