Description
Properties
| Name | Value |
|---|---|
| ANSI Nulls ON | True |
| Quoted Identifier ON | True |
| Encrypted | False |
| Execute As | |
| Assembly |
Parameters
| Name | Data Type | Length | Description |
|---|---|---|---|
| @PARAMETER | xml | -1 |
SQL Script
SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.MG_MS_MEMO_SOURCE_AVAILABLE
@PARAMETER xml
---XencX---with encryption
as
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
declare @xStsnoheader varchar(2)
,@xStsno varchar(2)
,@xStsdes varchar(max)
,@XmlSts xml
,@raw xml
,@rowspage int
,@page int
,@desc int
,@sort varchar(255)
,@rowformat varchar(255)
,@XMLMAPING xml
,@TABELDATA varchar(max)
,@OutSortBy varchar(255)
,@rumusWhere varchar(max)
,@search xml
,@SQLString nvarchar(MAX)
,@fixWhere nvarchar(2000)
,@ParmDefinition nvarchar(MAX)
,@detail xml
,@totalpages bigint
,@rowJSON varchar(max);
set @xStsnoheader='01';
set @xStsno='00';
set @xStsdes='Success';
declare @ms_memo_detail_id bigint ,@mode_source int
SELECT
@rowspage=isnull(tempTable.xItem.value('@rowspage', 'int'),0)
,@page=isnull(tempTable.xItem.value('@page', 'int'),1)
,@desc=isnull(tempTable.xItem.value('@desc', 'bit'),0)
,@sort=isnull(tempTable.xItem.value('@sort', 'varchar(255)'),'r1')
,@rowformat=isnull(tempTable.xItem.value('@rowformat', 'varchar(255)'),'xml')
,@ms_memo_detail_id=isnull(tempTable.xItem.value('ms_memo_detail_id[1]', 'bigint'),-1)
,@mode_source=isnull(tempTable.xItem.value('mode_source[1]', 'int'),0)
FROM @PARAMETER.nodes('param/field') tempTable(xItem)
----#####START SOURCE#####
----SELECT gab_id, detail_in_id, mode_source_gab, mode_source_name_2, doc_no, doc_date, quantity_sisa, uom, receive_no, receive_date, info, qty_out, qty_plan, ms_item_custom_no, ms_item_base_no, part_no, part_name, mode_source, mode_source_name, ms_item_id, id_source_header, ori_detail_in_id, ms_type_id, gab_mode_source, mode_source_2, status_id
----FROM vw_ms_memo_detail_suplly_available;
----#####END SOURCE#####
set @XMLMAPING =
'
';
declare @check_ms_memo_detail_id bigint
,@check_ms_item_id bigint
,@check_ms_memo_type_id int
,@check_doc_date date
declare @total_quantity decimal(38,4)
SELECT top (1)
@check_ms_memo_detail_id=dt_ms_memo_detail.ms_memo_detail_id
, @check_ms_item_id=dt_ms_memo_detail.ms_item_id
, @check_ms_memo_type_id=dt_ms_memo_header.ms_memo_type_id
, @check_doc_date=dt_ms_memo_header.doc_date
, @total_quantity=dt_ms_memo_detail.quantity
FROM dt_ms_memo_detail INNER JOIN
dt_ms_memo_header ON dt_ms_memo_detail.ms_memo_header_id = dt_ms_memo_header.ms_memo_header_id
WHERE (dt_ms_memo_detail.ms_memo_detail_id = @ms_memo_detail_id)
declare @total_avialable decimal(38,4)
declare @total_supply decimal(38,4)
set @total_supply=(isnull((SELECT SUM(quantity) AS Expr1
FROM vw_ms_memo_detail_suplly
where ms_memo_detail_id=@ms_memo_detail_id
),0))
--SELECT gab_id, quantity ,ms_memo_detail_supply_id
-- FROM vw_ms_memo_detail_suplly
-- WHERE (ms_memo_detail_id = cast(@ms_memo_detail_id as varchar(255) ))
--FROM vw_ms_memo_detail_suplly_available' + case when @mode_source=0 then '_production' when @mode_source=1 then '_stock' else '' end + ' as source_data LEFT OUTER JOIN
set @TABELDATA=
' FROM vw_ms_memo_detail_suplly_available' + case when @mode_source=0 then '_production' when @mode_source=1 then '_stock' else '' end +
' as source_data ';
if @check_ms_memo_type_id=0
begin
set @fixWhere ='((not (ms_item_id=' + cast(@check_ms_item_id as varchar(255)) + ')) AND (allow_to_scrap=1)) AND ';
end
else if @check_ms_memo_type_id=1
begin
set @fixWhere ='((ms_item_id=' + cast(@check_ms_item_id as varchar(255)) + ') AND (allow_to_repair=1)) AND ';
end
else if @check_ms_memo_type_id=2
begin
set @fixWhere ='((ms_item_id=' + cast(@check_ms_item_id as varchar(255)) + ') AND (allow_to_selling=1)) AND ';
end
EXEC [dbo].[MyRUMUSWhereAndSortView10]
@PARAMETER =@PARAMETER
,@XMLMAPING = @XMLMAPING
,@fixWhere =@fixWhere
,@Sort =@sort
,@desc = @desc
,@OutSortBy = @OutSortBy OUTPUT
,@rumusWhere = @rumusWhere OUTPUT
,@search = @search OUTPUT;
--OLAH DETAIL +++++++++++++++++++++
declare @jmlrow bigint;
SET @SQLString = N' SELECT @jmlrow=count(*) ,@total_avialable= sum(isnull(quantity_sisa,0))' +@TABELDATA+ @rumusWhere + '' ;
SET @ParmDefinition = N'@check_doc_date date ,@total_avialable decimal(38,4) OUTPUT, @jmlrow bigint OUTPUT';
EXECUTE sp_executesql @SQLString, @ParmDefinition
,@check_doc_date =@check_doc_date
,@total_avialable=@total_avialable OUTPUT
,@jmlrow = @jmlrow OUTPUT
declare @SISAROWS bigint
set @SISAROWS =@jmlrow % @rowspage
if @SISAROWS=0
set @totalpages= @jmlrow/@rowspage
Else
set @totalpages= ((@jmlrow-@SISAROWS)/@rowspage) + 1;
set @detail=(
select
@sort as 'sort'
,@desc as 'desc'
,@rowspage as 'rowspage'
,@jmlrow as 'rows'
,@totalpages as 'pages'
,@search as 'search'
for xml path('detail') )
if Upper(@rowformat)='XML'
begin
SET @SQLString =
N' set @raw=( SELECT rn , rID , r1 , r2 , r3 , r4 , r5 , r6 , r7 , r8 , r9 , r10 , r11 , r12 , r13 , r14 , r15 , r16 , r17 , r18 , r19 , r20 , r21 , r22 , r23 , r24 , r25 ,r_s , r_u , r_uq, r_ua, r_uid FROM( SELECT ROW_NUMBER() OVER(ORDER BY ' + @OutSortBy +
') as rn , source_data.gab_id AS rID , source_data.detail_in_id AS r1 , source_data.mode_source_gab AS r2 , source_data.mode_source_name_2 AS r3 , source_data.doc_no AS r4 , dbo.date_to_str(source_data.doc_date ) AS r5 , cast(source_data.quantity_sisa AS decimal(18,4)) AS r6 , source_data.uom AS r7 , source_data.receive_no AS r8 , dbo.date_to_str(source_data.receive_date ) AS r9 , source_data.info AS r10 , cast(source_data.qty_out AS decimal(18,4)) AS r11 , cast(source_data.qty_plan AS decimal(18,4)) AS r12 , source_data.ms_item_custom_no AS r13 , source_data.ms_item_base_no AS r14 , source_data.part_no AS r15 , source_data.part_name AS r16 , source_data.mode_source AS r17 , source_data.mode_source_name AS r18 , source_data.ms_item_id AS r19 , source_data.id_source_header AS r20 , source_data.ori_detail_in_id AS r21 , source_data.ms_type_id AS r22 , source_data.gab_mode_source AS r23 , source_data.mode_source_2 AS r24 , source_data.status_id AS r25 , case when source_data.doc_date>@check_doc_date then 0 else 1 end as r_s , cast(case when used_table.gab_id is null then 0 else 1 end as bit) as r_u , cast(isnull(used_table.quantity,0) AS decimal(18,4)) as r_uq , cast(source_data.quantity_sisa + isnull(used_table.quantity,0) AS decimal(18,4)) as r_ua , isnull(used_table.ms_memo_detail_supply_id,-1) as r_uid '
+ @TABELDATA + @rumusWhere + ' ) XDATA '
set @SQLString = @SQLString + 'WHERE rN BETWEEN (' + cast((@page) as varchar(max)) +' -1) * ' + cast(@ROWSPAGE as varchar(max)) +
' + 1 AND '+ CAST(@page * @ROWSPAGE as varchar(max)) + ' for xml PATH(''xrow'')) '
SET @ParmDefinition = N'@check_doc_date date ,@raw XML OUTPUT';
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@check_doc_date=@check_doc_date,
@raw = @raw OUTPUT
end
else if Upper(@rowformat)='JSON'
begin
SET @SQLString =
N' BEGIN TRY DROP TABLE #data_supply END TRY BEGIN CATCH END CATCH CREATE TABLE #data_supply (gab_id varchar(255) primary key , quantity decimal(24,4), ms_memo_detail_supply_id bigint ) insert into #data_supply SELECT gab_id, sum(isnull(quantity,0)) ,ms_memo_detail_supply_id FROM vw_ms_memo_detail_suplly WHERE (ms_memo_detail_id ='+ cast(@ms_memo_detail_id as varchar(255) )+
') group by gab_id, ms_memo_detail_supply_id set @rowJSON=(''['' + STUFF(( SELECT '',{"rn":"'' + dbo.JSONEscaped(rn) + ''","rID":"'' + dbo.JSONEscaped(rID) + ''","r1":"'' + dbo.JSONEscaped(r1) + ''","r2":"'' + dbo.JSONEscaped(r2) + ''","r3":"'' + dbo.JSONEscaped(r3) + ''","r4":"'' + dbo.JSONEscaped(r4) + ''","r5":"'' + dbo.JSONEscaped(r5) + ''","r6":"'' + dbo.JSONEscaped(r6) + ''","r7":"'' + dbo.JSONEscaped(r7) + ''","r8":"'' + dbo.JSONEscaped(r8) + ''","r9":"'' + dbo.JSONEscaped(r9) + ''","r10":"'' + dbo.JSONEscaped(r10) + ''","r11":"'' + dbo.JSONEscaped(r11) + ''","r12":"'' + dbo.JSONEscaped(r12) + ''","r13":"'' + dbo.JSONEscaped(r13) + ''","r14":"'' + dbo.JSONEscaped(r14) + ''","r15":"'' + dbo.JSONEscaped(r15) + ''","r16":"'' + dbo.JSONEscaped(r16) + ''","r17":"'' + dbo.JSONEscaped(r17) + ''","r18":"'' + dbo.JSONEscaped(r18) + ''","r19":"'' + dbo.JSONEscaped(r19) + ''","r20":"'' + dbo.JSONEscaped(r20) + ''","r21":"'' + dbo.JSONEscaped(r21) + ''","r22":"'' + dbo.JSONEscaped(r22) + ''","r23":"'' + dbo.JSONEscaped(r23) + ''","r24":"'' + dbo.JSONEscaped(r24) + ''","r25":"'' + dbo.JSONEscaped(r25) + ''","r_s":"'' + dbo.JSONEscaped(case when xdata.doc_date>@check_doc_date then 0 else 1 end ) + ''","r_u":"'' + dbo.JSONEscaped(cast(case when used_table.gab_id is null then 0 else 1 end as bit)) + ''","r_uq":"'' + dbo.JSONEscaped(cast(isnull(used_table.quantity,0) AS decimal(18,4))) + ''","r_ua":"'' + dbo.JSONEscaped(cast(xdata.quantity_sisa + isnull(used_table.quantity,0) AS decimal(18,4))) + ''","r_uid":"'' + dbo.JSONEscaped(isnull(used_table.ms_memo_detail_supply_id,-1)) + ''"}'' FROM( SELECT 1 as rn , source_data.gab_id AS rID , source_data.detail_in_id AS r1 , source_data.mode_source_gab AS r2 , source_data.mode_source_name_2 AS r3 , source_data.doc_no AS r4 , dbo.date_to_str(source_data.doc_date ) AS r5 , cast(source_data.quantity_sisa AS decimal(18,4)) AS r6 , source_data.uom AS r7 , source_data.receive_no AS r8 , dbo.date_to_str(source_data.receive_date ) AS r9 , source_data.info AS r10 , cast(source_data.qty_out AS decimal(18,4)) AS r11 , cast(source_data.qty_plan AS decimal(18,4)) AS r12 , source_data.ms_item_custom_no AS r13 , source_data.ms_item_base_no AS r14 , source_data.part_no AS r15 , source_data.part_name AS r16 , source_data.mode_source AS r17 , source_data.mode_source_name AS r18 , source_data.ms_item_id AS r19 , source_data.id_source_header AS r20 , source_data.ori_detail_in_id AS r21 , source_data.ms_type_id AS r22 , source_data.gab_mode_source AS r23 , source_data.mode_source_2 AS r24 , source_data.status_id AS r25 ,source_data.doc_date ,source_data.quantity_sisa '
+ @TABELDATA + @rumusWhere +
' ORDER BY ' + @OutSortBy +
N' OFFSET ' + cast(((@page -1)*@ROWSPAGE) as varchar(max)) +
' ROWS FETCH NEXT ' + cast(@ROWSPAGE as varchar(max)) +
' ROWS ONLY )xdata LEFT OUTER JOIN #data_supply AS used_table ON xdata.rID = used_table.gab_id for xml PATH(''''), type ).value(''.'', ''varchar(max)''), 1, 1, '''') + '']'')';
SET @ParmDefinition = N'@check_doc_date date ,@rowJSON varchar(max) OUTPUT';
--print @SQLString
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@check_doc_date=@check_doc_date,
@rowJSON = @rowJSON OUTPUT
set @rowJSON ='{"userdata":{' +
'"total_avialable":"'+cast(cast( isnull(@total_avialable,0) as decimal(38,4))as varchar(255)) +'"' +
',"total_quantity":"'+cast(cast( isnull(@total_quantity,0) as decimal(38,4))as varchar(255)) +'"' +
',"total_supply":"'+ cast(cast( isnull(@total_supply,0) as decimal(38,4))as varchar(255)) +'"'+
',"total_less":"'+ cast(cast( isnull(@total_quantity,0)-isnull(@total_supply,0) as decimal(38,4))as varchar(255)) +'"'+
'}, "page":"'+ cast(@page as varchar(255)) + '","total":"' + cast(@totalpages as varchar(255)) + '","records":"' + cast(@jmlrow as varchar(255)) +'","rows":' + isnull(@rowJSON,'{}') + '}'
end
f:
set @XmlSts=(select @xStsnoheader + @xStsno as 'no', @xStsdes as 'des' for xml path('sts'))
SET NOCOUNT OFF
select CAST( (select @XmlSts,@detail,@raw , @rowJSON as jsondata for xml path ('result')) as XML) as dt
GO
GO
CREATE PROCEDURE dbo.MG_MS_MEMO_SOURCE_AVAILABLE
@PARAMETER xml
---XencX---with encryption
as
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
declare @xStsnoheader varchar(2)
,@xStsno varchar(2)
,@xStsdes varchar(max)
,@XmlSts xml
,@raw xml
,@rowspage int
,@page int
,@desc int
,@sort varchar(255)
,@rowformat varchar(255)
,@XMLMAPING xml
,@TABELDATA varchar(max)
,@OutSortBy varchar(255)
,@rumusWhere varchar(max)
,@search xml
,@SQLString nvarchar(MAX)
,@fixWhere nvarchar(2000)
,@ParmDefinition nvarchar(MAX)
,@detail xml
,@totalpages bigint
,@rowJSON varchar(max);
set @xStsnoheader='01';
set @xStsno='00';
set @xStsdes='Success';
declare @ms_memo_detail_id bigint ,@mode_source int
SELECT
@rowspage=isnull(tempTable.xItem.value('@rowspage', 'int'),0)
,@page=isnull(tempTable.xItem.value('@page', 'int'),1)
,@desc=isnull(tempTable.xItem.value('@desc', 'bit'),0)
,@sort=isnull(tempTable.xItem.value('@sort', 'varchar(255)'),'r1')
,@rowformat=isnull(tempTable.xItem.value('@rowformat', 'varchar(255)'),'xml')
,@ms_memo_detail_id=isnull(tempTable.xItem.value('ms_memo_detail_id[1]', 'bigint'),-1)
,@mode_source=isnull(tempTable.xItem.value('mode_source[1]', 'int'),0)
FROM @PARAMETER.nodes('param/field') tempTable(xItem)
----#####START SOURCE#####
----SELECT gab_id, detail_in_id, mode_source_gab, mode_source_name_2, doc_no, doc_date, quantity_sisa, uom, receive_no, receive_date, info, qty_out, qty_plan, ms_item_custom_no, ms_item_base_no, part_no, part_name, mode_source, mode_source_name, ms_item_id, id_source_header, ori_detail_in_id, ms_type_id, gab_mode_source, mode_source_2, status_id
----FROM vw_ms_memo_detail_suplly_available;
----#####END SOURCE#####
set @XMLMAPING =
'
declare @check_ms_memo_detail_id bigint
,@check_ms_item_id bigint
,@check_ms_memo_type_id int
,@check_doc_date date
declare @total_quantity decimal(38,4)
SELECT top (1)
@check_ms_memo_detail_id=dt_ms_memo_detail.ms_memo_detail_id
, @check_ms_item_id=dt_ms_memo_detail.ms_item_id
, @check_ms_memo_type_id=dt_ms_memo_header.ms_memo_type_id
, @check_doc_date=dt_ms_memo_header.doc_date
, @total_quantity=dt_ms_memo_detail.quantity
FROM dt_ms_memo_detail INNER JOIN
dt_ms_memo_header ON dt_ms_memo_detail.ms_memo_header_id = dt_ms_memo_header.ms_memo_header_id
WHERE (dt_ms_memo_detail.ms_memo_detail_id = @ms_memo_detail_id)
declare @total_avialable decimal(38,4)
declare @total_supply decimal(38,4)
set @total_supply=(isnull((SELECT SUM(quantity) AS Expr1
FROM vw_ms_memo_detail_suplly
where ms_memo_detail_id=@ms_memo_detail_id
),0))
--SELECT gab_id, quantity ,ms_memo_detail_supply_id
-- FROM vw_ms_memo_detail_suplly
-- WHERE (ms_memo_detail_id = cast(@ms_memo_detail_id as varchar(255) ))
--FROM vw_ms_memo_detail_suplly_available' + case when @mode_source=0 then '_production' when @mode_source=1 then '_stock' else '' end + ' as source_data LEFT OUTER JOIN
set @TABELDATA=
' FROM vw_ms_memo_detail_suplly_available' + case when @mode_source=0 then '_production' when @mode_source=1 then '_stock' else '' end +
' as source_data ';
if @check_ms_memo_type_id=0
begin
set @fixWhere ='((not (ms_item_id=' + cast(@check_ms_item_id as varchar(255)) + ')) AND (allow_to_scrap=1)) AND ';
end
else if @check_ms_memo_type_id=1
begin
set @fixWhere ='((ms_item_id=' + cast(@check_ms_item_id as varchar(255)) + ') AND (allow_to_repair=1)) AND ';
end
else if @check_ms_memo_type_id=2
begin
set @fixWhere ='((ms_item_id=' + cast(@check_ms_item_id as varchar(255)) + ') AND (allow_to_selling=1)) AND ';
end
EXEC [dbo].[MyRUMUSWhereAndSortView10]
@PARAMETER =@PARAMETER
,@XMLMAPING = @XMLMAPING
,@fixWhere =@fixWhere
,@Sort =@sort
,@desc = @desc
,@OutSortBy = @OutSortBy OUTPUT
,@rumusWhere = @rumusWhere OUTPUT
,@search = @search OUTPUT;
--OLAH DETAIL +++++++++++++++++++++
declare @jmlrow bigint;
SET @SQLString = N' SELECT @jmlrow=count(*) ,@total_avialable= sum(isnull(quantity_sisa,0))' +@TABELDATA+ @rumusWhere + '' ;
SET @ParmDefinition = N'@check_doc_date date ,@total_avialable decimal(38,4) OUTPUT, @jmlrow bigint OUTPUT';
EXECUTE sp_executesql @SQLString, @ParmDefinition
,@check_doc_date =@check_doc_date
,@total_avialable=@total_avialable OUTPUT
,@jmlrow = @jmlrow OUTPUT
declare @SISAROWS bigint
set @SISAROWS =@jmlrow % @rowspage
if @SISAROWS=0
set @totalpages= @jmlrow/@rowspage
Else
set @totalpages= ((@jmlrow-@SISAROWS)/@rowspage) + 1;
set @detail=(
select
@sort as 'sort'
,@desc as 'desc'
,@rowspage as 'rowspage'
,@jmlrow as 'rows'
,@totalpages as 'pages'
,@search as 'search'
for xml path('detail') )
if Upper(@rowformat)='XML'
begin
SET @SQLString =
N' set @raw=( SELECT rn , rID , r1 , r2 , r3 , r4 , r5 , r6 , r7 , r8 , r9 , r10 , r11 , r12 , r13 , r14 , r15 , r16 , r17 , r18 , r19 , r20 , r21 , r22 , r23 , r24 , r25 ,r_s , r_u , r_uq, r_ua, r_uid FROM( SELECT ROW_NUMBER() OVER(ORDER BY ' + @OutSortBy +
') as rn , source_data.gab_id AS rID , source_data.detail_in_id AS r1 , source_data.mode_source_gab AS r2 , source_data.mode_source_name_2 AS r3 , source_data.doc_no AS r4 , dbo.date_to_str(source_data.doc_date ) AS r5 , cast(source_data.quantity_sisa AS decimal(18,4)) AS r6 , source_data.uom AS r7 , source_data.receive_no AS r8 , dbo.date_to_str(source_data.receive_date ) AS r9 , source_data.info AS r10 , cast(source_data.qty_out AS decimal(18,4)) AS r11 , cast(source_data.qty_plan AS decimal(18,4)) AS r12 , source_data.ms_item_custom_no AS r13 , source_data.ms_item_base_no AS r14 , source_data.part_no AS r15 , source_data.part_name AS r16 , source_data.mode_source AS r17 , source_data.mode_source_name AS r18 , source_data.ms_item_id AS r19 , source_data.id_source_header AS r20 , source_data.ori_detail_in_id AS r21 , source_data.ms_type_id AS r22 , source_data.gab_mode_source AS r23 , source_data.mode_source_2 AS r24 , source_data.status_id AS r25 , case when source_data.doc_date>@check_doc_date then 0 else 1 end as r_s , cast(case when used_table.gab_id is null then 0 else 1 end as bit) as r_u , cast(isnull(used_table.quantity,0) AS decimal(18,4)) as r_uq , cast(source_data.quantity_sisa + isnull(used_table.quantity,0) AS decimal(18,4)) as r_ua , isnull(used_table.ms_memo_detail_supply_id,-1) as r_uid '
+ @TABELDATA + @rumusWhere + ' ) XDATA '
set @SQLString = @SQLString + 'WHERE rN BETWEEN (' + cast((@page) as varchar(max)) +' -1) * ' + cast(@ROWSPAGE as varchar(max)) +
' + 1 AND '+ CAST(@page * @ROWSPAGE as varchar(max)) + ' for xml PATH(''xrow'')) '
SET @ParmDefinition = N'@check_doc_date date ,@raw XML OUTPUT';
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@check_doc_date=@check_doc_date,
@raw = @raw OUTPUT
end
else if Upper(@rowformat)='JSON'
begin
SET @SQLString =
N' BEGIN TRY DROP TABLE #data_supply END TRY BEGIN CATCH END CATCH CREATE TABLE #data_supply (gab_id varchar(255) primary key , quantity decimal(24,4), ms_memo_detail_supply_id bigint ) insert into #data_supply SELECT gab_id, sum(isnull(quantity,0)) ,ms_memo_detail_supply_id FROM vw_ms_memo_detail_suplly WHERE (ms_memo_detail_id ='+ cast(@ms_memo_detail_id as varchar(255) )+
') group by gab_id, ms_memo_detail_supply_id set @rowJSON=(''['' + STUFF(( SELECT '',{"rn":"'' + dbo.JSONEscaped(rn) + ''","rID":"'' + dbo.JSONEscaped(rID) + ''","r1":"'' + dbo.JSONEscaped(r1) + ''","r2":"'' + dbo.JSONEscaped(r2) + ''","r3":"'' + dbo.JSONEscaped(r3) + ''","r4":"'' + dbo.JSONEscaped(r4) + ''","r5":"'' + dbo.JSONEscaped(r5) + ''","r6":"'' + dbo.JSONEscaped(r6) + ''","r7":"'' + dbo.JSONEscaped(r7) + ''","r8":"'' + dbo.JSONEscaped(r8) + ''","r9":"'' + dbo.JSONEscaped(r9) + ''","r10":"'' + dbo.JSONEscaped(r10) + ''","r11":"'' + dbo.JSONEscaped(r11) + ''","r12":"'' + dbo.JSONEscaped(r12) + ''","r13":"'' + dbo.JSONEscaped(r13) + ''","r14":"'' + dbo.JSONEscaped(r14) + ''","r15":"'' + dbo.JSONEscaped(r15) + ''","r16":"'' + dbo.JSONEscaped(r16) + ''","r17":"'' + dbo.JSONEscaped(r17) + ''","r18":"'' + dbo.JSONEscaped(r18) + ''","r19":"'' + dbo.JSONEscaped(r19) + ''","r20":"'' + dbo.JSONEscaped(r20) + ''","r21":"'' + dbo.JSONEscaped(r21) + ''","r22":"'' + dbo.JSONEscaped(r22) + ''","r23":"'' + dbo.JSONEscaped(r23) + ''","r24":"'' + dbo.JSONEscaped(r24) + ''","r25":"'' + dbo.JSONEscaped(r25) + ''","r_s":"'' + dbo.JSONEscaped(case when xdata.doc_date>@check_doc_date then 0 else 1 end ) + ''","r_u":"'' + dbo.JSONEscaped(cast(case when used_table.gab_id is null then 0 else 1 end as bit)) + ''","r_uq":"'' + dbo.JSONEscaped(cast(isnull(used_table.quantity,0) AS decimal(18,4))) + ''","r_ua":"'' + dbo.JSONEscaped(cast(xdata.quantity_sisa + isnull(used_table.quantity,0) AS decimal(18,4))) + ''","r_uid":"'' + dbo.JSONEscaped(isnull(used_table.ms_memo_detail_supply_id,-1)) + ''"}'' FROM( SELECT 1 as rn , source_data.gab_id AS rID , source_data.detail_in_id AS r1 , source_data.mode_source_gab AS r2 , source_data.mode_source_name_2 AS r3 , source_data.doc_no AS r4 , dbo.date_to_str(source_data.doc_date ) AS r5 , cast(source_data.quantity_sisa AS decimal(18,4)) AS r6 , source_data.uom AS r7 , source_data.receive_no AS r8 , dbo.date_to_str(source_data.receive_date ) AS r9 , source_data.info AS r10 , cast(source_data.qty_out AS decimal(18,4)) AS r11 , cast(source_data.qty_plan AS decimal(18,4)) AS r12 , source_data.ms_item_custom_no AS r13 , source_data.ms_item_base_no AS r14 , source_data.part_no AS r15 , source_data.part_name AS r16 , source_data.mode_source AS r17 , source_data.mode_source_name AS r18 , source_data.ms_item_id AS r19 , source_data.id_source_header AS r20 , source_data.ori_detail_in_id AS r21 , source_data.ms_type_id AS r22 , source_data.gab_mode_source AS r23 , source_data.mode_source_2 AS r24 , source_data.status_id AS r25 ,source_data.doc_date ,source_data.quantity_sisa '
+ @TABELDATA + @rumusWhere +
' ORDER BY ' + @OutSortBy +
N' OFFSET ' + cast(((@page -1)*@ROWSPAGE) as varchar(max)) +
' ROWS FETCH NEXT ' + cast(@ROWSPAGE as varchar(max)) +
' ROWS ONLY )xdata LEFT OUTER JOIN #data_supply AS used_table ON xdata.rID = used_table.gab_id for xml PATH(''''), type ).value(''.'', ''varchar(max)''), 1, 1, '''') + '']'')';
SET @ParmDefinition = N'@check_doc_date date ,@rowJSON varchar(max) OUTPUT';
--print @SQLString
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@check_doc_date=@check_doc_date,
@rowJSON = @rowJSON OUTPUT
set @rowJSON ='{"userdata":{' +
'"total_avialable":"'+cast(cast( isnull(@total_avialable,0) as decimal(38,4))as varchar(255)) +'"' +
',"total_quantity":"'+cast(cast( isnull(@total_quantity,0) as decimal(38,4))as varchar(255)) +'"' +
',"total_supply":"'+ cast(cast( isnull(@total_supply,0) as decimal(38,4))as varchar(255)) +'"'+
',"total_less":"'+ cast(cast( isnull(@total_quantity,0)-isnull(@total_supply,0) as decimal(38,4))as varchar(255)) +'"'+
'}, "page":"'+ cast(@page as varchar(255)) + '","total":"' + cast(@totalpages as varchar(255)) + '","records":"' + cast(@jmlrow as varchar(255)) +'","rows":' + isnull(@rowJSON,'{}') + '}'
end
f:
set @XmlSts=(select @xStsnoheader + @xStsno as 'no', @xStsdes as 'des' for xml path('sts'))
SET NOCOUNT OFF
select CAST( (select @XmlSts,@detail,@raw , @rowJSON as jsondata for xml path ('result')) as XML) as dt
GO
Depends On
4Used By
No items found