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.
