/*
Server:		samnb705db01.amavita.ch
Format:		GCM 
Business:	TPPHAR
type:		VALI
Version:	23.1.21006.00066

24.06.2024, TSC
*/
use Arizona_delphix

select Organizational_unit_ID, OU_subsidiary, OU_system_site, OU_profit_cost_center, OU_address, OU_code, OU_short_name, OU_type, OU_database_name, OU_TS, OU_email, OU_VGUID, OU_master_ID, OU_manager_address, OU_item_dispatch_type, OU_rowguid, OU_pharmacy_code
from Organizational_unit
where not exists(select 1 from dbo.address where address.tt_masking='persist' and address.Address_ID=OU_address)
or not exists(select 1 from dbo.address where address.tt_masking='persist' and address.Address_ID=OU_manager_address)

select PH_insurance_GUID, PHIN_address, PHIN_key, PHIN_EAN_key, PHIN_type, PHIN_invoice_type, PHIN_name_french, PHIN_name_german, PHIN_name_italian, PHIN_slogan_french, PHIN_slogan_german, PHIN_slogan_italian, PHIN_type_of_proof, PHIN_plan_LS, PHIN_plan_HL, PHIN_plan_MC, PHIN_plan_LPPA, PHIN_plan_FHLN, PHIN_check_physician_number, PHIN_address_1, PHIN_address_2, PHIN_zip_code, PHIN_location, PHIN_language, PHIN_telephone, PHIN_fax, PHIN_IN_minimum_length, PHIN_IN_maximum_length, PHIN_IN_type, PHIN_IN_alignment, PHIN_IN_start_position, PHIN_IN_end_position, PHIN_IN_check_digit_ref, PHIN_ISO_key, PHIN_section_minimum_length, PHIN_section_maximum_length, PHIN_section_type, PHIN_section_alignment, PHIN_invoicing_method, PHIN_origin, PHIN_invoice_transmission, PHIN_APS_TS, PHIN_TS, PHIN_VGUID, PHIN_master_ID, PHIN_bill_to, PHIN_status, PHIN_renewable_bonus_factor, PHIN_insurance_bonus_factor, PHIN_recipient_EAN_key, PHIN_Invoice_delay, PHIN_Invoice_gap, PHIN_view_prescription_image, PHIN_hl_accept_limited_coverage, PHIN_mc_accept_limited_coverage, PHIN_code_model, PHIN_benefit_number, PHIN_tp_min_amount, PHIN_tp_min_amount_invoice, PHIN_assignment_of_claim, PHIN_rowguid, PHIN_pharmacy_code, PHIN_contract_name
from PH_insurance
where not exists(select 1 from dbo.address where address.tt_masking='persist' and address.Address_ID=PHIN_address)

select PH_organizational_unit_GUID, PHOU_organizational_unit, PHOU_manager_address, PHOU_galenica_ID, PHOU_ofac_ID, PHOU_EAN_number, PHOU_contract_number, PHOU_ofac_invoice_limit, PHOU_manager_data_to_print, PHOU_TS, PHOU_APS_TS, PHOU_VGUID, PHOU_master_ID, PHOU_ofac_invoice_alert, PHOU_ofac_max_documents, PHOU_posology_label_header, PHOU_IMS_ID, PHOU_sales_insurance_check, PHOU_loyalty_ID, PHOU_NIF, PHOU_IMS_ID_DP, PHOU_distribution_center, PHOU_AB_level, PHOU_AB_schedule_day_of_month, PHOU_AB_schedule_month, PHOU_AB_schedule_day_of_week, PHOU_AB_closing_mode, PHOU_AB_delivery_delay, PHOU_rowguid, PHOU_pharmacy_code, PHOU_EPD_provider, PHOU_OID, PHOU_mediproc_nursing_home
from PH_Organizational_unit
where not exists(select 1 from dbo.address where address.tt_masking='persist' and address.Address_ID=PHOU_manager_address)
or not exists(select 1 from dbo.address where address.tt_masking='persist' and address.Address_ID=PHOU_distribution_center)

