Помогите с запросом получения просроченной задолженности : Супермаг Плюс (Супермаг 2000)

Starter · █ 01.11.2016 22:27

Помогите с запросом - как из СМ+ вытащить данные по просроченной задолженности на определенную дату.
Как вытащить саму задолженность - понятно.

SQL код:

 select (sum(d.ACCEPTINSUM)-sum(d.ACCEPTOUTSUM)) from SVFinObligationAll d,SVCompanies SUPPL 
where d.BEGINDATE<to_date('31.10.2016','DD.MM.YYYY') and d.docstate=3
and d.FinAgent=SUPPL.ID and UPPER(SUPPL.INN) LIKE UPPER('%5032272863%') ESCAPE '\';

И как определить просроченную задолженность - непонятно.
Вроде как в табличке SVFINOBLIGATIONWI есть поле CALCENDDATE (дата погашения). А вот как связать эти две таблицы - знаний не хватает.

denxz · █ 02.11.2016 13:07

ну как бы тут непонятно как раз то что уже написано - ты из всех входящих вычитаешь все исходящие (причем там есть нулы)
тут еще как бы надо учесть то что уже погашено, достоверность фин.обязательств
а просроченное это все у чего дата погашения будет больше текущей
а SVCompanies это что должно быть? у меня просто она совсем не используется

Starter · █ 02.11.2016 13:13

То, что нулы не учтены - согласен, более корректно будет:

SQL код:

 select (sum(nvl(d.ACCEPTINSUM,0))-sum(nvl(d.ACCEPTOUTSUM,0))) from SVFinObligationAll d,SVCompanies SUPPL 
where d.BEGINDATE<to_date('31.10.2016','DD.MM.YYYY') and d.docstate=3
and d.FinAgent=SUPPL.ID and UPPER(SUPPL.INN) LIKE UPPER('%5032272863%') ESCAPE '\';

SVCompanies - чтобы вытаскивать данные по ИНН. Непринципиально, можно и по коду, важно узнать - как.

Да, просроченное - то, у чего дата погашения будет больше, только не текущей а указанной в выборке. и тут и есть проблема.

denxz · █ 02.11.2016 14:31

SQL код:

 select suppl.id, (sum(nvl(d.ACCEPTINSUM-d.linksum,0))-sum(nvl(d.ACCEPTOUTSUM-d.linksum,0))) from supermag.SVFinObligationAll d 
join SUPERMAG.SVCompanies SUPPL  on d.FinAgent=SUPPL.ID
join SUPERMAG.SMFINOBLIGATION fin on FIN.DOCID=D.DOCID and FIN.DOCTYPE=d.doctype
where D.ISADMITTED=1 and FIN.CALCENDDATE<sysdate
/* and UPPER(SUPPL.INN) LIKE UPPER('%5032272863%') ESCAPE '\'*/
group by  suppl.id

попробуй у меня SVCompanies пустой просто

Starter · █ 02.11.2016 15:49

Попробовал - работает, к сожалению, в супермаге так и не нашел отчета по просроченной задолженности (на определенную дату), где смог легко проверить - там сходится. Спасибо!

Starter · █ 02.11.2016 18:34

Потестировал на более сложных случаях - не работает.

Starter · █ 02.11.2016 18:44

И да - нужно получить просроченную задолженность не на текущую дату (это сделать просто), а на любую.

Starter · █ 02.11.2016 23:23

В общем, как то так:

SQL код:

 select (sum(nvl(d.ACCEPTINSUM,0))-sum(nvl(d.ACCEPTOUTSUM,0))) from SVFinObligationAll d
join SUPERMAG.SVCompanies SUPPL  on d.FinAgent=SUPPL.ID 
left join SUPERMAG.SMFINOBLIGATION fin on FIN.DOCID=D.DOCID and FIN.DOCTYPE=d.doctype
where  d.docstate=3
and d.FinAgent=SUPPL.ID and trim(UPPER(SUPPL.INN))=trim(UPPER('5032272863'))
and d.BEGINDATE<=to_date('20.10.2016','DD.MM.YYYY')
and ((fin.calcenddate is null) or (fin.calcenddate<to_date('20.10.2016','DD.MM.YYYY')));

Starter · █ 04.11.2016 00:28

Хотя, на самом деле, таки не так :(
Тут вычисляется ПКЗ (просроченная кредиторская задолженность) как КЗ (кредиторская задолженность) на определенную дату и вычитаются те финобязательства, срок погашения которых больше указанной даты. Точнее, берутся все платежи до определенной даты и берутся все фин. обязательства, дата погашения которых меньше этой даты).

Однако фин. обязательство может быть погашено заранее. Получается, платеж мы по нему учитываем, а само финобязательство выкидываем. В итоге получаем отрицательную ПКЗ. С одной стороны, если сумма в минус - мы молодцы, платим заранее и это видно, с другой стороны, запрос недостоверный.

Получается, нужно как то вычислять было ли ФО погашено на определенную дату, точнее, насколько было погашено.

Starter · █ 08.11.2016 11:22

Еще вариант (более корректный). Берем полный долг на дату, но для обязательств, у которых срок погашения больше или равен определенной дате берется не сумма обязательства а сумма, оплаченная на эту дату.
т.е. предположим, есть обязательство на 100 т.р. от 01.11.16, срок погашения у него 20.11.16, однако к 07.11.16 мы уже 20 т.р. проплатили.

Получается: общий долг на 07.11 = 100 000 (сумма об-ва) - 20 000 (сумма платежа) = 80 000.
просроченный долг: 20 000 (сумма уже проплаченного на 07.11 по обязательству) - 20 000 (сумма платежа) = 0.
То есть, вычисляется более корректно.

В предыдущем варианте это фин. обязательство вообще не учитывалось (выкидывалось при отборе) и получалось, что сумма просроченных = - 20000 (платежи отбираются все на определенную дату)

SQL код:

 select (sum(nvl(case when fin.calcenddate>=to_date('07.11.2016','DD.MM.YYYY') then supermag.get_doc_debt_on_date(to_date ('07.11.2016','DD.MM.YYYY'),d.doctype,d.docid) else d.acceptinsum end,0))-sum(nvl(d.ACCEPTOUTSUM,0))) from SVFinObligationAll d
join SUPERMAG.SVCompanies SUPPL  on d.FinAgent=SUPPL.ID 
left join SUPERMAG.SMFINOBLIGATION fin on FIN.DOCID=D.DOCID and FIN.DOCTYPE=d.doctype
where  d.docstate=3
and d.FinAgent=SUPPL.ID and trim(UPPER(SUPPL.INN))=trim(UPPER('5029154179'))
and d.BEGINDATE<=to_date('07.11.2016','DD.MM.YYYY');

Однако проблема в том, что с учетом скудных познаний в SQL пришлось городить функцию.

SQL код:

 --получение погашенного долга (сколько оплачено) по конкретному документу на дату (дата включается)
--дата (дата включается), тип документа, номер документа
create or replace function get_doc_debt_on_date(ADate in date, ADocType in supermag.smdocuments.doctype%type,ADocID in supermag.smdocuments.id%type)
    return number
    is
      saldo NUMBER;
    begin
       select SUM(nvl(FL.LINKSUM,0)) into saldo from supermag.SVFINLINKALL FL where FL.DOCTYPE=ADocType and FL.ID=ADocID and FL.ENDDATE<=ADate;
    return saldo;
    end get_doc_debt_on_date;
/
commit;

Вопрос к знатокам - можно ли все вышеописанное таки одним запросом реализовать ?