eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.MSC_CL_PRE_PROCESS dependencies on MSC_ST_VISITS

Line 12590: lv_batch_id msc_st_visits.batch_id%TYPE;

12586: lv_error_text VARCHAR2(250);
12587: lv_where_str VARCHAR2(5000);
12588: lv_sql_stmt VARCHAR2(5000);
12589: lv_column_names VARCHAR2(5000); --stores concatenated column names
12590: lv_batch_id msc_st_visits.batch_id%TYPE;
12591: lv_message_text msc_errors.error_text%TYPE;
12592:
12593: ex_logging_err EXCEPTION;
12594:

Line 12597: FROM msc_st_visits

12593: ex_logging_err EXCEPTION;
12594:
12595: CURSOR c1(p_batch_id NUMBER) IS
12596: SELECT rowid
12597: FROM msc_st_visits
12598: WHERE process_flag IN (G_IN_PROCESS,G_ERROR_FLG)
12599: AND batch_id = p_batch_id
12600: AND sr_instance_code = v_instance_code;
12601:

Line 12604: FROM msc_st_visits

12600: AND sr_instance_code = v_instance_code;
12601:
12602: CURSOR C2(p_batch_id NUMBER) IS
12603: SELECT max(rowid)
12604: FROM msc_st_visits
12605: WHERE process_flag = G_IN_PROCESS
12606: AND sr_instance_code = v_instance_code
12607: AND batch_id = p_batch_id
12608: AND NVL(visit_id,NULL_VALUE) = NULL_VALUE

Line 12623: --Validation check for the table msc_st_visits

12619: IF lv_return <> 0 THEN
12620: RAISE ex_logging_err;
12621: END IF;
12622:
12623: --Validation check for the table msc_st_visits
12624:
12625: --Duplicate records check for the records whose source is other than XML
12626: --Different SQL is used because in XML we can identify the latest records
12627: --whereas in batch load we cannot.

Line 12631: 'UPDATE msc_st_visits mssc1 '

12627: --whereas in batch load we cannot.
12628:
12629: v_sql_stmt := 01;
12630: lv_sql_stmt :=
12631: 'UPDATE msc_st_visits mssc1 '
12632: ||' SET process_flag = '||G_ERROR_FLG||','
12633: ||' error_text = '||''''||lv_message_text||''''
12634: ||' WHERE EXISTS ( SELECT 1 FROM msc_st_visits mssc2 '
12635: ||' WHERE mssc2.sr_instance_code = mssc1.sr_instance_code'

Line 12634: ||' WHERE EXISTS ( SELECT 1 FROM msc_st_visits mssc2 '

12630: lv_sql_stmt :=
12631: 'UPDATE msc_st_visits mssc1 '
12632: ||' SET process_flag = '||G_ERROR_FLG||','
12633: ||' error_text = '||''''||lv_message_text||''''
12634: ||' WHERE EXISTS ( SELECT 1 FROM msc_st_visits mssc2 '
12635: ||' WHERE mssc2.sr_instance_code = mssc1.sr_instance_code'
12636: ||' AND mssc2.visit_name = mssc1.visit_name'
12637: ||' AND mssc2.process_flag = mssc1.process_flag'
12638: ||' AND NVL(mssc2.message_id,NULL) IS NULL'

Line 12657: 'UPDATE msc_st_visits mssc1 '

12653:
12654: --Duplicate records check for the records whose source is XML
12655: v_sql_stmt := 02;
12656: lv_sql_stmt :=
12657: 'UPDATE msc_st_visits mssc1 '
12658: ||' SET process_flag = '||G_ERROR_FLG||','
12659: ||' error_text = '||''''||lv_message_text||''''
12660: ||' WHERE message_id < ( SELECT max(message_id) '
12661: ||' FROM msc_st_visits mssc2'

Line 12661: ||' FROM msc_st_visits mssc2'

