- 技术人生
- oracle cursor 简单用法
procedure
changeSpecialDiscount( compID_in
IN
VARCHAR2
, ccID_in
IN
VARCHAR2
, coNO_in
IN
VARCHAR2
)
IS
sum_cc_all
number
(
14
,
4
):
=
0
; wsp_disc
number
(
14
,
4
); wspcl_disc
number
; cnt_u
number
; cnt_i
number
(
4
); cnt2
number
(
4
) :
=
1
; tot_disc
number
(
14
,
4
) :
=
0
; act_cd
varchar2
(
2
); witem_no
varchar2
(
4
); wcost_cc
number
(
14
,
4
); wqty_order
number
(
14
,
4
); wlp_contr
number
(
14
,
4
); wstatus
varchar2
(
4
); wsrce_type
varchar2
(
1
); wsp_per_unit_contr
number
(
14
,
4
);
cursor
c1
is
select
ITEM_NO,COST_CC_CONTR,QTY_ORDER,LP_CUST_CONTR,STATUS,SRCE_TYPE,SP_PER_UNIT_CONTR
from
tbco_item
where
COMP_ID
=
compID_in
AND
CC_ID
=
ccID_in
AND
CO_NO
=
coNO_in ;
BEGIN
select
sum
( sp_per_unit_contr
*
qty_order )
into
sum_cc_all
from
tbco_item
where
COMP_ID
=
compID_in
AND
CC_ID
=
ccID_in
AND
CO_NO
=
coNO_in ;
select
DISC_AMT
into
wspcl_disc
from
tbco_head
where
COMP_ID
=
compID_in
AND
CC_ID
=
ccID_in
AND
CO_NO
=
coNO_in ;
select
count
(
*
)
into
cnt_u
from
tbco_item
where
COMP_ID
=
compID_in
AND
CC_ID
=
ccID_in
AND
CO_NO
=
coNO_in
AND
date_cancel
is
null
AND
nvl( sp_per_unit_contr,
0
)
<>
0
;
select
count
(
*
)
into
cnt_i
from
tbco_item
where
COMP_ID
=
compID_in
AND
CC_ID
=
ccID_in
AND
CO_NO
=
coNO_in ;
open
c1;
for
idx
in
1
..cnt_i loop
fetch
c1
into
witem_no, wcost_cc, wqty_order, wlp_contr, wstatus, wsrce_type, wsp_per_unit_contr;
SELECT
ACTIVITY_CODE
INTO
act_cd
FROM
TBCM_STATUS
WHERE
SYSTEM_CODE
=
'
CO
'
AND
TABLE_LEVEL
=
'
2
'
AND
DATA_TYPE
=
wsrce_type
AND
(STATUS_NAME1
=
wstatus
OR
STATUS_NAME2
=
wstatus );
--
< Special discount distribution >
if
wsp_per_unit_contr
=
0
or
act_cd
=
'
15
'
then
wsp_disc :
=
0
; elsif cnt2
<
cnt_u
then
wsp_disc :
=
round
(wspcl_disc
*
( wsp_per_unit_contr
*
wqty_order
/
sum_cc_all),
2
); cnt2 :
=
cnt2
+
1
; elsif cnt2
>=
cnt_u
then
wsp_disc :
=
wspcl_disc
-
tot_disc;
end
if
;
--
Accumulation of distributed selling price
tot_disc :
=
tot_disc
+
round
(wsp_disc,
2
);
--
Updating item special discount
update
tbco_item
set
special_disc
=
round
(wsp_disc,
2
), date_modify
=
sysdate
where
COMP_ID
=
compID_in
AND
CC_ID
=
ccID_in
AND
CO_NO
=
coNO_in
AND
item_no
=
witem_no;
end
loop;
close
c1;
END
;
