[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_programupdatedate DATE;
l_programupdatedate := p_tran_attrs_tbl(i).programupdatedate;
SELECT Count(*) CNT
INTO l_versioned_value_set
FROM EGO_FLEX_VALUESET_VERSION_B
WHERE FLEX_VALUE_SET_ID = l_value_set_id
AND VERSION_SEQ_ID>0;
SELECT EGO_TRANS_AG_S.NEXTVAL INTO l_ag_seq_value FROM dual;
SELECT max(data_level_id) INTO l_data_level_id
FROM ego_data_level_b
WHERE application_id = G_APPLICATION_ID;
/* INSERT record into ego_attr_group_dl*/
INSERT INTO ego_attr_group_dl
(attr_group_id
,data_level_id
,defaulting
,view_privilege_id
,edit_privilege_id
,raise_pre_event
,raise_post_event
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login)
VALUES(l_attr_group_id,l_data_level_id,
null,null,null,'N','N',G_CURRENT_USER_ID,SYSDATE,G_CURRENT_USER_ID,SYSDATE,G_CURRENT_LOGIN_ID);
SELECT MEANING
INTO l_is_column_indexed
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'YES_NO'
AND LANGUAGE = USERENV('LANG')
AND VIEW_APPLICATION_ID = 0
AND LOOKUP_CODE = 'Y';
SELECT object_id INTO l_item_obj_id FROM fnd_objects WHERE obj_name = 'EGO_ITEM';
SELECT ASSOCIATION_ID INTO l_association_id
FROM EGO_OBJ_AG_ASSOCS_B
WHERE CLASSIFICATION_CODE= l_item_cat_group_id
AND ATTR_GROUP_ID= l_attr_group_id
AND OBJECT_ID= l_item_obj_id;
SELECT attr_id INTO l_attr_id
FROM EGO_FND_DF_COL_USGS_EXT
WHERE APPLICATION_ID = G_APPLICATION_ID
AND DESCRIPTIVE_FLEXFIELD_NAME = l_ag_type
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = l_ag_int_name
AND APPLICATION_COLUMN_NAME = l_column;
/* INSERTING values in tables*/
BEGIN
INSERT INTO EGO_TRANS_ATTR_VERS_B
(association_id,attr_id,icc_version_number,attr_display_name,"SEQUENCE",value_set_id,uom_class,
default_value,rejected_value,required_flag,readonly_flag,hidden_flag, searchable_flag,
check_eligibility,inventory_item_id,organization_id, revision_id,metadata_level,created_by,
creation_date,last_updated_by,last_update_date,last_update_login,program_application_id,
program_id,program_update_date,request_id,item_catalog_group_id)
VALUES(l_association_id,l_attr_id,l_icc_version_number,l_attr_disp_name,
l_attr_sequence,l_value_set_id,l_uom_class,l_default_value,l_rejectedvalue,l_required,l_readonlyflag,
l_hiddenflag,l_searchable,l_checkeligibility,l_inventoryitemid,l_organizationid,l_revision_id,
l_metadatalevel,G_CURRENT_USER_ID,SYSDATE,G_CURRENT_USER_ID,SYSDATE,G_CURRENT_LOGIN_ID,
l_programapplicationid,l_programid,l_programupdatedate,l_requestid,l_item_cat_group_id);
x_msg_data := 'TA_INSERT_FAILED';
/*SELECT * FROM fnd_new_messages WHERE message_name LIKE 'EGO_PLSQL_ERR%'*/
FND_MSG_PUB.Add;
SELECT item_catalog_group_id,
icc_version_NUMBER ,
SEQUENCE ,
attr_display_name ,
attr_name ,
attr_id ,
lev
FROM
(SELECT versions.item_catalog_group_id,
versions.icc_version_NUMBER ,
versions.SEQUENCE ,
attrs.attr_display_name ,
attrs.attr_name ,
attrs.attr_id ,
hier.lev
FROM ego_obj_AG_assocs_b assocs ,
ego_attrs_v attrs ,
ego_attr_groups_v ag ,
EGO_TRANS_ATTR_VERS_B versions ,
mtl_item_catalog_groups_kfv icv ,
(SELECT item_catalog_group_id ,
LEVEL lev
FROM mtl_item_catalog_groups_b START
WITH item_catalog_group_id = p_item_cat_group_id CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
) hier
WHERE ag.attr_group_type = 'EGO_ITEM_TRANS_ATTR_GROUP'
AND assocs.attr_group_id = ag.attr_group_id
AND assocs.classification_code = TO_CHAR(hier.item_catalog_group_id)
AND attrs.attr_group_name = ag.attr_group_name
AND TO_CHAR(icv.item_catalog_group_id) = assocs.classification_code
AND TO_CHAR(versions.association_id) = assocs.association_id
AND TO_CHAR(versions.item_catalog_group_id) = assocs.classification_code
AND attrs.attr_id = versions.attr_id
)
WHERE
(
(
LEV = 1
AND ICC_VERSION_NUMBER = p_icc_version_number -- bug 9980051
)
OR
(
LEV <> 1
AND
(
item_catalog_group_id, ICC_VERSION_NUMBER
)
IN
(SELECT item_catalog_group_id,
VERSION_SEQ_ID
FROM EGO_MTL_CATALOG_GRP_VERS_B
WHERE start_active_date <=
(SELECT NVL(start_active_date,SYSDATE)
FROM EGO_MTL_CATALOG_GRP_VERS_B
WHERE ITEM_CATALOG_GROUP_ID = p_item_cat_group_id
AND VERSION_SEQ_ID = p_icc_version_number -- bug 9980051
)
AND NVL(end_active_date, sysdate) >=
(SELECT NVL(start_active_date,SYSDATE)
FROM EGO_MTL_CATALOG_GRP_VERS_B
WHERE ITEM_CATALOG_GROUP_ID = p_item_cat_group_id
AND VERSION_SEQ_ID = p_icc_version_number -- bug 9980051
)
AND version_seq_id > 0
)
)
); --end CURSOR cur_list
SELECT *
FROM
(SELECT *
FROM
(SELECT versions.item_catalog_group_id,
versions.ICC_VERSION_NUMBER ,
versions.ATTR_ID ,
versions.SEQUENCE ,
versions.attr_display_name ,
versions.metadata_level ,
attrs.attr_name ,
Hier.lev
FROM EGO_TRANS_ATTR_VERS_B VERSIONS,
EGO_ATTRS_V ATTRS ,
(SELECT ITEM_CATALOG_GROUP_ID ,
LEVEL LEV
FROM MTL_ITEM_CATALOG_GROUPS_B START
WITH ITEM_CATALOG_GROUP_ID = p_item_cat_group_id CONNECT BY PRIOR PARENT_CATALOG_GROUP_ID =ITEM_CATALOG_GROUP_ID
) HIER
WHERE HIER.ITEM_CATALOG_GROUP_ID = versions.item_catalog_group_id
AND attrs.attr_id = versions.attr_id
AND attrs.attr_group_type ='EGO_ITEM_TRANS_ATTR_GROUP'
AND versions.metadata_level ='ICC'
)
WHERE
(
(
LEV = 1
AND ICC_VERSION_number = p_icc_version_number -- bug 9980051
)
OR
(
LEV <> 1
AND
(
item_catalog_group_id, ICC_VERSION_NUMBER
)
IN
(SELECT item_catalog_group_id,
VERSION_SEQ_ID
FROM EGO_MTL_CATALOG_GRP_VERS_B
WHERE
(
item_catalog_group_id,start_active_date
)
IN
(SELECT item_catalog_group_id,
MAX(start_active_date) start_active_date
FROM EGO_MTL_CATALOG_GRP_VERS_B
WHERE NVL(end_active_date, sysdate) >=
(SELECT NVL(start_active_date,SYSDATE)
FROM EGO_MTL_CATALOG_GRP_VERS_B
WHERE ITEM_CATALOG_GROUP_ID = p_item_cat_group_id
AND VERSION_SEQ_ID = p_icc_version_number -- bug 9980051
)
AND version_seq_id > 0
AND start_active_date <=
(SELECT NVL(start_active_date,SYSDATE)
FROM EGO_MTL_CATALOG_GRP_VERS_B
WHERE ITEM_CATALOG_GROUP_ID = p_item_cat_group_id
AND VERSION_SEQ_ID = p_icc_version_number -- bug 9980051
)
GROUP BY item_catalog_group_id
HAVING MAX(start_active_date)<=
(SELECT NVL(start_active_date,SYSDATE)
FROM EGO_MTL_CATALOG_GRP_VERS_B
WHERE ITEM_CATALOG_GROUP_ID = p_item_cat_group_id
AND VERSION_SEQ_ID = p_icc_version_number -- bug 9980051
)
)
)
)
)
)
WHERE
(
lev,attr_id
)
IN
(SELECT MIN(lev),
attr_id
FROM
(SELECT versions.item_catalog_group_id,
versions.ICC_VERSION_NUMBER ,
versions.ATTR_ID ,
versions.SEQUENCE ,
versions.attr_display_name ,
versions.metadata_level ,
Hier.lev
FROM EGO_TRANS_ATTR_VERS_B VERSIONS,
(SELECT ITEM_CATALOG_GROUP_ID ,
LEVEL LEV
FROM MTL_ITEM_CATALOG_GROUPS_B
START WITH ITEM_CATALOG_GROUP_ID = p_item_cat_group_id
CONNECT BY PRIOR PARENT_CATALOG_GROUP_ID =ITEM_CATALOG_GROUP_ID
) HIER
WHERE HIER.ITEM_CATALOG_GROUP_ID = versions.item_catalog_group_id
AND versions.metadata_level ='ICC'
AND versions.attr_display_name IS NOT NULL
)
WHERE
(
(
LEV =1
AND ICC_VERSION_number = p_icc_version_number -- bug 9980051
)
OR
(
LEV <> 1
AND
(
item_catalog_group_id, ICC_VERSION_NUMBER
)
IN
(SELECT item_catalog_group_id,
VERSION_SEQ_ID
FROM EGO_MTL_CATALOG_GRP_VERS_B
WHERE
(
item_catalog_group_id,start_active_date
)
IN
(SELECT item_catalog_group_id,
MAX(start_active_date) start_active_date
FROM EGO_MTL_CATALOG_GRP_VERS_B
WHERE NVL(end_active_date, sysdate) >=
(SELECT NVL(start_active_date,SYSDATE)
FROM EGO_MTL_CATALOG_GRP_VERS_B
WHERE ITEM_CATALOG_GROUP_ID = p_item_cat_group_id
AND VERSION_SEQ_ID = p_icc_version_number -- bug 9980051
)
AND version_seq_id > 0
AND start_active_date <=
(SELECT NVL(start_active_date,SYSDATE)
FROM EGO_MTL_CATALOG_GRP_VERS_B
WHERE ITEM_CATALOG_GROUP_ID = p_item_cat_group_id
AND VERSION_SEQ_ID = p_icc_version_number -- bug 9980051
)
GROUP BY item_catalog_group_id
HAVING MAX(start_active_date)<=
(SELECT NVL(start_active_date,SYSDATE)
FROM EGO_MTL_CATALOG_GRP_VERS_B
WHERE ITEM_CATALOG_GROUP_ID = p_item_cat_group_id
AND VERSION_SEQ_ID = p_icc_version_number -- bug 9980051
)
)
)
)
--AND metadata_level ='ICC'
)
GROUP BY attr_id
)
AND attr_id=l_attr_id
AND attr_id<> nvl(p_attr_id,-1); -- bug 9980051
SELECT * FROM
(SELECT versions.item_catalog_group_id,
versions.ICC_VERSION_NUMBER ,
versions.ATTR_ID ,
versions.attr_display_name ,
versions.metadata_level ,
versions.association_id ,
VERSIONS.VALUE_SET_ID ,
VERSIONS.UOM_CLASS ,
VERSIONS.DEFAULT_VALUE ,
versions.revision_id ,
versions.organization_id ,
versions.inventory_item_id ,
VERSIONS.REJECTED_VALUE ,
VERSIONS.REQUIRED_FLAG ,
VERSIONS.READONLY_FLAG ,
VERSIONS.HIDDEN_FLAG ,
VERSIONS.SEARCHABLE_FLAG ,
VERSIONS.CHECK_ELIGIBILITY ,
Hier.lev
FROM EGO_TRANS_ATTR_VERS_B VERSIONS,
(SELECT ITEM_CATALOG_GROUP_ID ,
LEVEL LEV
FROM MTL_ITEM_CATALOG_GROUPS_B
START WITH ITEM_CATALOG_GROUP_ID = CP_ITEM_CAT_GROUP_ID
CONNECT BY PRIOR PARENT_CATALOG_GROUP_ID =ITEM_CATALOG_GROUP_ID
) HIER
WHERE HIER.ITEM_CATALOG_GROUP_ID = versions.item_catalog_group_id
AND versions.attr_id = CP_ATTR_ID
)
WHERE
(
/*(
LEV =1
AND ICC_VERSION_number = 0
AND metadata_level ='ICC'
)
OR */
(
LEV > 1
AND metadata_level='ICC'
AND
(
item_catalog_group_id, ICC_VERSION_NUMBER
)
IN
(SELECT item_catalog_group_id,
VERSION_SEQ_ID
FROM EGO_MTL_CATALOG_GRP_VERS_B
WHERE start_active_date <=
(SELECT NVL(start_active_date,SYSDATE)
FROM EGO_MTl_CATALOG_GRP_VERS_B
WHERE ITEM_CATALOG_GROUP_ID = CP_ITEM_CAT_GROUP_ID
AND VERSION_SEQ_ID = 0
)
AND NVL(end_active_date, sysdate) >=
(SELECT NVL(start_active_date,SYSDATE)
FROM EGO_MTl_CATALOG_GRP_VERS_B
WHERE ITEM_CATALOG_GROUP_ID = CP_ITEM_CAT_GROUP_ID
AND VERSION_SEQ_ID = 0
)
AND version_seq_id > 0
)
)
)
ORDER BY Lev ASC ;
p_tran_attrs_tbl(i).LastUpdatedBy,
p_tran_attrs_tbl(i).LastUpdateDate,
p_tran_attrs_tbl(i).LastUpdateLogin,
p_tran_attrs_tbl(i).ProgramApplicationId,
p_tran_attrs_tbl(i).ProgramId,
p_tran_attrs_tbl(i).ProgramUpdateDate,
p_tran_attrs_tbl(i).RequestId,
p_tran_attrs_tbl(i).ItemCatalogGroupId,
p_tran_attrs_tbl(i).AttrName,
--p_tran_attrs_tbl(i).AttrDisplayName,
l_out_attr_disp_name,
p_tran_attrs_tbl(i).DataType,
p_tran_attrs_tbl(i).DisplayAs ,
p_tran_attrs_tbl(i).ValueSetName
--)
);
p_tran_attrs_tbl(i).LastUpdatedBy,
p_tran_attrs_tbl(i).LastUpdateDate,
p_tran_attrs_tbl(i).LastUpdateLogin,
p_tran_attrs_tbl(i).ProgramApplicationId,
p_tran_attrs_tbl(i).ProgramId,
p_tran_attrs_tbl(i).ProgramUpdateDate,
p_tran_attrs_tbl(i).RequestId,
p_tran_attrs_tbl(i).ItemCatalogGroupId,
p_tran_attrs_tbl(i).AttrName,
p_tran_attrs_tbl(i).AttrDisplayName,
p_tran_attrs_tbl(i).DataType,
p_tran_attrs_tbl(i).DisplayAs ,
p_tran_attrs_tbl(i).ValueSetName
--)
);
l_programupdatedate DATE;
l_programupdatedate := l_ta_metadata_tbl(i).programupdatedate;
SELECT COUNT(*) CNT
INTO l_versioned_value_set
FROM EGO_FLEX_VALUESET_VERSION_B
WHERE FLEX_VALUE_SET_ID = l_value_set_id
AND VERSION_SEQ_ID >0;
/* checking whether any of the columns are updated before inserting
bug 8356736 */
IF(l_attr_disp_name IS NOT NULL OR l_default_value IS NOT NULL OR l_rejectedvalue IS NOT NULL OR l_required IS NOT NULL OR
l_readonlyflag IS NOT NULL OR l_hiddenflag IS NOT NULL OR l_searchable IS NOT NULL OR l_checkeligibility IS NOT NULL ) THEN
/* INSERTING values in tables*/
BEGIN
INSERT INTO EGO_TRANS_ATTR_VERS_B
(association_id,
attr_id,
icc_version_number,
attr_display_name,
sequence,
value_set_id,
uom_class,
default_value,
rejected_value,
required_flag,
readonly_flag,
hidden_flag,
searchable_flag,
check_eligibility,
inventory_item_id,
organization_id,
revision_id,
metadata_level,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
program_application_id,
program_id,
program_update_date,
request_id,
item_catalog_group_id)
VALUES (l_association_id ,
l_attr_id ,
l_icc_version_number ,
l_attr_disp_name ,
l_attr_sequence ,
l_value_set_id ,
l_uom_class ,
l_default_value ,
l_rejectedvalue ,
l_required ,
l_readonlyflag ,
l_hiddenflag ,
l_searchable ,
l_checkeligibility ,
l_inventoryitemid ,
l_organizationid ,
l_revision_id ,
l_metadatalevel ,
G_CURRENT_USER_ID ,
SYSDATE ,
G_CURRENT_USER_ID ,
SYSDATE ,
G_CURRENT_LOGIN_ID ,
l_programapplicationid,
l_programid ,
l_programupdatedate ,
l_requestid ,
l_item_cat_group_id
);
x_msg_data := 'TA_REC_INSERT_FAILED';
PROCEDURE Update_Transaction_Attribute (
p_api_version IN NUMBER,
p_tran_attrs_tbl IN EGO_TRAN_ATTR_TBL,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
/* Declaring local parameters*/
l_attr_desc VARCHAR2(100); --confirm about size
l_api_name CONSTANT VARCHAR2(30) := 'Update_Transaction_Attribute';
UPDATE EGO_TRANS_ATTR_VERS_B
SET "SEQUENCE" =l_attr_sequence,
ATTR_DISPLAY_NAME = l_attr_disp_name,
value_set_id = l_value_set_id,
uom_class = l_uom_class,
default_value = l_default_value,
rejected_value = l_rejectedvalue,
required_flag = l_required,
readonly_flag = l_readonlyflag,
hidden_flag = l_hiddenflag,
searchable_flag = l_searchable,
check_eligibility = l_checkeligibility,
last_updated_by = G_CURRENT_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_CURRENT_LOGIN_ID
WHERE ASSOCIATION_ID= l_association_id
AND ATTR_ID =l_attr_id
AND INVENTORY_ITEM_ID = l_inventoryitemid
AND ORGANIZATION_ID = l_organizationid
AND REVISION_ID = l_revisionid
AND metadata_level = 'ITM';
UPDATE EGO_TRANS_ATTR_VERS_B
SET "SEQUENCE" =l_attr_sequence,
ATTR_DISPLAY_NAME = l_attr_disp_name,
value_set_id = l_value_set_id,
uom_class = l_uom_class,
default_value = l_default_value,
rejected_value = l_rejectedvalue,
required_flag = l_required,
readonly_flag = l_readonlyflag,
hidden_flag = l_hiddenflag,
searchable_flag = l_searchable,
check_eligibility = l_checkeligibility,
last_updated_by = G_CURRENT_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_CURRENT_LOGIN_ID
WHERE ASSOCIATION_ID= l_association_id
AND ATTR_ID =l_attr_id
AND ITEM_CATALOG_GROUP_ID = l_item_cat_group_id
AND ICC_VERSION_NUMBER =0
AND metadata_level = 'ICC';
SELECT attr_group_name INTO l_ag_int_name
FROM EGO_OBJ_ATTR_GRP_ASSOCS_V
WHERE (object_id, classification_code, attr_group_id) IN
(SELECT object_id, classification_code, attr_group_id
FROM ego_obj_ag_assocs_b
where association_id = l_association_id);
/*EGO_EXT_FWK_PUB.Update_Attribute (
p_api_version => 1.0
,p_application_id => G_APPLICATION_ID
,p_attr_group_type => l_ag_type
,p_attr_group_name => l_ag_int_name
,p_internal_name => l_attr_name
,p_display_name => l_attr_disp_name
,p_description => l_attr_desc
,p_sequence => l_attr_sequence
,p_required => l_required
,p_searchable => l_searchable
,p_column => l_column
,p_value_set_id => l_value_set_id
,p_info_1 => null
,p_default_value => l_default_value
,p_unique_key_flag => null
,p_enabled => 'Y'
,p_display => l_display
,p_control_level => -1
,p_attribute_code => G_MISS_CHAR
,p_view_in_hierarchy_code => G_MISS_CHAR
,p_edit_in_hierarchy_code => G_MISS_CHAR
,p_customization_level => G_MISS_CHAR
,p_owner => NULL
,p_lud => SYSDATE
,p_init_msg_list => null
,p_commit => null
,p_is_nls_mode => FND_API.G_FALSE
,p_uom_class => l_uom_class
,x_return_status => l_return_status
,x_errorcode => l_errorcode
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);*/
END Update_Transaction_Attribute;
PROCEDURE Delete_Transaction_Attribute (
p_api_version IN NUMBER,
p_association_id IN NUMBER,
p_attr_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Transaction_Attribute';
DELETE FROM EGO_TRANS_ATTR_VERS_B
WHERE ASSOCIATION_ID= p_association_id
AND ATTR_ID =p_attr_id
AND ICC_VERSION_NUMBER=0;
END Delete_Transaction_Attribute;
PROCEDURE Delete_Transaction_Attribute (
p_api_version IN NUMBER,
p_tran_attrs_tbl IN EGO_TRAN_ATTR_TBL,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
/* Declaring local parameters*/
l_association_id EGO_TRANS_ATTR_VERS_B.ASSOCIATION_ID%TYPE;
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Transaction_Attribute';
DELETE FROM EGO_TRANS_ATTR_VERS_B
WHERE ASSOCIATION_ID= l_association_id
AND ATTR_ID =l_attr_id
AND ICC_VERSION_NUMBER=0;
END Delete_Transaction_Attribute;
SELECT Max(icc_version_number) maxver
FROM EGO_TRANS_ATTR_VERS_B
WHERE ITEM_CATALOG_GROUP_ID=l_item_cat_group_id;
SELECT *
FROM EGO_TRANS_ATTR_VERS_B
WHERE ITEM_CATALOG_GROUP_ID=l_item_cat_group_id
AND ICC_VERSION_NUMBER =0;
INSERT INTO EGO_TRANS_ATTR_VERS_B
(association_id,attr_id,icc_version_number,attr_display_name,"SEQUENCE",value_set_id,uom_class,
default_value,rejected_value,required_flag,readonly_flag,hidden_flag, searchable_flag,
check_eligibility,inventory_item_id,organization_id, revision_id,metadata_level,created_by,
creation_date,last_updated_by,last_update_date,last_update_login,program_application_id,
program_id,program_update_date,request_id,item_catalog_group_id)
VALUES (i.association_id,i.attr_id,l_icc_version_number,i.attr_display_name,i.SEQUENCE,i.value_set_id,i.uom_class,
i.default_value,i.rejected_value,i.required_flag,i.readonly_flag,i.hidden_flag,i.searchable_flag,
i.check_eligibility,i.inventory_item_id,i.organization_id,i.revision_id,i.metadata_level,G_CURRENT_USER_ID,
sysdate,G_CURRENT_USER_ID,SYSDATE,G_CURRENT_LOGIN_ID,i.program_application_id,
i.program_id,i.program_update_date,i.request_id,i.item_catalog_group_id);
SELECT *
FROM EGO_TRANS_ATTR_VERS_B
WHERE ITEM_CATALOG_GROUP_ID=l_item_cat_group_id
AND ICC_VERSION_NUMBER =0;*/
INSERT INTO EGO_TRANS_ATTR_VERS_B
(SELECT association_id,attr_id,icc_version_number,attr_display_name,SEQUENCE,value_set_id,uom_class,
default_value,rejected_value,required_flag,readonly_flag,hidden_flag,searchable_flag,
check_eligibility,p_dest_item_id,p_dest_org_id,p_dest_rev_id,'ITM',G_CURRENT_USER_ID,
sysdate,G_CURRENT_USER_ID,SYSDATE,G_CURRENT_LOGIN_ID,program_application_id,
program_id,program_update_date,request_id,item_catalog_group_id
FROM EGO_TRANS_ATTR_VERS_B
WHERE inventory_item_id =p_sorce_item_id
AND organization_id =p_source_org_id
AND revision_id= p_source_rev_id);
INSERT INTO EGO_TRANS_ATTR_VERS_B
(SELECT association_id,attr_id,p_dest_ver_no,attr_display_name,SEQUENCE,value_set_id,uom_class,
default_value,rejected_value,required_flag,readonly_flag,hidden_flag,searchable_flag,
check_eligibility,inventory_item_id,organization_id,revision_id,'ICC',G_CURRENT_USER_ID,
sysdate,G_CURRENT_USER_ID,SYSDATE,G_CURRENT_LOGIN_ID,program_application_id,
program_id,program_update_date,request_id,p_dest_icc_id
FROM EGO_TRANS_ATTR_VERS_B
WHERE ITEM_CATALOG_GROUP_ID =p_source_icc_id
AND ICC_VERSION_NUMBER = p_source_ver_no);
DELETE FROM EGO_TRANS_ATTR_VERS_B
WHERE ITEM_CATALOG_GROUP_ID = p_source_icc_id
AND ICC_VERSION_NUMBER = 0;
SELECT Count(*) cnt
INTO l_vs_valid_data_type
FROM EGO_VS_FORMAT_CODES_V
WHERE lookup_code IN (p_data_type);
SELECT FORMAT_TYPE
INTO l_value_set_format_code
FROM FND_FLEX_VALUE_SETS
WHERE FLEX_VALUE_SET_ID = l_value_set_id;
l_attr_disp_name_sql := ' SELECT ATTR_DISPLAY_NAME FROM (';
' SELECT * '||
' FROM '||
' ( SELECT * '||
' FROM '||
' ( SELECT TA_VERS.item_catalog_group_id, '||
' TA_VERS.ICC_VERSION_NUMBER , '||
' TA_VERS.ATTR_ID , '||
' TA_VERS.attr_display_name , '||
' TA_VERS.metadata_level , '||
' TA_VERS.INVENTORY_ITEM_ID , '||
' TA_VERS.ORGANIZATION_ID , '||
' TA_VERS.REVISION_ID , '||
' TA_VERS.VALUE_SET_ID , '||
' HIERLEVEL.lev '||
' FROM EGO_TRANS_ATTR_VERS_B TA_VERS , '||
' ( SELECT ITEM_CATALOG_GROUP_ID , '||
' LEVEL LEV '||
' FROM MTL_ITEM_CATALOG_GROUPS_B '||
' START WITH ITEM_CATALOG_GROUP_ID =:1 '||
' CONNECT BY PRIOR PARENT_CATALOG_GROUP_ID =ITEM_CATALOG_GROUP_ID '||
' ) HIERLEVEL '||
' WHERE HIERLEVEL.ITEM_CATALOG_GROUP_ID = TA_VERS.item_catalog_group_id '||
' ) '||
' WHERE '||
' ( '||
' ( '||
' LEV = 1 '||
' AND ICC_VERSION_NUMBER = :2 '||
' ) '||
' OR '||
' ( '||
' METADATA_LEVEL = ''ITM'''||
' AND INVENTORY_ITEM_ID = :3 '||
' AND ORGANIZATION_ID = :4 '||
' AND REVISION_ID = :5 '||
' ) '||
' OR '||
' ( '||
' LEV > 1 '||
' AND metadata_level=''ICC'''||
' AND '||
' ( '||
' item_catalog_group_id, ICC_VERSION_NUMBER '||
' ) '||
' IN '||
' ( SELECT item_catalog_group_id,VERSION_SEQ_ID '||
' FROM EGO_MTL_CATALOG_GRP_VERS_B ';
' ( SELECT item_catalog_group_id, MAX(start_active_date) start_active_date '||
' FROM EGO_MTL_CATALOG_GRP_VERS_B '||
' WHERE creation_date <= :6 '||
' AND version_seq_id > 0 '||
' AND start_active_date <= :7 '||
' GROUP BY item_catalog_group_id '||
' HAVING MAX(start_active_date)<=:8 '||
' ) ';
l_value_set_sql := ' SELECT VALUE_SET_ID FROM (';
' SELECT * '||
' FROM '||
' ( SELECT * '||
' FROM '||
' ( SELECT TA_VERS.item_catalog_group_id, '||
' TA_VERS.ICC_VERSION_NUMBER , '||
' TA_VERS.ATTR_ID , '||
' TA_VERS.attr_display_name , '||
' TA_VERS.metadata_level , '||
' TA_VERS.INVENTORY_ITEM_ID , '||
' TA_VERS.ORGANIZATION_ID , '||
' TA_VERS.REVISION_ID , '||
' TA_VERS.VALUE_SET_ID , '||
' HIERLEVEL.lev '||
' FROM EGO_TRANS_ATTR_VERS_B TA_VERS , '||
' ( SELECT ITEM_CATALOG_GROUP_ID , '||
' LEVEL LEV '||
' FROM MTL_ITEM_CATALOG_GROUPS_B '||
' START WITH ITEM_CATALOG_GROUP_ID =:1 '||
' CONNECT BY PRIOR PARENT_CATALOG_GROUP_ID =ITEM_CATALOG_GROUP_ID '||
' ) HIERLEVEL '||
' WHERE HIERLEVEL.ITEM_CATALOG_GROUP_ID = TA_VERS.item_catalog_group_id '||
' ) '||
' WHERE '||
' ( '||
' ( '||
' LEV = 1 '||
' AND ICC_VERSION_NUMBER = :2 '||
' ) '||
' OR '||
' ( '||
' METADATA_LEVEL = ''ITM'''||
' AND INVENTORY_ITEM_ID = :3 '||
' AND ORGANIZATION_ID = :4 '||
' AND REVISION_ID = :5 '||
' ) '||
' OR '||
' ( '||
' LEV > 1 '||
' AND metadata_level=''ICC'''||
' AND '||
' ( '||
' item_catalog_group_id, ICC_VERSION_NUMBER '||
' ) '||
' IN '||
' ( SELECT item_catalog_group_id,VERSION_SEQ_ID '||
' FROM EGO_MTL_CATALOG_GRP_VERS_B ';
' ( SELECT item_catalog_group_id, MAX(start_active_date) start_active_date '||
' FROM EGO_MTL_CATALOG_GRP_VERS_B '||
' WHERE creation_date <= :6 '||
' AND version_seq_id > 0 '||
' AND start_active_date <= :7 '||
' GROUP BY item_catalog_group_id '||
' HAVING MAX(start_active_date)<=:8 '||
' ) ';
SELECT * FROM
( SELECT versions.item_catalog_group_id,
versions.ICC_VERSION_NUMBER ,
versions.ATTR_ID ,
versions.attr_display_name ,
versions.SEQUENCE , -- Bug 8643860
versions.metadata_level ,
versions.association_id ,
VERSIONS.VALUE_SET_ID ,
VERSIONS.UOM_CLASS ,
VERSIONS.DEFAULT_VALUE ,
versions.revision_id ,
versions.organization_id ,
versions.inventory_item_id ,
VERSIONS.REJECTED_VALUE ,
VERSIONS.REQUIRED_FLAG ,
VERSIONS.READONLY_FLAG ,
VERSIONS.HIDDEN_FLAG ,
VERSIONS.SEARCHABLE_FLAG ,
VERSIONS.CHECK_ELIGIBILITY ,
Hier.lev
FROM EGO_TRANS_ATTR_VERS_B VERSIONS,
(SELECT ITEM_CATALOG_GROUP_ID , LEVEL LEV
FROM MTL_ITEM_CATALOG_GROUPS_B START WITH ITEM_CATALOG_GROUP_ID = v_ITEM_CATALOG_GROUP_ID CONNECT BY PRIOR PARENT_CATALOG_GROUP_ID =ITEM_CATALOG_GROUP_ID
) HIER
WHERE HIER.ITEM_CATALOG_GROUP_ID = versions.item_catalog_group_id AND versions.attr_id = v_attr_id )
WHERE
( (LEV =1 AND ICC_VERSION_NUMBER = l_version_seq_id AND METADATA_LEVEL = 'ICC' )
OR
(
METADATA_LEVEL = 'ITM' AND
INVENTORY_ITEM_ID = v_INVENTORY_ITEM_ID AND
ORGANIZATION_ID = v_ORGANIZATION_ID AND
REVISION_ID = v_REVISION_ID
)
OR
(
LEV > 1 AND
metadata_level='ICC'
AND
(
item_catalog_group_id, ICC_VERSION_NUMBER
)
IN
( SELECT item_catalog_group_id,VERSION_SEQ_ID
FROM EGO_MTL_CATALOG_GRP_VERS_B
WHERE
(
item_catalog_group_id,start_active_date
)
IN
(SELECT item_catalog_group_id,MAX(start_active_date) start_active_date
FROM EGO_MTL_CATALOG_GRP_VERS_B
WHERE creation_date <= v_creation_date
AND version_seq_id > 0
AND start_active_date <= v_start_active_date
GROUP BY item_catalog_group_id
HAVING MAX(start_active_date)<= v_max_start_active_date
)
AND version_seq_id > 0
)
) )
ORDER BY METADATA_LEVEL DESC ,LEV ASC ; -- Bug 14121048
SELECT * FROM
( SELECT versions.item_catalog_group_id,
versions.ICC_VERSION_NUMBER ,
versions.ATTR_ID ,
versions.attr_display_name ,
versions.SEQUENCE , -- Bug 8643860
versions.metadata_level ,
versions.association_id ,
VERSIONS.VALUE_SET_ID ,
VERSIONS.UOM_CLASS ,
VERSIONS.DEFAULT_VALUE ,
versions.revision_id ,
versions.organization_id ,
versions.inventory_item_id ,
VERSIONS.REJECTED_VALUE ,
VERSIONS.REQUIRED_FLAG ,
VERSIONS.READONLY_FLAG ,
VERSIONS.HIDDEN_FLAG ,
VERSIONS.SEARCHABLE_FLAG ,
VERSIONS.CHECK_ELIGIBILITY ,
Hier.lev
FROM EGO_TRANS_ATTR_VERS_B VERSIONS,
(SELECT ITEM_CATALOG_GROUP_ID , LEVEL LEV
FROM MTL_ITEM_CATALOG_GROUPS_B START WITH ITEM_CATALOG_GROUP_ID = v_ITEM_CATALOG_GROUP_ID CONNECT BY PRIOR PARENT_CATALOG_GROUP_ID =ITEM_CATALOG_GROUP_ID
) HIER
WHERE HIER.ITEM_CATALOG_GROUP_ID = versions.item_catalog_group_id AND versions.attr_id = v_attr_id )
WHERE
( (LEV =1 AND ICC_VERSION_NUMBER = p_icc_version AND METADATA_LEVEL = 'ICC' )
OR
(
LEV > 1
AND metadata_level='ICC'
AND
(
item_catalog_group_id, ICC_VERSION_NUMBER
)
IN
( SELECT item_catalog_group_id,
VERSION_SEQ_ID
FROM EGO_MTL_CATALOG_GRP_VERS_B
WHERE
(
item_catalog_group_id,start_active_date
)
IN
(SELECT item_catalog_group_id,
MAX(start_active_date) start_active_date
FROM EGO_MTL_CATALOG_GRP_VERS_B
WHERE creation_date <= v_creation_date
AND version_seq_id > 0
AND start_active_date <= v_start_active_date
GROUP BY item_catalog_group_id
HAVING MAX(start_active_date)<= v_max_start_active_date
)
AND version_seq_id > 0
)
)
)
ORDER BY LEV ASC ; -- Bug 14121048 , Though not require for this bug, However it may result wrong data if sql does not sort. Added to avoid any future bug.
SELECT ITEM_CATALOG_GROUP_ID INTO l_item_catalog_group_id FROM MTL_SYSTEM_ITEMS_VL
WHERE INVENTORY_ITEM_ID = p_inventory_item_id AND ORGANIZATION_ID = p_organization_id ;
SELECT EFFECTIVITY_DATE ,CREATION_DATE INTO l_efectivity_date,l_creation_date
FROM MTL_ITEM_REVISIONS_VL WHERE INVENTORY_ITEM_ID = p_inventory_item_id
AND ORGANIZATION_ID = p_organization_id AND REVISION_ID = p_revision_id;
SELECT VERSION_SEQ_ID INTO l_version_seq_id FROM EGO_MTL_CATALOG_GRP_VERS_B
WHERE (ITEM_CATALOG_GROUP_ID, start_active_date) IN
(
SELECT ITEM_CATALOG_GROUP_ID,Max(START_ACTIVE_DATe) START_ACTIVE_DATe
FROM EGO_MTL_CATALOG_GRP_VERS_B
WHERE CREATION_DATE <= l_creation_date AND
ITEM_CATALOG_GROUP_ID = l_item_catalog_group_id AND
START_ACTIVE_DATE <= l_efectivity_date AND VERSION_SEQ_ID >0
GROUP BY ITEM_CATALOG_GROUP_ID
HAVING MAX(START_ACTIVE_DATE) <= l_efectivity_date
) ;
SELECT START_ACTIVE_DATE,CREATION_DATE INTO l_icc_start_date, l_icc_create_date
FROM EGO_MTL_CATALOG_GRP_VERS_B
WHERE ITEM_CATALOG_GROUP_ID = l_item_catalog_group_id AND VERSION_SEQ_ID = l_version_seq_id;
SELECT FLEX_VALUE_SET_NAME INTO l_value_set_name
FROM FND_FLEX_VALUE_SETS
WHERE FLEX_VALUE_SET_ID = l_Value_Set_Id;
SELECT START_ACTIVE_DATE,CREATION_DATE INTO l_icc_start_date, l_icc_create_date
FROM EGO_MTL_CATALOG_GRP_VERS_B
WHERE ITEM_CATALOG_GROUP_ID = p_item_catalog_category_id AND VERSION_SEQ_ID = p_icc_version;
SELECT DATA_TYPE_CODE INTO l_data_type_code
FROM EGO_ATTRS_V
WHERE ATTR_ID = l_attrid;
SELECT FLEX_VALUE_SET_NAME INTO l_value_set_name
FROM FND_FLEX_VALUE_SETS
WHERE FLEX_VALUE_SET_ID = l_Value_Set_Id;