1. Datasource

# datasource
datasource:
  url: jdbc:oracle:thin:@//${DB_HOST:EXAPRDDB02-VIP.AZERCELL.COM}:${DB_PORT:1521}/${DB_SERVICE:ONSZH2.AZERCELL.COM}
  username: ${DB_USER:sysadm}
  password: ${DB_PASS:sysadm}
  driver-class-name: oracle.jdbc.OracleDriver

2. Schema: SUBSCRIPTION

Cedveller:

a) CREDIT_SRV_LIST: kredit mehsulunun kataloqu. "Azercell ne teklif edir?" siyahisi.

* nece ay taksit

* hansi billing kodu (SNCode) pay_code

PAY_CODE = SNCode ---> billing + mpulktmb ucun esas kod

LIST olmadan: bilmirik hansı SNCode ilə billing vurulur, neçə ay müddət var.

mpulktmb-dən vat_price (əsas qiymət, SNCode + TMCODE)

×

CREDIT_SRV_LIST.paycode_multiplier

=

snapshot-dakı aylıq ödəniş (monthly_payment_amount)

Aylıq kredit məbləği vat_price * paycode_multiplier ilə götürürük

Aylıq kredit məbləğini vat_price × paycode_multiplier ilə təyin edirsiniz — tech lead təsdiq etdi, çünki eyni SNCode bir neçə məhsulda ola bilər və fərq multiplier-dadır.

b) CREDIT_SRV_SUBS - mushterinin konkret kredit muqavilesi, sanki imzalanmish muqavile.

CREDIT_SRV_SUBS.service_id = CREDIT_SRV_LIST.id

Bu cedveldeki ID hemishe unikaldir. Yeni eyni mushteri ikinci telefon alsa yeni ID si olacaq.

CREDIT_NOTIFICATION_EVENT.credit_subs_id = CREDIT_SRV_SUBS.id yeni buradki ID set olunur.

3. BSCS

a) CUSTOMER_ALL ---> BSCS-de mushteri karti "bu adam hansi tarif planindadir, hesabi nece ishleyir?"

CUSTOMER_ALL.tmcode = tarif plani kodu. "BSCS-de bu mushteri hansi qiymet cedvelindedir".

Eyni pay_code (1163) ola bilər, amma TMCODE 311 olanla 410 olanın aylıq məbləği fərqli ola bilər.

Bu müştərinin TMCODE-u (311)

+

Bu kreditin pay_code-u (1163)

=

Ayda neçə manat (vat_price)

TMCODE olmadan “1163 üçün neçə manat?” deyə bilməzsən — hansı tarif planı bilinmir.

b) CCONTACT_ALL ---> mushterinin shexsi melumat cedveli - ad, soyad, passport, FIN, dogum, sheher. Bu Snapshot-un "Borrower" hissesidir.

c) MPULKTMB ---> tarif qiymet cedveli. Billing-de charge vurulan esas reqem buradan gelir.

SNCODE --> Odenish kodu = LIST.pay_code(1163)

TMCODE --> customer_all.tmcode(311) mushterinin tarif plani kodu. BSCS-de bu abuneci hansi qiymet paketindedir.

EVENT --> esas mebleg (VAT-dan evvel)

VSCODE --> Tarif versiyasi - rateplan_version ile en son secilir

vat_price = EVENT + (EVENT × 18 / 100)    -- 18% VAT

aylıq = vat_price × paycode_multiplier     -- LIST-dən

SELECT t.sncode,

       t.event,

       (t.event + (t.event * 18 / 100)) AS vat_price

FROM mpulktmb@ONSUBS_BSCS t,

     customer_all@ONSUBS_BSCS cua

WHERE t.tmcode = cua.tmcode

  AND cua.customer_id = 16391796

  AND t.sncode = 1163

  AND t.vscode = (SELECT MAX(vscode) FROM rateplan_version@ONSUBS_BSCS WHERE tmcode = t.tmcode);

SELECT *

FROM mpulktmb t

WHERE t.tmcode = 311

AND t.sncode = 1163

AND t.vscode = (

SELECT MAX(rv.vscode)

FROM rateplan_version rv

WHERE rv.tmcode = 311

);

d) ORDERHDR --> Hesab senedi / invoice bashligi - billing mushteriye ne vurub, ne qalib, ne vaxt odenilmelidir.

OHINVAMT_DOC - invoice cemi

OHOPNAMT_DOC - aciq(odenilmeyen)

OHENTDATE - sened tarixi

OHDUEDATE - son odenish tarixi

ödənilib = OHINVAMT_DOC - OHOPNAMT_DOC

SELECT customer_id,
       ohrefnum,
       ohinvamt_doc,
       ohopnamt_doc,
       (ohinvamt_doc - ohopnamt_doc) AS odenilen
FROM orderhdr@ONSUBS_BSCS
WHERE customer_id = 16391796
  AND ohopnamt_doc > 0
ORDER BY ohentdate DESC;

SELECT SUM(ohinvamt_doc - ohopnamt_doc) AS cemi_odenilen
FROM orderhdr@ONSUBS_BSCS
WHERE customer_id = 16391796
  AND ohopnamt_doc > 0;

Mock data yaradiram:

INSERT INTO customer_all (

customer_id,

custcode,

tmcode,

billcycle,

rec_version,

csentdate,

csmoddate,

currency,

primary_doc_currency,

secondary_doc_currency,

prim_convratetype_doc,

sec_convratetype_doc

)

SELECT

99990001,

'5.59966.00.00.99990001',

311,

'01',

SYSDATE,

c.currency,

c.primary_doc_currency,

c.secondary_doc_currency,

c.prim_convratetype_doc,

c.sec_convratetype_doc

FROM customer_all c

WHERE c.customer_id = 16391796

AND NOT EXISTS (

SELECT 1 FROM customer_all x WHERE x.customer_id = 99990001

);

INSERT INTO ccontact_all (

customer_id,

ccseq,

ccfname,

cclname,

birthdate,

cccity,

cssocialsecno,

passportno

)

SELECT

99990001,

'Pervin',

'Etibarli',

DATE '1991-11-02',

'Baki',

'5C89MF5',

'AZE 16916812'

FROM dual

WHERE NOT EXISTS (

SELECT 1 FROM ccontact_all x WHERE x.customer_id = 99990001

);

INSERT INTO SUBSCRIPTION.CREDIT_SRV_LIST (
    id,
    name,
    auto_payable,
    counter,
    period,
    cron_expression,
    pay_code,
    before_insert,
    after_delete,
    srv_group,
    add_paycode_val,
    is_corporate,
    credit_status,
    cancel_period,
    right_id,
    notification_sms_template,
    validator_name,
    imei_ref_id,
    unsubs_notif_sms_template,
    vsme_monthly_pack_product_id,
    vsme_daily_pack_product_id,
    vsme_hourly_pack_product_id,
    max_limit,
    paycode_multiplier,
    family_group,
    exception_status_id,
    ssr_penalty_days,
    sin_penalty_days,
    sfc_penalty_days,
    paydebt_notif_sms_template,
    ocm_right_id
)
SELECT
    (SELECT MAX(id) + 1 FROM SUBSCRIPTION.CREDIT_SRV_LIST),
    'Pervin Test iPhone 12m',
    l.auto_payable,
    l.counter,
    l.period,
    l.cron_expression,
    l.pay_code,
    l.before_insert,
    l.after_delete,
    l.srv_group,
    l.add_paycode_val,
    l.is_corporate,
    l.credit_status,
    l.cancel_period,
    l.right_id,
    l.notification_sms_template,
    l.validator_name,
    l.imei_ref_id,
    l.unsubs_notif_sms_template,
    l.vsme_monthly_pack_product_id,
    l.vsme_daily_pack_product_id,
    l.vsme_hourly_pack_product_id,
    l.max_limit,
    l.paycode_multiplier,
    l.family_group,
    l.exception_status_id,
    l.ssr_penalty_days,
    l.sin_penalty_days,
    l.sfc_penalty_days,
    l.paydebt_notif_sms_template,
    l.ocm_right_id
FROM SUBSCRIPTION.CREDIT_SRV_LIST l
WHERE l.id = 516
  AND NOT EXISTS (
      SELECT 1 FROM SUBSCRIPTION.CREDIT_SRV_LIST x
      WHERE x.name = 'Pervin Test iPhone 12m'
  );

INSERT INTO SUBSCRIPTION.CREDIT_SRV_SUBS (

id,

    customer_id,

    subs_date,

    counter_cnt,

    status,

    service_id

)

SELECT

    (SELECT MAX(id) + 1 FROM SUBSCRIPTION.CREDIT_SRV_SUBS),

    99990001,

    TIMESTAMP '2026-05-25 15:00:00',

11,

    'a',

770

FROM dual

WHERE NOT EXISTS (

    SELECT 1 FROM SUBSCRIPTION.CREDIT_SRV_SUBS

    WHERE customer_id = 99990001

      AND service_id = 770

);

INSERT INTO orderhdr (

ohxact,

ohstatus,

ohentdate,

customer_id,

rec_version,

document_currency,

document_convratetype_id,

gl_currency,

ohinvamt_doc,

ohopnamt_doc,

ohinvamt_gl,

ohopnamt_gl,

ohduedate

) VALUES (

338584164,

'IN',

DATE '2026-06-05',

99990001,

193,

100,

DATE '2026-06-19'

);

SELECT customer_id,

ohentdate,

ohstatus,

ohinvamt_doc,

ohopnamt_doc,

ohinvamt_doc - ohopnamt_doc AS paid_part,

ohduedate,

gl_currency

FROM orderhdr

WHERE customer_id = 99990001

ORDER BY ohentdate;

SUBSCRIBE — Sıfırdan Test Təlimatı

Step 1 — Hazırkı maksimum cihaz ID-sini tap

ALTER SESSION SET TIME_ZONE = '+04:00';
SELECT MAX(id) FROM subscription.credit_srv_list;

Yeni test cihazı üçün növbəti sərbəst ID-ni istifadə edəcəyik.

Step 2 — Müştərinin tarif planı üçün istifadə olunmamış qiymət kodu tap

ALTER SESSION SET TIME_ZONE = '+04:00';
SELECT DISTINCT sncode, event
FROM SYSADM.MPULKTMB@onsubs_bscs
WHERE tmcode = 321
AND vscode = (SELECT MAX(vscode) FROM SYSADM.RATEPLAN_VERSION@onsubs_bscs WHERE tmcode = 321)
AND event > 5
AND ROWNUM <= 10;

Nəticədən bir sncode seç, hələ istifadə olunmadığını yoxla:

ALTER SESSION SET TIME_ZONE = '+04:00';
SELECT id, name, pay_code FROM subscription.credit_srv_list WHERE pay_code = <sncode>;

Boş nəticə = sərbəstdir, istifadə edə bilərsən. Bu test üçün sncode=147 (EVENT=77) Step 2-nin nəticəsindən seçildi, çünki istifadə olunmamışdı.

Step 3 — Yeni test cihazı yarat

ALTER SESSION SET TIME_ZONE = '+04:00';

INSERT INTO subscription.credit_srv_list (
    id, name, auto_payable, counter, pay_code,
    is_corporate, credit_status, cancel_period,
    paycode_multiplier, sale_type
) VALUES (
    908, 'QA TEST - Fresh Round Device', 4, 6, 147,
    NULL, 'a', 48,
    1, 'CMP'
);
COMMIT;

INSERT INTO subscription.credit_srv_sncodes (service_id, sncode_val, remove_at_billing, deassign_sncode)
VALUES (908, 147, 0, 1);
COMMIT;

Doğrula:

SELECT id, name, pay_code, counter, auto_payable 
FROM subscription.credit_srv_list WHERE id = 908;

Bu cihazın aylıq qiyməti: EVENT × 1.18 = 77 × 1.18 = 90.86 AZN.

Step 4 — Qiymət kodunun müştərinin tarif planına uyğun olduğunu təsdiqlə

ALTER SESSION SET TIME_ZONE = '+04:00';
SELECT SYSADM.SERVICE_MAN.can_assign_sncode_to_tariff@onsubs_bscs(321, 147) AS can_assign FROM dual;

Gözlənilən: 1 (uyğundur). Əgər 0 gəlsə, Step 2-dən fərqli bir qiymət kodu seç.

Step 4.5 — PKG_CREDIT_SERVICES-in düzgün deploy olunduğunu təsdiqlə

ALTER SESSION SET TIME_ZONE = '+04:00';
SELECT object_name, object_type, status
FROM all_objects
WHERE object_name = 'PKG_CREDIT_SERVICES'
AND owner = 'SUBSCRIPTION'
ORDER BY object_type;

Gözlənilən: həm PACKAGE, həm PACKAGE BODY VALID göstərir.

Əgər subscribe ORA-06502: PL/SQL: numeric or value error: character to number conversion error xətası versə (line 1860-da), bu, PKG body-nin köhnə, düzəlişsiz versiyasının deploy olunduğunu göstərir. Düzəldilmiş .pkb faylını (bu səhifəyə əlavə olunmuş) eyni sessiyada, ALTER SESSION SET TIME_ZONE ilə birgə yenidən deploy et, sonra bu yoxlamanı təkrarlayıb VALID statusunu təsdiqlə, sonra davam et.

Step 5 — Müştərini subscribe et

ALTER SESSION SET TIME_ZONE = '+04:00';

DECLARE
  v_resp_code NUMBER;
  v_resp_msg  VARCHAR2(2000);
BEGIN
  SUBSCRIPTION.PKG_CREDIT_SERVICES.SUBSCRIBE(
    msisdn_      => '502110211',
    service_id_  => '908',
    user_        => 'TEST_USER',
    resp_code    => v_resp_code,
    resp_message => v_resp_msg
  );
  DBMS_OUTPUT.PUT_LINE('resp_code: ' || v_resp_code);
  DBMS_OUTPUT.PUT_LINE('resp_msg: ' || v_resp_msg);
END;

Doğrula:

ALTER SESSION SET TIME_ZONE = '+04:00';
SELECT id, customer_id, service_id, counter_cnt, status, subs_date
FROM subscription.credit_srv_subs
WHERE customer_id = 18
ORDER BY id DESC;

Gözlənilən: ən üst sırada yeni bir sətir, service_id = 908, status = 'a', counter_cnt = 6. id-ni (subscription ID) qeyd et — növbəti addımda lazım olacaq.

Step 6 — SUBSCRIBE event-inin yarandığını təsdiqlə

ALTER SESSION SET TIME_ZONE = '+04:00';
SELECT id, customer_id, credit_subs_id, notification_type, notification_status
FROM subscription.credit_notification_event
WHERE credit_subs_id = <step_5-dən_alınan_subscription_id>
ORDER BY id DESC;

Gözlənilən: bir sətir, notification_type = subscribe, notification_status = UNPROCESSED.

Step 7 — Tətbiqi işə sal və snapshot-u təsdiqlə

AkbNotificationProcessor-u local-da işə sal (SUBSCRIBE scheduler). Event-i götürüb snapshot yaradacaq.

ALTER SESSION SET TIME_ZONE = '+04:00';
SELECT *
FROM subscription.credit_notification_snapshot
WHERE event_id = (SELECT MAX(id) FROM subscription.credit_notification_event WHERE credit_subs_id = <subscription_id>);

Gözlənilən əsas dəyərlər:

MONTHLY_PAYMENT_AMOUNT = Step 3-də hesablanan qiymət
INITIAL_AMOUNT_OF_CREDIT = (counter + 1) × monthly_price (auto_payable=4 üçün)
DISOUT_AMOUNT_OF_CREDIT = counter × monthly_price
CREDIT_STATUS_CODE = 007
LAST_PAYMENT_DATE = null (hələ ödəniş yoxdur)

Əgər nəticələrin bu nümunəyə uyğundursa, SUBSCRIBE təsdiqlənmişdir ✅.

==========================================================================

Yaxşı, gəl indiyə qədər etdiyimizi (DB təmizləmə + Case 1) Confluence formatında yazaq.

Test mühitini təmizləmək (lazım olduqda)

Əgər müştərinin ORDERHDR_ALL-da çoxlu köhnə test qeydi yığılıbsa, və bunlar yeni testlərə mane olursa, sadəcə silmək mümkün olmaya bilər (ORA-02292: integrity constraint violated - child record found). Bu halda, real qeydləri saxlayaraq, onları "uzaq keçmişə" köçürmək daha təhlükəsizdir:

1. Bütün açıq qalan invoice-ları ödənmiş et:

ALTER SESSION SET TIME_ZONE = '+04:00';
UPDATE sysadm.orderhdr_all@ONSUBS_BSCS
SET ohopnamt_doc = 0
WHERE customer_id = <customer_id>
AND ohopnamt_doc != 0;
COMMIT;

2. Bütün tarixləri 100 il geri çək:

ALTER SESSION SET TIME_ZONE = '+04:00';
UPDATE sysadm.orderhdr_all@ONSUBS_BSCS
SET ohentdate = ohentdate - (365*100),
    ohduedate = ohduedate - (365*100)
WHERE customer_id = <customer_id>;
COMMIT;

Doğrula:

ALTER SESSION SET TIME_ZONE = '+04:00';
SELECT COUNT(*), MAX(ohentdate), MAX(ohduedate)
FROM sysadm.orderhdr_all@ONSUBS_BSCS
WHERE customer_id = <customer_id>;

Bundan sonra, bütün köhnə qeydlər "uzaq keçmişdə" sayılacaq, yeni test ssenariləri üçün maneə yaratmayacaq.

SERVICE_MONTHLY — Case 1: Vaxtında və tam ödəniş

Step 1 — Açıq invoice yoxluğunu təsdiqlə

ALTER SESSION SET TIME_ZONE = '+04:00';
SELECT COUNT(*) FROM sysadm.orderhdr_all@ONSUBS_BSCS
WHERE customer_id = 18 AND ohopnamt_doc > 0;

Gözlənilən: 0.

Step 2 — "Vaxtında ödənmiş" invoice yarat

ALTER SESSION SET TIME_ZONE = '+04:00';

INSERT INTO sysadm.orderhdr_all@ONSUBS_BSCS
SELECT
    SEQ_MAX_OHXACT.NEXTVAL@ONSUBS_BSCS, 'IN', OHSTATUSFLG,
    TRUNC(SYSDATE) - 20,
    OHREFNUM, OHTSHDATE, OHSLS, 18,
    OHCSEQ, OHSNAME, OHSADDR1, OHSADDR2, OHSADDR3, OHSCITY, OHSST, OHSZIP, OHSCOUNTRY, OHSCRC,
    OHATTN, OHTERMS, OHTRUCKER, OHPONUM, OHRELEASE, OHPSNO, OHFRTTERMS, OHORDTYP, OHCANDATE,
    OHASSOCXACT, OHPRT1, OHPRT2, OHPRT3, OHUSERID, OHREFDATE, OHSHPDATE,
    TRUNC(SYSDATE) - 5,
    OHIPP, OHILPP, OHPOSTGL, OHPOSTAR, OHARCUSTOMER_ID, OHGLAR, OHDISDATE, DDEBIT_FLAG,
    OHARFLG, OHINVTYPE, OHFULFILDATE, OHPAYMENT_ID, OHPOSTFIBU, OHREASON, OHRS_ID,
    OHDDHFLAG, OHDDHDATE, OHFCT, OHLCT, OHMOD, OHXRATE, OHECCODE, OHECNAME, OHGLEXACT,
    OHDUNSTEP, OHFLFCT, OHFLLCT, CO_ID, COMPLAINT, COMPLAINT_DATE, RTX_STATUS,
    OHBILLSEQNO, OHCANCELFLAG, CONVDATE_EXCHANGE, OHGLEXACT_TAX, GLACODE_DIFF,
    JOBCOST_ID_DIFF, TAX_ROUNDING_FLAG, REC_VERSION, FEE_BALANCE, FEE_BALANCE_GROSS,
    CURRENCY, OHCOSTCENT, GL_CURRENCY, DOCUMENT_CURRENCY, DOCUMENT_CONVRATETYPE_ID,
    SECONDARY_CURRENCY, SECONDARY_CONVRATETYPE_ID,
    90.86, 0, OHDISAMT_GL, OHDISTAKAMT_GL, OHTAXAMT_GL, TAXAMT_DIFF_GL,
    90.86, 0, OHDISAMT_DOC, OHDISTAKAMT_DOC, OHTAXAMT_DOC,
    GL_CONVRATETYPE_ID, OHINVAMT_SECDOC, TAXAMT_DIFF_SECDOC, PAYMENTFAILED
FROM sysadm.orderhdr_all@ONSUBS_BSCS
WHERE customer_id = 18 AND ROWNUM = 1;

INSERT INTO sysadm.orderhdr_all@ONSUBS_BSCS
SELECT
    SEQ_MAX_OHXACT.NEXTVAL@ONSUBS_BSCS, 'CO', OHSTATUSFLG,
    TRUNC(SYSDATE) - 5,
    OHREFNUM, OHTSHDATE, OHSLS, 18,
    OHCSEQ, OHSNAME, OHSADDR1, OHSADDR2, OHSADDR3, OHSCITY, OHSST, OHSZIP, OHSCOUNTRY, OHSCRC,
    OHATTN, OHTERMS, OHTRUCKER, OHPONUM, OHRELEASE, OHPSNO, OHFRTTERMS, OHORDTYP, OHCANDATE,
    OHASSOCXACT, OHPRT1, OHPRT2, OHPRT3, OHUSERID, OHREFDATE, OHSHPDATE,
    TRUNC(SYSDATE) - 5,
    OHIPP, OHILPP, OHPOSTGL, OHPOSTAR, OHARCUSTOMER_ID, OHGLAR, OHDISDATE, DDEBIT_FLAG,
    OHARFLG, OHINVTYPE, OHFULFILDATE, OHPAYMENT_ID, OHPOSTFIBU, OHREASON, OHRS_ID,
    OHDDHFLAG, OHDDHDATE, OHFCT, OHLCT, OHMOD, OHXRATE, OHECCODE, OHECNAME, OHGLEXACT,
    OHDUNSTEP, OHFLFCT, OHFLLCT, CO_ID, COMPLAINT, COMPLAINT_DATE, RTX_STATUS,
    OHBILLSEQNO, OHCANCELFLAG, CONVDATE_EXCHANGE, OHGLEXACT_TAX, GLACODE_DIFF,
    JOBCOST_ID_DIFF, TAX_ROUNDING_FLAG, REC_VERSION, FEE_BALANCE, FEE_BALANCE_GROSS,
    CURRENCY, OHCOSTCENT, GL_CURRENCY, DOCUMENT_CURRENCY, DOCUMENT_CONVRATETYPE_ID,
    SECONDARY_CURRENCY, SECONDARY_CONVRATETYPE_ID,
    90.86, 0, OHDISAMT_GL, OHDISTAKAMT_GL, OHTAXAMT_GL, TAXAMT_DIFF_GL,
    90.86, 0, OHDISAMT_DOC, OHDISTAKAMT_DOC, OHTAXAMT_DOC,
    GL_CONVRATETYPE_ID, OHINVAMT_SECDOC, TAXAMT_DIFF_SECDOC, PAYMENTFAILED
FROM sysadm.orderhdr_all@ONSUBS_BSCS
WHERE customer_id = 18 AND ROWNUM = 1;

COMMIT;

Doğrula:

ALTER SESSION SET TIME_ZONE = '+04:00';
SELECT ohxact, ohstatus, ohentdate, ohduedate, ohinvamt_doc, ohopnamt_doc
FROM sysadm.orderhdr_all@ONSUBS_BSCS
WHERE customer_id = 18
AND ohinvamt_doc = 90.86
ORDER BY ohentdate DESC;

Step 3 — SERVICE_MONTHLY event yarat

ALTER SESSION SET TIME_ZONE = '+04:00';

DECLARE
  l_customer_id NUMBER := 18;
  l_subs_id     NUMBER := 256383;
  l_la          NUMBER;
BEGIN
  SELECT NVL(customer_id_high, 0) INTO l_la
  FROM SYSADM.customer_all@ONSUBS_BSCS
  WHERE customer_id = l_customer_id;

  IF l_la = 0 THEN
    UPDATE subscription.credit_srv_subs
    SET upd_date = SYSDATE, counter_cnt = counter_cnt - 1
    WHERE id = l_subs_id;

    INSERT INTO SUBSCRIPTION.CREDIT_NOTIFICATION_EVENT (
        ID, CUSTOMER_ID, CREDIT_SUBS_ID, NOTIFICATION_TYPE,
        NOTIFICATION_DATE, NOTIFICATION_STATUS, PENALTY_PAY_MULTIPLIER
    ) VALUES (
        SUBSCRIPTION.SQ_CREDIT_NOTIFICATION_EVENT.NEXTVAL,
        l_customer_id, l_subs_id, 'service_monthly',
        SYSDATE, 'UNPROCESSED', 0
    );

    COMMIT;
  END IF;
