viw_accworkcenterreport.md 4.77 KB

Edit Raw Blame History



viw_accworkcenterreport (view)


VIEW


Updatable: YES

Definer: xlyprint@%


Columns


#
Name
Type
Null


1
sId
varchar(63)
YES


2
sSlaveId
varchar(63)
YES


3
sConfirmMonth
varchar(100)
NO


4
sDepartId
varchar(255)
NO


5
sWorkCenterName
varchar(255)
NO


6
sWorkCenterId
varchar(100)
NO


7
sBrandsId
varchar(100)
NO


8
sSubsidiaryId
varchar(100)
NO


9
dProcessQty
decimal(18,6)
NO


10
dMonthHour
decimal(18,6)
NO


11
dMonthArea
decimal(18,6)
NO


12
dColorQty
decimal(18,6)
NO


13
dMaterialsMoney
decimal(41,6)
YES


14
dPeopleMoney
decimal(43,6)
YES


15
dMakeMoney
decimal(52,6)
YES


16
dOutsideMoney
decimal(43,6)
YES


17
dOtherMoney
decimal(45,6)
YES


18
dMakeOhterMoney
decimal(53,6)
YES


19
iAssignStandardName
int
NO


20
dPeopleProcessPrice
decimal(53,10)
YES


21
dPeopleAreaPrice
decimal(53,10)
YES


22
dPeopleColorPrice
decimal(53,10)
YES


23
dPeopleHourPrice
decimal(53,10)
YES


24
dMakeOtherProcessPrice
decimal(63,10)
YES


25
dMakeOtherAreaPrice
decimal(63,10)
YES


26
dMakeOtherColorPrice
decimal(63,10)
YES


27
dMakeOtherHourPrice
decimal(63,10)
YES


28
dMaterialsMakeOtherHourPrice
decimal(64,10)
YES


Definition
select concat(date_format(now(),'%Y%m%d%H%i%s'),ceiling(((rand() * 9000000) + 1000000)),ceiling(((rand() * 9000000000) + 1000000000))) AS `sId`,concat(date_format(now(),'%Y%m%d%H%i%s'),ceiling(((rand() * 9000000) + 1000000)),ceiling(((rand() * 9000000000) + 1000000000))) AS `sSlaveId`,`a`.`sConfirmMonth` AS `sConfirmMonth`,`a`.`sDepartId` AS `sDepartId`,`a`.`sName` AS `sWorkCenterName`,`a`.`sSrcId` AS `sWorkCenterId`,`a`.`sBrandsId` AS `sBrandsId`,`a`.`sSubsidiaryId` AS `sSubsidiaryId`,`a`.`dProcessQty` AS `dProcessQty`,`a`.`dMonthHour` AS `dMonthHour`,`a`.`dMonthArea` AS `dMonthArea`,`a`.`dColorQty` AS `dColorQty`,`b`.`dMaterialsMoney` AS `dMaterialsMoney`,`b`.`dPeopleMoney` AS `dPeopleMoney`,`b`.`dMakeMoney` AS `dMakeMoney`,`b`.`dOutsideMoney` AS `dOutsideMoney`,`b`.`dOtherMoney` AS `dOtherMoney`,(`b`.`dMakeMoney` + `b`.`dOtherMoney`) AS `dMakeOhterMoney`,`d`.`iAssignStandardName` AS `iAssignStandardName`,(case when (`d`.`iAssignStandardName` = '2') then (`b`.`dPeopleMoney` / `a`.`dProcessQty`) else 0 end) AS `dPeopleProcessPrice`,(case when (`d`.`iAssignStandardName` = '3') then (`b`.`dPeopleMoney` / `a`.`dMonthArea`) else 0 end) AS `dPeopleAreaPrice`,(case when (`d`.`iAssignStandardName` = '4') then (`b`.`dPeopleMoney` / `a`.`dColorQty`) else 0 end) AS `dPeopleColorPrice`,(`b`.`dPeopleMoney` / `a`.`dMonthHour`) AS `dPeopleHourPrice`,(case when (`d`.`iAssignStandardName` = '2') then ((`b`.`dMakeMoney` + `b`.`dOtherMoney`) / `a`.`dProcessQty`) else 0 end) AS `dMakeOtherProcessPrice`,(case when (`d`.`iAssignStandardName` = '3') then ((`b`.`dMakeMoney` + `b`.`dOtherMoney`) / `a`.`dMonthArea`) else 0 end) AS `dMakeOtherAreaPrice`,(case when (`d`.`iAssignStandardName` = '4') then ((`b`.`dMakeMoney` + `b`.`dOtherMoney`) / `a`.`dColorQty`) else 0 end) AS `dMakeOtherColorPrice`,((`b`.`dMakeMoney` + `b`.`dOtherMoney`) / `a`.`dMonthHour`) AS `dMakeOtherHourPrice`,(((`b`.`dPeopleMoney` + `b`.`dMakeMoney`) + `b`.`dOtherMoney`) / `a`.`dMonthHour`) AS `dMaterialsMakeOtherHourPrice` from ((`xlyweberp_saas_ai`.`accworkcenter` `a` join (select `c`.`sConfirmMonth` AS `sConfirmMonth`,`c`.`sWorkCenterId` AS `sWorkCenterId`,sum((`c`.`dExpense1Money` + `c`.`dExpense2Money`)) AS `dMaterialsMoney`,sum((((`c`.`dExpense3Money` + `c`.`dExpense4Money`) + `c`.`dExpense5Money`) + `c`.`dExpense6Money`)) AS `dPeopleMoney`,sum(((((((((((((`c`.`dExpense7Money` + `c`.`dExpense8Money`) + `c`.`dExpense9Money`) + `c`.`dExpense10Money`) + `c`.`dExpense11Money`) + `c`.`dExpense12Money`) + `c`.`dExpense13Money`) + `c`.`dExpense14Money`) + `c`.`dExpense15Money`) + `c`.`dExpense16Money`) + `c`.`dExpense17Money`) + `c`.`dExpense18Money`) + `c`.`dExpense19Money`)) AS `dMakeMoney`,sum((((`c`.`dExpense20Money` + `c`.`dExpense21Money`) + `c`.`dExpense22Money`) + `c`.`dExpense23Money`)) AS `dOutsideMoney`,sum((((((`c`.`dExpense24Money` + `c`.`dExpense25Money`) + `c`.`dExpense26Money`) + `c`.`dExpense27Money`) + `c`.`dExpense28Money`) + `c`.`dExpense29Money`)) AS `dOtherMoney` from `xlyweberp_saas_ai`.`accordercostanalysis` `c` where (`c`.`iType` = 2) group by `c`.`sConfirmMonth`,`c`.`sWorkCenterId`) `b` on(((`a`.`sSrcId` = `b`.`sWorkCenterId`) and (`a`.`sConfirmMonth` = `b`.`sConfirmMonth`)))) join `xlyweberp_saas_ai`.`sisworkcenter` `d` on((`a`.`sSrcId` = `d`.`sId`)))


Narrative

No human-written narrative yet.