IFS Cloud – Duplicate rows when joining GEN_LED_VOUCHER_ROW_QRY with Customer Order and Invoice data

Title: IFS Cloud – Duplicate rows when joining GEN_LED_VOUCHER_ROW_QRY with Customer Order and Invoice data

Hi everyone,

I’m working on an SQL query in IFS Cloud that retrieves data from the Analysis Rows – GL (PK/KG) view, using GEN_LED_VOUCHER_ROW_QRY.

After adding this view to my existing query, some rows are duplicated. I suspect that I might be missing a view or a proper join condition that links customer order lines to the corresponding General Ledger analysis rows.

My query joins CUSTOMER_ORDER, CUSTOMER_ORDER_LINE, invoice-related views, customer ledger information, project data, and finally GEN_LED_VOUCHER_ROW_QRY.

My query:

select 

glvrq.voucher_no,
glvrq.row_no,
glvrq.internal_seq_number,
glvrq.trans_code,
glvrq.text,
glvrq.quantity,
glvrq.currency_amount,
glvrq.amount

from 
customer_order co 
join customer_order_line col 
on co.order_no=col.order_no

left join customer_order_line_cfv colcf 
on
col.order_no=colcf.order_no
and col.line_no=colcf.line_no
and col.rel_no=colcf.rel_no
and col.line_item_no=colcf.line_item_no


join IDENTITY_PAY_INFO_CU_QRY ipicq 
on co.customer_no_pay=ipicq.identity

join LEDGER_ITEM_CU_DET_QRY licdq 
on ipicq.identity=licdq.customer_id
and co.order_no=licdq.order_reference


left join INVOICE_PARTY_TYPE_GROUP iptg
on ipicq.group_id =iptg.group_id
and ipicq.party_type=iptg.party_type


 join project_base pb 
on pb.project_id = col.project_id


left join
(select lpo.project_id,

rtrim(xmlagg(xmlelement(e,bo.cf$_bu || ', ')
order by bo.cf$_bu).extract('//text()').getclobval(),', ') as bu,

rtrim(xmlagg(xmlelement(e,bo.cf$_ftms || ', ')
order by bo.cf$_ftms).extract('//text()').getclobval(),', ') as ftms

from l_project_opportunity lpo

left join business_opportunity_cfv bo 
on bo.opportunity_no = lpo.opportunity_no

group by lpo.project_id) opp
on opp.project_id = pb.project_id


join CUST_ORDER_INV_HEAD_UIV_ALL coihua 
on co.order_no=coihua.creators_reference
and licdq.Ledger_Item_Id=coihua.invoice_no
and licdq.Ledger_Item_Series_Id=coihua.series_id

join CUST_INV_INT_FINE_ALL_QRY ciifaq 
on coihua.invoice_no=ciifaq.invoice_no
and coihua.series_id=ciifaq.invoice_series


join  CUST_AGE_ANALYSIS_REP caar
on ciifaq.identity=caar.identity
and ciifaq.ledger_item_id=caar.invoice_no
and ciifaq.ledger_item_series_id=caar.series_id

JOIN GEN_LED_VOUCHER_ROW_QRY glvrq
    ON glvrq.reference_serie = coihua.series_id
   AND glvrq.reference_number = coihua.invoice_no
   AND glvrq.code_f = col.project_id
   AND glvrq.project_activity_id = col.activity_seq
   AND glvrq.account = '7322000'
   AND glvrq.voucher_no = 2606000010
  AND INSTR(
      ',' || REPLACE(colcf.cf$_ao_invoice_no, ' ', '') || ',',
      ',' || coihua.series_id || TO_CHAR(coihua.invoice_no) || ','
    ) > 0

The goal is to retrieve the relevant GL analysis rows for a specific account and voucher while keeping the relationship with the original customer order lines.

My questions are:

1. Is there a recommended view or standard IFS relationship that links CUSTOMER_ORDER_LINE to the corresponding rows in GEN_LED_VOUCHER_ROW_QRY?

2. Could another reference field or key be required in the join condition to avoid duplicate rows?

3. Is there a recommended approach to retrieving the GL analysis rows related to a customer order line and its invoice without multiplying the results?

I suspect that joining the GL analysis rows directly using the invoice reference, project ID, and project activity may not be sufficient to uniquely identify the corresponding rows.

Has anyone encountered a similar issue or found the correct relationship between customer order lines and the GL analysis rows in IFS?

Any suggestions or examples would be greatly appreciated.

Thanks in advance!