2014-12-27 Created By BaoXinjian
1. 创建数据源
<?xml version ="1.0" encoding="ISO-8859-1"?>
<!-- =======================================================================-->
<!-- Program : $XXWIP_4409_ROUTING_CARD$ -->
<!-- Purpose : Data definition of Work Order Structure -->
<!-- Revision : $1.0$ -->
<!-- Programmer : $BaoXinjian$ -->
<!-- -->
<!-- Table Accessed : Table SEL INS UPD DEL -->
<!-- xxwip.xxwip_4409_jobs_gt Y N N N -->
<!-- xxwip.xxwip_4409_components_gt Y N N N -->
<!-- xxwip.xxwip_4409_resources_gt Y N N N -->
<!-- -->
<!-- Parameters : P_ORGANIZATION_ID -->
<!-- P_ASSEMBLY_FROM -->
<!-- P_ASSEMBLY_TO -->
<!-- P_JOB_FROM -->
<!-- P_JOB_TO -->
<!-- P_COLLECTION_PLAN_ID -->
<!-- -->
<!-- Change History : Revision Date Edit by Comments -->
<!-- 1.0 17-SEP-2010 BaoXinjianInitial revision -->
<!-- =======================================================================-->
<dataTemplate name = "XXWIP_4409_WO_STRUCTURE"
description = "Work Order Structure"
defaultPackage = "XXWIP.XXWIP_4409_WO_STRUCTURE_PKG"
version= "1.0">
<parameters>
<!-- 2011/06/22 V1.3 DEL Start -->
<!-- <parameter name = "P_ORGANIZATION_ID" dataType = "character"/> -->
<!-- 2011/06/22 V1.3 DEL Start -->
<parameter name = "P_ASSEMBLY_FROM" dataType = "character"/>
<parameter name = "P_ASSEMBLY_TO" dataType = "character"/>
<parameter name = "P_JOB_FROM" dataType = "character"/>
<parameter name = "P_JOB_TO" dataType = "character"/>
<!-- 2011/06/10 V1.1 ADD Start -->
<parameter name = "P_DATE_FROM" dataType = "character"/>
<parameter name = "P_DATE_TO" dataType = "character"/>
<parameter name = "P_COLLECTION_PLAN_ID" dataType = "character"/>
<!-- 2011/06/10 V1.1 ADD End -->
</parameters>
<dataQuery>
<sqlStatement name ="Q1">
<![CDATA[
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY') current_date
,xjg.wip_entity_id wip_entity_id
,xjg.organization_id organization_id
,xjg.product_number product_number
,xjg.product_description product_description
,xjg.job_number job_number
--===2011/06/21 V1.2 MOD Start==================
--,xjg.quantity quantity
,xjg.quantity quantity
--===2011/06/21 V1.2 MOD End====================
,xjg.so_number so_number
,xjg.uom_code uom_code
--===2011/06/10 V1.1 MOD Start==================
--,TO_CHAR(xjg.job_start_date
-- ,'DD-MON-YYYY, HH:MI am') job_start_date
,TO_CHAR(xjg.job_start_date
,'DD-MON-YYYY') job_start_date
--===2011/06/10 V1.1 MOD End====================
,xjg.customer_name customer_name
--===2011/06/10 V1.1 MOD Start==================
--,TO_CHAR(xjg.job_finish_date
-- ,'DD-MON-YYYY, HH:MI am') job_finish_date
,TO_CHAR(xjg.job_finish_date
,'DD-MON-YYYY') job_finish_date
--===2011/06/10 V1.1 MOD End====================
,xjg.serial_number serial_number
FROM xxwip.xxwip_4409_jobs_gt xjg
ORDER BY xjg.job_start_date
,xjg.job_number
/*******V1.1 Add START 2011/5/20**********/
,xjg.so_number
,xjg.customer_name
,xjg.serial_number
/*******V1.1 Add End 2011/5/20************/
]]>
</sqlStatement>
<sqlStatement name ="Q2">
<![CDATA[
/*******V1.1 Modify START 2011/5/20**********/
--SELECT DISTINCT xcg.operation_sequence operation_sequence
--FROM xxwip.xxwip_4409_components_gt xcg
--WHERE xcg.wip_entity_id = :WIP_ENTITY_ID
--AND xcg.organization_id = :ORGANIZATION_ID
--ORDER BY xcg.operation_sequence
SELECT wor.operation_seq_num AS operation_sequence
FROM wip_operation_resources wor
WHERE wor.organization_id = :ORGANIZATION_ID
AND wor.wip_entity_id = :WIP_ENTITY_ID
UNION
SELECT DISTINCT xcg.operation_sequence AS operation_sequence
FROM xxwip.xxwip_4409_components_gt xcg
WHERE xcg.wip_entity_id = :WIP_ENTITY_ID
AND xcg.organization_id = :ORGANIZATION_ID
ORDER BY operation_sequence
/*******V1.1 Modify End 2011/5/20*************/
]]>
</sqlStatement>
<sqlStatement name ="Q3">
<![CDATA[
SELECT xcg.operation_sequence component_operation_sequence
,xcg.component component
,xcg.component_description component_description
--===2011/06/21 V1.2 MOD Start==================
--,xcg.qty qty
,xcg.qty * :quantity AS qty
--===2011/06/21 V1.2 MOD End====================
,xcg.uom uom
--===2011/06/10 V1.1 MOD Start==================
--,TO_CHAR(xcg.reserved_date
-- ,'DD-MON-YYYY, HH:MI am') reserved_date
,TO_CHAR(xcg.reserved_date
,'DD-MON-YYYY') reserved_date
--===2011/06/10 V1.1 MOD End====================
,xcg.subinventory subinv
,xcg.locator locator
,xcg.revision
FROM xxwip.xxwip_4409_components_gt xcg
WHERE xcg.wip_entity_id = :WIP_ENTITY_ID
AND xcg.organization_id = :ORGANIZATION_ID
AND xcg.operation_sequence = :OPERATION_SEQUENCE
ORDER BY xcg.operation_sequence
]]>
</sqlStatement>
<sqlStatement name ="Q4">
<![CDATA[
SELECT xrg.operation_sequence resource_operation_sequence
,xrg.resource_code resource_code
,xrg.operation_description operation_description
--===2011/06/21 V1.2 MOD Start==================
--,xrg.run_time run_time
,xrg.run_time * :quantity AS run_time
--===2011/06/10 V1.1 MOD End====================
--===2011/06/10 V1.1 MOD Start==================
--,TO_CHAR(xrg.operation_start_date
-- ,'DD-MON-YYYY, HH:MI am') operation_start_date
,TO_CHAR(xrg.operation_start_date
,'DD-MON-YYYY') operation_start_date
--===2011/06/10 V1.1 MOD End====================
FROM xxwip.xxwip_4409_resources_gt xrg
WHERE xrg.wip_entity_id = :WIP_ENTITY_ID
AND xrg.organization_id = :ORGANIZATION_ID
AND xrg.operation_sequence = :OPERATION_SEQUENCE
ORDER BY xrg.operation_sequence
,xrg.operation_start_date
]]>
</sqlStatement>
<!-- 2011/06/10 V1.1 ADD Start -->
<sqlStatement name = "Q5">
<![CDATA[
SELECT qr.character5 serial_number
FROM qa_results qr
WHERE qr.plan_id = :P_COLLECTION_PLAN_ID
AND qr.wip_entity_id = :WIP_ENTITY_ID
AND qr.organization_id = :ORGANIZATION_ID
ORDER BY qr.serial_number
]]>
</sqlStatement>
<sqlStatement name = "Q6">
<![CDATA[
SELECT xrg.resource_code res,
SUM(xrg.run_time * xjg.quantity) qty
FROM xxwip.xxwip_4409_resources_gt xrg,
xxwip.xxwip_4409_jobs_gt xjg
WHERE xrg.wip_entity_id = :WIP_ENTITY_ID
AND xrg.organization_id = :ORGANIZATION_ID
AND xjg.wip_entity_id = xrg.wip_entity_id
AND xjg.organization_id = xrg.organization_id
GROUP BY xrg.resource_code
]]>
</sqlStatement>
<!-- 2011/06/10 V1.1 ADD End -->
</dataQuery>
<dataTrigger name = "beforeReport"
source = "xxwip.xxwip_4409_wo_structure_pkg.before_report_trigger"/>
<dataStructure>
<group name = "G1" source = "Q1">
<element name = "CURRENT_DATE" value = "CURRENT_DATE"/>
<element name = "WIP_ENTITY_ID" value = "WIP_ENTITY_ID"/>
<element name = "ORGANIZATION_ID" value = "ORGANIZATION_ID"/>
<element name = "PRODUCT_NUMBER" value = "PRODUCT_NUMBER"/>
<element name = "PRODUCT_DESCRIPTION" value = "PRODUCT_DESCRIPTION"/>
<element name = "JOB_NUMBER" value = "JOB_NUMBER"/>
<element name = "QUANTITY" value = "QUANTITY"/>
<element name = "SO_NUMBER" value = "SO_NUMBER"/>
<element name = "UOM_CODE" value = "UOM_CODE"/>
<element name = "JOB_START_DATE" value = "JOB_START_DATE"/>
<element name = "CUSTOMER_NAME" value = "CUSTOMER_NAME"/>
<element name = "JOB_FINISH_DATE" value = "JOB_FINISH_DATE"/>
<!-- 2011/06/10 V1.1 DEL Start -->
<!--<element name = "SERIAL_NUMBER" value = "SERIAL_NUMBER"/>-->
<!-- 2011/06/10 V1.1 DEL End -->
<group name = "G2" source = "Q2">
<element name = "OPERATION_SEQUENCE" value = "OPERATION_SEQUENCE"/>
<group name = "G3" source = "Q3">
<element name = "COMPONENT_OPERATION_SEQUENCE" value = "COMPONENT_OPERATION_SEQUENCE"/>
<element name = "COMPONENT" value = "COMPONENT"/>
<element name = "COMPONENT_DESCRIPTION" value = "COMPONENT_DESCRIPTION"/>
<element name = "QTY" value = "QTY"/>
<element name = "UOM" value = "UOM"/>
<element name = "RESERVED_DATE" value = "RESERVED_DATE"/>
<element name = "SUBINV" value = "SUBINV"/>
<element name = "LOCATOR" value = "LOCATOR"/>
<element name = "REVISION" value = "REVISION"/>
</group>
<group name = "G4" source = "Q4">
<element name = "RESOURCE_OPERATION_SEQUENCE" value = "RESOURCE_OPERATION_SEQUENCE"/>
<element name = "RESOURCE_CODE" value = "RESOURCE_CODE"/>
<element name = "OPERATION_DESCRIPTION" value = "OPERATION_DESCRIPTION"/>
<element name = "RUN_TIME" value = "RUN_TIME"/>
<element name = "OPERATION_START_DATE" value = "OPERATION_START_DATE"/>
</group>
</group>
<!-- 2011/06/10 V1.1 ADD Start -->
<group name = "G5" source = "Q5">
<element name = "SERIAL_NUMBER" value = "SERIAL_NUMBER"/>
</group>
<group name = "G6" source = "Q6">
<element name = "RES" value = "RES"/>
<element name = "QTY" value = "QTY"/>
</group>
<!-- 2011/06/10 V1.1 ADD End -->
</group>
</dataStructure>
</dataTemplate>
2. 创建RTF模板
3. 浏览字段
Thhanks and Regards
技术交流，技术讨论，欢迎加入
Technology Blog Created By Oracle ERP - 鲍新建
