Untitled
raw download clone
TEXT
views 22
,
size 5876 b
select specId, isExecuted, docId, series, expireDate, countryId, vat, producerId, prodDate, modifId, modifId, modifName, unitName, modifMNEI, countInSecPack, modifCommodityGroup, certificate, amountVat, amount, priceVat, price, quantity, mneiQuantity, statusId, docStatusId, contractId, docDate, contractSpecId, contractSpecPrice, 
                coalesce((select t.code from fin_contract c inner join fin_contract_type t on t.id = c.type_id
                where c.id=contractId) = 'SINGLE_DRUG_SUPPLY', false)
            , margin, priceWithMargin, amountWithMargin, 
                'Поставлено: ' || trim(to_char(deliveredQuantity, 'FM9999990.999999'), '.') || ' ' || unitName
            , 
                
                    case when contrSpecQuantity - deliveredQuantity < 0
                    then 'Остаток: 0' || ' ' || unitName
                    else
                     'Остаток: ' || trim(to_char(contrSpecQuantity - deliveredQuantity, 'FM9999990.999999'), '.') || ' ' || unitName
                    end
                
            , 
                
                    case when contrSpecQuantity - deliveredQuantity < 0
                    then 0
                    else
                       contrSpecQuantity - deliveredQuantity
                    end
                
            , 
                contrSpecQuantity - deliveredQuantity < 0
            , (select result from inventory.get_unpackaging_multiplier(typePackagingCode, 0, modifId))
                from (select
                          s.id                                                      specId,
                          (s.status_id = 2)                                         isExecuted,
                          s.doc_id                                                  docId,
                          s.series                                                  series,
                          s.expire_dt                                               expireDate,
                          s.country_id                                              countryId,
                          s.vat                                                     vat,
                          s.producer_id                                             producerId,
                          s.production_date                                         prodDate,
                          s.hold_modif_id                                           modifId,
                          m.name                                                    modifName,
                          mes.mnemocode                                             modifMNEI,
                          coalesce(m.mnei_count_in_sec_pk, 1)                       countInSecPack,
                          s.certificate                                             certificate,
                          s.amount_vat                                              amountVat,
                          s.amount_vat / (1 + coalesce(s.vat, 0) * 0.01)            amount,
                          s.price_vat                                               priceVat,
                          s.price_vat / (1 + coalesce(s.vat, 0) * 0.01)             price,
                          s.quantity                                                quantity,
                          s.mnei_quantity                                           mneiQuantity,
                          s.status_id                                               statusId,
                          i.doc_status_id                                           docStatusId,
                          ai.base_doc_id                                            contractId,
                          i.int_doc_dt                                              docDate,
                          s.contract_spec_id                                        contractSpecId,
                          round(fs.price, 2)                                        contractSpecPrice,
                          s.margin                                                  margin,
                          s.price_with_margin                                       priceWithMargin,
                          s.amount_with_margin                                      amountWithMargin,
              			  h.commodity_group_id 										modifCommodityGroup,
                          (select mnemocode
                           from cmn_measure
                           where id = inventory.get_price_unit_id(m.id))            unitName,
                          (select coalesce(sum(arr.quantity), 0)
                           from inventory.arrival_spec arr
                           where arr.status_id = 2 and arr.contract_spec_id = s.id) deliveredQuantity,
                          fs.quantity                                               contrSpecQuantity,
                          tp.code                                                   typePackagingCode
                      from ААААinventory.arrival_spec s
                          join inventory.hold_modif m on m.id = s.hold_modif_id
                          join inventory.document i on i.id = s.doc_id
              			  join inventory.holding h on h.id=m.holding_id
                          join inventory.arrival_invoice ai on ai.id = i.id
                          join inventory.type_packaging_to_price_hold_modif tphm on m.price_unit_flag = tphm.price_id
                          join inventory.type_packaging tp on tphm.type_packaging_code = tp.code
                          left join inventory.fin_contract_spec fs on fs.id = s.contract_spec_id
                          left join cmn_measure mes on mes.id = m.mnei_id) spec
                where specId=:id
                order by 1
             limit 1
close fullscreen
Login or Register to edit or fork this paste. It's free.