Using X++ Find out List purchase price agreements with MOQ

I often have received the data requirement from the Soucing department.

such as below.

 

Hi Jimmy,

Could you please based on as below standard run out list with all supplier, unit price, MOQ, Last 12 months purchase QTY us? 

Thanks!

 ...................

Hi Jimmy

 

Thanks for your work. I have two questions as follows: 

1.       Can you please check if we have any other cards (CRD-) which are not included in 5 rages. If so, please list them in your table. 

2.       Have you got MOQ 5K, 10K and 20K prices? If so, please list in your table. 

Thanks

 ...........

 

 
  static 
    
  void 
   Jimmy_PurchPriceForMOQWithCRD(Args _args)
{
    priceDiscTable  _priceDiscTable;
    test            test;
     
  int 
               i;
    InventTable     IT;
    VendTable       VendTable;
    UTCDateTime     CreateDateTime  
  = 
   DateTimeUtil::addMonths(DateTimeUtil::newDateTime(today(), 
  0 
  ), 
  - 
  12 
  );

 
  void 
   getPrice(VendAccount       _VendAccount,
              ItemId            _ItemId)
{
    priceDiscTable priceDiscTable;
;
     
  while 
   select priceDiscTable
            order by priceDiscTable.QuantityAmount,priceDiscTable.Amount desc
             
  where 
   priceDiscTable.ItemCode        
  == 
   TableGroupAll::Table
             
  && 
      priceDiscTable.AccountCode     
  == 
   TableGroupAll::Table
             
  && 
      priceDiscTable.relation        
  == 
   PriceType::PricePurch
             
  && 
      priceDiscTable.Module          
  == 
   ModuleInventCustVend::Vend
             
  && 
      priceDiscTable.ItemRelation    
  == 
   _ItemId
             
  && 
      priceDiscTable.AccountRelation 
  == 
   _VendAccount
             
  /* 
  
            &&   (systemdateget() >= priceDiscTable.fromDate || !priceDiscTable.fromDate) &&
                 (systemdateget() <= priceDiscTable.toDate   || !priceDiscTable.toDate  )
            &&   (priceDiscTable.QuantityAmount == 1         ||  priceDiscTable.QuantityAmount == 2000
            ||    priceDiscTable.QuantityAmount == 5000      ||  priceDiscTable.QuantityAmount == 10000
            ||    priceDiscTable.QuantityAmount == 20000)
             
  */ 
  
         {
             
  switch 
  (priceDiscTable.QuantityAmount)
            {
                 
  case 
    
  1 
   :
                    test.Price01   
  = 
   priceDiscTable.Amount; 
  break 
  ;
                 
  case 
    
  2000 
   :
                    test.Price02  
  = 
   priceDiscTable.Amount; 
  break 
  ;
                 
  case 
    
  5000 
   :
                    test.Price03  
  = 
   priceDiscTable.Amount; 
  break 
  ;
                 
  case 
    
  10000 
   :
                    test.Price04  
  = 
   priceDiscTable.Amount; 
  break 
  ;
                 
  case 
    
  20000 
   :
                    test.Price05  
  = 
   priceDiscTable.Amount; 
  break 
  ;
                 
  case 
    
  30000 
   :
                    test.Price06  
  = 
   priceDiscTable.Amount; 
  break 
  ;
                 
  case 
    
  50000 
   :
                    test.Price07  
  = 
   priceDiscTable.Amount; 
  break 
  ;
                 
  case 
    
  80000 
   :
                    test.Price08  
  = 
   priceDiscTable.Amount; 
  break 
  ;
                 
  case 
    
  100000 
   :
                    test.Price09  
  = 
   priceDiscTable.Amount; 
  break 
  ;
                 
  case 
    
  150000 
   :
                    test.Price10  
  = 
   priceDiscTable.Amount; 
  break 
  ;
                 
  default 
   :
                     
  break 
  ;
            }
         }
}

PurchQty getPurchQty(VendAccount   _VendAccount,
                      ItemId        _ItemId)
{
    purchLine   PurchLine;
    ;
    select sum(PurchQty) from PurchLine
         
  where 
   PurchLine.VendAccount  
  == 
   _VendAccount
         
  && 
      PurchLine.ItemId       
  == 
   _ItemId
         
  && 
      PurchLine.createdDateTime  
  >= 
   CreateDateTime;
     
  return 
   PurchLine.PurchQty;
}
;
    delete_from test;
 
  // 
  请再帮忙增加编号为LABT和PET和PVC开头的的物料到附属的表格中。 
  
 
       
  while 
   select _priceDiscTable
        group by AccountRelation,ItemRelation,Currency  
  // 
  ,fromDate,toDate 
  
 
           
  where 
   _priceDiscTable.ItemCode        
  == 
   TableGroupAll::Table
         
  && 
      _priceDiscTable.AccountCode     
  == 
   TableGroupAll::Table
         
  && 
      _priceDiscTable.relation        
  == 
   PriceType::PricePurch
         
  && 
      _priceDiscTable.Module          
  == 
   ModuleInventCustVend::Vend
        
  // 
   &&    _priceDiscTable.AccountRelation== "30299" 
  
 
           
  && 
      _priceDiscTable.ItemRelation   like  
  " 
  CRD* 
  " 
  
         
  /* 
  
        &&   (systemdateget() >= _priceDiscTable.fromDate || !_priceDiscTable.fromDate) &&
             (systemdateget() <= _priceDiscTable.toDate   || !_priceDiscTable.toDate  )
        &&   (_priceDiscTable.QuantityAmount == 1         ||  _priceDiscTable.QuantityAmount == 2000
        ||    _priceDiscTable.QuantityAmount == 5000      ||  _priceDiscTable.QuantityAmount == 10000
        ||    _priceDiscTable.QuantityAmount == 20000)
         
  */ 
  

     {
        i 
  ++ 
  ;
        test.Price01     
  = 
    
  0 
  ;
        test.Price02     
  = 
    
  0 
  ;
        test.Price03     
  = 
    
  0 
  ;
        test.Price04     
  = 
    
  0 
  ;
        test.Price05     
  = 
    
  0 
  ;
        test.Price06     
  = 
    
  0 
  ;
        test.Price07     
  = 
    
  0 
  ;
        test.Price08     
  = 
    
  0 
  ;
        test.Price09     
  = 
    
  0 
  ;
        test.Price10     
  = 
    
  0 
  ;

        test.ItemId      
  = 
   _priceDiscTable.ItemRelation;
        test.Name        
  = 
   InventTable::find(test.ItemId).ItemName;
        test.VendAccount  
  = 
   _priceDiscTable.AccountRelation;
        VendTable  
  = 
   VendTable::find(test.VendAccount);
        test.VendName     
  = 
   VendTable.ChineseName  
  ? 
   VendTable.ChineseName : VendTable.Name;

        test.BomId        
  = 
   _priceDiscTable.Currency;
        test.CustName     
  = 
   Tec_PrimaryVendor::find(test.ItemId).PrimaryVendorId;
         
  if 
  (test.VendAccount  
  == 
   test.CustName)
            test.IsPic        
  = 
   Noyes::Yes;
         
  else 
  
            test.IsPic        
  = 
   Noyes::No;
        test.FromDate         
  = 
   _priceDiscTable.FromDate;
        test.ToDate           
  = 
   _priceDiscTable.ToDate;
        getPrice(test.VendAccount,test.ItemId);
        test.Qty              
  = 
   getPurchQty(test.VendAccount,test.ItemId);
        test.doinsert();
     }
     info(strfmt( 
  " 
  Total updated %1 records! 
  " 
  ,i));
}
 
  // 
  也请帮忙导出像附件一样的BOX-，CPP-，LAB-，BLS- 对应价格及其它 
  
/* 
  
select a.ITEMID,NAME, a.VENDACCOUNT,a.VENDNAME,a.BOMID,a.ISPIC,qty,
a.PRICE01,a.PRICE02,a.PRICE03,a.PRICE04,a.PRICE05,a.PRICE06,a.PRICE07,a.PRICE08,a.PRICE09,a.PRICE10
from TEST a
order by a.ITEMID,a.VENDACCOUNT,a.PRICE01,a.PRICE02,a.PRICE03,a.PRICE04,a.PRICE05,a.PRICE06,a.PRICE07,a.PRICE08,a.PRICE09,a.PRICE10
go
 
  */