quarta-feira, 6 de agosto de 2025

AMDP - Uso do RANK para filtrar apenas valor único de seleção

  METHOD executar BY DATABASE FUNCTION FOR HDB LANGUAGE SQLSCRIPT OPTIONS
  READ-ONLY USING I_CreditDecisionDocAttrib
                  c_outbdelivprocfloW
                  i_salesdocument
                  i_salesdocumentitem
                  i_salesdocitempricingelement.

    it_creddec = select client,
                        caseuuid as Id,
                        CreditDecisionObjectType as DecType,
                        CreditDecisionSalesDocument as DecDoc
                   from I_CreditDecisionDocAttrib as _CredDec
                  where client = session_context('CLIENT');
    it_condPag = SELECT _CredDec.client as Client,
                      _CredDec.Id as Id,
                      _SalesOrder.salesdocument as SalesDocument,
                      _SalesOrder.CustomerPaymentTerms as CondPag,
                      rank ( ) over ( partition by client, _CredDec.Id order by _CredDec.Id desc ) as rank
                 from :it_creddec as _CredDec
                     inner join i_salesdocument as _SalesOrder
                     on _SalesOrder.salesdocument = _CredDec.DecDoc
                where _CredDec.DecType = 'VBAK'
                 UNION ALL
            SELECT _CredDec.client as Client,
                   _CredDec.Id as Id,
                   _SalesOrder.salesdocument as SalesDocument,
                   _SalesOrder.CustomerPaymentTerms as CondPag,
                   rank ( ) over ( partition by client, _CredDec.Id order by _CredDec.Id desc ) as rank
                 from :it_creddec as _CredDec
                     inner join c_outbdelivprocflow as _Outb
                     on _Outb.outbounddelivery = _CredDec.DecDoc
                     inner join i_salesdocument as _SalesOrder
                     on _SalesOrder.salesdocument = _Outb.precedingdocument
                     and precedingdocumentcategory = 'C'
                where _CredDec.DecType = 'LIKP'
                  and EXISTS ( SELECT * FROM i_salesdocumentitem
                                       WHERE salesdocument = _Outb.PrecedingDocument
                                         and sddocumentrejectionstatus <> 'C' );


    RETURN select Client,
                  Id,
                  CondPag,
                  _CondPag.SalesDocument,
                  sum( _Pricing.conditionamount ) as ValorTotal
            from :it_condPag as _CondPag
                 left outer join i_salesdocitempricingelement as _Pricing
                   on _Pricing.salesdocument = _CondPag.salesdocument
                  and _Pricing.conditiontype = 'ZTOT'
           where rank = 1
             GROUP BY _CondPag.Client, _CondPag.Id, _CondPag.CondPag, _CondPag.SalesDocument;
endmethod.