use pvsmaster_gent_copy
use pvsmaster_gent
use [101]

SELECT name, type_desc
FROM sys.database_principals
WHERE name = 'PVS-GENT\Parvathy'


 ALTER TABLE CustomerRequirements
ADD product_article VARCHAR(100) NULL,
 product_id int NULL;

ALTER TABLE PriceMaster
ADD EmailForOffer VARCHAR(1000) NULL;

ALTER TABLE ProductMaster
ADD TariffNumber VARCHAR(150) NULL;


ALTER TABLE ProductMaster
 ADD ChemicalNameId int NULL,
 GradeId int NULL

ALTER TABLE Addressmaster
ADD IsActive int NULL;

select * from UnloadInstructions
select * from ShiptoMapping


ALTER TABLE CustomerContacts
ADD product_article VARCHAR(100) NULL,
 product_id int NULL;

ALTER TABLE PriceMaster
ADD BillTo int NULL;

CREATE TABLE ShiptoMapping (
    Id INT IDENTITY(1,1) PRIMARY KEY,

    shipto_code INT NULL,
    ship_to_id INT NULL,

    parent_shipto_code INT NULL,
    parent_shipto_id INT NULL,

    product_article VARCHAR(100) NULL,
    product_id INT NULL
);



CREATE TABLE LinkedPrice (
    id INT IDENTITY(1,1) PRIMARY KEY,

    -- LinkedPrice fields
    order_price_id INT NULL,

    article_code NVARCHAR(50) NULL,
    product_name NVARCHAR(255) NULL,

    bill_to INT NULL,
    ordered_by_customer NVARCHAR(255) NULL,

    currency NVARCHAR(10) NULL,
    currency_id INT NULL,

    delivery_destination NVARCHAR(255) NULL,

    exact_erp_address_id INT NULL,

    incoterm NVARCHAR(50) NULL,

    lead_time_days INT NULL DEFAULT 0,

    min_quantity FLOAT NULL,
    min_quantity_operator NVARCHAR(50) NULL,

    packaging NVARCHAR(100) NULL,
    packaging_id INT NULL,

    kg_per_packaging FLOAT NULL,
    no_of_packings FLOAT NULL,

    packing_remarks BIT NULL DEFAULT 0,

    payment_term NVARCHAR(100) NULL,
    payment_term_id INT NULL,

    price FLOAT NULL,
    price_confirmation BIT NULL DEFAULT 0,

    offer_ref NVARCHAR(100) NULL,
    remarks NVARCHAR(MAX) NULL,

    quarter_name NVARCHAR(255) NULL,

    validity_start_date DATE NULL,
    validity_end_date DATE NULL,

    customer_id INT NULL,
    pricelist_id INT NULL,

    emails NVARCHAR(MAX) NULL, 

    -- BaseModel fields
    created_by INT NULL,
    created_on DATETIME2 NULL DEFAULT GETDATE(),
    updated_by INT NULL,
    updated_on DATETIME2 NULL DEFAULT GETDATE(),

    is_deleted BIT NULL DEFAULT 0,
    deleted_by INT NULL,
    deleted_on DATETIME2 NULL,

    -- Foreign Key Constraint
    CONSTRAINT fk_linked_price_order_price
    FOREIGN KEY (order_price_id)
    REFERENCES OrderPriceLink(id)
    ON DELETE CASCADE
);


ALTER TABLE PriceMaster
ADD DatePriceOfferRequest DATE NULL,
    OfferDate DATE NULL,
    Responsivnes NVARCHAR(100) NULL,
    LeadTimeDays NVARCHAR(100) NULL


========================================
select * from EmailAttachment
select * from AddressMaster where Id=3053

SELECT pm.QuarterName, pm.ValidityStartDate, pm.ValidityEndDate, pm.OrderedByCustomer, pm.ParentShipTo as new_shipto, pm.EmailForOffer, pm.PaymentTerm, pm.ArticleCode, pm.ProductName, pm.IncoTerm,
pm.DeliveryDestination, pm.Packaging, pm.no_of_packings, pm.MinQtyOperator, pm.MinQuantity, pm.Price,pm.IsConfirmed, pm.BillTo, pm.kg_per_packaging, pm.packing_remarks, pm.Remarks, 
pm.DatePriceOfferRequest, pm.OfferDate, pm.Responsivnes as responsive_working_days, pm.LeadTimeDays as leadtime_working_days, pm.OfferRef
FROM PriceMaster as pm
WHERE pm.ValidityStartDate >= '2026-01-01'
  AND pm.ValidityEndDate <= '2026-12-31'  
ORDER BY pm.QuarterName DESC, pm.OrderedByCustomer ASC  ;


update PriceMaster set ParentShipTo=ExactERPAddressId 

update AddressMaster set ParentShipTo=ExactERPAddressId 

select * from AddressMaster where ExactERPAddressId in (2217, 2219)


select * from ProductMaster


SELECT pm.ArticleCode, itm.StatisticalNumber
FROM [pvsmaster_gent].[dbo].[ProductMaster] pm
LEFT JOIN [101].[dbo].[items] itm
    ON pm.ArticleCode COLLATE DATABASE_DEFAULT =
       itm.SearchCode COLLATE DATABASE_DEFAULT
WHERE itm.SearchCode IS NULL;


SELECT orq.ExactOrderNo, ors.Quantity as selected_qty, ors.Price as selected_price, pm.MinQuantity as sheet_min, pm.Price as sheet_price
FROM OrderRequests as orq join Orders as ors  on orq.Id=ors.OrderNo join OrderPriceLink as op on op.OrderId=orq.Id join PriceMaster as pm on op.PriceRowId=pm.Id
WHERE orq.ExactOrderNo IN (
'0260731')

