[Home] [Help]
1132: application_short_name => 'MSC',
1133: statement_type => AD_DDL.CREATE_INDEX,
1134: statement =>
1135: 'create index MSC_ST_LOC_ASC_N1_'||v_instance_code
1136: ||' on MSC_ST_LOCATION_ASSOCIATIONS '
1137: ||'(sr_instance_code, location_code, tp_site_code, partner_name, partner_type, company_name) '
1138: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
1139: object_name =>'MSC_ST_LOC_ASC_N1_'||v_instance_code);
1140:
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:
1214: END IF;
1215:
6297: AND batch_id = p_batch_id;
6298:
6299: CURSOR c6(p_batch_id NUMBER) IS
6300: SELECT rowid
6301: FROM msc_st_location_associations
6302: WHERE process_flag IN (G_IN_PROCESS,G_ERROR_FLG)
6303: AND sr_instance_code = v_instance_code
6304: AND batch_id = p_batch_id;
6305:
6412: ----
6413:
6414: CURSOR c14(p_batch_id NUMBER) IS
6415: SELECT max(rowid)
6416: FROM msc_st_location_associations
6417: WHERE process_flag = G_IN_PROCESS
6418: AND deleted_flag = SYS_NO
6419: AND NVL(location_id,NULL_VALUE) = NULL_VALUE
6420: AND sr_instance_code = v_instance_code
8766: IF lv_return <> 0 THEN
8767: RAISE ex_logging_err;
8768: END IF;
8769:
8770: --Validation check for the table MSC_ST_LOCATION_ASSOCIATIONS
8771:
8772: --Duplicate records check for the records whose source is other than XML
8773: --Different SQL is used because in XML we can identify the latest records
8774: --whereas in batch load we cannot.
8774: --whereas in batch load we cannot.
8775:
8776: v_sql_stmt := 34;
8777: lv_sql_stmt :=
8778: 'UPDATE msc_st_location_associations mla1 '
8779: ||' SET process_flag = '||G_ERROR_FLG||','
8780: ||' error_text = '||''''||lv_message_text||''''
8781: ||' WHERE EXISTS ( SELECT 1 FROM msc_st_location_associations mla2 '
8782: ||' WHERE mla2.sr_instance_code = mla1.sr_instance_code'
8777: lv_sql_stmt :=
8778: 'UPDATE msc_st_location_associations mla1 '
8779: ||' SET process_flag = '||G_ERROR_FLG||','
8780: ||' error_text = '||''''||lv_message_text||''''
8781: ||' WHERE EXISTS ( SELECT 1 FROM msc_st_location_associations mla2 '
8782: ||' WHERE mla2.sr_instance_code = mla1.sr_instance_code'
8783: ||' AND NVL(mla2.company_name, '||''''||NULL_CHAR||''''||') = '
8784: ||' NVL(mla1.company_name, '||''''||NULL_CHAR||''''||')'
8785: ||' AND mla2.partner_name = mla1.partner_name '
8815:
8816: --Duplicate records check for the records whose source is XML
8817: v_sql_stmt := 35;
8818: lv_sql_stmt :=
8819: 'UPDATE msc_st_location_associations mla1 '
8820: ||' SET process_flag = '||G_ERROR_FLG||','
8821: ||' error_text = '||''''||lv_message_text||''''
8822: ||' WHERE message_id < ( SELECT max(message_id) '
8823: ||' FROM msc_st_location_associations mla2 '
8819: 'UPDATE msc_st_location_associations mla1 '
8820: ||' SET process_flag = '||G_ERROR_FLG||','
8821: ||' error_text = '||''''||lv_message_text||''''
8822: ||' WHERE message_id < ( SELECT max(message_id) '
8823: ||' FROM msc_st_location_associations mla2 '
8824: ||' WHERE mla2.sr_instance_code = mla1.sr_instance_code'
8825: ||' AND NVL(mla2.company_name, '||''''||NULL_CHAR||''''||') = '
8826: ||' NVL(mla1.company_name, '||''''||NULL_CHAR||''''||')'
8827: ||' AND mla2.partner_name = mla1.partner_name '
8860: FROM DUAL;
8861:
8862: v_sql_stmt := 37;
8863: lv_sql_stmt :=
8864: 'UPDATE msc_st_location_associations '
8865: ||' SET batch_id = :lv_batch_id '
8866: ||' WHERE process_flag IN ('||G_IN_PROCESS||','||G_ERROR_FLG||')'
8867: ||' AND sr_instance_code = :v_instance_code'
8868: ||' AND NVL(batch_id,'||NULL_VALUE||') = '||NULL_VALUE
8882: CLOSE c6;
8883:
8884: v_sql_stmt := 38;
8885: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
8886: UPDATE msc_st_location_associations
8887: SET st_transaction_id = msc_st_location_associations_s.NEXTVAL,
8888: refresh_id = v_refresh_id,
8889: last_update_date = v_current_date,
8890: last_updated_by = v_current_user,
8883:
8884: v_sql_stmt := 38;
8885: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
8886: UPDATE msc_st_location_associations
8887: SET st_transaction_id = msc_st_location_associations_s.NEXTVAL,
8888: refresh_id = v_refresh_id,
8889: last_update_date = v_current_date,
8890: last_updated_by = v_current_user,
8891: creation_date = v_current_date,
8897: p_error_code => 'MSC_PP_NO_DELETION',
8898: p_message_text => lv_message_text,
8899: p_error_text => lv_error_text,
8900: p_token1 => 'TABLE_NAME',
8901: p_token_value1 => 'MSC_ST_LOCATION_ASSOCIATIONS');
8902:
8903: -- Deletion is not allowed for this table
8904: v_sql_stmt := 39;
8905: lv_sql_stmt :=
8902:
8903: -- Deletion is not allowed for this table
8904: v_sql_stmt := 39;
8905: lv_sql_stmt :=
8906: 'UPDATE msc_st_location_associations '
8907: ||' SET process_flag = '||G_ERROR_FLG||','
8908: ||' error_text = '||''''||lv_message_text||''''
8909: ||' WHERE deleted_flag = '||SYS_YES
8910: ||' AND process_flag = '||G_IN_PROCESS
8939: lv_where_str := ' AND NVL(deleted_flag,'||NULL_VALUE||') '
8940: ||' NOT IN(1,2)';
8941:
8942: lv_return := MSC_ST_UTIL.LOG_ERROR
8943: (p_table_name => 'MSC_ST_LOCATION_ASSOCIATIONS',
8944: p_instance_code => v_instance_code,
8945: p_row => lv_column_names,
8946: p_severity => G_SEV_WARNING,
8947: p_message_text => lv_message_text,
8970:
8971: -- Error out records where partner_name/tp_site_code/location_code is NULL
8972: v_sql_stmt := 40;
8973: lv_sql_stmt :=
8974: 'UPDATE msc_st_location_associations'
8975: ||' SET process_flag = '||G_ERROR_FLG||','
8976: ||' error_text = '||''''||lv_message_text||''''
8977: ||' WHERE (NVL(partner_name,'||''''||NULL_CHAR||''''||') '
8978: ||' = '||''''||NULL_CHAR||''''
9006:
9007: -- Error out records where partner_type NOT in (1,2,3)
9008: v_sql_stmt := 41;
9009: lv_sql_stmt :=
9010: 'UPDATE msc_st_location_associations'
9011: ||' SET process_flag = '||G_ERROR_FLG||','
9012: ||' error_text = '||''''||lv_message_text||''''
9013: ||' WHERE sr_instance_code = :v_instance_code'
9014: ||' AND batch_id = :lv_batch_id'
9034: END IF;
9035:
9036: -- Update organization_id
9037: lv_return :=msc_st_util.derive_partner_org_id
9038: (p_table_name =>'MSC_ST_LOCATION_ASSOCIATIONS',
9039: p_org_partner_name =>'ORGANIZATION_CODE',
9040: p_org_partner_id =>'ORGANIZATION_ID',
9041: p_instance_code => v_instance_code,
9042: p_partner_type => G_ORGANIZATION,
9052:
9053: --Update sr_tp_id (organizations)
9054: v_sql_stmt := 41.1;
9055: lv_sql_stmt :=
9056: 'UPDATE msc_st_location_associations mla'
9057: ||' SET SR_TP_ID = ORGANIZATION_ID'
9058: ||' WHERE process_flag = '||G_IN_PROCESS
9059: ||' AND partner_type = '||G_ORGANIZATION
9060: ||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
9084: END IF;
9085:
9086: -- Update sr_tp_id(customer)
9087: lv_return :=msc_st_util.derive_partner_org_id
9088: (p_table_name =>'MSC_ST_LOCATION_ASSOCIATIONS',
9089: p_org_partner_name =>'PARTNER_NAME',
9090: p_org_partner_id =>'SR_TP_ID',
9091: p_instance_code => v_instance_code,
9092: p_partner_type => G_CUSTOMER,
9099: END IF;
9100:
9101: -- Update sr_tp_id(supplier)
9102: lv_return :=msc_st_util.derive_partner_org_id
9103: (p_table_name =>'MSC_ST_LOCATION_ASSOCIATIONS',
9104: p_org_partner_name =>'PARTNER_NAME',
9105: p_org_partner_id =>'SR_TP_ID',
9106: p_instance_code => v_instance_code,
9107: p_partner_type => G_VENDOR,
9129: END IF;
9130:
9131: -- Derive sr_tp_site_id(Customer)
9132: lv_return := MSC_ST_UTIL.DERIVE_PARTNER_SITE_ID
9133: (p_table_name => 'MSC_ST_LOCATION_ASSOCIATIONS',
9134: p_partner_name => 'PARTNER_NAME',
9135: p_partner_site_code => 'TP_SITE_CODE',
9136: p_partner_site_id => 'SR_TP_SITE_ID',
9137: p_instance_code => v_instance_code,
9148: END IF;
9149:
9150: -- Derive sr_tp_site_id(Supplier)
9151: lv_return := MSC_ST_UTIL.DERIVE_PARTNER_SITE_ID
9152: (p_table_name => 'MSC_ST_LOCATION_ASSOCIATIONS',
9153: p_partner_name => 'PARTNER_NAME',
9154: p_partner_site_code => 'TP_SITE_CODE',
9155: p_partner_site_id => 'SR_TP_SITE_ID',
9156: p_instance_code => v_instance_code,
9169:
9170: --Deriving location_id
9171: v_sql_stmt := 42;
9172: lv_sql_stmt :=
9173: 'UPDATE msc_st_location_associations mla'
9174: ||' SET location_id = (SELECT local_id'
9175: ||' FROM msc_local_id_setup mls'
9176: ||' WHERE mls.char1 = mla.sr_instance_code'
9177: ||' AND mls.char3 = mla.location_code'
9202:
9203: -- Error out the record if location_id is NULL
9204: v_sql_stmt := 43;
9205: lv_sql_stmt :=
9206: 'UPDATE msc_st_location_associations'
9207: ||' SET process_flag = '||G_ERROR_FLG||','
9208: ||' error_text = '||''''||lv_message_text||''''
9209: ||' WHERE NVL(location_id,'||NULL_VALUE||')= '||NULL_VALUE
9210: ||' AND batch_id = :lv_batch_id'
9229: (ERRBUF => lv_error_text,
9230: RETCODE => lv_return,
9231: pBatchID => lv_batch_id,
9232: pInstanceCode => v_instance_code,
9233: pEntityName => 'MSC_ST_LOCATION_ASSOCIATIONS',
9234: pInstanceID => v_instance_id);
9235:
9236: IF NVL(lv_return,0) <> 0 THEN
9237: RAISE ex_logging_err;
9245:
9246: IF c14%ROWCOUNT > 0 THEN
9247: v_sql_stmt := 43.1;
9248: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
9249: UPDATE msc_st_location_associations
9250: SET location_id = msc_st_location_id_s.NEXTVAL
9251: WHERE rowid = lb_rowid(j);
9252:
9253: -- Insert record in LID
9280: v_current_date,
9281: v_current_user,
9282: v_current_date,
9283: v_current_user
9284: FROM msc_st_location_associations
9285: WHERE rowid = lb_rowid(j);
9286: END IF;
9287: CLOSE C14;
9288:
9288:
9289: --Deriving location_id
9290: v_sql_stmt := 43.3;
9291: lv_sql_stmt :=
9292: 'UPDATE msc_st_location_associations mla'
9293: ||' SET location_id = (SELECT local_id'
9294: ||' FROM msc_local_id_setup mls'
9295: ||' WHERE mls.char1 = mla.sr_instance_code'
9296: ||' AND mls.char3 = mla.location_code'
9311:
9312: -- Update partner_site_id with location id for orgs
9313: v_sql_stmt := 43.4;
9314: lv_sql_stmt :=
9315: 'UPDATE msc_st_location_associations'
9316: ||' SET partner_site_id = location_id'
9317: ||' WHERE NVL(partner_site_id,'||NULL_VALUE||')= '||NULL_VALUE
9318: ||' AND batch_id = :lv_batch_id'
9319: ||' AND partner_type = '||G_ORGANIZATION
9328:
9329:
9330: -- Set the process flag as Valid and populate instance_id
9331: lv_return := MSC_ST_UTIL.SET_PROCESS_FLAG
9332: (p_table_name => 'MSC_ST_LOCATION_ASSOCIATIONS',
9333: p_instance_id => v_instance_id,
9334: p_instance_code => v_instance_code,
9335: p_process_flag => G_VALID,
9336: p_error_text => lv_error_text,
9343:
9344: -- At the end calling the LOG_ERROR for logging all
9345: -- errored out records.
9346: lv_return := MSC_ST_UTIL.LOG_ERROR
9347: (p_table_name => 'MSC_ST_LOCATION_ASSOCIATIONS',
9348: p_instance_code => v_instance_code,
9349: p_row => lv_column_names,
9350: p_severity => G_SEV_ERROR,
9351: p_message_text => NULL,
50989: End IF;
50990: END IF;
50991:
50992: IF v_tp_enabled = SYS_YES THEN
50993: lv_count:= SET_IN_PROCESS (p_table_name => 'MSC_ST_LOCATION_ASSOCIATIONS');
50994: IF lv_count > 0 Then
50995: prec.tp_customer_flag:= SYS_YES;
50996: prec.tp_vendor_flag:= SYS_YES;
50997: End IF;