create procedure [dbo].[InvoiceExpenseInsertUpdate4]
@DomainID int
, @InvoiceUIDList varchar(max)
, @ExpenseMetricSkuValueTable ExpenseMetricSkuValueTable readonly
, @LoginID int
, @LoginEmail nvarchar(200)
, @UserID varchar(50)
, @LoginName nvarchar(200)
, @AppVersion varchar(15) = 'not passed'
/\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* Logic \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*
Create a PX Expense according the given Allocation method among :
--0 -> Even Split at P1 level
--1 -> Gross Sales (GSR)
--2 -> Retail Sales
--3 -> Manual, Data is saved as P1 level with values provided by .Net
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/
as
begin

set nocount on

/\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*
Helpers
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/
declare @ModifiedBy varchar(200) = @LoginEmail -- :)
-- declare @AllocationMethodEven int = 0
-- declare @AllocationMethodGSR int = 1
-- declare @AllocationMethodManual int = 3

drop table if exists #InvoiceUIDList
create table #InvoiceUIDList(
Uid uniqueidentifier
)

insert into #InvoiceUIDList(Uid)
select value from string\_split(@InvoiceUIDList, ',')

drop table if exists #InvoiceExpense
create table #InvoiceExpense(
RowNo int identity(1,1) -- will be used to manage insert split
, InvoiceExpenseID int
, AllocationMethod tinyint
, Interval int null
, SkuID2 int
, Date date
, MetricID int
, Value decimal(18,5)
)

insert #InvoiceExpense(
InvoiceExpenseID
, AllocationMethod
, Interval
, SkuID2
, Date
, MetricID
, Value
)
select distinct
e.InvoiceExpenseID
, e.AllocationMethod
, e.Interval
, p1.SkuID2
, e.Date
, e.MetricID
, e.Value
from
@ExpenseMetricSkuValueTable e
join LP\_Sku p1 on e.SkuMemberID = p1.MemberID
join #InvoiceUIDList uid on e.InvoiceUID = uid.Uid
where
p1.DomainID = @DomainID

drop table if exists #InvoiceExpenseSku
create table #InvoiceExpenseSku(
RowNo int
, InvoiceExpenseID int
, SkuID2 int
, MetricID int
, [Date] date
, Value decimal(18,5)
)

insert #InvoiceExpenseSku
select
e.RowNo
, e.InvoiceExpenseID
, et.P1SkuID2
, e.MetricID
, e.Date
, et.P1Value
from
#InvoiceExpense e
join @ExpenseMetricSkuValueTable et on
e.InvoiceExpenseID = et.InvoiceExpenseID
and e.MetricID = et.MetricID
and e.Date = et.Date
and e.AllocationMethod = et.AllocationMethod
join LP\_Sku px on
px.MemberID = et.SkuMemberID
and px.SkuID2 = e.SkuID2

/\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*
Merge to physical tables
재연산 프로시저 [dbo].[InvoiceExpenseInsertUpdate4]
도메인ID int
, @InvoiceUIDList varchar(max)
, @ExpenseMetricSkuValueTable 비용 측정값 테이블 읽기 전용
, @로그인ID int
, @로그인이메일 nvarchar(200)
, @UserID varchar(50)
, @LoginName nvarchar(200)
, @AppVersion varchar(15) = '전달되지 않음'
/\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* Logic \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*
중에서 주어진 할당 방법에 따라 PX 비용을 생성합니다:
--0 -> P1 수준에서 균등 분할
-1 -> 총 판매액(GSR)
--2 -> 소매 판매
-3 -> 수동, 데이터는 .Net에서 제공한 값으로 P1 수준으로 저장됩니다.
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/
as
begin

SET NOCOUNT ON

/\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*
Helpers
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/
선언 @ModifiedBy varchar(200) = @로그인이메일 -- :)
-- 선언 @AllocationMethodEven int = 0
-- 선언 @AllocationMethodGSR int = 1
-- 선언 @AllocationMethodManual int = 3

인보이스유아이디리스트가 있는 경우 테이블 삭제
테이블 생성 #InvoiceUIDList(
Uid 고유 식별자
)

인보이스유아이디리스트에 삽입(Uid)
문자열\_split(@InvoiceUIDList, ',')에서 값 선택

인보이스 비용 테이블이 있는 경우 테이블 삭제
인보이스 비용 테이블 생성(
RowNo int identity(1,1) -- 삽입 분할을 관리하는 데 사용됩니다.
, InvoiceExpenseID int
, 할당 메서드 tinyint
, Interval int null
, SkuID2 int
, 날짜 날짜
, MetricID int
, 값 십진수(18,5)
)

삽입 #인보이스비용(
InvoiceExpenseID
, 할당 방법
, Interval
, SkuID2
, Date
, MetricID
, Value
)
고유 선택
e.InvoiceExpenseID
, e.AllocationMethod
, e.Interval
, p1.SkuID2
, e.Date
, e.MetricID
, e.Value
에서
비용 메트릭스큐밸류테이블 e
조인 LP\_Sku p1 on e.SkuMemberID = p1.MemberID
조인 #InvoiceUIDList uid on e.InvoiceUID = uid.Uid
여기서
p1.DomainID = @DomainID

인보이스 비용 테이블이 있으면 삭제합니다.
인보이스 비용 테이블 생성(
RowNo int
, InvoiceExpenseID int
, SkuID2 int
, MetricID int
, [Date] 날짜
, 값 십진수(18,5)
)

삽입 #InvoiceExpenseSku
select
e.RowNo
, e.InvoiceExpenseID
, et.P1SkuID2
, e.MetricID
, e.Date
, et.P1Value
에서
#인보이스 비용 e
조인 @ExpenseMetricSkuValueTable et on
e.InvoiceExpenseID = et.InvoiceExpenseID
및 e.MetricID = et.MetricID
및 e.Date = et.Date
및 e.AllocationMethod = et.AllocationMethod
LP\_Sku px에 조인
px.MemberID = et.SkuMemberID
및 px.SkuID2 = e.SkuID2

/\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*
실제 테이블에 병합