END;

Step 4 — Tətbiqi işə sal və nəticəni təsdiqlə

Gözlənilən BillingResult:

creditDebt = 0
overdueDays = 0

Real nəticə: creditDebt=0, overdueDays=0, outstandingAmount=454.30, lastPaymentDate=2026-06-25 ✅

Case 1 təsdiqlənmişdir ✅.

==========================================================================

SERVICE_MONTHLY — Case 2: Tam ödəyib, lakin gecikdirib

Qeyd: Bu testdən əvvəl, Case 1-in invoice/payment-i (OHXACT=74, 75) 1 ay geriyə çəkildi ki, real billing siklini (hər ay yeni invoice) əks etdirsin:

ALTER SESSION SET TIME_ZONE = '+04:00';
UPDATE sysadm.orderhdr_all@ONSUBS_BSCS
SET ohentdate = ohentdate - 30,
    ohduedate = ohduedate - 30
WHERE customer_id = 18
AND ohxact IN (74, 75);
COMMIT;

Step 1 — Gecikmiş, lakin tam ödənilmiş invoice yarat

ALTER SESSION SET TIME_ZONE = '+04:00';

INSERT INTO sysadm.orderhdr_all@ONSUBS_BSCS
SELECT
    SEQ_MAX_OHXACT.NEXTVAL@ONSUBS_BSCS, 'IN', OHSTATUSFLG,
    TRUNC(SYSDATE) - 25,
    OHREFNUM, OHTSHDATE, OHSLS, 18,
    OHCSEQ, OHSNAME, OHSADDR1, OHSADDR2, OHSADDR3, OHSCITY, OHSST, OHSZIP, OHSCOUNTRY, OHSCRC,
    OHATTN, OHTERMS, OHTRUCKER, OHPONUM, OHRELEASE, OHPSNO, OHFRTTERMS, OHORDTYP, OHCANDATE,
    OHASSOCXACT, OHPRT1, OHPRT2, OHPRT3, OHUSERID, OHREFDATE, OHSHPDATE,
    TRUNC(SYSDATE) - 10,
    OHIPP, OHILPP, OHPOSTGL, OHPOSTAR, OHARCUSTOMER_ID, OHGLAR, OHDISDATE, DDEBIT_FLAG,
    OHARFLG, OHINVTYPE, OHFULFILDATE, OHPAYMENT_ID, OHPOSTFIBU, OHREASON, OHRS_ID,
    OHDDHFLAG, OHDDHDATE, OHFCT, OHLCT, OHMOD, OHXRATE, OHECCODE, OHECNAME, OHGLEXACT,
    OHDUNSTEP, OHFLFCT, OHFLLCT, CO_ID, COMPLAINT, COMPLAINT_DATE, RTX_STATUS,
    OHBILLSEQNO, OHCANCELFLAG, CONVDATE_EXCHANGE, OHGLEXACT_TAX, GLACODE_DIFF,
    JOBCOST_ID_DIFF, TAX_ROUNDING_FLAG, REC_VERSION, FEE_BALANCE, FEE_BALANCE_GROSS,
    CURRENCY, OHCOSTCENT, GL_CURRENCY, DOCUMENT_CURRENCY, DOCUMENT_CONVRATETYPE_ID,
    SECONDARY_CURRENCY, SECONDARY_CONVRATETYPE_ID,
    90.86, 0, OHDISAMT_GL, OHDISTAKAMT_GL, OHTAXAMT_GL, TAXAMT_DIFF_GL,
    90.86, 0, OHDISAMT_DOC, OHDISTAKAMT_DOC, OHTAXAMT_DOC,
    GL_CONVRATETYPE_ID, OHINVAMT_SECDOC, TAXAMT_DIFF_SECDOC, PAYMENTFAILED
FROM sysadm.orderhdr_all@ONSUBS_BSCS
WHERE customer_id = 18 AND ROWNUM = 1;

INSERT INTO sysadm.orderhdr_all@ONSUBS_BSCS
SELECT
    SEQ_MAX_OHXACT.NEXTVAL@ONSUBS_BSCS, 'CO', OHSTATUSFLG,
    TRUNC(SYSDATE) - 5,
    OHREFNUM, OHTSHDATE, OHSLS, 18,
    OHCSEQ, OHSNAME, OHSADDR1, OHSADDR2, OHSADDR3, OHSCITY, OHSST, OHSZIP, OHSCOUNTRY, OHSCRC,
    OHATTN, OHTERMS, OHTRUCKER, OHPONUM, OHRELEASE, OHPSNO, OHFRTTERMS, OHORDTYP, OHCANDATE,
    OHASSOCXACT, OHPRT1, OHPRT2, OHPRT3, OHUSERID, OHREFDATE, OHSHPDATE,
    TRUNC(SYSDATE) - 5,
    OHIPP, OHILPP, OHPOSTGL, OHPOSTAR, OHARCUSTOMER_ID, OHGLAR, OHDISDATE, DDEBIT_FLAG,
    OHARFLG, OHINVTYPE, OHFULFILDATE, OHPAYMENT_ID, OHPOSTFIBU, OHREASON, OHRS_ID,
    OHDDHFLAG, OHDDHDATE, OHFCT, OHLCT, OHMOD, OHXRATE, OHECCODE, OHECNAME, OHGLEXACT,
    OHDUNSTEP, OHFLFCT, OHFLLCT, CO_ID, COMPLAINT, COMPLAINT_DATE, RTX_STATUS,
    OHBILLSEQNO, OHCANCELFLAG, CONVDATE_EXCHANGE, OHGLEXACT_TAX, GLACODE_DIFF,
    JOBCOST_ID_DIFF, TAX_ROUNDING_FLAG, REC_VERSION, FEE_BALANCE, FEE_BALANCE_GROSS,
    CURRENCY, OHCOSTCENT, GL_CURRENCY, DOCUMENT_CURRENCY, DOCUMENT_CONVRATETYPE_ID,
    SECONDARY_CURRENCY, SECONDARY_CONVRATETYPE_ID,
    90.86, 0, OHDISAMT_GL, OHDISTAKAMT_GL, OHTAXAMT_GL, TAXAMT_DIFF_GL,
    90.86, 0, OHDISAMT_DOC, OHDISTAKAMT_DOC, OHTAXAMT_DOC,
    GL_CONVRATETYPE_ID, OHINVAMT_SECDOC, TAXAMT_DIFF_SECDOC, PAYMENTFAILED
FROM sysadm.orderhdr_all@ONSUBS_BSCS
WHERE customer_id = 18 AND ROWNUM = 1;

COMMIT;

Nə yaradılır:

Invoice due date: bugündən 10 gün əvvəl
Payment date: bugündən 5 gün əvvəl (due date-dən 5 gün sonra — gecikmiş, lakin tam ödəniş)

Doğrula:

ALTER SESSION SET TIME_ZONE = '+04:00';
SELECT ohxact, ohstatus, ohentdate, ohduedate, ohinvamt_doc, ohopnamt_doc
FROM sysadm.orderhdr_all@ONSUBS_BSCS
WHERE customer_id = 18
AND ohinvamt_doc = 90.86
ORDER BY ohentdate DESC;

Step 2 — SERVICE_MONTHLY event yarat

ALTER SESSION SET TIME_ZONE = '+04:00';

DECLARE
  l_customer_id NUMBER := 18;
  l_subs_id     NUMBER := 256383;
  l_la          NUMBER;
BEGIN
  SELECT NVL(customer_id_high, 0) INTO l_la
  FROM SYSADM.customer_all@ONSUBS_BSCS
  WHERE customer_id = l_customer_id;

  IF l_la = 0 THEN
    UPDATE subscription.credit_srv_subs
    SET upd_date = SYSDATE, counter_cnt = counter_cnt - 1
    WHERE id = l_subs_id;

    INSERT INTO SUBSCRIPTION.CREDIT_NOTIFICATION_EVENT (
        ID, CUSTOMER_ID, CREDIT_SUBS_ID, NOTIFICATION_TYPE,
        NOTIFICATION_DATE, NOTIFICATION_STATUS, PENALTY_PAY_MULTIPLIER
    ) VALUES (
        SUBSCRIPTION.SQ_CREDIT_NOTIFICATION_EVENT.NEXTVAL,
        l_customer_id, l_subs_id, 'service_monthly',
        SYSDATE, 'UNPROCESSED', 0
    );

    COMMIT;
  END IF;
