Lock Management
---------------
 
  Lock Summary                    per sec      per xact       count  % of total
  -------------------------  ------------  ------------  ----------  ---------- 
  Total Lock Requests             22966.4         568.1    31578734       n/a   
  Avg Lock Contention                 0.0           0.0           5       0.0 %
  Deadlock Percentage                 0.0           0.0           2       0.0 %
 
  Lock Detail                     per sec      per xact       count  % of total
  -------------------------  ------------  ------------  ----------  ---------- 
 
  Table Lock Hashtable
    Lookups                          52.9           1.3       72799       n/a   
    Avg Chain Length                  n/a           n/a     0.05930       n/a   
    Spinlock Contention               n/a           n/a         n/a       0.0 %
 
  Exclusive Table
    Granted                           3.3           0.1        4560     100.0 %
    Waited                            0.0           0.0           2       0.0 %
  -------------------------  ------------  ------------  ----------  ---------- 
  Total EX-Table Requests             3.3           0.1        4562       0.0 %
 
  Shared Table
    Granted                           0.2           0.0         268     100.0 %
    Waited                            0.0           0.0           0       0.0 %
  -------------------------  ------------  ------------  ----------  ---------- 
  Total SH-Table Requests             0.2           0.0         268       0.0 %
 
  Exclusive Intent
    Granted                          20.4           0.5       28068     100.0 %
    Waited                            0.0           0.0           0       0.0 %
  -------------------------  ------------  ------------  ----------  ---------- 
  Total EX-Intent Requests           20.4           0.5       28068       0.1 %
 
  Shared Intent
    Granted                          28.6           0.7       39380     100.0 %
    Waited                            0.0           0.0           1       0.0 %
  -------------------------  ------------  ------------  ----------  ---------- 
  Total SH-Intent Requests           28.6           0.7       39381       0.1 %
 
  Page & Row Lock HashTable
    Lookups                       16224.6         401.3    22308892       n/a   
    Avg Chain Length                  n/a           n/a     0.58730       n/a   
    Spinlock Contention               n/a           n/a         n/a       0.0 %
 
  Exclusive Page
    Granted                         115.9           2.9      159420     100.0 %
    Waited                            0.0           0.0           0       0.0 %
  -------------------------  ------------  ------------  ----------  ---------- 
  Total EX-Page Requests            115.9           2.9      159420       0.5 %
 
  Update Page
    Granted                           5.1           0.1        7033     100.0 %
    Waited                            0.0           0.0           0       0.0 %
  -------------------------  ------------  ------------  ----------  ---------- 
  Total UP-Page Requests              5.1           0.1        7033       0.0 %
 
  Shared Page
    Granted                       15924.0         393.9    21895495     100.0 %
    Waited                            0.0           0.0           1       0.0 %
  -------------------------  ------------  ------------  ----------  ---------- 
  Total SH-Page Requests          15924.0         393.9    21895496      69.3 %
 
 
  Exclusive Row
    Granted                           0.3           0.0         371     100.0 %
    Waited                            0.0           0.0           0       0.0 %
  -------------------------  ------------  ------------  ----------  ---------- 
  Total EX-Row Requests               0.3           0.0         371       0.0 %
 
  Update Row
    Granted                           0.1           0.0         148     100.0 %
    Waited                            0.0           0.0           0       0.0 %
  -------------------------  ------------  ------------  ----------  ---------- 
  Total UP-Row Requests               0.1           0.0         148       0.0 %
 
  Shared Row
    Granted                          25.0           0.6       34366     100.0 %
    Waited                            0.0           0.0           1       0.0 %
  -------------------------  ------------  ------------  ----------  ---------- 
  Total SH-Row Requests              25.0           0.6       34367       0.1 %
 
 
  Next-Key
    Total Next-Key Requests           0.0           0.0           0       n/a   
 
  Address Lock Hashtable
    Lookups                        6843.3         169.3     9409518       n/a   
    Avg Chain Length                  n/a           n/a     0.00007       n/a   
    Spinlock Contention               n/a           n/a         n/a       0.0 %
 
  Exclusive Address
    Granted                         759.5          18.8     1044263     100.0 %
    Waited                            0.0           0.0           0       0.0 %
  -------------------------  ------------  ------------  ----------  ---------- 
  Total EX-Address Requests         759.5          18.8     1044263       3.3 %
 
  Shared Address
    Granted                        6083.9         150.5     8365357     100.0 %
    Waited                            0.0           0.0           0       0.0 %
  -------------------------  ------------  ------------  ----------  ---------- 
  Total SH-Address Requests        6083.9         150.5     8365357      26.5 %
 
 
  Last Page Locks on Heaps
    Granted                        5686.4         140.7     7818845     100.0 %
    Waited                            0.0           0.0           0       0.0 %
  -------------------------  ------------  ------------  ----------  ---------- 
  Total Last Pg Locks              5686.4         140.7     7818845     100.0 %
 
 
  Deadlocks by Lock Type          per sec      per xact       count  % of total
  -------------------------  ------------  ------------  ----------  ---------- 
    Exclusive Table                   0.0           0.0           1      50.0 %
    Shared Table                      0.0           0.0           0       0.0 %
    Exclusive Intent                  0.0           0.0           1      50.0 %
    Shared Intent                     0.0           0.0           0       0.0 %
    Exclusive Page                    0.0           0.0           0       0.0 %
    Update Page                       0.0           0.0           0       0.0 %
    Shared Page                       0.0           0.0           0       0.0 %
    Exclusive Row                     0.0           0.0           0       0.0 %
    Update Row                        0.0           0.0           0       0.0 %
    Shared Row                        0.0           0.0           0       0.0 %
    Shared Next-Key                   0.0           0.0           0       0.0 %
    Exclusive Address                 0.0           0.0           0       0.0 %
    Shared Address                    0.0           0.0           0       0.0 %
    Others                            0.0           0.0           0       0.0 %
  -------------------------  ------------  ------------  ----------
  Total Deadlocks                     0.0           0.0           2             
 
 
  Deadlock Detection
    Deadlock Searches                 0.0           0.0           7       n/a   
    Searches Skipped                  0.0           0.0           0       0.0 %
    Avg Deadlocks per Search          n/a           n/a     0.28571       n/a   
 
 
  Lock Promotions
    Total Lock Promotions             0.0           0.0           0       n/a   
 
 
  Lock Timeouts by Lock Type      per sec      per xact       count  % of total
  -------------------------  ------------  ------------  ----------  ---------- 
  Total Timeouts                      0.0           0.0           0       n/a   
 
  Tuning Recommendations for Lock Management                                    
  ------------------------------------------