12657: 'UPDATE msc_st_visits mssc1 '
12658: ||' SET process_flag = '||G_ERROR_FLG||','
12659: ||' error_text = '||''''||lv_message_text||''''
12660: ||' WHERE message_id < ( SELECT max(message_id) '
12661: ||' FROM msc_st_visits mssc2'
12662: ||' WHERE mssc2.sr_instance_code = mssc1.sr_instance_code'
12663: ||' AND mssc2.visit_name = mssc1.visit_name'
12664: ||' AND mssc2.process_flag = mssc1.process_flag'
12665: ||' AND NVL(mssc2.message_id,NULL) IS NOT NULL) '

Line 12695: 'UPDATE msc_st_visits '

12691: FROM dual;
12692:
12693: v_sql_stmt := 04;
12694: lv_sql_stmt :=
12695: 'UPDATE msc_st_visits '
12696: ||' SET batch_id = :lv_batch_id'
12697: ||' WHERE process_flag IN ('||G_IN_PROCESS||','||G_ERROR_FLG||')'
12698: ||' AND sr_instance_code = :v_instance_code'
12699: ||' AND NVL(batch_id,'||NULL_VALUE||') = '||NULL_VALUE

Line 12716: UPDATE msc_st_visits

12712: FETCH c1 BULK COLLECT INTO lb_rowid;
12713: CLOSE c1;
12714:
12715: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
12716: UPDATE msc_st_visits
12717: SET st_transaction_id = msc_st_visits_s.nextval,
12718: refresh_id = v_refresh_id,
12719: last_update_date = v_current_date,
12720: last_updated_by = v_current_user,

Line 12717: SET st_transaction_id = msc_st_visits_s.nextval,

12713: CLOSE c1;
12714:
12715: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
12716: UPDATE msc_st_visits
12717: SET st_transaction_id = msc_st_visits_s.nextval,
12718: refresh_id = v_refresh_id,
12719: last_update_date = v_current_date,
12720: last_updated_by = v_current_user,
12721: creation_date = v_current_date,

