[Home] [Help]
2776: END;
2777:
2778: END IF;
2779:
2780: msc_analyse_tables_pk.analyse_table( 'MSC_LOCAL_ID_SETUP', v_instance_id, -1);
2781: msc_analyse_tables_pk.analyse_table( 'MSC_LOCAL_ID_MISC', v_instance_id, -1);
2782:
2783:
2784: EXCEPTION
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
3223: AND NVL(char2,NULL_CHAR) = NVL(company_name,NULL_CHAR)
3224: AND char3 = modeled_customer_name
3225: AND number1 = G_CUSTOMER
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
3238: AND NVL(char2,NULL_CHAR) = NVL(company_name,NULL_CHAR)
3239: AND char3 = modeled_supplier_name
3240: AND number1 = G_VENDOR
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
3253: AND NVL(char2,NULL_CHAR) = NVL(company_name,NULL_CHAR)
3254: AND char3 = modeled_customer_name
3255: AND char4 = modeled_customer_site_code
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
3269: AND NVL(char2,NULL_CHAR) = NVL(company_name,NULL_CHAR)
3270: AND char3 = modeled_supplier_name
3271: AND char4 = modeled_supplier_site_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
3315: AND NVL(char2,NULL_CHAR) = NVL(company_name,NULL_CHAR)
3316: AND char3 = master_organization_code
3317: AND number1 = G_ORGANIZATION
3651:
3652: IF p_shift_exist = SYS_NO THEN
3653: v_sql_stmt := 2;
3654: lv_sql_stmt :=
3655: 'INSERT INTO msc_local_id_setup'
3656: ||' (local_id, '
3657: ||' st_transaction_id,'
3658: ||' instance_id,'
3659: ||' entity_name,'
4423: BEGIN
4424: v_sql_stmt := 12;
4425: lv_sql_stmt :=
4426: 'SELECT local_id '
4427: ||' FROM msc_local_id_setup'
4428: ||' WHERE char1 = :v_instance_code'
4429: ||' AND NVL(char2, '||''''||NULL_CHAR||''''||') = '
4430: ||' NVL(:company_name,'||''''||NULL_CHAR||''''||') '
4431: ||' AND char3 = :calendar_code'
6917: WHERE mstp.organization_code = mtp.organization_code
6918: AND mtp.sr_instance_id = v_instance_id
6919: AND mtp.partner_type = G_ORGANIZATION
6920: MINUS
6921: SELECT mlis.char3 from msc_local_id_setup mlis
6922: WHERE mlis.entity_name = 'SR_TP_ID'
6923: AND mlis.char1 = mstp.sr_instance_code
6924: AND mlis.number1 = mstp.partner_type
6925: AND nvl(mlis.char2, NULL_CHAR) = nvl(mstp.company_name,NULL_CHAR));
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'
7255: and rownum = 1) -- we need the first occurence of sr_tp_id
7510: -- user defined key
7511:
7512: v_sql_stmt := 18;
7513: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
7514: INSERT INTO msc_local_id_setup
7515: (local_id,
7516: st_transaction_id,
7517: instance_id,
7518: entity_name,
8171: v_sql_stmt := 28;
8172: lv_sql_stmt :=
8173: 'UPDATE msc_st_trading_partner_sites mts'
8174: ||' SET sr_tp_site_id = (SELECT local_id'
8175: ||' FROM msc_local_id_setup mls'
8176: ||' WHERE mls.char1 = mts.sr_instance_code'
8177: ||' AND mls.char3 = mts.location'
8178: ||' AND mls.entity_name = ''LOCATION_ID'' )'
8179: ||' WHERE process_flag = '||G_IN_PROCESS
8199:
8200: v_sql_stmt := 29;
8201: v_sql_stmt := 29;
8202: lv_sql_stmt :=
8203: 'UPDATE msc_local_id_setup mls'
8204: ||' SET mls.char4 = NVL( (select mts.location '
8205: ||' FROM msc_st_trading_partner_sites mts'
8206: ||' WHERE mls.char1 = mts.sr_instance_code'
8207: ||' AND NVL(mls.char2, '||''''||NULL_CHAR||''''||') = '
8250:
8251: -- Insert record in LID
8252: v_sql_stmt := 30;
8253: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
8254: INSERT INTO msc_local_id_setup
8255: (local_id,
8256: st_transaction_id,
8257: instance_id,
8258: entity_name,
8305:
8306: -- Insert record in LID
8307: v_sql_stmt := 32;
8308: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
8309: INSERT INTO msc_local_id_setup
8310: (local_id,
8311: st_transaction_id,
8312: instance_id,
8313: entity_name,
8343: v_sql_stmt := 32.1;
8344: lv_sql_stmt :=
8345: 'UPDATE msc_st_trading_partner_sites mts'
8346: ||' SET sr_tp_site_id = (SELECT local_id'
8347: ||' FROM msc_local_id_setup mls'
8348: ||' WHERE mls.char1 = mts.sr_instance_code'
8349: ||' AND mls.char3 = mts.location'
8350: ||' AND mls.entity_name = ''LOCATION_ID'' )'
8351: ||' WHERE process_flag = '||G_IN_PROCESS
8390: v_sql_stmt := 32.2;
8391: lv_sql_stmt :=
8392: 'UPDATE msc_st_trading_partner_sites mts'
8393: ||' SET location_id = (SELECT local_id'
8394: ||' FROM msc_local_id_setup mls'
8395: ||' WHERE mls.char1 = mts.sr_instance_code'
8396: ||' AND mls.char3 = mts.location'
8397: ||' AND mls.entity_name = ''LOCATION_ID'' )'
8398: ||' WHERE process_flag = '||G_IN_PROCESS
8424:
8425: -- Insert record in LID
8426: v_sql_stmt := 32.4;
8427: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
8428: INSERT INTO msc_local_id_setup
8429: (local_id,
8430: st_transaction_id,
8431: instance_id,
8432: entity_name,
8462: v_sql_stmt := 32.5;
8463: lv_sql_stmt :=
8464: 'UPDATE msc_st_trading_partner_sites mts'
8465: ||' SET location_id = (SELECT local_id'
8466: ||' FROM msc_local_id_setup mls'
8467: ||' WHERE mls.char1 = mts.sr_instance_code'
8468: ||' AND mls.char3 = mts.location'
8469: ||' AND mls.entity_name = ''LOCATION_ID'' )'
8470: ||' WHERE process_flag = '||G_IN_PROCESS
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'
9304: ||' AND mls.entity_name = ''LOCATION_ID'' )'
9305: ||' WHERE process_flag = '||G_IN_PROCESS
9378:
9379: -- Insert record in LID
9380: v_sql_stmt := 43.2;
9381: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
9382: INSERT INTO msc_local_id_setup
9383: (local_id,
9384: st_transaction_id,
9385: instance_id,
9386: entity_name,
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'
9423: ||' AND mls.entity_name = ''LOCATION_ID'' )'
9424: ||' WHERE process_flag = '||G_IN_PROCESS
10348: WHERE rowid = lb_rowid(j);
10349:
10350: v_sql_stmt := 65;
10351: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
10352: INSERT INTO msc_local_id_setup
10353: (local_id,
10354: st_transaction_id,
10355: instance_id,
10356: entity_name,
12859: v_sql_stmt := 08;
12860: lv_sql_stmt :=
12861: 'UPDATE msc_st_visits msv'
12862: ||' SET visit_id = (SELECT local_id'
12863: ||' FROM msc_local_id_setup mls'
12864: ||' WHERE mls.char1 = msv.sr_instance_code'
12865: ||' AND mls.char4 = msv.visit_name'
12866: ||' AND mls.entity_name = ''VISIT'' )'
12867: ||' WHERE process_flag = '||G_IN_PROCESS
12895: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
12896:
12897: -- Insert into the LID table
12898:
12899: INSERT INTO msc_local_id_setup
12900: (local_id,
12901: st_transaction_id,
12902: instance_id,
12903: entity_name,
12930:
12931: lv_sql_stmt:=
12932: 'UPDATE MSC_ST_VISITS msv'
12933: ||' SET visit_id = (SELECT local_id '
12934: ||' FROM msc_local_id_setup mlis'
12935: ||' WHERE mlis.entity_name= ''VISIT'' '
12936: ||' AND mlis.char1 = msv.sr_instance_code'
12937: ||' AND mlis.char4 = msv.visit_name)'
12938: ||' WHERE sr_instance_code = :v_instance_code'
13246: v_sql_stmt := 07;
13247: lv_sql_stmt :=
13248: 'UPDATE msc_st_wo_milestones msv'
13249: ||' SET visit_id = (SELECT local_id'
13250: ||' FROM msc_local_id_setup mls'
13251: ||' WHERE mls.char1 = msv.sr_instance_code'
13252: ||' AND mls.char4 = msv.visit_name'
13253: ||' AND mls.entity_name = ''VISIT'' )'
13254: ||' WHERE process_flag = '||G_IN_PROCESS
13270: p_error_text => lv_error_text,
13271: p_token1 => 'COLUMN_NAME',
13272: p_token_value1 => 'VISIT_NAME');
13273:
13274: -- Validate the visit name with data in msc_local_id_setup
13275: v_sql_stmt := 08;
13276: lv_sql_stmt :=
13277: 'UPDATE msc_st_wo_milestones '
13278: ||' SET process_flag = '||G_ERROR_FLG||','
13916: v_sql_stmt := 08;
13917: lv_sql_stmt :=
13918: 'UPDATE msc_st_wo_attributes mswa'
13919: ||' SET visit_id = (SELECT local_id'
13920: ||' FROM msc_local_id_setup mls'
13921: ||' WHERE mls.char1 = mswa.sr_instance_code'
13922: ||' AND mls.char4 = mswa.visit_name'
13923: ||' AND mls.entity_name = ''VISIT'' )'
13924: ||' WHERE process_flag = '||G_IN_PROCESS
13940: p_error_text => lv_error_text,
13941: p_token1 => 'COLUMN_NAME',
13942: p_token_value1 => 'VISIT_NAME');
13943:
13944: -- Validate the visit name with data in msc_local_id_setup
13945: v_sql_stmt := 09;
13946: lv_sql_stmt :=
13947: 'UPDATE msc_st_wo_attributes '
13948: ||' SET process_flag = '||G_ERROR_FLG||','
16453: v_sql_stmt := 08;
16454: lv_sql_stmt :=
16455: 'UPDATE msc_st_interorg_ship_methods msis'
16456: ||' SET from_location_id = (SELECT local_id'
16457: ||' FROM msc_local_id_setup mls'
16458: ||' WHERE mls.char1 = msis.sr_instance_code'
16459: ||' AND mls.char3 = msis.from_location_code'
16460: ||' AND mls.entity_name = ''LOCATION_ID'' )'
16461: ||' WHERE process_flag = '||G_IN_PROCESS
16475: v_sql_stmt := 09;
16476: lv_sql_stmt :=
16477: 'UPDATE msc_st_interorg_ship_methods msis'
16478: ||' SET to_location_id = (SELECT local_id'
16479: ||' FROM msc_local_id_setup mls'
16480: ||' WHERE mls.char1 = msis.sr_instance_code'
16481: ||' AND mls.char3 = msis.to_location_code'
16482: ||' AND mls.entity_name = ''LOCATION_ID'' )'
16483: ||' WHERE process_flag = '||G_IN_PROCESS
16600:
16601: lv_sql_stmt :=
16602: 'UPDATE msc_st_interorg_ship_methods msr'
16603: ||' SET from_region_id = (SELECT local_id'
16604: ||' FROM msc_local_id_setup mls'
16605: ||' WHERE mls.char1 = msr.sr_instance_code'
16606: ||' AND mls.number1 = nvl(msr.from_region_type,'||NULL_VALUE||')'
16607: ||' AND NVL(mls.char2, '||''''||NULL_CHAR||''''||') = '
16608: ||' NVL(msr.from_country, '||''''||NULL_CHAR||''''||') '
16639:
16640: lv_sql_stmt :=
16641: 'UPDATE msc_st_interorg_ship_methods msr'
16642: ||' SET to_region_id = (SELECT local_id'
16643: ||' FROM msc_local_id_setup mls'
16644: ||' WHERE mls.char1 = msr.sr_instance_code'
16645: ||' AND mls.number1 = nvl(msr.to_region_type,'||NULL_VALUE||')'
16646: ||' AND NVL(mls.char2, '||''''||NULL_CHAR||''''||') = '
16647: ||' NVL(msr.to_country, '||''''||NULL_CHAR||''''||') '
17406:
17407: lv_sql_stmt :=
17408: 'UPDATE msc_st_regions msr'
17409: ||' SET region_id = nvl((SELECT local_id'
17410: ||' FROM msc_local_id_setup mls'
17411: ||' WHERE mls.char1 = msr.sr_instance_code'
17412: ||' AND mls.number1 = msr.region_type'
17413: ||' AND NVL(mls.char2, '||''''||NULL_CHAR||''''||') = '
17414: ||' NVL(msr.country, '||''''||NULL_CHAR||''''||') '
17500: WHERE rowid = lb_rowid(j);
17501:
17502: v_sql_stmt := 13;
17503: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
17504: INSERT INTO msc_local_id_setup
17505: (local_id,
17506: st_transaction_id,
17507: instance_id,
17508: entity_name,
17948:
17949: lv_sql_stmt :=
17950: 'UPDATE msc_st_zone_regions mszr'
17951: ||' SET region_id = nvl((SELECT local_id'
17952: ||' FROM msc_local_id_setup mls'
17953: ||' WHERE mls.char1 = mszr.sr_instance_code'
17954: ||' AND NVL(mls.char2, '||''''||NULL_CHAR||''''||') = '
17955: ||' NVL(mszr.country, '||''''||NULL_CHAR||''''||') '
17956: ||' AND NVL(mls.char3, '||''''||NULL_CHAR||''''||') = '
18013:
18014: lv_sql_stmt :=
18015: 'UPDATE msc_st_zone_regions mszr'
18016: ||' SET parent_region_id = nvl((SELECT local_id'
18017: ||' FROM msc_local_id_setup mls'
18018: ||' WHERE mls.char1 = mszr.sr_instance_code'
18019: ||' AND NVL(mls.char10, '||''''||NULL_CHAR||''''||') = '
18020: ||' NVL(mszr.zone, '||''''||NULL_CHAR||''''||') '
18021: ||' AND mls.number1 = 10 '
18084:
18085: lv_sql_stmt :=
18086: 'UPDATE msc_st_zone_regions mszr'
18087: ||' SET zone_region_id = nvl((SELECT local_id'
18088: ||' FROM msc_local_id_setup mls'
18089: ||' WHERE mls.char1 = mszr.sr_instance_code'
18090: ||' AND NVL(mls.char2, '||''''||NULL_CHAR||''''||') = '
18091: ||' NVL(mszr.country, '||''''||NULL_CHAR||''''||') '
18092: ||' AND NVL(mls.char3, '||''''||NULL_CHAR||''''||') = '
18143: WHERE rowid = lb_rowid(j);
18144:
18145: v_sql_stmt := 13;
18146: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
18147: INSERT INTO msc_local_id_setup
18148: (local_id,
18149: st_transaction_id,
18150: instance_id,
18151: entity_name,
18528:
18529: lv_sql_stmt :=
18530: 'UPDATE msc_st_region_locations msr'
18531: ||' SET region_id = (SELECT local_id'
18532: ||' FROM msc_local_id_setup mls'
18533: ||' WHERE mls.char1 = msr.sr_instance_code'
18534: ||' AND NVL(mls.number1, '||NULL_VALUE||') = NVL(msr.region_type,'||NULL_VALUE||')'
18535: ||' AND NVL(mls.char2, '||''''||NULL_CHAR||''''||') = '
18536: ||' NVL(msr.country, '||''''||NULL_CHAR||''''||') '
18565: --Deriving location_id
18566: lv_sql_stmt :=
18567: 'UPDATE msc_st_region_locations msr'
18568: ||' SET location_id = nvl((SELECT local_id'
18569: ||' FROM msc_local_id_setup mls'
18570: ||' WHERE mls.char1 = msr.sr_instance_code'
18571: ||' AND mls.char3 = msr.location_code'
18572: ||' AND mls.entity_name = ''LOCATION_ID'' ), ' ||NULL_VALUE|| ')'
18573: ||' WHERE process_flag = '||G_IN_PROCESS
18637: WHERE rowid = lb_rowid(j);
18638:
18639: -- Insert record in LID
18640: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
18641: INSERT INTO msc_local_id_setup
18642: (local_id,
18643: st_transaction_id,
18644: instance_id,
18645: entity_name,
18671:
18672: lv_sql_stmt :=
18673: 'UPDATE msc_st_region_locations msr'
18674: ||' SET location_id = nvl((SELECT local_id'
18675: ||' FROM msc_local_id_setup mls'
18676: ||' WHERE mls.char1 = msr.sr_instance_code'
18677: ||' AND mls.char3 = msr.location_code'
18678: ||' AND mls.entity_name = ''LOCATION_ID'' ), ' ||NULL_VALUE|| ')'
18679: ||' WHERE process_flag = '||G_IN_PROCESS
18999:
19000: lv_sql_stmt :=
19001: 'UPDATE msc_st_region_sites msr'
19002: ||' SET region_id = (SELECT local_id'
19003: ||' FROM msc_local_id_setup mls'
19004: ||' WHERE mls.char1 = msr.sr_instance_code'
19005: ||' AND NVL(mls.char2, '||''''||NULL_CHAR||''''||') = '
19006: ||' NVL(msr.country, '||''''||NULL_CHAR||''''||') '
19007: ||' AND NVL(mls.char3, '||''''||NULL_CHAR||''''||') = '
31575:
31576: lv_sql_stmt :=
31577: 'UPDATE msc_st_department_resources msdr'
31578: ||' SET department_id= (SELECT local_id'
31579: ||' FROM msc_local_id_setup mlis'
31580: ||' WHERE mlis.char1 = msdr.sr_instance_code'
31581: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '
31582: ||' = NVL(msdr.company_name,'||''''||NULL_CHAR||''''||') '
31583: ||' AND mlis.char3 = msdr.organization_code'
31599:
31600: lv_sql_stmt :=
31601: 'UPDATE msc_st_department_resources msdr'
31602: ||' SET department_id= (SELECT local_id '
31603: ||' FROM msc_local_id_setup mlis'
31604: ||' WHERE mlis.char1 = msdr.sr_instance_code'
31605: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '
31606: ||' = NVL(msdr.company_name,'||''''||NULL_CHAR||''''||') '
31607: ||' AND mlis.char3 = msdr.organization_code'
31624:
31625: lv_sql_stmt :=
31626: 'UPDATE msc_st_department_resources msdr'
31627: ||' SET resource_id= (SELECT local_id'
31628: ||' FROM msc_local_id_setup mlis'
31629: ||' WHERE mlis.char1 = msdr.sr_instance_code'
31630: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '
31631: ||' = NVL(msdr.company_name,'||''''||NULL_CHAR||''''||') '
31632: ||' AND mlis.char3 = msdr.organization_code'
31693: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
31694:
31695: -- Insert into the LID table this new department with the user defined key
31696:
31697: INSERT INTO msc_local_id_setup
31698: (local_id,
31699: st_transaction_id,
31700: instance_id,
31701: entity_name,
31737:
31738: lv_sql_stmt :=
31739: 'UPDATE msc_st_department_resources msdr'
31740: ||' SET department_id= (SELECT local_id'
31741: ||' FROM msc_local_id_setup mlis'
31742: ||' WHERE mlis.char1 = msdr.sr_instance_code'
31743: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '
31744: ||' = NVL(msdr.company_name,'||''''||NULL_CHAR||''''||') '
31745: ||' AND mlis.char3 = msdr.organization_code'
31778:
31779: lv_sql_stmt :=
31780: 'UPDATE msc_st_department_resources msdr'
31781: ||' SET owning_department_id= (SELECT local_id'
31782: ||' FROM msc_local_id_setup mlis'
31783: ||' WHERE mlis.char1 = msdr.sr_instance_code'
31784: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '
31785: ||' = NVL(msdr.company_name,'||''''||NULL_CHAR||''''||') '
31786: ||' AND mlis.char3 = msdr.organization_code'
31857: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
31858:
31859: -- Insert into the LID table this new resource with the user defined key
31860:
31861: INSERT INTO msc_local_id_setup
31862: (local_id,
31863: st_transaction_id,
31864: instance_id,
31865: entity_name,
31904:
31905: lv_sql_stmt :=
31906: 'UPDATE msc_st_department_resources msdr'
31907: ||' SET resource_id= (SELECT local_id'
31908: ||' FROM msc_local_id_setup mlis'
31909: ||' WHERE mlis.char1 = msdr.sr_instance_code'
31910: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '
31911: ||' = NVL(msdr.company_name,'||''''||NULL_CHAR||''''||') '
31912: ||' AND mlis.char3 = msdr.organization_code'
31942: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
31943:
31944: -- Insert into the LID table this new department with the user defined key
31945:
31946: INSERT INTO msc_local_id_setup
31947: (local_id,
31948: st_transaction_id,
31949: instance_id,
31950: entity_name,
32243: v_sql_stmt := 25;
32244: lv_sql_stmt :=
32245: 'UPDATE msc_st_resource_shifts msrs'
32246: ||' SET department_id = (SELECT local_id'
32247: ||' FROM msc_local_id_setup mlis'
32248: ||' WHERE mlis.char1 = msrs.sr_instance_code'
32249: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '
32250: ||' = NVL(msrs.company_name,'||''''||NULL_CHAR||''''||') '
32251: ||' AND mlis.char3 = msrs.organization_code'
32300: -- Popluate the resource id from LOCAL_ID table
32301: v_sql_stmt := 27;
32302: lv_sql_stmt:=
32303: 'UPDATE msc_st_resource_shifts msrs'
32304: ||' SET resource_id = (SELECT local_id FROM msc_local_id_setup mlis'
32305: ||' WHERE mlis.char1 = msrs.sr_instance_code'
32306: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '
32307: ||' = NVL(msrs.company_name,'||''''||NULL_CHAR||''''||') '
32308: ||' AND mlis.char3 = msrs.organization_code'
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||''''||') '
32386:
32387: lv_sql_stmt :=
32388: 'UPDATE msc_st_resource_shifts msrs'
32389: ||' SET shift_num =(SELECT mlis.local_id'
32390: ||' FROM msc_local_id_setup mlis, msc_trading_partners mtp '
32391: ||' WHERE mtp.sr_instance_id = '||v_instance_id
32392: ||' AND mlis.char1 = msrs.sr_instance_code'
32393: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '
32394: ||' = NVL(msrs.company_name,'||''''||NULL_CHAR||''''||') '
32819: v_sql_stmt := 39;
32820: lv_sql_stmt :=
32821: 'UPDATE msc_st_resource_changes msrc'
32822: ||' SET department_id = (SELECT local_id'
32823: ||' FROM msc_local_id_setup mlis'
32824: ||' WHERE mlis.char1 = msrc.sr_instance_code'
32825: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '
32826: ||' = NVL(msrc.company_name,'||''''||NULL_CHAR||''''||') '
32827: ||' AND mlis.char3 = msrc.organization_code'
32877: v_sql_stmt := 41;
32878: lv_sql_stmt:=
32879: 'UPDATE msc_st_resource_changes msrc'
32880: ||' SET resource_id = (SELECT local_id'
32881: ||' FROM msc_local_id_setup mlis'
32882: ||' WHERE mlis.char1 = msrc.sr_instance_code'
32883: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '
32884: ||' = NVL(msrc.company_name,'||''''||NULL_CHAR||''''||') '
32885: ||' AND mlis.char3 = msrc.organization_code'
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||''''||') '
32964:
32965: lv_sql_stmt :=
32966: 'UPDATE msc_st_resource_changes msrc'
32967: ||' SET shift_num =(SELECT mlis.local_id'
32968: ||' FROM msc_local_id_setup mlis, msc_trading_partners mtp '
32969: ||' WHERE mtp.sr_instance_id = '||v_instance_id
32970: ||' AND mlis.char1 = msrc.sr_instance_code'
32971: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '
32972: ||' = NVL(msrc.company_name,'||''''||NULL_CHAR||''''||') '
33267:
33268: lv_sql_stmt :=
33269: 'UPDATE msc_st_resource_setups msrs'
33270: ||' SET setup_id= (SELECT local_id'
33271: ||' FROM msc_local_id_setup mlis'
33272: ||' WHERE mlis.char1 = msrs.sr_instance_code'
33273: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||')'
33274: || '=NVL(msrs.company_name,'||''''||NULL_CHAR||''''||') '
33275: ||' AND mlis.char3 = msrs.organization_code'
33323:
33324: lv_sql_stmt :=
33325: 'UPDATE msc_st_resource_setups msrs'
33326: ||' SET resource_id= (SELECT local_id'
33327: ||' FROM msc_local_id_setup mlis'
33328: ||' WHERE mlis.char1 = msrs.sr_instance_code'
33329: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||')'
33330: || '=NVL(msrs.company_name,'||''''||NULL_CHAR||''''||') '
33331: ||' AND mlis.char3 = msrs.organization_code'
33395:
33396: v_sql_stmt := 08;
33397:
33398: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
33399: INSERT INTO msc_local_id_setup
33400: (local_id,
33401: st_transaction_id,
33402: instance_id,
33403: entity_name,
33612:
33613: lv_sql_stmt :=
33614: 'UPDATE msc_st_setup_transitions msst'
33615: ||' SET from_setup_id= (SELECT local_id'
33616: ||' FROM msc_local_id_setup mlis'
33617: ||' WHERE mlis.char1 = msst.sr_instance_code'
33618: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||')'
33619: ||' =NVL(msst.company_name,'||''''||NULL_CHAR||''''||') '
33620: ||' AND mlis.char3 = msst.organization_code'
33669: v_sql_stmt := 05;
33670: lv_sql_stmt :=
33671: 'UPDATE msc_st_setup_transitions msst'
33672: ||' SET to_setup_id= (SELECT local_id'
33673: ||' FROM msc_local_id_setup mlis'
33674: ||' WHERE mlis.char1 = msst.sr_instance_code'
33675: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||')'
33676: ||' =NVL(msst.company_name,'||''''||NULL_CHAR||''''||') '
33677: ||' AND mlis.char3 = msst.organization_code'
33726:
33727: lv_sql_stmt :=
33728: 'UPDATE msc_st_setup_transitions msst'
33729: ||' SET standard_operation_id= (SELECT local_id'
33730: ||' FROM msc_local_id_setup mlis'
33731: ||' WHERE mlis.char1 = msst.sr_instance_code'
33732: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||')'
33733: || '=NVL(msst.company_name,'||''''||NULL_CHAR||''''||') '
33734: ||' AND mlis.char3 = msst.organization_code'
33747: v_sql_stmt := 08;
33748: lv_sql_stmt :=
33749: 'UPDATE MSC_ST_SETUP_TRANSITIONS msrc'
33750: ||' SET department_id = (SELECT local_id'
33751: ||' FROM msc_local_id_setup mlis'
33752: ||' WHERE mlis.char1 = msrc.sr_instance_code'
33753: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '
33754: ||' = NVL(msrc.company_name,'||''''||NULL_CHAR||''''||') '
33755: ||' AND mlis.char3 = msrc.organization_code'
33805: v_sql_stmt := 10;
33806: lv_sql_stmt :=
33807: 'UPDATE msc_st_setup_transitions msst'
33808: ||' SET resource_id= (SELECT local_id'
33809: ||' FROM msc_local_id_setup mlis'
33810: ||' WHERE mlis.char1 = msst.sr_instance_code'
33811: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||')'
33812: ||' =NVL(msst.company_name,'||''''||NULL_CHAR||''''||') '
33813: ||' AND mlis.char3 = msst.organization_code'
34345: END IF;
34346:
34347: UPDATE MSC_ST_SUPPLIES
34348: SET PLANNING_PARTNER_SITE_ID = (SELECT local_id
34349: FROM msc_local_id_setup
34350: WHERE char1 = sr_instance_code
34351: AND NVL(char2,NULL_CHAR) = NVL(company_name,NULL_CHAR)
34352: AND char4 = PLANNING_PARTNER_SITE_CODE
34353: AND number1 = G_VENDOR
34362:
34363:
34364: UPDATE MSC_ST_SUPPLIES
34365: SET PLANNING_PARTNER_SITE_ID = (SELECT local_id
34366: FROM msc_local_id_setup
34367: WHERE char1 = sr_instance_code
34368: AND char3 = PLANNING_PARTNER_SITE_CODE
34369: AND number1 = G_ORGANIZATION
34370: AND entity_name = 'SR_TP_ID' )
34391: AND ORDER_TYPE=18;
34392:
34393: UPDATE MSC_ST_SUPPLIES
34394: SET OWNING_PARTNER_SITE_ID = (SELECT local_id
34395: FROM msc_local_id_setup
34396: WHERE char1 = sr_instance_code
34397: AND NVL(char2,NULL_CHAR) = NVL(company_name,NULL_CHAR)
34398: AND char4 = OWNING_PARTNER_SITE_CODE
34399: AND number1 = G_VENDOR
34406: AND ORDER_TYPE=18;
34407:
34408: UPDATE MSC_ST_SUPPLIES
34409: SET OWNING_PARTNER_SITE_ID = (SELECT local_id
34410: FROM msc_local_id_setup
34411: WHERE char1 = sr_instance_code
34412: AND char3 = OWNING_PARTNER_SITE_CODE
34413: AND number1 = G_ORGANIZATION
34414: AND entity_name = 'SR_TP_ID')
39898: /* Calling derive_company_id
39899: v_sql_stmt := 04;
39900:
39901: UPDATE MSC_ST_COMPANY_USERS mscu
39902: SET SR_COMPANY_ID = (SELECT local_id from MSC_LOCAL_ID_SETUP
39903: WHERE char1 = mscu.sr_instance_code
39904: and NVL(char3,NULL_CHAR) = NVL(mscu.company_name,NULL_CHAR)
39905: and entity_name = 'SR_TP_ID'
39906: and rownum = 1) -- we need the first occurence of sr_tp_id
39956:
39957: v_sql_stmt := 05;
39958:
39959: UPDATE MSC_ST_COMPANY_USERS mscu
39960: SET PARTNER_TYPE = (SELECT number1 from MSC_LOCAL_ID_SETUP
39961: WHERE char1 = mscu.sr_instance_code
39962: and NVL(char3,NULL_CHAR) = NVL(mscu.company_name,NULL_CHAR)
39963: and entity_name = 'SR_TP_ID'
39964: and number1 in (1,2)
40285: -- Derive the component_sequence_id --
40286: lv_sql_stmt :=
40287: 'UPDATE msc_st_bom_components mbc'
40288: ||' SET component_sequence_id= (SELECT local_id '
40289: ||' FROM msc_local_id_setup mlis'
40290: ||' WHERE mlis.char1 = mbc.sr_instance_code'
40291: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '
40292: ||' = NVL(mbc.company_name,'||''''||NULL_CHAR||''''||') '
40293: ||' AND mlis.char3 = mbc.organization_code'
40324: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
40325:
40326: -- Insert into the LID table
40327:
40328: INSERT INTO msc_local_id_setup
40329: (local_id,
40330: st_transaction_id,
40331: instance_id,
40332: entity_name,
40458: AND batch_id = p_batch_id;
40459:
40460: CURSOR c7(p_batch_id NUMBER) IS
40461: SELECT mlis.rowid
40462: FROM msc_st_bom_components mbc, msc_local_id_setup mlis
40463: WHERE mlis.char1 = mbc.sr_instance_code
40464: AND NVL(mlis.char2,'-23453') = NVL(mbc.company_name,'-23453')
40465: AND mlis.char3 = mbc.organization_code
40466: AND mlis.char4 = mbc.bom_name
41003: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
41004:
41005: -- Insert into the LID table this new BOM with the user defined key
41006:
41007: INSERT INTO msc_local_id_setup
41008: (local_id,
41009: st_transaction_id,
41010: instance_id,
41011: entity_name,
41377:
41378: lv_sql_stmt:=
41379: 'UPDATE msc_st_bom_components mbc'
41380: ||' SET assembly_name = (SELECT char5'
41381: ||' FROM msc_local_id_setup mlis'
41382: ||' WHERE mlis.char1 = mbc.sr_instance_code'
41383: ||' AND mlis.char3 = mbc.organization_code'
41384: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '
41385: ||' = NVL(mbc.company_name,'||''''||NULL_CHAR||''''||') '
41558: v_sql_stmt := 20;
41559: lv_sql_stmt :=
41560: 'UPDATE msc_st_bom_components mbc'
41561: ||' SET component_sequence_id= (SELECT local_id '
41562: ||' FROM msc_local_id_setup mlis'
41563: ||' WHERE mlis.char1 = mbc.sr_instance_code'
41564: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '
41565: ||' = NVL(mbc.company_name,'||''''||NULL_CHAR||''''||') '
41566: ||' AND mlis.char3 = mbc.organization_code'
41634: 'UPDATE msc_st_bom_components mbc'
41635: ||' SET process_flag = '||G_ERROR_FLG||','
41636: ||' error_text = '||''''||lv_message_text||''''
41637: ||' WHERE EXISTS (SELECT 1 FROM'
41638: ||' msc_local_id_setup mlis'
41639: ||' WHERE mlis.char1 = mbc.sr_instance_code'
41640: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '
41641: ||' = NVL(mbc.company_name,'||''''||NULL_CHAR||''''||') '
41642: ||' AND mlis.char3 = mbc.organization_code'
42091: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
42092:
42093: -- Insert into the LID table
42094:
42095: INSERT INTO msc_local_id_setup
42096: (local_id,
42097: st_transaction_id,
42098: instance_id,
42099: entity_name,
42152: -- Update the DISABLE_DATE to SYSDATE for the deleted component in LID table
42153: v_sql_stmt := 25;
42154: /*
42155: lv_sql_stmt :=
42156: 'UPDATE msc_local_id_setup mlis'
42157: ||' SET date2 = SYSDATE'
42158: ||' WHERE EXISTS ( SELECT 1 FROM msc_st_bom_components mbc'
42159: ||' WHERE mlis.char1 = mbc.sr_instance_code'
42160: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '
42182: IF c7%ROWCOUNT > 0 THEN
42183:
42184: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
42185:
42186: UPDATE msc_local_id_setup
42187: SET date2 = SYSDATE
42188: WHERE rowid = lb_rowid(j);
42189:
42190: END IF;
42544:
42545: lv_sql_stmt:=
42546: 'UPDATE msc_st_component_substitutes mcs'
42547: ||' SET assembly_name = (SELECT char5'
42548: ||' FROM msc_local_id_setup mlis'
42549: ||' WHERE mlis.char1 = mcs.sr_instance_code'
42550: ||' AND mlis.char3 = mcs.organization_code'
42551: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '
42552: ||' = NVL(mcs.company_name,'||''''||NULL_CHAR||''''||') '
42665:
42666: lv_sql_stmt :=
42667: 'UPDATE msc_st_component_substitutes mcs'
42668: ||' SET component_sequence_id= (SELECT local_id '
42669: ||' FROM msc_local_id_setup mlis'
42670: ||' WHERE mlis.char1 = mcs.sr_instance_code'
42671: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '
42672: ||' = NVL(mcs.company_name,'||''''||NULL_CHAR||''''||') '
42673: ||' AND mlis.char3 = mcs.organization_code'
43148: lv_sql_stmt :=
43149: 'UPDATE MSC_ST_CO_PRODUCTS t1'
43150: ||' SET CO_PRODUCT_GROUP_ID '
43151: ||' = (SELECT local_id'
43152: ||' FROM msc_local_id_setup t2'
43153: ||' WHERE t2.char1 = t1.sr_instance_code'
43154: ||' AND NVL(t2.char2,'||''''||NULL_CHAR||''''||') '
43155: ||' = NVL(t1.company_name,'||''''||NULL_CHAR||''''||') '
43156: ||' AND t2.char4 = t1.COMPONENT_NAME '
43354: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
43355:
43356: -- Insert into the LID table with the user defined key
43357:
43358: INSERT INTO msc_local_id_setup
43359: (local_id,
43360: st_transaction_id,
43361: instance_id,
43362: entity_name,
43996:
43997: lv_sql_stmt :=
43998: 'UPDATE msc_st_routings msr'
43999: ||' SET line_id = ( SELECT local_id'
44000: ||' FROM msc_local_id_setup mlis'
44001: ||' WHERE mlis.char1 = msr.sr_instance_code'
44002: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '
44003: ||' = NVL(msr.company_name,'||''''||NULL_CHAR||''''||') '
44004: ||' AND mlis.char3 = msr.organization_code'
44270: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
44271:
44272: -- Insert into the LID table with the user defined key
44273:
44274: INSERT INTO msc_local_id_setup
44275: (local_id,
44276: st_transaction_id,
44277: instance_id,
44278: entity_name,
44789: v_sql_stmt := 24;
44790: lv_sql_Stmt :=
44791: 'UPDATE msc_st_routing_operations msro'
44792: ||' SET (operation_sequence_id,operation_seq_num)= (SELECT local_id,number1'
44793: ||' FROM msc_local_id_setup mlis'
44794: ||' WHERE mlis.char1 = msro.sr_instance_code'
44795: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '
44796: ||' = NVL(msro.company_name,'||''''||NULL_CHAR||''''||') '
44797: ||' AND mlis.char3 = msro.organization_code'
44818:
44819: lv_sql_stmt :=
44820: 'UPDATE msc_st_routing_operations msro'
44821: ||' SET operation_seq_num = ( SELECT number1'
44822: ||' FROM msc_local_id_setup mlis'
44823: ||' WHERE local_id = msro.operation_sequence_id'
44824: ||' AND mlis.entity_name = ''OPERATION_SEQUENCE_ID'' '
44825: ||' AND mlis.instance_id = '||v_instance_id||')'
44826: ||' WHERE sr_instance_code = :v_instance_code'
44899: v_sql_stmt := 27;
44900: lv_sql_stmt :=
44901: 'UPDATE msc_st_routing_operations msro'
44902: ||' SET department_id= (SELECT local_id'
44903: ||' FROM msc_local_id_setup mlis'
44904: ||' WHERE mlis.char1 = msro.sr_instance_code'
44905: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '
44906: ||' = NVL(msro.company_name,'||''''||NULL_CHAR||''''||') '
44907: ||' AND mlis.char3 = msro.organization_code'
44940: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
44941:
44942: -- Insert into the LID table this new department with the user defined key
44943:
44944: INSERT INTO msc_local_id_setup
44945: (local_id,
44946: st_transaction_id,
44947: instance_id,
44948: entity_name,
44984:
44985: lv_sql_stmt :=
44986: 'UPDATE msc_st_routing_operations msdr'
44987: ||' SET department_id= (SELECT local_id'
44988: ||' FROM msc_local_id_setup mlis'
44989: ||' WHERE mlis.char1 = msdr.sr_instance_code'
44990: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '
44991: ||' = NVL(msdr.company_name,'||''''||NULL_CHAR||''''||') '
44992: ||' AND mlis.char3 = msdr.organization_code'
45118:
45119: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
45120: -- Insert into the LID table with the user defined key
45121:
45122: INSERT INTO msc_local_id_setup
45123: (local_id,
45124: st_transaction_id,
45125: instance_id,
45126: entity_name,
45201: ||' msro.created_by,'
45202: ||' msro.REFRESH_ID'
45203: ||' FROM msc_st_routing_operations msro,'
45204: ||' msc_st_bom_components msbc,'
45205: ||' msc_local_id_setup mlis'
45206: ||' WHERE msbc.bill_sequence_id = mlis.number1'
45207: ||' AND mlis.entity_name = ''ROUTING_SEQUENCE_ID'' '
45208: ||' AND mlis.local_id = msro.routing_sequence_id'
45209: ||' AND msbc.process_flag = '||G_VALID
45227: ||' msro.created_by,'
45228: ||' msro.REFRESH_ID'
45229: ||' FROM msc_st_routing_operations msro,'
45230: ||' msc_bom_components mbc,'
45231: ||' msc_local_id_setup mlis,'
45232: ||' msc_local_id_setup mlis1'
45233: ||' WHERE mbc.component_sequence_id = mlis1.local_id '
45234: ||' AND mbc.bill_sequence_id = mlis.number1'
45235: ||' AND mbc.sr_instance_id = :v_instance_id'
45228: ||' msro.REFRESH_ID'
45229: ||' FROM msc_st_routing_operations msro,'
45230: ||' msc_bom_components mbc,'
45231: ||' msc_local_id_setup mlis,'
45232: ||' msc_local_id_setup mlis1'
45233: ||' WHERE mbc.component_sequence_id = mlis1.local_id '
45234: ||' AND mbc.bill_sequence_id = mlis.number1'
45235: ||' AND mbc.sr_instance_id = :v_instance_id'
45236: ||' AND mbc.plan_id = -1'
45667: v_sql_stmt := 38;
45668: lv_sql_stmt:=
45669: 'UPDATE msc_st_operation_resources msor'
45670: ||' SET operation_sequence_id= (SELECT local_id'
45671: ||' FROM msc_local_id_setup mlis'
45672: ||' WHERE mlis.char1 = msor.sr_instance_code'
45673: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '
45674: ||' = NVL(msor.company_name,'||''''||NULL_CHAR||''''||') '
45675: ||' AND mlis.char3 = msor.organization_code'
45807: v_sql_stmt := 40;
45808: lv_sql_stmt :=
45809: 'UPDATE msc_st_operation_resources msor'
45810: ||' SET resource_seq_num = (SELECT local_id '
45811: ||' FROM msc_local_id_setup mlis'
45812: ||' WHERE mlis.char1 = msor.sr_instance_code'
45813: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '
45814: ||' = NVL(msor.company_name,'||''''||NULL_CHAR||''''||') '
45815: ||' AND mlis.char3 = msor.organization_code'
45868:
45869: lv_sql_stmt :=
45870: 'UPDATE msc_st_operation_resources msor'
45871: ||' SET resource_id = (SELECT local_id'
45872: ||' FROM msc_local_id_setup mlis'
45873: ||' WHERE mlis.char1 = msor.sr_instance_code'
45874: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '
45875: ||' = NVL(msor.company_name,'||''''||NULL_CHAR||''''||') '
45876: ||' AND mlis.char3 = msor.organization_code'
45891:
45892: lv_sql_stmt :=
45893: 'UPDATE msc_st_operation_resources msor'
45894: ||' SET department_id = (SELECT local_id'
45895: ||' FROM msc_local_id_setup mlis'
45896: ||' WHERE mlis.char1 = msor.sr_instance_code'
45897: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '
45898: ||' = NVL(msor.company_name,'||''''||NULL_CHAR||''''||') '
45899: ||' AND mlis.char3 = msor.organization_code'
45949:
45950: lv_sql_stmt :=
45951: 'UPDATE msc_st_operation_resources msor'
45952: ||' SET setup_id= (SELECT local_id'
45953: ||' FROM msc_local_id_setup mlis'
45954: ||' WHERE mlis.char1 = msor.sr_instance_code'
45955: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||')'
45956: ||' =NVL(msor.company_name,'||''''||NULL_CHAR||''''||') '
45957: ||' AND mlis.char3 = msor.organization_code'
46114:
46115: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
46116: -- Insert into the LID table with the user defined key
46117:
46118: INSERT INTO msc_local_id_setup
46119: (local_id,
46120: st_transaction_id,
46121: instance_id,
46122: entity_name,
46174: v_sql_stmt := 47;
46175: lv_sql_stmt :=
46176: 'UPDATE msc_st_operation_resources msor'
46177: ||' SET resource_seq_num = (SELECT local_id '
46178: ||' FROM msc_local_id_setup mlis'
46179: ||' WHERE mlis.char1 = msor.sr_instance_code'
46180: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '
46181: ||' = NVL(msor.company_name,'||''''||NULL_CHAR||''''||') '
46182: ||' AND mlis.char3 = msor.organization_code'
46789: v_sql_stmt := 65;
46790: lv_sql_Stmt :=
46791: 'UPDATE msc_st_operation_networks mson'
46792: ||' SET from_op_seq_id= (SELECT local_id'
46793: ||' FROM msc_local_id_setup mlis'
46794: ||' WHERE mlis.char1 = mson.sr_instance_code'
46795: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '
46796: ||' = NVL(mson.company_name,'||''''||NULL_CHAR||''''||') '
46797: ||' AND mlis.char3 = mson.organization_code'
46851: v_sql_stmt := 67;
46852: lv_sql_Stmt :=
46853: 'UPDATE msc_st_operation_networks mson'
46854: ||' SET to_op_seq_id= (SELECT local_id'
46855: ||' FROM msc_local_id_setup mlis'
46856: ||' WHERE mlis.char1 = mson.sr_instance_code'
46857: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '
46858: ||' = NVL(mson.company_name,'||''''||NULL_CHAR||''''||') '
46859: ||' AND mlis.char3 = mson.organization_code'
47908: ||' NVL(mss.company_name,'||''''||NULL_CHAR||''''||') '
47909: ||' AND mls.char1 = mss.sr_instance_code'
47910: ||' AND mls.entity_name = ''SCHEDULE_GROUP_ID'' ),'
47911: ||' line_id = (SELECT local_id'
47912: ||' FROM msc_local_id_setup mls'
47913: ||' WHERE mls.char4 = mss.line_code'
47914: ||' AND mls.char3 = mss.organization_code'
47915: ||' AND NVL(mls.char2, '||''''||NULL_CHAR||''''||') = '
47916: ||' NVL(mss.company_name,'||''''||NULL_CHAR||''''||') '
47916: ||' NVL(mss.company_name,'||''''||NULL_CHAR||''''||') '
47917: ||' AND mls.char1 = mss.sr_instance_code'
47918: ||' AND mls.entity_name = ''LINE_ID''),'
47919: ||' operation_seq_num = (SELECT number1'
47920: ||' FROM msc_local_id_setup mls'
47921: ||' WHERE mls.char5 = mss.operation_seq_code'
47922: ||' AND mls.char4 = mss.routing_name'
47923: ||' AND NVL(mls.char6, '||''''||NULL_CHAR||''''||') = '
47924: ||' NVL(mss.alternate_routing_designator,'||''''||NULL_CHAR||''''||') '
48054:
48055: lv_sql_stmt :=
48056: 'UPDATE msc_st_supplies mss'
48057: ||' SET jump_op_seq_num = (SELECT number1'
48058: ||' FROM msc_local_id_setup mls'
48059: ||' WHERE NVL(mls.char5,'||''''||NULL_CHAR||''''||') = '
48060: ||' NVL(mss.jump_op_seq_code,'||''''||NULL_CHAR||''''||') '
48061: ||' AND mls.char4 = mss.routing_name'
48062: ||' AND NVL(mls.char6, '||''''||NULL_CHAR||''''||') = '
48740:
48741: lv_sql_stmt :=
48742: 'UPDATE msc_st_resource_requirements msrr'
48743: ||' SET resource_id= (SELECT local_id '
48744: ||' FROM msc_local_id_setup mlis'
48745: ||' WHERE mlis.char1 = msrr.sr_instance_code'
48746: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '
48747: ||' = NVL(msrr.company_name,'||''''||NULL_CHAR||''''||') '
48748: ||' AND mlis.char3 = msrr.organization_code'
48844:
48845: lv_sql_stmt :=
48846: 'UPDATE msc_st_resource_requirements msrr'
48847: ||' SET department_id = (SELECT local_id'
48848: ||' FROM msc_local_id_setup mlis'
48849: ||' WHERE mlis.char1 = msrr.sr_instance_code'
48850: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '
48851: ||' = NVL(msrr.company_name,'||''''||NULL_CHAR||''''||') '
48852: ||' AND mlis.char3 = msrr.organization_code'
49117: v_sql_stmt := 14;
49118: lv_sql_stmt:=
49119: 'UPDATE msc_st_resource_requirements msrr'
49120: ||' SET operation_seq_num = (SELECT number1'
49121: ||' FROM msc_local_id_setup mlis'
49122: ||' WHERE mlis.char1 = msrr.sr_instance_code'
49123: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '
49124: ||' = NVL(msrr.company_name,'||''''||NULL_CHAR||''''||') '
49125: ||' AND mlis.char3 = msrr.organization_code'
49146: v_sql_stmt := 15;
49147: lv_sql_Stmt :=
49148: 'UPDATE msc_st_resource_requirements msrr'
49149: ||' SET operation_sequence_id= (SELECT local_id'
49150: ||' FROM msc_local_id_setup mlis'
49151: ||' WHERE mlis.char1 = msrr.sr_instance_code'
49152: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '
49153: ||' = NVL(msrr.company_name,'||''''||NULL_CHAR||''''||') '
49154: ||' AND mlis.char3 = msrr.organization_code'
49221:
49222: lv_sql_stmt :=
49223: 'UPDATE msc_st_resource_requirements msor'
49224: ||' SET resource_seq_num = (SELECT local_id '
49225: ||' FROM msc_local_id_setup mlis'
49226: ||' WHERE mlis.char1 = msor.sr_instance_code'
49227: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '
49228: ||' = NVL(msor.company_name,'||''''||NULL_CHAR||''''||') '
49229: ||' AND mlis.char3 = msor.organization_code'
49388:
49389: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
49390:
49391: -- Insert into the LID table
49392: INSERT INTO msc_local_id_setup
49393: (local_id,
49394: st_transaction_id,
49395: instance_id,
49396: entity_name,
49450: v_sql_stmt := 19;
49451: lv_sql_Stmt :=
49452: 'UPDATE msc_st_resource_requirements msrr'
49453: ||' SET (operation_sequence_id, operation_seq_num) = (SELECT local_id, number1'
49454: ||' FROM msc_local_id_setup mlis'
49455: ||' WHERE mlis.char1 = msrr.sr_instance_code'
49456: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '
49457: ||' = NVL(msrr.company_name,'||''''||NULL_CHAR||''''||') '
49458: ||' AND mlis.char3 = msrr.organization_code'
49490:
49491: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
49492: -- Insert into the LID table with the user defined key
49493:
49494: INSERT INTO msc_local_id_setup
49495: (local_id,
49496: st_transaction_id,
49497: instance_id,
49498: entity_name,
49551:
49552: lv_sql_stmt :=
49553: 'UPDATE msc_st_resource_requirements msor'
49554: ||' SET resource_seq_num = (SELECT local_id '
49555: ||' FROM msc_local_id_setup mlis'
49556: ||' WHERE mlis.char1 = msor.sr_instance_code'
49557: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '
49558: ||' = NVL(msor.company_name,'||''''||NULL_CHAR||''''||') '
49559: ||' AND mlis.char3 = msor.organization_code'
50295: v_sql_stmt := 12;
50296: lv_sql_stmt:=
50297: 'UPDATE msc_st_demands msd'
50298: ||' SET operation_seq_num= (SELECT number1'
50299: ||' FROM msc_local_id_setup mlis'
50300: ||' WHERE mlis.char1 = msd.sr_instance_code'
50301: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '
50302: ||' = NVL(msd.company_name,'||''''||NULL_CHAR||''''||') '
50303: ||' AND mlis.char3 = msd.organization_code'
50590: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
50591:
50592: -- Insert into the LID table
50593:
50594: INSERT INTO msc_local_id_setup
50595: (local_id,
50596: st_transaction_id,
50597: instance_id,
50598: entity_name,
50652: v_sql_stmt := 12;
50653: lv_sql_stmt:=
50654: 'UPDATE msc_st_demands msd'
50655: ||' SET operation_seq_num= (SELECT number1'
50656: ||' FROM msc_local_id_setup mlis'
50657: ||' WHERE mlis.char1 = msd.sr_instance_code'
50658: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '
50659: ||' = NVL(msd.company_name,'||''''||NULL_CHAR||''''||') '
50660: ||' AND mlis.char3 = msd.organization_code'
51425: v_sql_stmt := 16;
51426: lv_sql_Stmt :=
51427: 'UPDATE msc_st_job_operation_networks mson'
51428: ||' SET (from_op_seq_id, from_op_seq_num) = (SELECT local_id, number1'
51429: ||' FROM msc_local_id_setup mlis'
51430: ||' WHERE mlis.char1 = mson.sr_instance_code'
51431: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '
51432: ||' = NVL(mson.company_name,'||''''||NULL_CHAR||''''||') '
51433: ||' AND mlis.char3 = mson.organization_code'
51487: v_sql_stmt := 18;
51488: lv_sql_Stmt :=
51489: 'UPDATE msc_st_job_operation_networks mson'
51490: ||' SET (to_op_seq_id, to_op_seq_num) = (SELECT local_id, number1'
51491: ||' FROM msc_local_id_setup mlis'
51492: ||' WHERE mlis.char1 = mson.sr_instance_code'
51493: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '
51494: ||' = NVL(mson.company_name,'||''''||NULL_CHAR||''''||') '
51495: ||' AND mlis.char3 = mson.organization_code'
52183: v_sql_stmt := 18;
52184: lv_sql_Stmt :=
52185: 'UPDATE msc_st_job_operations mson'
52186: ||' SET (operation_sequence_id, operation_seq_num) = (SELECT local_id, number1'
52187: ||' FROM msc_local_id_setup mlis'
52188: ||' WHERE mlis.char1 = mson.sr_instance_code'
52189: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '
52190: ||' = NVL(mson.company_name,'||''''||NULL_CHAR||''''||') '
52191: ||' AND mlis.char3 = mson.organization_code'
52243: v_sql_stmt := 27;
52244: lv_sql_stmt :=
52245: 'UPDATE msc_st_job_operations msro'
52246: ||' SET department_id= (SELECT local_id'
52247: ||' FROM msc_local_id_setup mlis'
52248: ||' WHERE mlis.char1 = msro.sr_instance_code'
52249: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '
52250: ||' = NVL(msro.company_name,'||''''||NULL_CHAR||''''||') '
52251: ||' AND mlis.char3 = msro.organization_code'
53009: v_sql_stmt := 12;
53010: lv_sql_stmt:=
53011: 'UPDATE msc_st_job_requirement_ops msd'
53012: ||' SET operation_seq_num= (SELECT number1'
53013: ||' FROM msc_local_id_setup mlis'
53014: ||' WHERE mlis.char1 = msd.sr_instance_code'
53015: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '
53016: ||' = NVL(msd.company_name,'||''''||NULL_CHAR||''''||') '
53017: ||' AND mlis.char3 = msd.organization_code'
53067:
53068: lv_sql_stmt :=
53069: 'UPDATE msc_st_job_requirement_ops mjro'
53070: ||' SET component_sequence_id= (SELECT local_id '
53071: ||' FROM msc_local_id_setup mlis'
53072: ||' WHERE mlis.char1 = mjro.sr_instance_code'
53073: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '
53074: ||' = NVL(mjro.company_name,'||''''||NULL_CHAR||''''||') '
53075: ||' AND mlis.char3 = mjro.organization_code'
53124:
53125: lv_sql_stmt :=
53126: 'UPDATE msc_st_job_requirement_ops mjro'
53127: ||' SET department_id= (SELECT local_id'
53128: ||' FROM msc_local_id_setup mlis'
53129: ||' WHERE mlis.char1 = mjro.sr_instance_code'
53130: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '
53131: ||' = NVL(mjro.company_name,'||''''||NULL_CHAR||''''||') '
53132: ||' AND mlis.char3 = mjro.organization_code'
53477:
53478: lv_sql_stmt :=
53479: 'UPDATE msc_st_job_op_resources msrr'
53480: ||' SET resource_id= (SELECT local_id '
53481: ||' FROM msc_local_id_setup mlis'
53482: ||' WHERE mlis.char1 = msrr.sr_instance_code'
53483: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '
53484: ||' = NVL(msrr.company_name,'||''''||NULL_CHAR||''''||') '
53485: ||' AND mlis.char3 = msrr.organization_code'
53540: v_sql_stmt := 25;
53541: lv_sql_stmt :=
53542: 'UPDATE msc_st_job_op_resources msrs'
53543: ||' SET department_id = (SELECT local_id'
53544: ||' FROM msc_local_id_setup mlis'
53545: ||' WHERE mlis.char1 = msrs.sr_instance_code'
53546: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '
53547: ||' = NVL(msrs.company_name,'||''''||NULL_CHAR||''''||') '
53548: ||' AND mlis.char3 = msrs.organization_code'
53773: v_sql_stmt := 14;
53774: lv_sql_stmt:=
53775: 'UPDATE msc_st_job_op_resources msrr'
53776: ||' SET operation_seq_num = (SELECT number1'
53777: ||' FROM msc_local_id_setup mlis'
53778: ||' WHERE mlis.char1 = msrr.sr_instance_code'
53779: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '
53780: ||' = NVL(msrr.company_name,'||''''||NULL_CHAR||''''||') '
53781: ||' AND mlis.char3 = msrr.organization_code'
53868:
53869: lv_sql_stmt :=
53870: 'UPDATE msc_st_job_op_resources msor'
53871: ||' SET resource_seq_num = (SELECT local_id '
53872: ||' FROM msc_local_id_setup mlis'
53873: ||' WHERE mlis.char1 = msor.sr_instance_code'
53874: ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '
53875: ||' = NVL(msor.company_name,'||''''||NULL_CHAR||''''||') '
53876: ||' AND mlis.char3 = msor.organization_code'
57542: lv_sql_stmt :=
57543: 'UPDATE msc_st_apps_instances mai'
57544: ||' SET validation_org_id'
57545: ||' = (SELECT local_id'
57546: ||' FROM msc_local_id_setup '
57547: ||' WHERE char1 = sr_instance_code'
57548: ||' AND char3 = msc_org_for_bom_explosion'
57549: ||' AND number1 = '|| G_ORGANIZATION
57550: ||' AND entity_name = ''SR_TP_ID'' )'