The following lines contain the word 'select', 'insert', 'update' or 'delete':
, LAST_UPDATE_DATE DATE
, LAST_UPDATED_BY NUMBER
, CREATION_DATE DATE
, CREATED_BY NUMBER
, LAST_UPDATE_LOGIN NUMBER
, DESCRIPTION VARCHAR2(240)
, DISABLE_DATE DATE
, ATTRIBUTE_CATEGORY VARCHAR2(30)
, ATTRIBUTE1 VARCHAR2(150)
, ATTRIBUTE2 VARCHAR2(150)
, ATTRIBUTE3 VARCHAR2(150)
, ATTRIBUTE4 VARCHAR2(150)
, ATTRIBUTE5 VARCHAR2(150)
, ATTRIBUTE6 VARCHAR2(150)
, ATTRIBUTE7 VARCHAR2(150)
, ATTRIBUTE8 VARCHAR2(150)
, ATTRIBUTE9 VARCHAR2(150)
, ATTRIBUTE10 VARCHAR2(150)
, ATTRIBUTE11 VARCHAR2(150)
, ATTRIBUTE12 VARCHAR2(150)
, ATTRIBUTE13 VARCHAR2(150)
, ATTRIBUTE14 VARCHAR2(150)
, ATTRIBUTE15 VARCHAR2(150)
, REQUEST_ID NUMBER
, PROGRAM_APPLICATION_ID NUMBER
, PROGRAM_ID NUMBER
, PROGRAM_UPDATE_DATE DATE
, STRUCTURE_TYPE_ID NUMBER
, IS_PREFERRED VARCHAR2(1)
--- Extra attributes added for internal usage
, DISPLAY_NAME VARCHAR2(80)
, Alt_Desig_Code_Old VARCHAR2(10)
, api_version NUMBER
);
PROCEDURE Insert_Row (p_alt_desig_rec IN Bom_Alt_Desig_Rec
,x_return_status OUT NOCOPY VARCHAR2
,x_errorcode OUT NOCOPY NUMBER
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
);
PROCEDURE Update_Row (p_alt_desig_rec IN Bom_Alt_Desig_Rec
,x_return_status OUT NOCOPY VARCHAR2
,x_errorcode OUT NOCOPY NUMBER
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
);
SELECT 1
FROM BOM_BILL_OF_MATERIALS
WHERE BOM_BILL_OF_MATERIALS.Alternate_Bom_Designator =
X_Alt_Desig_Code
UNION
SELECT 1
FROM BOM_OPERATIONAL_ROUTINGS
WHERE BOM_OPERATIONAL_ROUTINGS.ALTERNATE_ROUTING_DESIGNATOR =
X_Alt_Desig_Code
UNION
SELECT 1
FROM CST_COST_TYPES
WHERE CST_COST_TYPES.ALTERNATE_BOM_DESIGNATOR =
X_Alt_Desig_Code;
SELECT 1 INTO DUMMY FROM DUAL WHERE NOT EXISTS
(SELECT 1 FROM BOM_ALTERNATE_DESIGNATORS
WHERE Organization_Id = X_Organization_Id
AND Alternate_Designator_Code = X_Alternate_Designator_Code
);
SELECT 1 INTO DUMMY FROM DUAL WHERE NOT EXISTS
(SELECT 1 FROM BOM_BILL_OF_MATERIALS
WHERE BOM_BILL_OF_MATERIALS.Organization_ID = X_Organization_Id
AND BOM_BILL_OF_MATERIALS.Alternate_Bom_Designator =
X_Alternate_Designator_Code
);
SELECT 1 INTO DUMMY FROM DUAL WHERE NOT EXISTS
(SELECT 1 FROM BOM_OPERATIONAL_ROUTINGS
WHERE BOM_OPERATIONAL_ROUTINGS.Organization_Id = X_Organization_Id
AND BOM_OPERATIONAL_ROUTINGS.ALTERNATE_ROUTING_DESIGNATOR =
X_Alternate_Designator_Code
);
SELECT 1 INTO DUMMY FROM DUAL WHERE NOT EXISTS
(SELECT 1 FROM CST_COST_TYPES
WHERE CST_COST_TYPES.Organization_Id = X_Organization_Id
AND CST_COST_TYPES.ALTERNATE_BOM_DESIGNATOR =
X_Alternate_Designator_Code
);
PROCEDURE Insert_Row ( --- not used, retaining for the moment
p_api_version IN NUMBER
,p_alt_desig_code IN VARCHAR2
,p_organization_id IN NUMBER
,p_description IN VARCHAR2
,p_disable_date IN DATE
,p_structure_type_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_errorcode OUT NOCOPY NUMBER
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
BEGIN
Insert_Row (
p_api_version => p_api_version
,p_alt_desig_code => p_alt_desig_code
,p_organization_id => p_organization_id
,p_display_name => p_alt_desig_code
,p_description => p_description
,p_disable_date => p_disable_date
,p_structure_type_id => p_structure_type_id
,p_is_preferred => null
,x_return_status => x_return_status
,x_errorcode => x_errorcode
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
END Insert_row;
PROCEDURE Insert_Row ( --- Called by OA Pages
p_api_version IN NUMBER
,p_alt_desig_code IN VARCHAR2
,p_organization_id IN NUMBER
,p_display_name IN VARCHAR2
,p_description IN VARCHAR2
,p_disable_date IN DATE
,p_structure_type_id IN NUMBER
,p_is_preferred IN VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
,x_errorcode OUT NOCOPY NUMBER
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_version CONSTANT NUMBER := 1.0;
rec.LAST_UPDATE_DATE := l_Sysdate;
rec.LAST_UPDATED_BY := g_current_user_id;
rec.LAST_UPDATE_LOGIN := g_current_login_id;
rec.PROGRAM_UPDATE_DATE := NULL;
Insert_Row (p_alt_desig_rec => rec,
x_return_status => x_return_status,
x_errorcode => x_errorcode,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
END Insert_row;
PROCEDURE Update_Row ( --- not used, retaining for the moment
p_api_version IN NUMBER
,p_alt_desig_code_old IN VARCHAR2
,p_organization_id IN NUMBER
,p_alt_desig_code_new IN VARCHAR2
,p_description IN VARCHAR2
,p_disable_date IN DATE
,p_structure_type_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_errorcode OUT NOCOPY NUMBER
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
BEGIN
Update_Row (
p_api_version => p_api_version
,p_alt_desig_code_old => p_alt_desig_code_old
,p_organization_id => p_organization_id
,p_alt_desig_code_new => p_alt_desig_code_new
,p_display_name_new => p_alt_desig_code_new
,p_description => p_description
,p_disable_date => p_disable_date
,p_structure_type_id => p_structure_type_id
,p_is_preferred => null
,x_return_status => x_return_status
,x_errorcode => x_errorcode
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
END Update_Row;
PROCEDURE Update_Row ( --- Called by OA Pages
p_api_version IN NUMBER
,p_alt_desig_code_old IN VARCHAR2
,p_organization_id IN NUMBER
,p_alt_desig_code_new IN VARCHAR2
,p_display_name_new IN VARCHAR2
,p_description IN VARCHAR2
,p_disable_date IN DATE
,p_structure_type_id IN NUMBER
,p_is_preferred IN VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
,x_errorcode OUT NOCOPY NUMBER
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_version CONSTANT NUMBER := 1.0;
rec.LAST_UPDATE_DATE := l_Sysdate;
rec.LAST_UPDATED_BY := g_current_user_id;
rec.LAST_UPDATE_LOGIN := g_current_login_id;
rec.PROGRAM_UPDATE_DATE := NULL;
Update_Row (p_alt_desig_rec => rec
,x_return_status => x_return_status
,x_errorcode => x_errorcode
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
END Update_Row;
PROCEDURE Delete_Row (
p_api_version IN NUMBER
,p_alt_desig_code IN VARCHAR2
,p_from_struct_alt_page IN VARCHAR2 DEFAULT 'N'
,x_return_status OUT NOCOPY VARCHAR2
,x_errorcode OUT NOCOPY NUMBER
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Alternate_In_All_Org';
SAVEPOINT Delete_Alternate_PUB;
DELETE FROM BOM_ALTERNATE_DESIGNATORS
WHERE ALTERNATE_DESIGNATOR_CODE = p_alt_desig_code;
DELETE FROM BOM_ALTERNATE_DESIGNATORS_TL
WHERE ALTERNATE_DESIGNATOR_CODE = p_alt_desig_code;
UPDATE BOM_ALTERNATE_DESIGNATORS SET structure_type_id =
(SELECT structure_type_id
FROM bom_structure_types_b
WHERE parent_structure_type_id IS NULL)
WHERE ALTERNATE_DESIGNATOR_CODE = p_alt_desig_code ;
ROLLBACK TO Delete_Alternate_PUB;
END Delete_Row;
PROCEDURE Delete_Row (
p_api_version IN NUMBER
,p_alt_desig_code IN VARCHAR2
,p_organization_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_errorcode OUT NOCOPY NUMBER
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Alternate_For_Org';
SAVEPOINT Delete_Alternate_PUB;
DELETE FROM BOM_ALTERNATE_DESIGNATORS
WHERE ALTERNATE_DESIGNATOR_CODE = p_alt_desig_code
AND ORGANIZATION_ID = p_organization_id;
DELETE FROM BOM_ALTERNATE_DESIGNATORS_TL
WHERE ALTERNATE_DESIGNATOR_CODE = p_alt_desig_code
and ORGANIZATION_ID = p_organization_id;
ROLLBACK TO Delete_Alternate_PUB;
END Delete_Row;
UPDATE BOM_ALTERNATE_DESIGNATORS
SET structure_type_id = p_structure_type_id
WHERE alternate_designator_code = p_alternate_designator_code;
FND_MESSAGE.SET_NAME('BOM', 'BOM_UPDATE_FAILED');
DELETE FROM BOM_ALTERNATE_DESIGNATORS_TL T
WHERE not exists
(SELECT NULL
FROM BOM_ALTERNATE_DESIGNATORS B
WHERE B.ALTERNATE_DESIGNATOR_CODE = T.ALTERNATE_DESIGNATOR_CODE
and B.ORGANIZATION_ID = T.ORGANIZATION_ID
);
UPDATE BOM_ALTERNATE_DESIGNATORS_TL T SET (
DISPLAY_NAME,
DESCRIPTION
) = (SELECT
B.DISPLAY_NAME,
B.DESCRIPTION
FROM BOM_ALTERNATE_DESIGNATORS_TL B
WHERE B.ALTERNATE_DESIGNATOR_CODE = T.ALTERNATE_DESIGNATOR_CODE
AND B.ORGANIZATION_ID = T.ORGANIZATION_ID
AND B.LANGUAGE = T.SOURCE_LANG)
WHERE (
T.ALTERNATE_DESIGNATOR_CODE,
T.ORGANIZATION_ID,
T.LANGUAGE
) IN (SELECT
SUBT.ALTERNATE_DESIGNATOR_CODE,
SUBT.ORGANIZATION_ID,
SUBT.LANGUAGE
FROM BOM_ALTERNATE_DESIGNATORS_TL SUBB, BOM_ALTERNATE_DESIGNATORS_TL SUBT
WHERE SUBB.ALTERNATE_DESIGNATOR_CODE = SUBT.ALTERNATE_DESIGNATOR_CODE
AND SUBB.ORGANIZATION_ID = SUBT.ORGANIZATION_ID
AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
AND (SUBB.DISPLAY_NAME <> SUBT.DISPLAY_NAME
OR (SUBB.DISPLAY_NAME IS NULL AND SUBT.DISPLAY_NAME IS NOT NULL)
OR (SUBB.DISPLAY_NAME IS NOT NULL AND SUBT.DISPLAY_NAME IS NULL)
OR SUBB.DESCRIPTION <> SUBT.DESCRIPTION
OR (SUBB.DESCRIPTION IS NULL AND SUBT.DESCRIPTION IS NOT NULL)
OR (SUBB.DESCRIPTION IS NOT NULL AND SUBT.DESCRIPTION IS NULL)
));
INSERT INTO BOM_ALTERNATE_DESIGNATORS_TL (
ALTERNATE_DESIGNATOR_CODE,
ORGANIZATION_ID,
DISPLAY_NAME,
DESCRIPTION,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
LANGUAGE,
SOURCE_LANG
) SELECT /*+ ORDERED */
B.ALTERNATE_DESIGNATOR_CODE,
B.ORGANIZATION_ID,
B.DISPLAY_NAME,
B.DESCRIPTION,
B.LAST_UPDATE_DATE,
B.LAST_UPDATED_BY,
B.CREATION_DATE,
B.CREATED_BY,
B.LAST_UPDATE_LOGIN,
L.LANGUAGE_CODE,
B.SOURCE_LANG
FROM BOM_ALTERNATE_DESIGNATORS_TL B, FND_LANGUAGES L
WHERE L.INSTALLED_FLAG IN ('I', 'B')
AND B.LANGUAGE = USERENV('LANG')
AND NOT EXISTS
(SELECT NULL
FROM BOM_ALTERNATE_DESIGNATORS_TL T
WHERE T.ALTERNATE_DESIGNATOR_CODE = B.ALTERNATE_DESIGNATOR_CODE
AND T.ORGANIZATION_ID = B.ORGANIZATION_ID
AND T.LANGUAGE = L.LANGUAGE_CODE);
PROCEDURE Insert_Row ( --- Called by form BOMFDBAD.fmb
P_ALTERNATE_DESIGNATOR_CODE in VARCHAR2,
P_ORGANIZATION_ID in NUMBER,
P_STRUCTURE_TYPE_ID in NUMBER,
P_DISABLE_DATE in DATE,
P_ATTRIBUTE_CATEGORY in VARCHAR2,
P_ATTRIBUTE1 in VARCHAR2,
P_ATTRIBUTE2 in VARCHAR2,
P_ATTRIBUTE3 in VARCHAR2,
P_ATTRIBUTE4 in VARCHAR2,
P_ATTRIBUTE5 in VARCHAR2,
P_ATTRIBUTE6 in VARCHAR2,
P_ATTRIBUTE7 in VARCHAR2,
P_ATTRIBUTE8 in VARCHAR2,
P_ATTRIBUTE9 in VARCHAR2,
P_ATTRIBUTE10 in VARCHAR2,
P_ATTRIBUTE11 in VARCHAR2,
P_ATTRIBUTE12 in VARCHAR2,
P_ATTRIBUTE13 in VARCHAR2,
P_ATTRIBUTE14 in VARCHAR2,
P_ATTRIBUTE15 in VARCHAR2,
P_REQUEST_ID in NUMBER,
P_DISPLAY_NAME in VARCHAR2,
P_DESCRIPTION in VARCHAR2,
P_CREATION_DATE in DATE,
P_CREATED_BY in NUMBER,
P_LAST_UPDATE_DATE in DATE,
P_LAST_UPDATED_BY in NUMBER,
P_LAST_UPDATE_LOGIN in NUMBER
) IS
l_api_version CONSTANT NUMBER := 1.0;
rec.LAST_UPDATE_DATE := P_LAST_UPDATE_DATE;
rec.LAST_UPDATED_BY := P_LAST_UPDATED_BY;
rec.LAST_UPDATE_LOGIN := P_LAST_UPDATE_LOGIN;
rec.PROGRAM_UPDATE_DATE := NULL;
Insert_Row (p_alt_desig_rec => rec,
x_return_status => x_return_status,
x_errorcode => x_errorcode,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
END Insert_Row;
PROCEDURE Insert_Row (
p_alt_desig_rec IN Bom_Alt_Desig_Rec
,x_return_status OUT NOCOPY VARCHAR2
,x_errorcode OUT NOCOPY NUMBER
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Create_Alternate';
update bom_alternate_designators
set is_preferred ='N'
where
alternate_designator_code =old_preferred_name;
INSERT INTO BOM_ALTERNATE_DESIGNATORS
(
ALTERNATE_DESIGNATOR_CODE
,ORGANIZATION_ID
,DESCRIPTION
,DISABLE_DATE
,STRUCTURE_TYPE_ID
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,REQUEST_ID
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
, IS_PREFERRED
)
VALUES
(
p_alt_desig_rec.ALTERNATE_DESIGNATOR_CODE
,p_alt_desig_rec.ORGANIZATION_ID
,p_alt_desig_rec.DESCRIPTION
,p_alt_desig_rec.DISABLE_DATE
,l_structure_type_id
,p_alt_desig_rec.ATTRIBUTE_CATEGORY
,p_alt_desig_rec.ATTRIBUTE1
,p_alt_desig_rec.ATTRIBUTE2
,p_alt_desig_rec.ATTRIBUTE3
,p_alt_desig_rec.ATTRIBUTE4
,p_alt_desig_rec.ATTRIBUTE5
,p_alt_desig_rec.ATTRIBUTE6
,p_alt_desig_rec.ATTRIBUTE7
,p_alt_desig_rec.ATTRIBUTE8
,p_alt_desig_rec.ATTRIBUTE9
,p_alt_desig_rec.ATTRIBUTE10
,p_alt_desig_rec.ATTRIBUTE11
,p_alt_desig_rec.ATTRIBUTE12
,p_alt_desig_rec.ATTRIBUTE13
,p_alt_desig_rec.ATTRIBUTE14
,p_alt_desig_rec.ATTRIBUTE15
,p_alt_desig_rec.REQUEST_ID
,p_alt_desig_rec.CREATION_DATE
,p_alt_desig_rec.CREATED_BY
,p_alt_desig_rec.LAST_UPDATE_DATE
,p_alt_desig_rec.LAST_UPDATED_BY
,p_alt_desig_rec.LAST_UPDATE_LOGIN
,p_alt_desig_rec.PROGRAM_APPLICATION_ID
,p_alt_desig_rec.PROGRAM_ID
,p_alt_desig_rec.PROGRAM_UPDATE_DATE
,p_alt_desig_rec.IS_PREFERRED
);
insert into BOM_ALTERNATE_DESIGNATORS_TL (
ALTERNATE_DESIGNATOR_CODE,
ORGANIZATION_ID,
DISPLAY_NAME,
DESCRIPTION,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
LANGUAGE,
SOURCE_LANG
) select
p_alt_desig_rec.ALTERNATE_DESIGNATOR_CODE,
p_alt_desig_rec.ORGANIZATION_ID,
p_alt_desig_rec.DISPLAY_NAME,
p_alt_desig_rec.DESCRIPTION,
p_alt_desig_rec.LAST_UPDATE_DATE,
p_alt_desig_rec.LAST_UPDATED_BY,
p_alt_desig_rec.CREATION_DATE,
p_alt_desig_rec.CREATED_BY,
p_alt_desig_rec.LAST_UPDATE_LOGIN,
L.LANGUAGE_CODE,
userenv('LANG')
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from BOM_ALTERNATE_DESIGNATORS_TL T
where T.ALTERNATE_DESIGNATOR_CODE = p_alt_desig_rec.ALTERNATE_DESIGNATOR_CODE
and T.ORGANIZATION_ID = p_alt_desig_rec.ORGANIZATION_ID
and T.LANGUAGE = L.LANGUAGE_CODE);
END Insert_row;
procedure UPDATE_ROW ( --- Called from the form BOMFDBAD.fmb
P_ALTERNATE_DESIGNATOR_CODE in VARCHAR2,
P_ORGANIZATION_ID in NUMBER,
P_STRUCTURE_TYPE_ID in NUMBER,
P_DISABLE_DATE in DATE,
P_ATTRIBUTE_CATEGORY in VARCHAR2,
P_ATTRIBUTE1 in VARCHAR2,
P_ATTRIBUTE2 in VARCHAR2,
P_ATTRIBUTE3 in VARCHAR2,
P_ATTRIBUTE4 in VARCHAR2,
P_ATTRIBUTE5 in VARCHAR2,
P_ATTRIBUTE6 in VARCHAR2,
P_ATTRIBUTE7 in VARCHAR2,
P_ATTRIBUTE8 in VARCHAR2,
P_ATTRIBUTE9 in VARCHAR2,
P_ATTRIBUTE10 in VARCHAR2,
P_ATTRIBUTE11 in VARCHAR2,
P_ATTRIBUTE12 in VARCHAR2,
P_ATTRIBUTE13 in VARCHAR2,
P_ATTRIBUTE14 in VARCHAR2,
P_ATTRIBUTE15 in VARCHAR2,
P_REQUEST_ID in NUMBER,
P_DISPLAY_NAME in VARCHAR2,
P_DESCRIPTION in VARCHAR2,
P_LAST_UPDATE_DATE in DATE,
P_LAST_UPDATED_BY in NUMBER,
P_LAST_UPDATE_LOGIN in NUMBER
) IS
l_api_version CONSTANT NUMBER := 1.0;
rec.LAST_UPDATE_DATE := P_LAST_UPDATE_DATE;
rec.LAST_UPDATED_BY := P_LAST_UPDATED_BY;
rec.LAST_UPDATE_LOGIN := P_LAST_UPDATE_LOGIN;
rec.PROGRAM_UPDATE_DATE := NULL;
Update_Row (p_alt_desig_rec => rec
,x_return_status => x_return_status
,x_errorcode => x_errorcode
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
PROCEDURE Update_Row (
p_alt_desig_rec IN Bom_Alt_Desig_Rec
,x_return_status OUT NOCOPY VARCHAR2
,x_errorcode OUT NOCOPY NUMBER
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Alternate';
SAVEPOINT Update_Alternate_PUB;
UPDATE bom_alternate_designators
SET is_preferred ='N'
WHERE
structure_type_id = p_alt_desig_rec.structure_type_id
AND is_preferred ='Y';
UPDATE BOM_ALTERNATE_DESIGNATORS
SET
ALTERNATE_DESIGNATOR_CODE = p_alt_desig_rec.ALTERNATE_DESIGNATOR_CODE,
DESCRIPTION = p_alt_desig_rec.DESCRIPTION,
DISABLE_DATE = p_alt_desig_rec.DISABLE_DATE,
STRUCTURE_TYPE_ID = p_alt_desig_rec.STRUCTURE_TYPE_ID,
ATTRIBUTE_CATEGORY = p_alt_desig_rec.ATTRIBUTE_CATEGORY,
ATTRIBUTE1 = p_alt_desig_rec.ATTRIBUTE1,
ATTRIBUTE2 = p_alt_desig_rec.ATTRIBUTE2,
ATTRIBUTE3 = p_alt_desig_rec.ATTRIBUTE3,
ATTRIBUTE4 = p_alt_desig_rec.ATTRIBUTE4,
ATTRIBUTE5 = p_alt_desig_rec.ATTRIBUTE5,
ATTRIBUTE6 = p_alt_desig_rec.ATTRIBUTE6,
ATTRIBUTE7 = p_alt_desig_rec.ATTRIBUTE7,
ATTRIBUTE8 = p_alt_desig_rec.ATTRIBUTE8,
ATTRIBUTE9 = p_alt_desig_rec.ATTRIBUTE9,
ATTRIBUTE10 = p_alt_desig_rec.ATTRIBUTE10,
ATTRIBUTE11 = p_alt_desig_rec.ATTRIBUTE11,
ATTRIBUTE12 = p_alt_desig_rec.ATTRIBUTE12,
ATTRIBUTE13 = p_alt_desig_rec.ATTRIBUTE13,
ATTRIBUTE14 = p_alt_desig_rec.ATTRIBUTE14,
ATTRIBUTE15 = p_alt_desig_rec.ATTRIBUTE15,
REQUEST_ID = p_alt_desig_rec.REQUEST_ID,
LAST_UPDATE_DATE = p_alt_desig_rec.LAST_UPDATE_DATE,
LAST_UPDATED_BY = p_alt_desig_rec.LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = p_alt_desig_rec.LAST_UPDATE_LOGIN,
IS_PREFERRED = p_alt_desig_rec.IS_PREFERRED
WHERE
ALTERNATE_DESIGNATOR_CODE = p_alt_desig_rec.alt_desig_code_old
AND ORGANIZATION_ID = p_alt_desig_rec.ORGANIZATION_ID;
UPDATE BOM_ALTERNATE_DESIGNATORS_TL
SET
ALTERNATE_DESIGNATOR_CODE = p_alt_desig_rec.ALTERNATE_DESIGNATOR_CODE,
DESCRIPTION = p_alt_desig_rec.DESCRIPTION,
DISPLAY_NAME = p_alt_desig_rec.DISPLAY_NAME,
LAST_UPDATE_DATE = p_alt_desig_rec.LAST_UPDATE_DATE,
LAST_UPDATED_BY = p_alt_desig_rec.LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = p_alt_desig_rec.LAST_UPDATE_LOGIN,
SOURCE_LANG = userenv('LANG')
WHERE
ALTERNATE_DESIGNATOR_CODE = p_alt_desig_rec.alt_desig_code_old
and ORGANIZATION_ID = p_alt_desig_rec.ORGANIZATION_ID
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
UPDATE BOM_ALTERNATE_DESIGNATORS
SET
ALTERNATE_DESIGNATOR_CODE = p_alt_desig_rec.ALTERNATE_DESIGNATOR_CODE,
DESCRIPTION = p_alt_desig_rec.description,
DISABLE_DATE = p_alt_desig_rec.disable_date,
STRUCTURE_TYPE_ID = p_alt_desig_rec.structure_type_id,
ATTRIBUTE_CATEGORY = p_alt_desig_rec.ATTRIBUTE_CATEGORY,
ATTRIBUTE1 = p_alt_desig_rec.ATTRIBUTE1,
ATTRIBUTE2 = p_alt_desig_rec.ATTRIBUTE2,
ATTRIBUTE3 = p_alt_desig_rec.ATTRIBUTE3,
ATTRIBUTE4 = p_alt_desig_rec.ATTRIBUTE4,
ATTRIBUTE5 = p_alt_desig_rec.ATTRIBUTE5,
ATTRIBUTE6 = p_alt_desig_rec.ATTRIBUTE6,
ATTRIBUTE7 = p_alt_desig_rec.ATTRIBUTE7,
ATTRIBUTE8 = p_alt_desig_rec.ATTRIBUTE8,
ATTRIBUTE9 = p_alt_desig_rec.ATTRIBUTE9,
ATTRIBUTE10 = p_alt_desig_rec.ATTRIBUTE10,
ATTRIBUTE11 = p_alt_desig_rec.ATTRIBUTE11,
ATTRIBUTE12 = p_alt_desig_rec.ATTRIBUTE12,
ATTRIBUTE13 = p_alt_desig_rec.ATTRIBUTE13,
ATTRIBUTE14 = p_alt_desig_rec.ATTRIBUTE14,
ATTRIBUTE15 = p_alt_desig_rec.ATTRIBUTE15,
REQUEST_ID = p_alt_desig_rec.REQUEST_ID,
LAST_UPDATE_DATE = p_alt_desig_rec.LAST_UPDATE_DATE,
LAST_UPDATED_BY = p_alt_desig_rec.LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = p_alt_desig_rec.LAST_UPDATE_LOGIN
WHERE
ALTERNATE_DESIGNATOR_CODE = p_alt_desig_rec.alt_desig_code_old -- 4054618
AND ORGANIZATION_ID = p_alt_desig_rec.ORGANIZATION_ID;
UPDATE BOM_ALTERNATE_DESIGNATORS_TL
SET
ALTERNATE_DESIGNATOR_CODE = p_alt_desig_rec.ALTERNATE_DESIGNATOR_CODE,
DESCRIPTION = p_alt_desig_rec.description,
DISPLAY_NAME = p_alt_desig_rec.display_name,
LAST_UPDATE_DATE = p_alt_desig_rec.LAST_UPDATE_DATE,
LAST_UPDATED_BY = p_alt_desig_rec.LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = p_alt_desig_rec.LAST_UPDATE_LOGIN,
SOURCE_LANG = userenv('LANG')
WHERE
ALTERNATE_DESIGNATOR_CODE = p_alt_desig_rec.alt_desig_code_old
and ORGANIZATION_ID = p_alt_desig_rec.organization_id
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
ROLLBACK TO Update_Alternate_PUB;
END Update_Row;
procedure DELETE_ROW ( ---- Called from form BOMFDBAD.fmb
P_ALTERNATE_DESIGNATOR_CODE in VARCHAR2,
P_ORGANIZATION_ID in NUMBER
) IS
l_api_version CONSTANT NUMBER := 1.0;
Delete_Row (
p_api_version => l_api_version
,p_alt_desig_code => P_ALTERNATE_DESIGNATOR_CODE
,p_organization_id => P_ORGANIZATION_ID
,x_return_status => x_return_status
,x_errorcode => x_errorcode
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
END DELETE_ROW;
cursor c is select
STRUCTURE_TYPE_ID,
DISABLE_DATE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
REQUEST_ID
from BOM_ALTERNATE_DESIGNATORS
where ALTERNATE_DESIGNATOR_CODE = P_ALTERNATE_DESIGNATOR_CODE
and ORGANIZATION_ID = P_ORGANIZATION_ID
for update of ALTERNATE_DESIGNATOR_CODE nowait;
cursor c1 is select
-- DISPLAY_NAME,
DESCRIPTION,
decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
from BOM_ALTERNATE_DESIGNATORS_TL
where ALTERNATE_DESIGNATOR_CODE = P_ALTERNATE_DESIGNATOR_CODE
and ORGANIZATION_ID = P_ORGANIZATION_ID
and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
for update of ALTERNATE_DESIGNATOR_CODE nowait;
fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
select distinct alternate_designator_code from bom_alternate_designators
where is_preferred ='Y'
and structure_type_id = p_structure_type_id
and alternate_designator_code <> p_alt_code;
SELECT display_name,
description,
NULL disable_date,
structure_type_id,
is_preferred
FROM bom_alternate_designators_vl badv
WHERE badv.alternate_designator_code = cp_alt_desig_code
AND badv.organization_id = cp_org_id;
insert_row (
p_api_version => 1.0
,p_alt_desig_code => p_alt_desig_code
,p_organization_id => p_to_org_id
,p_display_name => l_display_name
,p_description => l_description
,p_disable_date => l_disable_date
,p_structure_type_id => l_structure_type_id
,p_is_preferred => l_is_preferred
,x_return_status => x_return_status
,x_errorcode => x_errorcode
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
p_program_update_date IN DATE,
p_creation_date IN DATE,
p_created_by IN NUMBER,
p_last_update_date IN DATE,
p_last_updated_by IN NUMBER,
p_last_update_login IN NUMBER,
p_custom_mode IN VARCHAR2,
p_is_preferred IN VARCHAR2)
IS
CURSOR GET_ALL_ORGS IS
SELECT organization_id
FROM mtl_parameters ;
p_program_update_date => p_program_update_date,
p_creation_date => p_creation_date,
p_created_by => p_created_by,
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_custom_mode => p_custom_mode,
p_is_preferred => p_is_preferred
);
p_program_update_date => p_program_update_date,
p_creation_date => p_creation_date,
p_created_by => p_created_by,
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_custom_mode => p_custom_mode,
p_is_preferred => p_is_preferred
);
p_program_update_date IN DATE,
p_creation_date IN DATE,
p_created_by IN NUMBER,
p_last_update_date IN DATE,
p_last_updated_by IN NUMBER,
p_last_update_login IN NUMBER,
p_custom_mode IN VARCHAR2,
p_is_preferred IN VARCHAR2)
IS
db_luby NUMBER; -- entity owner in db
db_ludate DATE; -- entity update date in db
SELECT
bad.LAST_UPDATED_BY, bad.LAST_UPDATE_DATE
INTO
db_luby, db_ludate
FROM
BOM_ALTERNATE_DESIGNATORS bad
WHERE
(
(
p_alternate_designator_code IS NULL
AND bad.ALTERNATE_DESIGNATOR_CODE IS NULL
)
OR
(
p_alternate_designator_code = bad.ALTERNATE_DESIGNATOR_CODE
)
)
AND ( bad.ORGANIZATION_ID = p_organization_id
or (bad.organization_id is null and p_organization_id is null));
IF ( FND_LOAD_UTIL.UPLOAD_TEST(p_last_updated_by, p_last_update_date, db_luby, db_ludate, p_custom_mode) )
THEN
-- When is_preferred is set for current structure name and there exists another preferred structure name already
-- then reset that value.
IF (p_is_preferred = 'Y' )
THEN
old_preferred_name := Get_Preferred_Name(p_structure_type_id, p_alternate_designator_code);
UPDATE BOM_ALTERNATE_DESIGNATORS
SET is_preferred ='N'
WHERE alternate_designator_code = old_preferred_name;
UPDATE BOM_ALTERNATE_DESIGNATORS
SET
DESCRIPTION = NVL(p_description, DESCRIPTION),
DISABLE_DATE = p_disable_date,
STRUCTURE_TYPE_ID = p_structure_type_id,
ATTRIBUTE_CATEGORY = p_attribute_category,
ATTRIBUTE1 = p_attribute1,
ATTRIBUTE2 = p_attribute2,
ATTRIBUTE3 = p_attribute3,
ATTRIBUTE4 = p_attribute4,
ATTRIBUTE5 = p_attribute5,
ATTRIBUTE6 = p_attribute6,
ATTRIBUTE7 = p_attribute7,
ATTRIBUTE8 = p_attribute8,
ATTRIBUTE9 = p_attribute9,
ATTRIBUTE10 = p_attribute10,
ATTRIBUTE11 = p_attribute11,
ATTRIBUTE12 = p_attribute12,
ATTRIBUTE13 = p_attribute13,
ATTRIBUTE14 = p_attribute14,
ATTRIBUTE15 = p_attribute15,
REQUEST_ID = p_request_id,
PROGRAM_APPLICATION_ID = p_program_application_id,
PROGRAM_ID = p_program_id,
PROGRAM_UPDATE_DATE = p_program_update_date,
LAST_UPDATE_DATE = p_last_update_date,
LAST_UPDATED_BY = p_last_updated_by,
LAST_UPDATE_LOGIN = p_last_update_login,
IS_PREFERRED = p_is_preferred
WHERE
(
(
p_alternate_designator_code IS NULL
AND ALTERNATE_DESIGNATOR_CODE IS NULL
)
OR
( ALTERNATE_DESIGNATOR_CODE = p_alternate_designator_code )
)
AND (ORGANIZATION_ID = p_organization_id
or (organization_id is null and p_organization_id is null));
UPDATE BOM_ALTERNATE_DESIGNATORS_TL
SET
DESCRIPTION = NVL(p_description, DESCRIPTION),
DISPLAY_NAME = NVL(p_display_name, DISPLAY_NAME),
LAST_UPDATE_DATE = p_last_update_date,
LAST_UPDATED_BY = p_last_updated_by,
LAST_UPDATE_LOGIN = p_last_update_login,
SOURCE_LANG = userenv('LANG')
WHERE
(
(
p_alternate_designator_code IS NULL
AND ALTERNATE_DESIGNATOR_CODE IS NULL
)
OR
( ALTERNATE_DESIGNATOR_CODE = p_alternate_designator_code )
)
AND ORGANIZATION_ID = p_organization_id
AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
INSERT INTO BOM_ALTERNATE_DESIGNATORS
(
ALTERNATE_DESIGNATOR_CODE
,ORGANIZATION_ID
,DESCRIPTION
,DISABLE_DATE
,STRUCTURE_TYPE_ID
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,REQUEST_ID
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,IS_PREFERRED
)
VALUES
(
p_alternate_designator_code
,p_organization_id
,p_description
,p_disable_date
,p_structure_type_id
,p_attribute_category
,p_attribute1
,p_attribute2
,p_attribute3
,p_attribute4
,p_attribute5
,p_attribute6
,p_attribute7
,p_attribute8
,p_attribute9
,p_attribute10
,p_attribute11
,p_attribute12
,p_attribute13
,p_attribute14
,p_attribute15
,p_request_id
,p_creation_date
,p_created_by
,p_last_update_date
,p_last_updated_by
,p_last_update_login
,p_program_application_id
,p_program_id
,p_program_update_date
,p_is_preferred
);
INSERT INTO BOM_ALTERNATE_DESIGNATORS_TL
(
ALTERNATE_DESIGNATOR_CODE,
ORGANIZATION_ID,
DISPLAY_NAME,
DESCRIPTION,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
LANGUAGE,
SOURCE_LANG
)
SELECT
p_alternate_designator_code,
p_organization_id,
p_display_name,
p_description,
p_last_update_date,
p_last_updated_by,
p_creation_date,
p_created_by,
p_last_update_login,
L.LANGUAGE_CODE,
userenv('LANG')
FROM
FND_LANGUAGES L
WHERE
L.INSTALLED_FLAG IN ('I', 'B')
AND NOT EXISTS
(
SELECT NULL
FROM BOM_ALTERNATE_DESIGNATORS_TL T
WHERE
(
(
p_alternate_designator_code IS NULL
AND T.ALTERNATE_DESIGNATOR_CODE IS NULL
)
OR
( T.ALTERNATE_DESIGNATOR_CODE = p_alternate_designator_code )
)
AND T.ORGANIZATION_ID = p_organization_id
AND T.LANGUAGE = L.LANGUAGE_CODE
);
UPDATE BOM_ALTERNATE_DESIGNATORS
SET is_preferred ='N'
WHERE alternate_designator_code = old_preferred_name;
p_last_update_date IN DATE,
p_last_updated_by IN NUMBER,
p_last_update_login IN NUMBER,
p_custom_mode IN VARCHAR2)
IS
db_luby NUMBER; -- entity owner in db
db_ludate DATE; -- entity update date in db
SELECT
badtl.LAST_UPDATED_BY, badtl.LAST_UPDATE_DATE
INTO
db_luby, db_ludate
FROM
BOM_ALTERNATE_DESIGNATORS_TL badtl
WHERE
badtl.LANGUAGE = userenv('LANG')
AND
(
(
p_alternate_designator_code IS NULL
AND badtl.ALTERNATE_DESIGNATOR_CODE IS NULL
)
OR
(
p_alternate_designator_code = badtl.ALTERNATE_DESIGNATOR_CODE
)
)
AND badtl.ORGANIZATION_ID = p_organization_id;
IF ( FND_LOAD_UTIL.UPLOAD_TEST(p_last_updated_by, p_last_update_date, db_luby, db_ludate, p_custom_mode) )
THEN
-- Update translations for this language
UPDATE BOM_ALTERNATE_DESIGNATORS_TL
SET
DISPLAY_NAME = NVL(p_display_name, DISPLAY_NAME),
DESCRIPTION = NVL(p_description, DESCRIPTION),
LAST_UPDATE_DATE = p_last_update_date,
LAST_UPDATED_BY = p_last_updated_by,
LAST_UPDATE_LOGIN = p_last_update_login,
SOURCE_LANG = userenv('LANG')
WHERE
userenv('LANG') IN (LANGUAGE, SOURCE_LANG)
AND
(
(
p_alternate_designator_code IS NULL
AND ALTERNATE_DESIGNATOR_CODE IS NULL
)
OR
(
p_alternate_designator_code = ALTERNATE_DESIGNATOR_CODE
)
)
AND ORGANIZATION_ID = p_organization_id;