chalie001
asked on
inserting value in table in oracle form
hi i what to insert value in my screen like this
IN my screen the use will enter value like this
the user will pass this value passed for the screen to appear 1234-12-125-8328 its code is 5890851
Associted_Account Ass_quantity Ass_type End_item_Code Nha_code
8745155258732 23 A 9874166787831 3748177602234
8745-15-525-8732 9874-16-678-7831
after saving the value should display like this
Associted_Account Ass_quantity Ass_type End_item_Code Nha_code
8745-15-525-8732 23 A 9874-16-678-7831 3748-17-760-2234
am only saving in one table ASSOCIATED_Account_ITEM after saving the value will be like o r the value which will be inserted ill be like
Insert into ASSOCIATED_Account_ITEM (code,ASS_code,ASS_QUANTIT Y,ASS_TYPE ,END_ITEM_ code,NHA_c ode) values(5890851,1890855,500 ,'D',28538 26,3857007 );
this is my test data
IN my screen the use will enter value like this
the user will pass this value passed for the screen to appear 1234-12-125-8328 its code is 5890851
Associted_Account Ass_quantity Ass_type End_item_Code Nha_code
8745155258732 23 A 9874166787831 3748177602234
8745-15-525-8732 9874-16-678-7831
after saving the value should display like this
Associted_Account Ass_quantity Ass_type End_item_Code Nha_code
8745-15-525-8732 23 A 9874-16-678-7831 3748-17-760-2234
am only saving in one table ASSOCIATED_Account_ITEM after saving the value will be like o r the value which will be inserted ill be like
Insert into ASSOCIATED_Account_ITEM (code,ASS_code,ASS_QUANTIT
this is my test data
create table account_ITEM_tt
(
code NUMBER(11) not null,
serial_code VARCHAR2(20) not null,
nrm varchar(10),
mcr VARCHAR2(2) default '7' not null
);
alter table account_ITEM_tt
add constraint code_ITEM_PK primary key (code);
-- accNo= mcr + serial_code
Insert into account_ITEM_tt (code,serial_code,mcr,nrm) values (5890851,'1258328','12',1234);
Insert into account_ITEM_tt (code,serial_code,mcr,nrm) values (6853822,'1734937','13',9887);
Insert into account_ITEM_tt (code,serial_code,mcr,nrm) values (7857003,'1602830','14',987);
Insert into account_ITEM_tt (code,serial_code,mcr,nrm) values (8146674,'1875331','13',2344);
5325-14-242-9490 accNo = nrm+mcr+serial_code 8745-15-525-8732
Insert into account_ITEM_tt (code,serial_code,mcr,nrm) values (1890855,'5258732','15',8745);
Insert into account_ITEM_tt (code,serial_code,mcr,nrm) values (2853826,'6787831','16',9874);
Insert into account_ITEM_tt (code,serial_code,mcr,nrm) values (3857007,'7602234','17',3748);
Insert into account_ITEM_tt (code,serial_code,mcr,nrm) values (4146678,'8875635','18',9847);
9874-16-678-7831
create table account_ITEM_ww
(
code NUMBER(11) not null
);
alter table account_ITEM_ww
add constraint code_ITEM_ww_PK primary key (code);
alter table account_ITEM_ww
add constraint FK_ww_code foreign key (code)
references account_ITEM_tt (code)
deferrable initially deferred;
Insert into account_ITEM_ww (code)values(5890851);
Insert into account_ITEM_ww (code)values(6853822);
Insert into account_ITEM_ww (code)values(7857003);
Insert into account_ITEM_ww (code)values(8146674);
Insert into account_ITEM_ww (code)values(1890855);
Insert into account_ITEM_ww (code)values(2853826);
Insert into account_ITEM_ww (code)values(3857007);
Insert into account_ITEM_ww (code)values(4146678);
create table ASSOCIATED_Account_ITEM
(
code NUMBER(11) not null,
ass_code NUMBER(11) not null,
ass_quantity NUMBER(11,2),
ass_type VARCHAR2(1),
end_item_code NUMBER(11),
nha_code NUMBER(11)
);
alter table ASSOCIATED_Account_ITEM
add constraint PK_ASS_ITEM primary key (code,ass_code);
alter table ASSOCIATED_Account_ITEM
add constraint CHK_ASS_ASS_QTY
check (ass_quantity > 0);
alter table ASSOCIATED_Account_ITEM
add constraint CHK_ASS_ASS_code_EIS
check (ass_code <> end_item_code);
alter table ASSOCIATED_Account_ITEM
add constraint CHK_ASS_ASS_code_NHA
check (ass_code <> nha_code);
alter table ASSOCIATED_Account_ITEM
add constraint CHK_ASS_code_ASS_code
check (code <> ass_code);
alter table ASSOCIATED_Account_ITEM
add constraint CHK_ASS_code_EIS
check (code <> end_item_code);
alter table ASSOCIATED_Account_ITEM
add constraint FK_ASS_CII_ASS_code foreign key (ASS_code)
references account_ITEM_ww (code);
alter table ASSOCIATED_Account_ITEM
add constraint FK_ASS_CII_END_ITEM_code foreign key (END_ITEM_code)
references account_ITEM_ww (code);
alter table ASSOCIATED_Account_ITEM
add constraint FK_ASS_CII_NHA_code foreign key (NHA_code)
references account_ITEM_ww (code);
alter table ASSOCIATED_Account_ITEM
add constraint FK_ASS_CII_code foreign key (code)
references account_ITEM_ww (code);
Insert into ASSOCIATED_Account_ITEM (code,ASS_code,ASS_QUANTITY,ASS_TYPE,END_ITEM_code,NHA_code) values(5890851,6853822,500,'D',7857003,8146674);
i pre-insert trigger like this
decalre
cursor get_code is
select ww.code from ccount_ITEM_ww ww,account_ITEM_tt tt
where nrm = substr(:blockName.global_code,1,4)
and mcr = substr(:blockname.global_code,5,2);
and serial_code = substr(:blockname.globalcode,10,4)
and ww.code = tt.code;
cursor get_ass_code is
select ww.code from ccount_ITEM_ww ww,account_ITEM_tt tt
where nrm = substr(:blockName.ass_code,1,4)
and mcr = substr(:blockname.ss_code,5,2);
and serial_code = substr(:blockname.ass_code,10,4)
and ww.code = tt.code;
cursor get_nha_code is
select ww.code from ccount_ITEM_ww ww,account_ITEM_tt tt
where nrm = substr(:blockName.nha_code,1,4)
and mcr = substr(:blockname.nha_code,5,2);
and serial_code = substr(:blockname.nha_code,10,4)
and ww.code = tt.code;
cursor get_end_code is
select ww.code from ccount_ITEM_ww ww,account_ITEM_tt tt
where nrm = substr(:blockName.endcode_code,1,4)
and mcr = substr(:blockname.end_code,5,2);
and serial_code = substr(:blockname.end_code,10,4)
and ww.code = tt.code;
begin
--- how can i put those cursor in one palce so i can do this
for curr in get_code loop
:blockname.codevalue := curr.applicode;
end loop;
for curr in get_ass_code loop
:blockname.ass_code_ent := curr.code;
end loop;
for curr in get_nha_code loop
:blockname.nhacode_ent := curr.code;
end loop;
for curr in get_end_code loop
:blockname.end_code_ent := curr.code;
end loop;
end;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER