Project

General

Profile

Download (51 KB)

Feature #3501 » PFILOUPDATEMDSTOCKINFO_20250313_ChangeByJd250.sql

Tri Rizqiaty, 03/13/2025 03:26 PM

 
1 
??ALTER PROCEDURE [dbo].[PFILOUPDATEMDSTOCKINFO]
2
3 
(
4
5 
	@MaterialID VARCHAR(20)
6
7 
	,@Quantity DECIMAL(22,2)
8
9 
	,@TotalPrice DECIMAL(20,2)
10
11 
	,@DocType VARCHAR(20)
12
13 
	,@UserID VARCHAR(20)
14
15 
	,@CompanyID VARCHAR(20)
16
17 
	,@LogisticArea VARCHAR(20)
18
19 
	,@Warehouse VARCHAR(20)
20
21 
	,@Storage VARCHAR(20)
22
23 
	,@Bin VARCHAR(20)
24
25 
	,@ReceiverLogisticArea VARCHAR(20)
26
27 
	,@ReceiverWarehouse VARCHAR(20)
28
29 
	,@ReceiverStorage VARCHAR(20)
30
31 
	,@ReceiverBin VARCHAR(20)
32
33 
	,@DocIDRef VARCHAR(20)
34
35 
	,@DocTypeRef VARCHAR(20)
36
37 
	,@TransTypeRef VARCHAR(20)
38
39 
	,@ItemFlow VARCHAR(50)
40
41 
	,@StockStatus VARCHAR(20)
42
43 
	,@DocStatus VARCHAR(20)
44
45 
)
46
47 
AS
48
49
50
51 
--DECLARE @MaterialID VARCHAR(20) = '00000010'
52
53 
--DECLARE @Quantity DECIMAL(22,2) = 1.0
54
55 
--DECLARE @TotalPrice DECIMAL(20,2) = 0
56
57 
--DECLARE @DocType VARCHAR(20) = 'GICU'
58
59 
--DECLARE @UserID VARCHAR(20) = 'tri'
60
61 
--DECLARE @CompanyID VARCHAR(20) = ''
62
63 
--DECLARE @LogisticArea VARCHAR(20) = ''
64
65 
--DECLARE @Warehouse VARCHAR(20) = ''
66
67 
--DECLARE @Storage VARCHAR(20) = ''
68
69 
--DECLARE @Bin VARCHAR(20) = ''
70
71 
--DECLARE @ReceiverLogisticArea VARCHAR(20) = ''
72
73 
--DECLARE @ReceiverWarehouse VARCHAR(20) = ''
74
75 
--DECLARE @ReceiverStorage VARCHAR(20) = ''
76
77 
--DECLARE @ReceiverBin VARCHAR(20) = ''
78
79 
--DECLARE @DocIDRef VARCHAR(20) = '00000264'
80
81 
--DECLARE @DocTypeRef VARCHAR(20) = 'SORD'
82
83 
--DECLARE @TransTypeRef VARCHAR(20) = 'SLS'
84
85 
--DECLARE @ItemFlow VARCHAR(50) = ''--'ISSU' --ISSU --RCPT
86
87 
--DECLARE @StockStatus VARCHAR(20) = ''
88
89 
--DECLARE @DocStatus VARCHAR(20) = '3' -- 1:Draft , 2:In Process, 3:Done, 4:Cancel
90
91
92
93 
DECLARE @now VARCHAR(20) = dbo.fn_formatdatetime(GETDATE(), 'yyyymmdd')
94
95 
DECLARE @now16 VARCHAR(20) = dbo.fn_formatdatetime(GETDATE(), 'yyyymmddhhnnss')
96
97
98
99 
DECLARE @IsSuccess VARCHAR(250)
100
101
102
103 
IF(@StockStatus = '')
104
105 
BEGIN SET @StockStatus = '0001' END
106
107
108
109 
IF(@Quantity < 0)
110
111 
BEGIN SET @Quantity = @Quantity * -1 END
112
113
114
115 
DECLARE @TblMAT4 TABLE
116
117 
(
118
119 
	[MaterialID] [VARCHAR](10) NOT NULL,
120
121 
	[SeqID] [BIGINT] NOT NULL,
122
123 
	[CompanyID] [VARCHAR](4) NOT NULL,
124
125 
	[LogisticAreaID] [VARCHAR](10) NOT NULL,
126
127 
	[Warehouse] [VARCHAR](20) NOT NULL,
128
129 
	[Storage] [VARCHAR](10) NOT NULL,
130
131 
	[Bin] [VARCHAR](10) NOT NULL,
132
133 
	[StockStatus] [VARCHAR](10) NOT NULL,
134
135 
	[GLValuationID] [VARCHAR](20) NULL,
136
137 
	[Qty] DECIMAL(22,2) NULL,
138
139 
	[TotalValuation] [DECIMAL](22, 2) NULL,
140
141 
	[Sequence] [INT] NULL,
142
143 
	[SerialNumber] [VARCHAR](50) NULL,
144
145 
	[CreateBy] [VARCHAR](250) NULL,
146
147 
	[CreateDate] [VARCHAR](14) NULL,
148
149 
	[ChangeBy] [VARCHAR](250) NULL,
150
151 
	[ChangeDate] [VARCHAR](14) NULL,
152
153 
	[SORDID] [VARCHAR](18) NULL,
154
155 
	[PRODID] [VARCHAR](18) NULL,
156
157 
	[INTRID] [VARCHAR](18) NULL,
158
159 
	[POID] [VARCHAR](18) NULL,
160
161 
	[StockType] [VARCHAR](10) NULL,
162
163 
	[DocIDRef] [VARCHAR](10) NULL,
164
165 
	[DocTypeRef] [VARCHAR](10) NULL,
166
167 
	[TransTypeRef] [VARCHAR](10) NULL
168
169 
	--,HargaSatuan DECIMAL(22,2)
170
171 
)
172
173 
INSERT INTO @TblMAT4
174
175 
SELECT [MaterialID]
176
177 
      ,[SeqID]
178
179 
      ,[CompanyID]
180
181 
      ,[LogisticAreaID]
182
183 
      ,[Warehouse]
184
185 
      ,[Storage]
186
187 
      ,[Bin]
188
189 
      ,[StockStatus]
190
191 
      ,[GLValuationID]
192
193 
      ,[Qty]
194
195 
      ,[TotalValuation]
196
197 
      ,[Sequence]
198
199 
      ,[SerialNumber]
200
201 
      ,[CreateBy]
202
203 
      ,[CreateDate]
204
205 
      ,[ChangeBy]
206
207 
      ,[ChangeDate]
208
209 
      ,[SORDID]
210
211 
      ,[PRODID]
212
213 
      ,[INTRID]
214
215 
      ,[POID]
216
217 
      ,[StockType]
218
219 
      ,[DocIDRef]
220
221 
      ,[DocTypeRef]
222
223 
      ,[TransTypeRef]
224
225 
	  --,HargaSatuan
226
227 
FROM dbo.PMDMAT0004
228
229 
WHERE MaterialID = @MaterialID
230
231
232
233 
DECLARE @MAP_MAT1 DECIMAL(22,2) = 0
234
235 
DECLARE @NewTotalValuation DECIMAL(22,2)
236
237 
DECLARE @CekItemNotDoneAloc VARCHAR(20)
238
239 
DECLARE @CekItemNotDone VARCHAR(20)
240
241 
DECLARE @SisaQty DECIMAL(22,2) = @Quantity
242
243
244
245 
IF(@DocType = 'GICU' AND (@DocStatus = '3' OR @DocStatus = '2') )
246
247 
BEGIN
248
249 
	SELECT @MAP_MAT1 = MAP FROM dbo.PMDMAT0001 WHERE MaterialID = @MaterialID
250
251
252
253 
	IF(@DocIDRef <> '' AND @DocTypeRef <> '' AND @TransTypeRef <>'') ----// jika doc ref dr sales order
254
255 
	BEGIN
256
257 
		DECLARE @CekAlocate DECIMAL(22,0)
258
259 
		SELECT DISTINCT @CekAlocate = COUNT(MaterialID)
260
261 
		FROM dbo.PMDMAT0004 WHERE SORDID = @DocIDRef AND StockStatus = '0004' AND MaterialID = @MaterialID
262
263
264
265 
		IF(@CekAlocate > 0)  ----// ada data alocate
266
267 
		BEGIN		----// delete data alocate begin
268
269 
			DELETE dbo.PMDMAT0004
270
271 
			--SELECT *
272
273 
			FROM dbo.PMDMAT0004 WHERE SORDID = @DocIDRef AND StockStatus = '0004' AND MaterialID = @MaterialID
274
275 
			UPDATE dbo.PTRSALESITEMS
276
277 
			SET ItemStatus = '2'
278
279 
			WHERE DocNo = @DocIDRef AND Material = @MaterialID
280
281
282
283 
			SELECT @CekItemNotDone = COUNT(ItemStatus)
284
285 
			FROM dbo.PTRSALESITEMS
286
287 
			WHERE DocNo = @DocIDRef AND (ItemStatus = '0' OR ItemStatus = '1')
288
289
290
291 
			IF(@CekItemNotDone = 0)
292
293 
			BEGIN
294
295 
				UPDATE dbo.PTRSALESHEADER
296
297 
				SET ProcessStatus = 'C'
298
299 
				WHERE DocNo = @DocIDRef
300
301 
			END
302
303
304
305 
			SET @IsSuccess = 'Y'
306
307 
		END		----// delete data alocate end
308
309 
		ELSE	----// tdk ada data alocate & docstatus sord closed
310
311 
		BEGIN	----// tdk ada data alocate begin
312
313 
			DECLARE @tbl_warehouse TABLE
314
315 
			(
316
317 
				wh VARCHAR(20)
318
319 
			)
320
321 
			INSERT INTO @tbl_warehouse
322
323 
			SELECT Warehouse FROM PTRSALESHEADER AS head LEFT JOIN PCMFILOSALESTOWAREHOUSE AS sls ON head.SalesOffice = sls.SalesOfficeCode WHERE DocNo = @DocIDRef
324
325
326
327 
			IF((SELECT COUNT(wh) FROM @tbl_warehouse) = 0)  BEGIN DELETE FROM @tbl_warehouse INSERT INTO @tbl_warehouse SELECT Warehouse FROM PMDMAT0004 WHERE MaterialID = @MaterialID ORDER BY SeqID ASC END
328
329
330
331 
			DECLARE @c_wh VARCHAR(20)
332
333 
			DECLARE cur_wh CURSOR FOR (SELECT DISTINCT wh FROM @tbl_warehouse)
334
335 
			OPEN cur_wh
336
337 
			FETCH cur_wh INTO @c_wh
338
339 
			WHILE @@Fetch_Status = 0
340
341 
			BEGIN
342
343
344
345 
				DECLARE @SumMat4Aloc DECIMAL(22,2) = 0
346
347 
				IF(@Warehouse = '')
348
349 
				BEGIN
350
351 
					SELECT @SumMat4Aloc = SUM(Qty)	FROM @TblMAT4
352
353 
					WHERE MaterialID = @MaterialID --AND Warehouse = @Warehouse --AND StockStatus = @StockStatus
354
355 
				END
356
357 
				ELSE
358
359 
				BEGIN
360
361 
					SELECT @SumMat4Aloc = SUM(Qty)	FROM @TblMAT4
362
363 
					WHERE MaterialID = @MaterialID AND Warehouse = @Warehouse --AND StockStatus = @StockStatus
364
365 
				END
366
367
368
369 
				IF(@Quantity > @SumMat4Aloc)
370
371 
				BEGIN
372
373 
					SET @IsSuccess = 'OVERQTY'
374
375 
				END
376
377 
				ELSE
378
379 
				BEGIN
380
381 
					DECLARE @QtyAlocateMAT4 DECIMAL(22,2) = 0
382
383 
					DECLARE @c_materialidA VARCHAR(20)
384
385 
					DECLARE @c_seqA VARCHAR(20)
386
387 
					DECLARE @c_qtyA VARCHAR(20)
388
389 
					DECLARE cur_mat4A CURSOR FOR (SELECT MaterialID, SeqID, Qty FROM @TblMAT4 WHERE MaterialID = @MaterialID AND Warehouse = @c_wh) --AND StockStatus = @StockStatus )
390
391 
					OPEN cur_mat4A
392
393 
					FETCH cur_mat4A INTO @c_materialidA, @c_seqA, @c_qtyA
394
395 
					WHILE @@Fetch_Status = 0
396
397 
					BEGIN
