测试脚本

DECLARE
--离散任务表查工单
CURSOR Cur_Header IS
SELECT We.Wip_Entity_Name, --工单号
Wdj.Primary_Item_Id Pri_Id, --装配件物料ID
Wdj.Date_Released, --日期
Wdj.Start_Quantity, --数量
Wdj.Description --返工原因

FROM Wip_Discrete_Jobs Wdj, Wip_Entities We
WHERE 1 = 1
AND Wdj.Wip_Entity_Id = We.Wip_Entity_Id
AND We.Organization_Id = 121 --传入参数：组织ID
AND We.Wip_Entity_Name = 'R121B076' --随便找一个工单就行了，只要满足组件有多个成品/子装配件
AND We.Organization_Id = Wdj.Organization_Id
AND Wdj.Status_Type = 3; --已发放

CURSOR Cur_Compo(t_Mo VARCHAR2) IS
SELECT Wro.Inventory_Item_Id Compo_Id, --组件物料ID
Wro.Supply_Subinventory, --领料工序
Wro.Required_Quantity, --领料数量
We.Wip_Entity_Name
FROM Wip_Discrete_Jobs Wdj,
Wip_Entities We,
Wip_Requirement_Operations Wro,
Mtl_System_Items_b Msi
WHERE 1 = 1
AND Wdj.Wip_Entity_Id = We.Wip_Entity_Id
AND Wdj.Wip_Entity_Id = Wro.Wip_Entity_Id
AND We.Organization_Id = 121 --传入参数：组织ID
AND We.Wip_Entity_Name = t_Mo --随便找一个工单就行了，只要满足组件有多个成品/子装配件
AND We.Organization_Id = Wdj.Organization_Id
AND Wdj.Organization_Id = Wro.Organization_Id
AND Msi.Inventory_Item_Id = Wro.Inventory_Item_Id
AND Msi.Organization_Id = Wro.Organization_Id
AND Wro.Required_Quantity > 0 --领料
AND Msi.Item_Type IN ('FG', 'SA')
AND (Wro.Required_Quantity <> Wdj.Start_Quantity OR
Wro.Inventory_Item_Id <> Wdj.Primary_Item_Id)
AND Wdj.Status_Type = 3; --已发放

--收件人
CURSOR Cur_Mail(t_Org_Code VARCHAR2, t_Type VARCHAR2) IS
SELECT Flv.Description Mail
FROM Fnd_Lookup_Values Flv
WHERE Flv.Lookup_Type = 'XXTP_EMAIL_MO_CHANGE' --收件人维护挂在WIP模块客制菜单
AND Substr(Flv.Lookup_Code, 1, 3) = t_Org_Code
AND Flv.Language = 'ZHS'
AND Flv.Enabled_Flag = 'Y'
AND Nvl(Flv.Tag, 'N') = t_Type;

l_Line Xxtp_Html_Test.Line_Tbl_Type;
l_Header Xxtp_Html_Test.Header_Tbl_Type;
l_Sign Xxtp_Html_Test.Sign_Rec_Type;
l_Mail_Rec Xxtp_Html_Test.Mail_Rec_Type;
l_Error Xxtp_Html_Test.Error_Info_Tbl_Type;
l_Org_Code VARCHAR2(10);
l_Reciever VARCHAR2(4000);
l_Copyer VARCHAR2(4000);
l_Jixing_After VARCHAR2(100); --返工后机型
l_Jixing_Before VARCHAR2(100); --返工前机型
l_Pri_Segment1 VARCHAR2(100);
l_Compo_Segment1 VARCHAR2(100);
l_Index NUMBER;
l_Header_Num NUMBER;
l_Count NUMBER := 0;
l_Flag NUMBER := 0;
l_Temp VARCHAR2(100) := 'test';
l_Num_p VARCHAR2(50);
BEGIN
l_Org_Code := Xxtp_Inv_Util_Pkg.Get_Orgcode_By_Orgid(121);
Dbms_Output.Put_Line('开始发送邮件');
--收件人，多个收件人用逗号隔开
FOR r_Reciever IN Cur_Mail(l_Org_Code, 'N') LOOP
l_Reciever := l_Reciever || CASE
WHEN l_Reciever IS NOT NULL THEN
','
END || r_Reciever.Mail;
END LOOP;

