目的:V20305J –[非TLS品項批次提料]鈕
–挑選xls檔案, 匯入2個Table(AMM_UOC,AMM_UOCD)
- Transaction - rollback - loadmark -timer
- Transaction - rollback - loadmark -timer
處理緣由: 1>因為採用 Transaction 方式,採用SQL_List方式批次寫入Table
2> 有可能兩筆資料在AMM_UOC PK重覆, 第一筆Insert, 但第二筆必需Update
-2.1>>準備SQL時,第一筆還沒寫入,所以判斷用Insert
-2.2>>實際執行Transaction時,第一筆寫入,
第二筆寫入時,PK已存在(第一筆剛寫入),又執行Insert SQL
---> AMM_UOC 會 Key Violation
-2.3 更新 AMM_UOC 改用 MERGE 方式,
--> 不用自行處理 - Tmp_AMM_UOC_LIST.IndexOf(Tmp_AMM_UOC_PK) ==-1 方式
3> 如上2>在AMM_UOCD.ITMUOCD=MAX(ITMUOCD)+1 ,
-3.1>>準備SQL時,第一筆還沒寫入,所以第一筆&第二筆.MAX(ITMUOCD)均=0
-3.2>>實際執行Transaction時,第一筆寫入,
第二筆.MAX(ITMUOCD)應該=1,而不是0
---> AMM_UOCD 會 Key Violation
4>Tmp_cnt=excuteSQLTran(SQL_List_A);
//批次執行 SQL 有錯誤
if (Tmp_cnt == -1)
{
Tmp_RtnMsg = getExcMsg();
raiseEXP(new Exception() , Tmp_RtnMsg);
}
處理說明: 2>兩筆相同資料寫入, AMM_UOC
將PK存入 PK_LIST
若PK未存在 PK_LIST ,則用 Insert SQL
若PK存在 PK_LIST (重覆) ,則改用 Update SQL
3>兩筆相同資料寫入, AMM_UOCD(ITMUOCD)
利用 INSERT INTO SELECT 語法, 即時取得 MAX(ITMUOCD)+1
Ex:
INSERT INTO AMM_UOCD
(RPNOAF, EIAC, EI_LCN, EI_ALC, NHA_LCN, NHA_ALC, LCN, ALC, PN,
ITMUOCD,
TMNOXX, AMMNO)
SELECT '20140214U320-A07','UH-1H','A','01','A0Y0U2H07','00','A0Y0U2H070C','00','CEC5091-0998-55',
(SELECT nvl(MAX(TO_NUMBER(ITMUOCD)),0) + 1
FROM AMM_UOCD
WHERE RPNOAF = '20140214U320-A07'
AND EIAC = 'UH-1H'
AND EI_LCN = 'A'
AND EI_ALC = '01'
AND NHA_LCN = 'A0Y0U2H07'
AND NHA_ALC = '00'
AND LCN = 'A0Y0U2H070C'
AND ALC = '00'
AND PN = 'CEC5091-0998-55'),
'TMNOXX1',
'AMMNO1'
FROM DUAL;
function V20305J_OK(me) {
console.log(" 1 inside Ok_process");
//取得 [檔案上傳].檔名
var Tmp_FName = Ext.getCmp('sub_File').getValue();
var np = {};
np["sub_FName"] = Tmp_FName;
var Tmp_Str = "";
//先清除 cookies 欄位值
Ext.util.Cookies.set("Rtn_Msg", "");
me.up("form").submit({
//standardSubmit: false, //default:false
url: '../api/V20305JAPI/INS_AMM_UOC_UOCD',
method: 'POST',
headers: { 'Content-type': 'multipart/form-data' },
params: np,
}); // end of me.up("form").submit({
console.log(" close window ");
var mask = new Ext.LoadMask(Ext.getBody(), {
msg: '資料處理中, 請稍待...'
});
mask.show();//使用 mask 需手動呼叫show() 方法下
//隔兩秒才顯示完成訊息
var timer = setInterval(function () {
var cookie_token = Ext.util.Cookies.get("Rtn_Msg");
if (!checkisnull(cookie_token)) {
clearInterval(timer);
mask.hide();
timer = null;
var rtn_msg = r_cookies("Rtn_Msg");
mysuccessalert(rtn_msg);
me.up("window").close();
me.up("window").destroy();
}
}, 2000); //等待 2000ms =1sec
}; // end of Ok_process() { //批次新增
2>*.cs
[HttpPost]
public void INS_AMM_UOC_UOCD()
{
HttpContext c = HttpContext.Current;
NameValueCollection nvc = c.Request.Form;
//OracleConnection conn = new OracleConnection(DBService.ConnectionString(("AMMEU"));//
OracleConnection conn = new OracleConnection(DBService.ConnectionString((DBLINK)));//
OracleCommand cmd = new OracleCommand();
DataSet ds = null;
//取得匯入檔案名稱 of filefield
string Tmp_FName = nvc["sub_FName"];
string Tmp_in_FName="", Tmp_out_FName="";
Workbook wk = null;
MemoryStream stream = new MemoryStream();
Worksheet ws=null;
HttpRequest Request = HttpContext.Current.Request;
var response = this.Request.CreateResponse();
//string file_Name = Tmp_in_FName;
string FILEDT = DateTime.Now.ToString("yyyy/MM/dd");
string Tmp_RtnMsg = "";
string Tmp_RtnMsg1 = "";
string Tmp_Sql = "", Tmp_Str = "";
int Tmp_cnt = 0;
int Tmp_rowcnt = 0;
HttpCookie MyCookie = new HttpCookie("Rtn_Msg");
conn.Open();
cmd.Connection = conn;
try
{
foreach (string cur_FName in Request.Files)
{
string fileType = Request.Files[cur_FName].ContentType;
Stream Tmp_in_Stream = Request.Files[cur_FName].InputStream;
Tmp_in_FName = Path.GetFileName(Request.Files[cur_FName].FileName);
Tmp_out_FName = "Log_" + Tmp_in_FName;
int fileSize = Request.Files[cur_FName].ContentLength;
byte[] fileRcrd = new byte[fileSize];
//更新資料庫欄位值
//匯入的 xls
wk = new Workbook();
wk.LoadFromStream(Tmp_in_Stream);
ws = wk.Worksheets[0];
/*
//共 43 欄
//string[] outFieldArray = { 提領編號、交修單號、維修交修單號、工單號碼、序號、
展BOMM件號、展BOM件號名稱、圖號、索引、上層件號、
SBOM件號、SBOM件號中文名稱、規範(需求)件號、提料件號、每具數量、
需求數量、需求單位、更換情形、故障現象說明、可修件狀態(Y/N)、
可修件更換原因說明、超量原因、子件工單號碼、子件序號、特製件說明、
經常更換、配換率、QPEI、拆挪LRU交修單號、拆挪子件序號、
LCN、ALC、NHA_LCN、NHA_ALC、EIAC、
EI_LCN、EI_ALC、SBOM廠碼、規範廠碼、SBOM單位、
技令號碼、WP、SBOM件號英文名稱};
*/
string Tmp_OGONOAF, Tmp_RPNOAF, Tmp_MRPNOAF, Tmp_SAPNO, Tmp_SN;//序號: Tmp_SN
string Tmp_SRO_PN, Tmp_SRO_C_NM, Tmp_FIG, Tmp_IND, Tmp_NHA_PN;
string Tmp_SBOM_PN, Tmp_SBOM_C_NM, Tmp_RQPN, Tmp_FACPN, Tmp_QPA;
string Tmp_NQTYAH, Tmp_C_UNIT, Tmp_RSTHAH, Tmp_FRMK, Tmp_FASTAT;
string Tmp_FACHRN, Tmp_OVERAHD, Tmp_FRNOAF_S, Tmp_SUB_SN, Tmp_NOTE; //子件序號: Tmp_SUB_SN
string Tmp_RATE_MORE, Tmp_RATE, Tmp_QPEI, Tmp_RMRPNOAF, Tmp_RMSBSN;
string Tmp_LCN, Tmp_ALC, Tmp_NHA_LCN, Tmp_NHA_ALC, Tmp_EIAC;
string Tmp_EI_LCN, Tmp_EI_ALC, Tmp_SBOM_CAGE, Tmp_RQPN_CAGE, Tmp_SBOM_UNIT;
string Tmp_TMNOXX, Tmp_WP, Tmp_SBOM_PN_E_NM,Tmp_FACPN_UNIT;
string Tmp_PN,Tmp_CAGE,Tmp_NSN,Tmp_AMMNO;
string Tmp_ITMUOCD, Tmp_NEXT_ITMUOCD;
string Tmp_AMM_UOC_PK, Tmp_AMM_UOCD_PK;
//請料者,請料日期,狀態,可修狀態
string Tmp_EMPLYID = User.Identity.Name;
string Tmp_RDUOCD = DateTime.Now.ToString("yyyy/MM/dd"); ;
string Tmp_STAUOCD = "A";
//List<String> Tmp_AMM_UOC_LIST 儲存 AMM_UOC.PK
List<String> Tmp_AMM_UOC_LIST = new List<string>();
//STEP1 檢核 XLS 欄位名稱資料是否正確 -取得 標題 of 工號,處置說明
int cur_row=1;
Tmp_OGONOAF = ws.Range[myfunc.GetExcelPos(0, cur_row)].Value.Trim();
Tmp_RPNOAF = ws.Range[myfunc.GetExcelPos(1, cur_row)].Value.Trim();
Tmp_MRPNOAF = ws.Range[myfunc.GetExcelPos(2, cur_row)].Value.Trim();
Tmp_SAPNO = ws.Range[myfunc.GetExcelPos(3, cur_row)].Value.Trim();
Tmp_SN = ws.Range[myfunc.GetExcelPos(4, cur_row)].Value.Trim(); //序號
Tmp_SRO_PN= ws.Range[myfunc.GetExcelPos(5, cur_row)].Value.Trim();
Tmp_SRO_C_NM= ws.Range[myfunc.GetExcelPos(6, cur_row)].Value.Trim();
Tmp_FIG= ws.Range[myfunc.GetExcelPos(7, cur_row)].Value.Trim();
Tmp_IND= ws.Range[myfunc.GetExcelPos(8, cur_row)].Value.Trim();
Tmp_NHA_PN= ws.Range[myfunc.GetExcelPos(9, cur_row)].Value.Trim();
Tmp_SBOM_PN=ws.Range[myfunc.GetExcelPos(10, cur_row)].Value.Trim();
Tmp_SBOM_C_NM=ws.Range[myfunc.GetExcelPos(11, cur_row)].Value.Trim();
Tmp_RQPN=ws.Range[myfunc.GetExcelPos(12, cur_row)].Value.Trim();
Tmp_FACPN=ws.Range[myfunc.GetExcelPos(13, cur_row)].Value.Trim();
Tmp_QPA=ws.Range[myfunc.GetExcelPos(14, cur_row)].Value.Trim(); // (QPA,每具數量)
Tmp_NQTYAH=ws.Range[myfunc.GetExcelPos(15, cur_row)].Value.Trim();
Tmp_C_UNIT=ws.Range[myfunc.GetExcelPos(16, cur_row)].Value.Trim();
Tmp_RSTHAH=ws.Range[myfunc.GetExcelPos(17, cur_row)].Value.Trim();
Tmp_FRMK=ws.Range[myfunc.GetExcelPos(18, cur_row)].Value.Trim();
Tmp_FASTAT=ws.Range[myfunc.GetExcelPos(19, cur_row)].Value.Trim(); //可修件(y/n)
Tmp_FACHRN=ws.Range[myfunc.GetExcelPos(20, cur_row)].Value.Trim();
Tmp_OVERAHD=ws.Range[myfunc.GetExcelPos(21, cur_row)].Value.Trim();
Tmp_FRNOAF_S=ws.Range[myfunc.GetExcelPos(22, cur_row)].Value.Trim(); //子件工單號碼
Tmp_SUB_SN=ws.Range[myfunc.GetExcelPos(23, cur_row)].Value.Trim(); //子件序號
Tmp_NOTE=ws.Range[myfunc.GetExcelPos(24, cur_row)].Value.Trim(); //特製件說明
Tmp_RATE_MORE =ws.Range[myfunc.GetExcelPos(25, cur_row)].Value.Trim();
Tmp_RATE=ws.Range[myfunc.GetExcelPos(26, cur_row)].Value.Trim();
Tmp_QPEI=ws.Range[myfunc.GetExcelPos(27, cur_row)].Value.Trim();
Tmp_RMRPNOAF=ws.Range[myfunc.GetExcelPos(28, cur_row)].Value.Trim();
Tmp_RMSBSN=ws.Range[myfunc.GetExcelPos(29, cur_row)].Value.Trim(); //拆挪子件序號
Tmp_LCN= ws.Range[myfunc.GetExcelPos(30, cur_row)].Value.Trim();
Tmp_ALC =ws.Range[myfunc.GetExcelPos(31, cur_row)].Value.Trim();
Tmp_NHA_LCN=ws.Range[myfunc.GetExcelPos(32, cur_row)].Value.Trim();
Tmp_NHA_ALC=ws.Range[myfunc.GetExcelPos(33, cur_row)].Value.Trim();
Tmp_EIAC=ws.Range[myfunc.GetExcelPos(34, cur_row)].Value.Trim(); //EIAC
Tmp_EI_LCN=ws.Range[myfunc.GetExcelPos(35, cur_row)].Value.Trim();
Tmp_EI_ALC=ws.Range[myfunc.GetExcelPos(36, cur_row)].Value.Trim();
Tmp_SBOM_CAGE=ws.Range[myfunc.GetExcelPos(37, cur_row)].Value.Trim();
Tmp_RQPN_CAGE=ws.Range[myfunc.GetExcelPos(38, cur_row)].Value.Trim();
Tmp_SBOM_UNIT=ws.Range[myfunc.GetExcelPos(39, cur_row)].Value.Trim(); //SBOM單位
Tmp_TMNOXX=ws.Range[myfunc.GetExcelPos(40, cur_row)].Value.Trim();
Tmp_WP=ws.Range[myfunc.GetExcelPos(41, cur_row)].Value.Trim();
Tmp_SBOM_PN_E_NM=ws.Range[myfunc.GetExcelPos(42, cur_row)].Value.Trim();
Tmp_FACPN_UNIT = ws.Range[myfunc.GetExcelPos(43, cur_row)].Value.Trim(); //撥發單位
//xls 欄位名稱有問題
if ( (Tmp_OGONOAF != "提領編號") || (Tmp_RPNOAF != "交修單號") || ( Tmp_MRPNOAF!= "維修交修單號") || ( Tmp_SAPNO!= "工單號碼") || ( Tmp_SN!= "序號") ||
(Tmp_SRO_PN!= "展BOM件號") || ( Tmp_SRO_C_NM!= "展BOM件號名稱") || ( Tmp_FIG!= "圖號") || ( Tmp_IND!= "索引") || ( Tmp_NHA_PN!= "上層件號") ||
(Tmp_SBOM_PN!= "SBOM件號") || ( Tmp_SBOM_C_NM!= "SBOM件號中文名稱") || ( Tmp_RQPN!= "規範件號") || ( Tmp_FACPN!= "提料件號") || ( Tmp_QPA!= "每具數量") ||
(Tmp_NQTYAH!= "需求數量") || ( Tmp_C_UNIT!= "需求單位") || ( Tmp_RSTHAH!= "更換情形") || ( Tmp_FRMK!= "故障現象說明") || ( Tmp_FASTAT!= "可修件(Y/N)") ||
(Tmp_FACHRN != "可修件更換原因說明") || ( Tmp_OVERAHD!= "超量原因") || ( Tmp_FRNOAF_S!= "子件工單號碼") || ( Tmp_SUB_SN!= "子件序號") || ( Tmp_NOTE!= "特製件說明") ||
(Tmp_RATE_MORE != "經常更換") || ( Tmp_RATE!= "配換率") || ( Tmp_QPEI!= "QPEI") || ( Tmp_RMRPNOAF!= "拆挪LRU交修單號") || ( Tmp_RMSBSN!= "拆挪子件序號") ||
(Tmp_LCN != "LCN") || ( Tmp_ALC!= "ALC") || ( Tmp_NHA_LCN!= "NHA_LCN") || ( Tmp_NHA_ALC!= "NHA_ALC") || ( Tmp_EIAC!= "EIAC") ||
(Tmp_EI_LCN != "EI_LCN") || ( Tmp_EI_ALC!= "EI_ALC") || ( Tmp_SBOM_CAGE!= "SBOM廠碼") || ( Tmp_RQPN_CAGE!= "規範廠碼") || ( Tmp_SBOM_UNIT!= "SBOM單位") ||
(Tmp_TMNOXX != "技令號碼") || ( Tmp_WP!= "WP") || ( Tmp_SBOM_PN_E_NM!= "SBOM件號英文名稱") || ( Tmp_FACPN_UNIT!= "撥發單位") )
{
Tmp_RtnMsg = "匯入檔案欄位名稱必需如下:<br>";
//(Tmp_DTSOR != "資料來源") if (Tmp_OGONOAF != "拆檢提領編號") if (Tmp_NGONOAF != "維修提領編號") if (Tmp_RPNOAF != "拆檢交修單號") if (Tmp_MRPNOAF != "維修約交修單號") if
if (Tmp_OGONOAF != "提領編號")
Tmp_RtnMsg = Tmp_RtnMsg + "第1欄欄位名稱: [提領編號];";
if (Tmp_RPNOAF != "交修單號")
Tmp_RtnMsg = Tmp_RtnMsg + "第2欄欄位名稱: [交修單號];";
if (Tmp_MRPNOAF != "維修交修單號")
Tmp_RtnMsg = Tmp_RtnMsg + "第3欄欄位名稱: [維修交修單號];";
if (Tmp_SAPNO != "工單號碼")
Tmp_RtnMsg = Tmp_RtnMsg + "第4欄欄位名稱: [工單號碼];";
if (Tmp_SN != "序號")
Tmp_RtnMsg = Tmp_RtnMsg + "第5欄欄位名稱: [序號];";
if (Tmp_SRO_PN != "展BOM件號")
Tmp_RtnMsg = Tmp_RtnMsg + "第6欄欄位名稱: [展BOM件號];";
if (Tmp_SRO_C_NM != "展BOM件號名稱")
Tmp_RtnMsg = Tmp_RtnMsg + "第7欄欄位名稱: [展BOM件號名稱];";
if (Tmp_FIG != "圖號")
Tmp_RtnMsg = Tmp_RtnMsg + "第8欄欄位名稱: [圖號];";
if (Tmp_IND != "索引")
Tmp_RtnMsg = Tmp_RtnMsg + "第9欄欄位名稱: [索引];";
if (Tmp_NHA_PN != "上層件號")
Tmp_RtnMsg = Tmp_RtnMsg + "第10欄欄位名稱: [上層件號];";
if (Tmp_SBOM_PN != "SBOM件號")
Tmp_RtnMsg = Tmp_RtnMsg + "第11欄欄位名稱: [SBOM件號];";
if (Tmp_SBOM_C_NM != "SBOM件號中文名稱")
Tmp_RtnMsg = Tmp_RtnMsg + "第12欄欄位名稱: [SBOM件號中文名稱];";
if (Tmp_RQPN != "規範件號")
Tmp_RtnMsg = Tmp_RtnMsg + "第13欄欄位名稱: [規範件號];";
if (Tmp_FACPN != "提料件號")
Tmp_RtnMsg = Tmp_RtnMsg + "第14欄欄位名稱: [提料件號];";
if (Tmp_QPA != "每具數量")
Tmp_RtnMsg = Tmp_RtnMsg + "第15欄欄位名稱: [每具數量];";
if (Tmp_NQTYAH != "需求數量")
Tmp_RtnMsg = Tmp_RtnMsg + "第16欄欄位名稱: [需求數量];";
if (Tmp_C_UNIT != "需求單位")
Tmp_RtnMsg = Tmp_RtnMsg + "第17欄欄位名稱: [需求單位];";
if (Tmp_RSTHAH != "更換情形")
Tmp_RtnMsg = Tmp_RtnMsg + "第18欄欄位名稱: [更換情形];";
if (Tmp_FRMK != "故障現象說明")
Tmp_RtnMsg = Tmp_RtnMsg + "第19欄欄位名稱: [故障現象說明];";
if (Tmp_FASTAT != "可修件(Y/N)")
Tmp_RtnMsg = Tmp_RtnMsg + "第20欄欄位名稱: [可修件(Y/N)];";
if (Tmp_FACHRN != "可修件更換原因說明")
Tmp_RtnMsg = Tmp_RtnMsg + "第21欄欄位名稱: [可修件更換原因說明];";
if (Tmp_OVERAHD != "超量原因")
Tmp_RtnMsg = Tmp_RtnMsg + "第22欄欄位名稱: [超量原因];";
if (Tmp_FRNOAF_S != "子件工單號碼")
Tmp_RtnMsg = Tmp_RtnMsg + "第23欄欄位名稱: [子件工單號碼];";
if (Tmp_SUB_SN != "子件序號")
Tmp_RtnMsg = Tmp_RtnMsg + "第24欄欄位名稱: [子件序號];";
if (Tmp_NOTE != "特製件說明")
Tmp_RtnMsg = Tmp_RtnMsg + "第25欄欄位名稱: [特製件說明];";
if (Tmp_RATE_MORE != "經常更換")
Tmp_RtnMsg = Tmp_RtnMsg + "第26欄欄位名稱: [經常更換];";
if (Tmp_RATE != "配換率")
Tmp_RtnMsg = Tmp_RtnMsg + "第27欄欄位名稱: [配換率];";
if (Tmp_QPEI != "QPEI")
Tmp_RtnMsg = Tmp_RtnMsg + "第28欄欄位名稱: [QPEI];";
if (Tmp_RMRPNOAF != "拆挪LRU交修單號")
Tmp_RtnMsg = Tmp_RtnMsg + "第29欄欄位名稱: [拆挪LRU交修單號];";
if (Tmp_RMSBSN != "拆挪子件序號")
Tmp_RtnMsg = Tmp_RtnMsg + "第30欄欄位名稱: [拆挪子件序號];";
if (Tmp_LCN != "LCN")
Tmp_RtnMsg = Tmp_RtnMsg + "第31欄欄位名稱: [LCN];";
if (Tmp_ALC != "ALC")
Tmp_RtnMsg = Tmp_RtnMsg + "第32欄欄位名稱: [ALC];";
if (Tmp_NHA_LCN != "NHA_LCN")
Tmp_RtnMsg = Tmp_RtnMsg + "第33欄欄位名稱: [NHA_LCN];";
if (Tmp_NHA_ALC != "NHA_ALC")
Tmp_RtnMsg = Tmp_RtnMsg + "第34欄欄位名稱: [NHA_ALC];";
if (Tmp_EIAC != "EIAC")
Tmp_RtnMsg = Tmp_RtnMsg + "第35欄欄位名稱: [EIAC];";
if (Tmp_EI_LCN != "EI_LCN")
Tmp_RtnMsg = Tmp_RtnMsg + "第36欄欄位名稱: [EI_LCN];";
if (Tmp_EI_ALC != "EI_ALC")
Tmp_RtnMsg = Tmp_RtnMsg + "第37欄欄位名稱: [EI_ALC];";
if (Tmp_SBOM_CAGE != "SBOM廠碼")
Tmp_RtnMsg = Tmp_RtnMsg + "第38欄欄位名稱: [SBOM廠碼];";
if (Tmp_RQPN_CAGE != "規範廠碼")
Tmp_RtnMsg = Tmp_RtnMsg + "第39欄欄位名稱: [規範廠碼];";
if (Tmp_SBOM_UNIT != "SBOM單位")
Tmp_RtnMsg = Tmp_RtnMsg + "第40欄欄位名稱: [SBOM單位];";
if (Tmp_TMNOXX != "技令號碼")
Tmp_RtnMsg = Tmp_RtnMsg + "第41欄欄位名稱: [技令號碼];";
if (Tmp_WP != "WP")
Tmp_RtnMsg = Tmp_RtnMsg + "第42欄欄位名稱: [WP];";
if (Tmp_SBOM_PN_E_NM != "SBOM件號英文名稱")
Tmp_RtnMsg = Tmp_RtnMsg + "第43欄欄位名稱: [SBOM件號英文名稱];";
if (Tmp_FACPN_UNIT != "撥發單位")
Tmp_RtnMsg = Tmp_RtnMsg + "第44欄欄位名稱: [撥發單位];";
MyCookie.Value = HttpUtility.UrlEncode(Tmp_RtnMsg);
HttpContext.Current.Response.Cookies.Add(MyCookie);
HttpContext.Current.Response.End();
return;
};
//新增一欄位:[匯入結果說明]
ws.Range[myfunc.GetExcelPos(44, 0)].Value = "匯入結果說明";
//STEP2 取得xls.[目前件號].報工資料
// Transaction SQL List - 存放 Transaction 的 SQL
List<string> SQL_List_A = new List<string>();
int RowsCount = ws.LastRow;
//int Tmp_cnt;
for (int i = 0; i < RowsCount; i++)
{
cur_row = cur_row + 1;
Tmp_OGONOAF = ws.Range[myfunc.GetExcelPos(0, cur_row)].Value.Trim(); //提領編號
Tmp_RPNOAF = ws.Range[myfunc.GetExcelPos(1, cur_row)].Value.Trim(); //交修單號
Tmp_MRPNOAF = ws.Range[myfunc.GetExcelPos(2, cur_row)].Value.Trim();
Tmp_SAPNO = ws.Range[myfunc.GetExcelPos(3, cur_row)].Value.Trim();
Tmp_SN = ws.Range[myfunc.GetExcelPos(4, cur_row)].Value.Trim(); //序號: Tmp_SN
Tmp_SRO_PN = ws.Range[myfunc.GetExcelPos(5, cur_row)].Value.Trim();
Tmp_SRO_C_NM = ws.Range[myfunc.GetExcelPos(6, cur_row)].Value.Trim();
Tmp_FIG = ws.Range[myfunc.GetExcelPos(7, cur_row)].Value.Trim();
Tmp_IND = ws.Range[myfunc.GetExcelPos(8, cur_row)].Value.Trim();
Tmp_NHA_PN = ws.Range[myfunc.GetExcelPos(9, cur_row)].Value.Trim();
Tmp_SBOM_PN = ws.Range[myfunc.GetExcelPos(10, cur_row)].Value.Trim();
Tmp_SBOM_C_NM = ws.Range[myfunc.GetExcelPos(11, cur_row)].Value.Trim();
Tmp_RQPN = ws.Range[myfunc.GetExcelPos(12, cur_row)].Value.Trim();
Tmp_FACPN = ws.Range[myfunc.GetExcelPos(13, cur_row)].Value.Trim();
Tmp_QPA = ws.Range[myfunc.GetExcelPos(14, cur_row)].Value.Trim(); //(QPA, 每具數量)
Tmp_NQTYAH = ws.Range[myfunc.GetExcelPos(15, cur_row)].Value.Trim();
Tmp_C_UNIT = ws.Range[myfunc.GetExcelPos(16, cur_row)].Value.Trim();
Tmp_RSTHAH = ws.Range[myfunc.GetExcelPos(17, cur_row)].Value.Trim();
Tmp_FRMK = ws.Range[myfunc.GetExcelPos(18, cur_row)].Value.Trim();
Tmp_FASTAT = ws.Range[myfunc.GetExcelPos(19, cur_row)].Value.Trim(); //可修件(y/n)
Tmp_FACHRN = ws.Range[myfunc.GetExcelPos(20, cur_row)].Value.Trim();
Tmp_OVERAHD = ws.Range[myfunc.GetExcelPos(21, cur_row)].Value.Trim();
Tmp_FRNOAF_S = ws.Range[myfunc.GetExcelPos(22, cur_row)].Value.Trim(); //子件工單號碼
Tmp_SUB_SN = ws.Range[myfunc.GetExcelPos(23, cur_row)].Value.Trim(); //子件序號: Tmp_SUB_SN
Tmp_NOTE = ws.Range[myfunc.GetExcelPos(24, cur_row)].Value.Trim(); //特製件說明
Tmp_RATE_MORE = ws.Range[myfunc.GetExcelPos(25, cur_row)].Value.Trim();
Tmp_RATE = ws.Range[myfunc.GetExcelPos(26, cur_row)].Value.Trim();
Tmp_QPEI = ws.Range[myfunc.GetExcelPos(27, cur_row)].Value.Trim();
Tmp_RMRPNOAF = ws.Range[myfunc.GetExcelPos(28, cur_row)].Value.Trim();
Tmp_RMSBSN = ws.Range[myfunc.GetExcelPos(29, cur_row)].Value.Trim(); //拆挪子件序號
Tmp_LCN = ws.Range[myfunc.GetExcelPos(30, cur_row)].Value.Trim();
Tmp_ALC = ws.Range[myfunc.GetExcelPos(31, cur_row)].Value.Trim();
Tmp_NHA_LCN = ws.Range[myfunc.GetExcelPos(32, cur_row)].Value.Trim();
Tmp_NHA_ALC = ws.Range[myfunc.GetExcelPos(33, cur_row)].Value.Trim();
Tmp_EIAC = ws.Range[myfunc.GetExcelPos(34, cur_row)].Value.Trim(); //EIAC
Tmp_EI_LCN = ws.Range[myfunc.GetExcelPos(35, cur_row)].Value.Trim();
Tmp_EI_ALC = ws.Range[myfunc.GetExcelPos(36, cur_row)].Value.Trim();
Tmp_SBOM_CAGE = ws.Range[myfunc.GetExcelPos(37, cur_row)].Value.Trim();
Tmp_RQPN_CAGE = ws.Range[myfunc.GetExcelPos(38, cur_row)].Value.Trim();
Tmp_SBOM_UNIT = ws.Range[myfunc.GetExcelPos(39, cur_row)].Value.Trim(); //SBOM單位
Tmp_TMNOXX = ws.Range[myfunc.GetExcelPos(40, cur_row)].Value.Trim();
Tmp_WP = ws.Range[myfunc.GetExcelPos(41, cur_row)].Value.Trim();
Tmp_SBOM_PN_E_NM = ws.Range[myfunc.GetExcelPos(42, cur_row)].Value.Trim();
Tmp_FACPN_UNIT = ws.Range[myfunc.GetExcelPos(43, cur_row)].Value.Trim(); //撥發單位
Tmp_PN = Tmp_SBOM_PN;
Tmp_CAGE= Tmp_SBOM_CAGE;
Tmp_AMM_UOC_PK = Tmp_RPNOAF +","+ Tmp_EIAC +","+ Tmp_EI_LCN +","+ Tmp_EI_ALC +","+ Tmp_NHA_LCN +","+ Tmp_NHA_ALC +","+ Tmp_LCN +","+ Tmp_ALC +","+ Tmp_PN;
if (myfunc.checkisnull(Tmp_OGONOAF)) //若[提領編號]空白,則離開
{ break; }
Tmp_Sql=" SELECT COUNT(*) "
+" FROM AMM_FM "
+" WHERE OGONOAF=" + myfunc.AA(Tmp_OGONOAF);
Tmp_cnt = int.Parse(myfunc.SqlValue(Tmp_Sql));
if (Tmp_cnt == 0)
{
ws.Range[myfunc.GetExcelPos(44, cur_row)].Value = "提領編號("+Tmp_OGONOAF +")不存在AMM系統(AMM_FM)";
}
else
{
if (Tmp_rowcnt >= 6)
Tmp_rowcnt = Tmp_rowcnt;
//取得 AMMNO - 由SAPNO 至 AMM_SRO.AMMNO
Tmp_Sql = " SELECT AMMNO "
+ " FROM AMM_SRO "
+ " WHERE SAPNO=" + myfunc.AA(Tmp_SAPNO)
+" AND rownum=1 ";
Tmp_AMMNO = myfunc.SqlValue(Tmp_Sql);
//取得 NSN
Tmp_Sql =" SELECT NSN "
+" FROM SP_NSN@TLS_245 "
+" WHERE PN=" + myfunc.AA(Tmp_PN)
+" AND rownum=1 ";
Tmp_NSN = myfunc.SqlValue(Tmp_Sql);
//更新 AMM_UOC
Tmp_Sql=" SELECT COUNT(*) "
+" FROM AMM_UOC "
+ " WHERE RPNOAF=" + myfunc.AA(Tmp_RPNOAF)
+ " AND EIAC=" + myfunc.AA(Tmp_EIAC)
+ " AND EI_LCN=" + myfunc.AA(Tmp_EI_LCN)
+ " AND EI_ALC=" + myfunc.AA(Tmp_EI_ALC)
+ " AND NHA_LCN=" + myfunc.AA(Tmp_NHA_LCN)
+ " AND NHA_ALC=" + myfunc.AA(Tmp_NHA_ALC)
+ " AND LCN=" + myfunc.AA(Tmp_LCN)
+ " AND ALC=" + myfunc.AA(Tmp_ALC)
+ " AND PN=" + myfunc.AA(Tmp_PN);
Tmp_cnt = myfunc.str2int(myfunc.SqlValue(Tmp_Sql));
if (Tmp_cnt>0)
{
Tmp_Sql = " UPDATE AMM_UOC "
+ " SET AMMNO=" + myfunc.AA(Tmp_AMMNO) + ","
+ " RQPN= " + myfunc.AA(Tmp_RQPN) + ","
+ " FACPN= " + myfunc.AA(Tmp_RQPN) + ","
+ " CAGE= " + myfunc.AA(Tmp_SBOM_CAGE) + ","
+ " NSN= " + myfunc.AA(Tmp_NSN)
+ " WHERE RPNOAF=" + myfunc.AA(Tmp_RPNOAF)
+ " AND EIAC=" + myfunc.AA(Tmp_EIAC)
+ " AND EI_LCN=" + myfunc.AA(Tmp_EI_LCN)
+ " AND EI_ALC=" + myfunc.AA(Tmp_EI_ALC)
+ " AND NHA_LCN=" + myfunc.AA(Tmp_NHA_LCN)
+ " AND NHA_ALC=" + myfunc.AA(Tmp_NHA_ALC)
+ " AND LCN=" + myfunc.AA(Tmp_LCN)
+ " AND ALC=" + myfunc.AA(Tmp_ALC)
+ " AND PN=" + myfunc.AA(Tmp_PN);
Tmp_Str = "更新(AMM_UOC)";
}
else
{ //新增 AMM_UOC
//不在資料庫 && PK 未重覆,則新增
if (Tmp_AMM_UOC_LIST.IndexOf(Tmp_AMM_UOC_PK) == -1)
{
Tmp_Sql = " INSERT INTO AMM_UOC "
+ "( RPNOAF,EIAC,EI_LCN,EI_ALC,NHA_LCN,NHA_ALC,LCN,ALC,PN,"
+ " AMMNO,RQPN,FACPN,CAGE,NSN ) "
+ " VALUES "
+ "(" + myfunc.AA(Tmp_RPNOAF) + "," + myfunc.AA(Tmp_EIAC) + "," + myfunc.AA(Tmp_EI_LCN) + "," + myfunc.AA(Tmp_EI_ALC) + "," + myfunc.AA(Tmp_NHA_LCN) + "," + myfunc.AA(Tmp_NHA_ALC) + "," + myfunc.AA(Tmp_LCN) + "," + myfunc.AA(Tmp_ALC) + "," + myfunc.AA(Tmp_PN) + ","
+ myfunc.AA(Tmp_AMMNO) + "," + myfunc.AA(Tmp_RQPN) + "," + myfunc.AA(Tmp_FACPN) + "," + myfunc.AA(Tmp_CAGE) + "," + myfunc.AA(Tmp_NSN)
+ ")";
Tmp_Str = "新增(AMM_UOC)";
}
else
{ //不在資料庫 ,但是 目前 PK 在(匯入檔案中) 已重覆,則更新
Tmp_Sql = " UPDATE AMM_UOC "
+ " SET AMMNO=" + myfunc.AA(Tmp_AMMNO) + ","
+ " RQPN= " + myfunc.AA(Tmp_RQPN) + ","
+ " FACPN= " + myfunc.AA(Tmp_RQPN) + ","
+ " CAGE= " + myfunc.AA(Tmp_SBOM_CAGE) + ","
+ " NSN= " + myfunc.AA(Tmp_NSN)
+ " WHERE RPNOAF=" + myfunc.AA(Tmp_RPNOAF)
+ " AND EIAC=" + myfunc.AA(Tmp_EIAC)
+ " AND EI_LCN=" + myfunc.AA(Tmp_EI_LCN)
+ " AND EI_ALC=" + myfunc.AA(Tmp_EI_ALC)
+ " AND NHA_LCN=" + myfunc.AA(Tmp_NHA_LCN)
+ " AND NHA_ALC=" + myfunc.AA(Tmp_NHA_ALC)
+ " AND LCN=" + myfunc.AA(Tmp_LCN)
+ " AND ALC=" + myfunc.AA(Tmp_ALC)
+ " AND PN=" + myfunc.AA(Tmp_PN);
Tmp_Str = "更新(AMM_UOC)";
}
}
SQL_List_A.Add(Tmp_Sql);
Tmp_AMM_UOC_LIST.Add(Tmp_AMM_UOC_PK); //處理完成,才將 PK 加入 String_List
//更新 AMM_UOCD
//取得 AMM_UOCD_PK - 因為無 ITMUOCD , 所以以 原PK+Tmp_TMNOXX+Tmp_FIG+Tmp_IND+Tmp_WP 當成 PK
Tmp_AMM_UOCD_PK = Tmp_RPNOAF + "," + Tmp_EIAC + "," + Tmp_EI_LCN + "," + Tmp_EI_ALC + "," + Tmp_NHA_LCN + "," + Tmp_NHA_ALC + "," + Tmp_LCN + "," + Tmp_ALC + "," + Tmp_PN
+Tmp_TMNOXX+Tmp_FIG+Tmp_IND+Tmp_WP;
//判斷 AMM_UOCD 是否已存在 原PK+Tmp_TMNOXX+Tmp_FIG+Tmp_IND+Tmp_WP
Tmp_Sql = " SELECT ITMUOCD "
+ " FROM AMM_UOCD "
+ " WHERE RPNOAF=" + myfunc.AA(Tmp_RPNOAF)
+ " AND EIAC=" + myfunc.AA(Tmp_EIAC)
+ " AND EI_LCN=" + myfunc.AA(Tmp_EI_LCN)
+ " AND EI_ALC=" + myfunc.AA(Tmp_EI_ALC)
+ " AND NHA_LCN=" + myfunc.AA(Tmp_NHA_LCN)
+ " AND NHA_ALC=" + myfunc.AA(Tmp_NHA_ALC)
+ " AND LCN=" + myfunc.AA(Tmp_LCN)
+ " AND ALC=" + myfunc.AA(Tmp_ALC)
+ " AND PN=" + myfunc.AA(Tmp_PN)
+ " AND TMNOXX=" + myfunc.AA(Tmp_TMNOXX)
+ " AND FIG=" + myfunc.AA(Tmp_FIG)
+ " AND IND=" + myfunc.AA(Tmp_IND)
+ " AND WP=" + myfunc.AA(Tmp_WP);
Tmp_ITMUOCD=myfunc.SqlValue(Tmp_Sql);
if (!myfunc.checkisnull(Tmp_ITMUOCD)) //Tmp_AMM_UOCD 已存在,則更新
{
Tmp_Sql = " UPDATE AMM_UOCD "
+ " SET EFF='Y' ,"
+ " AMMNO=" + myfunc.AA(Tmp_AMMNO) + ","
+ " TMNOXX= " + myfunc.AA(Tmp_TMNOXX) + ","
+ " FIG= " + myfunc.AA(Tmp_FIG) + ","
+ " IND= " + myfunc.AA(Tmp_IND) + ","
+ " WP= " + myfunc.AA(Tmp_WP) + ","
+ " OVERAHD= " + myfunc.AA(Tmp_OVERAHD) + ","
+ " RSTHAH= " + myfunc.AA(Tmp_RSTHAH) + ","
+ " FRMK= " + myfunc.AA(Tmp_FRMK) + ","
+ " FRNOAF_S= " + myfunc.AA(Tmp_FRNOAF_S) + ","
+ " SN= " + myfunc.AA(Tmp_SN) + ","
+ " FACHRN= " + myfunc.AA(Tmp_FACHRN) + ","
+ " RMRPNOAF= " + myfunc.AA(Tmp_RMRPNOAF) + ","
+ " RMSBSN= " + myfunc.AA(Tmp_RMSBSN) + ","
+ " NQTYAH= " + myfunc.AA(Tmp_NQTYAH) + ","
+ " C_UNIT= " + myfunc.AA(Tmp_C_UNIT) + ","
+ " EMPLYID= " + myfunc.AA(Tmp_EMPLYID) + ","
+ " RDUOCD= " + myfunc.AA(Tmp_RDUOCD) + ","
+ " STAUOCD= " + myfunc.AA(Tmp_STAUOCD) + ","
+ " FASTAT= " + myfunc.AA(Tmp_FASTAT)
+ " WHERE RPNOAF=" + myfunc.AA(Tmp_RPNOAF)
+ " AND EIAC=" + myfunc.AA(Tmp_EIAC)
+ " AND EI_LCN=" + myfunc.AA(Tmp_EI_LCN)
+ " AND EI_ALC=" + myfunc.AA(Tmp_EI_ALC)
+ " AND NHA_LCN=" + myfunc.AA(Tmp_NHA_LCN)
+ " AND NHA_ALC=" + myfunc.AA(Tmp_NHA_ALC)
+ " AND LCN=" + myfunc.AA(Tmp_LCN)
+ " AND ALC=" + myfunc.AA(Tmp_ALC)
+ " AND PN=" + myfunc.AA(Tmp_PN)
+ " AND ITMUOCD=" + myfunc.AA(Tmp_ITMUOCD);
Tmp_Str = Tmp_Str + "," + "更新(AMM_UOCD)";
}
else //不存在資料庫
{
Tmp_Sql= " INSERT INTO AMM_UOCD "
+" (RPNOAF, EIAC, EI_LCN, EI_ALC, NHA_LCN, NHA_ALC, LCN, ALC, PN,"
+" ITMUOCD,"
+ " EFF,AMMNO,TMNOXX,FIG,IND,WP, "
+ " OVERAHD,RSTHAH,FRMK,FRNOAF_S,SN, "
+ " FACHRN,RMRPNOAF,RMSBSN,NQTYAH,C_UNIT, "
+ " EMPLYID,RDUOCD,STAUOCD,FASTAT "
+ ") "
+" SELECT "+ myfunc.AA(Tmp_RPNOAF) + "," + myfunc.AA(Tmp_EIAC) + "," + myfunc.AA(Tmp_EI_LCN) + "," + myfunc.AA(Tmp_EI_ALC) + "," + myfunc.AA(Tmp_NHA_LCN) + "," + myfunc.AA(Tmp_NHA_ALC) + "," + myfunc.AA(Tmp_LCN) + "," + myfunc.AA(Tmp_ALC) + "," + myfunc.AA(Tmp_PN) + ","
//取得 MAX(ITMUOCD)+1
+ " ( SELECT nvl(MAX(TO_NUMBER(ITMUOCD)), 0) + 1 "
+" FROM AMM_UOCD "
+ " WHERE RPNOAF = " +myfunc.AA(Tmp_RPNOAF)
+" AND EIAC = "+ myfunc.AA(Tmp_EIAC)
+" AND EI_LCN = "+ myfunc.AA(Tmp_EI_LCN)
+" AND EI_ALC = " + myfunc.AA(Tmp_EI_ALC)
+ " AND NHA_LCN = " + myfunc.AA(Tmp_NHA_LCN)
+ " AND NHA_ALC = " + myfunc.AA(Tmp_NHA_ALC)
+" AND LCN ="+ myfunc.AA(Tmp_LCN)
+" AND ALC = "+ myfunc.AA(Tmp_ALC)
+" AND PN =" + myfunc.AA(Tmp_PN)+"),"
+ myfunc.AA("Y") + "," + myfunc.AA(Tmp_AMMNO) + "," + myfunc.AA(Tmp_TMNOXX) + "," + myfunc.AA(Tmp_FIG) + "," + myfunc.AA(Tmp_IND) + "," + myfunc.AA(Tmp_WP) + ","
+ myfunc.AA(Tmp_OVERAHD) + "," + myfunc.AA(Tmp_RSTHAH) + "," + myfunc.AA(Tmp_FRMK) + "," + myfunc.AA(Tmp_FRNOAF_S) + "," + myfunc.AA(Tmp_SN) + ","
+ myfunc.AA(Tmp_FACHRN) + "," + myfunc.AA(Tmp_RMRPNOAF) + "," + myfunc.AA(Tmp_RMSBSN) + "," + myfunc.AA(Tmp_NQTYAH) + "," + myfunc.AA(Tmp_C_UNIT) + ","
+ myfunc.AA(Tmp_EMPLYID) + "," + myfunc.AA(Tmp_RDUOCD) + "," + myfunc.AA(Tmp_STAUOCD) + "," + myfunc.AA(Tmp_FASTAT)
+" FROM DUAL ";
Tmp_Str = Tmp_Str + "," + "新增(AMM_UOCD)";
}
SQL_List_A.Add(Tmp_Sql); // 新增/更新 AMM_UOCD
Tmp_AMM_UOCD_LIST.Add(Tmp_AMM_UOCD_PK);
//myfunc.SqlExec(Tmp_Sql);
Tmp_Str = "更新完成!! [ " + Tmp_Str + " ] ";
ws.Range[myfunc.GetExcelPos(44, cur_row)].Value = Tmp_Str;
}
Tmp_rowcnt = Tmp_rowcnt + 1;
} // end of for (int i = 0; i < RowsCount; i++)
// 批次執行 SQL , 若 有誤,則傳回 -1 , 若正確,則傳回 1
Tmp_Str = String.Join(";",SQL_List_A);
Tmp_cnt=excuteSQLTran(SQL_List_A);
//批次執行 SQL 有錯誤
if (Tmp_cnt == -1)
{
Tmp_RtnMsg = getExcMsg();
raiseEXP(new Exception() , Tmp_RtnMsg);
}
} //end of foreach (string cur_FName in Request.Files)
ws.AllocatedRange.AutoFitColumns();
wk.SaveToStream(stream, FileFormat.Version2007);
Tmp_Str = "[非TLS品項批次提料]完成!!(共更新" + Tmp_rowcnt.ToString() + "筆)<br>"
+ "匯入檔案名稱(" + Tmp_FName + ")";
MyCookie = new HttpCookie("Rtn_Msg", HttpUtility.UrlEncode(Tmp_Str));
//MyCookie.Value = HttpUtility.UrlEncode(Tmp_Str);
HttpContext.Current.Response.Cookies.Add(MyCookie);
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=\"" + HttpUtility.UrlEncode(Tmp_out_FName, System.Text.Encoding.UTF8) + "\"");
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
HttpContext.Current.Response.BinaryWrite(stream.ToArray());
HttpContext.Current.Response.End();
} // end fo try{
catch (Exception e)
{
//Console.WriteLine(e);
Tmp_RtnMsg = "錯誤訊息如下:"
+ e.Message;
MyCookie.Value = HttpUtility.UrlEncode(Tmp_RtnMsg);
HttpContext.Current.Response.Cookies.Add(MyCookie);
HttpContext.Current.Response.End();
//throw;
}
finally
{
wk.Dispose();
//reader.Dispose();
cmd.Dispose();
conn.Dispose();
conn.Close();
}
//return response;
//Tmp_Cnt = 6;
} // end of INS_AMM_UOC_UOCD()
3>更新 AMM_UOC 改用 MERGE 方式, 不用自行處理 - Tmp_AMM_UOC_LIST.IndexOf(Tmp_AMM_UOC_PK)
//- 寫入資料庫時,若不存在,則 Insert ,
//若已存在,則Update - ORACLE 採用 MERGE 語法 - MYSQL 採用 ON DUPLICATE KEY UPDATE
//2025/04/10 改用 MERGE 語法處理 , 不採用自行處理 - Tmp_AMM_UOC_LIST.IndexOf(Tmp_AMM_UOC_PK)
Tmp_Sql = " MERGE INTO AMM_UOC B "
+" USING (SELECT "+myfunc.AA(Tmp_RPNOAF)+" AS RPNOAF, "
+myfunc.AA(Tmp_EIAC)+ " AS EIAC, "
+ myfunc.AA(Tmp_EI_LCN) + "AS EI_LCN,"
+ myfunc.AA(Tmp_EI_ALC) + " AS EI_ALC, "
+ myfunc.AA(Tmp_NHA_LCN) + " AS NHA_LCN,"
+ myfunc.AA(Tmp_NHA_ALC) + " AS NHA_ALC,"
+ myfunc.AA(Tmp_LCN) + " AS LCN,"
+ myfunc.AA(Tmp_ALC) + " AS ALC,"
+ myfunc.AA(Tmp_PN) + " AS PN,"
+ myfunc.AA(Tmp_AMMNO) + " AS AMMNO,"
+ myfunc.AA(Tmp_RQPN) + " AS RQPN, "
+ myfunc.AA(Tmp_FACPN) + " AS FACPN, "
+ myfunc.AA(Tmp_CAGE) + " AS CAGE, "
+ myfunc.AA(Tmp_NSN) + " AS NSN "
+" FROM dual) A "
+"ON ( B.RPNOAF = A.RPNOAF "
+" AND B.EIAC = A.EIAC "
+" AND B.EI_LCN = A.EI_LCN "
+" AND B.EI_ALC = A.EI_ALC "
+" AND B.NHA_LCN = A.NHA_LCN "
+" AND B.NHA_ALC = A.NHA_ALC "
+" AND B.LCN = A.LCN "
+" AND B.ALC = A.ALC "
+" AND B.PN = A.PN "
+" ) "
+" WHEN MATCHED THEN "
+" UPDATE SET B.AMMNO = A.AMMNO, "
+" B.RQPN = A.RQPN, "
+" B.FACPN = A.FACPN, "
+" B.CAGE = A.CAGE, "
+" B.NSN = A.NSN "
+" WHEN NOT MATCHED THEN "
+" INSERT(RPNOAF, EIAC, EI_LCN, EI_ALC, NHA_LCN, NHA_ALC, LCN, ALC, PN, AMMNO, RQPN,FACPN,CAGE,NSN) "
+" VALUES"
+"(A.RPNOAF, A.EIAC, A.EI_LCN, A.EI_ALC, A.NHA_LCN, A.NHA_ALC, A.LCN, A.ALC, A.PN, A.AMMNO, A.RQPN, A.FACPN,A.CAGE,A.NSN) ";
SQL_List_A.Add(Tmp_Sql);
Tmp_Str = "MERGE(AMM_UOC)";
沒有留言:
張貼留言