398
399
400
401 
						SET @QtyAlocateMAT4 = @c_qtyA
402
403
404
405 
						IF(@SisaQty = 0)
406
407 
						BEGIN
408
409 
							BREAK
410
411 
						END
412
413 
						ELSE IF(@QtyAlocateMAT4 > @SisaQty)				----// case good issue sebagian(partial)
414
415 
						BEGIN
416
417 
							DECLARE @SisaQtyAlocateMAT4 DECIMAL(22,2) = 0
418
419 
							SET @SisaQtyAlocateMAT4 = @QtyAlocateMAT4 - @SisaQty
420
421
422
423 
							UPDATE dbo.PMDMAT0004					----// update quantity mat4
424
425 
							SET Qty = @SisaQtyAlocateMAT4
426
427 
								,TotalValuation = @SisaQtyAlocateMAT4 * @MAP_MAT1
428
429 
								,ChangeBy = @UserID
430
431 
								,ChangeDate = @now16
432
433 
							--SELECT 'update qty mat4', @SisaQtyAlocateMAT4, * FROM dbo.PMDMAT0004
434
435 
							WHERE MaterialID = @MaterialID
436
437 
								AND Warehouse = @c_wh
438
439 
								AND SeqID = @c_seqA
440
441
442
443 
							SET @IsSuccess = 'Y'
444
445
446
447 
							BREAK
448
449 
						END
450
451 
						ELSE IF(@SisaQty >= @QtyAlocateMAT4)
452
453 
						BEGIN
454
455 
							SET @SisaQtyAlocateMAT4 = 0
456
457 
							SET @SisaQty = @SisaQty - @QtyAlocateMAT4
458
459
460
461 
							UPDATE dbo.PMDMAT0004					----// update quantity mat4
462
463 
							SET Qty = @SisaQtyAlocateMAT4
464
465 
								,TotalValuation = @SisaQtyAlocateMAT4 * @MAP_MAT1
466
467 
								,ChangeBy = @UserID
468
469 
								,ChangeDate = @now16
470
471 
							--SELECT 'update qty mat4', @SisaQtyAlocateMAT4, * FROM dbo.PMDMAT0004
472
473 
							WHERE MaterialID = @MaterialID
474
475 
								AND Warehouse = @c_wh
476
477 
								AND SeqID = @c_seqA
478
479
480
481 
							SET @IsSuccess = 'Y'
482
483 
						END
484
485 
						FETCH cur_mat4A INTO @c_materialidA, @c_seqA, @c_qtyA
486
487 
					END
488
489 
					CLOSE cur_mat4A
490
491 
					DEALLOCATE cur_mat4A
492
493
494
495 
					UPDATE dbo.PTRSALESITEMS
496
497 
					SET ItemStatus = '2'
498
499 
					WHERE DocNo = @DocIDRef AND Material = @MaterialID
500
501
502
503 
					SELECT @CekItemNotDone = COUNT(ItemStatus)
504
505 
					FROM dbo.PTRSALESITEMS
506
507 
					WHERE DocNo = @DocIDRef AND (ItemStatus = '0' OR ItemStatus = '1')
508
509
510
511 
					IF(@CekItemNotDone = 0)
512
513 
					BEGIN
514
515 
						UPDATE dbo.PTRSALESHEADER
516
517 
						SET ProcessStatus = 'C'
518
519 
						WHERE DocNo = @DocIDRef
520
521 
					END
522
523 
				END
524
525 
			FETCH cur_wh INTO @c_wh
526
527 
			END
528
529 
			CLOSE cur_wh
530
531 
			DEALLOCATE cur_wh
532
533 
		END		----// tdk ada data alocate begin
534
535 
	END
536
537 
	ELSE		----// tdk ada doc ref
538
539 
	BEGIN		----// tdk ada doc ref begin
540
541 
		DECLARE @SumQtyMAT4 DECIMAL(22,2)
542
543 
		SELECT @SumQtyMAT4 =  SUM(CONVERT(DECIMAL(22,2),Qty)) FROM @TblMAT4
544
545 
		WHERE (CompanyID = @CompanyID OR @CompanyID = '')
546
547 
			AND (LogisticAreaID = @LogisticArea OR @LogisticArea = '')
548
549 
			AND (Warehouse = @Warehouse OR @Warehouse = '')
550
551 
			AND (Storage = @Storage OR @Storage = '')
552
553 
			AND StockStatus = @StockStatus
554
555
556
557 
		IF(@Quantity <= @SumQtyMAT4)
558
559 
		BEGIN
560
561 
			DECLARE @SisaQuantity DECIMAL(22,2) = @Quantity
562
563 
			DECLARE @c_materialid VARCHAR(20)
564
565 
			DECLARE @c_seq VARCHAR(20)
566
567 
			DECLARE @c_qty VARCHAR(20)
568
569 
			DECLARE cur_mat4 CURSOR FOR (SELECT MaterialID, SeqID, Qty FROM @TblMAT4 WHERE (CompanyID = @CompanyID OR @CompanyID = '')
570
571 
										AND (LogisticAreaID = @LogisticArea OR @LogisticArea = '') AND (Warehouse = @Warehouse OR @Warehouse = '')
572
573 
										AND (Storage = @Storage OR @Storage = '') AND (Storage = @Bin OR @Bin = '') AND StockStatus = @StockStatus)
574
575 
			OPEN cur_mat4
576
577 
			FETCH cur_mat4 INTO @c_materialid, @c_seq, @c_qty
578
579 
			WHILE @@Fetch_Status = 0
580
581 
			BEGIN
582
583 
				IF(@SisaQuantity <= 0)
584
585 
				BREAK
586
587
588
589 
				DECLARE @SisaQtyMat4 DECIMAL(22,2)
590
591 
				IF(@SisaQuantity > @c_qty)
592
593 
				BEGIN
594
595 
					SET @SisaQtyMat4 = 0
596
597 
					SET @SisaQuantity = @SisaQuantity - @c_qty
598
599 
				END
600
601 
				ELSE IF(@SisaQuantity < @c_qty)
602
603 
				BEGIN
604
605 
					SET @SisaQtyMat4 = @c_qty - @SisaQuantity
606
607 
					SET @SisaQuantity = @SisaQuantity - @c_qty
608
609 
				END
610
611 
				ELSE IF(@SisaQuantity = @c_qty)
612
613 
				BEGIN
