Hi all,
i am trying to get the parent and the related child entities from the DB.
mby requirement is while getting the child i need to restrict the the no of childs for the parent.
e.g.
1.lets say parent is a invoice header while child is invoice line iem.
2. now the invoice line item has 3 version of each line item distiguished by a insert date
3. so in all foa a invoice with 4 line items there will be total of 12 lines that means each line has 3 version 1, 2 and 3.
4. i am interested in fetching only a specific line item based on the insert date.
for that i wrote following code
my parent mapping is as below
Code:
<hibernate-mapping schema="ODS" default-lazy="false">
<class name="com.toro.service.invoice.vo.Invoice" table="CP_INV_TRAN_HDR">
<id column="ODS_CP_INV_TRAN_SEQID" name="id"/>
<set name="lineItems" >
<key column="ODS_CP_INV_TRAN_SEQID" not-null="true"/>
<one-to-many class="com.toro.service.invoice.vo.InvoiceLineItem"/>
</set>
<many-to-one name="channelPartner" column="ODS_CP_SEQID" insert="false" update="false"/>
<many-to-one name="billToParty" column="BILL_TO_SEQID"/>
<many-to-one name="shipToParty" column="SHP_TO_SEQID"/>
<many-to-one name="soldToParty" column="SLD_TO_SEQID"/>
<property name="invoiceNumber" column="INV_NUM" />
<property name="invoiceDate" column="INV_DT" />
some more fields
and then queries
<sql-query name = "getInvoiceSeqIdsFromNumberDateCp">
<return-scalar column="ods_cp_inv_tran_seqid" type="long"/>
select unique ods_cp_inv_tran_seqid from ods.cp_inv_tran_hdr
where trunc(inv_dt) = :invoiceDate
and inv_num = :invoiceNumber
and ods_cp_seqid = (select ods_cp_seqid from ods.chnl_prtnr where cp_id = :channelPartner)
</sql-query>
<query name="InvoiceForRebateBySeqId">
from Invoice inv where inv.id in (:list)
and (select cust.customerType from ChannelPartnerShippingRelationship cust
where cust.channelPartnerId = inv.channelPartner.partnerId
and cust.soldToPartyId = inv.soldToParty.partnerPartyId
and cust.soldToPartyName = inv.soldToParty.name
and cust.shipToPartyId = inv.shipToParty.partnerPartyId
and cust.shipToPartyName = inv.shipToParty.name) <> 'Non-Revenue' order by id
</query>
my chaild looks as below
Code:
<hibernate-mapping schema="ODS" default-lazy="false">
<class name="com.toro.service.invoice.vo.InvoiceLineItem" table="CP_INV_TRAN_ITM">
<id column="ODS_CP_INV_ITM_SEQID" name="id"/>
<property name="version" column="INV_LN_ITM_VRSN_CONTR" />
<property name="lineNumber" column="INV_LN_NUM" />
<property name="materialDescription" column="SPLR_MATL_DESC" />
<property name="manufacturerCode" column="MNFCTR_CD_TXT" />
<property name="manufacturerName" column="MNFCTR_NM" />
<property name="dispositionCode" column="MATL_DISP_TYP_CD" />
<property name="materialId" column="ITM_ID" />
some more fields
and the code to fetch the parent and the childs in a go is as below
Code:
invoiceList.addAll(getHibernateTemplate()
.findByNamedQueryAndNamedParam(
"InvoiceForRebateBySeqId", "list", subList));
// "InvoiceForRebateBySeqId", new String[]{"list", "insertDate"}, new Object[]{subList, beginDate}));
now here the list is obtained by the query getInvoiceSeqIdsFromNumberDateCp and i use it as a param for the query InvoiceForRebateBySeqId
but here this quesry fetches all the line items for each ID(Invoice in the parent table)
to add a restrication on the childs i addeda where clause in the set definition in parent as
where="INSRT_DT := beginDate" (check the commented line in the code above)
but this throws an exception as the where clause in the set is not eligible for acepting the dynamic condition.
can sombody suggest me the best way to get the childs (line items) by applying restrictions using the above queries..
or else suggest me new query.
regards,
Suhs [/code]
Login
FAQ
Search