BIP_开发案例02_BI Publisher中复杂案例实现代码（案例）

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