Difference between revisions of "BACKROOM"

Revision as of 08:27, 17 June 2016

Getting undefine items in backroom

SELECT

   a.SL_ITMC,
   a.recid,
    IF(b.ARTM_DPCD IS NULL,'XX',b.ARTM_DPCD) DPCD,
    IF((SELECT CO_NAME FROM mybrnueva.art_co_master WHERE CO_CODE = b.ARTM_DPCD LIMIT 1) IS NULL,'Undefined Items',
    (SELECT CO_NAME FROM mybrnueva.art_co_master WHERE CO_CODE = b.ARTM_DPCD LIMIT 1)) DPNAME,
    SUM(SL_QNTY) QTY,
    SUM(SL_GROSS) GROSS,
    SUM(SL_GROSS - SL_NET) DISC,
    SUM(SL_NET) NET
    FROM mybrnueva.sales_line a LEFT JOIN mybrnueva.art_master b ON a.SL_ITMC = b.ARTM_IT_CDE
    WHERE DATE(a.SL_DATE) >= DATE('2015-11-30') AND DATE(a.SL_DATE) <= DATE('2015-12-05')
    GROUP BY SL_ITMC

SELECT * FROM mybrnueva.`sales_line` WHERE recid = '2015'

SELECT * FROM mybrnueva.`tbl_products_shwrm` WHERE fld_alias = '017874'

SELECT * FROM mybrnueva.`art_master` WHERE artm_it_cde = 'F200304077'

UPDATE mybrnueva.`sales_line` SET sl_itmc = 'F200304077' WHERE recid = '2015'

==wala sa art_master=======

INSERT INTO `mybrnueva`.`art_master` (

 `ARTM_IT_CDE`,
 `ARTM_DESC`,
 `ARTM_SDU`,
 `ARTM_SKU`,
 `ARTM_COST`,
 `ARTM_PRICE`,
 `ARTM_VAT`,
 `ARTM_SRU`,
 `ARTM_ALIAS`,
 `ARTM_CLASS`,
 `ARTM_BARCDE`,
 `ARTM_UPDATE`,
 `ARTM_SUPP`,
 `ARTM_TERMS`,
 `ARTM_STAT`,
 `ARTM_STKCDE`,
 `ARTM_GROUP`,
 `ARTM_COCD`,
 `ARTM_DPCD`

)

 SELECT
   m.fld_itemcode,
   m.fld_itemdesc,
   m.fld_sellunit,
   m.fld_orderunit,
   m. fld_cost,
   m.fld_srp,
   '0.00000',
   ,
   m.fld_alias,
   m.fld_matclass,
   n.fld_barcode,
   '0000-00-00 00:00:00',
   ,
   ,
   ,
   ,
   ,
   '02',
   '02'
 FROM mybrnueva.`tbl_products_shwrm` m
 JOIN mybrnueva.`tbl_barcodes` n ON m.fld_itemcode = n.fld_itemcode_fk
 WHERE m.fld_itemcode = 'F200304083'

Show room discrepancy

SELECT * FROM smdsrslac.`trxlogs` WHERE trx_companyname = 'SLAC IN-HOUSE' AND DATE(trx_transactdate) = DATE('2015-09-07') AND trx_sitename = 'ShowRoom - Makati'

DELETE FROM smdsrslac.`trxlogs` WHERE trx_companyname = 'SLAC IN-HOUSE' AND DATE(trx_transactdate) = DATE('2015-09-07') AND trx_sitename = 'ShowRoom - Makati'

Undefined Items Background 89

CREATE TABLE mybackroom.`art_master_20150507` LIKE mybackroom.`art_master`; INSERT INTO mybackroom.art_master_20150507 SELECT * FROM mybackroom.`art_master`

INSERT IGNORE INTO `mybackroom`.`art_master`

           (`ARTM_IT_CDE`,
            `ARTM_DESC`,
            `ARTM_SDU`,
            `ARTM_SKU`,
            `ARTM_COST`,
            `ARTM_PRICE`,
            `ARTM_VAT`,
            `ARTM_SRU`,
            `ARTM_ALIAS`,
            `ARTM_CLASS`,
            `ARTM_BARCDE`,
            `ARTM_UPDATE`,
            `ARTM_SUPP`,
            `ARTM_TERMS`,
            `ARTM_STAT`,
            `ARTM_STKCDE`,
            `ARTM_GROUP`,
            `ARTM_COCD`,
            `ARTM_DPCD`) 
            
            SELECT `F009IT_CDE`,
            F009DESC,
            F009SDU,
            F009SKU,
            F009COST,
            F009PRICE,
            F009VAT,
            F009SRU,
            F009ALIAS,
            F009CLASS,
            F009BARCDE,
            F009UPDATE,
            F009SUPP,
            F009TERMS,
            F009STAT,
            F009STKCDE,
            F009GROUP,' ',' ' FROM mybackroom.POSD0009             
     
      

Update Items Backroom

SELECT * FROM mybrnueva.`cbsMSTR0909` WHERE COCD = 'CBSI'

UPDATE mybrnueva.`art_master` aa, mybrnueva.`cbsMSTR0909` bb SET aa.ARTM_SKU = bb.SKU

WHERE aa.ARTM_IT_CDE = bb.ITEM_CDE 
AND bb.COCD = 'CBSI'