Processes
=======
declare
v_mst_id number ;
v_STUFFING_PLAN_ID varchar2(50);
begin
FOR i in 1..apex_application.g_f01.count
LOOP
if apex_application.g_f01(i) is not null then
update SHIP_PLAN_MST set
APPROVE_BY_LG=:GUSER_ID,
APPROVE_DATE_LG=SYSDATE,
APPROVE_STUTAS_LG=1
WHERE ID=apex_application.g_f01(i);
select nvl(max(id),100)+1 into v_mst_id from STUFFING_PLAN_MST ;
select GET_SETUP_COMPANY_PREFIX(:GCOM_ID)||'-STUFFING-'|| v_mst_id INTO v_STUFFING_PLAN_ID FROM dual;
----- insert stuffing master
Insert into STUFFING_PLAN_MST (
ID, STUFFING_PLAN_ID, SHIP_PLAN_MST_ID, SETUP_PARTY_COM_ID, ORIGIN_CITY, DESTI_CITY, PORT_OF_LOADING, PORT_OF_DESTI, NOTE,
SETUP_SEA_CARRIER_ID, CUT_OF_DATE, VESSEL_INFO_ID, FV_NAME, FV_ETA_POL, FV_ETA_DATE_1, FV_ETD_DATE,
FV_VOYAGE_NO, FV_TS_PORT, FV_ETA_DATE_2, MV1_NAME, MV1_ETA_POL, MV1_ETA_DATE_1, MV1_ETD_DATE, MV1_VOYAGE_NO,
MV1_TS_PORT, MV1_ETA_DATE_2, MV2_VESSEL_NAME,MV2_ETA_POL, MV2_ETA_DATE_1, MV2_ETD_DATE, MV2_VOYAGE_NO, MV2_TS_PORT, MV2_ETA_DATE_2, STUFFING_DATE,
STATUS, COM_ID, CREATE_BY, CREATE_DATE)
SELECT v_mst_id, v_STUFFING_PLAN_ID, A.ID, A.SETUP_PARTY_COM_ID, A.ORIGIN_CITY, A.DESTI_CITY, A.PORT_OF_LOADING, A.PORT_OF_DESTI, A.NOTE,
B.SETUP_SEA_CARRIER_ID, B.CUT_OF_DATE, B.VESSEL_INFO_ID, B.FV_NAME, B.FV_ETA_POL, B.FV_ETA_DATE_1, B.FV_ETD_DATE,
B.FV_VOYAGE_NO, B.FV_TS_PORT, B.FV_ETA_DATE_2, B.MV1_NAME, B.MV1_ETA_POL, B.MV1_ETA_DATE_1, B.MV1_ETD_DATE, B.MV1_VOYAGE_NO,
B.MV1_TS_PORT, B.MV1_ETA_DATE_2, B.MV2_VESSEL_NAME, B.MV2_ETA_POL, B.MV2_ETA_DATE_1, B.MV2_ETD_DATE, B.MV2_VOYAGE_NO, B.MV2_TS_PORT, B.MV2_ETA_DATE_2, SYSDATE,
1, :GCOM_ID, :GUSER_ID, SYSDATE
FROM SHIP_PLAN_MST A, SHIP_PLAN_VESSEL B
WHERE A.ID=B.SHIP_PLAN_MST_ID
AND B.APPROVE_STUTAS=1
AND A.ID=apex_application.g_f01(i);
----- insert container
declare
v_con_seq number;
CURSOR cont_data is
SELECT ID, SEQ_STUFFING_PLAN_CONT_ID.NEXTVAL, 3, CONTAINER_TYPE, CAPACITY_CBM,
ALLOCATION, CONTAINER_MODE, FOREIGN_AGENT, TOTAL_ADDED
from SHIP_PLAN_CONT
where SHIP_PLAN_MST_ID=apex_application.g_f01(i);
begin
FOR x IN cont_data LOOP
v_con_seq:= SEQ_STUFFING_PLAN_CONT_ID.NEXTVAL;
INSERT INTO STUFFING_PLAN_CONT (
ID, STUFFING_PLAN_MST_ID,CONTAINER_TYPE, CAPACITY_CBM,
ALLOCATION, CONTAINER_MODE, FOREIGN_AGENT,TOTAL_ADDED)
values(
v_con_seq, v_mst_id ,x.CONTAINER_TYPE, x.CAPACITY_CBM,
x.ALLOCATION, x.CONTAINER_MODE, x.FOREIGN_AGENT,x.TOTAL_ADDED);
insert into stuffing_plan_goods (
ID, STUFFING_PLAN_CONT_ID, SEAEXPORT_MST_ID, SETUP_PARTY_COM_ID, BUYER_ID, CTN_TOTAL, CTN_ALLOCATED, CTN_REMAIN, CBM_TOTAL,
CBM_ALLOCATED, CBM_REMAIN, GW_TOTAL, GW_ALLOCATED, GW_REMAIN, LOCATION)
select SEQ_stuffing_goods_id.nextval, v_con_seq, SEAEXPORT_MST_ID, SETUP_PARTY_COM_ID, BUYER_ID, CTN_TOTAL, CTN_ALLOCATED, CTN_REMAIN, CBM_TOTAL,
CBM_ALLOCATED, CBM_REMAIN, GW_TOTAL, GW_ALLOCATED, GW_REMAIN, LOCATION
from ship_plan_goods
where SHIP_PLAN_CONT_ID=x.ID;
---- UPDATE_TRACKING_STUFFING
UPDATE SEAEXPORT_MST SET
STUFFING =1,
STUFFING_BY=:GUSER_ID,
STUFFING_DATE = SYSDATE
WHERE ID in (SELECT C.SEAEXPORT_MST_ID
FROM STUFFING_PLAN_MST A, STUFFING_PLAN_CONT B, STUFFING_PLAN_GOODS C
WHERE A.ID=B.STUFFING_PLAN_MST_ID
AND B.ID=C.STUFFING_PLAN_CONT_ID
AND A.ID=v_mst_id);
END LOOP;
END;
end if;
end loop;
end;