[Home] [Help]
39811: ex_logging_err EXCEPTION;
39812: lv_return NUMBER;
39813: lv_error_text VARCHAR2(250);
39814: lv_sql_stmt VARCHAR2(5000);
39815: lv_batch_id msc_st_company_users.batch_id%TYPE;
39816: lv_message_text msc_errors.error_text%TYPE;
39817:
39818: TYPE RowidTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
39819: lb_rowid RowidTab;
39819: lb_rowid RowidTab;
39820:
39821: CURSOR c1(p_batch_id NUMBER) IS
39822: SELECT mscu.rowid
39823: FROM MSC_ST_COMPANY_USERS mscu
39824: WHERE mscu.process_flag IN (G_IN_PROCESS,G_ERROR_FLG)
39825: AND mscu.sr_instance_code = v_instance_code
39826: AND batch_id = p_batch_id;
39827:
39835: FROM DUAL;
39836:
39837: v_sql_stmt := 02;
39838: lv_sql_stmt :=
39839: ' UPDATE MSC_ST_COMPANY_USERS '
39840: ||' SET batch_id = :lv_batch_id'
39841: ||' WHERE process_flag IN ('||G_IN_PROCESS||','||G_ERROR_FLG||')'
39842: ||' AND sr_instance_code = :v_instance_code'
39843: ||' AND NVL(batch_id,'||NULL_VALUE||') = '||NULL_VALUE
39858:
39859: v_sql_stmt := 03;
39860:
39861: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
39862: UPDATE MSC_ST_COMPANY_USERS
39863: SET st_transaction_id = msc_st_company_users_s.NEXTVAL,
39864: refresh_id = v_refresh_id,
39865: last_update_date = v_current_date,
39866: last_update_login = v_current_user,
39859: v_sql_stmt := 03;
39860:
39861: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
39862: UPDATE MSC_ST_COMPANY_USERS
39863: SET st_transaction_id = msc_st_company_users_s.NEXTVAL,
39864: refresh_id = v_refresh_id,
39865: last_update_date = v_current_date,
39866: last_update_login = v_current_user,
39867: creation_date = v_current_date,
39881: END IF;
39882:
39883:
39884: lv_return := MSC_ST_UTIL.DERIVE_COMPANY_ID
39885: (p_table_name => 'MSC_ST_COMPANY_USERS',
39886: p_company_name => 'COMPANY_NAME',
39887: p_company_id => 'SR_COMPANY_ID',
39888: p_instance_code => v_instance_code,
39889: p_error_text => lv_error_text,
39897:
39898: /* Calling derive_company_id
39899: v_sql_stmt := 04;
39900:
39901: UPDATE MSC_ST_COMPANY_USERS mscu
39902: SET SR_COMPANY_ID = (SELECT local_id from MSC_LOCAL_ID_SETUP
39903: WHERE char1 = mscu.sr_instance_code
39904: and NVL(char3,NULL_CHAR) = NVL(mscu.company_name,NULL_CHAR)
39905: and entity_name = 'SR_TP_ID'
39921: END IF;
39922:
39923: v_sql_stmt := 05;
39924:
39925: UPDATE msc_st_company_users
39926: SET process_flag = G_ERROR_FLG,
39927: error_text = lv_message_text
39928: WHERE NVL(sr_company_id,NULL_VALUE) = NULL_VALUE
39929: AND NVL(company_name,'-1') <> '-1'
39945:
39946: v_sql_stmt := 05.1;
39947:
39948:
39949: UPDATE msc_st_company_users mscu
39950: SET mscu.process_flag = G_ERROR_FLG,
39951: mscu.error_text = lv_message_text
39952: WHERE ( mscu.user_name IS NULL OR NOT EXISTS(select '1' from fnd_user fu where fu.user_name =UPPER(mscu.user_name)))
39953: AND mscu.process_flag = G_IN_PROCESS
39955: AND mscu.sr_instance_code = v_instance_code;
39956:
39957: v_sql_stmt := 05;
39958:
39959: UPDATE MSC_ST_COMPANY_USERS mscu
39960: SET PARTNER_TYPE = (SELECT number1 from MSC_LOCAL_ID_SETUP
39961: WHERE char1 = mscu.sr_instance_code
39962: and NVL(char3,NULL_CHAR) = NVL(mscu.company_name,NULL_CHAR)
39963: and entity_name = 'SR_TP_ID'
39974: (ERRBUF => lv_error_text,
39975: RETCODE => lv_return,
39976: pBatchID => lv_batch_id,
39977: pInstanceCode => v_instance_code,
39978: pEntityName => 'MSC_ST_COMPANY_USERS',
39979: pInstanceID => v_instance_id);
39980:
39981: IF NVL(lv_return,0) <> 0 THEN
39982: RAISE ex_logging_err;
39983: END IF;
39984:
39985: -- Set the process flag as Valid and poulate instance_id
39986: lv_return :=
39987: MSC_ST_UTIL.SET_PROCESS_FLAG(p_table_name => 'MSC_ST_COMPANY_USERS',
39988: p_instance_id => v_instance_id,
39989: p_instance_code => v_instance_code,
39990: p_process_flag => G_VALID,
39991: p_error_text => lv_error_text,
56020: End IF;
56021: END IF;
56022:
56023: IF v_comp_users_enabled = SYS_YES THEN
56024: lv_count:= SET_IN_PROCESS (p_table_name => 'MSC_ST_COMPANY_USERS');
56025: IF lv_count > 0 Then
56026: prec.user_company_flag:= G_COMP_USR_YES;
56027: End IF;
56028: END IF;