17,382
社区成员




SELECT ERP_PART_NAME,ERP_CAR_MODEL,AUTO_BRAND_CODE_ALL,OEM_PART_CODE_ALL ,
ERP_ASSIST_NAME,APSIS_PART_UNIT_NAME,APSIS_PART_CODE,DISTRIBUTION_CHANNEL_NAME,PART_BRAND_NAME,
(CASE WHEN DISTRIBUTION_CHANNEL_CODE = '002' THEN
(SELECT PRICE FROM (
SELECT MATERIAL_ID,PRICE,SPPE.EFFECTIVE_DATE,SALE_ORG_CODE,CODE
FROM SALES_POLICY_PRICE_ENTRY SPPE,SALES_POLICY_PRICE SPP
WHERE SPPE.PARENT_ID = SPP.ID AND SPPE.BLOCKED_STATUS='0'
AND SPPE.EFFECTIVE_DATE<=SYSDATE AND SPPE.EXPIRE_DATE>=SYSDATE
AND SALE_ORG_CODE='0101' AND CODE='apsis001'
ORDER BY EFFECTIVE_DATE
) WHERE MATERIAL_ID = AP.ID AND ROWNUM=1)
END) as SALE_PRICE,
(CASE WHEN DISTRIBUTION_CHANNEL_CODE = '001' THEN
(SELECT PRICE FROM (
SELECT MATERIAL_ID,PRICE,SPPE.EFFECTIVE_DATE,SALE_ORG_CODE,CODE
FROM SALES_POLICY_PRICE_ENTRY SPPE,SALES_POLICY_PRICE SPP
WHERE SPPE.PARENT_ID = SPP.ID AND SPPE.BLOCKED_STATUS='0'
AND SPPE.EFFECTIVE_DATE<=SYSDATE AND SPPE.EXPIRE_DATE>=SYSDATE
AND SALE_ORG_CODE='01' AND CODE='2sOC'
ORDER BY EFFECTIVE_DATE
) WHERE MATERIAL_ID = AP.ID AND ROWNUM=1)
END) as OC_PRICE,
(CASE WHEN DISTRIBUTION_CHANNEL_CODE = '001' THEN
(SELECT PRICE FROM (
SELECT MATERIAL_ID,PRICE,SPPE.EFFECTIVE_DATE,SALE_ORG_CODE,CODE
FROM SALES_POLICY_PRICE_ENTRY SPPE,SALES_POLICY_PRICE SPP
WHERE SPPE.PARENT_ID = SPP.ID AND SPPE.BLOCKED_STATUS='0'
AND SPPE.EFFECTIVE_DATE<=SYSDATE AND SPPE.EXPIRE_DATE>=SYSDATE
AND SALE_ORG_CODE='01' AND (CODE='Ds2sSC' OR CODE='2sOC')
ORDER BY EFFECTIVE_DATE
) WHERE MATERIAL_ID = AP.ID AND ROWNUM=1)
END) as SC_PRICE,
PRICE,FLOOR_PRICE,MAX_PRICE ,MINIMUM_PACKING
FROM APSIS_PART AP
LEFT JOIN (
SELECT PRICE,FLOOR_PRICE,MAX_PRICE,MATERIAL_ID FROM SALES_POLICY_BASE_PRICE
WHERE AUDIT_FLAG='1' AND BLOCKED_FLAG='0') SPBP
ON SPBP.MATERIAL_ID=AP.ID
ORDER BY ERP_PART_NAME DESC,AUTO_BRAND_CODE_ALL DESC
SELECT ERP_PART_NAME,ERP_CAR_MODEL,AUTO_BRAND_CODE_ALL,OEM_PART_CODE_ALL ,
ERP_ASSIST_NAME,APSIS_PART_UNIT_NAME,APSIS_PART_CODE,DISTRIBUTION_CHANNEL_NAME,PART_BRAND_NAME,
(CASE WHEN DISTRIBUTION_CHANNEL_CODE = '002' THEN
t1.PRICE
END) as SALE_PRICE,
(CASE WHEN DISTRIBUTION_CHANNEL_CODE = '001' THEN
t2.PRICE
END) as OC_PRICE,
(CASE WHEN DISTRIBUTION_CHANNEL_CODE = '001' THEN
t3.PRICE
END) as SC_PRICE,
PRICE,FLOOR_PRICE,MAX_PRICE ,MINIMUM_PACKING
FROM APSIS_PART AP
LEFT JOIN (
SELECT PRICE,FLOOR_PRICE,MAX_PRICE,MATERIAL_ID FROM SALES_POLICY_BASE_PRICE
WHERE AUDIT_FLAG='1' AND BLOCKED_FLAG='0') SPBP
ON SPBP.MATERIAL_ID=AP.ID
LEFT JOIN (
SELECT MATERIAL_ID,PRICE,
ROW_NUMBER() OVER(PARTITION BY MATERIAL_ID ORDER BY EFFECTIVE_DATE) rn
FROM SALES_POLICY_PRICE_ENTRY SPPE,SALES_POLICY_PRICE SPP
WHERE SPPE.PARENT_ID = SPP.ID AND SPPE.BLOCKED_STATUS='0'
AND SPPE.EFFECTIVE_DATE<=SYSDATE AND SPPE.EXPIRE_DATE>=SYSDATE
AND SALE_ORG_CODE='0101' AND CODE='apsis001'
) t1
ON t1.MATERIAL_ID = AP.ID AND t1.rn = 1
LEFT JOIN (
SELECT MATERIAL_ID,PRICE,
ROW_NUMBER() OVER(PARTITION BY MATERIAL_ID ORDER BY EFFECTIVE_DATE) rn
FROM SALES_POLICY_PRICE_ENTRY SPPE,SALES_POLICY_PRICE SPP
WHERE SPPE.PARENT_ID = SPP.ID AND SPPE.BLOCKED_STATUS='0'
AND SPPE.EFFECTIVE_DATE<=SYSDATE AND SPPE.EXPIRE_DATE>=SYSDATE
AND SALE_ORG_CODE='01' AND CODE='2sOC'
) t2
ON t2.MATERIAL_ID = AP.ID AND t2.rn = 1
LEFT JOIN (
SELECT MATERIAL_ID,PRICE,
ROW_NUMBER() OVER(PARTITION BY MATERIAL_ID ORDER BY EFFECTIVE_DATE) rn
FROM SALES_POLICY_PRICE_ENTRY SPPE,SALES_POLICY_PRICE SPP
WHERE SPPE.PARENT_ID = SPP.ID AND SPPE.BLOCKED_STATUS='0'
AND SPPE.EFFECTIVE_DATE<=SYSDATE AND SPPE.EXPIRE_DATE>=SYSDATE
AND SALE_ORG_CODE='01' AND (CODE='Ds2sSC' OR CODE='2sOC')
) t3
ON t3.MATERIAL_ID = AP.ID AND t3.rn = 1
ORDER BY ERP_PART_NAME DESC,AUTO_BRAND_CODE_ALL DESC