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.

[Home] [Help]

APPS.MSC_CL_PRE_PROCESS dependencies on MSC_ST_TRADING_PARTNER_SITES

Line 1100: ||' on MSC_ST_TRADING_PARTNER_SITES '

1096: application_short_name => 'MSC',
1097: statement_type => AD_DDL.CREATE_INDEX,
1098: statement =>
1099: 'create index MSC_ST_TRD_PART_SITES_N1_'||v_instance_code
1100: ||' on MSC_ST_TRADING_PARTNER_SITES '
1101: ||'(sr_instance_code ,tp_site_code, partner_name, partner_type) '
1102: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
1103: object_name =>'MSC_ST_TRD_PART_SITES_N1_'||v_instance_code);
1104:

Line 1209: msc_analyse_tables_pk.analyse_table( 'MSC_ST_TRADING_PARTNER_SITES', v_instance_id, -1);

1205:
1206:
1207:
1208: msc_analyse_tables_pk.analyse_table( 'MSC_ST_TRADING_PARTNERS', v_instance_id, -1);
1209: msc_analyse_tables_pk.analyse_table( 'MSC_ST_TRADING_PARTNER_SITES', v_instance_id, -1);
1210: msc_analyse_tables_pk.analyse_table( 'MSC_ST_SUB_INVENTORIES', v_instance_id, -1);
1211: msc_analyse_tables_pk.analyse_table( 'MSC_ST_LOCATION_ASSOCIATIONS', v_instance_id, -1);
1212: msc_analyse_tables_pk.analyse_table( 'MSC_ST_PARTNER_CONTACTS', v_instance_id, -1);
1213:

Line 6277: FROM msc_st_trading_partner_sites

6273: AND deleted_flag = SYS_NO;
6274:
6275: CURSOR c3(p_batch_id NUMBER) IS
6276: SELECT rowid
6277: FROM msc_st_trading_partner_sites
6278: WHERE process_flag IN (G_IN_PROCESS,G_ERROR_FLG)
6279: AND sr_instance_code = v_instance_code
6280: AND batch_id = p_batch_id;
6281:

Line 6284: FROM msc_st_trading_partner_sites

6280: AND batch_id = p_batch_id;
6281:
6282: CURSOR c4(p_batch_id NUMBER) IS
6283: SELECT rowid
6284: FROM msc_st_trading_partner_sites
6285: WHERE process_flag = G_IN_PROCESS
6286: AND sr_instance_code = v_instance_code
6287: AND batch_id = p_batch_id
6288: AND NVL(sr_tp_site_id,NULL_VALUE) = NULL_VALUE

Line 6308: FROM msc_st_trading_partner_sites

6304: AND batch_id = p_batch_id;
6305:
6306: CURSOR c7(p_batch_id NUMBER) IS
6307: SELECT max(rowid)
6308: FROM msc_st_trading_partner_sites
6309: WHERE process_flag = G_IN_PROCESS
6310: AND deleted_flag = SYS_NO
6311: AND NVL(sr_tp_site_id,NULL_VALUE) = NULL_VALUE
6312: AND partner_type = 3

Line 7609: --Validation for MSC_ST_TRADING_PARTNER_SITES

7605: END IF ;
7606: COMMIT;
7607: END LOOP;
7608:
7609: --Validation for MSC_ST_TRADING_PARTNER_SITES
7610:
7611: lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
7612: (p_app_short_name => 'MSC',
7613: p_error_code => 'MSC_PP_DUP_REC_FOR_BATCH_LOAD',

Line 7624: ' UPDATE msc_st_trading_partner_sites mstp1'

7620: /*
7621: -- UDK validations
7622: v_sql_stmt := 19;
7623: lv_sql_stmt :=
7624: ' UPDATE msc_st_trading_partner_sites mstp1'
7625: ||' SET process_flag = '||G_ERROR_FLG||', '
7626: ||' error_text = '||''''||lv_message_text||''''
7627: ||' WHERE EXISTS ( SELECT 1 '
7628: ||' FROM msc_st_trading_partner_sites mstp2 '

Line 7628: ||' FROM msc_st_trading_partner_sites mstp2 '

7624: ' UPDATE msc_st_trading_partner_sites mstp1'
7625: ||' SET process_flag = '||G_ERROR_FLG||', '
7626: ||' error_text = '||''''||lv_message_text||''''
7627: ||' WHERE EXISTS ( SELECT 1 '
7628: ||' FROM msc_st_trading_partner_sites mstp2 '
7629: ||' WHERE mstp2.sr_instance_code = mstp1.sr_instance_code '
7630: ||' AND NVL(mstp2.company_name, '||''''||NULL_CHAR||''''||') = '
7631: ||' NVL(mstp1.company_name, '||''''||NULL_CHAR||''''||')'
7632: ||' AND mstp2.partner_name = mstp1.partner_name '

Line 7655: ' UPDATE msc_st_trading_partner_sites mstp1'

7651:
7652: -- UDK validations for locations
7653: v_sql_stmt := 19;
7654: lv_sql_stmt :=
7655: ' UPDATE msc_st_trading_partner_sites mstp1'
7656: ||' SET process_flag = '||G_ERROR_FLG||', '
7657: ||' error_text = '||''''||lv_message_text||''''
7658: ||' WHERE EXISTS ( SELECT 1 '
7659: ||' FROM msc_st_trading_partner_sites mstp2 '

Line 7659: ||' FROM msc_st_trading_partner_sites mstp2 '

7655: ' UPDATE msc_st_trading_partner_sites mstp1'
7656: ||' SET process_flag = '||G_ERROR_FLG||', '
7657: ||' error_text = '||''''||lv_message_text||''''
7658: ||' WHERE EXISTS ( SELECT 1 '
7659: ||' FROM msc_st_trading_partner_sites mstp2 '
7660: ||' WHERE mstp2.sr_instance_code = mstp1.sr_instance_code '
7661: ||' AND NVL(mstp2.company_name, '||''''||NULL_CHAR||''''||') = '
7662: ||' NVL(mstp1.company_name, '||''''||NULL_CHAR||''''||')'
7663: ||' AND mstp2.partner_name = mstp1.partner_name '

Line 7686: ' UPDATE msc_st_trading_partner_sites mstp1'

7682:
7683: -- UDK validations for tp sites
7684: v_sql_stmt := 19;
7685: lv_sql_stmt :=
7686: ' UPDATE msc_st_trading_partner_sites mstp1'
7687: ||' SET process_flag = '||G_ERROR_FLG||', '
7688: ||' error_text = '||''''||lv_message_text||''''
7689: ||' WHERE EXISTS ( SELECT 1 '
7690: ||' FROM msc_st_trading_partner_sites mstp2 '

Line 7690: ||' FROM msc_st_trading_partner_sites mstp2 '

7686: ' UPDATE msc_st_trading_partner_sites mstp1'
7687: ||' SET process_flag = '||G_ERROR_FLG||', '
7688: ||' error_text = '||''''||lv_message_text||''''
7689: ||' WHERE EXISTS ( SELECT 1 '
7690: ||' FROM msc_st_trading_partner_sites mstp2 '
7691: ||' WHERE mstp2.sr_instance_code = mstp1.sr_instance_code '
7692: ||' AND NVL(mstp2.company_name, '||''''||NULL_CHAR||''''||') = '
7693: ||' NVL(mstp1.company_name, '||''''||NULL_CHAR||''''||')'
7694: ||' AND mstp2.partner_name = mstp1.partner_name '

Line 7726: ' UPDATE msc_st_trading_partner_sites mstp1'

7722:
7723: -- Duplicate UDK validation for XML message
7724: v_sql_stmt := 20;
7725: lv_sql_stmt :=
7726: ' UPDATE msc_st_trading_partner_sites mstp1'
7727: ||' SET process_flag = '||G_ERROR_FLG||', '
7728: ||' error_text = '||''''||lv_message_text||''''
7729: ||' WHERE message_id < ( SELECT max(message_id) '
7730: ||' FROM msc_st_trading_partner_sites mstp2 '

Line 7730: ||' FROM msc_st_trading_partner_sites mstp2 '

7726: ' UPDATE msc_st_trading_partner_sites mstp1'
7727: ||' SET process_flag = '||G_ERROR_FLG||', '
7728: ||' error_text = '||''''||lv_message_text||''''
7729: ||' WHERE message_id < ( SELECT max(message_id) '
7730: ||' FROM msc_st_trading_partner_sites mstp2 '
7731: ||' WHERE mstp2.sr_instance_code = mstp1.sr_instance_code '
7732: ||' AND NVL(mstp2.company_name, '||''''||NULL_CHAR||''''||') = '
7733: ||' NVL(mstp1.company_name, '||''''||NULL_CHAR||''''||')'
7734: ||' AND mstp2.partner_name = mstp1.partner_name '

Line 7773: 'UPDATE msc_st_trading_partner_sites '

7769: FROM DUAL;
7770:
7771: v_sql_stmt := 22;
7772: lv_sql_stmt :=
7773: 'UPDATE msc_st_trading_partner_sites '
7774: ||' SET batch_id = :lv_batch_id'
7775: ||' WHERE process_flag IN ('||G_IN_PROCESS||','||G_ERROR_FLG||')'
7776: ||' AND sr_instance_code = :v_instance_code'
7777: ||' AND NVL(batch_id,'||NULL_VALUE||') = '||NULL_VALUE

Line 7797: UPDATE msc_st_trading_partner_sites

7793:
7794: -- Populate the st_transaction_id and WHO columns
7795: v_sql_stmt := 23;
7796: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
7797: UPDATE msc_st_trading_partner_sites
7798: SET st_transaction_id = msc_st_trading_partner_sites_s.NEXTVAL ,
7799: refresh_id = v_refresh_id,
7800: last_update_date = v_current_date,
7801: last_updated_by = v_current_user,

Line 7798: SET st_transaction_id = msc_st_trading_partner_sites_s.NEXTVAL ,

7794: -- Populate the st_transaction_id and WHO columns
7795: v_sql_stmt := 23;
7796: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
7797: UPDATE msc_st_trading_partner_sites
7798: SET st_transaction_id = msc_st_trading_partner_sites_s.NEXTVAL ,
7799: refresh_id = v_refresh_id,
7800: last_update_date = v_current_date,
7801: last_updated_by = v_current_user,
7802: creation_date = v_current_date,

Line 7812: p_token_value1 => 'MSC_ST_TRADING_PARTNER_SITES');

7808: p_error_code => 'MSC_PP_NO_DELETION',
7809: p_message_text => lv_message_text,
7810: p_error_text => lv_error_text,
7811: p_token1 => 'TABLE_NAME',
7812: p_token_value1 => 'MSC_ST_TRADING_PARTNER_SITES');
7813:
7814: IF lv_return <> 0 THEN
7815: RAISE ex_logging_err;
7816: END IF;

Line 7821: 'UPDATE msc_st_trading_partner_sites '

7817:
7818: -- Deletion is not allowed for this table
7819: v_sql_stmt := 24;
7820: lv_sql_stmt :=
7821: 'UPDATE msc_st_trading_partner_sites '
7822: ||' SET process_flag = '||G_ERROR_FLG||','
7823: ||' error_text = '||''''||lv_message_text||''''
7824: ||' WHERE deleted_flag = '||SYS_YES
7825: ||' AND process_flag = '||G_IN_PROCESS

Line 7859: (p_table_name => 'MSC_ST_TRADING_PARTNER_SITES',

7855: lv_where_str := ' AND NVL(deleted_flag,'||NULL_VALUE||') '
7856: ||' NOT IN(1,2)';
7857:
7858: lv_return := MSC_ST_UTIL.LOG_ERROR
7859: (p_table_name => 'MSC_ST_TRADING_PARTNER_SITES',
7860: p_instance_code => v_instance_code,
7861: p_row => lv_column_names,
7862: p_severity => G_SEV_WARNING,
7863: p_message_text => lv_message_text,

Line 7891: 'UPDATE msc_st_trading_partner_sites '

7887:
7888: -- Error out records where partner_type NOT in (1,2,3)
7889: v_sql_stmt := 25;
7890: lv_sql_stmt :=
7891: 'UPDATE msc_st_trading_partner_sites '
7892: ||' SET process_flag = '||G_ERROR_FLG||', '
7893: ||' error_text = '||''''||lv_message_text||''''
7894: ||' WHERE sr_instance_code = :v_instance_code'
7895: ||' AND batch_id = :lv_batch_id'

Line 7926: 'UPDATE msc_st_trading_partner_sites '

7922:
7923: -- Error out records where partner_type NOT in (2)
7924: v_sql_stmt := 25;
7925: lv_sql_stmt :=
7926: 'UPDATE msc_st_trading_partner_sites '
7927: ||' SET process_flag = '||G_ERROR_FLG||', '
7928: ||' error_text = '||''''||lv_message_text||''''
7929: ||' WHERE sr_instance_code = :v_instance_code'
7930: ||' AND batch_id = :lv_batch_id'

Line 7961: 'UPDATE msc_st_trading_partner_sites '

7957: -- Error out records where partner_name/tp_site_code is NULL
7958: -- For partner type 1 and 2
7959: v_sql_stmt := 26;
7960: lv_sql_stmt :=
7961: 'UPDATE msc_st_trading_partner_sites '
7962: ||' SET process_flag = '||G_ERROR_FLG||','
7963: ||' error_text = '||''''||lv_message_text||''''
7964: ||' WHERE sr_instance_code = :v_instance_code'
7965: ||' AND batch_id = :lv_batch_id'

Line 7998: 'UPDATE msc_st_trading_partner_sites '

7994: -- Error out records where partner_name/location is NULL
7995: -- For partner type 3
7996: v_sql_stmt := 27;
7997: lv_sql_stmt :=
7998: 'UPDATE msc_st_trading_partner_sites '
7999: ||' SET process_flag = '||G_ERROR_FLG||','
8000: ||' error_text = '||''''||lv_message_text||''''
8001: ||' WHERE sr_instance_code = :v_instance_code'
8002: ||' AND batch_id = :lv_batch_id'

Line 8032: (p_table_name =>'MSC_ST_TRADING_PARTNER_SITES',

8028: END IF;
8029:
8030: -- Update sr_tp_id(customer)
8031: lv_return :=msc_st_util.derive_partner_org_id
8032: (p_table_name =>'MSC_ST_TRADING_PARTNER_SITES',
8033: p_org_partner_name =>'PARTNER_NAME',
8034: p_org_partner_id =>'SR_TP_ID',
8035: p_instance_code => v_instance_code,
8036: p_partner_type => G_CUSTOMER,

Line 8047: (p_table_name => 'MSC_ST_TRADING_PARTNER_SITES',

8043: END IF;
8044:
8045: -- Update sr_tp_id(Organization)
8046: lv_return :=msc_st_util.derive_partner_org_id
8047: (p_table_name => 'MSC_ST_TRADING_PARTNER_SITES',
8048: p_org_partner_name => 'PARTNER_NAME',
8049: p_org_partner_id => 'SR_TP_ID',
8050: p_instance_code => v_instance_code,
8051: p_partner_type => G_ORGANIZATION,

Line 8063: (p_table_name =>'MSC_ST_TRADING_PARTNER_SITES',

8059: END IF ; --v_install_msc
8060:
8061: -- Update sr_tp_id(supplier)
8062: lv_return :=msc_st_util.derive_partner_org_id
8063: (p_table_name =>'MSC_ST_TRADING_PARTNER_SITES',
8064: p_org_partner_name =>'PARTNER_NAME',
8065: p_org_partner_id =>'SR_TP_ID',
8066: p_instance_code => v_instance_code,
8067: p_partner_type => G_VENDOR,

Line 8081: 'UPDATE msc_st_trading_partner_sites'

8077:
8078: -- Error out the record if sr_tp_id is NULL
8079: v_sql_stmt := 28;
8080: lv_sql_stmt :=
8081: 'UPDATE msc_st_trading_partner_sites'
8082: ||' SET process_flag = '||G_ERROR_FLG||','
8083: ||' error_text = '||''''||lv_message_text||''''
8084: ||' WHERE NVL(sr_tp_id,'||NULL_VALUE||')= '||NULL_VALUE
8085: ||' AND batch_id = :lv_batch_id'

Line 8110: (p_table_name => 'MSC_ST_TRADING_PARTNER_SITES',

8106: END IF;
8107:
8108: -- Update sr_tp_site_id(Vendor)
8109: lv_return := MSC_ST_UTIL.DERIVE_PARTNER_SITE_ID
8110: (p_table_name => 'MSC_ST_TRADING_PARTNER_SITES',
8111: p_partner_name => 'PARTNER_NAME',
8112: p_partner_site_code => 'TP_SITE_CODE',
8113: p_partner_site_id => 'SR_TP_SITE_ID',
8114: p_instance_code => v_instance_code,

Line 8128: (p_table_name => 'MSC_ST_TRADING_PARTNER_SITES',

8124:
8125: IF(v_install_msc) THEN
8126: -- Update sr_tp_site_id(Customer)
8127: lv_return := MSC_ST_UTIL.DERIVE_PARTNER_SITE_ID
8128: (p_table_name => 'MSC_ST_TRADING_PARTNER_SITES',
8129: p_partner_name => 'PARTNER_NAME',
8130: p_partner_site_code => 'TP_SITE_CODE',
8131: p_partner_site_id => 'SR_TP_SITE_ID',
8132: p_instance_code => v_instance_code,

Line 8147: 'UPDATE msc_st_trading_partner_sites mts'

8143: -- Update sr_tp_site_id(Organization)
8144: -- location_id goes into sr_tp_site_id
8145: v_sql_stmt := 28;
8146: lv_sql_stmt :=
8147: 'UPDATE msc_st_trading_partner_sites mts'
8148: ||' SET sr_tp_site_id = (SELECT local_id'
8149: ||' FROM msc_local_id_setup mls'
8150: ||' WHERE mls.char1 = mts.sr_instance_code'
8151: ||' AND mls.char3 = mts.location'

Line 8177: ||' FROM msc_st_trading_partner_sites mts'

8173: v_sql_stmt := 29;
8174: lv_sql_stmt :=
8175: 'UPDATE msc_local_id_setup mls'
8176: ||' SET mls.char4 = NVL( (select mts.location '
8177: ||' FROM msc_st_trading_partner_sites mts'
8178: ||' WHERE mls.char1 = mts.sr_instance_code'
8179: ||' AND NVL(mls.char2, '||''''||NULL_CHAR||''''||') = '
8180: ||' NVL(mts.company_name,'||''''||NULL_CHAR||''''||') '
8181: ||' AND mls.char3 = mts.partner_name'

Line 8206: pEntityName => 'MSC_ST_TRADING_PARTNER_SITES',

8202: (ERRBUF => lv_error_text,
8203: RETCODE => lv_return,
8204: pBatchID => lv_batch_id,
8205: pInstanceCode => v_instance_code,
8206: pEntityName => 'MSC_ST_TRADING_PARTNER_SITES',
8207: pInstanceID => v_instance_id);
8208:
8209: IF NVL(lv_return,0) <> 0 THEN
8210: RAISE ex_logging_err;

Line 8219: UPDATE msc_st_trading_partner_sites

8215:
8216: IF c4%ROWCOUNT > 0 THEN
8217: v_sql_stmt := 29;
8218: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
8219: UPDATE msc_st_trading_partner_sites
8220: SET sr_tp_site_id = msc_st_sr_tp_site_id_s.NEXTVAL
8221: WHERE rowid = lb_rowid(j);
8222:
8223: -- Insert record in LID

Line 8260: FROM msc_st_trading_partner_sites

8256: v_current_date,
8257: v_current_user,
8258: v_current_date,
8259: v_current_user
8260: FROM msc_st_trading_partner_sites
8261: WHERE rowid = lb_rowid(j);
8262: END IF;
8263: CLOSE C4;
8264:

Line 8272: UPDATE msc_st_trading_partner_sites

8268:
8269: IF c7%ROWCOUNT > 0 THEN
8270: v_sql_stmt := 31;
8271: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
8272: UPDATE msc_st_trading_partner_sites
8273: SET sr_tp_site_id = msc_st_location_id_s.NEXTVAL
8274: WHERE rowid = lb_rowid(j);
8275:
8276: -- Insert record in LID

Line 8307: FROM msc_st_trading_partner_sites

8303: v_current_date,
8304: v_current_user,
8305: v_current_date,
8306: v_current_user
8307: FROM msc_st_trading_partner_sites
8308: WHERE rowid = lb_rowid(j);
8309: END IF;
8310: CLOSE C7;
8311:

Line 8315: 'UPDATE msc_st_trading_partner_sites mts'

8311:
8312: --Deriving location_id
8313: v_sql_stmt := 32.1;
8314: lv_sql_stmt :=
8315: 'UPDATE msc_st_trading_partner_sites mts'
8316: ||' SET sr_tp_site_id = (SELECT local_id'
8317: ||' FROM msc_local_id_setup mls'
8318: ||' WHERE mls.char1 = mts.sr_instance_code'
8319: ||' AND mls.char3 = mts.location'

Line 8343: 'UPDATE msc_st_trading_partner_sites '

8339: -- of Customer Sites SCE would pick LOCATION as the COMPANY_SITE_NAME for MSC_COMPANY_SITES.
8340:
8341: v_sql_stmt := 33;
8342: lv_sql_stmt :=
8343: 'UPDATE msc_st_trading_partner_sites '
8344: ||' SET LOCATION = TP_SITE_CODE '
8345: ||' WHERE process_flag = '|| G_IN_PROCESS
8346: ||' AND partner_type = '|| G_CUSTOMER
8347: ||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'

Line 8362: (p_table_name => 'MSC_ST_TRADING_PARTNER_SITES',

8358:
8359:
8360: -- Set the process flag as Valid and poulate instance_id
8361: lv_return := MSC_ST_UTIL.SET_PROCESS_FLAG
8362: (p_table_name => 'MSC_ST_TRADING_PARTNER_SITES',
8363: p_instance_id => v_instance_id,
8364: p_instance_code => v_instance_code,
8365: p_process_flag => G_VALID,
8366: p_error_text => lv_error_text,

Line 8377: (p_table_name => 'MSC_ST_TRADING_PARTNER_SITES',

8373:
8374: -- At the end calling the LOG_ERROR for logging all errored out records
8375:
8376: lv_return := MSC_ST_UTIL.LOG_ERROR
8377: (p_table_name => 'MSC_ST_TRADING_PARTNER_SITES',
8378: p_instance_code => v_instance_code,
8379: p_row => lv_column_names,
8380: p_severity => G_SEV_ERROR,
8381: p_message_text => NULL,

Line 50998: lv_count:= SET_IN_PROCESS (p_table_name => 'MSC_ST_TRADING_PARTNER_SITES');

50994: IF lv_count > 0 Then
50995: prec.tp_customer_flag:= SYS_YES;
50996: prec.tp_vendor_flag:= SYS_YES;
50997: End IF;
50998: lv_count:= SET_IN_PROCESS (p_table_name => 'MSC_ST_TRADING_PARTNER_SITES');
50999: IF lv_count > 0 Then
51000: prec.tp_customer_flag:= SYS_YES;
51001: prec.tp_vendor_flag:= SYS_YES;
51002: End IF;