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_OUT_CAR_LINE_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_car_line_out_detail_id bigint
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_car_line_out_detail_id=isnull(tempTable.xItem.value('ms_car_line_out_detail_id[1]', 'int'),-1)
FROM @PARAMETER.nodes('param/field') tempTable(xItem)
----#####START SOURCE#####
----SELECT ms_in_item_detail_id, mode_source, mode_source_name, mode_source_2, mode_source_name_2, car, bc_no, bc_date, receive_no, receive_date, status_id, quantity_pakai, quantity_sisa, qty_out, qty_plan, car_line_out, memo_out, car_line_plan, memo_plan, ms_type_id, ms_item_custom_no, ms_item_base_no, part_no, part_name, uom, ori_ms_in_item_detail_id, id_source_header
----FROM vw_ms_item_in_detail;
----#####END SOURCE#####
declare @check_ms_car_line_out_detail_id bigint
declare @check_ms_item_id bigint
declare @check_doc_date date
declare @total_quantity decimal(38,4)
SELECT top(1)
@check_ms_car_line_out_detail_id=dt_ms_car_line_out_detail.ms_car_line_out_detail_id
, @check_ms_item_id=dt_ms_car_line_out_detail.ms_item_id
, @check_doc_date=dt_ms_car_line_out_header.doc_date
,@total_quantity= dt_ms_car_line_out_detail.quantity
FROM dt_ms_car_line_out_detail INNER JOIN
dt_ms_car_line_out_header ON dt_ms_car_line_out_detail.ms_car_line_out_header_id = dt_ms_car_line_out_header.ms_car_line_out_header_id
WHERE (dt_ms_car_line_out_detail.ms_car_line_out_detail_id = @ms_car_line_out_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_out_car_line_detail_suplly
where ms_car_line_out_detail_id=@ms_car_line_out_detail_id
),0))
declare @sta_end_date date
SELECT TOP (1) @sta_end_date=end_date
FROM dt_ms_cost_final_avg
WHERE (ms_cost_final_avg_id = 0)
set @XMLMAPING =
'
';
set @TABELDATA=
' FROM vw_ms_item_in_detail LEFT OUTER JOIN ( SELECT ms_in_item_detail_id, quantity,ms_item_out_detail_id FROM dt_ms_item_out_detail WHERE (mode_source = 0) AND (id_source =@ms_car_line_out_detail_id) ) AS used_table ON vw_ms_item_in_detail.ms_in_item_detail_id = used_table.ms_in_item_detail_id ';
set @fixWhere =
'((vw_ms_item_in_detail.ms_item_id=@check_ms_item_id) AND (vw_ms_item_in_detail.status_id=1) AND (vw_ms_item_in_detail.quantity_sisa>0)) and (vw_ms_item_in_detail.status_id = 1) and ( (vw_ms_item_in_detail.mode_source=1 and vw_ms_item_in_detail.receive_date<=@sta_end_date) or (not(vw_ms_item_in_detail.mode_source=1) and vw_ms_item_in_detail.receive_date>@sta_end_date)) AND ';
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'@sta_end_date date , @ms_car_line_out_detail_id bigint, @check_ms_item_id bigint ,@check_doc_date date , @jmlrow bigint OUTPUT, @total_avialable decimal(38,4) OUTPUT';
EXECUTE sp_executesql @SQLString, @ParmDefinition
,@sta_end_date=@sta_end_date
,@ms_car_line_out_detail_id =@ms_car_line_out_detail_id
,@check_ms_item_id = @check_ms_item_id
,@check_doc_date = @check_doc_date
,@jmlrow = @jmlrow OUTPUT
,@total_avialable=@total_avialable 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'
,@total_avialable as 'qty_available'
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 , r26 ,r_s , r_u , r_uq, r_ua, r_uid FROM( SELECT ROW_NUMBER() OVER(ORDER BY ' + @OutSortBy +
') as rn , vw_ms_item_in_detail.ms_in_item_detail_id AS rID , vw_ms_item_in_detail.mode_source AS r1 , vw_ms_item_in_detail.mode_source_name AS r2 , vw_ms_item_in_detail.mode_source_2 AS r3 , vw_ms_item_in_detail.mode_source_name_2 AS r4 , vw_ms_item_in_detail.car AS r5 , vw_ms_item_in_detail.bc_no AS r6 , dbo.date_to_str(vw_ms_item_in_detail.bc_date ) AS r7 , vw_ms_item_in_detail.receive_no AS r8 , dbo.date_to_str(vw_ms_item_in_detail.receive_date ) AS r9 , vw_ms_item_in_detail.status_id AS r10 , cast(vw_ms_item_in_detail.quantity_pakai AS decimal(18,4)) AS r11 , cast(vw_ms_item_in_detail.quantity_sisa AS decimal(18,4)) AS r12 , cast(vw_ms_item_in_detail.qty_out AS decimal(18,4)) AS r13 , cast(vw_ms_item_in_detail.qty_plan AS decimal(18,4)) AS r14 , cast(vw_ms_item_in_detail.car_line_out AS decimal(18,4)) AS r15 , cast(vw_ms_item_in_detail.memo_out AS decimal(18,4)) AS r16 , cast(vw_ms_item_in_detail.car_line_plan AS decimal(18,4)) AS r17 , cast(vw_ms_item_in_detail.memo_plan AS decimal(18,4)) AS r18 , vw_ms_item_in_detail.ms_type_id AS r19 , vw_ms_item_in_detail.ms_item_custom_no AS r20 , vw_ms_item_in_detail.ms_item_base_no AS r21 , vw_ms_item_in_detail.part_no AS r22 , vw_ms_item_in_detail.part_name AS r23 , vw_ms_item_in_detail.uom AS r24 , vw_ms_item_in_detail.ori_ms_in_item_detail_id AS r25 , vw_ms_item_in_detail.id_source_header AS r26 , (case when @check_doc_date is null or vw_ms_item_in_detail.receive_date>@check_doc_date then 0 else 1 end ) as r_s , cast(case when used_table.ms_in_item_detail_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(vw_ms_item_in_detail.quantity_sisa + isnull(used_table.quantity,0) AS decimal(18,4)) as r_ua , isnull(used_table.ms_item_out_detail_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'@sta_end_date date ,@check_ms_item_id bigint ,@check_doc_date date , @raw XML OUTPUT';
EXECUTE sp_executesql @SQLString, @ParmDefinition
,@sta_end_date=@sta_end_date
,@ms_car_line_out_detail_id=@ms_car_line_out_detail_id
,@check_ms_item_id = @check_ms_item_id
,@check_doc_date = @check_doc_date
,@raw = @raw OUTPUT
end
else if Upper(@rowformat)='JSON'
begin
SET @SQLString =
N' 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) + ''","r26":"'' + dbo.JSONEscaped(r26) + ''","r_s":"'' + dbo.JSONEscaped(r_s) + ''","r_u":"'' + dbo.JSONEscaped(r_u) + ''","r_uq":"'' + dbo.JSONEscaped(r_uq) + ''","r_ua":"'' + dbo.JSONEscaped(r_ua) + ''","r_uid":"'' + dbo.JSONEscaped(r_uid) + ''"}'' FROM( SELECT ROW_NUMBER() OVER(ORDER BY ' + @OutSortBy +
') as rn , vw_ms_item_in_detail.ms_in_item_detail_id AS rID , vw_ms_item_in_detail.mode_source AS r1 , vw_ms_item_in_detail.mode_source_name AS r2 , vw_ms_item_in_detail.mode_source_2 AS r3 , vw_ms_item_in_detail.mode_source_name_2 AS r4 , vw_ms_item_in_detail.car AS r5 , vw_ms_item_in_detail.bc_no AS r6 , dbo.date_to_str(vw_ms_item_in_detail.bc_date ) AS r7 , vw_ms_item_in_detail.receive_no AS r8 , dbo.date_to_str(vw_ms_item_in_detail.receive_date ) AS r9 , vw_ms_item_in_detail.status_id AS r10 , cast(vw_ms_item_in_detail.quantity_pakai AS decimal(18,4)) AS r11 , cast(vw_ms_item_in_detail.quantity_sisa AS decimal(18,4)) AS r12 , cast(vw_ms_item_in_detail.qty_out AS decimal(18,4)) AS r13 , cast(vw_ms_item_in_detail.qty_plan AS decimal(18,4)) AS r14 , cast(vw_ms_item_in_detail.car_line_out AS decimal(18,4)) AS r15 , cast(vw_ms_item_in_detail.memo_out AS decimal(18,4)) AS r16 , cast(vw_ms_item_in_detail.car_line_plan AS decimal(18,4)) AS r17 , cast(vw_ms_item_in_detail.memo_plan AS decimal(18,4)) AS r18 , vw_ms_item_in_detail.ms_type_id AS r19 , vw_ms_item_in_detail.ms_item_custom_no AS r20 , vw_ms_item_in_detail.ms_item_base_no AS r21 , vw_ms_item_in_detail.part_no AS r22 , vw_ms_item_in_detail.part_name AS r23 , vw_ms_item_in_detail.uom AS r24 , vw_ms_item_in_detail.ori_ms_in_item_detail_id AS r25 , vw_ms_item_in_detail.id_source_header AS r26 , (case when @check_doc_date is null or vw_ms_item_in_detail.receive_date>@check_doc_date then 0 else 1 end ) as r_s , cast(case when used_table.ms_in_item_detail_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(vw_ms_item_in_detail.quantity_sisa + isnull(used_table.quantity,0) AS decimal(18,4)) as r_ua , isnull(used_table.ms_item_out_detail_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(''''), type ).value(''.'', ''varchar(max)''), 1, 1, '''') + '']'')';
SET @ParmDefinition = N'@sta_end_date date , @ms_car_line_out_detail_id bigint, @check_ms_item_id bigint ,@check_doc_date date , @rowJSON varchar(max) OUTPUT';
EXECUTE sp_executesql @SQLString, @ParmDefinition
,@sta_end_date=@sta_end_date
,@ms_car_line_out_detail_id =@ms_car_line_out_detail_id
,@check_ms_item_id = @check_ms_item_id
,@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_OUT_CAR_LINE_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_car_line_out_detail_id bigint
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_car_line_out_detail_id=isnull(tempTable.xItem.value('ms_car_line_out_detail_id[1]', 'int'),-1)
FROM @PARAMETER.nodes('param/field') tempTable(xItem)
----#####START SOURCE#####
----SELECT ms_in_item_detail_id, mode_source, mode_source_name, mode_source_2, mode_source_name_2, car, bc_no, bc_date, receive_no, receive_date, status_id, quantity_pakai, quantity_sisa, qty_out, qty_plan, car_line_out, memo_out, car_line_plan, memo_plan, ms_type_id, ms_item_custom_no, ms_item_base_no, part_no, part_name, uom, ori_ms_in_item_detail_id, id_source_header
----FROM vw_ms_item_in_detail;
----#####END SOURCE#####
declare @check_ms_car_line_out_detail_id bigint
declare @check_ms_item_id bigint
declare @check_doc_date date
declare @total_quantity decimal(38,4)
SELECT top(1)
@check_ms_car_line_out_detail_id=dt_ms_car_line_out_detail.ms_car_line_out_detail_id
, @check_ms_item_id=dt_ms_car_line_out_detail.ms_item_id
, @check_doc_date=dt_ms_car_line_out_header.doc_date
,@total_quantity= dt_ms_car_line_out_detail.quantity
FROM dt_ms_car_line_out_detail INNER JOIN
dt_ms_car_line_out_header ON dt_ms_car_line_out_detail.ms_car_line_out_header_id = dt_ms_car_line_out_header.ms_car_line_out_header_id
WHERE (dt_ms_car_line_out_detail.ms_car_line_out_detail_id = @ms_car_line_out_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_out_car_line_detail_suplly
where ms_car_line_out_detail_id=@ms_car_line_out_detail_id
),0))
declare @sta_end_date date
SELECT TOP (1) @sta_end_date=end_date
FROM dt_ms_cost_final_avg
WHERE (ms_cost_final_avg_id = 0)
set @XMLMAPING =
'
set @TABELDATA=
' FROM vw_ms_item_in_detail LEFT OUTER JOIN ( SELECT ms_in_item_detail_id, quantity,ms_item_out_detail_id FROM dt_ms_item_out_detail WHERE (mode_source = 0) AND (id_source =@ms_car_line_out_detail_id) ) AS used_table ON vw_ms_item_in_detail.ms_in_item_detail_id = used_table.ms_in_item_detail_id ';
set @fixWhere =
'((vw_ms_item_in_detail.ms_item_id=@check_ms_item_id) AND (vw_ms_item_in_detail.status_id=1) AND (vw_ms_item_in_detail.quantity_sisa>0)) and (vw_ms_item_in_detail.status_id = 1) and ( (vw_ms_item_in_detail.mode_source=1 and vw_ms_item_in_detail.receive_date<=@sta_end_date) or (not(vw_ms_item_in_detail.mode_source=1) and vw_ms_item_in_detail.receive_date>@sta_end_date)) AND ';
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'@sta_end_date date , @ms_car_line_out_detail_id bigint, @check_ms_item_id bigint ,@check_doc_date date , @jmlrow bigint OUTPUT, @total_avialable decimal(38,4) OUTPUT';
EXECUTE sp_executesql @SQLString, @ParmDefinition
,@sta_end_date=@sta_end_date
,@ms_car_line_out_detail_id =@ms_car_line_out_detail_id
,@check_ms_item_id = @check_ms_item_id
,@check_doc_date = @check_doc_date
,@jmlrow = @jmlrow OUTPUT
,@total_avialable=@total_avialable 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'
,@total_avialable as 'qty_available'
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 , r26 ,r_s , r_u , r_uq, r_ua, r_uid FROM( SELECT ROW_NUMBER() OVER(ORDER BY ' + @OutSortBy +
') as rn , vw_ms_item_in_detail.ms_in_item_detail_id AS rID , vw_ms_item_in_detail.mode_source AS r1 , vw_ms_item_in_detail.mode_source_name AS r2 , vw_ms_item_in_detail.mode_source_2 AS r3 , vw_ms_item_in_detail.mode_source_name_2 AS r4 , vw_ms_item_in_detail.car AS r5 , vw_ms_item_in_detail.bc_no AS r6 , dbo.date_to_str(vw_ms_item_in_detail.bc_date ) AS r7 , vw_ms_item_in_detail.receive_no AS r8 , dbo.date_to_str(vw_ms_item_in_detail.receive_date ) AS r9 , vw_ms_item_in_detail.status_id AS r10 , cast(vw_ms_item_in_detail.quantity_pakai AS decimal(18,4)) AS r11 , cast(vw_ms_item_in_detail.quantity_sisa AS decimal(18,4)) AS r12 , cast(vw_ms_item_in_detail.qty_out AS decimal(18,4)) AS r13 , cast(vw_ms_item_in_detail.qty_plan AS decimal(18,4)) AS r14 , cast(vw_ms_item_in_detail.car_line_out AS decimal(18,4)) AS r15 , cast(vw_ms_item_in_detail.memo_out AS decimal(18,4)) AS r16 , cast(vw_ms_item_in_detail.car_line_plan AS decimal(18,4)) AS r17 , cast(vw_ms_item_in_detail.memo_plan AS decimal(18,4)) AS r18 , vw_ms_item_in_detail.ms_type_id AS r19 , vw_ms_item_in_detail.ms_item_custom_no AS r20 , vw_ms_item_in_detail.ms_item_base_no AS r21 , vw_ms_item_in_detail.part_no AS r22 , vw_ms_item_in_detail.part_name AS r23 , vw_ms_item_in_detail.uom AS r24 , vw_ms_item_in_detail.ori_ms_in_item_detail_id AS r25 , vw_ms_item_in_detail.id_source_header AS r26 , (case when @check_doc_date is null or vw_ms_item_in_detail.receive_date>@check_doc_date then 0 else 1 end ) as r_s , cast(case when used_table.ms_in_item_detail_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(vw_ms_item_in_detail.quantity_sisa + isnull(used_table.quantity,0) AS decimal(18,4)) as r_ua , isnull(used_table.ms_item_out_detail_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'@sta_end_date date ,@check_ms_item_id bigint ,@check_doc_date date , @raw XML OUTPUT';
EXECUTE sp_executesql @SQLString, @ParmDefinition
,@sta_end_date=@sta_end_date
,@ms_car_line_out_detail_id=@ms_car_line_out_detail_id
,@check_ms_item_id = @check_ms_item_id
,@check_doc_date = @check_doc_date
,@raw = @raw OUTPUT
end
else if Upper(@rowformat)='JSON'
begin
SET @SQLString =
N' 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) + ''","r26":"'' + dbo.JSONEscaped(r26) + ''","r_s":"'' + dbo.JSONEscaped(r_s) + ''","r_u":"'' + dbo.JSONEscaped(r_u) + ''","r_uq":"'' + dbo.JSONEscaped(r_uq) + ''","r_ua":"'' + dbo.JSONEscaped(r_ua) + ''","r_uid":"'' + dbo.JSONEscaped(r_uid) + ''"}'' FROM( SELECT ROW_NUMBER() OVER(ORDER BY ' + @OutSortBy +
') as rn , vw_ms_item_in_detail.ms_in_item_detail_id AS rID , vw_ms_item_in_detail.mode_source AS r1 , vw_ms_item_in_detail.mode_source_name AS r2 , vw_ms_item_in_detail.mode_source_2 AS r3 , vw_ms_item_in_detail.mode_source_name_2 AS r4 , vw_ms_item_in_detail.car AS r5 , vw_ms_item_in_detail.bc_no AS r6 , dbo.date_to_str(vw_ms_item_in_detail.bc_date ) AS r7 , vw_ms_item_in_detail.receive_no AS r8 , dbo.date_to_str(vw_ms_item_in_detail.receive_date ) AS r9 , vw_ms_item_in_detail.status_id AS r10 , cast(vw_ms_item_in_detail.quantity_pakai AS decimal(18,4)) AS r11 , cast(vw_ms_item_in_detail.quantity_sisa AS decimal(18,4)) AS r12 , cast(vw_ms_item_in_detail.qty_out AS decimal(18,4)) AS r13 , cast(vw_ms_item_in_detail.qty_plan AS decimal(18,4)) AS r14 , cast(vw_ms_item_in_detail.car_line_out AS decimal(18,4)) AS r15 , cast(vw_ms_item_in_detail.memo_out AS decimal(18,4)) AS r16 , cast(vw_ms_item_in_detail.car_line_plan AS decimal(18,4)) AS r17 , cast(vw_ms_item_in_detail.memo_plan AS decimal(18,4)) AS r18 , vw_ms_item_in_detail.ms_type_id AS r19 , vw_ms_item_in_detail.ms_item_custom_no AS r20 , vw_ms_item_in_detail.ms_item_base_no AS r21 , vw_ms_item_in_detail.part_no AS r22 , vw_ms_item_in_detail.part_name AS r23 , vw_ms_item_in_detail.uom AS r24 , vw_ms_item_in_detail.ori_ms_in_item_detail_id AS r25 , vw_ms_item_in_detail.id_source_header AS r26 , (case when @check_doc_date is null or vw_ms_item_in_detail.receive_date>@check_doc_date then 0 else 1 end ) as r_s , cast(case when used_table.ms_in_item_detail_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(vw_ms_item_in_detail.quantity_sisa + isnull(used_table.quantity,0) AS decimal(18,4)) as r_ua , isnull(used_table.ms_item_out_detail_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(''''), type ).value(''.'', ''varchar(max)''), 1, 1, '''') + '']'')';
SET @ParmDefinition = N'@sta_end_date date , @ms_car_line_out_detail_id bigint, @check_ms_item_id bigint ,@check_doc_date date , @rowJSON varchar(max) OUTPUT';
EXECUTE sp_executesql @SQLString, @ParmDefinition
,@sta_end_date=@sta_end_date
,@ms_car_line_out_detail_id =@ms_car_line_out_detail_id
,@check_ms_item_id = @check_ms_item_id
,@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
5Used By
No items found