[Home] [Help]
1140: application_short_name => 'MSC',
1141: statement_type => AD_DDL.CREATE_INDEX,
1142: statement =>
1143: 'create index MSC_ST_LOC_ASC_N1_'||v_instance_code
1144: ||' on MSC_ST_LOCATION_ASSOCIATIONS '
1145: ||'(sr_instance_code, location_code, tp_site_code, partner_name, partner_type, company_name) '
1146: ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
1147: object_name =>'MSC_ST_LOC_ASC_N1_'||v_instance_code);
1148:
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);
1221:
1222: END IF;
1223:
6304: AND batch_id = p_batch_id;
6305:
6306: CURSOR c6(p_batch_id NUMBER) IS
6307: SELECT rowid
6308: FROM msc_st_location_associations
6309: WHERE process_flag IN (G_IN_PROCESS,G_ERROR_FLG)
6310: AND sr_instance_code = v_instance_code
6311: AND batch_id = p_batch_id;
6312:
6419: ----
6420:
6421: CURSOR c14(p_batch_id NUMBER) IS
6422: SELECT max(rowid)
6423: FROM msc_st_location_associations
6424: WHERE process_flag = G_IN_PROCESS
6425: AND deleted_flag = SYS_NO
6426: AND NVL(location_id,NULL_VALUE) = NULL_VALUE
6427: AND sr_instance_code = v_instance_code
8892: IF lv_return <> 0 THEN
8893: RAISE ex_logging_err;
8894: END IF;
8895:
8896: --Validation check for the table MSC_ST_LOCATION_ASSOCIATIONS
8897:
8898: --Duplicate records check for the records whose source is other than XML
8899: --Different SQL is used because in XML we can identify the latest records
8900: --whereas in batch load we cannot.
8900: --whereas in batch load we cannot.
8901:
8902: v_sql_stmt := 34;
8903: lv_sql_stmt :=
8904: 'UPDATE msc_st_location_associations mla1 '
8905: ||' SET process_flag = '||G_ERROR_FLG||','
8906: ||' error_text = '||''''||lv_message_text||''''
8907: ||' WHERE EXISTS ( SELECT 1 FROM msc_st_location_associations mla2 '
8908: ||' WHERE mla2.sr_instance_code = mla1.sr_instance_code'
8903: lv_sql_stmt :=
8904: 'UPDATE msc_st_location_associations mla1 '
8905: ||' SET process_flag = '||G_ERROR_FLG||','
8906: ||' error_text = '||''''||lv_message_text||''''
8907: ||' WHERE EXISTS ( SELECT 1 FROM msc_st_location_associations mla2 '
8908: ||' WHERE mla2.sr_instance_code = mla1.sr_instance_code'
8909: ||' AND NVL(mla2.company_name, '||''''||NULL_CHAR||''''||') = '
8910: ||' NVL(mla1.company_name, '||''''||NULL_CHAR||''''||')'
8911: ||' AND mla2.partner_name = mla1.partner_name '
8941:
8942: --Duplicate records check for the records whose source is XML
8943: v_sql_stmt := 35;
8944: lv_sql_stmt :=
8945: 'UPDATE msc_st_location_associations mla1 '
8946: ||' SET process_flag = '||G_ERROR_FLG||','
8947: ||' error_text = '||''''||lv_message_text||''''
8948: ||' WHERE message_id < ( SELECT max(message_id) '
8949: ||' FROM msc_st_location_associations mla2 '
8945: 'UPDATE msc_st_location_associations mla1 '
8946: ||' SET process_flag = '||G_ERROR_FLG||','
8947: ||' error_text = '||''''||lv_message_text||''''
8948: ||' WHERE message_id < ( SELECT max(message_id) '
8949: ||' FROM msc_st_location_associations mla2 '
8950: ||' WHERE mla2.sr_instance_code = mla1.sr_instance_code'
8951: ||' AND NVL(mla2.company_name, '||''''||NULL_CHAR||''''||') = '
8952: ||' NVL(mla1.company_name, '||''''||NULL_CHAR||''''||')'
8953: ||' AND mla2.partner_name = mla1.partner_name '
8986: FROM DUAL;
8987:
8988: v_sql_stmt := 37;
8989: lv_sql_stmt :=
8990: 'UPDATE msc_st_location_associations '
8991: ||' SET batch_id = :lv_batch_id '
8992: ||' WHERE process_flag IN ('||G_IN_PROCESS||','||G_ERROR_FLG||')'
8993: ||' AND sr_instance_code = :v_instance_code'
8994: ||' AND NVL(batch_id,'||NULL_VALUE||') = '||NULL_VALUE
9008: CLOSE c6;
9009:
9010: v_sql_stmt := 38;
9011: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
9012: UPDATE msc_st_location_associations
9013: SET st_transaction_id = msc_st_location_associations_s.NEXTVAL,
9014: refresh_id = v_refresh_id,
9015: last_update_date = v_current_date,
9016: last_updated_by = v_current_user,
9009:
9010: v_sql_stmt := 38;
9011: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
9012: UPDATE msc_st_location_associations
9013: SET st_transaction_id = msc_st_location_associations_s.NEXTVAL,
9014: refresh_id = v_refresh_id,
9015: last_update_date = v_current_date,
9016: last_updated_by = v_current_user,
9017: creation_date = v_current_date,
9023: p_error_code => 'MSC_PP_NO_DELETION',
9024: p_message_text => lv_message_text,
9025: p_error_text => lv_error_text,
9026: p_token1 => 'TABLE_NAME',
9027: p_token_value1 => 'MSC_ST_LOCATION_ASSOCIATIONS');
9028:
9029: -- Deletion is not allowed for this table
9030: v_sql_stmt := 39;
9031: lv_sql_stmt :=
9028:
9029: -- Deletion is not allowed for this table
9030: v_sql_stmt := 39;
9031: lv_sql_stmt :=
9032: 'UPDATE msc_st_location_associations '
9033: ||' SET process_flag = '||G_ERROR_FLG||','
9034: ||' error_text = '||''''||lv_message_text||''''
9035: ||' WHERE deleted_flag = '||SYS_YES
9036: ||' AND process_flag = '||G_IN_PROCESS
9065: lv_where_str := ' AND NVL(deleted_flag,'||NULL_VALUE||') '
9066: ||' NOT IN(1,2)';
9067:
9068: lv_return := MSC_ST_UTIL.LOG_ERROR
9069: (p_table_name => 'MSC_ST_LOCATION_ASSOCIATIONS',
9070: p_instance_code => v_instance_code,
9071: p_row => lv_column_names,
9072: p_severity => G_SEV_WARNING,
9073: p_message_text => lv_message_text,
9096:
9097: -- Error out records where partner_name/tp_site_code/location_code is NULL
9098: v_sql_stmt := 40;
9099: lv_sql_stmt :=
9100: 'UPDATE msc_st_location_associations'
9101: ||' SET process_flag = '||G_ERROR_FLG||','
9102: ||' error_text = '||''''||lv_message_text||''''
9103: ||' WHERE (NVL(partner_name,'||''''||NULL_CHAR||''''||') '
9104: ||' = '||''''||NULL_CHAR||''''
9132:
9133: -- Error out records where partner_type NOT in (1,2,3)
9134: v_sql_stmt := 41;
9135: lv_sql_stmt :=
9136: 'UPDATE msc_st_location_associations'
9137: ||' SET process_flag = '||G_ERROR_FLG||','
9138: ||' error_text = '||''''||lv_message_text||''''
9139: ||' WHERE sr_instance_code = :v_instance_code'
9140: ||' AND batch_id = :lv_batch_id'
9160: END IF;
9161:
9162: -- Update organization_id
9163: lv_return :=msc_st_util.derive_partner_org_id
9164: (p_table_name =>'MSC_ST_LOCATION_ASSOCIATIONS',
9165: p_org_partner_name =>'ORGANIZATION_CODE',
9166: p_org_partner_id =>'ORGANIZATION_ID',
9167: p_instance_code => v_instance_code,
9168: p_partner_type => G_ORGANIZATION,
9178:
9179: --Update sr_tp_id (organizations)
9180: v_sql_stmt := 41.1;
9181: lv_sql_stmt :=
9182: 'UPDATE msc_st_location_associations mla'
9183: ||' SET SR_TP_ID = ORGANIZATION_ID'
9184: ||' WHERE process_flag = '||G_IN_PROCESS
9185: ||' AND partner_type = '||G_ORGANIZATION
9186: ||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
9210: END IF;
9211:
9212: -- Update sr_tp_id(customer)
9213: lv_return :=msc_st_util.derive_partner_org_id
9214: (p_table_name =>'MSC_ST_LOCATION_ASSOCIATIONS',
9215: p_org_partner_name =>'PARTNER_NAME',
9216: p_org_partner_id =>'SR_TP_ID',
9217: p_instance_code => v_instance_code,
9218: p_partner_type => G_CUSTOMER,
9225: END IF;
9226:
9227: -- Update sr_tp_id(supplier)
9228: lv_return :=msc_st_util.derive_partner_org_id
9229: (p_table_name =>'MSC_ST_LOCATION_ASSOCIATIONS',
9230: p_org_partner_name =>'PARTNER_NAME',
9231: p_org_partner_id =>'SR_TP_ID',
9232: p_instance_code => v_instance_code,
9233: p_partner_type => G_VENDOR,
9255: END IF;
9256:
9257: -- Derive sr_tp_site_id(Customer)
9258: lv_return := MSC_ST_UTIL.DERIVE_PARTNER_SITE_ID
9259: (p_table_name => 'MSC_ST_LOCATION_ASSOCIATIONS',
9260: p_partner_name => 'PARTNER_NAME',
9261: p_partner_site_code => 'TP_SITE_CODE',
9262: p_partner_site_id => 'SR_TP_SITE_ID',
9263: p_instance_code => v_instance_code,
9274: END IF;
9275:
9276: -- Derive sr_tp_site_id(Supplier)
9277: lv_return := MSC_ST_UTIL.DERIVE_PARTNER_SITE_ID
9278: (p_table_name => 'MSC_ST_LOCATION_ASSOCIATIONS',
9279: p_partner_name => 'PARTNER_NAME',
9280: p_partner_site_code => 'TP_SITE_CODE',
9281: p_partner_site_id => 'SR_TP_SITE_ID',
9282: p_instance_code => v_instance_code,
9295:
9296: --Deriving location_id
9297: v_sql_stmt := 42;
9298: lv_sql_stmt :=
9299: 'UPDATE msc_st_location_associations mla'
9300: ||' SET location_id = (SELECT local_id'
9301: ||' FROM msc_local_id_setup mls'
9302: ||' WHERE mls.char1 = mla.sr_instance_code'
9303: ||' AND mls.char3 = mla.location_code'
9328:
9329: -- Error out the record if location_id is NULL
9330: v_sql_stmt := 43;
9331: lv_sql_stmt :=
9332: 'UPDATE msc_st_location_associations'
9333: ||' SET process_flag = '||G_ERROR_FLG||','
9334: ||' error_text = '||''''||lv_message_text||''''
9335: ||' WHERE NVL(location_id,'||NULL_VALUE||')= '||NULL_VALUE
9336: ||' AND batch_id = :lv_batch_id'
9355: (ERRBUF => lv_error_text,
9356: RETCODE => lv_return,
9357: pBatchID => lv_batch_id,
9358: pInstanceCode => v_instance_code,
9359: pEntityName => 'MSC_ST_LOCATION_ASSOCIATIONS',
9360: pInstanceID => v_instance_id);
9361:
9362: IF NVL(lv_return,0) <> 0 THEN
9363: RAISE ex_logging_err;
9371:
9372: IF c14%ROWCOUNT > 0 THEN
9373: v_sql_stmt := 43.1;
9374: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
9375: UPDATE msc_st_location_associations
9376: SET location_id = msc_st_location_id_s.NEXTVAL
9377: WHERE rowid = lb_rowid(j);
9378:
9379: -- Insert record in LID
9406: v_current_date,
9407: v_current_user,
9408: v_current_date,
9409: v_current_user
9410: FROM msc_st_location_associations
9411: WHERE rowid = lb_rowid(j);
9412: END IF;
9413: CLOSE C14;
9414:
9414:
9415: --Deriving location_id
9416: v_sql_stmt := 43.3;
9417: lv_sql_stmt :=
9418: 'UPDATE msc_st_location_associations mla'
9419: ||' SET location_id = (SELECT local_id'
9420: ||' FROM msc_local_id_setup mls'
9421: ||' WHERE mls.char1 = mla.sr_instance_code'
9422: ||' AND mls.char3 = mla.location_code'
9437:
9438: -- Update partner_site_id with location id for orgs
9439: v_sql_stmt := 43.4;
9440: lv_sql_stmt :=
9441: 'UPDATE msc_st_location_associations'
9442: ||' SET partner_site_id = location_id'
9443: ||' WHERE NVL(partner_site_id,'||NULL_VALUE||')= '||NULL_VALUE
9444: ||' AND batch_id = :lv_batch_id'
9445: ||' AND partner_type = '||G_ORGANIZATION
9454:
9455:
9456: -- Set the process flag as Valid and populate instance_id
9457: lv_return := MSC_ST_UTIL.SET_PROCESS_FLAG
9458: (p_table_name => 'MSC_ST_LOCATION_ASSOCIATIONS',
9459: p_instance_id => v_instance_id,
9460: p_instance_code => v_instance_code,
9461: p_process_flag => G_VALID,
9462: p_error_text => lv_error_text,
9469:
9470: -- At the end calling the LOG_ERROR for logging all
9471: -- errored out records.
9472: lv_return := MSC_ST_UTIL.LOG_ERROR
9473: (p_table_name => 'MSC_ST_LOCATION_ASSOCIATIONS',
9474: p_instance_code => v_instance_code,
9475: p_row => lv_column_names,
9476: p_severity => G_SEV_ERROR,
9477: p_message_text => NULL,
55911: End IF;
55912: END IF;
55913:
55914: IF v_tp_enabled = SYS_YES THEN
55915: lv_count:= SET_IN_PROCESS (p_table_name => 'MSC_ST_LOCATION_ASSOCIATIONS');
55916: IF lv_count > 0 Then
55917: prec.tp_customer_flag:= SYS_YES;
55918: prec.tp_vendor_flag:= SYS_YES;
55919: End IF;