[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
** Synopsis : To delete the existing data from
** upgraded tables
**/
PROCEDURE cleanup_tables
IS
l_log_string varchar2(400);
SELECT iso_territory, language_code, iso_language,nls_territory,nls_language BULK COLLECT INTO
l_iso_territory_tbl,
l_language_code_tbl,
l_iso_language_tbl,
l_nls_territory_tbl,
l_nls_language_tbl
FROM fnd_languages
WHERE installed_flag in ('B','I');
SELECT distinct pohi.interface_header_id,
pohi.vendor_id,
pohi.vendor_site_id,
pohi.org_id,
pohi.currency_code,
pohi.cpa_reference,
poai.language
FROM po_headers_interface pohi,
po_attr_values_tlp_interface poai
WHERE pohi.batch_id = p_batch_id
AND poai.interface_header_id = pohi.interface_header_id
AND EXISTS (SELECT 1
FROM po_interface_errors poie
WHERE poie.interface_header_id = pohi.interface_header_id
-- to retireve only those languages for which
-- corresponding lines has errors
AND (poie.interface_line_id IS NULL OR
poie.interface_line_id = poai.interface_line_id))
AND NOT EXISTS (SELECT 1
FROM icx_cat_r12_upg_excep_files
WHERE interface_header_id = pohi.interface_header_id
AND language = poai.language)
ORDER BY pohi.interface_header_id;
l_interface_header_id_tbl.DELETE;
l_vendor_id_tbl.DELETE;
l_vendor_site_id_tbl.DELETE;
l_org_id_tbl.DELETE;
l_currency_code_tbl.DELETE;
l_contract_num_tbl.DELETE;
l_language_tbl.DELETE;
INSERT INTO icx_cat_r12_upg_excep_files (interface_header_id,
vendor_id,
vendor_site_id,
org_id,
currency_code,
contract_num,
language,
data_file,
creation_date,
created_by,
last_update_date,
last_updated_by)
VALUES (l_interface_header_id_tbl(headers_index),
l_vendor_id_tbl(headers_index),
l_vendor_site_id_tbl(headers_index),
l_org_id_tbl(headers_index),
l_currency_code_tbl(headers_index),
l_contract_num_tbl(headers_index),
l_language_tbl(headers_index),
EMPTY_CLOB(),
SYSDATE,
UPGRADE_USER_ID,
SYSDATE,
UPGRADE_USER_ID);
DELETE FROM icx_cat_r12_upg_excep_files
WHERE file_name is null;
SELECT distinct poli.ip_category_id
FROM po_lines_interface poli
WHERE poli.interface_header_id = p_interface_header_id
AND EXISTS (SELECT 1
FROM po_interface_errors poie
WHERE poie.interface_header_id = poli.interface_header_id
AND (poie.interface_line_id IS NULL OR
poie.interface_line_id = poli.interface_line_id))
AND EXISTS (SELECT 1
FROM po_attr_values_tlp_interface poai
WHERE poai.interface_header_id = poli.interface_header_id
AND poai.interface_line_id = poli.interface_line_id
AND poai.language = p_language);
l_ip_category_id_tbl.DELETE;
UPDATE icx_cat_r12_upg_excep_files
SET file_name = l_file_name,
data_file = l_headerXML
WHERE interface_header_id = l_interface_header_id
AND language = l_language;
SELECT error_message_name,
LTRIM(replace(MAX(SYS_CONNECT_BY_PATH(column_value, '**R12MDIGREPL**'))
KEEP (DENSE_RANK LAST ORDER BY curr),'**R12MDIGREPL**', ','),',') AS token_value
FROM (SELECT error_message_name,
column_value,
ROW_NUMBER() OVER (PARTITION BY error_message_name ORDER BY column_value) AS curr,
ROW_NUMBER() OVER (PARTITION BY error_message_name ORDER BY column_value) -1 AS prev
FROM po_interface_errors
WHERE interface_header_id = p_interface_header_id
AND error_message_name <> 'ICX_CAT_UPG_ALL_LINES_FAILED'
GROUP BY error_message_name, column_value)
GROUP BY error_message_name
CONNECT BY prev = PRIOR curr AND error_message_name = PRIOR error_message_name
START WITH curr = 1;
l_log_string := 'populate_upg_error_msgs:'||'Inserting messages for interface header id'
||l_interface_header_id|| 'l_progress:' ||l_progress;
INSERT INTO icx_cat_r12_upg_error_msgs(interface_header_id,
error_message_name,
token_value,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
VALUES (p_interface_header_id_tbl(header_id_index),
l_po_msg_tbl(i),
l_column_value_tbl(i),
SYSDATE,
UPGRADE_USER_ID,
SYSDATE,
UPGRADE_USER_ID,
NULL);
l_qryString := 'SELECT XMLConcat(
XMLElement("ADMIN",
XMLElement("NAME", ''CATALOG EXT''),
XMLElement("INFORMATION",
XMLElement("SOURCE", ''RELEASE 12 UPGRADE''),
XMLElement("DATE", sysdate))),
XMLElement("DATA",
XMLElement("DOCUMENT",
XMLAttributes(''GBPA'' as "type"),
XMLElement("HEADER",
XMLElement("DOCUMENT_NUMBER", pohi.document_num),
XMLElement("OPERATING_UNIT",
XMLElement("OWNING_ORG", hro.name)),
XMLElement("SUPPLIER_NAME", pv.vendor_name),
XMLElement("SUPPLIER_SITE", pvs.vendor_site_Code),
XMLElement("CURRENCY", pohi.currency_code)),
XMLElement("LINES", ''R12_ITEM_DET'')))) AS CATALOG
FROM po_headers_interface pohi, HR_ALL_ORGANIZATION_UNITS hro,
po_vendors pv, po_vendor_sites_all pvs
WHERE pohi.interface_header_id = :INTERFACE_HEADER_ID
AND pohi.org_id = hro.organization_id (+)
AND pohi.vendor_id = pv.vendor_id (+)
AND pohi.vendor_site_id = pvs.vendor_site_id (+)';
SELECT distinct icat.key
INTO l_shopping_category
FROM icx_cat_categories_tl icat
WHERE icat.rt_category_id = p_category_id;
'SELECT XMLAgg(
XMLElement("ITEM",
XMLAttributes(''SYNC'' as "action"),
XMLElement("SHOPPING_CATEGORY", :SHOPPING_CAT),
XMLELEMENT("SUPPLIER_PART_NUM", nvl(to_char(poli.vendor_product_num), '' '')),
XMLElement("SUPPLIER_PART_AUXID", nvl(decode (poli.supplier_part_auxid,''##NULL##'','' '',
poli.supplier_part_auxid),'' '')),
XMLElement("DMIG_NVP", '|| g_descriptors_list(0) ||'),
'|| l_att_list_qry ||'
XMLElement("PRICE",
XMLAttributes(nvl(poli.negotiated_by_preparer_flag, '' '') as "negotiated"),
XMLElement("UNIT_PRICE", poli.unit_price),
XMLElement("UNIT_OF_MEASURE", poli.uom_code)
))) AS ITEMS_INF
FROM po_lines_interface poli, po_attr_values_tlp_interface poavti, po_attr_values_interface poavi
WHERE poli.interface_header_id = :INTERFACE_HEADER_ID
AND poli.ip_category_id = :IP_CATEGORY_ID
AND poavi.interface_header_id = poli.interface_header_id
AND poavi.interface_line_id = poli.interface_line_id
AND poavti.interface_header_id = poli.interface_header_id
AND poavti.interface_line_id = poli.interface_line_id
AND poavti.language = :LANGUAGE
AND EXISTS (SELECT 1 FROM po_interface_errors poie
WHERE poie.interface_header_id = poli.interface_header_id
AND (poie.interface_line_id IS NULL OR
poie.interface_line_id = poli.interface_line_id))';
SELECT distinct replace(key, '''', ''''''),
decode(stored_in_table,
'PO_ATTRIBUTE_VALUES', 'poavi',
'PO_ATTRIBUTE_VALUES_TLP', 'poavti')
stored_in_table,
stored_in_column
BULK COLLECT INTO l_descriptors_tbl
FROM icx_cat_attributes_tl
WHERE rt_category_id = p_category_id
AND stored_in_Table is not null
AND stored_in_column is not null;