platformaerp.com

Powiązaniaa dokumentów magazynowych edokumenty porczyk

STATUS TODO

Problem:

Towary przychodzą bez faktur zakupu. Pozycje są przyjmowane dokumentem PZ z ceną ostatnią Faktura zakupu dociera po czasie z inną kwotą

Scenariusz1:

Faktura przychodiz po czasie

Scenariusz2:

Powiązanie faktur

Pozycja nr 19 była powiązana  z PZ/2328/102/2022 Przepiołem na dokument PZ/2840/102/2022. Z systemu platformacrm nie ma możliwości wykonania na tym etapie powiązania, Faktura zakupu musi być w szkicu. Powiązanie to przeniesienie pozycji z dokumentu PZ więc należało by usunąc pozycje i dodać a status zatwierdzony/fk to blokuje.

Rozwiązanie

czyli powiazanie od strony dokumentu magazynowego do faktury zakupu

Gdzie Wyświetlić

Wyświetlić wna wydruku Wyśiwetlić w exporcie do teta

Zapytania

Dodanie powiązań na pozycji dokuemntu korygującego

update dl_ko set
dl_ko.invoicelineid = dl.invoiceLineId
--select top 100
-- dl_ko.documentFullNR ,dl_ko.invoicelineid, dl.invoiceLineId ,
-- d.documentDate,d.documentFullNR,dl.documentOrdinalNumber lp
from
document.documentLine dl_ko
inner join document.documentline dl on dl_ko.initialSourceDocumentLineId = dl.documentlineid
inner join document.document d on d.documentID =dl.documentID
inner join document.documentType dt on d.documentType = dt.documentType
where
dt.baseDocumentType in ( 'PZ')
and d.documentDate > (getdate() -180)
and dl_ko.isCorrectiveLine = 1
and dl_ko.status >0
and dl_ko.invoiceLineId is null
and dl.invoiceLineId is not null

Lsita dokumentów powiązanych z pozycjami

select d.documentDate,d.documentFullNR,dl.documentOrdinalNumber lp
from
    document.document d
    inner join document.documentline dl on d.documentid = dl.documentID
    inner join document.documentType dt on d.documentType = dt.documentType
    left join document.invoiceline il on dl.invoicelineid = il.invoicelineid
    left join document.invoice i on il.invoiceid =i.invoiceid and i.status >-1 and il.status >-1
where
    dt.baseDocumentType in ( 'PZ','PR_PZ','PR_PZI')
    and d.documentDate > (getdate() -180)
    and dl.status >0
    and d.status >0
    --and dl.invoiceLineID is null
    and i.invoiceID ='ad086c75-1506-4da7-ad99-d1256491546e'
order by documentDate desc

Lista dokumentów powiązanych dla faktury

select  convert(varchar,d.documentdate, 23) documentdate ,d.documentFullNR,  sum(dl.sysitemvalue) sysitemvalue
from
    document.document d
    inner join document.documentline dl on d.documentid = dl.documentID
    inner join document.documentType dt  on d.documentType  = dt.documentType
    left join document.invoiceline il on dl.invoicelineid = il.invoicelineid
    left join document.invoice i on il.invoiceid  =i.invoiceid and i.status >-1  and il.status >-1
where
    dt.baseDocumentType  in ( 'PZ','PR_PZ','PR_PZI')
    and dl.status >0
    and d.status >0
    and i.invoiceID =@invoiceid
group by d.documentdate,d.documentFullNR
order by 1

Co powinnismy mieć

1. Z ostatnich 3 miesiecy , Dokument magazynowy , datę , powiązana pozycje zewnetrzna pogrupowana

Stary srs fktury bez pz

 <command name="FVbezPZ" label="FV bez PZ"><![CDATA[
      SELECT
        iv.addUsername,
        iv.invoiceFullNR purchaseFullNR,
        iv.invoiceFullNR2,
        iv.[status] ,
        case iv.status
                      WHEN 0 THEN 'SZKIC'
                      WHEN 1 THEN 'ZATW'
                      WHEN 2 THEN 'FK'
            else cast(iv.[status] as varchar(5))
        END invoicestatus_name,
        iv.invoiceissueDate,

        CASE
        when s.count_signature > 0 and s.count_signature = s.count_signed then 'TAK'
        when s.count_signature > 0 and s.count_rejected > 0 then 'ODMOWA'
        when s.count_signature > 0 and s.count_signature != s.count_signed then 'BRAK'
        else ''
        END acceptation,

        loc.locationcode warehousecode,
            sup.firmID,
    sup.firmnr + ' ' + sup.firmname firmname,
        iv.netValue,
        iv.vatValue,
        iv.grossValue,
        UPPER(iv.currency) currency,
        p.productname,
        p.productid,
        iv.invoiceid,
        ts.team_short
from [document].[invoice] iv
        INNER join [app].[user_teams] ut on iv.groupid = ut.team_id and ut.user_id = @userid
        INNER join [app].[teams] ts on ut.team_id = ts.team_id
        INNER JOIN ( SELECT il.invoiceID, count(il.documentLineID) countRelationWithPZ from document.invoiceLine il
          group by il.invoiceID
          HAVING count(il.documentLineID) = 0
        ) i on i.invoiceID = iv.invoiceID
        left join wms.[location] loc ON loc.locationID = iv.warehouseID
        LEFT JOIN firm.firm sup ON sup.firmID = iv.issuerID
        left join document.documentType typ on typ.documentType = iv.invoiceCategory
        left join product.product p on iv.productid = p.productid
 left join ( select
            signature_remote_uuid,
            count(signature_uuid) count_signature,
            sum(case when signature_sign_date is not null and signature_status >=30 then 1 else 0 end) count_signed,
            sum(case when signature_sign_date is not null and signature_status = 10 then 1 else 0 end) count_rejected
            from  document.[signature]
            where signature_remote_source = 'invoicePurchase' and signature_status > -1
            group by signature_remote_uuid
            ) s on s.signature_remote_uuid = iv.invoiceID
    WHERE iv.[status] > 0
       and typ.componentName = 'orderInvoice' --zakup
        AND (
            @datefrom IS NULL
            OR (
                iv.invoiceIssueDate >= @datefrom
                AND iv.invoiceIssueDate <= @dateto
            )
        )
        AND (
            @firmname IS NULL
            OR sup.firmname LIKE '%' + @firmname + '%'
        )
        AND (
            @firmid IS NULL
            OR sup.firmid LIKE '%' + @firmid + '%'
        )
        AND (
            @invoiceCategory IS NULL
            OR iv.invoiceCategory = @invoiceCategory
        )
        AND (
            @invoicestatus IS NULL
            OR iv.[status] = @invoicestatus
        )
        and (
            @invoiceFullNR is null
            or iv.invoiceFullNR = @invoiceFullNR
        )
        and (
            @invoiceFullNR2 is null
            or iv.invoiceFullNr2 = @invoiceFullNR2
        )
        and (
            @warehouseCode is null
            or loc.locationCode = @warehouseCode
        )
    order by
        iv.invoiceissueDate desc,iv.invoicenr DESC
    ]]>
    </command>