CREATE PROCEDURE [dbo].[USP_REQUEST_INSERT]
(
@XML_INFO NVARCHAR(MAX) = NULL
)
AS
BEGIN
SET NOCOUNT ON
BEGIN TRY
BEGIN TRANSACTION -- Start the transaction
---------------------------
DECLARE @REQUEST_XML INT
EXEC SP_XML_PREPAREDOCUMENT @REQUEST_XML OUTPUT, @XML_INFO
--1.------INSERT TO T_PROCUREMENT_HEADER--------
INSERT INTO T_EP_PROCUREMENT_HEADER(DOCNUM, IOR_NO, COST_CENTER, SIF_ENTITY_CODE, ESTABLISHMENT_ID, PURPOSE,CURRENCY,TOTAL_AMOUNT,TAX_AMOUNT,GROSS_AMOUNT, STATUS,APP_LEVEL, ENTERED_DATE, ENTERED_BY,REMARKS)
SELECT DOCNUM, IOR_NO, COST_CENTER,SIF_ENTITY_CODE, ESTABLISHMENT_ID, PURPOSE,CURRENCY,TOTAL_AMOUNT,TAX_AMOUNT,GROSS_AMOUNT,STATUS,
APP_LEVEL, getdate(), ENTERED_BY ,REMARKS
FROM OPENXML (@REQUEST_XML , '/EPROC/HEADER' , 1)
WITH
(
DOCNUM VARCHAR(22)'DOCNUM/text()' ,
IOR_NO VARCHAR(22)'IOR_NO/text()' ,
COST_CENTER VARCHAR(10)'COST_CENTER/text()' ,
SIF_ENTITY_CODE VARCHAR(10)'SIF_ENTITY_CODE/text()' ,
ESTABLISHMENT_ID VARCHAR(10)'ESTABLISHMENT_ID/text()' ,
PURPOSE VARCHAR(250)'PURPOSE/text()' ,
CURRENCY VARCHAR(10)'CURRENCY/text()' ,
TOTAL_AMOUNT VARCHAR(10)'TOTAL_AMOUNT/text()' ,
STATUS VARCHAR(15)'STATUS/text()' ,
APP_LEVEL VARCHAR(100)'APP_LEVEL/text()' ,
TAX_AMOUNT VARCHAR(10)'TAX_AMOUNT/text()' ,
GROSS_AMOUNT VARCHAR(10)'GROSS_AMOUNT/text()' ,
ENTERED_BY VARCHAR(10)'ENTERED_BY/text()' ,
REMARKS VARCHAR(250)'REMARKS/text()'
)
--2.-------------Insert to T_PROCUREMENT_DETAILS --------------
INSERT INTO T_EP_PROCUREMENT_DETAILS (DOCNUM,LINE_ITEM,TYPE,DESCRIPTION,EXP_CATEGORY,UOM,QUANTITY,UNIT_PRIZE,NET_AMOUNT)
SELECT DOCNUM, LINE_ITEM,TYPE,DESCRIPTION, EXP_CATEGORY, UOM, QUANTITY, UNIT_PRIZE,NET_AMOUNT
FROM
openxml(@REQUEST_XML, '/EPROC/DETAILS', 1) WITH
(
DOCNUM VARCHAR(10) 'DOCNUM /text()',
LINE_ITEM VARCHAR(9) 'LINE_ITEM /text()',
DESCRIPTION VARCHAR(150) 'DESCRIPTION /text()',
TYPE VARCHAR(10)'TYPE/text()' ,
EXP_CATEGORY VARCHAR(10)'EXP_CATEGORY/text()' ,
UOM VARCHAR(10) 'UOM /text()',
QUANTITY VARCHAR(15) 'QUANTITY/text()',
UNIT_PRIZE VARCHAR(15) 'UNIT_PRIZE/text()',
NET_AMOUNT VARCHAR(9) 'NET_AMOUNT/text()')
SELECT 'SUCCESS' AS OUTPUT
-- If we reach here , success!
COMMIT
END TRY
--END TRY--------------------------------------------------------------
BEGIN CATCH
-- Whoops , there was an error
IF @@TRANCOUNT > 0
ROLLBACK
-- Raise an error with the details of the exception
DECLARE @ErrMsg nvarchar(4000) , @ErrSeverity int
SELECT @ErrMsg = error_message()
, @ErrSeverity = error_severity()
RAISERROR(@ErrMsg , @ErrSeverity , 1)
END CATCH
SET NOCOUNT OFF
END
No comments:
Post a Comment