################## DB실행 팁 ##################
컬럼찾기
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%컬럼명%' AND TABLE_SCHEMA='디비명';
컬럼찾기
여러쿼리, 다중쿼리 실행 : 쿼리드래그 + Alt + X
AuroraDB에서 추출할려면 등록일 기준으로 1년치정도씩 끊어서 뽑아야 하고,
그래도 결과 안나오면 Athena에서 뽑아야 하는데 Athena에 대상 테이블 있는지 먼저 확인하셔야 겠네요.
################## DB접속 (개발) ##################
DynamoDB?
--공통
ltcmdev.c0xgcmsoxdvu.ap-northeast-2.rds.amazonaws.com (LTCMSTDEV)
--엘롯데
elltdev.cbue0iqm0djc.ap-northeast-2.rds.amazonaws.com (ELLTETDEV, ELLTGDDEV)
username : b2_dev
pw : qwer1234
################## DB접속 (테스트) ##################
--상품
ellttst1.cluster-cncbkablkgnk.ap-northeast-2.rds.amazonaws.com : elltgdtst
ellttst3.cluster-cncbkablkgnk.ap-northeast-2.rds.amazonaws.com : elltettst
ellttst7.cluster-cncbkablkgnk.ap-northeast-2.rds.amazonaws.com : elltostst
--공통
ltcmtst1.cluster-coq9a9jwz0c5.ap-northeast-2.rds.amazonaws.com : ltcmsttst
username : b2_dev
pw : qwer1234
################## DB접속 (운영) ##################
https://pium.lotte.com/projects/DB/issues/DB-5134?filter=allissues-- 내꺼
https://pium.lotte.com/projects/DB/issues/DB-5141?filter=allissues-- 양경희
https://pium.lotte.com/browse/DB-5166-- 정남섭
생성은 지라작성 후에 메일보내기
---------------------------------
DB공용계정 개인계정(쓰기)
elltgdprd(상품) : u_p_yglee6 > 0805_Jira_5134 -> forever209!Q
ltcmstprd(시스템공통) : u_p_yglee6 > u_Jira_5445 -> forever209!Q -> forever209!!
elltetprd(거래처) : u_p_yglee6 > 0805_Jira_5134 -> forever209!Q
https://pium.lotte.com/projects/DB/issues/DB-5445?filter=allissues
--시스템공통
ltcmprd1.cluster-c5szlalpckan.ap-northeast-2.rds.amazonaws.com : ltcmstprd
--상품
elltprd1.cluster-ci9foimk0hp1.ap-northeast-2.rds.amazonaws.com : elltgdprd
elltprd3.cluster-ci9foimk0hp1.ap-northeast-2.rds.amazonaws.com : elltetprd
elltprd7.cluster-ci9foimk0hp1.ap-northeast-2.rds.amazonaws.com : elltosprd
---------------------------------
DB공용계정 ro로 사용(읽기)
u_ro_gduser / ozebwfvbs1u8u1k4 상품
u_ro_etuser / rn2g08hvqscmymss 거래처
u_ro_osuser / 5Vhx7dY-{mfE8~fF 출점
u_ro_stuser / bkn4onx2d5ero3z4 icms
u_ro_pruser / c37goqf4z4ic4m3p 판촉
u_ro_mbuser / 0esieko7cwj4syb1 회원
--상품
elltprd1.cluster-ro-ci9foimk0hp1.ap-northeast-2.rds.amazonaws.com : elltgdprd (elltprd1)
elltprd3.cluster-ro-ci9foimk0hp1.ap-northeast-2.rds.amazonaws.com : elltetprd (elltprd3)
elltprd7.cluster-ro-ci9foimk0hp1.ap-northeast-2.rds.amazonaws.com : elltosprd (elltprd7)
--공통
ltcmprd1.cluster-ro-c5szlalpckan.ap-northeast-2.rds.amazonaws.com : ltcmstprd (ltcmprd1)
################## 기존 EAI ##################
---------------------------------------------------------------------------------
Oracle DB계정
( HOST = 10.195.121.208 )(PORT = 1521)
---------------------------------------------------------------------------------
# 운영 : EAI_M3_RW / C070B56FKF0 EAI_EC
# 테스트 : EAI_TEST_RW / C4SKB13810F EAI_EC_TEST
# 개발 : EAI_DEV_RW / SPEW5V&NECR EAI_DEV
---------------------------------------------------------------------------------
Host : 10.195.121.208
Port : 1521
Database : EAI
User name / Password
EAI_DEV_RW / SPEW5V&NECR
EAI_TEST_RW / C4SKB13810F
EAI_M3_RO / CV2NBT6767W
################## 신규 EAI ##################
dev :
Server Host : eaidev.cluster-cbue0iqm0djc.ap-northeast-2.rds.amazonaws.com
Port : 3306
b2_dev / qwer1234
tst :
Server Host : eaitst.cluster-cncbkablkgnk.ap-northeast-2.rds.amazonaws.com
Port : 3306
b2_dev / qwer1234
prd_ro :
Server Host : eaiprd.cluster-ro-ci9foimk0hp1.ap-northeast-2.rds.amazonaws.com
Port : 3306
계정: u_ro_eaiuser
비번 : !jjV00P@7sfQ!!fGv
################## 쿼리 ##################
#엘롯데 공통코드조회
select scc.CSCO_ID
, scc.GROUP_CD
, (select scgc.GROUP_CD_NM from st_csco_grp_cd scgc where scgc.GROUP_CD = scc.GROUP_CD limit 0, 1) GROUP_CD_NM
, scc.CD
, scc.CD_NM
, scc.CD_EXPLN
, scc.SORT_RNKG
, scc.PLF_USE_YN
, scc.CSCO_APLY_YN
from st_csco_cd scc
where 1=1
and scc.csco_id = 'ELLOTTE'
and scc.group_cd = 'GOODS_APRV_STAT_CD' -- 상품승인상태코드
;
#파플출점코드
select * from os_ols_cd_mapng_out where site_no = '99999';
# 사용자인증 번호
select * from st_user_athnt
where USER_ID ='jeongim0001'
and RGST_DTTM >= date_add(now() ,interval -1 Hour )
#주문이 몰렸었네요
select a.`MODI_DTTM`,a.* From os_goods_api_cnetn_mtxt a
where `API_CNETN_SN` in (
select `API_CNETN_SN` From os_goods_api_cnetn
where `RGST_DTTM` >= '2022-08-18 19:25:00'
and `RGST_DTTM` <= '2022-08-18 19:27:18'
and API_BTCH_SN = 219
)
and `SEND_MTXT_CNTS` like '%LE1211542446_1265295207%'
#동일상품 g마켓 실시간 재고 호출입니다.
select *
from os_api_cnetn_mtxt where `API_CNETN_SN` in (
select API_CNETN_SN From os_api_cnetn
where `RGST_DTTM` >= '2022-08-18 19:25:00'
and `RGST_DTTM` <= '2022-08-18 19:27:18'
and `API_BTCH_SN` = 75
and `RECV_MTXT_CNTS` like '%1211542446%1265295207%'
)
#문성호수석 gd_goods_prc에 세일인데 gd_goods에는 일반가유형으로 되어 있거나 할인금액이 0원으로 되어있는 쿼리
select /*+ [ellotte-batch-goods].GoodsEndSaleDataModifyPrcDAO.getGoodsEndSaleGoodsDataModifyTgtList */
a.goods_no
,(select `DC_TYPE_CD` from gd_goods_prc x where x.goods_no = a.goods_no and `PRC_TYPE_CD` != '22' and now() between `STRT_DTTM` and `END_DTTM` order by `RGST_DTTM` desc limit 1) dc_type_prc
,(select `DC_AMT` from gd_goods_prc x where x.goods_no = a.goods_no and `PRC_TYPE_CD` != '22' and now() between `STRT_DTTM` and `END_DTTM` order by `RGST_DTTM` desc limit 1) dc_amt_prc
From gd_goods a
where a.MODI_DTTM >= date_add(now() , interval - 1 day)
and ifnull(concat(case when now() between a.SLPRC_STRT_DTTM and a.SLPRC_END_DTTM then SLPRC
else ORIG_SLPRC end , dc_amt ) , 'null')
!= (select concat(slprc , dc_amt)
from gd_goods_prc b , gd_sup_entprz_magn c
where a.goods_no = b.goods_no
and b.goods_no = c.goods_no
and b.PRC_TYPE_CD != '22'
and c.PRC_TYPE_CD != '22'
and b.PRC_TYPE_CD = c.PRC_TYPE_CD
and now() between c.STRT_DTTM and c.END_DTTM
and now() between b.STRT_DTTM and b.END_DTTM
order by b.PRC_TYPE_CD desc ,b.RGST_DTTM desc limit 1)
#최준혁수석 가격수정일시 기준으로 뽑으려면 조건을 바꾸면 될 거 같아요
select *
from (
select *
from (
select
goods_no,
(select PRC_TYPE_CD
from gd_goods_prc prc
where prc.GOODS_NO = a.GOODS_NO
and prc.END_DTTM > now()
and PRC_TYPE_CD != '22'
order by prc.PRC_TYPE_CD desc, prc.RGST_DTTM desc
limit 1) as prc_PRC_TYPE_CD,
(select dc_amt
from gd_goods_prc prc
where prc.GOODS_NO = a.GOODS_NO
and prc.END_DTTM > now()
and PRC_TYPE_CD != '22'
order by prc.PRC_TYPE_CD desc, prc.RGST_DTTM desc
limit 1) as prc_dc_amt,
RGST_DTTM,
DC_TYPE_CD,
dc_amt
from gd_goods a
where a.SL_STAT_CD in ('1', '2')
and a.BRND_NO != '0'
) t1
where t1.RGST_DTTM between '2018-08-01 00:00:00' and '2022-8-30 00:00:00'
and t1.prc_PRC_TYPE_CD = '20'
) t2
where 1= 1
-- and t2.prc_PRC_TYPE_CD != t2.dc_type_cd
and (t2.prc_PRC_TYPE_CD != t2.dc_type_cd or t2.dc_amt < 1)
#최준혁 수석 일일모니터링
-- 1.거래처 상품이 존재하지 않습니다.
-- 1) 엘롯데 거래처 상품 사용여부가 모두 N 이고 가격수신 내역이 없으면서 파플 DB 확인 해서 사용여부가 Y 존재하면 파플 DB 기준으로 엘롯데 DB 사용여부 데이터 보정 처리
-- 2.마진 불일치 실패 인데 그 차이가 엘롯데가 0.1 이 더 큰 경우
-- 1) 엘롯데 마진 데이터를 파포 수신 기준으로 보정
-- 3. 금액 불일치로 실패
-- 1) 파포 가격 수신 내역이 있는 경우
-- (1) 수신 가격이 있고 수신 된 가격과 파포 수신 가격이 불일치 하면 skip (파포에서 가격 수정 처리 중 오류 발생 된 케이스)
-- 2) 파포 가격 수신 내역이 없는 경우
-- (1) 파플DB 가격 확인해서 파플DB 가격과 엘롯데DB 가격이 맞는 다면 SKIP
-- (2) 파플DB 가격 확인해서 파플DB 가격과 파포수신 가격이 일치한다면
파플DB 의 판매조건번호와 엘롯데의 사용여부 Y 인 판매조건 번호를 확인 하고 불일치 한다면 Y,N 뒤바뀌어 보정 대상
-- 4. 판매가/마진변경(11)이지만, 변경 판매가격이 변경전 판매가격과 같습니다. 대상이 제일 많으나 skip
## 가격 수정IF 일일 모니터링 작업입니다.
select *
From gd_goods_if_recv_failr_log
where `MODI_DTTM` >= '2022-08-23 00:00:00'
and `GOODS_IF_ID` = 'PR_DS_15'
and `IF_RSLT_MSG_CNTS` <> '판매가/마진변경(11)이지만, 변경 판매가격이 변경전 판매가격과 같습니다.'
5:31
## 묶음상품 연동 확인 쿼리
select * from gd_goods where ENTPRZ_GOODS_NO = 'LE1448534764' and BRND_NO = '0'; ### 롯데ON 묶음상품번호로 엘롯데 상품번호 찾기
select * from gd_bgoods_cpnt where BGOODS_NO = '1206558620'; ### 묶음상품 구성상품 확인
### 출점DB
select GOODS_NO '상품번호'
, DATA_CNETN_DTTM '연동일시'
, CNETN_ERR_MSG '연동메시지'
, CNETN_BIZNES_DVS_CD
from os_intg_ec_goods_cnetn t1
where GOODS_NO in ('1206558620'); ### 롯데ON 묶음상품 연동 엘롯데묶음상품번호로 확인
### 출점배치
SELECT /*+ [open-batch] cohttp://m.lotte.batch.app.open.dao.openSlave.GdCommDAO.selectDuplicateGoods */ COUNT(1)
FROM os_11st_goods_cnetn a
, gd_goods_out b
, gd_brnd_out c
WHERE a.goods_no=b.goods_no
AND b.BRND_NO =c.BRND_NO
AND b.GOODS_NM ='다크네이비컬러 솔리드스타일 슬림라인 춘하정장(ZSEA01-102)'
AND IF(NOW() BETWEEN b.SLPRC_STRT_DTTM AND b.SLPRC_END_DTTM, b.SLPRC, b.ORIG_SLPRC)=50000
AND c.BRND_NM='맨잇슈트'
AND a.SITE_GOODS_NO IS NOT NULL
AND a.site_goods_no != ''
AND IFNULL(a.SL_END_YN,'N')='N'
AND a.SITE_GOODS_STAT_CD='SALE'
AND a.site_goods_no != '3524549643';
## 연동타임아웃 확인
select *
from OS_GOODS_API_CNETN t1, os_goods_api_cnetn_mtxt t2
where t1.API_CNETN_SN >= '4190700000'
# and RECV_MTXT_CNTS like '{%'
and t1.CNETN_MSG = '서버에 오류가 발생하여 요청을 수행할 수 없습니다.'
and t1.RGST_DTTM between '2022-08-30 20:15:00' and '2022-08-30 20:20:00'
and t1.API_CNETN_SN = t2.API_CNETN_SN;
#옵션명 변경해서 컬러칩 연동안되게
update gd_colr_chip_goods_opt
set OPT_NM = '컬러칩사용안함'
where goods_no = '1211491174'
and OPT_NM = '리필색상';
#포탈 연동
select * from gd_prtpt_recv_if_log
where IF_TYPE_CD = 'PR_DS_01'
order by IF_NO desc limit 2000;
#연동이력에 판매상태 업데이트
update os_intg_ec_goods_cnetn
set SITE_GOODS_STAT_CD = '4'
where GOODS_NO in (
'1212039368','1212039288','1212039325','1212039301','1212039271','1212039280','1212039275','1212039251','1212039281','1212039269','1212039293','1212039272','1212039266','1212039279'
)
and CNETN_BIZNES_DVS_CD = 'PD_API_0005';
#아이몰 연동성공,실패
select site_cd, site_cd_nm, cnt10014 as '아이몰'
from (
select
'000' as site_cd,
case btch_cnetn_stat_cd when '10' then '상품연동 성공합계' else '상품연동 실패합계' end as site_cd_nm,
ifnull(sum(case site_no when '10014' then btch_cnetn_cnt end),0) cnt10014
from (
select tm.agrg_tgt_dt as dt,
md.site_cd,
md.btch_cnetn_stat_cd as btch_cnetn_stat_cd,
sum(md.btch_cnetn_cnt) as btch_cnetn_cnt,
md.site_no as site_no
from os_ols_co_by_btch_mntrng_dtl md
inner join (select max(m2.btch_sn) as btch_sn,
m2.agrg_tgt_dt
from os_ols_co_by_btch_mntrng m2
where 1=1
and m2.agrg_tgt_dt in ('20221005', '20221006' , '20221007' ,'20221008' ,'20221009' ,'20221010' ,'20221011')
and m2.btch_xecut_stat_cd ='OK'
and m2.btch_dvs_cd = '20'
group by m2.agrg_tgt_dt) tm
where 1=1
and md.btch_sn = tm.btch_sn
group by tm.agrg_tgt_dt, btch_cnetn_stat_cd, md.site_cd, md.site_no
)fd
group by site_cd_nm, btch_cnetn_stat_cd
union all
select
gp.site_cd,
gp.site_cd_nm,
ifnull(sum(case site_no when '10014' then btch_cnetn_cnt end),0) cnt10014
from ( select c.site_cd_nm,
c.site_cd
from os_ols_cd_mapng c
where site_no = '10000'
and site_group_cd = 'MNTRNG_TYPE_CD'
and site_cd <> 'M00'
and c.use_yn ='Y') gp
left outer join (select tm.agrg_tgt_dt as dt,
md.site_cd,
sum(md.btch_cnetn_cnt) as btch_cnetn_cnt,
md.site_no as site_no
from os_ols_co_by_btch_mntrng_dtl md
inner join (select max(m2.btch_sn) as btch_sn , m2.agrg_tgt_dt
from os_ols_co_by_btch_mntrng m2
where 1=1
and m2.agrg_tgt_dt in ('20221005', '20221006' , '20221007' ,'20221008' ,'20221009' ,'20221010' ,'20221011')
and m2.btch_xecut_stat_cd = 'OK'
and m2.btch_dvs_cd = '20'
group by m2.agrg_tgt_dt) tm
where md.btch_sn = tm.btch_sn
and md.btch_cnetn_stat_cd = '20'
group by tm.agrg_tgt_dt, btch_cnetn_stat_cd, md.site_cd, md.site_no
) fd on gp.site_cd = fd.site_cd
group by gp.site_cd, gp.site_cd_nm
) mm
order by mm.site_cd
## 아울렛 상품정보 추출 (이용구)
select gseg.ENTSHP_NO, gseg.SUP_ENTPRZ_NO, eeo.ENTSHP_NM , eseo.ENTPRZ_NM
, gg.MDL_NO, gg.GOODS_NO, gg.GOODS_NM, gg.SL_STAT_CD
, gg.GOODS_NO,gseg.DPTSTR_UNT_CD, gseg.DPTSTR_BRNC_CD
from gd_goods gg
inner join gd_sup_entprz_goods gseg
on gg.GOODS_NO = gseg.GOODS_NO
inner join et_entshp_out eeo
on gseg.ENTSHP_NO = eeo.ENTSHP_NO
inner join et_sup_entprz_out eseo
on gseg.ENTSHP_NO = eseo.ENTSHP_NO and gseg.SUP_ENTPRZ_NO = eseo.SUP_ENTPRZ_NO
where gg.SL_STAT_CD in (1,2) and -- 256830
gseg.ENTSHP_NO in (
10048,10050,10051,10052,10053,10054,10055,10056,10057,10058,10060,10061,10062,10063,10064,10065,10067,10068,10069,10070,10073,10074
);
# 이상품이 문제가 있네요.승인테이블에 존재하고 있고 gd_goods 아직 생성 전 이네요.
select * from os_imll_goods_cnetn_tgt
where goods_no ='1212325298'
# 아울렛 상품 데이터 추출 요청드립니다.
# 추출 요건 : 입점업체가 아울렛(아울렛광주월드컵점 제외)인 상품 중 센터경유 상품 (판매중, 품절)
# 추출 항목 : 입점업체번호/명, 공급업체번호/명, 상품번호, 담당MD명
select ENTSHP_NO '입점업체번호'
, (select ENTSHP_NM from et_entshp_out ent where ent.ENTSHP_NO = t1.ENTSHP_NO) '입점업체명'
, SUP_ENTPRZ_NO '공급업체번호'
, (select ENTPRZ_NM from et_sup_entprz_out sup where sup.ENTSHP_NO = t1.ENTSHP_NO and sup.SUP_ENTPRZ_NO = t1.SUP_ENTPRZ_NO) '공급업체명'
, goods_no '상품번호'
, (select MD_ID from gd_md_gsgr gsgr where gsgr.MD_GSGR_NO = t1.MD_GSGR_NO and gsgr.LEVEL = '2') 'MD_ID'
from gd_goods t1
where t1.ENTSHP_NO in (
'10050','10051','10052','10053','10054','10055','10056','10057','10058','10063','10064','10065','10066','10068','10069','10070','10071','10072','10073','10061','10062','10074','10060'
)
and t1.DLV_PRCS_TYPE_CD = '11'
and t1.SL_STAT_CD in ('1', '2')
and t1.SL_END_DTTM >= now();
#아이몰 상품연동 성공 실패건수
select site_cd, site_cd_nm, cnt10014 as '아이몰'
from (
select
'000' as site_cd,
case btch_cnetn_stat_cd when '10' then '상품연동 성공합계' else '상품연동 실패합계' end as site_cd_nm,
ifnull(sum(case site_no when '10014' then btch_cnetn_cnt end),0) cnt10014
from (
select tm.agrg_tgt_dt as dt,
md.site_cd,
md.btch_cnetn_stat_cd as btch_cnetn_stat_cd,
sum(md.btch_cnetn_cnt) as btch_cnetn_cnt,
md.site_no as site_no
from os_ols_co_by_btch_mntrng_dtl md
inner join (select max(m2.btch_sn) as btch_sn,
m2.agrg_tgt_dt
from os_ols_co_by_btch_mntrng m2
where 1=1
and m2.agrg_tgt_dt in ('20221019', '20221020' , '20221021' ,'20221022' ,'20221023' ,'20221024' ,'20221025')
and m2.btch_xecut_stat_cd ='OK'
and m2.btch_dvs_cd = '10'
group by m2.agrg_tgt_dt) tm
where 1=1
and md.btch_sn = tm.btch_sn
group by tm.agrg_tgt_dt, btch_cnetn_stat_cd, md.site_cd, md.site_no
)fd
group by site_cd_nm, btch_cnetn_stat_cd
union all
select
gp.site_cd,
gp.site_cd_nm,
ifnull(sum(case site_no when '10014' then btch_cnetn_cnt end),0) cnt10014
from ( select c.site_cd_nm,
c.site_cd
from os_ols_cd_mapng c
where site_no = '10000'
and site_group_cd = 'MNTRNG_TYPE_CD'
and site_cd <> 'M00'
and c.use_yn ='Y') gp
left outer join (select tm.agrg_tgt_dt as dt,
md.site_cd,
sum(md.btch_cnetn_cnt) as btch_cnetn_cnt,
md.site_no as site_no
from os_ols_co_by_btch_mntrng_dtl md
inner join (select max(m2.btch_sn) as btch_sn , m2.agrg_tgt_dt
from os_ols_co_by_btch_mntrng m2
where 1=1
and m2.agrg_tgt_dt in ('20221019', '20221020' , '20221021' ,'20221022' ,'20221023' ,'20221024' ,'20221025')
and m2.btch_xecut_stat_cd = 'OK'
and m2.btch_dvs_cd = '10'
group by m2.agrg_tgt_dt) tm
where md.btch_sn = tm.btch_sn
and md.btch_cnetn_stat_cd = '20'
group by tm.agrg_tgt_dt, btch_cnetn_stat_cd, md.site_cd, md.site_no
) fd on gp.site_cd = fd.site_cd
group by gp.site_cd, gp.site_cd_nm
) mm
order by mm.site_cd
# 표준카테고리 데이터 보정
상품 :
update elltgdprd.gd_goods_intg_ec_scat
set INTG_EC_SCAT_ID = 'BC73120100',
MODR_ID = 'shmoon7_BC52090200'
where goods_no = '1211193753'
;
출점 :
update elltosprd.gd_goods_intg_ec_scat_out
set INTG_EC_SCAT_ID = 'BC73120100',
MODR_ID = 'shmoon7_BC52090200'
where goods_no = '1211193753'
;
-- 포탈 연동 거래처 패널티
-- 재고제한등록+센터배송제외 상품품절 (20221018010000 ~ 20221117080000)
-- 정상연동 처리
select * From DPTS_PR_CLBR_GOODS_PROC_HIST
where rgst_dtm >='20221018010000'
and RPSNT_CSTR_CHG_YN = 'PRD133140'
and up_delpl_cd= '013131'
and dwn_delpl_cd = '0020'
select * From gd_item_sup_entprz_stk a ,gd_goods b
where a.`SUP_ENTPRZ_NO` =25464
and a.`GOODS_NO` = b.`GOODS_NO`
and `STK_QTY` > 0
and b.`DLV_PRCS_TYPE_CD` <> '10'
and b.`SL_STAT_CD` <> '3'
-- 아이몰 상품
select * from elltosprd.os_imll_goods_opt_cnetn oigoc where site_goods_no = ’2028855488’;
-- 품번이 171149인 상품(판매중, 품절)
select a.goods_no, b.DPTSTR_GOODS_NO, a.MORD_GOODS_NO
from gd_goods a, gd_sup_entprz_goods b
where a.goods_no = b.GOODS_NO
and a.SL_STAT_CD <> '3'
and b.DPTSTR_UNT_CD = '171149'
;
# 상품 판매시작일시 데이터 보정 완료되었습니다.
update gd_goods
set SL_STRT_DTTM = '2022-11-17 00:00:00',
MODI_DTTM = now(),
MODR_ID = 'shmoon7_sl_strt_dttm'
where goods_no in (
'1212479101',
'1212479122',
'1212478976',
'1212479030'
);
update gd_goods_smry
set SL_STRT_DTTM = '2022-11-17 00:00:00',
MODI_DTTM = now(),
MODR_ID = 'shmoon7_sl_strt_dttm'
where goods_no in (
'1212479101',
'1212479122',
'1212478976',
'1212479030'
);
update gd_goods
set SL_STRT_DTTM = '2022-11-12 00:00:00',
MODI_DTTM = now(),
MODR_ID = 'shmoon7_sl_strt_dttm'
where goods_no in (
'1212475543',
'1212475528',
'1212475539'
);
update gd_goods_smry
set SL_STRT_DTTM = '2022-11-12 00:00:00',
MODI_DTTM = now(),
MODR_ID = 'shmoon7_sl_strt_dttm'
where goods_no in (
'1212475543',
'1212475528',
'1212475539'
);
# 표준카테고리 상품데이터 추출
select gd.goods_no '상품번호'
, gd.GOODS_NM '상품명'
, (select BRND_NM from gd_brnd brnd where brnd.BRND_NO = gd.BRND_NO) '브랜드명'
from gd_goods_intg_ec_scat t1, gd_goods gd
where t1.INTG_EC_SCAT_ID in (
'BC73010100','BC73010200','BC73010300','BC73010400','BC73010500','BC73010600','BC73010700','BC73010800','BC73010900','BC73011000','BC73011100','BC73011200','BC73011300','BC73020100','BC73020200','BC73020300','BC73020400','BC73020500','BC73020600','BC73020700','BC73020800','BC73020900','BC73030100','BC73030201','BC73030202','BC73030203','BC73030204','BC73030301','BC73030302','BC73030303','BC73030304','BC73030305','BC73030306','BC73030401','BC73030402','BC73030403','BC73040100','BC73040200','BC73040300','BC73040400','BC73040500','BC73040600','BC73040700','BC73050100','BC73050200','BC73050300','BC73050400','BC73050500','BC73050600','BC73050700','BC73050800','BC73060100','BC73060200','BC73060300','BC73060400','BC73060500','BC73060600','BC73060700','BC73060800','BC73060900','BC73061000','BC73061100','BC73070100','BC73070200','BC73070300','BC73070401','BC73070402','BC73070403','BC73070404','BC73070405','BC73070501','BC73070502','BC73070601','BC73070602','BC73070603','BC73070701','BC73070702','BC73070703','BC73070800','BC73070901','BC73070902','BC73070903','BC73070904','BC73070905','BC73070906','BC73070907','BC73071000','BC73080101','BC73080102','BC73080103','BC73080104','BC73080105','BC73080106','BC73080200','BC73080300','BC73080400','BC73080501','BC73080502','BC73080503','BC73080504','BC73080505','BC73080506','BC73080600','BC73090100','BC73090200','BC73090300','BC73090400','BC73090500','BC73090600','BC73090700','BC73100100','BC73100200','BC73100300','BC73100400','BC73100500','BC73100600','BC73100700','BC73100800','BC73100900','BC73110100','BC73110200','BC73110300','BC73110400','BC73110500','BC73110600','BC73110700','BC73110800','BC73110900','BC73111000','BC73111100','BC73111200','BC73120100','BC73120200','BC73120300','BC73120400', 'BC71010100','BC71010200','BC71010300','BC71010400','BC71010500','BC71010600','BC71010700','BC71010800','BC71010900','BC71011000','BC71011100','BC71011200','BC71011300','BC71011400','BC71020100','BC71020200','BC71020300','BC71020400','BC71030100','BC71030200','BC71030300','BC71030400','BC71030500','BC71030600','BC71030700','BC71040100','BC71040200','BC71040300','BC71040400','BC71040500','BC71040600','BC71050100','BC71050200','BC71050300','BC71050400','BC71050500','BC71050600','BC71050700','BC71050800','BC71050900','BC71051000','BC71060100','BC71060200','BC71060300','BC71060400','BC71060500','BC71060600','BC71060700','BC71060800','BC71060900','BC71061000','BC71061100'
)
and gd.goods_no = t1.goods_no
and gd.SL_STAT_CD in ('1', '2')
and gd.SL_END_DTTM >= now()
and gd.brnd_no != '0'
and gd.DTGT_SITE_DVS_CD not in ('20', '21')
and gd.ENTSHP_NO not in (
'10074','10073','10072','10053','10071','10068','10067','10066','10065','10064','10063','10062','10058','10054','10056','10055','10052','10050','10048'
);
- 추출기간 : 2019.11.1 ~ 2022.11.14
- 추출항목 : 상품번호, 상품명, 브랜드명
- 요건
1. 기간 내 등록된 상품 (판매중, 품절)
2. 대상상품 중 표준카테고리 1Depth 가 화장품/향수, 헤어/바디인 상품
3. 아래 거래처 상품은 제외
https://pium.lotte.com/browse/ELOP-16688
select gd.goods_no '상품번호'
, gd.GOODS_NM '상품명'
, (select BRND_NM from gd_brnd brnd where brnd.BRND_NO = gd.BRND_NO) '브랜드명'
from gd_goods_intg_ec_scat t1, gd_goods gd
where t1.INTG_EC_SCAT_ID in (
'BC73010100','BC73010200','BC73010300','BC73010400','BC73010500','BC73010600','BC73010700','BC73010800','BC73010900','BC73011000','BC73011100','BC73011200','BC73011300','BC73020100','BC73020200','BC73020300','BC73020400','BC73020500','BC73020600','BC73020700','BC73020800','BC73020900','BC73030100','BC73030201','BC73030202','BC73030203','BC73030204','BC73030301','BC73030302','BC73030303','BC73030304','BC73030305','BC73030306','BC73030401','BC73030402','BC73030403','BC73040100','BC73040200','BC73040300','BC73040400','BC73040500','BC73040600','BC73040700','BC73050100','BC73050200','BC73050300','BC73050400','BC73050500','BC73050600','BC73050700','BC73050800','BC73060100','BC73060200','BC73060300','BC73060400','BC73060500','BC73060600','BC73060700','BC73060800','BC73060900','BC73061000','BC73061100','BC73070100','BC73070200','BC73070300','BC73070401','BC73070402','BC73070403','BC73070404','BC73070405','BC73070501','BC73070502','BC73070601','BC73070602','BC73070603','BC73070701','BC73070702','BC73070703','BC73070800','BC73070901','BC73070902','BC73070903','BC73070904','BC73070905','BC73070906','BC73070907','BC73071000','BC73080101','BC73080102','BC73080103','BC73080104','BC73080105','BC73080106','BC73080200','BC73080300','BC73080400','BC73080501','BC73080502','BC73080503','BC73080504','BC73080505','BC73080506','BC73080600','BC73090100','BC73090200','BC73090300','BC73090400','BC73090500','BC73090600','BC73090700','BC73100100','BC73100200','BC73100300','BC73100400','BC73100500','BC73100600','BC73100700','BC73100800','BC73100900','BC73110100','BC73110200','BC73110300','BC73110400','BC73110500','BC73110600','BC73110700','BC73110800','BC73110900','BC73111000','BC73111100','BC73111200','BC73120100','BC73120200','BC73120300','BC73120400', 'BC71010100','BC71010200','BC71010300','BC71010400','BC71010500','BC71010600','BC71010700','BC71010800','BC71010900','BC71011000','BC71011100','BC71011200','BC71011300','BC71011400','BC71020100','BC71020200','BC71020300','BC71020400','BC71030100','BC71030200','BC71030300','BC71030400','BC71030500','BC71030600','BC71030700','BC71040100','BC71040200','BC71040300','BC71040400','BC71040500','BC71040600','BC71050100','BC71050200','BC71050300','BC71050400','BC71050500','BC71050600','BC71050700','BC71050800','BC71050900','BC71051000','BC71060100','BC71060200','BC71060300','BC71060400','BC71060500','BC71060600','BC71060700','BC71060800','BC71060900','BC71061000','BC71061100'
)
and gd.goods_no = t1.goods_no
and gd.SL_STAT_CD in ('1', '2')
and gd.SL_END_DTTM >= now()
and gd.brnd_no != '0'
and gd.DTGT_SITE_DVS_CD not in ('20', '21')
and gd.ENTSHP_NO not in (
'10074','10073','10072','10053','10071','10068','10067','10066','10065','10064','10063','10062','10058','10054','10056','10055','10052','10050','10048'
);
- 추출기간 : ~ 2022.11.15
- 추출항목 : 상품번호, 브랜드명
- 요건
1. 기간 내 등록된 상품 (판매중, 품절)
2. 대상상품 중 첨부파일 ‘데이터 추출요청’ 시트 내 브랜드에 등록된 상품
3. 첨부파일 ‘거래처 제외 대상_아울렛’ 시트 내 거래처 상품은 제외
https://pium.lotte.com/browse/ELOP-16696
select gd.goods_no '상품번호'
, gd.GOODS_NM '상품명'
, (select BRND_NM from gd_brnd brnd where brnd.BRND_NO = gd.BRND_NO) '브랜드명'
from gd_intg_ec_brnd t1
, gd_intg_ec_ellt_brnd_mapng t2
, gd_goods gd
where 1=1
and t1.INTG_EC_BRND_ID in (
'P4005',
'P894',
'P305',
'P82',
'P514',
'P658',
'P849',
'P17664',
'P1404',
'P1404',
'P1430',
'P1582',
'P1757',
'P1828',
'P1894',
'P1946',
'P1987',
'P5618',
'P12828',
'P2314',
'P2419',
'P2601 ',
'P21560',
'P2687',
'P17664',
'P2860',
'P2883',
'P3124',
'P3242',
'P3252',
'P3734',
'P3916',
'P4209',
'P21538',
'P4248',
'P4402',
'P20650',
'P20650',
'P4712',
'P4868',
'P4933'
)
and t1.INTG_EC_BRND_ID = t2.INTG_EC_BRND_ID
and t2.BRND_NO = gd.BRND_NO
and gd.SL_STAT_CD in ('1', '2')
and gd.SL_END_DTTM >= now()
and gd.brnd_no != '0'
and gd.DTGT_SITE_DVS_CD not in ('20', '21')
and gd.ENTSHP_NO not in (
'10074','10073','10072','10053','10071','10068','10067','10066','10065','10064','10063','10062','10058','10054','10056','10055','10052','10050','10048'
);
--가격확인 7346 건중 상품등록 실패 2건 제외하고 7344 건 가격 재연동 하겠습니다.
select count(*) From gd_goods_prc a
,gd_goods b
where `PRC_TYPE_CD` = '20'
and `END_DTTM` > now()
and a.goods_no = b.goods_no
and b.`SL_STAT_CD` <> '3'
and b.`DTGT_SITE_DVS_CD` not in ( '20' , '21')
-- 묶음상품 -> 테마를 6개까지 등록한다. -> 1테마에 100개까지 구성상품 등록가능하다.
엘롯데배치 : gd_goods_cpnt_out
캠페인에서 사용하는 묶음상품 out 테이블 엘롯데front 있을때 사용하던 거라서 영향도 없어요.
-- 포탈 표준카테고리 제어건 관련하여 카테고리 데이터 보정이 필요하여 데이터 추출 요청드립니다.
추출대상 : 첨부파일 내 표준카테고리 목록 시트에 있는 카테고리로 등록된 상품 (판매중, 품절)
추출항목 : 엘롯데 상품번호, 아이몰상품번호, 모델번호, 백화점 대표단품코드, 자점, 품번, 표준카테고리번호, 표준카테고리코드, 담당MD명
SELECT
a.goods_no AS "엘롯데 상품번호",
a.MDL_NO AS "모델번호",
gseg.dptstr_goods_no AS "백화점 대표단품코드",
gseg.dptstr_brnc_cd AS "자점",
gseg.dptstr_unt_cd AS "품번",
kkk.INTG_EC_SCAT_ID AS "표준카테고리번호",
CASE
WHEN kkk.DPTH_NO = 3
THEN
(SELECT concat(k1.INTG_EC_SCAT_NM, '>', k2.INTG_EC_SCAT_NM , '>', k3.INTG_EC_SCAT_NM)
FROM GD_INTG_EC_SCAT k3, GD_INTG_EC_SCAT k2, GD_INTG_EC_SCAT k1
WHERE k3.INTG_EC_SCAT_ID = kkk.INTG_EC_SCAT_ID
AND k3.INTG_EC_RNKH_SCAT_ID = k2.INTG_EC_SCAT_ID
AND k2.INTG_EC_RNKH_SCAT_ID = k1.INTG_EC_SCAT_ID )
ELSE
(SELECT concat(k1.INTG_EC_SCAT_NM, '>', k2.INTG_EC_SCAT_NM , '>', k3.INTG_EC_SCAT_NM, '>', k4.INTG_EC_SCAT_NM)
FROM GD_INTG_EC_SCAT k4, GD_INTG_EC_SCAT k3, GD_INTG_EC_SCAT k2, GD_INTG_EC_SCAT k1
WHERE k4.INTG_EC_SCAT_ID = kkk.INTG_EC_SCAT_ID
AND k4.INTG_EC_RNKH_SCAT_ID = k2.INTG_EC_SCAT_ID
AND k3.INTG_EC_RNKH_SCAT_ID = k2.INTG_EC_SCAT_ID
AND k2.INTG_EC_RNKH_SCAT_ID = k1.INTG_EC_SCAT_ID )
END AS "표준카테고리",
(
SELECT l.user_nm
FROM gd_md_gsgr k, st_user_out l
WHERE k.md_gsgr_no = a.md_gsgr_no
AND k.MD_ID = l.user_id
AND l.sys_dvs_cd = '02'
AND l.csco_id = 'ELLOTTE'
) AS "담당MD명"
FROM gd_intg_ec_scat kkk,
gd_goods_intg_ec_scat lll,
gd_goods a
JOIN gd_sup_entprz_goods gseg
ON (gseg.GOODS_NO = a.GOODS_NO)
WHERE kkk.PRTPT_USE_YN IS NOT NULL
AND lll.INTG_EC_SCAT_ID = kkk.INTG_EC_SCAT_ID
AND a.GOODS_NO = lll.GOODS_NO
AND a.SL_STAT_CD IN ('1', '2')
;
아이몰 주문연동 건 중에 아이몰 상품연동 테이블에 존재하지 않는 상품번호가 있어서 확인 요청 드립니다.
아이몰 주문번호: 20221128H35676
아이몰 상품번호: 2028856156
상품명: 여아) 티어드 니트 원피스 MKC2OO2001
select * from elltosprd.os_imll_goods_opt_cnetn oigoc where site_goods_no = ’2028856156’;
주문처리시 아이몰 상품번호/단품번호로 엘롯데 상품번호/단품번호를 매핑 시키는데
해당 테이블에 데이터가 존재하지 않아서 주문을 처리하지 못하고 있는 상태입니다.
데이터 확인 부탁드립니다.
-->
아이몰 상품번호로 마이그테이블 확인해보니 아이몰 상품번호가 다릅니다.
아이몰상품번호 : 2029081897로 확인됩니다.
select * from elltgdprd.elltmig.usm_mig_gd_item where IMLL_GOODS_NO = '2028856156'
select * from elltgdprd.gd_goods where MORD_GOODS_NO = '110000014237108'and MORD_SL_CNDT_NO = '22035845653';
select * from elltosprd.os_imll_goods_cnetn where goods_no = '1211375659';
-- PO / MPO 문자인증번호 확인 (jeongim0001 / wjddla1!)
select *
from st_user_athnt
where USER_ID ='jeongim0001'
and RGST_DTTM >= date_add(now() ,interval -1 Hour )
#아울렛 상품 데이터 추출 요청드립니다.
추출 요건 : 입점업체가 아울렛(아울렛광주월드컵점 제외)인 상품 중 센터경유 상품 (판매중, 품절)
추출 항목 : 입점업체번호/명, 공급업체번호/명, 상품번호, 담당MD명
https://pium.lotte.com/browse/ELOP-16770
담당MD이름, 전시대상사이트 추가
select ENTSHP_NO '입점업체번호'
, (select ENTSHP_NM from et_entshp_out ent where ent.ENTSHP_NO = t1.ENTSHP_NO) '입점업체명'
, SUP_ENTPRZ_NO '공급업체번호'
, (select ENTPRZ_NM from et_sup_entprz_out sup where sup.ENTSHP_NO = t1.ENTSHP_NO and sup.SUP_ENTPRZ_NO = t1.SUP_ENTPRZ_NO) '공급업체명'
, goods_no '상품번호'
, (select MD_ID from gd_md_gsgr gsgr where gsgr.MD_GSGR_NO = t1.MD_GSGR_NO and gsgr.LEVEL = '2') 'MD_ID'
, (SELECT l.user_nm
FROM gd_md_gsgr k, st_user_out l
WHERE k.md_gsgr_no = t1.MD_GSGR_NO
AND k.MD_ID = l.user_id
AND l.sys_dvs_cd = '02'
AND l.csco_id = 'ELLOTTE') 'MD명'
, case when t1.DTGT_SITE_DVS_CD = '00' then '닷컴+아이몰+엘롯데'
when t1.DTGT_SITE_DVS_CD = '10' then '닷컴+엘롯데'
when t1.DTGT_SITE_DVS_CD = '11' then '닷컴'
when t1.DTGT_SITE_DVS_CD = '12' then '엘롯데'
when t1.DTGT_SITE_DVS_CD = '20' then '아이몰'
when t1.DTGT_SITE_DVS_CD = '21' then '닷컴+아이몰'
when t1.DTGT_SITE_DVS_CD = '22' then '엘롯데+아이몰'
end '전시대상사이트'
from gd_goods t1
where t1.ENTSHP_NO in (
'10050','10051','10052','10053','10054','10055','10056','10057','10058','10063','10064','10065','10066','10068','10069','10070','10071','10072','10073','10061','10062','10074','10060'
) # 10067 광주월드컵 아울렛점 제외
and t1.DLV_PRCS_TYPE_CD = '11' # 센터경유
and t1.SL_STAT_CD in ('1', '2') # 판매중 , 품절
and t1.SL_END_DTTM >= now()
;
select ENTSHP_NO '입점업체번호'
, (select ENTSHP_NM from et_entshp_out ent where ent.ENTSHP_NO = t1.ENTSHP_NO) '입점업체명'
, SUP_ENTPRZ_NO '공급업체번호'
, (select ENTPRZ_NM from et_sup_entprz_out sup where sup.ENTSHP_NO = t1.ENTSHP_NO and sup.SUP_ENTPRZ_NO = t1.SUP_ENTPRZ_NO) '공급업체명'
, goods_no '상품번호'
, (select MD_ID from gd_md_gsgr gsgr where gsgr.MD_GSGR_NO = t1.MD_GSGR_NO and gsgr.LEVEL = '2') 'MD_ID'
from gd_goods t1
where t1.ENTSHP_NO in (
'10050','10051','10052','10053','10054','10055','10056','10057','10058','10063','10064','10065','10066','10068','10069','10070','10071','10072','10073','10061','10062','10074','10060'
) # 10067 광주월드컵 아울렛점 제외
and t1.DLV_PRCS_TYPE_CD = '11' # 센터경유
and t1.SL_STAT_CD in ('1', '2') # 판매중 , 품절
and t1.SL_END_DTTM >= now()
;
## 상품등록일 기준으로 2022-08 ~ 2022-11 까지 공통상품/전용상품(전시대상사이트구분코드)해서 등록건수, 상위MD상품군, MD명 추출하면 됩니다.
select temp.MD_GSGR_NO '상위 MD상품군번호'
, temp.MD_GSGR_NM '상위 MD상품군명'
, temp.USER_NM 'MD명'
, sum(`8월 공통상품` + `8월 엘롯데전용` + `8월 아이몰전용`) '8월합계'
, sum(`8월 공통상품`) '8월 공통상품'
, sum(`8월 엘롯데전용`) '8월 엘롯데전용'
, sum(`8월 아이몰전용`) '8월 아이몰전용'
, sum(`9월 공통상품` + `9월 엘롯데전용` + `9월 아이몰전용`) '9월합계'
, sum(`9월 공통상품`) '9월 공통상품'
, sum(`9월 엘롯데전용`) '9월 엘롯데전용'
, sum(`9월 아이몰전용`) '9월 아이몰전용'
, sum(`10월 공통상품` + `10월 엘롯데전용` + `10월 아이몰전용`) '10월합계'
, sum(`10월 공통상품`) '10월 공통상품'
, sum(`10월 엘롯데전용`) '10월 엘롯데전용'
, sum(`10월 아이몰전용`) '10월 아이몰전용'
, sum(`11월 공통상품` + `11월 엘롯데전용` + `11월 아이몰전용`) '11월합계'
, sum(`11월 공통상품`) '11월 공통상품'
, sum(`11월 엘롯데전용`) '11월 엘롯데전용'
, sum(`11월 아이몰전용`) '11월 아이몰전용'
from (
select rnkh_md.MD_GSGR_NO
, rnkh_md.MD_GSGR_NM
, mduser.USER_NM
, if(gd.DTGT_SITE_DVS_CD = '00' and gd.RGST_DTTM between '2022-08-01 00:00:00' and '2022-08-31 23:59:59', 1, 0) as '8월 공통상품'
, if(gd.DTGT_SITE_DVS_CD = '12' and gd.RGST_DTTM between '2022-08-01 00:00:00' and '2022-08-31 23:59:59', 1, 0) as '8월 엘롯데전용'
, if(gd.DTGT_SITE_DVS_CD = '20' and gd.RGST_DTTM between '2022-08-01 00:00:00' and '2022-08-31 23:59:59', 1, 0) as '8월 아이몰전용'
, if(gd.DTGT_SITE_DVS_CD = '00' and gd.RGST_DTTM between '2022-09-01 00:00:00' and '2022-09-30 23:59:59', 1, 0) as '9월 공통상품'
, if(gd.DTGT_SITE_DVS_CD = '12' and gd.RGST_DTTM between '2022-09-01 00:00:00' and '2022-09-30 23:59:59', 1, 0) as '9월 엘롯데전용'
, if(gd.DTGT_SITE_DVS_CD = '20' and gd.RGST_DTTM between '2022-09-01 00:00:00' and '2022-09-30 23:59:59', 1, 0) as '9월 아이몰전용'
, if(gd.DTGT_SITE_DVS_CD = '00' and gd.RGST_DTTM between '2022-10-01 00:00:00' and '2022-10-31 23:59:59', 1, 0) as '10월 공통상품'
, if(gd.DTGT_SITE_DVS_CD = '12' and gd.RGST_DTTM between '2022-10-01 00:00:00' and '2022-10-31 23:59:59', 1, 0) as '10월 엘롯데전용'
, if(gd.DTGT_SITE_DVS_CD = '20' and gd.RGST_DTTM between '2022-10-01 00:00:00' and '2022-10-31 23:59:59', 1, 0) as '10월 아이몰전용'
, if(gd.DTGT_SITE_DVS_CD = '00' and gd.RGST_DTTM between '2022-11-01 00:00:00' and '2022-11-30 23:59:59', 1, 0) as '11월 공통상품'
, if(gd.DTGT_SITE_DVS_CD = '12' and gd.RGST_DTTM between '2022-11-01 00:00:00' and '2022-11-30 23:59:59', 1, 0) as '11월 엘롯데전용'
, if(gd.DTGT_SITE_DVS_CD = '20' and gd.RGST_DTTM between '2022-11-01 00:00:00' and '2022-11-30 23:59:59', 1, 0) as '11월 아이몰전용'
from gd_Goods gd, gd_md_gsgr md, gd_md_gsgr rnkh_md, st_user_out mduser
where gd.brnd_no != '0'
and gd.RGST_DTTM between '2022-08-01 00:00:00' and '2022-11-30 23:59:59'
and gd.MD_GSGR_NO = md.MD_GSGR_NO
and md.RNKH_MD_GSGR_NO = rnkh_md.MD_GSGR_NO
and rnkh_md.MD_ID = mduser.USER_ID
and mduser.SYS_DVS_CD = '02'
) temp
group by temp.MD_GSGR_NO, temp.MD_GSGR_NM, temp.USER_NM;
# 배송지변경
update gd_goods
set ENTPRZ_DLV_RTPL_ADDR = '24315'
, MODI_DTTM = now()
, MODR_ID = 'dykim62_dlvrplc'
where SUP_ENTPRZ_NO = '40464'
and DLV_PRCS_TYPE_CD = '20'
and ENTPRZ_DLV_RTPL_ADDR = '20153'
and SL_STAT_CD in ('1', '2');
# 기간 내 브랜드 별 공통상품,롯데온전용,아이몰전용 상품 등록건 수
select temp.BRND_NO '브랜드번호'
, temp.BRND_NM '브랜드명'
, (select md_user.USER_NM
from gd_brnd_md brnd_md, st_user_out md_user
where brnd_md.BRND_NO = temp.BRND_NO
and md_user.USER_ID = brnd_md.MD_ID
and now() between STRT_DTTM and END_DTTM
) 'MD명'
, sum(`8월 공통상품` + `8월 엘롯데전용` + `8월 아이몰전용`) '8월합계'
, sum(`8월 공통상품`) '8월 공통상품'
, sum(`8월 엘롯데전용`) '8월 엘롯데전용'
, sum(`8월 아이몰전용`) '8월 아이몰전용'
, sum(`9월 공통상품` + `9월 엘롯데전용` + `9월 아이몰전용`) '9월합계'
, sum(`9월 공통상품`) '9월 공통상품'
, sum(`9월 엘롯데전용`) '9월 엘롯데전용'
, sum(`9월 아이몰전용`) '9월 아이몰전용'
, sum(`10월 공통상품` + `10월 엘롯데전용` + `10월 아이몰전용`) '10월합계'
, sum(`10월 공통상품`) '10월 공통상품'
, sum(`10월 엘롯데전용`) '10월 엘롯데전용'
, sum(`10월 아이몰전용`) '10월 아이몰전용'
, sum(`11월 공통상품` + `11월 엘롯데전용` + `11월 아이몰전용`) '11월합계'
, sum(`11월 공통상품`) '11월 공통상품'
, sum(`11월 엘롯데전용`) '11월 엘롯데전용'
, sum(`11월 아이몰전용`) '11월 아이몰전용'
from (
select gd.BRND_NO
, brnd.BRND_NM
, if(gd.DTGT_SITE_DVS_CD = '00' and gd.RGST_DTTM between '2022-08-01 00:00:00' and '2022-08-31 23:59:59', 1, 0) as '8월 공통상품'
, if(gd.DTGT_SITE_DVS_CD = '12' and gd.RGST_DTTM between '2022-08-01 00:00:00' and '2022-08-31 23:59:59', 1, 0) as '8월 엘롯데전용'
, if(gd.DTGT_SITE_DVS_CD = '20' and gd.RGST_DTTM between '2022-08-01 00:00:00' and '2022-08-31 23:59:59', 1, 0) as '8월 아이몰전용'
, if(gd.DTGT_SITE_DVS_CD = '00' and gd.RGST_DTTM between '2022-09-01 00:00:00' and '2022-09-30 23:59:59', 1, 0) as '9월 공통상품'
, if(gd.DTGT_SITE_DVS_CD = '12' and gd.RGST_DTTM between '2022-09-01 00:00:00' and '2022-09-30 23:59:59', 1, 0) as '9월 엘롯데전용'
, if(gd.DTGT_SITE_DVS_CD = '20' and gd.RGST_DTTM between '2022-09-01 00:00:00' and '2022-09-30 23:59:59', 1, 0) as '9월 아이몰전용'
, if(gd.DTGT_SITE_DVS_CD = '00' and gd.RGST_DTTM between '2022-10-01 00:00:00' and '2022-10-31 23:59:59', 1, 0) as '10월 공통상품'
, if(gd.DTGT_SITE_DVS_CD = '12' and gd.RGST_DTTM between '2022-10-01 00:00:00' and '2022-10-31 23:59:59', 1, 0) as '10월 엘롯데전용'
, if(gd.DTGT_SITE_DVS_CD = '20' and gd.RGST_DTTM between '2022-10-01 00:00:00' and '2022-10-31 23:59:59', 1, 0) as '10월 아이몰전용'
, if(gd.DTGT_SITE_DVS_CD = '00' and gd.RGST_DTTM between '2022-11-01 00:00:00' and '2022-11-30 23:59:59', 1, 0) as '11월 공통상품'
, if(gd.DTGT_SITE_DVS_CD = '12' and gd.RGST_DTTM between '2022-11-01 00:00:00' and '2022-11-30 23:59:59', 1, 0) as '11월 엘롯데전용'
, if(gd.DTGT_SITE_DVS_CD = '20' and gd.RGST_DTTM between '2022-11-01 00:00:00' and '2022-11-30 23:59:59', 1, 0) as '11월 아이몰전용'
from gd_Goods gd, gd_brnd brnd
where gd.BRND_NO = brnd.BRND_NO
and gd.RGST_DTTM between '2022-08-01 00:00:00' and '2022-11-30 23:59:59'
) temp
group by temp.BRND_NO, temp.BRND_NM;
# md상품군 데이터 보정
--update gd_goods a
--update gd_goods_smry a
set a.MD_GSGR_NO = '1001',
a.MODI_DTTM = now(),
a.MODR_ID = 'shmoon7_chg_md_gsgr_no'
where a.goods_no in (
'1211914857',
'1211914746'
);
#표준카테고리 데이터보정
상품DB 참고쿼리 :
-- explain
update (select t1.goods_no
from dbaprd.jira_5344_intg_ec_scat_20221216 t1
inner join gd_goods t2 on t1.goods_no = t2.goods_no
where t2.sl_stat_cd <> '3'
and t1.mdl_no != 'OK'
limit 300
) tmp
inner join dbaprd.jira_5344_intg_ec_scat_20221216 b on tmp.goods_no = b.goods_no
inner join gd_goods_intg_ec_scat a on a.intg_ec_scat_id = b.intg_ec_scat_id and a.goods_no = b.goods_no
set a.modr_id = concat('shmoon7_',a.intg_ec_scat_id),
a.intg_ec_scat_id = b.chg_intg_ec_scat_id,
b.mdl_no = 'OK'
;
출점DB 참고쿼리 :
-- explain
update (select t1.goods_no
from dbaprd.jira_5344_intg_ec_scat_20221216 t1
inner join gd_goods_out t2 on t1.goods_no = t2.goods_no
where t2.sl_stat_cd <> '3'
and t1.mdl_no != 'OK'
limit 3000
) tmp
inner join dbaprd.jira_5344_intg_ec_scat_20221216 b on tmp.goods_no = b.goods_no
inner join gd_goods_intg_ec_scat_out a on a.intg_ec_scat_id = b.intg_ec_scat_id and a.goods_no = b.goods_no
set a.modr_id = concat('shmoon7_',a.intg_ec_scat_id),
a.intg_ec_scat_id = b.chg_intg_ec_scat_id,
a.modi_dttm = now(),
b.mdl_no = 'OK'
1. 추출 기간 : 상품등록일 기준 2019.9.1 ~ 현재 시점
2. 첨부파일 ★최종_12월 카드 7%_1221-1231’ 파일 중 “최종_카드 7%” 시트에 있는 롯데온 브랜드에 매핑된 엘롯데브랜드 상품
3. 첨부파일 ‘아울렛 거래처번호 리스트’의 거래처 상품은 추출대상에서 제외
4. 판매중+품절 상품 대상 / 엘롯데전용, 엘롯데+아이몰 공용 (아이몰 전용상품 제외)
5. 추출항목 : 상품번호, 롯데온 브랜드명, 롯데온 브랜드코드
select gd.GOODS_NO '상품번호', INTG_EC_BRND_ID '롯데온브랜드번호'
, (select BRND_RPRT_NM from GD_INTG_EC_BRND ecbrnd where ecbrnd.INTG_EC_BRND_ID = brmap.INTG_EC_BRND_ID) '롯데온브랜드명'
from gd_intg_ec_ellt_brnd_mapng brmap
inner join gd_goods gd on brmap.BRND_NO = gd.brnd_no
where brmap.INTG_EC_BRND_ID in(
'P2860','P2314','P4248','P870','P3270'
,'P849','P1422','P4533','P4849','P2052'
,'P37027','P1907','P393','P4410','P4005'
,'P1934','P3282','P6486','P326','P751'
,'P1558','P3976','P364','P2687','P306'
,'P400','P3972','P2601','P4130','P1654'
,'P4854','P21542','P2299','P2056','P2002'
,'P21538','P4633','P2419','P1828','P3734'
)
and gd.SL_STAT_CD in ('1', '2')
and gd.DTGT_SITE_DVS_CD not in ('20', '21')
and gd.brnd_no != '0'
and gd.SL_END_DTTM >= now()
and gd.RGST_DTTM >='2019-09-01'
and gd.ENTSHP_NO not in (
'10074','10073','10072','10053','10071','10068','10067','10066','10065','10064','10063','10062','10058','10054','10056','10055','10052','10050','10048'
)
#표준카테고리 목록 시트에 있는 카테고리로 등록된 상품 (판매중, 품절) 건수 확인요청드립니다.
5,385건입니다.
엘롯데 단독 : 423
아이몰 단독 : 3732
엘롯데+아이몰 : 1234
총 : 5385 -> 5389
select count(1)
from gd_goods_intg_ec_scat a, gd_goods b
where a.goods_no = b.goods_no
and b.sl_stat_cd in ('1','2')
and b.goods_cpnt_cd != '04'
and now() between b.sl_strt_dttm and b.sl_end_dttm
and a.intg_ec_scat_id in (
'BC04010100', 'BC04010200', …);
#등록된 하위브랜드 중 제작설치 사용여부가 Y인 브랜드의 하위브랜드번호, 브랜드명 추출 요청드립니다
select BRND_NO '엘롯데 브랜드번호'
, BRND_NM '브랜드명'
from gd_brnd where MFG_INST_PSB_YN = 'Y' and LEVEL = '2';
#MD 퇴사로 인하여 MD에 매핑되어있는 브랜드 정보 이관 요청드립니다.
기존 : 홍성준 (121600057)
변경 : 김경은 (121600222)
update gd_brnd_md
set END_DTTM = now()
, MODI_DTTM = now()
, MODR_ID = 'sijung4'
where MD_ID = '121600057'
insert gd_brnd_md
select BRND_NO, now(), '9999-12-31 23:59:59', '121600222', now(), 'sijung4' , now(), 'sijung4'
from gd_brnd_md a
where MD_ID = '121600057'
and BRND_NO not in(select brnd_no from gd_brnd_md where MD_ID = '121600222' )
#상품 배송기일코드 변경 완료했습니다.
update gd_goods
set DLV_DUDT_CD = '30'
, MODR_ID = 'sijung4'
, MODI_DTTM = now()
where GOODS_NO in(
'1207562173','1207561900','1207561758','1207561657','1207561613','1207107312','1207107178','1207106945','1207106821','1207106501'
)
#상세내용확인
select * from gd_goods where RGST_DTTM >= '2021-01-01' and RGST_DTTM <= '2021-12-01'
https://s3-ap-northeast-2.amazonaws.com/ellt.static.lotteeps.com/goods/dtl/cont/cont_1208792865
https://s3-ap-northeast-2.amazonaws.com/ellt.static.lotteeps.com/goods/dtl/cont/cont_1206442814
#업무연락미확인건 가능여부! 검토필요
#표준카테고리 업데이트 요청드리며, 업데이트 및 포탈 연동 완료 후 대상 상품 데이터 추출 요청드립니다.
추출대상 : 첨부파일 내 표준카테고리 목록 시트에 있는 카테고리로 등록된 상품 (판매중, 품절)
추출항목 : 엘롯데 상품번호, 아이몰상품번호, 모델번호, 백화점 대표단품코드, 자점, 품번, 표준카테고리번호, 표준카테고리코드
SELECT
a.goods_no AS "엘롯데 상품번호",
case when a.dtgt_site_dvs_cd in ('00', '20' ,'21', '22') then
(select site_goods_no from os_imll_goods_cnetn_out ia where ia.goods_no = a.goods_no AND ia.SITE_GOODS_NO IS NOT NULL AND ia.APRV_CNETN_STAT_CD != '30' order by modi_dttm limit 1)
else null end as "아이몰 상품번호",
a.MDL_NO AS "모델번호",
gseg.dptstr_goods_no AS "백화점 대표단품코드",
gseg.dptstr_brnc_cd AS "자점",
gseg.dptstr_unt_cd AS "품번",
kkk.INTG_EC_SCAT_ID AS "표준카테고리번호",
CASE
WHEN kkk.DPTH_NO = 3
THEN
(SELECT concat(k1.INTG_EC_SCAT_NM, '>', k2.INTG_EC_SCAT_NM , '>', k3.INTG_EC_SCAT_NM)
FROM GD_INTG_EC_SCAT k3, GD_INTG_EC_SCAT k2, GD_INTG_EC_SCAT k1
WHERE k3.INTG_EC_SCAT_ID = kkk.INTG_EC_SCAT_ID
AND k3.INTG_EC_RNKH_SCAT_ID = k2.INTG_EC_SCAT_ID
AND k2.INTG_EC_RNKH_SCAT_ID = k1.INTG_EC_SCAT_ID )
ELSE
(SELECT concat(k1.INTG_EC_SCAT_NM, '>', k2.INTG_EC_SCAT_NM , '>', k3.INTG_EC_SCAT_NM, '>', k4.INTG_EC_SCAT_NM)
FROM GD_INTG_EC_SCAT k4, GD_INTG_EC_SCAT k3, GD_INTG_EC_SCAT k2, GD_INTG_EC_SCAT k1
WHERE k4.INTG_EC_SCAT_ID = kkk.INTG_EC_SCAT_ID
AND k4.INTG_EC_RNKH_SCAT_ID = k2.INTG_EC_SCAT_ID
AND k3.INTG_EC_RNKH_SCAT_ID = k2.INTG_EC_SCAT_ID
AND k2.INTG_EC_RNKH_SCAT_ID = k1.INTG_EC_SCAT_ID )
END AS "표준카테고리",
(
SELECT l.user_nm
FROM gd_md_gsgr k, st_user_out l
WHERE k.md_gsgr_no = a.md_gsgr_no
AND k.MD_ID = l.user_id
AND l.sys_dvs_cd = '02'
AND l.csco_id = 'ELLOTTE'
) AS "담당MD명"
FROM gd_intg_ec_scat kkk,
gd_goods_intg_ec_scat lll,
gd_goods a
JOIN gd_sup_entprz_goods gseg
ON (gseg.GOODS_NO = a.GOODS_NO)
WHERE kkk.PRTPT_USE_YN IS NOT NULL
AND lll.INTG_EC_SCAT_ID = kkk.INTG_EC_SCAT_ID
AND a.GOODS_NO = lll.GOODS_NO
AND a.SL_STAT_CD IN ('1', '2')
;
#먼저 추출한 데이터가 파트너에서 조회하는 기준으로 추출을 한 것인데…
프로 기준으로 조건 추가하여 다시 추출해서 첨부합니다.
추출쿼리
SELECT
a.goods_no AS "엘롯데 상품번호",
case when a.dtgt_site_dvs_cd in ('00', '20' ,'21', '22') then
(select site_goods_no from os_imll_goods_cnetn_out ia where ia.goods_no = a.goods_no AND ia.SITE_GOODS_NO IS NOT NULL AND ia.APRV_CNETN_STAT_CD != '30' order by modi_dttm limit 1)
else null end as "아이몰 상품번호",
a.MDL_NO AS "모델번호",
gseg.dptstr_goods_no AS "백화점 대표단품코드",
gseg.dptstr_brnc_cd AS "자점",
gseg.dptstr_unt_cd AS "품번",
kkk.INTG_EC_SCAT_ID AS "표준카테고리번호",
CASE
WHEN kkk.DPTH_NO = 3
THEN
(SELECT concat(k1.INTG_EC_SCAT_NM, '>', k2.INTG_EC_SCAT_NM , '>', k3.INTG_EC_SCAT_NM)
FROM GD_INTG_EC_SCAT k3, GD_INTG_EC_SCAT k2, GD_INTG_EC_SCAT k1
WHERE k3.INTG_EC_SCAT_ID = kkk.INTG_EC_SCAT_ID
AND k3.INTG_EC_RNKH_SCAT_ID = k2.INTG_EC_SCAT_ID
AND k2.INTG_EC_RNKH_SCAT_ID = k1.INTG_EC_SCAT_ID )
ELSE
(SELECT concat(k1.INTG_EC_SCAT_NM, '>', k2.INTG_EC_SCAT_NM , '>', k3.INTG_EC_SCAT_NM, '>', k4.INTG_EC_SCAT_NM)
FROM GD_INTG_EC_SCAT k4, GD_INTG_EC_SCAT k3, GD_INTG_EC_SCAT k2, GD_INTG_EC_SCAT k1
WHERE k4.INTG_EC_SCAT_ID = kkk.INTG_EC_SCAT_ID
AND k4.INTG_EC_RNKH_SCAT_ID = k2.INTG_EC_SCAT_ID
AND k3.INTG_EC_RNKH_SCAT_ID = k2.INTG_EC_SCAT_ID
AND k2.INTG_EC_RNKH_SCAT_ID = k1.INTG_EC_SCAT_ID )
END AS "표준카테고리",
(
SELECT l.user_nm
FROM gd_md_gsgr k, st_user_out l
WHERE k.md_gsgr_no = a.md_gsgr_no
AND k.MD_ID = l.user_id
AND l.sys_dvs_cd = '02'
AND l.csco_id = 'ELLOTTE'
) AS "담당MD명"
FROM gd_intg_ec_scat kkk,
gd_goods_intg_ec_scat lll,
gd_goods a
JOIN gd_sup_entprz_goods gseg
ON (gseg.GOODS_NO = a.GOODS_NO
AND gseg.SUP_ENTPRZ_NO = A.SUP_ENTPRZ_NO
)
WHERE kkk.PRTPT_USE_YN IS NOT NULL
AND lll.INTG_EC_SCAT_ID = kkk.INTG_EC_SCAT_ID
AND a.GOODS_NO = lll.GOODS_NO
AND a.SL_STAT_CD IN ('1', '2')
;
#상품 예약정보 변경부탁드립니다.
1201662970
예약 시작일 : 1/3
예약 종료일 : 1/5
발송예정일 : 1/6
update gd_goods_rsv_hst
set RSV_STRT_DTTM = '2023-01-03 00:00:00',
RSV_END_DTTM = '2023-01-05 23:59:59',
RLOR_BGT_DTTM = '2023-01-06 00:00:00',
`HST_STRT_DTTM` = '2023-01-03 00:00:00' ,
`HST_END_DTTM` = '2023-01-05 23:59:59'
MODI_DTTM = now(),
MODR_ID = 'shmoon7'
where goods_no = '1201662970'
;
update gd_goods
set `SL_WAY_CD` = '2'
where goods_no = '1201662970'
;
update gd_goods_smry
set `SL_WAY_CD` = '2'
where goods_no = '1201662970'
#판매종료일시 데이터보정요청건
gd_goods 테이블 테이블 -> sl_end_dttm, SLPRC_END_DTTM 2개다 업데이트
gd_goods_smry 테이블 -> sl_end_dttm
#포탈 상품연동 실패건입니다
select * from gd_goods_if_recv_failr_log
where goods_if_id in ('PR_DS_01', 'PR_DS_02', 'PR_DS_06', 'PR_DS_07', 'PR_DS_08', 'PR_DS_10', 'PR_DS_12',
'PR_DS_15', 'PR_DS_16', 'PR_DS_18', 'PR_DS_21', 'PR_DS_25', 'PR_DS_10_COM')
and rgst_dttm >= '2022-12-28'
and rgst_dttm < '2023-01-04'
group by goods_if_id, if_rslt_msg_cnts, rgst_dttm
#하위브랜드번호 : 9026945(아울렛브랜드) / 271479 / 624
상품판매조건 : 판매중 + 품절 (판매종료 제외)
필요 데이터 : 상품조회 화면 기준 전체 데이터 (추출 항목은 첨부파일 참고)
select gd.goods_no '엘롯데 상품번호'
, sup_goods.DPTSTR_GOODS_NO '백화점 대표단품코드'
, sup_goods.DPTSTR_BRNC_CD '자점코드'
, sup_goods.DPTSTR_UNT_CD '점포품번'
, gd.MORD_GOODS_NO '통판상품번호'
, gd.MORD_SL_CNDT_NO '통판판매조건번호'
, sup_goods.ENTSHP_NO '입점업체번호'
, sup_goods.SUP_ENTPRZ_NO '공급업체번호'
, (select ENTSHP_NM from et_entshp_out ent where ent.ENTSHP_NO = sup_goods.ENTSHP_NO) '입점업체명'
, (select ENTPRZ_NM from et_sup_entprz_out sup where sup.SUP_ENTPRZ_NO = sup_goods.SUP_ENTPRZ_NO) '공급업체명'
, BRND_NO '브랜드번호'
, (select BRND_NM from gd_brnd brnd where brnd.BRND_NO = gd.BRND_NO) '브랜드명'
from gd_goods gd
inner join gd_sup_entprz_goods sup_goods
ON gd.goods_no = sup_goods.GOODS_NO
and USE_YN = 'Y'
where gd.BRND_NO in ('9026945','271479','624')
and SL_STAT_CD in ('1', '2');
추출 요건은 다음과 같습니다.
1. 추출 기간 : 상품등록일 기준 2019.9.1 ~ 현재 시점
2. 첨부파일 ★최종_12월 카드 7%_1221-1231’ 파일 중 “최종_카드 7%” 시트에 있는 롯데온 브랜드에 매핑된 엘롯데브랜드 상품
3. 첨부파일 ‘아울렛 거래처번호 리스트’의 거래처 상품은 추출대상에서 제외
4. 판매중+품절 상품 대상 / 엘롯데전용, 엘롯데+아이몰 공용 (아이몰 전용상품 제외)
5. 추출항목 : 상품번호, 롯데온 브랜드명, 롯데온 브랜드코드
-- 476,016건 (상품번호_롯데온브랜드 추출_20221219_1.csv)
select gd.GOODS_NO '상품번호', INTG_EC_BRND_ID '롯데온브랜드번호'
, (select BRND_RPRT_NM from GD_INTG_EC_BRND ecbrnd where ecbrnd.INTG_EC_BRND_ID = brmap.INTG_EC_BRND_ID) '롯데온브랜드명'
from gd_intg_ec_ellt_brnd_mapng brmap
inner join gd_goods gd on brmap.BRND_NO = gd.brnd_no
where brmap.INTG_EC_BRND_ID in(
'P2860','P2314','P4248','P870','P3270'
,'P849','P1422','P4533','P4849','P2052'
,'P37027','P1907','P393','P4410','P4005'
,'P1934','P3282','P6486','P326','P751'
,'P1558','P3976','P364','P2687','P306'
,'P400','P3972','P2601','P4130','P1654'
,'P4854','P21542','P2299','P2056','P2002'
,'P21538','P4633','P2419','P1828','P3734'
)
and gd.SL_STAT_CD in ('1', '2')
and gd.DTGT_SITE_DVS_CD not in ('20', '21')
and gd.brnd_no != '0'
and gd.SL_END_DTTM >= now()
and gd.RGST_DTTM >='2019-09-01'
and gd.ENTSHP_NO not in (
'10074','10073','10072','10053','10071','10068','10067','10066','10065','10064','10063','10062','10058','10054','10056','10055','10052','10050','10048'
)
-- 601,447건 (상품번호_롯데온브랜드 추출_20221219_2.csv)
select gd.GOODS_NO '상품번호', INTG_EC_BRND_ID '롯데온브랜드번호'
, (select BRND_RPRT_NM from GD_INTG_EC_BRND ecbrnd where ecbrnd.INTG_EC_BRND_ID = brmap.INTG_EC_BRND_ID) '롯데온브랜드명'
from gd_intg_ec_ellt_brnd_mapng brmap
inner join gd_goods gd on brmap.BRND_NO = gd.brnd_no
where brmap.INTG_EC_BRND_ID in(
'P3242','P2883','P3656','P2479','P3779'
,'P305','P3252','P725','P2557','P1170'
,'P894','P1946','P19218','P3917','P367'
,'P1947','P4591','P398','P1573','P4367'
,'P17572','P1937','P2901','P3186','P10534'
,'P4856','P2045','P21385','P3124','P3764'
,'P82','P2541','P3916','P16789','P1143'
,'P4209','P496','P4868','P508','P2536'
,'P1697','P156','P1097','P3697','P4851','P1404')
and gd.SL_STAT_CD in ('1', '2')
and gd.DTGT_SITE_DVS_CD not in ('20', '21')
and gd.brnd_no != '0'
and gd.SL_END_DTTM >= now()
and gd.RGST_DTTM >='2019-09-01'
and gd.ENTSHP_NO not in (
'10074','10073','10072','10053','10071','10068','10067','10066','10065','10064','10063','10062','10058','10054','10056','10055','10052','10050','10048'
)
# 업체배송비 보정 (이용구)
배송비정책번호 확인
-- 11861로 변경 요청(파플번호임) -> 20998 로 변경
select MORD_DLV_CST_POL_NO, ET_DLEX_PLC.* from ET_DLEX_PLC
where SUP_ENTPRZ_NO = 45343
-- 21008 로 변경 요청
select MORD_DLV_CST_POL_NO, ET_DLEX_PLC.* from ET_DLEX_PLC
where SUP_ENTPRZ_NO = 45939
update gd_goods set
DLV_CST_POL_NO = 20998,
MODI_DTTM = now(),
MODR_ID = 'yglee6_DLV_CST_POL_NO'
-- select DLV_CST_POL_NO, DLV_PRCS_TYPE_CD, MODI_DTTM, MODR_ID from gd_goods
where SUP_ENTPRZ_NO = 45343
and DLV_PRCS_TYPE_CD ='20' # 업체배송
and goods_no in (
'1212853825'
,'1212853511'...
update gd_goods_smry set
DLV_CST_POL_NO = 20998,
MODI_DTTM = now(),
MODR_ID = 'yglee6_DLV_CST_POL_NO'
-- select DLV_CST_POL_NO, MODI_DTTM, MODR_ID from gd_goods_smry
where SUP_ENTPRZ_NO = 45343
and DLV_CST_POL_NO NOT IN (0,100,99) # 센터제외(DLV_PRCS_TYPE_CD 컬럼미존재)
and goods_no in (
'1212853825'
,'1212853511'...
# 보정처리 대상 상품데이터 추출
추출항목 : 엘롯데 상품번호, 모델번호, 백화점 대표단품번호, 자점, 품번
select
gg.goods_no as '엘롯데상품번호',
gg.mdl_no as '모델번호',
gseg.dptstr_goods_no AS '백화점대표단품번호',
gseg.dptstr_brnc_cd AS '자점',
gseg.dptstr_unt_cd AS '품번'
from gd_goods gg
inner JOIN gd_sup_entprz_goods gseg
ON (gseg.GOODS_NO = gg.GOODS_NO
AND gseg.SUP_ENTPRZ_NO = gg.SUP_ENTPRZ_NO)
where gg.SUP_ENTPRZ_NO = 45343
and gg.DLV_PRCS_TYPE_CD = '20' -- 업체배송
and gg.goods_no in (
'1212853825'
,'1212853511'...
update gd_goods
set DLV_GOODS_DVS_CD = '02' #설치상품무료
, PRST_PCK_USE_YN = 'N' #선물포장사용여부
, MODI_DTTM = now()
, MODR_ID = 'pnamsubjung'
where GOODS_NO in(
:GOODS_NO
)
#1/10 17:08 ~ 17:10분 사이 OPEN-API가 작동이 안된거 같다고 하는데,
실시간재고정보, 판매가능여부 이력 확인 좀 해주세요.
select * from os_goods_api_cnetn t1, os_goods_api_cnetn_mtxt t2
where t1.API_CNETN_SN = t2.API_CNETN_SN
and t1.RGST_DTTM between '2023-01-10 17:08:00' and '2023-01-10 17:10:59';
SELECT
*
-- a.ord_no ,count(*)
FROM os_api_cnetn a
inner join os_api_cnetn_mtxt b
on a. API_CNETN_SN = b.API_CNETN_SN
where true
and a.API_BTCH_SN in ( 11009 )
and a.SITE_NO IN ( 10014)
-- AND ORD_NO = '1210322885'
and a.RGST_DTTM >= '2023-01-10 17:05:00'
and a.RGST_DTTM <= '2023-01-10 17:10:00'
-- and a.`RGST_DTTM` <= '2022-11-29 10:31:00'
-- and 1 > (select count(DISTINCTROW c.`SUP_ENTPRZ_NO`) from gd_item_sup_entprz_stk_out c where a.`ORD_NO` = c.goods_no )
-- and ord_no in ('1000547680' ,'1333594693')
-- and b.`SEND_MTXT_CNTS` like '%2028856241%'
-- and b.`RECV_MTXT_CNTS` like '%2028856241%'
-- group by ord_no
-- and a.`SCS_FAILR_YN` = ''
order by b.`MODI_DTTM` asc
# 판매종료일자 변경처리
update gd_goods
set SL_END_DTTM = '2023-01-31 23:59:00',
MODI_DTTM = now(),
MODR_ID = 'shmoon7_sl_end_dttm'
where goods_no = '1212631731'
;
update gd_goods_smry
set SL_END_DTTM = '2023-01-31 23:59:00',
MODI_DTTM = now(),
MODR_ID = 'shmoon7_sl_end_dttm'
where goods_no = '1212631731'
;
update gd_sup_entprz_magn
set END_DTTM = '2023-01-31 23:59:00',
MODI_DTTM = now(),
MODR_ID = 'shmoon7_end_dttm'
where goods_no = '1212631731'
;
update gd_goods_prc
set END_DTTM = '2023-01-31 23:59:00',
MODI_DTTM = now(),
MODR_ID = 'shmoon7_end_dttm'
where goods_no = '1212631731'
;
#카테고리 변경은 날짜수정 안하는 것인지? 확인필요
#물류팀 요청으로 설치/주문제작 상품 데이터 추출 요청드립니다.
대상 상품
- 판매상태 : 판매중, 품절
- 전시사이트 : 엘롯데, 엘롯데+아이몰
- 배송상품구분 : 주문제작, 설치유료, 설치무료 상품
추출항목
- 항목명: 상품번호, 상품명, 브랜드, 표준카테고리, 표준카테고리 번호, 배송처리유형, 배송상품구분, 판매상태, 판매단가, 모델번호, 상위거래처명, 하위거래처명, 하위거래처번호, MD상품군, 담당MD명
select gd.GOODS_NO '상품번호'
, gd.GOODS_NM '상품명'
, gd.BRND_NO '브랜드번호'
, brnd.BRND_NM '브랜드명'
, base_scat.INTG_EC_SCAT_ID '표준카테고리 ID'
, CASE WHEN base_scat.DPTH_NO = '3'
THEN (SELECT concat(dp1.INTG_EC_SCAT_NM, '>', dp2.INTG_EC_SCAT_NM , '>', dp3.INTG_EC_SCAT_NM)
FROM GD_INTG_EC_SCAT dp3,GD_INTG_EC_SCAT dp2,GD_INTG_EC_SCAT dp1
WHERE dp3.INTG_EC_SCAT_ID = base_scat.INTG_EC_SCAT_ID
AND dp3.INTG_EC_RNKH_SCAT_ID = dp2.INTG_EC_SCAT_ID
AND dp2.INTG_EC_RNKH_SCAT_ID = dp1.INTG_EC_SCAT_ID
)
ELSE (SELECT concat(dp1.INTG_EC_SCAT_NM, '>', dp2.INTG_EC_SCAT_NM , '>', dp3.INTG_EC_SCAT_NM, '>', dp4.INTG_EC_SCAT_NM)
FROM GD_INTG_EC_SCAT dp4,GD_INTG_EC_SCAT dp3,GD_INTG_EC_SCAT dp2,GD_INTG_EC_SCAT dp1
WHERE dp4.INTG_EC_SCAT_ID = base_scat.INTG_EC_SCAT_ID
AND dp4.INTG_EC_RNKH_SCAT_ID = dp3.INTG_EC_SCAT_ID
AND dp3.INTG_EC_RNKH_SCAT_ID = dp2.INTG_EC_SCAT_ID
AND dp2.INTG_EC_RNKH_SCAT_ID = dp1.INTG_EC_SCAT_ID
)
END AS '표준카테고리_경로'
, CASE WHEN gd.DLV_PRCS_TYPE_CD = '10' THEN '센터배송'
WHEN gd.DLV_PRCS_TYPE_CD = '11' THEN '센터경유'
WHEN gd.DLV_PRCS_TYPE_CD = '20' THEN '업체배송' ELSE gd.DLV_PRCS_TYPE_CD
END '배송처리유형'
, CASE WHEN DLV_GOODS_DVS_CD = '01' THEN '일반상품'
WHEN DLV_GOODS_DVS_CD = '02' THEN '설치상품무료'
WHEN DLV_GOODS_DVS_CD = '03' THEN '주문제작'
WHEN DLV_GOODS_DVS_CD = '04' THEN '설치상품유료' ELSE DLV_GOODS_DVS_CD
END '배송상품구분'
, CASE
WHEN SL_STAT_CD = '1' THEN '정상'
WHEN SL_STAT_CD = '2' THEN '품절'
WHEN SL_STAT_CD = '3' THEN '판매종료' ELSE SL_STAT_CD END '판매상태'
, CASE WHEN NOW() BETWEEN SLPRC_STRT_DTTM AND SLPRC_END_DTTM THEN SLPRC ELSE ORIG_SLPRC END '판매가'
, gd.MDL_NO '모델번호'
, gd.ENTSHP_NO '상위거래처_번호'
, gd.SUP_ENTPRZ_NO '하위거래처명_번호'
, (select ENTSHP_NM from et_entshp_out ent where ent.ENTSHP_NO = gd.ENTSHP_NO) '상위거래처명'
, (select ENTPRZ_NM from et_sup_entprz_out sup where sup.SUP_ENTPRZ_NO = gd.SUP_ENTPRZ_NO) '하위거래처명'
, md_gsgr.MD_GSGR_NM 'MD상품군명'
, md_gsgr.MD_ID 'MD_ID'
from gd_goods gd
INNER JOIN gd_brnd brnd on gd.BRND_NO = brnd.BRND_NO
INNER JOIN gd_goods_intg_ec_scat scat ON gd.GOODS_NO = scat.GOODS_NO
INNER JOIN gd_intg_ec_scat base_scat ON replace(scat.INTG_EC_SCAT_ID, char(13), '') = base_scat.INTG_EC_SCAT_ID
INNER JOIN gd_md_gsgr md_gsgr ON md_gsgr.LEVEL = '2' AND md_gsgr.MD_GSGR_NO = gd.MD_GSGR_NO
where gd.SL_STAT_CD in ('1', '2') ### 판매상태 정상, 품절
and gd.DLV_GOODS_DVS_CD IN ('02', '03', '04') ### 배송상품구분 설치유무료, 주문제작
and gd.DTGT_SITE_DVS_CD in ('00','10','12', '22'); ### 전시대상사이트 엘롯데, 엘롯데 + 아이몰;
#아이몰상품번호 추출
select gd.GOODS_NO '상품번호'
, sup_goods.DPTSTR_GOODS_NO '백화점상품번호'
, (select SITE_GOODS_NO from os_imll_goods_cnetn_out imll where imll.GOODS_NO = gd.GOODS_NO and SITE_GOODS_NO is not null) '아이몰 상품번호'
from gd_goods gd
, gd_sup_entprz_goods sup_goods
where gd.GOODS_NO = sup_goods.GOODS_NO
and gd.BRND_NO = '9032656';
#MD상품군 보정
elltgdprd> update gd_goods
set MD_GSGR_NO = '1088'
, MODI_DTTM = now()
, MODR_ID = 'sijung4'
where goods_no in('1212339436','1210485193')
elltgdprd> update gd_goods_smry
set MD_GSGR_NO = '1088'
, MODI_DTTM = now()
, MODR_ID = 'sijung4'
where goods_no in('1212339436','1210485193')
#해당 쿼리로 1만건씩 보정하고 out테이블 보정 진행하겠습니다.
상품통합EC표준카테고리 본테이블 보정작업 진행하겠습니다.
update gd_goods_intg_ec_scat t1,
(select GOODS_NO from gd_goods_intg_ec_scat where INTG_EC_SCAT_ID like concat('%', char(13) ,'%') limit 0, 10000) t2
set t1.INTG_EC_SCAT_ID = replace(t1.INTG_EC_SCAT_ID, char(13), '')
, t1.MODR_ID = 'sijung4_0113'
where t1.INTG_EC_SCAT_ID like concat('%', char(13) ,'%')
and t1.GOODS_NO = t2.goods_no
# 문자인증?
PO계정 테스트
jeongim0001 / wjddla1!
select *
from st_user_athnt
where USER_ID ='jeongim0001'
and RGST_DTTM >= date_add(now() ,interval -1 Hour )
롯데온에 상품연동할때, 판매상태, 재고 api 같이 나갈텐데, 순서가 정해져 있나요?
1: PD_API_0003("재고API", "/v1/openapi/product/v1/item/stock/change"),
2: PD_API_0005("판매상태변경", "/v1/openapi/product/v1/product/status/change"),
3: PD_API_0028("단품판매상태변경", "/v1/openapi/product/v1/item/status/change"),
4: PD_API_0039("기획전형 판매상태변경", "/v1/openapi/product/v1/product/status/change"),
job 1개에 step으로 다음 step 실행하게 되어있습니다.
실시간은 순서가 다르네요
1: PD_API_0003("재고API", "/v1/openapi/product/v1/item/stock/change"),
2: PD_API_0028("단품판매상태변경", "/v1/openapi/product/v1/item/status/change"),
3: PD_API_0005("판매상태변경", "/v1/openapi/product/v1/product/status/change"),
PD_API_0028("단품판매상태변경", "/v1/openapi/product/v1/item/status/change"),
LE1204385537_1223573206
품절하고, 재고15로 연동했는데, 롯데온에서 판매상태를 판매중으로 변경해서 출고점선정오류가 있었던 상품이에요
PD_API_0028("단품판매상태변경", "/v1/openapi/product/v1/item/status/change"),
엘롯데는 단품의 판매상태가 없는데 이건 어떤거에요?
소스에 있던데요
/*+ [goods-api] GoodsEcDAO.getGoodsPrdItemStatList */
SELECT 'LE' AS TR_GRP_CD
, CONCAT('LE', GOODS.ENTSHP_NO) AS TR_NO
, CONCAT('SLE', GOODS.SUP_ENTPRZ_NO) AS LRTR_NO
, CONCAT('LE', GOODS.GOODS_NO) AS SPD_NO
, CONCAT('LE', GOODS.GOODS_NO, '_', STK.ITEM_NO) AS SITM_NO
, CASE WHEN SUM(STK.STK_QTY) > 0 THEN 'SALE'
ELSE 'SOUT'
END SL_STAT_CD
FROM GD_GOODS GOODS
INNER JOIN GD_ITEM ITEM
ON GOODS.GOODS_NO = ITEM.GOODS_NO
AND ITEM.USE_YN = 'Y'
INNER JOIN GD_ITEM_OPT OPT
ON GOODS.GOODS_NO = OPT.GOODS_NO
AND ITEM.ITEM_NO = OPT.ITEM_NO
AND OPT.OPT_SEQ = 1
INNER JOIN GD_ITEM_SUP_ENTPRZ_STK STK
ON GOODS.GOODS_NO = STK.GOODS_NO
AND ITEM.ITEM_NO = STK.ITEM_NO
WHERE GOODS.SL_STAT_CD != '3'
<if test="goodsNo != null and !goodsNo.isEmpty()">
AND GOODS.GOODS_NO = #{goodsNo}
</if>
<if test="itemNo != null and !itemNo.isEmpty()">
AND STK.ITEM_NO = #{itemNo}
</if>
GROUP BY GOODS.GOODS_NO
, GOODS.ENTSHP_NO
, GOODS.SUP_ENTPRZ_NO
, STK.ITEM_NO
ORDER BY STK.ITEM_NO
LIMIT #{firstIndexOfRows}, #{lastIndexOfRows}
상품등록 연동할때, 판매상태를 품절로 연동하고, 재고 있는 상태로 연동하는게 많이 있을까요?
있을거 같은데요
PRO에서 품절치면 판매상태만 재연동 하는거 같은데요
# 상품데이터 추출(이용구)
1. 추출 기간 : 상품등록일 기준 2020.1.1 ~ 현재 시점
2. 첨부파일 ★브랜드리스트_1.21_31_7% 카드 적용 대상’ 파일 중 “1월 후반부 7% 최종” 시트에 있는 롯데온 브랜드에 매핑된 엘롯데브랜드 상품
3. 첨부파일 ‘아울렛 거래처번호 리스트’의 거래처 상품은 추출대상에서 제외
4. 판매중+품절 상품 대상 / 엘롯데전용, 엘롯데+아이몰 공용 (아이몰 전용상품 제외)
5. 추출항목 : 상품번호, 롯데온 브랜드명, 롯데온 브랜드코드
select gd.GOODS_NO '상품번호', INTG_EC_BRND_ID '롯데온브랜드번호'
, (select BRND_RPRT_NM from GD_INTG_EC_BRND ecbrnd where ecbrnd.INTG_EC_BRND_ID = brmap.INTG_EC_BRND_ID) '롯데온브랜드명'
-- select count(1) --926401
from gd_intg_ec_ellt_brnd_mapng brmap
inner join gd_goods gd on brmap.BRND_NO = gd.brnd_no
where brmap.INTG_EC_BRND_ID in(
'P2860','P870','P4248','P2314','P849','P4849','P2052','P4533','P1907','P37027','P496','P2949','P393','P751','P4005'
,'P4410','P2687','p309','p6486','p3976','P306','P400','P2601','P4854','P21542','P2299','P2002','P21542','P2299','P2002'
,'P2883','P2419','P3734','P4633','P2557','P3252','P3242','P305','P2479','P725','P1170','P4533','P3778','P3519','P398'
,'P17572','P367','P3186','P10534','P1573','P21385','P4533','P3917','P3124','P2541','P3916','P82','P16789','P1143','P4209'
,'P3764','P496','P2344','P6372','P1097','P508','P1404','P2536','P4096','P2535','P3697','P4851'
)
and gd.SL_STAT_CD in ('1', '2')
and gd.DTGT_SITE_DVS_CD not in ('20', '21')
and gd.brnd_no != '0'
and gd.SL_END_DTTM >= now()
and gd.RGST_DTTM >='2020-01-01'
and gd.ENTSHP_NO not in (
10074,10073,10072,10053,10071,10068,10067,10066,10065,10064,10063,10062,10058,10054,10056,10055,10052,10050,10048
);
# 아이몰 연동 상태 os_imll_goods_cnetn_out 테이블 DATA_CNETN_STAT_CD = 'OK'
select t2.SL_STAT_CD, count(1)
from os_imll_goods_cnetn t1
, gd_goods_out t2
where t1.GOODS_NO = t2.GOODS_NO
and t2.SL_STAT_CD in ('1', '2')
and t1.DATA_CNETN_STAT_CD = 'OK'
and t2.SL_END_DTTM >= now()
group by t2.SL_STAT_CD;
# 파트너포탈 상품연동 이력조회
, CASE
WHEN IF_RSLT_MSG_CNTS LIKE '%### Error updating database. Cause: java.sql.SQLIntegrityConstraintViolationException: (conn:%) Column ''GITMS_ARTCL_VAL'' cannot be null%' THEN '품목항목코드 NULL'
WHEN IF_RSLT_MSG_CNTS LIKE '%### Error updating database. Cause: java.sql.SQLDataException: (conn:%) Data too long for column ''THREED_IMG_VAL'' at row%' THEN '3D이미지값 길이 초과'
WHEN IF_RSLT_MSG_CNTS LIKE '%신규 판매조건번호 생성 중 오류 발생=%' THEN '신규 판매조건번호 생성 중 오류 발생'
WHEN IF_RSLT_MSG_CNTS LIKE '사이트 품목 항목코드가 존재하지 않습니다.%' THEN '사이트 품목 항목코드가 존재하지 않습니다.'
WHEN IF_RSLT_MSG_CNTS LIKE ' 공통코드 변환 실패:%' THEN '공통코드 변환 실패:'
WHEN IF_RSLT_MSG_CNTS LIKE ' 상품정보 존재하지 않습니다.%' THEN '상품정보 존재하지 않습니다.'
WHEN IF_RSLT_MSG_CNTS LIKE '상품상세 파일 업로드 실패%' THEN '상품상세 파일 업로드 실패'
WHEN IF_RSLT_MSG_CNTS LIKE '거래처 상품조회 중 오류 발생[%]/엘롯데에 존재하지 않는 상품입니다.' THEN '거래처 상품조회 중 오류 발생 / 엘롯데에 존재하지 않는 상품입니다.'
WHEN IF_RSLT_MSG_CNTS LIKE '거래처 상품이 존재하지 않습니다.%' THEN '거래처 상품이 존재하지 않습니다.'
WHEN IF_RSLT_MSG_CNTS LIKE '[현재 판매가격(%)]와 [세일 원판매가격(%)]이 같아야 합니다.' THEN '현재 판매가격과 세일 원판매가격이 같아야 합니다.'
WHEN IF_RSLT_MSG_CNTS LIKE '[현재 판매가격(%)]와 [변경전 판매가격(%)]이 같아야 합니다.' THEN '현재 판매가격과 변경전 판매가격이 같아야 합니다.'
WHEN IF_RSLT_MSG_CNTS LIKE '[현재 마진율(%)]과 [세일 원마진율(%)]이 같아야 합니다.' THEN '현재 마진율과 세일 원마진율이 같아야 합니다.'
WHEN IF_RSLT_MSG_CNTS LIKE '[현재 마진율(%)]과 [변경전 마진율(%)]이 같아야 합니다.' THEN '현재 마진율과 변경전 마진율이 같아야 합니다.'
WHEN IF_RSLT_MSG_CNTS LIKE '세일 해제 후 전문 원가격[%] [%]' THEN '세일 해제 후 전문 원가격'
WHEN IF_RSLT_MSG_CNTS LIKE '단품정보 조회 중 오류 발생[%][%]' THEN '단품정보 조회 중 오류 발생'
WHEN IF_RSLT_MSG_CNTS LIKE '# 상품노출여부수정(PR_DS_25) 상품기본정보 없음 # 통판상품번호 =[%]' THEN '상품노출여부수정(PR_DS_25) 상품기본정보 없음'
WHEN IF_RSLT_MSG_CNTS LIKE '거래처 정보 조회 중 오류 발생[%][%]' THEN '거래처 정보 조회 중 오류 발생'
WHEN IF_RSLT_MSG_CNTS LIKE '엘롯데 상품코드가 존재하지 않습니다.[%]' THEN '엘롯데 상품코드가 존재하지 않습니다.'
WHEN IF_RSLT_MSG_CNTS LIKE '[상세수정 (PR_DS_07)] 상품이 존재하지 않습니다. 상품번호 :%' THEN '[상세수정 (PR_DS_07)] 상품이 존재하지 않습니다.'
WHEN IF_RSLT_MSG_CNTS LIKE '이미 등록된 단품정보 입니다. 중복된 단품 정보%' THEN '이미 등록된 단품정보 입니다.'
WHEN IF_RSLT_MSG_CNTS LIKE '세일 해제 후 전문 원가격% 'THEN '세일 해제 후 전문 원가격'
WHEN IF_RSLT_MSG_CNTS LIKE '상품번호 : % 판매조건번호를 찾을수 없습니다.' THEN '판매조건번호를 찾을수 없습니다.'
WHEN IF_RSLT_MSG_CNTS LIKE '[출고지시일]을 [주문예약가능시간](%) 이전으로 설정할 수 없습니다.' THEN '[출고지시일]을 [주문예약가능시간] 이전으로 설정할 수 없습니다.'
WHEN IF_RSLT_MSG_CNTS LIKE '%번째 옵션번호 % 존재하지 않습니다.' THEN '옵션번호 존재하지 않습니다.'
WHEN IF_RSLT_MSG_CNTS LIKE 'Invalid Url!! %' THEN '유효하지 않은 이미지 URL'
ELSE IF_RSLT_MSG_CNTS
END IF_RSLT_MSG_CNTS
# 마진코드 없는것
# 백화점 마진코드 누락된 데이터 존재하는데 마이그 테이블에도 해당 마진코드가 없는데요.
select * from gd_sup_entprz_goods a, gd_goods b
where `DPTSTR_MAGN_CD`=''
and use_yn = 'Y'
and a.goods_no = b.goods_no
and b.`SL_STAT_CD` <> '3'
and b.`SL_END_DTTM` > now()
# http://api.open.lotteeps.com/open-api/goods/ec/getRealTimeGoodsStockInfo
# goods-api/goods/inner/getRealTimeGoodsStockInfo
# 이후에 받아서 주문연동 테이블에 주문수량 만큼 차감하고 응답주네요
SELECT CONCAT('LE', GOODS.GOODS_NO) AS SPD_NO
, CONCAT('LE', GOODS.GOODS_NO, '_', ITEM.ITEM_NO) AS SITM_NO
, 'LE' AS TR_GRP_CD
, CASE WHEN GOODS.SL_STAT_CD = '1' AND SUM(STK.STK_QTY) >= #{odQty} THEN 'Y'
ELSE 'N'
END SL_PSB_YN
, SUM(STK.STK_QTY) AS STK_QTY
FROM GD_GOODS GOODS
INNER JOIN GD_ITEM ITEM
ON GOODS.GOODS_NO = ITEM.GOODS_NO
INNER JOIN GD_ITEM_SUP_ENTPRZ_STK STK
ON ITEM.ITEM_NO = STK.ITEM_NO
WHERE GOODS.GOODS_NO = SUBSTRING(#{spdNo}, 3)
GROUP BY ITEM.ITEM_NO
# 정상판매, 품절중에 마진정보가 없는 상품의 거래처를 찾아야 하는데요.. 확인 가능할까요?
정상판매건수 , 품절건수 확인해서 마진누락된건 판매종료처리 해야 할거 같습니다.
재고 데이터가 2억건인데, rdb에서는 쿼리 안돌아갑니다
찾으려면 아데나로 내려서 찾아야합니다
select distinct gd.goods_no
from gd_goods gd
inner join gd_item item ON gd.goods_no = item.goods_no
inner join gd_item_sup_entprz_stk stk on stk.ITEM_NO = item.ITEM_NO
where gd.SL_STAT_CD in ('1', '2')
and gd.SL_END_DTTM > now()
and not exists(select 1 from gd_sup_entprz_magn magn where magn.goods_no = stk.GOODS_NO and magn.SUP_ENTPRZ_NO = stk.SUP_ENTPRZ_NO and now() between magn.STRT_DTTM and magn.END_DTTM)
limit 10;
-- 바로배송 서비스 운영 종료
update gd_specl_dlv_goods sp
,(select gd.goods_no
from gd_goods gd
, gd_specl_dlv_goods spe
where gd.GOODS_NO = spe.goods_no
and right(spe.SPECL_DLV_TYPE_CD,2) in( '20', '21')
and gd.SL_STAT_CD != '3'
limit 20000
) temp
set sp.SPECL_DLV_TYPE_CD = concat(left(sp.SPECL_DLV_TYPE_CD,3),'10')
, MODR_ID = concat('sijung4_',sp.SPECL_DLV_TYPE_CD)
, MODI_DTTM = now()
where sp.GOODS_NO = temp.GOODS_NO
- 피팅예약
select gd.goods_no
from gd_goods gd
, gd_avn_mall_fitg_dgoods fit
where gd.MORD_GOODS_NO = fit.MORD_GOODS_NO
and fit.FITG_DISP_SETUP_CD = '4'
and gd.SL_STAT_CD = 1
order by gd.RGST_DTTM desc
limit 10
- 스토어픽
select gd.GOODS_NO
from gd_goods gd
,gd_sup_entprz_strpic str
where gd.GOODS_NO = str.GOODS_NO
and str.SHOP_PICUP_YN = 'Y'
and gd.SL_STAT_CD = '1'
and gd.DLV_TYPE_CD in('20','30')
order by gd.RGST_DTTM desc
limit 10
- 바로배송
select gd.goods_no
from gd_goods gd
, gd_specl_dlv_goods spe
where gd.GOODS_NO = spe.goods_no
and right(spe.SPECL_DLV_TYPE_CD,2) in( '20', '21')
and gd.SL_STAT_CD = 1
order by gd.RGST_DTTM desc
limit 10
- 일반택배
select gd.goods_no, DLV_MEAN_CD
from gd_goods gd
where gd.DLV_TYPE_CD = '10'
and gd.SL_STAT_CD = 1
order by gd.RGST_DTTM desc
limit 10
-- 재고넣는 이유
GD_GOODS -> SL_STAT_CD 상품 판매상태 데이터 보정시
GD_GOODS_SL_STAT_HST(상품판매상태이력) 데이터 같이 넣어야 합니다.
판매상태 변경 이력 데이터 있어야 롯데ON 판매상태 연동 집계에서 집계 후 연동 가능합니다
# 거래처사용자 미등록 거래처정보
select shp.ENTSHP_NO
, shp.ENTSHP_NM
, sup.SUP_ENTPRZ_NO
, sup.ENTPRZ_NM
, count(USER_ID) cnt
FROM et_entshp_out shp
INNER JOIN ET_SUP_ENTPRZ_out sup on sup.ENTSHP_NO = shp.ENTSHP_NO
LEFT OUTER JOIN ST_USER usr
on sup.SUP_ENTPRZ_NO = usr.SUP_ENTPRZ_NO
AND use_yn = 'Y' AND work_stat_cd = '01'
AND instr(usr.USER_ID, '@') = 0 AND ifnull(DEPT_NM,' ') NOT IN ('imall', 'ellt', 'BO운영')
WHERE 1=1
and ENTSHP_STAT_CD = '1'
and sup.ENTPRZ_STAT_CD = '1'
and FINSHP_DT is null
group by shp.ENTSHP_NO, shp.ENTSHP_NM, sup.SUP_ENTPRZ_NO, sup.ENTPRZ_NM
having count(USER_ID) = 0
1. 추출 기간 : 상품등록일 기준 2020.01.07 ~ 2023.1.07
2. 첨부파일 ‘★2월 SWITCH ON 카드 7% 대상브랜드_최종 (1)’ 파일 중 “추출요청” 시트에 있는 롯데온 브랜드에 매핑된 엘롯데브랜드 상품
3. 첨부파일 ‘아울렛 거래처번호 리스트’의 거래처 상품은 추출대상에서 제외
4. 판매중+품절 상품 대상 / 엘롯데전용, 엘롯데+아이몰 공용 (아이몰 전용상품 제외)
5. 추출항목 : 상품번호, 롯데온 브랜드명, 롯데온 브랜드코드
select gd.GOODS_NO '상품번호'
, (select BRND_RPRT_NM from GD_INTG_EC_BRND ecbrnd where ecbrnd.INTG_EC_BRND_ID = brmap.INTG_EC_BRND_ID) '롯데온 브랜드명'
, INTG_EC_BRND_ID '롯데온 브랜드코드'
from gd_intg_ec_ellt_brnd_mapng brmap
inner join gd_goods gd on brmap.BRND_NO = gd.brnd_no
where brmap.INTG_EC_BRND_ID in (
'P870','P2314','P960','P849','P4849','P2052','P4533','P496','P393','P751','P4005',
'P4410','P2687','P6486','P11553','P2109','P306','P400','P2601','P3972','P1654','P4924',
'P21542','P2299','P2002','P21542','P2299','P2002','P2883','P2419','P3734','P2557',
'P3252','P3242','P305','P2479','P725','P1170','P4533','P398','P17572','P367',
'P3186','P10534','P1573','P21385','P4533','P3917','P514','P4856','P2409','P17572','P41530',
'P17578','P3124','P3916','P82','P16789','P1143','P4209','P496','P6372','P2413','P3650',
'P1097','P508','P1404','P2536','P4096','P2535','P1105','P3697','P4851'
)
and gd.SL_STAT_CD in ('1', '2')
and gd.DTGT_SITE_DVS_CD not in ('20', '21')
and gd.brnd_no != '0'
and gd.SL_END_DTTM >= now()
and gd.RGST_DTTM BETWEEN date_format('20200107000000' ,'%Y%m%d%H%i%s') and date_format('20230107235959' ,'%Y%m%d%H%i%s')
and gd.ENTSHP_NO not in (
10074,10073,10072,10053,10071,10068,10067,10066,10065,
10064,10063,10062,10058,10054,10056,10055,10052,10050,10048
)
;
--단품공급업체재고
select * from gd_item_sup_entprz_stk where goods_no = '1213108469' and ITEM_NO = '1274651424';
--실시간 재고체크
select *
from os_api_cnetn t1, os_api_cnetn_mtxt t2
where t1.API_CNETN_SN = t2.API_CNETN_SN
and t1.RGST_DTTM >= '2023-02-07 17:00:00'
and t1.SITE_NO = '10014'
and t1.API_BTCH_SN in ('11009')
and SCS_FAILR_YN = 'N'
and ORD_NO = '1213110319'
order by API_BTCH_SN desc;
--상품옵션재고
select * from os_goods_regist_api_cnetn_tmp t1
where 1=1
and t1.RGST_DTTM >= '2023-02-07 17:00:00'
and t1.SITE_NO = '10014'
and api_btch_sn = '10014209'
and goods_no = '2168001069'
limit 1000;
- 판매시작일 보정할때, 2023년 2월 7일 -> 2023년 2월 13일
gd_goods
gd_goods_smry
gd_goods_prc
gd_sup_entprz_magn
위 테이블 4개만 보정하면 되나
- (김동윤)
조건 : 상위거래처 - 10014 (본점 영프라자), 10002(본점) 등록 상품
판촉여부 – 판촉(Y)
판매상태 – 정상판매 또는 품절
전시채널 – 엘롯데 또는 아이몰 (엘롯데+아이몰 등록한 공통샹품은 제외)
상품등록일 – 22년1월1일 ~ 23년1월31일
추출데이터 : 엘롯데상품코드/품번/상품명
select gd.goods_no '상품번호'
, gd.goods_nm '상품명'
, (select DPTSTR_UNT_CD from gd_sup_entprz_goods gdsup where gdsup.goods_no = gd.goods_no and gd.SUP_ENTPRZ_NO = gdsup.SUP_ENTPRZ_NO) '품번'
from gd_goods gd
where gd.ENTSHP_NO in ('10014', '10002')
and gd.PROM_YN = 'Y'
and gd.SL_STAT_CD in ('1', '2')
and gd.DTGT_SITE_DVS_CD != '00'
and gd.RGST_DTTM between '2022-01-01 00:00:00' and '2023-01-31 23:59:59';
# 조건 : 상위거래처 - 10014 (본점 영프라자), 10002(본점) 등록 상품
판촉여부 – 판촉(Y)
판매상태 – 정상판매 또는 품절
전시채널 – 엘롯데 또는 아이몰 (엘롯데+아이몰 등록한 공통샹품은 제외)
상품등록일 – 22년1월1일 ~ 23년1월31일
추출데이터 : 엘롯데상품코드/품번/상품명
브랜드명 추가
상품데이터에 표준카테고리번호, 표준카테고리명(전체경로) 추가 추출
select
a.goods_no '상품번호',
a.goods_nm '상품명',
(select dptstr_unt_cd from gd_sup_entprz_goods gdsup where gdsup.goods_no = a.goods_no and a.sup_entprz_no = gdsup.sup_entprz_no) '품번',
(select brnd_nm from gd_brnd gb where gb.brnd_no = a.brnd_no) '브랜드명',
b.intg_ec_scat_id '표준카테고리번호',
case
when c.dpth_no = 3
THEN
(select concat(k1.intg_ec_scat_nm, '>', k2.intg_ec_scat_nm , '>', k3.intg_ec_scat_nm)
from gd_intg_ec_scat k3, gd_intg_ec_scat k2, gd_intg_ec_scat k1
where k3.intg_ec_scat_id = c.intg_ec_scat_id
and k3.intg_ec_rnkh_scat_id = k2.intg_ec_scat_id
and k2.intg_ec_rnkh_scat_id = k1.intg_ec_scat_id
limit 1)
ELSE
(select concat(k1.intg_ec_scat_nm, '>', k2.intg_ec_scat_nm , '>', k3.intg_ec_scat_nm, '>', k4.intg_ec_scat_nm)
from gd_intg_ec_scat k4, gd_intg_ec_scat k3, gd_intg_ec_scat k2, gd_intg_ec_scat k1
where k4.intg_ec_scat_id = c.intg_ec_scat_id
and k4.intg_ec_rnkh_scat_id = k2.intg_ec_scat_id
and k3.intg_ec_rnkh_scat_id = k2.intg_ec_scat_id
and k2.intg_ec_rnkh_scat_id = k1.intg_ec_scat_id
limit 1)
END '표준카테고리'
from gd_goods a
inner join gd_goods_intg_ec_scat b on a.goods_no = b.goods_no
inner join gd_intg_ec_scat c on b.intg_ec_scat_id = c.intg_ec_scat_id
where a.entshp_no in ('10014', '10002')
and a.prom_yn = 'Y'
and a.sl_stat_cd in ('1', '2')
and a.dtgt_site_dvs_cd != '00'
and a.rgst_dttm between '2022-01-01 00:00:00' and '2023-01-31 23:59:59'
; -- 142369
-단품별 속성값 및 속성세부값 정보 데이터 보정처리 요청드립니다.(김동윤)
롯데온은 이미 보정처리 완료된 건으로 PRO쪽 보정 부탁드립니다.
상품코드 : 1209017435
속성유형 ID : 12440 색조메이크업 색상
1209017435_1248813969 131 칠리 모로코
속성값/ID : 114854 레드
세부값 : 131 칠리 모로코
update gd_intg_ec_ellt_item_attr_type
set INTG_EC_ATTR_VAL_ID = '114854'
, MODI_DTTM = now()
, MODR_ID = 'dykim62'
where item_no = '1248813969';
티켓 : https://pium.lotte.com/browse/ELOP-17190
1. 추출 기간 : 상품등록일 기준 2020.02.15 ~ 2020.02.15
2. 첨부파일 ★엠블럼 상품추출 코드 대상 브랜드’ 파일 중 “상품코드 추출 요청 대상 브랜드” 시트에 있는 롯데온 브랜드에 매핑된 엘롯데브랜드 상품
3. 첨부파일 ‘아울렛 거래처번호 리스트’의 거래처 상품은 추출대상에서 제외
4. 판매중+품절 상품 대상 / 엘롯데전용, 엘롯데+아이몰 공용 (아이몰 전용상품 제외)
5. 추출항목 : 상품번호, 롯데온 브랜드명, 롯데온 브랜드코드
SELECT gg.GOODS_NO AS "상품번호",
gieb.INTG_EC_BRND_ID AS "롯데온 브랜드명",
gieb.BRND_RPRT_NM AS "롯데온 브랜드코드"
FROM gd_goods gg, gd_intg_ec_ellt_brnd_mapng gieebm, gd_intg_ec_brnd gieb
WHERE gg.SL_STAT_CD IN ('1', '2')
AND gg.DTGT_SITE_DVS_CD IN ('00', '22', '21', '12')
AND gg.RGST_DTTM > '20200215'
AND gg.RGST_DTTM < '20230215'
AND gg.BRND_NO = gieebm.BRND_NO
AND gieebm.INTG_EC_BRND_ID = gieb.INTG_EC_BRND_ID
AND gieebm.INTG_EC_BRND_ID IN (
'P870', 'P2314', 'P2860', 'P1602', 'P2397', 'P849', 'P4849', 'P2052', 'P4533', 'P393', 'P751', 'P4005', 'P4410', 'P2687', 'P6486', 'P306', 'P400', 'P2601', 'P1654', 'P17235', 'P21542', 'P2299', 'P2002', 'P2056', 'P21538', 'P21545', 'P2419', 'P3734', 'P2557', 'P3252', 'P3242', 'P305', 'P2479', 'P725', 'P1170', 'P4533', 'P3656', 'P3267', 'P2883', 'P398', 'P17572', 'P367', 'P3186', 'P10534', 'P1573', 'P21385', 'P4533', 'P3917', 'P514', 'P4856', 'P2409', 'P17572', 'P41530', 'P3124', 'P82', 'P16789', 'P25794', 'P496', 'P6372', 'P3648', 'P4868', 'P2413', 'P2344', 'P3914', 'P1097', 'P508', 'P1404', 'P2536', 'P2535', 'P4096', 'P1105', 'P3697', 'P4851', 'P1907'
)
AND gg.ENTSHP_NO NOT IN (
10074, 10073, 10072, 10053, 10071, 10068, 10067, 10066, 10065, 10064, 10063, 10062, 10058, 10054, 10056, 10055, 10052, 10050, 10048
)
# 거래처 상품 배송비번호 변경 완료하였습니다.
update gd_goods
set DLV_CST_POL_NO = '25923'
, MODI_DTTM = now()
, MODR_ID = 'sijung4_0220'
where SUP_ENTPRZ_NO = '52386'
and DLV_PRCS_TYPE_CD = '20'
update gd_goods_smry
set DLV_CST_POL_NO = '25923'
, MODI_DTTM = now()
, MODR_ID = 'sijung4_0220'
where SUP_ENTPRZ_NO = '52386'
and GOODS_NO in(:goods_no)
update gd_goods
set DLV_CST_POL_NO = '25454'
, MODI_DTTM = now()
, MODR_ID = 'sijung4_0220'
where SUP_ENTPRZ_NO = '30273'
and DLV_CST_POL_NO in('10922','17629','21554')
and DLV_PRCS_TYPE_CD = '20'
update gd_goods_smry
set DLV_CST_POL_NO = '25454'
, MODI_DTTM = now()
, MODR_ID = 'sijung4_0220'
where goods_no in(
:goods
)
# 포탈에서 상품등록할때 전시사이트구분코드 00,12,20
select distinct DTGT_SITE_DVS_CD
from gd_aprv_tgt_goods;
# 아이몰 상품연동 성공/실패 쿼리 공유
select site_cd, site_cd_nm, cnt10014 as '아이몰'
from (
select
'000' as site_cd,
case btch_cnetn_stat_cd when '10' then '상품연동 성공합계' else '상품연동 실패합계' end as site_cd_nm,
ifnull(sum(case site_no when '10014' then btch_cnetn_cnt end),0) cnt10014
from (
select tm.agrg_tgt_dt as dt,
md.site_cd,
md.btch_cnetn_stat_cd as btch_cnetn_stat_cd,
sum(md.btch_cnetn_cnt) as btch_cnetn_cnt,
md.site_no as site_no
from os_ols_co_by_btch_mntrng_dtl md
inner join (select max(m2.btch_sn) as btch_sn,
m2.agrg_tgt_dt
from os_ols_co_by_btch_mntrng m2
where 1=1
and m2.agrg_tgt_dt in ('20230222', '20230223' , '20230224' ,'20230225' ,'20230226' ,'20230227' ,'20230228')
and m2.btch_xecut_stat_cd ='OK'
and m2.btch_dvs_cd = '10'
group by m2.agrg_tgt_dt) tm
where 1=1
and md.btch_sn = tm.btch_sn
and md.site_no = '10014'
and md.site_cd <> 'M14'
group by tm.agrg_tgt_dt, btch_cnetn_stat_cd, md.site_cd, md.site_no
)fd
group by site_cd_nm, btch_cnetn_stat_cd
union all
select
gp.site_cd,
gp.site_cd_nm,
ifnull(sum(case site_no when '10014' then btch_cnetn_cnt end),0) cnt10014
from ( select c.site_cd_nm,
c.site_cd
from os_ols_cd_mapng c
where site_no = '10000'
and site_group_cd = 'MNTRNG_TYPE_CD'
and site_cd <> 'M00'
and c.use_yn ='Y') gp
left outer join (select tm.agrg_tgt_dt as dt,
md.site_cd,
sum(md.btch_cnetn_cnt) as btch_cnetn_cnt,
md.site_no as site_no
from os_ols_co_by_btch_mntrng_dtl md
inner join (select max(m2.btch_sn) as btch_sn , m2.agrg_tgt_dt
from os_ols_co_by_btch_mntrng m2
where 1=1
and m2.agrg_tgt_dt in ('20230222', '20230223' , '20230224' ,'20230225' ,'20230226' ,'20230227' ,'20230228')
and m2.btch_xecut_stat_cd = 'OK'
and m2.btch_dvs_cd = '10'
group by m2.agrg_tgt_dt) tm
where md.btch_sn = tm.btch_sn
and md.btch_cnetn_stat_cd = '20'
-- and md.BTCH_CNETN_TYPE_CD = '10'
group by tm.agrg_tgt_dt, btch_cnetn_stat_cd, md.site_cd, md.site_no
) fd on gp.site_cd = fd.site_cd
group by gp.site_cd, gp.site_cd_nm
) mm
where mm.site_cd <> 'M14'
order by mm.site_cd
# 정성일
select *
FROM et_entshp shp
INNER JOIN ET_SUP_ENTPRZ sup on sup.ENTSHP_NO = shp.ENTSHP_NO
where ENTSHP_STAT_CD = '1'
and sup.ENTPRZ_STAT_CD = '1'
and FINSHP_DT is null
# 원산지 변경
update gd_goods
set PLORGN_NO = 'CN',
PLORGN_NM = '중국',
MODI_DTTM = now(),
MODR_ID = 'shmoon7_plorgn_no'
where goods_no = '1213268329';
update gd_goods_smry
set PLORGN_NM = '중국',
MODI_DTTM = now(),
MODR_ID = 'shmoon7_plorgn_no'
where goods_no = '1213268329';
# 재고연동
select * from os_intg_ec_goods_cnetn where goods_no = '1209720415' and CNETN_BIZNES_DVS_CD = 'PD_API_0003' limit 10;
select * from gd_item_sup_entprz_stk where goods_no = '1209720415' and ITEM_NO = '1252534638' order by MODI_DTTM desc;
select * from gd_stk_chng_hst where goods_no = '1209720415' and ITEM_NO = '1252534638' order by STK_CHNG_HST_NO desc;
재고이력에는 변경된게 없는데, 재고연동은 발생한거 같은데.
재고 상품단위로 연동해서, 연동 될듯한데요
gd_item_sup_entprz_stk
gd_stk_chng_hst
이 테이블에 재고 변경이력이 없는데, 롯데온에 CNETN_BIZNES_DVS_CD = 'PD_API_0003' 이걸로 연동될수가 있나?
# 배치에서 재고연동 쿼리 getGoodsStockItemStkList
SELECT CONCAT('LE', GOODS.GOODS_NO) AS SPD_NO
, CONCAT('LE', GOODS.GOODS_NO, '_', ITEM.ITEM_NO) AS SITM_NO
, 'LE' AS TR_GRP_CD
, CONCAT('LE', STK.ENTSHP_NO) AS TR_NO
, CONCAT('SLE', STK.SUP_ENTPRZ_NO) AS LRTR_NO
, IF(STK.STK_QTY <![CDATA[<]]> 0, 0, STK.STK_QTY) AS STK_QTY
, CASE WHEN STK.STK_QTY > 0 THEN 'SALE'
ELSE 'SOUT'
END AS SL_STAT_CD
, CASE WHEN STKS.STK_QTY > 0 THEN 'SALE'
ELSE 'SOUT'
END SITM_SL_STAT_CD
, CASE WHEN GOODS.SL_STAT_CD = '1' THEN 'SALE'
ELSE 'SOUT'
END SPD_SL_STAT_CD
FROM GD_GOODS GOODS
INNER JOIN GD_ITEM ITEM
ON GOODS.GOODS_NO = ITEM.GOODS_NO
AND ITEM.USE_YN = 'Y'
INNER JOIN GD_ITEM_OPT OPT
ON GOODS.GOODS_NO = OPT.GOODS_NO
AND ITEM.ITEM_NO = OPT.ITEM_NO
AND OPT.OPT_SEQ = 1
INNER JOIN GD_ITEM_SUP_ENTPRZ_STK STK
ON GOODS.GOODS_NO = STK.GOODS_NO
AND ITEM.ITEM_NO = STK.ITEM_NO
INNER JOIN (SELECT GOODS_NO, ITEM_NO, SUM(STK_QTY) STK_QTY FROM GD_ITEM_SUP_ENTPRZ_STK WHERE GOODS_NO = #{goodsNo} GROUP BY ITEM_NO) STKS
ON STK.GOODS_NO = STKS.GOODS_NO
AND STK.ITEM_NO = STKS.ITEM_NO
WHERE GOODS.SL_STAT_CD != '3'
AND GOODS.GOODS_NO = #{goodsNo}
<!-- <foreach collection="list" item="goods" open="(" close=")" separator=","> -->
<!-- #{goods} -->
<!-- </foreach> -->
<!-- <if test="succDttm != null and !succDttm.isEmpty()"> -->
<!-- AND STK.RGST_DTTM <![CDATA[<=]]> #{succDttm} -->
<!-- </if> -->
ORDER BY STK.STK_QTY DESC, ITEM.ITEM_NO, STK.ENTSHP_NO, STK.SUP_ENTPRZ_NO
<include refid="paging" />
{spdNo=LE1209720415, sitmNo=LE1209720415_1252534638, lrtrNo=SLE44160, resultCode=8888, resultMessage=거래처 정보가 존재하지 않습니다.[LE / LE10034 / SLE44160 / LE1209720415 / LE1209720415_1252534638]}
2023-02-27 18:20:28
연동실패했네요
2023-02-27 18:20:28 시간에 거래처정보가 없어서 재고연동 실패가된건가요?
2023-02-27 18:11:37 가격변경으로 판매조건 여기로 붙은거 같은데요
2023-02-27 18:21:25 -> 점포별관리
재고배치는 20분부터 시작해서 도니 2023-02-27 18:20:28 이때 나간거 같은데 점포별관리에서 점포추가 안되어서 실패했고요
ResponseInfo responseInfo = null;
EcGoodsConst.EC_API api = EcGoodsConst.EC_API.valueOf(goodsTgt.getCnetnBiznesDvsCd());
int firstIndexOfRows = 0;//페이징 시작
int lastIndexOfRows = 200;//페이징 종료
//String succDttm = gdCommTrxDAO.getPdApi0026SuccDttm(goodsTgt.getGoodsNo());
for(int i = 0; i < 1000; i++){
GoodsStockInfo goodsStockInfo = new GoodsStockInfo(); //재고 API 정보
HashMap<String, Object> param = new HashMap<String, Object>();
/*
List<String> goodsList = new ArrayList<>();
goodsList.add(goodsTgt.getGoodsNo());
param.put("succDttm", succDttm);
*/
param.put("goodsNo", goodsTgt.getGoodsNo());
param.put("firstIndexOfRows", firstIndexOfRows);
param.put("lastIndexOfRows", lastIndexOfRows);
List<GoodsStockSpdItmStkInfo> itmStkLst = goodsEcDAO.getGoodsStockItemStkList(param); //상품 + 단품 + 재고목록 + 판매상태(거래처, 단품, 상품)
재고 배치에서 보낼때 상품단위로 조회해서 단품공급업체 단위 200개씩 페이징해서 보내는거같은데요
나중에 마지막페이지에서 성공해서 상품단위 연동 성공시킨거 같은데요.
DATA_CNETN_DTTM CNETN_ERR_CD CNETN_ERR_MSG logfilePath
2023-02-27 18:20:30 0000 정상 처리되었습니다. https://s3.console.aws.amazon.com/s3/object/ellt.interface.data.lotte.net/open/goods/log/10002/2023/02/27/PD_API_0003/-2075064460.txt
2023-02-27 18:20:28 8888 거래처 정보가 존재하지 않습니다.[LE / LE10034 / SLE44160 / LE1209720415 / LE1209720415_1252534631] https://s3.console.aws.amazon.com/s3/object/ellt.interface.data.lotte.net/open/goods/log/10002/2023/02/27/PD_API_0003/-2075064626.txt
이건 2월이고 3월엔 연동이력이 없는게 맞나요?
DATA_CNETN_DTTM CNETN_ERR_CD CNETN_ERR_MSG logfilePath
2023-03-02 08:50:29 0000 정상 처리되었습니다. https://s3.console.aws.amazon.com/s3/object/ellt.interface.data.lotte.net/open/goods/log/10002/2023/03/02/PD_API_0003/-2061225060.txt
2023-03-02 08:50:28 0000 정상 처리되었습니다. https://s3.console.aws.amazon.com/s3/object/ellt.interface.data.lotte.net/open/goods/log/10002/2023/03/02/PD_API_0003/-2061225126.txt
2023-03-01 17:20:03에 실패했다는거 아닌가요?
3/1 , 3/7일 연동이력을 확인해달라고 하네요.
엘롯데 2023-02-27 18:11:37 가격분기 점추가
엘롯데 -> 롯데ON 재고 연동 실패 2023-02-27 18:20:28
엘롯데 -> 롯데ON 재고 연동 성공 2023-03-02 08:50:28
3.1에는 롯데ON에는 재고 없었겠네요
2023-03-02 08:50:28에 연동 성공했어서요
###################### 아이몰 업무연락 테스트 ######################
use elltomtst
select oo.EXTRNL_ORD_NO, oo.* from om_ord oo
where oo.ORD_NO = '202303070018027'
order by oo.RGST_DTTM desc
use elltettst
select * from elltettst.et_sup_entprz where SUP_ENTPRZ_NO = '31096';
select * from elltettst.et_sup_entprz_mapng where SUP_ENTPRZ_NO = '31096';
use elltostst
SELECT b.RECV_MTXT_CNTS , a.CNETN_MSG, a.RGST_DTTM
, b.* , a.*
FROM os_api_cnetn a
inner join os_api_cnetn_mtxt b
on a. API_CNETN_SN = b.API_CNETN_SN
where 1=1
and a.RGST_DTTM > '2023-01-01'
-- and a.API_BTCH_SN in ( 100140601,100140602,100140603,100140604,100140611,100140612,100140613 )
and a.API_BTCH_SN in ( 100140611, 100140604 )
and a.SITE_NO = 10014
order by a.API_CNETN_SN DESC
;
select * from OS_OLS_GOODS_QNA_MAPNG
where QNA_DVS_CD = 'SBQ'
order by QNA_MAPNG_SN desc
SELECT
a.site_goods_no, a.*
FROM os_imll_goods_cnetn a
WHERE a.goods_no = '1203901865'
# Share-batch에서 Q&A 적재시 주문번호 조회(param : 롯데온로그인ID, 롯데온상품번호, 문의등록일시)
select
a.ORD_NO
, a.EXTRNL_ORD_NO
from om_ord a
, om_intg_ec_ord_cnetn b
where a.ORD_NO = b.ORD_NO
and a.EXTRNL_ORD_NO = b.SITE_ORD_NO
and a.MBR_LOGIN_ID = 'jsungill@naver.com'
and b.INTG_EC_SLR_GOODS_NO = 'LE1204047167'
and a.ORD_ACCP_DTTM <= '20230314134151'
order by a.ORD_ACCP_DTTM desc
limit 1
# 업체배송비 보정 (이용구)
-- 10036 52386 -> 배송비 26054로 변경
select MORD_DLV_CST_POL_NO, ET_DLEX_PLC.* from ET_DLEX_PLC
where SUP_ENTPRZ_NO = 52386
update gd_goods set
DLV_CST_POL_NO = 26054,
MODI_DTTM = now(),
MODR_ID = 'yglee6_DLV_CST_POL_NO'
-- select goods_no, DLV_CST_POL_NO, DLV_PRCS_TYPE_CD, MODI_DTTM, MODR_ID from gd_goods
where SUP_ENTPRZ_NO = 52386
and DLV_PRCS_TYPE_CD ='20' -- 업체배송
update gd_goods_smry set
DLV_CST_POL_NO = 26054,
MODI_DTTM = now(),
MODR_ID = 'yglee6_DLV_CST_POL_NO'
-- select goods_no, DLV_CST_POL_NO, MODI_DTTM, MODR_ID from gd_goods_smry
where SUP_ENTPRZ_NO = 52386
-- and DLV_CST_POL_NO NOT IN (0,100,99) -- 센터제외(DLV_PRCS_TYPE_CD 컬럼미존재)
and goods_no in (
select GOODS_NO from gd_goods
where SUP_ENTPRZ_NO = 52386
and DLV_CST_POL_NO != 26054 -- 변경되지 않은 배송비
and DLV_PRCS_TYPE_CD ='20'
)
#상품QA 추출
- 23.3.1 ~ 현재까지 등록된 Q&A 상품 중 미답변 Q&A 상품의 상위거래처 건수
- 추출항목 : 상위거래처번호, 상위거래처명, 상품건수
select entshp_no '상위거래처번호'
,(select ENTSHP_NM from et_entshp_out x where t.entshp_no = x.ENTSHP_NO) '상위거래처명'
, goodsCnt '상품건수'
from (
select replace(SITE_SLR_ID,'LE','') entshp_no, count(GOODS_NO) goodsCnt
from os_ols_goods_qna_mapng a
where SITE_NO = '10002'
and QNA_INQ_DTTM >= '2023-03-01 00:00:00'
and QNA_DVS_CD = 'Q'
and not exists(select 1
from os_ols_goods_qna_mapng b
where a.ELLT_QNA_SN = b.ELLT_QNA_SN and b.SITE_NO = '10002' and b.QNA_DVS_CD = 'A')
group by 1
) T
#현재 MD에 매핑되어 있는 브랜드 매핑 데이터 이관처리 요청드립니다.
현재매핑된 MD -> 변경되는 MD
120900140 -> 122300155
1. 120900140 -> 122300155
update gd_brnd_md
set END_DTTM = now()
, MODI_DTTM = now()
, MODR_ID = 'kyunghee.yang'
WHERE md_id ='120900140'
insert gd_brnd_md
select BRND_NO, now(), '9999-12-31 23:59:59', '122300155', now(), 'kyunghee.yang' , now(), 'kyunghee.yang'
from gd_brnd_md a
where MD_ID = '120900140'
and BRND_NO not in(select brnd_no from gd_brnd_md where MD_ID = '122300155' )
1. 점별 월별 신규상품등록수 (최초등록/점추가 모두 포함)
-기간: 2022년1월~12월
2. 본점 하이마트 상품정보
-거래처: 21816 본점(가전_하이마트)
-조건: 판매중/품절 (판매종료 제외)
-추출데이터: 백화점 품번/백화점대표단품코드/판매가/배송지주소
select gd.GOODS_NO '엘롯데상품번호'
, sup.DPTSTR_UNT_CD '품번'
, sup.DPTSTR_GOODS_NO '대표단품번호'
, if(now() between SL_STRT_DTTM and SL_END_DTTM,
gd.SLPRC, gd.ORIG_SLPRC) '판매가'
, (select concat(ZIP_ADDR,' ',DTL_ADDR) from et_dlvrplc_out dlv where dlv.DLVPLC_NO = gd.ENTPRZ_DLV_RTPL_ADDR ) '배송지주소'
from gd_goods gd
, gd_sup_entprz_goods sup
where gd.GOODS_NO = sup.GOODS_NO
and gd.SUP_ENTPRZ_NO = sup.SUP_ENTPRZ_NO
and SL_STAT_CD in ('1')
and gd.SUP_ENTPRZ_NO = '21816'
PRO 메뉴 사용자 데이터 추출 요청드립니다.
https://pium.lotte.com/browse/ELOP-17483
요청사유 : 특정 메뉴 사용자 이력 확인 필요
완료 희망일 : 2023. 4. 7
대상채널 : 엘롯데 PRO
대상 메뉴 : 판촉/이벤트/포인트 > 상품할인/쿠폰관리 > 즉석쿠폰 일괄처리
기간 : 2023.04.01 ~ 2023.04.07
추출데이터 : 사용자 ID / 이름
# 즉석쿠폰 메뉴접속이력 : 2023.04.01 ~ 2023.04.07
select a.USER_ID, b.USER_NM
from st_screen_use_log a, st_user b
where 1 = 1
and a.USER_ID = b.USER_ID
and a.SYS_DVS_CD = '02'
and a.MENU_NO = '308'
and b.SYS_DVS_CD = '02'
and b.csco_id = 'ELLOTTE'
and a.USE_DTTM between str_to_date('20230401000000','%Y%m%d%H%i%s') and str_to_date('20230407235959','%Y%m%d%H%i%s')
group by a.USER_ID, b.USER_NM
1. 요청 사유 : 프리미엄 배송 운영 상품 현황 파악을 위함
- pro 데이터 이슈로 1개월 이상 시 타임아웃 발생
2. 완료 희망일 : 2023.04.11
3. 대상 채널 : 엘롯데PRO
4. 대상 사이트 : 전체
5. 기간 : 2020.06~현재
6. 추출 조건 : 상품번호, 상위거래처, 하위거래처, 판매상태, 담당MD, 판매가
- 배송유형 : 프리미엄배송
- 판매상태 : 정상판매+품절
select gd.goods_no, eeo.ENTSHP_NM, eseo.ENTPRZ_NM, gd.SLPRC
, case gd.SL_STAT_CD when 1 then '판매중' else '품절' end sale_stat_nm
,(SELECT suo.USER_NM FROM gd_md_gsgr gmg, st_user_out suo where gmg.MD_ID = suo.USER_ID and gmg.md_gsgr_no = gd.md_gsgr_no) AS mdnm
-- ,(SELECT md_id FROM gd_md_gsgr WHERE md_gsgr_no = gd.md_gsgr_no) AS mdId
-- , sup.ENTSHP_NO, sup.SUP_ENTPRZ_NO, gd.SL_STAT_CD, gd.SLPRC, gd.md_gsgr_no
from gd_goods gd
inner join gd_specl_dlv_goods spe
on (gd.GOODS_NO = spe.goods_no
and right(spe.SPECL_DLV_TYPE_CD,2) = '30') -- 프리미엄배송
inner join gd_sup_entprz_goods sup
on (gd.GOODS_NO = sup.GOODS_NO
and gd.SUP_ENTPRZ_NO = sup.SUP_ENTPRZ_NO)
inner join et_entshp_out eeo
on (sup.ENTSHP_NO = eeo.ENTSHP_NO)
inner join et_sup_entprz_out eseo
on (sup.SUP_ENTPRZ_NO = eseo.SUP_ENTPRZ_NO)
where gd.RGST_DTTM >= '2020-06-01'
and gd.SL_STAT_CD in (1,2)
order by gd.RGST_DTTM desc
# 해당시간에 주문 들어왔는데 이때 판매가능여부보면 거래처정보는 없네요..
select *
from os_goods_api_cnetn t1, os_goods_api_cnetn_mtxt t2
where t1.API_CNETN_SN = t2.API_CNETN_SN
-- and t1.API_BTCH_SN = '10598' -- 단품 리스트
and t1.API_BTCH_SN = '219' -- 단품 단건
-- and (t1.API_BTCH_SN = '10598' or t1.API_BTCH_SN = '219')
and t1.RGST_DTTM between '2023-04-07 14:45:00' and '2023-04-07 15:19:59'
and t2.send_mtxt_cnts like '%"sitmNo":"LE1212361664_1270194805"%'
;
select * From os_goods_api_cnetn_mtxt
where `API_CNETN_SN` = 5697511586
#상위MD상품군별로 판매중, 품절 데이터 뽑아야하는데 지금 바로 가능할까요?
아이몰전용상품은 제외입니다.
판매중, 품절은 건수입니다.
상위md상품군은 번호하고 명 같이 부탁드립니다.
select rnhk_md_gsgr.MD_GSGR_NO
, rnhk_md_gsgr.MD_GSGR_NM
, count(1)
from gd_goods gd
inner join gd_md_gsgr md_gsgr on gd.MD_GSGR_NO = md_gsgr.MD_GSGR_NO
inner join gd_md_gsgr rnhk_md_gsgr on md_gsgr.RNKH_MD_GSGR_NO = rnhk_md_gsgr.MD_GSGR_NO
where sl_stat_cd in ('1', '2')
and DTGT_SITE_DVS_CD not in ('20', '21')
group by rnhk_md_gsgr.MD_GSGR_NO, rnhk_md_gsgr.MD_GSGR_NM;
#판매중 품절 건수 나눠서 나와야 하나요?
select rnhk_md_gsgr.MD_GSGR_NO '상위 MD상품군 번호'
, rnhk_md_gsgr.MD_GSGR_NM '상위 MD상품군'
, sum(if(gd.SL_STAT_CD = '1', 1, 0)) '판매중_건수'
, sum(if(gd.SL_STAT_CD = '2', 1, 0)) '품절_건수'
from gd_goods gd
inner join gd_md_gsgr md_gsgr on gd.MD_GSGR_NO = md_gsgr.MD_GSGR_NO
inner join gd_md_gsgr rnhk_md_gsgr on md_gsgr.RNKH_MD_GSGR_NO = rnhk_md_gsgr.MD_GSGR_NO
where sl_stat_cd in ('1', '2')
and DTGT_SITE_DVS_CD not in ('20', '21')
group by rnhk_md_gsgr.MD_GSGR_NO, rnhk_md_gsgr.MD_GSGR_NM;
-- 40 GD_BATCH GOODS 10002 롯데온 상품연동
-- 41 GD_BATCH STOCK 10002 롯데온 실시간재고
-- 58 GD_CNETN_DATA STOCK 10002 롯데온 실시간재고체크
-- 106 OLS_CNETN_DATA STOCK 10014 아이몰 실시간재고
-- 107 OLS_CNETN_DATA GOODS 10014 아이몰 상품연동
;
-- insert into os_biznes_mntrng_tms_tgt
-- (`MNTRNG_TGT_PSN_SN`, `BIZNES_MNTRNG_TGT_SN`, `USE_YN`, `RGSTR_ID`, `RGST_DTTM`, `MODR_ID`, `MODI_DTTM`)
select
*
from os_biznes_mntrng_tms_tgt
where `BIZNES_MNTRNG_TGT_SN` in (select `BIZNES_MNTRNG_TGT_SN` from os_biznes_mntrng_tgt
where `BIZNES_DVS_CD` in ('GOODS','STOCK')
and site_no in ( 10014 ,10002)
)
and `BIZNES_MNTRNG_TGT_SN` in (40,41,58,106,107)
and use_yn = 'Y'
-- 롯데ON 실시간 재고 체크
select b.`MNTRNG_TGT_PSN_NM`,b.`USE_YN`,a.*
from os_biznes_mntrng_tms_tgt a
, os_mntrng_tms_tgt_psn b
where true
and a.`MNTRNG_TGT_PSN_SN` = b.`MNTRNG_TGT_PSN_SN`
and `BIZNES_MNTRNG_TGT_SN` in
(select `BIZNES_MNTRNG_TGT_SN` from os_biznes_mntrng_tgt a
where `BIZNES_DVS_CD` in ('GOODS','STOCK')
and site_no in ( 10014 ,10002)
-- and `BIZNES_MNTRNG_TGT_SN` = 58
)
and `BIZNES_MNTRNG_TGT_SN` in (40,41,58,106,107)
and a.use_yn = 'Y'
and b.`USE_YN` = 'Y'
즉석쿠폰 메뉴접속이력 : 2023.04.01 ~ 2023.04.07
select a.USER_ID, b.USER_NM
from st_screen_use_log a, st_user b
where 1 = 1
and a.USER_ID = b.USER_ID
and a.SYS_DVS_CD = '02'
and a.MENU_NO = '308'
and b.SYS_DVS_CD = '02'
and b.csco_id = 'ELLOTTE'
and a.USE_DTTM between str_to_date('20230401000000','%Y%m%d%H%i%s') and str_to_date('20230407235959','%Y%m%d%H%i%s')
group by a.USER_ID, b.USER_NM
# 거래처별 현 시점 등록되어 있는 MD 매핑 목록
SELECT DISTINCT b.ENTSHP_NO AS "상위거래처번호", b.ENTSHP_NM AS "상위거래처명", a.SUP_ENTPRZ_NO AS "하위거래처번호" , a.ENTPRZ_NM AS "하위거래처명", e.USER_NM AS "MD명"
FROM et_sup_entprz_out a, et_entshp_out b, et_sup_entprz_brnd_out c, gd_brnd_md d, st_user_out e
WHERE a.ENTSHP_NO = b.ENTSHP_NO
AND a.SUP_ENTPRZ_NO = c.SUP_ENTPRZ_NO
AND c.BRND_NO = d.BRND_NO
AND d.MD_ID = e.USER_ID
AND e.USE_YN = 'Y'
AND e.WORK_STAT_CD = '01'
AND a.SUP_ENTPRZ_NO IN (
20001,
20002,
# 2그룹에서 개발, 테스트서버 거래처 확인요청이 왔는데 혹시 조건에 맞는 거래처가 있는지 확인부탁드립니다.
상위거래처 창원점(10026)
개발, 테스트서버 양쪽에 모두 존재하는 하위거래처
센터경유 or 센터배송이 있는 하위거래처
판매중 or 품절상품이 존재하는 하위거래처
저 조건에 모두 만족하는 거래처 2~3개 정도가 필요하다고 합니다
select gseg.ENTSHP_NO, gseg.SUP_ENTPRZ_NO -- , gg.*
from gd_goods gg
inner join gd_sup_entprz_goods gseg
on gg.GOODS_NO = gseg.GOODS_NO
where gseg.ENTSHP_NO = '10026'
and gseg.RGST_DTTM >= '2022-01-01'
and gg.DLV_PRCS_TYPE_CD in ('10','11')
and gg.SL_STAT_CD in (1,2)
group by gseg.ENTSHP_NO, gseg.SUP_ENTPRZ_NO
# 센터배송상품으로 보정
34950 창원점(잡화_비비안) -> 34955 창원점(유아_밍크뮤)
수석님 개발, 테스트서버에 대항 거래처 상품 센터배송으로 보정부탁드립니다.
-- update 조인문
update gd_goods gg
inner join gd_sup_entprz_goods gseg
on gg.GOODS_NO = gseg.GOODS_NO
set gg.DLV_PRCS_TYPE_CD = 10, gg.MODI_DTTM = now(), gg.MODR_ID = 'p_yglee6'
where gseg.ENTSHP_NO = '10026'
and gseg.SUP_ENTPRZ_NO = '34955'
and gseg.RGST_DTTM >= '2021-01-01'
-- and gg.DLV_PRCS_TYPE_CD in ('20')
and gg.SL_STAT_CD in (1,2)
# 판매시작일 보정 요청
update gd_goods
set SL_STRT_DTTM = '2023-06-19 00:00:00'
, MODI_DTTM = now()
, MODR_ID = 'dykim62_sl_strt_dttm'
where goods_no in ( '1214418789','1214418667','1214418573','1214418454','1214418362','1214418284','1214418205','1214418127','1214417385','1214417291','1214417219','1214417150','1214416863','1214416771','1214416469','1214416371','1214416334','1214416248','1214416131','1214416087','1214416021','1214415967','1214415929'
);
update gd_goods_smry
set SL_STRT_DTTM = '2023-06-19 00:00:00'
, MODI_DTTM = now()
, MODR_ID = 'dykim62_sl_strt_dttm'
where goods_no in ( '1214418789','1214418667','1214418573','1214418454','1214418362','1214418284','1214418205','1214418127','1214417385','1214417291','1214417219','1214417150','1214416863','1214416771','1214416469','1214416371','1214416334','1214416248','1214416131','1214416087','1214416021','1214415967','1214415929'
);
- 롯데ON브랜드에 매핑된 엘롯데 브랜드가 2개이상인 경우 매핑된 브랜드 모두 추출부탁드립니다
select t1.BRND_RPRT_NM as 브랜드
, t1.INTG_EC_BRND_ID as 롯데백화점몰브랜드번호
, t1.BRND_NO as PRO브랜드번호
, (select count(1) from gd_goods where BRND_NO = t1.BRND_NO) as 전체상품
, (select count(1) from gd_goods where BRND_NO = t1.BRND_NO and SL_STAT_CD = '1') as 전상판매
, (select count(1) from gd_goods where BRND_NO = t1.BRND_NO and SL_STAT_CD = '2') as 품절
, (select count(1) from gd_goods where BRND_NO = t1.BRND_NO and SL_STAT_CD = '3') as 판매종료
from (
select brnd.BRND_RPRT_NM
, brnd.INTG_EC_BRND_ID
, bmp.BRND_NO
from gd_intg_ec_brnd brnd
, gd_intg_ec_ellt_brnd_mapng bmp
where brnd.INTG_EC_BRND_ID = bmp.INTG_EC_BRND_ID
and brnd.INTG_EC_BRND_ID in (
'P39097',
'P1022',
'P43741',
'P44278',
'P47627',
'P4034',
'P3175',
'P3343',
'P11892',
'P1133',
'P4755',
'P11977',
…
…
)
group by brnd.BRND_RPRT_NM
, brnd.INTG_EC_BRND_ID
, bmp.BRND_NO
) t1
#조형배 대리님 요청이 재고 보정 요청입니다.
gd_item_sup_entprz_stk 재고 변경 및 gd_stk_chng_hst 이력을 넣으면 됨
select * from st_user_out where user_nm = '박정호';
select * from gd_md_gsgr where MD_ID = '122300088' and level = '2';
select gd.goods_no '상품번호'
, gd.goods_nm '상품명'
, (select BRND_NM from gd_brnd brnd where brnd.BRND_NO = gd.BRND_NO) '브랜드명'
, gd.MD_GSGR_NO 'MD상품군번호'
, md_gsgr.MD_GSGR_NM 'MD상품군명'
, gd.ENTSHP_NO '상위거래처번호'
, gd.SUP_ENTPRZ_NO '하위거래처번호'
, (select ENTSHP_NM from et_entshp_out ent where ent.ENTSHP_NO = gd.ENTSHP_NO) '상위거래처명'
, (select ENTPRZ_NM from et_sup_entprz_out sup where sup.SUP_ENTPRZ_NO = gd.SUP_ENTPRZ_NO) '하위거래처명'
, CASE
WHEN SL_STAT_CD = '1' THEN '정상판매'
WHEN SL_STAT_CD = '2' THEN '품절'
WHEN SL_STAT_CD = '3' THEN '판매종료' ELSE SL_STAT_CD END '판매상태'
, gd.RGST_DTTM '상품등록일'
, gd.SL_STRT_DTTM '판매시작일시'
, gd.SL_END_DTTM '판매종료일시'
from gd_goods gd, gd_md_gsgr md_gsgr
where gd.MD_GSGR_NO = md_gsgr.MD_GSGR_NO
and gd.MD_GSGR_NO in (
'1000302','1572','1573','1574','1575','1576','1579','1580','1581','1582','6610'
)
and gd.sl_stat_cd = '1'
and gd.DTGT_SITE_DVS_CD not in ('20', '21');
select USER_ID, ENTSHP_NO, SUP_ENTPRZ_NO, SUP_ENTPRZ_NM
from st_user
where USER_ID = 'polhamcs';
-- 내재화 오픈하고 승인반려 상품 재승인 되었을때 데이터 들인데요.
4월 경에 데이터 모두 처리 하고 발생은 안하고 있습니다.
공통상품 중 점추가 되어서 재고 테이블에 데이터 없는 상품들은 모두 비정상 상품으로 남아 있습니다
select DISTINCTROW a.goods_no,c.`DTGT_SITE_DVS_CD` ,a.`RGST_DTTM`
From gd_aprv_tgt_item a
,gd_item b , gd_goods c
where a.use_yn ='N'
and b.`USE_YN` = 'Y'
and a.goods_no = c.goods_no
-- and c.`ITEM_RGST_YN` = 'Y'
and c.`SL_STAT_CD` <> '3'
and a.`GOODS_NO` = b.`GOODS_NO`
and a.`ITEM_NO` = b.`ITEM_NO`
-- and a.`MODI_DTTM` >= '2023-03-01'
-- and exists (select 'x' from gd_item_stk
order by a.`RGST_DTTM`
1. #### 일자별 상품등록 롯데ON 연동 성공 실패 건수 조회
select date_format(temp2.RGST_DTTM, '%Y-%m-%d') '일자'
, if(temp2.FINAL_PRCS_YN = 'Y', '성공', '실패') '성공 실패'
, count(1) '건수'
from (
SELECT GOODS_NO
, IF(PD_API_0001_DATA_CNETN_DTTM IS NOT NULL AND PD_API_0001_PRCS_YN = 'Y', 'Y', 'N') AS PD_API_0001_PRCS_YN
, IF(MIG_GOODS = 'Y', 'Y', IF(PD_API_0026_DATA_CNETN_DTTM IS NOT NULL, 'Y', 'N')) AS PD_API_0026_PRCS_YN
, IF(MIG_GOODS = 'Y', 'Y', IF(PD_API_0004_DATA_CNETN_DTTM IS NOT NULL, 'Y', 'N')) AS PD_API_0004_PRCS_YN
, IF(MIG_GOODS = 'Y', 'Y', IF(PD_API_0003_DATA_CNETN_DTTM IS NOT NULL, 'Y', 'N')) AS PD_API_0003_PRCS_YN
, PD_API_0001_MSG
, IF(MIG_GOODS = 'Y', 'Y'
, IF(PD_API_0001_DATA_CNETN_DTTM IS NOT NULL
AND PD_API_0026_DATA_CNETN_DTTM IS NOT NULL
AND PD_API_0004_DATA_CNETN_DTTM IS NOT NULL
AND PD_API_0003_DATA_CNETN_DTTM IS NOT NULL
, 'Y', 'N')
) AS FINAL_PRCS_YN
, IF(MIG_GOODS = 'Y', PD_API_0001_DATA_CNETN_DTTM
, GREATEST(PD_API_0001_DATA_CNETN_DTTM
, PD_API_0026_DATA_CNETN_DTTM
, PD_API_0004_DATA_CNETN_DTTM
, PD_API_0003_DATA_CNETN_DTTM
)
) AS FINAL_DATA_CNETN_DTTM
, RGST_DTTM
FROM (
SELECT /*+ [goods-api].GdGoodsOlsReadDAO.selectIntgEcGoodsCnetnList */
gd.GOODS_NO
, IF(cnetn.CNETN_ERR_CD = '0000' AND cnetn.RGSTR_ID = 'DBA', 'Y', 'N') MIG_GOODS
, cnetn.DATA_CNETN_DTTM AS PD_API_0001_DATA_CNETN_DTTM
, IF(cnetn.CNETN_ERR_CD = '0000', 'Y', 'N') AS PD_API_0001_PRCS_YN
, CASE
WHEN tgt.GOODS_NO is null AND cnetn.GOODS_NO is null THEN '연동대상 없음 / 연동내용 없음'
WHEN tgt.NT_MAPNG_TYPE_CD = '01' THEN '표준카테고리 미매핑'
WHEN tgt.NT_MAPNG_TYPE_CD IN ('02', '03') THEN 'I속성 미매핑'
WHEN tgt.NT_MAPNG_TYPE_CD = '04' AND cnetn.CNETN_ERR_CD is null THEN '상품등록 대기중'
WHEN cnetn.CNETN_ERR_CD = '0000' AND cnetn.RGSTR_ID = 'DBA' THEN '이관상품 연동완료'
WHEN cnetn.CNETN_ERR_CD = '0000' THEN cnetn.CNETN_ERR_MSG
ELSE cnetn.CNETN_ERR_MSG
END PD_API_0001_MSG
, (SELECT api26.DATA_CNETN_DTTM
FROM OS_INTG_EC_GOODS_CNETN api26
WHERE api26.GOODS_NO = cnetn.GOODS_NO
AND api26.CNETN_BIZNES_DVS_CD = 'PD_API_0026'
AND api26.CNETN_ERR_CD = '0000'
UNION
SELECT api26hst.DATA_CNETN_DTTM
FROM OS_INTG_EC_GOODS_CNETN_HST api26hst
WHERE api26hst.GOODS_NO = cnetn.GOODS_NO
AND api26hst.CNETN_BIZNES_DVS_CD = 'PD_API_0026'
AND api26hst.CNETN_ERR_CD = '0000'
ORDER BY 1 ASC
LIMIT 1
) PD_API_0026_DATA_CNETN_DTTM
, (SELECT api04.DATA_CNETN_DTTM
FROM OS_INTG_EC_GOODS_CNETN api04
WHERE api04.GOODS_NO = cnetn.GOODS_NO
AND api04.CNETN_BIZNES_DVS_CD = 'PD_API_0004'
AND api04.CNETN_ERR_CD = '0000'
UNION
SELECT api04hst.DATA_CNETN_DTTM
FROM OS_INTG_EC_GOODS_CNETN_HST api04hst
WHERE api04hst.GOODS_NO = cnetn.GOODS_NO
AND api04hst.CNETN_BIZNES_DVS_CD = 'PD_API_0004'
AND api04hst.CNETN_ERR_CD = '0000'
ORDER BY 1 ASC
LIMIT 1
) PD_API_0004_DATA_CNETN_DTTM
, (SELECT api03.DATA_CNETN_DTTM
FROM OS_INTG_EC_GOODS_CNETN api03
WHERE api03.GOODS_NO = cnetn.GOODS_NO
AND api03.CNETN_BIZNES_DVS_CD = 'PD_API_0003'
AND api03.CNETN_ERR_CD = '0000'
UNION
SELECT api03hst.DATA_CNETN_DTTM
FROM OS_INTG_EC_GOODS_CNETN_HST api03hst
WHERE api03hst.GOODS_NO = cnetn.GOODS_NO
AND api03hst.CNETN_BIZNES_DVS_CD = 'PD_API_0003'
AND api03hst.CNETN_ERR_CD = '0000'
ORDER BY 1 ASC
LIMIT 1
) PD_API_0003_DATA_CNETN_DTTM
, gd.RGST_DTTM
FROM GD_GOODS_OUT gd
LEFT OUTER JOIN OS_INTG_EC_GOODS_CNETN_TGT tgt
ON gd.GOODS_NO = tgt.GOODS_NO
AND tgt.CNETN_BIZNES_DVS_CD = 'PD_API_0001'
LEFT OUTER JOIN OS_INTG_EC_GOODS_CNETN cnetn
ON gd.GOODS_NO = cnetn.GOODS_NO
AND cnetn.CNETN_BIZNES_DVS_CD = 'PD_API_0001'
WHERE gd.GOODS_CPNT_CD != '04' ## 묶음상품 제외
AND ifnull(gd.GOODS_DVS_CD, '10') != '20' ## E쿠폰상품제외
AND gd.RGST_DTTM between '2023-09-20 00:00:00' and '2023-09-26 23:59:59' ## 상품등록일자 기준
AND ETL_LOAD_DTTM >= '2023-09-19 00:00:00' # 상품등록일자 인덱스가 없어서 인덱스 타기 위하여 조건추가
and cnetn.CNETN_ERR_MSG not in ('ISBN코드 입력이 필수인 카테고리 입니다.', '해외직구 카테고리를 사용하지않는 카테고리는 해외배송구분코드 해외배송(OVS)을 입력할 수 없습니다.')
) temp
) temp2
group by date_format(temp2.RGST_DTTM, '%Y-%m-%d'), temp2.FINAL_PRCS_YN;
2. 상품별 실패사유
select DISTINCT
GOODS_NO
, date_format(temp2.RGST_DTTM, '%Y-%m-%d') '일자'
, case when PD_API_0001_PRCS_YN = 'N' THEN PD_API_0001_MSG
when PD_API_0026_PRCS_YN = 'N' THEN '점포별관리 연동실패'
when PD_API_0004_PRCS_YN = 'N' THEN '가격 연동 실패'
when PD_API_0003_PRCS_YN = 'N' THEN '재고 연동실패'
else '확인필요'
END '실패케이스'
from (
SELECT GOODS_NO
, IF(PD_API_0001_DATA_CNETN_DTTM IS NOT NULL AND PD_API_0001_PRCS_YN = 'Y', 'Y', 'N') AS PD_API_0001_PRCS_YN
, IF(MIG_GOODS = 'Y', 'Y', IF(PD_API_0026_DATA_CNETN_DTTM IS NOT NULL, 'Y', 'N')) AS PD_API_0026_PRCS_YN
, IF(MIG_GOODS = 'Y', 'Y', IF(PD_API_0004_DATA_CNETN_DTTM IS NOT NULL, 'Y', 'N')) AS PD_API_0004_PRCS_YN
, IF(MIG_GOODS = 'Y', 'Y', IF(PD_API_0003_DATA_CNETN_DTTM IS NOT NULL, 'Y', 'N')) AS PD_API_0003_PRCS_YN
, PD_API_0001_MSG
, IF(MIG_GOODS = 'Y', 'Y'
, IF(PD_API_0001_DATA_CNETN_DTTM IS NOT NULL
AND PD_API_0026_DATA_CNETN_DTTM IS NOT NULL
AND PD_API_0004_DATA_CNETN_DTTM IS NOT NULL
AND PD_API_0003_DATA_CNETN_DTTM IS NOT NULL
, 'Y', 'N')
) AS FINAL_PRCS_YN
, IF(MIG_GOODS = 'Y', PD_API_0001_DATA_CNETN_DTTM
, GREATEST(PD_API_0001_DATA_CNETN_DTTM
, PD_API_0026_DATA_CNETN_DTTM
, PD_API_0004_DATA_CNETN_DTTM
, PD_API_0003_DATA_CNETN_DTTM
)
) AS FINAL_DATA_CNETN_DTTM
, RGST_DTTM
FROM (
SELECT /*+ [goods-api].GdGoodsOlsReadDAO.selectIntgEcGoodsCnetnList */
gd.GOODS_NO
, IF(cnetn.CNETN_ERR_CD = '0000' AND cnetn.RGSTR_ID = 'DBA', 'Y', 'N') MIG_GOODS
, cnetn.DATA_CNETN_DTTM AS PD_API_0001_DATA_CNETN_DTTM
, IF(cnetn.CNETN_ERR_CD = '0000', 'Y', 'N') AS PD_API_0001_PRCS_YN
, CASE
WHEN tgt.GOODS_NO is null AND cnetn.GOODS_NO is null THEN '연동대상 없음 / 연동내용 없음'
WHEN tgt.NT_MAPNG_TYPE_CD = '01' THEN '표준카테고리 미매핑'
WHEN tgt.NT_MAPNG_TYPE_CD IN ('02', '03') THEN 'I속성 미매핑'
WHEN tgt.NT_MAPNG_TYPE_CD = '04' AND cnetn.CNETN_ERR_CD is null THEN '상품등록 대기중'
WHEN cnetn.CNETN_ERR_CD = '0000' AND cnetn.RGSTR_ID = 'DBA' THEN '이관상품 연동완료'
WHEN cnetn.CNETN_ERR_CD = '0000' THEN cnetn.CNETN_ERR_MSG
ELSE cnetn.CNETN_ERR_MSG
END PD_API_0001_MSG
, (SELECT api26.DATA_CNETN_DTTM
FROM OS_INTG_EC_GOODS_CNETN api26
WHERE api26.GOODS_NO = cnetn.GOODS_NO
AND api26.CNETN_BIZNES_DVS_CD = 'PD_API_0026'
AND api26.CNETN_ERR_CD = '0000'
UNION
SELECT api26hst.DATA_CNETN_DTTM
FROM OS_INTG_EC_GOODS_CNETN_HST api26hst
WHERE api26hst.GOODS_NO = cnetn.GOODS_NO
AND api26hst.CNETN_BIZNES_DVS_CD = 'PD_API_0026'
AND api26hst.CNETN_ERR_CD = '0000'
ORDER BY 1 ASC
LIMIT 1
) PD_API_0026_DATA_CNETN_DTTM
, (SELECT api04.DATA_CNETN_DTTM
FROM OS_INTG_EC_GOODS_CNETN api04
WHERE api04.GOODS_NO = cnetn.GOODS_NO
AND api04.CNETN_BIZNES_DVS_CD = 'PD_API_0004'
AND api04.CNETN_ERR_CD = '0000'
UNION
SELECT api04hst.DATA_CNETN_DTTM
FROM OS_INTG_EC_GOODS_CNETN_HST api04hst
WHERE api04hst.GOODS_NO = cnetn.GOODS_NO
AND api04hst.CNETN_BIZNES_DVS_CD = 'PD_API_0004'
AND api04hst.CNETN_ERR_CD = '0000'
ORDER BY 1 ASC
LIMIT 1
) PD_API_0004_DATA_CNETN_DTTM
, (SELECT api03.DATA_CNETN_DTTM
FROM OS_INTG_EC_GOODS_CNETN api03
WHERE api03.GOODS_NO = cnetn.GOODS_NO
AND api03.CNETN_BIZNES_DVS_CD = 'PD_API_0003'
AND api03.CNETN_ERR_CD = '0000'
UNION
SELECT api03hst.DATA_CNETN_DTTM
FROM OS_INTG_EC_GOODS_CNETN_HST api03hst
WHERE api03hst.GOODS_NO = cnetn.GOODS_NO
AND api03hst.CNETN_BIZNES_DVS_CD = 'PD_API_0003'
AND api03hst.CNETN_ERR_CD = '0000'
ORDER BY 1 ASC
LIMIT 1
) PD_API_0003_DATA_CNETN_DTTM
, gd.RGST_DTTM
FROM GD_GOODS_OUT gd
LEFT OUTER JOIN OS_INTG_EC_GOODS_CNETN_TGT tgt
ON gd.GOODS_NO = tgt.GOODS_NO
AND tgt.CNETN_BIZNES_DVS_CD = 'PD_API_0001'
LEFT OUTER JOIN OS_INTG_EC_GOODS_CNETN cnetn
ON gd.GOODS_NO = cnetn.GOODS_NO
AND cnetn.CNETN_BIZNES_DVS_CD = 'PD_API_0001'
WHERE gd.GOODS_CPNT_CD != '04' ## 묶음상품 제외
AND ifnull(gd.GOODS_DVS_CD, '10') != '20' # E쿠폰상품제외
AND gd.RGST_DTTM between '2023-09-20 00:00:00' and '2023-09-26 23:59:59' ## 상품등록일자 기준
AND ETL_LOAD_DTTM >= '2023-09-19 00:00:00' # 상품등록일자 인덱스가 없어서 인덱스 타기 위하여 조건추가
and cnetn.CNETN_ERR_MSG not in ('ISBN코드 입력이 필수인 카테고리 입니다.', '해외직구 카테고리를 사용하지않는 카테고리는 해외배송구분코드 해외배송(OVS)을 입력할 수 없습니다.')
) temp
) temp2
where temp2.FINAL_PRCS_YN = 'N';
3. 해외직구 카테고리 실패건
select DISTINCT
GOODS_NO
, date_format(temp2.RGST_DTTM, '%Y-%m-%d') '일자'
, case when PD_API_0001_PRCS_YN = 'N' THEN PD_API_0001_MSG
when PD_API_0026_PRCS_YN = 'N' THEN '점포별관리 연동실패'
when PD_API_0004_PRCS_YN = 'N' THEN '가격 연동 실패'
when PD_API_0003_PRCS_YN = 'N' THEN '재고 연동실패'
else '확인필요'
END '실패케이스'
from (
SELECT GOODS_NO
, IF(PD_API_0001_DATA_CNETN_DTTM IS NOT NULL AND PD_API_0001_PRCS_YN = 'Y', 'Y', 'N') AS PD_API_0001_PRCS_YN
, IF(MIG_GOODS = 'Y', 'Y', IF(PD_API_0026_DATA_CNETN_DTTM IS NOT NULL, 'Y', 'N')) AS PD_API_0026_PRCS_YN
, IF(MIG_GOODS = 'Y', 'Y', IF(PD_API_0004_DATA_CNETN_DTTM IS NOT NULL, 'Y', 'N')) AS PD_API_0004_PRCS_YN
, IF(MIG_GOODS = 'Y', 'Y', IF(PD_API_0003_DATA_CNETN_DTTM IS NOT NULL, 'Y', 'N')) AS PD_API_0003_PRCS_YN
, PD_API_0001_MSG
, IF(MIG_GOODS = 'Y', 'Y'
, IF(PD_API_0001_DATA_CNETN_DTTM IS NOT NULL
AND PD_API_0026_DATA_CNETN_DTTM IS NOT NULL
AND PD_API_0004_DATA_CNETN_DTTM IS NOT NULL
AND PD_API_0003_DATA_CNETN_DTTM IS NOT NULL
, 'Y', 'N')
) AS FINAL_PRCS_YN
, IF(MIG_GOODS = 'Y', PD_API_0001_DATA_CNETN_DTTM
, GREATEST(PD_API_0001_DATA_CNETN_DTTM
, PD_API_0026_DATA_CNETN_DTTM
, PD_API_0004_DATA_CNETN_DTTM
, PD_API_0003_DATA_CNETN_DTTM
)
) AS FINAL_DATA_CNETN_DTTM
, RGST_DTTM
FROM (
SELECT /*+ [goods-api].GdGoodsOlsReadDAO.selectIntgEcGoodsCnetnList */
gd.GOODS_NO
, IF(cnetn.CNETN_ERR_CD = '0000' AND cnetn.RGSTR_ID = 'DBA', 'Y', 'N') MIG_GOODS
, cnetn.DATA_CNETN_DTTM AS PD_API_0001_DATA_CNETN_DTTM
, IF(cnetn.CNETN_ERR_CD = '0000', 'Y', 'N') AS PD_API_0001_PRCS_YN
, CASE
WHEN tgt.GOODS_NO is null AND cnetn.GOODS_NO is null THEN '연동대상 없음 / 연동내용 없음'
WHEN tgt.NT_MAPNG_TYPE_CD = '01' THEN '표준카테고리 미매핑'
WHEN tgt.NT_MAPNG_TYPE_CD IN ('02', '03') THEN 'I속성 미매핑'
WHEN tgt.NT_MAPNG_TYPE_CD = '04' AND cnetn.CNETN_ERR_CD is null THEN '상품등록 대기중'
WHEN cnetn.CNETN_ERR_CD = '0000' AND cnetn.RGSTR_ID = 'DBA' THEN '이관상품 연동완료'
WHEN cnetn.CNETN_ERR_CD = '0000' THEN cnetn.CNETN_ERR_MSG
ELSE cnetn.CNETN_ERR_MSG
END PD_API_0001_MSG
, (SELECT api26.DATA_CNETN_DTTM
FROM OS_INTG_EC_GOODS_CNETN api26
WHERE api26.GOODS_NO = cnetn.GOODS_NO
AND api26.CNETN_BIZNES_DVS_CD = 'PD_API_0026'
AND api26.CNETN_ERR_CD = '0000'
UNION
SELECT api26hst.DATA_CNETN_DTTM
FROM OS_INTG_EC_GOODS_CNETN_HST api26hst
WHERE api26hst.GOODS_NO = cnetn.GOODS_NO
AND api26hst.CNETN_BIZNES_DVS_CD = 'PD_API_0026'
AND api26hst.CNETN_ERR_CD = '0000'
ORDER BY 1 ASC
LIMIT 1
) PD_API_0026_DATA_CNETN_DTTM
, (SELECT api04.DATA_CNETN_DTTM
FROM OS_INTG_EC_GOODS_CNETN api04
WHERE api04.GOODS_NO = cnetn.GOODS_NO
AND api04.CNETN_BIZNES_DVS_CD = 'PD_API_0004'
AND api04.CNETN_ERR_CD = '0000'
UNION
SELECT api04hst.DATA_CNETN_DTTM
FROM OS_INTG_EC_GOODS_CNETN_HST api04hst
WHERE api04hst.GOODS_NO = cnetn.GOODS_NO
AND api04hst.CNETN_BIZNES_DVS_CD = 'PD_API_0004'
AND api04hst.CNETN_ERR_CD = '0000'
ORDER BY 1 ASC
LIMIT 1
) PD_API_0004_DATA_CNETN_DTTM
, (SELECT api03.DATA_CNETN_DTTM
FROM OS_INTG_EC_GOODS_CNETN api03
WHERE api03.GOODS_NO = cnetn.GOODS_NO
AND api03.CNETN_BIZNES_DVS_CD = 'PD_API_0003'
AND api03.CNETN_ERR_CD = '0000'
UNION
SELECT api03hst.DATA_CNETN_DTTM
FROM OS_INTG_EC_GOODS_CNETN_HST api03hst
WHERE api03hst.GOODS_NO = cnetn.GOODS_NO
AND api03hst.CNETN_BIZNES_DVS_CD = 'PD_API_0003'
AND api03hst.CNETN_ERR_CD = '0000'
ORDER BY 1 ASC
LIMIT 1
) PD_API_0003_DATA_CNETN_DTTM
, gd.RGST_DTTM
FROM GD_GOODS_OUT gd
LEFT OUTER JOIN OS_INTG_EC_GOODS_CNETN_TGT tgt
ON gd.GOODS_NO = tgt.GOODS_NO
AND tgt.CNETN_BIZNES_DVS_CD = 'PD_API_0001'
LEFT OUTER JOIN OS_INTG_EC_GOODS_CNETN cnetn
ON gd.GOODS_NO = cnetn.GOODS_NO
AND cnetn.CNETN_BIZNES_DVS_CD = 'PD_API_0001'
WHERE gd.GOODS_CPNT_CD != '04' ## 묶음상품 제외
AND ifnull(gd.GOODS_DVS_CD, '10') != '20' # E쿠폰상품제외
AND gd.RGST_DTTM between '2023-09-20 00:00:00' and '2023-09-26 23:59:59' ## 상품등록일자 기준
AND ETL_LOAD_DTTM >= '2023-09-19 00:00:00' # 상품등록일자 인덱스가 없어서 인덱스 타기 위하여 조건추가
# and cnetn.CNETN_ERR_MSG not in ('ISBN코드 입력이 필수인 카테고리 입니다.', '해외직구 카테고리를 사용하지않는 카테고리는 해외배송구분코드 해외배송(OVS)을 입력할 수 없습니다.')
and cnetn.CNETN_ERR_MSG in ('해외직구 카테고리를 사용하지않는 카테고리는 해외배송구분코드 해외배송(OVS)을 입력할 수 없습니다.')
) temp
) temp2
where temp2.FINAL_PRCS_YN = 'N';
판매자상품명에 저장 불가한 단어가 포함되어있습니다.
상품등록 146
상품수정 493
판매자단품명에 저장 불가한 단어가 포함되어있습니다.
옵션값을 다시 확인해 주세요.
옵션값에도 금칙어 걸려있네요 건수에 포함입니다.
select * from os_intg_ec_goods_cnetn_hst where GOODS_NO = '1215275635' and CNETN_BIZNES_DVS_CD = 'PD_API_0001';
-- update 조인문
-- PRO 선물포장사용 여부 보정
select gg.PRST_PCK_USE_YN, gg.MODI_DTTM, gg.MODR_ID from gd_goods gg, gd_sup_entprz_goods gseg where gg.GOODS_NO = gseg.GOODS_NO and gseg.DPTSTR_UNT_CD = '010180' and gseg.DPTSTR_BRNC_CD = '0001' and gseg.ENTSHP_NO = '10002' and gseg.SUP_ENTPRZ_NO = '20058'
update gd_goods gg
inner join gd_sup_entprz_goods gseg
on gg.GOODS_NO = gseg.GOODS_NO
set gg.PRST_PCK_USE_YN = 'Y', gg.MODI_DTTM = now(), gg.MODR_ID = 'pyglee6_PRST_PCK_USE_YN'
where gseg.DPTSTR_UNT_CD = '010180' and gseg.DPTSTR_BRNC_CD = '0001' and gseg.ENTSHP_NO = '10002' and gseg.SUP_ENTPRZ_NO = '20058'
-- 정영훈수석 쿼리
SELECT @pv,
a.*
FROM gd_brnd A force index(primary),
(SELECT @pv := 31129) D
WHERE FIND_IN_SET(A.rnkh_brnd_no, @pv) > 0
AND @pv := CONCAT(@pv, ',', A.brnd_no);
SELECT @pv,
scat.INTG_EC_SCAT_ID
FROM gd_intg_ec_scat scat,
(SELECT @pv := 'BC03000000') D
WHERE FIND_IN_SET(scat.INTG_EC_RNKH_SCAT_ID, @pv) > 0
AND @pv := CONCAT(@pv, ',', scat.INTG_EC_SCAT_ID)