sp_sysmon begin_sample
report_cargo_moves_list.... 
sp_sysmon end_sample

create procedure dbo.recive_act_item_add
	@recive_act_code		numeric( 12 ),
	@cargo_code			    numeric( 12 ),
    @agreement_code         numeric( 12 ),
    @agreement_cargo_code   numeric( 12 ),
	@cargo_type			    char( 4 ),
	@part_number		    varchar( 20 ),
	@lot_number			    varchar( 20 ),
	@cargo_mark			    varchar( 60 ),
	@order_number		    varchar( 60 ), 
	@color_mark			    varchar( 60 ),
	@cargo_size			    varchar( 60 ),
	@place_number		    varchar( 20 ),
	@locking_arm_number	    varchar( 20 ),
	@packet_code		    numeric( 12 ),
	@packet_count		    int,
	@package_code		    numeric( 12 ),
	@package_count		    int,
	@cargo_place_count	    int,
	@weight_gross		    float,
	@weight_nett		    float,
	@weight_specification	float,
	@container_number		varchar( 20 ),
	@returnable_equipment	varchar( 20 ),
	@equipment_weight		float,
	@percent_refuse		    float,
	@weight_refuse		    float,
	@pure_weight_nett		float,
	@cargo_defect_descrip	varchar( 255 ),
	@packet_defect_descrip	varchar( 255 ),
	@revision_descrip		varchar( 255 ),
    @car_invoice_item_code  numeric( 12 , 0 ) = null,
    @contract               varchar( 60 ) = null,
    @refuse_name            varchar( 50 ) = null,
    @commentary             varchar( 255 ) = null,
    @supply_act_item_code   numeric( 12 ) = null,
    @weight_specification_nett      float = null,
    @place_count_specification      int   = null,
    @place_number_specification     varchar( 20 ) = null,
    @goods_code                     varchar( 20 ) = null,
    @stack_number                   varchar( 20 ) = null,
    @container_type                 char( 2 ) = null,
    @weight_tare                    float = null
/*
//	процедура добавляет позицию приемного акта
*/

