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