select PH_prescriber_GUID, PHPR_address, PHPR_contract_number, PHPR_EAN_number, PHPR_remark, PHPR_origin, PHPR_APS_TS, PHPR_TS, PHPR_VGUID, PHPR_master_ID, PHPR_status, PHPR_store_remark, PHPR_spa_sequence, PHPR_deactivation_date, PHPR_tiers_garant_only, PHPR_contract_number_state
from PH_prescriber
where not exists(select 1 from dbo.address where address.tt_masking='persist' and address.Address_ID=PHPR_address)

select Incident_header_ID, INH_subsidiary, INH_received_by, INH_originator_address, INH_default_incident_type, INH_incident_number, INH_caller_name, INH_call_date, INH_TS, INH_VGUID, INH_master_ID, INH_contact_address, INH_caller_number
from Incident_header
where not exists(select 1 from dbo.address where address.tt_masking='persist' and address.Address_ID=INH_contact_address)
or not exists(select 1 from dbo.address where address.tt_masking='persist' and address.Address_ID=INH_originator_address)

select Serial_number_history_ID, SNH_serial_number, SNH_serial_number_status, SNH_address, SNH_document_line, SNH_value_date, SNH_remark, SNH_APS_TS, SNH_TS, SNH_VGUID, SNH_master_ID
from Serial_number_history
where not exists(select 1 from dbo.address where address.tt_masking='persist' and address.Address_ID=SNH_address)

select Item_relation_info_ID, ITRI_item, ITRI_address, ITRI_address_type, ITRI_BOM_option_header, ITRI_process_option, ITRI_address_item_number, ITRI_text, ITRI_barcode, ITRI_legalized, ITRI_quality_control_remark, ITRI_ALT_weighting_factor, ITRI_certification, ITRI_remark, ITRI_APS_TS, ITRI_VGUID, ITRI_master_ID, ITRI_TS, ITRI_ALT_weighting_factor_unit, ITRI_delivery_delay, ITRI_order_weeks, ITRI_availability_status, ITRI_availability_value_date, ITRI_availability_origin, ITRI_availability_refresh_date, ITRI_availability_remark, ITRI_default_pharmacy_priority, ITRI_pharmacy_availability, ITRI_origin, ITRI_default_central_priority, ITRI_stock_info_code
from Item_relation_info
where not exists(select 1 from dbo.address where address.tt_masking='persist' and address.Address_ID=ITRI_address)

select Fixed_price_ID, FP_subsidiary, FP_tariff_type, FP_item_price_range, FP_item, FP_address_group, FP_address_category, FP_address, FP_currency, FP_price_code, FP_sales_tax_code, FP_SP_catalog, FP_start_date, FP_end_date, FP_tax_free_price_per_unit, FP_tax_free_min_price_per_unit, FP_tax_free_max_price_per_unit, FP_tax_incl_price_per_unit, FP_tax_incl_min_price_per_unit, FP_tax_incl_max_price_per_unit, FP_price_per_unit_factor, FP_price_per_unit_unit_code, FP_tax_free_flat_fee, FP_tax_incl_flat_fee, FP_flat_fee_quantity, FP_quantity, FP_unit_code, FP_final_discount_possible, FP_bonus_order_quantity, FP_bonus_delivery_quantity, FP_label_type, FP_discount_level_1_possible, FP_discount_level_2_possible, FP_origin, FP_manual_price, FP_remark, FP_APS_TS, FP_VGUID, FP_master_ID, FP_TS, FP_bonus_order_point_qty
from Fixed_price
where not exists(select 1 from dbo.address where address.tt_masking='persist' and address.Address_ID=FP_address)

select Tariff_ID, TF_subsidiary, TF_tariff_type, TF_item_price_range, TF_item, TF_address_group, TF_address_category, TF_address, TF_delivery_code, TF_payment_method, TF_SP_catalog, TF_marketing_activity_header, TF_assortment, TF_product_line, TF_quantity, TF_unit_code, TF_total_amount, TF_currency, TF_start_date, TF_end_date, TF_price_code, TF_discount_percentage_1, TF_discount_percentage_2, TF_margin_amount, TF_margin_factor, TF_rounding_precision, TF_price_type, TF_level, TF_final_discount_possible, TF_origin, TF_manual_tariff, TF_remark, TF_VGUID, TF_master_ID, TF_TS
from Tariff
where not exists(select 1 from dbo.address where address.tt_masking='persist' and address.Address_ID=TF_address)