614
615 
					SET @SisaQtyMat4 = 0
616
617 
					SET @SisaQuantity = @SisaQuantity - @c_qty
618
619 
				END
620
621
622
623 
				SET @NewTotalValuation = @MAP_MAT1 * @SisaQtyMat4
624
625 
				UPDATE dbo.PMDMAT0004
626
627 
				SET Qty = @SisaQtyMat4
628
629 
					,TotalValuation = @NewTotalValuation
630
631 
					,ChangeBy = @UserID
632
633 
					,ChangeDate = @now16
634
635 
				--SELECT * FROM dbo.PMDMAT0004
636
637 
				WHERE MaterialID = @c_materialid
638
639 
					AND SeqID = @c_seq
640
641
642
643 
			FETCH cur_mat4 INTO @c_materialid, @c_seq, @c_qty
644
645 
			END
646
647 
			CLOSE cur_mat4
648
649 
			DEALLOCATE cur_mat4
650
651
652
653 
			SET @IsSuccess = 'Y'
654
655 
		END
656
657 
		ELSE
658
659 
		BEGIN
660
661 
			SET @IsSuccess = 'OVERQTY'
662
663 
		END
664
665 
	END ----// tdk ada doc ref begin end
666
667 
END
668
669
670
671
672
673 
IF(@DocType = 'RCSP' AND (@DocStatus = '3' OR @DocStatus = '2') )
674
675 
BEGIN
676
677 
	DECLARE @IsMat4Exist VARCHAR(20) = ISNULL((SELECT COUNT(*) FROM @TblMAT4),0)
678
679 
	SELECT @MAP_MAT1 = MAP FROM dbo.PMDMAT0001 WHERE MaterialID = @MaterialID
680
681
682
683 
	DECLARE @TotalValuationAllMAT4 DECIMAL(22,2)
684
685 
	DECLARE @TotalQtyAllMAT4 DECIMAL(22,2)
686
687 
	DECLARE @TotalValuationAll DECIMAL(22,2)
688
689 
	DECLARE @TotalQtyAll DECIMAL(22,2)
690
691 
	DECLARE @NewMAP DECIMAL(22,2)
692
693
694
695 
	IF(@IsMat4Exist <= 0)
696
697 
	BEGIN
698
699
700
701 
		IF(@DocIDRef <> '')
702
703 
		BEGIN
704
705 
			SELECT @NewMAP = (CONVERT(DECIMAL(22,2),TotAmount) / CONVERT(DECIMAL(22,0),MaterialQuantity))
706
707 
			FROM dbo.PTRPURCHITEM
708
709 
			WHERE Material = @MaterialID
710
711 
		END
712
713 
		ELSE
714
715 
		BEGIN
716
717 
			SELECT @NewMAP = @MAP_MAT1
718
719 
		END
720
721
722
723 
		INSERT INTO PMDMAT0004
724
725 
		SELECT @MaterialID
726
727 
			  ,'1'
728
729 
			  ,@CompanyID
730
731 
			  ,@LogisticArea
732
733 
			  ,@Warehouse
734
735 
			  ,@Storage
736
737 
			  ,@Bin
738
739 
			  ,@StockStatus --0001
740
741 
			  ,''
742
743 
			  ,@Quantity
744
745 
			  ,@Quantity * @NewMAP AS TotalValuation
746
747 
			  ,'1' AS [Sequence]
748
749 
			  ,'' AS [SerialNumber]
750
751 
			  ,@UserID
752
753 
			  ,@now16
754
755 
			  ,@UserID
756
757 
			  ,@now16
758
759 
			  ,'' AS [SORDID]
760
761 
			  ,'' AS [PRODID]
762
763 
			  ,'' AS [INTRID]
764
765 
			  ,'' AS [POID]
766
767 
			  ,'1' AS [StockType]
768
769 
			  ,'' AS [DocIDRef]
770
771 
			  ,'' AS [DocTypeRef]
772
773 
			  ,'' AS [TransTypeRef]
774
775 
			  --,@MAP_MAT1 AS HargaSatuan
776
777 
		SET @IsSuccess = 'Y'
778
779
780
781 
		UPDATE dbo.PMDMAT0001
782
783 
		SET MAP = @NewMAP
784
785 
		WHERE MaterialID = @MaterialID
786
787 
	END
788
789 
	ELSE
790
791 
	BEGIN
792
793 
		DECLARE @MaxSeqMat4Detail VARCHAR(20) = (SELECT MAX(SeqID) FROM @TblMAT4)
794
795
796
797 
		DECLARE @IsMat4ExistDetail VARCHAR(20)
798
799 
		SELECT @IsMat4ExistDetail = COUNT(MaterialID)
800
801 
		FROM @TblMAT4
802
803 
		WHERE (CompanyID = @CompanyID OR @CompanyID = '')
804
805 
			AND (LogisticAreaID = @LogisticArea OR @LogisticArea = '')
806
807 
			AND (Warehouse = @Warehouse OR @Warehouse = '')
808
809 
			AND (Storage = @Storage OR @Storage = '')
810
811 
			AND (Storage = @Bin OR @Bin = '')
812
813 
			AND (StockStatus = @StockStatus)
814
815
816
817 
		DECLARE @PricePurSatuan DECIMAL(22,2) = 0
818
819 
		IF(@DocIDRef <> '')
820
821 
		BEGIN
822
823 
			SELECT @PricePurSatuan = (CONVERT(DECIMAL(22,2),TotAmount) / CONVERT(DECIMAL(22,0),MaterialQuantity))
824
825 
			FROM dbo.PTRPURCHITEM
826
827 
			WHERE DocNo = @DocIDRef
828
829 
				AND Material = @MaterialID
830
831 
		END
832
833 
		ELSE
834
835 
		BEGIN
836
837 
			SELECT @PricePurSatuan = @TotalPrice / @Quantity
838
839 
		END
840
841 
		SET @TotalValuationAllMAT4 = (SELECT SUM(CONVERT(DECIMAL(22,2),Qty)) FROM @TblMAT4) * @MAP_MAT1 --(SELECT SUM(CONVERT(DECIMAL(22,2),TotalValuation)) FROM @TblMAT4)
842
843 
		SET @TotalQtyAllMAT4 = (SELECT SUM(CONVERT(DECIMAL(22,2),Qty)) FROM @TblMAT4)
