136 lines
6.7 KiB
Transact-SQL
136 lines
6.7 KiB
Transact-SQL
USE [Arizona]
|
|
|
|
SELECT TOP 10 *
|
|
FROM PHGD_ACXI
|
|
/*
|
|
CREATE TABLE [dbo].[PHGD_ACXI]
|
|
(
|
|
[PHGD_ACXI_GUID] [dbo].[GUID_identifier] NOT NULL,
|
|
[PHGD_ACXI_pharmacode] [dbo].[GUID_identifier] NULL,
|
|
[PHGD_ACXI_interaction] [dbo].[GUID_identifier] NULL,
|
|
[PHGD_ACXI_classification] [dbo].[Alpha_table_key] NULL,
|
|
[PHGD_ACXI_relevance] [dbo].[Small_integer] NULL,
|
|
[PHGD_ACXI_status] [dbo].[Small_integer] NULL,
|
|
[PHGD_ACXI_TS] [timestamp] NOT NULL,
|
|
[PHGD_ACXI_VGUID] [dbo].[VGUID_identifier] NULL CONSTRAINT [DF__PHGD_ACXI__PHGD___59CFA7C1] DEFAULT (newid()),
|
|
[PHGD_ACXI_master_ID] [dbo].[Large_name_field] NULL,
|
|
[PHGD_ACXI_PharmacodeNum] [dbo].[Small_alphabetical_field] NOT NULL
|
|
) ON [PRIMARY]
|
|
GO
|
|
ALTER TABLE [dbo].[PHGD_ACXI] ADD CONSTRAINT [PK__PHGD_ACXI__58DB8388] PRIMARY KEY NONCLUSTERED ([PHGD_ACXI_GUID]) WITH (FILLFACTOR=90) ON [PRIMARY]
|
|
GO
|
|
CREATE NONCLUSTERED INDEX [NCIX_PHGD_ACXI_COL_PHGD_ACXI_PharmacodeNum] ON [dbo].[PHGD_ACXI] ([PHGD_ACXI_PharmacodeNum]) WITH (FILLFACTOR=100) ON [PRIMARY]
|
|
GO
|
|
CREATE UNIQUE NONCLUSTERED INDEX [NCIX_PHGD_ACXI_PharmacodeNum_Interaction_Classification] ON [dbo].[PHGD_ACXI] ([PHGD_ACXI_PharmacodeNum], [PHGD_ACXI_interaction], [PHGD_ACXI_classification]) WITH (FILLFACTOR=90) ON [PRIMARY]
|
|
GO
|
|
|
|
*/
|
|
|
|
SELECT TOP 10 *
|
|
FROM PHGD_ACSC
|
|
/*
|
|
|
|
CREATE TABLE [dbo].[PHGD_ACSC]
|
|
(
|
|
[PHGD_ACSC_GUID] [dbo].[GUID_identifier] NOT NULL,
|
|
[PHGD_ACSC_pharmacode] [dbo].[GUID_identifier] NULL,
|
|
[PHGD_ACSC_substance] [dbo].[GUID_identifier] NULL,
|
|
[PHGD_ACSC_sequence] [dbo].[Unique_identifier] NULL,
|
|
[PHGD_ACSC_quantity] [dbo].[Small_alphabetical_field] NULL,
|
|
[PHGD_ACSC_quantity_unit] [dbo].[Normal_name_field] NULL,
|
|
[PHGD_ACSC_code] [dbo].[Alpha_table_key] NULL,
|
|
[PHGD_ACSC_status] [dbo].[Small_integer] NULL,
|
|
[PHGD_ACSC_TS] [timestamp] NOT NULL,
|
|
[PHGD_ACSC_VGUID] [dbo].[VGUID_identifier] NULL CONSTRAINT [DF__PHGD_ACSC__PHGD___513A61C0] DEFAULT (newid()),
|
|
[PHGD_ACSC_master_ID] [dbo].[Large_name_field] NULL,
|
|
[PHGD_ACSC_PharmacodeNum] [dbo].[Small_alphabetical_field] NULL
|
|
) ON [PRIMARY]
|
|
GO
|
|
ALTER TABLE [dbo].[PHGD_ACSC] ADD CONSTRAINT [PK__PHGD_ACSC__50463D87] PRIMARY KEY NONCLUSTERED ([PHGD_ACSC_GUID]) WITH (FILLFACTOR=90) ON [PRIMARY]
|
|
GO
|
|
CREATE UNIQUE NONCLUSTERED INDEX [NCUIX_PHGDACSC_COL_PHGDPharmacodeNum] ON [dbo].[PHGD_ACSC] ([PHGD_ACSC_PharmacodeNum], [PHGD_ACSC_sequence]) WITH (FILLFACTOR=90) ON [PRIMARY]
|
|
GO
|
|
*/
|
|
|
|
SELECT TOP 10 *
|
|
FROM [dbo].[PH_item] [pi]
|
|
/*
|
|
CREATE TABLE [dbo].[PH_item]
|
|
(
|
|
[PH_item_GUID] [dbo].[GUID_identifier] NOT NULL,
|
|
[PHIT_OICM_code] [dbo].[GUID_identifier] NULL,
|
|
[PHIT_refund_code] [dbo].[GUID_identifier] NULL,
|
|
[PHIT_item] [dbo].[Unique_identifier] NOT NULL,
|
|
[PHIT_std_posology] [dbo].[GUID_identifier] NULL,
|
|
[PHIT_code] [dbo].[Small_alphabetical_field] NULL,
|
|
[PHIT_type] [dbo].[Small_integer] NULL,
|
|
[PHIT_tax] [dbo].[Boolean_Code] NOT NULL CONSTRAINT [DF__PH_item__PHIT_ta__566BF0CA] DEFAULT (0),
|
|
[PHIT_points] [dbo].[Quantities] NULL,
|
|
[PHIT_OFAC_code] [dbo].[Small_alphabetical_field] NULL,
|
|
[PHIT_tariff_code] [dbo].[Alpha_table_key] NULL,
|
|
[PHIT_prescription_mandatory] [dbo].[Boolean_Code] NOT NULL CONSTRAINT [DF__PH_item__PHIT_pr__57601503] DEFAULT (0),
|
|
[PHIT_posology_mandatory] [dbo].[Boolean_Code] NOT NULL CONSTRAINT [DF__PH_item__PHIT_po__5854393C] DEFAULT (0),
|
|
[PHIT_APS_TS] [dbo].[APS_timestamp] NOT NULL CONSTRAINT [DF__PH_item__PHIT_AP__59485D75] DEFAULT (getdate()),
|
|
[PHIT_VGUID] [dbo].[VGUID_identifier] NULL CONSTRAINT [DF__PH_item__PHIT_VG__5A3C81AE] DEFAULT (newid()),
|
|
[PHIT_master_ID] [dbo].[Large_name_field] NULL,
|
|
[PHIT_TS] [timestamp] NOT NULL,
|
|
[PHIT_substitution] [dbo].[Small_integer] NULL,
|
|
[PHIT_usage_limit_management] [dbo].[Small_integer] NULL,
|
|
[PHIT_economical_order_qty] [dbo].[Quantities] NULL,
|
|
[PHIT_supplying_batch_qty] [dbo].[Quantities] NULL,
|
|
[PHIT_safety_qty] [dbo].[Quantities] NULL,
|
|
[PHIT_generic_source] [dbo].[Small_alphabetical_field] NULL,
|
|
[PHIT_strict_generic] [dbo].[Small_integer] NULL,
|
|
[PHIT_GD_withdrawal_override] [dbo].[Small_integer] NULL,
|
|
[PHIT_GD_info_override] [dbo].[Small_integer] NULL,
|
|
[PHIT_GD_refund_code_override] [dbo].[Small_integer] NULL,
|
|
[PHIT_aids_and_appliances_list] [dbo].[Small_integer] NULL CONSTRAINT [DF__PH_item__PHIT_ai__2BF09CED] DEFAULT ((1)),
|
|
[PHIT_insurance_code] [dbo].[Small_integer] NULL,
|
|
[PHIT_Product_number] [dbo].[Unique_identifier] NULL,
|
|
[PHIT_AbusePotential] [dbo].[Boolean_Code] NOT NULL CONSTRAINT [DF__PH_item__PHIT_Ab__5766CF20] DEFAULT ((0)),
|
|
[PHIT_expiry] [smallint] NULL,
|
|
[PHIT_group_code] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
|
|
[PHIT_large_package_type] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
|
|
[PHIT_narcotic] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
|
|
[PHIT_narcotic_code] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
|
|
[PHIT_number_of_pieces] [int] NULL,
|
|
[PHIT_refrigeration] [int] NULL,
|
|
[PHIT_safety_data_sheet_date_english] [datetime] NULL,
|
|
[PHIT_safety_data_sheet_date_french] [datetime] NULL,
|
|
[PHIT_safety_data_sheet_date_german] [datetime] NULL,
|
|
[PHIT_safety_data_sheet_date_italian] [datetime] NULL,
|
|
[PHIT_sloplus] [int] NULL,
|
|
[PHIT_withdrawal_date] [datetime] NULL,
|
|
[PHIT_assortment_1] [dbo].[GUID_identifier] NULL,
|
|
[PHIT_assortment_2] [dbo].[GUID_identifier] NULL,
|
|
[PHIT_temperature] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
|
|
[PHIT_toxic] [bit] NULL,
|
|
[PHIT_toxicity_class] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
|
|
[PHIT_packaging] [int] NULL,
|
|
[PHIT_chemical_group] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
|
|
[PHIT_TariffModel] [bit] NOT NULL CONSTRAINT [DF_PHIT_TariffModel] DEFAULT ((0)),
|
|
[PHIT_XyxleInfo] [bit] NOT NULL CONSTRAINT [DF_PHIT_XyxleInfo] DEFAULT ((0)),
|
|
[PHIT_large_generic_sequence] [smallint] NULL,
|
|
[PHIT_limitation_exist] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
|
|
) ON [PRIMARY]
|
|
GO
|
|
ALTER TABLE [dbo].[PH_item] ADD CONSTRAINT [PK__PH_item__5577CC91] PRIMARY KEY NONCLUSTERED ([PH_item_GUID]) WITH (FILLFACTOR=90) ON [PRIMARY]
|
|
GO
|
|
CREATE UNIQUE NONCLUSTERED INDEX [XIF3030PH_item] ON [dbo].[PH_item] ([PHIT_item]) WITH (FILLFACTOR=90) ON [PRIMARY]
|
|
GO
|
|
CREATE NONCLUSTERED INDEX [NCIX_PH_item_COL_PHIT_Product_number] ON [dbo].[PH_item] ([PHIT_Product_number]) INCLUDE ([PH_item_GUID]) WITH (FILLFACTOR=90) ON [PRIMARY]
|
|
GO
|
|
CREATE NONCLUSTERED INDEX [XIE2PH_item] ON [dbo].[PH_item] ([PHIT_master_ID]) WITH (FILLFACTOR=90) ON [PRIMARY]
|
|
GO
|
|
ALTER TABLE [dbo].[PH_item] ADD CONSTRAINT [FKPHITAssortment1] FOREIGN KEY ([PHIT_assortment_1]) REFERENCES [dbo].[PHGD_CODES] ([PHGD_CODES_GUID])
|
|
GO
|
|
ALTER TABLE [dbo].[PH_item] ADD CONSTRAINT [FKPHITAssortment2] FOREIGN KEY ([PHIT_assortment_2]) REFERENCES [dbo].[PHGD_CODES] ([PHGD_CODES_GUID])
|
|
GO
|
|
EXEC sp_addextendedproperty N'1', N'Avec Modèle tarifaire', 'SCHEMA', N'dbo', 'TABLE', N'PH_item', 'COLUMN', N'PHIT_TariffModel'
|
|
GO
|
|
|
|
*/
|
|
|
|
SELECT TOP (100) * FROM [dbo].[Item_key] [ik]
|
|
WHERE [ik].[ITK_type] = 1 --phcode
|