END;

Step 3 — Tətbiqi işə sal və nəticəni təsdiqlə

Gözlənilən BillingResult:

creditDebt = 0 (tam ödənib)
overdueDays = 5 (due date-dən ödəniş tarixinə qədər)

Real nəticə: creditDebt=0, overdueDays=5, outstandingAmount=272.58, lastPaymentDate=2026-06-25 ✅

Case 2 təsdiqlənmişdir ✅.

==========================================================================

SERVICE_MONTHLY — Case 3: İki ay ardıcıl ödəməyib

Qeyd: Bu testdən əvvəl, Case 2-nin invoice/payment-i (OHXACT=76, 77) 1 ay geriyə çəkildi ki, real billing siklini əks etdirsin:

ALTER SESSION SET TIME_ZONE = '+04:00';
UPDATE sysadm.orderhdr_all@ONSUBS_BSCS
SET ohentdate = ohentdate - 30,
    ohduedate = ohduedate - 30
WHERE customer_id = 18
AND ohxact IN (76, 77);
COMMIT;

Step 1 — İki ardıcıl ödənməmiş invoice yarat

ALTER SESSION SET TIME_ZONE = '+04:00';

-- Invoice 1 (köhnə ay, ~60 gün əvvəl due)
INSERT INTO sysadm.orderhdr_all@ONSUBS_BSCS
SELECT
    SEQ_MAX_OHXACT.NEXTVAL@ONSUBS_BSCS, 'IN', OHSTATUSFLG,
    TRUNC(SYSDATE) - 75,
    OHREFNUM, OHTSHDATE, OHSLS, 18,
    OHCSEQ, OHSNAME, OHSADDR1, OHSADDR2, OHSADDR3, OHSCITY, OHSST, OHSZIP, OHSCOUNTRY, OHSCRC,
    OHATTN, OHTERMS, OHTRUCKER, OHPONUM, OHRELEASE, OHPSNO, OHFRTTERMS, OHORDTYP, OHCANDATE,
    OHASSOCXACT, OHPRT1, OHPRT2, OHPRT3, OHUSERID, OHREFDATE, OHSHPDATE,
    TRUNC(SYSDATE) - 60,
    OHIPP, OHILPP, OHPOSTGL, OHPOSTAR, OHARCUSTOMER_ID, OHGLAR, OHDISDATE, DDEBIT_FLAG,
    OHARFLG, OHINVTYPE, OHFULFILDATE, OHPAYMENT_ID, OHPOSTFIBU, OHREASON, OHRS_ID,
    OHDDHFLAG, OHDDHDATE, OHFCT, OHLCT, OHMOD, OHXRATE, OHECCODE, OHECNAME, OHGLEXACT,
    OHDUNSTEP, OHFLFCT, OHFLLCT, CO_ID, COMPLAINT, COMPLAINT_DATE, RTX_STATUS,
    OHBILLSEQNO, OHCANCELFLAG, CONVDATE_EXCHANGE, OHGLEXACT_TAX, GLACODE_DIFF,
    JOBCOST_ID_DIFF, TAX_ROUNDING_FLAG, REC_VERSION, FEE_BALANCE, FEE_BALANCE_GROSS,
    CURRENCY, OHCOSTCENT, GL_CURRENCY, DOCUMENT_CURRENCY, DOCUMENT_CONVRATETYPE_ID,
    SECONDARY_CURRENCY, SECONDARY_CONVRATETYPE_ID,
    90.86, 90.86, OHDISAMT_GL, OHDISTAKAMT_GL, OHTAXAMT_GL, TAXAMT_DIFF_GL,
    90.86, 90.86, OHDISAMT_DOC, OHDISTAKAMT_DOC, OHTAXAMT_DOC,
    GL_CONVRATETYPE_ID, OHINVAMT_SECDOC, TAXAMT_DIFF_SECDOC, PAYMENTFAILED
FROM sysadm.orderhdr_all@ONSUBS_BSCS
WHERE customer_id = 18 AND ROWNUM = 1;

-- Invoice 2 (sonrakı ay, ~30 gün əvvəl due)
INSERT INTO sysadm.orderhdr_all@ONSUBS_BSCS
SELECT
    SEQ_MAX_OHXACT.NEXTVAL@ONSUBS_BSCS, 'IN', OHSTATUSFLG,
    TRUNC(SYSDATE) - 45,
    OHREFNUM, OHTSHDATE, OHSLS, 18,
    OHCSEQ, OHSNAME, OHSADDR1, OHSADDR2, OHSADDR3, OHSCITY, OHSST, OHSZIP, OHSCOUNTRY, OHSCRC,
    OHATTN, OHTERMS, OHTRUCKER, OHPONUM, OHRELEASE, OHPSNO, OHFRTTERMS, OHORDTYP, OHCANDATE,
    OHASSOCXACT, OHPRT1, OHPRT2, OHPRT3, OHUSERID, OHREFDATE, OHSHPDATE,
    TRUNC(SYSDATE) - 30,
    OHIPP, OHILPP, OHPOSTGL, OHPOSTAR, OHARCUSTOMER_ID, OHGLAR, OHDISDATE, DDEBIT_FLAG,
    OHARFLG, OHINVTYPE, OHFULFILDATE, OHPAYMENT_ID, OHPOSTFIBU, OHREASON, OHRS_ID,
    OHDDHFLAG, OHDDHDATE, OHFCT, OHLCT, OHMOD, OHXRATE, OHECCODE, OHECNAME, OHGLEXACT,
    OHDUNSTEP, OHFLFCT, OHFLLCT, CO_ID, COMPLAINT, COMPLAINT_DATE, RTX_STATUS,
    OHBILLSEQNO, OHCANCELFLAG, CONVDATE_EXCHANGE, OHGLEXACT_TAX, GLACODE_DIFF,
    JOBCOST_ID_DIFF, TAX_ROUNDING_FLAG, REC_VERSION, FEE_BALANCE, FEE_BALANCE_GROSS,
    CURRENCY, OHCOSTCENT, GL_CURRENCY, DOCUMENT_CURRENCY, DOCUMENT_CONVRATETYPE_ID,
    SECONDARY_CURRENCY, SECONDARY_CONVRATETYPE_ID,
    90.86, 90.86, OHDISAMT_GL, OHDISTAKAMT_GL, OHTAXAMT_GL, TAXAMT_DIFF_GL,
    90.86, 90.86, OHDISAMT_DOC, OHDISTAKAMT_DOC, OHTAXAMT_DOC,
    GL_CONVRATETYPE_ID, OHINVAMT_SECDOC, TAXAMT_DIFF_SECDOC, PAYMENTFAILED
FROM sysadm.orderhdr_all@ONSUBS_BSCS
WHERE customer_id = 18 AND ROWNUM = 1;

COMMIT;

Doğrula:

ALTER SESSION SET TIME_ZONE = '+04:00';
SELECT ohxact, ohstatus, ohentdate, ohduedate, ohinvamt_doc, ohopnamt_doc
FROM sysadm.orderhdr_all@ONSUBS_BSCS
WHERE customer_id = 18
AND ohopnamt_doc > 0
ORDER BY ohduedate ASC;

Step 2 — SERVICE_MONTHLY event yarat

ALTER SESSION SET TIME_ZONE = '+04:00';

DECLARE
  l_customer_id NUMBER := 18;
  l_subs_id     NUMBER := 256383;
  l_la          NUMBER;
BEGIN
  SELECT NVL(customer_id_high, 0) INTO l_la
  FROM SYSADM.customer_all@ONSUBS_BSCS
  WHERE customer_id = l_customer_id;

  IF l_la = 0 THEN
    UPDATE subscription.credit_srv_subs
    SET upd_date = SYSDATE, counter_cnt = counter_cnt - 1
    WHERE id = l_subs_id;

    INSERT INTO SUBSCRIPTION.CREDIT_NOTIFICATION_EVENT (
        ID, CUSTOMER_ID, CREDIT_SUBS_ID, NOTIFICATION_TYPE,
        NOTIFICATION_DATE, NOTIFICATION_STATUS, PENALTY_PAY_MULTIPLIER
    ) VALUES (
        SUBSCRIPTION.SQ_CREDIT_NOTIFICATION_EVENT.NEXTVAL,
        l_customer_id, l_subs_id, 'service_monthly',
        SYSDATE, 'UNPROCESSED', 0
    );

    COMMIT;
  END IF;
