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; |
Partager