[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 1
INTO l_count
FROM po_uda_ag_template_usages
WHERE TEMPLATE_ID = p_template_id
AND ATTRIBUTE_GROUP_ID = p_attr_grp_or_page_id
AND ROWNUM < 2;
SELECT DISTINCT ATTRIBUTE_CATEGORY
INTO x_usage_code
FROM po_uda_ag_template_usages
WHERE TEMPLATE_ID = p_template_id
AND ATTRIBUTE_GROUP_ID = p_attr_grp_or_page_id;
SELECT 1
INTO l_count
FROM EGO_PAGE_ENTRIES_B P,
po_uda_AG_templates t
WHERE P.PAGE_ID = p_attr_grp_or_page_id
AND t.TEMPLATE_ID = p_template_id
AND p.CLASSIFICATION_CODE = p_template_id || ''
AND ROWNUM < 2;
SELECT END_USER_COLUMN_NAME
FROM FND_DESCR_FLEX_COLUMN_USAGES
WHERE DESCRIPTIVE_FLEXFIELD_NAME = p_attr_grp_type
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_grp_name;
SELECT PO_UDA_INTERFACE_ROW_ID_S.NEXTVAL
INTO l_row_identifier
FROM DUAL;
SELECT DESCRIPTIVE_FLEXFIELD_NAME, DESCRIPTIVE_FLEX_CONTEXT_CODE
INTO l_attr_group_type, l_attr_group_name
FROM EGO_FND_DSC_FLX_CTX_EXT
WHERE ATTR_GROUP_ID = p_attr_grp_id;
SELECT DATA_LEVEL_NAME
INTO l_data_level
FROM EGO_DATA_LEVEL_B
WHERE ATTR_GROUP_TYPE = l_attr_group_type
AND DATA_LEVEL_NAME NOT LIKE '%ARCHIVE';
SELECT PO_UDA_INTERFACE_USER_ROW_ID_S.NEXTVAL
INTO l_user_row_identifier
FROM DUAL;
SELECT ENTITY_CODE
INTO v_entity_type
FROM PO_UDA_AG_TEMPLATES
WHERE TEMPLATE_ID = p_template_id
AND ROWNUM = 1;
SELECT OBJECT_ID
INTO v_object_id
FROM FND_OBJECTS
WHERE OBJ_NAME = g_object_dff_tl(v_entity_type).l_object_name;
SELECT DATA_LEVEL_NAME
INTO v_data_level
FROM EGO_DATA_LEVEL_B
WHERE DATA_LEVEL_NAME NOT LIKE '%ARCHIVE'
AND ATTR_GROUP_TYPE = v_entity_type;
SELECT EGO_ASSOCS_S.NEXTVAL
INTO x_association_id
FROM sys.dual;
SELECT ENTITY_CODE
INTO v_entity_type
FROM PO_UDA_AG_TEMPLATES
WHERE TEMPLATE_ID = p_template_id
AND ROWNUM = 1;
SELECT OBJECT_ID
INTO v_object_id
FROM FND_OBJECTS
WHERE OBJ_NAME = g_object_dff_tl(v_entity_type).l_object_name;
SELECT 1
INTO l_count
FROM po_uda_AG_templates b, po_uda_ag_template_usages u
WHERE u.template_id = b.template_id
AND b.template_id = p_template_id
AND u.ATTRIBUTE_CATEGORY = p_usage_code
AND ROWNUM < 2;
v_sql_pg := 'SELECT PAGE_ID, count(*) FROM EGO_PAGE_ENTRIES_B PG, EGO_OBJ_AG_ASSOCS_B ATTR WHERE ';
v_sql_pg := v_sql_pg || ' SELECT DISTINCT PAGE_ID FROM EGO_PAGE_ENTRIES_B PG1, EGO_OBJ_AG_ASSOCS_B ATTR1';
l_insert_pk_sql VARCHAR2(500);
l_select_pk_sql VARCHAR2(2000);
SELECT ENTITY_CODE
INTO from_attr_group_Type
FROM PO_UDA_AG_TEMPLATES
WHERE TEMPLATE_ID = from_template_id;
SELECT ENTITY_CODE
INTO to_attr_group_type
FROM PO_UDA_AG_TEMPLATES
WHERE TEMPLATE_ID = to_template_id;
SELECT *
INTO l_from_object
FROM FND_OBJECTS
WHERE OBJ_NAME = l_from_object_cr.l_object_name;
SELECT *
INTO l_to_object
FROM FND_OBJECTS
WHERE OBJ_NAME = l_to_object_cr.l_object_name;
l_insert_pk_sql := l_insert_pk_sql || 'destination.'|| to_pk_col_value_pairs(l_index).NAME ||', ';
l_select_pk_sql := l_select_pk_sql ||'''' ||to_pk_col_value_pairs(l_index).VALUE ||''', ';
PO_LOG.stmt(d_module,d_progress,'l_insert_pk_sql',l_insert_pk_sql);
PO_LOG.stmt(d_module,d_progress,'l_select_pk_sql',l_select_pk_sql);
in the merge statement below updated it with value to_template_id*/
l_where_not_in_sql := l_where_not_in_sql ||
'''EXTENSION_ID'', '||
'''ATTR_GROUP_ID'', '||
'''DATA_LEVEL_ID'', '||
'''UDA_TEMPLATE_ID'', '||
'''PK1_VALUE'', '||
'''PK2_VALUE'', '||
'''PK3_VALUE'', '||
'''PK4_VALUE'', '||
'''PK5_VALUE'', '||
'''CREATED_BY'', '||
'''CREATION_DATE'', '||
'''LAST_UPDATED_BY'', '||
'''LAST_UPDATE_DATE'', '||
'''LAST_UPDATE_LOGIN''';
SELECT attr_group_id
INTO l_attr_group_id
FROM EGO_ATTR_GROUPS_V
WHERE ATTR_GROUP_TYPE = from_attr_group_Type
AND ATTR_GROUP_NAME = attribute_group_table(i);
SELECT DISTINCT(DATA_LEVEL_ID)
INTO l_to_data_level_id
FROM UDA_SETUP_METADATA_V WHERE Attr_group_type = to_attr_group_Type;
SELECT DISTINCT(DATA_LEVEL_ID)
INTO l_from_data_level_id
FROM UDA_SETUP_METADATA_V WHERE Attr_group_type = from_attr_group_Type;
/*Bug 13892657 : Added UDA_TEMPALTE_ID in the insert statement*/
l_base_table_copy_dml := 'MERGE INTO ' || l_to_object_cr.l_object_ext_b_table || ' destination' ||
' USING ' ||
'( SELECT '||
':1 TO_EXTENSION_ID, '||
'(SELECT ' || l_to_object_cr.l_ag_mapping_column || ' FROM po_uda_mappings WHERE ' || l_from_object_cr.l_ag_mapping_column || ' = ATTR_GROUP_ID) TO_ATTR_GROUP_ID, '|| -- ATTR_GROUP_ID
'ATTR_GROUP_ID, ' ||
l_b_table_col_names_list||' '||
'last_updated_by,last_update_date '||
' FROM '||l_from_object_cr.l_object_ext_b_table||
' WHERE EXTENSION_ID = :2 ' ||
l_where_ag_sql||
' AND EXISTS ( SELECT 1 FROM po_uda_mappings WHERE ' || l_from_object_cr.l_ag_mapping_column || ' = ATTR_GROUP_ID AND ' || l_to_object_cr.l_ag_mapping_column || ' IS NOT null )' ||
') source ' ||
'ON ( '||
l_to_where_pk_sql ||
' destination.attr_group_id = source.TO_ATTR_GROUP_ID ' ||
' and destination.data_level_id = ' || l_to_data_level_id || ' )' ||
' WHEN MATCHED THEN ' ||
' UPDATE ' ||
' SET ' ||
'destination.last_updated_by = source.last_updated_by ,' ||
'destination.last_update_date = source.last_update_date ,' ||
l_b_table_upt_list ||
' WHEN NOT MATCHED THEN '||
' INSERT ' ||
' (destination.EXTENSION_ID, '||
'destination.ATTR_GROUP_ID, '||
l_insert_pk_sql ||' '||
'destination.DATA_LEVEL_ID, '||
'destination.UDA_TEMPLATE_ID, '||
l_d_b_table_col_names_list||' '||
'destination.CREATED_BY, '||
'destination.CREATION_DATE, '||
'destination.LAST_UPDATED_BY, '||
'destination.LAST_UPDATE_DATE, '||
'destination.LAST_UPDATE_LOGIN)' ||
' VALUES ' ||
'(source.TO_EXTENSION_ID, '||
' source.TO_ATTR_GROUP_ID, '||
l_select_pk_sql ||' '||
l_to_data_level_id||', '||
to_template_id||', '||
l_s_b_table_col_names_list||' '||
l_current_user_id||', '||
'SYSDATE, '||
l_current_user_id||', '||
'SYSDATE, '||
l_current_login_id ||
') ';
/*Bug 13892657 : Added UDA_TEMPALTE_ID in the insert statement*/
l_tl_table_copy_dml := 'MERGE INTO ' || l_to_object_cr.l_object_ext_tl_table || ' destination' ||
' USING ' ||
'( SELECT '||
':1 TO_EXTENSION_ID, '||
'(SELECT ' || l_to_object_cr.l_ag_mapping_column || ' FROM po_uda_mappings WHERE ' || l_from_object_cr.l_ag_mapping_column || ' = ATTR_GROUP_ID) TO_ATTR_GROUP_ID, '|| -- ATTR_GROUP_ID
'ATTR_GROUP_ID, ' ||
l_tl_table_col_names_list||' '||
'last_updated_by,last_update_date '||
' FROM '||l_from_object_cr.l_object_ext_tl_table||
' WHERE EXTENSION_ID = :2 ' ||
' AND language = :3 ' ||
l_where_ag_sql||
' AND EXISTS ( SELECT 1 FROM po_uda_mappings WHERE ' || l_from_object_cr.l_ag_mapping_column || ' = ATTR_GROUP_ID AND ' || l_to_object_cr.l_ag_mapping_column || ' IS NOT null )' ||
') source ' ||
'ON ( '||
l_to_where_pk_sql ||
' destination.LANGUAGE = source.LANGUAGE '||
' AND destination.attr_group_id = source.TO_ATTR_GROUP_ID ' ||
' and destination.data_level_id = ' || l_to_data_level_id || ' )' ||
' WHEN MATCHED THEN ' ||
' UPDATE ' ||
' SET ' ||
'destination.last_updated_by = source.last_updated_by ,' ||
'destination.last_update_date = source.last_update_date ,' ||
l_tl_table_upt_list ||
' WHEN NOT MATCHED THEN '||
' INSERT ' ||
' (destination.EXTENSION_ID, '||
'destination.ATTR_GROUP_ID, '||
l_insert_pk_sql ||' '||
'destination.DATA_LEVEL_ID, '||
'destination.UDA_TEMPLATE_ID, '||
l_d_tl_table_col_names_list||' '||
'destination.CREATED_BY, '||
'destination.CREATION_DATE, '||
'destination.LAST_UPDATED_BY, '||
'destination.LAST_UPDATE_DATE, '||
'destination.LAST_UPDATE_LOGIN)' ||
' VALUES ' ||
'(source.TO_EXTENSION_ID, '||
' source.TO_ATTR_GROUP_ID, '||
l_select_pk_sql ||' '||
l_to_data_level_id||', '||
to_template_id||', '||
l_s_tl_table_col_names_list||' '||
l_current_user_id||', '||
'SYSDATE, '||
l_current_user_id||', '||
'SYSDATE, '||
l_current_login_id ||
') ';
-- Creating a Dynamic SQL to select which Extension Records need to Copied
-- for the giving Source Document.
-- Only those Extension records will be picked pertaing to Attribute Groups
-- which has a entry in mapping table.
-- Extension record pertaing to Destination Attribute groups which are not
-- there in target documents Template will be skipped.
-----------------------------------------------------------------------------
l_dynamic_sql := ' SELECT EXTENSION_ID, EGO_EXTFWK_S.NEXTVAL '||
' FROM '||l_from_object_cr.l_object_ext_b_table||
' WHERE '||l_from_where_pk_sql ||
' DATA_LEVEL_ID = ' || l_from_data_level_id ||
' AND EXISTS (SELECT 1 from PO_UDA_AG_TEMPLATE_USAGES where template_id = ' || to_template_id ||
' AND attribute_group_id = ' ||
'(SELECT ' || l_to_object_cr.l_ag_mapping_column || ' FROM po_uda_mappings WHERE ' || l_from_object_cr.l_ag_mapping_column || ' = ATTR_GROUP_ID)) ';
l_dynamic_Lang_sql := 'SELECT language from '|| l_from_object_cr.l_object_ext_tl_table || ' where extension_id = ' || l_copy_from_ext_id;
l_in_update_mode BOOLEAN;
/*l_dynamic_sql := ' SELECT tab.COLUMN_NAME COLUMN_NAME,' ||
' Decode(tab.DATA_TYPE,''NUMBER'',''N'', ''DATE'',''D'',''VARCHAR2'',''V'',NULL) COLUMN_TYPE '||
' FROM SYS.ALL_TAB_COLUMNS tab, ' ||
'ALL_SYNONYMS syn ' ||
' WHERE tab.table_name = syn.table_name '||
' AND tab.owner = syn.table_owner '||
' AND syn.synonym_name = :1 ';*/
select application_id
into l_app_id
from fnd_application
where application_short_name = 'PO' ;
l_dynamic_sql := ' SELECT DISTINCT col.column_name COLUMN_NAME, col.column_type COLUMN_TYPE' ||
' FROM fnd_tables tab, fnd_columns col ' ||
' WHERE col.table_id = tab.table_id ' ||
' AND tab.table_name = :1 ' ||
' AND tab.application_id = ' || l_app_id ||
' AND col.application_id = tab.application_id ';
l_in_update_mode := (p_to_table_name IS NOT NULL);
IF (l_in_update_mode) THEN
l_table_column_names_list := l_table_column_names_list || p_to_table_alias_prefix || '.' ||
l_column_name || '= ' ;
SELECT ENTITY_CODE
INTO l_attr_group_Type
FROM PO_UDA_AG_TEMPLATES
WHERE TEMPLATE_ID = x_template_id;
SELECT *
INTO l_object
FROM FND_OBJECTS
WHERE OBJ_NAME = l_object_dff_cr.l_object_name;
SELECT data_level_id
INTO l_main_data_level_id
FROM EGO_DATA_LEVEL_B
WHERE ATTR_GROUP_TYPE = l_attr_group_Type
AND DATA_LEVEL_NAME NOT LIKE '%ARCHIVE';
SELECT *
INTO l_archive_data_level
FROM EGO_DATA_LEVEL_B
WHERE ATTR_GROUP_TYPE = l_attr_group_Type
AND DATA_LEVEL_NAME LIKE '%ARCHIVE';
l_Rev_num_dml := ' SELECT count(1) from ( '||
' SELECT * '||
' FROM '|| l_object_dff_cr.l_object_ext_b_table||
' WHERE '|| l_where_pk_sql ||
' PK1_VALUE IS NOT NULL AND PK1_VALUE = ' || x_revision_id ||
' AND ROWNUM < 2 )';
SELECT ENTITY_CODE
INTO l_attr_group_Type
FROM PO_UDA_AG_TEMPLATES
WHERE TEMPLATE_ID = x_template_id;
SELECT *
INTO l_object
FROM FND_OBJECTS
WHERE OBJ_NAME = l_object_dff_cr.l_object_name;
SELECT *
INTO l_main_data_level
FROM EGO_DATA_LEVEL_B
WHERE ATTR_GROUP_TYPE = l_attr_group_Type
AND DATA_LEVEL_NAME NOT LIKE '%ARCHIVE';
SELECT attr_group_id
INTO l_attr_group_id
FROM EGO_ATTR_GROUPS_V
WHERE ATTR_GROUP_TYPE = l_attr_group_Type
AND ATTR_GROUP_NAME = x_attribute_group_table(i);
l_Rev_num_dml := ' SELECT count(1) from ( '||
' SELECT * '||
' FROM '|| l_object_dff_cr.l_object_ext_b_table||
' WHERE '|| l_to_where_pk_sql ||
l_where_ag_sql ||
' AND ROWNUM < 2 )';
l_get_attr_list_sql := 'SELECT UNIQUE ATTR_GROUP_ID
FROM ' || l_object_dff_cr.l_object_ext_b_table || ' SRC
WHERE '|| l_from_where_pk_sql || l_where_ag_sql ||
' AND NOT EXISTS
(SELECT 1 FROM '|| l_object_dff_cr.l_object_ext_b_table ||' DEST WHERE ' || l_to_where_pk_sql||' AND DEST.ATTR_GROUP_ID = SRC.ATTR_GROUP_ID )';
SELECT ATTR_GROUP_ID
INTO l_attr_group_id
FROM EGO_ATTR_GROUPS_V
WHERE ATTR_GROUP_TYPE = 'PO_REQ_HEADER_EXT_ATTRS'
AND ATTR_GROUP_NAME = 'addresses';
SELECT * BULK COLLECT INTO amd_address_rows FROM po_req_headers_ext_b
WHERE requisition_header_id = x_amd_requisition_header_id
AND ATTR_GROUP_ID = l_attr_group_id;
SELECT * BULK COLLECT INTO amd_tl_address_rows FROM po_req_headers_ext_tl
WHERE requisition_header_id = x_amd_requisition_header_id
AND ATTR_GROUP_ID = l_attr_group_id
AND extension_id = amd_address_rows(row_b).extension_id;
SELECT EGO_EXTFWK_S.NEXTVAL
INTO amd_address_rows(row_b).extension_id
FROM sys.dual;
UPDATE po_req_headers_ext_tl SET ROW = amd_tl_address_rows(row_tl)
WHERE extension_id IN ( SELECT extension_id FROM po_req_headers_ext_b
WHERE C_EXT_ATTR39 = amd_address_rows(row_b).C_EXT_ATTR39
AND attr_group_id = l_attr_group_id
AND requisition_header_id = x_requisition_header_id)
AND po_req_headers_ext_tl.LANGUAGE = amd_tl_address_rows(row_tl).LANGUAGE;
UPDATE po_req_headers_ext_b SET ROW = amd_address_rows(row_b)
WHERE C_EXT_ATTR39 = amd_address_rows(row_b).C_EXT_ATTR39
AND attr_group_id = l_attr_group_id
AND requisition_header_id = x_requisition_header_id;
SELECT ATTR_GROUP_ID
INTO l_attr_group_id
FROM EGO_ATTR_GROUPS_V
WHERE ATTR_GROUP_TYPE = 'PO_HEADER_EXT_ATTRS'
AND ATTR_GROUP_NAME = 'addresses';
SELECT * BULK COLLECT INTO po_address_rows FROM po_headers_all_ext_b
WHERE po_header_id = p_po_header_id
AND ATTR_GROUP_ID = l_attr_group_id
AND draft_id = p_draft_id;
SELECT * BULK COLLECT INTO po_tl_address_rows FROM po_headers_all_ext_tl
WHERE po_header_id = p_po_header_id
AND draft_id = p_draft_id
AND ATTR_GROUP_ID = l_attr_group_id
AND extension_id = po_address_rows(row_b).extension_id;
SELECT EGO_EXTFWK_S.NEXTVAL
INTO po_address_rows(row_b).extension_id
FROM sys.dual;
UPDATE po_headers_all_ext_tl SET ROW = po_tl_address_rows(row_tl)
WHERE extension_id IN ( SELECT extension_id FROM po_headers_all_ext_b
WHERE C_EXT_ATTR39 = po_address_rows(row_b).C_EXT_ATTR39
AND attr_group_id = l_attr_group_id
AND po_header_id = p_po_header_id
AND draft_id = -1)
AND po_headers_all_ext_tl.LANGUAGE = po_tl_address_rows(row_tl).LANGUAGE;
UPDATE po_headers_all_ext_b SET ROW = po_address_rows(row_b)
WHERE C_EXT_ATTR39 = po_address_rows(row_b).C_EXT_ATTR39
AND attr_group_id = l_attr_group_id
AND po_header_id = p_po_header_id
AND draft_id = -1 ;
query := 'SELECT attr_group_id FROM '|| g_object_dff_tl(p_attr_grp_type).l_object_ext_b_table || ' WHERE ' || p_where_clause || ' AND pk1_value IS NULL';
count_query := 'SELECT count(*) FROM '|| g_object_dff_tl(p_attr_grp_type).l_object_ext_b_table || ' WHERE ' || p_where_clause || ' AND pk1_value IS NULL';
SELECT template_id
INTO l_template_id
FROM po_uda_ag_templates
WHERE functional_area = p_functional_area
AND document_level = p_document_level
AND Nvl(document_type, '@@@') = Nvl(p_document_type, '@@@')
AND Nvl(document_style_id , -1) = Nvl(p_document_style_id, -1)
AND p_input_date >= effective_from
AND ( effective_to IS NULL
OR
p_input_date < effective_to
)
AND compiled_flag = 'Y';
SELECT ATTRACT.ACTION_ID
FROM EGO_ACTION_DISPLAYS_B ATTRACTDISP,
EGO_ACTIONS_B ATTRACT
WHERE ATTRACT.ACTION_ID = ATTRACTDISP.ACTION_ID
AND ATTRACT.ATTR_GROUP_ID = p_attribute_group_id
AND ATTRACTDISP.EXECUTION_METHOD = p_event
AND ATTRACT.CLASSIFICATION_CODE = p_template_id ;
SELECT ENTITY_CODE
INTO l_attr_group_Type
FROM PO_UDA_AG_TEMPLATES
WHERE TEMPLATE_ID = p_template_id;
select attr_group_name
into l_attr_grp_name
from ego_attr_groups_v
where ATTR_GROUP_TYPE = l_attr_group_Type
and attr_group_id = l_attr_grp_id
AND MULTI_ROW_CODE = 'N';
select attr_group_id
into l_attr_grp_id
from ego_attr_groups_v
where ATTR_GROUP_TYPE = l_attr_group_Type
and attr_group_name = l_attr_grp_name
AND MULTI_ROW_CODE = 'N';
select database_column, application_id, data_type_code
into l_db_col_name, l_application_id, l_data_type
from ego_attrs_v
where attr_id = p_attr_id
and ATTR_GROUP_NAME = l_attr_grp_name
and ATTR_GROUP_TYPE = l_attr_group_Type;
select database_column
into l_db_col_name
from ego_attrs_v
where ATTR_NAME = p_attr_int_name
and ATTR_GROUP_NAME = l_attr_grp_name
and ATTR_GROUP_TYPE = l_attr_group_Type;
l_sql_stmt := 'select '|| l_db_col_name ||' from '||l_ext_table_name
||' where attribute_group_id = '|| l_attr_grp_id ;
select pk1_column_name, pk2_column_name, pk3_column_name, pk4_column_name, pk5_column_name
into l_pk1_col_name, l_pk2_col_name, l_pk3_col_name, l_pk4_col_name, l_pk5_col_name
from fnd_objects
where obj_name = l_db_object_name
and rownum < 2;
select distinct(attribute_category) from po_uda_ag_template_usages
where template_id = p_template_id;
SELECT clm_standard_form into l_clm_standard_form FROM po_headers_all
WHERE po_header_id = p_po_header_id;
SELECT DISTINCT(ATTRIBUTE_CATEGORY) FROM po_uda_ag_template_usages
WHERE template_id = p_template_id;
SELECT CONTRACT_TYPE, CLM_IDC_TYPE
INTO l_contract_type ,l_clm_idc_type
FROM po_lines_all
WHERE po_line_id = p_po_line_id;
SELECT CONTRACT_TYPE, CLM_IDC_TYPE
INTO l_contract_type ,l_clm_idc_type
FROM po_lines_draft_all
WHERE po_line_id = p_po_line_id
AND draft_id = p_draft_id;
SELECT DISTINCT(ATTRIBUTE_CATEGORY) FROM po_uda_ag_template_usages
WHERE template_id = p_template_id;
PROCEDURE global_update_line_uda
(
po_src_line_id IN NUMBER ,
po_dest_line_ids_tbl IN PO_TBL_NUMBER,
p_draft_id IN NUMBER
)
IS
d_api_name CONSTANT VARCHAR2(30) := 'global_update_line_uda';
selectStmt VARCHAR(2000) := '';
INSERT INTO po_lines_all_ext_b DESTlINE
(DESTLINE.extension_id,
DESTLINE.attr_group_id,
DESTLINE.po_line_id,
DESTLINE.data_level_id,
DESTLINE.pk1_value,
DESTLINE.pk2_value,
DESTLINE.pk3_value,
DESTLINE.pk4_value,
DESTLINE.pk5_value,
DESTLINE.last_update_date,
DESTLINE.last_updated_by,
DESTLINE.last_update_login,
DESTLINE.created_by,
DESTLINE.creation_date,
DESTLINE.c_ext_attr1,
DESTLINE.c_ext_attr2,
DESTLINE.c_ext_attr3,
DESTLINE.c_ext_attr4,
DESTLINE.c_ext_attr5,
DESTLINE.c_ext_attr6,
DESTLINE.c_ext_attr7,
DESTLINE.c_ext_attr8,
DESTLINE.c_ext_attr9,
DESTLINE.c_ext_attr10,
DESTLINE.c_ext_attr11,
DESTLINE.c_ext_attr12,
DESTLINE.c_ext_attr13,
DESTLINE.c_ext_attr14,
DESTLINE.c_ext_attr15,
DESTLINE.c_ext_attr16,
DESTLINE.c_ext_attr17,
DESTLINE.c_ext_attr18,
DESTLINE.c_ext_attr19,
DESTLINE.c_ext_attr20,
DESTLINE.c_ext_attr21,
DESTLINE.c_ext_attr22,
DESTLINE.c_ext_attr23,
DESTLINE.c_ext_attr24,
DESTLINE.c_ext_attr25,
DESTLINE.c_ext_attr26,
DESTLINE.c_ext_attr27,
DESTLINE.c_ext_attr28,
DESTLINE.c_ext_attr29,
DESTLINE.c_ext_attr30,
DESTLINE.c_ext_attr31,
DESTLINE.c_ext_attr32,
DESTLINE.c_ext_attr33,
DESTLINE.c_ext_attr34,
DESTLINE.c_ext_attr35,
DESTLINE.c_ext_attr36,
DESTLINE.c_ext_attr37,
DESTLINE.c_ext_attr38,
DESTLINE.c_ext_attr39,
DESTLINE.c_ext_attr40,
DESTLINE.n_ext_attr1,
DESTLINE.n_ext_attr2,
DESTLINE.n_ext_attr3,
DESTLINE.n_ext_attr4,
DESTLINE.n_ext_attr5,
DESTLINE.n_ext_attr6,
DESTLINE.n_ext_attr7,
DESTLINE.n_ext_attr8,
DESTLINE.n_ext_attr9,
DESTLINE.n_ext_attr10,
DESTLINE.n_ext_attr11,
DESTLINE.n_ext_attr12,
DESTLINE.n_ext_attr13,
DESTLINE.n_ext_attr14,
DESTLINE.n_ext_attr15,
DESTLINE.n_ext_attr16,
DESTLINE.n_ext_attr17,
DESTLINE.n_ext_attr18,
DESTLINE.n_ext_attr19,
DESTLINE.n_ext_attr20,
DESTLINE.uom_ext_attr1,
DESTLINE.uom_ext_attr2,
DESTLINE.uom_ext_attr3,
DESTLINE.uom_ext_attr4,
DESTLINE.uom_ext_attr5,
DESTLINE.uom_ext_attr6,
DESTLINE.uom_ext_attr7,
DESTLINE.uom_ext_attr8,
DESTLINE.uom_ext_attr9,
DESTLINE.uom_ext_attr10,
DESTLINE.uom_ext_attr11,
DESTLINE.uom_ext_attr12,
DESTLINE.uom_ext_attr13,
DESTLINE.uom_ext_attr14,
DESTLINE.uom_ext_attr15,
DESTLINE.uom_ext_attr16,
DESTLINE.uom_ext_attr17,
DESTLINE.uom_ext_attr18,
DESTLINE.uom_ext_attr19,
DESTLINE.uom_ext_attr20,
DESTLINE.d_ext_attr1,
DESTLINE.d_ext_attr2,
DESTLINE.d_ext_attr3,
DESTLINE.d_ext_attr4,
DESTLINE.d_ext_attr5,
DESTLINE.d_ext_attr6,
DESTLINE.d_ext_attr7,
DESTLINE.d_ext_attr8,
DESTLINE.d_ext_attr9,
DESTLINE.d_ext_attr10,
DESTLINE.uda_template_id,
DESTLINE.draft_id)
SELECT
EGO_EXTFWK_S.NEXTVAL,
SRCLINE.attr_group_id,
po_dest_line_ids_tbl(i),
SRCLINE.data_level_id,
SRCLINE.pk1_value,
SRCLINE.pk2_value,
SRCLINE.pk3_value,
SRCLINE.pk4_value,
SRCLINE.pk5_value,
SRCLINE.last_update_date,
SRCLINE.last_updated_by,
SRCLINE.last_update_login,
SRCLINE.created_by,
SRCLINE.creation_date,
SRCLINE.c_ext_attr1,
SRCLINE.c_ext_attr2,
SRCLINE.c_ext_attr3,
SRCLINE.c_ext_attr4,
SRCLINE.c_ext_attr5,
SRCLINE.c_ext_attr6,
SRCLINE.c_ext_attr7,
SRCLINE.c_ext_attr8,
SRCLINE.c_ext_attr9,
SRCLINE.c_ext_attr10,
SRCLINE.c_ext_attr11,
SRCLINE.c_ext_attr12,
SRCLINE.c_ext_attr13,
SRCLINE.c_ext_attr14,
SRCLINE.c_ext_attr15,
SRCLINE.c_ext_attr16,
SRCLINE.c_ext_attr17,
SRCLINE.c_ext_attr18,
SRCLINE.c_ext_attr19,
SRCLINE.c_ext_attr20,
SRCLINE.c_ext_attr21,
SRCLINE.c_ext_attr22,
SRCLINE.c_ext_attr23,
SRCLINE.c_ext_attr24,
SRCLINE.c_ext_attr25,
SRCLINE.c_ext_attr26,
SRCLINE.c_ext_attr27,
SRCLINE.c_ext_attr28,
SRCLINE.c_ext_attr29,
SRCLINE.c_ext_attr30,
SRCLINE.c_ext_attr31,
SRCLINE.c_ext_attr32,
SRCLINE.c_ext_attr33,
SRCLINE.c_ext_attr34,
SRCLINE.c_ext_attr35,
SRCLINE.c_ext_attr36,
SRCLINE.c_ext_attr37,
SRCLINE.c_ext_attr38,
SRCLINE.c_ext_attr39,
SRCLINE.c_ext_attr40,
SRCLINE.n_ext_attr1,
SRCLINE.n_ext_attr2,
SRCLINE.n_ext_attr3,
SRCLINE.n_ext_attr4,
SRCLINE.n_ext_attr5,
SRCLINE.n_ext_attr6,
SRCLINE.n_ext_attr7,
SRCLINE.n_ext_attr8,
SRCLINE.n_ext_attr9,
SRCLINE.n_ext_attr10,
SRCLINE.n_ext_attr11,
SRCLINE.n_ext_attr12,
SRCLINE.n_ext_attr13,
SRCLINE.n_ext_attr14,
SRCLINE.n_ext_attr15,
SRCLINE.n_ext_attr16,
SRCLINE.n_ext_attr17,
SRCLINE.n_ext_attr18,
SRCLINE.n_ext_attr19,
SRCLINE.n_ext_attr20,
SRCLINE.uom_ext_attr1,
SRCLINE.uom_ext_attr2,
SRCLINE.uom_ext_attr3,
SRCLINE.uom_ext_attr4,
SRCLINE.uom_ext_attr5,
SRCLINE.uom_ext_attr6,
SRCLINE.uom_ext_attr7,
SRCLINE.uom_ext_attr8,
SRCLINE.uom_ext_attr9,
SRCLINE.uom_ext_attr10,
SRCLINE.uom_ext_attr11,
SRCLINE.uom_ext_attr12,
SRCLINE.uom_ext_attr13,
SRCLINE.uom_ext_attr14,
SRCLINE.uom_ext_attr15,
SRCLINE.uom_ext_attr16,
SRCLINE.uom_ext_attr17,
SRCLINE.uom_ext_attr18,
SRCLINE.uom_ext_attr19,
SRCLINE.uom_ext_attr20,
SRCLINE.d_ext_attr1,
SRCLINE.d_ext_attr2,
SRCLINE.d_ext_attr3,
SRCLINE.d_ext_attr4,
SRCLINE.d_ext_attr5,
SRCLINE.d_ext_attr6,
SRCLINE.d_ext_attr7,
SRCLINE.d_ext_attr8,
SRCLINE.d_ext_attr9,
SRCLINE.d_ext_attr10,
SRCLINE.uda_template_id,
SRCLINE.draft_id
FROM po_lines_all_ext_b SRCLINE WHERE SRCLINE.po_line_id = po_src_line_id AND draft_id = -1
AND NOT EXISTS (SELECT 1 FROM po_lines_all_ext_b pleb WHERE
pleb.po_line_id =po_dest_line_ids_tbl(i) AND
pleb.attr_group_id = SRCLINE.attr_group_id AND
pleb.draft_id = p_draft_id);
SELECT
attr_group_id,
po_line_id,
data_level_id,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
pk1_value,
pk2_value,
pk3_value,
pk4_value,
pk5_value,
c_ext_attr1,
c_ext_attr2,
c_ext_attr3,
c_ext_attr4,
c_ext_attr5,
c_ext_attr6,
c_ext_attr7,
c_ext_attr8,
c_ext_attr9,
c_ext_attr10,
c_ext_attr11,
c_ext_attr12,
c_ext_attr13,
c_ext_attr14,
c_ext_attr15,
c_ext_attr16,
c_ext_attr17,
c_ext_attr18,
c_ext_attr19,
c_ext_attr20,
c_ext_attr21,
c_ext_attr22,
c_ext_attr23,
c_ext_attr24,
c_ext_attr25,
c_ext_attr26,
c_ext_attr27,
c_ext_attr28,
c_ext_attr29,
c_ext_attr30,
c_ext_attr31,
c_ext_attr32,
c_ext_attr33,
c_ext_attr34,
c_ext_attr35,
c_ext_attr36,
c_ext_attr37,
c_ext_attr38,
c_ext_attr39,
c_ext_attr40,
n_ext_attr1,
n_ext_attr2,
n_ext_attr3,
n_ext_attr4,
n_ext_attr5,
n_ext_attr6,
n_ext_attr7,
n_ext_attr8,
n_ext_attr9,
n_ext_attr10,
n_ext_attr11,
n_ext_attr12,
n_ext_attr13,
n_ext_attr14,
n_ext_attr15,
n_ext_attr16,
n_ext_attr17,
n_ext_attr18,
n_ext_attr19,
n_ext_attr20,
uom_ext_attr1,
uom_ext_attr2,
uom_ext_attr3,
uom_ext_attr4,
uom_ext_attr5,
uom_ext_attr6,
uom_ext_attr7,
uom_ext_attr8,
uom_ext_attr9,
uom_ext_attr10,
uom_ext_attr11,
uom_ext_attr12,
uom_ext_attr13,
uom_ext_attr14,
uom_ext_attr15,
uom_ext_attr16,
uom_ext_attr17,
uom_ext_attr18,
uom_ext_attr19,
uom_ext_attr20,
d_ext_attr1,
d_ext_attr2,
d_ext_attr3,
d_ext_attr4,
d_ext_attr5,
d_ext_attr6,
d_ext_attr7,
d_ext_attr8,
d_ext_attr9,
d_ext_attr10,
uda_template_id,
draft_id
FROM po_lines_all_ext_b WHERE po_line_id = ' || po_src_line_id || ' and draft_id = ' || p_draft_id || ' ) SRCLINE
ON
(DESTLINE.po_line_id IN (' || po_dest_line_ids || ') AND DESTLINE.attr_group_id = SRCLINE.attr_group_id AND DESTLINE.draft_id = '|| p_draft_id ||')
WHEN MATCHED THEN
UPDATE
SET
DESTLINE.last_update_date = SYSDATE ,
DESTLINE.last_updated_by = SRCLINE.last_updated_by,
DESTLINE.last_update_login = SRCLINE.last_update_login,
DESTLINE.created_by = SRCLINE.created_by,
DESTLINE.creation_date = SYSDATE,
DESTLINE.c_ext_attr1 = nvl(SRCLINE.c_ext_attr1 , DESTLINE.c_ext_attr1),
DESTLINE.c_ext_attr2 = nvl(SRCLINE.c_ext_attr2 , DESTLINE.c_ext_attr2),
DESTLINE.c_ext_attr3 = nvl(SRCLINE.c_ext_attr3 , DESTLINE.c_ext_attr3),
DESTLINE.c_ext_attr4 = nvl(SRCLINE.c_ext_attr4 , DESTLINE.c_ext_attr4),
DESTLINE.c_ext_attr5 = nvl(SRCLINE.c_ext_attr5 , DESTLINE.c_ext_attr5),
DESTLINE.c_ext_attr6 = nvl(SRCLINE.c_ext_attr6 , DESTLINE.c_ext_attr6),
DESTLINE.c_ext_attr7 = nvl(SRCLINE.c_ext_attr7 , DESTLINE.c_ext_attr7),
DESTLINE.c_ext_attr8 = nvl(SRCLINE.c_ext_attr8 , DESTLINE.c_ext_attr8),
DESTLINE.c_ext_attr9 = nvl(SRCLINE.c_ext_attr9 , DESTLINE.c_ext_attr9),
DESTLINE.c_ext_attr10 = nvl(SRCLINE.c_ext_attr10 , DESTLINE.c_ext_attr10),
DESTLINE.c_ext_attr11 = nvl(SRCLINE.c_ext_attr11 , DESTLINE.c_ext_attr11),
DESTLINE.c_ext_attr12 = nvl(SRCLINE.c_ext_attr12 , DESTLINE.c_ext_attr12),
DESTLINE.c_ext_attr13 = nvl(SRCLINE.c_ext_attr13 , DESTLINE.c_ext_attr13),
DESTLINE.c_ext_attr14 = nvl(SRCLINE.c_ext_attr14 , DESTLINE.c_ext_attr14),
DESTLINE.c_ext_attr15 = nvl(SRCLINE.c_ext_attr15 , DESTLINE.c_ext_attr15),
DESTLINE.c_ext_attr16 = nvl(SRCLINE.c_ext_attr16 , DESTLINE.c_ext_attr16),
DESTLINE.c_ext_attr17 = nvl(SRCLINE.c_ext_attr17 , DESTLINE.c_ext_attr17),
DESTLINE.c_ext_attr18 = nvl(SRCLINE.c_ext_attr18 , DESTLINE.c_ext_attr18),
DESTLINE.c_ext_attr19 = nvl(SRCLINE.c_ext_attr19 , DESTLINE.c_ext_attr19),
DESTLINE.c_ext_attr20 = nvl(SRCLINE.c_ext_attr20 , DESTLINE.c_ext_attr20),
DESTLINE.c_ext_attr21 = nvl(SRCLINE.c_ext_attr21 , DESTLINE.c_ext_attr21),
DESTLINE.c_ext_attr22 = nvl(SRCLINE.c_ext_attr22 , DESTLINE.c_ext_attr22),
DESTLINE.c_ext_attr23 = nvl(SRCLINE.c_ext_attr23 , DESTLINE.c_ext_attr23),
DESTLINE.c_ext_attr24 = nvl(SRCLINE.c_ext_attr24 , DESTLINE.c_ext_attr24),
DESTLINE.c_ext_attr25 = nvl(SRCLINE.c_ext_attr25 , DESTLINE.c_ext_attr25),
DESTLINE.c_ext_attr26 = nvl(SRCLINE.c_ext_attr26 , DESTLINE.c_ext_attr26),
DESTLINE.c_ext_attr27 = nvl(SRCLINE.c_ext_attr27 , DESTLINE.c_ext_attr27),
DESTLINE.c_ext_attr28 = nvl(SRCLINE.c_ext_attr28 , DESTLINE.c_ext_attr28),
DESTLINE.c_ext_attr29 = nvl(SRCLINE.c_ext_attr29 , DESTLINE.c_ext_attr29),
DESTLINE.c_ext_attr30 = nvl(SRCLINE.c_ext_attr30 , DESTLINE.c_ext_attr30),
DESTLINE.c_ext_attr31 = nvl(SRCLINE.c_ext_attr31 , DESTLINE.c_ext_attr31),
DESTLINE.c_ext_attr32 = nvl(SRCLINE.c_ext_attr32 , DESTLINE.c_ext_attr32),
DESTLINE.c_ext_attr33 = nvl(SRCLINE.c_ext_attr33 , DESTLINE.c_ext_attr33),
DESTLINE.c_ext_attr34 = nvl(SRCLINE.c_ext_attr34 , DESTLINE.c_ext_attr34),
DESTLINE.c_ext_attr35 = nvl(SRCLINE.c_ext_attr35 , DESTLINE.c_ext_attr35),
DESTLINE.c_ext_attr36 = nvl(SRCLINE.c_ext_attr36 , DESTLINE.c_ext_attr36),
DESTLINE.c_ext_attr37 = nvl(SRCLINE.c_ext_attr37 , DESTLINE.c_ext_attr37),
DESTLINE.c_ext_attr38 = nvl(SRCLINE.c_ext_attr38 , DESTLINE.c_ext_attr38),
DESTLINE.c_ext_attr39 = nvl(SRCLINE.c_ext_attr39 , DESTLINE.c_ext_attr39),
DESTLINE.c_ext_attr40 = nvl(SRCLINE.c_ext_attr40 , DESTLINE.c_ext_attr40),
DESTLINE.n_ext_attr1 = nvl(SRCLINE.n_ext_attr1 , DESTLINE.n_ext_attr1),
DESTLINE.n_ext_attr2 = nvl(SRCLINE.n_ext_attr2 , DESTLINE.n_ext_attr2),
DESTLINE.n_ext_attr3 = nvl(SRCLINE.n_ext_attr3 , DESTLINE.n_ext_attr3),
DESTLINE.n_ext_attr4 = nvl(SRCLINE.n_ext_attr4 , DESTLINE.n_ext_attr4),
DESTLINE.n_ext_attr5 = nvl(SRCLINE.n_ext_attr5 , DESTLINE.n_ext_attr5),
DESTLINE.n_ext_attr6 = nvl(SRCLINE.n_ext_attr6 , DESTLINE.n_ext_attr6),
DESTLINE.n_ext_attr7 = nvl(SRCLINE.n_ext_attr7 , DESTLINE.n_ext_attr7),
DESTLINE.n_ext_attr8 = nvl(SRCLINE.n_ext_attr8 , DESTLINE.n_ext_attr8),
DESTLINE.n_ext_attr9 = nvl(SRCLINE.n_ext_attr9 , DESTLINE.n_ext_attr9),
DESTLINE.n_ext_attr10 = nvl(SRCLINE.n_ext_attr10 , DESTLINE.n_ext_attr10),
DESTLINE.n_ext_attr11 = nvl(SRCLINE.n_ext_attr11 , DESTLINE.n_ext_attr11),
DESTLINE.n_ext_attr12 = nvl(SRCLINE.n_ext_attr12 , DESTLINE.n_ext_attr12),
DESTLINE.n_ext_attr13 = nvl(SRCLINE.n_ext_attr13 , DESTLINE.n_ext_attr13),
DESTLINE.n_ext_attr14 = nvl(SRCLINE.n_ext_attr14 , DESTLINE.n_ext_attr14),
DESTLINE.n_ext_attr15 = nvl(SRCLINE.n_ext_attr15 , DESTLINE.n_ext_attr15),
DESTLINE.n_ext_attr16 = nvl(SRCLINE.n_ext_attr16 , DESTLINE.n_ext_attr16),
DESTLINE.n_ext_attr17 = nvl(SRCLINE.n_ext_attr17 , DESTLINE.n_ext_attr17),
DESTLINE.n_ext_attr18 = nvl(SRCLINE.n_ext_attr18 , DESTLINE.n_ext_attr18),
DESTLINE.n_ext_attr19 = nvl(SRCLINE.n_ext_attr19 , DESTLINE.n_ext_attr19),
DESTLINE.n_ext_attr20 = nvl(SRCLINE.n_ext_attr20 , DESTLINE.n_ext_attr20),
DESTLINE.uom_ext_attr1 = nvl(SRCLINE.uom_ext_attr1 , DESTLINE.uom_ext_attr1),
DESTLINE.uom_ext_attr2 = nvl(SRCLINE.uom_ext_attr2 , DESTLINE.uom_ext_attr2),
DESTLINE.uom_ext_attr3 = nvl(SRCLINE.uom_ext_attr3 , DESTLINE.uom_ext_attr3),
DESTLINE.uom_ext_attr4 = nvl(SRCLINE.uom_ext_attr4 , DESTLINE.uom_ext_attr4),
DESTLINE.uom_ext_attr5 = nvl(SRCLINE.uom_ext_attr5 , DESTLINE.uom_ext_attr5),
DESTLINE.uom_ext_attr6 = nvl(SRCLINE.uom_ext_attr6 , DESTLINE.uom_ext_attr6),
DESTLINE.uom_ext_attr7 = nvl(SRCLINE.uom_ext_attr7 , DESTLINE.uom_ext_attr7),
DESTLINE.uom_ext_attr8 = nvl(SRCLINE.uom_ext_attr8 , DESTLINE.uom_ext_attr8),
DESTLINE.uom_ext_attr9 = nvl(SRCLINE.uom_ext_attr9 , DESTLINE.uom_ext_attr9),
DESTLINE.uom_ext_attr10 = nvl(SRCLINE.uom_ext_attr10 , DESTLINE.uom_ext_attr10),
DESTLINE.uom_ext_attr11 = nvl(SRCLINE.uom_ext_attr11 , DESTLINE.uom_ext_attr11),
DESTLINE.uom_ext_attr12 = nvl(SRCLINE.uom_ext_attr12 , DESTLINE.uom_ext_attr12),
DESTLINE.uom_ext_attr13 = nvl(SRCLINE.uom_ext_attr13 , DESTLINE.uom_ext_attr13),
DESTLINE.uom_ext_attr14 = nvl(SRCLINE.uom_ext_attr14 , DESTLINE.uom_ext_attr14),
DESTLINE.uom_ext_attr15 = nvl(SRCLINE.uom_ext_attr15 , DESTLINE.uom_ext_attr15),
DESTLINE.uom_ext_attr16 = nvl(SRCLINE.uom_ext_attr16 , DESTLINE.uom_ext_attr16),
DESTLINE.uom_ext_attr17 = nvl(SRCLINE.uom_ext_attr17 , DESTLINE.uom_ext_attr17),
DESTLINE.uom_ext_attr18 = nvl(SRCLINE.uom_ext_attr18 , DESTLINE.uom_ext_attr18),
DESTLINE.uom_ext_attr19 = nvl(SRCLINE.uom_ext_attr19 , DESTLINE.uom_ext_attr19),
DESTLINE.uom_ext_attr20 = nvl(SRCLINE.uom_ext_attr20 , DESTLINE.uom_ext_attr20),
DESTLINE.d_ext_attr1 = nvl(SRCLINE.d_ext_attr1 , DESTLINE.d_ext_attr1),
DESTLINE.d_ext_attr2 = nvl(SRCLINE.d_ext_attr2 , DESTLINE.d_ext_attr2),
DESTLINE.d_ext_attr3 = nvl(SRCLINE.d_ext_attr3 , DESTLINE.d_ext_attr3),
DESTLINE.d_ext_attr4 = nvl(SRCLINE.d_ext_attr4 , DESTLINE.d_ext_attr4),
DESTLINE.d_ext_attr5 = nvl(SRCLINE.d_ext_attr5 , DESTLINE.d_ext_attr5),
DESTLINE.d_ext_attr6 = nvl(SRCLINE.d_ext_attr6 , DESTLINE.d_ext_attr6),
DESTLINE.d_ext_attr7 = nvl(SRCLINE.d_ext_attr7 , DESTLINE.d_ext_attr7),
DESTLINE.d_ext_attr8 = nvl(SRCLINE.d_ext_attr8 , DESTLINE.d_ext_attr8),
DESTLINE.d_ext_attr9 = nvl(SRCLINE.d_ext_attr9 , DESTLINE.d_ext_attr9),
DESTLINE.d_ext_attr10 = nvl(SRCLINE.d_ext_attr10 , DESTLINE.d_ext_attr10)' ;
INSERT INTO po_lines_all_ext_b DESTlINE
(DESTLINE.extension_id,
DESTLINE.attr_group_id,
DESTLINE.po_line_id,
DESTLINE.data_level_id,
DESTLINE.pk1_value,
DESTLINE.pk2_value,
DESTLINE.pk3_value,
DESTLINE.pk4_value,
DESTLINE.pk5_value,
DESTLINE.last_update_date,
DESTLINE.last_updated_by,
DESTLINE.last_update_login,
DESTLINE.created_by,
DESTLINE.creation_date,
DESTLINE.c_ext_attr1,
DESTLINE.c_ext_attr2,
DESTLINE.c_ext_attr3,
DESTLINE.c_ext_attr4,
DESTLINE.c_ext_attr5,
DESTLINE.c_ext_attr6,
DESTLINE.c_ext_attr7,
DESTLINE.c_ext_attr8,
DESTLINE.c_ext_attr9,
DESTLINE.c_ext_attr10,
DESTLINE.c_ext_attr11,
DESTLINE.c_ext_attr12,
DESTLINE.c_ext_attr13,
DESTLINE.c_ext_attr14,
DESTLINE.c_ext_attr15,
DESTLINE.c_ext_attr16,
DESTLINE.c_ext_attr17,
DESTLINE.c_ext_attr18,
DESTLINE.c_ext_attr19,
DESTLINE.c_ext_attr20,
DESTLINE.c_ext_attr21,
DESTLINE.c_ext_attr22,
DESTLINE.c_ext_attr23,
DESTLINE.c_ext_attr24,
DESTLINE.c_ext_attr25,
DESTLINE.c_ext_attr26,
DESTLINE.c_ext_attr27,
DESTLINE.c_ext_attr28,
DESTLINE.c_ext_attr29,
DESTLINE.c_ext_attr30,
DESTLINE.c_ext_attr31,
DESTLINE.c_ext_attr32,
DESTLINE.c_ext_attr33,
DESTLINE.c_ext_attr34,
DESTLINE.c_ext_attr35,
DESTLINE.c_ext_attr36,
DESTLINE.c_ext_attr37,
DESTLINE.c_ext_attr38,
DESTLINE.c_ext_attr39,
DESTLINE.c_ext_attr40,
DESTLINE.n_ext_attr1,
DESTLINE.n_ext_attr2,
DESTLINE.n_ext_attr3,
DESTLINE.n_ext_attr4,
DESTLINE.n_ext_attr5,
DESTLINE.n_ext_attr6,
DESTLINE.n_ext_attr7,
DESTLINE.n_ext_attr8,
DESTLINE.n_ext_attr9,
DESTLINE.n_ext_attr10,
DESTLINE.n_ext_attr11,
DESTLINE.n_ext_attr12,
DESTLINE.n_ext_attr13,
DESTLINE.n_ext_attr14,
DESTLINE.n_ext_attr15,
DESTLINE.n_ext_attr16,
DESTLINE.n_ext_attr17,
DESTLINE.n_ext_attr18,
DESTLINE.n_ext_attr19,
DESTLINE.n_ext_attr20,
DESTLINE.uom_ext_attr1,
DESTLINE.uom_ext_attr2,
DESTLINE.uom_ext_attr3,
DESTLINE.uom_ext_attr4,
DESTLINE.uom_ext_attr5,
DESTLINE.uom_ext_attr6,
DESTLINE.uom_ext_attr7,
DESTLINE.uom_ext_attr8,
DESTLINE.uom_ext_attr9,
DESTLINE.uom_ext_attr10,
DESTLINE.uom_ext_attr11,
DESTLINE.uom_ext_attr12,
DESTLINE.uom_ext_attr13,
DESTLINE.uom_ext_attr14,
DESTLINE.uom_ext_attr15,
DESTLINE.uom_ext_attr16,
DESTLINE.uom_ext_attr17,
DESTLINE.uom_ext_attr18,
DESTLINE.uom_ext_attr19,
DESTLINE.uom_ext_attr20,
DESTLINE.d_ext_attr1,
DESTLINE.d_ext_attr2,
DESTLINE.d_ext_attr3,
DESTLINE.d_ext_attr4,
DESTLINE.d_ext_attr5,
DESTLINE.d_ext_attr6,
DESTLINE.d_ext_attr7,
DESTLINE.d_ext_attr8,
DESTLINE.d_ext_attr9,
DESTLINE.d_ext_attr10,
DESTLINE.uda_template_id,
DESTLINE.draft_id)
SELECT
EGO_EXTFWK_S.NEXTVAL,
SRCLINE.attr_group_id,
po_dest_line_ids_tbl(i),
SRCLINE.data_level_id,
SRCLINE.pk1_value,
SRCLINE.pk2_value,
SRCLINE.pk3_value,
SRCLINE.pk4_value,
SRCLINE.pk5_value,
SRCLINE.last_update_date,
SRCLINE.last_updated_by,
SRCLINE.last_update_login,
SRCLINE.created_by,
SRCLINE.creation_date,
SRCLINE.c_ext_attr1,
SRCLINE.c_ext_attr2,
SRCLINE.c_ext_attr3,
SRCLINE.c_ext_attr4,
SRCLINE.c_ext_attr5,
SRCLINE.c_ext_attr6,
SRCLINE.c_ext_attr7,
SRCLINE.c_ext_attr8,
SRCLINE.c_ext_attr9,
SRCLINE.c_ext_attr10,
SRCLINE.c_ext_attr11,
SRCLINE.c_ext_attr12,
SRCLINE.c_ext_attr13,
SRCLINE.c_ext_attr14,
SRCLINE.c_ext_attr15,
SRCLINE.c_ext_attr16,
SRCLINE.c_ext_attr17,
SRCLINE.c_ext_attr18,
SRCLINE.c_ext_attr19,
SRCLINE.c_ext_attr20,
SRCLINE.c_ext_attr21,
SRCLINE.c_ext_attr22,
SRCLINE.c_ext_attr23,
SRCLINE.c_ext_attr24,
SRCLINE.c_ext_attr25,
SRCLINE.c_ext_attr26,
SRCLINE.c_ext_attr27,
SRCLINE.c_ext_attr28,
SRCLINE.c_ext_attr29,
SRCLINE.c_ext_attr30,
SRCLINE.c_ext_attr31,
SRCLINE.c_ext_attr32,
SRCLINE.c_ext_attr33,
SRCLINE.c_ext_attr34,
SRCLINE.c_ext_attr35,
SRCLINE.c_ext_attr36,
SRCLINE.c_ext_attr37,
SRCLINE.c_ext_attr38,
SRCLINE.c_ext_attr39,
SRCLINE.c_ext_attr40,
SRCLINE.n_ext_attr1,
SRCLINE.n_ext_attr2,
SRCLINE.n_ext_attr3,
SRCLINE.n_ext_attr4,
SRCLINE.n_ext_attr5,
SRCLINE.n_ext_attr6,
SRCLINE.n_ext_attr7,
SRCLINE.n_ext_attr8,
SRCLINE.n_ext_attr9,
SRCLINE.n_ext_attr10,
SRCLINE.n_ext_attr11,
SRCLINE.n_ext_attr12,
SRCLINE.n_ext_attr13,
SRCLINE.n_ext_attr14,
SRCLINE.n_ext_attr15,
SRCLINE.n_ext_attr16,
SRCLINE.n_ext_attr17,
SRCLINE.n_ext_attr18,
SRCLINE.n_ext_attr19,
SRCLINE.n_ext_attr20,
SRCLINE.uom_ext_attr1,
SRCLINE.uom_ext_attr2,
SRCLINE.uom_ext_attr3,
SRCLINE.uom_ext_attr4,
SRCLINE.uom_ext_attr5,
SRCLINE.uom_ext_attr6,
SRCLINE.uom_ext_attr7,
SRCLINE.uom_ext_attr8,
SRCLINE.uom_ext_attr9,
SRCLINE.uom_ext_attr10,
SRCLINE.uom_ext_attr11,
SRCLINE.uom_ext_attr12,
SRCLINE.uom_ext_attr13,
SRCLINE.uom_ext_attr14,
SRCLINE.uom_ext_attr15,
SRCLINE.uom_ext_attr16,
SRCLINE.uom_ext_attr17,
SRCLINE.uom_ext_attr18,
SRCLINE.uom_ext_attr19,
SRCLINE.uom_ext_attr20,
SRCLINE.d_ext_attr1,
SRCLINE.d_ext_attr2,
SRCLINE.d_ext_attr3,
SRCLINE.d_ext_attr4,
SRCLINE.d_ext_attr5,
SRCLINE.d_ext_attr6,
SRCLINE.d_ext_attr7,
SRCLINE.d_ext_attr8,
SRCLINE.d_ext_attr9,
SRCLINE.d_ext_attr10,
SRCLINE.uda_template_id,
SRCLINE.draft_id
FROM po_lines_all_ext_b SRCLINE WHERE SRCLINE.po_line_id = po_src_line_id AND draft_id = p_draft_id
AND NOT EXISTS (SELECT 1 FROM po_lines_all_ext_b pleb WHERE
pleb.po_line_id =po_dest_line_ids_tbl(i) AND
pleb.attr_group_id = SRCLINE.attr_group_id AND
pleb.draft_id = SRCLINE.draft_id);
selectStmt := selectStmt ||
' SELECT extension_id,attr_group_id,po_line_id,data_level_id,uda_template_id
FROM po_lines_all_ext_b
WHERE po_line_id IN ( ' || po_dest_line_ids || ') AND draft_id = '|| p_draft_id;
EXECUTE IMMEDIATE selectStmt BULK COLLECT INTO extension_id_tbl,attr_group_id_tbl,po_line_id_tbl,data_level_id_tbl,uda_template_id_tbl;
INSERT INTO po_lines_all_ext_tl
(
EXTENSION_ID,
ATTR_GROUP_ID,
PO_LINE_ID,
DATA_LEVEL_ID,
SOURCE_LANG,
LANGUAGE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
UDA_TEMPLATE_ID,
DRAFT_ID
)
SELECT
extension_id_tbl(i),
attr_group_id_tbl(i),
po_line_id_tbl(i),
data_level_id_tbl(i),
USERENV('LANG'),
L.LANGUAGE_CODE,
SYSDATE,
0,
0,
0,
SYSDATE,
uda_template_id_tbl(i),
p_draft_id
FROM FND_LANGUAGES L
WHERE L.INSTALLED_FLAG in ('I', 'B')
AND NOT EXISTS
(SELECT 1 FROM po_lines_all_ext_tl pltl WHERE
pltl.extension_id = extension_id_tbl(i)
AND pltl.LANGUAGE = L.LANGUAGE_CODE);
DELETE FROM po_lines_all_ext_b WHERE po_line_id = po_src_line_id AND draft_id = p_draft_id;
END GLOBAL_UPDATE_LINE_UDA;
PROCEDURE global_update_shipment_uda
(
po_src_line_location_id IN NUMBER ,
po_dest_line_location_ids_tbl IN PO_TBL_NUMBER,
p_draft_id IN NUMBER
)
IS
d_api_name CONSTANT VARCHAR2(30) := 'global_update_shipment_uda';
selectStmt VARCHAR(2000) := '';
INSERT INTO po_line_locations_all_ext_b DESTlINE
(DESTLINE.extension_id,
DESTLINE.attr_group_id,
DESTLINE.line_location_id,
DESTLINE.data_level_id,
DESTLINE.pk1_value,
DESTLINE.pk2_value,
DESTLINE.pk3_value,
DESTLINE.pk4_value,
DESTLINE.pk5_value,
DESTLINE.last_update_date,
DESTLINE.last_updated_by,
DESTLINE.last_update_login,
DESTLINE.created_by,
DESTLINE.creation_date,
DESTLINE.c_ext_attr1,
DESTLINE.c_ext_attr2,
DESTLINE.c_ext_attr3,
DESTLINE.c_ext_attr4,
DESTLINE.c_ext_attr5,
DESTLINE.c_ext_attr6,
DESTLINE.c_ext_attr7,
DESTLINE.c_ext_attr8,
DESTLINE.c_ext_attr9,
DESTLINE.c_ext_attr10,
DESTLINE.c_ext_attr11,
DESTLINE.c_ext_attr12,
DESTLINE.c_ext_attr13,
DESTLINE.c_ext_attr14,
DESTLINE.c_ext_attr15,
DESTLINE.c_ext_attr16,
DESTLINE.c_ext_attr17,
DESTLINE.c_ext_attr18,
DESTLINE.c_ext_attr19,
DESTLINE.c_ext_attr20,
DESTLINE.c_ext_attr21,
DESTLINE.c_ext_attr22,
DESTLINE.c_ext_attr23,
DESTLINE.c_ext_attr24,
DESTLINE.c_ext_attr25,
DESTLINE.c_ext_attr26,
DESTLINE.c_ext_attr27,
DESTLINE.c_ext_attr28,
DESTLINE.c_ext_attr29,
DESTLINE.c_ext_attr30,
DESTLINE.c_ext_attr31,
DESTLINE.c_ext_attr32,
DESTLINE.c_ext_attr33,
DESTLINE.c_ext_attr34,
DESTLINE.c_ext_attr35,
DESTLINE.c_ext_attr36,
DESTLINE.c_ext_attr37,
DESTLINE.c_ext_attr38,
DESTLINE.c_ext_attr39,
DESTLINE.c_ext_attr40,
DESTLINE.n_ext_attr1,
DESTLINE.n_ext_attr2,
DESTLINE.n_ext_attr3,
DESTLINE.n_ext_attr4,
DESTLINE.n_ext_attr5,
DESTLINE.n_ext_attr6,
DESTLINE.n_ext_attr7,
DESTLINE.n_ext_attr8,
DESTLINE.n_ext_attr9,
DESTLINE.n_ext_attr10,
DESTLINE.n_ext_attr11,
DESTLINE.n_ext_attr12,
DESTLINE.n_ext_attr13,
DESTLINE.n_ext_attr14,
DESTLINE.n_ext_attr15,
DESTLINE.n_ext_attr16,
DESTLINE.n_ext_attr17,
DESTLINE.n_ext_attr18,
DESTLINE.n_ext_attr19,
DESTLINE.n_ext_attr20,
DESTLINE.uom_ext_attr1,
DESTLINE.uom_ext_attr2,
DESTLINE.uom_ext_attr3,
DESTLINE.uom_ext_attr4,
DESTLINE.uom_ext_attr5,
DESTLINE.uom_ext_attr6,
DESTLINE.uom_ext_attr7,
DESTLINE.uom_ext_attr8,
DESTLINE.uom_ext_attr9,
DESTLINE.uom_ext_attr10,
DESTLINE.uom_ext_attr11,
DESTLINE.uom_ext_attr12,
DESTLINE.uom_ext_attr13,
DESTLINE.uom_ext_attr14,
DESTLINE.uom_ext_attr15,
DESTLINE.uom_ext_attr16,
DESTLINE.uom_ext_attr17,
DESTLINE.uom_ext_attr18,
DESTLINE.uom_ext_attr19,
DESTLINE.uom_ext_attr20,
DESTLINE.d_ext_attr1,
DESTLINE.d_ext_attr2,
DESTLINE.d_ext_attr3,
DESTLINE.d_ext_attr4,
DESTLINE.d_ext_attr5,
DESTLINE.d_ext_attr6,
DESTLINE.d_ext_attr7,
DESTLINE.d_ext_attr8,
DESTLINE.d_ext_attr9,
DESTLINE.d_ext_attr10,
DESTLINE.uda_template_id,
DESTLINE.draft_id)
SELECT
EGO_EXTFWK_S.NEXTVAL,
SRCLINE.attr_group_id,
po_dest_line_location_ids_tbl(i),
SRCLINE.data_level_id,
SRCLINE.pk1_value,
SRCLINE.pk2_value,
SRCLINE.pk3_value,
SRCLINE.pk4_value,
SRCLINE.pk5_value,
SRCLINE.last_update_date,
SRCLINE.last_updated_by,
SRCLINE.last_update_login,
SRCLINE.created_by,
SRCLINE.creation_date,
SRCLINE.c_ext_attr1,
SRCLINE.c_ext_attr2,
SRCLINE.c_ext_attr3,
SRCLINE.c_ext_attr4,
SRCLINE.c_ext_attr5,
SRCLINE.c_ext_attr6,
SRCLINE.c_ext_attr7,
SRCLINE.c_ext_attr8,
SRCLINE.c_ext_attr9,
SRCLINE.c_ext_attr10,
SRCLINE.c_ext_attr11,
SRCLINE.c_ext_attr12,
SRCLINE.c_ext_attr13,
SRCLINE.c_ext_attr14,
SRCLINE.c_ext_attr15,
SRCLINE.c_ext_attr16,
SRCLINE.c_ext_attr17,
SRCLINE.c_ext_attr18,
SRCLINE.c_ext_attr19,
SRCLINE.c_ext_attr20,
SRCLINE.c_ext_attr21,
SRCLINE.c_ext_attr22,
SRCLINE.c_ext_attr23,
SRCLINE.c_ext_attr24,
SRCLINE.c_ext_attr25,
SRCLINE.c_ext_attr26,
SRCLINE.c_ext_attr27,
SRCLINE.c_ext_attr28,
SRCLINE.c_ext_attr29,
SRCLINE.c_ext_attr30,
SRCLINE.c_ext_attr31,
SRCLINE.c_ext_attr32,
SRCLINE.c_ext_attr33,
SRCLINE.c_ext_attr34,
SRCLINE.c_ext_attr35,
SRCLINE.c_ext_attr36,
SRCLINE.c_ext_attr37,
SRCLINE.c_ext_attr38,
SRCLINE.c_ext_attr39,
SRCLINE.c_ext_attr40,
SRCLINE.n_ext_attr1,
SRCLINE.n_ext_attr2,
SRCLINE.n_ext_attr3,
SRCLINE.n_ext_attr4,
SRCLINE.n_ext_attr5,
SRCLINE.n_ext_attr6,
SRCLINE.n_ext_attr7,
SRCLINE.n_ext_attr8,
SRCLINE.n_ext_attr9,
SRCLINE.n_ext_attr10,
SRCLINE.n_ext_attr11,
SRCLINE.n_ext_attr12,
SRCLINE.n_ext_attr13,
SRCLINE.n_ext_attr14,
SRCLINE.n_ext_attr15,
SRCLINE.n_ext_attr16,
SRCLINE.n_ext_attr17,
SRCLINE.n_ext_attr18,
SRCLINE.n_ext_attr19,
SRCLINE.n_ext_attr20,
SRCLINE.uom_ext_attr1,
SRCLINE.uom_ext_attr2,
SRCLINE.uom_ext_attr3,
SRCLINE.uom_ext_attr4,
SRCLINE.uom_ext_attr5,
SRCLINE.uom_ext_attr6,
SRCLINE.uom_ext_attr7,
SRCLINE.uom_ext_attr8,
SRCLINE.uom_ext_attr9,
SRCLINE.uom_ext_attr10,
SRCLINE.uom_ext_attr11,
SRCLINE.uom_ext_attr12,
SRCLINE.uom_ext_attr13,
SRCLINE.uom_ext_attr14,
SRCLINE.uom_ext_attr15,
SRCLINE.uom_ext_attr16,
SRCLINE.uom_ext_attr17,
SRCLINE.uom_ext_attr18,
SRCLINE.uom_ext_attr19,
SRCLINE.uom_ext_attr20,
SRCLINE.d_ext_attr1,
SRCLINE.d_ext_attr2,
SRCLINE.d_ext_attr3,
SRCLINE.d_ext_attr4,
SRCLINE.d_ext_attr5,
SRCLINE.d_ext_attr6,
SRCLINE.d_ext_attr7,
SRCLINE.d_ext_attr8,
SRCLINE.d_ext_attr9,
SRCLINE.d_ext_attr10,
SRCLINE.uda_template_id,
SRCLINE.draft_id
FROM po_line_locations_all_ext_b SRCLINE WHERE SRCLINE.line_location_id = po_src_line_location_id AND draft_id = -1
AND NOT EXISTS (SELECT 1 FROM po_line_locations_all_ext_b pleb WHERE
pleb.line_location_id =po_dest_line_location_ids_tbl(i) AND
pleb.attr_group_id = SRCLINE.attr_group_id AND
pleb.draft_id = SRCLINE.draft_id);
SELECT
attr_group_id,
line_location_id,
data_level_id,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
pk1_value,
pk2_value,
pk3_value,
pk4_value,
pk5_value,
c_ext_attr1,
c_ext_attr2,
c_ext_attr3,
c_ext_attr4,
c_ext_attr5,
c_ext_attr6,
c_ext_attr7,
c_ext_attr8,
c_ext_attr9,
c_ext_attr14,
c_ext_attr15,
c_ext_attr16,
c_ext_attr17,
c_ext_attr18,
c_ext_attr19,
c_ext_attr20,
c_ext_attr21,
c_ext_attr22,
c_ext_attr23,
c_ext_attr24,
c_ext_attr25,
c_ext_attr26,
c_ext_attr27,
c_ext_attr28,
c_ext_attr29,
c_ext_attr30,
c_ext_attr31,
c_ext_attr32,
c_ext_attr33,
c_ext_attr34,
c_ext_attr35,
c_ext_attr36,
c_ext_attr37,
c_ext_attr38,
c_ext_attr39,
c_ext_attr40,
n_ext_attr1,
n_ext_attr2,
n_ext_attr3,
n_ext_attr4,
n_ext_attr5,
n_ext_attr6,
n_ext_attr7,
n_ext_attr8,
n_ext_attr9,
n_ext_attr10,
n_ext_attr11,
n_ext_attr12,
n_ext_attr13,
n_ext_attr14,
n_ext_attr15,
n_ext_attr16,
n_ext_attr17,
n_ext_attr18,
n_ext_attr19,
n_ext_attr20,
uom_ext_attr1,
uom_ext_attr2,
uom_ext_attr3,
uom_ext_attr4,
uom_ext_attr5,
uom_ext_attr6,
uom_ext_attr7,
uom_ext_attr8,
uom_ext_attr9,
uom_ext_attr10,
uom_ext_attr11,
uom_ext_attr12,
uom_ext_attr13,
uom_ext_attr14,
uom_ext_attr15,
uom_ext_attr16,
uom_ext_attr17,
uom_ext_attr18,
uom_ext_attr19,
uom_ext_attr20,
d_ext_attr1,
d_ext_attr2,
d_ext_attr3,
d_ext_attr4,
d_ext_attr5,
d_ext_attr6,
d_ext_attr7,
d_ext_attr8,
d_ext_attr9,
d_ext_attr10,
uda_template_id,
draft_id
FROM po_line_locations_all_ext_b WHERE line_location_id = ' || po_src_line_location_id || ' and draft_id = ' || p_draft_id || ' ) SRCLINE
ON
(DESTLINE.line_location_id IN (' || po_dest_line_location_ids || ') AND DESTLINE.attr_group_id = SRCLINE.attr_group_id AND DESTLINE.draft_id = '|| p_draft_id ||')
WHEN MATCHED THEN
UPDATE
SET
DESTLINE.last_update_date = SYSDATE ,
DESTLINE.last_updated_by = SRCLINE.last_updated_by,
DESTLINE.last_update_login = SRCLINE.last_update_login,
DESTLINE.created_by = SRCLINE.created_by,
DESTLINE.creation_date = SYSDATE,
DESTLINE.c_ext_attr1 = nvl(SRCLINE.c_ext_attr1 , DESTLINE.c_ext_attr1),
DESTLINE.c_ext_attr2 = nvl(SRCLINE.c_ext_attr2 , DESTLINE.c_ext_attr2),
DESTLINE.c_ext_attr3 = nvl(SRCLINE.c_ext_attr3 , DESTLINE.c_ext_attr3),
DESTLINE.c_ext_attr4 = nvl(SRCLINE.c_ext_attr4 , DESTLINE.c_ext_attr4),
DESTLINE.c_ext_attr5 = nvl(SRCLINE.c_ext_attr5 , DESTLINE.c_ext_attr5),
DESTLINE.c_ext_attr6 = nvl(SRCLINE.c_ext_attr6 , DESTLINE.c_ext_attr6),
DESTLINE.c_ext_attr7 = nvl(SRCLINE.c_ext_attr7 , DESTLINE.c_ext_attr7),
DESTLINE.c_ext_attr8 = nvl(SRCLINE.c_ext_attr8 , DESTLINE.c_ext_attr8),
DESTLINE.c_ext_attr9 = nvl(SRCLINE.c_ext_attr9 , DESTLINE.c_ext_attr9),
DESTLINE.c_ext_attr14 = nvl(SRCLINE.c_ext_attr14 , DESTLINE.c_ext_attr14),
DESTLINE.c_ext_attr15 = nvl(SRCLINE.c_ext_attr15 , DESTLINE.c_ext_attr15),
DESTLINE.c_ext_attr16 = nvl(SRCLINE.c_ext_attr16 , DESTLINE.c_ext_attr16),
DESTLINE.c_ext_attr17 = nvl(SRCLINE.c_ext_attr17 , DESTLINE.c_ext_attr17),
DESTLINE.c_ext_attr18 = nvl(SRCLINE.c_ext_attr18 , DESTLINE.c_ext_attr18),
DESTLINE.c_ext_attr19 = nvl(SRCLINE.c_ext_attr19 , DESTLINE.c_ext_attr19),
DESTLINE.c_ext_attr20 = nvl(SRCLINE.c_ext_attr20 , DESTLINE.c_ext_attr20),
DESTLINE.c_ext_attr21 = nvl(SRCLINE.c_ext_attr21 , DESTLINE.c_ext_attr21),
DESTLINE.c_ext_attr22 = nvl(SRCLINE.c_ext_attr22 , DESTLINE.c_ext_attr22),
DESTLINE.c_ext_attr23 = nvl(SRCLINE.c_ext_attr23 , DESTLINE.c_ext_attr23),
DESTLINE.c_ext_attr24 = nvl(SRCLINE.c_ext_attr24 , DESTLINE.c_ext_attr24),
DESTLINE.c_ext_attr25 = nvl(SRCLINE.c_ext_attr25 , DESTLINE.c_ext_attr25),
DESTLINE.c_ext_attr26 = nvl(SRCLINE.c_ext_attr26 , DESTLINE.c_ext_attr26),
DESTLINE.c_ext_attr27 = nvl(SRCLINE.c_ext_attr27 , DESTLINE.c_ext_attr27),
DESTLINE.c_ext_attr28 = nvl(SRCLINE.c_ext_attr28 , DESTLINE.c_ext_attr28),
DESTLINE.c_ext_attr29 = nvl(SRCLINE.c_ext_attr29 , DESTLINE.c_ext_attr29),
DESTLINE.c_ext_attr30 = nvl(SRCLINE.c_ext_attr30 , DESTLINE.c_ext_attr30),
DESTLINE.c_ext_attr31 = nvl(SRCLINE.c_ext_attr31 , DESTLINE.c_ext_attr31),
DESTLINE.c_ext_attr32 = nvl(SRCLINE.c_ext_attr32 , DESTLINE.c_ext_attr32),
DESTLINE.c_ext_attr33 = nvl(SRCLINE.c_ext_attr33 , DESTLINE.c_ext_attr33),
DESTLINE.c_ext_attr34 = nvl(SRCLINE.c_ext_attr34 , DESTLINE.c_ext_attr34),
DESTLINE.c_ext_attr35 = nvl(SRCLINE.c_ext_attr35 , DESTLINE.c_ext_attr35),
DESTLINE.c_ext_attr36 = nvl(SRCLINE.c_ext_attr36 , DESTLINE.c_ext_attr36),
DESTLINE.c_ext_attr37 = nvl(SRCLINE.c_ext_attr37 , DESTLINE.c_ext_attr37),
DESTLINE.c_ext_attr38 = nvl(SRCLINE.c_ext_attr38 , DESTLINE.c_ext_attr38),
DESTLINE.c_ext_attr39 = nvl(SRCLINE.c_ext_attr39 , DESTLINE.c_ext_attr39),
DESTLINE.c_ext_attr40 = nvl(SRCLINE.c_ext_attr40 , DESTLINE.c_ext_attr40),
DESTLINE.n_ext_attr1 = nvl(SRCLINE.n_ext_attr1 , DESTLINE.n_ext_attr1),
DESTLINE.n_ext_attr2 = nvl(SRCLINE.n_ext_attr2 , DESTLINE.n_ext_attr2),
DESTLINE.n_ext_attr3 = nvl(SRCLINE.n_ext_attr3 , DESTLINE.n_ext_attr3),
DESTLINE.n_ext_attr4 = nvl(SRCLINE.n_ext_attr4 , DESTLINE.n_ext_attr4),
DESTLINE.n_ext_attr5 = nvl(SRCLINE.n_ext_attr5 , DESTLINE.n_ext_attr5),
DESTLINE.n_ext_attr6 = nvl(SRCLINE.n_ext_attr6 , DESTLINE.n_ext_attr6),
DESTLINE.n_ext_attr7 = nvl(SRCLINE.n_ext_attr7 , DESTLINE.n_ext_attr7),
DESTLINE.n_ext_attr8 = nvl(SRCLINE.n_ext_attr8 , DESTLINE.n_ext_attr8),
DESTLINE.n_ext_attr9 = nvl(SRCLINE.n_ext_attr9 , DESTLINE.n_ext_attr9),
DESTLINE.n_ext_attr10 = nvl(SRCLINE.n_ext_attr10 , DESTLINE.n_ext_attr10),
DESTLINE.n_ext_attr11 = nvl(SRCLINE.n_ext_attr11 , DESTLINE.n_ext_attr11),
DESTLINE.n_ext_attr12 = nvl(SRCLINE.n_ext_attr12 , DESTLINE.n_ext_attr12),
DESTLINE.n_ext_attr13 = nvl(SRCLINE.n_ext_attr13 , DESTLINE.n_ext_attr13),
DESTLINE.n_ext_attr14 = nvl(SRCLINE.n_ext_attr14 , DESTLINE.n_ext_attr14),
DESTLINE.n_ext_attr15 = nvl(SRCLINE.n_ext_attr15 , DESTLINE.n_ext_attr15),
DESTLINE.n_ext_attr16 = nvl(SRCLINE.n_ext_attr16 , DESTLINE.n_ext_attr16),
DESTLINE.n_ext_attr17 = nvl(SRCLINE.n_ext_attr17 , DESTLINE.n_ext_attr17),
DESTLINE.n_ext_attr18 = nvl(SRCLINE.n_ext_attr18 , DESTLINE.n_ext_attr18),
DESTLINE.n_ext_attr19 = nvl(SRCLINE.n_ext_attr19 , DESTLINE.n_ext_attr19),
DESTLINE.n_ext_attr20 = nvl(SRCLINE.n_ext_attr20 , DESTLINE.n_ext_attr20),
DESTLINE.uom_ext_attr1 = nvl(SRCLINE.uom_ext_attr1 , DESTLINE.uom_ext_attr1),
DESTLINE.uom_ext_attr2 = nvl(SRCLINE.uom_ext_attr2 , DESTLINE.uom_ext_attr2),
DESTLINE.uom_ext_attr3 = nvl(SRCLINE.uom_ext_attr3 , DESTLINE.uom_ext_attr3),
DESTLINE.uom_ext_attr4 = nvl(SRCLINE.uom_ext_attr4 , DESTLINE.uom_ext_attr4),
DESTLINE.uom_ext_attr5 = nvl(SRCLINE.uom_ext_attr5 , DESTLINE.uom_ext_attr5),
DESTLINE.uom_ext_attr6 = nvl(SRCLINE.uom_ext_attr6 , DESTLINE.uom_ext_attr6),
DESTLINE.uom_ext_attr7 = nvl(SRCLINE.uom_ext_attr7 , DESTLINE.uom_ext_attr7),
DESTLINE.uom_ext_attr8 = nvl(SRCLINE.uom_ext_attr8 , DESTLINE.uom_ext_attr8),
DESTLINE.uom_ext_attr9 = nvl(SRCLINE.uom_ext_attr9 , DESTLINE.uom_ext_attr9),
DESTLINE.uom_ext_attr10 = nvl(SRCLINE.uom_ext_attr10 , DESTLINE.uom_ext_attr10),
DESTLINE.uom_ext_attr11 = nvl(SRCLINE.uom_ext_attr11 , DESTLINE.uom_ext_attr11),
DESTLINE.uom_ext_attr12 = nvl(SRCLINE.uom_ext_attr12 , DESTLINE.uom_ext_attr12),
DESTLINE.uom_ext_attr13 = nvl(SRCLINE.uom_ext_attr13 , DESTLINE.uom_ext_attr13),
DESTLINE.uom_ext_attr14 = nvl(SRCLINE.uom_ext_attr14 , DESTLINE.uom_ext_attr14),
DESTLINE.uom_ext_attr15 = nvl(SRCLINE.uom_ext_attr15 , DESTLINE.uom_ext_attr15),
DESTLINE.uom_ext_attr16 = nvl(SRCLINE.uom_ext_attr16 , DESTLINE.uom_ext_attr16),
DESTLINE.uom_ext_attr17 = nvl(SRCLINE.uom_ext_attr17 , DESTLINE.uom_ext_attr17),
DESTLINE.uom_ext_attr18 = nvl(SRCLINE.uom_ext_attr18 , DESTLINE.uom_ext_attr18),
DESTLINE.uom_ext_attr19 = nvl(SRCLINE.uom_ext_attr19 , DESTLINE.uom_ext_attr19),
DESTLINE.uom_ext_attr20 = nvl(SRCLINE.uom_ext_attr20 , DESTLINE.uom_ext_attr20),
DESTLINE.d_ext_attr1 = nvl(SRCLINE.d_ext_attr1 , DESTLINE.d_ext_attr1),
DESTLINE.d_ext_attr2 = nvl(SRCLINE.d_ext_attr2 , DESTLINE.d_ext_attr2),
DESTLINE.d_ext_attr3 = nvl(SRCLINE.d_ext_attr3 , DESTLINE.d_ext_attr3),
DESTLINE.d_ext_attr4 = nvl(SRCLINE.d_ext_attr4 , DESTLINE.d_ext_attr4),
DESTLINE.d_ext_attr5 = nvl(SRCLINE.d_ext_attr5 , DESTLINE.d_ext_attr5),
DESTLINE.d_ext_attr6 = nvl(SRCLINE.d_ext_attr6 , DESTLINE.d_ext_attr6),
DESTLINE.d_ext_attr7 = nvl(SRCLINE.d_ext_attr7 , DESTLINE.d_ext_attr7),
DESTLINE.d_ext_attr8 = nvl(SRCLINE.d_ext_attr8 , DESTLINE.d_ext_attr8),
DESTLINE.d_ext_attr9 = nvl(SRCLINE.d_ext_attr9 , DESTLINE.d_ext_attr9),
DESTLINE.d_ext_attr10 = nvl(SRCLINE.d_ext_attr10 , DESTLINE.d_ext_attr10)' ;
INSERT INTO po_line_locations_all_ext_b DESTlINE
(DESTLINE.extension_id,
DESTLINE.attr_group_id,
DESTLINE.line_location_id,
DESTLINE.data_level_id,
DESTLINE.pk1_value,
DESTLINE.pk2_value,
DESTLINE.pk3_value,
DESTLINE.pk4_value,
DESTLINE.pk5_value,
DESTLINE.last_update_date,
DESTLINE.last_updated_by,
DESTLINE.last_update_login,
DESTLINE.created_by,
DESTLINE.creation_date,
DESTLINE.c_ext_attr1,
DESTLINE.c_ext_attr2,
DESTLINE.c_ext_attr3,
DESTLINE.c_ext_attr4,
DESTLINE.c_ext_attr5,
DESTLINE.c_ext_attr6,
DESTLINE.c_ext_attr7,
DESTLINE.c_ext_attr8,
DESTLINE.c_ext_attr9,
DESTLINE.c_ext_attr14,
DESTLINE.c_ext_attr15,
DESTLINE.c_ext_attr16,
DESTLINE.c_ext_attr17,
DESTLINE.c_ext_attr18,
DESTLINE.c_ext_attr19,
DESTLINE.c_ext_attr20,
DESTLINE.c_ext_attr21,
DESTLINE.c_ext_attr22,
DESTLINE.c_ext_attr23,
DESTLINE.c_ext_attr24,
DESTLINE.c_ext_attr25,
DESTLINE.c_ext_attr26,
DESTLINE.c_ext_attr27,
DESTLINE.c_ext_attr28,
DESTLINE.c_ext_attr29,
DESTLINE.c_ext_attr30,
DESTLINE.c_ext_attr31,
DESTLINE.c_ext_attr32,
DESTLINE.c_ext_attr33,
DESTLINE.c_ext_attr34,
DESTLINE.c_ext_attr35,
DESTLINE.c_ext_attr36,
DESTLINE.c_ext_attr37,
DESTLINE.c_ext_attr38,
DESTLINE.c_ext_attr39,
DESTLINE.c_ext_attr40,
DESTLINE.n_ext_attr1,
DESTLINE.n_ext_attr2,
DESTLINE.n_ext_attr3,
DESTLINE.n_ext_attr4,
DESTLINE.n_ext_attr5,
DESTLINE.n_ext_attr6,
DESTLINE.n_ext_attr7,
DESTLINE.n_ext_attr8,
DESTLINE.n_ext_attr9,
DESTLINE.n_ext_attr10,
DESTLINE.n_ext_attr11,
DESTLINE.n_ext_attr12,
DESTLINE.n_ext_attr13,
DESTLINE.n_ext_attr14,
DESTLINE.n_ext_attr15,
DESTLINE.n_ext_attr16,
DESTLINE.n_ext_attr17,
DESTLINE.n_ext_attr18,
DESTLINE.n_ext_attr19,
DESTLINE.n_ext_attr20,
DESTLINE.uom_ext_attr1,
DESTLINE.uom_ext_attr2,
DESTLINE.uom_ext_attr3,
DESTLINE.uom_ext_attr4,
DESTLINE.uom_ext_attr5,
DESTLINE.uom_ext_attr6,
DESTLINE.uom_ext_attr7,
DESTLINE.uom_ext_attr8,
DESTLINE.uom_ext_attr9,
DESTLINE.uom_ext_attr10,
DESTLINE.uom_ext_attr11,
DESTLINE.uom_ext_attr12,
DESTLINE.uom_ext_attr13,
DESTLINE.uom_ext_attr14,
DESTLINE.uom_ext_attr15,
DESTLINE.uom_ext_attr16,
DESTLINE.uom_ext_attr17,
DESTLINE.uom_ext_attr18,
DESTLINE.uom_ext_attr19,
DESTLINE.uom_ext_attr20,
DESTLINE.d_ext_attr1,
DESTLINE.d_ext_attr2,
DESTLINE.d_ext_attr3,
DESTLINE.d_ext_attr4,
DESTLINE.d_ext_attr5,
DESTLINE.d_ext_attr6,
DESTLINE.d_ext_attr7,
DESTLINE.d_ext_attr8,
DESTLINE.d_ext_attr9,
DESTLINE.d_ext_attr10,
DESTLINE.uda_template_id,
DESTLINE.draft_id)
SELECT
EGO_EXTFWK_S.NEXTVAL,
SRCLINE.attr_group_id,
po_dest_line_location_ids_tbl(i),
SRCLINE.data_level_id,
SRCLINE.pk1_value,
SRCLINE.pk2_value,
SRCLINE.pk3_value,
SRCLINE.pk4_value,
SRCLINE.pk5_value,
SRCLINE.last_update_date,
SRCLINE.last_updated_by,
SRCLINE.last_update_login,
SRCLINE.created_by,
SRCLINE.creation_date,
SRCLINE.c_ext_attr1,
SRCLINE.c_ext_attr2,
SRCLINE.c_ext_attr3,
SRCLINE.c_ext_attr4,
SRCLINE.c_ext_attr5,
SRCLINE.c_ext_attr6,
SRCLINE.c_ext_attr7,
SRCLINE.c_ext_attr8,
SRCLINE.c_ext_attr9,
SRCLINE.c_ext_attr14,
SRCLINE.c_ext_attr15,
SRCLINE.c_ext_attr16,
SRCLINE.c_ext_attr17,
SRCLINE.c_ext_attr18,
SRCLINE.c_ext_attr19,
SRCLINE.c_ext_attr20,
SRCLINE.c_ext_attr21,
SRCLINE.c_ext_attr22,
SRCLINE.c_ext_attr23,
SRCLINE.c_ext_attr24,
SRCLINE.c_ext_attr25,
SRCLINE.c_ext_attr26,
SRCLINE.c_ext_attr27,
SRCLINE.c_ext_attr28,
SRCLINE.c_ext_attr29,
SRCLINE.c_ext_attr30,
SRCLINE.c_ext_attr31,
SRCLINE.c_ext_attr32,
SRCLINE.c_ext_attr33,
SRCLINE.c_ext_attr34,
SRCLINE.c_ext_attr35,
SRCLINE.c_ext_attr36,
SRCLINE.c_ext_attr37,
SRCLINE.c_ext_attr38,
SRCLINE.c_ext_attr39,
SRCLINE.c_ext_attr40,
SRCLINE.n_ext_attr1,
SRCLINE.n_ext_attr2,
SRCLINE.n_ext_attr3,
SRCLINE.n_ext_attr4,
SRCLINE.n_ext_attr5,
SRCLINE.n_ext_attr6,
SRCLINE.n_ext_attr7,
SRCLINE.n_ext_attr8,
SRCLINE.n_ext_attr9,
SRCLINE.n_ext_attr10,
SRCLINE.n_ext_attr11,
SRCLINE.n_ext_attr12,
SRCLINE.n_ext_attr13,
SRCLINE.n_ext_attr14,
SRCLINE.n_ext_attr15,
SRCLINE.n_ext_attr16,
SRCLINE.n_ext_attr17,
SRCLINE.n_ext_attr18,
SRCLINE.n_ext_attr19,
SRCLINE.n_ext_attr20,
SRCLINE.uom_ext_attr1,
SRCLINE.uom_ext_attr2,
SRCLINE.uom_ext_attr3,
SRCLINE.uom_ext_attr4,
SRCLINE.uom_ext_attr5,
SRCLINE.uom_ext_attr6,
SRCLINE.uom_ext_attr7,
SRCLINE.uom_ext_attr8,
SRCLINE.uom_ext_attr9,
SRCLINE.uom_ext_attr10,
SRCLINE.uom_ext_attr11,
SRCLINE.uom_ext_attr12,
SRCLINE.uom_ext_attr13,
SRCLINE.uom_ext_attr14,
SRCLINE.uom_ext_attr15,
SRCLINE.uom_ext_attr16,
SRCLINE.uom_ext_attr17,
SRCLINE.uom_ext_attr18,
SRCLINE.uom_ext_attr19,
SRCLINE.uom_ext_attr20,
SRCLINE.d_ext_attr1,
SRCLINE.d_ext_attr2,
SRCLINE.d_ext_attr3,
SRCLINE.d_ext_attr4,
SRCLINE.d_ext_attr5,
SRCLINE.d_ext_attr6,
SRCLINE.d_ext_attr7,
SRCLINE.d_ext_attr8,
SRCLINE.d_ext_attr9,
SRCLINE.d_ext_attr10,
SRCLINE.uda_template_id,
SRCLINE.draft_id
FROM po_line_locations_all_ext_b SRCLINE WHERE SRCLINE.line_location_id = po_src_line_location_id AND draft_id = p_draft_id
AND NOT EXISTS (SELECT 1 FROM po_line_locations_all_ext_b pleb WHERE
pleb.line_location_id =po_dest_line_location_ids_tbl(i) AND
pleb.attr_group_id = SRCLINE.attr_group_id AND
pleb.draft_id = SRCLINE.draft_id);
SELECT SubStr(c_ext_attr13,Length('')+1,instr(c_ext_attr13,' ')-(Length('')+1 )) ship_to_location_id
INTO l_ship_to_loc_id
FROM po_line_locations_all_ext_b
WHERE line_location_id = po_src_line_location_id
AND draft_id = p_draft_id;
get_valid_line_loc_stmt := 'SELECT line_location_id
FROM po_line_locations_merge_v
WHERE line_location_id IN ( ' || po_dest_line_location_ids || ')
AND nvl(draft_id,-1) = '|| p_draft_id ||
' AND 0 = PO_LOCATIONS_S.val_location('|| l_ship_to_loc_id||',null,org_id )';
SELECT
line_location_id,
attr_group_id,
draft_id,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
c_ext_attr10,
c_ext_attr11,
c_ext_attr12,
c_ext_attr13
FROM po_line_locations_all_ext_b WHERE line_location_id = po_src_line_location_id and draft_id = p_draft_id ) SRCLINE
ON
(DESTLINE.line_location_id IN (SELECT column_value FROM TABLE(get_valid_line_loc_tbl) ) AND DESTLINE.attr_group_id = SRCLINE.attr_group_id AND DESTLINE.draft_id = p_draft_id )
WHEN MATCHED THEN
UPDATE
SET
DESTLINE.last_update_date = SYSDATE ,
DESTLINE.last_updated_by = SRCLINE.last_updated_by,
DESTLINE.last_update_login = SRCLINE.last_update_login,
DESTLINE.created_by = SRCLINE.created_by,
DESTLINE.creation_date = SYSDATE,
DESTLINE.c_ext_attr10 = nvl(SRCLINE.c_ext_attr10 , DESTLINE.c_ext_attr10),
DESTLINE.c_ext_attr11 = nvl(SRCLINE.c_ext_attr11 , DESTLINE.c_ext_attr11),
DESTLINE.c_ext_attr12 = nvl(SRCLINE.c_ext_attr12 , DESTLINE.c_ext_attr12),
DESTLINE.c_ext_attr13 = nvl(SRCLINE.c_ext_attr13 , DESTLINE.c_ext_attr13);
selectStmt := selectStmt ||
' SELECT extension_id,attr_group_id,line_location_id,data_level_id,uda_template_id
FROM po_line_locations_all_ext_b
WHERE line_location_id IN ( ' || po_dest_line_location_ids || ') AND draft_id = '|| p_draft_id;
EXECUTE IMMEDIATE selectStmt BULK COLLECT INTO extension_id_tbl,attr_group_id_tbl,line_location_id_tbl,data_level_id_tbl,uda_template_id_tbl;
INSERT INTO po_line_locations_all_ext_tl
(
EXTENSION_ID,
ATTR_GROUP_ID,
LINE_LOCATION_ID,
DATA_LEVEL_ID,
SOURCE_LANG,
LANGUAGE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
UDA_TEMPLATE_ID,
DRAFT_ID
)
SELECT
extension_id_tbl(i),
attr_group_id_tbl(i),
line_location_id_tbl(i),
data_level_id_tbl(i),
USERENV('LANG'),
L.LANGUAGE_CODE,
SYSDATE,
0,
0,
0,
SYSDATE,
uda_template_id_tbl(i),
p_draft_id
FROM FND_LANGUAGES L
WHERE L.INSTALLED_FLAG in ('I', 'B')
AND NOT EXISTS
(SELECT 1 FROM po_line_locations_all_ext_tl pltl WHERE
pltl.extension_id = extension_id_tbl(i)
AND pltl.LANGUAGE = L.LANGUAGE_CODE);
DELETE FROM po_line_locations_all_ext_b WHERE line_location_id = po_src_line_location_id AND draft_id = p_draft_id;
END GLOBAL_UPDATE_SHIPMENT_UDA;
l_sql_stmt := 'SELECT COUNT(*)
FROM HR_LOCATIONS_ALL
WHERE LOCATION_ID = :1
AND BILL_TO_SITE_FLAG = ''Y''
AND SYSDATE < NVL(INACTIVE_DATE, SYSDATE + 1)
AND (NVL(BUSINESS_GROUP_ID, NVL(HR_GENERAL.GET_BUSINESS_GROUP_ID, -99)) = NVL(HR_GENERAL.GET_BUSINESS_GROUP_ID, -99))';
l_sql_stmt := 'SELECT Count(*) FROM HR_LOCATION_EXTRA_INFO '
||' WHERE INFORMATION_TYPE = ''CLM_OFFICE_CODE'' '
||' AND location_id = :1 AND ';
SELECT attribute_group_id
INTO l_attr_group_id
FROM po_uda_ag_template_usages
WHERE attribute2 = p_contract_type
AND attribute1 = p_idc_type
AND template_id = p_template_id
AND attribute_category = p_attr_category;
SELECT END_USER_COLUMN_NAME, APPLICATION_COLUMN_NAME
BULK COLLECT INTO user_col_name_tbl, appl_col_name_tbl
FROM fnd_descr_flex_column_usages
WHERE APPLICATION_ID=800
and DESCRIPTIVE_FLEXFIELD_NAME='Extra Location Info DDF'
and DESCRIPTIVE_FLEX_CONTEXT_CODE = 'CLM_OFFICE_CODE' ;