Fetch last active contract for a given item and customer account

Fetch last active contract for a given item and customer account

This query retrieves the contract related to a specific item for an account.

     SELECT  H.Id
       ,       H.Contract_Number
       ,       H.Contract_Number_Modifier
       ,       L.Start_date -- H.Start_Date
       ,       L.End_Date -- H.End_Date
       ,       DECODE (H.Sts_Code,'ACTIVE',L.Sts_Code,H.Sts_Code) Sts_Code
       ,       HT.Short_Description
       FROM    OKC_K_LINES_B L
       ,       OKC_K_ITEMS   I
       ,       OKC_K_HEADERS_B H
       ,       OKC_K_HEADERS_TL HT
       ,       HZ_CUST_SITE_USES_ALL BAUI
       ,       HZ_CUST_SITE_USES_ALL SAUI
       ,       hz_cust_acct_sites_all bsi
       ,       hz_cust_acct_sites_all ssi
       WHERE   1=1
       -- Site use of the contract line relate to cust account
       AND     L.Bill_To_Site_Use_id = BAUI.Site_Use_Id
       AND     L.Ship_To_Site_Use_Id = SAUI.Site_Use_Id
       AND     BAUI.Cust_Acct_SIte_Id = bsi.cust_acct_site_id
       AND     SAUI.Cust_Acct_Site_Id = ssi.cust_acct_site_id
       AND     bsi.cust_account_id = cp_bill_account_id
       AND     ssi.cust_account_id = cp_ship_account_id
       --
       AND     L.ID = I.Cle_Id
       AND     H.Id = HT.Id
       AND     HT.Language = USERENV ('LANG')
       AND     I.Object1_Id1 IN cp_inventory_item_id
       AND     I.Object1_Id2 = cp_organization_Id
       AND     L.Dnz_Chr_Id = h.id
       -- Fetch active contracts first, then the one with the latest end date/start date.
       -- If you find multiple lines with the same end date, take the lowest
       -- start date.
       ORDER BY DECODE (h.sts_code,'ACTIVE',0,1), NVL (L.End_Date,L.Start_Date) DESC, L.Start_Date,H.Contract_Number desc
       ;