Oracle 账户

账户:oracle 密码账户:root 密码 SYS SYSTEM 密码 ncc 数据库密码

ORACLE 版本

# 方法一：
select * from v$version;

#方法二：
select * from PRODUCT_COMPONENT_VERSION;

ORACLE 启动命令

su - oracle lsnrctl start //启动监听 sqlplus /nolog //登录sqlplus SQL> connect /as sysdba //连接oracle SQL> startup //起动数据库 SQL> exit //退出sqlplus ，启动监听

第一步：sqlplus / as sysdba; 第二步：connect ncccc10/abc123xxx;

ORACLE 分页查询

rownum 从 1开始,

入参 PageIndex =1 ， PageSize =50

paramVo.setPageSize(2000);
paramVo.setPageStart((paramVo.getPageIndex()-1)*paramVo.getPageSize()+1);  // 从1，51 开始
paramVo.setPageEnd(paramVo.getPageIndex()*paramVo.getPageSize());  // 游标 右移动 50


SELECT * FROM ( 
SELECT rownum AS rn, t.*  FROM (
				SELECT
					SUBSTR(a.PAYDATE, 0, 10) AS settlementDate,
					SUBSTR(a.BILLDATE, 0, 10) AS documentDate,
					dr
				FROM
					AP_PAYBILL a
				WHERE
					a.dr = 0
				AND a.BILLSTATUS = 8
				AND a.APPROVESTATUS = 1
				AND a.EFFECTSTATUS = 10
				AND a.PK_BILLTYPE = 'F3'
			) t
		WHERE t.dr = 0 
     AND rownum <= 5

SELECT
	substr(a.PREPAREDDATEV, 0, 10),
	sum(a.CREDITAMOUNT) AS creditAmount,
	sum(a.DEBITAMOUNT) AS debitAmount,
	(
		sum(a.CREDITAMOUNT) - sum(a.DEBITAMOUNT)
	) AS 变动数
FROM
	GL_DETAIL a,
	BD_ACCASOA b,
	gl_freevalue c,
	bd_cust_supplier d
WHERE
	a.PK_ACCASOA = b.PK_ACCASOA (+)
AND a.assid = c.freevalueid (+)
AND SUBSTR(c.TYPEVALUE1 ,- 20) = d.PK_CUST_SUP (+)
AND a.dr = 0
AND a.accountcode IN ('2202', '122301', '122302')
AND d. NAME LIKE '%佛山市新碧%'
AND a.PREPAREDDATEV LIKE '2022-12%'
AND a.PREPAREDDATEV >= '2000-11-30 23:59:59%'
AND c.TYPEVALUE2 LIKE '%1001A11000000000EVE9'
GROUP BY
	substr(a.PREPAREDDATEV, 0, 10)

SELECT
	gl_detail.pk_detail
FROM
	gl_detail
LEFT OUTER JOIN gl_dtlfreevalue ON gl_detail.pk_detail = gl_dtlfreevalue.pk_detail
WHERE
	pk_voucher IN ('1001A1100000008AYM1K')
AND gl_detail.dr = 0

SELECT
	SUBSTR(a.APPROVEDATE, 0, 10) AS checkDate审核时间,
	SUBSTR(a.BILLDATE, 0, 10) AS documentDate单据日期,
	e. CODE AS supplyCode供应商编码,
	e. NAME AS supplyName,
	a.BILLNO AS billNo,
	decode(
		a.def12,
		'1001A1100000000017G9',
		'国际机票',
		'1001A110000000000U0R',
		'国内机票',
		'1001A1100000000017GH',
		'国内酒店',
		'1001A1100000000017GM',
		'用车',
		'1001A1100000000017GF',
		'火车票',
		'1001A1100000003CJP2B',
		'外卖',
		'1001A1100000000017HC',
		'保险',
		'1001A1100000004HZULA',
		'B2B保险'
	) AS def12busiName,
	a.def12 AS def12辅助核算,
	h. NAME AS paymentType辅助核算项,
	a.def3 AS def3,
	decode(
		a.def3,
		'1001A11000000000GDQG',
		'预付',
		'1001A11000000000GDQH',
		'现付',
		'1001A11000000000GDQI',
		'赊销',
		'1001A1100000000DGA5F',
		'一单一结'
	) AS settlementType支付方式,
	a.pk_tradetypeid AS pk_tradetypeid,
	g.billtypename AS transactionType应付类型,
	a.MONEY AS 应付单金额
FROM
	AP_PAYABLEBILL a,
	BD_SUPPLIER e,
	bd_currtype f,
	BD_BILLTYPE g,
	bd_defdoc h,
	(
		SELECT
			billno,
			SUPPLIER,
			rate,
			MIN(SCOMMENT) AS scomment
		FROM
			AP_PAYABLEITEM
		GROUP BY
			billno,
			SUPPLIER,
			rate
	) i
WHERE
	i.SUPPLIER = e.pk_supplier (+)
AND a.PK_CURRTYPE = f.PK_CURRTYPE (+)
AND a.PK_TRADETYPEID = g.PK_BILLTYPEID (+)
AND a.def12 = h.pk_defdoc (+)
AND a.billno = i.billno (+)
AND a.dr = 0 --  '已签字' AS documentState,
--       '审批通过' AS approvalState,
--      '已生效' AS effectiveState,
AND a.BILLSTATUS = 1
AND a.APPROVESTATUS = 1
AND a.EFFECTSTATUS = 10
AND a.PK_BILLTYPE = 'F1'
AND a.def12 IN (
	'1001A1100000000017G9',
	'1001A1100000000017GF',
	'1001A1100000000017GM',
	'1001A1100000003CJP2B',
	'1001A110000000000U0R',
	'1001A1100000000017GH',
	'1001A1100000000017HC',
	'1001A1100000004HZULA'
)
AND a.pk_tradetypeid IN (
	'1001A1100000003DZ2LB',
	'1001A110000000398JAD',
	'1001A11000000000J6M5',
	'1001A11000000000J6JS',
	'1001A11000000000J6N2',
	'1001A11000000000J6KS',
	'1001A11000000000J6O1',
	'1001A11000000001VW9W' --  增加 航天华有、红色加力
)
ORDER BY
	checkDate审核时间 DESC,
	documentDate单据日期 DESC,
	billNo DESC

SELECT
	substr(a.PREPAREDDATEV, 0, 4),
	sum(a.CREDITAMOUNT) AS creditAmount,
	sum(a.DEBITAMOUNT) AS debitAmount,
	(
		sum(a.DEBITAMOUNT) - sum(a.CREDITAMOUNT)
	) AS 变动数
FROM
	GL_DETAIL a,
	BD_ACCASOA b,
	gl_freevalue c,
	bd_cust_supplier d
WHERE
	a.PK_ACCASOA = b.PK_ACCASOA (+)
AND a.assid = c.freevalueid (+)
AND SUBSTR(c.TYPEVALUE1 ,- 20) = d.PK_CUST_SUP (+)
AND a.dr = 0
AND a.accountcode IN (
	'122101',
	'112201',
	'1221',
	'1122'
)
AND d. CODE = '20180626110257052133'
AND a.PREPAREDDATEV <= '2022-11-30 23:59:59%'
AND c.TYPEVALUE2 LIKE '%1001A11000000000EVE9'
GROUP BY
	substr(a.PREPAREDDATEV, 0, 4)

SELECT SUBSTR( a.APPROVEDATE, 0, 10 ) AS checkDate审核时间, SUBSTR( a.BILLDATE, 0, 10 ) AS documentDate单据日期, e.Code AS supplyCode供应商编码, e.NAME AS supplyName, a.BILLNO AS billNo, decode( a.def12, '1001A1100000000017G9', '国际机票', '1001A110000000000U0R', '国内机票', '1001A1100000000017GH', '国内酒店', '1001A1100000000017GM', '用车' ,'1001A1100000000017GF', '火车票' ,'1001A1100000003CJP2B', '外卖', '1001A1100000000017HC', '保险', '1001A1100000004HZULA', 'B2B保险') AS def12busiName, a.def12 as def12辅助核算, h.NAME AS paymentType辅助核算项, a.def3 as def3, decode( a.def3, '1001A11000000000GDQG', '预付', '1001A11000000000GDQH', '现付', '1001A11000000000GDQI', '赊销', '1001A1100000000DGA5F', '一单一结' ) AS settlementType支付方式, a.pk_tradetypeid as pk_tradetypeid, g.billtypename AS transactionType应付类型, a.MONEY AS 应付单金额 FROM AP_PAYABLEBILL a, BD_SUPPLIER e, bd_currtype f, BD_BILLTYPE g, bd_defdoc h, ( SELECT billno, SUPPLIER, rate,MIN(SCOMMENT) as scomment FROM AP_PAYABLEITEM GROUP BY billno, SUPPLIER, rate ) i WHERE i.SUPPLIER = e.pk_supplier (+) AND a.PK_CURRTYPE = f.PK_CURRTYPE (+) AND a.PK_TRADETYPEID = g.PK_BILLTYPEID (+) AND a.def12 = h.pk_defdoc (+) AND a.billno = i.billno (+) AND a.dr = 0 -- '已签字' AS documentState, -- '审批通过' AS approvalState, -- '已生效' AS effectiveState, AND a.BILLSTATUS = 1 AND a.APPROVESTATUS = 1 AND a.EFFECTSTATUS = 10 AND a.PK_BILLTYPE = 'F1' AND a.def12 IN ( '1001A1100000000017G9', '1001A1100000000017GF', '1001A1100000000017GM', '1001A1100000003CJP2B', '1001A110000000000U0R', '1001A1100000000017GH', '1001A1100000000017HC', '1001A1100000004HZULA' ) AND a.pk_tradetypeid IN ( '1001A1100000003DZ2LB', '1001A110000000398JAD', '1001A11000000000J6M5', '1001A11000000000J6JS', '1001A11000000000J6N2', '1001A11000000000J6KS', '1001A11000000000J6O1', '1001A11000000001VW9W' -- 增加 航天华有、红色加力 ) ORDER BY checkDate审核时间 DESC, documentDate单据日期 DESC, billNo DESC


SELECT
	yfd.accountPeriod,
	yfd.corpCode,
	yfd.corpName,
	yfd.amount,
	fkd.amount AS b
FROM
	(
		SELECT
			SUBSTR(a.APPROVEDATE, 0, 7) AS accountPeriod,
			e. CODE AS corpCode,
			e. NAME AS corpName,
			sum(a.MONEY) AS amount,
			'应付单' AS 单据类型
		FROM
			AP_PAYABLEBILL a,
			BD_SUPPLIER e,
			bd_currtype f,
			BD_BILLTYPE g,
			bd_defdoc h,
			(
				SELECT
					billno,
					SUPPLIER,
					rate,
					MIN(SCOMMENT) AS scomment
				FROM
					AP_PAYABLEITEM
				GROUP BY
					billno,
					SUPPLIER,
					rate
			) i
		WHERE
			i.SUPPLIER = e.pk_supplier (+)
		AND a.PK_CURRTYPE = f.PK_CURRTYPE (+)
		AND a.PK_TRADETYPEID = g.PK_BILLTYPEID (+)
		AND a.def12 = h.pk_defdoc (+)
		AND a.billno = i.billno (+)
		AND a.dr = 0
		AND a.BILLSTATUS = 1
		AND a.APPROVESTATUS = 1
		AND a.EFFECTSTATUS = 10
		AND a.PK_BILLTYPE = 'F1'
		AND a.def12 IN (
			'1001A1100000000017G9',
			'1001A1100000000017GF',
			'1001A1100000000017GM',
			'1001A1100000003CJP2B',
			'1001A110000000000U0R',
			'1001A1100000000017GH',
			'1001A1100000000017HC',
			'1001A1100000004HZULA'
		)
		AND a.pk_tradetypeid IN (
			'1001A1100000003DZ2LB',
			'1001A110000000398JAD',
			'1001A11000000000J6M5',
			'1001A11000000000J6JS',
			'1001A11000000000J6N2',
			'1001A11000000000J6KS',
			'1001A11000000000J6O1',
			'1001A11000000001VW9W',
			'1001A1100000006K1IQC',
			'1001A1100000006UVBRE'
		)
		AND a.APPROVEDATE LIKE '2022-11%'
		GROUP BY
			SUBSTR(a.APPROVEDATE, 0, 7),
			e. CODE,
			e. NAME
	) yfd
LEFT OUTER JOIN (
	SELECT
		SUBSTR(a.paydate, 0, 7) AS accountPeriod,
		e. CODE AS corpCode,
		e. NAME AS corpName,
		sum(a.LOCAL_MONEY) AS amount,
		'付款单' AS 单据类型
	FROM
		AP_PAYBILL a,
		BD_SUPPLIER e,
		ORG_ORGS d,
		(
			SELECT
				billno,
				SUPPLIER,
				rate
			FROM
				ap_payitem
			GROUP BY
				billno,
				SUPPLIER,
				rate
		) i,
		(
			SELECT
				sum(
					abs(LOCAL_MONEY_CR) - abs(LOCAL_MONEY_DE)
				) AS money,
				billno2
			FROM
				ARAP_VERIFYDETAIL
			WHERE
				REDFLAG = 0
			GROUP BY
				billno2
		) b
	WHERE
		a.billno = b.billno2 (+)
	AND a.PK_FIORG = d.PK_ORG (+)
	AND a.billno = i.billno (+)
	AND i.SUPPLIER = e.pk_supplier (+)
	AND a.dr = 0
	AND a.BILLSTATUS = 8
	AND a.APPROVESTATUS = 1
	AND a.EFFECTSTATUS = 10
	AND a.PK_BILLTYPE = 'F3' --
	AND a.billno = 'D32022112100133167'
	AND a.paydate LIKE '2022-11%'
	GROUP BY
		SUBSTR(a.paydate, 0, 7),
		e. CODE,
		e. NAME
) fkd ON yfd.corpCode = fkd.corpCode
AND yfd.corpName = fkd.corpName
AND yfd.accountPeriod = fkd.accountPeriod


    select * from (
    select rownum as rn,t.* from (
    SELECT
    SUBSTR( a.PAYDATE, 0, 10 ) AS settlementDate,
    SUBSTR( a.BILLDATE, 0, 10 ) AS documentDate
    FROM
    AP_PAYBILL a,
    ( SELECT sum( DECODE(PK_BILLTYPE,'F3',abs(LOCAL_MONEY_CR),LOCAL_MONEY_CR) - LOCAL_MONEY_DE ) AS money, billno2
    FROM ARAP_VERIFYDETAIL where REDFLAG =0 and billno2!=billno
    GROUP BY billno2) b,
    sm_user c,
    ORG_ORGS d,
    BD_SUPPLIER e,
    bd_currtype f,
    BD_BILLTYPE g,
    bd_defdoc h,
    ( SELECT billno, SUPPLIER, rate,MIN(SCOMMENT) as scomment FROM ap_payitem GROUP BY billno, SUPPLIER, rate ) i
    WHERE
    a.billno = b.billno2 ( + )
    AND a.billmaker = c.CUSERID ( + )
    AND a.pk_org = d.PK_ORG ( + )
    AND a.PK_CURRTYPE = f.PK_CURRTYPE ( + )
    AND a.pk_tradetypeid = g.PK_BILLTYPEID ( + )
    AND a.def12 = h.pk_defdoc ( + )
    AND a.billno = i.billno ( + )
    AND i.SUPPLIER = e.pk_supplier ( + )
    AND a.dr = 0
    AND a.BILLSTATUS = 8
    AND a.APPROVESTATUS = 1
    AND a.EFFECTSTATUS = 10
    AND a.PK_BILLTYPE = 'F3'
    order by settlementDate desc,documentDate desc,billNo desc
    ) t  where t.dr=0 and rownum <= 50 pageEnd
    ) where rn >= 1 pageStart

SELECT a.billno as nccPayBillNo,a.local_money ,
                          ( a.local_money - NVL ( b.money, 0 ) ) as checkOverAmount,
                          a.APPROVEDATE as checkOverDate,
                          b.billNo AS payBillNo
                   FROM
                   AP_PAYABLEBILL a,
                   (
                     SELECT  sum( abs(LOCAL_MONEY_DE) - abs( LOCAL_MONEY_CR ) ) AS money, listagg(BILLNO,',') within group(order by BILLNO ) as BILLNO,  billno2,  BUSIFLAG  FROM   ARAP_VERIFYDETAIL 
 WHERE   REDFLAG = 0 AND BILLCLASS='fk' GROUP BY   billno2 ,BUSIFLAG ) b,
                   BD_SUPPLIER e, 
                   bd_currtype f, 
                   BD_BILLTYPE g, 
                   bd_defdoc h, 
                   ( SELECT billno, SUPPLIER, rate,MIN(SCOMMENT) as scomment FROM AP_PAYABLEITEM GROUP BY billno, SUPPLIER, rate ) i 
                   WHERE 
                   a.billno = b.billno2 ( + ) 
                   AND i.SUPPLIER = e.pk_supplier (+) 
                   AND a.PK_CURRTYPE = f.PK_CURRTYPE (+) 
                   AND a.PK_TRADETYPEID = g.PK_BILLTYPEID (+) 
                   AND a.def12 = h.pk_defdoc (+) 
                   AND a.billno = i.billno (+) 
                   AND a.dr = 0 
                   AND a.BILLSTATUS = 1 
                   AND a.APPROVESTATUS = 1 
                   AND a.EFFECTSTATUS = 10 
                   AND a.PK_BILLTYPE = 'F1' 
                   AND a.def12 IN ( 
                       '1001A1100000000017G9', 
                       '1001A1100000000017GF', 
                       '1001A1100000000017GM', 
                       '1001A1100000003CJP2B', 
                       '1001A110000000000U0R', 
                       '1001A1100000000017GH', 
                       '1001A1100000000017HC', 
                       '1001A1100000004HZULA' 
                     ) 
                   AND a.pk_tradetypeid IN ( 
                   '1001A1100000003DZ2LB', 
                   '1001A110000000398JAD', 
                   '1001A11000000000J6M5', 
                   '1001A11000000000J6JS', 
                   '1001A11000000000J6N2', 
                   '1001A11000000000J6KS', 
                   '1001A11000000000J6O1', 
                   '1001A11000000001VW9W', 
                   '1001A1100000006K1IQC', 
                   '1001A1100000006UVBRE' 
                   ) 
                  and a.billNo = 'D12022121300012565'

Oracle的Nvl函数

nvl( ) 函数

从两个表达式返回一个非null 值。

语法

NVL(eExpression1, eExpression2)

参数

eExpression1, eExpression2 如果 eExpression1 的计算结果为 null 值，则 NVL( ) 返回 eExpression2。如果 eExpression1 的计算结果不是 null 值，则返回 eExpression1。 eExpression1 和 eExpression2 可以是任意一种数据类型。如果 eExpression1 与 eExpression2 的结果皆为 null 值，则 NVL( ) 返回 NULL。

(case (付款单金额-sum(已核销金额)) when 0 then '已核销' else '未核销' end) as 核销状态

select 
付款单单号,供应商编号, 供应商名称,  单据类型,  业务线, 支付方式,  制单时间 , 支付时间 ,
审批状态, 付款单金额, sum(已核销金额),(付款单金额-sum(已核销金额)) as 未核销金额,
(case (付款单金额-sum(已核销金额))   when 0 then  '已核销'   else   '未核销'    end) as 核销状态
from (
select x.billno as 付款单单号,max(x.corpCode) as 供应商编号  ,max(x.corpName) as 供应商名称, x.transactionType as 单据类型,x.busiName as 业务线,x.settlementType as 支付方式,x.documentDate as 制单时间 ,x.settlementDate as 支付时间 ,
x.审批状态,x.付款单金额,x.BUSIFLAG,x.处理日期,'摘要' as 摘要 ,x.已核销金额
 from (
SELECT
	 e.CODE AS corpCode,
    e.NAME AS corpName,
    g.billtypename AS transactionType,
    decode( a.def12, '1001A1100000000017G9', '国际机票', '1001A110000000000U0R', '国内机票', '1001A1100000000017GH', '国内酒店',
        '1001A1100000000017GM', '用车' ,'1001A1100000000017GF', '火车票' ,'1001A1100000003CJP2B', '外卖',
        '1001A1100000000017HC', '保险', '1001A1100000004HZULA', 'B2B保险') AS busiName,
    decode( a.def3, '1001A11000000000GDQG', '预付', '1001A11000000000GDQH', '现付', '1001A11000000000GDQI', '赊销',
        '1001A1100000000DGA5F', '一单一结' ) AS settlementType,
   a.billNo,   
SUBSTR( a.BILLDATE, 0, 10 ) AS documentDate,
SUBSTR( a.PAYDATE, 0, 10 ) AS settlementDate,
'摘要'as 摘要,
decode(a.APPROVESTATUS,'-1' ,'自由','1','审批通过','2','审批中','3','已提交','0','审批不通过'） as 审批状态,
 a.LOCAL_MONEY AS 付款单金额, b.BUSIFLAG,b.BUSIDATE AS 处理日期, b.money as 已核销金额
    FROM 	AP_PAYBILL a,
	(
SELECT
	 sum( abs(LOCAL_MONEY_CR) - abs( LOCAL_MONEY_DE) ) AS money,
	billno2 ,BUSIFLAG,BUSIDATE
FROM
	ARAP_VERIFYDETAIL 
WHERE
      REDFLAG = 0  -- and BUSIFLAG=0
GROUP BY
	billno2 ,BUSIFLAG,BUSIDATE
	) b ,

	BD_SUPPLIER e,
       ORG_ORGS d,
	AP_PAYITEM item,	
   BD_BILLTYPE g
WHERE
	a.billno=b.billno2 ( + ) 
	AND a.PK_FIORG = d.PK_ORG ( + ) 
	AND  a.PK_PAYBILL =item.PK_PAYBILL(+)
	AND item.SUPPLIER = e.pk_supplier ( + ) 
	AND a.dr = 0 
	AND a.BILLSTATUS = 8 
--	AND a.APPROVESTATUS = 1 
    AND a.EFFECTSTATUS = 10 
  AND a.pk_tradetypeid = g.PK_BILLTYPEID ( + )
	AND a.PK_BILLTYPE = 'F3' 
   AND a.def12 IN (
           '1001A1100000000017G9',
   '1001A1100000000017GF',
   '1001A1100000000017GM',
   '1001A1100000003CJP2B',
   '1001A110000000000U0R',
   '1001A1100000000017GH',
   '1001A1100000000017HC',
   '1001A1100000004HZULA',
   '1001A1100000008M8749'
    )  
AND a.pk_tradetypeid IN (
      '1001A1100000006UVEYK',
'1001A11000000032MT10',
'1001A1100000004HPNJ8',
'1001A11000000001UVPG',
'1001A11000000000MLQ5',
'1001A11000000000MLRX',
'1001A11000000000MLOC',
'1001A11000000032M88O',
'1001A110000000398S79',
'1001A110000000398OQM',
'1001A1100000006K15I8',
'1001A11000000000MLLR',
'1001A11000000032ME7S',
'1001A1100000003DYX69',
'1001A11000000032MON2',
'1001A11000000000M2L5',
'1001A1100000002SIIWS',
'1001A1100000001A4OLJ',
'1001A1100000008M591K',
'1001A11000000068HSPG'
    ) 

      and a.billno='D32022031400033379'
       order by  a.BILLDATE desc
       )  x  group by   x.billno,x.transactionType,x.busiName,x.settlementType,x.documentDate,x.settlementDate,
   x.审批状态,x.付款单金额,x.BUSIFLAG,x.处理日期,x.已核销金额
order by x.settlementDate desc, x.billno
 ) s
  
group by 付款单单号,供应商编号, 供应商名称,  单据类型,  业务线, 支付方式,  制单时间 , 支付时间 ,
 审批状态, 付款单金额

decode decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值)

IF 条件=值1 THEN
　　　　RETURN(翻译值1)
ELSIF 条件=值2 THEN
　　　　RETURN(翻译值2)
　　　　......
ELSIF 条件=值n THEN
　　　　RETURN(翻译值n)
ELSE
　　　　RETURN(缺省值)
END IF

-- 判断字符串

 select id,name,
    decode(id,1,'第一个',2,'第二个',3,'第三个','没有') new_id 
from t_decode;

(case when (付款单金额-sum(已核销金额)) = 0 then '已核销' when (付款单金额 -sum(已核销金额)) >0 then '部分核销' else '未核销' end) as 核销状态

select 
付款单单号,供应商编号, 供应商名称,  单据类型,  业务线, 支付方式,  制单时间 , 支付时间 ,
审批状态, 付款单金额, sum(已核销金额),(付款单金额-sum(已核销金额)) as 未核销金额,
(case  when (付款单金额-sum(已核销金额)) = 0 then  '已核销' 
     when (付款单金额 -sum(已核销金额)) >0  then '部分核销'
     else   '未核销'    end) as 核销状态
from (
select x.billno as 付款单单号,max(x.corpCode) as 供应商编号  ,max(x.corpName) as 供应商名称, x.transactionType as 单据类型,x.busiName as 业务线,x.settlementType as 支付方式,x.documentDate as 制单时间 ,x.settlementDate as 支付时间 ,
x.审批状态,x.付款单金额,x.BUSIFLAG,x.处理日期,'摘要' as 摘要 ,x.已核销金额
 from (
SELECT
	 e.CODE AS corpCode,
    e.NAME AS corpName,
    g.billtypename AS transactionType,
    decode( a.def12, '1001A1100000000017G9', '国际机票', '1001A110000000000U0R', '国内机票', '1001A1100000000017GH', '国内酒店',
        '1001A1100000000017GM', '用车' ,'1001A1100000000017GF', '火车票' ,'1001A1100000003CJP2B', '外卖',
        '1001A1100000000017HC', '保险', '1001A1100000004HZULA', 'B2B保险') AS busiName,
    decode( a.def3, '1001A11000000000GDQG', '预付', '1001A11000000000GDQH', '现付', '1001A11000000000GDQI', '赊销',
        '1001A1100000000DGA5F', '一单一结' ) AS settlementType,
   a.billNo,   
SUBSTR( a.BILLDATE, 0, 10 ) AS documentDate,
SUBSTR( a.PAYDATE, 0, 10 ) AS settlementDate,
'摘要'as 摘要,
decode(a.APPROVESTATUS,'-1' ,'自由','1','审批通过','2','审批中','3','已提交','0','审批不通过'） as 审批状态,
 a.LOCAL_MONEY AS 付款单金额, b.BUSIFLAG,b.BUSIDATE AS 处理日期, b.money as 已核销金额
    FROM 	AP_PAYBILL a,
	(
SELECT
	 sum( abs(LOCAL_MONEY_CR) - abs( LOCAL_MONEY_DE) ) AS money,
	billno2 ,BUSIFLAG,BUSIDATE
FROM
	ARAP_VERIFYDETAIL 
WHERE
      REDFLAG = 0  -- and BUSIFLAG=0
GROUP BY
	billno2 ,BUSIFLAG,BUSIDATE
	) b ,

	BD_SUPPLIER e,
       ORG_ORGS d,
	AP_PAYITEM item,	
   BD_BILLTYPE g
WHERE
	a.billno=b.billno2 ( + ) 
	AND a.PK_FIORG = d.PK_ORG ( + ) 
	AND  a.PK_PAYBILL =item.PK_PAYBILL(+)
	AND item.SUPPLIER = e.pk_supplier ( + ) 
	AND a.dr = 0 
	AND a.BILLSTATUS = 8 
--	AND a.APPROVESTATUS = 1 
    AND a.EFFECTSTATUS = 10 
  AND a.pk_tradetypeid = g.PK_BILLTYPEID ( + )
	AND a.PK_BILLTYPE = 'F3' 
   AND a.def12 IN (
           '1001A1100000000017G9',
   '1001A1100000000017GF',
   '1001A1100000000017GM',
   '1001A1100000003CJP2B',
   '1001A110000000000U0R',
   '1001A1100000000017GH',
   '1001A1100000000017HC',
   '1001A1100000004HZULA',
   '1001A1100000008M8749'
    )  
AND a.pk_tradetypeid IN (
      '1001A1100000006UVEYK',
'1001A11000000032MT10',
'1001A1100000004HPNJ8',
'1001A11000000001UVPG',
'1001A11000000000MLQ5',
'1001A11000000000MLRX',
'1001A11000000000MLOC',
'1001A11000000032M88O',
'1001A110000000398S79',
'1001A110000000398OQM',
'1001A1100000006K15I8',
'1001A11000000000MLLR',
'1001A11000000032ME7S',
'1001A1100000003DYX69',
'1001A11000000032MON2',
'1001A11000000000M2L5',
'1001A1100000002SIIWS',
'1001A1100000001A4OLJ',
'1001A1100000008M591K',
'1001A11000000068HSPG'
    ) 

      and a.billno='D32022031400033379'
       order by  a.BILLDATE desc
       )  x  group by   x.billno,x.transactionType,x.busiName,x.settlementType,x.documentDate,x.settlementDate,
   x.审批状态,x.付款单金额,x.BUSIFLAG,x.处理日期,x.已核销金额
order by x.settlementDate desc, x.billno
 ) s
  
group by 付款单单号,供应商编号, 供应商名称,  单据类型,  业务线, 支付方式,  制单时间 , 支付时间 ,
 审批状态, 付款单金额

Lpad函数，如何使主键的值自动加1并在前面补0

Oracle 语法+语句

Oracle 账户

ORACLE 版本

ORACLE 启动命令

ORACLE 分页查询