[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERT INTO po_session_gt
(index_char1
,num1
,num2
,num3
,num4
,char1
,char2
,char3
,char4
,char5)
VALUES
(g_error_key
,num1
,num2
,num3
,num4
,char1
,char2
,char3
,char4
,char5);
FUNCTION update_num
(
p_version VARCHAR2)
RETURN NUMBER
IS
BEGIN
If ( p_version LIKE '120.%' AND NOT p_version LIKE '120.%.%' )
or p_version LIKE '120.%.%.1' THEN
RETURN 12;
logUnexpectedException (g_pkg_name, 'update_num','Exception sqlerrm'||sqlerrm||' code='||sqlcode);
END update_num;
UPDATE po_session_gt
SET num3 = update_num( g_instance_versions_tbl(i) ) ,
num2 = update_num( g_file_versions_tbl(i) )
WHERE index_char1 =ICX_ITEM_DIAG_PVT.g_file_key
AND char1 = g_file_tbl(i);
-- check if instance file is less than should have version then update apply patch as Y
select char2, char3, num2, num3 into l_shld_ver, l_inst_ver , l_num2,l_num3
FROM po_session_gt
WHERE index_char1 =ICX_ITEM_DIAG_PVT.g_file_key
AND char1 = g_file_tbl(i);
UPDATE po_session_gt set char4 = l_apply_patch
WHERE index_char1 =ICX_ITEM_DIAG_PVT.g_file_key
AND char1 = g_file_tbl(i);
SELECT filename ,
version
FROM
(SELECT filename ,
version
FROM ad_file_versions v ,
ad_files f
WHERE f.file_id = v.file_id
AND app_short_name IN ( 'ICX', 'PO')
AND subdir = 'patch/115/sql'
AND filename = g_file_tbl(i)
ORDER BY file_version_id DESC
)
WHERE rownum = 1
ORDER BY filename;
ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'start of insert '|| i || ' file :' || g_file_tbl(i)||g_file_versions_tbl(i)|| g_instance_versions_tbl(i));
INSERT
INTO po_session_gt
(
index_char1 --'ITEM_DIAG_FILE_VERSIONS'
,
char1 -- pls file name
,
char2 -- should have version
,
char3 -- instance version
)
VALUES
(
ICX_ITEM_DIAG_PVT.g_file_key ,
g_file_tbl(i) ,
g_file_versions_tbl(i) ,
g_instance_versions_tbl(i)
);
SELECT char1 ,
char2 ,
char3,
num1,
num2,
num3
FROM po_session_gt
WHERE index_char1 = ICX_ITEM_DIAG_PVT.g_file_key;
select ORG_ID, org.SHORT_CODE OU_CODE, org.NAME OU_NAME ,
fsp.INVENTORY_ORGANIZATION_ID inventory_ORGANIZATION, org1.ORGANIZATION_CODE INV_ORG_CODE,org1.ORGANIZATION_NAME INV_ORG_NAME
,mparams.MASTER_ORGANIZATION_ID
from financials_system_params_all fsp, hr_operating_units org ,org_organization_definitions org1 ,mtl_parameters mparams
where fsp.org_id =ICX_ITEM_DIAG_PVT.g_org_id
and fsp.org_id =org.ORGANIZATION_ID
and fsp.INVENTORY_ORGANIZATION_ID=org1.ORGANIZATION_ID
AND mparams.organization_id=fsp.INVENTORY_ORGANIZATION_ID;
SELECT functional_area_id
,category_set_id
,validate_flag
,structure_id
FROM mtl_default_sets_view
WHERE functional_area_id = 2;
SELECT inventory_item_id ,organization_id ,segment1 ,internal_order_enabled_flag
,purchasing_enabled_flag ,outside_operation_flag,list_price_per_unit
,rfq_required_flag ,primary_uom_code ,replenish_to_order_flag ,base_item_id
,auto_created_config_flag,nvl( (select 'Not Valid Record' from dual where replenish_to_order_flag = 'Y'
AND base_item_id IS NOT NULL
AND auto_created_config_flag = 'Y'), 'Valid Record') RULE_1_ISVALID ,
nvl( ( select 'Not Valid Record' from dual
where nvl(internal_order_enabled_flag,'N') ='N'
and ( list_price_per_unit is null or nvl(outside_operation_flag,'Y') ='Y' ) ),'Valid Record') RULE_2_ISVALID
FROM mtl_system_items_b
WHERE inventory_item_id = g_source_ids(j)
AND organization_id =nvl(g_organization_id,organization_id) ;
SELECT inventory_item_id,organization_id,language
,source_lang ,description ,long_description
,nvl(( select 'Not Valid Record' from dual
where mtl.language <> mtl.source_lang), 'Valid Record') RULE_1_ISVALID
FROM mtl_system_items_tl mtl
WHERE mtl.inventory_item_id = g_source_ids(j)
AND mtl.organization_id = nvl(g_organization_id,organization_id) ;
select inventory_item_id ,organization_id,category_set_id, mic.category_id PO_CATEGORY, nvl(i.category_key ,-2) category_key,'Validate'
FROM mtl_item_categories mic , icx_por_category_data_sources i
WHERE mic.inventory_item_id = g_source_ids(j)
AND mic.organization_id = nvl(g_organization_id,mic.organization_id)
AND mic.category_set_id = g_category_set_id
and i.external_source(+) = 'Oracle'
and i.external_source_key(+) = TO_CHAR(mic.category_id)
order by mic.inventory_item_id,mic.organization_id;
SELECT mi.inventory_item_id ,mi.organization_id , muom.unit_of_measure,muom.uom_code
FROM mtl_units_of_measure muom,mtl_system_items_b mi
WHERE mi.inventory_item_id = g_source_ids(j)
AND mi.organization_id = nvl(g_organization_id,mi.organization_id)
AND muom.uom_code = mi.primary_uom_code ;
SELECT mtl.inventory_item_id ,mtl.organization_id, i.language,
nvl (i.rt_category_id,- 2) ip_category_id,i.category_name ip_category_name
FROM icx_cat_categories_tl i
,mtl_system_items_tl mtl
WHERE mtl.inventory_item_id = g_source_ids(j)
AND mtl.organization_id = nvl(g_organization_id,mtl.organization_id)
AND i.key = g_category_key(j)
AND i.type = 2
AND i.language = mtl.language;
SELECT inventory_item_id ,po_line_id ,req_template_name
,req_template_line_num ,org_id ,language
,unit_price ,unit_meas_lookup_code ,line_type_id
,document_number ,item_type ,supplier_site_id
,supplier_id ,po_category_id ,ip_category_id
,ip_category_name ,source_type , decode(ip_category_id,-2,'May not be searchable',decode(supplier_id,-2,'May not be searchable',null)) WARNING
FROM icx_cat_items_ctx_hdrs_tlp
WHERE inventory_item_id =g_source_ids(j)
AND org_id = nvl(g_org_id,org_id)
order by language,source_type;
SELECT inventory_item_id ,po_line_id ,req_template_name
,req_template_line_num ,org_id ,language
,SEQUENCE , htf.escape_sc(CTX_DESC)
FROM icx_cat_items_ctx_dtls_tlp
WHERE inventory_item_id =g_source_ids(j)
AND org_id = nvl(g_org_id,org_id)
order by po_line_id ,req_template_name,language , sequence;
select rownum,SQE_OWNER#,SQE_NAME,SQE_QUERY from ctxsys.dr$sqe
where SQE_NAME in (
SELECT UPPER(decode( fnd_profile.value('REQUISITION_TYPE'),'INTERNAL','icxzi','PURCHASE','icxzp','icxzb') || SQE_SEQUENCE)
FROM
ICX_CAT_CONTENT_ZONES_B zoneb,
ICX_CAT_STORE_CONTENTS_V contentv
WHERE
zoneb.ZONE_ID = contentv.CONTENT_ID AND
zoneb.TYPE ='LOCAL' );
select
INVENTORY_ITEM_ID,ORG_ID,PO_LINE_ID,REQ_TEMPLATE_NAME,REQ_TEMPLATE_LINE_NUM,IP_CATEGORY_ID,MANUFACTURER_PART_NUM,LEAD_TIME,PICTURE,THUMBNAIL_IMAGE,SUPPLIER_URL,MANUFACTURER_URL,ATTACHMENT_URL,UNSPSC,AVAILABILITY
from po_attribute_values
WHERE INVENTORY_ITEM_ID =g_source_ids(j)
and org_id = g_org_id;
select INVENTORY_ITEM_ID,ORG_ID,LANGUAGE,PO_LINE_ID,REQ_TEMPLATE_NAME,REQ_TEMPLATE_LINE_NUM,IP_CATEGORY_ID,DESCRIPTION,MANUFACTURER,LONG_DESCRIPTION
from po_attribute_values_tlp
WHERE INVENTORY_ITEM_ID =g_source_ids(j)
and org_id = g_org_id;
select
INVENTORY_ITEM_ID,ORG_ID,PO_LINE_ID,REQ_TEMPLATE_NAME,REQ_TEMPLATE_LINE_NUM,IP_CATEGORY_ID,MANUFACTURER_PART_NUM,LEAD_TIME,PICTURE,THUMBNAIL_IMAGE,SUPPLIER_URL,MANUFACTURER_URL,ATTACHMENT_URL,UNSPSC,AVAILABILITY
from icx_cat_attribute_values
WHERE INVENTORY_ITEM_ID =g_source_ids(j)
and org_id = g_org_id;
select INVENTORY_ITEM_ID,ORG_ID,LANGUAGE,PO_LINE_ID,REQ_TEMPLATE_NAME,REQ_TEMPLATE_LINE_NUM,IP_CATEGORY_ID,DESCRIPTION,MANUFACTURER,LONG_DESCRIPTION
from icx_cat_attribute_values_tlp
WHERE INVENTORY_ITEM_ID =g_source_ids(j)
and org_id = g_org_id;
select index_char1,num1,num2,num3,num4,char1,char2,char3,char4
from po_session_gt
WHERE index_char1 = ICX_ITEM_DIAG_PVT.g_error_key ;
SELECT /*+ LEADING(doc) */
doc.*,
nvl(ic1.rt_category_id, -2) ip_category_id,
ic1.category_name ip_category_name,
ctx.inventory_item_id ctx_inventory_item_id,
ctx.source_type ctx_source_type,
ctx.item_type ctx_item_type,
ctx.purchasing_org_id ctx_purchasing_org_id,
ctx.supplier_id ctx_supplier_id,
ctx.supplier_site_id ctx_supplier_site_id,
ctx.supplier_part_num ctx_supplier_part_num,
ctx.supplier_part_auxid ctx_supplier_part_auxid,
ctx.ip_category_id ctx_ip_category_id,
ctx.po_category_id ctx_po_category_id,
ctx.ip_category_name ctx_ip_category_name,
ROWIDTOCHAR(ctx.rowid) ctx_rowid,
null IS_VALID
FROM
(
SELECT /*+ ROWID(mi) NO_EXPAND use_nl(mitl,mic,catMap) */
mi.inventory_item_id inventory_item_id,
-2 po_line_id,
-2 req_template_name,
-2 req_template_line_num,
NVL(fsp.org_id, -2) org_id,
mitl.language,
'MASTER_ITEM' source_type,
NVL(fsp.org_id, -2) purchasing_org_id,
mic.category_id po_category_id,
catMap.category_key category_key,
mi.internal_order_enabled_flag,
mi.purchasing_enabled_flag,
mi.outside_operation_flag,
muom.unit_of_measure unit_meas_lookup_code,
DECODE(mi.purchasing_enabled_flag, 'Y', mi.list_price_per_unit, to_number(null)) unit_price,
mi.rfq_required_flag,
mitl.description,
mitl.long_description,
mparams.organization_id,
mparams.master_organization_id
FROM mtl_system_items_b mi,
mtl_parameters mparams,
mtl_system_items_tl mitl,
mtl_item_categories mic,
mtl_units_of_measure muom,
financials_system_params_all fsp,
icx_por_category_data_sources catMap
WHERE mi.inventory_item_id = g_source_ids(j)
AND mi.organization_id = mparams.organization_id
AND (mparams.organization_id = nvl(g_organization_id,mparams.organization_id)
OR mparams.master_organization_id =nvl(g_organization_id,mparams.master_organization_id))
AND mi.inventory_item_id = mitl.inventory_item_id
AND mi.organization_id = mitl.organization_id
AND mitl.language = mitl.source_lang
AND mic.inventory_item_id = mi.inventory_item_id
AND mic.organization_id = mi.organization_id
AND mic.category_set_id = 2
AND muom.uom_code = mi.primary_uom_code
AND NOT (mi.replenish_to_order_flag = 'Y'
AND mi.base_item_id IS NOT NULL
AND mi.auto_created_config_flag = 'Y')
AND mi.organization_id = fsp.inventory_organization_id
AND catMap.external_source_key (+) = TO_CHAR(mic.category_id)
AND catMap.external_source (+) = 'Oracle'
) doc,
icx_cat_categories_tl ic1,
icx_cat_items_ctx_hdrs_tlp ctx
WHERE ic1.key (+) = doc.category_key
AND ic1.type (+) = 2
AND ic1.language (+) = doc.language
AND doc.inventory_item_id = ctx.inventory_item_id (+)
AND doc.po_line_id = ctx.po_line_id (+)
AND doc.req_template_name = ctx.req_template_name (+)
AND doc.req_template_line_num = ctx.req_template_line_num (+)
AND doc.org_id = ctx.org_id (+)
AND doc.language = ctx.language (+)
AND doc.source_type = ctx.source_type (+)
order by doc.ORG_ID,doc.LANGUAGE ;
select distinct to_number(CHAR4) from po_session_gt
where INDEX_CHAR1='ITEM_DIAG_ERRORS'
and CHAR1='IP_CATEGORY_MISSING';
select distinct to_number(CHAR4) from po_session_gt
where INDEX_CHAR1='ITEM_DIAG_ERRORS'
and CHAR1=g_error_code(3)
AND CHAR4 NOT IN (select distinct CHAR4 from po_session_gt
where INDEX_CHAR1='ITEM_DIAG_ERRORS'
and CHAR1='IP_CATEGORY_MISSING') ;
select distinct num1, num2 from po_session_gt
where INDEX_CHAR1='ITEM_DIAG_ERRORS'
and CHAR1='ICX_CTX_HDRS_MISSING';
ICX_CAT_POPULATE_CATG_GRP.populateValidCategorySetInsert
( p_api_version =>1.0 ,
p_commit =>FND_API.G_TRUE ,
x_return_status => l_ret ,
p_category_set_id => g_category_set_id ,
p_category_id =>l_po_category_id
);
ICX_CAT_POPULATE_CATG_GRP.populateValidCategorySetInsert
(p_api_version =>1.0 ,
p_commit =>FND_API.G_TRUE ,
x_return_status => l_ret ,
p_category_set_id => g_category_set_id ,
p_category_id =>l_po_category_id
);
SELECT *
FROM icx_cat_items_ctx_hdrs_tlp ctx
WHERE ctx.PO_LINE_ID=-2
AND ctx.REQ_TEMPLATE_NAME='-2'
AND ctx.REQ_TEMPLATE_LINE_NUM = -2
AND ctx.SOURCE_TYPE = 'MASTER_ITEM'
AND NOT EXISTS ( SELECT 'Row Found for inventory item id'
FROM po_attribute_values poav
WHERE poav.INVENTORY_ITEM_ID = ctx.INVENTORY_ITEM_ID
AND poav.ORG_ID = ctx.ORG_ID
AND poav.PO_LINE_ID=-2
AND poav.REQ_TEMPLATE_NAME='-2'
AND poav.REQ_TEMPLATE_LINE_NUM = -2)
ORDER BY INVENTORY_ITEM_ID;
SELECT organization_id, master_organization_id
INTO l_organization_id,l_master_organization_id
FROM(
SELECT mparams.organization_id organization_id, mparams.master_organization_id master_organization_id
FROM mtl_parameters mparams,financials_system_params_all fsp, mtl_system_items_b mtlb
WHERE fsp.ORG_ID = rec_MI.ORG_ID
AND mtlb.inventory_item_id = rec_MI.inventory_item_id
AND mtlb.organization_id = mparams.organization_id
AND (mparams.organization_id = fsp.INVENTORY_ORGANIZATION_ID
OR mparams.master_organization_id = fsp.INVENTORY_ORGANIZATION_ID)
) WHERE ROWNUM =1;
SELECT 1
INTO l_dummy
FROM mtl_system_items_b
WHERE inventory_item_id = rec_MI.INVENTORY_ITEM_ID
AND organization_id = l_organization_id;
SELECT LONG_DESCRIPTION
INTO l_long_description
FROM MTL_SYSTEM_ITEMS_TL
WHERE inventory_item_id = rec_MI.INVENTORY_ITEM_ID
AND organization_id = l_master_organization_id
AND LANGUAGE = rec_MI.LANGUAGE;
USING (SELECT *
FROM po_attribute_values
WHERE inventory_item_id = rec_MI.INVENTORY_ITEM_ID
AND po_line_id = -2
AND req_template_name = '-2'
AND req_template_line_num = -2
AND org_id = rec_MI.ORG_ID) temp
ON (icav.inventory_item_id = temp.inventory_item_id AND
icav.po_line_id = temp.po_line_id AND
icav.req_template_name = temp.req_template_name AND
icav.req_template_line_num = temp.req_template_line_num AND
icav.org_id = temp.org_id)
WHEN NOT MATCHED THEN INSERT VALUES (
temp.attribute_values_id, temp.po_line_id, temp.req_template_name,
temp.req_template_line_num, temp.ip_category_id, temp.inventory_item_id,
temp.org_id, temp.manufacturer_part_num, temp.picture, temp.thumbnail_image,
temp.supplier_url, temp.manufacturer_url, temp.attachment_url, temp.unspsc,
temp.availability, temp.lead_time,
temp.text_base_attribute1, temp.text_base_attribute2, temp.text_base_attribute3,
temp.text_base_attribute4, temp.text_base_attribute5, temp.text_base_attribute6,
temp.text_base_attribute7, temp.text_base_attribute8, temp.text_base_attribute9,
temp.text_base_attribute10, temp.text_base_attribute11, temp.text_base_attribute12,
temp.text_base_attribute13, temp.text_base_attribute14, temp.text_base_attribute15,
temp.text_base_attribute16, temp.text_base_attribute17, temp.text_base_attribute18,
temp.text_base_attribute19, temp.text_base_attribute20, temp.text_base_attribute21,
temp.text_base_attribute22, temp.text_base_attribute23, temp.text_base_attribute24,
temp.text_base_attribute25, temp.text_base_attribute26, temp.text_base_attribute27,
temp.text_base_attribute28, temp.text_base_attribute29, temp.text_base_attribute30,
temp.text_base_attribute31, temp.text_base_attribute32, temp.text_base_attribute33,
temp.text_base_attribute34, temp.text_base_attribute35, temp.text_base_attribute36,
temp.text_base_attribute37, temp.text_base_attribute38, temp.text_base_attribute39,
temp.text_base_attribute40, temp.text_base_attribute41, temp.text_base_attribute42,
temp.text_base_attribute43, temp.text_base_attribute44, temp.text_base_attribute45,
temp.text_base_attribute46, temp.text_base_attribute47, temp.text_base_attribute48,
temp.text_base_attribute49, temp.text_base_attribute50, temp.text_base_attribute51,
temp.text_base_attribute52, temp.text_base_attribute53, temp.text_base_attribute54,
temp.text_base_attribute55, temp.text_base_attribute56, temp.text_base_attribute57,
temp.text_base_attribute58, temp.text_base_attribute59, temp.text_base_attribute60,
temp.text_base_attribute61, temp.text_base_attribute62, temp.text_base_attribute63,
temp.text_base_attribute64, temp.text_base_attribute65, temp.text_base_attribute66,
temp.text_base_attribute67, temp.text_base_attribute68, temp.text_base_attribute69,
temp.text_base_attribute70, temp.text_base_attribute71, temp.text_base_attribute72,
temp.text_base_attribute73, temp.text_base_attribute74, temp.text_base_attribute75,
temp.text_base_attribute76, temp.text_base_attribute77, temp.text_base_attribute78,
temp.text_base_attribute79, temp.text_base_attribute80, temp.text_base_attribute81,
temp.text_base_attribute82, temp.text_base_attribute83, temp.text_base_attribute84,
temp.text_base_attribute85, temp.text_base_attribute86, temp.text_base_attribute87,
temp.text_base_attribute88, temp.text_base_attribute89, temp.text_base_attribute90,
temp.text_base_attribute91, temp.text_base_attribute92, temp.text_base_attribute93,
temp.text_base_attribute94, temp.text_base_attribute95, temp.text_base_attribute96,
temp.text_base_attribute97, temp.text_base_attribute98, temp.text_base_attribute99,
temp.text_base_attribute100,
temp.num_base_attribute1, temp.num_base_attribute2, temp.num_base_attribute3,
temp.num_base_attribute4, temp.num_base_attribute5, temp.num_base_attribute6,
temp.num_base_attribute7, temp.num_base_attribute8, temp.num_base_attribute9,
temp.num_base_attribute10, temp.num_base_attribute11, temp.num_base_attribute12,
temp.num_base_attribute13, temp.num_base_attribute14, temp.num_base_attribute15,
temp.num_base_attribute16, temp.num_base_attribute17, temp.num_base_attribute18,
temp.num_base_attribute19, temp.num_base_attribute20, temp.num_base_attribute21,
temp.num_base_attribute22, temp.num_base_attribute23, temp.num_base_attribute24,
temp.num_base_attribute25, temp.num_base_attribute26, temp.num_base_attribute27,
temp.num_base_attribute28, temp.num_base_attribute29, temp.num_base_attribute30,
temp.num_base_attribute31, temp.num_base_attribute32, temp.num_base_attribute33,
temp.num_base_attribute34, temp.num_base_attribute35, temp.num_base_attribute36,
temp.num_base_attribute37, temp.num_base_attribute38, temp.num_base_attribute39,
temp.num_base_attribute40, temp.num_base_attribute41, temp.num_base_attribute42,
temp.num_base_attribute43, temp.num_base_attribute44, temp.num_base_attribute45,
temp.num_base_attribute46, temp.num_base_attribute47, temp.num_base_attribute48,
temp.num_base_attribute49, temp.num_base_attribute50, temp.num_base_attribute51,
temp.num_base_attribute52, temp.num_base_attribute53, temp.num_base_attribute54,
temp.num_base_attribute55, temp.num_base_attribute56, temp.num_base_attribute57,
temp.num_base_attribute58, temp.num_base_attribute59, temp.num_base_attribute60,
temp.num_base_attribute61, temp.num_base_attribute62, temp.num_base_attribute63,
temp.num_base_attribute64, temp.num_base_attribute65, temp.num_base_attribute66,
temp.num_base_attribute67, temp.num_base_attribute68, temp.num_base_attribute69,
temp.num_base_attribute70, temp.num_base_attribute71, temp.num_base_attribute72,
temp.num_base_attribute73, temp.num_base_attribute74, temp.num_base_attribute75,
temp.num_base_attribute76, temp.num_base_attribute77, temp.num_base_attribute78,
temp.num_base_attribute79, temp.num_base_attribute80, temp.num_base_attribute81,
temp.num_base_attribute82, temp.num_base_attribute83, temp.num_base_attribute84,
temp.num_base_attribute85, temp.num_base_attribute86, temp.num_base_attribute87,
temp.num_base_attribute88, temp.num_base_attribute89, temp.num_base_attribute90,
temp.num_base_attribute91, temp.num_base_attribute92, temp.num_base_attribute93,
temp.num_base_attribute94, temp.num_base_attribute95, temp.num_base_attribute96,
temp.num_base_attribute97, temp.num_base_attribute98, temp.num_base_attribute99,
temp.num_base_attribute100,
temp.text_cat_attribute1, temp.text_cat_attribute2, temp.text_cat_attribute3,
temp.text_cat_attribute4, temp.text_cat_attribute5, temp.text_cat_attribute6,
temp.text_cat_attribute7, temp.text_cat_attribute8, temp.text_cat_attribute9,
temp.text_cat_attribute10, temp.text_cat_attribute11, temp.text_cat_attribute12,
temp.text_cat_attribute13, temp.text_cat_attribute14, temp.text_cat_attribute15,
temp.text_cat_attribute16, temp.text_cat_attribute17, temp.text_cat_attribute18,
temp.text_cat_attribute19, temp.text_cat_attribute20, temp.text_cat_attribute21,
temp.text_cat_attribute22, temp.text_cat_attribute23, temp.text_cat_attribute24,
temp.text_cat_attribute25, temp.text_cat_attribute26, temp.text_cat_attribute27,
temp.text_cat_attribute28, temp.text_cat_attribute29, temp.text_cat_attribute30,
temp.text_cat_attribute31, temp.text_cat_attribute32, temp.text_cat_attribute33,
temp.text_cat_attribute34, temp.text_cat_attribute35, temp.text_cat_attribute36,
temp.text_cat_attribute37, temp.text_cat_attribute38, temp.text_cat_attribute39,
temp.text_cat_attribute40, temp.text_cat_attribute41, temp.text_cat_attribute42,
temp.text_cat_attribute43, temp.text_cat_attribute44, temp.text_cat_attribute45,
temp.text_cat_attribute46, temp.text_cat_attribute47, temp.text_cat_attribute48,
temp.text_cat_attribute49, temp.text_cat_attribute50,
temp.num_cat_attribute1, temp.num_cat_attribute2, temp.num_cat_attribute3,
temp.num_cat_attribute4, temp.num_cat_attribute5, temp.num_cat_attribute6,
temp.num_cat_attribute7, temp.num_cat_attribute8, temp.num_cat_attribute9,
temp.num_cat_attribute10, temp.num_cat_attribute11, temp.num_cat_attribute12,
temp.num_cat_attribute13, temp.num_cat_attribute14, temp.num_cat_attribute15,
temp.num_cat_attribute16, temp.num_cat_attribute17, temp.num_cat_attribute18,
temp.num_cat_attribute19, temp.num_cat_attribute20, temp.num_cat_attribute21,
temp.num_cat_attribute22, temp.num_cat_attribute23, temp.num_cat_attribute24,
temp.num_cat_attribute25, temp.num_cat_attribute26, temp.num_cat_attribute27,
temp.num_cat_attribute28, temp.num_cat_attribute29, temp.num_cat_attribute30,
temp.num_cat_attribute31, temp.num_cat_attribute32, temp.num_cat_attribute33,
temp.num_cat_attribute34, temp.num_cat_attribute35, temp.num_cat_attribute36,
temp.num_cat_attribute37, temp.num_cat_attribute38, temp.num_cat_attribute39,
temp.num_cat_attribute40, temp.num_cat_attribute41, temp.num_cat_attribute42,
temp.num_cat_attribute43, temp.num_cat_attribute44, temp.num_cat_attribute45,
temp.num_cat_attribute46, temp.num_cat_attribute47, temp.num_cat_attribute48,
temp.num_cat_attribute49, temp.num_cat_attribute50,
temp.last_update_login, temp.last_updated_by, temp.last_update_date, temp.created_by,
temp.creation_date, temp.request_id, temp.program_application_id, temp.program_id,
temp.program_update_date, temp.last_updated_program, temp.rebuild_search_index_flag);
logStatement(g_pkg_name, l_api_name ,'Num. of rows inserted into icx_cat_attribute_values:' ||SQL%ROWCOUNT);
USING (SELECT *
FROM po_attribute_values_tlp
WHERE inventory_item_id = rec_MI.INVENTORY_ITEM_ID
AND po_line_id = -2
AND req_template_name = '-2'
AND req_template_line_num = -2
AND org_id = rec_MI.ORG_ID
AND language = rec_MI.LANGUAGE ) temp
ON (icavt.inventory_item_id = temp.inventory_item_id AND
icavt.po_line_id = temp.po_line_id AND
icavt.req_template_name = temp.req_template_name AND
icavt.req_template_line_num = temp.req_template_line_num AND
icavt.org_id = temp.org_id AND
icavt.language = temp.language)
WHEN NOT MATCHED THEN INSERT VALUES (
temp.attribute_values_tlp_id, temp.po_line_id, temp.req_template_name,
temp.req_template_line_num, temp.ip_category_id, temp.inventory_item_id,
temp.org_id, temp.language, temp.description, temp.manufacturer,
temp.comments, temp.alias, temp.long_description,
temp.tl_text_base_attribute1, temp.tl_text_base_attribute2, temp.tl_text_base_attribute3,
temp.tl_text_base_attribute4, temp.tl_text_base_attribute5, temp.tl_text_base_attribute6,
temp.tl_text_base_attribute7, temp.tl_text_base_attribute8, temp.tl_text_base_attribute9,
temp.tl_text_base_attribute10, temp.tl_text_base_attribute11, temp.tl_text_base_attribute12,
temp.tl_text_base_attribute13, temp.tl_text_base_attribute14, temp.tl_text_base_attribute15,
temp.tl_text_base_attribute16, temp.tl_text_base_attribute17, temp.tl_text_base_attribute18,
temp.tl_text_base_attribute19, temp.tl_text_base_attribute20, temp.tl_text_base_attribute21,
temp.tl_text_base_attribute22, temp.tl_text_base_attribute23, temp.tl_text_base_attribute24,
temp.tl_text_base_attribute25, temp.tl_text_base_attribute26, temp.tl_text_base_attribute27,
temp.tl_text_base_attribute28, temp.tl_text_base_attribute29, temp.tl_text_base_attribute30,
temp.tl_text_base_attribute31, temp.tl_text_base_attribute32, temp.tl_text_base_attribute33,
temp.tl_text_base_attribute34, temp.tl_text_base_attribute35, temp.tl_text_base_attribute36,
temp.tl_text_base_attribute37, temp.tl_text_base_attribute38, temp.tl_text_base_attribute39,
temp.tl_text_base_attribute40, temp.tl_text_base_attribute41, temp.tl_text_base_attribute42,
temp.tl_text_base_attribute43, temp.tl_text_base_attribute44, temp.tl_text_base_attribute45,
temp.tl_text_base_attribute46, temp.tl_text_base_attribute47, temp.tl_text_base_attribute48,
temp.tl_text_base_attribute49, temp.tl_text_base_attribute50, temp.tl_text_base_attribute51,
temp.tl_text_base_attribute52, temp.tl_text_base_attribute53, temp.tl_text_base_attribute54,
temp.tl_text_base_attribute55, temp.tl_text_base_attribute56, temp.tl_text_base_attribute57,
temp.tl_text_base_attribute58, temp.tl_text_base_attribute59, temp.tl_text_base_attribute60,
temp.tl_text_base_attribute61, temp.tl_text_base_attribute62, temp.tl_text_base_attribute63,
temp.tl_text_base_attribute64, temp.tl_text_base_attribute65, temp.tl_text_base_attribute66,
temp.tl_text_base_attribute67, temp.tl_text_base_attribute68, temp.tl_text_base_attribute69,
temp.tl_text_base_attribute70, temp.tl_text_base_attribute71, temp.tl_text_base_attribute72,
temp.tl_text_base_attribute73, temp.tl_text_base_attribute74, temp.tl_text_base_attribute75,
temp.tl_text_base_attribute76, temp.tl_text_base_attribute77, temp.tl_text_base_attribute78,
temp.tl_text_base_attribute79, temp.tl_text_base_attribute80, temp.tl_text_base_attribute81,
temp.tl_text_base_attribute82, temp.tl_text_base_attribute83, temp.tl_text_base_attribute84,
temp.tl_text_base_attribute85, temp.tl_text_base_attribute86, temp.tl_text_base_attribute87,
temp.tl_text_base_attribute88, temp.tl_text_base_attribute89, temp.tl_text_base_attribute90,
temp.tl_text_base_attribute91, temp.tl_text_base_attribute92, temp.tl_text_base_attribute93,
temp.tl_text_base_attribute94, temp.tl_text_base_attribute95, temp.tl_text_base_attribute96,
temp.tl_text_base_attribute97, temp.tl_text_base_attribute98, temp.tl_text_base_attribute99,
temp.tl_text_base_attribute100,
temp.tl_text_cat_attribute1, temp.tl_text_cat_attribute2, temp.tl_text_cat_attribute3,
temp.tl_text_cat_attribute4, temp.tl_text_cat_attribute5, temp.tl_text_cat_attribute6,
temp.tl_text_cat_attribute7, temp.tl_text_cat_attribute8, temp.tl_text_cat_attribute9,
temp.tl_text_cat_attribute10, temp.tl_text_cat_attribute11, temp.tl_text_cat_attribute12,
temp.tl_text_cat_attribute13, temp.tl_text_cat_attribute14, temp.tl_text_cat_attribute15,
temp.tl_text_cat_attribute16, temp.tl_text_cat_attribute17, temp.tl_text_cat_attribute18,
temp.tl_text_cat_attribute19, temp.tl_text_cat_attribute20, temp.tl_text_cat_attribute21,
temp.tl_text_cat_attribute22, temp.tl_text_cat_attribute23, temp.tl_text_cat_attribute24,
temp.tl_text_cat_attribute25, temp.tl_text_cat_attribute26, temp.tl_text_cat_attribute27,
temp.tl_text_cat_attribute28, temp.tl_text_cat_attribute29, temp.tl_text_cat_attribute30,
temp.tl_text_cat_attribute31, temp.tl_text_cat_attribute32, temp.tl_text_cat_attribute33,
temp.tl_text_cat_attribute34, temp.tl_text_cat_attribute35, temp.tl_text_cat_attribute36,
temp.tl_text_cat_attribute37, temp.tl_text_cat_attribute38, temp.tl_text_cat_attribute39,
temp.tl_text_cat_attribute40, temp.tl_text_cat_attribute41, temp.tl_text_cat_attribute42,
temp.tl_text_cat_attribute43, temp.tl_text_cat_attribute44, temp.tl_text_cat_attribute45,
temp.tl_text_cat_attribute46, temp.tl_text_cat_attribute47, temp.tl_text_cat_attribute48,
temp.tl_text_cat_attribute49, temp.tl_text_cat_attribute50,
temp.last_update_login, temp.last_updated_by, temp.last_update_date, temp.created_by,
temp.creation_date, temp.request_id, temp.program_application_id, temp.program_id,
temp.program_update_date, temp.last_updated_program, temp.rebuild_search_index_flag);
logStatement(g_pkg_name, l_api_name ,'Num. of rows inserted into icx_cat_attribute_values_tlp:' ||SQL%ROWCOUNT);
select distinct inventory_item_id,organization_id from ( SELECT /*+ LEADING(doc) */
doc.*,
nvl(ic1.rt_category_id, -2) ip_category_id,
ic1.category_name ip_category_name,
ctx.inventory_item_id ctx_inventory_item_id,
ctx.source_type ctx_source_type,
ctx.item_type ctx_item_type,
ctx.purchasing_org_id ctx_purchasing_org_id,
ctx.supplier_id ctx_supplier_id,
ctx.supplier_site_id ctx_supplier_site_id,
ctx.supplier_part_num ctx_supplier_part_num,
ctx.supplier_part_auxid ctx_supplier_part_auxid,
ctx.ip_category_id ctx_ip_category_id,
ctx.po_category_id ctx_po_category_id,
ctx.ip_category_name ctx_ip_category_name,
ctx.unit_price ctx_unit_price,
ROWIDTOCHAR(ctx.rowid) ctx_rowid
FROM
(
SELECT /*+ ROWID(mi) NO_EXPAND use_nl(mitl,mic,catMap) */
mi.inventory_item_id inventory_item_id,
-2 po_line_id,
'-2' req_template_name,
-2 req_template_line_num,
NVL(fsp.org_id, -2) org_id,
mitl.language,
'MASTER_ITEM' source_type,
NVL(fsp.org_id, -2) purchasing_org_id,
mic.category_id po_category_id,
catMap.category_key category_key,
mi.internal_order_enabled_flag,
mi.purchasing_enabled_flag,
mi.outside_operation_flag,
muom.unit_of_measure unit_meas_lookup_code,
DECODE(mi.purchasing_enabled_flag, 'Y', mi.list_price_per_unit, to_number(null)) unit_price,
mi.rfq_required_flag,
mitl.description,
mitl.long_description,
mparams.organization_id,
mparams.master_organization_id
FROM mtl_system_items_b mi,
mtl_parameters mparams,
mtl_system_items_tl mitl,
mtl_item_categories mic,
mtl_units_of_measure muom,
financials_system_params_all fsp,
icx_por_category_data_sources catMap
WHERE
mi.organization_id = mparams.organization_id
AND (mparams.organization_id = nvl(p_org_id,mparams.organization_id)
OR mparams.master_organization_id = nvl(p_org_id,mparams.master_organization_id))
AND mi.inventory_item_id = mitl.inventory_item_id
AND mi.organization_id = mitl.organization_id
AND mitl.language = mitl.source_lang
AND mic.inventory_item_id = mi.inventory_item_id
AND mic.organization_id = mi.organization_id
AND mic.category_set_id = 2
AND muom.uom_code = mi.primary_uom_code
AND NOT (mi.replenish_to_order_flag = 'Y'
AND mi.base_item_id IS NOT NULL
AND mi.auto_created_config_flag = 'Y')
AND mi.organization_id = fsp.inventory_organization_id
AND catMap.external_source_key (+) = TO_CHAR(mic.category_id)
AND catMap.external_source (+) = 'Oracle'
) doc,
icx_cat_categories_tl ic1,
icx_cat_items_ctx_hdrs_tlp ctx
WHERE ic1.key (+) = doc.category_key
AND ic1.type (+) = 2
AND ic1.language (+) = doc.language
AND doc.inventory_item_id = ctx.inventory_item_id (+)
AND doc.po_line_id = ctx.po_line_id (+)
AND doc.req_template_name = ctx.req_template_name (+)
AND doc.req_template_line_num = ctx.req_template_line_num (+)
AND doc.org_id = ctx.org_id (+)
AND doc.language = ctx.language (+)
AND doc.source_type = ctx.source_type (+))
where ip_category_id <> ctx_ip_category_id
or unit_price <> ctx_unit_price
or ctx_inventory_item_id <> inventory_item_id ;
update po_headers_all set last_update_date = sysdate
where po_header_id in ( select distinct pol.po_header_id from po_attribute_values_tlp po , icx_cat_items_ctx_hdrs_tlp ctx, po_lines_all pol
where po.po_line_id=ctx.po_line_id
and po.po_line_id=pol.po_line_id
and ctx.source_type in ('BLANKET','QUOTATION','GLOBAL_BLANKET')
and (po.ip_category_id <> ctx.ip_category_id or pol.unit_price <> ctx.unit_price)
);
select min(rowid), max(rowid) into l_min_row_id,l_max_row_id from po_headers_all;