AiDOPWarehouse/doc/migrations/2026-04-17_aidopdev_linkage_ddl_part5_view.sql

SET NAMES utf8mb4;
DROP VIEW IF EXISTS `vscm_cgshrk`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `vscm_cgshrk` AS
SELECT
  `d`.`Receiver` AS `Receiver`,
  `d`.`Line` AS `Line`,
  `d`.`QtyReceived` AS `QtyReceived`,
  `m`.`PurOrd` AS `OrdNbr`,
  `m`.`Line` AS `OrdLine`,
  `d`.`Delivery` AS `Delivery`,
  `d`.`ItemNum` AS `ItemNum`,
  `d`.`CreateTime` AS `CreateTime`,
  `d`.`QCNbr` AS `QCNbr`,
  `d`.`StatusByQC` AS `StatusByQC`,
  `d`.`QCDescr` AS `QCDescr`,
  `d`.`RctQty` AS `RctQty`,
  `d`.`QtyReturn` AS `QtyReturn`,
  `m`.`QtyOrded` AS `QtyOrded`,
  IF((`m`.`ReceiptQty` >= `m`.`RctQty`), `m`.`ReceiptQty`, `m`.`RctQty`) AS `ReceiptQty`,
  `m`.`RctQty` AS `yssl`,
  `m`.`QtyReturned` AS `QtyReturned`,
  `d`.`LotSerial` AS `LotSerial`
FROM (
  (`PurOrdDetail` `m`
  LEFT JOIN `PurOrdRctDetail` `d`
    ON (
      (`d`.`Domain` = `m`.`Domain`)
      AND (`d`.`OrdNbr` = `m`.`PurOrd`)
      AND (`d`.`OrdLine` = `m`.`Line`)
      AND (`d`.`RctType` = 'temp')
    )
  )
  LEFT JOIN `PurOrdRctMaster` `p`
    ON (
      (`d`.`Domain` = `p`.`Domain`)
      AND (`d`.`Receiver` = `p`.`Receiver`)
    )
);