select Asset_ID, AS_subsidiary, AS_depreciation_method, AS_asset_group, AS_asset_family, AS_asset_location, AS_supplier, AS_manufacturer, AS_owner, AS_entry, AS_number, AS_description, AS_remark, AS_serial_number, AS_short_description, AS_purchase_date, AS_purchase_amount, AS_implementation_date, AS_technical_life_time, AS_warranty_expiration_date, AS_supplier_reference, AS_inventory_number, AS_status, AS_number_of_ident_entities, AS_GL_initial_amount, AS_GL_assets_account, AS_assets_account, AS_GL_cost_account, AS_cost_account, AS_GL_deprec_type, AS_GL_deprec_SP, AS_GL_deprec_percentage, AS_GL_deprec_start_date, AS_GL_deprec_number_of_months, AS_GL_residual_amount_method, AS_GL_residual_percentage, AS_GL_residual_amount, AS_GL_deprec_end_date, AS_GL_deprec_account, AS_deprec_account, AS_GL_sinking_fund, AS_sinking_fund_account, AS_GL_quantity, AS_GL_quantity_unit_code, AS_GL_price_per_unit, AS_GL_price_factor, AS_GL_price_unit_code, AS_CA_initial_amount, AS_CA_deprec_type, AS_CA_deprec_SP, AS_CA_deprec_percentage, AS_CA_deprec_number_of_months, AS_CA_residual_amount_method, AS_CA_residual_percentage, AS_CA_residual_amount, AS_CA_deprec_start_date, AS_CA_deprec_end_date, AS_CA_cost_center, AS_cost_center, AS_CA_deprec_cost_center, AS_deprec_cost_center, AS_CA_deprec_cost_unit, AS_deprec_cost_type, AS_CA_sinking_fund_cost_unit, AS_sinking_fund_cost_type, AS_CA_investment_cost_unit, AS_investment_cost_type, AS_CA_quantity, AS_CA_quantity_unit_code, AS_CA_price_per_unit, AS_CA_price_factor, AS_CA_price_unit_code, AS_APS_TS, AS_VGUID, AS_master_ID, AS_TS, AS_GL_residual_balance_amount, AS_GL_residual_balance_percent, AS_CA_residual_balance_amount, AS_CA_residual_balance_percent
from Asset
where not exists(select 1 from dbo.address where address.tt_masking='persist' and address.Address_ID=AS_owner)
or not exists(select 1 from dbo.address where address.tt_masking='persist' and address.Address_ID=AS_supplier)
or not exists(select 1 from dbo.address where address.tt_masking='persist' and address.Address_ID=AS_manufacturer)

select Supplier_ID, SUPP_subsidiary, SUPP_address, SUPP_address_group, SUPP_address_category, SUPP_delivery_code, SUPP_shipping_method, SUPP_payment_terms, SUPP_payment_method, SUPP_sales_tax_code, SUPP_currency, SUPP_pay_to, SUPP_send_order_to, SUPP_default_invoicing_method, SUPP_customer_number, SUPP_order_notes_copies, SUPP_active, SUPP_active_remark, SUPP_remark, SUPP_tax_in_price, SUPP_payment_freezed, SUPP_print_payment_notice, SUPP_default_delivery_type, SUPP_bmc_user_profile, SUPP_creation_date, SUPP_VAT_method, SUPP_abc_code, SUPP_computed_abc_code, SUPP_delivery_note_sort_order, SUPP_VGUID, SUPP_master_ID, SUPP_TS, SUPP_ALT_weighting_factor, SUPP_default_backlog_type, SUPP_ALT_weighting_factor_unit, SUPP_element_requirement_term, SUPP_delivery_delay, SUPP_order_weeks, SUPP_backlog_processing_mode, SUPP_backlog_processing_delay, SUPP_external_number, SUPP_GLN_key, SUPP_COMP_key, SUPP_WHS_key
from Supplier
where not exists(select 1 from dbo.address where address.tt_masking='persist' and address.Address_ID=SUPP_pay_to)
or not exists(select 1 from dbo.address where address.tt_masking='persist' and address.Address_ID=SUPP_send_order_to)