--  Description  : اصلاح سند مربوط به فاکتورهای سالن SAC-819

    DECLARE @QUERY          NVARCHAR(MAX)= '' ,
			@PARAM          NVARCHAR(MAX),  
			@SchemaName     NVARCHAR(200)
    
    SET @SchemaName = (SELECT FNY_Name FROM [Prince].[Common].[FinancialYear] WHERE ISNULL(FNY_IsDeleted,0)= 0 AND (ISNULL(FNY_CloseDate_Int ,0) = 0))
    

	-- ****************** Get Factor ID
	SET @Query = ''
	SET @QUERY = N'
	DECLARE @BadFactor TABLE (POP_ID INT,POP_HandledID INT,CPN_ACN_ID INT,POP_Amount MONEY,POP_UserID INT,SFC_Date NVARCHAR(20),SFC_Time NVARCHAR(20))

    INSERT INTO @BadFactor
    SELECT POP_ID,POP_HandledID,CPN_ACN_ID = CASE WHEN (POP_From IS NULL ) THEN POP_TO ELSE POP_From END ,POP_Amount,POP_UserID,
           SFC_Date,SFC_Time
	  FROM [Prince].['+@SchemaName+'].SaffronFactor
	  JOIN [Prince].['+@SchemaName+'].PaymentOperation ON (POP_TBL_Handled = 331) AND (POP_HandledID = SFC_ID)
	 WHERE (SFC_Style = 1) AND (SFC_Status NOT IN (2,5,6)) and 
			(SFC_CPN_ID IS NULL) AND 
			(POP_status <> 3) AND 
			(POP_Type = 10) AND 
			((POP_From IS NULL ) OR (POP_To IS NULL)) AND 
			(SFC_IsDraft = 0 ) AND 
			(SFC_Total = POP_Amount)

       IF (SELECT count(1) FROM @BadFactor) = 0
          RETURN
          
       DECLARE  
			@BAC_ID			INT,
			@BAC_Name		NVARCHAR(2000),
			@ACN_ID			INT,
			@POP_ID			INT,
			@POP_HandledID	INT,
			@CPN_ACN_ID		INT,
			@POP_Amount		MONEY,
			@POP_UserID		INT,
			@SFC_Date		NVARCHAR(20),
			@SFC_Time		NVARCHAR(20),
			@OutPOP_ID		INT,
			@DO_OPR			INT   
          
	SET @BAC_ID = ISNULL((SELECT MIN(BAC_ID) FROM [Prince].[Common].[BankAccount] WHERE BAC_IsDisabled = 0 ),0)
	IF (@BAC_ID = 0)
	BEGIN
	   EXEC [Prince].[Common].BAC_Insert;1 69,NULL,'''','''','''',''123456'','''',1,0,0,'''',0,1,1,-1,1,'''',@BAC_ID output
	END

	SELECT @ACN_ID = ACN_ID ,@BAC_Name = ACN_Name  
	  FROM [Prince].[Common].[Account]  
	 WHERE (ACN_TBL_Holder = 18) AND (ACN_HolderID = @BAC_ID)
	 
		DECLARE POP CURSOR for
			   SELECT * FROM @BadFactor
		OPEN POP
		FETCH NEXT FROM POP INTO @POP_ID,@POP_HandledID,@CPN_ACN_ID,@POP_Amount,@POP_UserID,@SFC_Date,@SFC_Time 
		WHILE @@FETCH_STATUS =0
		BEGIN     

			  UPDATE [Prince].['+@SchemaName+'].PaymentOperation
				 SET POP_status = 3
			   WHERE POP_ID = @POP_ID
	           
			  EXEC [Prince].['+@SchemaName+'].[GeneralTransferCredit];1 67,7,@POP_Amount,@SFC_Date,@SFC_Time,@ACN_ID,'''',@CPN_ACN_ID,'''',331,@POP_HandledID,NULL,'''','''','''','''',@BAC_Name,'''','''','''','''','''',@POP_UserID,@OutPOP_ID output,@DO_OPR output
	            
		      
		   FETCH NEXT FROM POP INTO @POP_ID,@POP_HandledID,@CPN_ACN_ID,@POP_Amount,@POP_UserID,@SFC_Date,@SFC_Time
		END
		CLOSE POP
		DEALLOCATE POP	          
          
 ' 
	--PRINT @QUERY
	EXEC [Prince].sys.sp_executesql @QUERY
