Spaces:

cuatrolabs
/

cuatrolabs-scm-ms

Runtime error

cuatrolabs-scm-ms / docs /database /migrations /migration_make_po_optional.sql

self grn po item id issue

30d1376 8 months ago

773 Bytes

-- Migration: Make PO optional in GRN tables
-- This allows creating GRNs without requiring a Purchase Order

-- Step 1: Make po_id nullable in scm_grn table
ALTER TABLE trans.scm_grn 
ALTER COLUMN po_id DROP NOT NULL;

-- Step 2: Make po_item_id nullable in scm_grn_item table  
ALTER TABLE trans.scm_grn_item 
ALTER COLUMN po_item_id DROP NOT NULL;

-- Step 3: Make batch_no nullable in scm_grn_item table
ALTER TABLE trans.scm_grn_item 
ALTER COLUMN batch_no DROP NOT NULL;

-- Verify the changes
SELECT 
    table_name,
    column_name,
    is_nullable,
    data_type
FROM information_schema.columns 
WHERE table_schema = 'trans'
  AND table_name IN ('scm_grn', 'scm_grn_item') 
  AND column_name IN ('po_id', 'po_item_id', 'batch_no')
ORDER BY table_name, column_name;

	-- Migration: Make PO optional in GRN tables
	-- This allows creating GRNs without requiring a Purchase Order

	-- Step 1: Make po_id nullable in scm_grn table
	ALTER TABLE trans.scm_grn
	ALTER COLUMN po_id DROP NOT NULL;

	-- Step 2: Make po_item_id nullable in scm_grn_item table
	ALTER TABLE trans.scm_grn_item
	ALTER COLUMN po_item_id DROP NOT NULL;

	-- Step 3: Make batch_no nullable in scm_grn_item table
	ALTER TABLE trans.scm_grn_item
	ALTER COLUMN batch_no DROP NOT NULL;

	-- Verify the changes
	SELECT
	table_name,
	column_name,
	is_nullable,
	data_type
	FROM information_schema.columns
	WHERE table_schema = 'trans'
	AND table_name IN ('scm_grn', 'scm_grn_item')
	AND column_name IN ('po_id', 'po_item_id', 'batch_no')
	ORDER BY table_name, column_name;