/*
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 NULL
AND 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 NULL
AND 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 NULL
AND 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 NULL
AND 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 NULL
AND 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 NULL
AND 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 NULL
AND dtl_adj.tier_to_date IS NULL;
DROP TABLE temp_manual_table;
/*
manual charge end
*/