[Home] [Help]
1068: application_short_name => 'MSC',
1069: statement_type => AD_DDL.CREATE_INDEX,
1070: statement =>
1071: 'create index MSC_ST_TRADING_PART_N1_'||v_instance_code
1072: ||' on MSC_ST_TRADING_PARTNERS '
1073: ||'(sr_instance_code, partner_name, partner_type, company_name) '
1074: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
1075: object_name =>'MSC_ST_TRADING_PART_N1_'||v_instance_code);
1076:
1086: application_short_name => 'MSC',
1087: statement_type => AD_DDL.CREATE_INDEX,
1088: statement =>
1089: 'create index MSC_ST_TRADING_PART_N2_'||v_instance_code
1090: ||' on MSC_ST_TRADING_PARTNERS '
1091: ||'(sr_instance_code, organization_code, partner_type, company_name) '
1092: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
1093: object_name =>'MSC_ST_TRADING_PART_N2_'||v_instance_code);
1094:
1212: END;
1213:
1214:
1215:
1216: msc_analyse_tables_pk.analyse_table( 'MSC_ST_TRADING_PARTNERS', v_instance_id, -1);
1217: msc_analyse_tables_pk.analyse_table( 'MSC_ST_TRADING_PARTNER_SITES', v_instance_id, -1);
1218: msc_analyse_tables_pk.analyse_table( 'MSC_ST_SUB_INVENTORIES', v_instance_id, -1);
1219: msc_analyse_tables_pk.analyse_table( 'MSC_ST_LOCATION_ASSOCIATIONS', v_instance_id, -1);
1220: msc_analyse_tables_pk.analyse_table( 'MSC_ST_PARTNER_CONTACTS', v_instance_id, -1);
3180: SELECT 1
3181: INTO lv_rec_count_mtp
3182: FROM dual
3183: WHERE EXISTS(SELECT 1
3184: FROM msc_st_trading_partners
3185: WHERE sr_instance_id = v_instance_id
3186: AND process_flag = G_VALID);
3187:
3188: EXCEPTION
3214:
3215: BEGIN
3216:
3217: v_sql_stmt := 1;
3218: UPDATE msc_st_trading_partners
3219: SET modeled_customer_id =
3220: (SELECT local_id
3221: FROM msc_local_id_setup
3222: WHERE char1 = sr_instance_code
3229: AND process_flag = G_VALID
3230: AND partner_type = G_ORGANIZATION;
3231:
3232: v_sql_stmt := 2;
3233: UPDATE msc_st_trading_partners
3234: SET modeled_supplier_id =
3235: (SELECT local_id
3236: FROM msc_local_id_setup
3237: WHERE char1 = sr_instance_code
3244: AND process_flag = G_VALID
3245: AND partner_type = G_ORGANIZATION;
3246:
3247: v_sql_stmt := 3;
3248: UPDATE msc_st_trading_partners
3249: SET modeled_customer_site_id =
3250: (SELECT local_id
3251: FROM msc_local_id_setup
3252: WHERE char1 = sr_instance_code
3260: AND process_flag = G_VALID
3261: AND partner_type = G_ORGANIZATION;
3262:
3263: v_sql_stmt := 4;
3264: UPDATE msc_st_trading_partners
3265: SET modeled_supplier_site_id =
3266: (SELECT local_id
3267: FROM msc_local_id_setup
3268: WHERE char1 = sr_instance_code
3289: RAISE ex_logging_err;
3290: END IF;
3291:
3292: v_sql_stmt := 5;
3293: UPDATE msc_st_trading_partners
3294: SET error_text = lv_message_text
3295: WHERE sr_instance_code = v_instance_code
3296: AND partner_type = G_ORGANIZATION
3297: AND process_flag = G_VALID
3306:
3307: v_sql_stmt := 6;
3308: -- bug 2774016 (validation for master organization code)
3309:
3310: UPDATE msc_st_trading_partners
3311: SET master_organization =
3312: (SELECT local_id
3313: FROM msc_local_id_setup
3314: WHERE char1 = sr_instance_code
3359: msc_errors_s.NEXTVAL,
3360: st_transaction_id,
3361: message_id,
3362: sr_instance_code,
3363: 'MSC_ST_TRADING_PARTNERS',
3364: 'N',
3365: data_source_type,
3366: 'MASTER_ORGANIZATION_CODE',
3367: G_SEV_WARNING,
3376: program_id,
3377: program_update_date,
3378: lv_message_text
3379: FROM
3380: MSC_ST_TRADING_PARTNERS
3381: WHERE sr_instance_code = v_instance_code
3382: AND partner_type = G_ORGANIZATION
3383: AND process_flag = G_VALID
3384: AND (master_organization IS NULL
6075: (ERRBUF => lv_error_text,
6076: RETCODE => lv_return,
6077: pBatchID => lv_batch_id,
6078: pInstanceCode => v_instance_code,
6079: pEntityName => 'MSC_ST_TRADING_PARTNERS',
6080: pInstanceID => v_instance_id);
6081:
6082: IF NVL(lv_return,0) <> 0 THEN
6083: RAISE ex_logging_err;
6264: ex_logging_err EXCEPTION;
6265:
6266: CURSOR c1(p_batch_id NUMBER) IS
6267: SELECT rowid
6268: FROM msc_st_trading_partners
6269: WHERE process_flag IN (G_IN_PROCESS,G_ERROR_FLG)
6270: AND sr_instance_code = v_instance_code
6271: AND batch_id = p_batch_id;
6272:
6271: AND batch_id = p_batch_id;
6272:
6273: CURSOR c2(p_batch_id NUMBER) IS
6274: SELECT rowid
6275: FROM msc_st_trading_partners
6276: WHERE process_flag = G_IN_PROCESS
6277: AND sr_instance_code = v_instance_code
6278: AND batch_id = p_batch_id
6279: AND NVL(sr_tp_id,NULL_VALUE) = NULL_VALUE
6450: IF lv_return <> 0 THEN
6451: RAISE ex_logging_err;
6452: END IF;
6453:
6454: --Validation check for the table MSC_ST_TRADING_PARTNERS
6455:
6456: --Duplicate records check for the records whose source is other than XML
6457: --Different SQL is used because in XML we can identify the latest records
6458: --whereas in batch load we cannot.
6458: --whereas in batch load we cannot.
6459:
6460: v_sql_stmt := 01;
6461: lv_sql_stmt :=
6462: 'UPDATE msc_st_trading_partners mstp1 '
6463: ||' SET process_flag = '||G_ERROR_FLG||','
6464: ||' error_text = '||''''||lv_message_text||''''
6465: ||' WHERE EXISTS ( SELECT 1 FROM msc_st_trading_partners mstp2 '
6466: ||' WHERE mstp2.sr_instance_code = mstp1.sr_instance_code'
6461: lv_sql_stmt :=
6462: 'UPDATE msc_st_trading_partners mstp1 '
6463: ||' SET process_flag = '||G_ERROR_FLG||','
6464: ||' error_text = '||''''||lv_message_text||''''
6465: ||' WHERE EXISTS ( SELECT 1 FROM msc_st_trading_partners mstp2 '
6466: ||' WHERE mstp2.sr_instance_code = mstp1.sr_instance_code'
6467: ||' AND NVL(mstp2.company_name, '||''''||NULL_CHAR||''''||') = '
6468: ||' NVL(mstp1.company_name, '||''''||NULL_CHAR||''''||')'
6469: ||' AND mstp2.partner_name = mstp1.partner_name'
6489:
6490: IF v_MSC_CONFIGURATION = 3 THEN -- For SCE Standalone add company in udk
6491: v_sql_stmt := 02;
6492: lv_sql_stmt :=
6493: 'UPDATE msc_st_trading_partners mstp1 '
6494: ||' SET process_flag = '||G_ERROR_FLG||','
6495: ||' error_text = '||''''||lv_message_text||''''
6496: ||' WHERE EXISTS ( SELECT 1 FROM msc_st_trading_partners mstp2 '
6497: ||' WHERE mstp2.sr_instance_code = mstp1.sr_instance_code'
6492: lv_sql_stmt :=
6493: 'UPDATE msc_st_trading_partners mstp1 '
6494: ||' SET process_flag = '||G_ERROR_FLG||','
6495: ||' error_text = '||''''||lv_message_text||''''
6496: ||' WHERE EXISTS ( SELECT 1 FROM msc_st_trading_partners mstp2 '
6497: ||' WHERE mstp2.sr_instance_code = mstp1.sr_instance_code'
6498: ||' AND NVL(mstp2.company_name, '||''''||NULL_CHAR||''''||') = '
6499: ||' NVL(mstp1.company_name, '||''''||NULL_CHAR||''''||')'
6500: ||' AND mstp2.organization_code = mstp1.organization_code'
6511:
6512: ELSE
6513: v_sql_stmt := 02;
6514: lv_sql_stmt :=
6515: 'UPDATE msc_st_trading_partners mstp1 '
6516: ||' SET process_flag = '||G_ERROR_FLG||','
6517: ||' error_text = '||''''||lv_message_text||''''
6518: ||' WHERE EXISTS ( SELECT 1 FROM msc_st_trading_partners mstp2 '
6519: ||' WHERE mstp2.sr_instance_code = mstp1.sr_instance_code'
6514: lv_sql_stmt :=
6515: 'UPDATE msc_st_trading_partners mstp1 '
6516: ||' SET process_flag = '||G_ERROR_FLG||','
6517: ||' error_text = '||''''||lv_message_text||''''
6518: ||' WHERE EXISTS ( SELECT 1 FROM msc_st_trading_partners mstp2 '
6519: ||' WHERE mstp2.sr_instance_code = mstp1.sr_instance_code'
6520: -- ||' AND NVL(mstp2.company_name, '||''''||NULL_CHAR||''''||') = '
6521: -- ||' NVL(mstp1.company_name, '||''''||NULL_CHAR||''''||')'
6522: ||' AND mstp2.organization_code = mstp1.organization_code'
6553:
6554: --Duplicate records check for the records whose source is XML
6555: v_sql_stmt := 03;
6556: lv_sql_stmt :=
6557: 'UPDATE msc_st_trading_partners mstp1 '
6558: ||' SET process_flag = '||G_ERROR_FLG||','
6559: ||' error_text = '||''''||lv_message_text||''''
6560: ||' WHERE message_id < ( SELECT max(message_id) '
6561: ||' FROM msc_st_trading_partners mstp2'
6557: 'UPDATE msc_st_trading_partners mstp1 '
6558: ||' SET process_flag = '||G_ERROR_FLG||','
6559: ||' error_text = '||''''||lv_message_text||''''
6560: ||' WHERE message_id < ( SELECT max(message_id) '
6561: ||' FROM msc_st_trading_partners mstp2'
6562: ||' WHERE mstp2.sr_instance_code = mstp1.sr_instance_code'
6563: ||' AND NVL(mstp2.company_name, '||''''||NULL_CHAR||''''||') = '
6564: ||' NVL(mstp1.company_name, '||''''||NULL_CHAR||''''||')'
6565: ||' AND mstp2.partner_name = mstp1.partner_name'
6582: USING v_instance_code;
6583:
6584: v_sql_stmt := 04;
6585: lv_sql_stmt :=
6586: 'UPDATE msc_st_trading_partners mstp1 '
6587: ||' SET process_flag = '||G_ERROR_FLG||','
6588: ||' error_text = '||''''||lv_message_text||''''
6589: ||' WHERE message_id < ( SELECT max(message_id) '
6590: ||' FROM msc_st_trading_partners mstp2'
6586: 'UPDATE msc_st_trading_partners mstp1 '
6587: ||' SET process_flag = '||G_ERROR_FLG||','
6588: ||' error_text = '||''''||lv_message_text||''''
6589: ||' WHERE message_id < ( SELECT max(message_id) '
6590: ||' FROM msc_st_trading_partners mstp2'
6591: ||' WHERE mstp2.sr_instance_code = mstp1.sr_instance_code'
6592: -- ||' AND NVL(mstp2.company_name, '||''''||NULL_CHAR||''''||') = '
6593: -- ||' NVL(mstp1.company_name, '||''''||NULL_CHAR||''''||')'
6594: ||' AND mstp2.organization_code = mstp1.organization_code'
6648: FROM DUAL;
6649:
6650: v_sql_stmt := 06;
6651: lv_sql_stmt :=
6652: 'UPDATE msc_st_trading_partners '
6653: ||' SET batch_id = :lv_batch_id '
6654: ||' WHERE process_flag IN ('||G_IN_PROCESS||','||G_ERROR_FLG||')'
6655: ||' AND sr_instance_code = :v_instance_code'
6656: ||' AND NVL(batch_id,'||NULL_VALUE||') = '||NULL_VALUE
6671: CLOSE c1;
6672:
6673: v_sql_stmt := 07;
6674: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
6675: UPDATE msc_st_trading_partners
6676: SET st_transaction_id = msc_st_trading_partners_s.NEXTVAL,
6677: refresh_id = v_refresh_id,
6678: last_update_date = v_current_date,
6679: last_updated_by = v_current_user,
6672:
6673: v_sql_stmt := 07;
6674: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
6675: UPDATE msc_st_trading_partners
6676: SET st_transaction_id = msc_st_trading_partners_s.NEXTVAL,
6677: refresh_id = v_refresh_id,
6678: last_update_date = v_current_date,
6679: last_updated_by = v_current_user,
6680: creation_date = v_current_date,
6686: p_error_code => 'MSC_PP_NO_DELETION',
6687: p_message_text => lv_message_text,
6688: p_error_text => lv_error_text,
6689: p_token1 => 'TABLE_NAME',
6690: p_token_value1 => 'MSC_ST_TRADING_PARTNERS');
6691:
6692: -- Deletion is not allowed for this table
6693: v_sql_stmt := 08;
6694: lv_sql_stmt :=
6691:
6692: -- Deletion is not allowed for this table
6693: v_sql_stmt := 08;
6694: lv_sql_stmt :=
6695: 'UPDATE msc_st_trading_partners '
6696: ||' SET process_flag = '||G_ERROR_FLG||','
6697: ||' error_text = '||''''||lv_message_text||''''
6698: ||' WHERE deleted_flag = '||SYS_YES
6699: ||' AND process_flag = '||G_IN_PROCESS
6728: lv_where_str := ' AND NVL(deleted_flag,'||NULL_VALUE||') '
6729: ||' NOT IN(1,2)';
6730:
6731: lv_return := MSC_ST_UTIL.LOG_ERROR
6732: (p_table_name => 'MSC_ST_TRADING_PARTNERS',
6733: p_instance_code => v_instance_code,
6734: p_row => lv_column_names,
6735: p_severity => G_SEV_WARNING,
6736: p_message_text => lv_message_text,
6759:
6760: -- Error out records where partner_name is NULL
6761: v_sql_stmt := 09;
6762: lv_sql_stmt :=
6763: 'UPDATE msc_st_trading_partners'
6764: ||' SET process_flag = '||G_ERROR_FLG||','
6765: ||' error_text = '||''''||lv_message_text||''''
6766: ||' WHERE NVL(partner_name, '||''''||NULL_CHAR||''''||') '
6767: ||' = '||''''||NULL_CHAR||''''
6791:
6792: -- Error out records where partner_type NOT in (1,2,3,4)
6793: v_sql_stmt := 10;
6794: lv_sql_stmt :=
6795: 'UPDATE msc_st_trading_partners'
6796: ||' SET process_flag = '||G_ERROR_FLG||','
6797: ||' error_text = '||''''||lv_message_text||''''
6798: ||' WHERE sr_instance_code = :v_instance_code'
6799: ||' AND batch_id = :lv_batch_id'
6822:
6823: -- Error out records where organization_code is NULL for partner_type = 3
6824: v_sql_stmt := 11;
6825: lv_sql_stmt :=
6826: 'UPDATE msc_st_trading_partners '
6827: ||' SET process_flag = '||G_ERROR_FLG||','
6828: ||' error_text = '||''''||lv_message_text||''''
6829: ||' WHERE sr_instance_code = :v_instance_code'
6830: ||' AND batch_id = :lv_batch_id'
6852:
6853: -- Resource type validation
6854: v_sql_stmt := 11.1;
6855: lv_sql_stmt :=
6856: 'UPDATE msc_st_trading_partners '
6857: ||' SET process_flag = '||G_ERROR_FLG||','
6858: ||' error_text = '||''''||lv_message_text||''''
6859: ||' WHERE process_flag = '||G_IN_PROCESS
6860: ||' AND batch_id = :lv_batch_id'
6871: v_instance_code;
6872:
6873: v_sql_stmt := 11.2;
6874: lv_sql_stmt :=
6875: 'UPDATE msc_st_trading_partners '
6876: ||' SET RESOURCE_TYPE ='''''
6877: ||' WHERE process_flag = '||G_IN_PROCESS
6878: ||' AND batch_id = :lv_batch_id'
6879: ||' AND sr_instance_code = :v_instance_code'
6904: IF lv_return <> 0 THEN
6905: RAISE ex_logging_err;
6906: END IF;
6907:
6908: UPDATE msc_st_trading_partners mstp
6909: SET process_flag = G_ERROR_FLG,
6910: error_text = lv_message_text
6911: WHERE mstp.process_flag = G_IN_PROCESS
6912: AND mstp.sr_instance_code = v_instance_code
6943:
6944: IF (v_install_msc) THEN
6945: v_sql_stmt := 11;
6946: lv_sql_stmt :=
6947: 'UPDATE msc_st_trading_partners '
6948: ||' SET process_flag = '||G_ERROR_FLG||','
6949: ||' error_text = '||''''||lv_message_text||''''
6950: ||' WHERE sr_instance_code = :v_instance_code'
6951: ||' AND batch_id = :lv_batch_id'
6966: -- Update all partner_name and calendar_code as instance_code concatenated with name
6967: -- Where partner_type = 3 (organization)
6968: v_sql_stmt := 12;
6969: lv_sql_stmt :=
6970: 'UPDATE msc_st_trading_partners '
6971: ||' SET partner_name = sr_instance_code'||'||'':''||'||'partner_name,'
6972: ||' calendar_code = sr_instance_code'||'||'':''||'||'calendar_code'
6973: ||' WHERE partner_type = '||G_ORGANIZATION
6974: ||' AND sr_instance_code = :v_instance_code'
6991: p_token_value1 => 'SR_INSTANCE_ID AND CALENDAR_CODE',
6992: p_token2 => 'MASTER_TABLE',
6993: p_token_value2 => 'MSC_CALENDARS',
6994: p_token3 => 'CHILD_TABLE',
6995: p_token_value3 => 'MSC_ST_TRADING_PARTNERS');
6996: IF lv_return <> 0 THEN
6997: RAISE ex_logging_err;
6998: END IF;
6999:
6999:
7000: -- Validate the calendar code for org
7001: v_sql_stmt := 14;
7002: lv_sql_stmt :=
7003: 'UPDATE msc_st_trading_partners mstp'
7004: ||' SET process_flag = '||G_ERROR_FLG||','
7005: ||' error_text = '||''''||lv_message_text||''''
7006: ||' WHERE NOT EXISTS ( SELECT 1 '
7007: ||' FROM msc_calendars mc '
7038: ' AND calendar_exception_set_id <> -1 '
7039: ||' AND partner_type = '||G_ORGANIZATION ;
7040:
7041: lv_return := MSC_ST_UTIL.LOG_ERROR
7042: (p_table_name => 'MSC_ST_TRADING_PARTNERS',
7043: p_instance_code => v_instance_code,
7044: p_row => lv_column_names,
7045: p_severity => G_SEV_WARNING,
7046: p_message_text => lv_message_text,
7065: p_token_value1 => 'SR_INSTANCE_CODE OR WEIGHT_UOM',
7066: p_token2 => 'MASTER_TABLE',
7067: p_token_value2 => 'MSC_ST_UNITS_OF_MEASURE',
7068: p_token3 => 'CHILD_TABLE',
7069: p_token_value3 => 'MSC_ST_TRADING_PARTNERS');
7070: IF lv_return <> 0 THEN
7071: RAISE ex_logging_err;
7072: END IF;
7073:
7073:
7074: -- Validate all UOM code both Weight/Volume
7075: v_sql_stmt := 15;
7076: lv_sql_stmt :=
7077: 'UPDATE msc_st_trading_partners mstp'
7078: ||' SET process_flag = '||G_ERROR_FLG||','
7079: ||' error_text = '||''''||lv_message_text||''''
7080: ||' WHERE NOT EXISTS(SELECT 1 '
7081: ||' FROM msc_units_of_measure muom '
7113: p_token_value1 => 'SR_INSTANCE_CODE OR VOLUME_UOM',
7114: p_token2 => 'MASTER_TABLE',
7115: p_token_value2 => 'MSC_ST_UNITS_OF_MEASURE',
7116: p_token3 => 'CHILD_TABLE',
7117: p_token_value3 => 'MSC_ST_TRADING_PARTNERS');
7118: IF lv_return <> 0 THEN
7119: RAISE ex_logging_err;
7120: END IF;
7121:
7120: END IF;
7121:
7122: v_sql_stmt := 16;
7123: lv_sql_stmt :=
7124: 'UPDATE msc_st_trading_partners mstp'
7125: ||' SET process_flag = '||G_ERROR_FLG||','
7126: ||' error_text = '||''''||lv_message_text||''''
7127: ||' WHERE NOT EXISTS(SELECT 1 '
7128: ||' FROM msc_units_of_measure muom'
7179: -- Validate Demand Class, if value provided it should exists
7180: -- in ODS or staging table
7181:
7182: lv_return :=
7183: MSC_ST_UTIL.VALIDATE_DMD_CLASS(p_table_name => 'MSC_ST_TRADING_PARTNERS',
7184: p_dmd_class_column => 'DEFAULT_DEMAND_CLASS',
7185: p_instance_id => v_instance_id,
7186: p_instance_code => v_instance_code,
7187: p_severity => G_SEV3_ERROR,
7209: END IF;
7210:
7211: --Derive company_id
7212: lv_return := MSC_ST_UTIL.DERIVE_COMPANY_ID
7213: (p_table_name => 'MSC_ST_TRADING_PARTNERS',
7214: p_company_name => 'COMPANY_NAME',
7215: p_company_id => 'COMPANY_ID',
7216: p_instance_code => v_instance_code,
7217: p_error_text => lv_error_text,
7233: IF v_sce_installed THEN
7234: lv_my_company := GET_MY_COMPANY;
7235: -- if record is for OEM's partners, company name will be null or 'My Company'
7236:
7237: UPDATE msc_st_trading_partners mscu
7238: SET company_id = -1
7239: WHERE exists( SELECT 1
7240: FROM msc_companies mc
7241: WHERE mc.company_name = nvl(mscu.company_name,lv_my_company)
7246: AND mscu.batch_id = lv_batch_id;
7247:
7248: -- if not 'My Company' then get the first occurence of sr_tp_id for this company_name
7249:
7250: UPDATE msc_st_trading_partners mscu
7251: SET company_id = (SELECT local_id from MSC_LOCAL_ID_SETUP
7252: WHERE char1 = mscu.sr_instance_code
7253: and NVL(char3,NULL_CHAR) = NVL(mscu.company_name,NULL_CHAR)
7254: and entity_name = 'SR_TP_ID'
7271: IF lv_return <> 0 THEN
7272: RAISE ex_logging_err;
7273: END IF;
7274:
7275: UPDATE msc_st_trading_partners mscu
7276: SET process_flag = G_ERROR_FLG,
7277: error_text = lv_message_text
7278: WHERE mscu.company_id IS NULL
7279: AND mscu.process_flag = G_IN_PROCESS
7296: END IF;
7297:
7298: -- Derive source_org_id
7299: lv_return :=msc_st_util.derive_partner_org_id
7300: (p_table_name => 'MSC_ST_TRADING_PARTNERS',
7301: p_org_partner_name => 'SOURCE_ORG_CODE',
7302: p_org_partner_id => 'SOURCE_ORG_ID',
7303: p_instance_code => v_instance_code,
7304: p_partner_type => G_ORGANIZATION,
7327: END IF;
7328:
7329: v_sql_stmt := 16.1;
7330: lv_sql_stmt :=
7331: 'UPDATE msc_st_trading_partners mstp'
7332: ||' SET process_flag = '||G_ERROR_FLG||','
7333: ||' error_text = '||''''||lv_message_text||''''
7334: ||' WHERE EXISTS(SELECT 1 '
7335: ||' FROM msc_trading_partners mtp,'
7357: lv_batch_id;
7358:
7359: v_sql_stmt := 16.2;
7360: lv_sql_stmt :=
7361: 'UPDATE msc_st_trading_partners mstp'
7362: ||' SET process_flag = '||G_ERROR_FLG||','
7363: ||' error_text = '||''''||lv_message_text||''''
7364: ||' WHERE EXISTS(SELECT 1 '
7365: ||' FROM msc_trading_partners mtp'
7383: -- ssurendr - bug 2647423: check for partner name - org code mismatch with respect to ODS - end.
7384:
7385: -- Derive sr_tp_id(Organization)
7386: lv_return :=msc_st_util.derive_partner_org_id
7387: (p_table_name => 'MSC_ST_TRADING_PARTNERS',
7388: p_org_partner_name => 'ORGANIZATION_CODE',
7389: p_org_partner_id => 'SR_TP_ID',
7390: p_instance_code => v_instance_code,
7391: p_partner_type => G_ORGANIZATION,
7398: END IF;
7399:
7400: -- Derive sr_tp_id(Customer)
7401: lv_return :=msc_st_util.derive_partner_org_id
7402: (p_table_name => 'MSC_ST_TRADING_PARTNERS',
7403: p_org_partner_name => 'PARTNER_NAME',
7404: p_cust_account_number=>'CUST_ACCOUNT_NUMBER',
7405: p_org_partner_id => 'SR_TP_ID',
7406: p_instance_code => v_instance_code,
7414: END IF;
7415:
7416: -- Derive sr_tp_id(Supplier).
7417: lv_return :=msc_st_util.derive_partner_org_id
7418: (p_table_name => 'MSC_ST_TRADING_PARTNERS',
7419: p_org_partner_name => 'PARTNER_NAME',
7420: p_org_partner_id => 'SR_TP_ID',
7421: p_instance_code => v_instance_code,
7422: p_partner_type => G_VENDOR,
7429: END IF;
7430:
7431: -- Derive sr_tp_id(Carrier).
7432: lv_return :=msc_st_util.derive_partner_org_id
7433: (p_table_name => 'MSC_ST_TRADING_PARTNERS',
7434: p_org_partner_name => 'PARTNER_NAME',
7435: p_org_partner_id => 'SR_TP_ID',
7436: p_instance_code => v_instance_code,
7437: p_partner_type => G_CARRIER,
7465: ||' NOT IN(1,2)'
7466: ||' AND partner_type = '||G_ORGANIZATION ;
7467:
7468: lv_return := MSC_ST_UTIL.LOG_ERROR
7469: (p_table_name => 'MSC_ST_TRADING_PARTNERS',
7470: p_instance_code => v_instance_code,
7471: p_row => lv_column_names,
7472: p_severity => G_SEV_WARNING,
7473: p_message_text => lv_message_text,
7487: (ERRBUF => lv_error_text,
7488: RETCODE => lv_return,
7489: pBatchID => lv_batch_id,
7490: pInstanceCode => v_instance_code,
7491: pEntityName => 'MSC_ST_TRADING_PARTNERS',
7492: pInstanceID => v_instance_id);
7493:
7494: IF NVL(lv_return,0) <> 0 THEN
7495: RAISE ex_logging_err;
7501:
7502: IF c2%ROWCOUNT > 0 THEN
7503: v_sql_stmt := 17;
7504: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
7505: UPDATE msc_st_trading_partners
7506: SET sr_tp_id = msc_st_sr_tp_id_s.NEXTVAL
7507: WHERE rowid = lb_rowid(j);
7508:
7509: -- Insert into the LID table this new partner with the
7544: v_current_date,
7545: v_current_user,
7546: v_current_date,
7547: v_current_user
7548: FROM msc_st_trading_partners
7549: WHERE rowid = lb_rowid(j);
7550:
7551: END IF;
7552: CLOSE c2 ;
7554: -- Update all organization_code as instance_code concatenated with org_code
7555: -- Where partner_type = 3 (organization)
7556: v_sql_stmt := 19;
7557: lv_sql_stmt :=
7558: 'UPDATE msc_st_trading_partners '
7559: ||' SET organization_code = sr_instance_code'||'||'':''||'||'organization_code,'
7560: ||' master_organization = sr_tp_id'
7561: ||' WHERE partner_type = '||G_ORGANIZATION
7562: ||' AND sr_instance_code = :v_instance_code'
7571: lv_batch_id; */
7572:
7573: -- Set the process flag as Valid and populate instance_id
7574: lv_return := MSC_ST_UTIL.SET_PROCESS_FLAG
7575: (p_table_name => 'MSC_ST_TRADING_PARTNERS',
7576: p_instance_id => v_instance_id,
7577: p_instance_code => v_instance_code,
7578: p_process_flag => G_VALID,
7579: p_error_text => lv_error_text,
7587: -- At the end calling the LOG_ERROR for logging all
7588: -- errored out records.
7589:
7590: lv_return := MSC_ST_UTIL.LOG_ERROR
7591: (p_table_name => 'MSC_ST_TRADING_PARTNERS',
7592: p_instance_code => v_instance_code,
7593: p_row => lv_column_names,
7594: p_severity => G_SEV_ERROR,
7595: p_message_text => NULL,
32360: v_sql_stmt := 29;
32361: lv_sql_stmt :=
32362: 'UPDATE msc_st_resource_shifts msrs'
32363: ||' SET shift_num =(SELECT mlis.local_id'
32364: ||' FROM msc_local_id_setup mlis, msc_st_trading_partners mtp '
32365: ||' WHERE mtp.sr_instance_id = '||v_instance_id
32366: ||' AND mlis.char1 = msrs.sr_instance_code'
32367: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '
32368: ||' = NVL(msrs.company_name,'||''''||NULL_CHAR||''''||') '
32938:
32939: lv_sql_stmt :=
32940: 'UPDATE msc_st_resource_changes msrc'
32941: ||' SET shift_num =(SELECT mlis.local_id'
32942: ||' FROM msc_local_id_setup mlis, msc_st_trading_partners mtp '
32943: ||' WHERE mtp.sr_instance_id = '||v_instance_id
32944: ||' AND mlis.char1 = msrc.sr_instance_code'
32945: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '
32946: ||' = NVL(msrc.company_name,'||''''||NULL_CHAR||''''||') '
54050:
54051:
54052: /*==========================================================================+
54053: | DESCRIPTION : This procedure generates records in msc_st_bis_periods if |
54054: | records are coming into msc_st_trading_partners and/or |
54055: | records are coming in msc_st_calendars. |
54056: +==========================================================================*/
54057: PROCEDURE LOAD_BIS_PERIODS IS
54058: lv_error_text VARCHAR2(250);
54162: v_current_date,
54163: v_current_user,
54164: v_current_date,
54165: v_current_user
54166: FROM msc_period_start_dates mpsd,msc_st_trading_partners mtp
54167: WHERE mpsd.calendar_code = mtp.calendar_code
54168: AND mpsd.sr_instance_id = mtp.sr_instance_id
54169: AND mpsd.period_start_date <> mpsd.next_date -- bug 8591107
54170: AND mtp.partner_type = G_ORGANIZATION
55926: IF lv_count > 0 Then
55927: prec.tp_customer_flag:= SYS_YES;
55928: prec.tp_vendor_flag:= SYS_YES;
55929: End IF;
55930: lv_count:= SET_IN_PROCESS (p_table_name => 'MSC_ST_TRADING_PARTNERS');
55931: IF lv_count > 0 Then
55932: prec.tp_customer_flag:= SYS_YES;
55933: prec.tp_vendor_flag:= SYS_YES;
55934: prec.sourcing_rule_flag:= SYS_YES;