gl_enter_credencearh.pas

一个MRPII系统源代码版本

               +'          case when Sum(TotalTaxAmountC)<0 then -Sum(TotalTaxAmountC)  '
               +'               else 0                                                  '
               +'          end as CreditAmount,             '//本位币贷方金额            '
}
               +'          Sum(TotalTaxAmount) as FDebitAmount,'//原币借方金额                           '
               +'          0.0 as FCreditAmount,         '//原币贷方金额                 '
               +'          Sum(TotalTaxAmountC)  as DebitAmount,'//本位币借方金额                         '
               +'          0.0 as CreditAmount,             '//本位币贷方金额            '

               +'          ''销售发票'', '
               +'          0, '
               +'          ''客户：''+CustomerCode+'' 发票号：''+InvoiceNo as RemArk'//备注  '
               +'    from  #TMpSaleInvoice                                              '
               +'   where  TotalTaxAmountC>0                                            '
               +'   group by InvoiceNo,CurrencyCode,CurrencyName,CurrencyFlag,ExchangeRate,InvoiceArKmCode,CustomerCode                     '
               +'                                                                       '
               +'   union  all                                                             '
               +'   select InvoiceNo,  '//发票号                                         '
               +'          @SaleTaxKmCode as KmCode,  '//应交税金'//销项税科目代码        '
               +'          CurrencyCode, '
               +'          CurrencyName, '
               +'          CurrencyFlag, '
               +'          ExchangeRate,  '
{               +'          Case when Sum(TotalTax)<0 then -Sum(TotalTax)                '
               +'               else 0                                                  '
               +'          end as FDebitAmount,'//原币借方金额                           '
               +'          Case when Sum(TotalTax)>0 then Sum(TotalTax)                 '
               +'               else 0                                                  '
               +'          end as FCreditAmount,         '//原币贷方金额                 '
               +'          case when Sum(TotalTaxC)<0 then -Sum(TotalTaxC)              '
               +'               else 0                                                  '
               +'          end  as DebitAmount,'//本位币借方金额                         '
               +'          case when Sum(TotalTaxC)>0 then Sum(TotalTaxC)               '
               +'               else 0                                                  '
               +'          end as CreditAmount,             '//本位币贷方金额            '
}
               +'          0.0 as FDebitAmount,'//原币借方金额                           '
               +'          Sum(TotalTax) as FCreditAmount,         '//原币贷方金额                 '
               +'          0.0  as DebitAmount,'//本位币借方金额                         '
               +'          Sum(TotalTaxC) as CreditAmount,             '//本位币贷方金额            '

               +'          ''销售发票'', '
               +'          0,'
               +'          ''客户：''+CustomerCode+'' 发票号：''+InvoiceNo as RemArk'//备注  '
               +'    from  #TMpSaleInvoice                                              '
               +'   where  TotalTaxAmountC>0                                            '
               +'   group by InvoiceNo,CurrencyCode,CurrencyName,CurrencyFlag,ExchangeRate,CustomerCode                                     '
               +'                                                                       ';
     SqlText := SqlText
               +'   union  all                                                             '
               +'  select InvoiceNo,  '//发票号                                          '
               +'         case when BillTypeCode=''0206'' then case when InvoiceType=2 then ExportTradeArKmCode '            
               +'                                                  else GenerallyInvoiceArKmCode  '                                  
               +'                                             end                  '
               +'              else GenerallyInvoiceArKmCode                       '
               +'         end as KmCode,  '//主营业务收入科目代码                   '   
               +'         CurrencyCode,                                            '
               +'         CurrencyName,                                            '
               +'         CurrencyFlag,                                            '
               +'         ExchangeRate,                                            '
{               +'         Case when Convert(decimal(20,2),Sum(TotalTaxAmount))-Convert(decimal(20,2),Sum(TotalTax))<0 then '
               +'              -Convert(decimal(20,2),Sum(TotalTaxAmount))-Convert(decimal(20,2),Sum(TotalTax)) '      
               +'              else 0                                              '                                     
               +'         end as FDebitAmount,'//原币借方金额                       '                                     
               +'         Case when Convert(decimal(20,2),Sum(TotalTaxAmount))-Convert(decimal(20,2),Sum(TotalTax))>0 then '
               +'              Convert(decimal(20,2),Sum(TotalTaxAmount))-Convert(decimal(20,2),Sum(TotalTax)) '         
               +'              else 0                                               '                                    
               +'         end as FCreditAmount,         '//原币贷方金额              '                                    
               +'         case when Convert(decimal(20,2),Sum(TotalTaxAmountC))-Convert(decimal(20,2),Sum(TotalTaxC))<0 then '
               +'             -Convert(decimal(20,2),Sum(TotalTaxAmountC))-Convert(decimal(20,2),Sum(TotalTaxC)) '  
               +'              else 0                                               '                                    
               +'         end  as DebitAmount,'//本位币借方金额                      '                                    
               +'         case when Convert(decimal(20,2),Sum(TotalTaxAmountC))-Convert(decimal(20,2),Sum(TotalTaxC))>0 then '
               +'               Convert(decimal(20,2),Sum(TotalTaxAmountC))-Convert(decimal(20,2),Sum(TotalTaxC)) '     
               +'              else 0                                               '                                    
               +'         end as CreditAmount,             '//本位币贷方金额         '                                    
}
               +'         0.0 as FDebitAmount,'//原币借方金额                       '                                     
               +'         Convert(decimal(20,2),Sum(TotalTaxAmount))-Convert(decimal(20,2),Sum(TotalTax)) '         
               +'          as FCreditAmount,         '//原币贷方金额              '                                    
               +'         0.0  as DebitAmount,'//本位币借方金额                      '                                    
               +'         Convert(decimal(20,2),Sum(TotalTaxAmountC))-Convert(decimal(20,2),Sum(TotalTaxC)) '     
               +'          as CreditAmount,             '//本位币贷方金额         '                                    

               +'         ''销售发票'',                                               '
               +'         0,                                                        '
               +'         ''客户：''+CustomerCode+'' 发票号：''+InvoiceNo as RemArk'//备注  '                                 
               +'   from  #TMpSaleInvoice                                              '                                 
               +'  where  TotalTaxAmountC>0                                            '                                 
               +'  group by InvoiceNo,BillTypeCode,CurrencyCode,CurrencyName,CurrencyFlag,ExchangeRate,InvoiceType,CustomerCode,ExportTradeArKmCode,GenerallyInvoiceArKmCode '                     
               +'   /***********************************************************************************************/    '
               +'   ';//二、销售回款：                                                                                     ';;
    SqlText := SqlText
               +'   union  all                                                                                              '
               +'   select GatheringNo,                                                                                  '
               +'          GatheringKmCode as KmCode, '//银行存款科目代码                                                     '
               +'          CurrencyCode, '
               +'          CurrencyName, '
               +'          CurrencyFlag, '
               +'          ExchangeRate,   '
{               +'          Case when TotalTaxAmount>0 then TotalTaxAmount                                                '
               +'               else 0                                                                                   '
               +'          end as FDebitAmount, '//原币借方金额                                                           '
               +'          case when TotalTaxAmount<0 then -TotalTaxAmount                                               '
               +'               else 0                                                                                   '
               +'          end as FCreditAmount,           '//原币贷方金额                                                '
               +'          case when TotalTaxAmountC>0 then TotalTaxAmountC                                              '
               +'               else 0                                                                                   '
               +'          end as DebitAmount ,    '//本位币借方金额                                                      '
               +'          case when TotalTaxAmountC<0 then -TotalTaxAmountC                                             '
               +'               else 0                                                                                   '
               +'          end as CreditAmount,       '//本位币贷方金额                                                   '
}
               +'          TotalTaxAmount as FDebitAmount, '//原币借方金额                                                           '
               +'          0.0 as FCreditAmount,           '//原币贷方金额                                                '
               +'          TotalTaxAmountC                                              '
               +'          as DebitAmount ,    '//本位币借方金额                                                      '
               +'          0.0 as CreditAmount,       '//本位币贷方金额                                                   '

               +'          ''销售回款'','
               +'          1,'
               +'          ''客户：''+CustomerCode+'' 收款序号：''+GatheringNo                                               '
               +'   from  #TmpGathering                                                                                  '
               +'   union  all                                                                                              '
               +'   select GatheringNo,                                                                                  '
               +'          InvoiceArKmCode as KmCode, '//收款科目代码                                            '
               +'          CurrencyCode, '
               +'          CurrencyName, '
               +'          CurrencyFlag, '
               +'          ExchangeRate, '
{               +'          Case when TotalTaxAmount<0 then -TotalTaxAmount                                               '
               +'               else 0                                                                                   '
               +'          end as FDebitAmount, '//原币借方金额                                                           '
               +'          case when TotalTaxAmount>0 then TotalTaxAmount                                                '
               +'               else 0                                                                                   '
               +'          end as FCreditAmount,           '//原币贷方金额                                                '
               +'          case when TotalTaxAmountC<0 then -TotalTaxAmountC                                             '
               +'               else 0                                                                                   '
               +'          end as DebitAmount ,    '//本位币借方金额                                                      '
               +'          case when TotalTaxAmountC>0 then TotalTaxAmountC                                              '
               +'               else 0                                                                                   '
               +'          end as CreditAmount,       '//本位币贷方金额                                                   '
}
               +'          0.0 as FDebitAmount, '//原币借方金额                                                           '
               +'          TotalTaxAmount as FCreditAmount,           '//原币贷方金额                                                '
               +'          0.0 as DebitAmount ,    '//本位币借方金额                                                      '
               +'          TotalTaxAmountC as CreditAmount,       '//本位币贷方金额                                                   '

               +'          ''销售回款'','
               +'          1,'
               +'          ''客户：''+CustomerCode+'' 收款序号：''+GatheringNo                                               '
               +'   from  #TmpGathering                                                                                  '
               +'                                                                                                        '
               +'                                                                                                        '
               +'   /***********************************************************************************************/    '
               +'   drop table #TMpSaleInvoice                                                                           '
               +'   drop table #TmpGathering                                                                             ';
  end                                                                                                                    
  else  
  If UpperCase(Param1)='Ap' then 
  begin                                                                                                                
    SqlText := SqlText 
             +' DeclAre @PurchaseTaxkmCode varchAr(30),                      '
             +'           @ErrPurchaseRangeKmCode varchAr(30)                '
             +'   select @PurchaseTaxKmCode=ParamValueC from Gl_Param        '
             +'   where ParamCode=''PurchaseTaxkmCode''                        '
             +'   select @ErrPurchaseRangeKmCode=ParamValuec from Gl_Param   '
             +'   where ParamCode=''ErrPurchaseRangeKmCode''  '
             +'                                             '                                                            
             +' select InvInBillLine.InvBillId,            '
             +'        InvInBill.InvBillNo,                '
             +'        InvInBillLine.InvBillLineNo,        '
             +'        InvInBill.InvBillWhChCk,            '
             +'        InvInBill.BillTypeCode,             '