Alter Procedure FETCH_RECEIVABLE_AGING_DETAIL_MSAN

/******************************************************************************
*PROCEDURE            :        FETCH_RECEIVABLE_AGING_DETAIL
*DESCRIPTION          :        RETURNS AMOUNT RECEIVABLE DETAILS
*CREATED BY           :        PARIMALA.V.R.,GIRISH PAI
*CREATED ON           :        27/11/2003
*REVISION HISTORY     :
*  MODIFED BY      : SREEKANTH
*  MODIFIED ON     : NOVEMBER 16, 2004
*  DESCRIPTION     : AR AGING QUICK DETAIL REPORT IS NOT SORTED ON DATE
*  TASK ID         : 255106
*
*  MODIFED BY      : SREEKANTH
*  MODIFIED ON     : FEB 23, 2005, MARCH 02, 2005[Revision]
*  DESCRIPTION     : SIGNOFF- CLARIFICATION DOCUMENT 252463 /252464-'POSTED TRANSACTIONS ONLY' IN AGING REPORT
*  TASK ID         : 260668

*  MODIFED BY      : SREEKANTH
*  MODIFIED ON     : APRIL 23, 2005
*  DESCRIPTION     : Account payable Report showing Wrong information after importing His. Vend. Doc.
*  TASK ID         : 261805

*  MODIFED BY      : NAYAN MANSINHA
*  MODIFIED ON     : FEB 09 2006
*  DESCRIPTION     : Incident 2489931 - The Customer account number is not printing on the AR Aging report
*  MR ID           : 34103
******************************************************************************/

--Alter        PROCEDURE dbo.FETCH_RECEIVABLE_AGING_DETAIL

--  @IID   INTEGER, --(ID FROM TABLE) 1

  @I_GROUPBY     INTEGER, --(Group By - 1. Account Name,2. Account Code 3.Account Balance) 2
  @I_SORTBY      INTEGER, --(Sort By - 1. Document Date,2. Due Date 3.Document Balance) 3
  @I_AGEDETAIL   INTEGER,    --(1 -Age using due date,2-Age using document date,3-Do not show aging USED) 4
  @I_INCLDTXN    INTEGER,    --(Transactions to Include 1. All Transactions 2. Posted Transactions Only) 5
  @D_ASOFDATE    DATETIME, --(As of Date) 6
  @C_SHOWDOC     VARCHAR(1),  --(Show Document Details (T/F) COMPULSARY) 7
  @C_SHOWPAYMENT VARCHAR(1),   --(Show Payment Details  (T/F) COMPULSARY) 8
  @C_EXCCRMEMO   VARCHAR(1),   --(Exclude Credit Memo (T/F) USED) 9
  @C_EXCADVANCE  VARCHAR(1),   --(Exclude Advances (T/F) USED) 10
  @I_SALESREP    INTEGER,     --(1 -All ,2-Specific Sales Rep,3-Sales Rep Range USED) 11
  @V_FROMSREP    VARCHAR(15), --(Sales Rep From USE) 12
  @V_TOSREP    VARCHAR(15), --(Sales Rep To USED) 13
  @I_CUSTOMER   INTEGER, --(1 -All Customer, 2- specific customer ,3- Customer Range USED) 14
  @V_FROMCUST    VARCHAR(15), --(Customer From USED) 15
  @V_TOCUST      VARCHAR(15), --(Customer To USED) 16
  @I_DOCALIAS    INTEGER,  --(1- All 2 -selected aliases USED) 17
  @I_TERMS    INTEGER,   --(1- All 2 -selected Terms USED) 18
  @I_DEPART  INTEGER,   --(1- All 2 -selected Departments USED) 19
  @I_DOC_DATES  INTEGER,   --(1- All Dates 2- Date Range USED)  20
  @D_FROMDATE    DATETIME,  --(Document Date From USED) 21
  @D_TODATE      DATETIME,  --(Document Date From USED) 22
  @I_FROMDAYS    INTEGER,   --(Days Overdue From) 23
  @I_TODAYS      INTEGER,  --(Days Overdue To) 24
  @V_DUMMY  CHAR,  -- This is required to maintain the count of parameters same as Account Payable Aging.SQL, this is important as both report have common dialog.
  @V_ADVSRCH     VARCHAR(255),
  @V_FROM_AC   VARCHAR(40),
  @V_TO_AC   VARCHAR(40),
  @V_BC				VARCHAR(1)	-- print in T = Base currency, F = Vendor currency
