The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_program_update_date DATE;
l_last_update_date DATE;
l_last_update_login NUMBER;
l_last_updated_by NUMBER;
l_last_updated_by_name VARCHAR2(100);
SELECT INTERFACE_REFERENCE_ID
,TRANSACTION_ID
,ROW_IDENTIFIER
,ATTR_GROUP_ID
,ATTR_GROUP_APP_ID
,ATTR_GROUP_NAME
,DATA_LEVEL_1
,DATA_LEVEL_2
,DATA_LEVEL_3
,TRANSACTION_TYPE
,ATTR_NAME
,ATTR_VALUE_STR
,ATTR_VALUE_NUM
,ATTR_VALUE_DATE
,ATTR_DISP_VALUE
,ATTR_UNIT_OF_MEASURE
,USER_ROW_IDENTIFIER
,UDA_TEMPLATE_ID
,PROCESS_STATUS
,PROCESS_PHASE
,REQUEST_ID
,PROGRAM_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_UPDATE_DATE
,LAST_UPDATE_DATE
,CREATION_DATE
,LAST_UPDATE_LOGIN
,CREATED_BY
,LAST_UPDATED_BY
,CREATED_BY_NAME
,LAST_UPDATED_BY_NAME
,INTERFACE_SOURCE_CODE
,INTERFACE_SOURCE_LINE_ID
FROM PO_UDA_INTERFACE
WHERE PROCESS_STATUS = PON_UDA_IMPORT_PKG.IN_PROGRESS
AND NVL(PK1_VALUE, -999) = NVL(p_pk1_value, -999)
AND NVL(PK2_VALUE, -999) = NVL(p_pk2_value, -999)
AND NVL(PK3_VALUE, -999) = NVL(p_pk3_value, -999)
AND NVL(PK4_VALUE, -999) = NVL(p_pk4_value, -999)
AND NVL(PK5_VALUE, -999) = NVL(p_pk5_value, -999)
AND ATTR_GROUP_TYPE = p_attr_grp_type
ORDER BY ATTR_GROUP_ID;
SELECT INTERFACE_REFERENCE_ID
INTO g_interface_reference_id
FROM PO_UDA_INTERFACE
WHERE NVL(PK1_VALUE, -999) = NVL(p_pk1_value, -999)
AND NVL(PK2_VALUE, -999) = NVL(p_pk2_value, -999)
AND NVL(PK3_VALUE, -999) = NVL(p_pk3_value, -999)
AND NVL(PK4_VALUE, -999) = NVL(p_pk4_value, -999)
AND NVL(PK5_VALUE, -999) = NVL(p_pk5_value, -999)
AND ROWNUM < 2;
,l_program_update_date
,l_last_update_date
,l_creation_date
,l_last_update_login
,l_created_by
,l_last_updated_by
,l_created_by_name
,l_last_updated_by_name
,l_interface_source_code
,l_interface_source_line_id;
PON_UDA_IMPORT_PKG.DEBUG('l_program_update_date : ' || l_program_update_date, d_progress);
PON_UDA_IMPORT_PKG.DEBUG('l_last_update_date : ' || l_last_update_date, d_progress);
PON_UDA_IMPORT_PKG.DEBUG('l_last_updated_by : ' || l_last_updated_by, d_progress);
PON_UDA_IMPORT_PKG.DEBUG('l_last_updated_by_name : ' || l_last_updated_by_name, d_progress);
SELECT ATTRIBUTE_CATEGORY INTO l_attr_category
FROM po_uda_ag_template_usages
WHERE TEMPLATE_ID=l_uda_template_id AND
ATTRIBUTE_GROUP_ID = l_attr_group_id;
SELECT distinct agtl.DESCRIPTIVE_FLEX_CONTEXT_NAME, attr.DATABASE_COLUMN
INTO l_desc_flex_cntxt_name, l_database_col
FROM FND_DESCR_FLEX_CONTEXTS_tl agtl,
FND_DESCR_FLEX_CONTEXTS ag,
EGO_ATTRS_V attr,
po_uda_ag_templates t,
po_uda_ag_template_usages u,
ego_fnd_dsc_flx_ctx_ext egoag
WHERE attr.ATTR_GROUP_TYPE = ag.DESCRIPTIVE_FLEXFIELD_NAME
AND attr.ATTR_GROUP_NAME = ag.DESCRIPTIVE_FLEX_CONTEXT_CODE
AND ag.DESCRIPTIVE_FLEXFIELD_NAME = agtl.DESCRIPTIVE_FLEXFIELD_NAME
AND ag.DESCRIPTIVE_FLEX_CONTEXT_CODE = agtl.DESCRIPTIVE_FLEX_CONTEXT_CODE
AND agtl.LANGUAGE = UserEnv('LANG')
AND t.template_id = u.template_id
AND egoag.attr_group_id = u.attribute_group_id
AND egoag.DESCRIPTIVE_FLEXFIELD_NAME = ag.DESCRIPTIVE_FLEXFIELD_NAME
AND egoag.DESCRIPTIVE_FLEX_CONTEXT_CODE = ag.DESCRIPTIVE_FLEX_CONTEXT_CODE
AND t.template_id = l_uda_template_id
AND u.ATTRIBUTE_CATEGORY = l_attr_category
AND egoag.attr_group_id = l_attr_group_id
AND attr.ATTR_NAME = l_attr_name;
SELECT REGEXP_SUBSTR(l_database_col,'N[^N]+R') INTO l_compare_string FROM dual;
SELECT END_USER_COLUMN_NAME, APPLICATION_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 ATTR_GROUP_ID INTO l_attr_group_id
FROM ego_fnd_dsc_flx_ctx_ext
WHERE DESCRIPTIVE_FLEXFIELD_NAME = l_attr_group_type AND
DESCRIPTIVE_FLEX_CONTEXT_CODE = l_attr_group_name;
SELECT TEMPLATE_ID INTO l_uda_template_id
FROM po_uda_ag_templates
WHERE FUNCTIONAL_AREA='SOURCING' AND
ENTITY_CODE=l_attr_group_type;
SELECT ATTRIBUTE_CATEGORY INTO l_attr_category
FROM po_uda_ag_template_usages
WHERE TEMPLATE_ID=l_uda_template_id AND
ATTRIBUTE_GROUP_ID = l_attr_group_id;
SELECT PO_UDA_INTERFACE_USER_ROW_ID_S.NEXTVAL
INTO l_user_row_identifier
FROM DUAL;
SELECT REGEXP_SUBSTR(l_attr_col_name,'N[^N]+R') INTO l_compare_string FROM dual;
SELECT REGEXP_SUBSTR(l_attr_col_name,'N[^N]+R') INTO l_compare_string FROM dual;
SELECT END_USER_COLUMN_NAME, APPLICATION_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 DISTINCT LOOKUP_CODE , TAG
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = p_address_lookup_type
ORDER BY TAG;
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 = l_attr_group_id;
l_sql_query := 'SELECT 1 FROM ' || l_base_table;
SELECT DATA_LEVEL_NAME
INTO l_data_level
FROM EGO_DATA_LEVEL_B
WHERE APPLICATION_ID = 201
AND ATTR_GROUP_TYPE = l_attr_group_type
AND DATA_LEVEL_NAME NOT LIKE '%ARCHIVE'
AND ATTR_GROUP_TYPE NOT LIKE '%DUMMY%';
SELECT Count(*)
INTO l_xd_present
FROM EGO_ACTION_DISPLAYS_B, EGO_ACTIONS_B
WHERE classification_code = p_template_id
AND attr_group_id = l_attr_group_id
AND execution_method = 'XD'
AND EGO_ACTION_DISPLAYS_B.action_id = EGO_ACTIONS_B.action_id;
SELECT PO_UDA_INTERFACE_ROW_ID_S.NEXTVAL
INTO l_row_identifier
FROM DUAL;
SELECT PO_UDA_INTERFACE_USER_ROW_ID_S.NEXTVAL
INTO l_user_row_identifier
FROM DUAL;
SELECT PO_UDA_INTERFACE_ROW_ID_S.NEXTVAL
INTO l_row_identifier
FROM DUAL;
SELECT PO_UDA_INTERFACE_USER_ROW_ID_S.NEXTVAL
INTO l_user_row_identifier
FROM DUAL;
SELECT EGO_ACTION_DISPLAYS_B.action_id FROM EGO_ACTION_DISPLAYS_B, EGO_ACTIONS_B
WHERE classification_code = p_template_id
AND attr_group_id = p_attribute_group_id
AND execution_method = p_event
AND EGO_ACTION_DISPLAYS_B.action_id = EGO_ACTIONS_B.action_id
ORDER BY SEQUENCE;
SELECT DATA_LEVEL_NAME, DATA_LEVEL_ID
INTO l_data_level_name, l_data_level_id
FROM EGO_DATA_LEVEL_B
WHERE ATTR_GROUP_TYPE = l_attr_row_obj.ATTR_GROUP_TYPE
AND DATA_LEVEL_NAME NOT LIKE '%ARCHIVE';
x_attr_name_value_pairs.DELETE(l_attr_id_index);
x_attributes_row_table.DELETE(l_attr_group_id_index);
PON_UDA_IMPORT_PKG.DEBUG('Before PON_UDA_IMPORT_PKG.UPDATE_STATUS to change to IN_PROGRESS from PENDING', d_progress);
PON_UDA_IMPORT_PKG.UPDATE_STATUS
(
p_process_status => PON_UDA_IMPORT_PKG.IN_PROGRESS
,p_pre_process_status => PON_UDA_IMPORT_PKG.PENDING
,p_process_phase => PON_UDA_IMPORT_PKG.PRE_DEFAULTING
,p_pk1_value => p_pk1_value
,p_pk2_value => p_pk2_value
,p_pk3_value => p_pk3_value
,p_pk4_value => p_pk4_value
,p_pk5_value => p_pk5_value
);
PON_UDA_IMPORT_PKG.DEBUG('After PON_UDA_IMPORT_PKG.UPDATE_STATUS to change to PENDING from IN_PROGRESS', d_progress);
PON_UDA_IMPORT_PKG.UPDATE_STATUS
(
p_process_status => PON_UDA_IMPORT_PKG.UDA_ERROR
,p_pre_process_status => PON_UDA_IMPORT_PKG.IN_PROGRESS
,p_process_phase => PON_UDA_IMPORT_PKG.PRE_DEFAULTING
,p_pk1_value => p_pk1_value
,p_pk2_value => p_pk2_value
,p_pk3_value => p_pk3_value
,p_pk4_value => p_pk4_value
,p_pk5_value => p_pk5_value
);
PON_UDA_IMPORT_PKG.UPDATE_STATUS
(
p_process_status => PON_UDA_IMPORT_PKG.UDA_ERROR
,p_pre_process_status => PON_UDA_IMPORT_PKG.IN_PROGRESS
,p_process_phase => PON_UDA_IMPORT_PKG.PRE_DEFAULTING
,p_pk1_value => p_pk1_value
,p_pk2_value => p_pk2_value
,p_pk3_value => p_pk3_value
,p_pk4_value => p_pk4_value
,p_pk5_value => p_pk5_value
);
PON_UDA_IMPORT_PKG.UPDATE_STATUS
(
p_process_status => PON_UDA_IMPORT_PKG.IN_PROGRESS
,p_pre_process_status => PON_UDA_IMPORT_PKG.IN_PROGRESS
,p_process_phase => PON_UDA_IMPORT_PKG.DEFAULTING
,p_pk1_value => p_pk1_value
,p_pk2_value => p_pk2_value
,p_pk3_value => p_pk3_value
,p_pk4_value => p_pk4_value
,p_pk5_value => p_pk5_value
);
PON_UDA_IMPORT_PKG.UPDATE_STATUS
(
p_process_status => PON_UDA_IMPORT_PKG.UDA_ERROR
,p_pre_process_status => PON_UDA_IMPORT_PKG.IN_PROGRESS
,p_process_phase => PON_UDA_IMPORT_PKG.PRE_DEFAULTING
,p_pk1_value => p_pk1_value
,p_pk2_value => p_pk2_value
,p_pk3_value => p_pk3_value
,p_pk4_value => p_pk4_value
,p_pk5_value => p_pk5_value
);
PON_UDA_IMPORT_PKG.UPDATE_STATUS
(
p_process_status => PON_UDA_IMPORT_PKG.UDA_ERROR
,p_pre_process_status => PON_UDA_IMPORT_PKG.IN_PROGRESS
,p_process_phase => PON_UDA_IMPORT_PKG.DEFAULTING
,p_pk1_value => p_pk1_value
,p_pk2_value => p_pk2_value
,p_pk3_value => p_pk3_value
,p_pk4_value => p_pk4_value
,p_pk5_value => p_pk5_value
);
PON_UDA_IMPORT_PKG.UPDATE_STATUS
(
p_process_status => PON_UDA_IMPORT_PKG.IN_PROGRESS
,p_pre_process_status => PON_UDA_IMPORT_PKG.IN_PROGRESS
,p_process_phase => PON_UDA_IMPORT_PKG.EGO_VALIDATE
,p_pk1_value => p_pk1_value
,p_pk2_value => p_pk2_value
,p_pk3_value => p_pk3_value
,p_pk4_value => p_pk4_value
,p_pk5_value => p_pk5_value
);
PON_UDA_IMPORT_PKG.UPDATE_STATUS
(
p_process_status => PON_UDA_IMPORT_PKG.UDA_ERROR
,p_pre_process_status => PON_UDA_IMPORT_PKG.IN_PROGRESS
,p_process_phase => PON_UDA_IMPORT_PKG.DEFAULTING
,p_pk1_value => p_pk1_value
,p_pk2_value => p_pk2_value
,p_pk3_value => p_pk3_value
,p_pk4_value => p_pk4_value
,p_pk5_value => p_pk5_value
);
PON_UDA_IMPORT_PKG.UPDATE_STATUS
(
p_process_status => PON_UDA_IMPORT_PKG.IN_PROGRESS
,p_pre_process_status => PON_UDA_IMPORT_PKG.IN_PROGRESS
,p_process_phase => PON_UDA_IMPORT_PKG.CUSTOM_VALIDATE
,p_pk1_value => p_pk1_value
,p_pk2_value => p_pk2_value
,p_pk3_value => p_pk3_value
,p_pk4_value => p_pk4_value
,p_pk5_value => p_pk5_value
);
PON_UDA_IMPORT_PKG.UPDATE_STATUS
(
p_process_status => PON_UDA_IMPORT_PKG.UDA_ERROR
,p_pre_process_status => PON_UDA_IMPORT_PKG.IN_PROGRESS
,p_process_phase => PON_UDA_IMPORT_PKG.EGO_VALIDATE
,p_pk1_value => p_pk1_value
,p_pk2_value => p_pk2_value
,p_pk3_value => p_pk3_value
,p_pk4_value => p_pk4_value
,p_pk5_value => p_pk5_value
);
PON_UDA_IMPORT_PKG.UPDATE_STATUS
(
p_process_status => PON_UDA_IMPORT_PKG.UDA_ERROR
,p_pre_process_status => PON_UDA_IMPORT_PKG.IN_PROGRESS
,p_process_phase => PON_UDA_IMPORT_PKG.LOAD
,p_pk1_value => p_pk1_value
,p_pk2_value => p_pk2_value
,p_pk3_value => p_pk3_value
,p_pk4_value => p_pk4_value
,p_pk5_value => p_pk5_value
);
PON_UDA_IMPORT_PKG.UPDATE_STATUS
(
p_process_status => PON_UDA_IMPORT_PKG.IN_PROGRESS
,p_pre_process_status => PON_UDA_IMPORT_PKG.IN_PROGRESS
,p_process_phase => PON_UDA_IMPORT_PKG.LOAD
,p_pk1_value => p_pk1_value
,p_pk2_value => p_pk2_value
,p_pk3_value => p_pk3_value
,p_pk4_value => p_pk4_value
,p_pk5_value => p_pk5_value
);
PON_UDA_IMPORT_PKG.UPDATE_STATUS
(
p_process_status => PON_UDA_IMPORT_PKG.UDA_ERROR
,p_pre_process_status => PON_UDA_IMPORT_PKG.IN_PROGRESS
,p_process_phase => PON_UDA_IMPORT_PKG.CUSTOM_VALIDATE
,p_pk1_value => p_pk1_value
,p_pk2_value => p_pk2_value
,p_pk3_value => p_pk3_value
,p_pk4_value => p_pk4_value
,p_pk5_value => p_pk5_value
);
PON_UDA_IMPORT_PKG.UPDATE_STATUS
(
p_process_status => PON_UDA_IMPORT_PKG.UDA_ERROR
,p_pre_process_status => PON_UDA_IMPORT_PKG.IN_PROGRESS
,p_process_phase => PON_UDA_IMPORT_PKG.LOAD
,p_pk1_value => p_pk1_value
,p_pk2_value => p_pk2_value
,p_pk3_value => p_pk3_value
,p_pk4_value => p_pk4_value
,p_pk5_value => p_pk5_value
);
PON_UDA_IMPORT_PKG.UPDATE_STATUS
(
p_process_status => PON_UDA_IMPORT_PKG.COMPLETED
,p_pre_process_status => PON_UDA_IMPORT_PKG.IN_PROGRESS
,p_process_phase => PON_UDA_IMPORT_PKG.LOAD
,p_pk1_value => p_pk1_value
,p_pk2_value => p_pk2_value
,p_pk3_value => p_pk3_value
,p_pk4_value => p_pk4_value
,p_pk5_value => p_pk5_value
);
UPDATE PO_UDA_INTERFACE
SET PK1_VALUE = NULL, PK2_VALUE=NULL, PROCESS_STATUS = 1, PROCESS_PHASE = NULL
WHERE PK1_VALUE = p_pk1_value;
PO_INTERFACE_ERRORS_SV1.insert_po_interface_errors_msg
(
X_interface_type => g_attr_grp_type,
X_Interface_Header_ID => g_interface_reference_id,
X_Interface_Line_Id => NULL,
X_Interface_Dist_Id => NULL,
X_error_message_text => p_msg,
X_error_message_name => p_msg_code,
X_column_name => NULL,
X_table_name => NULL,
X_batch_id => p_attr_grp_id
);
PROCEDURE UPDATE_STATUS
(
p_process_status IN NUMBER
,p_pre_process_status IN NUMBER
,p_process_phase IN VARCHAR2
,p_pk1_value IN NUMBER
,p_pk2_value IN NUMBER
,p_pk3_value IN NUMBER
,p_pk4_value IN NUMBER
,p_pk5_value IN NUMBER
)
IS
BEGIN
IF p_process_status IN (PON_UDA_IMPORT_PKG.PENDING, PON_UDA_IMPORT_PKG.IN_PROGRESS, PON_UDA_IMPORT_PKG.COMPLETED) THEN
UPDATE PO_UDA_INTERFACE
SET PROCESS_STATUS = p_process_status
,PROCESS_PHASE = p_process_phase
WHERE NVL(PK1_VALUE, -999) = NVL(p_pk1_value, -999)
AND NVL(PK2_VALUE, -999) = NVL(p_pk2_value, -999)
AND NVL(PK3_VALUE, -999) = NVL(p_pk3_value, -999)
AND NVL(PK4_VALUE, -999) = NVL(p_pk4_value, -999)
AND NVL(PK5_VALUE, -999) = NVL(p_pk5_value, -999)
AND PROCESS_STATUS = p_pre_process_status;
select o.pk1_column_name,
o.pk2_column_name,
o.pk3_column_name,
o.pk4_column_name,
o.pk5_column_name
into l_pk1_column_name,
l_pk2_column_name,
l_pk3_column_name,
l_pk4_column_name,
l_pk5_column_name
from fnd_objects_vl o
where o.obj_name = p_obj_name;