as

	begin

        declare 
            @expenses_cargo_place_count     int,
            @expenses_packet_count          int,
            @expenses_package_count         int,
            @expenses_weight_gross          float,
            @expenses_weight_nett           float,
            @loc_agreement_code             numeric( 12 )

        -- проверка кода договора в позиции и шапке добавлено 04.05.2004
        if @agreement_code !=  0  begin
           select @loc_agreement_code = agreement_code from reciving_acts where recive_act_id = @recive_act_code and agreement_code !=  0 
           if (@loc_agreement_code is not NULL) and (@loc_agreement_code != @agreement_code) begin
              raiserror  77777  'Ошибка, код договора в шапке не совпадает с кодом договора в позиции.'
              select recive_act_item_id = - 1  
              return
           end
        end
        
        select @expenses_cargo_place_count =  0 
        select @expenses_packet_count =  0 
        select @expenses_package_count =  0 
        select @expenses_weight_gross =  0 . 000 
        select @expenses_weight_nett =  0 . 000 

        if @car_invoice_item_code is not null begin
          select @cargo_code = (select cargo_code from dbo.car_invoice_items where car_invoice_item_id = @car_invoice_item_code)
          select @packet_code = (select packet_code from dbo.car_invoice_items where car_invoice_item_id = @car_invoice_item_code)
          select @cargo_place_count = (select place_count from dbo.car_invoice_items where car_invoice_item_id = @car_invoice_item_code)
          select @weight_gross = (select weight_gross from dbo.car_invoice_items where car_invoice_item_id = @car_invoice_item_code)
          select @weight_nett = (select weight_nett from dbo.car_invoice_items where car_invoice_item_id = @car_invoice_item_code)
          select @cargo_mark = (select cargo_mark from dbo.car_invoice_items where car_invoice_item_id = @car_invoice_item_code)
          select @cargo_size = (select cargo_size from dbo.car_invoice_items where car_invoice_item_id = @car_invoice_item_code)
        end
        if @cargo_place_count is null
           select @cargo_place_count =  0 
        if @packet_count is null
           select @packet_count =  0 
        if @package_count is null
           select @package_count =  0 
        if @weight_gross is null
           select @weight_gross =  0 . 000 
        if @weight_nett is null
           select @weight_nett =  0 . 000 
        if @weight_specification is null
           select @weight_specification=  0 . 000 
        if @weight_specification_nett is null
           select @weight_specification_nett =  0 . 000 
        if @place_count_specification is null
           select @place_count_specification =  0 
        if @weight_tare is null
           select @weight_tare =  0 . 00  

        -- если тип груза навал cargo_type='BULK' выставляем сразу расходные КВХ
        if @cargo_type = 'BULK' begin
           -- select @expenses_cargo_place_count = @cargo_place_count
           -- select @expenses_packet_count = @packet_count
           -- select @expenses_package_count = @package_count
           select @expenses_weight_gross = @weight_gross
           -- select @expenses_weight_nett = 0.000
        end

		insert into dbo.reciving_act_items

		( recive_act_code,
		  cargo_code,
          agreement_code,
          agreement_cargo_code,
		  cargo_type,
		  part_number,
		  lot_number,
		  cargo_mark,
		  order_number,
		  color_mark,
		  cargo_size,
		  place_number,
		  locking_arm_number,
		  packet_code,
		  packet_count,
		  package_code,
		  package_count,
		  cargo_place_count,
		  weight_gross,
		  weight_nett,
		  weight_specification,
		  container_number,
		  returnable_equipment,
		  equipment_weight,
		  percent_refuse,
		  weight_refuse,
		  pure_weight_nett,
		  cargo_defect_descrip,
		  packet_defect_descrip,
		  revision_descrip,
          car_invoice_item_code,
          contract,
          refuse_name,
          commentary,
          supply_act_item_code,
          weight_specification_nett,
          place_count_specification,
          place_number_specification,
          goods_code,
          stack_number,
          container_type,
          weight_tare,
          expenses_cargo_place_count,
          expenses_packet_count,
          expenses_package_count,
          expenses_weight_gross,
          expenses_weight_nett
        ) values
        ( @recive_act_code,
		  @cargo_code,
          @agreement_code,
          @agreement_cargo_code,
		  @cargo_type,
		  @part_number,
		  @lot_number,
		  @cargo_mark,
		  @order_number,
		  @color_mark,
		  @cargo_size,
		  @place_number,
		  @locking_arm_number,
		  @packet_code,
		  @packet_count,
		  @package_code,
		  @package_count,
		  @cargo_place_count,
		  Round(@weight_gross, 3 ),
		  Round(@weight_nett, 3 ),
		  Round(@weight_specification, 3 ),
		  @container_number,
		  @returnable_equipment,
		  Round(@equipment_weight, 3 ),
		  Round(@percent_refuse, 3 ),
		  Round(@weight_refuse, 3 ),
		  Round(@pure_weight_nett, 3 ),
		  @cargo_defect_descrip,
		  @packet_defect_descrip,
		  @revision_descrip,
          @car_invoice_item_code,
          @contract,
          @refuse_name,
          @commentary,
          null, -- @supply_act_item_code всегда равен null p.s.исправить потом
          Round(@weight_specification_nett, 3 ),
          @place_count_specification,
          @place_number_specification,
          @goods_code,
          @stack_number,
          @container_type,
          Round(@weight_tare, 3 ),
          @expenses_cargo_place_count,
          @expenses_packet_count,
          @expenses_package_count,
          Round(@expenses_weight_gross, 3 ),
          Round(@expenses_weight_nett, 3 )
		)
		select recive_act_item_id = @@identity

	end

create procedure dbo.report_cargo_moves_list
@start_date           	datetime,
@end_date           	datetime,

@object_type            integer =  0 ,    -- valuse: 0 - порт, 1 - район,  2 - склад
@object_code            char( 12 ) = null,
@only_cargo_group       integer =  0 ,  	-- valuse: 1 - группа груза, else - груз

-- далее используем 
-- null- не учитываем	0- любой		[value]	- равен value
@allow_agreement        numeric( 12 , 0 )	=  0 ,  	-- учитывать договор  
@allow_agreement_cargo  numeric( 12 , 0 )	=  0 ,  	--   -//-    груз по договору
@allow_shipper          numeric( 12 , 0 )	=  0 ,  	--   -//-    отправитель
@allow_consignee        numeric( 12 , 0 )	=  0 ,  	--   -//-    получатель
@allow_forward          numeric( 12 , 0 )	=  0 ,  	--   -//-    экспедитор

-- далее просто флаги
@reciving_act		integer =  0 ,	-- приход по приемным актам               index 1
@supply_acts_rec	integer =  0 ,	--    -//-   сдаточным акта               index 2 
@supply_acts_trans	integer =  0 ,	-- расход по сдаточным акта               index 4
@discharge_act		integer =  0 ,	--    -//-   актам списания               index 5  	
@expenses_order		integer =  0 ,	--    -//-   расходным ордерам            index 6
@cargo_loading_list	integer	=  0 	    --    -//-   список погруженных грузов    index 3

as 

set forceplan on

create table #cargo_moves_list (
    doc_type		        int             null,	
    cargo_code              numeric( 12 ,  0 )  null,
    agreement_code          numeric( 12 ,  0 )  null,
    agreement_cargo_code    numeric( 12 ,  0 )  null,
    shipper_code            int             null,
    consignee_code          int             null,
    forward_code            numeric( 12 ,  0 )  null,
    packet_code             numeric( 12 ,  0 )  null,
    package_code            numeric( 12 ,  0 )  null,
    place_count             int             null,
    packet_count            int             null,
    package_count           int             null,
    weight_gross            float           null,
    weight_nett             float           null
)