AS
BEGIN
SET NOCOUNT ON

/* CREATE RESULT TABLE */
DECLARE @ACCOUNT_RECEIVABLE TABLE
(
 TE_DOC_ALIAS     	TEXT COLLATE DATABASE_DEFAULT ,
 TE_DOC_TERMS     	TEXT COLLATE DATABASE_DEFAULT ,
 TE_DOC_DEPT     	TEXT COLLATE DATABASE_DEFAULT ,
 T_DATE_FLD     	DATETIME,
 T_ORDER_DATE     	DATETIME,
 T_INV_AMOUNT     	MONEY,
 T_INV_AMOUNT_FEX	MONEY,
 T_INV_DUE_ON     	DATETIME,
 T_TERMS      		VARCHAR(15) COLLATE DATABASE_DEFAULT ,  -- COD/N30 ? STORE INVOICE/CUST TERM
 T_DOC_TYPE     	INTEGER,
 T_DOC_NO     		VARCHAR(15) COLLATE DATABASE_DEFAULT,
 T_CUST_CODE     	VARCHAR(15) COLLATE DATABASE_DEFAULT,
 T_DEP      		VARCHAR(15) COLLATE DATABASE_DEFAULT  DEFAULT '' NOT NULL,
 T_REF_NO     		VARCHAR(40) COLLATE DATABASE_DEFAULT ,
 T_SALES_REP     	VARCHAR(15) COLLATE DATABASE_DEFAULT,
 T_DOC_ALIAS     	VARCHAR(25) COLLATE DATABASE_DEFAULT,
 T_AMOUNT     		MONEY,
 T_AMOUNT_FEX		MONEY,
 T_TRANCURRCODE		VARCHAR(15) COLLATE DATABASE_DEFAULT,  
 --T_PAID_AMT   	MONEY, --NOT USED
 T_DR_CR      		INTEGER, --??
 T_ENTRY_DATE     	DATETIME,
 T_PAY_TYPE     	VARCHAR(15) COLLATE DATABASE_DEFAULT , -- CASH/CHECK
 T_DELQ      		INTEGER,
 T_TR_DISC    		MONEY,
 T_TR_DISC_FEX		MONEY,
 T_TRAN_TYPE   		VARCHAR(15) COLLATE DATABASE_DEFAULT,
 T_BATCH_TYPE   	INTEGER,
 T_INV_RMA   		VARCHAR(40) COLLATE DATABASE_DEFAULT,
 T_DISC_DATE   		DATETIME
)

/*TEMP TABLE TO HOLD ACCOUNTS BASED ON ADV SEARCH EXPN*/
/*CREATE TABLE #COA
(
 ACNT_CODE   VARCHAR(40) COLLATE DATABASE_DEFAULT,
 PARENT_ACNT   VARCHAR(40) COLLATE DATABASE_DEFAULT,
 TYPE   TINYINT,
 DETAIL   TINYINT,
 DESCRIPT  VARCHAR(40) COLLATE DATABASE_DEFAULT,
 BANK_AC  CHAR(1),
 ACTIVE   CHAR(1),
 CURR_CODE  VARCHAR(15) COLLATE DATABASE_DEFAULT,
 ACNT_LEVEL  TINYINT
)
*/



DECLARE @I_DOC_1 INTEGER
DECLARE @I_DOC_2 INTEGER
DECLARE @I_DOC_3 INTEGER
DECLARE @I_DOC_4 INTEGER
DECLARE @V_ALL VARCHAR(1) 




 /*All accounts?*/

 
 
SET @I_DOC_1 = 8   -- SALES ORDER
SET @I_DOC_2 = 9   -- SALES INVOICE
SET @I_DOC_3 = 11  -- CREDIT ORDER
SET @I_DOC_4 = 12  -- CREDIT MEMO







IF @C_EXCCRMEMO = 'T'
   SET @I_DOC_4 = 9  -- EXCLUDE CREDIT MEMO
IF @C_EXCADVANCE = 'T'
BEGIN
  SET @I_DOC_3 = 9  -- EXCLUDE CREDIT ORDER
  SET @I_DOC_1 = 9   -- EXCLUDE SALES ORDER
END



/*
IF @V_ADVSRCH = '' AND @V_FROM_AC = '' AND @V_TO_AC = ''
BEGIN
  SET @V_ALL = 'T'
  INSERT INTO #COA EXEC FETCH_FROM_COA_MSAN '', '', '', '2,5'
END
ELSE
BEGIN
  SET @V_ALL = 'F'
  INSERT INTO #COA EXEC FETCH_FROM_COA_MSAN @V_FROM_AC, @V_TO_AC, @V_ADVSRCH, '2,5'
END
*/

DECLARE @TEMP_TYPE INTEGER
DECLARE @TEMP_DOC_ALIAS VARCHAR(40)
DECLARE @TEMP_TERMS VARCHAR(15)
DECLARE @TEMP_DEPART VARCHAR(15)
DECLARE @DOC_ALIAS_CON VARCHAR(1000)
DECLARE @TERMS_CON VARCHAR(1000)
DECLARE @DEPART_CON VARCHAR(1000)
DECLARE @SEMPOS INTEGER
DECLARE @DOCTYPE INTEGER

SELECT @DOC_ALIAS_CON = ''
SELECT @TERMS_CON = ''
SELECT @DEPART_CON = ''

DECLARE CURTEMPDOC1 CURSOR FOR
 SELECT TYPE,DOC_ALIAS,TERMS,DEPART FROM PRINT_ARAP_AGING_DETAIL WHERE PRINT_ARAP_ID = IID
OPEN CURTEMPDOC1
FETCH FROM CURTEMPDOC1 INTO @TEMP_TYPE ,@TEMP_DOC_ALIAS, @TEMP_TERMS ,@TEMP_DEPART
WHILE @@FETCH_STATUS = 0
BEGIN
 IF @TEMP_TYPE = 1
 BEGIN
  SET @SEMPOS = CHARINDEX(';', @TEMP_DOC_ALIAS)
  SET @DOCTYPE = CAST(SUBSTRING(@TEMP_DOC_ALIAS, @SEMPOS+1, LEN(@TEMP_DOC_ALIAS)) AS INTEGER)
  IF @SEMPOS <> 0
   SET @TEMP_DOC_ALIAS = SUBSTRING(@TEMP_DOC_ALIAS, 1 , @SEMPOS-1 ) + ';' +
       CASE WHEN @DOCTYPE = 7 THEN 'SQ'
          WHEN @DOCTYPE = 8 THEN 'SO'
          WHEN @DOCTYPE = 9 THEN 'SI'
          WHEN @DOCTYPE = 10 THEN 'CQ'
          WHEN @DOCTYPE = 11 THEN 'CO'
          WHEN @DOCTYPE = 12 THEN 'CM'
       ELSE '' END
 END

 SELECT @DOC_ALIAS_CON = CASE @TEMP_TYPE WHEN 1 THEN  @DOC_ALIAS_CON  + (CASE WHEN LTRIM(@DOC_ALIAS_CON) <> '' THEN ',' ELSE '' END) + @TEMP_DOC_ALIAS
     ELSE  @DOC_ALIAS_CON  +''
    END

 SELECT @TERMS_CON = CASE @TEMP_TYPE WHEN 2 THEN  @TERMS_CON + (CASE WHEN LTRIM(@TERMS_CON) <> '' THEN ',' ELSE '' END) +  @TEMP_TERMS
     ELSE  @TERMS_CON +''
    END
 SELECT @DEPART_CON = CASE @TEMP_TYPE WHEN 3 THEN  @DEPART_CON  + (CASE WHEN LTRIM(@DEPART_CON) <> '' THEN ',' ELSE '' END) + @TEMP_DEPART
     ELSE @DEPART_CON  +''
    END

  FETCH FROM CURTEMPDOC1 INTO @TEMP_TYPE ,@TEMP_DOC_ALIAS, @TEMP_TERMS ,@TEMP_DEPART
END

CLOSE CURTEMPDOC1     DEALLOCATE CURTEMPDOC1

IF @I_INCLDTXN = '1' -- all transactions
BEGIN
  INSERT INTO @ACCOUNT_RECEIVABLE
  SELECT @DOC_ALIAS_CON, @TERMS_CON, @DEPART_CON,
 ISNULL(I.DATE_FLD,'1900-01-01'),
 ISNULL(I.ORDER_DATE,'1900-01-01'),
 CASE WHEN T.DOC_TYPE IN (9, 12) OR T.BATCH_TYPE in (4) THEN T.AMOUNT ELSE 0 END,
 CASE WHEN T.DOC_TYPE IN (9, 12) OR T.BATCH_TYPE in (4) THEN T.AMOUNT_FEX ELSE 0 END,
 CASE WHEN ISNULL(I.INV_DUE_ON,'') = '' THEN IsNull(I.ORDER_DATE, '1900-01-01')
             WHEN YEAR(I.INV_DUE_ON) = 1899 THEN IsNull(I.ORDER_DATE, '1900-01-01')
        ELSE IsNull(I.INV_DUE_ON, '1900-01-01') END,
       ISNULL(T.TERM_CODE,''),
        ISNULL(T.DOC_TYPE, 0), ISNULL(T.DOC_NO,''), ISNULL(T.SUB_CODE,''), ISNULL(T.DEP_CODE,''),ISNULL(T.REF_NO,''),
        ISNULL(T.SALES_REP, I.SALES_REP), ISNULL(T.DOC_ALIAS, I.DOC_ALIAS),
 CASE T.BATCH_TYPE WHEN 4 THEN 0 ELSE T.AMOUNT END, 
 CASE T.BATCH_TYPE WHEN 4 THEN 0 ELSE T.AMOUNT_FEX END, 
 T.CURRCODE,
 T.DR_CR, IsNull(T.ENTRY_DATE, '1900-01-01'), IsNull(T.PAYTYPE, ''), 0,
 ISNULL(I.TR_DISC, 0), ISNULL(I.TR_DISC_FEX, 0), IsNull(T.TRAN_TYPE, ''), T.BATCH_TYPE,
 I.PO, DBO.GET_DISCOUNT_DUEDATE(IsNull(I.TERMS, ''), IsNull(I.ORDER_DATE, '1900-01-01'))
  FROM TRANSACT T WITH(NOLOCK),
       INVOICES I WITH(NOLOCK),
       DOCALIAS D WITH(NOLOCK)
  WHERE I.DOC_NO = T.DOC_NO AND I.STATUS = T.DOC_TYPE AND
 I.CUST_CODE = T.SUB_CODE AND I.STATUS = D.DOC_TYPE AND D.IS_DEFAULT = 'T' AND
 T.DOC_TYPE IN (@I_DOC_1, @I_DOC_2, @I_DOC_3, @I_DOC_4) AND
 T.ENTRY_DATE <= @D_ASOFDATE AND
 (@I_CUSTOMER = 1 OR (@I_CUSTOMER = 2 AND T.SUB_CODE = @V_FROMCUST) OR
       (@I_CUSTOMER = 3 AND T.SUB_CODE BETWEEN @V_FROMCUST AND @V_TOCUST)) AND
 (T.ACNT_CODE IN ('A1300', 'L2313')) AND
 (@I_TERMS = 1 OR (@I_TERMS = 2 AND I.TERMS IN (SELECT TERMS FROM PRINT_ARAP_AGING_DETAIL WITH(NOLOCK)
         WHERE TYPE=2 AND PRINT_ARAP_ID = IID))) AND
 (@I_DOC_DATES = 1 OR (@I_DOC_DATES <> 1 AND I.ORDER_DATE BETWEEN @D_FROMDATE AND @D_TODATE)) AND
 (@I_SALESREP = 1 OR (@I_SALESREP = 2 AND I.SALES_REP = @V_FROMSREP) OR
       (@I_SALESREP = 3 AND I.SALES_REP BETWEEN @V_FROMSREP AND @V_TOSREP)) AND
 (@I_DEPART = 1 OR (@I_DEPART = 2 AND I.DEP IN (SELECT DEPART FROM PRINT_ARAP_AGING_DETAIL WITH(NOLOCK)
         WHERE TYPE=3 AND PRINT_ARAP_ID = IID))) AND
 (@I_DOCALIAS = 1 OR (@I_DOCALIAS = 2 AND '''' + ISNULL(I.DOC_ALIAS, D.DOC_ALIAS) + '''' + ';'+  CAST(STATUS AS VARCHAR(10)) IN
  (SELECT DOC_ALIAS FROM PRINT_ARAP_AGING_DETAIL WITH (NOLOCK) WHERE TYPE=1 AND PRINT_ARAP_ID = IID))) AND
 ISNULL(I.IS_HISTORICAL,'F') = 'F'
  UNION ALL
  SELECT @DOC_ALIAS_CON, @TERMS_CON, @DEPART_CON,
 0,
 ISNULL(I.ORDER_DATE,'1900-01-01'),
  0, 0,
 CASE WHEN ISNULL(I.INV_DUE_ON,'') = '' THEN IsNull(I.ORDER_DATE, '1900-01-01')
             WHEN YEAR(I.INV_DUE_ON) = 1899 THEN IsNull(I.ORDER_DATE, '1900-01-01')
        ELSE IsNull(I.INV_DUE_ON, '1900-01-01') END,
        ISNULL(I.TERMS,''),
        ISNULL(T.DOC_TYPE, 0), ISNULL(T.DOC_NO,''), ISNULL(T.SUB_CODE,''), ISNULL(T.DEP_CODE,''),ISNULL(B.REF_NO,''),
        ISNULL(I.SALES_REP,''), ISNULL(I.DOC_ALIAS,''),
 T.AMOUNT, T.AMOUNT_FEX, T.CURRCODE, T.DR_CR, IsNull(T.ENTRY_DATE, '1900-01-01'), B.PAYTYPE, 0,
 0, 0, 'RCT', T.BATCH_TYPE,
 I.PO, DBO.GET_DISCOUNT_DUEDATE(IsNull(I.TERMS, ''), IsNull(I.ORDER_DATE, '1900-01-01'))
  FROM BCH_TRAN T WITH(NOLOCK),
       BCH_HEAD B WITH(NOLOCK),
       INVOICES I WITH(NOLOCK),
       DOCALIAS D WITH(NOLOCK)
  WHERE T.BATCH_TYPE = 5 AND T.BATCH_TYPE = B.BATCH_TYPE AND T.BATCH_NO = B.BATCH_NO AND T.TRAN_NO = B.TRAN_NO AND
 I.DOC_NO = T.DOC_NO AND I.STATUS = T.DOC_TYPE AND
 I.CUST_CODE = T.SUB_CODE AND I.STATUS = D.DOC_TYPE AND D.IS_DEFAULT = 'T' AND
 T.DOC_TYPE IN (@I_DOC_1, @I_DOC_2, @I_DOC_3, @I_DOC_4) AND
 T.ENTRY_DATE <= @D_ASOFDATE AND
 (@I_CUSTOMER = 1 OR (@I_CUSTOMER = 2 AND T.SUB_CODE = @V_FROMCUST) OR
       (@I_CUSTOMER = 3 AND T.SUB_CODE BETWEEN @V_FROMCUST AND @V_TOCUST)) AND
 (T.ACNT_CODE IN ('A1300', 'L2313')) AND
 (@I_TERMS = 1 OR (@I_TERMS = 2 AND I.TERMS IN (SELECT TERMS FROM PRINT_ARAP_AGING_DETAIL WITH(NOLOCK)
         WHERE TYPE=2 AND PRINT_ARAP_ID = IID))) AND
 (@I_DOC_DATES = 1 OR (@I_DOC_DATES <> 1 AND I.ORDER_DATE BETWEEN @D_FROMDATE AND @D_TODATE)) AND
 (@I_SALESREP = 1 OR (@I_SALESREP = 2 AND I.SALES_REP = @V_FROMSREP) OR
       (@I_SALESREP = 3 AND I.SALES_REP BETWEEN @V_FROMSREP AND @V_TOSREP)) AND
 (@I_DEPART = 1 OR (@I_DEPART = 2 AND I.DEP IN (SELECT DEPART FROM PRINT_ARAP_AGING_DETAIL WITH(NOLOCK)
         WHERE TYPE=3 AND PRINT_ARAP_ID = IID))) AND
  (@I_DOCALIAS = 1 OR (@I_DOCALIAS = 2 AND '''' + ISNULL(I.DOC_ALIAS, D.DOC_ALIAS) + '''' + ';'+  CAST(STATUS AS VARCHAR(10)) IN
  (SELECT DOC_ALIAS FROM PRINT_ARAP_AGING_DETAIL WITH (NOLOCK) WHERE TYPE=1 AND PRINT_ARAP_ID = IID))) AND
 ISNULL(I.IS_HISTORICAL,'F') = 'F'
  UNION ALL
  --get unposted Invoices/Credit Memos
  SELECT @DOC_ALIAS_CON, @TERMS_CON, @DEPART_CON,
 ISNULL(I.DATE_FLD,'1900-01-01'),
 ISNULL(I.ORDER_DATE,'1900-01-01'),
  CASE WHEN T.DOC_TYPE IN (9, 12) THEN T.TRAN_AMT ELSE 0 END,
  CASE WHEN T.DOC_TYPE IN (9, 12) THEN T.AMOUNT_FEX ELSE 0 END,
 CASE WHEN ISNULL(I.INV_DUE_ON,'') = '' THEN IsNull(I.ORDER_DATE, '1900-01-01')
             WHEN YEAR(I.INV_DUE_ON) = 1899 THEN IsNull(I.ORDER_DATE, '1900-01-01')
        ELSE IsNull(I.INV_DUE_ON, '1900-01-01') END,
        ISNULL(T.TERM_CODE,''),
        ISNULL(T.DOC_TYPE, 0), ISNULL(T.INV_NO,''), ISNULL(T.SUB_CODE,''), ISNULL(T.SUB_DEP,''),ISNULL(T.REF_NO,''),
        ISNULL(I.SALES_REP,''), ISNULL(T.DOC_ALIAS,''),
 0, 0, T.CURRCODE, T.DR_CR, IsNull(T.TRAN_DATE, '1900-01-01'), IsNull(T.PAYTYPE, ''), 0,
 ISNULL(I.TR_DISC, 0), ISNULL(I.TR_DISC_FEX, 0), 'INV', T.BATCH_TYPE,
 I.PO, DBO.GET_DISCOUNT_DUEDATE(IsNull(I.TERMS, ''), ISNULL(I.ORDER_DATE,'1900-01-01'))
  FROM BCH_HEAD T WITH(NOLOCK),
       INVOICES I WITH(NOLOCK),
       DOCALIAS D WITH(NOLOCK)
  WHERE T.BATCH_TYPE = 4 AND
 I.DOC_NO = T.INV_NO AND I.STATUS = T.DOC_TYPE AND
 I.CUST_CODE = T.SUB_CODE AND I.STATUS = D.DOC_TYPE AND D.IS_DEFAULT = 'T' AND
 T.DOC_TYPE IN (@I_DOC_1, @I_DOC_2, @I_DOC_3, @I_DOC_4) AND
 T.TRAN_DATE <= @D_ASOFDATE AND
 (@I_CUSTOMER = 1 OR (@I_CUSTOMER = 2 AND T.SUB_CODE = @V_FROMCUST) OR
       (@I_CUSTOMER = 3 AND T.SUB_CODE BETWEEN @V_FROMCUST AND @V_TOCUST)) AND
 (T.BANK_CODE IN ('A1300', 'L2313')) AND
 (@I_TERMS = 1 OR (@I_TERMS = 2 AND I.TERMS IN (SELECT TERMS FROM PRINT_ARAP_AGING_DETAIL WITH(NOLOCK)
         WHERE TYPE=2 AND PRINT_ARAP_ID = IID))) AND
 (@I_DOC_DATES = 1 OR (@I_DOC_DATES <> 1 AND I.ORDER_DATE BETWEEN @D_FROMDATE AND @D_TODATE)) AND
 (@I_SALESREP = 1 OR (@I_SALESREP = 2 AND I.SALES_REP = @V_FROMSREP) OR
       (@I_SALESREP = 3 AND I.SALES_REP BETWEEN @V_FROMSREP AND @V_TOSREP)) AND
 (@I_DEPART = 1 OR (@I_DEPART = 2 AND I.DEP IN (SELECT DEPART FROM PRINT_ARAP_AGING_DETAIL WITH(NOLOCK)
         WHERE TYPE=3 AND PRINT_ARAP_ID = IID))) AND
 (@I_DOCALIAS = 1 OR (@I_DOCALIAS = 2 AND '''' + ISNULL(I.DOC_ALIAS, D.DOC_ALIAS) + '''' + ';'+  CAST(STATUS AS VARCHAR(10)) IN
  (SELECT DOC_ALIAS FROM PRINT_ARAP_AGING_DETAIL WITH (NOLOCK) WHERE TYPE=1 AND PRINT_ARAP_ID = IID))) AND
 ISNULL(I.IS_HISTORICAL,'F') = 'F'
END
ELSE
BEGIN
  INSERT INTO @ACCOUNT_RECEIVABLE
  SELECT @DOC_ALIAS_CON, @TERMS_CON, @DEPART_CON,
 ISNULL(I.DATE_FLD,'1900-01-01'),
 ISNULL(I.ORDER_DATE,'1900-01-01'),
 CASE WHEN T.DOC_TYPE IN (9, 12) OR T.BATCH_TYPE in (4) THEN T.AMOUNT ELSE 0 END INV_AMOUNT,
 CASE WHEN T.DOC_TYPE IN (9, 12) OR T.BATCH_TYPE in (4) THEN T.AMOUNT_FEX ELSE 0 END INV_AMOUNT,
 CASE WHEN ISNULL(I.INV_DUE_ON,'') = '' THEN IsNull(I.ORDER_DATE, '1900-01-01')
             WHEN YEAR(I.INV_DUE_ON) = 1899 THEN IsNull(I.ORDER_DATE, '1900-01-01')
        ELSE IsNull(I.INV_DUE_ON, '1900-01-01') END,
        ISNULL(I.TERMS,''),
        ISNULL(T.DOC_TYPE, 0), ISNULL(T.DOC_NO,''), ISNULL(T.SUB_CODE,''), ISNULL(T.DEP_CODE,''),ISNULL(T.REF_NO,''),
        ISNULL(T.SALES_REP, I.SALES_REP), ISNULL(T.DOC_ALIAS, I.DOC_ALIAS),
 CASE WHEN T.BATCH_TYPE = 4 THEN 0 ELSE T.AMOUNT END, 
 CASE WHEN T.BATCH_TYPE = 4 THEN 0 ELSE T.AMOUNT_FEX END, 
 T.CURRCODE, T.DR_CR, IsNull(T.ENTRY_DATE, '1900-01-01'), IsNull(T.PAYTYPE, ''), 0,
 ISNULL(I.TR_DISC, 0), ISNULL(I.TR_DISC_FEX, 0), IsNull(T.TRAN_TYPE, ''), T.BATCH_TYPE,
 I.PO, DBO.GET_DISCOUNT_DUEDATE(IsNull(I.TERMS, ''), IsNull(I.ORDER_DATE, '1900-01-01'))
  FROM TRANSACT T WITH(NOLOCK),
       INVOICES I WITH(NOLOCK),
       DOCALIAS D WITH(NOLOCK)
  WHERE I.DOC_NO = T.DOC_NO AND I.STATUS = T.DOC_TYPE AND
 I.CUST_CODE = T.SUB_CODE AND I.STATUS = D.DOC_TYPE AND D.IS_DEFAULT = 'T' AND
 T.DOC_TYPE IN (@I_DOC_1, @I_DOC_2, @I_DOC_3, @I_DOC_4) AND
 T.ENTRY_DATE <= @D_ASOFDATE AND
 (@I_CUSTOMER = 1 OR (@I_CUSTOMER = 2 AND T.SUB_CODE = @V_FROMCUST) OR
       (@I_CUSTOMER = 3 AND T.SUB_CODE BETWEEN @V_FROMCUST AND @V_TOCUST)) AND
 (T.ACNT_CODE IN ('A1300', 'L2313')) AND
 (@I_TERMS = 1 OR (@I_TERMS = 2 AND I.TERMS IN (SELECT TERMS FROM PRINT_ARAP_AGING_DETAIL WITH(NOLOCK)
         WHERE TYPE=2 AND PRINT_ARAP_ID = IID))) AND
 (@I_DOC_DATES = 1 OR (@I_DOC_DATES <> 1 AND I.ORDER_DATE BETWEEN @D_FROMDATE AND @D_TODATE)) AND
 (@I_SALESREP = 1 OR (@I_SALESREP = 2 AND I.SALES_REP = @V_FROMSREP) OR
       (@I_SALESREP = 3 AND I.SALES_REP BETWEEN @V_FROMSREP AND @V_TOSREP)) AND
 (@I_DEPART = 1 OR (@I_DEPART = 2 AND I.DEP IN (SELECT DEPART FROM PRINT_ARAP_AGING_DETAIL WITH(NOLOCK)
         WHERE TYPE=3 AND PRINT_ARAP_ID = IID))) AND
 (@I_DOCALIAS = 1 OR (@I_DOCALIAS = 2 AND '''' + ISNULL(I.DOC_ALIAS, D.DOC_ALIAS) + '''' + ';'+  CAST(STATUS AS VARCHAR(10)) IN
  (SELECT DOC_ALIAS FROM PRINT_ARAP_AGING_DETAIL WITH (NOLOCK) WHERE TYPE=1 AND PRINT_ARAP_ID = IID))) AND
 ISNULL(I.IS_HISTORICAL,'F') = 'F'
END

UPDATE @ACCOUNT_RECEIVABLE SET T_DELQ = CASE @I_AGEDETAIL WHEN 2 THEN DATEDIFF(DAY, T_ORDER_DATE,GETDATE())
                                                          WHEN 1 THEN DATEDIFF(DAY, T_INV_DUE_ON,GETDATE())
                                                          ELSE 0 END

SELECT AR.*, C.NAME, C.PAY_TERMS, C.CR_LIMIT, C.CR_LIMIT_FEX, C.ONHOLD, C.CURRCODE, A.FIRST_NAME, A.LAST_NAME, A.CUSTCHAR1,
 A.TEL1, A.EXTN_TEL1, A.TEL2, A.EXTN_TEL2, A.APPLY_MASK_TEL, CT.TELE_FORMAT, IsNull(P.PT_TYPE, 0) T_PT_TYPE
FROM @ACCOUNT_RECEIVABLE AR INNER JOIN
	(SELECT T_DOC_TYPE, T_DOC_NO
		FROM @ACCOUNT_RECEIVABLE 
		GROUP BY T_DOC_TYPE, T_DOC_NO
		HAVING 	SUM(DBO.ARINV(T_BATCH_TYPE, T_DOC_TYPE, T_INV_AMOUNT, T_DR_CR)) - 
			SUM(DBO.ARPAID(T_BATCH_TYPE, T_DOC_TYPE, T_INV_AMOUNT, T_AMOUNT, T_DR_CR)) <> 0) AR1 
	ON AR.T_DOC_TYPE = AR1.T_DOC_TYPE AND AR.T_DOC_NO = AR1.T_DOC_NO
 	LEFT OUTER JOIN CUST C WITH(NOLOCK) ON AR.T_CUST_CODE = C.CUST_CODE
	LEFT OUTER JOIN ADDRESS A WITH(NOLOCK) ON C.BILLCODE = A.ADDR_CODE
	LEFT OUTER JOIN COUNTRY CT WITH(NOLOCK) ON A.COUNTRY = CT.COUNTRY_NAME
	LEFT OUTER JOIN PAYTYPE P WITH(NOLOCK) ON AR.T_PAY_TYPE = P.PAY_CODE
WHERE (((T_DELQ >= @I_FROMDAYS AND T_DELQ <= @I_TODAYS) AND (@I_FROMDAYS <> 0 AND @I_TODAYS <> 0 ))
 OR (@I_FROMDAYS = 0 AND @I_TODAYS = 0))

SET NOCOUNT OFF
END


--EXEC FETCH_RECEIVABLE_AGING_DETAIL 0, 1, 1, 1, 1, {ts '2006-10-31 00:00:00'}, 'T', 'F', 'F', 'F', 1, '', '', 1, '21447', '', 1, 1, 1, 1, {ts '2005-10-01 00:00:00'}, {ts '2005-10-31 00:00:00'}, 0, 0, '', '', '', '', 'T'
--exec FETCH_RECEIVABLE_AGING_DETAIL 0, 1, 1, 1, 2, {ts '2006-08-07 00:00:00'}, 'T', 'T', 'F', 'F', 1, '', '', 1, '', '', 1, 1, 1, 1, {ts '2005-11-22 00:00:00'}, {ts '2005-11-22 00:00:00'}, 0, 0, '', '', '', '', 'T'





	return 


