1. get_pk
declare
v1 number;
v2 number;
begin
select to_number(count(1))+1 into v2 from IT_ITEM_RCV_MAST where to_date(RCV_DT)=to_date(sysdate);
if v2>1 then
select distinct('RCV-NUM-'||''||to_char(sysdate,'RRMMDD'))||''|| v2 into :P30_RCV_NO from SYS.DUAL;
else
:P30_RCV_NO:='RCV-NUM-'||''||to_char(sysdate,'RRMMDD')||''||1;
end if;
end;
select nvl(max(to_number(SL_NO)),0)+1 into :P30_SL_NO from IT_ITEM_RCV_MAST;
-----------
--Sequence
CREATE SEQUENCE "IT"."IT_ITEM_RCV_CHILD_SLNO"
MINVALUE 1
MAXVALUE 99999999999999999999999999
INCREMENT BY 1
START WITH 23 NOCACHE NOORDER CYCLE NOKEEP NOSCALE GLOBAL ;
2. Direct Insert master Table
3. Insert Detail Table
if :P30_RCV_NO is not null and :ITEM_CD is not null and :QTY is not null then
insert into IT_ITEM_RCV_CHILD
(
RCV_NO
, SLNO
, ITEM_CD
, BRAND
, ITEM_SERIAL
, QTY
, UNIT_COST
, MAF_DT
, EXPIR_DT
, WARRNTY
, ITEM_DESC
, MODEL_NM
, RCV_DTLNO
)
values
(
:P30_RCV_NO
, it.IT_ITEM_RCV_CHILD_SLNO.nextval
, :ITEM_CD
, :BRAND
, :ITEM_SERIAL
, :QTY
, :UNIT_COST
, :MAF_DT
, :EXPIR_DT
, :WARRNTY
, :ITEM_DESC
, :MODEL_NM
, :RCV_DTLNO
);
else
null;
end if;
4. Process form update
begin
if :RCV_NO is not null then
Update IT_ITEM_RCV_CHILD
set
SLNO=:SLNO
,ITEM_CD=:ITEM_CD
,BRAND=:BRAND
,ITEM_SERIAL=:ITEM_SERIAL
,QTY=:QTY
,UNIT_COST=:UNIT_COST
,MAF_DT=:MAF_DT
,EXPIR_DT=:EXPIR_DT
,WARRNTY=:WARRNTY
,ITEM_DESC=:ITEM_DESC
,MODEL_NM=:MODEL_NM
,RCV_DTLNO=:RCV_DTLNO
where
RCV_NO=:P30_RCV_NO;
end if;
End;
5. New Insert
if :SLNO is null and :P30_REQ_NO is not null then
insert into IT_ITEM_RCV_CHILD
(
RCV_NO
, SLNO
, ITEM_CD
, BRAND
, ITEM_SERIAL
, QTY
, UNIT_COST
, MAF_DT
, EXPIR_DT
, WARRNTY
, ITEM_DESC
, MODEL_NM
, RCV_DTLNO
)
values
(
:P30_RCV_NO
, it.IT_ITEM_RCV_CHILD_SLNO.nextval
, :ITEM_CD
, :BRAND
, :ITEM_SERIAL
, :QTY
, :UNIT_COST
, :MAF_DT
, :EXPIR_DT
, :WARRNTY
, :ITEM_DESC
, :MODEL_NM
, :RCV_DTLNO
);
else
null;
end if;
No comments:
Post a Comment