SELECT
HSSI.ID AS id,
HSSI.INVOICE_HEADER_ID AS invoiceHeaderId,
HSSI.ISSUE_NUMBER AS issueNumber,
HSSI.SITE_ID AS siteId,
HSSI.COMPANY_NAME AS companyName,
CASE WHEN HSSI.TAXPAYER_NATURE = 1 THEN '一般纳税人' ELSE '小规模' END AS taxpayerNatureString,
CASE WHEN HSSI.TAXPAYER_NATURE = 1 THEN '数电专票' ELSE '数电普票' END AS invoiceType,
HSSI.SITE_CODE AS siteCode,
HSSI.SITE_NAME AS siteName,
COALESCE(SUM(DISTINCT tax_sum.openedTax), 0) AS openedTax,
COALESCE(SUM(DISTINCT tax_sum.openedExcludeTax), 0) AS openedExcludeTax,
COALESCE(SUM(DISTINCT tax_sum.openedTaxPercent), 0) AS openedTaxPercent,
CASE
WHEN COALESCE(SUM(DISTINCT tax_sum.openedTax), 0) >= COALESCE(MAX(should_sum.itemAmount), 0) THEN '已开票'
WHEN COALESCE(SUM(DISTINCT tax_sum.openedTax), 0) = 0 THEN '未开票'
WHEN COALESCE(SUM(DISTINCT tax_sum.openedTax), 0) > 0
AND COALESCE(SUM(DISTINCT tax_sum.openedTax), 0) < COALESCE(MAX(should_sum.itemAmount), 0) THEN '开票中'
ELSE ''
END AS invoiceStatus,
COALESCE(MAX(should_sum01.itemAmount), 0) AS materialShouldTax,
COALESCE(MAX(should_sum02.itemAmount), 0) AS summaryShouldTax,
COALESCE(SUM(red_sum.flushRedAmount), 0) AS flushRedAmount,
HSSI.WORK_CODE AS workCode
FROM HS_SITE_SHOULD_INVOICE HSSI
LEFT JOIN (
SELECT
ISSUE_NUMBER,
SITE_ID,
LISTAGG(WORK_CODE, ',') WITHIN GROUP (ORDER BY WORK_CODE) AS ADVANCE_WORK_CODES
FROM HS_SITE_ADVANCE_INVOICE
WHERE INVOICE_STATUS = 'INVOICE'
GROUP BY ISSUE_NUMBER, SITE_ID
) advance_sum
ON advance_sum.ISSUE_NUMBER = HSSI.ISSUE_NUMBER
AND advance_sum.SITE_ID = HSSI.SITE_ID
LEFT JOIN (
SELECT
work_code,
NVL(SUM(TOTAL_PRICE_TAX), 0) AS openedTax,
NVL(SUM(TOTAL_AMOUNT), 0) AS openedExcludeTax,
NVL(SUM(TOTAL_TAX), 0) AS openedTaxPercent
FROM HS_TAX_ELECTRON_INVOICE
WHERE PUSH_STATUS = 'INVOICE'
AND ACCEPT_MESSAGE = '上传成功'
GROUP BY work_code
) tax_sum
ON tax_sum.work_code = HSSI.WORK_CODE
OR (advance_sum.ADVANCE_WORK_CODES IS NOT NULL
AND INSTR(',' || advance_sum.ADVANCE_WORK_CODES || ',', ',' || tax_sum.work_code || ',') > 0)
LEFT JOIN (
SELECT
ISSUE_NUMBER,
BIZ_ID,
NVL(SUM(AMOUNT), 0) AS itemAmount
FROM HS_SITE_SHOULD_INVOICE_ITEM
WHERE ITEM_TYPE IN ('MATERIAL', 'SUMMARY')
AND EXTEND_TYPE IN ('TOTAL_AMOUNT', 'CONTAIN_TAX')
GROUP BY ISSUE_NUMBER, BIZ_ID
) should_sum
ON should_sum.ISSUE_NUMBER = HSSI.ISSUE_NUMBER
AND should_sum.BIZ_ID = HSSI.ID
LEFT JOIN (
SELECT
ISSUE_NUMBER,
BIZ_ID,
NVL(SUM(AMOUNT), 0) AS itemAmount
FROM HS_SITE_SHOULD_INVOICE_ITEM
WHERE ITEM_TYPE = 'MATERIAL'
AND EXTEND_TYPE = 'TOTAL_AMOUNT'
GROUP BY ISSUE_NUMBER, BIZ_ID
) should_sum01
ON should_sum01.ISSUE_NUMBER = HSSI.ISSUE_NUMBER
AND should_sum01.BIZ_ID = HSSI.ID
LEFT JOIN (
SELECT
ISSUE_NUMBER,
BIZ_ID,
NVL(SUM(AMOUNT), 0) AS itemAmount
FROM HS_SITE_SHOULD_INVOICE_ITEM
WHERE ITEM_TYPE = 'SUMMARY'
AND EXTEND_TYPE = 'CONTAIN_TAX'
GROUP BY ISSUE_NUMBER, BIZ_ID
) should_sum02
ON should_sum02.ISSUE_NUMBER = HSSI.ISSUE_NUMBER
AND should_sum02.BIZ_ID = HSSI.ID
LEFT JOIN (
SELECT
HSIL_INNER.INVOICE_HEADER_ID,
HSIL_INNER.ISSUE_NUMBER,
NVL(SUM(HTEI_INNER.TOTAL_PRICE_TAX), 0) AS flushRedAmount
FROM HS_SITE_INVOICE_INFO_LOG HSIL_INNER
JOIN HS_TAX_ELECTRON_INVOICE HTEI_INNER
ON HSIL_INNER.INVOICE_NUMBER = HTEI_INNER.INVOICE_NUMBER
WHERE HSIL_INNER.BIZ_TYPE = 'RED_FLUSH'
AND HTEI_INNER.PUSH_STATUS = 'INVOICE'
GROUP BY HSIL_INNER.INVOICE_HEADER_ID, HSIL_INNER.ISSUE_NUMBER
) red_sum
ON red_sum.INVOICE_HEADER_ID = HSSI.INVOICE_HEADER_ID
AND red_sum.ISSUE_NUMBER = HSSI.ISSUE_NUMBER
<include refid="dynamicDetailWhere"/>
GROUP BY
HSSI.ID,
HSSI.INVOICE_HEADER_ID,
HSSI.ISSUE_NUMBER,
HSSI.SITE_ID,
HSSI.COMPANY_NAME,
HSSI.TAXPAYER_NATURE,
HSSI.SITE_CODE,
HSSI.SITE_NAME,
HSSI.WORK_CODE
ORDER BY HSSI.ID DESC, HSSI.SITE_ID DESC帮我优化一下
扫一扫
3182
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
