[Home] [Help]
270: TMP NUMBER;
271:
272: begin
273:
274: SELECT 1 INTO TMP FROM HZ_TRANS_FUNCTIONS_VL
275: WHERE function_id = X_FUNCTION_ID;
276:
277: SELECT nvl(ACTIVE_FLAG,'Y'), PROCEDURE_NAME,
278: nvl(PRIMARY_FLAG,'N'), last_updated_by, staged_flag,
279: transformation_name, description,
280: nvl(INDEX_REQUIRED_FLAG, 'N'), STAGED_ATTRIBUTE_COLUMN
281: into l_db_act_flag, l_db_proc_name, l_db_primary_flag, l_db_upd_by, l_db_stg_flag,
282: l_db_trans_name, l_db_desc, l_db_index_req_flag, l_db_stg_atr_col
283: from HZ_TRANS_FUNCTIONS_VL
284: where function_id =X_FUNCTION_ID;
285: l_object_version_number := NVL(X_object_version_number, 1) + 1;
286:
287: IF (X_LAST_UPDATED_BY = 1 AND l_db_upd_by <> 1) THEN
527: FUNCTION get_valid_tx_column(x_staged_attribute_table VARCHAR2,
528: x_staged_attribute_column VARCHAR2)
529: return varchar2
530: IS
531: CURSOR c_stg_attr_col IS select substr(staged_attribute_column,3)-1 from hz_trans_functions_vl vl1
532: where staged_attribute_table = x_staged_attribute_table
533: and substr(staged_attribute_column,3) > 2
534: and NOT EXISTS(
535: select 'Y'from hz_trans_functions_vl vl2
531: CURSOR c_stg_attr_col IS select substr(staged_attribute_column,3)-1 from hz_trans_functions_vl vl1
532: where staged_attribute_table = x_staged_attribute_table
533: and substr(staged_attribute_column,3) > 2
534: and NOT EXISTS(
535: select 'Y'from hz_trans_functions_vl vl2
536: where vl2.staged_attribute_table=vl1.staged_attribute_table
537: and substr(vl2.staged_attribute_column,3) = substr(vl1.staged_attribute_column,3)-1
538: )
539: and rownum=1 ;
536: where vl2.staged_attribute_table=vl1.staged_attribute_table
537: and substr(vl2.staged_attribute_column,3) = substr(vl1.staged_attribute_column,3)-1
538: )
539: and rownum=1 ;
540: CURSOR c_max_stg_col IS select max(to_number(substr(staged_attribute_column,3)))+1 from hz_trans_functions_vl
541: where staged_attribute_table = X_STAGED_ATTRIBUTE_TABLE;
542: l_staged_attribute_column VARCHAR2(255);
543: l_prefix VARCHAR2(2);
544: BEGIN