--抄送人，多个抄送人用逗号隔开
FOR r_Copyer IN Cur_Mail(l_Org_Code, 'Y') LOOP
l_Copyer := l_Copyer || CASE
WHEN l_Copyer IS NOT NULL THEN
','
END || r_Copyer.Mail;
END LOOP;

--签名
l_Sign.Text := '普联技术有限公司，TP-LINK TECHNOLOGIES CO,地址：深圳市南山区科技园中区科苑路5号南楼，网址：www.tp-link.com.cn';
l_Sign.Color := 'gray';

l_Header_Num := l_Header.Count;
l_Header(l_Header_Num).Text := '如下MDS调整帮忙今天下班前处理下，感谢~';
l_Header(l_Header_Num).Wsize := 14;

--正文表格
l_Line.Delete;
l_Index := l_Line.Count;
l_Line(l_Index).Attribute1 := '返工单号';
l_Line(l_Index).Attribute2 := '原成品代码';
l_Line(l_Index).Attribute3 := '原机型名称';
l_Line(l_Index).Attribute4 := 'MDS调整';

l_Line(l_Index).Attribute5 := '改制后成品代码';
l_Line(l_Index).Attribute6 := '改制后机型型号';
l_Line(l_Index).Attribute7 := 'MDS调整';

FOR r_Cur_Header IN Cur_Header LOOP
--返工后的机型,装配件料号
BEGIN
SELECT Msi.Attribute4, Msi.Segment1
INTO l_Jixing_After, l_Pri_Segment1
FROM Mtl_System_Items_b Msi
WHERE Msi.Inventory_Item_Id = r_Cur_Header.Pri_Id
AND Msi.Organization_Id = 121;
EXCEPTION
WHEN OTHERS THEN
l_Jixing_After := NULL;
l_Pri_Segment1 := NULL;
END;

l_Index := l_Line.Count;
l_Line(l_Index).Attribute5 := l_Pri_Segment1;
l_Line(l_Index).Attribute6 := l_Jixing_After;
l_Line(l_Index).Attribute7 := r_Cur_Header.Start_Quantity;

FOR r IN Cur_Compo(r_Cur_Header.Wip_Entity_Name) LOOP
l_Flag := l_Flag + 1;
END LOOP;

FOR r_Cur_Compo IN Cur_Compo(r_Cur_Header.Wip_Entity_Name) LOOP
--返工前的机型,组件料号
BEGIN
SELECT Msi.Attribute4, Msi.Segment1
INTO l_Jixing_Before, l_Compo_Segment1
FROM Mtl_System_Items_b Msi
WHERE Msi.Inventory_Item_Id = r_Cur_Compo.Compo_Id
AND Msi.Organization_Id = 121;
EXCEPTION
WHEN OTHERS THEN
l_Jixing_Before := NULL;
l_Compo_Segment1 := NULL;
END;

l_Count := l_Count + 1;
--l_Index := l_Line.Count;
l_Line(l_Index).Attribute1 := r_Cur_Compo.Wip_Entity_Name;
l_Line(l_Index).Attribute2 := l_Compo_Segment1;
l_Line(l_Index).Attribute3 := l_Jixing_Before;
l_Line(l_Index).Attribute4 := To_Char(0 -
To_Number(r_Cur_Compo.Required_Quantity)); --负数

IF l_Count < l_Flag THEN
l_Index := l_Line.Count;
END IF;

END LOOP;
END LOOP;
l_Header(l_Header_Num).Line_Info := l_Line;

--主题
l_Mail_Rec.Subject := 'MDS调整';
--收件人
l_Mail_Rec.Receiver := l_Reciever;
--抄送人
l_Mail_Rec.Copy_Receiver := l_Copyer;
l_Mail_Rec.Header_Info := l_Header;
l_Mail_Rec.Sign_Info := l_Sign;

--发送邮件
Xxtp_Html_Test.Send_Mail(p_Mail_Rec => l_Mail_Rec,
x_Error_Info_Tbl => l_Error);

--查看邮件是否发送成功
IF l_Error.Count = 0 THEN
Dbms_Output.Put_Line('邮件格式检查通过');
ELSE
FOR i IN l_Error.First .. l_Error.Last LOOP
Dbms_Output.Put_Line('邮件发送失败');
Dbms_Output.Put_Line(l_Error(i));
END LOOP;
END IF;
EXCEPTION
WHEN OTHERS THEN
Dbms_Output.Put_Line('邮件发送失败');
Dbms_Output.Put_Line(Dbms_Utility.Format_Error_Backtrace);
END;