[Home] [Help]
8:
9: ----------------------------------------------------
10: -- Global PL/SQL Tables --
11: ----------------------------------------------------
12: -- INSERT icx_cat_items_ctx_hdrs_tlp
13: gIHInventoryItemIdTbl DBMS_SQL.NUMBER_TABLE;
14: gIHPoLineIdTbl DBMS_SQL.NUMBER_TABLE;
15: gIHReqTemplateNameTbl DBMS_SQL.VARCHAR2_TABLE;
16: gIHReqTemplateLineNumTbl DBMS_SQL.NUMBER_TABLE;
66: gIDLanguageTbl DBMS_SQL.VARCHAR2_TABLE;
67: gIDPurchasingOrgIdTbl DBMS_SQL.NUMBER_TABLE;
68: gIDOwningOrgIdTbl DBMS_SQL.NUMBER_TABLE;
69:
70: -- UPDATE icx_cat_items_ctx_hdrs_tlp
71: gUHInventoryItemIdTbl DBMS_SQL.NUMBER_TABLE;
72: gUHPoLineIdTbl DBMS_SQL.NUMBER_TABLE;
73: gUHReqTemplateNameTbl DBMS_SQL.VARCHAR2_TABLE;
74: gUHReqTemplateLineNumTbl DBMS_SQL.NUMBER_TABLE;
114: gUHSupplierTbl DBMS_SQL.VARCHAR2_TABLE;
115: gUHGlobalAgreementFlagTbl DBMS_SQL.VARCHAR2_TABLE;
116: gUHMergedSourceTypeTbl DBMS_SQL.VARCHAR2_TABLE;
117:
118: -- DELETE icx_cat_items_ctx_hdrs_tlp
119: gDHInventoryItemIdTbl DBMS_SQL.NUMBER_TABLE;
120: gDHPoLineIdTbl DBMS_SQL.NUMBER_TABLE;
121: gDHReqTemplateNameTbl DBMS_SQL.VARCHAR2_TABLE;
122: gDHReqTemplateLineNumTbl DBMS_SQL.NUMBER_TABLE;
195: BEGIN
196: l_err_loc := 100;
197: IF (p_action_mode IN ('ALL', 'INSERT_CTX_HDRS', 'INSERT_ATTR_VALUES', 'INSERT_ATTR_VALUES_TLP')) THEN
198: l_err_loc := 200;
199: -- INSERT icx_cat_items_ctx_hdrs_tlp
200: gIHInventoryItemIdTbl.DELETE;
201: gIHPoLineIdTbl.DELETE;
202: gIHReqTemplateNameTbl.DELETE;
203: gIHReqTemplateLineNumTbl.DELETE;
262: l_err_loc := 500;
263:
264: IF (p_action_mode IN ('ALL', 'UPDATE_CTX_HDRS', 'INSERT_TO_UPDATE_ATTR_VALUES', 'INSERT_TO_UPDATE_ATTR_VALUES_TLP')) THEN
265: l_err_loc := 600;
266: -- UPDATE icx_cat_items_ctx_hdrs_tlp
267: gUHInventoryItemIdTbl.DELETE;
268: gUHPoLineIdTbl.DELETE;
269: gUHReqTemplateNameTbl.DELETE;
270: gUHReqTemplateLineNumTbl.DELETE;
313: l_err_loc := 700;
314:
315: IF (p_action_mode IN ('ALL', 'DELETE_CTX_HDRS', 'DELETE_ATTR_VALUES', 'DELETE_ATTR_VALUES_TLP')) THEN
316: l_err_loc := 800;
317: -- DELETE icx_cat_items_ctx_hdrs_tlp
318: gDHInventoryItemIdTbl.DELETE;
319: gDHPoLineIdTbl.DELETE;
320: gDHReqTemplateNameTbl.DELETE;
321: gDHReqTemplateLineNumTbl.DELETE;
434: l_string VARCHAR2(4000);
435: BEGIN
436: l_string := 'logPLSQLTableRow('||p_action_mode||')['||p_index||']--';
437: IF (p_action_mode = 'INSERT_CTX_HDRS') THEN
438: --INSERT icx_cat_items_ctx_hdrs_tlp
439: l_string := l_string || ' gIHInventoryItemIdTbl: ' ||
440: ICX_CAT_UTIL_PVT.getTableElement(gIHInventoryItemIdTbl, p_index) || ', ';
441: l_string := l_string || ' gIHPoLineIdTbl: ' ||
442: ICX_CAT_UTIL_PVT.getTableElement(gIHPoLineIdTbl, p_index) || ', ';
542: ICX_CAT_UTIL_PVT.getTableElement(gIDOwningOrgIdTbl, p_index) || ', ';
543: END IF;
544:
545: IF (p_action_mode = 'UPDATE_CTX_HDRS') THEN
546: --UPDATE icx_cat_items_ctx_hdrs_tlp
547: l_string := l_string || ' gUHInventoryItemIdTbl: ' ||
548: ICX_CAT_UTIL_PVT.getTableElement(gUHInventoryItemIdTbl, p_index) || ', ';
549: l_string := l_string || ' gUHPoLineIdTbl: ' ||
550: ICX_CAT_UTIL_PVT.getTableElement(gUHPoLineIdTbl, p_index) || ', ';
628: ICX_CAT_UTIL_PVT.getTableElement(gUHMergedSourceTypeTbl, p_index) || ', ';
629: END IF;
630:
631: IF (p_action_mode = 'DELETE_CTX_HDRS') THEN
632: --DELETE icx_cat_items_ctx_hdrs_tlp
633: l_string := l_string || ' gDHInventoryItemIdTbl: ' ||
634: ICX_CAT_UTIL_PVT.getTableElement(gDHInventoryItemIdTbl, p_index) || ', ';
635: l_string := l_string || ' gDHPoLineIdTbl: ' ||
636: ICX_CAT_UTIL_PVT.getTableElement(gDHPoLineIdTbl, p_index) || ', ';
769:
770: l_err_loc := 240;
771: IF (p_action_mode = 'INSERT_CTX_HDRS') THEN
772: l_err_loc := 300;
773: -- INSERT icx_cat_items_ctx_hdrs_tlp
774: l_log_string := ' gIHInventoryItemIdTbl['||p_index||']: ' ||
775: ICX_CAT_UTIL_PVT.getTableElement(gIHInventoryItemIdTbl, p_index) || '; ';
776: FND_LOG.string(p_log_level, l_module_name, l_log_string);
777:
983: l_err_loc := 600;
984:
985: IF (p_action_mode = 'UPDATE_CTX_HDRS') THEN
986: l_err_loc := 700;
987: -- UPDATE icx_cat_items_ctx_hdrs_tlp
988: l_log_string := ' gUHInventoryItemIdTbl['||p_index||']: ' ||
989: ICX_CAT_UTIL_PVT.getTableElement(gUHInventoryItemIdTbl, p_index) || '; ';
990: FND_LOG.string(p_log_level, l_module_name, l_log_string);
991:
1154: l_err_loc := 800;
1155:
1156: IF (p_action_mode = 'DELETE_CTX_HDRS') THEN
1157: l_err_loc := 900;
1158: -- DELETE icx_cat_items_ctx_hdrs_tlp
1159: l_log_string := ' gDHInventoryItemIdTbl['||p_index||']: ' ||
1160: ICX_CAT_UTIL_PVT.getTableElement(gDHInventoryItemIdTbl, p_index) || '; ';
1161: FND_LOG.string(p_log_level, l_module_name, l_log_string);
1162:
2085: l_description VARCHAR2(4000);
2086: l_long_description po_attribute_values_tlp.long_description%TYPE;
2087: l_organization_id NUMBER;
2088: l_master_organization_id NUMBER;
2089: l_language icx_cat_items_ctx_hdrs_tlp.language%TYPE;
2090: --BUG 6599217: end 1
2091: BEGIN
2092: l_err_loc := 100;
2093: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2118:
2119: l_err_loc := 250;
2120: l_action_mode := 'INSERT_CTX_HDRS';
2121: FORALL i in 1..gIHInventoryItemIdTbl.COUNT
2122: INSERT INTO icx_cat_items_ctx_hdrs_tlp
2123: (inventory_item_id, po_line_id,
2124: req_template_name, req_template_line_num,
2125: org_id, language,
2126: source_type, item_type, purchasing_org_id, owning_org_id,
2495:
2496: l_err_loc := 350;
2497: l_action_mode := 'UPDATE_CTX_HDRS';
2498: FORALL i in 1..gUHInventoryItemIdTbl.COUNT
2499: UPDATE icx_cat_items_ctx_hdrs_tlp
2500: SET ctx_desc = null,
2501: purchasing_org_id = gUHPurchasingOrgIdTbl(i),
2502: ip_category_id = gUHIpCategoryIdTbl(i),
2503: ip_category_name = gUHIpCategoryNameTbl(i),
2871:
2872: l_err_loc := 550;
2873: l_action_mode := 'DELETE_CTX_HDRS';
2874: FORALL i in 1..gDHInventoryItemIdTbl.COUNT
2875: DELETE FROM icx_cat_items_ctx_hdrs_tlp
2876: WHERE inventory_item_id = gDHInventoryItemIdTbl(i)
2877: AND po_line_id = gDHPoLineIdTbl(i)
2878: AND req_template_name = gDHReqTemplateNameTbl(i)
2879: AND req_template_line_num = gDHReqTemplateLineNumTbl(i)
3698: l_err_loc := 600;
3699: l_rowid_tbl.DELETE;
3700:
3701: l_err_loc := 700;
3702: UPDATE icx_cat_items_ctx_hdrs_tlp
3703: SET ctx_desc = NULL,
3704: supplier = p_vendor_name,
3705: last_update_login = ICX_CAT_UTIL_PVT.g_who_columns_rec.login_id,
3706: last_updated_by = ICX_CAT_UTIL_PVT.g_who_columns_rec.login_id,
3741: program_application_id = ICX_CAT_UTIL_PVT.g_who_columns_rec.program_application_id,
3742: program_id = ICX_CAT_UTIL_PVT.g_who_columns_rec.program_id,
3743: program_login_id = ICX_CAT_UTIL_PVT.g_who_columns_rec.program_login_id
3744: WHERE sequence = ICX_CAT_BUILD_CTX_SQL_PVT.g_seqForSupplierRow
3745: AND EXISTS ( SELECT 'x' FROM icx_cat_items_ctx_hdrs_tlp hdrs
3746: WHERE hdrs.po_line_id = dtls.po_line_id
3747: AND hdrs.req_template_name = dtls.req_template_name
3748: AND hdrs.req_template_line_num = dtls.req_template_line_num
3749: AND hdrs.inventory_item_id = dtls.inventory_item_id
3793: l_err_loc := 1500;
3794: l_rowid_tbl.DELETE;
3795:
3796: l_err_loc := 1600;
3797: UPDATE icx_cat_items_ctx_hdrs_tlp
3798: SET supplier = p_vendor_name,
3799: last_update_login = ICX_CAT_UTIL_PVT.g_who_columns_rec.login_id,
3800: last_updated_by = ICX_CAT_UTIL_PVT.g_who_columns_rec.login_id,
3801: last_update_date = sysdate,
3858: END populateVendorNameChanges;
3859:
3860: /*
3861: Steps:
3862: 1. update supplier_id, supplier_site_id and ctx_desc in icx_cat_items_ctx_hdrs_tlp
3863: 2. delete and insert into icx_cat_items_ctx_dtls_tlp : sequence 1 for supid and siteid
3864: 3. Check if supplier is searchable, if yes then update icx_cat_items_ctx_dtls_tlp : sequence 2 for supplier
3865: 4. call rebuild_index.
3866: */
3923: l_err_loc := 800;
3924: l_rowid_tbl.DELETE;
3925:
3926: l_err_loc := 900;
3927: UPDATE icx_cat_items_ctx_hdrs_tlp
3928: SET ctx_desc = NULL,
3929: supplier_id = p_to_vendor_id,
3930: supplier_site_id = p_to_site_id,
3931: supplier = l_to_vendor_name,
3962: -- delete the mandatory row in icx_cat_items_ctx_dtls_tlp i.e. the row with sequence = 1
3963: FORALL i IN 1..l_rowid_tbl.COUNT
3964: DELETE FROM icx_cat_items_ctx_dtls_tlp dtls
3965: WHERE sequence = ICX_CAT_BUILD_CTX_SQL_PVT.g_seqMandatoryBaseRow
3966: AND EXISTS ( SELECT 'x' FROM icx_cat_items_ctx_hdrs_tlp hdrs
3967: WHERE hdrs.po_line_id = dtls.po_line_id
3968: AND hdrs.req_template_name = dtls.req_template_name
3969: AND hdrs.req_template_line_num = dtls.req_template_line_num
3970: AND hdrs.inventory_item_id = dtls.inventory_item_id
4050: program_application_id = ICX_CAT_UTIL_PVT.g_who_columns_rec.program_application_id,
4051: program_id = ICX_CAT_UTIL_PVT.g_who_columns_rec.program_id,
4052: program_login_id = ICX_CAT_UTIL_PVT.g_who_columns_rec.program_login_id
4053: WHERE sequence = ICX_CAT_BUILD_CTX_SQL_PVT.g_seqForSupplierRow
4054: AND EXISTS ( SELECT 'x' FROM icx_cat_items_ctx_hdrs_tlp hdrs
4055: WHERE hdrs.po_line_id = dtls.po_line_id
4056: AND hdrs.req_template_name = dtls.req_template_name
4057: AND hdrs.req_template_line_num = dtls.req_template_line_num
4058: AND hdrs.inventory_item_id = dtls.inventory_item_id
4151: FROM mtl_categories_b mtlb
4152: WHERE mtlb.structure_id = ICX_CAT_UTIL_PVT.g_structure_id
4153: AND (NVL(mtlb.end_date_active, SYSDATE + 1) < SYSDATE
4154: OR NVL(mtlb.disable_date, SYSDATE + 1) < SYSDATE)
4155: AND EXISTS (SELECT 'x' FROM icx_cat_items_ctx_hdrs_tlp hdrs
4156: WHERE hdrs.po_category_id = mtlb.category_id
4157: AND hdrs.source_type = 'MASTER_ITEM');
4158: ELSE
4159: l_err_loc := 300;
4165: AND (NVL(mtlb.end_date_active, SYSDATE + 1) < SYSDATE
4166: OR NVL(mtlb.disable_date, SYSDATE + 1) < SYSDATE)
4167: AND mcsvc.category_set_id = ICX_CAT_UTIL_PVT.g_category_set_id
4168: AND mcsvc.category_id = mtlb.category_id
4169: AND EXISTS (SELECT 'x' FROM icx_cat_items_ctx_hdrs_tlp hdrs
4170: WHERE hdrs.po_category_id = mtlb.category_id
4171: AND hdrs.source_type = 'MASTER_ITEM');
4172: END IF;
4173: l_err_loc := 400;
4244: l_org_id_tbl.DELETE;
4245: l_language_tbl.DELETE;
4246:
4247: l_err_loc := 800;
4248: DELETE FROM icx_cat_items_ctx_hdrs_tlp hdrs
4249: WHERE hdrs.source_type = 'MASTER_ITEM'
4250: AND po_category_id = l_po_category_id_tbl(i)
4251: AND ROWNUM <= ICX_CAT_UTIL_PVT.g_batch_size
4252: RETURNING po_line_id, req_template_name, req_template_line_num,
4391: l_org_id_tbl.DELETE;
4392: l_language_tbl.DELETE;
4393:
4394: l_err_loc := 300;
4395: DELETE FROM icx_cat_items_ctx_hdrs_tlp hdrs
4396: WHERE hdrs.source_type IN ('TEMPLATE', 'INTERNAL_TEMPLATE')
4397: AND (
4398: -- Req template lines that are invalid.
4399: EXISTS ( SELECT 'x'
4540: l_org_id_tbl.DELETE;
4541: l_language_tbl.DELETE;
4542:
4543: l_err_loc := 300;
4544: DELETE FROM icx_cat_items_ctx_hdrs_tlp hdrs
4545: WHERE hdrs.source_type = 'QUOTATION'
4546: AND (
4547: -- Quote lines that are invalid.
4548: ICX_CAT_POPULATE_STATUS_PVT.getQuoteLineStatus(hdrs.po_line_id) = -1
4671: l_org_id_tbl.DELETE;
4672: l_language_tbl.DELETE;
4673:
4674: l_err_loc := 300;
4675: DELETE FROM icx_cat_items_ctx_hdrs_tlp hdrs
4676: WHERE hdrs.source_type IN ('BLANKET', 'GLOBAL_BLANKET')
4677: AND EXISTS ( SELECT 'x' FROM po_lines_all pl, po_headers_all ph
4678: WHERE hdrs.po_line_id = pl.po_line_id
4679: AND pl.po_header_id = ph.po_header_id