select * from    [pvsmaster_gent].[dbo].[CustomerContacts] where ExactERPAddressId= 

select * from AddressMaster where ExactERPAddressId=2090



select * from Productmaster  where Id=3

select Id, ParentShipTo, ExactERPAddressId from Addressmaster  where Id in (2715,	3373,	3373)

select ors.ProductId, ors.OrderedCustomerId, am.ExactERPAddressId, am.ParentShipto, pm.ArticleCode ,pm.Id as product_id from Orders as ors join ProductMaster as pm on ors.ProductId=pm.Id join AddressMaster as am on ors.OrderedCustomerId=am.Id

select distinct am.ExactERPAddressId, ors.OrderedCustomerId, am.ParentShipto, pm.ArticleCode, ors.ProductId from Orders as ors join ProductMaster as pm on ors.ProductId=pm.Id join AddressMaster as am on ors.OrderedCustomerId=am.Id 
where ors.IsDeleted=0 and ors.StatusId!=5


select * from AddressMaster where Id=3866


select * from ShiptoMapping

INSERT INTO ShiptoMapping (
    shipto_code,
    ship_to_id,
    parent_shipto_code,
    product_article,
    product_id
)
SELECT DISTINCT 
    am.ExactERPAddressId,     -- shipto_code
    ors.OrderedCustomerId,    -- ship_to_id
    am.ParentShipto,          -- parent_shipto
    pm.ArticleCode,           -- product_article
    ors.ProductId             -- product_id
FROM Orders AS ors
JOIN ProductMaster AS pm 
    ON ors.ProductId = pm.Id
JOIN AddressMaster AS am 
    ON ors.OrderedCustomerId = am.Id;

select * from Orders where CustomerName like '%bren%'


select distinct am.ExactERPAddressId, ors.OrderedCustomerId, am.ParentShipto, pm.ArticleCode, ors.ProductId from Orders as ors join ProductMaster as pm on ors.ProductId=pm.Id join AddressMaster as am on ors.OrderedCustomerId=am.Id 
where ors.IsDeleted=0 and ors.StatusId!=5 and  am.ExactERPAddressId=1589


select * from ShiptoMapping where parent_shipto_code=1354

select * from CustomerContacts where 
 product_article='30102' and ExactERPAddressId=618
                        ExactERPAddressId=old_ship_to,

						
select * from CustomerContacts where  ExactERPAddressId=1355 and product_article='30102'

select *  from AddressMaster 

select distinct  ProductCategory from ProductMaster 


select * from ProductMaster where HasOriginStatement=1


select * from CustomerRequirements where NoCustomerEmailRequired=1

use [101]

select trim(SearchCode) as ArticleCode, Description_2, Description, UserField_03, StatisticalNumber from Items where SearchCode IN (
'31113','30909','30119','30901','25000','25100','25600','25610','25650','25800',
'25900','30001','30002','30003','30004','31001','31002','31003','31065','30101',
'25010','30102','30103','30104','30105','30111','30112','30113','31101','31102',
'31103','50001','30201','31201','30203','30205','30208','30210','31210','30212',
'31212','30214','30215','30216','30217','30218','30230','30231','30234','30250',
'30500','30501','30502','30300','30505','25781','30301','30302','30303','30304',
'30305','30306','30307','30308','31308','30309','30310','30311','30312','30313',
'40210','30339','30331','30012','30013','30014','30314','31314','30315','30316',
'30317','30318','30319','30320','30321','30323','30324','30325','30326','30328',
'40201','50000','30020','30030','30329','30211','30213','30202','30206','25110',
'25150','25500','25510','25520','25530','30106','30109','30110','30116','30117',
'30207','30219','30220','30246','30221','30222','30224','30226','30227','30228',
'30229','30232','30233','30236','30237','30242','30243','30244','30245','30322',
'30327','30330','30332','30333','30334','30335','30336','30337','30338','30503',
'30504','31232','30011','40205','40208','30114','30115','40108','40124','30021',
'30022','40104','40107','40204','61100','25011','89012','30901','3090122','25261',
'30913','26813','25001','25262','31112','78976'
)

select  * from [pvsmaster_gent].[dbo].[ProductMaster] where ArticleCode in  (
'31113','30909','30119','30901','25000','25100','25600','25610','25650','25800',
'25900','30001','30002','30003','30004','31001','31002','31003','31065','30101',
'25010','30102','30103','30104','30105','30111','30112','30113','31101','31102',
'31103','50001','30201','31201','30203','30205','30208','30210','31210','30212',
'31212','30214','30215','30216','30217','30218','30230','30231','30234','30250',
'30500','30501','30502','30300','30505','25781','30301','30302','30303','30304',
'30305','30306','30307','30308','31308','30309','30310','30311','30312','30313',
'40210','30339','30331','30012','30013','30014','30314','31314','30315','30316',
'30317','30318','30319','30320','30321','30323','30324','30325','30326','30328',
'40201','50000','30020','30030','30329','30211','30213','30202','30206','25110',
'25150','25500','25510','25520','25530','30106','30109','30110','30116','30117',
'30207','30219','30220','30246','30221','30222','30224','30226','30227','30228',
'30229','30232','30233','30236','30237','30242','30243','30244','30245','30322',
'30327','30330','30332','30333','30334','30335','30336','30337','30338','30503',
'30504','31232','30011','40205','40208','30114','30115','40108','40124','30021',
'30022','40104','40107','40204','61100','25011','89012','30901','3090122','25261',
'30913','26813','25001','25262','31112','78976'
)


select * from AddressMaster Where ExactERPAddressId=31

select Description_2, * from Items where SearchCode='25100'

select  * from ProductMaster where ArticleCode='31112'