populate column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Other SQL Server 2008 Topics

populate column

Author

Topic

nord
Posting Yak Master

126 Posts

Posted - 2012-01-17 : 14:32:21

Hi, I new in sp and i need to populate the po_type column in the tables OB_EDI850A and OB_EDI850b.
thanks

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-17 : 14:46:39

ummm..you might need to supply a little more detailed info

Like where is the data coming from that will populate the column?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

nord
Posting Yak Master

126 Posts

Posted - 2012-01-17 : 14:52:59

Modify RaymSP_TransferEDI850UploadProcessSP to populate the po_type column in the above tables.

sp:
USE [WNG]
GO
/****** Object: StoredProcedure [dbo].[RaymSP_TransferEDI850UploadProcess] Script Date: 01/17/2012 13:51:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[RaymSP_TransferEDI850UploadProcess]
@batch_seq int, @activity_seq int, @return_value int OUTPUT
AS
-- lma 20060915
TRUNCATE TABLE OB_EDI850A
TRUNCATE TABLE OB_EDI850B

DECLARE cPO CURSOR READ_ONLY
FOR
SELECT p.po_number,
p.sub_po_number, p.creation_date, p.po_reference,
SUPPLIER.Supplier_Code, (SELECT top 1 product_group.description
FROM po_header p1 INNER JOIN
po_detail_loose ON PO_DETAIL_LOOSE.PO_NUMBER = p1.PO_NUMBER INNER JOIN
PRODUCT ON po_detail_loose.product_id = PRODUCT.Product_id INNER JOIN
product_group ON PRODUCT.level2_id=product_group.product_group_id
WHERE po_detail_loose.PoLineType=2 AND p1.po_number=p.po_number),
payterm.Payterm_Code,
p.scheduled_date, p.vendor_canc_date, store.store_code,
'0', 0, 0
FROM po_header p
INNER JOIN OBOutTransactionUpload ON OBOutTransactionUpload.OBOTU_key2=cast(p.po_number AS varchar(50))
LEFT JOIN STORE ON p.store_code_id=STORE.store_code_id
LEFT JOIN PAYTERM ON PAYTERM.Payterm_ID=p.payterm_id
LEFT JOIN SUPPLIER ON p.supplier_id=SUPPLIER.supplier_id
/*
column [OBOTU_key10] plays the role of [sended]
*/
WHERE OBOutTransactionUpload.OBOTU_TableName in ('po_header') AND OBOutTransactionUpload.OBOTU_Key1='07' AND OBOutTransactionUpload.OBOTU_key10 = 'V'
ORDER BY p.po_number

DECLARE
@OBEDI850A_BEG_CUST char(10),
@OBEDI850A_BEG_ORDER_DATE DATETIME,
@OBEDI850A_REF_ON char(20),
@OBEDI850A_REF_CS char(10),
@OBEDI850A_REF_DP varchar(50),
@OBEDI850A_ITD char(10),
@OBEDI850A_DTM_WANTED_DATE DATETIME,
@OBEDI850A_DTM_CANCEL_DATE DATETIME,
@OBEDI850A_N1_ST varchar(10),
@OBEDI850A_SAC_CODE char(10),
@OBEDI850A_SAC_PERCENT decimal(5,2),
@OBEDI850A_CountOfPO1 INT
DECLARE @id int, @po_number int, @CountOfPO1 int

OPEN cPO

FETCH NEXT FROM cPO INTO @po_number,
@OBEDI850A_BEG_CUST, @OBEDI850A_BEG_ORDER_DATE, @OBEDI850A_REF_ON,
@OBEDI850A_REF_CS, @OBEDI850A_REF_DP, @OBEDI850A_ITD,
@OBEDI850A_DTM_WANTED_DATE, @OBEDI850A_DTM_CANCEL_DATE, @OBEDI850A_N1_ST,
@OBEDI850A_SAC_CODE, @OBEDI850A_SAC_PERCENT, @OBEDI850A_CountOfPO1
WHILE (@@fetch_status = 0)
BEGIN
INSERT INTO OB_EDI850A (
OBEDI850A_BEG_CUST, OBEDI850A_BEG_ORDER_DATE, OBEDI850A_REF_ON,
OBEDI850A_REF_CS, OBEDI850A_REF_DP, OBEDI850A_ITD,
OBEDI850A_DTM_WANTED_DATE, OBEDI850A_DTM_CANCEL_DATE, OBEDI850A_N1_ST,
OBEDI850A_SAC_CODE, OBEDI850A_SAC_PERCENT, OBEDI850A_CountOfPO1)
SELECT
@OBEDI850A_BEG_CUST, @OBEDI850A_BEG_ORDER_DATE, @OBEDI850A_REF_ON,
@OBEDI850A_REF_CS, @OBEDI850A_REF_DP, @OBEDI850A_ITD,
@OBEDI850A_DTM_WANTED_DATE, @OBEDI850A_DTM_CANCEL_DATE, @OBEDI850A_N1_ST,
@OBEDI850A_SAC_CODE, @OBEDI850A_SAC_PERCENT, @OBEDI850A_CountOfPO1

