Project

General

Profile

Download (50.7 KB)

Feature #3917 ยป PFILOUPDATEMDSTOCKINFO_202509261540.sql

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