Step 1 :
Create New Page And Create Classic Report
Create New Item : P158_AIREXP_MST_ID
Query Condition :
from AIREXPORT_MST
and ID = :P158_AIREXP_MST_ID
Attributes :
Template : Value Attribute Pairs-Column
Create Button : COPY_BOOKING
Create Processing :
copy_booking(:P158_AIREXP_MST_ID);
When Button Pressed : COPY_BOOKING
DATABASE PROCEDURES :
create or replace procedure copy_booking (in_AirMstID number)
is
v_AirMstID number;
v_ST_REF_ID number;
v_booking_no varchar2(256);
v_airport_dest number;
v_setup_party_com_id number;
begin
select AIRPORT_DESTI, SETUP_PARTY_COM_ID into v_airport_dest, v_setup_party_com_id from AIREXPORT_MST where id = in_AirMstID;
SELECT NVL(MAX(ID),1000)+1 INTO v_AirMstID FROM AIREXPORT_MST;
select UPPER(GET_PARTY_COM_PREFIX(v_setup_party_com_id))||'-'||UPPER(get_airport_DTL('C',v_airport_dest))||'-'|| v_AirMstID into v_booking_no from dual;
insert into AIREXPORT_MST (ID, SETUP_PARTY_ORG_ID, SETUP_PARTY_COM_ID, ORIGIN_BANK,
SETUP_BUYING_HOUSE_ID, AGREEMENT_TYPE, SHIPPING_METHOD, BUYER_ID, DESTINATION_BANK,
CF_AGENT_NAME, PAYMENT_INST, AIRPORT_ORIGIN, AIRPORT_DESTI, TERMS_DELIVERY, COUNTRY_DESTI,
HAND_OVER_DATE, EARLIEST, LATEST, INSTRUCTION, ATTACHMENT, STATUS, COM_ID, CREATE_BY,
CREATE_DATE, UPDATE_BY, UPDATE_DATE, BOOKING_ID, SETUP_EMPLOYEES_ID, BOOKING_DATE, REGISTERED_BY,
REGISTERED_DATE, REGISTERED, BOOKED_BY, BOOKED_DATE, BOOKED, SHIPPING_MARK, GOODS_DESCRIPTION,
INTERNAL_REFERENCE, DELIVERY_ADDRESS, CUSTOMER_NOTES, CARRIER, CLOSING_DATE, PICKUP_DATE,
ETA_FINAL_DATE, GOODS_RECEIPT_DATE, PLACE_OF_RECEIPT_CITY, AIRPORT_OF_LOADING, AIRPORT_OF_DISCHARGE,
FINAL_DESTINATION_CITY, ADD_ATTACHMENT, FORWARDERS_NOTE, EDD_DATE, ORIGIN_BANK_BRANCH,
DESTINATION_BANK_BRANCH, INVOICE_STATUS, INVOICE_BY, INVOICE_DATE, EDD_TIME, PLACE_OF_RECEIPT,
PREFER_CARRIER, BOOKING_DELIVERY_ADDRESS, SHIPPER_WISHES_RELEASE_CITY, CITY_DESTI)
select v_AirMstID, SETUP_PARTY_ORG_ID, SETUP_PARTY_COM_ID, ORIGIN_BANK,
SETUP_BUYING_HOUSE_ID, AGREEMENT_TYPE, SHIPPING_METHOD, BUYER_ID, DESTINATION_BANK,
CF_AGENT_NAME, PAYMENT_INST, AIRPORT_ORIGIN, AIRPORT_DESTI, TERMS_DELIVERY, COUNTRY_DESTI,
HAND_OVER_DATE, EARLIEST, LATEST, INSTRUCTION, ATTACHMENT, STATUS, COM_ID, CREATE_BY,
CREATE_DATE, UPDATE_BY, UPDATE_DATE, v_booking_no, SETUP_EMPLOYEES_ID, BOOKING_DATE, REGISTERED_BY,
REGISTERED_DATE, REGISTERED, BOOKED_BY, BOOKED_DATE, BOOKED, SHIPPING_MARK, GOODS_DESCRIPTION,
INTERNAL_REFERENCE, DELIVERY_ADDRESS, CUSTOMER_NOTES, CARRIER, CLOSING_DATE, PICKUP_DATE,
ETA_FINAL_DATE, GOODS_RECEIPT_DATE, PLACE_OF_RECEIPT_CITY, AIRPORT_OF_LOADING, AIRPORT_OF_DISCHARGE,
FINAL_DESTINATION_CITY, ADD_ATTACHMENT, FORWARDERS_NOTE, EDD_DATE, ORIGIN_BANK_BRANCH,
DESTINATION_BANK_BRANCH, INVOICE_STATUS, INVOICE_BY, INVOICE_DATE, EDD_TIME, PLACE_OF_RECEIPT,
PREFER_CARRIER, BOOKING_DELIVERY_ADDRESS, SHIPPER_WISHES_RELEASE_CITY, CITY_DESTI from AIREXPORT_MST where id = in_AirMstID;
Insert into AIREXPORT_NOTIFY (ID, AIREXPORT_ID, SETUP_PARTY_COM_ID, STATUS, COM_ID, CREATE_BY, CREATE_DATE, UPDATE_BY, UPDATE_DATE)
select AIREXPORT_NOTIFY_seq.nextval, v_AirMstID, SETUP_PARTY_COM_ID, STATUS, COM_ID, CREATE_BY, CREATE_DATE, UPDATE_BY, UPDATE_DATE from
AIREXPORT_NOTIFY where AIREXPORT_ID = in_AirMstID;
Insert into AIREXPORT_GOODS_INFO (ID, AIREXPORT_ID, PIECES, TYPE, PO, STYLE, COLOR, SO, UNIT, LENGTH, WIDTH, HEIGHT, CBM, UNIT_PRICE,
CUR, QTY, WEIGHT, WEIGHT_UNIT, VM_WEIGHT, CHA_WEIGHT, COMPANY, STATUS, COM_ID, CREATE_BY, CREATE_DATE, UPDATE_BY, UPDATE_DATE,
HS_CODE, QTY_PER_PACK, QTY_PCS, RATE_PACK, AMOUNT, VOL_MET_WEIGHT_INC, VOL_MET_WEIGHT_CM, GOODS_DESCRIPTION)
select TEST_SEQ_2.nextval, v_AirMstID, PIECES, TYPE, PO, STYLE, COLOR, SO, UNIT, LENGTH, WIDTH, HEIGHT, CBM, UNIT_PRICE, CUR, QTY,
WEIGHT, WEIGHT_UNIT, VM_WEIGHT, CHA_WEIGHT, COMPANY, STATUS, COM_ID, CREATE_BY, CREATE_DATE, UPDATE_BY, UPDATE_DATE, HS_CODE,
QTY_PER_PACK, QTY_PCS, RATE_PACK, AMOUNT, VOL_MET_WEIGHT_INC, VOL_MET_WEIGHT_CM, GOODS_DESCRIPTION from
AIREXPORT_GOODS_INFO where AIREXPORT_ID = in_AirMstID;
FOR x IN ( select AIREXPORT_ID, TYPE, REF_NUMBER, REF_DATE, STATUS, COM_ID, CREATE_BY, CREATE_DATE, UPDATE_BY, UPDATE_DATE
from AIREXPORT_ST_REF where AIREXPORT_ID = in_AirMstID)
LOOP
SELECT NVL(MAX(ID),3000)+1 INTO v_ST_REF_ID FROM AIREXPORT_ST_REF;
Insert into AIREXPORT_ST_REF (ID, AIREXPORT_ID, TYPE, REF_NUMBER, REF_DATE, STATUS, COM_ID, CREATE_BY, CREATE_DATE, UPDATE_BY, UPDATE_DATE) values
(v_ST_REF_ID, v_AirMstID, x.TYPE, x.REF_NUMBER, x.REF_DATE, x.STATUS, x.COM_ID, x.CREATE_BY, x.CREATE_DATE, x.UPDATE_BY, x.UPDATE_DATE) ;
END LOOP;
end;
Step 2 :
Go to IR Report Query and add null Column (COPY)
Example :
select ID,
SETUP_PARTY_ORG_ID,
SETUP_PARTY_COM_ID,
BOOKING_ID,
NULL COPY
from AIREXPORT_MST
Step 2 :
Select COPY Column
Type : Link
Target :
Link Text :
<span class="fa fa-copy" aria-hidden="true"></span>