844
845 
		SET @TotalValuationAll = @TotalValuationAllMAT4 + (@PricePurSatuan * @Quantity) --@TotalPrice
846
847 
		SET @TotalQtyAll = @TotalQtyAllMAT4 + @Quantity
848
849 
		SET @NewMAP = @TotalValuationAll / @TotalQtyAll
850
851 
		SET @NewTotalValuation = @NewMAP * @Quantity
852
853
854
855 
		IF(@IsMat4ExistDetail <= 0)
856
857 
		BEGIN
858
859 
			INSERT INTO PMDMAT0004
860
861 
			SELECT @MaterialID
862
863 
				  ,@MaxSeqMat4Detail + 1
864
865 
				  ,@CompanyID
866
867 
				  ,@LogisticArea
868
869 
				  ,@Warehouse
870
871 
				  ,@Storage
872
873 
				  ,@Bin
874
875 
				  ,@StockStatus--'0001'
876
877 
				  ,''
878
879 
				  ,@Quantity
880
881 
				  ,@NewTotalValuation AS TotalValuation
882
883 
				  ,'1' AS [Sequence]
884
885 
				  ,'' AS [SerialNumber]
886
887 
				  ,@UserID
888
889 
				  ,@now16
890
891 
				  ,@UserID
892
893 
				  ,@now16
894
895 
				  ,'' AS [SORDID]
896
897 
				  ,'' AS [PRODID]
898
899 
				  ,'' AS [INTRID]
900
901 
				  ,'' AS [POID]
902
903 
				  ,'1' AS [StockType]
904
905 
				  ,'' AS [DocIDRef]
906
907 
				  ,'' AS [DocTypeRef]
908
909 
				  ,'' AS [TransTypeRef]
910
911
912
913 
			UPDATE dbo.PMDMAT0001
914
915 
			SET MAP = @NewMAP
916
917 
				,ChangeBy = @UserID
918
919 
				,ChangeDate = @now16
920
921 
			FROM dbo.PMDMAT0001
922
923 
			WHERE MaterialID = @MaterialID
924
925
926
927 
			UPDATE dbo.PMDMAT0004
928
929 
			SET TotalValuation = (CONVERT(DECIMAL(22,2),Qty) * @NewMAP)
930
931 
				,ChangeBy = @UserID
932
933 
				,ChangeDate = @now16
934
935 
			WHERE (MaterialID = @MaterialID)
936
937
938
939 
			SET @IsSuccess = 'Y'
940
941 
		END
942
943 
		ELSE
944
945 
		BEGIN
946
947 
			SET @TotalValuationAllMAT4 = (SELECT SUM(CONVERT(DECIMAL(22,2),Qty)) FROM @TblMAT4) * @MAP_MAT1 --(SELECT SUM(CONVERT(DECIMAL(22,2),TotalValuation)) FROM @TblMAT4)
948
949 
			SET @TotalQtyAllMAT4 = (SELECT SUM(CONVERT(DECIMAL(22,2),Qty)) FROM @TblMAT4)
950
951 
			SET @TotalValuationAll = @TotalValuationAllMAT4 + (@PricePurSatuan * @Quantity) --@TotalPrice
952
953 
			SET @TotalQtyAll = @TotalQtyAllMAT4 + @Quantity
954
955 
			SET @NewMAP = @TotalValuationAll / @TotalQtyAll
956
957
958
959 
			DECLARE @MinSeqMat4 VARCHAR(20)
960
961 
			SELECT @MinSeqMat4 = MIN(SeqID)
962
963 
			FROM @TblMAT4
964
965 
			WHERE (CompanyID = @CompanyID OR @CompanyID = '')
966
967 
				AND (LogisticAreaID = @LogisticArea OR @LogisticArea = '')
968
969 
				AND (Warehouse = @Warehouse OR @Warehouse = '')
970
971 
				AND (Storage = @Storage OR @Storage = '')
972
973 
				AND (Storage = @Bin OR @Bin = '')
974
975 
				AND (StockStatus = @StockStatus)
976
977
978
979 
			DECLARE @NewQty DECIMAL(22,2)
980
981 
			SELECT @NewQty = CONVERT(DECIMAL(22,2),Qty) + @Quantity
982
983 
				,@NewTotalValuation = (CONVERT(DECIMAL(22,2),Qty) + @Quantity) * @NewMAP
984
985 
			FROM @TblMAT4
986
987 
			WHERE (CompanyID = @CompanyID OR @CompanyID = '')
988
989 
				AND (LogisticAreaID = @LogisticArea OR @LogisticArea = '')
990
991 
				AND (Warehouse = @Warehouse OR @Warehouse = '')
992
993 
				AND (Storage = @Storage OR @Storage = '')
994
995 
				AND (Storage = @Bin OR @Bin = '')
996
997 
				AND (SeqID = @MinSeqMat4)
998
999
1000
1001 
			IF(@DocIDRef <> '' AND @DocTypeRef = 'PO' AND @TransTypeRef <> '')
1002
1003 
			BEGIN
1004
1005 
				DECLARE @QtyPurchaseReal DECIMAL(22,1) = 0
1006
1007 
				SELECT @QtyPurchaseReal = PurchaseRealization
1008
1009 
				FROM dbo.PTRPURCHREALIZATION
1010
1011 
				WHERE DocNo = @DocIDRef
1012
1013 
					AND MaterialID = @MaterialID
1014
1015 
					AND ItemStatus = '1'
1016
1017 
				--SELECT @Quantity, @QtyPurchaseReal
1018
1019 
				IF(@Quantity < @QtyPurchaseReal)
1020
1021 
				BEGIN
1022
1023 
					UPDATE dbo.PMDMAT0004
1024
1025 
					SET Qty = @NewQty
1026
1027 
						,TotalValuation = @NewTotalValuation
1028
1029 
						,ChangeBy = @UserID
1030
1031 
						,ChangeDate = @now16
1032
1033 
					WHERE (CompanyID = @CompanyID OR @CompanyID = '')
1034
1035 
						AND (MaterialID = @MaterialID)
1036
1037 
						AND (LogisticAreaID = @LogisticArea OR @LogisticArea = '')
1038
1039 
						AND (Warehouse = @Warehouse OR @Warehouse = '')
1040
1041 
						AND (Storage = @Storage OR @Storage = '')
1042
1043 
						AND (Storage = @Bin OR @Bin = '')
