별거 아닌데, 이거 만든다고 하룻밤을 꼬박 샜다;;;
ㅎㅎㅎ ;;;
나중에 보면 기분이 새로울거 같아 올려놓는다;
CREATE OR REPLACE PROCEDURE PVMS.P0_PRC_CREATE_PINMKDB(
vi_qty IN NUMBER, -- Creation Qty
vi_unit IN NUMBER
) AS
/*************************************************************************************
Copyright(C) 2008 TNC Technology commission
All rights reserved.
This package is a payment implementation written by FLEXI TELECOM
The implementation was written so as to conform with TNC Technology.
Copyright remains TNC Technology, and as such any Copyright notices in
the code are not to be removed.
If this package is used in a product, the uses should be notified and permitted by
TNC Technology.
This can be in the form of a textual message at program startup or
in documentation (online or textual) provided with the package.
Distribution and use in source and binary forms, with or without
modification, are NOT PERMITTED.
NAME : P0_PRC_CREATE_PINMKDB
**************************************************************************************/
-- ****************************************************************************
-- DESCRIPTION
-- CARD PIN ¿ ¿
--
-- AUTHOR
-- TNC (CHOE IN UK)
--
-- NOTE
-- TEST PROCEDURE(P0_PRC_CREATE_PIN)
--
-- REVISION
-- 0.1 2009- 03-11 16:00
--
--
-- BRIEF RETURN
--
-- ****************************************************************************
vi_gap_qty NUMBER(9);
vi_block_qty NUMBER(9);
vi_strg_qty NUMBER(9);
vi_loop_cnt NUMBER(9);
i NUMBER(8);
vi_issueseq NUMBER(7);
vi_price NUMBER(7);
vi_seq NUMBER(7);
vs_balance VARCHAR(14);
vs_call_used_period NUMBER(4);
vi_cardno NUMBER;
vi_pin_hrn NUMBER;
vs_cardno VARCHAR2(15);
vi_contno_first NUMBER;
vs_contno_first VARCHAR2(15);
vs_contno_last VARCHAR2(15);
vs_end_period VARCHAR(4);
vs_enddt VARCHAR(10);
vs_issuedt VARCHAR(10);
vs_grace_period VARCHAR(4);
vs_indt VARCHAR(4);
vd_issuedt DATE;
vs_issuestat1 VARCHAR(4);
vs_limit VARCHAR2(14);
vs_lotno VARCHAR2(20);
vs_price_model VARCHAR2(10);
vs_orderdt VARCHAR(10);
vs_partner VARCHAR2(20);
vs_partner_prefix VARCHAR2(60);
vs_pgm_id VARCHAR2(10);
vs_pinno VARCHAR2(20);
vs_priceplan VARCHAR2(10);
vs_remark VARCHAR2(60);
vs_user_id VARCHAR2(10);
P_ERR_MSG VARCHAR2(256);
vo_out VARCHAR2(100); -- Return message
----- *************************************************************************/
V_ERRTITLE VARCHAR2(256);
E_USERERROR EXCEPTION; -- ¿¿ ¿¿.
E_INTERNALERROR EXCEPTION; -- ¿¿ ¿¿.
--******************************************************************************
/*============================================================================*/
BEGIN
vs_issuestat1 := '1';
vs_price_model := '1000';
vs_lotno := '';
vi_price := '10000';
vs_remark := '';
vs_indt := '';
vs_orderdt := '';
vs_user_id := 'person';
vs_pgm_id := 'prc_per';
vs_enddt := '20101231';
vs_partner := '00000000';
vs_priceplan := '';
vi_loop_cnt := 0;
vs_partner_prefix := '000';
vs_balance := 10000;
BEGIN
SELECT nvl(max(CONTNO),'100000000000')
INTO vs_cardno
FROM P_CARDMAST;
SELECT nvl(max(PID),'10000000000000')
INTO vs_pinno
FROM P_CARDMAST;
SELECT nvl(max(ISSUESEQ),0), TO_CHAR(SYSDATE, 'yyyy-mm-dd')
INTO vi_issueseq, vs_issuedt
FROM P_ISSUEHOTLOG;
EXCEPTION
WHEN OTHERS THEN
RAISE e_InternalError;
RETURN;
END ;
BEGIN
SELECT grace_period,call_used_period,end_period
INTO vs_grace_period,vs_call_used_period ,vs_end_period
FROM SALEPRICEMODEL
WHERE PRICEMODEL =vs_price_model;
EXCEPTION
WHEN OTHERS THEN
RAISE e_InternalError;
RETURN;
END ;
vi_strg_qty := vi_qty;
vi_block_qty := vi_unit;
vi_loop_cnt := 0;
If vi_block_qty < 3 Then
vi_block_qty := 3;
End If;
If vi_block_qty > vi_strg_qty Then
vi_block_qty := vi_strg_qty;
End If;
-- CARD creation
vs_contno_first := vs_cardno;
vs_limit := vs_enddt;
vi_pin_hrn := TO_NUMBER(vs_pinno) + 1;
vi_cardno := TO_NUMBER(vs_cardno) + 1;
WHILE vi_strg_qty > 0 LOOP
If vi_block_qty < vi_strg_qty Then
vi_gap_qty := vi_block_qty;
vi_strg_qty := vi_strg_qty - vi_block_qty;
Else
vi_gap_qty := vi_strg_qty;
vi_strg_qty := vi_strg_qty - vi_gap_qty;
End If;
vi_contno_first := vi_cardno;
vs_contno_first := to_char(vi_contno_first,'00000000000000');
vs_contno_last := to_char(to_number(vs_contno_first) + vi_gap_qty - 1 ,'00000000000000');
vi_loop_cnt := vi_loop_cnt + 1;
vi_issueseq := vi_issueseq + 1;
INSERT INTO P_ISSUEHOTLOG
( issueseq, issuestat, issuedt, pricemodel, lotno,
issue_qty, card_amt, contno_fr, contno_to, remark,
indt, orderdt,
crtdt, crt_user, pgm_id, enddt, partner, priceplan)
VALUES
( vi_issueseq, vs_issuestat1, to_date(vs_issuedt, 'yyyy-mm-dd'),vs_price_model, vs_lotno,
vi_gap_qty, vi_price, vs_contno_first , vs_contno_last, vs_remark,
to_date(vs_indt, 'yyyy-mm-dd'),to_date(vs_orderdt, 'yyyy-mm-dd'),
sysdate, vs_user_id, vs_pgm_id,to_date(vs_enddt, 'yyyy-mm-dd'), vs_partner, vs_priceplan);
--P0_PRC_CREATE_PIN(ls_date, ll_seq, ld_crdno_first, ll_gap,li_len, gs_user_id, ls_sysdate, ld_lCard, ls_out,ls_msg) ;
FOR i IN 0..vi_gap_qty - 1 LOOP
BEGIN
INSERT INTO p_cardmAst
(pid, contno, status, issueseq, issuedt,
issue_user, priceplan, pricemodel, lotno,
refillsum_amt, usedsum_amt, salesum_amt, balance,
partner_prefix, refill_yn, first_refill_amt, first_sale_amt, last_refill_amt, sale_flag, remark,
indt, orderdt,
wkflag2, wkflag3,
crtdt, crt_user, pgm_id, enddt,
MDN_NO,LOCATION_CODE,
END_PERIOD, GRACE_PERIOD, CALL_USED_PERIOD,ENCRYPT_PIN)
VALUES
(TO_CHAR(vi_pin_hrn,'00000000000000'), TO_CHAR(vi_cardno,'000000000000'), '1', vi_issueseq, to_date(vs_issuedt, 'yyyy-mm-dd'),
vs_user_id , '', vs_price_model, vs_lotno,
vs_balance, 0, 0, vs_balance,
vs_partner_prefix, 'N', vs_balance, vs_balance, vs_balance, '2', vs_remark,
'', '',
'', '',
sysdate, vs_user_id, vs_pgm_id, to_date(vs_limit, 'yyyy-mm-dd') ,
'', '0',
vs_end_period, vs_grace_period,vs_call_used_period,'');
EXCEPTION
WHEN OTHERS THEN
P_ERR_MSG := '006' || ',' || SUBSTR(SQLERRM,1,900) ;
RAISE E_USERERROR;
RETURN;
END ;
vi_pin_hrn := vi_pin_hrn + 1;
vi_cardno := vi_cardno + 1;
END LOOP;
COMMIT;
END LOOP;
EXCEPTION
WHEN E_USERERROR THEN
ROLLBACK;
P_ERR_MSG := V_ERRTITLE || ' : ' || SUBSTR(SQLERRM, 1, 150);
vo_out := vo_out;
WHEN E_INTERNALERROR THEN
ROLLBACK;
P_ERR_MSG := V_ERRTITLE || ' : ' || SUBSTR(SQLERRM, 1, 150);
WHEN OTHERS THEN
ROLLBACK;
P_ERR_MSG := V_ERRTITLE || ' : ' || SUBSTR(SQLERRM, 1, 150);
COMMIT;
END P0_PRC_CREATE_PINMKDB;
-- show err procedure sp_card_create;
SELECT * FROM P_CARDMST WHERE CONTNO = '200000019224'
SELECT MAX(CONTNO) FROM P_CARDMST
SELECT * FROM P_CARDMAST ORDER BY PID DESC
SELECT MIN(CONTNO), MAX(CONTNO) FROM P_CARDMAST
WHERE ISSUESEQ = 53
DELETE FROM P_CARDMAST
truncate table P_ISSUEHOTLOG
SELECT * FROM P_ISSUEHOTLOG
ORDER BY ISSUESEQ DESC
DELETE FROM P_ISSUEHOTLOG
Posted by Julyus