declare @storage_code numeric( 12 )

if @object_type >  3   or  @object_type <  1  begin raiserror  99999  'Недопустимые параметры' return end

if @object_type =  3  select @storage_code = convert(numeric( 12 ), @object_code)

-- ---------------------------------------------------------------------------------------
--                          приходные квх по ПРИЕМНЫМ АКТАМ
-- ---------------------------------------------------------------------------------------
if @reciving_act =  1  
insert into #cargo_moves_list (doc_type, cargo_code, agreement_code, agreement_cargo_code, shipper_code, consignee_code, forward_code,
  packet_code, package_code, place_count, packet_count, package_count, weight_gross, weight_nett)
select 
   1 ,
  (case @only_cargo_group when  1  then cargo.cargo_group_code else ra_items.cargo_code end),
  (case when @allow_agreement is not null then ra_head.agreement_code else null end),
  (case when @allow_agreement_cargo is not null then ra_items.agreement_cargo_code else null end),
  (case when @allow_shipper is not null then ra_head.shipper_code else null end),
  (case when @allow_consignee is not null then ra_head.consignee_code else null end),
  (case when @allow_forward is not null then ra_head.forward_code else null end),

  ra_items.packet_code, 
  ra_items.package_code,
  sum(ra_items.cargo_place_count),
  sum(ra_items.packet_count), 
  sum(ra_items.package_count), 
  sum(ra_items.weight_gross), 
  sum(ra_items.weight_nett)

from 
    dbo.reciving_act_items  ra_items,
    dbo.cargo               cargo       (index ind_cargo),
    dbo.reciving_acts       ra_head     (index ind_recive_acts),
    dbo.sessions            sess
where
    ra_items.cargo_code = cargo.cargo_id and
    ra_items.recive_act_code = ra_head.recive_act_id and
    ra_head.source_storage_code is null and 	
    ra_head.session_code = sess.session_id and
    sess.session_date >= @start_date and
    sess.session_date <= @end_date and
    (case 
     when @object_type =  1  then  1 
     when @object_type =  2  and 
        (select MESTO_KRAT 
         from ref.dbo.S_SKLAD (index IND_SKLAD) 
         where PK_SKLAD = ra_head.target_storage_code) = @object_code then  1 
     when @object_type =  3  and ra_head.target_storage_code = @storage_code then  1 
     else  0 
     end) =  1  and
    (case 
     when @allow_agreement is null or @allow_agreement =  0  then  1  
     when @allow_agreement >  0  and @allow_agreement = ra_head.agreement_code then  1 
     else  0 
     end) =  1  and
    (case 
     when @allow_agreement_cargo is null or @allow_agreement_cargo =  0  then  1  
     when @allow_agreement_cargo >  0  and @allow_agreement_cargo = ra_items.agreement_cargo_code then  1 
     else  0 
     end) =  1  and
    (case 
     when @allow_shipper is null or @allow_shipper =  0  then  1  
     when @allow_shipper >  0  and @allow_shipper = ra_head.shipper_code then  1 
     else  0 
     end) =  1  and
    (case 
     when @allow_consignee is null or @allow_consignee =  0  then  1  
     when @allow_consignee >  0  and @allow_consignee = ra_head.consignee_code then  1 
     else  0 
     end) =  1  and
    (case 
     when @allow_forward is null or @allow_forward =  0  then  1  
     when @allow_forward >  0  and @allow_forward = ra_head.forward_code then  1 
     else  0 
     end) =  1 
group by
  (case @only_cargo_group when  1  then cargo.cargo_group_code else ra_items.cargo_code end),
  (case when @allow_agreement is not null then ra_head.agreement_code else null end),
  (case when @allow_agreement_cargo is not null then ra_items.agreement_cargo_code else null end),
  (case when @allow_shipper is not null then ra_head.shipper_code else null end),
  (case when @allow_consignee is not null then ra_head.consignee_code else null end),
  (case when @allow_forward is not null then ra_head.forward_code else null end),
  ra_items.packet_code, 
  ra_items.package_code

-- ---------------------------------------------------------------------------------------
--                          приходные квх по СДАТОЧНЫМ АКТАМ
-- ---------------------------------------------------------------------------------------
if @supply_acts_rec =  1 
insert into #cargo_moves_list (doc_type, cargo_code, agreement_code, agreement_cargo_code, shipper_code, consignee_code, forward_code,
  packet_code, package_code, place_count, packet_count, package_count, weight_gross, weight_nett)
select 
   2 ,
  (case @only_cargo_group when  1  then cargo.cargo_group_code else ra_items.cargo_code end),
  (case when @allow_agreement is not null then ra_head.agreement_code else null end),
  (case when @allow_agreement_cargo is not null then ra_items.agreement_cargo_code else null end),
  (case when @allow_shipper is not null then ra_head.shipper_code else null end),
  (case when @allow_consignee is not null then ra_head.consignee_code else null end),
  (case when @allow_forward is not null then ra_head.forward_code else null end),
  ra_items.packet_code, 
  ra_items.package_code,
  sum(ra_items.cargo_place_count),
  sum(ra_items.packet_count), 
  sum(ra_items.package_count), 
  sum(ra_items.weight_gross), 
  sum(ra_items.weight_nett)

from 
    dbo.reciving_act_items  ra_items,
    dbo.cargo               cargo       (index ind_cargo),
    dbo.reciving_acts       ra_head     (index ind_recive_acts),
    dbo.sessions            sess
where
    ra_items.cargo_code = cargo.cargo_id and
    ra_items.recive_act_code = ra_head.recive_act_id and
    ra_head.source_storage_code is not null and 	
    ra_head.session_code = sess.session_id and
    sess.session_date >= @start_date and
    sess.session_date <= @end_date and
    (case 
     when @object_type =  1  then  1 
     when @object_type =  2  and 
        (select MESTO_KRAT 
         from ref.dbo.S_SKLAD (index IND_SKLAD) 
         where PK_SKLAD = ra_head.target_storage_code) = @object_code then  1 
     when @object_type =  3  and ra_head.target_storage_code = @storage_code then  1 
     else  0 
     end) = 1and
    (case 
     when @allow_agreement is null or @allow_agreement =  0  then  1  
     when @allow_agreement >  0  and @allow_agreement = ra_head.agreement_code then  1 
     else  0 
     end) =  1  and
    (case 
     when @allow_agreement_cargo is null or @allow_agreement_cargo =  0  then  1  
     when @allow_agreement_cargo >  0  and @allow_agreement_cargo = ra_items.agreement_cargo_code then  1 
     else  0 
     end) =  1  and
    (case 
     when @allow_shipper is null or @allow_shipper =  0  then  1  
     when @allow_shipper >  0  and @allow_shipper = ra_head.shipper_code then  1 
     else  0 
     end) =  1  and
    (case 
     when @allow_consignee is null or @allow_consignee =  0  then  1  
     when @allow_consignee >  0  and @allow_consignee = ra_head.consignee_code then  1 
     else  0 
     end) =  1  and
    (case 
     when @allow_forward is null or @allow_forward =  0  then  1  
     when @allow_forward >  0  and @allow_forward = ra_head.forward_code then  1 
     else  0 
     end) =  1  
group by
  (case @only_cargo_group when  1  then cargo.cargo_group_code else ra_items.cargo_code end),
  (case when @allow_agreement is not null then ra_head.agreement_code else null end),
  (case when @allow_agreement_cargo is not null then ra_items.agreement_cargo_code else null end),
  (case when @allow_shipper is not null then ra_head.shipper_code else null end),
  (case when @allow_consignee is not null then ra_head.consignee_code else null end),
  (case when @allow_forward is not null then ra_head.forward_code else null end),
  ra_items.packet_code, 
  ra_items.package_code

-- ---------------------------------------------------------------------------------------
--                          расходные квх по СДАТОЧНЫМ АКТАМ
-- ---------------------------------------------------------------------------------------
if @supply_acts_trans =  1 
insert into #cargo_moves_list (doc_type, cargo_code, agreement_code, agreement_cargo_code, shipper_code, consignee_code, forward_code,
  packet_code, package_code, place_count, packet_count, package_count, weight_gross, weight_nett)
select 
   4 ,
  (case @only_cargo_group when  1  then cargo.cargo_group_code else ra_items.cargo_code end),
  (case when @allow_agreement is not null then ra_head.agreement_code else null end),
  (case when @allow_agreement_cargo is not null then ra_items.agreement_cargo_code else null end),
  (case when @allow_shipper is not null then ra_head.shipper_code else null end),
  (case when @allow_consignee is not null then ra_head.consignee_code else null end),
  (case when @allow_forward is not null then ra_head.forward_code else null end),

  ra_items.packet_code, 
  ra_items.package_code,
  sum(supply_act_items.cargo_place_count),
  sum(supply_act_items.packet_count), 
  sum(supply_act_items.package_count), 
  sum(supply_act_items.weight_gross), 
  sum(supply_act_items.weight_nett)
from 
    dbo.reciving_acts       supply_acts,
    dbo.reciving_act_items  supply_act_items, -- (index ind_fk_recive_act_code),
    dbo.reciving_act_items  ra_items (index ind_recive_act_items),
    dbo.cargo               cargo       (index ind_cargo),
    dbo.reciving_acts       ra_head     (index ind_recive_acts),
    dbo.sessions            sess
where
    supply_acts.source_storage_code is not null and
    supply_acts.recive_act_id = supply_act_items.recive_act_code and
    supply_act_items.supply_act_item_code = ra_items.recive_act_item_id and
    ra_items.cargo_code = cargo.cargo_id and
    ra_items.recive_act_code = ra_head.recive_act_id and
    supply_acts.session_code = sess.session_id and
    sess.session_date >= @start_date and
    sess.session_date <= @end_date and
    (case 
     when @object_type =  1  then  1 
     when @object_type =  2  and 
        (select MESTO_KRAT 
         from ref.dbo.S_SKLAD (index IND_SKLAD) 
         where PK_SKLAD = ra_head.target_storage_code) = @object_code then  1 
     when @object_type =  3  and ra_head.target_storage_code = @storage_code then  1 
     else  0 
     end) =  1  and
    (case 
     when @allow_agreement is null or @allow_agreement =  0  then  1  
     when @allow_agreement >  0  and @allow_agreement = ra_head.agreement_code then  1 
     else  0 
     end) =  1  and
    (case 
     when @allow_agreement_cargo is null or @allow_agreement_cargo =  0  then  1  
     when @allow_agreement_cargo >  0  and @allow_agreement_cargo = ra_items.agreement_cargo_code then  1 
     else  0 
     end) =  1  and
    (case 
     when @allow_shipper is null or @allow_shipper =  0  then  1  
     when @allow_shipper >  0  and @allow_shipper = ra_head.shipper_code then  1 
     else  0 
     end) =  1  and
    (case 
     when @allow_consignee is null or @allow_consignee =  0  then  1  
     when @allow_consignee >  0  and @allow_consignee = ra_head.consignee_code then  1 
     else  0 
     end) =  1  and
    (case 
     when @allow_forward is null or @allow_forward =  0  then  1  
     when @allow_forward >  0  and @allow_forward = ra_head.forward_code then  1 
     else  0 
     end) =  1 