SELECT @return_value = @@error
IF @return_value <> 0 GOTO error_process

SELECT @id=@@identity

SELECT @CountOfPO1=count(po_header.po_number)
FROM po_header
INNER JOIN PO_DETAIL_LOOSE ON po_header.po_number=po_detail_loose.po_number
INNER JOIN SKU_CONVERSION ON SKU_CONVERSION.product_id=PO_DETAIL_LOOSE.product_id AND SKU_CONVERSION.color_id=PO_DETAIL_LOOSE.color_id AND SKU_CONVERSION.size_id=PO_DETAIL_LOOSE.size_id
LEFT JOIN SKU_OTHERS ON SKU_CONVERSION.SHORT_SKU=SKU_OTHERS.SHORT_SKU
WHERE PoLineType=2 AND po_header.po_number=@po_number

UPDATE OB_EDI850A
SET OBEDI850A_CountOfPO1=@CountOfPO1
WHERE OBEDI850A_SystemSequenceNumber=@id

SELECT @return_value = @@error
IF @return_value <> 0 GOTO error_process

SELECT
identity(int,1,1) seq, COALESCE(loose_quantity_ordered,0) qty, COALESCE(landed_price,0) price, isnull(SKU_OTHERS.OTHER_SKU,'') upc
INTO #t
FROM po_header
INNER JOIN PO_DETAIL_LOOSE ON po_header.po_number=po_detail_loose.po_number
INNER JOIN SKU_CONVERSION ON SKU_CONVERSION.product_id=PO_DETAIL_LOOSE.product_id AND SKU_CONVERSION.color_id=PO_DETAIL_LOOSE.color_id AND SKU_CONVERSION.size_id=PO_DETAIL_LOOSE.size_id
LEFT JOIN SKU_OTHERS ON SKU_CONVERSION.SHORT_SKU=SKU_OTHERS.SHORT_SKU
-- column [OBOTU_key10] plays the role of [sended]
WHERE
po_detail_loose.PoLineType=2 AND po_header.po_number=@po_number

SELECT @return_value = @@error
IF @return_value <> 0 GOTO error_process

insert into OB_EDI850B (OBEDI850B_SystemSequenceNumber,
OBEDI850B_SEQ, OBEDI850B_QTY, OBEDI850B_PRICE,
OBEDI850B_UPC)
SELECT @id,
seq, qty, price,
upc
FROM #t ORDER BY seq

SELECT @return_value = @@error
IF @return_value <> 0 GOTO error_process

DROP TABLE #t

SELECT @return_value = @@error
IF @return_value <> 0 GOTO error_process

FETCH NEXT FROM cPO INTO @po_number,
@OBEDI850A_BEG_CUST, @OBEDI850A_BEG_ORDER_DATE, @OBEDI850A_REF_ON,
@OBEDI850A_REF_CS, @OBEDI850A_REF_DP, @OBEDI850A_ITD,
@OBEDI850A_DTM_WANTED_DATE, @OBEDI850A_DTM_CANCEL_DATE, @OBEDI850A_N1_ST,
@OBEDI850A_SAC_CODE, @OBEDI850A_SAC_PERCENT, @OBEDI850A_CountOfPO1
END

CLOSE cPO
DEALLOCATE cPO

