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