END;

Step 3 — Tətbiqi işə sal və nəticəni təsdiqlə

Gözlənilən BillingResult:

creditDebt = 90.86 + 90.86 = 181.72 (hər iki ayın tam borcu)
overdueDays = bugündən ən köhnə invoice-un due date-inə qədər (≈60 gün)

Real nəticə: creditDebt=181.72, overdueDays=60, outstandingAmount=181.72, lastPaymentDate=2026-05-26 ✅

Case 3 təsdiqlənmişdir ✅.

==========================================================================

SERVICE_MONTHLY — Case 4: Keçmiş borc + cari ayın borcunu birlikdə, vaxtında ödəyir

Bu ssenari göstərir ki, müştəri iki ay gecikmiş olsa belə, əgər bütün borcunu yeni (cari) ayın due date-i gəlməmişdən əvvəl ödəsə, sistem onu tam təmiz sayır — heç bir keçmiş gecikmə qalmır.

Step 1 — Test mühitini təmizlə (lazım olduqda)

Əgər köhnə test qeydləri yeni testə mane olursa:

ALTER SESSION SET TIME_ZONE = '+04:00';
UPDATE sysadm.orderhdr_all@ONSUBS_BSCS
SET ohopnamt_doc = 0
WHERE customer_id = 18
AND ohopnamt_doc != 0;

UPDATE sysadm.orderhdr_all@ONSUBS_BSCS
SET ohentdate = ohentdate - (365*100),
    ohduedate = ohduedate - (365*100)
WHERE customer_id = 18;
COMMIT;

Step 2 — Ay 1 invoice-i yarat (due date 60 gün əvvəl, açıq qalsın)

ALTER SESSION SET TIME_ZONE = '+04:00';

INSERT INTO sysadm.orderhdr_all@ONSUBS_BSCS
SELECT
    SEQ_MAX_OHXACT.NEXTVAL@ONSUBS_BSCS, 'IN', OHSTATUSFLG,
    TRUNC(SYSDATE) - 75,
    OHREFNUM, OHTSHDATE, OHSLS, 18,
    OHCSEQ, OHSNAME, OHSADDR1, OHSADDR2, OHSADDR3, OHSCITY, OHSST, OHSZIP, OHSCOUNTRY, OHSCRC,
    OHATTN, OHTERMS, OHTRUCKER, OHPONUM, OHRELEASE, OHPSNO, OHFRTTERMS, OHORDTYP, OHCANDATE,
    OHASSOCXACT, OHPRT1, OHPRT2, OHPRT3, OHUSERID, OHREFDATE, OHSHPDATE,
    TRUNC(SYSDATE) - 60,
    OHIPP, OHILPP, OHPOSTGL, OHPOSTAR, OHARCUSTOMER_ID, OHGLAR, OHDISDATE, DDEBIT_FLAG,
    OHARFLG, OHINVTYPE, OHFULFILDATE, OHPAYMENT_ID, OHPOSTFIBU, OHREASON, OHRS_ID,
    OHDDHFLAG, OHDDHDATE, OHFCT, OHLCT, OHMOD, OHXRATE, OHECCODE, OHECNAME, OHGLEXACT,
    OHDUNSTEP, OHFLFCT, OHFLLCT, CO_ID, COMPLAINT, COMPLAINT_DATE, RTX_STATUS,
    OHBILLSEQNO, OHCANCELFLAG, CONVDATE_EXCHANGE, OHGLEXACT_TAX, GLACODE_DIFF,
    JOBCOST_ID_DIFF, TAX_ROUNDING_FLAG, REC_VERSION, FEE_BALANCE, FEE_BALANCE_GROSS,
    CURRENCY, OHCOSTCENT, GL_CURRENCY, DOCUMENT_CURRENCY, DOCUMENT_CONVRATETYPE_ID,
    SECONDARY_CURRENCY, SECONDARY_CONVRATETYPE_ID,
    90.86, 90.86, OHDISAMT_GL, OHDISTAKAMT_GL, OHTAXAMT_GL, TAXAMT_DIFF_GL,
    90.86, 90.86, OHDISAMT_DOC, OHDISTAKAMT_DOC, OHTAXAMT_DOC,
    GL_CONVRATETYPE_ID, OHINVAMT_SECDOC, TAXAMT_DIFF_SECDOC, PAYMENTFAILED
FROM sysadm.orderhdr_all@ONSUBS_BSCS
WHERE customer_id = 18 AND ROWNUM = 1;

COMMIT;

Step 3 — Ay 2 invoice-i yarat (due date 30 gün əvvəl, açıq qalsın)

ALTER SESSION SET TIME_ZONE = '+04:00';

INSERT INTO sysadm.orderhdr_all@ONSUBS_BSCS
SELECT
    SEQ_MAX_OHXACT.NEXTVAL@ONSUBS_BSCS, 'IN', OHSTATUSFLG,
    TRUNC(SYSDATE) - 45,
    OHREFNUM, OHTSHDATE, OHSLS, 18,
    OHCSEQ, OHSNAME, OHSADDR1, OHSADDR2, OHSADDR3, OHSCITY, OHSST, OHSZIP, OHSCOUNTRY, OHSCRC,
    OHATTN, OHTERMS, OHTRUCKER, OHPONUM, OHRELEASE, OHPSNO, OHFRTTERMS, OHORDTYP, OHCANDATE,
    OHASSOCXACT, OHPRT1, OHPRT2, OHPRT3, OHUSERID, OHREFDATE, OHSHPDATE,
    TRUNC(SYSDATE) - 30,
    OHIPP, OHILPP, OHPOSTGL, OHPOSTAR, OHARCUSTOMER_ID, OHGLAR, OHDISDATE, DDEBIT_FLAG,
    OHARFLG, OHINVTYPE, OHFULFILDATE, OHPAYMENT_ID, OHPOSTFIBU, OHREASON, OHRS_ID,
    OHDDHFLAG, OHDDHDATE, OHFCT, OHLCT, OHMOD, OHXRATE, OHECCODE, OHECNAME, OHGLEXACT,
    OHDUNSTEP, OHFLFCT, OHFLLCT, CO_ID, COMPLAINT, COMPLAINT_DATE, RTX_STATUS,
    OHBILLSEQNO, OHCANCELFLAG, CONVDATE_EXCHANGE, OHGLEXACT_TAX, GLACODE_DIFF,
    JOBCOST_ID_DIFF, TAX_ROUNDING_FLAG, REC_VERSION, FEE_BALANCE, FEE_BALANCE_GROSS,
    CURRENCY, OHCOSTCENT, GL_CURRENCY, DOCUMENT_CURRENCY, DOCUMENT_CONVRATETYPE_ID,
    SECONDARY_CURRENCY, SECONDARY_CONVRATETYPE_ID,
    90.86, 90.86, OHDISAMT_GL, OHDISTAKAMT_GL, OHTAXAMT_GL, TAXAMT_DIFF_GL,
    90.86, 90.86, OHDISAMT_DOC, OHDISTAKAMT_DOC, OHTAXAMT_DOC,
    GL_CONVRATETYPE_ID, OHINVAMT_SECDOC, TAXAMT_DIFF_SECDOC, PAYMENTFAILED
FROM sysadm.orderhdr_all@ONSUBS_BSCS
WHERE customer_id = 18 AND ROWNUM = 1;

COMMIT;

Step 4 — Ay 3 invoice-i yarat (due date hələ gəlməyib — gələcəkdə)

ALTER SESSION SET TIME_ZONE = '+04:00';

INSERT INTO sysadm.orderhdr_all@ONSUBS_BSCS
SELECT
    SEQ_MAX_OHXACT.NEXTVAL@ONSUBS_BSCS, 'IN', OHSTATUSFLG,
    TRUNC(SYSDATE) - 15,
    OHREFNUM, OHTSHDATE, OHSLS, 18,
    OHCSEQ, OHSNAME, OHSADDR1, OHSADDR2, OHSADDR3, OHSCITY, OHSST, OHSZIP, OHSCOUNTRY, OHSCRC,
    OHATTN, OHTERMS, OHTRUCKER, OHPONUM, OHRELEASE, OHPSNO, OHFRTTERMS, OHORDTYP, OHCANDATE,
    OHASSOCXACT, OHPRT1, OHPRT2, OHPRT3, OHUSERID, OHREFDATE, OHSHPDATE,
    TRUNC(SYSDATE) + 5,
    OHIPP, OHILPP, OHPOSTGL, OHPOSTAR, OHARCUSTOMER_ID, OHGLAR, OHDISDATE, DDEBIT_FLAG,
    OHARFLG, OHINVTYPE, OHFULFILDATE, OHPAYMENT_ID, OHPOSTFIBU, OHREASON, OHRS_ID,
    OHDDHFLAG, OHDDHDATE, OHFCT, OHLCT, OHMOD, OHXRATE, OHECCODE, OHECNAME, OHGLEXACT,
    OHDUNSTEP, OHFLFCT, OHFLLCT, CO_ID, COMPLAINT, COMPLAINT_DATE, RTX_STATUS,
    OHBILLSEQNO, OHCANCELFLAG, CONVDATE_EXCHANGE, OHGLEXACT_TAX, GLACODE_DIFF,
    JOBCOST_ID_DIFF, TAX_ROUNDING_FLAG, REC_VERSION, FEE_BALANCE, FEE_BALANCE_GROSS,
    CURRENCY, OHCOSTCENT, GL_CURRENCY, DOCUMENT_CURRENCY, DOCUMENT_CONVRATETYPE_ID,
    SECONDARY_CURRENCY, SECONDARY_CONVRATETYPE_ID,
    90.86, 90.86, OHDISAMT_GL, OHDISTAKAMT_GL, OHTAXAMT_GL, TAXAMT_DIFF_GL,
    90.86, 90.86, OHDISAMT_DOC, OHDISTAKAMT_DOC, OHTAXAMT_DOC,
    GL_CONVRATETYPE_ID, OHINVAMT_SECDOC, TAXAMT_DIFF_SECDOC, PAYMENTFAILED
FROM sysadm.orderhdr_all@ONSUBS_BSCS
WHERE customer_id = 18 AND ROWNUM = 1;

COMMIT;

Doğrula:

ALTER SESSION SET TIME_ZONE = '+04:00';
SELECT ohxact, ohstatus, ohduedate, ohinvamt_doc, ohopnamt_doc
FROM sysadm.orderhdr_all@ONSUBS_BSCS
WHERE customer_id = 18
AND ohopnamt_doc > 0
ORDER BY ohduedate ASC;

Gözlənilən: 3 sətir (Ay 1, Ay 2, Ay 3-ün invoice-ləri).

Step 5 — Hamısını birlikdə, bugün ödə

ALTER SESSION SET TIME_ZONE = '+04:00';

UPDATE sysadm.orderhdr_all@ONSUBS_BSCS
SET ohopnamt_doc = 0
WHERE customer_id = 18
AND ohxact IN (<Step 4-ün doğrulamasından alınan 3 OHXACT>);

INSERT INTO sysadm.orderhdr_all@ONSUBS_BSCS
SELECT
    SEQ_MAX_OHXACT.NEXTVAL@ONSUBS_BSCS, 'CO', OHSTATUSFLG,
    TRUNC(SYSDATE),
    OHREFNUM, OHTSHDATE, OHSLS, 18,
    OHCSEQ, OHSNAME, OHSADDR1, OHSADDR2, OHSADDR3, OHSCITY, OHSST, OHSZIP, OHSCOUNTRY, OHSCRC,
    OHATTN, OHTERMS, OHTRUCKER, OHPONUM, OHRELEASE, OHPSNO, OHFRTTERMS, OHORDTYP, OHCANDATE,
    OHASSOCXACT, OHPRT1, OHPRT2, OHPRT3, OHUSERID, OHREFDATE, OHSHPDATE,
    TRUNC(SYSDATE),
    OHIPP, OHILPP, OHPOSTGL, OHPOSTAR, OHARCUSTOMER_ID, OHGLAR, OHDISDATE, DDEBIT_FLAG,
    OHARFLG, OHINVTYPE, OHFULFILDATE, OHPAYMENT_ID, OHPOSTFIBU, OHREASON, OHRS_ID,
    OHDDHFLAG, OHDDHDATE, OHFCT, OHLCT, OHMOD, OHXRATE, OHECCODE, OHECNAME, OHGLEXACT,
    OHDUNSTEP, OHFLFCT, OHFLLCT, CO_ID, COMPLAINT, COMPLAINT_DATE, RTX_STATUS,
    OHBILLSEQNO, OHCANCELFLAG, CONVDATE_EXCHANGE, OHGLEXACT_TAX, GLACODE_DIFF,
    JOBCOST_ID_DIFF, TAX_ROUNDING_FLAG, REC_VERSION, FEE_BALANCE, FEE_BALANCE_GROSS,
    CURRENCY, OHCOSTCENT, GL_CURRENCY, DOCUMENT_CURRENCY, DOCUMENT_CONVRATETYPE_ID,
    SECONDARY_CURRENCY, SECONDARY_CONVRATETYPE_ID,
    272.58, 0, OHDISAMT_GL, OHDISTAKAMT_GL, OHTAXAMT_GL, TAXAMT_DIFF_GL,
    272.58, 0, OHDISAMT_DOC, OHDISTAKAMT_DOC, OHTAXAMT_DOC,
    GL_CONVRATETYPE_ID, OHINVAMT_SECDOC, TAXAMT_DIFF_SECDOC, PAYMENTFAILED
FROM sysadm.orderhdr_all@ONSUBS_BSCS
WHERE customer_id = 18 AND ROWNUM = 1;

COMMIT;

Önəmli qeyd: overdueDays hesablaması, ən son ödənmiş invoice-un (ohentdate-ə görə ən son yaranan) due date-inə əsaslanır. Ay 3-ün invoice-i ən son yarandığı üçün, onun due date-i (gələcəkdə) götürülür — bu, ödəniş tarixindən (bugün) sonra olduğu üçün, overdueDays = max(0, mənfi) = 0 çıxır.

Step 6 — SERVICE_MONTHLY event yarat

ALTER SESSION SET TIME_ZONE = '+04:00';

DECLARE
  l_customer_id NUMBER := 18;
  l_subs_id     NUMBER := 256383;
  l_la          NUMBER;
BEGIN
  SELECT NVL(customer_id_high, 0) INTO l_la
  FROM SYSADM.customer_all@ONSUBS_BSCS
  WHERE customer_id = l_customer_id;

  IF l_la = 0 THEN
    UPDATE subscription.credit_srv_subs
    SET upd_date = SYSDATE, counter_cnt = counter_cnt - 1
    WHERE id = l_subs_id;

    INSERT INTO SUBSCRIPTION.CREDIT_NOTIFICATION_EVENT (
        ID, CUSTOMER_ID, CREDIT_SUBS_ID, NOTIFICATION_TYPE,
        NOTIFICATION_DATE, NOTIFICATION_STATUS, PENALTY_PAY_MULTIPLIER
    ) VALUES (
        SUBSCRIPTION.SQ_CREDIT_NOTIFICATION_EVENT.NEXTVAL,
        l_customer_id, l_subs_id, 'service_monthly',
        SYSDATE, 'UNPROCESSED', 0
    );

    COMMIT;
  END IF;
END;

Step 7 — Tətbiqi işə sal və nəticəni təsdiqlə

Gözlənilən BillingResult:

creditDebt = 0
overdueDays = 0

Real nəticə: creditDebt=0, overdueDays=0, outstandingAmount=0.00, lastPaymentDate=2026-06-30 ✅

Case 4 təsdiqlənmişdir ✅ — bu, real-dünya ssenarisini doğrulayır: müştəri keçmiş borcunu cari ayın due date-i gəlməmişdən əvvəl ödəsə, sistem onu tam təmiz sayır.

==========================================================================

FINISH — Bütün borc ödənilib, kredit bağlanır

Bu test, kreditin son ayının (counter_cnt=0) tamamlandığını və FINISH event-inin düzgün emal olunduğunu doğrulayır.

Step 1 — Subscription-un vəziyyətini yoxla

ALTER SESSION SET TIME_ZONE = '+04:00';
SELECT id, counter_cnt, status
FROM subscription.credit_srv_subs
WHERE id = 256383;

Gözlənilən: counter_cnt = 0, status = 'a' (hələ aktiv — real PKG bunu avtomatik dəyişdirməli idi, biz manual simulyasiya edirik).

Step 2 — Subscription-u deaktiv et və FINISH event yarat

ALTER SESSION SET TIME_ZONE = '+04:00';

UPDATE subscription.credit_srv_subs
SET status = 'd', unsubs_date = SYSDATE
WHERE id = 256383;

INSERT INTO SUBSCRIPTION.CREDIT_NOTIFICATION_EVENT (
    ID, CUSTOMER_ID, CREDIT_SUBS_ID, NOTIFICATION_TYPE,
    NOTIFICATION_DATE, NOTIFICATION_STATUS, PENALTY_PAY_MULTIPLIER
) VALUES (
    SUBSCRIPTION.SQ_CREDIT_NOTIFICATION_EVENT.NEXTVAL,
    18, 256383, 'finish',
    TRUNC(SYSDATE), 'UNPROCESSED', 0
);

COMMIT;

Doğrula:

ALTER SESSION SET TIME_ZONE = '+04:00';
SELECT id, customer_id, credit_subs_id, notification_type, notification_status
FROM subscription.credit_notification_event
WHERE credit_subs_id = 256383
ORDER BY id DESC;

Step 3 — FINISH üçün uyğun invoice yarat

Vacib qeyd: findFinishInvoice sorğusu ohentdate >= notification_date şərtinə baxır. Yəni invoice-un yaranma tarixi, FINISH event-inin tarixi ilə eyni gün və ya sonra olmalıdır, əks halda processor heç bir invoice tapmayıb event-i UNPROCESSED saxlayacaq.

ALTER SESSION SET TIME_ZONE = '+04:00';

INSERT INTO sysadm.orderhdr_all@ONSUBS_BSCS
SELECT
    SEQ_MAX_OHXACT.NEXTVAL@ONSUBS_BSCS, 'IN', OHSTATUSFLG,
    TRUNC(SYSDATE),
    OHREFNUM, OHTSHDATE, OHSLS, 18,
    OHCSEQ, OHSNAME, OHSADDR1, OHSADDR2, OHSADDR3, OHSCITY, OHSST, OHSZIP, OHSCOUNTRY, OHSCRC,
    OHATTN, OHTERMS, OHTRUCKER, OHPONUM, OHRELEASE, OHPSNO, OHFRTTERMS, OHORDTYP, OHCANDATE,
    OHASSOCXACT, OHPRT1, OHPRT2, OHPRT3, OHUSERID, OHREFDATE, OHSHPDATE,
    TRUNC(SYSDATE) + 14,
    OHIPP, OHILPP, OHPOSTGL, OHPOSTAR, OHARCUSTOMER_ID, OHGLAR, OHDISDATE, DDEBIT_FLAG,
    OHARFLG, OHINVTYPE, OHFULFILDATE, OHPAYMENT_ID, OHPOSTFIBU, OHREASON, OHRS_ID,
    OHDDHFLAG, OHDDHDATE, OHFCT, OHLCT, OHMOD, OHXRATE, OHECCODE, OHECNAME, OHGLEXACT,
    OHDUNSTEP, OHFLFCT, OHFLLCT, CO_ID, COMPLAINT, COMPLAINT_DATE, RTX_STATUS,
    OHBILLSEQNO, OHCANCELFLAG, CONVDATE_EXCHANGE, OHGLEXACT_TAX, GLACODE_DIFF,
    JOBCOST_ID_DIFF, TAX_ROUNDING_FLAG, REC_VERSION, FEE_BALANCE, FEE_BALANCE_GROSS,
    CURRENCY, OHCOSTCENT, GL_CURRENCY, DOCUMENT_CURRENCY, DOCUMENT_CONVRATETYPE_ID,
    SECONDARY_CURRENCY, SECONDARY_CONVRATETYPE_ID,
    90.86, 0, OHDISAMT_GL, OHDISTAKAMT_GL, OHTAXAMT_GL, TAXAMT_DIFF_GL,
    90.86, 0, OHDISAMT_DOC, OHDISTAKAMT_DOC, OHTAXAMT_DOC,
    GL_CONVRATETYPE_ID, OHINVAMT_SECDOC, TAXAMT_DIFF_SECDOC, PAYMENTFAILED
FROM sysadm.orderhdr_all@ONSUBS_BSCS
WHERE customer_id = 18 AND ROWNUM = 1;

COMMIT;

Nə yaradılır:

ohentdate = bugün, notification_date-ə uyğun (eyni gün)
ohopnamt_doc = 0 — ödənmiş, son invoice kimi

Step 4 — Tətbiqi işə sal və snapshot-u təsdiqlə

AkbNotificationProcessor-u işə sal (FINISH scheduler, billcycle=01).

ALTER SESSION SET TIME_ZONE = '+04:00';
SELECT *
FROM subscription.credit_notification_snapshot
WHERE event_id = (SELECT MAX(id) FROM subscription.credit_notification_event WHERE credit_subs_id = 256383 AND notification_type = 'finish');

Gözlənilən əsas dəyərlər:

CREDIT_STATUS_CODE = 001
DISOUT_AMOUNT_OF_CREDIT = 0
DAYS_MAIN_SUM_IS_OVERDUE = 0
OIA_FOR_REPPERIOD = 0

Real nəticə: disout_amount_of_credit=0, days_main_sum_is_overdue=0, oia_for_repperiod=0, credit_status_code=001 ✅

FINISH təsdiqlənmişdir ✅.

==========================================================================

AKB-Notification-Processor

SUBSCRIBE — Sıfırdan Test Təlimatı

Step 1 — Hazırkı maksimum cihaz ID-sini tap

Step 2 — Müştərinin tarif planı üçün istifadə olunmamış qiymət kodu tap

Step 3 — Yeni test cihazı yarat

Step 4 — Qiymət kodunun müştərinin tarif planına uyğun olduğunu təsdiqlə

Step 4.5 — PKG_CREDIT_SERVICES-in düzgün deploy olunduğunu təsdiqlə

Step 5 — Müştərini subscribe et

Step 6 — SUBSCRIBE event-inin yarandığını təsdiqlə

Step 7 — Tətbiqi işə sal və snapshot-u təsdiqlə

Test mühitini təmizləmək (lazım olduqda)

SERVICE_MONTHLY — Case 1: Vaxtında və tam ödəniş

Step 1 — Açıq invoice yoxluğunu təsdiqlə

Step 2 — "Vaxtında ödənmiş" invoice yarat

Step 3 — SERVICE_MONTHLY event yarat

Step 4 — Tətbiqi işə sal və nəticəni təsdiqlə

SERVICE_MONTHLY — Case 2: Tam ödəyib, lakin gecikdirib

Step 1 — Gecikmiş, lakin tam ödənilmiş invoice yarat

Step 2 — SERVICE_MONTHLY event yarat

Step 3 — Tətbiqi işə sal və nəticəni təsdiqlə

SERVICE_MONTHLY — Case 3: İki ay ardıcıl ödəməyib

Step 1 — İki ardıcıl ödənməmiş invoice yarat

Step 2 — SERVICE_MONTHLY event yarat

Step 3 — Tətbiqi işə sal və nəticəni təsdiqlə

SERVICE_MONTHLY — Case 4: Keçmiş borc + cari ayın borcunu birlikdə, vaxtında ödəyir

Step 1 — Test mühitini təmizlə (lazım olduqda)

Step 2 — Ay 1 invoice-i yarat (due date 60 gün əvvəl, açıq qalsın)

Step 3 — Ay 2 invoice-i yarat (due date 30 gün əvvəl, açıq qalsın)

Step 4 — Ay 3 invoice-i yarat (due date hələ gəlməyib — gələcəkdə)

Step 5 — Hamısını birlikdə, bugün ödə

Step 6 — SERVICE_MONTHLY event yarat

Step 7 — Tətbiqi işə sal və nəticəni təsdiqlə

FINISH — Bütün borc ödənilib, kredit bağlanır

Step 1 — Subscription-un vəziyyətini yoxla

Step 2 — Subscription-u deaktiv et və FINISH event yarat

Step 3 — FINISH üçün uyğun invoice yarat

Step 4 — Tətbiqi işə sal və snapshot-u təsdiqlə

Комментарии

Отправить комментарий

Популярные сообщения из этого блога

Interview questions

Lesson1: JDK, JVM, JRE

Lesson_2: Operations in Java