--------------------------------------------------
DECLARE @pDelimiter varchar(10)
SET @pDelimiter = '*'
--------------------------------------------------
DECLARE cPO CURSOR READ_ONLY
FOR
SELECT OBEDI850A_SystemSequenceNumber,
OBEDI850A_BEG_CUST,OBEDI850A_BEG_ORDER_DATE, -- line 1
OBEDI850A_REF_ON, -- line 2
OBEDI850A_REF_CS, -- line 3
OBEDI850A_REF_DP, -- line 4
OBEDI850A_ITD, -- line 5
OBEDI850A_DTM_WANTED_DATE, -- line 6
OBEDI850A_DTM_CANCEL_DATE, -- line 7
OBEDI850A_N1_ST, -- line 8
OBEDI850A_SAC_CODE,OBEDI850A_SAC_PERCENT, -- line 9
-- line 10 (detail)
OBEDI850A_CountOfPO1 -- line 11
FROM OB_EDI850A
ORDER BY OBEDI850A_SystemSequenceNumber

DECLARE @OBEDI850A_SystemSequenceNumber int

OPEN cPO
FETCH NEXT FROM cPO INTO
@OBEDI850A_SystemSequenceNumber,
@OBEDI850A_BEG_CUST,@OBEDI850A_BEG_ORDER_DATE, -- line 1
@OBEDI850A_REF_ON, -- line 2
@OBEDI850A_REF_CS, -- line 3
@OBEDI850A_REF_DP, -- line 4
@OBEDI850A_ITD, -- line 5
@OBEDI850A_DTM_WANTED_DATE, -- line 6
@OBEDI850A_DTM_CANCEL_DATE, -- line 7
@OBEDI850A_N1_ST, -- line 8
@OBEDI850A_SAC_CODE,@OBEDI850A_SAC_PERCENT, -- line 9
-- line 10 (detail)
@OBEDI850A_CountOfPO1 -- line 11
WHILE (@@fetch_status = 0)
BEGIN
INSERT INTO OB_EDI850 (OBEDI850_OutputString,OBEDI850_BatchSequence,OBEDI850_ActivitySequence)
SELECT 'BEG'+@pDelimiter+'00'+@pDelimiter+'SA'+@pDelimiter+isnull(@OBEDI850A_BEG_CUST,'')
+@pDelimiter+@pDelimiter+isnull(CONVERT(varCHAR(8),@OBEDI850A_BEG_ORDER_DATE,112),'')
,@batch_seq, @activity_seq
INSERT INTO OB_EDI850 (OBEDI850_OutputString,OBEDI850_BatchSequence,OBEDI850_ActivitySequence)
SELECT 'REF'+@pDelimiter+'ON'+@pDelimiter+rtrim(isnull(@OBEDI850A_REF_ON,''))
,@batch_seq, @activity_seq
INSERT INTO OB_EDI850 (OBEDI850_OutputString,OBEDI850_BatchSequence,OBEDI850_ActivitySequence)
SELECT 'REF'+@pDelimiter+'CS'+@pDelimiter+rtrim(isnull(@OBEDI850A_REF_CS,''))
,@batch_seq, @activity_seq
INSERT INTO OB_EDI850 (OBEDI850_OutputString,OBEDI850_BatchSequence,OBEDI850_ActivitySequence)
SELECT 'REF'+@pDelimiter+'DP'+@pDelimiter+rtrim(isnull(@OBEDI850A_REF_DP,''))
,@batch_seq, @activity_seq
INSERT INTO OB_EDI850 (OBEDI850_OutputString,OBEDI850_BatchSequence,OBEDI850_ActivitySequence)
SELECT 'IDT'+@pDelimiter+rtrim(isnull(@OBEDI850A_ITD,''))
,@batch_seq, @activity_seq
INSERT INTO OB_EDI850 (OBEDI850_OutputString,OBEDI850_BatchSequence,OBEDI850_ActivitySequence)
SELECT 'DTM'+@pDelimiter+'010'+@pDelimiter+isnull(CONVERT(VARCHAR(8),@OBEDI850A_DTM_WANTED_DATE,112),'')
,@batch_seq, @activity_seq
INSERT INTO OB_EDI850 (OBEDI850_OutputString,OBEDI850_BatchSequence,OBEDI850_ActivitySequence)
SELECT 'DTM'+@pDelimiter+'001'+@pDelimiter+isnull(CONVERT(VARCHAR(8),@OBEDI850A_DTM_CANCEL_DATE,112),'')
,@batch_seq, @activity_seq
INSERT INTO OB_EDI850 (OBEDI850_OutputString,OBEDI850_BatchSequence,OBEDI850_ActivitySequence)
SELECT 'N1'+@pDelimiter+'ST'+@pDelimiter+'92'+@pDelimiter+rtrim(isnull(@OBEDI850A_N1_ST,''))
,@batch_seq, @activity_seq
INSERT INTO OB_EDI850 (OBEDI850_OutputString,OBEDI850_BatchSequence,OBEDI850_ActivitySequence)
SELECT 'SAC'+@pDelimiter+'A'+@pDelimiter+rtrim(isnull(@OBEDI850A_SAC_CODE,''))+REPLICATE(@pDelimiter,6)+cast(isnull(@OBEDI850A_SAC_PERCENT,0) AS VARCHAR(10))
,@batch_seq, @activity_seq
INSERT INTO OB_EDI850 (OBEDI850_OutputString,OBEDI850_BatchSequence,OBEDI850_ActivitySequence)
SELECT ''
,@batch_seq, @activity_seq
INSERT INTO OB_EDI850 (OBEDI850_OutputString,OBEDI850_BatchSequence,OBEDI850_ActivitySequence)
SELECT 'PO1'+@pDelimiter+cast(OBEDI850B_SEQ AS varchar(15))+@pDelimiter+
cast(OBEDI850B_QTY AS varchar(15))+@pDelimiter+
'EA'+@pDelimiter+CAST(OBEDI850B_PRICE AS varchar(15))+@pDelimiter+@pDelimiter+
'UP'+@pDelimiter+RTRIM(OBEDI850B_UPC)
,@batch_seq, @activity_seq
FROM OB_EDI850B
WHERE OBEDI850B_SystemSequenceNumber=@OBEDI850A_SystemSequenceNumber
ORDER BY OBEDI850B_SEQ
INSERT INTO OB_EDI850 (OBEDI850_OutputString,OBEDI850_BatchSequence,OBEDI850_ActivitySequence)
SELECT 'CTT'+@pDelimiter+cast(isnull(@OBEDI850A_CountOfPO1,0) AS varchar(15))
,@batch_seq, @activity_seq

FETCH NEXT FROM cPO INTO
@OBEDI850A_SystemSequenceNumber,
@OBEDI850A_BEG_CUST,@OBEDI850A_BEG_ORDER_DATE, -- line 1
@OBEDI850A_REF_ON, -- line 2
@OBEDI850A_REF_CS, -- line 3
@OBEDI850A_REF_DP, -- line 4
@OBEDI850A_ITD, -- line 5
@OBEDI850A_DTM_WANTED_DATE, -- line 6
@OBEDI850A_DTM_CANCEL_DATE, -- line 7
@OBEDI850A_N1_ST, -- line 8
@OBEDI850A_SAC_CODE,@OBEDI850A_SAC_PERCENT, -- line 9
-- line 10 (detail)
@OBEDI850A_CountOfPO1 -- line 11
END

CLOSE cPO
DEALLOCATE cPO
--------------------------------------------------

GOTO no_error

error_process:
INSERT into OB_OutTransferEDI850UploadLog (
OBTEDI850UL_Severity, OBTEDI850UL_Type, OBTEDI850UL_NumberError,
OBTEDI850UL_Description, OBTEDI850UL_BatchSequence, OBTEDI850UL_ActivitySequence,
OBTEDI850UL_TsId)
VALUES (
'E', 'OB_EDI850', @return_value, 'Transfer EDI 850 Upload',
@batch_seq, @activity_seq, getdate())
GOTO fin
no_error:
INSERT into OB_OutTransferEDI850UploadLog (
OBTEDI850UL_Severity, OBTEDI850UL_Type, OBTEDI850UL_NumberError,
OBTEDI850UL_Description, OBTEDI850UL_BatchSequence, OBTEDI850UL_ActivitySequence,
OBTEDI850UL_TsId)
VALUES (
'I', 'OB_EDI850', @return_value, 'Transfer EDI 850 Upload' ,
@batch_seq, @activity_seq, getdate())
fin:
Return @return_value

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources