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
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
| CREATE OR REPLACE PACKAGE BODY essai
AS
gd_extract_date_deb DATE;
gd_extract_date_fin DATE;
--gv_filtre_lot VARCHAR2 (30);
gv_file_name VARCHAR2 (30);
PROCEDURE init
IS
BEGIN
cgey_tools_pkg.put_out_header;
--gv_filtre_lot := TO_CHAR (gd_extract_date, 'DDMMYY');
--Bich
gv_file_name := 'SATI-'|| -- gv_file_name := 'BICH-'
TO_CHAR (gd_extract_date_deb, 'YYYYMMDD') ||
'_'||
TO_CHAR (gd_extract_date_fin, 'YYYYMMDD') ||
'.txt';
EXCEPTION
WHEN OTHERS
THEN
cgey_errors_pkg.log_error (SQLCODE, 'AP', 'ISOAPSATI01', 'init');
RAISE err_general;
END init;
---------------------------------------------------------------------------------------------
-- Nom : to_date_checkrun_name
-- Description : fonction qui va retourner la date contenu dans le checkrun_name
-- PARAMETRES : p_checkrun_name
--
--------------------------------------------------------------------------------------------
FUNCTION to_date_checkrun_name(p_checkrun_name IN VARCHAR2) RETURN DATE IS
BEGIN
RETURN to_date(substr(p_checkrun_name,-6),'ddmmyy');
EXCEPTION
WHEN OTHERS THEN
RETURN to_date('01/01/1950','dd/mm/yyyy');
END to_date_checkrun_name;
PROCEDURE lecture_donnees_sati_dst (
pv_errbuff IN OUT VARCHAR2,
pn_retcode IN OUT NUMBER,
pn_org_id IN NUMBER
)
IS
ln_compteur_dst NUMBER := 0;
vn_id_line_dst NUMBER;
lv_soc VARCHAR2 (10);
BEGIN
INSERT /*+ append */
INTO spr_isoapsati01_dst_tmp
(
check_id,
code_enregistrement,
code_operation,
numero_sequence,
societe_reglement,
numero_paiement,
code_banque_siga,
code_expedition,
categorie_fournisseur,
numero_fournisseur,
numero_tiers_paye,
code_devises,
nombre_decimales,
nom_destinataire,
adresse_1_dest,
adresse_2_dest,
code_postal_dest,
bureau_distributeur,
pays_destinataire,
raison_sociale_tiers_facture,
date_emission_reglement,
numero_lot
)
(SELECT DISTINCT aca.check_id,
'03', --CODE_ENREGISTREMENT AP_RG520--
DECODE (aca.payment_method_lookup_code, 'CHECK', 'C', 'CLEARING', 'L', (SELECT 'T' FROM dual WHERE aca.payment_method_lookup_code LIKE 'EFT%')), --CODE_OPERATION AP_RG503--
'000001', --NUMERO_SEQUENCE AP_RG504--
haou.attribute1, --SOCIETE_REGLEMENT AP_RG505--
LPAD (aca.check_number, 10, 0), --NUMERO_PAIEMENT
' ', --CODE_BANQUE_SIGA AP_RG506--
DECODE (pvsfact.country, 'FR', 0, 5), --CODE_EXPEDITION AP_RG507--
pv.vendor_type_lookup_code, --CATEGORIE_FOURNISSEUR AP_RG508--
RPAD (SUBSTR (pv.attribute1, 1, 9), 9), --NUMERO_FOURNISSEUR AP_RG509--
RPAD (SUBSTR (pvsfact.attribute1, 1, 10), 10), --NUMERO_TIERS_PAYE AP_RG510--
aca.currency_code, --CODE_DEVISES AP_RG511--
'2', --NOMBRE_DECIMALES
RPAD (UPPER (NVL (pvsfact.vendor_site_code_alt, ' ')), 32), --NOM_DESTINATAIRE AP_RG512--
RPAD (UPPER (NVL (pvsfact.address_line1, ' ')), 32), --ADRESSE_1_DEST AP_RG513--
RPAD (UPPER (NVL (pvsfact.address_line2, ' ')), 32), --ADRESSE_2_DEST AP_RG514--
RPAD (NVL (pvsfact.zip, ' '), 7), --CODE_POSTAL_DEST AP_RG515--
RPAD (UPPER (NVL (pvsfact.city, ' ')), 32), --BUREAU_DISTRIBUTEUR AP_RG516--
--LTR 01/03/07 nvl(pvsfact.country, 'FR'), --PAYS_DESTINATAIRE AP_RG517--
(SELECT RPAD (UPPER (territory_short_name), 32)
FROM fnd_territories_vl
WHERE territory_code = NVL (pvsfact.country, 'FR')), --PAYS_DESTINATAIRE AP_RG517-- --LTR 01/03/07
RPAD (UPPER (NVL (pvs.vendor_site_code_alt, ' ')), 32), --RAISON_SOCIALE_TIERS_FACTURE AP_RG518--
nvl((SELECT to_char(to_date(aisc.attribute1,'DDMMYY'),'DDMMYYYY')
FROM
ap_inv_selection_criteria_all aisc
WHERE
aisc.checkrun_name = aca.checkrun_name
and aisc.org_id = haou.organization_id
and aca.payment_method_lookup_code LIKE 'EFT%'),
TO_CHAR (aca.check_date, 'DDMMYYYY')), --DATE_EMISSION_REGLEMENT
'0000' --NUMERO_LOT AP_RG519--
FROM ap_checks_all aca,
ap_invoices_all aia,
ap_invoice_payments_all aipa,
po_vendors pv,
po_vendor_sites_all pvs,
po_vendor_sites_all pvsfact,
hr_all_organization_units haou,
spr_sapautorgt1_ids_tmp ids --Table du Composant SPR_SAPAUTORGT
WHERE '0' = '0'
AND pvs.vendor_id = pv.vendor_id
AND aia.vendor_id = pvs.vendor_id
AND aia.org_id = pvs.org_id
AND aia.org_id = pn_org_id
AND aia.vendor_site_id = pvs.vendor_site_id
AND aca.vendor_site_id = pvs.vendor_site_id
AND aca.vendor_id = pvs.vendor_id
AND aca.org_id = pvs.org_id
AND aca.org_id = pn_org_id
AND aca.check_id = aipa.check_id
AND aipa.invoice_id = aia.invoice_id
AND haou.organization_id = aca.org_id
AND pvsfact.vendor_site_id = NVL (aia.attribute1, pvs.vendor_site_id)
AND pv.vendor_type_lookup_code NOT LIKE 'EMPLOYEE%'
AND ids.invoice_id = aia.invoice_id
AND ids.organization_id = haou.organization_id
AND ids.payment_num = aipa.payment_num
AND aia.invoice_id = ids.invoice_id
--AND (ids.check_name IN ('FP Chèque SATI', 'FP LCR SATI', 'FP Transfert SATI') OR (ids.check_name LIKE 'FP Virement%'))
--AND aca.checkrun_name LIKE '%' || gv_filtre_lot || '%' --la campagne de reglement du jour
AND (ids.check_name IN (select flex_value from fnd_flex_values
where enabled_flag = 'Y'
and flex_value_set_id in (select flex_value_set_id from fnd_flex_value_sets
where flex_value_set_name = ln_format_sati)))
--AND aca.creation_date between '01/06/2007' and '11/07/2007'
AND to_date_checkrun_name(aca.checkrun_name) between gd_extract_date_deb and gd_extract_date_fin
AND aca.attribute3 is null
--
AND aca.status_lookup_code NOT LIKE 'VOIDED%' --Ne pas prendre les règlements Annulées
AND aca.amount <> 0);
ln_compteur_dst := SQL%ROWCOUNT;
COMMIT;
cgey_tools_pkg.put_log_message (
TO_CHAR (ln_compteur_dst) || ' lignes insérées dans la table temporaire spr_isoapsati01_dst_tmp'
);
cgey_tools_pkg.put_log_message ('Calcul statistiques sur spr_isoapsati01_dst_tmp');
EXECUTE IMMEDIATE ('Analyze table cgey.spr_isoapsati01_dst_tmp estimate statistics');
cgey_tools_pkg.put_log_message ('Fin d''extraction pour spr_isoapsati01_dst_tmp ');
EXCEPTION
WHEN NO_DATA_FOUND
THEN
cgey_tools_pkg.put_log_message (' Pas de données Ã* extraire pour la société : '|| pn_org_id);
WHEN err_sati
THEN
ROLLBACK;
pn_retcode := SQLCODE;
cgey_errors_pkg.log_error (SQLCODE, 'AP', 'ISOAPSATI01', 'Lecture_donnees_sati_dst');
WHEN OTHERS
THEN
ROLLBACK;
pn_retcode := SQLCODE;
cgey_errors_pkg.log_error (SQLCODE, 'AP', 'ISOAPSATI01', 'Lecture_donnees_sati_dst');
END lecture_donnees_sati_dst;
END essai; |
PLS-00103: Symbole "SELECT" rencontré à la place d'un des symboles suivants
Répondre avec citation
Partager