group by
  (case @only_cargo_group when  1  then cargo.cargo_group_code else ra_items.cargo_code end),
  (case when @allow_agreement is not null then ra_head.agreement_code else null end),
  (case when @allow_agreement_cargo is not null then ra_items.agreement_cargo_code else null end),
  (case when @allow_shipper is not null then ra_head.shipper_code else null end),
  (case when @allow_consignee is not null then ra_head.consignee_code else null end),
  (case when @allow_forward is not null then ra_head.forward_code else null end),
  ra_items.packet_code, 
  ra_items.package_code

-- ---------------------------------------------------------------------------------------
--                          расходные квх по АКТАМ СПИСАНИЯ
-- ---------------------------------------------------------------------------------------
if @discharge_act =  1 
insert into #cargo_moves_list (doc_type, cargo_code, agreement_code, agreement_cargo_code, shipper_code, consignee_code, forward_code,
  packet_code, package_code, place_count, packet_count, package_count, weight_gross, weight_nett)
select 
   5 ,
  (case @only_cargo_group when  1  then cargo.cargo_group_code else ra_items.cargo_code end),
  (case when @allow_agreement is not null then ra_head.agreement_code else null end),
  (case when @allow_agreement_cargo is not null then ra_items.agreement_cargo_code else null end),
  (case when @allow_shipper is not null then ra_head.shipper_code else null end),
  (case when @allow_consignee is not null then ra_head.consignee_code else null end),
  (case when @allow_forward is not null then ra_head.forward_code else null end),
  ra_items.packet_code, 
  ra_items.package_code,
  sum(discharge_act_items.cargo_place_count),
  sum(discharge_act_items.packet_count), 
  sum(discharge_act_items.package_count), 
  sum(discharge_act_items.weight_gross), 
  sum(discharge_act_items.weight_nett)
from 
    dbo.discharge_acts          discharge_acts,
    dbo.discharge_act_items     discharge_act_items, -- (index ind_fk_recive_act_code),
    dbo.reciving_act_items      ra_items (index ind_recive_act_items),
    dbo.cargo                   cargo       (index ind_cargo),
    dbo.reciving_acts           ra_head     (index ind_recive_acts),
    dbo.sessions                sess
where
    discharge_acts.discharge_act_id = discharge_act_items.discharge_act_code and
    discharge_act_items.recive_act_item_code = ra_items.recive_act_item_id and
    ra_items.cargo_code = cargo.cargo_id and
    ra_items.recive_act_code = ra_head.recive_act_id and
    discharge_acts.session_code = sess.session_id and
    sess.session_date >= @start_date and
    sess.session_date <= @end_date and
    (case 
     when @object_type =  1  then  1 
     when @object_type =  2  and 
        (select MESTO_KRAT 
         from ref.dbo.S_SKLAD (index IND_SKLAD) 
         where PK_SKLAD = ra_head.target_storage_code) = @object_code then  1 
     when @object_type =  3  and ra_head.target_storage_code = @storage_code then  1 
     else  0 
     end) =  1  and
    (case 
     when @allow_agreement is null or @allow_agreement =  0  then  1  
     when @allow_agreement >  0  and @allow_agreement = ra_head.agreement_code then  1 
     else  0 
     end) =  1  and
    (case 
     when @allow_agreement_cargo is null or @allow_agreement_cargo =  0  then  1  
     when @allow_agreement_cargo >  0  and @allow_agreement_cargo = ra_items.agreement_cargo_code then  1 
     else  0 
     end) =  1  and
    (case 
     when @allow_shipper is null or @allow_shipper =  0  then  1  
     when @allow_shipper >  0  and @allow_shipper = ra_head.shipper_code then  1 
     else  0 
     end) =  1  and
    (case 
     when @allow_consignee is null or @allow_consignee =  0  then  1  
     when @allow_consignee >  0  and @allow_consignee = ra_head.consignee_code then  1 
     else  0 
     end) =  1  and
    (case 
     when @allow_forward is null or @allow_forward =  0  then  1  
     when @allow_forward >  0  and @allow_forward = ra_head.forward_code then  1 
     else  0 
     end) =  1 
group by
  (case @only_cargo_group when  1  then cargo.cargo_group_code else ra_items.cargo_code end),
  (case when @allow_agreement is not null then ra_head.agreement_code else null end),
  (case when @allow_agreement_cargo is not null then ra_items.agreement_cargo_code else null end),
  (case when @allow_shipper is not null then ra_head.shipper_code else null end),
  (case when @allow_consignee is not null then ra_head.consignee_code else null end),
  (case when @allow_forward is not null then ra_head.forward_code else null end),
  ra_items.packet_code, 
  ra_items.package_code

-- -------------------------------------------------------------------------------------
--                          расходные квх по РАСХОДНЫМ ОРДЕРАМ
-- -------------------------------------------------------------------------------------
if @expenses_order =  1 
insert into #cargo_moves_list (doc_type, cargo_code, agreement_code, agreement_cargo_code, shipper_code, consignee_code, forward_code,
  packet_code, package_code, place_count, packet_count, package_count, weight_gross, weight_nett)
select 
   6 ,
  (case @only_cargo_group when  1  then cargo.cargo_group_code else ra_items.cargo_code end),
  (case when @allow_agreement is not null then ra_head.agreement_code else null end),
  (case when @allow_agreement_cargo is not null then ra_items.agreement_cargo_code else null end),
  (case when @allow_shipper is not null then ra_head.shipper_code else null end),
  (case when @allow_consignee is not null then ra_head.consignee_code else null end),
  (case when @allow_forward is not null then ra_head.forward_code else null end),

  ra_items.packet_code, 
  ra_items.package_code,
  sum(expenses_order_items.cargo_place_count),
  sum(expenses_order_items.packet_count), 
  sum(expenses_order_items.package_count), 
  sum(expenses_order_items.weight_gross), 
  sum(expenses_order_items.weight_nett)
from 
    dbo.expenses_orders         expenses_orders,
    dbo.expenses_order_items    expenses_order_items (index ind_expenses_order_code),
    dbo.reciving_act_items      ra_items (index ind_recive_act_items),
    dbo.cargo                   cargo       (index ind_cargo),
    dbo.reciving_acts           ra_head     (index ind_recive_acts),
    dbo.sessions                sess
where
    expenses_orders.expenses_order_id = expenses_order_items.expenses_order_code and
    expenses_order_items.recive_act_item_code = ra_items.recive_act_item_id and
    ra_items.cargo_code = cargo.cargo_id and
    ra_items.recive_act_code = ra_head.recive_act_id and
    expenses_orders.session_code = sess.session_id and
    sess.session_date >= @start_date and
    sess.session_date <= @end_date and
    (case 
     when @object_type =  1  then  1 
     when @object_type =  2  and 
        (select MESTO_KRAT 
         from ref.dbo.S_SKLAD (index IND_SKLAD) 
         where PK_SKLAD = ra_head.target_storage_code) = @object_code then  1 
     when @object_type =  3  and ra_head.target_storage_code = @storage_code then  1 
     else  0 
     end) =  1  and
    (case 
     when @allow_agreement is null or @allow_agreement =  0  then  1  
     when @allow_agreement >  0  and @allow_agreement = ra_head.agreement_code then  1 
     else  0 
     end) =  1  and
    (case 
     when @allow_agreement_cargo is null or @allow_agreement_cargo =  0  then  1  
     when @allow_agreement_cargo >  0  and @allow_agreement_cargo = ra_items.agreement_cargo_code then  1 
     else  0 
     end) =  1  and
    (case 
     when @allow_shipper is null or @allow_shipper =  0  then  1  
     when @allow_shipper >  0  and @allow_shipper = ra_head.shipper_code then  1 
     else  0 
     end) =  1  and
    (case 
     when @allow_consignee is null or @allow_consignee =  0  then  1  
     when @allow_consignee >  0  and @allow_consignee = ra_head.consignee_code then  1 
     else  0 
     end) =  1  and
    (case 
     when @allow_forward is null or @allow_forward =  0  then  1  
     when @allow_forward >  0  and @allow_forward = ra_head.forward_code then  1 
     else  0 
     end) =  1 
group by
  (case @only_cargo_group when  1  then cargo.cargo_group_code else ra_items.cargo_code end),
  (case when @allow_agreement is not null then ra_head.agreement_code else null end),
  (case when @allow_agreement_cargo is not null then ra_items.agreement_cargo_code else null end),
  (case when @allow_shipper is not null then ra_head.shipper_code else null end),
  (case when @allow_consignee is not null then ra_head.consignee_code else null end),
  (case when @allow_forward is not null then ra_head.forward_code else null end),
  ra_items.packet_code, 
  ra_items.package_code

-- -------------------------------------------------------------------------------------
--                                  расходные квх по СПГ
-- -------------------------------------------------------------------------------------
if @cargo_loading_list =  1 
insert into #cargo_moves_list (doc_type, cargo_code, agreement_code, agreement_cargo_code, shipper_code, consignee_code, forward_code,
  packet_code, package_code, place_count, packet_count, package_count, weight_gross, weight_nett)
select 
   3 ,
  (case @only_cargo_group when  1  then cargo.cargo_group_code else ra_items.cargo_code end),
  (case when @allow_agreement is not null then ra_head.agreement_code else null end),
  (case when @allow_agreement_cargo is not null then ra_items.agreement_cargo_code else null end),
  (case when @allow_shipper is not null then ra_head.shipper_code else null end),
  (case when @allow_consignee is not null then ra_head.consignee_code else null end),
  (case when @allow_forward is not null then ra_head.forward_code else null end),

  ra_items.packet_code, 
  ra_items.package_code,
  sum(cargo_loading_list_items.cargo_place_count),
  sum(cargo_loading_list_items.packet_count), 
  sum(cargo_loading_list_items.package_count), 
  sum(cargo_loading_list_items.weight_gross), 
  sum(cargo_loading_list_items.weight_nett)
