/*
   شنبه, 8 خرداد 1400 ساعت 13:00:00 ب.ظ
   User: Ali
   Server: TAHERIAN
   Database: ATNDB
   Application: 
*/

Use ATNDBTest
Go
/*=============================================================================================================*/
-- حـذف همه جداول احتمالی قبلی
If Exists (Select * From INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' And TABLE_SCHEMA = 'PAY' And TABLE_NAME = 'LoanPaybackPlanAccCode') Drop Table PAY.LoanPaybackPlanAccCode
If Exists (Select * From INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' And TABLE_SCHEMA = 'PAY' And TABLE_NAME = 'LoanPaybackPlanPayment') Drop Table PAY.LoanPaybackPlanPayment
If Exists (Select * From INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' And TABLE_SCHEMA = 'PAY' And TABLE_NAME = 'LoanPaybackPlanDetail') Drop Table PAY.LoanPaybackPlanDetail
If Exists (Select * From INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' And TABLE_SCHEMA = 'PAY' And TABLE_NAME = 'LoanPaybackPlanHeader') Drop Table PAY.LoanPaybackPlanHeader
/*=============================================================================================================*/
Create Table PAY.LoanPaybackPlanHeader(
	Serial                            Bigint Identity(1,1) Not Null, 
	Code                              Bigint NULL, ------------------
	LoanTypeRef		                  Bigint NULL, ------------------
	EmployeeRef                       Bigint Not Null, --------------
	AccDocumentHeaderRef			  Bigint NULL, ------------------
	EffectiveDate                     Char(10) Null, ----------------
	InstallmentStartDate              Char(10) Null, ----------------
	InstallmentEndDate                Char(10) Null, ----------------
	InstallmentPeriodDuration         Smallint Null, ----------------
	LoanDurationInMonth               Int Null, ---------------------
	LoanDurationInDay                 Int Null, ---------------------
	LoanAmount		                  Money NULL, -------------------
	WagePercent                       Decimal(7,4) NULL, ------------
	WageAmount                        Money NULL, -------------------
	InterestPercent                   Decimal(7,4) NULL, ------------
	InterestAmount                    Money NULL, -------------------
	FineExemptDuration               Int Null, ----------------------
	FinePercent                      Decimal(7,4) NULL, -------------
	FineAmount                       Money NULL, --------------------
	TotalAmount                       Money NULL, -------------------
	PaidAmount                        Money NULL, -------------------
	RemainAmount                      Money NULL, -------------------
	TotalInstallment				  Int Null, ---------------------
	PaidInstallment		  		      Int Null, ---------------------
	RemainInstallment				  Int Null, ---------------------
	DelayedInstallment				  Int Null, ---------------------
	InstallmentRoundDigit		      Bigint NULL, ------------------
	InstallmentRound_FirstOrLast      Smallint NULL, ----------------
	Comment                           Varchar(300) Null, ------------
	RecorderRef                       Int Null, ---------------------
	RecordDate                        Char(10) Null, ----------------
	EditorRef                         Int Null, ---------------------
	EditDate                          char(10) Null,	-------------
	VerifierRef                       Int Null, ---------------------
	VerificationDate                  Char(10) Null, ----------------
	OfficePeriodRef                   Int Null, ---------------------
	OfficeDetailRef                   Int Null, ---------------------
	RowVersion                        DateTime)
Go
/*=============================================================================================================*/
Create Table PAY.LoanPaybackPlanDetail(
	Serial                   Bigint Identity(1,1) Not Null, 
	LoanPaybackPlanHeaderRef Bigint Not Null, --------------
	MatureDate               Char(10) Null, ----------------
	InstallmentAmount        Money NULL, -------------------
	WageAmount               Money NULL, -------------------
	InterestAmount           Money NULL, -------------------
	FineAmount               Money NULL, -------------------
	TotalAmount              Money NULL, -------------------
	Description              Varchar(300) Null) ------------
Go
/*=============================================================================================================*/
Create Table PAY.LoanPaybackPlanPayment(
	Serial                   Bigint Identity(1,1) Not Null, 
	LoanPaybackPlanDetailRef Bigint Not Null, --------------
	PaidAmount               Money NULL, -------------------
	PayDate                  Char(10) Null, ----------------
	PaymentType				 Smallint Null, ----------------
	AccDocumentHeaderRef	 Bigint NULL) ------------------
Go
/*=============================================================================================================*/
Create Table PAY.LoanPaybackPlanAccCode(
	Serial Bigint Identity(1,1) Not Null,
	LoanPaybackPlanHeaderRef Bigint Not Null,
	LoanPaybackPlanPaymentRef Bigint Null,
	SystemAccCodeDefaultBaseRef Int Not Null,
	DebitAccAdjutantL1CodeBookRef Bigint Null, ------
	DebitAccEntityL1CodeBookRef Bigint Null, --------
	DebitAccEntityL2CodeBookRef Bigint Null, --------
	DebitAccSecondEntityCodeBookRef Bigint Null, ----
	DebitAccThirdEntityCodeBookRef Bigint Null,	-----
	CreditAccAdjutantL1CodeBookRef Bigint Null,	-----
	CreditAccEntityL1CodeBookRef Bigint Null, -------
	CreditAccEntityL2CodeBookRef Bigint Null, -------
	CreditAccSecondEntityCodeBookRef Bigint Null, ---
	CreditAccThirdEntityCodeBookRef Bigint Null) ----
Go
