Unreconciled Transfers

Description

Compares transfers out against transfers in to produce a reconciliation report.

Formula

SET CREATENONMISSINGBLK ON;

FIX ([Scenario], [Version], [Year], @REMOVE(@LEVMBRS("Employee",0), @LIST(@CHILDREN("New Employees"), "Department General", "No Employee")), @LEVMBRS("Period", 0), "No Entity", "Local", "HSP_InputValue")
	"TransferOutCount" = #MISSING;
	"TransferInCount" = #MISSING;		
ENDFIX
FIX ([Scenario], [Version], [Year], @REMOVE(@LEVMBRS("Employee",0), @LIST(@CHILDREN("New Employees"), "Department General", "No Employee")), @LEVMBRS("Period", 0), "Local", "HSP_InputValue")
	FIX(@REMOVE(@LEVMBRS("Entity",0), @LIST("No Entity")))
	"Action" (
		IF  ("Action" == 7)
			"No Entity"->"TransferOutCount" = "No Entity"->"TransferOutCount" + 1;
		ELSEIF ("Action" == 8)
			"No Entity"->"TransferInCount" = "No Entity"->"TransferInCount" + 1;
		ENDIF	
	);
	ENDFIX
ENDFIX
FIX ([Scenario], [Version], [Year], @REMOVE(@LEVMBRS("Employee",0), @LIST(@CHILDREN("New Employees"), "Department General", "No Employee")), @LEVMBRS("Period", 0), "No Entity", "Local", "HSP_InputValue")
"Unreconciled Transfers"(
	IF ("TransferInCount" == "TransferOutCount")
		IF ("TransferInCount" == 1  OR "TransferInCount" == #MISSING)
			#MISSING;
		ELSE
			0;
		ENDIF
	ELSEIF ("TransferInCount" > "TransferOutCount")
		1;
	ELSE
		-1;
	ENDIF
);
ENDFIX