from 
    dbo.cargo_loading_list          cargo_loading_list,
    dbo.cargo_loading_list_items    cargo_loading_list_items, 
    dbo.reciving_act_items          ra_items (index ind_recive_act_items),
    dbo.cargo                       cargo       (index ind_cargo),
    dbo.reciving_acts               ra_head     (index ind_recive_acts),
    dbo.sessions                    sess
where
    cargo_loading_list.cargo_loading_list_id = cargo_loading_list_items.cargo_loading_list_code and
    cargo_loading_list_items.recive_act_item_code = ra_items.recive_act_item_id and
    ra_items.cargo_code = cargo.cargo_id and
    ra_items.recive_act_code = ra_head.recive_act_id and
    cargo_loading_list.session_code = sess.session_id and
    sess.session_date >= @start_date and
    sess.session_date <= @end_date and
    (case 
     when @object_type =  1  then  1 
     when @object_type =  2  and 
        (select MESTO_KRAT 
         from ref.dbo.S_SKLAD (index IND_SKLAD) 
         where PK_SKLAD = ra_head.target_storage_code) = @object_code then  1 
     when @object_type =  3  and ra_head.target_storage_code = @storage_code then  1 
     else  0 
     end) =  1  and
    (case 
     when @allow_agreement is null or @allow_agreement =  0  then  1  
     when @allow_agreement >  0  and @allow_agreement = ra_head.agreement_code then  1 
     else  0 
     end) =  1  and
    (case 
     when @allow_agreement_cargo is null or @allow_agreement_cargo =  0  then  1  
     when @allow_agreement_cargo >  0  and @allow_agreement_cargo = ra_items.agreement_cargo_code then  1 
     else  0 
     end) =  1  and
    (case 
     when @allow_shipper is null or @allow_shipper =  0  then  1  
     when @allow_shipper >  0  and @allow_shipper = ra_head.shipper_code then  1 
     else  0 
     end) =  1  and
    (case 
     when @allow_consignee is null or @allow_consignee =  0  then  1  
     when @allow_consignee >  0  and @allow_consignee = ra_head.consignee_code then  1 
     else  0 
     end) =  1  and
    (case 
     when @allow_forward is null or @allow_forward =  0  then  1  
     when @allow_forward >  0  and @allow_forward = ra_head.forward_code then  1 
     else  0 
     end) =  1 
group by
  (case @only_cargo_group when  1  then cargo.cargo_group_code else ra_items.cargo_code end),
  (case when @allow_agreement is not null then ra_head.agreement_code else null end),
  (case when @allow_agreement_cargo is not null then ra_items.agreement_cargo_code else null end),
  (case when @allow_shipper is not null then ra_head.shipper_code else null end),
  (case when @allow_consignee is not null then ra_head.consignee_code else null end),
  (case when @allow_forward is not null then ra_head.forward_code else null end),
  ra_items.packet_code, 
  ra_items.package_code

-- -------------------------------------------------------------------------------------
--                                  ПОЕХАЛИ
-- -------------------------------------------------------------------------------------

select 
    doc_type,
    cargo_exists.cargo_code,
    cargo_name = (case @only_cargo_group
        when  1  then (select cargo_group_name from dbo.cargo_group (index ind_cargo_group) where cargo_group.cargo_group_id = cargo_exists.cargo_code)
        else (select cargo_name from dbo.cargo (index ind_cargo) where cargo.cargo_id = cargo_exists.cargo_code)
        end),
    cargo_exists.agreement_code,
    agreement_number        = agreement.NUM_DOGOV,
    agreement_date          = agreement.DATE_DOGOV,
    cargo_exists.agreement_cargo_code,
    agreement_cargo_name    = agreement_cargo.CARGO_NAME,
    cargo_exists.shipper_code,
    shipper_name            = shipper.OTPRAV_NAME,
    cargo_exists.consignee_code,
    consignee_name          = consignee.POLYSH_NAME,
    cargo_exists.forward_code,
    forward_name            = forward.EKSP_NAME,
    cargo_exists.place_count,
    cargo_exists.packet_code,
    packet_short_rus        = packet.packet_short_rus,
    packet_name_rus         = packet.packet_name_rus,
    cargo_exists.packet_count,
    cargo_exists.package_code,
    package_short_rus       = package.packet_short_rus,
    package_name_rus        = package.packet_name_rus,
    cargo_exists.package_count,
    cargo_exists.weight_gross,
    cargo_exists.weight_nett
from 
    #cargo_moves_list           cargo_exists,
    ref.dbo.packet              packet (index ind_packet),
    ref.dbo.packet              package (index ind_packet),
    comer.dbo.S_COMER_DOGOV     agreement (index IND_DOGOVOR),
    comer.dbo.S_COMER_CARGO     agreement_cargo (index IND_COMER_CARGO),
    dbo.S_OTPRAV_GD             shipper (index IND_PK_OTPRAV),
    dbo.S_POLYSH_GD             consignee (index IND_PK_POLYSH),
    comer.dbo.S_COMER_EKSP      forward (index IND_COMER_EKSP)
where 
    cargo_exists.packet_code            *= packet.packet_id and
    cargo_exists.package_code           *= package.packet_id and
    cargo_exists.agreement_code         *= agreement.PK_DOGOV and
    cargo_exists.agreement_cargo_code   *= agreement_cargo.PK_CARGO and
    cargo_exists.shipper_code           *= shipper.PK_OTPRAV and
    cargo_exists.consignee_code         *= consignee.PK_POLYSH and
    cargo_exists.forward_code           *= forward.PK_EKSP

begin tran
report_cargo_moves_list...