Master Details or Grouping XML Publisher Report

Pass ID from Master query to Detail Query



<?xml version = '1.0' encoding = 'UTF-8'?>
<dataTemplate name="DK_BOM_DET" defaultPackage="" version="1.0">
<parameters>
   <parameter name="P_ORG_ID" dataType="character"/>  
   <parameter name="p_org_code" dataType="character"/>

</parameters>
<dataQuery>
 <sqlStatement name="Q_BOM">
   <![CDATA[
select
       (select mtl1.segment1||'-'||mtl1.segment2 from mtl_system_items_b mtl1
       where mtl1.inventory_item_id=bill.assembly_item_id and mtl1.ORGANIZATION_ID=bill.ORGANIZATION_ID) Assembly_item,
       bill.ALTERNATE_BOM_DESIGNATOR,
       bill.DESCRIPTION,
       bill.uom,
       org.ORGANIZATION_code,
       org.ORGANIZATION_name,
       bill.ITEM_TYPE,
       bill.ORGANIZATION_ID,
       bill.assembly_item_id
from BOM_BILL_OF_MATERIALS_V bill,
org_organization_definitions org
where  bill.ORGANIZATION_ID=org.ORGANIZATION_ID
and org.OPERATING_UNIT=:P_ORG_ID
and ORG.ORGANIZATION_code = nvl(:p_org_code,ORG.ORGANIZATION_code)
--and bill.assembly_item_id=35548
order by org.ORGANIZATION_code,bill.assembly_item_id,bill.ALTERNATE_BOM_DESIGNATOR

             
   ]]>
   </sqlStatement>
 
 
   <sqlStatement name="Q_COMP">
   <![CDATA[
select
       (select mtl1.segment1||'-'||mtl1.segment2 from mtl_system_items_b mtl1
       where mtl1.inventory_item_id=bill.assembly_item_id and mtl1.ORGANIZATION_ID=bill.ORGANIZATION_ID) Assembly_item,
       bill.ALTERNATE_BOM_DESIGNATOR,
       bill.DESCRIPTION,
       bill.uom,
       org.ORGANIZATION_code,
       org.ORGANIZATION_name,
       bill.ITEM_TYPE,
       bill.ORGANIZATION_ID,
       comp.ITEM_NUM,
       comp.operation_seq_num,
       (select mtl1.segment1||'-'||mtl1.segment2 from mtl_system_items_b mtl1
       where mtl1.inventory_item_id=comp.COMPONENT_ITEM_ID and mtl1.ORGANIZATION_ID=bill.ORGANIZATION_ID) component_item,
       comp.COMPONENT_QUANTITY,
  (comp.COMPONENT_QUANTITY*100) Contribuiton,
       oper.department_code,
       res.RESOURCE_SEQ_NUM ,
       res.RESOURCE_CODE ,
       decode (res.AUTOCHARGE_TYPE,4,'PO Move',2,'Manual',3,'PO Receipt',1,'WIP Move' ) Charge_type,
              (select mtl.segment1||'-'||mtl.segment2 from mtl_system_items_b mtl,BOM_RESOURCES_V b_res
         where  b_res.PURCHASE_ITEM_ID=mtl.INVENTORY_ITEM_ID
         and b_res.ORGANIZATION_ID=mtl.ORGANIZATION_ID
         and b_res.resource_id=res.RESOURCE_ID) item_code,
       (select DESCRIPTION from BOM_RESOURCES_V where resource_id=res.RESOURCE_ID) resoruce_item_desc,
       (select mtl.LIST_PRICE_PER_UNIT from mtl_system_items_b mtl,BOM_RESOURCES_V b_res
         where  b_res.PURCHASE_ITEM_ID=mtl.INVENTORY_ITEM_ID
         and b_res.ORGANIZATION_ID=mtl.ORGANIZATION_ID
         and b_res.resource_id=res.RESOURCE_ID) unit_price
from BOM_BILL_OF_MATERIALS_V bill,
BOM_INVENTORY_COMPONENTS_V comp,
BOM_OPERATIONAL_ROUTINGS_V rout,
BOM_OPERATION_SEQUENCES_V oper,
BOM_OPERATION_RESOURCES_V res,
org_organization_definitions org
where bill.BILL_SEQUENCE_ID=comp.BILL_SEQUENCE_ID
and bill.ASSEMBLY_ITEM_ID=rout.ASSEMBLY_ITEM_ID
AND  (bill.ALTERNATE_BOM_DESIGNATOR= rout.ALTERNATE_ROUTING_DESIGNATOR  or bill.ALTERNATE_BOM_DESIGNATOR is null)
and rout.ROUTING_SEQUENCE_ID=oper.ROUTING_SEQUENCE_ID
and oper.OPERATION_SEQUENCE_ID = res.OPERATION_SEQUENCE_ID(+)
and bill.ORGANIZATION_ID=org.ORGANIZATION_ID
and rout.ORGANIZATION_ID=bill.ORGANIZATION_ID
and org.OPERATING_UNIT=:P_ORG_ID
and comp.DISABLE_DATE is  null
and ORG.ORGANIZATION_code = nvl(:p_org_code,ORG.ORGANIZATION_code)
and bill.assembly_item_id=:assembly_item_id----------------PASS GROUP ID FROM MASTER BLOCK
order by org.ORGANIZATION_code,bill.assembly_item_id,bill.ALTERNATE_BOM_DESIGNATOR,comp.ITEM_NUM

             
   ]]>
   </sqlStatement>

</dataQuery>

<dataStructure>
 


 <group name ="Q_BOM"  source="Q_BOM">

<element name="ASSEMBLY_ITEM" value="ASSEMBLY_ITEM"/>
<element name="ALTERNATE_BOM_DESIGNATOR" value="ALTERNATE_BOM_DESIGNATOR"/>
<element name="DESCRIPTION" value="DESCRIPTION"/>
<element name="UOM" value="UOM"/>
<element name="ORGANIZATION_CODE" value="ORGANIZATION_CODE"/>
          <element name="ORGANIZATION_NAME" value="ORGANIZATION_NAME"/>
<element name="ITEM_TYPE" value="ITEM_TYPE"/>
<element name="ORGANIZATION_ID" value="ORGANIZATION_ID"/>
<element name="ASSEMBLY_ITEM_ID" value="ASSEMBLY_ITEM_ID"/>

 <group name ="Q_COMP"  source="Q_COMP">
<element name="ITEM_NUM" value="ITEM_NUM"/>
<element name="OPERATION_SEQ_NUM" value="OPERATION_SEQ_NUM"/>
<element name="COMPONENT_ITEM" value="COMPONENT_ITEM"/>
<element name="COMPONENT_QUANTITY" value="COMPONENT_QUANTITY"/>
<element name="CONTRIBUITON" value="CONTRIBUITON"/>
<element name="DEPARTMENT_CODE" value="DEPARTMENT_CODE"/>
<element name="RESOURCE_SEQ_NUM" value="RESOURCE_SEQ_NUM"/>
<element name="RESOURCE_CODE" value="RESOURCE_CODE"/>
<element name="CHARGE_TYPE" value="CHARGE_TYPE"/>
<element name="ITEM_CODE" value="ITEM_CODE"/>
<element name="RESORUCE_ITEM_DESC" value="RESORUCE_ITEM_DESC"/>
<element name="UNIT_PRICE" value="UNIT_PRICE"/>

              </group>

 </group>
 

</dataStructure>
</dataTemplate>