Page 1 of 3123

Archive for the ‘Accounts Receivables’ Category

Support Debugging Tool Customization #11 – Restricting Salesperson ID for Existing Customers

Recently there was a support request in the forum,

http://social.microsoft.com/Forums/en-US/partnerdynamicsgp/thread/139b760a-5c63-4b16-b480-79f0a569075c

where the requirement was as below.

We want to modify the Customer Maintenance window to make the Salesperson ID field uneditable for existing records only.  I went into Modifier and made this field uneditable but that makes it uneditable each time the window opens.  We want the field to be available when creating a new customer – we just don’t want users to be able to change that field for existing customers.

In this article, I am going to post the details of how this customization can be achieved using Support Debugging Tool.

Version Information

Dynamics GP : 11.0.1914 (SP2)

Support Debugging Tool : 11.00.0016

Screenshots of the Configuration

Trigger #1 – Resource Tab

image

This is a trigger on the Display Existing Record Change – After Original on the Customer Maintenance window. This trigger will check if both the Customer Number and the Salesperson ID fields are populated with data, and if these fields are populated with values, we will lock the Salesperson ID field and disable the corresponding lookup button for the Salesperson ID field.

Trigger #1 – Customization Script

in string IN_OldValue;
in string IN_NewValue;
out boolean OUT_Condition;
 
OUT_Condition = false;
 
if isopen(form RM_Customer_Maintenance) then
	if not empty('Customer Number' of window 'RM_Customer_Maintenance' of form 'RM_Customer_Maintenance') and 
		not empty('Salesperson ID' of window 'RM_Customer_Maintenance' of form 'RM_Customer_Maintenance') then
		lock field 'Salesperson ID' of window 'RM_Customer_Maintenance' of form 'RM_Customer_Maintenance';
		disable field 'Lookup Button 6' of window 'RM_Customer_Maintenance' of form 'RM_Customer_Maintenance';
		OUT_Condition = true;
	else
		unlock field 'Salesperson ID' of window 'RM_Customer_Maintenance' of form 'RM_Customer_Maintenance';
		enable field 'Lookup Button 6' of window 'RM_Customer_Maintenance' of form 'RM_Customer_Maintenance';
		OUT_Condition = true;
	end if;
end if;

Trigger #2 – Resource Tab

image

This is a trigger on the Display Existing Record Change – After Original on the Customer Address Maintenance window. This trigger will check if the Customer Number, Address Code and the Salesperson ID fields are populated with data, and if these fields are populated with values, we will lock the Salesperson ID field and disable the corresponding lookup button for the Salesperson ID field.

Trigger #2 – Customization Script

in string IN_OldValue;
in string IN_NewValue;
out boolean OUT_Condition;
 
OUT_Condition = false;
 
if isopen(form RM_Customer_Address) then
	if not empty('Customer Number' of window 'RM_Customer_Address' of form 'RM_Customer_Address') and 
		not empty('Address Code' of window 'RM_Customer_Address' of form 'RM_Customer_Address') and
		not empty('Salesperson ID' of window 'RM_Customer_Address' of form 'RM_Customer_Address') then
		lock field 'Salesperson ID' of window 'RM_Customer_Address' of form 'RM_Customer_Address';
		disable field 'Lookup Button 8' of window 'RM_Customer_Address' of form 'RM_Customer_Address';
		OUT_Condition = true;
	else
		unlock field 'Salesperson ID' of window 'RM_Customer_Address' of form 'RM_Customer_Address';
		enable field 'Lookup Button 8' of window 'RM_Customer_Address' of form 'RM_Customer_Address';
		OUT_Condition = true;
	end if;
end if;

Configuration File Location

You can download the configuration for this requirement here.

Reference

Take a look at the article below which summarizes the usage of Support Debugging Tool with some real life examples. Great compilation by David! http://blogs.msdn.com/b/developingfordynamicsgp/archive/2011/08/05/using-the-support-debugging-tool-with-real-life-examples.aspx

You can get more details about the Support Debugging Tool at http://aka.ms/SDT

Hope this helps the community…

Until next post!

March 13, 2012 · veeyeskay · One Comment
Tags: , , , , , ,  · Posted in: Accounts Receivables, Customizations, Dynamics, Great Plains, Support Debugging Tool Total Views: 2,208

Receivables Inquiry Window – YTD Sales Amount Logic

A client of ours, sent out an email today saying that the YTD Sales Amount that they see on the Receivables Yearly Summary Inquiry window does not match with the Receivables Transactions smartlist’s Sales Amount pulled for the specific customer for the specific year.

On analyzing the issue, I noticed that they have only pulled in Invoices in the Receivables Smartlist and taken a total of that and checked if it matches with the YTD sales amount. This ideally will not match since the formula for the YTD Sales Amount on the Receivables Yearly Summary Inquiry window is as follows.

Sales Amount = Sales Invoice Amount + Debit Memo Amount + Service/Repairs Amount – Credit Memo Amount

Hence when comparing the sales amounts in the Receivables Transaction Smartlist with the Summary Inquiry window in Receivables, we need to make sure to include the following document types in the smartlist.

  • Sales/Invoice
  • Debit Memo
  • Service/Repairs
  • Credit Memo

Note that when you view the transaction details in smartlist, the amount for Credit Notes also show up as positive. We need to subtract this amount from the other amounts, instead of taking a total of all the documents as such.

image 

When these transactions are totaled up based on the formula mentioned above, the Sales Amount comes to $4,192.25 and this is what shows up in the Summary Inquiry as seen below.

image

Note: I have used eOne Smartview for my transaction queries instead of Smartlist.

Hope this helps the community…

Until next post!

November 18, 2011 · veeyeskay · 3 Comments
Tags: , ,  · Posted in: Accounts Receivables, Dynamics, Great Plains Total Views: 2,323

Support Debugging Tool Customization #10 – Credit Limit Check Skip in Receivables Transaction Entry (using SQL Execute in SDT)

Recently there was a support request in the forum,

http://social.microsoft.com/Forums/en-US/partnerdynamicsgp/thread/a3237a4f-fa4c-4603-8048-94cc0b2af467

where the requirement was as below.

Is there anything in Accounts Receivable that will allow a certain type of document (whatever that may be) bypass the Credit Limit Check? My client has a customer with a Credit Limit of $5000. They are additionally seeking a donation to a Charity from this customer for $10,000. They do not want the $10,000 invoice to be included in the Credit Limit Calculation. I know they could temporarily adjust the Credit Limit or they could setup a second Customer but is there any other way to get around the credit Limit in this case?”.

As a follow up to the earlier article titled Support Debugging Tool Customization #9 – Credit Limit Check Skip in Receivables Transaction Entry (using Native Dexterity Save Logic), I am going to post the logic of using SQL Execute method to handle the same requirement, in this article.

As you would be aware of, SQL Execute is an interface/API available inside Support Debugging Tool, where we can execute SQL scripts without the need for a database tool. With the help of David Musgrave, I have explored this a little bit and explained how to create parameterized SQL procedures in Support Debugging Tool and execute them dynamically in runtime by passing various values to the parameters.

Version Information

Dynamics GP : 11.0.1752 (SP2)

Support Debugging Tool : 11.00.0015

Screenshots of the Configuration

SQL Script ID #1 – Screenshot

image

This script is to update the credit limit for the customer as UNLIMITED. The SQL query for this ID is defined below.

--DECLARE @CUSTNMBR VARCHAR(15)
UPDATE  RM00101
SET     CRLMTTYP = 1 ,
        CRLMTAMT = 0 ,
        CRLMTPER = 0 ,
        CRLMTPAM = 0
WHERE   CUSTNMBR = @CUSTNMBR

SQL Script ID #2 – Screenshot

image

This script is to update the credit limit for the customer as UNLIMITED. The SQL script for this ID is given below.

--DECLARE @CUSTNMBR VARCHAR(15)
--DECLARE @CRLMTTYP INTEGER
--DECLARE @CRLMTAMT NUMERIC(19, 5)
--DECLARE @CRLMTPER INTEGER
--DECLARE @CRLMTPAM NUMERIC(19, 5)
UPDATE  RM00101
SET     CRLMTTYP = @CRLMTTYP ,
        CRLMTAMT = @CRLMTAMT ,
        CRLMTPER = @CRLMTPER ,
        CRLMTPAM = @CRLMTPAM
WHERE   CUSTNMBR = @CUSTNMBR

Note that I have commented the declaration lines in both the scripts above. This is key to note in this instance, since as I had said earlier, we would be calling this procedure with parameters passed at run time. Once these scripts are created, we can call these SQL script ID’s from other scripts in SDT. This will be explained in the subsequent sections.

Trigger #1 – Resource Tab

This is a trigger on the Customer Number Change – After Original on the Receivables Transaction Entry window. This is to capture the customer number and the document type into a parameter. Note the use of “MBS_Param_Set” to set the parameter values. You can use the Helper button to select the coding syntax for setting parameter values.

image_thumb[13]

