[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_onlineUpdate_mode VARCHAR2(15) := 'OnlineUpdate';
g_bulkUpdate_mode VARCHAR2(15) := 'BulkUpdate';
g_catgItemUpdate_mode VARCHAR2(15) := 'CatgItemUpdate';
PROCEDURE openOnlineItemCatgDeleteCursor
( P_INVENTORY_ITEM_ID IN NUMBER ,
P_ORGANIZATION_ID IN NUMBER
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'openOnlineItemCatgDeleteCursor';
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
FROM
(
SELECT /*+ ROWID(mi) NO_EXPAND use_nl(mitl,mic,catMap) */
mi.inventory_item_id inventory_item_id,
TO_NUMBER(ICX_CAT_UTIL_PVT.g_NULL_NUMBER) po_line_id,
TO_CHAR(ICX_CAT_UTIL_PVT.g_NULL_NUMBER) req_template_name,
TO_NUMBER(ICX_CAT_UTIL_PVT.g_NULL_NUMBER) 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 = p_inventory_item_id
AND mi.organization_id = mparams.organization_id
AND (mparams.organization_id = p_organization_id
OR mparams.master_organization_id = p_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 (+) = ICX_CAT_UTIL_PVT.g_category_set_id
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 (+) ;
END openOnlineItemCatgDeleteCursor;
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
FROM
(
SELECT /*+ ROWID(mi) NO_EXPAND use_nl(mitl,mic,catMap) */
mi.inventory_item_id inventory_item_id,
TO_NUMBER(ICX_CAT_UTIL_PVT.g_NULL_NUMBER) po_line_id,
TO_CHAR(ICX_CAT_UTIL_PVT.g_NULL_NUMBER) req_template_name,
TO_NUMBER(ICX_CAT_UTIL_PVT.g_NULL_NUMBER) 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 = p_inventory_item_id
AND mi.organization_id = mparams.organization_id
AND (mparams.organization_id = p_organization_id
OR mparams.master_organization_id = p_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 = ICX_CAT_UTIL_PVT.g_category_set_id
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 (+) ;
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
FROM
(
SELECT /*+ ROWID(mi) NO_EXPAND use_nl(mitl,mic,catMap) */
mi.inventory_item_id inventory_item_id,
TO_NUMBER(ICX_CAT_UTIL_PVT.g_NULL_NUMBER) po_line_id,
TO_CHAR(ICX_CAT_UTIL_PVT.g_NULL_NUMBER) req_template_name,
TO_NUMBER(ICX_CAT_UTIL_PVT.g_NULL_NUMBER) 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_item_bulkload_recs mbulk,
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 mbulk.request_id = p_request_id
AND mbulk.entity_type = p_entity_type
AND mbulk.inventory_item_id >= p_inventory_item_id
AND mbulk.inventory_item_id = mi.inventory_item_id
AND mparams.organization_id = mi.organization_id
AND (mbulk.organization_id = mparams.organization_id OR mbulk.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 = ICX_CAT_UTIL_PVT.g_category_set_id
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.inventory_item_id;
SELECT * 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,
ROWIDTOCHAR(ctx.rowid) ctx_rowid
FROM
(
SELECT /*+ ROWID(mi) NO_EXPAND use_nl(mitl,mic,catMap) */
mi.inventory_item_id inventory_item_id,
TO_NUMBER(ICX_CAT_UTIL_PVT.g_NULL_NUMBER) po_line_id,
TO_CHAR(ICX_CAT_UTIL_PVT.g_NULL_NUMBER) req_template_name,
TO_NUMBER(ICX_CAT_UTIL_PVT.g_NULL_NUMBER) 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_item_bulkload_recs mbulk,
mtl_system_items_tl mitl,
mtl_item_categories mic,
mtl_units_of_measure muom,
mtl_category_sets msets,
financials_system_params_all fsp,
icx_por_category_data_sources catMap
WHERE mbulk.request_id = p_request_id
AND mbulk.entity_type = p_entity_type
AND mbulk.inventory_item_id >= p_inventory_item_id
AND mbulk.inventory_item_id = mi.inventory_item_id
AND mbulk.category_set_id = msets.category_set_id
AND msets.control_level = 1 -- Controlled at Master org level
AND mbulk.organization_id = mparams.master_organization_id
AND mparams.organization_id = mi.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 = ICX_CAT_UTIL_PVT.g_category_set_id
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 (+)
UNION ALL
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
FROM
(
SELECT /*+ ROWID(mi) NO_EXPAND use_nl(mitl,mic,catMap) */
mi.inventory_item_id inventory_item_id,
TO_NUMBER(ICX_CAT_UTIL_PVT.g_NULL_NUMBER) po_line_id,
TO_CHAR(ICX_CAT_UTIL_PVT.g_NULL_NUMBER) req_template_name,
TO_NUMBER(ICX_CAT_UTIL_PVT.g_NULL_NUMBER) 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_item_bulkload_recs mbulk,
mtl_system_items_tl mitl,
mtl_item_categories mic,
mtl_units_of_measure muom,
mtl_category_sets msets,
financials_system_params_all fsp,
icx_por_category_data_sources catMap
WHERE mbulk.request_id = p_request_id
AND mbulk.entity_type = p_entity_type
AND mbulk.inventory_item_id >= p_inventory_item_id
AND mbulk.inventory_item_id = mi.inventory_item_id
AND mbulk.category_set_id = msets.category_set_id
AND msets.control_level =2 -- Controlled at item org level
AND mbulk.organization_id = mi.organization_id
AND mi.organization_id = mparams.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 = ICX_CAT_UTIL_PVT.g_category_set_id
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 (+)
) doc1 ORDER by doc1.inventory_item_id;
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
FROM
(
SELECT /*+ ROWID(mi) NO_EXPAND use_nl(mitl,mic,catMap) */
mi.inventory_item_id inventory_item_id,
TO_NUMBER(ICX_CAT_UTIL_PVT.g_NULL_NUMBER) po_line_id,
TO_CHAR(ICX_CAT_UTIL_PVT.g_NULL_NUMBER) req_template_name,
TO_NUMBER(ICX_CAT_UTIL_PVT.g_NULL_NUMBER) 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 = mitl.inventory_item_id
AND mi.organization_id = mparams.organization_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 = ICX_CAT_UTIL_PVT.g_category_set_id
AND mic.category_id = p_mtl_category_id
AND mi.inventory_item_id >= p_inventory_item_id
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.inventory_item_id;
SELECT /*+ LEADING(doc) use_nl(ic1,ctx) */
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
FROM
(
SELECT /*+ ROWID(mi) NO_EXPAND use_nl(mitl,mic,catMap) */
mi.inventory_item_id inventory_item_id,
TO_NUMBER(ICX_CAT_UTIL_PVT.g_NULL_NUMBER) po_line_id,
TO_CHAR(ICX_CAT_UTIL_PVT.g_NULL_NUMBER) req_template_name,
TO_NUMBER(ICX_CAT_UTIL_PVT.g_NULL_NUMBER) 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 = mitl.inventory_item_id
AND mi.organization_id = mparams.organization_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 = ICX_CAT_UTIL_PVT.g_category_set_id
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'
AND mi.rowid BETWEEN g_start_rowid and g_end_rowid
) 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 (+);
SELECT /*+ LEADING(doc) use_nl(ic1,ctx) */
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
FROM
(
SELECT /*+ ROWID(mi) NO_EXPAND use_nl(mitl,mic,catMap) */
mi.inventory_item_id inventory_item_id,
TO_NUMBER(ICX_CAT_UTIL_PVT.g_NULL_NUMBER) po_line_id,
TO_CHAR(ICX_CAT_UTIL_PVT.g_NULL_NUMBER) req_template_name,
TO_NUMBER(ICX_CAT_UTIL_PVT.g_NULL_NUMBER) 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 = mitl.inventory_item_id
AND mi.organization_id = mparams.organization_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 = ICX_CAT_UTIL_PVT.g_category_set_id
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 (mi.last_update_date > g_upgrade_last_run_date
OR mitl.last_update_date > g_upgrade_last_run_date
OR mic.last_update_date > g_upgrade_last_run_date)
AND catMap.external_source_key (+) = TO_CHAR(mic.category_id)
AND catMap.external_source (+) = 'Oracle'
AND mi.rowid BETWEEN g_start_rowid and g_end_rowid
) 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 (+);
l_inv_item_id_tbl.DELETE;
l_po_line_id_tbl.DELETE;
l_req_template_name_tbl.DELETE;
l_req_template_line_num_tbl.DELETE;
l_org_id_tbl.DELETE;
l_language_tbl.DELETE;
l_source_type_tbl.DELETE;
l_purchasing_org_id_tbl.DELETE;
l_po_category_id_tbl.DELETE;
l_category_key_tbl.DELETE;
l_intrnl_order_enbld_flag_tbl.DELETE;
l_purchasing_enabled_flag_tbl.DELETE;
l_outside_operation_flag_tbl.DELETE;
l_ip_category_id_tbl.DELETE;
l_ip_category_name_tbl.DELETE;
l_unit_meas_lookup_code_tbl.DELETE;
l_unit_price_tbl.DELETE;
l_rfq_required_flag_tbl.DELETE;
l_description_tbl.DELETE;
l_long_description_tbl.DELETE;
l_organization_id_tbl.DELETE;
l_master_organization_id_tbl.DELETE;
l_ctx_inventory_item_id_tbl.DELETE;
l_ctx_source_type_tbl.DELETE;
l_ctx_item_type_tbl.DELETE;
l_ctx_purchasing_org_id_tbl.DELETE;
l_ctx_supplier_id_tbl.DELETE;
l_ctx_supplier_site_id_tbl.DELETE;
l_ctx_supplier_part_num_tbl.DELETE;
l_ctx_supplier_part_auxid_tbl.DELETE;
l_ctx_ip_category_id_tbl.DELETE;
l_ctx_po_category_id_tbl.DELETE;
l_ctx_ip_category_name_tbl.DELETE;
l_ctx_rowid_tbl.DELETE;
', ' || l_organization_id_tbl(i) || '), Item is inactive and invalid for purchase, delete from iP tables' ||
', internal_order_enabled_flag:' || l_intrnl_order_enbld_flag_tbl(i) ||
', purchasing_enabled_flag:' || l_purchasing_enabled_flag_tbl(i) ||
', outside_operation_flag:' || l_outside_operation_flag_tbl(i) ||
', list_price_per_unit:' || l_unit_price_tbl(i) ||
', po_category_id:' || l_po_category_id_tbl(i) ||
', status:' || l_inv_item_status);
populateItemDelete(l_inv_item_id_tbl(i), l_organization_id_tbl(i)); --Bug 7454766 delete item from iP tables.
IF (g_online_mode = g_bulkUpdate_mode) THEN
l_err_loc := 1800;
ELSIF (g_online_mode = g_catgItemUpdate_mode) THEN
l_err_loc := 1800;
g_online_mode := g_onlineUpdate_mode;
g_online_mode := g_bulkUpdate_mode;
PROCEDURE populateItemDelete
( P_INVENTORY_ITEM_ID IN NUMBER ,
P_ORGANIZATION_ID IN NUMBER
)
IS
CURSOR masterItemsToBeDeletedCsr(P_INVENTORY_ITEM_ID NUMBER,
P_ORGANIZATION_ID NUMBER) IS
SELECT ctx.inventory_item_id,
ctx.org_id,
ctx.language
FROM icx_cat_items_ctx_hdrs_tlp ctx,
financials_system_params_all fsp,
mtl_parameters mparams
WHERE ctx.inventory_item_id = P_INVENTORY_ITEM_ID
AND ctx.source_type = 'MASTER_ITEM'
AND (mparams.master_organization_id = P_ORGANIZATION_ID
OR mparams.organization_id = P_ORGANIZATION_ID)
AND fsp.inventory_organization_id = mparams.organization_id
AND fsp.org_id = ctx.org_id;
l_api_name CONSTANT VARCHAR2(30) := 'populateItemDelete';
OPEN masterItemsToBeDeletedCsr(P_INVENTORY_ITEM_ID, P_ORGANIZATION_ID);
l_inv_item_id_tbl.DELETE;
l_org_id_tbl.DELETE;
l_language_tbl.DELETE;
FETCH masterItemsToBeDeletedCsr BULK COLLECT INTO
l_inv_item_id_tbl, l_org_id_tbl, l_language_tbl
LIMIT ICX_CAT_UTIL_PVT.g_batch_size;
ICX_CAT_POPULATE_ITEM_PVT.deleteItemCtxHdrsTLP(l_current_ctx_item_rec);
ICX_CAT_POPULATE_ITEM_PVT.deleteItemCtxDtlsTLP(l_current_ctx_item_rec);
l_err_string := 'ICX_CAT_POPULATE_MI_PVT.populateItemDelete' ||l_err_loc
||', Total processeded batches:' ||l_batch_count
||', Cursor will be reopened;';
IF (masterItemsToBeDeletedCsr%ISOPEN) THEN
l_err_loc := 1500;
CLOSE masterItemsToBeDeletedCsr;
OPEN masterItemsToBeDeletedCsr(P_INVENTORY_ITEM_ID, P_ORGANIZATION_ID);
'populateItemDelete done; Total num. of batches processed:' || l_batch_count);
IF (masterItemsToBeDeletedCsr%ISOPEN) THEN
l_err_loc := 2000;
CLOSE masterItemsToBeDeletedCsr;
END populateItemDelete;
PROCEDURE populateItemCatgDelete
( P_INVENTORY_ITEM_ID IN NUMBER ,
P_ORGANIZATION_ID IN NUMBER
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'populateItemCatgDelete';
g_online_mode := g_onlineUpdate_mode;
openOnlineItemCatgDeleteCursor(P_INVENTORY_ITEM_ID, P_ORGANIZATION_ID);
END populateItemCatgDelete;
g_online_mode := g_catgItemUpdate_mode;