Line 12745: (p_table_name => 'MSC_ST_VISITS',

12741: ||' NOT IN(1,2)';
12742: --Log a warning for those records where the deleted_flag has a value other
12743: --than SYS_NO
12744: lv_return := MSC_ST_UTIL.LOG_ERROR
12745: (p_table_name => 'MSC_ST_VISITS',
12746: p_instance_code => v_instance_code,
12747: p_row => lv_column_names,
12748: p_severity => G_SEV_WARNING,
12749: p_message_text => lv_message_text,

Line 12779: 'UPDATE msc_st_visits'

12775: -- Error out records where visit_name, visit start/end date is NULL
12776:
12777: v_sql_stmt := 06;
12778: lv_sql_stmt :=
12779: 'UPDATE msc_st_visits'
12780: ||' SET process_flag = '||G_ERROR_FLG||','
12781: ||' error_text = '||''''||lv_message_text||''''
12782: ||' WHERE ( VISIT_NAME IS NULL'
12783: ||' OR VISIT_START_DATE IS NULL'

Line 12811: 'UPDATE msc_st_visits'

12807: -- Validate that start_date is greater than end_date
12808:
12809: v_sql_stmt := 07;
12810: lv_sql_stmt :=
12811: 'UPDATE msc_st_visits'
12812: ||' SET process_flag = '||G_ERROR_FLG||','
12813: ||' error_text = '||''''||lv_message_text||''''
12814: ||' WHERE (VISIT_START_DATE >= VISIT_END_DATE)'
12815: ||' AND process_flag = '||G_IN_PROCESS

Line 12842: MSC_ST_UTIL.DERIVE_PARTNER_ORG_ID(p_table_name => 'MSC_ST_VISITS',

12838:
12839: -- Populate organization id
12840:
12841: lv_return :=
12842: MSC_ST_UTIL.DERIVE_PARTNER_ORG_ID(p_table_name => 'MSC_ST_VISITS',
12843: p_org_partner_name => 'ORGANIZATION_CODE',
12844: p_org_partner_id => 'ORGANIZATION_ID',
12845: p_instance_code => v_instance_code,
12846: p_partner_type => G_ORGANIZATION,

Line 12861: 'UPDATE msc_st_visits msv'

12857:
12858: --Deriving visit_id
12859: v_sql_stmt := 08;
12860: lv_sql_stmt :=
12861: 'UPDATE msc_st_visits msv'
12862: ||' SET visit_id = (SELECT local_id'
12863: ||' FROM msc_local_id_setup mls'
12864: ||' WHERE mls.char1 = msv.sr_instance_code'
12865: ||' AND mls.char4 = msv.visit_name'

Line 12889: UPDATE msc_st_visits

12885:
12886: IF c2%ROWCOUNT > 0 THEN
12887: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
12888:
12889: UPDATE msc_st_visits
12890: SET visit_id = msc_st_visit_id_s.NEXTVAL
12891: WHERE rowid = lb_rowid(j);
12892:
12893: v_sql_stmt := 10;

Line 12923: FROM msc_st_visits

12919: v_current_date,
12920: v_current_user,
12921: v_current_date,
12922: v_current_user
12923: FROM msc_st_visits
12924: WHERE rowid = lb_rowid(j);
12925:
12926: END IF;
12927: CLOSE c2;

Line 12932: 'UPDATE MSC_ST_VISITS msv'

12928: -- Udpate visit_id
12929: v_sql_stmt := 11;
12930:
12931: lv_sql_stmt:=
12932: 'UPDATE MSC_ST_VISITS msv'
12933: ||' SET visit_id = (SELECT local_id '
12934: ||' FROM msc_local_id_setup mlis'
12935: ||' WHERE mlis.entity_name= ''VISIT'' '
12936: ||' AND mlis.char1 = msv.sr_instance_code'

Line 12955: pEntityName => 'MSC_ST_VISITS',

12951: (ERRBUF => lv_error_text,
12952: RETCODE => lv_return,
12953: pBatchID => NULL,
12954: pInstanceCode => v_instance_code,
12955: pEntityName => 'MSC_ST_VISITS',
12956: pInstanceID => v_instance_id);
12957:
12958: IF NVL(lv_return,0) <> 0 THEN
12959: RAISE ex_logging_err;

Line 12963: (p_table_name => 'MSC_ST_VISITS',

12959: RAISE ex_logging_err;
12960: END IF;
12961: -- Set the process flag as Valid and populate instance_id
12962: lv_return := MSC_ST_UTIL.SET_PROCESS_FLAG
12963: (p_table_name => 'MSC_ST_VISITS',
12964: p_instance_id => v_instance_id,
12965: p_instance_code => v_instance_code,
12966: p_process_flag => G_VALID,
12967: p_error_text => lv_error_text,

Line 12978: (p_table_name => 'MSC_ST_VISITS',

12974: -- At the end calling the LOG_ERROR for logging all
12975: -- errored out records.
12976:
12977: lv_return := MSC_ST_UTIL.LOG_ERROR
12978: (p_table_name => 'MSC_ST_VISITS',
12979: p_instance_code => v_instance_code,
12980: p_row => lv_column_names,
12981: p_severity => G_SEV_ERROR,
12982: p_message_text => NULL,

Line 56074: lv_count:= SET_IN_PROCESS (p_table_name => 'MSC_ST_VISITS', p_company_name_col => FALSE, p_erp_enabled => 'Y');

56070: End IF;
56071: END IF;
56072:
56073: IF v_CMRO_enabled = SYS_YES THEN
56074: lv_count:= SET_IN_PROCESS (p_table_name => 'MSC_ST_VISITS', p_company_name_col => FALSE, p_erp_enabled => 'Y');
56075: IF lv_count > 0 Then
56076: prec.CMRO_flag:= SYS_YES;
56077: End IF;
56078: