create or replace TYPE DBT_TRAN_SETUP AS OBJECT
(
PA_YYYYMM		NUMBER(6),
COMP_CODE		NUMBER(2),
EMP_TYPE		NUMBER(2),
CAT_CODE		NUMBER(4),
DESIG_CODE		NUMBER(8),
EMP_CODE		NUMBER(10),
SIMU_TYPE		VARCHAR2(10),
TRAN_CODE    		VARCHAR2(4),
PA_DOCTYPE		VARCHAR2(4),
ACTUAL_DATA		VARCHAR2(1),
VARIANT_TYPE 		VARCHAR2(10),
TRAN_VERSION   		NUMBER(2),  
TRAN_TYPE     		VARCHAR2(1),
TRAN_MODE       	VARCHAR2(1),  
TRAN_PERIOD    		VARCHAR2(1),
PRIORITY       	  	NUMBER(3),
ROUND_FORMULA		VARCHAR2(30),
CONTROL_TYPE		VARCHAR2(20),
PA_CODE        		NUMBER(3),
PAYABLE_PA_CODE		NUMBER(3),
FP_FLAG       		CHAR(1),
RATE              	NUMBER(10,2),
FORMULA			VARCHAR2(30),
FIXRATE_BASIS           VARCHAR2(10),     
FACTOR_FORMULA    	VARCHAR2(30),
PCTBASIS_FORMULA  	VARCHAR2(30),
UPPER_LIMIT       	NUMBER(10,2),
PF_FLAG        		VARCHAR2(1),
ESI_FLAG		VARCHAR2(1),       
LDAC_CODE      		VARCHAR2(30),
SAL_YYYYMM		NUMBER(6),
ARR_FLAG		VARCHAR2(1),
PDAYS			NUMBER(4,2),
AIDAYS                  NUMBER(4,2),
FACTOR                  NUMBER(3),
PAY_BASIS		VARCHAR2(10),
PR_FLAG			VARCHAR2(1),
BASIC			NUMBER(10,2),
BADLI_TYPE		VARCHAR2(1),
BADLI_TRAN		VARCHAR2(1),
INCENTIVE		NUMBER(10,2),
ERROR_FLAG		CHAR(1),
ERROR_MESSAGE		VARCHAR2(100),
ins_date	date		,
ins_uid		varchar2(20)	,
udt_date	date		,
udt_uid		varchar2(20)	
)
;

CREATE TABLE temp_tran_setup OF DBT_TRAN_SETUP
STORAGE
(
	INITIAL 	4 K
	NEXT		2 K
	PCTINCREASE 	0
);
alter table temp_tran_setup add constraint tts_pk 
primary key (pa_yyyymm, comp_code, emp_type, desig_code, emp_code,simu_type, tran_code);


CREATE OR REPLACE TYPE DBT_SALARY_TRAN AS OBJECT
(
pa_yyyymm	number(6),
comp_code	number(2),
emp_type	number(2),
desig_code	number(8),
sal_yyyymm	number(6),
emp_code	number(10),
simu_type	varchar2(10),
pa_doctype	varchar2(4),
tran_code	varchar2(4),
actual_data	varchar2(1),
badli_tran	VARCHAR2(1),
arr_flag 	varchar2(1),
tran_type	varchar2(1),
tran_mode	varchar2(1),
tran_period	varchar2(1),
variant_type	varchar2(10),
tran_version	number(2),
control_type	varchar2(20),
priority	number(3),
pa_code		number(3),
payable_pa_code	number(3),
rate		number(10,2),
amount		number(10,2) ,
round_Factor	number(4,2)  ,
ins_date	date		,
ins_uid		varchar2(20)	,
udt_date	date		,
udt_uid		varchar2(20),
ARR_YYYYMM number(8)	
);


create table salary_tran_simu of dbt_salary_tran
STORAGE
(
	INITIAL 	4 K
	NEXT		2 K
	PCTINCREASE 	0
);
alter table salary_tran_simu add
constraint sts_pk primary key 
(pa_yyyymm, comp_code, emp_type, desig_code, sal_yyyymm, emp_code, simu_type, tran_code);
 
CREATE TABLE TEMP_SALARY_TRAN OF DBT_SALARY_TRAN
STORAGE
(
	INITIAL 	4 K
	NEXT		2 K
	PCTINCREASE 	0
);
alter table temp_salary_tran add
constraint tst_UK UNIQUE 
(pa_yyyymm, comp_code, emp_type, desig_code, sal_yyyymm, emp_code, simu_type, tran_code);


create table salary_tran
(
pa_yyyymm	number(6),
comp_code	number(2),
emp_type	number(2),
desig_code	number(8),
sal_yyyymm	number(6),
emp_code	number(10),
tran_code	varchar2(4),
--pk---
pa_doctype	varchar2(4) 	not null,
badli_tran	varchar2(1) 	not null, -- badli check (Y/N)
arr_flag 	varchar2(1) 	not null, --arr flag (Y/N)
tran_type	varchar2(1) 	not null,
tran_mode	varchar2(1) 	not null,
tran_period	varchar2(1)	not null,
variant_type	varchar2(10)	not null,
tran_version	number(2)	not null,
control_type	varchar2(20)	not null,
priority	number(3)	not null,
pa_code		number(3)	not null,
payable_pa_code number(3)	not null,
rate		number(10,2)	not null,
amount		number(10,2) 	default 0 not null ,
round_Factor	number(4,2)  	default 0 not null ,
ins_date		date		,
ins_uid		varchar2(20)		,
udt_date		date		,
udt_uid		varchar2(20)	,
constraint saltran_pk primary key 
(pa_yyyymm, comp_code, emp_type, desig_code, sal_yyyymm, emp_code, tran_code),
constraint saltran_fk foreign key (pa_yyyymm, comp_code, emp_type, desig_code, sal_yyyymm, emp_code)
references salary (pa_yyyymm, comp_code, emp_type, desig_code, sal_yyyymm, emp_code)
)
STORAGE
(
	INITIAL 	4 K
	NEXT		2 K
	PCTINCREASE 	0
);





create or replace type dbt_salary as object
(
emp_code	number(10),
pa_yyyymm	number(6),
comp_code	number(2),
emp_type	number(2),
desig_code	number(8),
sal_yyyymm	number(6),
pa_doctype	varchar2(4),
simu_type	varchar2(10),
actual_data	varchar2(1),
arr_flag 	varchar2(1), 
pay_basis	varchar2(10),
pr_flag		varchar2(1),
badli_type	varchar2(1),
basic		number(10,2),
pdays		number(4,2),
aidays          number(4,2),
factor          number(3),
earned_basic	number(10,2),
pf_wages	number(10,2),
esi_wages	number(10,2),
tot_allowance	number(10,2),
tot_emp_scntr	number(10,2),
tot_emp_vcntr	number(10,2),
tot_emplr_scntr	number(10,2),
tot_vpmt	number(10,2),
tot_ded		number(10,2),
gross_sal	number(10,2),
net_sal		number(10,2),
payable_sal	number(10,2),
 round_factor	number(6,2),
comp_cost	number(10,2),
ins_date	date		,
ins_uid		varchar2(20)	,
udt_date	date		,
udt_uid		varchar2(20)	
);

create table salary_simu of dbt_salary
STORAGE
(
	INITIAL 	4 K
	NEXT		2 K
	PCTINCREASE 	0
);
alter table salary_simu add
constraint sals_pk primary key 
(pa_yyyymm, comp_code, emp_type, desig_code, sal_yyyymm, emp_code, simu_type);

create table salary
(
pa_yyyymm	number(6)	,
comp_code	number(2)	,
emp_type	number(2)	,
desig_code	number(8)	,
sal_yyyymm	number(6)	,
emp_code	number(10)	,
pa_doctype	varchar2(4) 	not null,
arr_flag 	varchar2(1) 	not null,
pay_basis	varchar2(10) 	not null,
pr_flag		varchar2(1)	not null,
badli_type	varchar2(1) 	not null,
basic		number(10,2) 	default 0 not null,
pdays		number(4,2) 	default 0 not null,
earned_basic	number(10,2) 	default 0 not null,
pf_wages	number(10,2) 	default 0 not null,
esi_wages	number(10,2) 	default 0 not null,
tot_allowance	number(10,2) 	default 0 not null,
tot_emp_scntr	number(10,2) 	default 0 not null,
tot_emp_vcntr	number(10,2) 	default 0 not null,
tot_emplr_scntr	number(10,2) 	default 0 not null,
tot_vpmt	number(10,2) 	default 0 not null,
tot_ded		number(10,2) 	default 0 not null,
gross_sal	number(10,2) 	default 0 not null,
net_sal		number(10,2) 	default 0 not null,
payable_sal	number(10,2) 	default 0 not null,
round_factor	number(6,2) 	default 0 not null,
comp_cost	number(10,2) 	default 0 not null,
ins_date	date		,
ins_uid		varchar2(20)	,
udt_date	date		,
udt_uid		varchar2(20)	,
aidays          number(4,2)     default 0 not null,
factor          number(3)       default 0 not null,
constraint sal_pk primary key  
(pa_yyyymm, comp_code, emp_type, desig_code, sal_yyyymm, emp_code)
)
STORAGE
(
	INITIAL 	4 M
	NEXT		2 M
	PCTINCREASE 	0
);


create table salary_log
(
yyyymm		number(6),
comp_code	number(2),
emp_type	number(2),
emp_code	number(10),
simu_type	varchar2(10),
ACTUAL_DATA	VARCHAR2(1),
tran_code	varchar2(4),
process_date	date,
process_uid	varchar2(20),
error_message	varchar2(100)
--constraint salary_log_UK UNIQUE (yyyymm, comp_code, emp_type, emp_code,tran_code, simu_type)
);