Trigger #1 – Customization Script

in string IN_OldValue;
in string IN_NewValue;
out boolean OUT_Condition;
local string MBS_Parameter;
OUT_Condition = false;
if isopen(form RM_Sales_Entry) then
	if 'Document Type' of window 'RM_Sales_Entry' of form 'RM_Sales_Entry' = 2 then
		if not empty('Customer Number' of window RM_Sales_Entry of form RM_Sales_Entry) then
			call with name "MBS_Param_Set" in dictionary 5261,
				"CustNmbr",
				'Customer Number' of window RM_Sales_Entry of form RM_Sales_Entry;
			call with name "MBS_Param_Set" in dictionary 5261,
				"RMDocTypeAll",
				str('Document Type' of window 'RM_Sales_Entry' of form 'RM_Sales_Entry');
		end if;
	end if;
end if;

Trigger #2 – Resource Tab

image

This is a trigger on the Form Level Procedure for Credit Limit Check – Before Original. This is to capture the existing credit limit information into parameters in SDT and update the credit limit as UNLIMITED for the specific customer (which was retrieved from the parameter value captured earlier). Note the use of “MBS_Param_Get to retrieve the parameter values. You can use the Helper button to select the coding syntax for retrieving parameter values.

Also, note that I have helper functions for getting values from table fields using the Helper button. I have used this helper function to get the Credit Limit Type, Credit Limit Amount, Credit Limit Period and Credit Limit Period Amount and store them in parameters.

image

And then I call the SQL script ID which I have created above to update the credit limit for the customer as UNLIMITED. For this, I first call the procedure to load the SQL Script ID (i.e.) “MBS_Script_Load_SQL”, using the Helper functions. This procedure basically retrieves the SQL script for the ID into a text field. We then append append the parameter declarations and the values passed to these parameters before the query which is retrieved in the text variable as shown below. Then the final SQL script is executed using the “MBS_SQL_Check_Exists” procedure as shown below.

image

Trigger #2 – Customization Script

out 	boolean 	OUT_Condition;
local 	text 		MBS_Text_Field;
local 	integer 	MBS_Status;
 
local 	string		ls_CustNmbr;
local	string		ls_DocType;
local	integer		li_DocType;
local	text		lt_ParamQuery;
local 	string 		ls_Result;
local 	integer 	li_Pos;
local 	integer 	li_CredLimitType;
local 	currency 	lc_CredLimitAmt;
local 	integer 	li_CredLimitPeriod;
local 	currency 	lc_CredLimitPeriodAmt;
 
OUT_Condition = false;
 
if true then { Insert Condition Here }
	call with name "MBS_Param_Get" in dictionary 5261, "CustNmbr", ls_CustNmbr;
	call with name "MBS_Param_Get" in dictionary 5261, "RMDocTypeAll", ls_DocType;
	set li_DocType to integer(ls_DocType);
 
	if li_DocType = 2 and not empty(ls_CustNmbr) then
 
		{To get the current Credit Limit Type}
		call with name "MBS_Get_Table_Value1" in dictionary 5261,
			0 {Dict},
			"RM_Customer_MSTR" {Table},
			"'Credit Limit Type'" {Field},
			li_CredLimitType, MBS_Status, 1 {Index},
			"'Customer Number'", ls_CustNmbr;
		call with name "MBS_Param_Set" in dictionary 5261, "CredLimitType", str(li_CredLimitType);
 
		{To get the current Credit Limit Amount}
		call with name "MBS_Get_Table_Value1" in dictionary 5261,
			0 {Dict},
			"RM_Customer_MSTR" {Table},
			"'Credit Limit Amount'" {Field},
			lc_CredLimitAmt, MBS_Status, 1 {Index},
			"'Customer Number'", ls_CustNmbr;
		call with name "MBS_Param_Set" in dictionary 5261, "CredLimitAmt", str(lc_CredLimitAmt);
 
		{To get the current Credit Limit Period}
		call with name "MBS_Get_Table_Value1" in dictionary 5261,
			0 {Dict},
			"RM_Customer_MSTR" {Table},
			"'Credit Limit Period'" {Field},
			li_CredLimitPeriod, MBS_Status, 1 {Index},
			"'Customer Number'", ls_CustNmbr;
		call with name "MBS_Param_Set" in dictionary 5261, "CredLimitPeriod", str(li_CredLimitPeriod);
 
		{To get the current Credit Limit Period Amount}
		call with name "MBS_Get_Table_Value1" in dictionary 5261,
			0 {Dict},
			"RM_Customer_MSTR" {Table},
			"'Credit Limit Period Amount'" {Field},
			lc_CredLimitPeriodAmt, MBS_Status, 1 {Index},
			"'Customer Number'", ls_CustNmbr;
		call with name "MBS_Param_Set" in dictionary 5261, "CredLimitPeriodAmt", str(lc_CredLimitPeriodAmt);
 
		if li_CredLimitType <> 1 then
			clear lt_ParamQuery;
			{Load the SQL Script ID into a text variable}
			call with name "MBS_Script_Load_SQL" in dictionary 5261,
				"CREDLIMITCLEAR", MBS_Text_Field;
			{Set the variable declaration and values to the variables at run time}
			set lt_ParamQuery to lt_ParamQuery + "DECLARE @CUSTNMBR VARCHAR(15);" + char(13);
			set lt_ParamQuery to lt_ParamQuery + "SET @CUSTNMBR = '" + ls_CustNmbr + "';" + char(13);
			{Append the variable declaration string before the actual SQL script fetched from the SQL Script ID}
			set MBS_Text_Field to lt_ParamQuery + MBS_Text_Field;
			{Execute the SQL Script}
			call with name "MBS_SQL_Check_Exists" in dictionary 5261,
				MBS_Text_Field, false {Return Data}, false {Show Names}, MBS_Status;
 
		end if;
	end if;
	OUT_Condition = true;
end if;

Trigger #3 – Resource Tab

This is a trigger on the Form Level Procedure for Credit Limit Check – After Original. This is to reset the credit limit information back to the original values for the selected customer. Note the use of MBS_Param_DelAll procedure to delete any parameters which have been used in the process. You can use the Helper button to select the coding syntax for deleting parameter values.

image

Trigger #3 – Customization Script

OUT BOOLEAN OUT_Condition;
LOCAL text MBS_Text_Field;
LOCAL INTEGER MBS_Status;
 
LOCAL 	string		ls_CustNmbr;
LOCAL	string		ls_DocType;
LOCAL	string		ls_CredLimitType;
LOCAL	string		ls_CredLimitAmt;
LOCAL	string		ls_CredLimitPeriod;
LOCAL	string		ls_CredLimitPeriodAmt;
LOCAL	INTEGER		li_DocType;
LOCAL	INTEGER		li_CredLimitType;
LOCAL	currency	lc_CredLimitAmt;
LOCAL	INTEGER		li_CredLimitPeriod;
LOCAL	currency	lc_CredLimitPeriodAmt;
LOCAL	text		lt_ParamQuery;
 
OUT_Condition = FALSE;
 
IF TRUE THEN { INSERT Condition Here }
	{GET the parameter VALUES}
	CALL WITH name "MBS_Param_Get" IN dictionary 5261, "CustNmbr", ls_CustNmbr;
	CALL WITH name "MBS_Param_Get" IN dictionary 5261, "RMDocTypeAll", ls_DocType;
	CALL WITH name "MBS_Param_Get" IN dictionary 5261, "CredLimitType", ls_CredLimitType;
	CALL WITH name "MBS_Param_Get" IN dictionary 5261, "CredLimitAmt", ls_CredLimitAmt;
	CALL WITH name "MBS_Param_Get" IN dictionary 5261, "CredLimitPeriod", ls_CredLimitPeriod;
	CALL WITH name "MBS_Param_Get" IN dictionary 5261, "CredLimitPeriodAmt", ls_CredLimitPeriodAmt;
 
	SET li_DocType TO INTEGER(ls_DocType);
	SET li_CredLimitType TO INTEGER(ls_CredLimitType);
	SET lc_CredLimitAmt TO VALUE(ls_CredLimitAmt);
	SET li_CredLimitPeriod TO INTEGER(ls_CredLimitPeriod);
	SET lc_CredLimitPeriodAmt TO VALUE(ls_CredLimitPeriodAmt);
 
	IF li_DocType = 2 AND NOT empty(ls_CustNmbr) THEN
		clear lt_ParamQuery;
		{LOAD the SQL Script ID}
		CALL WITH name "MBS_Script_Load_SQL" IN dictionary 5261,
			"CREDLIMITRESET", MBS_Text_Field;
		{Setting the dynamic parameter query string}
		SET lt_ParamQuery TO lt_ParamQuery + "DECLARE @CUSTNMBR VARCHAR(15);" + CHAR(13);
		SET lt_ParamQuery TO lt_ParamQuery + "DECLARE @CRLMTTYP INTEGER;" + CHAR(13);
		SET lt_ParamQuery TO lt_ParamQuery + "DECLARE @CRLMTAMT NUMERIC(19, 5);" + CHAR(13);
		SET lt_ParamQuery TO lt_ParamQuery + "DECLARE @CRLMTPER INTEGER;" + CHAR(13);
		SET lt_ParamQuery TO lt_ParamQuery + "DECLARE @CRLMTPAM NUMERIC(19, 5);" + CHAR(13);
		SET lt_ParamQuery TO lt_ParamQuery + "SET @CUSTNMBR = '" + ls_CustNmbr + "';" + CHAR(13);
		SET lt_ParamQuery TO lt_ParamQuery + "SET @CRLMTTYP = " + str(li_CredLimitType) + ";" + CHAR(13);
		SET lt_ParamQuery TO lt_ParamQuery + "SET @CRLMTAMT = " + str(lc_CredLimitAmt) + ";" + CHAR(13);
		SET lt_ParamQuery TO lt_ParamQuery + "SET @CRLMTPER = " + str(li_CredLimitPeriod) + ";" + CHAR(13);
		SET lt_ParamQuery TO lt_ParamQuery + "SET @CRLMTPAM = " + str(lc_CredLimitPeriodAmt) + ";" + CHAR(13);
		{Appending the dynamic parameter query string TO the actual query}
		SET MBS_Text_Field TO lt_ParamQuery + MBS_Text_Field;
		{EXECUTE the SQL script}
		CALL WITH name "MBS_SQL_Check_Exists" IN dictionary 5261,
			MBS_Text_Field, FALSE {RETURN DATA}, FALSE {SHOW Names}, MBS_Status;
	END IF;
	CALL WITH name "MBS_Param_DelAll" IN dictionary 5261;
	OUT_Condition = TRUE;
END IF;

Note: The earlier post was done using Dexterity code logic and this version was done using SQL Execute. It does not make a difference if either option is chosen. We have just provided an insight into this functionality and the use of SQL Execute in Support Debugging Tool. This type is generally very useful when you want to update tables which are not GP related, since the other options will not be viable to update non GP tables in SQL. As said earlier, the method to be used will differ on a case to case basis, and the appropriate choice can be used as and when needed. Thanks again to David, for guiding me through each step in utilizing Support Debugging Tool to help the community.

Configuration File Location

You can download the configuration for this requirement here.

Reference

Take a look at the article below which summarizes the usage of Support Debugging Tool with some real life examples. Great compilation by David! http://blogs.msdn.com/b/developingfordynamicsgp/archive/2011/08/05/using-the-support-debugging-tool-with-real-life-examples.aspx

Hope this helps the community…

Until next post!

November 4, 2011 · veeyeskay · 5 Comments
Tags: , , , , , , , , , , ,  · Posted in: Accounts Receivables, Customizations, Dynamics, Great Plains, Support Debugging Tool Total Views: 3,357

Indicator to specify single page or multi page customer statements

Recently, I was assisting a forum user, who had requested an option to provide a visual indicator in the footer of the customer statement report, which indicates if the statement has a single page or multiple pages.

After a little thought I had come up with this idea and replied back to the forum user who found this helpful. So I decided to share it with the community, so that it benefits all who meet up such a requirement anytime.

Steps to achieve the requirement:

1. Open the RM Statement on Blank Paper in Report Writer mode.

2. Add the Document Number field to the footer and make it invisible and set the Display Type of the Document Number in the footer as shown below (to show count value).

img1

Then create a calculated field as shown below and display it on the footer. The F2_FREQ Document Number field can be pulled from the list of Report Fields (by selecting Report Fields in the Resources drop down.

img2

By standard the customer statement on blank paper prints only 26 records in the body section on a single page. See the section properties for the additional footer called "footer" and it will indicate that "Records per Report Body = 26". :) We can make use of this logic to achieve your requirement and I have tested this and it works fine (whether you print statement for a single customer (or) multiple customers at once).

Hope this helps the community…

Until next post!

September 17, 2011 · veeyeskay · 2 Comments
Tags: , , , , , , , , ,  · Posted in: Accounts Receivables, Dynamics, Great Plains, Reporting Total Views: 1,327

Support Debugging Tool Customization #6 – Removing Voided Documents from Customer Statement

Based on a recent support request, I have decided to post yet another article which utilizes the Support Debugging Tool to achieve the above requirement, where in the voided documents do not appear in the Customer Statement.

For this requirement, I have customized the RM Statement On Blank Paper version of the Customer Statement. The other versions can also be modified accordingly.

Version Information

Dynamics GP : 11.0.1752 (SP2)

Support Debugging Tool : 11.00.0015

Screenshots of the Configuration

Create a Runtime Execute script block with the code given below with the script ID as STMTVOIDEXCL.

image

local string MBS_TableLineString;
local string MBS_Number;
local integer MBS_Type;
local currency MBS_SequenceOne;
local currency MBS_SequenceTwo;
local integer MBS_Control;
local string MBS_String;
 
call with name "MBS_Param_Get" in dictionary 5261, "Number", MBS_Number;
call with name "MBS_Param_Get" in dictionary 5261, "Type", MBS_String;
MBS_Type = integer(value(MBS_String));
call with name "MBS_Param_Get" in dictionary 5261, "SequenceOne", MBS_String;
MBS_SequenceOne = currency(value(MBS_String));
call with name "MBS_Param_Get" in dictionary 5261, "SequenceTwo", MBS_String;
MBS_SequenceTwo = currency(value(MBS_String));
call with name "MBS_Param_Get" in dictionary 5261, "Control", MBS_String;
MBS_Control = integer(value(MBS_String));
MBS_TableLineString = "";
 
{ Add your code below here }
release table RM_OPEN;
clear table RM_OPEN;
set 'RM Document Type-All' of table RM_OPEN to MBS_Type;
set 'Document Number' of table RM_OPEN to MBS_Number;
get table RM_OPEN by number 4;
if err() = OKAY then
	if 'Void Status' of table RM_OPEN = 1 then
		set MBS_TableLineString to "";
	else
		set MBS_TableLineString to "V";
	end if;
else
	set MBS_TableLineString to "V";
end if;
{ Add your code above here }
 
call with name "MBS_Param_Set" in dictionary 5261, "TableLineString", MBS_TableLineString;

Then modify the RM Statement On Blank Paper report and create a calculated field, as explained below, which calls the custom Report Writer function for which we have created the script block above.

Calculated Field Name : Valid

Result Type : String

Expression Type : Calculated

image

The formula for this calculated field is as shown below.

FUNCTION_SCRIPT(  rw_TableLineString  5261  “STMTVOIDEXCL”  RM_Statements_TRX_TEMP.Document Number  RM_Statements_TRX_TEMP.RM Document Type-All  0.00000  0.00000  1  )

Now, place the field on the body section of the report layout and make the field invisible.

image

And then go to the Section Properties and define the following setting for the body section, to hide the body conditions whenever the calculated field Valid is empty. Our custom runtime script which we have written in Support Debugging Tool will set this field value to V if it’s a valid record and will make this field blank when it is a voided record, thus hiding the voided records from the report.

image

Configuration File Download

You can download the configuration xml file here.

Reference

Take a look at the article below which summarizes the usage of Support Debugging Tool with some real life examples. Great compilation by David! http://blogs.msdn.com/b/developingfordynamicsgp/archive/2011/08/05/using-the-support-debugging-tool-with-real-life-examples.aspx

Hope this helps the community…

Until next post!

August 18, 2011 · veeyeskay · 2 Comments
Tags: , , ,  · Posted in: Accounts Receivables, Customizations, Dynamics, Great Plains, Support Debugging Tool Total Views: 1,689

Receivables HATB With Aging By Payment Terms (using Document Date)

As a follow up to my script for generating the Receivables HATB With Aging By Due Date (using Document Date), I have decided to post a script for the same with aging buckets based on the document date (with the logic of payment terms). This will provide the aging buckets for each transaction based on the receivables aging buckets setup.

/******************************************************************
Created July 21, 2011 by Sivakumar Venkataraman - Interdyn AKA
This view is for the Receivables HATB for aging by document
date (using the payment terms on the document) and
picking transactions using the Document Date.
 
Tables used:
 - RM20101 - RM Open File
 - RM30101 - RM History File
 - RM20201 - RM Apply Open File
 - RM30201 - RM Apply History File
 - RM40401 - Document Type Setup File
 - RM40201 - RM Period Setup
 
Revision History
 No	 Date		Reason for Change
 1	 12/12/2017	Updated the logic for due date for credits
 ******************************************************************/
 
DECLARE @EMPTYDATE AS DATETIME
DECLARE @ASOFDATE AS DATETIME
 
SET @EMPTYDATE = '1900-01-01'
SET @ASOFDATE = '2017-04-12' 
 
SELECT  W.CUSTNMBR AS CUSTOMERNO ,
        W1.DOCDESCR AS DOCTYPE ,
        W.DOCNUMBR ,
        W.DOCDATE ,
        W.TRXSORCE ,
        W.GLPOSTDT AS POSTINGDATE ,
        W.DUEDATE ,
        W.AGINGBUCKET ,
        W.DOCUMENTAMT ,
        W.CURTRXAMT
FROM    ( SELECT    X.CUSTNMBR ,
                    X.RMDTYPAL ,
                    X.DOCNUMBR ,
                    X.DOCDATE ,
                    X.TRXSORCE ,
                    X.VOIDED ,
                    X.GLPOSTDT ,
                    X.DUEDATE ,
                    X.DAYSDUE ,
                    CASE WHEN X.DAYSDUE > 999 THEN ( SELECT TOP 1
                                                            RMPERDSC
                                                     FROM   dbo.RM40201
                                                     ORDER BY RMPEREND DESC
                                                   )
                         WHEN X.DAYSDUE < 0 THEN 'Not Due'
                         ELSE ISNULL(( SELECT TOP 1
                                                RMPERDSC
                                       FROM     dbo.RM40201 AG
                                       WHERE    X.DAYSDUE <= AG.RMPEREND
                                       ORDER BY RMPEREND
                                     ), '')
                    END AS AGINGBUCKET ,
                    X.VOIDPDATE ,
                    X.DOCUMENTAMT ,
                    X.APPLIEDAMT ,
                    X.WRITEOFFAMT ,
                    X.DISCTAKENAMT ,
                    X.REALGAINLOSSAMT ,
                    CASE WHEN X.RMDTYPAL < 6
                         THEN ( X.DOCUMENTAMT - X.APPLIEDAMT - X.WRITEOFFAMT - X.DISCTAKENAMT - X.REALGAINLOSSAMT )
                         ELSE ( X.DOCUMENTAMT - X.APPLIEDAMT - X.WRITEOFFAMT - X.DISCTAKENAMT - X.REALGAINLOSSAMT ) * -1
                    END AS CURTRXAMT
          FROM      ( SELECT    Z.CUSTNMBR ,
                                Z.RMDTYPAL ,
                                Z.DOCDATE ,
                                Z.DOCNUMBR ,
                                Z.ORTRXAMT AS DOCUMENTAMT ,
                                CASE WHEN RMDTYPAL < 6 THEN ISNULL(( SELECT SUM(Y.APPLDAMT)
                                                                     FROM   ( SELECT    CUSTNMBR ,
                                                                                        APFRDCDT ,
                                                                                        DATE1 ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        APPTOAMT AS APPLDAMT
                                                                              FROM      dbo.RM20201
                                                                              WHERE     POSTED = 1
                                                                                        AND APTODCTY <> 6
                                                                              UNION
                                                                              SELECT    CUSTNMBR ,
                                                                                        APFRDCDT ,
                                                                                        DATE1 ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        APPTOAMT AS APPLDAMT
                                                                              FROM      dbo.RM30201
                                                                              WHERE     APTODCTY <> 6
                                                                            ) Y
                                                                     WHERE  Y.DATE1 <= @ASOFDATE
                                                                            AND Y.APFRDCDT <= @ASOFDATE
                                                                            AND Y.APFRDCDT <> @EMPTYDATE
                                                                            AND Y.CUSTNMBR = Z.CUSTNMBR
                                                                            AND Y.APTODCNM = Z.DOCNUMBR
                                                                            AND Y.APTODCTY = Z.RMDTYPAL
                                                                   ), 0)
                                     WHEN RMDTYPAL > 6
                                          AND RMDTYPAL <= 9 THEN ISNULL(( SELECT    SUM(Y.APPLDAMT)
                                                                          FROM      ( SELECT    CUSTNMBR ,
                                                                                                DATE1 ,
                                                                                                APPTOAMT AS APPLDAMT ,
                                                                                                APFRDCNM ,
                                                                                                APFRDCTY ,
                                                                                                APTODCNM ,
                                                                                                APTODCTY ,
                                                                                                APTODCDT
                                                                                      FROM      dbo.RM20201
                                                                                      WHERE     POSTED = 1
                                                                                                AND APTODCTY <> 6
                                                                                      UNION
                                                                                      SELECT    CUSTNMBR ,
                                                                                                DATE1 ,
                                                                                                APPTOAMT ,
                                                                                                APFRDCNM ,
                                                                                                APFRDCTY ,
                                                                                                APTODCNM ,
                                                                                                APTODCTY ,
                                                                                                APTODCDT
                                                                                      FROM      dbo.RM30201
                                                                                      WHERE     APTODCTY <> 6
                                                                                    ) Y
                                                                          WHERE     Y.DATE1 <= @ASOFDATE
                                                                                    AND Y.APTODCDT <= @ASOFDATE
                                                                                    AND Y.APTODCDT <> @EMPTYDATE
                                                                                    AND Y.CUSTNMBR = Z.CUSTNMBR
                                                                                    AND Y.APFRDCNM = Z.DOCNUMBR
                                                                                    AND Y.APFRDCTY = Z.RMDTYPAL
                                                                        ), 0)
                                     ELSE 0
                                END AS APPLIEDAMT ,
                                CASE WHEN RMDTYPAL < 6 THEN ISNULL(( SELECT SUM(Y.WROFAMNT)
                                                                     FROM   ( SELECT    CUSTNMBR ,
                                                                                        APFRDCDT ,
                                                                                        DATE1 ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        WROFAMNT
                                                                              FROM      dbo.RM20201
                                                                              WHERE     POSTED = 1
                                                                                        AND APTODCTY <> 6
                                                                              UNION
                                                                              SELECT    CUSTNMBR ,
                                                                                        APFRDCDT ,
                                                                                        DATE1 ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        WROFAMNT
                                                                              FROM      dbo.RM30201
                                                                              WHERE     APTODCTY <> 6
                                                                            ) Y
                                                                     WHERE  Y.DATE1 <= @ASOFDATE
                                                                            AND Y.APFRDCDT <= @ASOFDATE
                                                                            AND Y.APFRDCDT <> @EMPTYDATE
                                                                            AND Y.CUSTNMBR = Z.CUSTNMBR
                                                                            AND Y.APTODCNM = Z.DOCNUMBR
                                                                            AND Y.APTODCTY = Z.RMDTYPAL
                                                                   ), 0)
                                     ELSE 0
                                END AS WRITEOFFAMT ,
                                CASE WHEN RMDTYPAL < 6 THEN ISNULL(( SELECT SUM(Y.DISTKNAM)
                                                                     FROM   ( SELECT    CUSTNMBR ,
                                                                                        APFRDCDT ,
                                                                                        DATE1 ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        DISTKNAM
                                                                              FROM      dbo.RM20201
                                                                              WHERE     POSTED = 1
                                                                                        AND APTODCTY <> 6
                                                                              UNION
                                                                              SELECT    CUSTNMBR ,
                                                                                        APFRDCDT ,
                                                                                        DATE1 ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        DISTKNAM
                                                                              FROM      dbo.RM30201
                                                                              WHERE     APTODCTY <> 6
                                                                            ) Y
                                                                     WHERE  Y.DATE1 <= @ASOFDATE
                                                                            AND Y.APFRDCDT <= @ASOFDATE
                                                                            AND Y.APFRDCDT <> @EMPTYDATE
                                                                            AND Y.CUSTNMBR = Z.CUSTNMBR
                                                                            AND Y.APTODCNM = Z.DOCNUMBR
                                                                            AND Y.APTODCTY = Z.RMDTYPAL
                                                                   ), 0)
                                     ELSE 0
                                END AS DISCTAKENAMT ,
                                CASE WHEN RMDTYPAL > 6 THEN ISNULL(( SELECT SUM(Y.RLGANLOS)
                                                                     FROM   ( SELECT    CUSTNMBR ,
                                                                                        DATE1 ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        APTODCDT ,
                                                                                        RLGANLOS
                                                                              FROM      dbo.RM20201
                                                                              WHERE     POSTED = 1
                                                                                        AND APTODCTY <> 6
                                                                              UNION
                                                                              SELECT    CUSTNMBR ,
                                                                                        DATE1 ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        APTODCDT ,
                                                                                        RLGANLOS
                                                                              FROM      dbo.RM30201
                                                                              WHERE     APTODCTY <> 6
                                                                            ) Y
                                                                     WHERE  Y.DATE1 <= @ASOFDATE
                                                                            AND Y.APTODCDT <= @ASOFDATE
                                                                            AND Y.APTODCDT <> @EMPTYDATE
                                                                            AND Y.CUSTNMBR = Z.CUSTNMBR
                                                                            AND Y.APFRDCNM = Z.DOCNUMBR
                                                                            AND Y.APFRDCTY = Z.RMDTYPAL
                                                                   ), 0)
                                     ELSE 0
                                END AS REALGAINLOSSAMT ,
                                Z.TRXSORCE ,
                                Z.VOIDED ,
                                Z.GLPOSTDT ,
                                Z.DUEDATE ,
                                DATEDIFF(dd, Z.DOCDATE, @ASOFDATE) AS DAYSDUE ,
                                Z.VOIDPDATE
                      FROM      ( SELECT    CUSTNMBR ,
                                            RMDTYPAL ,
                                            DOCDATE ,
                                            DOCNUMBR ,
                                            ORTRXAMT ,
                                            BACHNUMB ,
                                            TRXSORCE ,
                                            BCHSOURC ,
                                            DISCDATE ,
                                            VOIDSTTS AS VOIDED ,
                                            GLPOSTDT ,
                                            CASE WHEN DUEDATE = @EMPTYDATE THEN DOCDATE
                                                 ELSE [DUEDATE]
                                            END AS DUEDATE ,
                                            VOIDDATE AS VOIDPDATE
                                  FROM      dbo.RM20101
                                  UNION
                                  SELECT    CUSTNMBR ,
                                            RMDTYPAL ,
                                            DOCDATE ,
                                            DOCNUMBR ,
                                            ORTRXAMT ,
                                            BACHNUMB ,
                                            TRXSORCE ,
                                            BCHSOURC ,
                                            DISCDATE ,
                                            VOIDSTTS AS VOIDED ,
                                            GLPOSTDT ,
                                            CASE WHEN DUEDATE = @EMPTYDATE THEN DOCDATE
                                                 ELSE [DUEDATE]
                                            END AS DUEDATE ,
                                            VOIDDATE AS VOIDPDATE
                                  FROM      dbo.RM30101
                                ) Z
                      WHERE     Z.DOCDATE <= @ASOFDATE
                                AND Z.VOIDED = 0
                                AND Z.RMDTYPAL <> 6
                    ) X
          UNION ALL
          SELECT    X.CUSTNMBR ,
                    X.RMDTYPAL ,
                    X.DOCNUMBR ,
                    X.DOCDATE ,
                    X.TRXSORCE ,
                    X.VOIDED ,
                    X.PSTGDATE ,
                    X.DUEDATE ,
                    X.DAYSDUE ,
                    CASE WHEN X.DAYSDUE > 999 THEN ( SELECT TOP 1
                                                            RMPERDSC
                                                     FROM   dbo.RM40201
                                                     ORDER BY RMPEREND DESC
                                                   )
                         WHEN X.DAYSDUE < 0 THEN 'Not Due'
                         ELSE ISNULL(( SELECT TOP 1
                                                RMPERDSC
                                       FROM     dbo.RM40201 AG
                                       WHERE    X.DAYSDUE <= AG.RMPEREND
                                       ORDER BY RMPEREND
                                     ), '')
                    END AS AGINGBUCKET ,
                    X.VOIDPDATE ,
                    X.DOCUMENTAMT ,
                    X.APPLIEDAMT ,
                    X.WRITEOFFAMT ,
                    X.DISCTAKENAMT ,
                    X.REALGAINLOSSAMT ,
                    CASE WHEN X.RMDTYPAL < 6
                         THEN ( X.DOCUMENTAMT - X.APPLIEDAMT - X.WRITEOFFAMT - X.DISCTAKENAMT - X.REALGAINLOSSAMT )
                         ELSE ( X.DOCUMENTAMT - X.APPLIEDAMT - X.WRITEOFFAMT - X.DISCTAKENAMT - X.REALGAINLOSSAMT ) * -1
                    END AS CURTRXAMT
          FROM      ( SELECT    Z.CUSTNMBR ,
                                Z.RMDTYPAL ,
                                Z.DOCDATE ,
                                Z.DOCNUMBR ,
                                Z.ORTRXAMT AS DOCUMENTAMT ,
                                CASE WHEN RMDTYPAL < 6 THEN ISNULL(( SELECT SUM(Y.APPLDAMT)
                                                                     FROM   ( SELECT    CUSTNMBR ,
                                                                                        APFRDCDT ,
                                                                                        DATE1 ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        APPTOAMT AS APPLDAMT
                                                                              FROM      dbo.RM20201
                                                                              WHERE     POSTED = 1
                                                                                        AND APTODCTY <> 6
                                                                              UNION
                                                                              SELECT    CUSTNMBR ,
                                                                                        APFRDCDT ,
                                                                                        DATE1 ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        APPTOAMT AS APPLDAMT
                                                                              FROM      dbo.RM30201
                                                                              WHERE     APTODCTY <> 6
                                                                            ) Y
                                                                     WHERE  Y.DATE1 <= @ASOFDATE
                                                                            AND Y.APFRDCDT <= @ASOFDATE
                                                                            AND Y.APFRDCDT <> @EMPTYDATE
                                                                            AND Y.CUSTNMBR = Z.CUSTNMBR
                                                                            AND Y.APTODCNM = Z.DOCNUMBR
                                                                            AND Y.APTODCTY = Z.RMDTYPAL
                                                                   ), 0)
                                     WHEN RMDTYPAL > 6
                                          AND RMDTYPAL <= 9 THEN ISNULL(( SELECT    SUM(Y.APPLDAMT)
                                                                          FROM      ( SELECT    CUSTNMBR ,
                                                                                                DATE1 ,
                                                                                                APPTOAMT AS APPLDAMT ,
                                                                                                APFRDCNM ,
                                                                                                APFRDCTY ,
                                                                                                APTODCNM ,
                                                                                                APTODCTY ,
                                                                                                APTODCDT
                                                                                      FROM      dbo.RM20201
                                                                                      WHERE     POSTED = 1
                                                                                                AND APTODCTY <> 6
                                                                                      UNION
                                                                                      SELECT    CUSTNMBR ,
                                                                                                DATE1 ,
                                                                                                APPTOAMT ,
                                                                                                APFRDCNM ,
                                                                                                APFRDCTY ,
                                                                                                APTODCNM ,
                                                                                                APTODCTY ,
                                                                                                APTODCDT
                                                                                      FROM      dbo.RM30201
                                                                                      WHERE     APTODCTY <> 6
                                                                                    ) Y
                                                                          WHERE     Y.DATE1 <= @ASOFDATE
                                                                                    AND Y.APTODCDT <= @ASOFDATE
                                                                                    AND Y.APTODCDT <> @EMPTYDATE
                                                                                    AND Y.CUSTNMBR = Z.CUSTNMBR
                                                                                    AND Y.APFRDCNM = Z.DOCNUMBR
                                                                                    AND Y.APFRDCTY = Z.RMDTYPAL
                                                                        ), 0)
                                     ELSE 0
                                END AS APPLIEDAMT ,
                                CASE WHEN RMDTYPAL < 6 THEN ISNULL(( SELECT SUM(Y.WROFAMNT)
                                                                     FROM   ( SELECT    CUSTNMBR ,
                                                                                        APFRDCDT ,
                                                                                        DATE1 ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        WROFAMNT
                                                                              FROM      dbo.RM20201
                                                                              WHERE     POSTED = 1
                                                                                        AND APTODCTY <> 6
                                                                              UNION
                                                                              SELECT    CUSTNMBR ,
                                                                                        APFRDCDT ,
                                                                                        DATE1 ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        WROFAMNT
                                                                              FROM      dbo.RM30201
                                                                              WHERE     APTODCTY <> 6
                                                                            ) Y
                                                                     WHERE  Y.DATE1 <= @ASOFDATE
                                                                            AND Y.APFRDCDT <= @ASOFDATE
                                                                            AND Y.APFRDCDT <> @EMPTYDATE
                                                                            AND Y.CUSTNMBR = Z.CUSTNMBR
                                                                            AND Y.APTODCNM = Z.DOCNUMBR
                                                                            AND Y.APTODCTY = Z.RMDTYPAL
                                                                   ), 0)
                                     ELSE 0
                                END AS WRITEOFFAMT ,
                                CASE WHEN RMDTYPAL < 6 THEN ISNULL(( SELECT SUM(Y.DISTKNAM)
                                                                     FROM   ( SELECT    CUSTNMBR ,
                                                                                        APFRDCDT ,
                                                                                        DATE1 ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        DISTKNAM
                                                                              FROM      dbo.RM20201
                                                                              WHERE     POSTED = 1
                                                                                        AND APTODCTY <> 6
                                                                              UNION
                                                                              SELECT    CUSTNMBR ,
                                                                                        APFRDCDT ,
                                                                                        DATE1 ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        DISTKNAM
                                                                              FROM      dbo.RM30201
                                                                              WHERE     APTODCTY <> 6
                                                                            ) Y
                                                                     WHERE  Y.DATE1 <= @ASOFDATE
                                                                            AND Y.APFRDCDT <= @ASOFDATE
                                                                            AND Y.APFRDCDT <> @EMPTYDATE
                                                                            AND Y.CUSTNMBR = Z.CUSTNMBR
                                                                            AND Y.APTODCNM = Z.DOCNUMBR
                                                                            AND Y.APTODCTY = Z.RMDTYPAL
                                                                   ), 0)
                                     ELSE 0
                                END AS DISCTAKENAMT ,
                                CASE WHEN RMDTYPAL > 6 THEN ISNULL(( SELECT SUM(Y.RLGANLOS)
                                                                     FROM   ( SELECT    CUSTNMBR ,
                                                                                        DATE1 ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        APTODCDT ,
                                                                                        RLGANLOS
                                                                              FROM      dbo.RM20201
                                                                              WHERE     POSTED = 1
                                                                                        AND APTODCTY <> 6
                                                                              UNION
                                                                              SELECT    CUSTNMBR ,
                                                                                        DATE1 ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        APTODCDT ,
                                                                                        RLGANLOS
                                                                              FROM      dbo.RM30201
                                                                              WHERE     APTODCTY <> 6
                                                                            ) Y
                                                                     WHERE  Y.DATE1 <= @ASOFDATE
                                                                            AND Y.APTODCDT <= @ASOFDATE
                                                                            AND Y.APTODCDT <> @EMPTYDATE
                                                                            AND Y.CUSTNMBR = Z.CUSTNMBR
                                                                            AND Y.APFRDCNM = Z.DOCNUMBR
                                                                            AND Y.APFRDCTY = Z.RMDTYPAL
                                                                   ), 0)
                                     ELSE 0
                                END AS REALGAINLOSSAMT ,
                                Z.TRXSORCE ,
                                Z.VOIDED ,
                                Z.PSTGDATE ,
                                Z.DUEDATE ,
                                DATEDIFF(dd, Z.DOCDATE, @ASOFDATE) AS DAYSDUE ,
                                Z.VOIDPDATE
                      FROM      ( SELECT    CUSTNMBR ,
                                            RMDTYPAL ,
                                            DOCDATE ,
                                            DOCNUMBR ,
                                            ORTRXAMT ,
                                            BACHNUMB ,
                                            TRXSORCE ,
                                            BCHSOURC ,
                                            DISCDATE ,
                                            VOIDSTTS AS VOIDED ,
                                            GLPOSTDT AS PSTGDATE ,
                                            CASE WHEN DUEDATE = @EMPTYDATE THEN DOCDATE
                                                 ELSE [DUEDATE]
                                            END AS DUEDATE ,
                                            VOIDDATE AS VOIDPDATE
                                  FROM      dbo.RM20101
                                  UNION
                                  SELECT    CUSTNMBR ,
                                            RMDTYPAL ,
                                            DOCDATE ,
                                            DOCNUMBR ,
                                            ORTRXAMT ,
                                            BACHNUMB ,
                                            TRXSORCE ,
                                            BCHSOURC ,
                                            DISCDATE ,
                                            VOIDSTTS AS VOIDED ,
                                            GLPOSTDT ,
                                            CASE WHEN DUEDATE = @EMPTYDATE THEN DOCDATE
                                                 ELSE [DUEDATE]
                                            END AS DUEDATE ,
                                            VOIDDATE AS VOIDPDATE
                                  FROM      dbo.RM30101
                                ) Z
                      WHERE     Z.DOCDATE <= @ASOFDATE
                                AND Z.VOIDED = 1
                                AND Z.RMDTYPAL <> 6
                                AND Z.VOIDPDATE > @ASOFDATE
                    ) X
        ) W
        INNER JOIN dbo.RM40401 W1 ON W.RMDTYPAL = W1.RMDTYPAL
WHERE   W.CURTRXAMT <> 0

Hope this helps the community…

Until next post!

July 21, 2011 · veeyeskay · One Comment
Tags: , , , ,  · Posted in: Accounts Receivables, Dynamics, Great Plains, SQL Server, SQL Server 2005 Total Views: 1,414

Receivables HATB With Aging By Payment Terms (using GL Posting Date)

As a follow up to my script for generating the Receivables HATB With Aging By Due Date (using GL Posting Date), I have decided to post a script for the same with aging buckets based on the document date (with the logic of payment terms). This will provide the aging buckets for each transaction based on the receivables aging buckets setup.

/******************************************************************
Created July 21, 2011 by Sivakumar Venkataraman - Interdyn AKA
This view is for the Receivables HATB for aging the document
date (using the payment terms on the document) and
picking transactions using the GL Posting Date.
 
Tables used:
 - RM20101 - RM Open File
 - RM30101 - RM History File
 - RM20201 - RM Apply Open File
 - RM30201 - RM Apply History File
 - RM40401 - Document Type Setup File
 - RM40201 - RM Period Setup
 - SY03300 - Payment Terms Master
 
Revision History
 No	 Date		Reason for Change
 1	 12/12/2017	Updated the logic for due date for credits
 ******************************************************************/
 
DECLARE @EMPTYDATE AS DATETIME
DECLARE @ASOFDATE AS DATETIME
 
SET @EMPTYDATE = '1900-01-01'
SET @ASOFDATE = '2017-04-12'
 
 
SELECT  W.CUSTNMBR AS CUSTOMERNO ,
        W1.DOCDESCR AS DOCTYPE ,
        W.DOCNUMBR ,
        W.DOCDATE ,
        W.TRXSORCE ,
        W.GLPOSTDT AS POSTINGDATE ,
        W.DUEDATE ,
        W.AGINGBUCKET ,
        W.DOCUMENTAMT ,
        W.CURTRXAMT
FROM    ( SELECT    X.CUSTNMBR ,
                    X.RMDTYPAL ,
                    X.DOCNUMBR ,
                    X.DOCDATE ,
                    X.TRXSORCE ,
                    X.VOIDED ,
                    X.GLPOSTDT ,
                    X.DUEDATE ,
                    X.DAYSDUE ,
                    CASE WHEN X.DAYSDUE > 999 THEN ( SELECT TOP 1
                                                            RMPERDSC
                                                     FROM   dbo.RM40201
                                                     ORDER BY RMPEREND DESC
                                                   )
                         WHEN X.DAYSDUE < 0 THEN 'Not Due'
                         ELSE ISNULL(( SELECT TOP 1
                                                RMPERDSC
                                       FROM     dbo.RM40201 AG
                                       WHERE    X.DAYSDUE <= AG.RMPEREND
                                       ORDER BY RMPEREND
                                     ), '')
                    END AS AGINGBUCKET ,
                    X.VOIDPDATE ,
                    X.DOCUMENTAMT ,
                    X.APPLIEDAMT ,
                    X.WRITEOFFAMT ,
                    X.DISCTAKENAMT ,
                    X.REALGAINLOSSAMT ,
                    CASE WHEN X.RMDTYPAL < 6
                         THEN ( X.DOCUMENTAMT - X.APPLIEDAMT - X.WRITEOFFAMT - X.DISCTAKENAMT - X.REALGAINLOSSAMT )
                         ELSE ( X.DOCUMENTAMT - X.APPLIEDAMT - X.WRITEOFFAMT - X.DISCTAKENAMT - X.REALGAINLOSSAMT ) * -1
                    END AS CURTRXAMT
          FROM      ( SELECT    Z.CUSTNMBR ,
                                Z.RMDTYPAL ,
                                Z.DOCDATE ,
                                Z.DOCNUMBR ,
                                Z.ORTRXAMT AS DOCUMENTAMT ,
                                CASE WHEN RMDTYPAL < 6 THEN ISNULL(( SELECT SUM(Y.APPLDAMT)
                                                                     FROM   ( SELECT    CUSTNMBR ,
                                                                                        ApplyFromGLPostDate ,
                                                                                        GLPOSTDT ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        APPTOAMT AS APPLDAMT
                                                                              FROM      dbo.RM20201
                                                                              WHERE     POSTED = 1
                                                                                        AND APTODCTY <> 6
                                                                              UNION
                                                                              SELECT    CUSTNMBR ,
                                                                                        ApplyFromGLPostDate ,
                                                                                        GLPOSTDT ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        APPTOAMT AS APPLDAMT
                                                                              FROM      dbo.RM30201
                                                                              WHERE     APTODCTY <> 6
                                                                            ) Y
                                                                     WHERE  Y.GLPOSTDT <= @ASOFDATE
                                                                            AND Y.ApplyFromGLPostDate <= @ASOFDATE
                                                                            AND Y.ApplyFromGLPostDate <> @EMPTYDATE
                                                                            AND Y.CUSTNMBR = Z.CUSTNMBR
                                                                            AND Y.APTODCNM = Z.DOCNUMBR
                                                                            AND Y.APTODCTY = Z.RMDTYPAL
                                                                   ), 0)
                                     WHEN RMDTYPAL > 6
                                          AND RMDTYPAL <= 9
                                     THEN ISNULL(( SELECT   SUM(Y.APPLDAMT)
                                                   FROM     ( SELECT    CUSTNMBR ,
                                                                        GLPOSTDT ,
                                                                        APPTOAMT AS APPLDAMT ,
                                                                        APFRDCNM ,
                                                                        APFRDCTY ,
                                                                        APTODCNM ,
                                                                        APTODCTY ,
                                                                        ApplyToGLPostDate
                                                              FROM      dbo.RM20201
                                                              WHERE     POSTED = 1
                                                                        AND APTODCTY <> 6
                                                              UNION
                                                              SELECT    CUSTNMBR ,
                                                                        GLPOSTDT ,
                                                                        APPTOAMT ,
                                                                        APFRDCNM ,
                                                                        APFRDCTY ,
                                                                        APTODCNM ,
                                                                        APTODCTY ,
                                                                        ApplyToGLPostDate
                                                              FROM      dbo.RM30201
                                                              WHERE     APTODCTY <> 6
                                                            ) Y
                                                   WHERE    Y.GLPOSTDT <= @ASOFDATE
                                                            AND Y.ApplyToGLPostDate <= @ASOFDATE
                                                            AND Y.ApplyToGLPostDate <> @EMPTYDATE
                                                            AND Y.CUSTNMBR = Z.CUSTNMBR
                                                            AND Y.APFRDCNM = Z.DOCNUMBR
                                                            AND Y.APFRDCTY = Z.RMDTYPAL
                                                 ), 0)
                                     ELSE 0
                                END AS APPLIEDAMT ,
                                CASE WHEN RMDTYPAL < 6 THEN ISNULL(( SELECT SUM(Y.WROFAMNT)
                                                                     FROM   ( SELECT    CUSTNMBR ,
                                                                                        ApplyFromGLPostDate ,
                                                                                        GLPOSTDT ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        WROFAMNT
                                                                              FROM      dbo.RM20201
                                                                              WHERE     POSTED = 1
                                                                                        AND APTODCTY <> 6
                                                                              UNION
                                                                              SELECT    CUSTNMBR ,
                                                                                        ApplyFromGLPostDate ,
                                                                                        GLPOSTDT ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        WROFAMNT
                                                                              FROM      dbo.RM30201
                                                                              WHERE     APTODCTY <> 6
                                                                            ) Y
                                                                     WHERE  Y.GLPOSTDT <= @ASOFDATE
                                                                            AND Y.ApplyFromGLPostDate <= @ASOFDATE
                                                                            AND Y.ApplyFromGLPostDate <> @EMPTYDATE
                                                                            AND Y.CUSTNMBR = Z.CUSTNMBR
                                                                            AND Y.APTODCNM = Z.DOCNUMBR
                                                                            AND Y.APTODCTY = Z.RMDTYPAL
                                                                   ), 0)
                                     ELSE 0
                                END AS WRITEOFFAMT ,
                                CASE WHEN RMDTYPAL < 6 THEN ISNULL(( SELECT SUM(Y.DISTKNAM)
                                                                     FROM   ( SELECT    CUSTNMBR ,
                                                                                        ApplyFromGLPostDate ,
                                                                                        GLPOSTDT ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        DISTKNAM
                                                                              FROM      dbo.RM20201
                                                                              WHERE     POSTED = 1
                                                                                        AND APTODCTY <> 6
                                                                              UNION
                                                                              SELECT    CUSTNMBR ,
                                                                                        ApplyFromGLPostDate ,
                                                                                        GLPOSTDT ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        DISTKNAM
                                                                              FROM      dbo.RM30201
                                                                              WHERE     APTODCTY <> 6
                                                                            ) Y
                                                                     WHERE  Y.GLPOSTDT <= @ASOFDATE
                                                                            AND Y.ApplyFromGLPostDate <= @ASOFDATE
                                                                            AND Y.ApplyFromGLPostDate <> @EMPTYDATE
                                                                            AND Y.CUSTNMBR = Z.CUSTNMBR
                                                                            AND Y.APTODCNM = Z.DOCNUMBR
                                                                            AND Y.APTODCTY = Z.RMDTYPAL
                                                                   ), 0)
                                     ELSE 0
                                END AS DISCTAKENAMT ,
                                CASE WHEN RMDTYPAL > 6 THEN ISNULL(( SELECT SUM(Y.RLGANLOS)
                                                                     FROM   ( SELECT    CUSTNMBR ,
                                                                                        GLPOSTDT ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        ApplyToGLPostDate ,
                                                                                        RLGANLOS
                                                                              FROM      dbo.RM20201
                                                                              WHERE     POSTED = 1
                                                                                        AND APTODCTY <> 6
                                                                              UNION
                                                                              SELECT    CUSTNMBR ,
                                                                                        GLPOSTDT ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        ApplyToGLPostDate ,
                                                                                        RLGANLOS
                                                                              FROM      dbo.RM30201
                                                                              WHERE     APTODCTY <> 6
                                                                            ) Y
                                                                     WHERE  Y.GLPOSTDT <= @ASOFDATE
                                                                            AND Y.ApplyToGLPostDate <= @ASOFDATE
                                                                            AND Y.ApplyToGLPostDate <> @EMPTYDATE
                                                                            AND Y.CUSTNMBR = Z.CUSTNMBR
                                                                            AND Y.APFRDCNM = Z.DOCNUMBR
                                                                            AND Y.APFRDCTY = Z.RMDTYPAL
                                                                   ), 0)
                                     ELSE 0
                                END AS REALGAINLOSSAMT ,
                                Z.TRXSORCE ,
                                Z.VOIDED ,
                                Z.GLPOSTDT ,
                                Z.DUEDATE ,
                                DATEDIFF(dd, Z.DOCDATE, @ASOFDATE) AS DAYSDUE ,
                                Z.VOIDPDATE
                      FROM      ( SELECT    CUSTNMBR ,
                                            RMDTYPAL ,
                                            DOCDATE ,
                                            DOCNUMBR ,
                                            ORTRXAMT ,
                                            BACHNUMB ,
                                            TRXSORCE ,
                                            BCHSOURC ,
                                            DISCDATE ,
                                            VOIDSTTS AS VOIDED ,
                                            GLPOSTDT ,
                                            CASE WHEN DUEDATE = @EMPTYDATE THEN DOCDATE
                                                 ELSE [DUEDATE]
                                            END AS DUEDATE ,
                                            VOIDDATE AS VOIDPDATE
                                  FROM      dbo.RM20101
                                  UNION
                                  SELECT    CUSTNMBR ,
                                            RMDTYPAL ,
                                            DOCDATE ,
                                            DOCNUMBR ,
                                            ORTRXAMT ,
                                            BACHNUMB ,
                                            TRXSORCE ,
                                            BCHSOURC ,
                                            DISCDATE ,
                                            VOIDSTTS AS VOIDED ,
                                            GLPOSTDT ,
                                            CASE WHEN DUEDATE = @EMPTYDATE THEN DOCDATE
                                                 ELSE [DUEDATE]
                                            END AS DUEDATE ,
                                            VOIDDATE AS VOIDPDATE
                                  FROM      dbo.RM30101
                                ) Z
                      WHERE     Z.GLPOSTDT <= @ASOFDATE
                                AND Z.VOIDED = 0
                                AND Z.RMDTYPAL <> 6
                    ) X
          UNION ALL
          SELECT    X.CUSTNMBR ,
                    X.RMDTYPAL ,
                    X.DOCNUMBR ,
                    X.DOCDATE ,
                    X.TRXSORCE ,
                    X.VOIDED ,
                    X.PSTGDATE ,
                    X.DUEDATE ,
                    X.DAYSDUE ,
                    CASE WHEN X.DAYSDUE > 999 THEN ( SELECT TOP 1
                                                            RMPERDSC
                                                     FROM   dbo.RM40201
                                                     ORDER BY RMPEREND DESC
                                                   )
                         WHEN X.DAYSDUE < 0 THEN 'Not Due'
                         ELSE ISNULL(( SELECT TOP 1
                                                RMPERDSC
                                       FROM     dbo.RM40201 AG
                                       WHERE    X.DAYSDUE <= AG.RMPEREND
                                       ORDER BY RMPEREND
                                     ), '')
                    END AS AGINGBUCKET ,
                    X.VOIDPDATE ,
                    X.DOCUMENTAMT ,
                    X.APPLIEDAMT ,
                    X.WRITEOFFAMT ,
                    X.DISCTAKENAMT ,
                    X.REALGAINLOSSAMT ,
                    CASE WHEN X.RMDTYPAL < 6
                         THEN ( X.DOCUMENTAMT - X.APPLIEDAMT - X.WRITEOFFAMT - X.DISCTAKENAMT - X.REALGAINLOSSAMT )
                         ELSE ( X.DOCUMENTAMT - X.APPLIEDAMT - X.WRITEOFFAMT - X.DISCTAKENAMT - X.REALGAINLOSSAMT ) * -1
                    END AS CURTRXAMT
          FROM      ( SELECT    Z.CUSTNMBR ,
                                Z.RMDTYPAL ,
                                Z.DOCDATE ,
                                Z.DOCNUMBR ,
                                Z.ORTRXAMT AS DOCUMENTAMT ,
                                CASE WHEN RMDTYPAL < 6 THEN ISNULL(( SELECT SUM(Y.APPLDAMT)
                                                                     FROM   ( SELECT    CUSTNMBR ,
                                                                                        ApplyFromGLPostDate ,
                                                                                        GLPOSTDT ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        APPTOAMT AS APPLDAMT
                                                                              FROM      dbo.RM20201
                                                                              WHERE     POSTED = 1
                                                                                        AND APTODCTY <> 6
                                                                              UNION
                                                                              SELECT    CUSTNMBR ,
                                                                                        ApplyFromGLPostDate ,
                                                                                        GLPOSTDT ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        APPTOAMT AS APPLDAMT
                                                                              FROM      dbo.RM30201
                                                                              WHERE     APTODCTY <> 6
                                                                            ) Y
                                                                     WHERE  Y.GLPOSTDT <= @ASOFDATE
                                                                            AND Y.ApplyFromGLPostDate <= @ASOFDATE
                                                                            AND Y.ApplyFromGLPostDate <> @EMPTYDATE
                                                                            AND Y.CUSTNMBR = Z.CUSTNMBR
                                                                            AND Y.APTODCNM = Z.DOCNUMBR
                                                                            AND Y.APTODCTY = Z.RMDTYPAL
                                                                   ), 0)
                                     WHEN RMDTYPAL > 6
                                          AND RMDTYPAL <= 9
                                     THEN ISNULL(( SELECT   SUM(Y.APPLDAMT)
                                                   FROM     ( SELECT    CUSTNMBR ,
                                                                        GLPOSTDT ,
                                                                        APPTOAMT AS APPLDAMT ,
                                                                        APFRDCNM ,
                                                                        APFRDCTY ,
                                                                        APTODCNM ,
                                                                        APTODCTY ,
                                                                        ApplyToGLPostDate
                                                              FROM      dbo.RM20201
                                                              WHERE     POSTED = 1
                                                                        AND APTODCTY <> 6
                                                              UNION
                                                              SELECT    CUSTNMBR ,
                                                                        GLPOSTDT ,
                                                                        APPTOAMT ,
                                                                        APFRDCNM ,
                                                                        APFRDCTY ,
                                                                        APTODCNM ,
                                                                        APTODCTY ,
                                                                        ApplyToGLPostDate
                                                              FROM      dbo.RM30201
                                                              WHERE     APTODCTY <> 6
                                                            ) Y
                                                   WHERE    Y.GLPOSTDT <= @ASOFDATE
                                                            AND Y.ApplyToGLPostDate <= @ASOFDATE
                                                            AND Y.ApplyToGLPostDate <> @EMPTYDATE
                                                            AND Y.CUSTNMBR = Z.CUSTNMBR
                                                            AND Y.APFRDCNM = Z.DOCNUMBR
                                                            AND Y.APFRDCTY = Z.RMDTYPAL
                                                 ), 0)
                                     ELSE 0
                                END AS APPLIEDAMT ,
                                CASE WHEN RMDTYPAL < 6 THEN ISNULL(( SELECT SUM(Y.WROFAMNT)
                                                                     FROM   ( SELECT    CUSTNMBR ,
                                                                                        ApplyFromGLPostDate ,
                                                                                        GLPOSTDT ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        WROFAMNT
                                                                              FROM      dbo.RM20201
                                                                              WHERE     POSTED = 1
                                                                                        AND APTODCTY <> 6
                                                                              UNION
                                                                              SELECT    CUSTNMBR ,
                                                                                        ApplyFromGLPostDate ,
                                                                                        GLPOSTDT ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        WROFAMNT
                                                                              FROM      dbo.RM30201
                                                                              WHERE     APTODCTY <> 6
                                                                            ) Y
                                                                     WHERE  Y.GLPOSTDT <= @ASOFDATE
                                                                            AND Y.ApplyFromGLPostDate <= @ASOFDATE
                                                                            AND Y.ApplyFromGLPostDate <> @EMPTYDATE
                                                                            AND Y.CUSTNMBR = Z.CUSTNMBR
                                                                            AND Y.APTODCNM = Z.DOCNUMBR
                                                                            AND Y.APTODCTY = Z.RMDTYPAL
                                                                   ), 0)
                                     ELSE 0
                                END AS WRITEOFFAMT ,
                                CASE WHEN RMDTYPAL < 6 THEN ISNULL(( SELECT SUM(Y.DISTKNAM)
                                                                     FROM   ( SELECT    CUSTNMBR ,
                                                                                        ApplyFromGLPostDate ,
                                                                                        GLPOSTDT ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        DISTKNAM
                                                                              FROM      dbo.RM20201
                                                                              WHERE     POSTED = 1
                                                                                        AND APTODCTY <> 6
                                                                              UNION
                                                                              SELECT    CUSTNMBR ,
                                                                                        ApplyFromGLPostDate ,
                                                                                        GLPOSTDT ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        DISTKNAM
                                                                              FROM      dbo.RM30201
                                                                              WHERE     APTODCTY <> 6
                                                                            ) Y
                                                                     WHERE  Y.GLPOSTDT <= @ASOFDATE
                                                                            AND Y.ApplyFromGLPostDate <= @ASOFDATE
                                                                            AND Y.ApplyFromGLPostDate <> @EMPTYDATE
                                                                            AND Y.CUSTNMBR = Z.CUSTNMBR
                                                                            AND Y.APTODCNM = Z.DOCNUMBR
                                                                            AND Y.APTODCTY = Z.RMDTYPAL
                                                                   ), 0)
                                     ELSE 0
                                END AS DISCTAKENAMT ,
                                CASE WHEN RMDTYPAL > 6 THEN ISNULL(( SELECT SUM(Y.RLGANLOS)
                                                                     FROM   ( SELECT    CUSTNMBR ,
                                                                                        GLPOSTDT ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        ApplyToGLPostDate ,
                                                                                        RLGANLOS
                                                                              FROM      dbo.RM20201
                                                                              WHERE     POSTED = 1
                                                                                        AND APTODCTY <> 6
                                                                              UNION
                                                                              SELECT    CUSTNMBR ,
                                                                                        GLPOSTDT ,
                                                                                        APFRDCNM ,
                                                                                        APFRDCTY ,
                                                                                        APTODCNM ,
                                                                                        APTODCTY ,
                                                                                        ApplyToGLPostDate ,
                                                                                        RLGANLOS
                                                                              FROM      dbo.RM30201
                                                                              WHERE     APTODCTY <> 6
                                                                            ) Y
                                                                     WHERE  Y.GLPOSTDT <= @ASOFDATE
                                                                            AND Y.ApplyToGLPostDate <= @ASOFDATE
                                                                            AND Y.ApplyToGLPostDate <> @EMPTYDATE
                                                                            AND Y.CUSTNMBR = Z.CUSTNMBR
                                                                            AND Y.APFRDCNM = Z.DOCNUMBR
                                                                            AND Y.APFRDCTY = Z.RMDTYPAL
                                                                   ), 0)
                                     ELSE 0
                                END AS REALGAINLOSSAMT ,
                                Z.TRXSORCE ,
                                Z.VOIDED ,
                                Z.PSTGDATE ,
                                Z.DUEDATE ,
                                DATEDIFF(dd, Z.DOCDATE, @ASOFDATE) AS DAYSDUE ,
                                Z.VOIDPDATE
                      FROM      ( SELECT    CUSTNMBR ,
                                            RMDTYPAL ,
                                            DOCDATE ,
                                            DOCNUMBR ,
                                            ORTRXAMT ,
                                            BACHNUMB ,
                                            TRXSORCE ,
                                            BCHSOURC ,
                                            DISCDATE ,
                                            VOIDSTTS AS VOIDED ,
                                            GLPOSTDT AS PSTGDATE ,
                                            CASE WHEN DUEDATE = @EMPTYDATE THEN DOCDATE
                                                 ELSE [DUEDATE]
                                            END AS DUEDATE ,
                                            VOIDDATE AS VOIDPDATE
                                  FROM      dbo.RM20101
                                  UNION
                                  SELECT    CUSTNMBR ,
                                            RMDTYPAL ,
                                            DOCDATE ,
                                            DOCNUMBR ,
                                            ORTRXAMT ,
                                            BACHNUMB ,
                                            TRXSORCE ,
                                            BCHSOURC ,
                                            DISCDATE ,
                                            VOIDSTTS AS VOIDED ,
                                            GLPOSTDT ,
                                            CASE WHEN DUEDATE = @EMPTYDATE THEN DOCDATE
                                                 ELSE [DUEDATE]
                                            END AS DUEDATE ,
                                            VOIDDATE AS VOIDPDATE
                                  FROM      dbo.RM30101
                                ) Z
                      WHERE     Z.PSTGDATE <= @ASOFDATE
                                AND Z.VOIDED = 1
                                AND Z.RMDTYPAL <> 6
                                AND Z.VOIDPDATE > @ASOFDATE
                    ) X
        ) W
        INNER JOIN dbo.RM40401 W1 ON W.RMDTYPAL = W1.RMDTYPAL
WHERE   W.CURTRXAMT <> 0

Hope this helps the community…

Until next post!

July 21, 2011 · veeyeskay · 2 Comments
Tags: , , , , ,  · Posted in: Accounts Receivables, Dynamics, Great Plains, SQL Server, SQL Server 2005 Total Views: 2,981