/* doc dtl start*/CREATE TABLE temp_table AS SELECT doc_data.doc_dtl_id temp_doc_dtl_id,charge_data.storage_from_date temp_from_date,charge_data.storage_to_date temp_to_date FROM nbis_doc_dtl_data doc_data,nbis_doc_dtl doc_dtl,nbis_charge_dtl charge_dtl,nbis_charge_data charge_data WHERE doc_data.doc_dtl_id = doc_dtl.id AND doc_data.charge_reference_id = charge_dtl.id AND charge_dtl.charge_data_id = charge_data.id AND doc_dtl.storage_from_date_cal IS NULL AND doc_dtl.storage_to_date_cal IS NULL AND charge_data.storage_from_date IS NOT NULL AND charge_data.storage_to_date IS NOT NULL;
INSERT INTO temp_table SELECT doc_data.doc_dtl_id temp_doc_dtl_id,charge_dtl.from_date temp_from_date,charge_dtl.to_date temp_to_date FROM nbis_doc_dtl_data doc_data,nbis_doc_dtl doc_dtl,nbis_man_charge_dtl charge_dtl WHERE doc_data.doc_dtl_id = doc_dtl.id AND doc_data.manual_charge_reference_id = charge_dtl.id AND doc_dtl.storage_from_date_cal IS NULL AND doc_dtl.storage_to_date_cal IS NULL AND charge_dtl.from_date IS NOT NULL AND charge_dtl.to_date IS NOT NULL; INSERT INTO temp_table SELECT doc_dtl.id temp_doc_dtl_id,charge_data.storage_from_date temp_from_date,charge_data.storage_to_date temp_to_date FROM nbis_doc_dtl doc_dtl,nbis_doc_dtl_data doc_data,nbis_man_charge_dtl man_charge_dtl ,nbis_man_charge_dtl_data man_charge_dtl_data,nbis_charge_dtl charge_dtl,nbis_charge_data charge_data WHERE doc_data.doc_dtl_id = doc_dtl.id AND doc_data.manual_charge_reference_id = man_charge_dtl.id AND man_charge_dtl_data.manual_charge_dtl_id = man_charge_dtl.id AND man_charge_dtl_data.charge_reference_id = charge_dtl.id AND charge_dtl.charge_data_id = charge_data.id AND doc_dtl.storage_from_date_cal IS NULL AND doc_dtl.storage_to_date_cal IS NULL AND charge_data.storage_from_date IS NOT NULL AND charge_data.storage_to_date IS NOT NULL; INSERT INTO temp_table SELECT doc_dtl.id temp_doc_dtl_id,charge_data.storage_from_date temp_from_date,charge_data.storage_to_date temp_to_date FROM nbis_doc_dtl doc_dtl,nbis_doc_dtl_data doc_data,nbis_credit_charge_dtl cre_charge_dtl ,nbis_credit_charge_dtl_data cre_charge_dtl_data,nbis_charge_dtl charge_dtl,nbis_charge_data charge_data WHERE doc_data.doc_dtl_id = doc_dtl.id AND doc_data.credit_charge_reference_id = cre_charge_dtl.id AND cre_charge_dtl_data.credit_charge_dtl_id = cre_charge_dtl.id AND cre_charge_dtl_data.charge_reference_id = charge_dtl.id AND doc_dtl.storage_from_date_cal IS NULL AND doc_dtl.storage_to_date_cal IS NULL AND charge_data.storage_from_date IS NOT NULL AND charge_data.storage_to_date IS NOT NULL; INSERT INTO temp_table SELECT doc_dtl.id temp_doc_dtl_id,charge_dtl.from_date temp_from_date,charge_dtl.to_date temp_to_date FROM nbis_doc_dtl doc_dtl,nbis_doc_dtl_data doc_data,nbis_man_charge_dtl man_charge_dtl ,nbis_man_charge_dtl_data man_charge_dtl_data,nbis_man_charge_dtl charge_dtl WHERE doc_data.doc_dtl_id = doc_dtl.id AND doc_data.manual_charge_reference_id = man_charge_dtl.id AND man_charge_dtl_data.manual_charge_dtl_id = man_charge_dtl.id AND man_charge_dtl_data.manual_charge_reference_id = charge_dtl.id AND doc_dtl.storage_from_date_cal IS NULL AND doc_dtl.storage_to_date_cal IS NULL AND charge_dtl.from_date IS NOT NULL AND charge_dtl.to_date IS NOT NULL;INSERT INTO temp_table SELECT doc_dtl.id temp_doc_dtl_id,charge_dtl.from_date temp_from_date,charge_dtl.to_date temp_to_date FROM nbis_doc_dtl doc_dtl,nbis_doc_dtl_data doc_data,nbis_credit_charge_dtl cre_charge_dtl ,nbis_credit_charge_dtl_data cre_charge_dtl_data,nbis_man_charge_dtl charge_dtl WHERE doc_data.doc_dtl_id = doc_dtl.id AND doc_data.credit_charge_reference_id = cre_charge_dtl.id AND cre_charge_dtl_data.credit_charge_dtl_id = cre_charge_dtl.id AND cre_charge_dtl_data.manual_charge_reference_id = charge_dtl.id AND doc_dtl.storage_from_date_cal IS NULL AND doc_dtl.storage_to_date_cal IS NULL AND charge_dtl.from_date IS NOT NULL AND charge_dtl.to_date IS NOT NULL;INSERT INTO temp_table SELECT doc_dtl.id temp_doc_dtl_id,recal_doc_dtl.from_date temp_from_date,recal_doc_dtl.to_date temp_to_date FROM nbis_doc_dtl doc_dtl,nbis_doc_dtl_data doc_data,nbis_man_charge_dtl man_charge_dtl ,nbis_man_charge_dtl_data man_charge_dtl_data, nbis_recal_doc_dtl_data recal_doc_dtl_data,nbis_recal_doc_dtl recal_doc_dtl WHERE doc_data.doc_dtl_id = doc_dtl.id AND doc_data.manual_charge_reference_id = man_charge_dtl.id AND man_charge_dtl_data.manual_charge_dtl_id = man_charge_dtl.id AND recal_doc_dtl_data.manual_charge_data_dtl_gen_id = man_charge_dtl_data.id AND recal_doc_dtl.id = recal_doc_dtl_data.recal_doc_dtl_id AND doc_dtl.storage_from_date_cal IS NULL AND doc_dtl.storage_to_date_cal IS NULL AND recal_doc_dtl.from_date IS NOT NULL AND recal_doc_dtl.to_date IS NOT NULL;INSERT INTO temp_table SELECT doc_dtl.id temp_doc_dtl_id,recal_doc_dtl.from_date temp_from_date,recal_doc_dtl.to_date temp_to_date FROM nbis_doc_dtl doc_dtl,nbis_doc_dtl_data doc_data,nbis_credit_charge_dtl cre_charge_dtl ,nbis_credit_charge_dtl_data cre_charge_dtl_data, nbis_recal_doc_dtl_data recal_doc_dtl_data,nbis_recal_doc_dtl recal_doc_dtl WHERE doc_data.doc_dtl_id = doc_dtl.id AND doc_data.credit_charge_reference_id = cre_charge_dtl.id AND cre_charge_dtl_data.credit_charge_dtl_id = cre_charge_dtl.id AND recal_doc_dtl_data.credit_chrage_data_dtl_gen_id = cre_charge_dtl_data.id AND recal_doc_dtl.id = recal_doc_dtl_data.recal_doc_dtl_id AND doc_dtl.storage_from_date_cal IS NULL AND doc_dtl.storage_to_date_cal IS NULL AND recal_doc_dtl.from_date IS NOT NULL AND recal_doc_dtl.to_date IS NOT NULL;UPDATE nbis_doc_dtl dtl SET (dtl.storage_from_date_cal,dtl.storage_to_date_cal) = (SELECT DISTINCT temp.temp_from_date,temp.temp_to_date FROM temp_table temp WHERE dtl.id = temp.temp_doc_dtl_id)WHERE dtl.storage_from_date_cal IS NULLAND dtl.storage_to_date_cal IS NULL;
UPDATE nbis_doc_dtl_adj dtl_adj SET (dtl_adj.tier_from_date,dtl_adj.tier_to_date) = (SELECT DISTINCT temp.temp_from_date,temp.temp_to_date FROM temp_table temp WHERE dtl_adj.doc_dtl_id = temp.temp_doc_dtl_id)WHERE dtl_adj.tier_from_date IS NULLAND dtl_adj.tier_to_date IS NULL;
DROP TABLE temp_table;/* doc end*/
/* recal start*/UPDATE nbis_recal_doc_dtl dtl SET (storage_from_date_cal,storage_to_date_cal) = ( SELECT DISTINCT doc_dtl.storage_from_date_cal,doc_dtl.storage_to_date_cal FROM nbis_recal_doc_dtl recal_dtl,nbis_doc_dtl doc_dtl WHERE recal_dtl.id = doc_dtl.recal_doc_dtl_id AND recal_dtl.storage_from_date_cal IS NULL AND recal_dtl.storage_to_date_cal IS NULL AND doc_dtl.storage_from_date_cal IS NOT NULL AND doc_dtl.storage_to_date_cal IS NOT NULL AND recal_dtl.id = dtl.id )WHERE dtl.storage_from_date_cal IS NULL AND dtl.storage_to_date_cal IS NULL;
UPDATE nbis_recal_doc_dtl_adj recal_adj SET(recal_adj.tier_from_date,recal_adj.tier_to_date) = ( SELECT DISTINCT recal_dtl.storage_from_date_cal,recal_dtl.storage_to_date_cal FROM nbis_recal_doc_dtl recal_dtl WHERE recal_dtl.storage_from_date_cal IS NOT NULL AND recal_dtl.storage_to_date_cal IS NOT NULL AND recal_adj.recal_doc_dtl_id = recal_dtl.id )WHERE recal_adj.tier_from_date IS NULLAND recal_adj.tier_to_date IS NULL;
/* recal end*/
/* credit charge start*/CREATE TABLE temp_credit_table AS SELECT cre_dtl.id temp_cre_dtl_id,charge_data.storage_from_date temp_from_date,charge_data.storage_to_date temp_to_date FROM nbis_credit_charge_dtl cre_dtl,nbis_credit_charge_dtl_data cre_data, nbis_charge_dtl charge_dtl,nbis_charge_data charge_data WHERE cre_dtl.id = cre_data.credit_charge_dtl_id AND cre_data.charge_reference_id = charge_dtl.id AND charge_dtl.charge_data_id = charge_data.id AND cre_dtl.storage_from_date_cal IS NULL AND cre_dtl.storage_to_date_cal IS NULL AND charge_data.storage_from_date IS NOT NULL AND charge_data.storage_to_date IS NOT NULL;
INSERT INTO temp_credit_table SELECT cre_dtl.id temp_cre_dtl_id,man_charge_dtl.from_date temp_from_date,man_charge_dtl.to_date temp_to_date FROM nbis_credit_charge_dtl cre_dtl,nbis_credit_charge_dtl_data cre_data,nbis_man_charge_dtl man_charge_dtl WHERE cre_dtl.id = cre_data.credit_charge_dtl_id AND cre_data.manual_charge_reference_id = man_charge_dtl.id AND cre_dtl.storage_from_date_cal IS NULL AND cre_dtl.storage_to_date_cal IS NULL AND man_charge_dtl.from_date IS NOT NULL AND man_charge_dtl.to_date IS NOT NULL;
INSERT INTO temp_credit_table SELECT cre_dtl.id temp_cre_dtl_id,recal_doc_dtl.from_date temp_from_date,recal_doc_dtl.to_date temp_to_date FROM nbis_credit_charge_dtl cre_dtl,nbis_credit_charge_dtl_data cre_data, nbis_recal_doc_dtl_data recal_doc_dtl_data,nbis_recal_doc_dtl recal_doc_dtl WHERE cre_dtl.id = cre_data.credit_charge_dtl_id AND recal_doc_dtl_data.credit_chrage_data_dtl_gen_id = cre_data.id AND recal_doc_dtl.id = recal_doc_dtl_data.recal_doc_dtl_id AND cre_dtl.storage_from_date_cal IS NULL AND cre_dtl.storage_to_date_cal IS NULL AND recal_doc_dtl.from_date IS NOT NULL AND recal_doc_dtl.to_date IS NOT NULL;INSERT INTO temp_credit_table SELECT cre_dtl.id temp_cre_dtl_id,doc_dtl.from_date temp_from_date,doc_dtl.to_date temp_to_date FROM nbis_credit_charge_dtl cre_dtl,nbis_credit_charge_dtl_data cre_data,nbis_doc_dtl doc_dtl WHERE cre_dtl.id = cre_data.credit_charge_dtl_id AND cre_data.doc_dtl_id = doc_dtl.id AND cre_dtl.storage_from_date_cal IS NULL AND cre_dtl.storage_to_date_cal IS NULL AND doc_dtl.from_date IS NOT NULL AND doc_dtl.to_date IS NOT NULL;UPDATE nbis_credit_charge_dtl dtl SET (dtl.storage_from_date_cal,dtl.storage_to_date_cal) = (SELECT DISTINCT temp.temp_from_date,temp.temp_to_date FROM temp_credit_table temp WHERE dtl.id = temp.temp_cre_dtl_id)WHERE dtl.storage_from_date_cal IS NULLAND dtl.storage_to_date_cal IS NULL;
UPDATE nbis_credit_charge_dtl_adj dtl_adj SET (dtl_adj.tier_from_date,dtl_adj.tier_to_date) = (SELECT DISTINCT temp.temp_from_date,temp.temp_to_date FROM temp_credit_table temp WHERE dtl_adj.credit_charge_dtl_id = temp.temp_cre_dtl_id)WHERE dtl_adj.tier_from_date IS NULLAND dtl_adj.tier_to_date IS NULL;
DROP TABLE temp_credit_table; /* credit charge end*/ /*
manual charge start
*/ CREATE TABLE temp_manual_table AS SELECT man_charge_dtl.id temp_man_dtl_id,charge_data.storage_from_date temp_from_date,charge_data.storage_to_date temp_to_date FROM nbis_man_charge_dtl man_charge_dtl,nbis_man_charge_dtl_data man_charge_data,nbis_man_charge_hdr man_charge_hdr, nbis_charge_dtl charge_dtl,nbis_charge_data charge_data WHERE man_charge_hdr.id = man_charge_dtl.man_charge_hdr_id AND man_charge_dtl.id = man_charge_data.manual_charge_dtl_id AND man_charge_data.charge_reference_id = charge_dtl.id AND charge_dtl.charge_data_id = charge_data.id AND man_charge_hdr.charge_cat = 'SP' AND man_charge_dtl.storage_from_date_cal IS NULL AND man_charge_dtl.storage_to_date_cal IS NULL AND charge_data.storage_from_date IS NOT NULL AND charge_data.storage_to_date IS NOT NULL;INSERT INTO temp_manual_table SELECT man_charge_dtl.id temp_man_dtl_id,ref_man_charge_dtl.from_date temp_from_date,ref_man_charge_dtl.to_date temp_to_date FROM nbis_man_charge_dtl man_charge_dtl,nbis_man_charge_dtl_data man_charge_data,nbis_man_charge_hdr man_charge_hdr, nbis_man_charge_dtl ref_man_charge_dtl WHERE man_charge_hdr.id = man_charge_dtl.man_charge_hdr_id AND man_charge_dtl.id = man_charge_data.manual_charge_dtl_id AND man_charge_data.manual_charge_reference_id = ref_man_charge_dtl.id AND man_charge_hdr.charge_cat = 'SP' AND man_charge_dtl.storage_from_date_cal IS NULL AND man_charge_dtl.storage_to_date_cal IS NULL AND ref_man_charge_dtl.from_date IS NOT NULL AND ref_man_charge_dtl.to_date IS NOT NULL;INSERT INTO temp_manual_table SELECT man_charge_dtl.id temp_man_dtl_id,from_date temp_from_date,to_date temp_to_date FROM nbis_man_charge_dtl man_charge_dtl,nbis_man_charge_hdr man_charge_hdr WHERE man_charge_hdr.id = man_charge_dtl.man_charge_hdr_id AND man_charge_hdr.charge_cat = 'MC' AND storage_from_date_cal IS NULL AND storage_to_date_cal IS NULL AND from_date IS NOT NULL AND to_date IS NOT NULL;UPDATE nbis_man_charge_dtl dtl SET (dtl.storage_from_date_cal,dtl.storage_to_date_cal) = (SELECT DISTINCT temp.temp_from_date,temp.temp_to_date FROM temp_manual_table temp WHERE dtl.id = temp.temp_man_dtl_id)WHERE dtl.storage_from_date_cal IS NULLAND dtl.storage_to_date_cal IS NULL;
UPDATE nbis_man_charge_dtl_adj dtl_adj SET (dtl_adj.tier_from_date,dtl_adj.tier_to_date) = (SELECT DISTINCT temp.temp_from_date,temp.temp_to_date FROM temp_manual_table temp WHERE dtl_adj.man_charge_dtl_id = temp.temp_man_dtl_id)WHERE dtl_adj.tier_from_date IS NULLAND dtl_adj.tier_to_date IS NULL;
DROP TABLE temp_manual_table;/*
manual charge end
*/
转载于:https://www.cnblogs.com/shouwangzhe-/p/3696610.html
