AKB-Notification-Processor
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.
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.
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
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:
1.
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',
0,
SYSDATE,
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
);
2.
INSERT INTO ccontact_all (
customer_id,
ccseq,
ccfname,
cclname,
birthdate,
cccity,
cssocialsecno,
passportno
)
SELECT
99990001,
1,
'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
);
3.
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'
);
4.
5.
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,
0,
193,
1,
193,
100,
100,
100,
100,
DATE '2026-06-19'
);
6.
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;
Комментарии
Отправить комментарий