PROMPT Creating Package Body 'PRA_FLEXKEEPING'
CREATE OR REPLACE PACKAGE BODY PRA_FLEXKEEPING IS
-- Private Declaration
data_m CLOB;
url_m szmp_parametri.vrijednost%TYPE;
odgovor_m CLOB;
opis_m CLOB;
metcvor_rbr_m smet_cvorovi.rbr%TYPE; -- IRN 3991
log_file_name_m VARCHAR2(100); -- IRN 3991
/*
Oznake podataka koje MISH �alje servisu Flexkeeping: A = ime, B = prezime, C = datum ro�enja,
D = datum ro�enja (samo za ro�endane u vrijeme boravka), E = dr�avljanstvo ili nebitno.
*/
flex_param_1003_m szmp_parametri.vrijednost%TYPE;
TYPE booking_rec_type IS RECORD (
ObjectF m_so.sifra%TYPE,
Room m_sj.broj%TYPE,
-- PrevRoom -- Previous room identificator, if it has been changed from last time.
BookingId VARCHAR2(100),
Status VARCHAR2(20), -- RESERVED, CANCELLED, NO SHOW, CHECKED IN, CHECKED OUT, PROSPECT
Arrival VARCHAR2(16), -- YYYY-MM-DD 00:00
Departure VARCHAR2(16), -- YYYY-MM-DD 00:00
Vip t_rezervacije.ozn_vip%TYPE,
CheckIn VARCHAR2(16), -- YYYY-MM-DD 00:00
CheckOut VARCHAR2(16), -- YYYY-MM-DD 00:00
Adults t_rezervacije.br_osoba1%TYPE,
-- Traces
Message t_rezervacije.napomena%TYPE,
-- Guests
GuestId VARCHAR2(100),
FirstName t_rezervacije.ime%TYPE,
LastName t_rezervacije.prezime%TYPE,
BirthDate VARCHAR2(10), -- YYYY-MM-DD
Country s_zemlje.sifra_2a%TYPE
);
booking_rec_m booking_rec_type;
-- END Private Declaration
PROCEDURE INSERT_RECIDOM
(SO_SIFRA_P IN m_so.sifra%TYPE
,SJ_ID_P IN trec_idomacice.sj_id%TYPE
,OZN_DOMACICE1_P IN trec_idomacice.ozn_domacice1%TYPE
,UNOS_KORISNIK_P IN trec_idomacice.unos_korisnik%TYPE
,ODGOVOR_P OUT VARCHAR2
)
IS
-- poziva se iz procedure post_room_cleanness
BEGIN
IF pra_zmp_s.oznaka ('P0021', so_sifra_p) = 0 THEN -- IRN 3016
-- Nedopu�ten pristup!
pra_zmp_zaj.vrati_poruku (-20138);
END IF;
INSERT INTO trec_idomacice (sj_id, datum, ozn_domacice1, ozn_unosa, unos_korisnik, unos_dat)
VALUES (sj_id_p, TRUNC (SYSDATE), ozn_domacice1_p, 'A', unos_korisnik_p, SYSDATE);
odgovor_p := 'OK (INSERTED ' || SQL%ROWCOUNT || ')';
END;
PROCEDURE UPISI_RACUN
(SO_SIFRA_P IN m_so.sifra%TYPE
,SJ_ID_P IN m_sj.id%TYPE
,PRO_ID_P IN m_proizvodi.id%TYPE
,QUANTITY_P IN t_rnkase_pro.kolicina%TYPE
,BOOKING_ID_P IN VARCHAR2
,GMPKAS_ID_P IN mgmp_kase.id%TYPE
,ODGOVOR_P OUT VARCHAR2
)
IS
-- poziva se iz procedure post_room_minibar
CURSOR gost1_c IS
SELECT id, ozn_rnk, ime, prezime
FROM t_gosti
WHERE sj_id = sj_id_p
AND god_poslovna = SUBSTR (booking_id_p, 1, 4) -- booking_id_p npr. 2018-123456
AND rbr = SUBSTR (booking_id_p, 6, 6);
CURSOR gost2_c IS
SELECT id, ozn_rnk, ime, prezime
FROM t_gosti
WHERE sj_id = sj_id_p
AND TRUNC (SYSDATE) BETWEEN dat_dolaska AND dat_odlaska
AND ozn_prisutnosti = 'P'
ORDER BY ozn_platitelj, rbr;
gost_c_l gost1_c%ROWTYPE; -- ili gost2_c%ROWTYPE
CURSOR asokase_c IS
SELECT asokase.pc, asokase.nac_id
FROM m_aso_kase asokase,
m_nac_vpla nacvpla,
s_vplacanja vpla,
m_nacpot nac
WHERE asokase.kase_id = gmpkas_id_p
AND asokase.pro_id = pro_id_p
AND asokase.nac_id = nacvpla.nac_id
AND nacvpla.nac_id = nac.id
AND nacvpla.vpla_id = vpla.id
AND vpla.ozn_irn = '1'
ORDER BY vpla.sifra, nac.sifra;
cijena_l m_aso_kase.pc%TYPE;
nac_id_l m_aso_kase.nac_id%TYPE;
odgovor_l VARCHAR2(500);
rbr_l t_rnkase.rbr%TYPE;
BEGIN
IF pra_zmp_s.oznaka ('P0021', so_sifra_p) = 0 THEN -- IRN 3016
-- Nedopu�ten pristup!
pra_zmp_zaj.vrati_poruku (-20138);
END IF;
IF booking_id_p IS NOT NULL THEN -- npr. 2018-123456
OPEN gost1_c;
FETCH gost1_c INTO gost_c_l;
CLOSE gost1_c;
IF gost_c_l.id IS NULL THEN
-- Pogre�na vrijednost ulaznog parametra (#0)!
pra_zmp_zaj.vrati_poruku (-20099, 'BookingId ' || booking_id_p);
END IF;
IF gost_c_l.ozn_rnk = 0 THEN
-- Knji�enje RNK na gosta #0 #1 nije dopu�teno!
pra_pka_zaj.vrati_poruku (-20100, gost_c_l.ime, gost_c_l.prezime);
END IF;
ELSE
OPEN gost2_c;
FETCH gost2_c INTO gost_c_l;
CLOSE gost2_c;
IF gost_c_l.id IS NULL THEN
-- Nedostaje podatak (#0)!
pra_zmp_zaj.vrati_poruku (-20098, pra_zmp_zaj.poruka ('REC', 'REC_GOST'));
END IF;
IF gost_c_l.ozn_rnk = 0 THEN
-- Knji�enje RNK na gosta #0 #1 nije dopu�teno!
pra_pka_zaj.vrati_poruku (-20100, gost_c_l.ime, gost_c_l.prezime);
END IF;
END IF;
OPEN asokase_c;
FETCH asokase_c INTO cijena_l, nac_id_l;
CLOSE asokase_c;
IF cijena_l IS NULL OR nac_id_l IS NULL THEN
-- Nedostaje podatak (#0)!
pra_zmp_zaj.vrati_poruku (-20098, pra_zmp_zaj.poruka ('GAS', 'GAS_ASOKASE'));
END IF;
odgovor_l := 'OK';
pra_gas_pka.upisi_racun (
datum_p => TRUNC (SYSDATE),
sj_id_p => sj_id_p,
pro_id_p => pro_id_p,
kolicina_p => quantity_p,
cijena_p => cijena_l,
gost_id_p => gost_c_l.id,
kase_id_p => gmpkas_id_p,
nac_id_p => nac_id_l,
pkaadr_id_p => NULL,
age_id_p => NULL,
pkakar_id_p => NULL,
poruka_p => odgovor_l,
rbr_p => rbr_l);
odgovor_p := odgovor_l;
END;
FUNCTION DAT_RODJENJA
(OZNAKE_P IN szmp_parametri.vrijednost%TYPE
,GOST_ID_P IN t_gosti.id%TYPE
,DAT_DOLASKA_P IN t_gosti.dat_dolaska%TYPE
,DAT_ODLASKA_P IN t_gosti.dat_odlaska%TYPE
,DAT_RODJENJA_P IN t_kgostiju.dat_rodjenja%TYPE := NULL
)
RETURN t_kgostiju.dat_rodjenja%TYPE
IS
-- datum ro�enja; poziva se iz procedura post_init_booking, post_booking_gost i post_booking_kgost
dat_rodjenja_l t_kgostiju.dat_rodjenja%TYPE;
BEGIN
IF INSTR (oznake_p, 'C') = 0 -- datum ro�enja
AND INSTR (oznake_p, 'D') = 0 -- datum ro�enja (samo za ro�endane u vrijeme boravka)
THEN
RETURN (NULL);
END IF;
IF gost_id_p IS NULL
OR dat_dolaska_p IS NULL
OR dat_odlaska_p IS NULL
THEN
RETURN (NULL);
END IF;
IF dat_rodjenja_p IS NOT NULL THEN
dat_rodjenja_l := dat_rodjenja_p;
ELSE
SELECT MIN (dat_rodjenja) INTO dat_rodjenja_l
FROM t_kgostiju
WHERE gost_id = gost_id_p
AND oznaka = 'V';
IF dat_rodjenja_l IS NULL THEN
RETURN (NULL);
END IF;
END IF;
IF INSTR (oznake_p, 'C') > 0 THEN
RETURN (dat_rodjenja_l);
END IF;
IF ADD_MONTHS (dat_rodjenja_l, (pra_rec_s3.dob (dat_rodjenja_l, (dat_dolaska_p - 1)) + 1) * 12)
BETWEEN dat_dolaska_p AND dat_odlaska_p
THEN
RETURN (dat_rodjenja_l);
END IF;
RETURN (NULL);
END;
PROCEDURE ZAJ_POST
(ENDPOINT_P IN VARCHAR2
)
IS
--
timeout_l NUMBER;
api_key_l szmp_parametri.vrijednost%TYPE;
req_l UTL_HTTP.req;
res_l UTL_HTTP.resp;
line_l CLOB;
BEGIN
timeout_l := pra_zmp_zaj.zmppar_vrijednost ('FLEX', '1001');
IF endpoint_p LIKE '%initialization%' THEN -- IRN 3625
timeout_l := timeout_l * 2;
END IF;
api_key_l := pra_zmp_zaj.zmppar_vrijednost ('FLEX', '1002');
url_m := pra_zmp_zaj.zmppar_vrijednost ('FLEX', '1005') || endpoint_p;
-- IRN 3130; Spremati komunikaciju sa servisom Flexkeeping u log: da ili ne.
IF pra_zmp_zaj.zmppar_vrijednost ('FLEX', '1004') = 'da' THEN
pra_pka2.clob_to_table (opis_m || CHR(10), log_file_name_m, NULL, 'FLEX'); -- IRN 3532, IRN 3991
pra_pka2.clob_to_table (url_m || CHR(10), log_file_name_m, NULL, 'FLEX');
pra_pka2.clob_to_table (data_m || CHR(10), log_file_name_m, NULL, 'FLEX');
END IF;
UTL_HTTP.set_detailed_excp_support (enable => true);
UTL_HTTP.set_transfer_timeout (timeout => timeout_l);
UTL_HTTP.set_body_charset (charset => 'UTF-8');
req_l := UTL_HTTP.begin_request (url => url_m, method => 'POST', http_version => 'HTTP/1.1');
UTL_HTTP.set_header (req_l, 'Content-Type', 'application/json; charset=utf-8');
UTL_HTTP.set_header (req_l, 'Api-Key', api_key_l);
/*
IRN 3625
Dodao Pjer�30.3.2023
https://test-aminess.flexkeeping.com zahtjeva da postoji header sa stringom validnog (registriranog) user agenta (aplikacije)
Neki od mogu�i stringova su: PostmanRuntime/7.31.3,� Mozilla/5.0 (najnoviji), itd
Ovdje se predstavljamo kao Chrome browser
ako se ne po�alje User-Agent javi se gre�ka {"error":"Invalid realm 'aminess_copy2'"}
*/
UTL_HTTP.set_header (req_l, 'User-Agent', 'Mozilla/5.0');
DECLARE
req_length_l BINARY_INTEGER;
buffer_l VARCHAR2(2000);
amount_l PLS_INTEGER := 2000;
offset_l PLS_INTEGER := 1;
BEGIN
req_length_l := pra_pka2.clob_length (data_m);
IF req_length_l <= 32767 THEN
UTL_HTTP.set_header (req_l, 'Content-Length', req_length_l);
UTL_HTTP.write_text (req_l, data_m);
ELSE
UTL_HTTP.set_header (req_l, 'Transfer-Encoding', 'chunked');
WHILE (offset_l < req_length_l) LOOP
buffer_l := DBMS_LOB.substr (data_m, amount_l, offset_l);
UTL_HTTP.write_text (req_l, buffer_l);
offset_l := offset_l + amount_l;
END LOOP;
END IF;
END;
res_l := UTL_HTTP.get_response (req_l);
BEGIN
LOOP
UTL_HTTP.read_line (res_l, line_l);
odgovor_m := odgovor_m || line_l;
END LOOP;
UTL_HTTP.end_response (res_l);
EXCEPTION
WHEN UTL_HTTP.end_of_body THEN
UTL_HTTP.end_response (res_l);
END;
IF pra_zmp_zaj.zmppar_vrijednost ('FLEX', '1004') = 'da' THEN
pra_pka2.clob_to_table ('[' || TO_CHAR (SYSDATE, 'DD.MM.RRRR HH24:MI:SS') || '] ' || odgovor_m || CHR(10) || CHR(10), log_file_name_m, NULL, 'FLEX');
END IF;
EXCEPTION
WHEN OTHERS THEN
UTL_HTTP.end_response (res_l);
RAISE;
END;
PROCEDURE POST_ROOM_CLEANNESS
(OBJECT_P IN m_so.sifra%TYPE
,ROOM_P IN m_sj.broj%TYPE
,CLEANNESS_P IN VARCHAR2
,ODGOVOR_P OUT VARCHAR2
)
IS
-- proceduru poziva ORDS modul flexkeeping /flex/roomcleanness
CURSOR so_c IS
SELECT id, metcvor_sifra
FROM m_so
WHERE sifra = object_p;
so_c_l so_c%ROWTYPE;
sj_id_l m_sj.id%TYPE;
ozn_domacice1_l trec_idomacice.ozn_domacice1%TYPE;
unos_korisnik_l trec_idomacice.unos_korisnik%TYPE := zmp_zaj.korisnik (-1);
naredba_l VARCHAR2(1000);
BEGIN
-- IRN 3991
log_file_name_m := 'flexkeeping-' || TO_CHAR (SYSDATE, 'RRRR-MM-DD') || '.log';
opis_m :=
'[' || TO_CHAR (SYSDATE, 'DD.MM.RRRR HH24:MI:SS') || '] ' ||
'{Room Cleanness} < ' || object_p || '; ' || room_p || '; ' || cleanness_p;
-- KRAJ IRN 3991
-- Nedostaje obvezan ulazni parametar (#0).
IF object_p IS NULL THEN
pra_rec_zaj.vrati_poruku (-20422, 'Object');
ELSIF room_p IS NULL THEN
pra_rec_zaj.vrati_poruku (-20422, 'Room');
ELSIF cleanness_p IS NULL THEN
pra_rec_zaj.vrati_poruku (-20422, 'Cleanness');
END IF;
OPEN so_c;
FETCH so_c INTO so_c_l;
CLOSE so_c;
IF so_c_l.id IS NULL THEN
-- Pogre�na vrijednost ulaznog parametra (#0)!
pra_zmp_zaj.vrati_poruku (-20099, 'Object ' || object_p);
END IF;
SELECT MIN(id) INTO sj_id_l FROM m_sj WHERE so_id = so_c_l.id AND broj = room_p;
IF sj_id_l IS NULL THEN
-- Pogre�na vrijednost ulaznog parametra (#0)!
pra_zmp_zaj.vrati_poruku (-20099, 'Room ' || room_p);
END IF;
-- Cleanness status. Available: CL = cleaned, DI = dirty, IN = inspected (optional)
IF cleanness_p = 'CL' THEN ozn_domacice1_l := 2; -- �ista
ELSIF cleanness_p = 'DI' THEN ozn_domacice1_l := 5; -- prljava
ELSIF cleanness_p = 'IN' THEN ozn_domacice1_l := 1; -- pregledana
-- IRN 3606; PI = pickup (Ready for final inspection)
ELSIF cleanness_p = 'PI' THEN ozn_domacice1_l := 2; -- �ista
ELSE
-- Pogre�na vrijednost ulaznog parametra (#0)!
pra_zmp_zaj.vrati_poruku (-20099, 'Cleanness ' || cleanness_p);
END IF;
-- IRN 3016, dodan parametar object_p
IF so_c_l.metcvor_sifra = pra_zmp_zaj.tekuci_cvor THEN
pra_flexkeeping.insert_recidom (object_p, sj_id_l, ozn_domacice1_l, unos_korisnik_l, odgovor_p);
ELSE
-- radi samo ako se poziva sa centra
naredba_l := 'BEGIN pra_flexkeeping.insert_recidom<CVOR> (:object_p, :sj_id_l, :ozn_domacice1_l, :unos_korisnik_l, :odgovor_p); END;';
EXECUTE IMMEDIATE REPLACE (naredba_l, '<CVOR>', '@' || so_c_l.metcvor_sifra)
USING object_p, sj_id_l, ozn_domacice1_l, unos_korisnik_l, OUT odgovor_p;
END IF;
EXCEPTION
WHEN OTHERS THEN
odgovor_p := SUBSTR ('ERROR ' || SQLERRM, 1, 2000);
pra_pka2.clob_to_table (opis_m || CHR(10) || SQLERRM || CHR(10) || CHR(10), log_file_name_m, NULL, 'FLEX'); -- IRN 3991
END;
PROCEDURE POST_ROOM_MINIBAR
(OBJECT_P IN m_so.sifra%TYPE
,ROOM_P IN m_sj.broj%TYPE
,BOOKING_ID_P IN VARCHAR2
,ITEM_ID_P IN m_proizvodi.sif_proizvoda_kasa%TYPE
,QUANTITY_P IN t_rnkase_pro.kolicina%TYPE
,ODGOVOR_P OUT VARCHAR2
)
IS
-- proceduru poziva ORDS modul flexkeeping endpoint /flex/roomminibar
CURSOR so_c IS
SELECT id, metcvor_sifra, ojr_id
FROM m_so
WHERE sifra = object_p;
so_c_l so_c%ROWTYPE;
sj_id_l m_sj.id%TYPE;
CURSOR gmpkas_c IS
SELECT id
FROM mgmp_kase
WHERE metapl_sifra = 'GAS'
AND ozn_kase = 'M'
AND zmpojr_id = so_c_l.ojr_id
AND NVL (so_id, so_c_l.id) = so_c_l.id
AND TRUNC (SYSDATE) BETWEEN dat_valjan AND (NVL (dat_ponisten, TO_DATE ('31.12.9999', 'DD.MM.RRRR')) - 1)
ORDER BY sifra;
gmpkas_id_l mgmp_kase.id%TYPE;
pro_id_l m_proizvodi.id%TYPE;
odgovor_l VARCHAR2(2000);
naredba_l VARCHAR2(1000);
booking_id_l VARCHAR2(20);
BEGIN
-- IRN 3991
log_file_name_m := 'flexkeeping-' || TO_CHAR (SYSDATE, 'RRRR-MM-DD') || '.log';
opis_m :=
'[' || TO_CHAR (SYSDATE, 'DD.MM.RRRR HH24:MI:SS') || '] ' ||
'{Room Minibar} < ' || object_p || '; ' || room_p || '; ' || booking_id_p || '; ' || item_id_p || '; ' || quantity_p;
-- KRAJ IRN 3991
-- Nedostaje obvezan ulazni parametar (#0).
IF object_p IS NULL THEN
pra_rec_zaj.vrati_poruku (-20422, 'Object');
ELSIF room_p IS NULL THEN
pra_rec_zaj.vrati_poruku (-20422, 'Room');
-- booking_id_p -- mo�e biti NULL
ELSIF item_id_p IS NULL THEN
pra_rec_zaj.vrati_poruku (-20422, 'ItemId');
ELSIF quantity_p IS NULL THEN
pra_rec_zaj.vrati_poruku (-20422, 'Quantity');
END IF;
OPEN so_c;
FETCH so_c INTO so_c_l;
CLOSE so_c;
IF so_c_l.id IS NULL THEN
-- Pogre�na vrijednost ulaznog parametra (#0)!
pra_zmp_zaj.vrati_poruku (-20099, 'Object ' || object_p);
END IF;
SELECT MIN(id) INTO sj_id_l FROM m_sj WHERE so_id = so_c_l.id AND broj = room_p;
IF sj_id_l IS NULL THEN
-- Pogre�na vrijednost ulaznog parametra (#0)!
pra_zmp_zaj.vrati_poruku (-20099, 'Room ' || room_p);
END IF;
OPEN gmpkas_c;
FETCH gmpkas_c INTO gmpkas_id_l;
CLOSE gmpkas_c;
IF gmpkas_id_l IS NULL THEN
-- #0 #1 --> Ne postoji kasa vrste minibar.
pra_zmp_zaj.vrati_poruku (-20000, 'GAS0632', pra_zmp_zaj.poruka ('GAS', 'GAS0632'));
END IF;
SELECT MIN (id) INTO pro_id_l FROM m_proizvodi WHERE sif_proizvoda_kasa = item_id_p;
IF pro_id_l IS NULL THEN
-- Pogre�na vrijednost ulaznog parametra (#0)!
pra_zmp_zaj.vrati_poruku (-20099, 'ItemId ' || item_id_p);
END IF;
-- IRN 2974; ukloni metcvor_rbr iz booking_id npr. 101-2025-123456 u 2025-123456
booking_id_l := SUBSTR (booking_id_p, INSTR (booking_id_p, '-') + 1);
-- IRN 3016; dodan parametar object_p
IF so_c_l.metcvor_sifra = pra_zmp_zaj.tekuci_cvor THEN
pra_flexkeeping.upisi_racun (object_p, sj_id_l, pro_id_l, quantity_p, booking_id_l, gmpkas_id_l, odgovor_l);
ELSE
-- radi samo ako se poziva sa centra
naredba_l := 'BEGIN pra_flexkeeping.upisi_racun<CVOR> (:object_p, :sj_id_l, :pro_id_l, :quantity_p, :booking_id_l, :gmpkas_id_l, :odgovor_l); END;';
EXECUTE IMMEDIATE REPLACE (naredba_l, '<CVOR>', '@' || so_c_l.metcvor_sifra)
USING object_p, sj_id_l, pro_id_l, quantity_p, booking_id_l, gmpkas_id_l, OUT odgovor_l;
END IF;
IF odgovor_l = 'OK' THEN
odgovor_p := odgovor_l;
ELSE
-- #0 #1
pra_zmp_zaj.vrati_poruku (-20000, odgovor_l, '{Room Minibar}');
END IF;
EXCEPTION
WHEN OTHERS THEN
odgovor_p := SUBSTR ('ERROR ' || SQLERRM, 1, 2000);
pra_pka2.clob_to_table (opis_m || CHR(10) || SQLERRM || CHR(10) || CHR(10), log_file_name_m, NULL, 'FLEX'); -- IRN 3991
END;
PROCEDURE POST_INIT_BOOKING
IS
-- Initialization Booking
-- proceduru poziva procedura post2flexkeeping_job
BEGIN
/*
Oznake podataka koje MISH �alje servisu Flexkeeping: A = ime, B = prezime, C = datum ro�enja,
D = datum ro�enja (samo za ro�endane u vrijeme boravka), E = dr�avljanstvo ili nebitno.
*/
flex_param_1003_m := pra_zmp_zaj.zmppar_vrijednost ('FLEX', '1003');
-- IRN 3991
metcvor_rbr_m := pra_zmp_zaj.tekuci_cvor_rbr;
log_file_name_m := 'flexkeeping-' || TO_CHAR (SYSDATE, 'RRRR-MM-DD') || '.log';
opis_m := '[' || TO_CHAR (SYSDATE, 'DD.MM.RRRR HH24:MI:SS') || '] {InitBooking}';
-- KRAJ IRN 3991
APEX_JSON.initialize_clob_output;
APEX_JSON.open_array; -- [
FOR c IN (
-- rezervacije
SELECT
-1 AS ozn_rez,
so.sifra AS so_sifra,
sj.broj AS sj_broj,
so.sifra || '-' || rez.god_poslovna || '-' || rez.rbr || '-' || rezvsj.id AS rbr,
'RESERVED' AS status,
TO_CHAR (rez.dat_dolaska, 'YYYY-MM-DD') || ' 00:00' AS dat_dolaska,
TO_CHAR (rez.dat_odlaska, 'YYYY-MM-DD') || ' 00:00' AS dat_odlaska,
TO_CHAR (NULL) AS unos_dat,
TO_CHAR (NULL) AS sat_odjave,
DECODE (INSTR (flex_param_1003_m, 'A'), 0, NULL, TRIM (rez.ime)) AS ime,
DECODE (INSTR (flex_param_1003_m, 'B'), 0, NULL, TRIM (rez.prezime)) AS prezime,
TO_CHAR (NULL) AS dat_rodjenja,
DECODE (INSTR (flex_param_1003_m, 'E'), 0, NULL, zem.sifra_2a) AS zem_sifra_2a,
rezvsj.br_osoba AS adults,
rez.ozn_vip AS ozn_vip,
rez.napomena AS napomena
FROM
(
SELECT id, rez_id, sj_id, br_osoba
FROM t_rez_vsj
WHERE sj_id IS NOT NULL
AND kol_sjz IS NULL -- jo� nije checked in
UNION
SELECT recrezsj.id, rezvsj2.rez_id, recrezsj.sj_id, recrezsj.br_osoba
FROM trec_rez_sj recrezsj,
t_rez_vsj rezvsj2
WHERE recrezsj.rezvsj_id = rezvsj2.id
AND recrezsj.br_osobaz IS NULL -- jo� nije checked in
) rezvsj,
m_sj sj,
t_rezervacije rez,
m_so so,
s_zemlje zem
WHERE
rezvsj.rez_id = rez.id
AND rezvsj.sj_id = sj.id
AND rez.so_id = so.id
AND NVL (rez.zem_id, rez.zemd_id) = zem.id(+)
AND rez.oznaka = 'V'
AND rez.ozn_vstorna IS NULL
AND rez.dat_dolaska >= TRUNC (SYSDATE)
AND pra_zmp_s.oznaka ('P0021', so.sifra) = -1
UNION
-- gosti
SELECT
0 AS ozn_rez,
so.sifra AS so_sifra,
sj.broj AS sj_broj,
metcvor_rbr_m || '-' || gost.god_poslovna || '-' || gost.rbr AS rbr, -- IRN 2974, IRN 3991
DECODE (gost.ozn_prisutnosti, 'P', 'CHECKED IN', 'CHECKED OUT') AS status,
TO_CHAR (gost.dat_dolaska, 'YYYY-MM-DD') || ' 00:00' AS dat_dolaska, -- IRN 3174
TO_CHAR (gost.dat_odlaska, 'YYYY-MM-DD') || ' 00:00' AS dat_odlaska, -- IRN 3174
TO_CHAR (gost.unos_dat, 'YYYY-MM-DD HH24:MI') AS unos_dat, -- IRN 3174
DECODE (gost.ozn_prisutnosti, 'P', NULL, TO_CHAR (gost.sat_odjave, 'YYYY-MM-DD HH24:MI')) AS sat_odjave, -- IRN 3174
DECODE (INSTR (flex_param_1003_m, 'A'), 0, NULL, TRIM (gost.ime)) AS ime,
DECODE (INSTR (flex_param_1003_m, 'B'), 0, NULL, TRIM (gost.prezime)) AS prezime,
TO_CHAR (pra_flexkeeping.dat_rodjenja (flex_param_1003_m, gost.id, gost.dat_dolaska, gost.dat_odlaska), 'YYYY-MM-DD') AS dat_rodjenja,
DECODE (INSTR (flex_param_1003_m, 'E'), 0, NULL, zem.sifra_2a) AS zem_sifra_2a,
1 AS adults,
gost.ozn_vip AS ozn_vip,
TO_CHAR (NULL) AS napomena
FROM
t_gosti gost,
m_sj sj,
m_so so,
s_zemlje zem
WHERE
TRUNC (SYSDATE) BETWEEN gost.dat_dolaska AND GREATEST (gost.dat_odlaska, NVL (TRUNC (gost.sat_odjave), gost.dat_odlaska))
AND NVL (gost.ozn_brisano, 0) = 0
AND gost.id NOT IN (SELECT id FROM trec_kplovila)
AND gost.sj_id = sj.id
AND sj.so_id = so.id
AND gost.zem_id = zem.id
AND pra_zmp_s.oznaka ('P0021', so.sifra) = -1 -- IRN 3016
ORDER BY 1, 2, 3, 4
)
LOOP
APEX_JSON.open_object; -- {
APEX_JSON.write ('Object', c.so_sifra);
APEX_JSON.write ('Room', c.sj_broj);
APEX_JSON.write ('BookingId', c.rbr);
APEX_JSON.write ('Status', c.status);
APEX_JSON.write ('Arrival', c.dat_dolaska);
APEX_JSON.write ('Departure', c.dat_odlaska);
APEX_JSON.write ('Vip', c.ozn_vip, TRUE);
APEX_JSON.write ('CheckIn', c.unos_dat, TRUE);
APEX_JSON.write ('CheckOut', c.sat_odjave, TRUE);
APEX_JSON.write ('Adults', c.adults);
IF c.napomena IS NOT NULL THEN
APEX_JSON.open_array ('Traces'); -- [
APEX_JSON.open_object; -- {
APEX_JSON.write ('DepartmentId', 'housekeeping');
APEX_JSON.write ('Message', c.napomena);
APEX_JSON.close_object; -- }
APEX_JSON.close_array; -- ]
END IF;
IF c.ozn_rez = -1 THEN
-- rezervacije
APEX_JSON.write ('GuestId', TO_CHAR (NULL), TRUE);
ELSE
-- gosti
APEX_JSON.write ('GuestId', c.rbr);
END IF;
APEX_JSON.write ('FirstName', c.ime, TRUE);
APEX_JSON.write ('LastName', c.prezime, TRUE);
APEX_JSON.write ('BirthDate', c.dat_rodjenja, TRUE);
APEX_JSON.write ('Country', c.zem_sifra_2a, TRUE);
APEX_JSON.close_object; -- }
END LOOP;
APEX_JSON.close_array; -- ]
data_m := APEX_JSON.get_clob_output;
data_m := REPLACE (data_m, CHR(10), ''); -- ukloni suvi�ne CrLf
data_m := REPLACE (data_m, '},', '},' || CHR(10)); -- jedan zapis u jedan redak
APEX_JSON.free_output;
IF data_m IS NULL THEN
RETURN;
END IF;
odgovor_m := NULL;
zaj_post ('/pms/initialization_booking');
EXCEPTION
WHEN OTHERS THEN
pra_pka2.clob_to_table (opis_m || CHR(10) || SQLERRM || CHR(10) || CHR(10), log_file_name_m, NULL, 'FLEX'); -- IRN 3991
END;
PROCEDURE POST_INIT_ROOM_STATUS
IS
-- Initialization Room Status
-- proceduru poziva procedura post2flexkeeping_job
BEGIN
-- IRN 3991
log_file_name_m := 'flexkeeping-' || TO_CHAR (SYSDATE, 'RRRR-MM-DD') || '.log';
opis_m := '[' || TO_CHAR (SYSDATE, 'DD.MM.RRRR HH24:MI:SS') || '] {InitRoomStatus}';
-- KRAJ IRN 3991
APEX_JSON.initialize_clob_output;
APEX_JSON.open_array; -- [
FOR c IN (
SELECT
so.sifra AS so_sifra,
sj.broj AS sj_broj,
so.sifra || '-' || recodr.god_poslovna || '-' || recodr.rbr AS recodr_rbr, -- IRN 2974
TO_CHAR (recodr.dat_poc, 'YYYY-MM-DD') AS recodr_dat_poc,
TO_CHAR (recodr.dat_zav, 'YYYY-MM-DD') AS recodr_dat_zav,
CASE -- 13.8.2018.
WHEN recodr.dat_poc = recodr.dat_zav AND recodr.dat_zav <= TRUNC (SYSDATE) THEN
TO_CHAR (recodr.dat_zav, 'YYYY-MM-DD')
WHEN recodr.dat_poc < recodr.dat_zav AND (recodr.dat_zav - 1) <= TRUNC (SYSDATE) THEN
TO_CHAR ((recodr.dat_zav - 1), 'YYYY-MM-DD')
ELSE
TO_CHAR (NULL)
END AS completed,
DECODE (recodr.ozn_i, 2, 'OOS', 3, 'OOO') AS status,
TRIM (recodr.napomena) AS recodr_napomena
FROM
trec_odrzavanje recodr,
m_sj sj,
m_so so
WHERE recodr.oznaka = 'V'
AND TRUNC (SYSDATE) BETWEEN recodr.dat_poc AND recodr.dat_zav
AND recodr.sj_id = sj.id
AND sj.so_id = so.id
AND recodr.ozn_i IN (2, 3) -- u kvaru ili izvan upotrebe
AND pra_zmp_s.oznaka ('P0021', so.sifra) = -1 -- IRN 3016
ORDER BY so.sifra, sj.broj -- IRN 2974
)
LOOP
APEX_JSON.open_object; -- {
APEX_JSON.write ('Object', c.so_sifra);
APEX_JSON.write ('Room', c.sj_broj);
APEX_JSON.write ('RoomStatusId', c.recodr_rbr);
APEX_JSON.write ('Begin', c.recodr_dat_poc);
APEX_JSON.write ('End', c.recodr_dat_zav);
APEX_JSON.write ('Completed', c.completed, TRUE);
APEX_JSON.write ('Status', c.status);
APEX_JSON.write ('Reason', c.recodr_napomena);
APEX_JSON.close_object; -- }
END LOOP;
APEX_JSON.close_array; -- ]
data_m := APEX_JSON.get_clob_output;
data_m := REPLACE (data_m, CHR(10), '');
data_m := REPLACE (data_m, '},', '},' || CHR(10));
APEX_JSON.free_output;
IF data_m IS NULL THEN
RETURN;
END IF;
odgovor_m := NULL;
zaj_post ('/pms/initialization_roomstatus');
EXCEPTION
WHEN OTHERS THEN
pra_pka2.clob_to_table (opis_m || CHR(10) || SQLERRM || CHR(10) || CHR(10), log_file_name_m, NULL, 'FLEX'); -- IRN 3991
END;
PROCEDURE POST2FLEXKEEPING_JOB
(VLASNIKPODATKA_P IN NUMBER := NULL
)
IS
-- proceduru poziva JOB
BEGIN
pra_zmp_context.set_vlasnikpodatka (vlasnikpodatka_p);
post_init_booking;
post_init_room_status;
END;
PROCEDURE POST_BOOKING_GOST
(ZAPIS_P IN tab_t_gosti.gost_rec_type
)
IS
-- Booking
-- proceduru pozivaju AIS_T_GOSTI i AUS_T_GOSTI
CURSOR sj_c (sj_id_k m_sj.id%TYPE) IS
SELECT so.sifra, sj.broj
FROM m_sj sj, m_so so
WHERE sj.id = sj_id_k
AND sj.so_id = so.id;
so_sifra_old_l m_so.sifra%TYPE; -- IRN 3991
sj_broj_old_l m_sj.broj%TYPE; -- IRN 3991
BEGIN
IF pra_zmp_zaj.zmppar_vrijednost ('FLEX', '1001') = 'nebitno' -- timeout
OR zapis_p.sj_id_new IS NULL -- IRN 3532
THEN
RETURN;
END IF;
-- IRN 3991
log_file_name_m := 'flexkeeping-' || TO_CHAR (SYSDATE, 'RRRR-MM-DD') || '.log';
opis_m := '[' || TO_CHAR (SYSDATE, 'DD.MM.RRRR HH24:MI:SS') || '] {Booking}';
-- KRAJ IRN 3991
/*
Oznake podataka koje MISH �alje servisu Flexkeeping: A = ime, B = prezime, C = datum ro�enja,
D = datum ro�enja (samo za ro�endane u vrijeme boravka), E = dr�avljanstvo ili nebitno.
*/
flex_param_1003_m := pra_zmp_zaj.zmppar_vrijednost ('FLEX', '1003');
opis_m := opis_m || ' [*** post_booking_gost'; -- IRN 3532
IF INSERTING THEN
opis_m := opis_m || ' | inserting | '; -- IRN 3532
NULL;
ELSIF UPDATING
AND (
zapis_p.sj_id_new <> zapis_p.sj_id_old
-- zapis_p.god_poslovna
-- zapis_p.rbr
OR zapis_p.ozn_prisutnosti_new <> zapis_p.ozn_prisutnosti_old
OR zapis_p.dat_dolaska_new <> zapis_p.dat_dolaska_old
OR zapis_p.dat_odlaska_new <> zapis_p.dat_odlaska_old
-- zapis_p.unos_dat
-- zapis_p.sat_odjave
OR (zapis_p.ime_new <> zapis_p.ime_old AND INSTR (flex_param_1003_m, 'A') > 0)
OR (zapis_p.prezime_new <> zapis_p.prezime_old AND INSTR (flex_param_1003_m, 'B') > 0)
-- zapis_p.gost_id
OR (zapis_p.zem_id_new <> zapis_p.zem_id_old AND INSTR (flex_param_1003_m, 'E') > 0)
OR NVL (zapis_p.ozn_vip_new, '�') <> NVL (zapis_p.ozn_vip_old, '�')
)
THEN
opis_m := opis_m || ' | updating | '; -- IRN 3532
NULL;
ELSE
RETURN;
END IF;
opis_m := opis_m || zmp_zaj.korisnik (-1) || ' | ' || zapis_p.ime_new || ' | ' || zapis_p.prezime_new || ']'; -- IRN 3532
booking_rec_m := NULL;
OPEN sj_c (zapis_p.sj_id_new);
FETCH sj_c INTO booking_rec_m.ObjectF, booking_rec_m.Room;
CLOSE sj_c;
IF booking_rec_m.ObjectF IS NULL
OR (INSERTING AND pra_zmp_s.oznaka ('P0021', booking_rec_m.ObjectF) = 0)
THEN
RETURN; -- podaci za promatrani SO ne �alju se u Flexkeeping
END IF;
IF UPDATING THEN
OPEN sj_c (zapis_p.sj_id_old);
FETCH sj_c INTO so_sifra_old_l, sj_broj_old_l;
CLOSE sj_c;
IF so_sifra_old_l IS NULL
OR (pra_zmp_s.oznaka ('P0021', so_sifra_old_l) = 0
AND pra_zmp_s.oznaka ('P0021', booking_rec_m.ObjectF) = 0)
THEN
RETURN; -- podaci za promatrane SO ne �alju se u Flexkeeping
END IF;
END IF;
metcvor_rbr_m := pra_zmp_zaj.tekuci_cvor_rbr; -- IRN 3991
booking_rec_m.BookingId := metcvor_rbr_m || '-' || zapis_p.god_poslovna || '-' || zapis_p.rbr; -- IRN 3991
IF UPDATING AND pra_zmp_s.oznaka ('P0021', booking_rec_m.ObjectF) = 0 THEN -- IRN 3991
-- odjavi gosta iz stare SJ jer seli u ne-Flex SJ
booking_rec_m.ObjectF := so_sifra_old_l;
booking_rec_m.Room := sj_broj_old_l;
booking_rec_m.Status := 'CHECKED OUT';
booking_rec_m.CheckOut := TO_CHAR (SYSDATE, 'YYYY-MM-DD HH24:MI');
booking_rec_m.Departure := TO_CHAR (SYSDATE, 'YYYY-MM-DD') || ' 00:00';
ELSE
IF zapis_p.ozn_prisutnosti_new = 'P' THEN
booking_rec_m.Status := 'CHECKED IN';
ELSE
booking_rec_m.Status := 'CHECKED OUT';
booking_rec_m.CheckOut := TO_CHAR (zapis_p.sat_odjave, 'YYYY-MM-DD HH24:MI');
END IF;
booking_rec_m.Departure := TO_CHAR (zapis_p.dat_odlaska_new, 'YYYY-MM-DD') || ' 00:00';
END IF;
booking_rec_m.Arrival := TO_CHAR (zapis_p.dat_dolaska_new, 'YYYY-MM-DD') || ' 00:00';
booking_rec_m.Vip := zapis_p.ozn_vip_new;
booking_rec_m.CheckIn := TO_CHAR (zapis_p.unos_dat, 'YYYY-MM-DD HH24:MI');
booking_rec_m.Adults := 1;
-- Guests
booking_rec_m.GuestId := metcvor_rbr_m || '-' || zapis_p.god_poslovna || '-' || zapis_p.rbr; -- IRN 3991
IF INSTR (flex_param_1003_m, 'A') > 0 THEN
booking_rec_m.FirstName := TRIM (zapis_p.ime_new);
END IF;
IF INSTR (flex_param_1003_m, 'B') > 0 THEN
booking_rec_m.LastName := TRIM (zapis_p.prezime_new);
END IF;
IF UPDATING THEN
booking_rec_m.BirthDate := TO_CHAR (pra_flexkeeping.dat_rodjenja (flex_param_1003_m, zapis_p.gost_id, zapis_p.dat_dolaska_new, zapis_p.dat_odlaska_new), 'YYYY-MM-DD');
END IF;
IF INSTR (flex_param_1003_m, 'E') > 0 THEN
SELECT MIN (sifra_2a) INTO booking_rec_m.Country FROM s_zemlje WHERE id = zapis_p.zem_id_new;
END IF;
post_booking_zaj (NULL);
EXCEPTION
WHEN OTHERS THEN
pra_pka2.clob_to_table (opis_m || CHR(10) || SQLERRM || CHR(10) || CHR(10), log_file_name_m, NULL, 'FLEX'); -- IRN 3991
END;
PROCEDURE POST_BOOKING_KGOST
(GOST_ID_NEW_P IN t_gosti.id%TYPE
,GOST_ID_OLD_P IN t_gosti.id%TYPE
,DAT_RODJENJA_NEW_P IN t_kgostiju.dat_rodjenja%TYPE
,DAT_RODJENJA_OLD_P IN t_kgostiju.dat_rodjenja%TYPE
)
IS
-- Booking
-- proceduru pozivaju AIS_T_KGOSTIJU i AUS_T_KGOSTIJU
dat_null_l DATE := TO_DATE ('01.01.1800', 'DD.MM.RRRR');
CURSOR gost_c (gost_id_k IN t_gosti.id%TYPE) IS
SELECT dat_dolaska, dat_odlaska
FROM t_gosti
WHERE id = gost_id_k;
dat_dolaska_l t_gosti.dat_dolaska%TYPE;
dat_odlaska_l t_gosti.dat_odlaska%TYPE;
BEGIN
IF pra_zmp_zaj.zmppar_vrijednost ('FLEX', '1001') = 'nebitno' -- timeout
OR gost_id_new_p IS NULL
THEN
RETURN;
END IF;
/*
Oznake podataka koje MISH �alje servisu Flexkeeping: A = ime, B = prezime, C = datum ro�enja,
D = datum ro�enja (samo za ro�endane u vrijeme boravka), E = dr�avljanstvo ili nebitno.
*/
flex_param_1003_m := pra_zmp_zaj.zmppar_vrijednost ('FLEX', '1003');
IF INSTR (flex_param_1003_m, 'C') = 0 AND INSTR (flex_param_1003_m, 'D') = 0 THEN
RETURN;
END IF;
-- IRN 3991
log_file_name_m := 'flexkeeping-' || TO_CHAR (SYSDATE, 'RRRR-MM-DD') || '.log';
opis_m := '[' || TO_CHAR (SYSDATE, 'DD.MM.RRRR HH24:MI:SS') || '] {Booking}';
-- KRAJ IRN 3991
opis_m := opis_m || ' [*** post_booking_kgost'; -- IRN 3532, IRN 3991
booking_rec_m := NULL;
IF INSERTING THEN
opis_m := opis_m || ' | inserting | '; -- IRN 3532
IF dat_rodjenja_new_p IS NULL THEN
RETURN;
END IF;
IF INSTR (flex_param_1003_m, 'D') > 0 THEN
OPEN gost_c (gost_id_new_p);
FETCH gost_c INTO dat_dolaska_l, dat_odlaska_l;
CLOSE gost_c;
IF dat_dolaska_l IS NULL OR dat_odlaska_l IS NULL THEN
RETURN;
END IF;
IF ADD_MONTHS (dat_rodjenja_new_p, (pra_rec_s3.dob (dat_rodjenja_new_p, (dat_dolaska_l - 1)) + 1) * 12)
BETWEEN dat_dolaska_l AND dat_odlaska_l
THEN
NULL;
ELSE
RETURN;
END IF;
ELSE
NULL;
END IF;
ELSIF UPDATING
AND (
gost_id_new_p <> gost_id_old_p -- IRN 3991 ne ra�unam da �e se promjena gost_id zaista dogoditi
OR NVL (dat_rodjenja_new_p, dat_null_l) <> NVL (dat_rodjenja_old_p, dat_null_l)
)
THEN
opis_m := opis_m || ' | updating | '; -- IRN 3532
NULL;
ELSE
RETURN;
END IF;
opis_m := opis_m || zmp_zaj.korisnik (-1) || ' | ' || TO_CHAR (dat_rodjenja_new_p, 'DD.MM.RRRR') || ' | ' || TO_CHAR (dat_rodjenja_old_p, 'DD.MM.RRRR') || ']'; -- IRN 3532
FOR c IN ( -- izvede se samo jednom
SELECT
so.sifra AS so_sifra,
sj.broj AS sj_broj,
pra_zmp_zaj.tekuci_cvor_rbr || '-' || gost.god_poslovna || '-' || gost.rbr AS BookingId, -- IRN 2974, IRN 3991
DECODE (gost.ozn_prisutnosti, 'P', 'CHECKED IN', 'CHECKED OUT') AS Status,
TO_CHAR (gost.dat_dolaska, 'YYYY-MM-DD') || ' 00:00' AS Arrival, -- IRN 3174
TO_CHAR (gost.dat_odlaska, 'YYYY-MM-DD') || ' 00:00' AS Departure, -- IRN 3174
gost.ozn_vip AS Vip,
TO_CHAR (gost.unos_dat, 'YYYY-MM-DD HH24:MI') AS CheckIn, -- IRN 3174
DECODE (gost.ozn_prisutnosti, 'P', NULL, TO_CHAR (gost.sat_odjave, 'YYYY-MM-DD HH24:MI')) AS CheckOut, -- IRN 3174
1 AS Adults,
pra_zmp_zaj.tekuci_cvor_rbr || '-' || gost.god_poslovna || '-' || gost.rbr AS GuestId, -- IRN 2974, IRN 3991
DECODE (INSTR (flex_param_1003_m, 'A'), 0, NULL, TRIM (gost.ime)) AS FirstName,
DECODE (INSTR (flex_param_1003_m, 'B'), 0, NULL, TRIM (gost.prezime)) AS LastName,
TO_CHAR (pra_flexkeeping.dat_rodjenja (flex_param_1003_m, gost_id_new_p, gost.dat_dolaska, gost.dat_odlaska, dat_rodjenja_new_p), 'YYYY-MM-DD') AS BirthDate,
DECODE (INSTR (flex_param_1003_m, 'E'), 0, NULL, zem.sifra_2a) AS Country
FROM
t_gosti gost,
m_sj sj,
m_so so,
s_zemlje zem
WHERE
gost.id = gost_id_new_p
AND gost.sj_id = sj.id
AND sj.so_id = so.id
AND gost.zem_id = zem.id
AND TRUNC (SYSDATE) BETWEEN gost.dat_dolaska AND gost.dat_odlaska
AND pra_zmp_s.oznaka ('P0021', so.sifra) = -1 -- IRN 3016
)
LOOP
booking_rec_m.ObjectF := c.so_sifra;
booking_rec_m.Room := c.sj_broj;
booking_rec_m.BookingId := c.BookingId;
booking_rec_m.Status := c.Status;
booking_rec_m.Arrival := c.Arrival;
booking_rec_m.Departure := c.Departure;
booking_rec_m.Vip := c.Vip;
booking_rec_m.CheckIn := c.CheckIn;
booking_rec_m.CheckOut := c.CheckOut;
booking_rec_m.Adults := c.Adults;
booking_rec_m.GuestId := c.GuestId;
booking_rec_m.FirstName := c.FirstName;
booking_rec_m.LastName := c.LastName;
booking_rec_m.BirthDate := c.BirthDate;
booking_rec_m.Country := c.Country;
post_booking_zaj (NULL);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
pra_pka2.clob_to_table (opis_m || CHR(10) || SQLERRM || CHR(10) || CHR(10), log_file_name_m, NULL, 'FLEX'); -- IRN 3991
END;
PROCEDURE POST_ROOM_STATUS
(SJ_ID_P IN trec_odrzavanje.sj_id%TYPE
,GOD_POSLOVNA_P IN trec_odrzavanje.god_poslovna%TYPE
,RBR_P IN trec_odrzavanje.rbr%TYPE
,DAT_POC_P IN trec_odrzavanje.dat_poc%TYPE
,DAT_ZAV_P IN trec_odrzavanje.dat_zav%TYPE
,OZNAKA_P IN trec_odrzavanje.oznaka%TYPE
,OZN_I_P IN trec_odrzavanje.ozn_i%TYPE
,NAPOMENA_P IN trec_odrzavanje.napomena%TYPE
)
IS
-- Room Status
-- proceduru pozivaju AIS_TREC_ODRZAVANJE i AUS_TREC_ODRZAVANJE
completed_l VARCHAR2(10); -- 13.08.2018.
BEGIN
IF pra_zmp_zaj.zmppar_vrijednost ('FLEX', '1001') = 'nebitno' -- timeout
THEN
RETURN;
END IF;
IF sj_id_p IS NULL
OR god_poslovna_p IS NULL
OR rbr_p IS NULL
OR dat_poc_p IS NULL
OR dat_zav_p IS NULL
OR oznaka_p IS NULL
OR ozn_i_p NOT IN (2, 3) -- u kvaru ili izvan upotrebe
-- napomena_p
THEN
RETURN;
END IF;
-- IRN 3991
log_file_name_m := 'flexkeeping-' || TO_CHAR (SYSDATE, 'RRRR-MM-DD') || '.log';
opis_m := '[' || TO_CHAR (SYSDATE, 'DD.MM.RRRR HH24:MI:SS') || '] {RoomStatus}';
-- KRAJ IRN 3991
IF oznaka_p = 'V' THEN -- 13.08.2018.
IF dat_poc_p = dat_zav_p AND dat_zav_p <= TRUNC (SYSDATE) THEN
completed_l := TO_CHAR (dat_zav_p, 'YYYY-MM-DD');
ELSIF dat_poc_p < dat_zav_p AND (dat_zav_p - 1) <= TRUNC (SYSDATE) THEN
completed_l := TO_CHAR ((dat_zav_p - 1), 'YYYY-MM-DD');
END IF;
ELSE
completed_l := TO_CHAR (SYSDATE, 'YYYY-MM-DD');
END IF;
APEX_JSON.initialize_clob_output;
APEX_JSON.open_array; -- [
FOR c IN (
SELECT
so.sifra AS so_sifra,
sj.broj AS Room,
so.sifra || '-' || god_poslovna_p || '-' || rbr_p AS RoomStatusId, -- IRN 2974
TO_CHAR (dat_poc_p, 'YYYY-MM-DD') AS dat_poc,
TO_CHAR (dat_zav_p, 'YYYY-MM-DD') AS dat_zav,
completed_l AS Completed, -- 13.08.2018.
DECODE (ozn_i_p, 2, 'OOS', 3, 'OOO') AS Status,
TRIM (napomena_p) AS Reason
FROM
m_sj sj,
m_so so
WHERE
sj.id = sj_id_p
AND sj.so_id = so.id
AND pra_zmp_s.oznaka ('P0021', so.sifra) = -1 -- IRN 3016
)
LOOP
APEX_JSON.open_object; -- {
APEX_JSON.write ('Object', c.so_sifra);
APEX_JSON.write ('Room', c.Room);
APEX_JSON.write ('RoomStatusId', c.RoomStatusId);
APEX_JSON.write ('Begin', c.dat_poc);
APEX_JSON.write ('End', c.dat_zav);
APEX_JSON.write ('Completed', c.Completed, TRUE);
APEX_JSON.write ('Status', c.Status);
APEX_JSON.write ('Reason', c.Reason);
APEX_JSON.close_object; -- }
END LOOP;
APEX_JSON.close_array; -- ]
data_m := APEX_JSON.get_clob_output;
data_m := REPLACE (data_m, CHR(10), '');
data_m := REPLACE (data_m, '},', '},' || CHR(10));
APEX_JSON.free_output;
IF data_m IS NULL THEN
RETURN;
END IF;
odgovor_m := NULL;
zaj_post ('/pms/roomstatus');
EXCEPTION
WHEN OTHERS THEN
pra_pka2.clob_to_table (opis_m || CHR(10) || SQLERRM || CHR(10) || CHR(10), log_file_name_m, NULL, 'FLEX'); -- IRN 3991
END;
PROCEDURE POST_BOOKING_ZAJ
(SJ_ID_P IN m_sj.id%TYPE
)
IS
-- IRN 3625
-- poziva se iz post_booking_gost, post_booking_kgost, post_booking_rezsj i post_booking_rez
BEGIN
IF sj_id_p IS NOT NULL THEN
SELECT MIN (broj) INTO booking_rec_m.Room FROM m_sj WHERE id = sj_id_p;
END IF;
APEX_JSON.initialize_clob_output;
APEX_JSON.open_array; -- [
APEX_JSON.open_object; -- {
APEX_JSON.write ('Object', booking_rec_m.ObjectF);
APEX_JSON.write ('Room', booking_rec_m.Room);
APEX_JSON.write ('BookingId', booking_rec_m.BookingId);
APEX_JSON.write ('Status', booking_rec_m.Status);
APEX_JSON.write ('Arrival', booking_rec_m.Arrival);
APEX_JSON.write ('Departure', booking_rec_m.Departure);
APEX_JSON.write ('Vip', booking_rec_m.Vip, TRUE);
APEX_JSON.write ('CheckIn', booking_rec_m.CheckIn, TRUE);
APEX_JSON.write ('CheckOut', booking_rec_m.CheckOut, TRUE);
APEX_JSON.write ('Adults', booking_rec_m.Adults);
IF booking_rec_m.Message IS NOT NULL THEN
APEX_JSON.open_array ('Traces'); -- [
APEX_JSON.open_object; -- {
APEX_JSON.write ('DepartmentId', 'housekeeping');
APEX_JSON.write ('Message', booking_rec_m.Message);
APEX_JSON.close_object; -- }
APEX_JSON.close_array; -- ]
END IF;
-- Guests
APEX_JSON.write ('GuestId', booking_rec_m.GuestId, TRUE);
APEX_JSON.write ('FirstName', booking_rec_m.FirstName, TRUE);
APEX_JSON.write ('LastName', booking_rec_m.LastName, TRUE);
APEX_JSON.write ('BirthDate', booking_rec_m.BirthDate, TRUE);
APEX_JSON.write ('Country', booking_rec_m.Country, TRUE);
APEX_JSON.close_object; -- }
APEX_JSON.close_array; -- ]
data_m := APEX_JSON.get_clob_output;
data_m := REPLACE (data_m, CHR(10), '');
data_m := REPLACE (data_m, '},', '},' || CHR(10));
APEX_JSON.free_output;
IF data_m IS NULL THEN
RETURN;
END IF;
odgovor_m := NULL;
zaj_post ('/pms/booking');
END;
PROCEDURE POST_BOOKING_REZSJ
(ZAPIS_P IN rezsj_rec_type
)
IS
-- IRN 3625; Booking - Arrivals
-- proceduru pozivaju AIS/AUS/ADS tablica T_REZ_VSJ i TREC_REZ_SJ
opis_end_l VARCHAR2(100);
BEGIN
IF pra_zmp_zaj.zmppar_vrijednost ('FLEX', '1001') = 'nebitno' THEN -- timeout
RETURN;
END IF;
booking_rec_m := NULL;
SELECT MIN (sifra) INTO booking_rec_m.ObjectF FROM m_so WHERE id = zapis_p.rez_row.so_id;
IF booking_rec_m.ObjectF IS NULL
OR pra_zmp_s.oznaka ('P0021', booking_rec_m.ObjectF) = 0
THEN
RETURN; -- podaci za promatrani SO ne �alju se u Flexkeeping
END IF;
IF zapis_p.rez_row.dat_dolaska < TRUNC (SYSDATE) THEN
RETURN; -- podaci za dolaske prije teku�eg datuma ne �alju se u Flexkeeping
END IF;
-- IRN 3991
log_file_name_m := 'flexkeeping-' || TO_CHAR (SYSDATE, 'RRRR-MM-DD') || '.log';
opis_m := '[' || TO_CHAR (SYSDATE, 'DD.MM.RRRR HH24:MI:SS') || '] {Booking}';
-- KRAJ IRN 3991
/*
Oznake podataka koje MISH �alje servisu Flexkeeping: A = ime, B = prezime, C = datum ro�enja,
D = datum ro�enja (samo za ro�endane u vrijeme boravka), E = dr�avljanstvo ili nebitno.
*/
flex_param_1003_m := pra_zmp_zaj.zmppar_vrijednost ('FLEX', '1003');
IF INSTR (flex_param_1003_m, 'A') > 0 THEN
booking_rec_m.FirstName := TRIM (zapis_p.rez_row.ime);
END IF;
IF INSTR (flex_param_1003_m, 'B') > 0 THEN
booking_rec_m.LastName := TRIM (zapis_p.rez_row.prezime);
END IF;
IF INSTR (flex_param_1003_m, 'E') > 0 THEN
SELECT MIN (sifra_2a) INTO booking_rec_m.Country
FROM s_zemlje
WHERE id = NVL (zapis_p.rez_row.zem_id, zapis_p.rez_row.zemd_id);
END IF;
opis_m := opis_m || ' [*** post_booking_rez'; -- IRN 3991
opis_end_l := zmp_zaj.korisnik (-1) || ' | ' || zapis_p.rez_row.ime || ' | ' || zapis_p.rez_row.prezime || ']';
booking_rec_m.BookingId := booking_rec_m.ObjectF || '-' || zapis_p.rez_row.god_poslovna || '-' || zapis_p.rez_row.rbr || '-' || zapis_p.id;
booking_rec_m.Arrival := TO_CHAR (zapis_p.rez_row.dat_dolaska, 'YYYY-MM-DD') || ' 00:00';
booking_rec_m.Departure := TO_CHAR (zapis_p.rez_row.dat_odlaska, 'YYYY-MM-DD') || ' 00:00';
booking_rec_m.Vip := zapis_p.rez_row.ozn_vip;
booking_rec_m.Adults := zapis_p.br_osoba;
booking_rec_m.Message := zapis_p.rez_row.napomena;
IF INSERTING AND zapis_p.sj_id_new IS NOT NULL THEN
opis_m := opis_m || ' | inserting | ' || opis_end_l;
booking_rec_m.Status := 'RESERVED';
post_booking_zaj (zapis_p.sj_id_new);
ELSIF UPDATING THEN
-- BUR stvori zapis samo ako su se mijenjali sj_id ili br_osoba
opis_m := opis_m || ' | updating | ' || opis_end_l;
IF zapis_p.sj_id_old IS NOT NULL AND zapis_p.sj_id_new IS NULL THEN
booking_rec_m.Status := 'CANCELLED';
post_booking_zaj (zapis_p.sj_id_old);
END IF;
IF zapis_p.sj_id_new IS NOT NULL THEN
booking_rec_m.Status := 'RESERVED';
post_booking_zaj (zapis_p.sj_id_new);
END IF;
ELSIF DELETING AND zapis_p.sj_id_old IS NOT NULL THEN
opis_m := opis_m || ' | deleting | ' || opis_end_l;
booking_rec_m.Status := 'CANCELLED';
post_booking_zaj (zapis_p.sj_id_old);
END IF;
EXCEPTION
WHEN OTHERS THEN
pra_pka2.clob_to_table (opis_m || CHR(10) || SQLERRM || CHR(10) || CHR(10), log_file_name_m, NULL, 'FLEX'); -- IRN 3991
END;
PROCEDURE POST_BOOKING_REZ
(ZAPIS_P IN tab_t_rezervacije_flex.rez_rec_type
)
IS
-- IRN 3625
-- poziva se iz AUS_T_REZERVACIJE
BookingId_l VARCHAR2(100);
BEGIN
IF pra_zmp_zaj.zmppar_vrijednost ('FLEX', '1001') = 'nebitno' THEN -- timeout
RETURN;
END IF;
booking_rec_m := NULL;
SELECT MIN (sifra) INTO booking_rec_m.ObjectF FROM m_so WHERE id = zapis_p.rez_row_new.so_id;
IF booking_rec_m.ObjectF IS NULL
OR pra_zmp_s.oznaka ('P0021', booking_rec_m.ObjectF) = 0
THEN
RETURN; -- podaci za promatrani SO ne �alju se u Flexkeeping
END IF;
IF zapis_p.rez_row_new.dat_dolaska < TRUNC (SYSDATE)
AND zapis_p.rez_row_old.dat_dolaska < TRUNC (SYSDATE)
THEN
RETURN;
END IF;
-- IRN 3991
log_file_name_m := 'flexkeeping-' || TO_CHAR (SYSDATE, 'RRRR-MM-DD') || '.log';
opis_m := '[' || TO_CHAR (SYSDATE, 'DD.MM.RRRR HH24:MI:SS') || '] {Booking}';
-- KRAJ IRN 3991
/*
Oznake podataka koje MISH �alje servisu Flexkeeping: A = ime, B = prezime, C = datum ro�enja,
D = datum ro�enja (samo za ro�endane u vrijeme boravka), E = dr�avljanstvo ili nebitno.
*/
flex_param_1003_m := pra_zmp_zaj.zmppar_vrijednost ('FLEX', '1003');
IF zapis_p.rez_row_new.oznaka <> zapis_p.rez_row_old.oznaka
OR NVL (zapis_p.rez_row_new.ozn_vstorna, 0) <> NVL (zapis_p.rez_row_old.ozn_vstorna, 0)
OR zapis_p.rez_row_new.dat_dolaska <> zapis_p.rez_row_old.dat_dolaska
OR zapis_p.rez_row_new.dat_odlaska <> zapis_p.rez_row_old.dat_odlaska
OR (NVL (zapis_p.rez_row_new.ime, '�') <> NVL (zapis_p.rez_row_old.ime, '�') AND INSTR (flex_param_1003_m, 'A') > 0)
OR (zapis_p.rez_row_new.prezime <> zapis_p.rez_row_old.prezime AND INSTR (flex_param_1003_m, 'B') > 0)
OR (NVL (zapis_p.rez_row_new.zem_id, 0) <> NVL (zapis_p.rez_row_old.zem_id, 0) AND INSTR (flex_param_1003_m, 'E') > 0)
OR (NVL (zapis_p.rez_row_new.zemd_id, 0) <> NVL (zapis_p.rez_row_old.zemd_id, 0) AND INSTR (flex_param_1003_m, 'E') > 0)
OR NVL (zapis_p.rez_row_new.ozn_vip, '�') <> NVL (zapis_p.rez_row_old.ozn_vip, '�')
OR NVL (zapis_p.rez_row_new.napomena, '�') <> NVL (zapis_p.rez_row_old.napomena, '�')
THEN
IF INSTR (flex_param_1003_m, 'A') > 0 THEN
booking_rec_m.FirstName := TRIM (TRANSLATE (zapis_p.rez_row_new.ime, ' ''";:{}\<&', ' '));
END IF;
IF INSTR (flex_param_1003_m, 'B') > 0 THEN
booking_rec_m.LastName := TRIM (TRANSLATE (zapis_p.rez_row_new.prezime, ' ''";:{}\<&', ' '));
END IF;
IF INSTR (flex_param_1003_m, 'E') > 0 THEN
SELECT MIN (sifra_2a) INTO booking_rec_m.Country
FROM s_zemlje
WHERE id = NVL (zapis_p.rez_row_new.zem_id, zapis_p.rez_row_new.zemd_id);
END IF;
opis_m := opis_m || ' [*** post_booking_rez' || ' | updating | ' || zmp_zaj.korisnik (-1) || ' | ' || zapis_p.rez_row_new.ime || ' | ' || zapis_p.rez_row_new.prezime || ']';
BookingId_l := booking_rec_m.ObjectF || '-' || zapis_p.rez_row_new.god_poslovna || '-' || zapis_p.rez_row_new.rbr;
IF zapis_p.rez_row_new.oznaka = 'V' AND zapis_p.rez_row_new.ozn_vstorna IS NULL THEN
booking_rec_m.Status := 'RESERVED';
ELSIF zapis_p.rez_row_new.oznaka = 'P'
OR (zapis_p.rez_row_old.dat_dolaska >= TRUNC (SYSDATE) AND zapis_p.rez_row_new.dat_dolaska < TRUNC (SYSDATE))
THEN
booking_rec_m.Status := 'CANCELLED';
ELSIF zapis_p.rez_row_new.ozn_vstorna = '3' THEN
booking_rec_m.Status := 'NO SHOW';
END IF;
booking_rec_m.Arrival := TO_CHAR (zapis_p.rez_row_new.dat_dolaska, 'YYYY-MM-DD') || ' 00:00';
booking_rec_m.Departure := TO_CHAR (zapis_p.rez_row_new.dat_odlaska, 'YYYY-MM-DD') || ' 00:00';
booking_rec_m.Vip := zapis_p.rez_row_new.ozn_vip;
booking_rec_m.Message := zapis_p.rez_row_new.napomena;
FOR rezvsj_c IN (
SELECT id, sj_id, br_osoba
FROM t_rez_vsj
WHERE rez_id = zapis_p.rez_row_new.id
AND br_osobaz IS NULL)
LOOP
IF rezvsj_c.sj_id IS NOT NULL THEN
booking_rec_m.BookingId := BookingId_l || '-' || rezvsj_c.id;
booking_rec_m.Adults := rezvsj_c.br_osoba;
post_booking_zaj (rezvsj_c.sj_id);
END IF;
FOR recrezsj_c IN (
SELECT id, sj_id, br_osoba
FROM trec_rez_sj
WHERE rezvsj_id = rezvsj_c.id
AND br_osobaz IS NULL)
LOOP
booking_rec_m.BookingId := BookingId_l || '-' || recrezsj_c.id;
booking_rec_m.Adults := recrezsj_c.br_osoba;
post_booking_zaj (recrezsj_c.sj_id);
END LOOP;
END LOOP;
END IF;
EXCEPTION
WHEN OTHERS THEN
pra_pka2.clob_to_table (opis_m || CHR(10) || SQLERRM || CHR(10) || CHR(10), log_file_name_m, NULL, 'FLEX'); -- IRN 3991
END;
END PRA_FLEXKEEPING;
/
SHOW ERROR