CREATE OR REPLACE procedure SEAEXPORT_MST_DTL_SAVE
(
IN_BOOKING_ID IN VARCHAR2 ,
IN_BOOKING_DATE IN DATE,
IN_SETUP_PARTY_ORG_ID IN NUMBER ,
IN_SETUP_PARTY_COM_ID IN NUMBER,
IN_ORIGIN_BANK_MST_ID IN NUMBER ,
IN_ORIGIN_BANK_BRANCH IN NUMBER,
IN_SETUP_BUYING_HOUSE_ID IN NUMBER,
IN_AGREEMENT_TYPE IN VARCHAR2 ,
IN_SHIPPING_METHOD IN VARCHAR2 ,
IN_BUYER_ID IN NUMBER ,
IN_STATUS IN NUMBER ,
IN_COM_ID IN NUMBER,
IN_CREATE_BY IN NUMBER,
IN_UPDATE_BY IN NUMBER,
)
is
V_SEAEXPORT_MST_ID number ;
V_BOOKING_ID VARCHAR2(50);
errorcode varchar2(100);
errormessage varchar2(100);
Begin
select nvl(max(id),1000)+1 into V_SEAEXPORT_MST_ID from SEAEXPORT_MST;
select 'TEC-'|| V_SEAEXPORT_MST_ID into V_BOOKING_ID from dual; --- For Booking_id Auto ganerate
insert into SEAEXPORT_MST (
ID,BOOKING_ID,BOOKING_DATE,SETUP_PARTY_ORG_ID,SETUP_PARTY_COM_ID,ORIGIN_BANK_MST_ID,ORIGIN_BANK_BRANCH, SETUP_BUYING_HOUSE_ID,AGREEMENT_TYPE,SHIPPING_METHOD,BUYER_ID,STATUS, COM_ID, CREATE_BY, UPDATE_BY,CREATE_DATE,UPDATE_DATE))
VALUES (
V_SEAEXPORT_MST_ID, V_BOOKING_ID, IN_BOOKING_DATE, IN_SETUP_PARTY_ORG_ID, IN_SETUP_PARTY_COM_ID, IN_ORIGIN_BANK_MST_ID,IN_ORIGIN_BANK_BRANCH, IN_SETUP_BUYING_HOUSE_ID,IN_AGREEMENT_TYPE, IN_SHIPPING_METHOD, IN_BUYER_ID,IN_STATUS, IN_COM_ID, IN_CREATE_BY, IN_UPDATE_BY,SYSDATE,SYSDATE);
---- INSERT DTL--1
insert into SEAEXPORT_NOTIFY (
ID, SEAEXPORT_MST_ID, SETUP_PARTY_COM_ID,CREATE_BY, CREATE_DATE, UPDATE_BY, UPDATE_DATE)
SELECT ID, V_SEAEXPORT_MST_ID, SETUP_PARTY_COM_ID,CREATE_BY, CREATE_DATE, UPDATE_BY, UPDATE_DATE
FROM SEAEXPORT_NOTIFY_TEMP
WHERE CREATE_BY = IN_CREATE_BY;
--INSERT DTL--2
INSERT INTO SEAEXPORT_ST_REF (
CREATE_BY, CREATE_DATE, ID, REF_DATE, REF_NUMBER, SEAEXPORT_MST_ID, TYPE, UPDATE_BY, UPDATE_DATE)
SELECT CREATE_BY, CREATE_DATE, ID, REF_DATE, REF_NUMBER, V_SEAEXPORT_MST_ID, TYPE, UPDATE_BY, UPDATE_DATE
FROM SEAEXPORT_ST_REF_TEMP
WHERE CREATE_BY = IN_CREATE_BY;
---- INSERT DTL--3
INSERT INTO SEAEXPORT_GOODS_INFO (
CBM, COLOR, CREATE_BY, CREATE_DATE, CUR, GOODS_DESCRIPTION, HEIGHT, ID, LENGTH, PIECES, PO, QTY, SEAEXPORT_MST_ID, SO, STYLE, TYPE)
SELECT CBM, COLOR, CREATE_BY, CREATE_DATE, CUR, GOODS_DESCRIPTION, HEIGHT, ID, LENGTH, PIECES, PO, QTY, V_SEAEXPORT_MST_ID, SO, STYLE, TYPE
FROM SEAEXPORT_GOODS_INFO_TEMP
WHERE CREATE_BY = IN_CREATE_BY;
--- Delete TEMP Table DATA
delete from SEAEXPORT_NOTIFY_TEMP where CREATE_BY = IN_CREATE_BY;
delete from SEAEXPORT_ST_REF_TEMP where CREATE_BY = IN_CREATE_BY;
delete from SEAEXPORT_GOODS_INFO_TEMP where CREATE_BY = IN_CREATE_BY;
EXCEPTION
WHEN OTHERS THEN
null;
commit;
end;
/
-------------------------------------------------------------------------------------------
---> GO TO temp Details table (as like SEAEXPORT_GOODS_INFO_TEMP , SEAEXPORT_NOTIFY_TEMP)
CREATE_BY Default GUSER_ID
CREATE_DATE Default sysdate
--> GO TO APEX Page design and create Process.......
Process name : CALLING_PROCESS
TYPE : Execute Code
PL/SQL Code :
When Button Pressed : Create (By default Button )
Look at the Interactive Grid (Details table) Process... (Temp & Main Table)
