B - Production Support » KFH Support

-------- Original Message --------
Subject: Re: Rentas file Generation
Date: Fri, 7 Sep 2012 10:25:04 +0800
From: Vincent Devethas <vincent.devethas@penril.net>
To: Lee Yong Tan <leeyong.tan@penril.net>
CC: kfh.support <kfh.support@penril.net>

Hi Lee Yong,

Sorry, I missed out the (<) symbol in the query.

SELECT RENTAS_DETAIL.*,
DET.BENE_EMAIL,
DET.BENE_BANK_NAME,
DET.PAYMENT_AMOUNT,
DET.PAYMENT_REF_NO,
DET.PAYMENT_DETAIL,
DET.BATCH_ID
FROM BIB_BULK_PAYMENT BULK
FULL JOIN BIB_BULK_PAYMENT_DETAIL DET ON BULK.BATCH_ID = DET.BATCH_ID
FULL JOIN RENTAS_DETAIL RTS DET.TRNX_ID = RTS.OID
WHERE RTS.STATUS = ? AND
RTS.VALUE_DATE <= TO_CHAR(SYSDATE, 'YYYYMMDD');

I've change the query to full join. Doesn't matter records available in all three table, its will still pull the records filter by status and value_date.

On Fri, Sep 7, 2012 at 8:16 AM, Lee Yong Tan <leeyong.tan@penril.net> wrote:

Hi Vicent,
    Great finding. Do you know why the < is not working as expected?
    On Sep 6, 2012 10:12 PM, "Vincent Devethas" &lt;vincent.devethas@penril.net&gt; wrote:

Lee Yong,

The issue happens when a single Rentas transaction created (via Interbank Transfer - selected payment type as RENTAS) in BIB. Here is the scenario, When a Rentas transaction uploaded via Bulkpayment, it stored into 3 tables:

• BIB_BULK_PAYMENT
• BIB_BULK_PAYMENT_DETAIL
• RENTAS_DETAIL

But when a single Rentas transaction created it only stored into RENTAS_DETAIL table.

Rentas file generation program will combine search from the above mentioned 3 tables filter by STATUS (T - Transferred) and VALUE_DATE (Must be lesser or equal to System Date). So, program unable to retrieve and data from DB since it doesn't meet the criteria. Due to no records are available, there was no file generated.

Noticed that the SQL Query inside Rentas.java file was last modified back in 03/05/2011. Since then the same query has been tested and moved to production. But now it become an issue.

I've simulated in Development and Staging. As expected the same problem happens. So, I've amended the query and gonna test in development tomorrow.

For you reference I've included the SQL Query:
================================================================================
        Before Change
================================================================================
        SELECT RENTAS_DETAIL.*, 
        BIB_BULK_PAYMENT_DETAIL.BENE_EMAIL, 
        BIB_BULK_PAYMENT_DETAIL.BENE_BANK_NAME, 
        BIB_BULK_PAYMENT_DETAIL.PAYMENT_AMOUNT,
        BIB_BULK_PAYMENT_DETAIL.PAYMENT_REF_NO, 
        BIB_BULK_PAYMENT_DETAIL.PAYMENT_DETAIL, 
        BIB_BULK_PAYMENT_DETAIL.BATCH_ID
        FROM 
        BIB_BULK_PAYMENT, 
        RENTAS_DETAIL JOIN BIB_BULK_PAYMENT_DETAIL ON BIB_BULK_PAYMENT_DETAIL.TRNX_ID=RENTAS_DETAIL.OID
        WHERE BIB_BULK_PAYMENT.BATCH_ID = BIB_BULK_PAYMENT_DETAIL.BATCH_ID 
        AND RENTAS_DETAIL.STATUS = ?
        AND RENTAS_DETAIL.VALUE_DATE <= TO_CHAR(SYSDATE, 'YYYYMMDD') 
        ORDER BY RENTAS_DETAIL.OID;
================================================================================

================================================================================
        After Change
================================================================================
        SELECT RENTAS_DETAIL.*, 
        DET.BENE_EMAIL, 
        DET.BENE_BANK_NAME, 
        DET.PAYMENT_AMOUNT,
        DET.PAYMENT_REF_NO, 
        DET.PAYMENT_DETAIL, 
        DET.BATCH_ID 
        FROM BIB_BULK_PAYMENT BULK
        FULL JOIN BIB_BULK_PAYMENT_DETAIL DET ON BULK.BATCH_ID = DET.BATCH_ID
        FULL JOIN RENTAS_DETAIL RTS DET.TRNX_ID = RTS.OID
        WHERE RTS.STATUS = ? AND
        RTS.VALUE_DATE = TO_CHAR(SYSDATE, 'YYYYMMDD');
================================================================================

Thanks,