Forum Linux.débutant changement de date

<<--select * from sysdirm where system_key ='ASMINBILL'

----Sauvegarder la table bill/call/gprs(creation de table sav)du mois de facturation

---Table call 
CREATE TABLE EPPIX.call_hyb20180301_bkp20180301
AS
SELECT * FROM EPPIX.call_hyb20180301; 

---Table bill 
CREATE  TABLE EPPIX.bill_hyb20180301_bkp20180301
AS
SELECT * FROM EPPIX.bill_hyb20180301;

---Table GPRS
CREATE TABLE EPPIX.gprs_hyb20180301_bkp20180301
AS
SELECT * FROM EPPIX.gprs_hyb20180301;


---Table CYCLE

CREATE TABLE EPPIX.cycle20180301_bkp20180301
AS
SELECT * FROM EPPIX.cycle;


--vider les tables bill , call et GPRS 

TRUNCATE TABLE EPPIX.bill_hyb20180301;
TRUNCATE TABLE EPPIX.gprs_hyb20180301;
TRUNCATE TABLE EPPIX.call_hyb20180301;
----------------------------
UPDATE eppix.sba_sub_aux sa 
set sa.sba_inv_account =(select s.sbd_bill_ac_no FROM eppix.sbd_sub_dets s
                          where sa.sba_subscriber_id=s.sbd_subscriber_id
                          and sa.sba_inv_account!= s.sbd_bill_ac_no)
where sa.sba_subscriber_id in (SELECT sd.sbd_subscriber_id 
From eppix.sbd_sub_dets sd INNER JOIN eppix.sba_sub_aux sa ON sa.sba_subscriber_id=sd.sbd_subscriber_id
WHERE sa.sba_inv_account!= sbd_bill_ac_no);

commit;
--------------------------------
update eppix.aux_customers
set sr_flag = 'C'
where bill_ac_no in (
select distinct sbd_bill_ac_no from eppix.sbd_sub_dets
where (sbd_package_code like 'PM%'
or sbd_package_code like 'FM%'
or sbd_package_code like 'PX%'
or sbd_package_code like 'FX%')
and sbd_bill_cycle in ('HYB')
and sr_flag != 'C');

commit ;

Delete from eppix.udv_userdoc_deliv
Where udv_subscriber_id is not null
And udv_subscriber_id in
(select sbd_subscriber_id from eppix.sbd_sub_dets,eppix.aux_customers
Where bill_ac_no=sbd_bill_ac_no
And sbd_bill_cycle in('HYB')
And sr_flag='C') ;

commit ;

Insert into eppix.udv_userdoc_deliv(udv_subscriber_id,udv_doc_type,udv_delivery,udv_format,udv_address_type,udv_frequency,udv_suspend,udv_period_count,udv_print_arrears)
Select distinct sba_subscriber_id,'INVOICE','PRINT','PDF','BILL',1,'N',0,'N'from eppix.sba_sub_aux
Where not exists(select * from eppix.udv_userdoc_deliv where udv_doc_type='INVOICE' and sba_subscriber_id=udv_subscriber_id)
And sba_subscriber_id in
(select sbd_subscriber_id from eppix.sbd_sub_dets,eppix.aux_customers
Where bill_ac_no=sbd_bill_ac_no
And sbd_bill_cycle in('HYB')
And sr_flag='S');

Insert into eppix.udv_userdoc_deliv(udv_subscriber_id,udv_doc_type,udv_delivery,udv_format,udv_address_type,udv_frequency,udv_suspend,udv_period_count,udv_print_arrears)
Select distinct sba_subscriber_id,'ITEMISED','PRINT','PDF','BILL',1,'N',0,'N'from eppix.sba_sub_aux
Where not exists(select * from eppix.udv_userdoc_deliv where udv_doc_type='ITEMISED' and sba_subscriber_id=udv_subscriber_id)
And  sba_subscriber_id in
(select sbd_subscriber_id from eppix.sbd_sub_dets,eppix.aux_customers
Where bill_ac_no=sbd_bill_ac_no
And sbd_bill_cycle in('HYB')
And sr_flag='S');

Insert into eppix.udv_userdoc_deliv(udv_bill_ac_no,udv_doc_type,udv_delivery,udv_format,udv_address_type,udv_frequency,udv_suspend,udv_period_count,udv_print_arrears)
Select distinct sba_inv_account,'INVOICE','PRINT','PDF','BILL',1,'N',0,'N' from eppix.sba_sub_aux
Where not exists(select * from eppix.udv_userdoc_deliv where udv_doc_type='INVOICE' and sba_inv_account=udv_bill_ac_no)
And sba_inv_account in
(select distinct sbd_bill_ac_no from eppix.sbd_sub_dets,eppix.aux_customers
Where sbd_bill_ac_no=bill_ac_no
And sbd_bill_cycle in ('HYB')
And sr_flag='C');

Insert into eppix.udv_userdoc_deliv(udv_bill_ac_no,udv_doc_type,udv_delivery,udv_format,udv_address_type,udv_frequency,udv_suspend,udv_period_count,udv_print_arrears)
Select distinct sba_inv_account,'ITEMISED','PRINT','PDF','BILL',1,'N',0,'N' from eppix.sba_sub_aux
Where not exists(select * from eppix.udv_userdoc_deliv where udv_doc_type='ITEMISED' and sba_inv_account=udv_bill_ac_no)
And sba_inv_account in
(select distinct sbd_bill_ac_no from eppix.sbd_sub_dets,aux_customers
Where sbd_bill_ac_no=bill_ac_no
And sbd_bill_cycle in('HYB')
5
And sr_flag='C');

commit;

--------------------------------------- Mise à jour date -----
UPDATE eppix.VSM_SERVICE_MSISDN
SET vsm_billed_upto = TO_DATE('2018-02-28','YYYY-MM-DD')-- Update here last day of month  # ça doit mettre le dernier jour du mois <<dernier jour du mois M-1>> <<la facturation se fait à M+1>>
WHERE vsm_subscriber_id IN(
                        Select distinct sbd_subscriber_id from eppix.sbd_sub_dets ,vas_active_service vas
                         Where vas_subscriber_id =sbd_subscriber_id
                         and sbd_bill_cycle='HYB'
                         and vas_sub_charge1 is not null
                         and vas_sub_charge1 != ' ');      
commit;

----------------------------------------Mise à jour service SPD
update vas_active_service set vas_sub_charge1=vas_service_code where  vas_service_code like 'SPD%' and  vas_service_code!=vas_sub_charge1  
commit; >>

Bonsoir à tous et à toutes je viens par ce message solliciter votre aide.
Je voudrais écrit un script qui me permet de créer deux variables date :

1) une première variable date dont la valeur va me permettre de changer ma date à ce niveau.

<<---Table call 
CREATE TABLE EPPIX.call_hyb20180301_bkp20180301 AS SELECT * FROM EPPIX.call_hyb20180301; 

---Table bill 
CREATE  TABLE EPPIX.bill_hyb20180301_bkp20180301 AS SELECT * FROM EPPIX.bill_hyb20180301;

---Table GPRS
CREATE TABLE EPPIX.gprs_hyb20180301_bkp20180301 AS SELECT * FROM EPPIX.gprs_hyb20180301;

---Table CYCLE

CREATE TABLE EPPIX.cycle20180301_bkp20180301 AS SELECT * FROM EPPIX.cycle;

--vider les tables bill , call et GPRS 

TRUNCATE TABLE EPPIX.bill_hyb20180301;
TRUNCATE TABLE EPPIX.gprs_hyb20180301;
TRUNCATE TABLE EPPIX.call_hyb20180301;>>

c'est à dire la variable date change seulement l'année et le mois en chaque année en cours et chaque mois en cours mais ne touche pas le jour qui est 01 lui reste intacte.

2) La seconde variable date dont la valeur va me permettre de changer ma date à ce niveau

<<UPDATE eppix.VSM_SERVICE_MSISDN
SET vsm_billed_upto = TO_DATE('2018-02-28','YYYY-MM-DD')-- Update here last day of month  # ça doit mettre le dernier jour du mois <<dernier jour du mois M-1>> <<la facturation se fait à M+1>>
WHERE vsm_subscriber_id IN(
                        Select distinct sbd_subscriber_id from eppix.sbd_sub_dets ,vas_active_service vas
                         Where vas_subscriber_id =sbd_subscriber_id
                         and sbd_bill_cycle='HYB'
                         and vas_sub_charge1 is not null
                         and vas_sub_charge1 != ' ');      
commit; >>

C'est à dire que la valeur de ma deuxième variable date me permettra de changer la date de mon fichier chaque fin de mois.

le tout sera réorienté dans un fichier.sql
bonne nuit à vous et merci d'avancean