[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
eitl.id_type
, eitl.item_id
--, eitl.item_code
--, eitl.org_id
, eitl.language
--, eitl.source_lang
--, NVL(eitl.item_catalog_group_id, 0)
--, eitl.item_code ||' '|| TRANSLATE(eitl.item_code, g_MSTK_Flex_Delimiter, ' ')
, eitl.item_code ||' '|| msitl.description ||' '|| msitl.long_description
INTO
l_id_type
, l_item_id
--, l_item_code
--, l_org_id
, l_language
--, l_source_lang
--, l_item_catalog_group_id
--, l_item_segments
, l_text
FROM
ego_item_text_tl eitl
, mtl_system_items_tl msitl
WHERE
eitl.rowid = p_rowid
AND msitl.inventory_item_id = eitl.item_id
AND msitl.organization_id = eitl.org_id
AND msitl.language = eitl.language;
SELECT
eitl.item_id
, eitl.org_id
, eitl.item_code
, msitl.description
, msitl.long_description
, eitl.item_catalog_group_id
, eitl.language
INTO
l_item_id
, l_org_id
, l_item_code
, l_description
, l_long_description
, l_item_catalog_group_id
, l_language
FROM
ego_item_text_tl eitl
, mtl_system_items_tl msitl
WHERE
eitl.rowid = p_rowid
AND msitl.inventory_item_id = eitl.item_id
AND msitl.organization_id = eitl.org_id
AND msitl.language = eitl.language;
SELECT micg.concatenated_segments item_catalog_group
INTO l_item_catalog_group
FROM mtl_item_catalog_groups_kfv micg
WHERE micg.item_catalog_group_id = l_item_catalog_group_id;
SELECT mmpn.mfg_part_num
, mm.manufacturer_name
BULK COLLECT INTO
l_mpn_table
, l_mfg_table
FROM MTL_MANUFACTURERS mm
, MTL_MFG_PART_NUMBERS mmpn
WHERE mm.manufacturer_id = mmpn.manufacturer_id
AND mmpn.inventory_item_id = l_item_id
AND mmpn.organization_id = l_org_id;
SELECT
SEGMENT1 ||' '|| SEGMENT2 ||' '|| SEGMENT3 ||' '|| SEGMENT4 ||' '|| SEGMENT5 ||' '||
SEGMENT6 ||' '|| SEGMENT7 ||' '|| SEGMENT8 ||' '|| SEGMENT9 ||' '|| SEGMENT10 ||' '||
SEGMENT11 ||' '|| SEGMENT12 ||' '|| SEGMENT13 ||' '|| SEGMENT14 ||' '|| SEGMENT15 ||' '||
SEGMENT16 ||' '|| SEGMENT17 ||' '|| SEGMENT18 ||' '|| SEGMENT19 ||' '|| SEGMENT20
INTO
l_item_segments
FROM
mtl_system_items_b msib
WHERE
msib.inventory_item_id = l_item_id
AND msib.organization_id = l_org_id;
SELECT
msitl.description, msitl.long_description
INTO
v_description, v_long_description
FROM
mtl_system_items_tl msitl
WHERE
msitl.inventory_item_id = l_item_id
AND msitl.organization_id = l_org_id
AND msitl.language = l_language;
SELECT
customer_item_desc, NULL
INTO
v_description, v_long_description
FROM
mtl_customer_items
WHERE
customer_item_id = l_item_id;
SELECT
description, NULL
INTO
v_description, v_long_description
FROM
mtl_cross_references
WHERE
cross_reference_type = l_id_type
AND cross_reference = l_item_code
AND inventory_item_id = l_item_id
AND org_independent_flag = DECODE(l_org_id, 0, 'Y', 'N')
AND ( organization_id = l_org_id
OR ( organization_id IS NULL AND l_org_id = 0 )
)
;
INSERT INTO mtl_interface_errors
(
transaction_id
, unique_id
, organization_id
, table_name
, message_name
, error_message
, creation_date
, created_by
, last_update_date
, last_updated_by
)
VALUES
(
mtl_system_items_interface_s.NEXTVAL
, p_item_id
, p_org_id
, 'EGO_ITEM_TEXT_TL'
, p_msg_name
, SUBSTRB(p_error_text, 1,240)
, l_sysdate
, 1
, l_sysdate
, 1
);
INV_ITEM_MSG.Write_List (p_delete => TRUE);
INV_ITEM_MSG.Write_List (p_delete => TRUE);
, p_last_update_date IN VARCHAR2
, p_last_updated_by IN VARCHAR2
, p_last_update_login IN VARCHAR2
, p_id_type IN VARCHAR2
, p_item_code IN VARCHAR2
, p_item_catalog_group_id IN VARCHAR2
)
IS
l_id_type VARCHAR2(30);
IF ( p_event = 'UPDATE' ) THEN
-- Item Code is passed in through a parameter.
-- Update rows for all languages.
UPDATE ego_item_text_tl
SET
item_code = DECODE(p_item_code, FND_API.G_MISS_CHAR, item_code, p_item_code)
, item_catalog_group_id = DECODE(p_item_catalog_group_id, FND_API.G_MISS_NUM, item_catalog_group_id, p_item_catalog_group_id)
, text = l_text_upd
, last_update_date = SYSDATE
, last_updated_by = DECODE(p_last_updated_by, FND_API.G_MISS_NUM, last_updated_by, p_last_updated_by)
, last_update_login = DECODE(p_last_update_login, FND_API.G_MISS_NUM, last_update_login, p_last_update_login)
WHERE
id_type = l_id_type
AND item_id = p_item_id
AND org_id = p_org_id;
IF ( p_event = 'INSERT' ) THEN
INSERT INTO ego_item_text_tl
(
id_type
, item_id
, item_code
, org_id
, language
, source_lang
, item_catalog_group_id
, inventory_item_id
, text
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
)
SELECT
l_id_type
, msik.inventory_item_id
, msik.concatenated_segments
, msik.organization_id
, p_language
, DECODE(p_source_lang, FND_API.G_MISS_CHAR, p_language, p_source_lang)
, msik.item_catalog_group_id
, msik.inventory_item_id
, l_text_ins
, SYSDATE
, msik.created_by
, SYSDATE
, DECODE(p_last_updated_by, FND_API.G_MISS_NUM, msik.last_updated_by, p_last_updated_by)
, DECODE(p_last_update_login, FND_API.G_MISS_NUM, msik.last_update_login, p_last_update_login)
FROM
mtl_system_items_b_kfv msik
WHERE
msik.inventory_item_id = p_item_id
AND msik.organization_id = p_org_id;
( SELECT 1 FROM ego_item_text_tl eitl1
WHERE
eitl1.id_type = l_id_type
AND eitl1.item_id = msik.inventory_item_id
AND eitl1.item_code = msik.concatenated_segments
AND eitl1.org_id = msik.organization_id
AND eitl1.language = p_language
);
ELSIF ( p_event = 'UPDATE' ) THEN
UPDATE ego_item_text_tl
SET
source_lang = DECODE(p_source_lang, FND_API.G_MISS_CHAR, source_lang, p_source_lang)
, text = l_text_upd
, last_update_date = SYSDATE
, last_updated_by = DECODE(p_last_updated_by, FND_API.G_MISS_NUM, last_updated_by, p_last_updated_by)
, last_update_login = DECODE(p_last_update_login, FND_API.G_MISS_NUM, last_update_login, p_last_update_login)
WHERE
id_type = l_id_type
AND item_id = p_item_id
AND org_id = p_org_id
AND language = p_language;
ELSIF ( p_event = 'DELETE' ) THEN
DELETE FROM ego_item_text_tl
WHERE
id_type = l_id_type
AND item_id = p_item_id
AND org_id = p_org_id
AND language = p_language;
UPDATE EGO_ITEM_TEXT_TL
SET text = l_text_upd
, last_update_date = SYSDATE
, last_updated_by = DECODE(p_last_updated_by, FND_API.G_MISS_NUM, last_updated_by, p_last_updated_by)
, last_update_login = DECODE(p_last_update_login, FND_API.G_MISS_NUM, last_update_login, p_last_update_login)
WHERE
id_type = l_id_type
AND (item_id,org_id) IN ( SELECT INVENTORY_ITEM_ID, ORGANIZATION_ID
FROM MTL_MFG_PART_NUMBERS
WHERE MANUFACTURER_ID = p_manufacturer_id );
IF ( p_event = 'UPDATE' OR p_event= 'INSERT' ) THEN
UPDATE EGO_ITEM_TEXT_TL
SET text = l_text_upd
, last_update_date = SYSDATE
, last_updated_by = DECODE(p_last_updated_by, FND_API.G_MISS_NUM, last_updated_by, p_last_updated_by)
, last_update_login = DECODE(p_last_update_login, FND_API.G_MISS_NUM, last_update_login, p_last_update_login)
WHERE
id_type = l_id_type
AND org_id = p_org_id
AND item_id IN (NVL(p_old_item_id,p_item_id),p_item_id);
UPDATE EGO_ITEM_TEXT_TL
SET text = l_text_upd
, last_update_date = SYSDATE
WHERE
id_type = l_id_type
AND org_id = p_org_id
AND item_id IN (NVL(p_old_item_id,p_item_id),p_item_id);
IF ( p_event IN ('INSERT', 'UPDATE', 'DELETE') ) THEN
--
-- Update rows for all languages
--
UPDATE ego_item_text_tl
SET
text = l_text_upd
, last_update_date = SYSDATE
WHERE
id_type = l_id_type
AND item_id = p_item_id
AND org_id = p_org_id;
IF ( p_event IN ('INSERT', 'UPDATE', 'DELETE') ) THEN
--
-- Update rows for a single language
--
UPDATE ego_item_text_tl
SET
text = l_text_upd
, last_update_date = SYSDATE
WHERE
id_type = l_id_type
AND item_id = p_item_id
AND org_id = p_org_id
AND language = p_language;
IF ( p_event = 'UPDATE' ) THEN
-- updated item catalog group ID is passed in through a parameter;
UPDATE ego_item_text_tl
SET
text = l_text_upd
, last_update_date = SYSDATE
, last_updated_by = DECODE(p_last_updated_by, FND_API.G_MISS_NUM, last_updated_by, p_last_updated_by)
, last_update_login = DECODE(p_last_update_login, FND_API.G_MISS_NUM, last_update_login, p_last_update_login)
WHERE
id_type = l_id_type
AND item_catalog_group_id = NVL(p_item_catalog_group_id, -1);
, p_last_update_date IN VARCHAR2 DEFAULT FND_API.G_MISS_DATE
, p_last_updated_by IN VARCHAR2 DEFAULT FND_API.G_MISS_NUM
, p_last_update_login IN VARCHAR2 DEFAULT FND_API.G_MISS_NUM
, p_id_type IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR
, p_item_code IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR
, p_item_catalog_group_id IN VARCHAR2 DEFAULT FND_API.G_MISS_NUM
)
IS
l_manufacturer_id NUMBER :=NULL;
, p_last_update_date => p_last_update_date
, p_last_updated_by => p_last_updated_by
, p_last_update_login => p_last_update_login
, p_id_type => p_id_type
, p_item_code => p_item_code
, p_item_catalog_group_id => p_item_catalog_group_id
);
SELECT SUBSTR(VALUE,0,1) into l_DB_Numeric_Character
FROM V$NLS_PARAMETERS
Where PARAMETER = 'NLS_NUMERIC_CHARACTERS';
SELECT concatenated_segment_delimiter
INTO g_MSTK_Flex_Delimiter
FROM fnd_id_flex_structures
WHERE
application_id = 401
AND id_flex_code = 'MSTK'
AND id_flex_num = 101
AND enabled_flag = 'Y';