CREATE OR REPLACE PROCEDURE EJMIS.Ledger_Migration_Daily
IS
/******************************************************************************
NAME: Ledger_Migration_Daily
PURPOSE:
--**************************************** EJODS 로부터 EJMIS 데이타 수집 ********************************
--Select sysdate, trunc(sysdate, 'dd'), to_char(trunc(sysdate, 'dd'), 'YYYYMMDD'), sysdate-1 from dual
--**************************************** ------------------------------ ********************************
--select to_timestamp(to_char(trunc(sysdate -1 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS') from dual
--select to_char(trunc(sysdate -1 +1, 'dd'), 'YYYYMMDD') from dual
--** 1. Insert
--GO LIVE DAILY
--select max(CreatedDate) , max(CreatedKST) from Payment
--select trunc(sysdate -1, 'dd')-9/24 from dual
--DECLARE
--V_Create varchar2(255);
******************************************************************************/
BEGIN
INSERT INTO EJMIS.BATCHJOBLOG( JOBDATE, JOBID, JOBNUMBER, JOBTYPE, TARGETTABLE, TARGETDATE, ENDTARGETDATE, CREATEDUSERID)
Select to_char(SysDate, 'YYYYMMDD') JobDate, 'Ledger_Migration_Daily' JobID, 1 JobNumber, 'Insert' JobType , 'EJMIS Daily Batch Start' TargetTable,
to_char(SysDate-1, 'YYYYMMDD') TargetDate, NULL EndTargetDate, 'BatchSystem' CreatedUserID From Dual
;
Commit
;
Insert Into EJMIS.ZZJob Select 'Delete and Insert Table Z_Temp_SCXIntl' , to_char(SysDate, 'YYYYMMDD') , SysDate ,NULL From Dual;
Commit
;
Delete from Z_Temp_SCXIntl
;
Commit
;
Insert Into Z_Temp_SCXIntl
Select DepartureStation , ArrivalStation, International
from (SELECT "IVID" IVID
,"BeginLegNumber" BeginLegNumber
,"EndLegNumber" EndLegNumber
,"XRefNumber" XRefNumber
,"CreatedUserID" CreatedUserID
,to_timestamp(to_char(("CreatedUTC"+9/24), 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS') CREATEDKST
,"ModifiedUserID" ModifiedUserID
,to_timestamp(to_char(("ModifiedUTC"+9/24), 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS') MODIFIEDKST
,"CarrierCode" CarrierCode
,"FlightNumber" FlightNumber
,"OpSuffix" OpSuffix
,"DepartureStation" DepartureStation
,"ArrivalStation" ArrivalStation
,"TrafficRestrictions" TrafficRestrictions
,"DeptTerminalOverride" DeptTerminalOverride
,"ArrvTerminalOverride" ArrvTerminalOverride
,"JointOpInfoOverride" JointOpInfoOverride
,"International" International
FROM Schedule.ItinerarySegment@NaviSCX
) V_1
Group by DepartureStation , ArrivalStation, International
;
Commit
;
UPDATE EJMIS.BATCHJOBLOG J
SET ( ROWCOUNT, MODIFIEDKST ) -- ( ROWCOUNT, RUNNINGTIME, MODIFIEDKST )
= ( Select
(Select Count(*) RowCount
From (Select * from (SELECT "DepartureStation" DepartureStation ,"ArrivalStation" ArrivalStation ,"International" International FROM Schedule.ItinerarySegment@NaviSCX) V_1
Group by DepartureStation , ArrivalStation, International)V ) RowCount
,
-- (Select (SYSDATE - CreatedKST)Diff from BatchJobLog
-- Where JobDate = to_char(sysdate, 'YYYYMMDD') and JobID = 'Ledger_Migration_Daily'
-- and JobNumber = 2 and TargetTable = 'Z_Temp_SCXIntl' ) RunningTime
-- ,
SYSDATE ModifiedKST
From DUAL
)
Where (JobDate, JobID, JobNumber, TargetTable) = (Select to_char(sysdate, 'YYYYMMDD'), 'Ledger_Migration_Daily', 2, 'Z_Temp_SCXIntl' from Dual)
;
Commit
;
Insert Into EJMIS.ZZJob Select 'INSERT INTO EJMIS.STATION' , to_char(SysDate, 'YYYYMMDD') , SysDate , NULL From Dual;
Commit
;
INSERT INTO EJMIS.STATION (STATIONCODE, STATIONNAME, ICAOCODE, COUNTRYCODE, TIMEZONECODE, CURRENCYCODE,
CONVERSIONCURRENCYCODE, INACTIVE, CREATEDKST, MODIFIEDKST)
SELECT D.StationCode, D.Name STATIONNAME,
D.ICAOCode ICAOCode, D.CountryCode CountryCode, D.TimeZoneCode TimeZoneCode,
D.CurrencyCode CurrencyCode, D.ConversionCurrencyCode ConversionCurrencyCode, D.InActive InActive,
to_timestamp(to_char((D.CreatedDate+9/24), 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS') CREATEDKST,
to_timestamp(to_char((D.ModifiedDate+9/24), 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS') MODIFIEDKST
From Station@EJODS D
Where D.CreatedDate >= to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.CreatedDate < to_timestamp(to_char(trunc(sysdate -2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
;
Commit
;
Insert Into EJMIS.ZZJob Select 'UPDATE EJMIS.STATION U' , to_char(SysDate, 'YYYYMMDD') , SysDate , NULL From Dual;
Commit
;
UPDATE EJMIS.STATION U
SET ( STATIONCODE, STATIONNAME, ICAOCODE, COUNTRYCODE, TIMEZONECODE, CURRENCYCODE,
CONVERSIONCURRENCYCODE, INACTIVE, CREATEDKST, MODIFIEDKST
)
=
(
SELECT D.StationCode, D.Name STATIONNAME,
D.ICAOCode ICAOCode, D.CountryCode CountryCode, D.TimeZoneCode TimeZoneCode,
D.CurrencyCode CurrencyCode, D.ConversionCurrencyCode ConversionCurrencyCode, D.InActive InActive,
to_timestamp(to_char((D.CreatedDate+9/24), 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS') CREATEDKST,
to_timestamp(to_char((D.ModifiedDate+9/24), 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS') MODIFIEDKST
From Station@EJODS D
Where D.ModifiedDate >= to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.ModifiedDate < to_timestamp(to_char(trunc(sysdate -2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.CreatedDate < to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and U.StationCode = D.StationCode
)
Where StationCode in (
Select StationCode from Station@EJODS D
Where D.ModifiedDate >= to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.ModifiedDate < to_timestamp(to_char(trunc(sysdate -2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.CreatedDate < to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
)
;
Commit
;
Insert Into EJMIS.ZZJob Select 'Delete and Insert Table Z_Migration_Temp_Distance' , to_char(SysDate, 'YYYYMMDD') , SysDate , NULL From Dual;
Commit
;
Delete from Z_Temp_Distance
;
Commit
;
Insert Into EJMIS.ZZJob Select 'INSERT INTO EJMIS.Z_Temp_Distance' , to_char(SysDate, 'YYYYMMDD') , SysDate , NULL From Dual;
Commit;
Insert Into Z_Temp_Distance
SELECT "DepartureStation" DepartureStation
,"ArrivalStation" ArrivalStation
,"InActive" InActive
,"ActualDistance" ActualDistance
,"ActualDistanceUnit" ActualDistanceUnit
,"CustomerDistance" CustomerDistance
,"CustomerDistanceUnit" CustomerDistanceUnit
,"CreatedAgentID" CreatedAgentID
,to_timestamp(to_char(("CreatedDate"+9/24), 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS') CREATEDKST
,"ModifiedAgentID" ModifiedAgentID
,to_timestamp(to_char(("ModifiedDate"+9/24), 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS') MODIFIEDKST
FROM Distance@NaviODS
;
Commit
;
Insert Into EJMIS.ZZJob Select 'INSERT INTO EJMIS.ROUTE' , to_char(SysDate, 'YYYYMMDD') , SysDate , NULL From Dual;
Commit;
INSERT INTO EJMIS.ROUTE
( ROUTEID, DEPARTURESTATION, ARRIVALSTATION, DISTANCE,
INTERNATIONAL, INACTIVE, CREATEDKST, MODIFIEDKST )
Select EJMIS.ROUTEID_SEQ.NEXTVAL,
DEPARTURESTATION, ARRIVALSTATION, DISTANCE,
INTERNATIONAL, INACTIVE, SysDate CREATEDKST, SysDate MODIFIEDKST
From (
SELECT x.departurestation, x.arrivalstation, x.international,
d.actualdistance, d.actualdistanceunit,
d.customerdistance distance, d.customerdistanceunit distanceunit,
d.inactive
FROM z_temp_scxintl x, z_temp_distance d, route r
--> Right 가 Null 인 경우 Data Insert ( 기존에 없었던 것으로 판단할 수 있음. )
WHERE x.departurestation = r.departurestation(+)
AND x.arrivalstation = r.arrivalstation(+)
AND r.departurestation IS NULL
AND x.departurestation = d.departurestation(+)
AND x.arrivalstation = d.arrivalstation(+)
ORDER BY x.departurestation, x.arrivalstation
) V
;
Commit
;
Insert Into EJMIS.ZZJob Select 'UPDATE EJMIS.ROUTE' , to_char(SysDate, 'YYYYMMDD') , SysDate , NULL From Dual;
Commit;
UPDATE EJMIS.ROUTE U
SET ( DISTANCE, INTERNATIONAL, INACTIVE, MODIFIEDKST )
= ( Select DISTANCE, INTERNATIONAL, INACTIVE, SysDate MODIFIEDKST
From (
SELECT x.departurestation, x.arrivalstation, x.international,
d.actualdistance, d.actualdistanceunit,
d.customerdistance distance, d.customerdistanceunit distanceunit,
d.inactive
FROM z_temp_scxintl x, z_temp_distance d, route r
--> Right 가 Null 인 경우 Data Insert ( 기존에 없었던 것으로 판단할 수 있음. )
WHERE x.departurestation = r.departurestation(+)
AND x.arrivalstation = r.arrivalstation(+)
AND x.departurestation = d.departurestation(+)
AND x.arrivalstation = d.arrivalstation(+)
) V
Where V.DepartureStation = U.DepartureStation
and V.ArrivalStation = U.ArrivalStation
)
Where ( U.DepartureStation, U.ArrivalStation ) = ( SELECT x.departurestation, x.arrivalstation
FROM z_temp_scxintl x, z_temp_distance d
WHERE x.departurestation = U.departurestation
AND x.arrivalstation = U.arrivalstation
AND x.departurestation = d.departurestation(+)
AND x.arrivalstation = d.arrivalstation(+)
)
and ( NVL(U.DISTANCE,0), NVL(U.INTERNATIONAL,0), NVL(U.INACTIVE,0) )
<> ( SELECT d.customerdistance distance, x.international, d.inactive
FROM z_temp_scxintl x, z_temp_distance d
WHERE x.departurestation = U.departurestation
AND x.arrivalstation = U.arrivalstation
AND x.departurestation = d.departurestation(+)
AND x.arrivalstation = d.arrivalstation(+)
)
;
Commit
;
Insert Into EJMIS.ZZJob Select 'INSERT INTO EJMIS.AIRCRAFT' , to_char(SysDate, 'YYYYMMDD') , SysDate , NULL From Dual;
Commit;
INSERT INTO EJMIS.AIRCRAFT ( AIRCRAFTID, TAILNUMBER, AIRCRAFTNAME, AIRCRAFTNICKNAME,
AIRCRAFTTYPE, CAPACITY, PAYLOAD, RANGE, AVERAGESPEED, INACTIVE )
Select A."AircraftID", A."TailNumber", A."Name" AircraftName , A."Description" AircraftNickName, A."AircraftType",
C."Capacity", C."Payload", C."Range", C."AverageSpeed", 0 Inactive
from Aircraft@NaviODS A, AircraftConfiguration@NaviODS C, Aircraft AC
Where A."AircraftType" = C."AircraftType"
and A."AircraftID" = AC.AircraftID(+)
and AC.AircraftID IS NULL -- 신규 발생건만을 대상으로 함.
;
Commit
;
Insert Into EJMIS.ZZJob Select 'INSERT INTO EJMIS.INVENTORYLEG' , to_char(SysDate, 'YYYYMMDD') , SysDate , NULL From Dual;
Commit
;
INSERT INTO EJMIS.INVENTORYLEG ( LEGID, DEPARTUREDATE, FLIGHTNUMBER, DEPARTURESTATION, ARRIVALSTATION, LEGSTD,
LEGSTA, AIRCRAFTID, Capacity, ROUTEID, INVENTORYLEGKEY,OperationSuffix,
AircraftChanged, UnFlown, Status, CREATEDKST, MODIFIEDKST )
Select A.LEGID, A.DEPARTUREDATE, A.FLIGHTNUMBER, A.DEPARTURESTATION, A.ARRIVALSTATION, A.LEGSTD,
A.LEGSTA, A.AIRCRAFTID, A.Capacity, A.ROUTEID, A.INVENTORYLEGKEY, A.OperationSuffix,
A.AircraftChanged, A.UnFlown, A.Status, A.CREATEDKST, A.MODIFIEDKST
From(
Select RowNum, O."InventoryLegOpID" LEGID, to_char(I."DepartureDate", 'YYYYMMDD') DepartureDate,
TRIM(I."FlightNumber") FLIGHTNUMBER, R.DepartureStation, R.ArrivalStation,
to_timestamp(to_char((I."STD"), 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS') LEGSTD,
to_timestamp(to_char((I."STA"), 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS') LEGSTA,
NULL AircraftID, I."Capacity" Capacity, R.RouteID, I."InventoryLegKey" INVENTORYLEGKEY,
0 OperationSuffix, 0 AircraftChanged, 0 UnFlown, 0 Status,
to_timestamp(to_char((I."CreatedDate" +9/24), 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS') CREATEDKST,
to_timestamp(to_char((I."ModifiedDate"+9/24), 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS') MODIFIEDKST
from InventoryLegOP@NaviODS O, InventoryLeg@NaviODS I, Route@EJMIS R
where O."InventoryLegKey" = I."InventoryLegKey"
and I."DepartureStation" = R.DepartureStation and I."ArrivalStation" = R.ArrivalStation
) A, InventoryLeg IL
Where A.LegID = IL.LegID(+)
and IL.LegID IS NULL -- 신규 발생건만을 대상으로 함.
Order by A.LegID
;
Commit
;
Insert Into EJMIS.ZZJob Select 'Update with Merge Into EJMIS.INVENTORYLEG U' , to_char(SysDate, 'YYYYMMDD') , SysDate , NULL From Dual;
Commit
;
Merge Into EJMIS.INVENTORYLEG U
Using
(Select DEPARTUREDATE, FLIGHTNUMBER, DEPARTURESTATION, ARRIVALSTATION, LEGSTD,
LEGSTA, AIRCRAFTID, Capacity, ROUTEID, INVENTORYLEGKEY,OperationSuffix,
AircraftChanged, UnFlown, Status, CREATEDKST, MODIFIEDKST, LegID
From(
Select /*+ Use_Hash( O I R) Ordered */RowNum, O."InventoryLegOpID" LEGID, to_char(I."DepartureDate", 'YYYYMMDD') DepartureDate,
TRIM(I."FlightNumber") FLIGHTNUMBER, R.DepartureStation, R.ArrivalStation,
to_timestamp(to_char((I."STD"), 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS') LEGSTD,
to_timestamp(to_char((I."STA"), 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS') LEGSTA,
NULL AircraftID, I."Capacity" Capacity, R.RouteID, I."InventoryLegKey" INVENTORYLEGKEY,
0 OperationSuffix, 0 AircraftChanged, 0 UnFlown, 0 Status,
to_timestamp(to_char((I."CreatedDate" +9/24), 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS') CREATEDKST,
to_timestamp(to_char((I."ModifiedDate"+9/24), 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS') MODIFIEDKST
from InventoryLegOP@NaviODS O, InventoryLeg@NaviODS I, Route R
where O."InventoryLegKey" = I."InventoryLegKey"
and I."DepartureStation" = R.DepartureStation and I."ArrivalStation" = R.ArrivalStation
and I."ModifiedDate" >= to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and I."ModifiedDate" < to_timestamp(to_char(trunc(sysdate -2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and I."CreatedDate" < to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
) A
)N
ON ( U.LegID = N.LegID )
When Matched Then
Update SET U.DEPARTUREDATE = N.DEPARTUREDATE,
U.FLIGHTNUMBER = N.FLIGHTNUMBER,
U.DEPARTURESTATION = N.DEPARTURESTATION,
U.ARRIVALSTATION = N.ARRIVALSTATION,
U.LEGSTD = N.LEGSTD,
U.LEGSTA = N.LEGSTA,
U.AIRCRAFTID = N.AIRCRAFTID,
U.Capacity = N.Capacity,
U.ROUTEID = N.ROUTEID,
U.INVENTORYLEGKEY = N.INVENTORYLEGKEY,
U.OperationSuffix = N.OperationSuffix,
U.AircraftChanged = N.AircraftChanged,
U.UnFlown = N.UnFlown,
U.Status = N.Status,
U.MODIFIEDKST = N.MODIFIEDKST
;
Commit
;
Insert Into EJMIS.ZZJob Select 'INSERT INTO EJMIS.ORGANIZATION' , to_char(SysDate, 'YYYYMMDD') , SysDate , NULL From Dual;
Commit;
--Error at line 213
--ORA-00913: 값의 수가 너무 많습니다
INSERT INTO EJMIS.ORGANIZATION ( ORGANIZATIONCODE, ORGANIZATIONTYPE, ORGANIZATIONNAME, STATUS)
SELECT D.ORGANIZATIONCODE, ORGANIZATIONTYPE, ORGANIZATIONNAME, STATUS
FROM ORGANIZATION@EJODS D, (Select OrganizationCode from EJMIS.ORGANIZATION) I
Where D.CreatedDate >= to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.CreatedDate < to_timestamp(to_char(trunc(sysdate -2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.OrganizationCode = I.OrganizationCode(+)
and I.OrganizationCode IS NULL
;
Commit
;
Insert Into EJMIS.ZZJob Select 'UPDATE EJMIS.ORGANIZATION U' , to_char(SysDate, 'YYYYMMDD') , SysDate , NULL From Dual;
Commit;
UPDATE EJMIS.ORGANIZATION U
SET ( ORGANIZATIONCODE, ORGANIZATIONTYPE, ORGANIZATIONNAME, STATUS
)
=
(
SELECT ORGANIZATIONCODE, ORGANIZATIONTYPE, ORGANIZATIONNAME, STATUS
FROM ORGANIZATION@EJODS D
Where D.ModifiedDate >= to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.ModifiedDate < to_timestamp(to_char(trunc(sysdate -2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.CreatedDate < to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and U.ORGANIZATIONCODE = D.ORGANIZATIONCODE
)
Where ORGANIZATIONCODE in (
SELECT ORGANIZATIONCODE
FROM ORGANIZATION@EJODS D
Where D.ModifiedDate >= to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.ModifiedDate < to_timestamp(to_char(trunc(sysdate -2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.CreatedDate < to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
)
;
Commit
;
Insert Into EJMIS.ZZJob Select 'INSERT INTO EJMIS.ORGANIZATIONAUX' , to_char(SysDate, 'YYYYMMDD') , SysDate , NULL From Dual;
Commit;
INSERT INTO EJMIS.ORGANIZATIONAUX ( ORGANIZATIONCODE, ADDRESSLINE1, ADDRESSLINE2, POSTALCODE, PHONE, FAX, EMAILADDRESS, CONTACTLASTNAME, CONTACTMIDDLENAME,
CONTACTFIRSTNAME, CONTACTPHONE, CONTACTOTHERPHONE, STATEMENTNOTE, COMMISSIONABLE, RECALCULATIONCOMMISSION, NETTEDTOTAL, GDSEMAILITINERARY, SOURCE,
SOURCESTATUS, TRACEQUEUECODE, REFERRALTYPE, CREATEDAGENTID, CREATEDKST, MODIFIEDAGENTID, MODIFIEDKST)
Select
D.ORGANIZATIONCODE, ADDRESSLINE1, ADDRESSLINE2, POSTALCODE, PHONE, FAX, EMAILADDRESS, CONTACTLASTNAME, CONTACTMIDDLENAME
,CONTACTFIRSTNAME, CONTACTPHONE, CONTACTOTHERPHONE, STATEMENTNOTE, COMMISSIONABLE, RECALLCOMMISSION RECALCULATIONCOMMISSION, NETTEDTOTAL, GDSEMAILITINERARY, SOURCE
,SOURCESTATUS, TRACEQUEUECODE, REFERRALTYPE, CREATEDAGENTID
,to_timestamp(to_char((CreatedDate+9/24), 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS') CREATEDKST
,MODIFIEDAGENTID
,to_timestamp(to_char((ModifiedDate+9/24), 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS') MODIFIEDKST
FROM ORGANIZATION@EJODS D, (Select OrganizationCode from OrganizationAux) OX
Where D.OrganizationCode = OX.OrganizationCode(+) and OX.OrganizationCode IS NULL
and D.CreatedDate >= to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.CreatedDate < to_timestamp(to_char(trunc(sysdate -2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
;
Commit
;
Insert Into EJMIS.ZZJob Select 'UPDATE EJMIS.ORGANIZATIONAUX U' , to_char(SysDate, 'YYYYMMDD') , SysDate , NULL From Dual;
Commit;
UPDATE EJMIS.ORGANIZATIONAUX U
SET ( ORGANIZATIONCODE, ADDRESSLINE1, ADDRESSLINE2, POSTALCODE, PHONE, FAX, EMAILADDRESS, CONTACTLASTNAME, CONTACTMIDDLENAME,
CONTACTFIRSTNAME, CONTACTPHONE, CONTACTOTHERPHONE, STATEMENTNOTE, COMMISSIONABLE, RECALCULATIONCOMMISSION, NETTEDTOTAL, GDSEMAILITINERARY, SOURCE,
SOURCESTATUS, TRACEQUEUECODE, REFERRALTYPE, CREATEDAGENTID, CREATEDKST, MODIFIEDAGENTID, MODIFIEDKST
)
=
(
Select ORGANIZATIONCODE, ADDRESSLINE1, ADDRESSLINE2, POSTALCODE, PHONE, FAX, EMAILADDRESS, CONTACTLASTNAME, CONTACTMIDDLENAME
,CONTACTFIRSTNAME, CONTACTPHONE, CONTACTOTHERPHONE, STATEMENTNOTE, COMMISSIONABLE, RECALLCOMMISSION RECALCULATIONCOMMISSION, NETTEDTOTAL, GDSEMAILITINERARY, SOURCE
,SOURCESTATUS, TRACEQUEUECODE, REFERRALTYPE, CREATEDAGENTID
,to_timestamp(to_char((CreatedDate+9/24), 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS') CREATEDKST
,MODIFIEDAGENTID
,to_timestamp(to_char((ModifiedDate+9/24), 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS') MODIFIEDKST
FROM ORGANIZATION@EJODS D
Where D.ModifiedDate >= to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.ModifiedDate < to_timestamp(to_char(trunc(sysdate -2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.CreatedDate < to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and U.ORGANIZATIONCODE = D.ORGANIZATIONCODE
)
Where ORGANIZATIONCODE in (
SELECT ORGANIZATIONCODE
FROM ORGANIZATION@EJODS D
Where D.ModifiedDate >= to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.ModifiedDate < to_timestamp(to_char(trunc(sysdate -2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.CreatedDate < to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
)
;
Commit
;
Insert Into EJMIS.ZZJob Select 'INSERT INTO EJMIS.AGENT' , to_char(SysDate, 'YYYYMMDD') , SysDate , NULL From Dual;
Commit;
INSERT INTO EJMIS.AGENT ( AGENTID, AGENTNAME, ORGANIZATIONCODE, DEPARTMENTCODE, LOCATIONCODE, STATUS, PERSONID )
Select D.AGENTID, AGENTNAME, UPPER(ORGANIZATIONCODE), DEPARTMENTCODE, LOCATIONCODE, STATUS, PERSONID
from Agent@EJODS D, (Select AgentID from EJMIS.Agent) I
Where D.CreatedDate >= to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.CreatedDate < to_timestamp(to_char(trunc(sysdate -2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.AgentID = I.AgentID(+)
and I.AgentID IS NULL
;
Commit
;
Insert Into EJMIS.ZZJob Select 'UPDATE EJMIS.AGENT U' , to_char(SysDate, 'YYYYMMDD') , SysDate , NULL From Dual;
Commit;
UPDATE EJMIS.AGENT U
SET ( AGENTID, AGENTNAME, ORGANIZATIONCODE, DEPARTMENTCODE, LOCATIONCODE, STATUS, PERSONID
)
=
(
Select AGENTID, AGENTNAME, UPPER(ORGANIZATIONCODE), DEPARTMENTCODE, LOCATIONCODE, STATUS, PERSONID
from Agent@EJODS D
Where D.ModifiedDate >= to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.ModifiedDate < to_timestamp(to_char(trunc(sysdate -2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.CreatedDate < to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and U.AGENTID = D.AGENTID
)
Where AGENTID in (
SELECT AGENTID
FROM Agent@EJODS D
Where D.ModifiedDate >= to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.ModifiedDate < to_timestamp(to_char(trunc(sysdate -2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.CreatedDate < to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
)
;
Commit
;
Insert Into EJMIS.ZZJob Select 'INSERT INTO EJMIS.AGENTAUX' , to_char(SysDate, 'YYYYMMDD') , SysDate , NULL From Dual;
Commit
;
INSERT INTO EJMIS.AGENTAUX (
LOCATIONGROUPCODE, ORGANIZATIONGROUPCODE, LOCKED, AUTHENTICATIONTYPE, HIREDATE, TERMINATIONDATE, AGENTNOTE, TRACEQUEUECODE, LOGONKST,
FAILEDLOGONS, LASTALERTREADKST, FORCEPASSWORDRESET, PASSWORDCHANGEDKST, CREATEDAGENTID, CREATEDKST, MODIFIEDAGENTID, MODIFIEDKST, AGENTID)
Select LOCATIONGROUPCODE, ORGANIZATIONGROUPCODE, LOCKED, AUTHENTICATIONTYPE, HIREDATE+9/24 HIREDATE, TERMINATIONDATE+9/24 TERMINATIONDATE, AGENTNOTE, TRACEQUEUECODE, LogonDateTime+9/24 LOGONKST,
FAILEDLOGONS, LastAlertReadDate+9/24 LASTALERTREADKST, FORCEPASSWORDRESET, PasswordChangedDate+9/24 PASSWORDCHANGEDKST, CREATEDAGENTID, CREATEDDATE+9/24 CREATEDKST, MODIFIEDAGENTID, MODIFIEDDATE+9/24 MODIFIEDKST, D.AGENTID
from Agent@EJODS D , (Select AgentID from EJMIS.Agent) I
Where D.CreatedDate >= to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.CreatedDate < to_timestamp(to_char(trunc(sysdate -2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.AgentID = I.AgentID(+)
and I.AgentID IS NULL
;
Commit
;
Insert Into EJMIS.ZZJob Select 'UPDATE EJMIS.AGENTAUX U' , to_char(SysDate, 'YYYYMMDD') , SysDate , NULL From Dual;
Commit
;
--select * from EJMIS.ZZjob order by createddate desc
--Error at line 163
--ORA-00927: 누락된 등호
UPDATE EJMIS.AGENTAUX U
SET ( LOCATIONGROUPCODE, ORGANIZATIONGROUPCODE, LOCKED, AUTHENTICATIONTYPE, HIREDATE, TERMINATIONDATE, AGENTNOTE, TRACEQUEUECODE, LOGONKST,
FAILEDLOGONS, LASTALERTREADKST, FORCEPASSWORDRESET, PASSWORDCHANGEDKST, CREATEDAGENTID, CREATEDKST, MODIFIEDAGENTID, MODIFIEDKST, AGENTID
)
=
(
Select LOCATIONGROUPCODE, ORGANIZATIONGROUPCODE, LOCKED, AUTHENTICATIONTYPE, HIREDATE+9/24 HIREDATE, TERMINATIONDATE+9/24 TERMINATIONDATE, AGENTNOTE, TRACEQUEUECODE, LogonDateTime+9/24 LOGONKST,
FAILEDLOGONS, LastAlertReadDate+9/24 LASTALERTREADKST, FORCEPASSWORDRESET, PasswordChangedDate+9/24 PASSWORDCHANGEDKST, CREATEDAGENTID, CREATEDDATE+9/24 CREATEDKST, MODIFIEDAGENTID, MODIFIEDDATE+9/24 MODIFIEDKST, AGENTID
from Agent@EJODS D
Where D.ModifiedDate >= to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.ModifiedDate < to_timestamp(to_char(trunc(sysdate -2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.CreatedDate < to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and U.AGENTID = D.AGENTID
)
Where AGENTID in (
SELECT AGENTID
FROM Agent@EJODS D
Where D.ModifiedDate >= to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.ModifiedDate < to_timestamp(to_char(trunc(sysdate -2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.CreatedDate < to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
)
;
Commit
;
Insert Into EJMIS.ZZJob Select 'INSERT INTO EJMIS.BOOKING ' , to_char(SysDate, 'YYYYMMDD') , SysDate , NULL From Dual;
Commit
;
INSERT INTO EJMIS.BOOKING
( BOOKINGID, BOOKINGPARENTID, RECORDLOCATOR, STATUS, PRICESTATUS, ORIGINALPARENTBOOKINGID )
SELECT /*+ Use_Hash(D, B) */
D.BOOKINGID, BOOKINGPARENTID, RECORDLOCATOR, STATUS, PRICESTATUS,
(SELECT BookingID
FROM Booking@EJODS B
Where B.BookingParentID < 1
START WITH B.BookingID = D.BookingID
CONNECT BY PRIOR B.BookingParentID = B.BookingID
) OriginalParentBookingID -- Find_OriginalParentBookingID(BookingID)
FROM Booking@EJODS D, (Select BookingID from Booking) B
Where D.CreatedDate >= to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.CreatedDate < to_timestamp(to_char(trunc(sysdate -2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.BookingID = B.BookingID(+)
and B.BookingID IS NULL
;
Commit
;
Insert Into EJMIS.ZZJob Select 'UPDATE EJMIS.BOOKING ' , to_char(SysDate, 'YYYYMMDD') , SysDate , NULL From Dual;
Commit
;
Update EJMIS.BOOKING U
Set ( BOOKINGID, BOOKINGPARENTID, RECORDLOCATOR, STATUS, PRICESTATUS, ORIGINALPARENTBOOKINGID )
=
( SELECT /*+ Use_Hash(D, B) */
D.BOOKINGID, BOOKINGPARENTID, RECORDLOCATOR, STATUS, PRICESTATUS,
(SELECT BookingID
FROM Booking@EJODS B
Where B.BookingParentID < 1
START WITH B.BookingID = D.BookingID
CONNECT BY PRIOR B.BookingParentID = B.BookingID
) OriginalParentBookingID -- Find_OriginalParentBookingID(BookingID)
FROM Booking@EJODS D --, (Select BookingID from Booking) B
Where D.CreatedDate < to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.ModifiedDate >= to_timestamp(to_char(trunc(sysdate -1-2+2, 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.ModifiedDate < to_timestamp(to_char(trunc(sysdate -2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and U.BookingID = D.BookingID
)
Where U.BookingID in
(
Select BookingID FROM Booking@EJODS D --, (Select BookingID from Booking) B
Where D.CreatedDate < to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.ModifiedDate >= to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.ModifiedDate < to_timestamp(to_char(trunc(sysdate -2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
)
-- and D.BookingID = B.BookingID(+)
-- and B.BookingID IS NOT NULL
;
Commit
;
Insert Into EJMIS.ZZJob Select 'INSERT INTO EJMIS.BOOKINGAUX ' , to_char(SysDate, 'YYYYMMDD') , SysDate , NULL From Dual;
Commit
;
INSERT INTO EJMIS.BOOKINGAUX
( BookingID, CURRENCYCODE, HOLDDATE, EXPIREDKST, RECEIVEDBY, RECEIVEDBYREFERENCE, PAXRESIDENTCOUNTRY,
CHANNELTYPE, CREATEDAGENTID, CREATEDAGENTCODE, CREATEDLOCATIONCODE, CREATEDORGANIZATIONCODE, CREATEDDOMAINCODE,
SOURCEAGENTCODE, SOURCEORGANIZATIONCODE, SOURCEDOMAINCODE, SOURCELOCATIONCODE, CREATEDKST, MODIFIEDAGENTID,
MODIFIEDKST, BOOKINGPROMOCODE, BOOKINGDATE, BOOKINGKST )
SELECT /*+ Use_Hash(D, B) */
D.BookingID, CURRENCYCODE, to_char(HoldDateTime+9/24, 'YYYYMMDD') HOLDDATE, ExpiredDate+9/24 EXPIREDKST, RECEIVEDBY, RECEIVEDBYREFERENCE, PAXRESIDENTCOUNTRY,
CHANNELTYPE, CREATEDAGENTID, CREATEDAGENTCODE, CREATEDLOCATIONCODE, CREATEDORGANIZATIONCODE, CREATEDDOMAINCODE,
SOURCEAGENTCODE, SOURCEORGANIZATIONCODE, SOURCEDOMAINCODE, SOURCELOCATIONCODE, CreatedDate+9/24 CREATEDKST, MODIFIEDAGENTID,
ModifiedDate+9/24 MODIFIEDKST, BOOKINGPROMOCODE, to_char(BookingDate +9/24, 'YYYYMMDD')BOOKINGDATE, BookingDate +9/24 BOOKINGKST
FROM Booking@EJODS D, (Select BookingID from Booking) B , (Select BookingID from BookingAux) BAUX
Where D.CreatedDate >= to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.CreatedDate < to_timestamp(to_char(trunc(sysdate -2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.BookingID = B.BookingID
and B.BookingID = BAUX.BookingID(+)
and BAUX.BookingID IS NULL
-- FROM Booking@EJODS D, (Select BookingID from Booking) B
-- Where D.CreatedDate >= to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
-- and D.CreatedDate < to_timestamp(to_char(trunc(sysdate -2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
-- and D.BookingID = B.BookingID(+)
-- and B.BookingID IS NULL
;
Commit
;
Insert Into EJMIS.ZZJob Select 'UPDATE EJMIS.BOOKINGAUX U' , to_char(SysDate, 'YYYYMMDD') , SysDate , NULL From Dual;
Commit
;
--****
--ORA-12899: "EJMIS"."BOOKINGAUX"."BOOKINGDATE" 열에 대한 값이 너무 큼(실제: 24, 최대값: 8)
UPDATE EJMIS.BOOKINGAUX U
SET ( BOOKINGID, CHANNELTYPE, CREATEDAGENTCODE, CREATEDLOCATIONCODE, CREATEDORGANIZATIONCODE, CREATEDDOMAINCODE, SOURCEAGENTCODE, SOURCEORGANIZATIONCODE, SOURCEDOMAINCODE,
SOURCELOCATIONCODE, CURRENCYCODE, BOOKINGPROMOCODE, HoldDate, EXPIREDKST, RECEIVEDBY, RECEIVEDBYREFERENCE, PAXRESIDENTCOUNTRY, BOOKINGDATE,
BOOKINGKST, CREATEDAGENTID, CREATEDKST, MODIFIEDAGENTID, MODIFIEDKST
)
=
(
Select BOOKINGID, CHANNELTYPE, CREATEDAGENTCODE, CREATEDLOCATIONCODE, CREATEDORGANIZATIONCODE, CREATEDDOMAINCODE, SOURCEAGENTCODE, SOURCEORGANIZATIONCODE, SOURCEDOMAINCODE,
SOURCELOCATIONCODE, CURRENCYCODE, BOOKINGPROMOCODE, HoldDateTime+9/24 HoldDate, ExpiredDate+9/24 EXPIREDKST, RECEIVEDBY, RECEIVEDBYREFERENCE, PAXRESIDENTCOUNTRY,
to_char(BookingDate+9/24, 'YYYYMMDD')BOOKINGDATE, BookingDate+9/24 BOOKINGKST, CREATEDAGENTID,
CreatedDate+9/24 CREATEDKST, MODIFIEDAGENTID, ModifiedDate+9/24 MODIFIEDKST
from Booking@EJODS D
Where D.ModifiedDate >= to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.ModifiedDate < to_timestamp(to_char(trunc(sysdate -2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.CreatedDate < to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and U.BOOKINGID = D.BOOKINGID
)
Where BOOKINGID in (
SELECT BOOKINGID
FROM Booking@EJODS D
Where D.ModifiedDate >= to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.ModifiedDate < to_timestamp(to_char(trunc(sysdate -2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.CreatedDate < to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
)
;
Commit
;
Insert Into EJMIS.ZZJob Select 'INSERT INTO EJMIS.PAXTYPE' , to_char(SysDate, 'YYYYMMDD') , SysDate , NULL From Dual;
Commit
;
INSERT INTO EJMIS.PAXTYPE
( PAXTYPE, NAME, NONADULT, MINIMUMAGE, MAXIMUMAGE, INACTIVE,
CREATEDAGENTID, CREATEDKST, MODIFIEDAGENTID, MODIFIEDKST, APFTYPE )
Select TYPECODE PAXTYPE, NAME, NONADULT, MINIMUMAGE, MAXIMUMAGE, INACTIVE,
CREATEDAGENTID, CREATEDDATE+9/24 CREATEDKST, MODIFIEDAGENTID, MODIFIEDDATE+9/24 MODIFIEDKST, NULL APFTYPE
from PaxType@EJODS D
Where D.CreatedDate >= to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.CreatedDate < to_timestamp(to_char(trunc(sysdate -2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
;
Commit
;
--******************
--select * from EJMIS.ZZJob order by createddate desc
Insert Into EJMIS.ZZJob Select 'UPDATE EJMIS.PAXTYPE U' , to_char(SysDate, 'YYYYMMDD') , SysDate , NULL From Dual;
Commit
;
UPDATE EJMIS.PAXTYPE U
SET ( PAXTYPE, NAME, NONADULT, MINIMUMAGE, MAXIMUMAGE, -- APFDISCOUNTABLE, FAREDISCOUNTABLE, NONREVENUE,
INACTIVE, CREATEDAGENTID, CREATEDKST, MODIFIEDAGENTID, MODIFIEDKST--, APFTYPE
)
=
(
Select TYPECODE PAXTYPE, NAME, NONADULT, MINIMUMAGE, MAXIMUMAGE, -- NULL APFDISCOUNTABLE, NULL FAREDISCOUNTABLE, NULL NONREVENUE,
INACTIVE, CREATEDAGENTID, CREATEDDATE+9/24 CREATEDKST, MODIFIEDAGENTID, MODIFIEDDATE+9/24 MODIFIEDKST--, NULL APFTYPE
from PaxType@EJODS D
Where D.ModifiedDate >= to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.ModifiedDate < to_timestamp(to_char(trunc(sysdate -2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.CreatedDate < to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and U.PAXTYPE = D.TYPECODE
)
Where PAXTYPE in (
SELECT TYPECODE
FROM PaxType@EJODS D
Where D.ModifiedDate >= to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.ModifiedDate < to_timestamp(to_char(trunc(sysdate -2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.CreatedDate < to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
)
;
Commit
;
Insert Into EJMIS.ZZJob Select 'INSERT INTO EJMIS.BOOKINGPASSENGER' , to_char(SysDate, 'YYYYMMDD') , SysDate , NULL From Dual;
Commit
;
INSERT INTO EJMIS.BOOKINGPASSENGER
( PASSENGERID, PAXTYPE, FULLNAME, BalanceDue, TotalCost, BOOKINGID)
Select D.PASSENGERID, PAXTYPE, (LASTNAME||MIDDLENAME||FIRSTNAME) FULLNAME, BalanceDue, TotalCost, BOOKINGID
from BookingPassenger@EJODS D , (Select PassengerID from BookingPassenger) I
Where D.CreatedDate >= to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.CreatedDate < to_timestamp(to_char(trunc(sysdate -2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.PassengerID = I.PassengerID(+)
and I.PassengerID IS NULL
;
Commit
;
Insert Into EJMIS.ZZJob Select 'UPDATE EJMIS.BOOKINGPASSENGER U' , to_char(SysDate, 'YYYYMMDD') , SysDate , NULL From Dual;
Commit
;
UPDATE EJMIS.BOOKINGPASSENGER U
SET ( PASSENGERID, PAXTYPE, FULLNAME, BalanceDue, TotalCost, BOOKINGID
)
=
(
Select PASSENGERID, PAXTYPE, (LASTNAME||MIDDLENAME||FIRSTNAME) FULLNAME, BalanceDue, TotalCost, BOOKINGID
from BOOKINGPASSENGER@EJODS D
Where D.ModifiedDate >= to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.ModifiedDate < to_timestamp(to_char(trunc(sysdate -2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.CreatedDate < to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and U.PASSENGERID = D.PASSENGERID
)
Where PASSENGERID in (
SELECT PASSENGERID
FROM BOOKINGPASSENGER@EJODS D
Where D.ModifiedDate >= to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.ModifiedDate < to_timestamp(to_char(trunc(sysdate -2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.CreatedDate < to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
)
;
Commit
;
Insert Into EJMIS.ZZJob Select 'INSERT INTO EJMIS.BOOKINGPASSENGERAUX' , to_char(SysDate, 'YYYYMMDD') , SysDate , NULL From Dual;
Commit
;
INSERT INTO EJMIS.BOOKINGPASSENGERAUX
( PASSENGERID, DOB, GENDER, WEIGHTCATEGORY, TITLE, NATIONALITY, RESIDENTCOUNTRY, INFANT, CUSTOMERNUMBER,
CREATEDAGENTID, CREATEDKST, MODIFIEDAGENTID, MODIFIEDKST, LASTNAME, MIDDLENAME, FIRSTNAME )
Select D.PASSENGERID, DOB, GENDER, WEIGHTCATEGORY, TITLE, NATIONALITY, RESIDENTCOUNTRY, INFANT, CUSTOMERNUMBER,
CREATEDAGENTID, CreatedDate+9/24 CREATEDKST, MODIFIEDAGENTID, ModifiedDate+9/24 MODIFIEDKST, LASTNAME, MIDDLENAME, FIRSTNAME
from BookingPassenger@EJODS D , (Select PassengerID from BookingPassengerAux) I
Where D.CreatedDate >= to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.CreatedDate < to_timestamp(to_char(trunc(sysdate -2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.PassengerID = I.PassengerID(+)
and I.PassengerID IS NULL
;
Commit
;
Insert Into EJMIS.ZZJob Select 'UPDATE EJMIS.BOOKINGPASSENGERAUX U' , to_char(SysDate, 'YYYYMMDD') , SysDate , NULL From Dual;
Commit
;
UPDATE EJMIS.BOOKINGPASSENGERAUX U
SET ( PASSENGERID, DOB, GENDER, WEIGHTCATEGORY, TITLE, NATIONALITY, RESIDENTCOUNTRY, INFANT, CUSTOMERNUMBER,
CREATEDAGENTID, CREATEDKST, MODIFIEDAGENTID, MODIFIEDKST, LASTNAME, MIDDLENAME, FIRSTNAME )
=
(
Select PASSENGERID, DOB, GENDER, WEIGHTCATEGORY, TITLE, NATIONALITY, RESIDENTCOUNTRY, INFANT, CUSTOMERNUMBER,
CREATEDAGENTID, CreatedDate+9/24 CREATEDKST, MODIFIEDAGENTID, ModifiedDate+9/24 MODIFIEDKST, LASTNAME, MIDDLENAME, FIRSTNAME
from BOOKINGPASSENGER@EJODS D
Where D.ModifiedDate >= to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.ModifiedDate < to_timestamp(to_char(trunc(sysdate -2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.CreatedDate < to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and U.PASSENGERID = D.PASSENGERID
)
Where PASSENGERID in (
SELECT PASSENGERID
FROM BOOKINGPASSENGER@EJODS D
Where D.ModifiedDate >= to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.ModifiedDate < to_timestamp(to_char(trunc(sysdate -2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.CreatedDate < to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
)
;
Commit
;
Insert Into EJMIS.ZZJob Select 'INSERT INTO EJMIS.PASSENGERJOURNEYSEGMENT Daily CreatedDate KST Base' , to_char(SysDate, 'YYYYMMDD') , SysDate , NULL From Dual;
Commit
; ---**>>> CreatedDate KST Base Loading Sql Sentence "ejmispjsDailyinsertview"
INSERT INTO EJMIS.PASSENGERJOURNEYSEGMENT
( SEGMENTID, LEGID, BOOKINGSTATUS, LIFTSTATUS, FARESTATUS, FAREBASIS, FAREDISCOUNTCODE, OVERBOOKINDICATOR, PASSENGERID,
CREATEDKST, MODIFIEDKST, CURRENCYCODE, APF, FSF, ISF, SSF, FAREBASISFARE, NETFARE, DISCOUNTAMOUNT, PROMOTIONDISCOUNTCODE,
PROMOTIONDISCOUNTAMOUNT, UNIDENTIFIEDCHARGE, FOREIGNCURRENCYCODE, FOREIGNBASISFARE, FOREIGNDISCOUNTAMOUNT, FOREIGNPROMOTIONDISCOUNTAMOUNT,
FOREIGNNETFARE, FOREIGNAPF, FOREIGNFSF, FOREIGNISF, FOREIGNSSF, FOREIGNUNIDENTIFIEDCHARGE, RULENUMBER, FLIGHTNUMBER,
DEPARTURESTATION, ARRIVALSTATION, DEPARTUREDATE, CANCELED, DISCOUNTCODE, FOREIGNNETFARECORRECTION,
FOREIGNFSFCORRECTION, FOREIGNAPFCORRECTION, EJCORRECTION, UNMATCHED, InsertedKST )
Select /*+ All_Rows */ V.SEGMENTID, LEGID, BOOKINGSTATUS, LIFTSTATUS, FARESTATUS, FAREBASIS, FAREDISCOUNTCODE, OVERBOOKINDICATOR, PASSENGERID,
CREATEDKST, MODIFIEDKST, CURRENCYCODE, APF, FSF, ISF, SSF, FAREBASISFARE, NETFARE, DISCOUNTAMOUNT, PROMOTIONDISCOUNTCODE,
PROMOTIONDISCOUNTAMOUNT, UNIDENTIFIEDCHARGE, FOREIGNCURRENCYCODE, FOREIGNBASISFARE, FOREIGNDISCOUNTAMOUNT, FOREIGNPROMOTIONDISCOUNTAMOUNT,
FOREIGNNETFARE, FOREIGNAPF, FOREIGNFSF, FOREIGNISF, FOREIGNSSF, FOREIGNUNIDENTIFIEDCHARGE, RULENUMBER, Trim(FLIGHTNUMBER) FLIGHTNUMBER,
DEPARTURESTATION, ARRIVALSTATION, DEPARTUREDATE, CANCELED, DISCOUNTCODE, NULL FOREIGNNETFARECORRECTION,
NULL FOREIGNFSFCORRECTION, NULL FOREIGNAPFCORRECTION, NULL EJCORRECTION, NULL UNMATCHED , sysdate InsertedKST
From (
Select /*+ Use_Hash(I, V) */
V.* , I.LegID
From (Select RowNum, V_0.* from ejmispjsDailyinsertview@EJODS V_0 ) V
,EJMIS.InventoryLeg I
Where ( V.DepartureDate = I.DepartureDate and V.FlightNumber = I.FlightNumber
)
) V
,(Select SegmentID from EJMIS.PASSENGERJOURNEYSEGMENT) PJS
Where V.SegmentID = PJS.SegmentID(+)
and PJS.SegmentID IS NULL
;
Commit
;
Insert Into EJMIS.ZZJob Select 'INSERT INTO EJMIS.PASSENGERJOURNEYSEGMENT DepartureDate Base' , to_char(SysDate, 'YYYYMMDD') , SysDate , NULL From Dual;
Commit
;
---->> DepartureDate KST Base Loading Sql Sentence "ejmispjsinsertview_departure"
INSERT INTO EJMIS.PASSENGERJOURNEYSEGMENT
( SEGMENTID, LEGID, BOOKINGSTATUS, LIFTSTATUS, FARESTATUS, FAREBASIS, FAREDISCOUNTCODE, OVERBOOKINDICATOR, PASSENGERID,
CREATEDKST, MODIFIEDKST, CURRENCYCODE, APF, FSF, ISF, SSF, FAREBASISFARE, NETFARE, DISCOUNTAMOUNT, PROMOTIONDISCOUNTCODE,
PROMOTIONDISCOUNTAMOUNT, UNIDENTIFIEDCHARGE, FOREIGNCURRENCYCODE, FOREIGNBASISFARE, FOREIGNDISCOUNTAMOUNT, FOREIGNPROMOTIONDISCOUNTAMOUNT,
FOREIGNNETFARE, FOREIGNAPF, FOREIGNFSF, FOREIGNISF, FOREIGNSSF, FOREIGNUNIDENTIFIEDCHARGE, RULENUMBER, FLIGHTNUMBER,
DEPARTURESTATION, ARRIVALSTATION, DEPARTUREDATE, CANCELED, DISCOUNTCODE, FOREIGNNETFARECORRECTION,
FOREIGNFSFCORRECTION, FOREIGNAPFCORRECTION, EJCORRECTION, UNMATCHED, InsertedKST )
Select /*+ All_Rows */ V.SEGMENTID, LEGID, BOOKINGSTATUS, LIFTSTATUS, FARESTATUS, FAREBASIS, FAREDISCOUNTCODE, OVERBOOKINDICATOR, PASSENGERID,
CREATEDKST, MODIFIEDKST, CURRENCYCODE, APF, FSF, ISF, SSF, FAREBASISFARE, NETFARE, DISCOUNTAMOUNT, PROMOTIONDISCOUNTCODE,
PROMOTIONDISCOUNTAMOUNT, UNIDENTIFIEDCHARGE, FOREIGNCURRENCYCODE, FOREIGNBASISFARE, FOREIGNDISCOUNTAMOUNT, FOREIGNPROMOTIONDISCOUNTAMOUNT,
FOREIGNNETFARE, FOREIGNAPF, FOREIGNFSF, FOREIGNISF, FOREIGNSSF, FOREIGNUNIDENTIFIEDCHARGE, RULENUMBER, Trim(FLIGHTNUMBER) FLIGHTNUMBER,
DEPARTURESTATION, ARRIVALSTATION, DEPARTUREDATE, CANCELED, DISCOUNTCODE, NULL FOREIGNNETFARECORRECTION,
NULL FOREIGNFSFCORRECTION, NULL FOREIGNAPFCORRECTION, NULL EJCORRECTION, NULL UNMATCHED, sysdate InsertedKST
From (
Select /*+ Use_Hash(I, V) */
V.* , I.LegID
From (Select RowNum, V_0.* from ejmispjsinsertview_departure@EJODS V_0 ) V
,EJMIS.InventoryLeg I
Where ( V.DepartureDate = I.DepartureDate and V.FlightNumber = I.FlightNumber
)
) V
,(Select SegmentID from EJMIS.PASSENGERJOURNEYSEGMENT) PJS
Where V.SegmentID = PJS.SegmentID(+)
and PJS.SegmentID IS NULL
;
Commit
;
Insert Into EJMIS.ZZJob Select 'UPDATE EJMIS.PASSENGERJOURNEYSEGMENT U' , to_char(SysDate, 'YYYYMMDD') , SysDate , NULL From Dual;
Commit
;
--->> ModifiedDate KST Base Update
UPDATE EJMIS.PASSENGERJOURNEYSEGMENT U
SET ( BOOKINGSTATUS, LIFTSTATUS, FARESTATUS, FAREBASIS, FAREDISCOUNTCODE, OVERBOOKINDICATOR, PASSENGERID,
CREATEDKST, MODIFIEDKST, CURRENCYCODE, APF, FSF, ISF, SSF, FAREBASISFARE, NETFARE, DISCOUNTAMOUNT, PROMOTIONDISCOUNTCODE,
PROMOTIONDISCOUNTAMOUNT, UNIDENTIFIEDCHARGE, FOREIGNCURRENCYCODE, FOREIGNBASISFARE, FOREIGNDISCOUNTAMOUNT, FOREIGNPROMOTIONDISCOUNTAMOUNT,
FOREIGNNETFARE, FOREIGNFSF, FOREIGNAPF, FOREIGNISF, FOREIGNSSF, FOREIGNUNIDENTIFIEDCHARGE, RULENUMBER, FLIGHTNUMBER,
DEPARTURESTATION, ARRIVALSTATION, DISCOUNTCODE, UpdatedKST --DEPARTUREDATE, due to partition key ORA-14402 분할영역 키 열을 수정하는것은 분할영역 변경이 생깁니다
)
=
(
Select
BOOKINGSTATUS, LIFTSTATUS, FARESTATUS, FAREBASIS, FAREDISCOUNTCODE, OVERBOOKINDICATOR, PASSENGERID,
CREATEDKST, MODIFIEDKST, CURRENCYCODE, APF, FSF, ISF, SSF, FAREBASISFARE, NETFARE, DISCOUNTAMOUNT, PROMOTIONDISCOUNTCODE,
PROMOTIONDISCOUNTAMOUNT, UNIDENTIFIEDCHARGE, FOREIGNCURRENCYCODE, FOREIGNBASISFARE, FOREIGNDISCOUNTAMOUNT, FOREIGNPROMOTIONDISCOUNTAMOUNT,
FOREIGNNETFARE, FOREIGNFSF, FOREIGNAPF, FOREIGNISF, FOREIGNSSF, FOREIGNUNIDENTIFIEDCHARGE, RULENUMBER, Trim(FLIGHTNUMBER) FLIGHTNUMBER ,
DEPARTURESTATION, ARRIVALSTATION, DISCOUNTCODE, sysdate UpdatedKST --to_char(DEPARTUREDATE, 'YYYYMMDD') DEPARTUREDATE,
from EJODS.ejmispjsdailyupdateview D --ejmispjsdailyUpdateview@EJODS D View 에 Daily 개념 포함
Where U.SEGMENTID = D.SEGMENTID
)
Where SEGMENTID in (
SELECT segmentid
From EJODS.ejmispjsdailyupdateview D
)
;
Commit
;
Insert Into EJMIS.ZZJob Select 'INSERT INTO EJMIS.PASSENGERJOURNEYSEGMENTAUX' , to_char(SysDate, 'YYYYMMDD') , SysDate , NULL From Dual;
Commit
;
INSERT INTO EJMIS.PASSENGERJOURNEYSEGMENTAUX (
SEGMENTID, JOURNEYNUMBER, TRIPTYPE, CABINOFSERVICE, CLASSTYPE, CLASSOFSERVICE, TIMECHANGED,
CHANGEREASONCODE, CHANNELTYPE, CREATEDAGENTID, CREATEDAGENTCODE, CREATEDLOCATIONCODE, CREATEDDOMAINCODE,
CREATEDORGANIZATIONCODE, SOURCEAGENTCODE, SOURCEORGANIZATIONCODE, SOURCEDOMAINCODE, SOURCELOCATIONCODE, MODIFIEDAGENTID
)
Select /*+ Use_Hash( D P ) */
D.SEGMENTID, JOURNEYNUMBER, TRIPTYPE, CABINOFSERVICE, CLASSTYPE, CLASSOFSERVICE, TIMECHANGED,
CHANGEREASONCODE, CHANNELTYPE, CREATEDAGENTID, CREATEDAGENTCODE, CREATEDLOCATIONCODE, CREATEDDOMAINCODE,
CREATEDORGANIZATIONCODE, SOURCEAGENTCODE, SOURCEORGANIZATIONCODE, SOURCEDOMAINCODE, SOURCELOCATIONCODE, MODIFIEDAGENTID
from PassengerJourneySegment@EJODS D, (Select SegmentID from EJMIS.PassengerJourneySegmentAux) I
Where D.CreatedDate >= to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.CreatedDate < to_timestamp(to_char(trunc(sysdate -2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.SegmentID = I.SegmentID(+)
and I.SegmentID IS NULL
;
Commit
;
Insert Into EJMIS.ZZJob Select 'UPDATE EJMIS.PASSENGERJOURNEYSEGMENTAUX U' , to_char(SysDate, 'YYYYMMDD') , SysDate , NULL From Dual;
Commit
;
UPDATE EJMIS.PASSENGERJOURNEYSEGMENTAUX U
SET ( SEGMENTID, JOURNEYNUMBER, TRIPTYPE, CABINOFSERVICE, CLASSTYPE, CLASSOFSERVICE, TIMECHANGED,
CHANGEREASONCODE, CHANNELTYPE, CREATEDAGENTID, CREATEDAGENTCODE, CREATEDLOCATIONCODE, CREATEDDOMAINCODE,
CREATEDORGANIZATIONCODE, SOURCEAGENTCODE, SOURCEORGANIZATIONCODE, SOURCEDOMAINCODE, SOURCELOCATIONCODE, MODIFIEDAGENTID )
=
(
Select SEGMENTID, JOURNEYNUMBER, TRIPTYPE, CABINOFSERVICE, CLASSTYPE, CLASSOFSERVICE, TIMECHANGED,
CHANGEREASONCODE, CHANNELTYPE, CREATEDAGENTID, CREATEDAGENTCODE, CREATEDLOCATIONCODE, CREATEDDOMAINCODE,
CREATEDORGANIZATIONCODE, SOURCEAGENTCODE, SOURCEORGANIZATIONCODE, SOURCEDOMAINCODE, SOURCELOCATIONCODE, MODIFIEDAGENTID
from PassengerJourneySegment@EJODS D
Where D.ModifiedDate >= to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.ModifiedDate < to_timestamp(to_char(trunc(sysdate -2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.CreatedDate < to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and U.SegmentID = D.SegmentID
)
Where SEGMENTID in (
SELECT SEGMENTID
FROM PassengerJourneySegment@EJODS D
Where D.ModifiedDate >= to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.ModifiedDate < to_timestamp(to_char(trunc(sysdate -2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.CreatedDate < to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
)
;
Commit
;
/*********** Payment Information related to Daily Sales Report *************/
Insert Into EJMIS.ZZJob Select 'INSERT INTO EJMIS.PAYMENT' , to_char(SysDate, 'YYYYMMDD') , SysDate , NULL From Dual;
Commit
;
--Select min(CreatedKST), Max(createdKST), Min(ModifiedKST), Max(ModifiedKST) from EJODS.PaymentTemp
--select * from Payment Where paymentid in (2281755,2281758,2281754,2281753,2281756) ModifiedDate modified Case late
INSERT INTO EJMIS.PAYMENT (
PAYMENTID, REFERENCEID, PAYMENTMETHODTYPE,
PAYMENTMETHODCODE, PAYMENTAMOUNT, COLLECTEDAMOUNT,
QUOTEDAMOUNT, STATUS, PAYMENTFIELDS,
TRANSFERRED, CREATEDDATE, INSTALLMENTS,
VOID, MODIFIEDDATE, AUTHORIZATIONCODE,
AUTHORIZATIONSTATUS, PAYMENTTEXT, MODIFIEDAGENTID,
CREATEDAGENTID, MODIFIEDKST, CREATEDKST,
CURRENCYCODE, COLLECTEDCURRENCYCODE, QUOTEDCURRENCYCODE, PARENTPAYMENTID,
SALESPAYMENTID, SALESAGENTID, CARDCOMPANYCODE,
EJCORRECTION
)
Select /*+ Use_Hash(T, I) Leading(T)*/
T.PAYMENTID, REFERENCEID, PAYMENTMETHODTYPE,
PAYMENTMETHODCODE, PAYMENTAMOUNT, COLLECTEDAMOUNT,
QUOTEDAMOUNT, STATUS, PAYMENTFIELDS,
TRANSFERRED, CREATEDDATE, INSTALLMENTS,
VOID, MODIFIEDDATE, AUTHORIZATIONCODE,
AUTHORIZATIONSTATUS, PAYMENTTEXT, MODIFIEDAGENTID,
CREATEDAGENTID, MODIFIEDKST, CREATEDKST,
CURRENCYCODE, COLLECTEDCURRENCYCODE, QUOTEDCURRENCYCODE, PARENTPAYMENTID,
SALESPAYMENTID, SALESAGENTID, CARDCOMPANYCODE,
EJCORRECTION
From EJMISPAYMENTVIEW@EJODS T, (Select PaymentID from EJMIS.Payment) I
Where T.PaymentID = I.PaymentID(+)
and I.PaymentID IS NULL
;
Commit
;
Insert Into EJMIS.ZZJob Select 'Update Booking.SalesAgentID from EJMIS.PAYMENT' , to_char(SysDate, 'YYYYMMDD') , SysDate , NULL From Dual;
Commit
;
Update EJMIS.Booking B
Set SalesAgentID = (
Select SalesAgentID
From
(
Select ReferenceID, SalesAgentID
From EJMIS.Payment P
Where P.ModifiedDate >= to_char(trunc(sysdate -1-2+2, 'dd'), 'YYYYMMDD')
and P.ModifiedDate < to_char(trunc(sysdate -2+2 , 'dd'), 'YYYYMMDD')
and P.Status = 3
Group by ReferenceID, SalesAgentID
) P
Where P.ReferenceID = B.BookingID
)
Where B.BookingID in (Select ReferenceID
From EJMIS.Payment P
Where P.ModifiedDate >= to_char(trunc(sysdate -1-2+2, 'dd'), 'YYYYMMDD')
and P.ModifiedDate < to_char(trunc(sysdate -2+2 , 'dd'), 'YYYYMMDD')
and P.Status = 3
Group by ReferenceID
)
;
Commit
;
Insert Into EJMIS.ZZJob Select 'INSERT INTO EJMIS.PAYMENTAUX' , to_char(SysDate, 'YYYYMMDD') , SysDate , NULL From Dual;
Commit
;
INSERT INTO EJMIS.PAYMENTAUX (
PAYMENTID, CREATEDORGANIZATIONCODE, CREATEDLOCATIONCODE,
CREATEDDOMAINCODE, SOURCEAGENTCODE, SOURCELOCATIONCODE,
SOURCEORGANIZATIONCODE, SOURCEDOMAINCODE, REFERENCETYPE)
Select D.PAYMENTID, CREATEDORGANIZATIONCODE, CREATEDLOCATIONCODE,
CREATEDDOMAINCODE, SOURCEAGENTCODE, SOURCELOCATIONCODE,
SOURCEORGANIZATIONCODE, SOURCEDOMAINCODE, REFERENCETYPE
From Payment@EJODS D, (Select PaymentID from PaymentAux) PX
Where D.ModifiedDate >= to_timestamp(to_char(trunc(sysdate -1-2+2, 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.ModifiedDate < to_timestamp(to_char(trunc(sysdate -2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.PaymentID = PX.PaymentID(+)
and PX.PaymentID IS NULL
;
Commit
;
/*********** Update International Flag in Table Booking *************/
Insert Into EJMIS.ZZJob Select 'UPDATE EJMIS.BOOKING 1.International' , to_char(SysDate, 'YYYYMMDD') , SysDate , NULL From Dual;
Commit
;
Update Booking U Set International = (
Select Max(R.International) International
from Booking B, BookingPassenger BP, PassengerJourneySegment PJS, InventoryLeg IL, Route R
Where B.BookingID = BP.BookingID
and BP.PassengerID = PJS.PassengerID
and PJS.DepartureDate = IL.DepartureDate
and PJS.FlightNumber = IL.FlightNumber
and IL.DepartureStation = R.DepartureStation
and IL.ArrivalStation = R.ArrivalStation
and B.BookingID = U.BookingID
Group by B.BookingID -- Having Max(R.International) = 1
)
Where U.International IS NULL
;
Commit
;
Insert Into EJMIS.ZZJob Select 'UPDATE EJMIS.BOOKING 2.International for VOID' , to_char(SysDate, 'YYYYMMDD') , SysDate , NULL From Dual;
Commit
;
Update Booking U Set International = (
Select (Select Max(International) from Route R
Where (R.DepartureStation, R.ArrivalStation)
In (select DepartureStation, ArrivalStation
from PassengerJourneyLegVersion@EJODS PJLV
where PJLV.PassengerID in (
Select PassengerID from BookingPassenger BP
where BP.BookingID = B.BookingID
)
)
) International
from Booking B
Where International IS NULL
and U.BookingID = B.BookingID
--and SalesAgentID IS NOT NULL
)
Where U.International IS NULL
--and U.SalesAgentID IS NOT NULL
;
Commit
;
--******* 2011.07.20 added PassengerJourneyLeg@EJODS Delete Failure -- ex. 11771714 11783181
-- Dynamic SQL Development Required
EXECUTE IMMEDIATE 'Drop Table PJSegmentOnly';
EXECUTE IMMEDIATE 'Create Table PJSegmentOnly AS Select "SegmentID" SEGMENTID from PassengerJourneyLeg@NaviODS';
--Delete PJSegmentOnly
-- ;
-- Commit
--;
--Insert Into EJMIS.PJSegmentOnly Select "SegmentID" SEGMENTID from PassengerJourneyLeg@NaviODS
-- ;
-- Commit
--;
--******* Segment Removal Flagging works 2011.07.13
Insert Into EJMIS.ZZJob Select 'Update EJMIS.PassengerJourneySegment Set Removed = 1' , to_char(SysDate, 'YYYYMMDD') , SysDate , NULL From Dual;
Commit
;
Update EJMIS.PassengerJourneySegment Set Removed = 1, UpdatedKST = sysdate
Where SegmentID IN (
select /*+ Use_Hash(PJS, S) */
PJS.SegmentID --, PJS.LiftStatus, S.SegmentID SSSID
from passengerjourneysegment PJS, (select SegmentID from PJSegmentOnly) S
Where PJS.SegmentID = S.SegmentID(+)
and S.SegmentID is NULL
)
;
Commit
;
--Update EJMIS.PassengerJourneySegment Set Removed = 1
--Where SegmentID IN (
-- select /*+ Use_Hash(PJS, S) */
-- PJS.SegmentID --, PJS.LiftStatus, S.SegmentID SSSID
-- from passengerjourneysegment PJS, (select SegmentID from PassengerJourneyLeg@EJODS) S
-- Where PJS.SegmentID = S.SegmentID(+)
-- and S.SegmentID is NULL
-- )
-- ;
--Commit
--;
/*********** Fee Revenue *************/
Insert Into EJMIS.ZZJob Select 'INSERT INTO EJMIS.FEETYPE' , to_char(SysDate, 'YYYYMMDD') , SysDate , NULL From Dual;
Commit
;
INSERT INTO EJMIS.FEETYPE ( FEECODE, NAME, DESCRIPTION, DISPLAYCODE, FEETYPE, VAT, COUNTRYCODE, TRAVELCOMPONENT, FEEAPPLICATION,
TAXAPPLICATION, CHARGELIMIT, CHARGELIMITMODE, CHARGELIMITTRAVELCOMPONENT, MINSTOPOVER, MINSTOPOVERINTL, FEEOPTIONMODE, ITEMIZABLE, TICKETABLE,
COMMISSIONABLE, CREATEDAGENTID, CREATEDKST, MODIFIEDAGENTID, MODIFIEDKST)
Select D.FEECODE, NAME, DESCRIPTION, DISPLAYCODE, FEETYPE, '0' VAT, COUNTRYCODE, TRAVELCOMPONENT, FEEAPPLICATION, TAXAPPLICATION, CHARGELIMIT, CHARGELIMITMODE,
CHARGELIMITTRAVELCOMPONENT, MINSTOPOVER, MINSTOPOVERINTL, FEEOPTIONMODE, ITEMIZABLE, TICKETABLE, COMMISSIONABLE,
CREATEDAGENTID, CreatedDate +9/24, MODIFIEDAGENTID, ModifiedDate + 9/24
From Fee@EJODS D, (Select FeeCode from FeeType ) I
Where D.CreatedDate >= to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.CreatedDate < to_timestamp(to_char(trunc(sysdate -2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.FeeCode = I.FeeCode(+)
and I.FeeCode IS NULL
;
Commit
;
Insert Into EJMIS.ZZJob Select 'UPDATE EJMIS.FEETYPE U' , to_char(SysDate, 'YYYYMMDD') , SysDate , NULL From Dual;
Commit
;
UPDATE EJMIS.FEETYPE U
SET ( FEECODE, NAME, DESCRIPTION, DISPLAYCODE, FEETYPE, COUNTRYCODE, TRAVELCOMPONENT, FEEAPPLICATION,
TAXAPPLICATION, CHARGELIMIT, CHARGELIMITMODE, CHARGELIMITTRAVELCOMPONENT, MINSTOPOVER, MINSTOPOVERINTL, FEEOPTIONMODE, ITEMIZABLE, TICKETABLE,
COMMISSIONABLE, CREATEDAGENTID, CREATEDKST, MODIFIEDAGENTID, MODIFIEDKST
)
=
(
Select FEECODE, NAME, DESCRIPTION, DISPLAYCODE, FEETYPE, COUNTRYCODE, TRAVELCOMPONENT, FEEAPPLICATION,
TAXAPPLICATION, CHARGELIMIT, CHARGELIMITMODE, CHARGELIMITTRAVELCOMPONENT, MINSTOPOVER, MINSTOPOVERINTL, FEEOPTIONMODE, ITEMIZABLE, TICKETABLE,
COMMISSIONABLE, CREATEDAGENTID, CreatedDate+9/24 CREATEDKST, MODIFIEDAGENTID, ModifiedDate+9/24 MODIFIEDKST
from Fee@EJODS D
Where D.ModifiedDate >= to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.ModifiedDate < to_timestamp(to_char(trunc(sysdate -2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.CreatedDate < to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and U.FEECODE = D.FEECODE
)
Where FEECODE in (
SELECT FEECODE
FROM Fee@EJODS D
Where D.ModifiedDate >= to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.ModifiedDate < to_timestamp(to_char(trunc(sysdate -2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.CreatedDate < to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
)
;
Commit
;
Insert Into EJMIS.ZZJob Select 'INSERT INTO EJMIS.PASSENGERFEECHARGE' , to_char(SysDate, 'YYYYMMDD') , SysDate , NULL From Dual;
Commit
;
INSERT INTO EJMIS.PASSENGERFEECHARGE
( PASSENGERID, CHARGENUMBER, FEECODE, FEENUMBER, FEETYPE, CHARGETYPE,
FEEOVERRIDE, CHARGECODE, TICKETCODE, STATUS, CURRENCYCODE, CHARGEAMOUNT,
FOREIGNCURRENCYCODE, FOREIGNAMOUNT, FEEDETAIL, FEENOTE, CHARGEDETAIL, CREATEDAGENTID,
CREATEDDATE, CREATEDKST, ModifiedDate, ModifiedKST)
Select A.*
From
(Select /*+ USE_HASH( PFC PF ) */
PFC.PASSENGERID, PFC.CHARGENUMBER, PF.FEECODE, PF.FEENUMBER, PF.FEETYPE, PFC.CHARGETYPE,
PF.FEEOVERRIDE, PFC.CHARGECODE, PFC.TICKETCODE, PF.STATUS, PFC.CURRENCYCODE, PFC.CHARGEAMOUNT,
PFC.FOREIGNCURRENCYCODE, PFC.FOREIGNAMOUNT, PF.FEEDETAIL, PF.NOTE, PFC.CHARGEDETAIL,
PFC.CREATEDAGENTID,
to_char(PFC.CreatedDate+9/24, 'YYYYMMDD') CREATEDDATE , PFC.CreatedDate+9/24 CREATEDKST,
to_char(PF.ModifiedDate+9/24, 'YYYYMMDD') ModifiedDate, PF.ModifiedDate+9/24 ModifiedKST
from PassengerFeeCharge@EJODS PFC, PassengerFee@EJODS PF
Where PFC.PassengerID = PF.PassengerID
AND PFC.FeeNumber = PF.FeeNumber
AND PFC.ChargeCode = PF.FeeCode
AND (
( PF.CreatedDate >= to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and PF.CreatedDate < to_timestamp(to_char(trunc(sysdate -2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
)
OR
( PFC.CreatedDate >= to_timestamp(to_char(trunc(sysdate -1-2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and PFC.CreatedDate < to_timestamp(to_char(trunc(sysdate -2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
)
)
) A
Left Outer Join
EJMIS.PassengerFeeCharge B
ON ( A.PassengerID = B.PassengerID
and A.FeeNumber = B.FeeNumber
and A.ChargeNumber = B.ChargeNumber
and A.ChargeType = B.ChargeType
and A.ChargeCode = B.ChargeCode
and A.ChargeAmount = B.ChargeAmount
)
Where B.PassengerID IS NULL
Order by A.PassengerID, A.FeeNumber, A.ChargeNumber
;
Commit
;
Insert Into EJMIS.ZZJob Select 'UPDATE EJMIS.PASSENGERFEECHARGE U' , to_char(SysDate, 'YYYYMMDD') , SysDate , NULL From Dual;
Commit
;
UPDATE EJMIS.PASSENGERFEECHARGE U
SET ( FEETYPE, FEEOVERRIDE, STATUS, FEEDETAIL, FEENOTE, ModifiedDate, ModifiedKST
)
=
(
Select PF.FEETYPE, PF.FEEOVERRIDE, PF.STATUS, PF.FEEDETAIL, PF.NOTE, to_char(PF.ModifiedDate+9/24, 'YYYYMMDD') ModifiedDate, PF.ModifiedDate+9/24 ModifiedKST
from PassengerFee@EJODS PF
Where PF.ModifiedDate >= to_timestamp(to_char(trunc(sysdate -1-2+2, 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and PF.ModifiedDate < to_timestamp(to_char(trunc(sysdate -2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and PF.CreatedDate < to_timestamp(to_char(trunc(sysdate -1-2+2, 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
AND U.PassengerID = PF.PassengerID
AND U.FeeNumber = PF.FeeNumber
-- AND U.ChargeNumber = PF.ChargeNumber
AND U.ChargeType = PF.FeeType
AND U.ChargeCode = PF.FeeCode
-- AND U.ChargeAmount = PF.ChargeAmount
)
Where Exists (
SELECT 'X', PassengerID, FeeNumber, FeeCode
FROM PassengerFee@EJODS D
Where D.ModifiedDate >= to_timestamp(to_char(trunc(sysdate -1-2+2, 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.ModifiedDate < to_timestamp(to_char(trunc(sysdate -2+2 , 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
and D.CreatedDate < to_timestamp(to_char(trunc(sysdate -1-2+2, 'dd')-9/24, 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS')
AND U.PassengerID = D.PassengerID
AND U.FeeNumber = D.FeeNumber
-- AND U.ChargeNumber = D.ChargeNumber
AND U.ChargeType = D.FeeType
AND U.ChargeCode = D.FeeCode
--AND U.ChargeAmount = D.ChargeAmount
)
;
Commit
;
Insert Into EJMIS.ZZJob Select 'EJMIS Batch Migration Finished' , to_char(SysDate, 'YYYYMMDD') , SysDate , NULL From Dual;
Commit
;
---- tmpVar := 0;
---- EXCEPTION
---- WHEN NO_DATA_FOUND THEN
---- NULL;
---- WHEN OTHERS THEN
---- -- Consider logging the error and then re-raise
---- RAISE;
END Ledger_Migration_Daily;
/
'SQL' 카테고리의 다른 글
| Query Hints in MSSQL (0) | 2011.07.22 |
|---|---|
| Recursive CTE Structure [ ;WITH .... AS (... UNION ALL ...) SELECT ] (0) | 2011.07.22 |
| SYS_CONNECT_BY_PATH (0) | 2011.07.15 |
| ANSI Standards (0) | 2011.07.15 |
| SQL:2008 is the sixth revision of the ISO and ANSI standard for the SQL database query language.해 주세요. (0) | 2011.07.15 |