暂无
任务描述
需求描述
需求地址：https://alidocs.dingtalk.com/i/nodes/Gl6Pm2Db8D3kB579t7qDZ0rGJxLq0Ee4?utm_scene=team_space
文档链接：https://alidocs.dingtalk.com/i/nodes/YndMj49yWjPBKdL0crdnwnlbJ3pmz5aA
参考：标签匹配智能策略（定稿） sheet 字段维度，做成可配置的，支持业务自行调整值来更改标签定义
给每个产品打上标签，确保全部的产品都有标签
具体的字段及取值逻辑，可对接智能团队杨宁海或陈楚如，目前线下已经在导表跟进
验收标准
158Create
库存库龄查询SQL
库存、库龄查询SQL
SELECT
1 AS id,
t1.asin AS asin,
t1.country_code AS countryCode,
sum( tafvi.fulfillable_quantity ) AS fulfillableQuantity,
sum( tafvi.pending_transshipment_quantity ) AS pendingTransshipmentQuantity,
sum( tafvi.fc_processing_quantity ) AS fcProcessingQuantity,
sum( tafvi.inventory_age_0_to_30_days ) AS inventoryAge0To30Days,
sum( tafvi.inventory_age_31_to_60_days ) AS inventoryAge31To60Days,
sum( tafvi.inventory_age_61_to_90_days ) AS inventoryAge61To90Days,
sum( tafvi.inventory_age_91_to_180_days ) AS inventoryAge91To180Days,
sum( tafvi.inventory_age_181_to_270_days ) AS inventoryAge181To270Days,
sum( tafvi.inventory_age_271_to_365_days ) AS inventoryAge271To365Days,
sum( tafvi.inventory_age_365_plus_days ) AS inventoryAge365PlusDays
FROM
(
SELECT
tafvic.asin,
tafvic.country_code,
tafvic.virtual_id
FROM
t_amz_fba_virtual_inventory_country tafvic
WHERE
tafvic.customer_id = 1
AND ( tafvic.asin, tafvic.country_code ) IN ( ( 'B0BG52SJ5N', 'US' ), ( 'B08JCJ1KDN', 'US' ) )
AND EXISTS (
SELECT
1
FROM
t_amz_fba_virtual_inventory_country t2
WHERE
tafvic.id = t2.id
AND ( t2.msku, t2.source_id ) IN ( ( 'WQ913NG2458S2YN7YZ58', 5247 ) )
)
AND tafvic.is_deleted = 0
) t1
LEFT JOIN t_amz_fba_virtual_inventory tafvi ON t1.virtual_id = tafvi.id
WHERE
tafvi.warehouse_type IN ( 3, 4 )
AND tafvi.is_deleted = 0
GROUP BY
t1.asin,
t1.country_code UNION ALL
SELECT
0 AS id,
t1.asin AS asin,
t1.country_code AS countryCode,
sum( tafvi.fulfillable_quantity ) AS fulfillableQuantity,
sum( tafvi.pending_transshipment_quantity ) AS pendingTransshipmentQuantity,
sum( tafvi.fc_processing_quantity ) AS fcProcessingQuantity,
sum( tafvi.inventory_age_0_to_30_days ) AS inventoryAge0To30Days,
sum( tafvi.inventory_age_31_to_60_days ) AS inventoryAge31To60Days,
sum( tafvi.inventory_age_61_to_90_days ) AS inventoryAge61To90Days,
sum( tafvi.inventory_age_91_to_180_days ) AS inventoryAge91To180Days,
sum( tafvi.inventory_age_181_to_270_days ) AS inventoryAge181To270Days,
sum( tafvi.inventory_age_271_to_365_days ) AS inventoryAge271To365Days,
sum( tafvi.inventory_age_365_plus_days ) AS inventoryAge365PlusDays
FROM
(
SELECT
tafvic.asin,
tafvic.country_code,
tafvic.virtual_id
FROM
t_amz_fba_virtual_inventory_country tafvic
WHERE
tafvic.customer_id = 1
AND ( tafvic.asin, tafvic.country_code ) IN ( ( 'B0BG52SJ5N', 'US' ), ( 'B08JCJ1KDN', 'US' ) )
AND EXISTS (
SELECT
1
FROM
t_amz_fba_virtual_inventory_country t2
WHERE
tafvic.id = t2.id
AND ( t2.msku, t2.source_id ) IN (
( 'CA-DKQO-QK7C', 16332 ),
( 'R8-OWE0-R1QV', 19218 ),
( 'ZK-CECZ-FFTM', 19218 ),
( 'water-flosser-22', 16549 ),
( 'TK-ABPL-5LW0', 16549 ),
( 'IF-3XCS-VA5Z', 16549 ),
( 'BS-NKBW-WLJ8', 16549 ),
( '68-4E0V-ERG2', 11977 ),
( 'EX-PSWF-LXK3', 11977 ),
( '513BB-WATERFLOSSER-BB', 11977 ),
( '1I-YVWF-4B61', 11977 ),
( 'Z4-DBZX-BWL2', 12503 ),
( '537-US-B0BG52SJ5N', 15729 ),
( 'C2-GRFR-FYOV', 15729 ),
( '761-AU-BLACK', 15729 ),
( 'UB-6I49-3DB4', 16549 ),
( 'TK-TK-B', 19218 ),
( 'RQ-QAEF-S87R', 16332 ),
( 'X0-6KZT-CF7E', 16332 ),
( '2R-4PRY-MD97', 15729 ),
( 'water-flosser-1', 15729 ),
( 'OB-OJ4P-VIXC', 15729 ),
( 'B0BG52SJ5N', 16549 ),
( 'C6-JCJW-NONF', 16332 ),
( '40-YC1J-OCVV', 16332 ),
( 'EH10EGAR4587BZ70E', 5276 ),
( 'amzn.gr.WQ913NG2458S2YN7YZ58-XJeYXppX-LN', 5247 )
)
)
AND tafvic.is_deleted = 0
) t1
LEFT JOIN t_amz_fba_virtual_inventory tafvi ON t1.virtual_id = tafvi.id
WHERE
tafvi.warehouse_type IN ( 3, 4 )
AND tafvi.is_deleted = 0
GROUP BY
t1.asin,
t1.country_code