[Home] [Help]
1060: application_short_name => 'MSC',
1061: statement_type => AD_DDL.CREATE_INDEX,
1062: statement =>
1063: 'create index MSC_ST_TRADING_PART_N1_'||v_instance_code
1064: ||' on MSC_ST_TRADING_PARTNERS '
1065: ||'(sr_instance_code, partner_name, partner_type, company_name) '
1066: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
1067: object_name =>'MSC_ST_TRADING_PART_N1_'||v_instance_code);
1068:
1078: application_short_name => 'MSC',
1079: statement_type => AD_DDL.CREATE_INDEX,
1080: statement =>
1081: 'create index MSC_ST_TRADING_PART_N2_'||v_instance_code
1082: ||' on MSC_ST_TRADING_PARTNERS '
1083: ||'(sr_instance_code, organization_code, partner_type, company_name) '
1084: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
1085: object_name =>'MSC_ST_TRADING_PART_N2_'||v_instance_code);
1086:
1204: END;
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);
3173: SELECT 1
3174: INTO lv_rec_count_mtp
3175: FROM dual
3176: WHERE EXISTS(SELECT 1
3177: FROM msc_st_trading_partners
3178: WHERE sr_instance_id = v_instance_id
3179: AND process_flag = G_VALID);
3180:
3181: EXCEPTION
3207:
3208: BEGIN
3209:
3210: v_sql_stmt := 1;
3211: UPDATE msc_st_trading_partners
3212: SET modeled_customer_id =
3213: (SELECT local_id
3214: FROM msc_local_id_setup
3215: WHERE char1 = sr_instance_code
3222: AND process_flag = G_VALID
3223: AND partner_type = G_ORGANIZATION;
3224:
3225: v_sql_stmt := 2;
3226: UPDATE msc_st_trading_partners
3227: SET modeled_supplier_id =
3228: (SELECT local_id
3229: FROM msc_local_id_setup
3230: WHERE char1 = sr_instance_code
3237: AND process_flag = G_VALID
3238: AND partner_type = G_ORGANIZATION;
3239:
3240: v_sql_stmt := 3;
3241: UPDATE msc_st_trading_partners
3242: SET modeled_customer_site_id =
3243: (SELECT local_id
3244: FROM msc_local_id_setup
3245: WHERE char1 = sr_instance_code
3253: AND process_flag = G_VALID
3254: AND partner_type = G_ORGANIZATION;
3255:
3256: v_sql_stmt := 4;
3257: UPDATE msc_st_trading_partners
3258: SET modeled_supplier_site_id =
3259: (SELECT local_id
3260: FROM msc_local_id_setup
3261: WHERE char1 = sr_instance_code
3282: RAISE ex_logging_err;
3283: END IF;
3284:
3285: v_sql_stmt := 5;
3286: UPDATE msc_st_trading_partners
3287: SET error_text = lv_message_text
3288: WHERE sr_instance_code = v_instance_code
3289: AND partner_type = G_ORGANIZATION
3290: AND process_flag = G_VALID
3299:
3300: v_sql_stmt := 6;
3301: -- bug 2774016 (validation for master organization code)
3302:
3303: UPDATE msc_st_trading_partners
3304: SET master_organization =
3305: (SELECT local_id
3306: FROM msc_local_id_setup
3307: WHERE char1 = sr_instance_code
3352: msc_errors_s.NEXTVAL,
3353: st_transaction_id,
3354: message_id,
3355: sr_instance_code,
3356: 'MSC_ST_TRADING_PARTNERS',
3357: 'N',
3358: data_source_type,
3359: 'MASTER_ORGANIZATION_CODE',
3360: G_SEV_WARNING,
3369: program_id,
3370: program_update_date,
3371: lv_message_text
3372: FROM
3373: MSC_ST_TRADING_PARTNERS
3374: WHERE sr_instance_code = v_instance_code
3375: AND partner_type = G_ORGANIZATION
3376: AND process_flag = G_VALID
3377: AND (master_organization IS NULL
6068: (ERRBUF => lv_error_text,
6069: RETCODE => lv_return,
6070: pBatchID => lv_batch_id,
6071: pInstanceCode => v_instance_code,
6072: pEntityName => 'MSC_ST_TRADING_PARTNERS',
6073: pInstanceID => v_instance_id);
6074:
6075: IF NVL(lv_return,0) <> 0 THEN
6076: RAISE ex_logging_err;
6257: ex_logging_err EXCEPTION;
6258:
6259: CURSOR c1(p_batch_id NUMBER) IS
6260: SELECT rowid
6261: FROM msc_st_trading_partners
6262: WHERE process_flag IN (G_IN_PROCESS,G_ERROR_FLG)
6263: AND sr_instance_code = v_instance_code
6264: AND batch_id = p_batch_id;
6265:
6264: AND batch_id = p_batch_id;
6265:
6266: CURSOR c2(p_batch_id NUMBER) IS
6267: SELECT rowid
6268: FROM msc_st_trading_partners
6269: WHERE process_flag = G_IN_PROCESS
6270: AND sr_instance_code = v_instance_code
6271: AND batch_id = p_batch_id
6272: AND NVL(sr_tp_id,NULL_VALUE) = NULL_VALUE
6432: IF lv_return <> 0 THEN
6433: RAISE ex_logging_err;
6434: END IF;
6435:
6436: --Validation check for the table MSC_ST_TRADING_PARTNERS
6437:
6438: --Duplicate records check for the records whose source is other than XML
6439: --Different SQL is used because in XML we can identify the latest records
6440: --whereas in batch load we cannot.
6440: --whereas in batch load we cannot.
6441:
6442: v_sql_stmt := 01;
6443: lv_sql_stmt :=
6444: 'UPDATE msc_st_trading_partners mstp1 '
6445: ||' SET process_flag = '||G_ERROR_FLG||','
6446: ||' error_text = '||''''||lv_message_text||''''
6447: ||' WHERE EXISTS ( SELECT 1 FROM msc_st_trading_partners mstp2 '
6448: ||' WHERE mstp2.sr_instance_code = mstp1.sr_instance_code'
6443: lv_sql_stmt :=
6444: 'UPDATE msc_st_trading_partners mstp1 '
6445: ||' SET process_flag = '||G_ERROR_FLG||','
6446: ||' error_text = '||''''||lv_message_text||''''
6447: ||' WHERE EXISTS ( SELECT 1 FROM msc_st_trading_partners mstp2 '
6448: ||' WHERE mstp2.sr_instance_code = mstp1.sr_instance_code'
6449: ||' AND NVL(mstp2.company_name, '||''''||NULL_CHAR||''''||') = '
6450: ||' NVL(mstp1.company_name, '||''''||NULL_CHAR||''''||')'
6451: ||' AND mstp2.partner_name = mstp1.partner_name'
6470:
6471: IF v_MSC_CONFIGURATION = 3 THEN -- For SCE Standalone add company in udk
6472: v_sql_stmt := 02;
6473: lv_sql_stmt :=
6474: 'UPDATE msc_st_trading_partners mstp1 '
6475: ||' SET process_flag = '||G_ERROR_FLG||','
6476: ||' error_text = '||''''||lv_message_text||''''
6477: ||' WHERE EXISTS ( SELECT 1 FROM msc_st_trading_partners mstp2 '
6478: ||' WHERE mstp2.sr_instance_code = mstp1.sr_instance_code'
6473: lv_sql_stmt :=
6474: 'UPDATE msc_st_trading_partners mstp1 '
6475: ||' SET process_flag = '||G_ERROR_FLG||','
6476: ||' error_text = '||''''||lv_message_text||''''
6477: ||' WHERE EXISTS ( SELECT 1 FROM msc_st_trading_partners mstp2 '
6478: ||' WHERE mstp2.sr_instance_code = mstp1.sr_instance_code'
6479: ||' AND NVL(mstp2.company_name, '||''''||NULL_CHAR||''''||') = '
6480: ||' NVL(mstp1.company_name, '||''''||NULL_CHAR||''''||')'
6481: ||' AND mstp2.organization_code = mstp1.organization_code'
6492:
6493: ELSE
6494: v_sql_stmt := 02;
6495: lv_sql_stmt :=
6496: 'UPDATE msc_st_trading_partners mstp1 '
6497: ||' SET process_flag = '||G_ERROR_FLG||','
6498: ||' error_text = '||''''||lv_message_text||''''
6499: ||' WHERE EXISTS ( SELECT 1 FROM msc_st_trading_partners mstp2 '
6500: ||' WHERE mstp2.sr_instance_code = mstp1.sr_instance_code'
6495: lv_sql_stmt :=
6496: 'UPDATE msc_st_trading_partners mstp1 '
6497: ||' SET process_flag = '||G_ERROR_FLG||','
6498: ||' error_text = '||''''||lv_message_text||''''
6499: ||' WHERE EXISTS ( SELECT 1 FROM msc_st_trading_partners mstp2 '
6500: ||' WHERE mstp2.sr_instance_code = mstp1.sr_instance_code'
6501: -- ||' AND NVL(mstp2.company_name, '||''''||NULL_CHAR||''''||') = '
6502: -- ||' NVL(mstp1.company_name, '||''''||NULL_CHAR||''''||')'
6503: ||' AND mstp2.organization_code = mstp1.organization_code'
6534:
6535: --Duplicate records check for the records whose source is XML
6536: v_sql_stmt := 03;
6537: lv_sql_stmt :=
6538: 'UPDATE msc_st_trading_partners mstp1 '
6539: ||' SET process_flag = '||G_ERROR_FLG||','
6540: ||' error_text = '||''''||lv_message_text||''''
6541: ||' WHERE message_id < ( SELECT max(message_id) '
6542: ||' FROM msc_st_trading_partners mstp2'
6538: 'UPDATE msc_st_trading_partners mstp1 '
6539: ||' SET process_flag = '||G_ERROR_FLG||','
6540: ||' error_text = '||''''||lv_message_text||''''
6541: ||' WHERE message_id < ( SELECT max(message_id) '
6542: ||' FROM msc_st_trading_partners mstp2'
6543: ||' WHERE mstp2.sr_instance_code = mstp1.sr_instance_code'
6544: ||' AND NVL(mstp2.company_name, '||''''||NULL_CHAR||''''||') = '
6545: ||' NVL(mstp1.company_name, '||''''||NULL_CHAR||''''||')'
6546: ||' AND mstp2.partner_name = mstp1.partner_name'
6562: USING v_instance_code;
6563:
6564: v_sql_stmt := 04;
6565: lv_sql_stmt :=
6566: 'UPDATE msc_st_trading_partners mstp1 '
6567: ||' SET process_flag = '||G_ERROR_FLG||','
6568: ||' error_text = '||''''||lv_message_text||''''
6569: ||' WHERE message_id < ( SELECT max(message_id) '
6570: ||' FROM msc_st_trading_partners mstp2'
6566: 'UPDATE msc_st_trading_partners mstp1 '
6567: ||' SET process_flag = '||G_ERROR_FLG||','
6568: ||' error_text = '||''''||lv_message_text||''''
6569: ||' WHERE message_id < ( SELECT max(message_id) '
6570: ||' FROM msc_st_trading_partners mstp2'
6571: ||' WHERE mstp2.sr_instance_code = mstp1.sr_instance_code'
6572: -- ||' AND NVL(mstp2.company_name, '||''''||NULL_CHAR||''''||') = '
6573: -- ||' NVL(mstp1.company_name, '||''''||NULL_CHAR||''''||')'
6574: ||' AND mstp2.organization_code = mstp1.organization_code'
6628: FROM DUAL;
6629:
6630: v_sql_stmt := 06;
6631: lv_sql_stmt :=
6632: 'UPDATE msc_st_trading_partners '
6633: ||' SET batch_id = :lv_batch_id '
6634: ||' WHERE process_flag IN ('||G_IN_PROCESS||','||G_ERROR_FLG||')'
6635: ||' AND sr_instance_code = :v_instance_code'
6636: ||' AND NVL(batch_id,'||NULL_VALUE||') = '||NULL_VALUE
6651: CLOSE c1;
6652:
6653: v_sql_stmt := 07;
6654: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
6655: UPDATE msc_st_trading_partners
6656: SET st_transaction_id = msc_st_trading_partners_s.NEXTVAL,
6657: refresh_id = v_refresh_id,
6658: last_update_date = v_current_date,
6659: last_updated_by = v_current_user,
6652:
6653: v_sql_stmt := 07;
6654: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
6655: UPDATE msc_st_trading_partners
6656: SET st_transaction_id = msc_st_trading_partners_s.NEXTVAL,
6657: refresh_id = v_refresh_id,
6658: last_update_date = v_current_date,
6659: last_updated_by = v_current_user,
6660: creation_date = v_current_date,
6666: p_error_code => 'MSC_PP_NO_DELETION',
6667: p_message_text => lv_message_text,
6668: p_error_text => lv_error_text,
6669: p_token1 => 'TABLE_NAME',
6670: p_token_value1 => 'MSC_ST_TRADING_PARTNERS');
6671:
6672: -- Deletion is not allowed for this table
6673: v_sql_stmt := 08;
6674: lv_sql_stmt :=
6671:
6672: -- Deletion is not allowed for this table
6673: v_sql_stmt := 08;
6674: lv_sql_stmt :=
6675: 'UPDATE msc_st_trading_partners '
6676: ||' SET process_flag = '||G_ERROR_FLG||','
6677: ||' error_text = '||''''||lv_message_text||''''
6678: ||' WHERE deleted_flag = '||SYS_YES
6679: ||' AND process_flag = '||G_IN_PROCESS
6708: lv_where_str := ' AND NVL(deleted_flag,'||NULL_VALUE||') '
6709: ||' NOT IN(1,2)';
6710:
6711: lv_return := MSC_ST_UTIL.LOG_ERROR
6712: (p_table_name => 'MSC_ST_TRADING_PARTNERS',
6713: p_instance_code => v_instance_code,
6714: p_row => lv_column_names,
6715: p_severity => G_SEV_WARNING,
6716: p_message_text => lv_message_text,
6739:
6740: -- Error out records where partner_name is NULL
6741: v_sql_stmt := 09;
6742: lv_sql_stmt :=
6743: 'UPDATE msc_st_trading_partners'
6744: ||' SET process_flag = '||G_ERROR_FLG||','
6745: ||' error_text = '||''''||lv_message_text||''''
6746: ||' WHERE NVL(partner_name, '||''''||NULL_CHAR||''''||') '
6747: ||' = '||''''||NULL_CHAR||''''
6771:
6772: -- Error out records where partner_type NOT in (1,2,3,4)
6773: v_sql_stmt := 10;
6774: lv_sql_stmt :=
6775: 'UPDATE msc_st_trading_partners'
6776: ||' SET process_flag = '||G_ERROR_FLG||','
6777: ||' error_text = '||''''||lv_message_text||''''
6778: ||' WHERE sr_instance_code = :v_instance_code'
6779: ||' AND batch_id = :lv_batch_id'
6802:
6803: -- Error out records where organization_code is NULL for partner_type = 3
6804: v_sql_stmt := 11;
6805: lv_sql_stmt :=
6806: 'UPDATE msc_st_trading_partners '
6807: ||' SET process_flag = '||G_ERROR_FLG||','
6808: ||' error_text = '||''''||lv_message_text||''''
6809: ||' WHERE sr_instance_code = :v_instance_code'
6810: ||' AND batch_id = :lv_batch_id'
6832:
6833: -- Resource type validation
6834: v_sql_stmt := 11.1;
6835: lv_sql_stmt :=
6836: 'UPDATE msc_st_trading_partners '
6837: ||' SET process_flag = '||G_ERROR_FLG||','
6838: ||' error_text = '||''''||lv_message_text||''''
6839: ||' WHERE process_flag = '||G_IN_PROCESS
6840: ||' AND batch_id = :lv_batch_id'
6851: v_instance_code;
6852:
6853: v_sql_stmt := 11.2;
6854: lv_sql_stmt :=
6855: 'UPDATE msc_st_trading_partners '
6856: ||' SET RESOURCE_TYPE ='''''
6857: ||' WHERE process_flag = '||G_IN_PROCESS
6858: ||' AND batch_id = :lv_batch_id'
6859: ||' AND sr_instance_code = :v_instance_code'
6884: IF lv_return <> 0 THEN
6885: RAISE ex_logging_err;
6886: END IF;
6887:
6888: UPDATE msc_st_trading_partners mstp
6889: SET process_flag = G_ERROR_FLG,
6890: error_text = lv_message_text
6891: WHERE mstp.process_flag = G_IN_PROCESS
6892: AND mstp.sr_instance_code = v_instance_code
6923:
6924: IF (v_install_msc) THEN
6925: v_sql_stmt := 11;
6926: lv_sql_stmt :=
6927: 'UPDATE msc_st_trading_partners '
6928: ||' SET process_flag = '||G_ERROR_FLG||','
6929: ||' error_text = '||''''||lv_message_text||''''
6930: ||' WHERE sr_instance_code = :v_instance_code'
6931: ||' AND batch_id = :lv_batch_id'
6946: -- Update all partner_name and calendar_code as instance_code concatenated with name
6947: -- Where partner_type = 3 (organization)
6948: v_sql_stmt := 12;
6949: lv_sql_stmt :=
6950: 'UPDATE msc_st_trading_partners '
6951: ||' SET partner_name = sr_instance_code'||'||'':''||'||'partner_name,'
6952: ||' calendar_code = sr_instance_code'||'||'':''||'||'calendar_code'
6953: ||' WHERE partner_type = '||G_ORGANIZATION
6954: ||' AND sr_instance_code = :v_instance_code'
6971: p_token_value1 => 'SR_INSTANCE_ID AND CALENDAR_CODE',
6972: p_token2 => 'MASTER_TABLE',
6973: p_token_value2 => 'MSC_CALENDARS',
6974: p_token3 => 'CHILD_TABLE',
6975: p_token_value3 => 'MSC_ST_TRADING_PARTNERS');
6976: IF lv_return <> 0 THEN
6977: RAISE ex_logging_err;
6978: END IF;
6979:
6979:
6980: -- Validate the calendar code for org
6981: v_sql_stmt := 14;
6982: lv_sql_stmt :=
6983: 'UPDATE msc_st_trading_partners mstp'
6984: ||' SET process_flag = '||G_ERROR_FLG||','
6985: ||' error_text = '||''''||lv_message_text||''''
6986: ||' WHERE NOT EXISTS ( SELECT 1 '
6987: ||' FROM msc_calendars mc '
7018: ' AND calendar_exception_set_id <> -1 '
7019: ||' AND partner_type = '||G_ORGANIZATION ;
7020:
7021: lv_return := MSC_ST_UTIL.LOG_ERROR
7022: (p_table_name => 'MSC_ST_TRADING_PARTNERS',
7023: p_instance_code => v_instance_code,
7024: p_row => lv_column_names,
7025: p_severity => G_SEV_WARNING,
7026: p_message_text => lv_message_text,
7045: p_token_value1 => 'SR_INSTANCE_CODE OR WEIGHT_UOM',
7046: p_token2 => 'MASTER_TABLE',
7047: p_token_value2 => 'MSC_ST_UNITS_OF_MEASURE',
7048: p_token3 => 'CHILD_TABLE',
7049: p_token_value3 => 'MSC_ST_TRADING_PARTNERS');
7050: IF lv_return <> 0 THEN
7051: RAISE ex_logging_err;
7052: END IF;
7053:
7053:
7054: -- Validate all UOM code both Weight/Volume
7055: v_sql_stmt := 15;
7056: lv_sql_stmt :=
7057: 'UPDATE msc_st_trading_partners mstp'
7058: ||' SET process_flag = '||G_ERROR_FLG||','
7059: ||' error_text = '||''''||lv_message_text||''''
7060: ||' WHERE NOT EXISTS(SELECT 1 '
7061: ||' FROM msc_units_of_measure muom '
7093: p_token_value1 => 'SR_INSTANCE_CODE OR VOLUME_UOM',
7094: p_token2 => 'MASTER_TABLE',
7095: p_token_value2 => 'MSC_ST_UNITS_OF_MEASURE',
7096: p_token3 => 'CHILD_TABLE',
7097: p_token_value3 => 'MSC_ST_TRADING_PARTNERS');
7098: IF lv_return <> 0 THEN
7099: RAISE ex_logging_err;
7100: END IF;
7101:
7100: END IF;
7101:
7102: v_sql_stmt := 16;
7103: lv_sql_stmt :=
7104: 'UPDATE msc_st_trading_partners mstp'
7105: ||' SET process_flag = '||G_ERROR_FLG||','
7106: ||' error_text = '||''''||lv_message_text||''''
7107: ||' WHERE NOT EXISTS(SELECT 1 '
7108: ||' FROM msc_units_of_measure muom'
7159: -- Validate Demand Class, if value provided it should exists
7160: -- in ODS or staging table
7161:
7162: lv_return :=
7163: MSC_ST_UTIL.VALIDATE_DMD_CLASS(p_table_name => 'MSC_ST_TRADING_PARTNERS',
7164: p_dmd_class_column => 'DEFAULT_DEMAND_CLASS',
7165: p_instance_id => v_instance_id,
7166: p_instance_code => v_instance_code,
7167: p_severity => G_SEV3_ERROR,
7189: END IF;
7190:
7191: --Derive company_id
7192: lv_return := MSC_ST_UTIL.DERIVE_COMPANY_ID
7193: (p_table_name => 'MSC_ST_TRADING_PARTNERS',
7194: p_company_name => 'COMPANY_NAME',
7195: p_company_id => 'COMPANY_ID',
7196: p_instance_code => v_instance_code,
7197: p_error_text => lv_error_text,
7213: IF v_sce_installed THEN
7214: lv_my_company := GET_MY_COMPANY;
7215: -- if record is for OEM's partners, company name will be null or 'My Company'
7216:
7217: UPDATE msc_st_trading_partners mscu
7218: SET company_id = -1
7219: WHERE exists( SELECT 1
7220: FROM msc_companies mc
7221: WHERE mc.company_name = nvl(mscu.company_name,lv_my_company)
7226: AND mscu.batch_id = lv_batch_id;
7227:
7228: -- if not 'My Company' then get the first occurence of sr_tp_id for this company_name
7229:
7230: UPDATE msc_st_trading_partners mscu
7231: SET company_id = (SELECT local_id from MSC_LOCAL_ID_SETUP
7232: WHERE char1 = mscu.sr_instance_code
7233: and NVL(char3,NULL_CHAR) = NVL(mscu.company_name,NULL_CHAR)
7234: and entity_name = 'SR_TP_ID'
7251: IF lv_return <> 0 THEN
7252: RAISE ex_logging_err;
7253: END IF;
7254:
7255: UPDATE msc_st_trading_partners mscu
7256: SET process_flag = G_ERROR_FLG,
7257: error_text = lv_message_text
7258: WHERE mscu.company_id IS NULL
7259: AND mscu.process_flag = G_IN_PROCESS
7276: END IF;
7277:
7278: -- Derive source_org_id
7279: lv_return :=msc_st_util.derive_partner_org_id
7280: (p_table_name => 'MSC_ST_TRADING_PARTNERS',
7281: p_org_partner_name => 'SOURCE_ORG_CODE',
7282: p_org_partner_id => 'SOURCE_ORG_ID',
7283: p_instance_code => v_instance_code,
7284: p_partner_type => G_ORGANIZATION,
7307: END IF;
7308:
7309: v_sql_stmt := 16.1;
7310: lv_sql_stmt :=
7311: 'UPDATE msc_st_trading_partners mstp'
7312: ||' SET process_flag = '||G_ERROR_FLG||','
7313: ||' error_text = '||''''||lv_message_text||''''
7314: ||' WHERE EXISTS(SELECT 1 '
7315: ||' FROM msc_trading_partners mtp,'
7337: lv_batch_id;
7338:
7339: v_sql_stmt := 16.2;
7340: lv_sql_stmt :=
7341: 'UPDATE msc_st_trading_partners mstp'
7342: ||' SET process_flag = '||G_ERROR_FLG||','
7343: ||' error_text = '||''''||lv_message_text||''''
7344: ||' WHERE EXISTS(SELECT 1 '
7345: ||' FROM msc_trading_partners mtp'
7363: -- ssurendr - bug 2647423: check for partner name - org code mismatch with respect to ODS - end.
7364:
7365: -- Derive sr_tp_id(Organization)
7366: lv_return :=msc_st_util.derive_partner_org_id
7367: (p_table_name => 'MSC_ST_TRADING_PARTNERS',
7368: p_org_partner_name => 'ORGANIZATION_CODE',
7369: p_org_partner_id => 'SR_TP_ID',
7370: p_instance_code => v_instance_code,
7371: p_partner_type => G_ORGANIZATION,
7378: END IF;
7379:
7380: -- Derive sr_tp_id(Customer)
7381: lv_return :=msc_st_util.derive_partner_org_id
7382: (p_table_name => 'MSC_ST_TRADING_PARTNERS',
7383: p_org_partner_name => 'PARTNER_NAME',
7384: p_org_partner_id => 'SR_TP_ID',
7385: p_instance_code => v_instance_code,
7386: p_partner_type => G_CUSTOMER,
7393: END IF;
7394:
7395: -- Derive sr_tp_id(Supplier).
7396: lv_return :=msc_st_util.derive_partner_org_id
7397: (p_table_name => 'MSC_ST_TRADING_PARTNERS',
7398: p_org_partner_name => 'PARTNER_NAME',
7399: p_org_partner_id => 'SR_TP_ID',
7400: p_instance_code => v_instance_code,
7401: p_partner_type => G_VENDOR,
7408: END IF;
7409:
7410: -- Derive sr_tp_id(Carrier).
7411: lv_return :=msc_st_util.derive_partner_org_id
7412: (p_table_name => 'MSC_ST_TRADING_PARTNERS',
7413: p_org_partner_name => 'PARTNER_NAME',
7414: p_org_partner_id => 'SR_TP_ID',
7415: p_instance_code => v_instance_code,
7416: p_partner_type => G_CARRIER,
7444: ||' NOT IN(1,2)'
7445: ||' AND partner_type = '||G_ORGANIZATION ;
7446:
7447: lv_return := MSC_ST_UTIL.LOG_ERROR
7448: (p_table_name => 'MSC_ST_TRADING_PARTNERS',
7449: p_instance_code => v_instance_code,
7450: p_row => lv_column_names,
7451: p_severity => G_SEV_WARNING,
7452: p_message_text => lv_message_text,
7466: (ERRBUF => lv_error_text,
7467: RETCODE => lv_return,
7468: pBatchID => lv_batch_id,
7469: pInstanceCode => v_instance_code,
7470: pEntityName => 'MSC_ST_TRADING_PARTNERS',
7471: pInstanceID => v_instance_id);
7472:
7473: IF NVL(lv_return,0) <> 0 THEN
7474: RAISE ex_logging_err;
7480:
7481: IF c2%ROWCOUNT > 0 THEN
7482: v_sql_stmt := 17;
7483: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
7484: UPDATE msc_st_trading_partners
7485: SET sr_tp_id = msc_st_sr_tp_id_s.NEXTVAL
7486: WHERE rowid = lb_rowid(j);
7487:
7488: -- Insert into the LID table this new partner with the
7521: v_current_date,
7522: v_current_user,
7523: v_current_date,
7524: v_current_user
7525: FROM msc_st_trading_partners
7526: WHERE rowid = lb_rowid(j);
7527:
7528: END IF;
7529: CLOSE c2 ;
7531: -- Update all organization_code as instance_code concatenated with org_code
7532: -- Where partner_type = 3 (organization)
7533: v_sql_stmt := 19;
7534: lv_sql_stmt :=
7535: 'UPDATE msc_st_trading_partners '
7536: ||' SET organization_code = sr_instance_code'||'||'':''||'||'organization_code,'
7537: ||' master_organization = sr_tp_id'
7538: ||' WHERE partner_type = '||G_ORGANIZATION
7539: ||' AND sr_instance_code = :v_instance_code'
7548: lv_batch_id; */
7549:
7550: -- Set the process flag as Valid and populate instance_id
7551: lv_return := MSC_ST_UTIL.SET_PROCESS_FLAG
7552: (p_table_name => 'MSC_ST_TRADING_PARTNERS',
7553: p_instance_id => v_instance_id,
7554: p_instance_code => v_instance_code,
7555: p_process_flag => G_VALID,
7556: p_error_text => lv_error_text,
7564: -- At the end calling the LOG_ERROR for logging all
7565: -- errored out records.
7566:
7567: lv_return := MSC_ST_UTIL.LOG_ERROR
7568: (p_table_name => 'MSC_ST_TRADING_PARTNERS',
7569: p_instance_code => v_instance_code,
7570: p_row => lv_column_names,
7571: p_severity => G_SEV_ERROR,
7572: p_message_text => NULL,
28806: v_sql_stmt := 29;
28807: lv_sql_stmt :=
28808: 'UPDATE msc_st_resource_shifts msrs'
28809: ||' SET shift_num =(SELECT mlis.local_id'
28810: ||' FROM msc_local_id_setup mlis, msc_st_trading_partners mtp '
28811: ||' WHERE mtp.sr_instance_id = '||v_instance_id
28812: ||' AND mlis.char1 = msrs.sr_instance_code'
28813: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '
28814: ||' = NVL(msrs.company_name,'||''''||NULL_CHAR||''''||') '
29381:
29382: lv_sql_stmt :=
29383: 'UPDATE msc_st_resource_changes msrc'
29384: ||' SET shift_num =(SELECT mlis.local_id'
29385: ||' FROM msc_local_id_setup mlis, msc_st_trading_partners mtp '
29386: ||' WHERE mtp.sr_instance_id = '||v_instance_id
29387: ||' AND mlis.char1 = msrc.sr_instance_code'
29388: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '
29389: ||' = NVL(msrc.company_name,'||''''||NULL_CHAR||''''||') '
49166:
49167:
49168: /*==========================================================================+
49169: | DESCRIPTION : This procedure generates records in msc_st_bis_periods if |
49170: | records are coming into msc_st_trading_partners and/or |
49171: | records are coming in msc_st_calendars. |
49172: +==========================================================================*/
49173: PROCEDURE LOAD_BIS_PERIODS IS
49174: lv_error_text VARCHAR2(250);
49273: v_current_date,
49274: v_current_user,
49275: v_current_date,
49276: v_current_user
49277: FROM msc_period_start_dates mpsd,msc_st_trading_partners mtp
49278: WHERE mpsd.calendar_code = mtp.calendar_code
49279: AND mpsd.sr_instance_id = mtp.sr_instance_id
49280: AND mtp.partner_type = G_ORGANIZATION
49281: AND mtp.process_flag = G_VALID
51004: IF lv_count > 0 Then
51005: prec.tp_customer_flag:= SYS_YES;
51006: prec.tp_vendor_flag:= SYS_YES;
51007: End IF;
51008: lv_count:= SET_IN_PROCESS (p_table_name => 'MSC_ST_TRADING_PARTNERS');
51009: IF lv_count > 0 Then
51010: prec.tp_customer_flag:= SYS_YES;
51011: prec.tp_vendor_flag:= SYS_YES;
51012: prec.sourcing_rule_flag:= SYS_YES;