xml report part1
CREATE OR REPLACE PACKAGE BODY CUX_XML_TEMPLATE_RPKG AS
Gv_Package_Name CONSTANT VARCHAR2(30) := 'CUX_XML_TEMPLATE_RPKG';
Gv_Debug VARCHAR2(1) := Nvl(Fnd_Profile.VALUE('AFLOG_ENABLED')
,'N');
Gv_Organization_Name Org_Organization_Definitions.Organization_Name%TYPE; --库存组织名称
Gd_Date_Sch_Fr DATE;
Gd_Date_Sch_To DATE;
/*===============================================================
Program Name: Put_Line
Author : xxxx
Created : 2019-01-10
Purpose : 格式化
Parameters :
in Pv_Xml_Str
Return : 返回值说明
Description:
Update History
Version Date Name Description
-------- ---------- --------------- --------------------
V1.0 2019-01-10 xxxx Creation
===============================================================*/
PROCEDURE Put_Line(Pv_Xml_Str VARCHAR2) IS
BEGIN
Fnd_File.Put_Line(Fnd_File.Output
,Pv_Xml_Str);
Dbms_Output.Put_Line(Pv_Xml_Str);
END;
/*===============================================================
Program Name: Replacespecialchar
Author : xxxx
Created : 2019-01-10
Purpose : 格式化
Parameters :
in Pv_Source
Return : 返回值说明
Description:
Update History
Version Date Name Description
-------- ---------- --------------- --------------------
V1.0 2019-01-10 xxxx Creation
===============================================================*/
FUNCTION Replacespecialchar(Pv_Source VARCHAR2) RETURN VARCHAR2 IS
BEGIN
RETURN REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(Pv_Source
,'&'
,'&' || 'amp;')
,'<'
,'&' || 'lt;')
,'>'
,'&' || 'gt;')
,'"'
,'&' || 'quot;')
,''''
,'&' || 'apos;');
END Replacespecialchar;
/*===============================================================
Program Name: Convert_Into_Xml
Author : xxxx
Created : 2019-01-10
Purpose : 格式化
Parameters :
in Pv_Name
in Pv_Value
Return : 返回值说明
Description:
Update History
Version Date Name Description
-------- ---------- --------------- --------------------
V1.0 2019-01-10 xxxx Creation
===============================================================*/
PROCEDURE Convert_Into_Xml(Pv_Name IN VARCHAR2
,Pv_Value IN VARCHAR2) IS
Lv_Convert_Data VARCHAR2(32767);
Lv_Name VARCHAR2(32767) := Replacespecialchar(Pv_Name);
BEGIN
Lv_Convert_Data := '<' || Lv_Name || '>' ||
Replacespecialchar(Pv_Value) || '</' || Lv_Name || '>';
Put_Line(Lv_Convert_Data);
END Convert_Into_Xml;
/*===============================================================
Program Name: get_organization_name
Author : xxxx
Created : 2019-01-10
Purpose : 获取库存组织名称
Parameters :
in Pn_Organization_Id --库存组织ID
Return : 返回业务实体名称
Description:
Update History
Version Date Name Description
-------- ---------- --------------- --------------------
V1.0 2019-01-10 xxxx Creation
===============================================================*/
FUNCTION Get_Organization_Name(Pn_Organization_Id IN NUMBER)
RETURN VARCHAR2 IS
Lv_Procedure_Name VARCHAR2(30) := 'Get_Organization_Name';
Lv_Api VARCHAR2(100) := Gv_Package_Name || '.' ||
Lv_Procedure_Name;
Lv_Ret_Status VARCHAR2(30) := Fnd_Api.g_Ret_Sts_Success;
Lv_Ret_Message VARCHAR2(2000);
Lv_Organization_Name VARCHAR2(240);
BEGIN
SELECT Ood.Organization_Name
INTO Lv_Organization_Name
FROM Org_Organization_Definitions Ood
WHERE Ood.Organization_Id = Pn_Organization_Id;
RETURN Lv_Organization_Name;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END;
/*===============================================================
Program Name: Process_Rpt_Data
Author : xxxx
Created: : 2019-01-10
Purpose : WIP电极加工任务卡单据输出程序
Parameters :
IN Pn_Organization_Id 库存组织id
IN PV_MODEL_NUMBER 模具号
IN PV_PLAN_GROUP 计划组
IN PV_ENTITY_NUMBER_F 工单号从
IN PV_ENTITY_NUMBER_T 工单号从
IN PV_SCHDULE_DATE_F 计划日期从
IN PV_SCHDULE_DATE_T 计划日期至
Return :返回值说明
Description:
Update History
Version Date Name Description
-------- ---------- --------------- --------------------
V1.0 2019-01-10 xxxx Creation
===============================================================*/
PROCEDURE Process_Rpt_Data(Pn_Organization_Id IN NUMBER
,Pv_Model_Number IN VARCHAR2
,Pv_Plan_Group IN VARCHAR2
,Pv_Entity_Number_f IN VARCHAR2
,Pv_Entity_Number_t IN VARCHAR2
,Pv_Schdule_Date_f IN DATE
,Pv_Schdule_Date_t IN DATE
,Xv_Ret_Status OUT VARCHAR2
,Xv_Ret_Message OUT VARCHAR2) IS
Lv_Procedure_Name VARCHAR2(30) := 'Process_Rpt_Data';
Lv_Api VARCHAR2(100) := Gv_Package_Name || '.' ||
Lv_Procedure_Name;
Lv_Ret_Status VARCHAR2(30) := Fnd_Api.g_Ret_Sts_Success;
Lv_Ret_Message VARCHAR2(2000);
Lv_Form_No VARCHAR2(100);
Lv_Company_Name VARCHAR2(100);
Ln_Org_Seq NUMBER;
Ln_Form_Seq NUMBER;
Ln_Length NUMBER;
i NUMBER;
Lv_Print VARCHAR2(5000);
Lv_Sy VARCHAR2(5000);
Ln_Row_Num NUMBER := 0; --行数
--定义游标一：头部信息
CURSOR Lcur_Jobs_Info IS
SELECT t.Organization_Id
,t.Completion_Subinventory --完工子库
,Msi.Description Completion_Sub_Desc --子库描述
,t.Schedule_Group_Id --计划组ID
,Wsg.Schedule_Group_Name --计划组
,t.Created_By
,Dd.Full_Name Create_User --编制
FROM dual;
CURSOR Lcur_Jobs_Line(Pn_Organization_Id NUMBER, Pn_Schedule_Group_Id NUMBER) IS
SELECT Wdj.Organization_Id
,Wdj.Wip_Entity_Id
,We.Wip_Entity_Name --工单号
,Msib.Segment1 Mtl_Code --产品编码
,Msib.Description Mtl_Desc --产品描述
,Wdj.Start_Quantity --计划生产数
,Wdj.Attribute8 Mould_Number --模具编号
,Wmt.Model_Desc Mould_Desc --模具名称
,To_Char(Wdj.Scheduled_Start_Date
,'YYYY-MM-DD HH24:MI:SS') First_Date --计划开始日期
,To_Char(Wdj.Scheduled_Completion_Date
,'YYYY-MM-DD HH24:MI:SS') Last_Date --计划完成日期
,Decode(Wdj.Status_Type
,3
,'已释放'
,4
,'完成'
,Wdj.Status_Type) Status --工单状态
,Wo.Operation_Seq_Num
FROM dual;
BEGIN
cux_Fnd_Log.Event(Lv_Api
,Lv_Procedure_Name || cux_Fnd_Log.Gv_Begin);
--xml起始文本
Put_Line('<?xml version="1.0" encoding="UTF-8"?><UNUSUAL>');
--第一层数据
FOR t_Header_Rec IN Lcur_Jobs_Info LOOP
--初始化
Lv_Form_No := NULL;
Lv_Company_Name := NULL;
Put_Line('<HEADER>');
Convert_Into_Xml('REPORT_TITLE'
,Lv_Company_Name); --题头
Convert_Into_Xml('SYSTEM_NUM'
,Lv_Form_No); --体系编号
Convert_Into_Xml('SCHEDULE_GROUP_NAME'
,t_Header_Rec.Schedule_Group_Name); --计划组
Convert_Into_Xml('COMPLETION_SUBINVENTORY'
,t_Header_Rec.Completion_Subinventory || '-' ||
t_Header_Rec.Completion_Sub_Desc); --接收子库
Convert_Into_Xml('CREATE_USER'
,t_Header_Rec.Created_By || '-' ||
t_Header_Rec.Create_User); --编制
Ln_Length := 0;
i := 1;
Lv_Print := '';
Lv_Sy := '';
FOR t_Line_Rec IN Lcur_Jobs_Line(t_Header_Rec.Organization_Id
,t_Header_Rec.Schedule_Group_Id) LOOP
Ln_Row_Num := Ln_Row_Num + 1;
Put_Line('<LINE>');
Convert_Into_Xml('SEQ_NO'
,Ln_Row_Num); --序号
Convert_Into_Xml('MOULD_NUMBER'
,t_Line_Rec.Mould_Number); --模具编号
Convert_Into_Xml('MTL_CODE'
,t_Line_Rec.Mtl_Code); --产品编码
BEGIN
SELECT Length(t_Line_Rec.Mtl_Desc)
INTO Ln_Length
FROM Dual;
i := 1;
Lv_Sy := t_Line_Rec.Mtl_Desc;
FOR i IN 1 .. 3 LOOP
Lv_Print := Substr(Lv_Sy
,1
,30);
Lv_Sy := Substr(Lv_Sy
,31
,Length(t_Line_Rec.Mtl_Desc) - 30);
Put_Line('<MTL_DESC>');
Convert_Into_Xml('MTL_DESC'
,Lv_Print); --产品描述
Put_Line('</MTL_DESC>');
END LOOP;
END;
Convert_Into_Xml('START_QUANTITY'
,t_Line_Rec.Start_Quantity); --计划生产数
Convert_Into_Xml('STATUS'
,t_Line_Rec.Status); --任务状态
Convert_Into_Xml('FIRST_DATE'
,t_Line_Rec.First_Date); --计划开始时间
Convert_Into_Xml('LAST_DATE'
,t_Line_Rec.Last_Date); --计划结束时间
Convert_Into_Xml('WIP_ENTITY_NAME'
,t_Line_Rec.Wip_Entity_Name); --工单号
Convert_Into_Xml('OPERATION_SEQ_NUM'
,t_Line_Rec.Operation_Seq_Num); --工序号
Convert_Into_Xml('SEQ_CODE'
,Cux_Strtocode_Pkg.Strtocode128a(t_Line_Rec.Wip_Entity_Id || '-' ||
t_Line_Rec.Operation_Seq_Num)); --工单工序条形码
Put_Line('</LINE>');
END LOOP;
Put_Line('</HEADER>');
END LOOP; --for t_header_rec in lcur_po_header loop
Put_Line('</UNUSUAL>');
Xv_Ret_Status := Lv_Ret_Status;
cux_Fnd_Log.Event(Lv_Api
,Lv_Procedure_Name || cux_Fnd_Log.Gv_End);
EXCEPTION
WHEN OTHERS THEN
cux_Fnd_Exception.Handle_Exception(Pv_Api => Lv_Api
,Xv_Ret_Status => Xv_Ret_Status
,Xv_Ret_Message => Xv_Ret_Message);
END;
/*===============================================================
Program Name: process_request
Author : xxxx
Created : 2019-01-10
Purpose : 调用的xml请求的输出
Parameters :
in Pn_Organization_Id 库存组织id
in PV_MODEL_NUMBER 模具号
in PV_PLAN_GROUP 计划组
in PV_ENTITY_NUMBER_F 工单号从
in PV_ENTITY_NUMBER_T 工单号从
in PV_SCHDULE_DATE_F 计划日期从
in PV_SCHDULE_DATE_T 计划日期至
Return : 返回值说明
Description:
Update History
Version Date Name Description
-------- ---------- --------------- --------------------
V1.0 2019-01-10 xxxx Creation
===============================================================*/
PROCEDURE Process_Request(Pn_Organization_Id IN NUMBER
,Pv_Model_Number IN VARCHAR2
,Pv_Plan_Group IN VARCHAR2
,Pv_Entity_Number_f IN VARCHAR2
,Pv_Entity_Number_t IN VARCHAR2
,Pv_Schdule_Date_f IN VARCHAR2
,Pv_Schdule_Date_t IN VARCHAR2
,Xv_Ret_Status OUT VARCHAR2
,Xv_Ret_Message OUT VARCHAR2) IS
Lv_Procedure_Name VARCHAR2(30) := 'Process_Request';
Lv_Api VARCHAR2(100) := Gv_Package_Name || '.' ||
Lv_Procedure_Name;
Lv_Ret_Status VARCHAR2(30) := Fnd_Api.g_Ret_Sts_Success;
Lv_Ret_Message VARCHAR2(2000);
BEGIN
cux_Fnd_Log.Event(Lv_Api
,Lv_Procedure_Name || cux_Fnd_Log.Gv_Begin);
--Output Data from cursor 报表数据
cux_Fnd_Log.Step(Lv_Api
,'1.Output Data from cursor');
Process_Rpt_Data(Pn_Organization_Id => Pn_Organization_Id
,Pv_Model_Number => Pv_Model_Number
,Pv_Plan_Group => Pv_Plan_Group
,Pv_Entity_Number_f => Pv_Entity_Number_f
,Pv_Entity_Number_t => Pv_Entity_Number_t
,Pv_Schdule_Date_f => Gd_Date_Sch_Fr --Pv_Schdule_Date_f
,Pv_Schdule_Date_t => Gd_Date_Sch_To --Pv_Schdule_Date_t
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
cux_Fnd_Exception.Raise_Exception(Pv_Ret_Status => Lv_Ret_Status
,Pv_Ret_Message => Lv_Ret_Message);
cux_Fnd_Log.Event(Lv_Api
,Lv_Procedure_Name || cux_Fnd_Log.Gv_End);
EXCEPTION
WHEN OTHERS THEN
cux_Fnd_Exception.Handle_Exception(Pv_Api => Lv_Api
,Xv_Ret_Status => Xv_Ret_Status
,Xv_Ret_Message => Xv_Ret_Message);
END Process_Request;
/*===============================================================
Program Name: parameter_check
Author : xxxx
Created : 2019-01-10
Purpose : 参数检查
Parameters :
in Pn_Organization_Id 库存组织id
in PV_MODEL_NUMBER 模具号
in PV_PLAN_GROUP 计划组
in PV_ENTITY_NUMBER_F 工单号从
in PV_ENTITY_NUMBER_T 工单号从
in PV_SCHDULE_DATE_F 计划日期从
in PV_SCHDULE_DATE_T 计划日期至
Return : 返回值说明
Description:
Update History
Version Date Name Description
-------- ---------- --------------- --------------------
V1.0 2019-01-10 xxxx Creation
===============================================================*/
PROCEDURE Parameter_Check(Pn_Organization_Id IN NUMBER
,Pv_Model_Number IN VARCHAR2
,Pv_Plan_Group IN VARCHAR2
,Pv_Entity_Number_f IN VARCHAR2
,Pv_Entity_Number_t IN VARCHAR2
,Pv_Schdule_Date_f IN VARCHAR2
,Pv_Schdule_Date_t IN VARCHAR2
,Xv_Ret_Status OUT VARCHAR2
,Xv_Ret_Message OUT VARCHAR2) IS
Lv_Procedure_Name VARCHAR2(30) := ' Parameter_Check';
Lv_Api VARCHAR2(100) := Gv_Package_Name || '.' ||
Lv_Procedure_Name;
Lv_Ret_Status VARCHAR2(30) := Fnd_Api.g_Ret_Sts_Success;
Lv_Ret_Message VARCHAR2(2000);
BEGIN
cux_Fnd_Log.Event(Lv_Api
,Lv_Procedure_Name || cux_Fnd_Log.Gv_Begin);
cux_Fnd_Log.Step(Lv_Api
,'1.Check Organization_Name ');
--获取库存组织名称
IF Pn_Organization_Id IS NULL THEN
cux_Fnd_Exception.Raise_Error(Pv_Api => Lv_Api
,Pv_Status => Fnd_Api.g_Ret_Sts_Unexp_Error
,Pv_Message => 'pn_organization_id Cannot be null.');
ELSE
Gv_Organization_Name := Get_Organization_Name(Pn_Organization_Id);
IF Gv_Organization_Name IS NULL THEN
cux_Fnd_Exception.Raise_Error(Pv_Api => Lv_Api
,Pv_Status => Fnd_Api.g_Ret_Sts_Unexp_Error
,Pv_Message => 'pn_organization_id validation error.' ||
To_Char(Pn_Organization_Id));
END IF;
END IF;
cux_Fnd_Log.Step(Lv_Api
,'2.Date ');
--获取日期全局变量
Gd_Date_Sch_Fr := Fnd_Date.Canonical_To_Date(Pv_Schdule_Date_f);
Gd_Date_Sch_To := Fnd_Date.Canonical_To_Date(Pv_Schdule_Date_t);
cux_Fnd_Log.Event(Lv_Api
,Lv_Procedure_Name || cux_Fnd_Log.Gv_End);
EXCEPTION
WHEN OTHERS THEN
cux_Fnd_Exception.Handle_Exception(Pv_Api => Lv_Api
,Xv_Ret_Status => Xv_Ret_Status
,Xv_Ret_Message => Xv_Ret_Message);
END Parameter_Check;
/*===============================================================
Program Name: main
Author : xxxx
Created : 2019-01-10
Purpose : 并发请求入口主过程
Parameters :
out errbuf 返回信息
out retcode 返回信息代码
in Pn_Organization_Id 库存组织id
in PV_MODEL_NUMBER 模具号
in PV_PLAN_GROUP 计划组
in PV_ENTITY_NUMBER_F 工单号从
in PV_ENTITY_NUMBER_T 工单号从
in PV_SCHDULE_DATE_F 计划日期从
in PV_SCHDULE_DATE_T 计划日期至
Return : 返回值说明
Description:
Update History
Version Date Name Description
-------- ---------- --------------- --------------------
V1.0 2019-01-10 xxxx Creation
===============================================================*/
PROCEDURE Main(Errbuf OUT VARCHAR2
,Retcode OUT VARCHAR2
,Pn_Organization_Id IN NUMBER
,Pv_Model_Number IN VARCHAR2
,Pv_Plan_Group IN VARCHAR2
,Pv_Entity_Number_f IN VARCHAR2
,Pv_Entity_Number_t IN VARCHAR2
,Pv_Schdule_Date_f IN VARCHAR2
,Pv_Schdule_Date_t IN VARCHAR2) IS
Lv_Procedure_Name VARCHAR2(30) := 'MAIN';
Lv_Api VARCHAR2(100) := Gv_Package_Name || '.' ||
Lv_Procedure_Name;
Lv_Ret_Status VARCHAR2(30) := Fnd_Api.g_Ret_Sts_Success;
Lv_Ret_Message VARCHAR2(2000);
BEGIN
--开始
Retcode := cux_Fnd_Log.Gv_Retcode_Exc_Success;
cux_Fnd_Log.Conc_Log_Header;
--cux_Fnd_Log.Debug('---------->1.检查参数合法性(非必需，可注释)');
Parameter_Check(Pn_Organization_Id => Pn_Organization_Id
,Pv_Model_Number => Pv_Model_Number
,Pv_Plan_Group => Pv_Plan_Group
,Pv_Entity_Number_f => Pv_Entity_Number_f
,Pv_Entity_Number_t => Pv_Entity_Number_t
,Pv_Schdule_Date_f => Pv_Schdule_Date_f
,Pv_Schdule_Date_t => Pv_Schdule_Date_t
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
cux_Fnd_Exception.Raise_Exception(Pv_Ret_Status => Lv_Ret_Status
,Pv_Ret_Message => Lv_Ret_Message);
--cux_Fnd_Log.Debug('---------->2.提交生成报表');
Process_Request(Pn_Organization_Id => Pn_Organization_Id
,Pv_Model_Number => Pv_Model_Number
,Pv_Plan_Group => Pv_Plan_Group
,Pv_Entity_Number_f => Pv_Entity_Number_f
,Pv_Entity_Number_t => Pv_Entity_Number_t
,Pv_Schdule_Date_f => Pv_Schdule_Date_f
,Pv_Schdule_Date_t => Pv_Schdule_Date_t
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
cux_Fnd_Exception.Raise_Exception(Pv_Ret_Status => Lv_Ret_Status
,Pv_Ret_Message => Lv_Ret_Message);
--procedure body end
-- concurrent footer log
cux_Fnd_Log.Conc_Log_Footer;
EXCEPTION
--Standard SRS Main Exception Handler
WHEN OTHERS THEN
cux_Fnd_Exception.Handle_Srs_Exception(Pv_Api => Lv_Api
,Errbuf => Errbuf
,Retcode => Retcode);
END Main;
END CUX_XML_TEMPLATE_RPKG;
/
