Akki
By: Anonymous12/29/202265 views Public Note
***********************************************************SOD-EOD ISSUE*****************************************************************
--------------------------------------------TILL STATUS------------------------------------
Select * From Storebo.As_Tl Where id_rpsty_tnd in ('102','103');
update Storebo.As_Tl set sc_tl=2 Where id_rpsty_tnd='20001';
Commit;
Select * From Storebo.Le_Hst_Tl Where id_rpsty_tnd='20001' And Dc_Dy_Bsn='2021-07-26';
Update Storebo.Le_Hst_Tl Set Sc_Hst_Tl=2 Where id_rpsty_tnd='20001' And Dc_Dy_Bsn='2021-07-26';
Commit;
--------------------------------------------REGISTER STATUS------------------------------------
Select * From Storebo.As_Ws Where Id_Ws='110';
Update Storebo.As_Ws Set Sc_Tml_Ws=0 Where Id_Ws='20001';
commit;
Select * From Storebo.Le_Hst_Ws Where Id_Ws=110 And Trunc(Ts_Tm_Srt)='26-july-21';
Update Storebo.Le_Hst_Ws Set Sc_Hst_Ws=0 Where Id_Ws=20001 And Trunc(Ts_Tm_Srt)='26-july-21';
commit;
--------------------------------------------ADDING REGISTER------------------------------------
select * from storebo.as_ws where id_ws='176';
INSERT INTO storebo.AS_WS (ID_WS,ID_STR_RT,CL_WS,ID_SPR,ID_PVR_SV,NM_WS_MF,NM_MDL_WS_TML,AD_DVC_TML,SC_TML_WS,QU_TL_WS,ID_AST_FX_WS_TML,TY_WS,FL_MOD_TRG,DC_DY_BSN,AI_TRN,EXT_ID_UNQ,TS_TM_SRT,ID_TL_CRT,CD_ACT,CP_BLNC_DFLT_OPN,CD_CL_TL_CNT,CD_OPN_FLT_CNT,CD_CL_FLT_CNT,CD_LON_CSH_CNT,CD_PKP_CSH_CNT,CD_PKP_CHK_CNT,FL_RCNL_TL,FL_ACT_WS,TS_CRT_RCRD,TS_MDF_RCRD) VALUES ('177','01655','RegularSales','0',0,NULL,NULL,NULL,0,1,0,NULL,'0','1999-09-04',0,'0000',NULL,NULL,'1',1000,'2','1','1','1','1','0','1','1',SYSDATE,SYSDATE);
commit;
--------------------------------------------ADSR STATUS------------------------------------
select * from storebo.TL_RCL_FL_DATA where dc_dy_bsn='2021-07-26' and id_rpsty_tnd='20001'; and id_rpsty_tnd='10013';
commit;
Insert into storebo.TL_RCL_FL_DATA
(ID_STR_RT,ID_WS,ID_RPSTY_TND,ID_OPR_OFF_SGN,DC_DY_BSN,CNT_RCL,FL_RCL,TS_CRT_RCRD,TS_MDF_RCRD,DSR_PRGRSS_EMP_ID,DSR_PRGRSS_FLAG,FLOAT_AMOUNT)
values ('01458',200,20001,0,'2021-07-26',0,'2',sysdate,sysdate,'0','N', '0');
commit;
select * from storebo.TL_DSR_FL_DATA where id_rpsty_tnd='112' and dsr_date='2021-10-12';
-----------------------------------------------MAX TRANSACTION / UNEXPECTED ERROR REGISTER----------------------------------------------
Select * From Storebo.As_Ws Where Id_Ws='991';
update max of ai_trn value.;
Select max(ai_trn) From Storebo.tr_trn where id_ws='991' and dc_dy_bsn='2022-03-19';
Select * From Storebo.tr_trn where id_ws='991' and dc_dy_bsn='2022-03-19';
update Storebo.As_Ws set ai_trn=137591 Where Id_Ws='991';
-----------------------------------------------UNEXPECTED ERROR TILL----------------------------------------------
unexpected error while opening TILL or register in POS
then check cash drawer if it is opened(1) then make it 0
SELECT * FROM storebo.as_drw_ws WHERE id_ws IN (102,103);
update storebo.as_drw_ws set sc_drw_ws = 0 WHERE id_ws IN (102,103);
update storebo.as_tl set TY_TL = 0 Where id_rpsty_tnd in ('102','103','101');
-----------------------------------------------EMERGENCY SOD----------------------------------------------
select *from storebo.CONFIG_PARAMETER;
UPDATE storebo.CONFIG_PARAMETER SET DSR_DAYS_VALIDITY='1';
commit;
-----------------------------------------------TILL RECONCLILATION NOT WORKING----------------------------------------------
As_Ws -> CD_CL_TL_CNT should be 2
reconciliation option not enable in 104 till
Select * From Storebo.As_Ws Where Id_Ws='104';
update storebo.as_ws set fl_rcnl_tl=1 where id_ws=104
-------------------------------------------------STORE OPEN-CLOSE------------------------------------------------
SELECT * from storebo.TR_STR_OPN_CL where dc_dy_bsn='2022-12-28';
select * from STOREBO.tr_tl_opn_cl where id_rpsty_tnd in ('14201') And Dc_Dy_Bsn='2022-12-28';
SELECT * from storebo.TR_WS_OPN_CL where dc_dy_bsn='2022-12-28';
------------------IN RMS----------
select store from rms.SA_STORE_DAY where BUSINESS_DATE='14-October-22' and store_status='W' and STORe in ('05207','05258') and store not in
(select store from rms.SA_STORE_DAY where BUSINESS_DATE='14-October-22' and store_status='C' and STORe in ('05207','05258'));
-------------------IN CO------------
select id_str_rt from storeco.tr_trn where dc_dy_bsn = '2022-10-14' and ty_trn = '6' and id_str_rt not in (select id_str_rt from storeco.tr_trn
where dc_dy_bsn = '2022-10-14' and ty_trn = '7');
-------------------------------------------------SOD-EOD SAME DAY-------------------------------------------------------
Make sure current rtlogs of day begin and day end not uploaded to ftp
If uploaded then remove it from FTP
FTP- ftp://172.19.101.120/RTlogs/04289/
select * from sa_store_day where store='04289' and dc_dy_bsn='2019-08-27';--Check in RMS
SELECT * from storebo.TR_STR_OPN_CL where dc_dy_bsn='2022-08-04';
select * from STOREBO.tr_tl_opn_cl where id_rpsty_tnd in ('14201') And Dc_Dy_Bsn='2022-08-04';
SELECT * from storebo.TR_WS_OPN_CL where dc_dy_bsn='2022-08-04';
SELECT * FROM storebo.tr_Trn WHERE dc_dy_bsn='2022-08-04' AND id_Str_Rt='01488';
Check in TR_TRN there should not be any financial Transaction.
select *from storebo.tr_trn where dc_dy_bsn='2022-08-04';
SELECT * FROM storebo.tr_Trn WHERE dc_dy_bsn='2022-08-04' AND id_Str_Rt='04402'; AND ai_trn IN ('1949','1950');
--DELETE FROM storebo.TR_TRN WHERE dc_dy_bsn='2022-08-04' AND ID_STR_RT='04402';AND ai_trn IN ('1949','1950');
SELECT id_pr_prd,L.* FROM storebo.LE_HST_STR L WHERE trunc(ts_tm_srt)='11-APR-22' AND ID_STR_RT='04402'; --id_pr_prd
--DELETE FROM storebo.LE_HST_STR where trunc(ts_tm_srt)='11-APR-22' AND ID_STR_RT='04402';
commit;
--- BELOW LINE, NEED TO REPLACE ID_PR_PRD VALUE BASED ON VALUE IN ABOVE LE_HST_STR QUERY
SELECT * FROM storebo.LE_HST_WS WHERE FY=2022 AND ID_STR_RT='01488' ORDER BY id_pr_prd DESC ;
--DELETE FROM storebo.LE_HST_WS WHERE ID_PR_PRD>=100 AND FY=2022 AND ID_STR_RT='01488';
SELECT * FROM storebo.cA_PRD_RP WHERE fy='2022' AND id_pr_prd='101';
--delete from storebo.cA_PRD_RP where id_pr_prd='101' and fy='2022';
SELECT * FROM storebo.LE_HST_STR_SF_TND WHERE dc_dy_bsn='2022-08-04' AND ID_STR_RT='01488';
--DELETE FROM storebo.LE_HST_STR_SF_TND where dc_dy_bsn='2022-08-04' AND ID_STR_RT='01488';
SELECT * FROM storebo.LE_LTM_MD_TND WHERE dc_dy_bsn='2022-08-04' AND ID_STR_RT='01488';
--DELETE FROM storebo.LE_LTM_MD_TND where dc_dy_bsn='2022-08-04' AND ID_STR_RT='01488' AND ai_trn IN ('1949','1950');
SELECT * FROM storebo.TR_CTL WHERE dc_dy_bsn='2022-08-04' AND ID_STR_RT='01488';
--DELETE FROM storebo.TR_CTL where dc_dy_bsn='2022-08-04' AND ID_STR_RT='01488';
SELECT * FROM storebo.TR_STR_OPN_CL WHERE dc_dy_bsn='2022-08-04' AND ID_STR_RT='01488';
--DELETE FROM storebo.TR_STR_OPN_CL where dc_dy_bsn='2022-08-04' AND ID_STR_RT='01488';
SELECT * FROM storebo.CA_DY_BSN_BTCH WHERE dc_dy_bsn='2022-08-04' AND ID_STR_RT='01488';
--DELETE FROM storebo.CA_DY_BSN_BTCH where dc_dy_bsn='2022-08-04' AND ID_STR_RT='01488';
SELECT * FROM storebo.TR_WS_OPN_CL WHERE DC_DY_BSN='2022-08-04' AND id_str_rt='01488';
--DELETE FROM storebo.TR_WS_OPN_CL WHERE DC_DY_BSN='2022-08-04' AND id_str_rt='01488';
SELECT * FROM storebo.TR_TL_OPN_CL WHERE dc_dy_bsn='2022-08-04';
--delete from storebo.TR_TL_OPN_CL where dc_dy_bsn='2022-08-04';
SELECT * FROM storebo.CA_DY_BSN WHERE fy='2022' AND dc_dy_bsn='2022-08-04';
--delete storebo.CA_DY_BSN where dc_dy_bsn='2022-08-04';
commit;
--PREPARE script ONLY FOR above transactions IN CO then send mail to DBA.support@landamrkgorup.in
==========================in CO========================
DELETE FROM storeco.LE_HST_STR where trunc(ts_tm_srt)='14-DEC-2020' AND ID_STR_RT='05532';
DELETE FROM storeco.LE_HST_WS WHERE ID_PR_PRD>=349 AND FY=2020 AND ID_STR_RT='05532';
DELETE FROM storeco.LE_HST_STR_SF_TND where dc_dy_bsn='2020-12-14' AND ID_STR_RT='05532';
DELETE FROM storeco.LE_LTM_MD_TND where dc_dy_bsn='2020-12-14' AND ID_STR_RT='05532';
DELETE FROM storeco.TR_CTL where dc_dy_bsn='2020-12-14' AND ID_STR_RT='05532';
DELETE FROM storeco.TR_STR_OPN_CL where dc_dy_bsn='2020-12-14' AND ID_STR_RT='05532';
DELETE FROM storeco.TR_TRN where dc_dy_bsn='2020-12-14' AND ID_STR_RT='05532';
DELETE FROM storeco.CA_DY_BSN_BTCH where dc_dy_bsn='2020-12-14' AND ID_STR_RT='05532';
DELETE FROM storeco.TR_WS_OPN_CL WHERE DC_DY_BSN='2020-12-14' AND id_str_rt='05532';
delete from storeco.TR_STR_OPN_CL where dc_dy_bsn='2020-12-14' AND ID_STR_RT='05532';
delete from storeco.TR_TL_OPN_CL where dc_dy_bsn='2020-12-14' AND ID_STR_RT='05532';
delete from storeco.TR_WS_OPN_CL where dc_dy_bsn='2020-12-14' AND ID_STR_RT='05532';
delete from storeco.cA_PRD_RP where id_pr_prd='349' and fy='2020';
delete storeco.CA_DY_BSN where dc_dy_bsn='2020-12-14' and dc_dy_bsn='2020-12-14';
delete storeco.cA_PRD_RP where id_pr_prd='349' and FY='2020';
--------------------------------------------------------ADSR--------------------------------------------
1.what till history have no of till same should be there reconcile table too.
2.CP_MD_TND_PKP_TOT, CP_TND_RCN_TOT, CP_TND_TOT.
SELECT T1.id_str_rt,T1.dc_dy_bsn,T2.ty_tnd,T1.id_rpsty_tnd,sum(T2.mo_itm_ln_tnd)
FROM STOREBO.tr_ltm_tnd T2,storebo.tr_trn T1 WHERE T1.dc_dy_bsn='2021-08-28'
AND T2.ty_tnd='CASH' and T1.id_ws=T2.id_ws and T1.ai_trn=T2.ai_trn and T1.dc_dy_bsn=T2.dc_dy_bsn
GROUP BY T1.id_str_rt,T1.dc_dy_bsn,T2.ty_tnd,T1.id_rpsty_tnd order by T1.id_rpsty_tnd;
--------------------------------------------------------------------------------------------------------
?Till Reconciliation Tables:
select * from storebo.TL_RCL_DATA Where Dc_Dy_Bsn='2021-08-30' and id_rpsty_tnd=112 ;
select * from storebo.TL_RCL_FL_DATA Where Dc_Dy_Bsn='2021-08-30' and id_rpsty_tnd=112 ;
Select * From Storebo.LE_HST_TL_TND Where TS_TM_SRT LIKE '0-AUG-21%'; and id_rpsty_tnd='112';
Select * From Storebo.Le_Hst_Tl Where id_rpsty_tnd='103' And Dc_Dy_Bsn='2021-10-11';
?DSR Adjustment Tables:
select * from storebo.TL_DSR_TENDERS_DETAILS where Dc_Dy_Bsn='2021-08-30' and id_rpsty_tnd=112 ;
select * from storebo.TL_DSR_FL_DATA where id_rpsty_tnd='112' and dsr_date='2021-10-12';
select * from storebo.TL_DSR_AMT_DETAILS where Dc_Dy_Bsn='2021-08-30' and id_rpsty_tnd=112 ;
select * from storebo.TL_DSR_SHRT_EXC_DETAILS where Dc_Dy_Bsn='2021-08-30' and id_rpsty_tnd=112 ;
select * from storebo.TL_DSR_JUMBLING_DETAILS where Dc_Dy_Bsn='2021-08-30' and id_rpsty_tnd=112 ;
--Update storebo.TL_DSR_FL_DATA set FL_DSR='3' where DSR_DATE='2021-08-16' and ID_RPSTY_TND='105';
?Bank Deposit tables
select * from storebo.LSIPL_BNK_DPST where dc_dy_bsn='2021-10-12';;--FL_DPST-check bank deposit approved or not
select * from storebo.LSIPL_BNK_DPST_ADJST;
select * from storebo.LSIPL_BNK_DPST_SUBMSN where dc_dy_bsnN='2021-10-12';;--FL_BNKDPST check bank deposit approved or not
select * from storebo.LSIPL_BNK_DPST_TMP;
select * from storebo.LSIPL_BNK_DPST_ADJST_TMP;
------------------------------------------CASE STUDY--------------------------------------------------------------
1.After cashier submission when chief cashier login and tried to do DSR activity that time security excess error coming .
a. First check STOREBO.CO_ACS_GP_RS table need to check ID_GP_WRK(5) write access given or not (fl_acs_gp_wr=1)
b. If excess was given and chief cashier security access error , Then restart the client after clean up and check .
2. If chief cashier reject DSR after submission by the cashier, the status was update in TL_RCL_FL_DATA table fl_rcl=3.Below step issue he will face after that. resolution follow the below step
a. Informed cashier to login and tried once again submission process .(tried to do reconciliation again)
b. If system not allowed to do the submission or reconciliation process to cashier then update FL_RCL=1 in TL_RCL_FL_DATA table.
Then Informed to login with chief cashierID and do the DSR process .
***********************************************************CAPILLARY ISSUE*****************************************************************
------------------------------------------CAPILLARY DATA NOT MOVING--------------------------------------------------------------
POS_CRM.bat, which is not scheduled or scheduled incorrect or not started.
In order to issue the coupon to the eligible customers, the mandatory application requirement is:
The batch file should be in running mode
The batch file should run from SOD till EOD with a frequency of 5 mins
Before EOD , please ensure that the batch file is run.
Please check this before raising a ticket in this regards and run the batch file instead of raising tickets. Capillary will trigger the coupon immediately as soon as transaction posted in Capillary DB.
Kindly contact to your Store IT immediately and inform. Please find the attached screenshot for reference.
check path D:\POS_CAP_INSERT PCL last updated time.
------------------------------------------CAPILLARY COUPON NOT GENERATING--------------------------------------------------------------
Capillary data status will reflect in Check control Report and check it on the same day If coupon not triggered.
cap.bat file should be running in CRM server for server to capillary.
Kindly contact to your Store IT immediately and inform.
As per DB, all capillary transactions in I status till today so cross check cap.bat file .
--To check capillary configuration parameters for a store(FL_ISSUE_CAPL_CPN = 1)
select FL_ISSUE_CAPL_CPN,P.* from storebo.config_parameter P;
Tran type and Tran status for the Invoice to be checked and capillary req status(0/1/2) has to be checked
--0- Req AND response issue FROM capillary
--1-Coupon issued/trigeered/printed
--2- NOT eligible FOR coupons
select capillary_req_status,ty_trn,sc_trn,T.* from storebo.tr_trn T WHERE id_ws||ai_trn in ('1059313') and dc_dy_bsn='2020-11-26';
--To check the receipts for coupon code(if generated)
SELECT * FROM storebo.jl_enr WHERE id_ws||ai_trn IN ('102117478','102117481','102117485') AND dc_dy_bsn='2020-09-24';
--To check for coupon code in below table(if generated CPN_NO)
SELECT * FROM storebo.lmg_capillary_issued_cpns WHERE ai_trn in ('21891020117478','21891020117481','21891020117485');
--To check REQ_URL and RESP_URL(resp code and resp message in resp_url)
SELECT * FROM storebo.ls_capl_log WHERE id_ws||ai_trn in ('102117478','102117481','102117485') and dc_dy_bsn='2020-09-24';
--Capillary stage atatus has to be checked
select status,S.* from corep.pos_cap_stage s order by inv_date desc;where inv_Date='24-SEP-20';
SELECT * FROM corep.pos_cap_stage WHERE inv_Date='17-AUG-20';
select * from storebo.tr_trn where dc_dy_bsn='2020-09-23';
select * from storebo.tr_ltm_tnd where id_ws||ai_trn in ('10433462','15170939') and dc_dy_bsn='2020-09-16';
select * from storebo.tr_rtl where id_ws||ai_trn in ('10433462','15170939') and dc_dy_bsn='2020-09-16';
SELECT * FROM corep.pos_cap_stage WHERE inv_number='044711060001105';
select * from storebo.pa_phn where tl_cnct='9810759997';
select * from storebo.pa_phn where id_prty='5128334';
SELECT * FROM STOREBO.pa_ct WHERE id_ct='6705811267';
select * from STOREBO.pa_ct where id_prty in ('18697701');
select * from STOREBO.co_mdfr_rtl_prc;
If coupon is not triggered to eligible Invoice,
a) Store should share the LMR ID or mobile number, Customer name, Bill details to call center to get the coupon status
b) Call center team will check the details in capillary tool and revert whether coupon is triggered/not triggered. Call centre team will be able to check the status within 10 minutes of invoice generation
c) If coupon is not triggered, call center agent will arrange to send the coupon to customer registered mobile number.
LMR Call centre # : 1800 208 2444
***********************************************************PROMO ISSUE*****************************************************************
------------------------------------------IN BO-----------------------------------------------------
select id_prm,a.* from storebo.ru_prdv a where id_prm in ();
select * from storebo.lsipl_promo_queue where rule_id in ();
select change_type,count(rule_id) from STOREBO.lsipl_promo_queue where exp='-1' group by change_type;
update STOREBO.lsipl_promo_queue set exp=0 where exp='-1' and change_type='UPDATE';
commit;
select rule_id,max(co_sequence) from STOREBO.lsipl_promo_queue where RULE_ID in ('177653',) and change_type='UPDATE' group by rule_id;
update STOREBO.lsipl_promo_queue set exp=0 where co_sequence in ();
commit;
select * from STOREBO.lmg_co_el_prdv_itm_grp where id_ru_prdv=26395;
SELECT * FROM storebo.lmg_grp_itm_lst WHERE ID_GRP IN('2062');
select * from STOREBO.as_itm where ID_DPT_POS = '91' ;
-------------------------------------------count-------------------------------------------------
select a.id_prm,a.id_ru_prdv,b.id_grp,a.NM_RU_PRDV,count(c.id_itm) from STOREBO.ru_prdv a,STOREBO.lmg_co_el_prdv_itm_grp b,storebo.lmg_grp_itm_lst
c where b.id_grp=c.id_grp and a.id_prm in(188238,188239)
and a.id_ru_prdv=b.id_ru_prdv group by a.id_prm,a.id_ru_prdv,b.id_grp,a.NM_RU_PRDV;
------------------------------------------IN CO-----------------------------------------------------
select * from ru_prdv where id_ru_prdv in (183452);
select * from ls_co_ru_prdv_str where id_ru_prdv in (183452) and id_str_rt='01289';
update ls_co_ru_prdv_str set exp=0 where id_ru_prdv in () and id_str_rt='01289' and change_type='ADD';
commit;
update ls_co_ru_prdv_str set exp=0 where id_ru_prdv in (181342) and id_str_rt='01344' and change_type='UPDATE';
update STORECO.ls_co_ru_prdv_str set exp = 1 where msg_id in () and id_str_rt = '01794' and change_type = 'UPDATE' ;
commit;
SELECT TRUNC(ts_crt_rcrd),COUNT(1) FROM lmg_grp_itm_lst WHERE ID_GRP='143520' GROUP BY TS_CRT_RCRD;
------------------------------------------------count CO----------------------------------------------------
select a.id_prm,a.id_ru_prdv,b.id_grp,a.NM_RU_PRDV,count(c.id_itm) from storeco.ru_prdv a,storeco.lmg_co_el_prdv_itm_grp b,storeco.lmg_grp_itm_lst
c where b.id_grp=c.id_grp and a.id_ru_prdv in(189235)
and a.id_ru_prdv=b.id_ru_prdv group by a.id_prm,a.id_ru_prdv,b.id_grp,a.NM_RU_PRDV;
select * from ls_co_ru_prdv_str where trunc(TS_CRT_RCRD) between '01-Jan-22' and '09-Jan-22' and exp=0 and id_str_rt='05289' and change_type='UPDATE';
--------------------------------------Web logic cleanup--------------------------------------
Please check with Cluster/Regional IT do the below step.
There firstly close the weblogic
Then go to the below path
D:\OracleApp\Middleware\user_projects\domains\RBO_V14\servers\AdminServer
Here rename log , cache and temp
Now restart the weblogic and check after some time.
Promo start uploading check after some time.
Promo has been updated Kindly do CO Vs BO reconcile ,let as know if anything pending.
------------------------------------------Active Rules in CO