1044
1045 
						AND (SeqID = @MinSeqMat4)
1046
1047
1048
1049 
					UPDATE dbo.PMDMAT0001
1050
1051 
					SET MAP = @NewMAP
1052
1053 
						,ChangeBy = @UserID
1054
1055 
						,ChangeDate = @now16
1056
1057 
					FROM dbo.PMDMAT0001
1058
1059 
					WHERE MaterialID = @MaterialID
1060
1061
1062
1063 
					UPDATE dbo.PMDMAT0004
1064
1065 
					SET TotalValuation = (CONVERT(DECIMAL(22,2),Qty) * @NewMAP)
1066
1067 
						,ChangeBy = @UserID
1068
1069 
						,ChangeDate = @now16
1070
1071 
					WHERE (MaterialID = @MaterialID)
1072
1073 
						AND (SeqID <> @MinSeqMat4)
1074
1075
1076
1077 
					DECLARE @MaxSeqPurReal DECIMAL(22,2) = 0
1078
1079 
					SELECT @MaxSeqPurReal = MAX(DocItemID)
1080
1081 
					FROM dbo.PTRPURCHREALIZATION
1082
1083 
					WHERE DocNo = @DocIDRef
1084
1085
1086
1087 
					INSERT INTO PTRPURCHREALIZATION
1088
1089 
					SELECT @DocIDRef AS [DocNo]
1090
1091 
						  ,@MaxSeqPurReal + 1 AS [DocItemID]
1092
1093 
						  ,[ItemType]
1094
1095 
						  ,[MaterialID]
1096
1097 
						  ,@Quantity
1098
1099 
						  ,[MaterialUnit]
1100
1101 
						  ,@CompanyID
1102
1103 
						  ,@LogisticArea
1104
1105 
						  ,@Warehouse
1106
1107 
						  ,@Storage
1108
1109 
						  ,@Bin
1110
1111 
						  ,'2' AS ItemStatus
1112
1113 
						  ,@Quantity * @PricePurSatuan AS TotAmount
1114
1115 
						  ,[StockType]
1116
1117 
						  ,@UserID
1118
1119 
						  ,@now16
1120
1121 
						  ,@UserID
1122
1123 
						  ,@now16
1124
1125 
					FROM dbo.PTRPURCHREALIZATION
1126
1127 
					WHERE DocNo = @DocIDRef
1128
1129 
						AND MaterialID = @MaterialID
1130
1131 
						AND ItemStatus = '1'
1132
1133
1134
1135 
					UPDATE dbo.PTRPURCHREALIZATION
1136
1137 
					SET PurchaseRealization = (PurchaseRealization - @Quantity)
1138
1139 
						,TotAmount = (PurchaseRealization - @Quantity) * @PricePurSatuan
1140
1141 
						,ChangeBy = @UserID
1142
1143 
						,ChangeDate = @now16
1144
1145 
					WHERE DocNo = @DocIDRef
1146
1147 
						AND MaterialID = @MaterialID
1148
1149 
						AND ItemStatus = '1'
1150
1151
1152
1153 
					UPDATE dbo.PTRPURCHITEM
1154
1155 
					SET ItemStatus = '1'
1156
1157 
						,ChangeBy = @UserID
1158
1159 
						,ChangeDate = @now16
1160
1161 
					WHERE DocNo = @DocIDRef
1162
1163 
						AND Material = @MaterialID
1164
1165
1166
1167 
					SET @IsSuccess = 'Y'
1168
1169 
				END
1170
1171 
				ELSE IF(@Quantity = @QtyPurchaseReal)
1172
1173 
				BEGIN
1174
1175 
					UPDATE dbo.PMDMAT0004
1176
1177 
					SET Qty = @NewQty
1178
1179 
						,TotalValuation = @NewTotalValuation
1180
1181 
						,ChangeBy = @UserID
1182
1183 
						,ChangeDate = @now16
1184
1185 
					WHERE (CompanyID = @CompanyID OR @CompanyID = '')
1186
1187 
						AND (MaterialID = @MaterialID)
1188
1189 
						AND (LogisticAreaID = @LogisticArea OR @LogisticArea = '')
1190
1191 
						AND (Warehouse = @Warehouse OR @Warehouse = '')
1192
1193 
						AND (Storage = @Storage OR @Storage = '')
1194
1195 
						AND (Storage = @Bin OR @Bin = '')
1196
1197 
						AND (SeqID = @MinSeqMat4)
1198
1199
1200
1201 
					UPDATE dbo.PMDMAT0001
1202
1203 
					SET MAP = @NewMAP
1204
1205 
						,ChangeBy = @UserID
1206
1207 
						,ChangeDate = @now16
1208
1209 
					FROM dbo.PMDMAT0001
1210
1211 
					WHERE MaterialID = @MaterialID
1212
1213
1214
1215 
					UPDATE dbo.PMDMAT0004
1216
1217 
					SET TotalValuation = (CONVERT(DECIMAL(22,2),Qty) * @NewMAP)
1218
1219 
						,ChangeBy = @UserID
1220
1221 
						,ChangeDate = @now16
1222
1223 
					WHERE (MaterialID = @MaterialID)
1224
1225 
						AND (SeqID <> @MinSeqMat4)
1226
1227
1228
1229 
					UPDATE dbo.PTRPURCHREALIZATION
1230
1231 
					SET ItemStatus = '2'
1232
1233 
						,Warehouse = @Warehouse
1234
1235 
						,LogisticAreaID = @LogisticArea
1236
1237 
						,Storage = @Storage
1238
1239 
						,Bin = @Bin
1240
1241 
						,ChangeBy = @UserID
1242
1243 
						,ChangeDate = @now16
1244
1245 
					WHERE DocNo = @DocIDRef
1246
1247 
						AND MaterialID = @MaterialID
1248
1249 
						AND ItemStatus = '1'
1250
1251
1252
1253 
					UPDATE dbo.PTRPURCHITEM
1254
1255 
					SET ItemStatus = '2'
1256
1257 
						,ChangeBy = @UserID
1258
1259 
						,ChangeDate = @now16
1260
1261 
					WHERE DocNo = @DocIDRef
1262
1263 
						AND Material = @MaterialID
1264
1265
1266
1267 
					SET @IsSuccess = 'Y'
1268
1269 
				END
1270
1271 
				ELSE
1272
1273 
				BEGIN
1274
1275 
					SET @IsSuccess = 'OVERQTY'
1276
1277 
				END
1278
1279
1280
1281 
				SELECT @CekItemNotDone = COUNT(ItemStatus)
1282
1283 
				FROM dbo.PTRPURCHITEM
1284
1285 
				WHERE DocNo = @DocIDRef AND (ItemStatus = '0' OR ItemStatus = '1')
1286
1287
1288
1289 
				IF(@CekItemNotDone = 0)
1290
1291 
				BEGIN
1292
1293 
					UPDATE dbo.PTRPURCHHEAD
1294
1295 
					SET ProcessStatus = 'C'
1296
1297 
					WHERE DocNo = @DocIDRef
1298
1299 
				END
1300
1301 
			END
1302
1303 
			IF(@DocIDRef <> ''AND @TransTypeRef = 'PRD')
1304
1305 
			BEGIN
1306
1307 
				UPDATE dbo.PMDMAT0004
1308
1309 
				SET Qty = @NewQty
1310
1311 
					,TotalValuation = @NewTotalValuation
1312
1313 
					,ChangeBy = @UserID
1314
1315 
					,ChangeDate = @now16
1316
1317 
				WHERE (CompanyID = @CompanyID OR @CompanyID = '')
1318
1319 
					AND (MaterialID = @MaterialID)
1320
1321 
					AND (LogisticAreaID = @LogisticArea OR @LogisticArea = '')
1322
1323 
					AND (Warehouse = @Warehouse OR @Warehouse = '')
1324
1325 
					AND (Storage = @Storage OR @Storage = '')
1326
1327 
					AND (Storage = @Bin OR @Bin = '')
1328
1329 
					AND (SeqID = @MinSeqMat4)
1330
1331
1332
1333 
				UPDATE dbo.PMDMAT0001
1334
1335 
				SET MAP = @NewMAP
1336
1337 
					,ChangeBy = @UserID
1338
1339 
					,ChangeDate = @now16
1340
1341 
				FROM dbo.PMDMAT0001
1342
1343 
				WHERE MaterialID = @MaterialID
1344
1345
1346
1347 
				UPDATE dbo.PMDMAT0004
1348
1349 
				SET TotalValuation = (CONVERT(DECIMAL(22,2),Qty) * @NewMAP)
1350
1351 
					,ChangeBy = @UserID
1352
1353 
					,ChangeDate = @now16
1354
1355 
				WHERE (MaterialID = @MaterialID)
1356
1357 
					AND (SeqID <> @MinSeqMat4)
1358
1359
1360
1361 
				UPDATE PMDMATWIP
1362
1363 
				SET StockStatus = '0005'
1364
1365 
					,ChangeBy = @UserID
1366
1367 
					,ChangeDate = @now16
1368
1369 
				--SELECT * FROM PMDMATWIP
1370
1371 
				WHERE ProdID = @DocIDRef
1372
1373 
					AND MaterialID = @MaterialID
1374
1375
1376
1377 
				DECLARE @SPK VARCHAR(20)
1378
1379 
				SELECT @SPK = SpkID
1380
1381 
				FROM PMDMATWIP
1382
1383 
				WHERE ProdID = @DocIDRef
1384
1385 
					AND MaterialID = @MaterialID
1386
1387
1388
1389 
				UPDATE PMDMATWIP
1390
1391 
				SET DocStatus = '4'
1392
1393 
					,StockStatus = '0005'
1394
1395 
					,ChangeBy = @UserID
1396
1397 
					,ChangeDate = @now16
1398
1399 
				--SELECT * FROM PMDMATWIP
1400
1401 
				WHERE ProdID = @SPK
1402
1403 
					AND MaterialID = @MaterialID
1404
1405
1406
1407 
				UPDATE PTRPRODUCTIONITEM
1408
1409 
				SET Status = '2'
1410
1411 
					,ChangeBy = @UserID
1412
1413 
					,ChangeDate = @now16
1414
1415 
				--SELECT * FROM PTRPRODUCTIONITEM
1416
1417 
				WHERE DocNo = @DocIDRef
1418
1419 
					AND FlowType = 'RCPT'
1420
1421
1422
1423 
				UPDATE PTRPRODUCTIONHEAD
1424
1425 
				SET DocStatus = '4'
1426
1427 
					,ProcessStatus = 'C'
1428
1429 
					,ChangeBy = @UserID
1430
1431 
					,ChangeDate = @now16
1432
1433 
				--SELECT * FROM PTRPRODUCTIONHEAD
1434
1435 
				WHERE DocNo = @SPK
1436
1437
1438
1439 
				UPDATE PTRPRODUCTIONITEM
1440
1441 
				SET Status = '2'
1442
1443 
					,ChangeBy = @UserID
1444
1445 
					,ChangeDate = @now16
1446
1447 
				--SELECT * FROM PTRPRODUCTIONITEM
1448
1449 
				WHERE DocNo = @SPK
1450
1451 
					AND FlowType = 'RCPT'
1452
1453
1454
1455 
				SET @IsSuccess = 'Y'
1456
1457 
			END
1458
1459 
			ELSE
1460
1461 
			BEGIN
1462
1463 
				UPDATE dbo.PMDMAT0004
1464
1465 
				SET Qty = @NewQty
1466
1467 
					,TotalValuation = @NewTotalValuation
1468
1469 
					,ChangeBy = @UserID
1470
1471 
					,ChangeDate = @now16
1472
1473 
				WHERE (CompanyID = @CompanyID OR @CompanyID = '')
1474
1475 
					AND (MaterialID = @MaterialID)
1476
1477 
					AND (LogisticAreaID = @LogisticArea OR @LogisticArea = '')
1478
1479 
					AND (Warehouse = @Warehouse OR @Warehouse = '')
1480
1481 
					AND (Storage = @Storage OR @Storage = '')
1482
1483 
					AND (Storage = @Bin OR @Bin = '')
1484
1485 
					AND (SeqID = @MinSeqMat4)
1486
1487
1488
1489 
				UPDATE dbo.PMDMAT0001
1490
1491 
				SET MAP = @NewMAP
1492
1493 
					,ChangeBy = @UserID
1494
1495 
					,ChangeDate = @now16
1496
1497 
				FROM dbo.PMDMAT0001
1498
1499 
				WHERE MaterialID = @MaterialID
1500
1501
1502
1503 
				UPDATE dbo.PMDMAT0004
1504
1505 
				SET TotalValuation = (CONVERT(DECIMAL(22,2),Qty) * @NewMAP)
1506
1507 
					,ChangeBy = @UserID
1508
1509 
					,ChangeDate = @now16
1510
1511 
				WHERE (MaterialID = @MaterialID)
1512
1513 
					AND (SeqID <> @MinSeqMat4)
1514
1515
1516
1517 
				SET @IsSuccess = 'Y'
1518
1519 
			END
1520
1521 
		END
1522
1523 
	END
1524
1525 
END
1526
1527
1528
1529 
--IF(@DocType = 'MTPR')
1530
1531 
--BEGIN
1532
1533 
--	IF(@ItemFlow = 'ISSU')
1534
1535 
--	BEGIN
1536
1537 
--		IF(ISNULL(@ExistStock,0) < @Quantity)
1538
1539 
--		BEGIN
1540
1541 
--			SET @IsSuccess = 'OVERQTY'
1542
1543 
--		END
1544
1545 
--		ELSE
1546
1547 
--		BEGIN
1548
1549 
--			SET @NewStock = @ExistStock - @Quantity
1550
1551 
--			SET @NewEachMAP = @ExistTotalMAP / @ExistStock  ---- menggunakan MAP sebelumnya
1552
1553 
--			SET @NewTotalMAP = @NewEachMAP * @NewStock
1554
1555 
--			--SELECT @NewStock, @NewEachMAP, @NewTotalMAP
1556
1557 
--			IF (@IsExist > 0)
1558
1559 
--			BEGIN
1560
1561 
--				SET @IsSuccess = 'Y'
1562
1563 
--				IF(@DocIDRef <> '' AND @DocTypeRef <> '' AND @TransTypeRef <> '')
1564
1565 
--				BEGIN
1566
1567 
--					DELETE FROM PMDMAT0004
1568
1569 
--					--SELECT * FROM PMDMAT0004
1570
1571 
--					WHERE MaterialID = @MaterialID
1572
1573 
--						AND DocIDRef = @DocIDRef
1574
1575 
--						AND DocTypeRef = @DocTypeRef
1576
1577 
--						AND TransTypeRef = @TransTypeRef
1578
1579 
--				END
1580
1581 
--				ELSE
1582
1583 
--				BEGIN
1584
1585 
--					UPDATE dbo.PMDMAT0004
1586
1587 
--					SET Qty = CONVERT(VARCHAR(4), ISNULL(@NewStock,0))
1588
1589 
--						,TotalValuation = ISNULL(@NewTotalMAP,0)
1590
1591 
--						,ChangeBy = @UserID
1592
1593 
--						,ChangeDate = @now16
1594
1595 
--					WHERE MaterialID = @MaterialID AND StockStatus = '0001' AND (CompanyID = @CompanyID OR @CompanyID = '')
1596
1597 
--						AND (Warehouse = @Warehouse OR @Warehouse = '') AND (Storage = @Storage OR @Storage = '') AND (Bin = @Bin OR @Bin = '')
1598
1599 
--						AND SeqID = @MinSeqMatExist
1600
1601 
--				END
1602
1603 
--			END
1604
1605 
--		END
1606
1607 
--	END
1608
1609 
--	ELSE IF(@ItemFlow = 'RCPT')
1610
1611 
--	BEGIN
1612
1613 
--		SET @NewStock = @ExistStock + @Quantity
1614
1615 
--		SET @NewEachMAP = @TotalPrice / @Quantity
1616
1617 
--		SET @NewTotalMAP = @NewEachMAP * @NewStock
1618
1619
1620
1621 
--		IF (@IsExist > 0)
1622
1623 
--		BEGIN
1624
1625
1626
1627 
--			SET @IsSuccess = 'Y'
1628
1629 
--			UPDATE dbo.PMDMAT0004
1630
1631 
--			SET Qty = @NewStock
1632
1633 
--				,TotalValuation = @NewTotalMAP
1634
1635 
--				,ChangeBy = @UserID
1636
1637 
--				,ChangeDate = @now16
1638
1639 
--			WHERE MaterialID = @MaterialID AND StockStatus = '0001' AND (CompanyID = @CompanyID OR @CompanyID = '')
1640
1641 
--				AND (Warehouse = @Warehouse OR @Warehouse = '') AND (Storage = @Storage OR @Storage = '') AND (Bin = @Bin OR @Bin = '')
1642
1643 
--				AND SeqID = @MinSeqMatExist
1644
1645 
--		END
1646
1647 
--		ELSE IF (@IsExist = 0)
1648
1649 
--		BEGIN
1650
1651 
--			SET @IsSuccess = 'Y'
1652
1653 
--			INSERT INTO dbo.PMDMAT0004
1654
1655 
--			SELECT DISTINCT @MaterialID
1656
1657 
--							,@MaxSeqMatExist + 1
1658
1659 
--							,@CompanyID
1660
1661 
--							,@LogisticArea
1662
1663 
--							,@Warehouse
1664
1665 
--							,@Storage
1666
1667 
--							,@Bin
1668
1669 
--							,'0001'
1670
1671 
--							,'1' AS StockType
1672
1673 
--							,'122324'
1674
1675 
--							,CONVERT(VARCHAR(4),@Quantity)
1676
1677 
--							,CONVERT(DECIMAL(18,2),@TotalPrice)
1678
1679 
--							,1
1680
1681 
--							,'113124'
1682
1683 
--							,@UserID AS CreateBy
1684
1685 
--							,@now16 AS CreateDate
1686
1687 
--							,@UserID AS ChangeBy
1688
1689 
--							,@now16 AS ChangeDate
1690
1691 
--							,'' AS SORDID
1692
1693 
--							,'' AS PRODID
1694
1695 
--							,'' AS INTRID
1696
1697 
--							,'' AS POID
1698
1699 
--							,'' AS DocIDREf
1700
1701 
--							,'' AS DocTypeRef
1702
1703 
--							,'' AS TransTypeRef
1704
1705 
--		END
1706
1707 
--	END
1708
1709 
--END
1710
1711
1712
1713 
SELECT @IsSuccess AS IsSuccess
1714
1715
1716
1717 
DELETE FROM @TblMAT4
1718
1719
1720
1721
1722
1723
1724
1725
1726
1727
1728
1729
1730
1731
(1-1/3)