1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
|
create or replace
function sic_bordereau_cc (
journal in varchar2,
bord_prop in varchar2
)
return varchar2
is
bord_seq varchar2(8) := to_char(sysdate,'yymm');
begin
-- controle de la possibilité de créer un bordereau sur le journal
select
bord_seq||to_char(jm_inde+1,'FM0000')
into bord_seq
from jourmens@dlpremcc.sic.intra
where
soc='01'
and jm_code=journal||bord_seq
and jm_exer=to_char(sysdate,'yyyy')
and jm_clot!='1';
-- si le controle est bon alors BORD_SEQ contient la sequence
if bord_seq is not null
then
-- mise à jour de JOURMENS pour conserver l'indice de la séquence
update jourmens@dlpremcc.sic.intra
set jm_inde=jm_inde+1
where
soc='01'
and jm_code=journal||substr(bord_seq,1,4)
and jm_exer=to_char(sysdate,'yyyy')
and jm_clot!='1';
commit;
-- création de l'enregistrement dans la liste des bordereaux
insert into bordereau@dlpremcc.sic.intra (bo_date, bo_ccmp, bo_prop, bo_clot, bo_edit, soc, bo_modu) values
(sysdate, journal||bord_seq, bord_prop, 0, 0, '01', 'ccecri');
commit;
-- création de la table du bordereau
sic_commande_directe@dlpremcc.sic.intra (
'create table s9881_cc_00.XB01'||journal||bord_seq||
'(
"BP_DATE" DATE NOT NULL ENABLE,
"BP_ECRI" VARCHAR2(16 BYTE) NOT NULL ENABLE,
"BP_LIBE" VARCHAR2(80 BYTE) NOT NULL ENABLE,
"BP_PIEC" VARCHAR2(2 BYTE),
"BP_DOSS" VARCHAR2(6 BYTE),
"BP_CMPT" VARCHAR2(12 BYTE) NOT NULL ENABLE,
"BP_BUDG" VARCHAR2(15 BYTE),
"BP_DEBI" NUMBER(16,2),
"BP_CRED" NUMBER(16,2),
"BP_ANLY" VARCHAR2(14 BYTE) NOT NULL ENABLE,
"BP_DATES" DATE NOT NULL ENABLE,
"BP_SENS" VARCHAR2(1 BYTE),
"BP_FACT" VARCHAR2(8 BYTE),
"BP_RECE" VARCHAR2(29 BYTE),
"BP_BANQ" VARCHAR2(5 BYTE),
"BP_LIBBANQ" VARCHAR2(30 BYTE),
"BP_GUIC" VARCHAR2(5 BYTE),
"BP_CPTE" VARCHAR2(11 BYTE),
"BP_CLE" VARCHAR2(2 BYTE),
"BP_BENEF" VARCHAR2(50 BYTE),
"BP_CCP" VARCHAR2(1 BYTE),
"BP_BORD" VARCHAR2(2 BYTE),
"DV_CODE" VARCHAR2(1 BYTE),
"BP_ADR1" VARCHAR2(40 BYTE),
"BP_ADR2" VARCHAR2(40 BYTE),
"BP_CP" VARCHAR2(5 BYTE),
"BP_VILLE" VARCHAR2(40 BYTE),
"BP_CHEQUE" VARCHAR2(20 BYTE),
"BP_PIECE" VARCHAR2(6 BYTE),
"BP_ECRI_DEST" VARCHAR2(16 BYTE),
"BP_ECRI_ORIG" VARCHAR2(16 BYTE),
"BP_REG" VARCHAR2(1 BYTE),
"BP_DOSSIER" VARCHAR2(10 BYTE),
"BP_CMPT_ORI" VARCHAR2(12 BYTE),
"BP_EXER_EXT_MODIF" VARCHAR2(1 BYTE),
"BP_PROG" VARCHAR2(6 BYTE),
"BP_LOT" VARCHAR2(4 BYTE),
"BP_RIB_MANDAT" VARCHAR2(35 BYTE)
)'
);
end if;
return bord_seq;
end; |
