SHARE
TWEET

Untitled

a guest Oct 12th, 2017 59 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. **********************************************************************************************
  2. * Program....: clsIpopr_CreateOrder.PRG
  3. * Author.....: Nasim Huq
  4. * Date.......: March, 2009
  5. * Notice.....: Copyright (c) 2005 CGS, Inc., All Rights Reserved.
  6. * Techreq....: 1033062
  7. * Abstract...: EDI 850 inbound Create open order
  8. * Changes....:
  9. ***********************************************************************************************
  10.  
  11. #include edi.h
  12. #include system.h
  13.  
  14. #Define LARGEST_DATE {^2999-01-01}
  15. #Define EDI_NOT_PROCESSORIGINAL_MSG "Control Flag set to Not Process Original EDI orders into Live Table."
  16. #Define EDI_NOT_PROCESSCONFIRM_MSG "Control Flag set to Not Process Confirm EDI orders into Live Table."
  17. #Define EDI_NOT_PROCESSDUPLICATE_MSG "Control Flag set to Not Process Duplicate EDI orders into Live Table."
  18. #Define NOT_YET_PROCESS "Not yet process."
  19. #Define NO_UOM_CONVERSION_REF "Require Style UOM."
  20. #Define UOM_NOT_DIVISIBLE "Total Units must be divisible by "
  21. #Define NO_UOM_CONVERSION_FACTOR "No conversion factor."
  22. #Define NO_PREPACK "Unable to determine Prepack code."
  23. #Define EDI_BULK_ORDER "BK"
  24. #Define EDI_BLANKET_ORDER "BL"
  25. #Define EDI_DISCOUNT_CODE "EDI"
  26.  
  27.  
  28. Define Class clsIpopr_CreateOrder   As clsIpopr_delegatebase
  29.  
  30.     *--- trans cursor
  31.     ceipoTH             = ""
  32.     ceipoTD             = ""
  33.     ceipoTH_nt          = ""
  34.     ceipoTD_nt          = ""
  35.     ceipoTC             = ""
  36.     ceipoTA             = ""
  37.     ceipoTS             = ""
  38.     ceipoTR             = ""    &&--- charges
  39.     ceiPOtsac           = ""
  40.    
  41.     *--- trans work tables
  42.     cZzeipoTH           = ""
  43.     cZzeipoTD           = ""
  44.     cZzeipoTH_nt        = ""
  45.     cZzeipoTD_nt        = ""
  46.     cZzeipoTC           = ""
  47.     cZzeipoTA           = ""
  48.     cZzeipoTS           = ""
  49.     cZzeipoTDExt        = ""
  50.     cZzeipoTR           = ""    &&-- Charges
  51.     cZzeiPOtsac         = ""
  52.    
  53.     *--- trans history work tables
  54.     cZzeipoHH           = ""
  55.     cZzeipoHD           = ""
  56.     cZzeipoHA           = ""
  57.     cZzeipoHC           = ""
  58.     cZzeipoHH_nt        = ""
  59.     cZzeipoHD_nt        = ""
  60.     cZzeipoHS           = ""
  61.    
  62.     *--- order work tables
  63.     cZzoordrh           = ""
  64.     cZzoordrd           = ""
  65.     cZzoordsp           = ""
  66.  
  67.     cZzoordad           = ""
  68.     cZzootadr           = ""
  69.     cZzoordcd           = ""
  70.    
  71.     cZzoordrh_nt        = ""
  72.     cZzoordrd_nt        = ""
  73.     cZzoordrdExt        = ""
  74.     cZzoordrd_chrg      = ""
  75.     cZzoordsac          = ""
  76.    
  77.     cZzoordrh_cncl      = ""
  78.     cZzoordrd_cncl      = ""
  79.     cZzoordrh_del       = ""
  80.     cZzoordrd_del       = ""
  81.     cZzoordsp_del       = ""
  82.  
  83.     cZzoordrh_Open      = ""
  84.     cZzoordrd_Open      = ""
  85.    
  86.  
  87.     ** TR 1050538 FH 11/10/10
  88.     ceiPOcr             = ""
  89.     cZzeipoCr           = ""       
  90.        
  91.     *--- bulk nibbler
  92.     cZzoordwh           = ""
  93.     cZzoordwd           = ""
  94.     cZzxniblr           = ""
  95.  
  96.     *--- TechRec 1054619 08-Jun-2011 MANI. ---
  97.     ceiPOtWHSE          = ""
  98.     czzedatawhse        = ""
  99.     cZzeiPOtWHSE        = ""
  100.     *=== TechRec 1054619 08-Jun-2011 MANI. ===
  101.  
  102.     DIMENSION aOrdTbls[1]
  103.     nOrdTbls            = 0
  104.  
  105.     *--- 1044208
  106.     oAfterUpdate = NULL
  107.     oBulk        = NULL
  108.     oBulkMatch   = NULL
  109.     *--- 1044208   
  110.     *--- TR 1075041 9-12-2013 VKK
  111.     oSB     = NULL
  112.     *=== TR 1075041 9-12-2013 VKK
  113.    
  114.     *------------------------------------------------------------------------
  115.    
  116.     PROCEDURE INIT
  117.         Lparameters toHostProcess
  118.        
  119.         DODEFAULT(toHostProcess)
  120.         this.oCopyUtil = NULL
  121.        
  122.         SET PROCEDURE TO copyutil ADDITIVE
  123.         this.ocopyutil = CREATEOBJECT("IPO_CopyUtil")
  124.    
  125.         *--- TR 1075041 9-12-2013 VKK  
  126.         this.oSB = NEWOBJECT("SQLStringBuilder","clsgnstr.fxp")
  127.         *=== TR 1075041 9-12-2013 VKK
  128.    
  129.     ENDPROC
  130.  
  131.  
  132.     PROCEDURE DESTROY
  133.    
  134.         this.oAfterUpdate = NULL
  135.         this.oBulk       = NULL
  136.         this.oBulkMatch  = NULL
  137.  
  138.         *--- TR 1075041 9-12-2013 VKK  
  139.         this.oSB = NULL
  140.         *=== TR 1075041 9-12-2013 VKK
  141.    
  142.         RETURN DODEFAULT()
  143.    
  144.     ENDPROC
  145.    
  146.     *--------------------------------------------------------------------------
  147.  
  148.     *-- [Main Process]
  149.     PROCEDURE CreateOrders
  150.    
  151.         LOCAL llRetVal, lnOldSelect
  152.         llRetVal        = .t.
  153.         lnOldSelect     = SELECT() 
  154.        
  155.         *#--- Send data to Server
  156.         WITH this
  157.             .LogMajorStage("Sales Order Creation.")
  158.             Local l_nThermoCnt
  159.             l_nThermoCnt = 0           
  160.  
  161.             CREATE CURSOR curTabDrop (tabname CHAR(100))
  162.            
  163.             *--- 1044208
  164.             .oAfterUpdate = NEWOBJECT("clsIpoPr_AfterUpdateBeforeCommit","clsIpoPr_AfterUpdateBeforeCommit.fxp",null,this.oHostPROCESS,this)
  165.             .oBulk        = NEWOBJECT("clsIpoPr_BulkNibbler","clsIpopr_BulkNibbler.fxp",null,this.oHostProcess,this)
  166.             .oBulkMatch   = NEWOBJECT("clsipopr_EdiBulkMatching","clsipopr_EdiBulkMatching.fxp","",null,this.oHostProcess)         
  167.             *=== 1044208           
  168.                
  169.             *=-=-=-=-=-=-=--=-=-=-=-=-===-
  170.             this.cFilterCriteria = " WHERE h.pkey IN (SELECT pkey FROM " + this.oHostProcess.cFilterTempTable + ")"
  171.             *=-=-=-=-=-=-=--=-=-=-=-=-===- 
  172.            
  173.             .AdvanceThermoTotalWithCaptionPlus("Creating single orders.", vcLogName)
  174.             This.InitThermoWithCaption(@l_nThermoCnt, 10, "Sending transaction data to server...")
  175.            
  176.             llRetVal        = llRetVal and .SendDataToTransWorkTables()
  177.             .LogEntry("Send transaction data to server work tables " + IIF(llRetVal,"completed.","failed."))
  178.            
  179.             *--- TR 1044017 07-JAN-2010 HNISAR
  180. *!*         This.AdvanceThermoPlus(@l_nThermoCnt,"Update 850 transaction server tables")
  181.             This.AdvanceThermoPlus(@l_nThermoCnt,"Update " + This.oHostProcess.cProcessNo +" transaction server tables")             
  182.             *=== TR 1044017 07-JAN-2010 HNISAR
  183.            
  184.             llRetVal        = llRetVal and .UpdateTransactionTables()
  185.             *--- TR 1044017 07-JAN-2010 HNISAR
  186. *!*         .LogEntry("Update 850 transaction server tables " + IIF(llRetVal,"completed.","failed."))
  187.             .LogEntry("Update " + This.oHostProcess.cProcessNo +" transaction server tables " + IIF(llRetVal,"completed.","failed."))          
  188.             *=== TR 1044017 07-JAN-2010 HNISAR
  189.  
  190.             This.AdvanceThermoPlus(@l_nThermoCnt,"Remove error records from transaction work tables")          
  191.            
  192.             llRetVal        = llRetVal and .DeleteErrorRecordsFromTransWorkTables()
  193.             .LogEntry("Remove error records from transaction work tables " + IIF(llRetVal,"completed.","failed."))
  194.             This.AdvanceThermoPlus(@l_nThermoCnt)          
  195.            
  196.             llRetVal        = llRetVal and .CreateOrderWorkTables()        
  197.            
  198.             llRetVal        = llRetVal and .PopulateOrderWorkTables()
  199.             .LogEntry("Populate order work tables " + IIF(llRetVal,"completed.","failed."))
  200.             This.AdvanceThermoPlus(@l_nThermoCnt,"Populate order work tables")         
  201.            
  202.             llRetVal        = llRetVal and .PrepareOrderForUpdate()        
  203.             .LogEntry("Prepare order data for update " + IIF(llRetVal,"completed.","failed."))
  204.             This.AdvanceThermoPlus(@l_nThermoCnt,"Prepare order data for update")          
  205.            
  206.             llRetVal        = llRetVal and .PopuldateTransHistoryWorkTables()
  207.             .LogEntry("Populate data in transaction history work tables " + IIF(llRetVal,"completed.","failed."))
  208.             This.AdvanceThermoPlus(@l_nThermoCnt,"Populate data in transaction history work tables")           
  209.            
  210.             *llRetVal       = llRetVal and .StampTimeAndUserID()
  211.            
  212.             llRetVal        = llRetVal and .UpdateAllTables()
  213.             .LogEntry("Sales Order Creation " + IIF(llRetVal,"completed.","failed."))
  214.             This.AdvanceThermoPlus(@l_nThermoCnt)          
  215.            
  216.             ** Populate Allocation tables based on Data warehouse
  217.             llRetVal = llRetVal AND InsertAllocationRuleFromEDIWarehouse(this.czzedatawhse)        
  218.            
  219.            
  220.             IF llRetVal AND USED("curTabDrop")
  221.                 SELECT curTabDrop
  222.                 SCAN
  223.                     v_sqlexecnoerror("DROP TABLE " + ALLTRIM(curTabDrop.tabname))
  224.                 ENDSCAN            
  225.             ENDIF
  226.             USE IN SELECT("curTabDrop")
  227.            
  228.             *--- 1044208       
  229.             .oAfterUpdate = NULL
  230.             .oBulk        = NULL
  231.             .oBulkMatch   = NULL
  232.             *--- 1044208       
  233.                                    
  234.            
  235.         *#--- Prepare Date before order Creation
  236.         *#--- Create order headers
  237.         *#--- Create Order Header Notes
  238.         *#--- create order Sp
  239.        
  240.  
  241.         *##-- credit card
  242.         *--- TR 1012711 17-OCT-2005 RG
  243.         * For credit card transaction, update Credit Card table and factor infor in Sales Order table.
  244.         *THIS.CreateCreditRecord('O','vzzoordrd',loRecordset.pkey,.T.)
  245.        
  246.  
  247.         *#-- Create Order Detail - Newline logic -
  248.         *--- Create Order Detail Notes
  249.        
  250.         *#-- FRM
  251.         *--- Create FRM records
  252.         *--- Copy_UDF_Data(This.nOldHeaderPKey, This.nNewHeaderPkey)
  253.  
  254.         *#-- Multi discount
  255.         *--- create multi discount - tcSACDetail - m.pkey     = v_nextPkey('zzoordsac')
  256.        
  257.         *#--- create transaction history header
  258.         *#--- create transaction history detail
  259.        
  260.         *#-- set credit card encription
  261.         *--- encription - loeipoth_ccard.cc_number = XP_ENCRYPT(loeipoth_ccard.CardNo)
  262.        
  263.         *#-- Charge    
  264.         *--- set fappv_amt With ChargeAmt, fexpn_date With ftran_date + facExpir_days
  265.         *#--- ResolveTax - exists
  266.        
  267.        
  268.         *#--- split order detail line base on detail start_date and end_date
  269.        
  270.         *--- create SAC records (ZZOORDSAC)
  271.         *--- Customer Price - This.oBPOSalesOrder.GetCustomerPrice(Evaluate(pceipoTH + ".customer"), lcDivision, ;
  272.        
  273.         *--- RollUpCanceledBuckets("tcUnavail", "tcCanceled")
  274.         *--- lnFKey = CreateCancelHdr(tcOrderHeader, lnCancQty, tcHistHeader)
  275.         *--- CreateCancelDtl("tcCanceled", tcOrderDetail, lnFKey, lcCancType, lcCancRsn)
  276.         *--- CreateCancelDtlHist("tcUnAvail", tcHistDetail, lnFKey, lcCancType, lcCancRsn)
  277.         *--- llUseBulkMatch  - This.InheritDataFromBulk(lnCurrOrderHeaderPkey,  Evaluate(pceipoTH + ".rr_pkey"), ;
  278.                         tcOrderHeader, tcOrderDetail,"tcBulkMatch",tcOrderh_notes,tcOrderD_notes,@llHeaderNotes)
  279.        
  280.         *#--- Comments
  281.         *--- Create order comment - ZZOORDCD
  282.         *--- Create order Address - 'OT' - ZZOORDAD
  283.         *--- Create order Address - 'BT' - ZZOOTADR
  284.         *--- Create order Address - 'SO' - ZZOOTADR
  285.        
  286.         *#--- BULK
  287.         *--- Create bulk nibbler Trx records
  288.         *---        If llRetVal And .GetNibblerTrxNumsAndPkeys(This.CountTotalRecs (tcOrderHeader), This.CountTotalRecs (tcOrderDetail),;
  289.         *---                @lnStrZZXNIBLR, @lnStrZZOORDWH, @lnStrZZOORDWD)
  290.         * recording work records for AutoBulkReduction when have some bulk for this customer
  291.         *---        This.oBPOSalesOrder.CreateNibblerQueuesForAddModeOnly
  292.         * recording work records for nibbler summary
  293.         *---        This.oBPOSalesOrder.CreateNibblerQueuesForAddModeOnly(;
  294.        
  295.         *#--- resolve Terms
  296.  
  297.         *--- 1009014 02/05 CB - Resolve the merchandise code on the sales order detail table:  
  298.        
  299.         *--- Resolve MultiCurrency  - exists       
  300.  
  301.         ENDWITH
  302.        
  303.         SELECT(lnOldSelect)
  304.         RETURN llRetVal    
  305.     ENDPROC
  306.    
  307.     *--------------------------------------------------------------------------
  308.  
  309.     PROCEDURE SetTransTables
  310.         LPARAMETERS pceipoTH, pceipoTD, pceipoTC, pceipoTA,pceipoTS, pceipoTR, pceiPOtsac, pceiPOcr, pceipotWHSE
  311.         *--- TechRec 1054619 08-Jun-2011 MANI. Added pceipotWHSE ===
  312.         * TR 1050538 FH 11/10/10 - added pceiPOcr to parameter         
  313.    
  314.         LOCAL llRetVal
  315.         llRetVal            = .t.
  316.         this.ceipoTH        = pceipoTH
  317.         this.ceipoTD        = pceipoTD
  318.         this.ceipoTC        = pceipoTC
  319.         this.ceipoTA        = pceipoTA
  320.         this.ceipoTS        = pceipoTS
  321.         this.ceipoTR        = pceipoTR      &&--- Charges
  322.         this.ceiPOtsac      = pceiPOtsac
  323.         this.ceiPOcr        = pceiPOcr    && * TR 1050538 FH       
  324.  
  325.         *--- TechRec 1054619 08-Jun-2011 MANI. ---
  326.         This.ceiPOtWHSE     = pceipotWHSE
  327.         *=== TechRec 1054619 08-Jun-2011 MANI. ===
  328.  
  329.         RETURN llRetVal
  330.     ENDPROC
  331.    
  332.     *--------------------------------------------------------------------------
  333.  
  334.     PROCEDURE SendDataToTransWorkTables
  335.        
  336.         LOCAL llRetVal, lcUnique, lcSql
  337.         llRetVal            = .t.
  338.         lcUnique            = SYS(2015)
  339.         WITH this
  340.             .cZzeipoTH      = "#zzeipoth" + lcUnique
  341.             llRetVal        = llRetVal and this.oCopyUtil.SendCursorToSqlTbl(this.ceipoTH, this.cZzeipoTH)
  342.            
  343.             lcSql           = "create index pkey on " + this.cZzeipoTH + " (pkey) "
  344.             llRetVal        = llRetVal and v_sqlexec(lcSql)
  345.            
  346.             *---- trans detail
  347.             LOCAL lcZzeipoTD           
  348.             lcZzeipoTD      = "#zzeipotdTmp" + lcUnique
  349.             .cZzeipoTD      = "#zzeipotd" + lcUnique
  350.             llRetVal        = llRetVal and this.oCopyUtil.SendCursorToSqlTbl(this.ceipoTD, lcZzeipoTD)
  351.             *--- add a order detail link pkey field - which will be poupulated when order detail is created from tran detail
  352.            
  353.             lcSql           = "select *, pkey as zzoordrd_pkey into " + this.cZzeipoTD + " from " + lcZzeipoTD
  354.             llRetVal        = llRetVal and v_sqlexec(lcSql)
  355.  
  356.             IF llRetVal
  357.                 .RegisterTable(this.cZzeipoTD)
  358.             ENDIF
  359.  
  360.  
  361.             lcSql           = "create index pkey on " + this.cZzeipoTD + " (pkey) "
  362.             llRetVal        = llRetVal and v_sqlexec(lcSql)
  363.            
  364.             lcSql           = "create index fkey on " + this.cZzeipoTD + " (fkey) "
  365.             llRetVal        = llRetVal and v_sqlexec(lcSql)
  366.            
  367.             lcSql           = "create index zzoordrd_pkey on " + this.cZzeipoTD + " (zzoordrd_pkey) "
  368.             llRetVal        = llRetVal and v_sqlexec(lcSql)
  369.                        
  370.             .cZzeipoTA      = "#zzeipota" + lcUnique
  371.             llRetVal        = llRetVal and this.oCopyUtil.SendCursorToSqlTbl(this.ceipoTA, this.cZzeipoTA)
  372.  
  373.             .cZzeipoTC      = "#zzeipotc" + lcUnique
  374.             llRetVal        = llRetVal and this.oCopyUtil.SendCursorToSqlTbl(this.ceipoTC, this.cZzeipoTC)
  375.  
  376.             *--- TechRec 1052693 20-Apr-2011 asharma ---       
  377.            
  378.             lcSql           = "create index " + This.cZzeipoTC + "_pkey on " + This.cZzeipoTC + " (pkey) "
  379.             llRetVal        = llRetVal and v_sqlexec(lcSql)
  380.            
  381.             *=== TechRec 1052693 20-Apr-2011 asharma ===           
  382.  
  383.             .cZzeipoTH_nt   = "#zzeipoth_nt" + lcUnique
  384.             lcSql           = "Select n.* into " + this.cZzeipoTH_nt + " from sysnotes n inner join " + this.cZzeipoTH + " h on h.pkey = n.fkey " + ;
  385.                                 " and n.table_name = 'ZZEIPOTH'"
  386.             llRetVal        = llRetVal and v_sqlexec(lcSql)
  387.  
  388.             *--- TechRec 1052693 20-Apr-2011 asharma ---
  389.            
  390.             lcSql           = "create index " + this.cZzeipoTH_nt + "_pkey on " + this.cZzeipoTH_nt + " (pkey) "
  391.             llRetVal        = llRetVal and v_sqlexec(lcSql)
  392.            
  393.             *=== TechRec 1052693 20-Apr-2011 asharma ===
  394.  
  395.             IF llRetVal
  396.                 .RegisterTable(this.cZzeipoTH_nt )
  397.             ENDIF
  398.  
  399.  
  400.             *--- trans detail notes
  401.             .cZzeipoTD_nt   = "#zzeipotd_nt" + lcUnique
  402.             lcSql           = "Select n.*, n.fkey as zzoordrd_pkey into " + this.cZzeipotd_nt + " from sysnotes n inner join " + this.cZzeipotd + " d on d.pkey = n.fkey " + ;
  403.                                 " and n.table_name = 'ZZEIPOTD'"
  404.                                
  405.             *--- add a order detail link pkey field - which will be poupulated when order detail is created from tran detail
  406.             llRetVal        = llRetVal and v_sqlexec(lcSql)
  407.  
  408.             *--- TechRec 1052693 20-Apr-2011 asharma ---
  409.            
  410.             lcSql           = "create index " + this.cZzeipotd_nt + "_pkey on " + this.cZzeipotd_nt + " (pkey) "
  411.             llRetVal        = llRetVal and v_sqlexec(lcSql)
  412.            
  413.             *=== TechRec 1052693 20-Apr-2011 asharma ===
  414.  
  415.             IF llRetVal
  416.                 .RegisterTable(this.cZzeipotd_nt )
  417.             ENDIF
  418.  
  419.             *--- trans extender table
  420.             .cZzeipoTDExt   = "#zzzUdfDt_ipo" + lcUnique
  421.             lcSql           = "select zzzUdfDt.*, HdrFKey as OldHdrFKey, DtlFKey as OldDtlFKey, g.UDF_Field " + ;
  422.                                 " ,u.Grp_T_Key, Grp_O_Key " + ;
  423.                                 " into " + .cZzeipoTDExt + ;
  424.                                 " from zzzUdfDt inner join " + .cZzeipoTH + " h on zzzUdfDt.HdrFKey = h.PKey " + ;
  425.                                 " inner join zzzUdfCR c on c.PKey = zzzUdfDt.GrpFKey and c.Module = 'ZZEIPOTH' " + ;
  426.                                 " inner join zzzUdfGd g on g.PKey = zzzUdfDt.UdfFKey " + ;
  427.                                 " inner join ( select u.UDF_Field, ci.PKey as Grp_I_Key, ct.PKey as Grp_T_Key, co.PKey as Grp_O_Key " + ;
  428.                                 "               from zzzUdfGD u join zzzUdfGH g on g.PKey = u.FKey " + ;
  429.                                 "       join zzzUdfCr ci on ci.FKey = g.PKey and ci.Module = 'ZZEIPOIH' and ci.Link_Table = 'ZZEIPOID' " + ;
  430.                                 "       join zzzUdfCr ct on ct.FKey = g.PKey and ct.Module = 'ZZEIPOTH' and ct.Link_Table = 'ZZEIPOTD' " + ;
  431.                                 "       join zzzUdfCr co on co.FKey = g.PKey and co.Module = 'ZZOORDRH' and co.Link_Table = 'ZZOORDRD') u " + ;
  432.                                 " on u.Udf_field = g.Udf_field "
  433.  
  434.             llRetVal        = llRetVal and v_sqlexec(lcSql)
  435.            
  436.             IF llRetVal
  437.                 .RegisterTable(this.cZzeipoTDExt )
  438.             ENDIF          
  439.            
  440.             *--- charges
  441.             .cZzeipoTR      = "#ZzeipoTR" + lcUnique
  442.             llRetVal        = llRetVal and this.oCopyUtil.SendCursorToSqlTbl(this.ceipoTR, this.cZzeipoTR)
  443.            
  444.             *--- add field hasChrgs to trans detail (zzeipotd) - it is need in rollup - ipotd => oordrd
  445.             lcSql           = "alter table " + this.cZzeipotd + " add hasCharges char(1) DEFAULT(' ') not null "
  446.             llRetVal        = llRetVal and v_sqlexec(lcSql)
  447.             lcSql           = "Update d Set hasCharges = 'Y' FROM " + this.cZzeipotd + " d INNER JOIN " + this.cZzeipotr + ;
  448.                                 " r on r.fkey = d.pkey "
  449.             llRetVal        = llRetVal and v_sqlexec(lcSql)
  450.            
  451.             *--- multi discount
  452.             .cZzeiPOtsac    = "#ZzeiPOtsac" + SYS(2015)
  453.             lcSql           = llRetval and this.oCopyUtil.SendCursorToSqlTbl(.ceiPOtsac,.cZzeiPOtsac)
  454.            
  455.             *--- TR 1044485
  456.             * SLN
  457.             .cZzeipoTS      = "#ZzeiPOts" + SYS(2015)
  458.             lcSql           = llRetval and this.oCopyUtil.SendCursorToSqlTbl(.ceipoTS,.cZzeipoTS)                      
  459.             *=== TR 1044485                
  460.            
  461.             *--- TechRec 1052693 20-Apr-2011 asharma ---
  462.            
  463.             lcSql           = "create index " + .cZzeipoTS + "_pkey on " + .cZzeipoTS + " (pkey) "
  464.             llRetVal        = llRetVal and v_sqlexec(lcSql)
  465.            
  466.             *=== TechRec 1052693 20-Apr-2011 asharma ===
  467.  
  468.             * TR 1050538 FH 11/10/10
  469.             .cZzeipoCr      = "#zzeipocr" + lcUnique
  470.             llRetVal        = llRetVal and this.oCopyUtil.SendCursorToSqlTbl(this.ceiPOcr, this.cZzeipoCr)                         
  471.  
  472.             *--- TechRec 1054619 09-Jun-2011 MANI. ---
  473.             .cZzeiPOtWHSE       = "#ZzeiPOtWHSE" + lcUnique
  474.             llRetVal        = llRetVal and this.oCopyUtil.SendCursorToSqlTbl(this.ceiPOtWHSE, this.cZzeiPOtWHSE)
  475.             *--- TechRec 1056973 08-Dec-2011 jisingh Commented ---
  476.             *llRetVal       = llRetVal and this.PrepareDataForWhse()
  477.             *=== TechRec 1056973 08-Dec-2011 jisingh ===
  478.             *=== TechRec 1054619 09-Jun-2011 MANI. ===
  479.                        
  480.         ENDWITH
  481.        
  482.         RETURN llRetVal
  483.     ENDPROC
  484.    
  485.     *--------------------------------------------------------------------------
  486.    
  487.     PROCEDURE UpdateTransactionTables
  488.        
  489.         LOCAL llRetVal, lcSql_update, lcSql_dtl_update, lcSql_delete, lcSql_Dtl_delete, lcSql_insert, lcSql_Dtl_insert, llNotExists
  490.         LOCAL lcSql_update_zzeipotsac, lcSql_insert_zzeipotsac, lcSql_delete_zzeipotsac
  491.         LOCAL lcSQL_update_zzeipots, lcSQL_insert_zzeipots, lcSQL_delete_zzeipots,lcSqlInsert_zzeipoWhse, lcSqlDelete_zzeipoWhse
  492.        
  493.         *--- TR 1059065 01-Feb-12 SK ===
  494.         LOCAL lcSqlUpdate_zzeipotwhse
  495.        
  496.         *--- TechRec 1056973 18-Nov-2011 jisingh Added lcSqlDelete_zzeipoWhse ===
  497.         &&--- TechRec 1054619 29-Sep-2011 MANI. Added variable lcSqlInsert_zzeipoWhse ===      
  498.         llRetVal    = .t.
  499.         llNotExists = .t.
  500.         STORE " " TO lcSql_update, lcSql_dtl_update, lcSql_delete, lcSql_Dtl_delete, lcSql_insert, lcSql_Dtl_insert
  501.         STORE " " TO lcSql_update_zzeipotr, lcSql_insert_zzeipotr, lcSql_delete_zzeipotr
  502.         STORE " " TO lcSql_update_zzeipotsac, lcSql_insert_zzeipotsac, lcSql_delete_zzeipotsac
  503.         STORE " " TO lcSQL_update_zzeipots, lcSQL_insert_zzeipots, lcSQL_delete_zzeipots, lcSqlInsert_zzeipoWhse, lcSqlDelete_zzeipoWhse
  504.        
  505.         *--- TR 1059065 01-Feb-12 SK ===
  506.         STORE " " TO lcSqlUpdate_zzeipotwhse
  507.        
  508.         *--- TechRec 1056973 18-Nov-2011 jisingh Added lcSqlDelete_zzeipoWhse ===
  509.         &&--- TechRec 1054619 29-Sep-2011 MANI. Added variable lcSqlInsert_zzeipoWhse ===      
  510.         *--- transaction temp tables
  511.                
  512.         lcZzeipoth  = this.cZzeipoTH
  513.         lcZzeipotd  = this.cZzeipoTD
  514.        
  515.         llRetVal    = llRetVal and this.oCopyUtil.GetUpdateSql(@lcSql_update,this.cZzeipoth,"zzeipoth")
  516.         llRetVal    = llRetVal and this.oCopyUtil.GetUpdateSql(@lcSql_dtl_update,this.cZzeipotd,"zzeipotd")
  517.        
  518.         llRetVal    = llRetVal and this.oCopyUtil.GetInsertSql(@lcSql_Insert,this.cZzeipoth,"zzeipoth")
  519.         llRetVal    = llRetVal and this.oCopyUtil.GetInsertSql(@lcSql_dtl_Insert,this.cZzeipotd,"zzeipotd")
  520.  
  521.         llRetVal    = llRetVal and this.oCopyUtil.GetDeleteSql(@lcSql_Delete,this.cZzeipoth,"zzeipoth",.t.,this.cFilterCriteria,"d","h")
  522.         llRetVal    = llRetVal and this.GetDeleteSql_TransDtl(@lcSql_dtl_Delete)       
  523.          
  524.         *--- charges
  525.         llRetVal    = llRetVal and this.oCopyUtil.GetUpdateSql(@lcSql_update_zzeipotr,this.cZzeipotR,"zzeipotr","'pkey'")
  526.         llRetVal    = llRetVal and this.oCopyUtil.GetInsertSql(@lcSql_insert_zzeipotr,this.cZzeipotR,"zzeipotr","'pkey'")
  527. *       llRetVal    = llRetVal and this.oCopyUtil.GetDeleteSql(@lcSql_delete_zzeipotr,this.cZzeipotR,"zzeipotr",.t.)       
  528.         llRetVal    = llRetVal and this.GetDeleteSQL_TransCharges(@lcSql_delete_zzeipotr)
  529.        
  530.         *--- multi discount
  531.         llRetVal    = llRetVal and this.oCopyUtil.GetUpdateSql(@lcSql_update_zzeipotsac,this.cZzeipotsac,"zzeipotsac")
  532.        
  533.         *- TR 1059993 FH/YIK 03/09/12      
  534.         *--- TR 1045120 FEB-18-2010 BR
  535. *!*         llRetVal    = llRetVal and this.oCopyUtil.GetInsertSql(@lcSql_insert_zzeipotsac,this.cZzeipotsac,"zzeipotsac",,,,.t.)
  536. *!*         llRetVal    = llRetVal and this.oCopyUtil.GetDeleteSql(@lcSql_delete_zzeipotsac,this.cZzeipotsac,"zzeipotsac",,this.cFilterCriteria,,,"zzeipoth","hfkey","h")
  537.         *=== TR 1045120 FEB-18-2010 BR
  538.        
  539.         llRetVal    = llRetVal and this.oCopyUtil.GetInsertSql(@lcSql_insert_zzeipotsac,this.cZzeipotsac,"zzeipotsac")     
  540.         llRetVal    = llRetVal and this.oCopyUtil.GetDeleteSql(@lcSql_delete_zzeipotsac,this.cZzeipotsac,"zzeipotsac",.T.,this.cFilterCriteria,,,"zzeipoth","hfkey","h")
  541.         *- TR 1059993 FH/YIK 03/09/12
  542.        
  543.         *- update - address, comment, notes
  544.         LOCAL lcSqlUpdate_zzeipota, lcSqlUpdate_zzeipotc,lcSqlUpdate_zzeipoth_nt,lcSqlUpdate_zzeipotd_nt
  545.         llRetVal    = llRetVal and this.oCopyUtil.GetUpdateSql(@lcSqlUpdate_zzeipota,this.cZzeipota,"zzeipota")
  546.         llRetVal    = llRetVal and this.oCopyUtil.GetUpdateSql(@lcSqlUpdate_zzeipotc,this.cZzeipotc,"zzeipotc")
  547.         llRetVal    = llRetVal and this.oCopyUtil.GetUpdateSql(@lcSqlUpdate_zzeipoth_nt,this.cZzeipoth_nt,"sysnotes")
  548.         llRetVal    = llRetVal and this.oCopyUtil.GetUpdateSql(@lcSqlUpdate_zzeipotd_nt,this.cZzeipotd_nt,"sysnotes")
  549. *       llRetVal    = llRetVal and this.oCopyUtil.GetUpdateSql(@lcSql_update_zzeipotr,this.cZzeipotr,"zzeipotr")
  550.         llRetVal    = llRetVal and this.oCopyUtil.GetUpdateSql(@lcSQL_update_zzeipots,this.cZzeipots,"zzeipots")
  551.        
  552.         *--- TR 1059065 01-Feb-12 SK Creating update string for zzeipotwhse (warehouse) table.
  553.         llRetVal    = llRetVal and this.oCopyUtil.GetUpdateSql(@lcSqlUpdate_zzeipotwhse,this.cZzeipotWhse,"zzeipotwhse")
  554.         *=== TR 1059065 01-Feb-12 SK
  555.        
  556.         *- insert - address, comment, notes
  557.         LOCAL lcSqlInsert_zzeipota, lcSqlInsert_zzeipotc,lcSqlInsert_zzeipoth_nt,lcSqlInsert_zzeipotd_nt
  558.         llRetVal    = llRetVal and this.oCopyUtil.GetInsertSQL(@lcSqlInsert_zzeipota,this.cZzeipota,"zzeipota")
  559.         llRetVal    = llRetVal and this.oCopyUtil.GetInsertSQL(@lcSqlInsert_zzeipotc,this.cZzeipotc,"zzeipotc")
  560.         llRetVal    = llRetVal and this.oCopyUtil.GetInsertSQL(@lcSqlInsert_zzeipoth_nt,this.cZzeipoth_nt,"sysnotes")
  561.         llRetVal    = llRetVal and this.oCopyUtil.GetInsertSQL(@lcSqlInsert_zzeipotd_nt,this.cZzeipotd_nt,"sysnotes")
  562. *       llRetVal    = llRetVal and this.oCopyUtil.GetInsertSQL(@lcSql_insert_zzeipotr,this.cZzeipotr,"zzeipotr")
  563.         llRetVal    = llRetVal and this.oCopyUtil.GetInsertSQL(@lcSQL_insert_zzeipots,this.cZzeipotS,"zzeipots")                       
  564.    
  565.         *--- TechRec 1054619 09-Jun-2011 MANI. ---
  566.         llRetVal    = llRetVal and this.oCopyUtil.GetInsertSQL(@lcSqlInsert_zzeipoWhse,this.cZzeipotWhse,"zzeipotwhse")
  567.         *=== TechRec 1054619 09-Jun-2011 MANI. ===
  568.  
  569.         LOCAL lcSqlDelete_zzeipota, lcSqlDelete_zzeipotc,lcSqlDelete_zzeipoth_nt,lcSqlDelete_zzeipotd_nt   
  570.         llRetVal    = llRetVal and this.GetDeleteSQL_TransAddr(@lcSqlDelete_zzeipota)
  571.         llRetVal    = llRetVal and this.GetDeleteSQL_TransCmt(@lcSqlDelete_zzeipotc)               
  572.         llRetVal    = llRetVal and this.GetDeleteSQL_TransHdrNotes(@lcSqlDelete_zzeipoth_nt)       
  573.         llRetVal    = llRetVal and this.GetDeleteSQL_TransDtlNotes(@lcSqlDelete_zzeipotd_nt)       
  574.         llRetVal    = llRetVal and this.GetDeleteSQL_TransSLN(@lcSQL_delete_zzeipots)      
  575.         *--- TechRec 1056973 18-Nov-2011 jisingh ---
  576.         llRetVal    = llRetVal and this.GetDeleteSQL_TransEDIS(@lcSqlDelete_zzeipoWhse)
  577.         *=== TechRec 1056973 18-Nov-2011 jisingh ===
  578.                
  579.         Local llBeganTransaction,lnTranErrCount,lcTranFailedMsg
  580.         llBeganTransaction  = This.BeginTransaction()
  581.         lnTranErrCount      = 0
  582.         lcTranFailedMsg     = ""
  583.        
  584.  
  585.         If llBeganTransaction And llRetVal
  586.             &&*--- TR 1044017 07-JAN-2010 HNISAR && REplace 850 WITH  " + This.oHostProcess.cProcessNo + " in All the statements below
  587.             this.SqlExecInTran(lcSql_update, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Update  " + This.oHostProcess.cProcessNo + "  transaction header failed.")
  588.             this.SqlExecInTran(lcSql_dtl_update, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Update  " + This.oHostProcess.cProcessNo + "  transaction detail failed.")
  589.            
  590.             this.SqlExecInTran(lcSqlUpdate_zzeipota, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Update  " + This.oHostProcess.cProcessNo + "  transaction address failed.")
  591.             this.SqlExecInTran(lcSqlUpdate_zzeipotc, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Update  " + This.oHostProcess.cProcessNo + "  transaction comment failed.")
  592.             this.SqlExecInTran(lcSqlUpdate_zzeipoth_nt, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Update  " + This.oHostProcess.cProcessNo + "  transaction header notes failed.")
  593.             this.SqlExecInTran(lcSqlUpdate_zzeipotd_nt, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Update  " + This.oHostProcess.cProcessNo + "  transaction detail notes failed.")
  594.             this.SqlExecInTran(lcSql_update_zzeipotr, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Update  " + This.oHostProcess.cProcessNo + "  transaction detail charges failed.")
  595.             this.SqlExecInTran(lcSql_update_zzeipotsac, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Update  " + This.oHostProcess.cProcessNo + "  transaction multi discount failed.")
  596.             this.SqlExecInTran(lcSQL_update_zzeipots, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Update  " + This.oHostProcess.cProcessNo + "  transaction sln failed.")                                           
  597.            
  598.             *--- TR 1059065 01-Feb-12 SK Executing update string for warehouse table
  599.             this.SqlExecInTran(lcSqlUpdate_zzeipotwhse, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Update  " + This.oHostProcess.cProcessNo + "  transaction address failed.")
  600.             *=== TR 1059065 01-Feb-12 SK
  601.            
  602.             this.SqlExecInTran(lcSql_delete, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Delete  " + This.oHostProcess.cProcessNo + "  transaction header failed.")
  603.             this.SqlExecInTran(lcSql_Dtl_delete, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Delete  " + This.oHostProcess.cProcessNo + "  transaction detail failed.")
  604.  
  605.             this.SqlExecInTran(lcSqlDelete_zzeipota, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Delete  " + This.oHostProcess.cProcessNo + "  transaction address failed.")
  606.             this.SqlExecInTran(lcSqlDelete_zzeipotc, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Delete  " + This.oHostProcess.cProcessNo + "  transaction comment failed.")
  607.             this.SqlExecInTran(lcSqlDelete_zzeipoth_nt, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Delete  " + This.oHostProcess.cProcessNo + "  transaction header notes failed.")
  608.             this.SqlExecInTran(lcSqlDelete_zzeipotd_nt, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Delete  " + This.oHostProcess.cProcessNo + "  transaction detail notes failed.")
  609.             this.SqlExecInTran(lcSql_delete_zzeipotr, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Delete  " + This.oHostProcess.cProcessNo + "  transaction detail charges failed.")
  610.  
  611.             *--- TechRec 1056973 18-Nov-2011 jisingh ---
  612.             this.SqlExecInTran(lcSqlDelete_zzeipoWhse, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Delete  " + This.oHostProcess.cProcessNo + "  transaction edis failed.")
  613.             *=== TechRec 1056973 18-Nov-2011 jisingh ===
  614.            
  615.             this.SqlExecInTran(lcSQL_delete_zzeipots, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Delete  " + This.oHostProcess.cProcessNo + "  transaction sln failed.")           
  616.            
  617.             *--- multi discount
  618.             this.SqlExecInTran(lcSql_delete_zzeipotsac, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Delete  " + This.oHostProcess.cProcessNo + "  transaction multi discount failed.")
  619.  
  620.             this.SqlExecInTran(lcSql_insert, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Insert  " + This.oHostProcess.cProcessNo + "  transaction header failed.")
  621.             this.SqlExecInTran(lcSql_Dtl_insert, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Insert  " + This.oHostProcess.cProcessNo + "  transaction detail failed.")
  622.  
  623.             this.SqlExecInTran(lcSqlInsert_zzeipota, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Insert  " + This.oHostProcess.cProcessNo + "  transaction address failed.")
  624.             this.SqlExecInTran(lcSqlInsert_zzeipotc, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Insert  " + This.oHostProcess.cProcessNo + "  transaction comment failed.")
  625.             this.SqlExecInTran(lcSqlInsert_zzeipoth_nt, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Insert  " + This.oHostProcess.cProcessNo + "  transaction header notes failed.")
  626.             this.SqlExecInTran(lcSqlInsert_zzeipotd_nt, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Insert  " + This.oHostProcess.cProcessNo + "  transaction detail notes failed.")
  627.             this.SqlExecInTran(lcSql_insert_zzeipotr, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Insert  " + This.oHostProcess.cProcessNo + "  transaction detail charges failed.")
  628.             this.SqlExecInTran(lcSql_insert_zzeipotsac, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Insert  " + This.oHostProcess.cProcessNo + "  transaction multi discount failed.")
  629.             this.SqlExecInTran(lcSql_insert_zzeipots, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Insert  " + This.oHostProcess.cProcessNo + "  transaction sln failed.")           
  630.            
  631.             *--- TR 1059065 01-Feb-12 SK Executing Insert string for warehouse table
  632.             this.SqlExecInTran(lcSqlInsert_zzeipoWhse, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Insert  " + This.oHostProcess.cProcessNo + "  transaction warehouse failed.")
  633.             *=== TR 1059065 01-Feb-12 SK
  634.                                    
  635.             If llRetVal
  636.                 This.EndTransaction()
  637.                 This.LogEntry(" " + This.oHostProcess.cProcessNo + "  Transaction successful.")
  638.                 *--- increase toProcess vaulue for new transaction records.
  639.             Else
  640.                 This.RollbackTransaction()
  641.                 This.LogEntry("---------------------------------------------")
  642.                 This.LogEntry(lcTranFailedMsg)
  643.                 This.LogEntry(" " + This.oHostProcess.cProcessNo + "  Transaction rolled back.")
  644.                 This.LogEntry("=============================================")
  645.             Endif
  646.         Else
  647.             If llRetVal && and not llBeganTransaction
  648.                 *--- LogEntry : unable to open Transaction
  649.                 This.LogEntry("Fatal error : Fail to open transaction.")
  650.             Endif
  651.         Endif
  652.  
  653.         RETURN llRetVal
  654.    
  655.     ENDPROC
  656.  
  657.     *--------------------------------------------------------------------------
  658.    
  659.     PROCEDURE GetDeleteSQL_TransAddr
  660.         LPARAMETERS pcSql
  661.        
  662.         LOCAL llRetVal, lcSQL, lcFilter
  663.        
  664.        
  665.         llRetVal    = .t.
  666.         IF EMPTY(this.cFilterCriteria)
  667.             lcFilter = "Where "
  668.         ELSE
  669.             lcFilter = IIF(AT("WHERE", UPPER(this.cFilterCriteria)) > 0, "","Where") + this.cFilterCriteria + " and "
  670.         ENDIF
  671.         lcSQL       = "delete d from zzeipota d inner join zzeipoth h on h.pkey = d.fkey " + lcFilter + ;
  672.                         " not exists ( select td.pkey from " + this.cZzeipota + " td where td.pkey = d.pkey )"
  673.         pcSql       = lcSql
  674.         RETURN llRetVal
  675.    
  676.     ENDPROC
  677.    
  678.     *-------------------------------------------------------------------------
  679.    
  680.     PROCEDURE GetDeleteSQL_TransSLN
  681.         LPARAMETERS pcSQL
  682.    
  683.    
  684.         LOCAL llRetVal, lcSQL, lcFilter
  685.        
  686.        
  687.         llRetVal    = .t.
  688.         IF EMPTY(this.cFilterCriteria)
  689.             lcFilter = "Where "
  690.         ELSE
  691.             lcFilter = IIF(AT("WHERE", UPPER(this.cFilterCriteria)) > 0, "","Where") + this.cFilterCriteria + " and "
  692.         ENDIF
  693.         lcSQL       = "delete d from zzeipots s inner join zzeipotd d on d.pkey = s.fkey join zzeipoth h on d.fkey = h.pkey " + lcFilter + ;
  694.                         " not exists ( select * from " + this.cZzeipots + " td where td.pkey = s.pkey )"
  695.         pcSql       = lcSql
  696.         RETURN llRetVal
  697.    
  698.     ENDPROC        
  699.  
  700.     *--- TechRec 1056973 18-Nov-2011 jisingh ---
  701.     *-------------------------------------------------------------------------
  702.    
  703.     PROCEDURE GetDeleteSQL_TransEDIS
  704.         LPARAMETERS pcSQL
  705.    
  706.    
  707.         LOCAL llRetVal, lcSQL, lcFilter
  708.        
  709.        
  710.         llRetVal    = .t.
  711.         IF EMPTY(this.cFilterCriteria)
  712.             lcFilter = "Where "
  713.         ELSE
  714.             lcFilter = IIF(AT("WHERE", UPPER(this.cFilterCriteria)) > 0, "","Where") + this.cFilterCriteria + " and "
  715.         ENDIF
  716.        
  717.         *--- TR 1059065 01-Feb-12 SK deleting only EDIS records by filtering sfkey > 0
  718. *!*         lcSQL       = "delete w from zzeipotwhse w join zzeipoth h on w.hfkey = h.pkey " + lcFilter + ;
  719. *!*                         " not exists ( select * from " + this.cZzeipots + " td where td.pkey = w.sfkey ) " + ;
  720. *!*                         " and h.errs_flg_h <> 'Y' and (h.warn_flg_h <> 'Y' " + ;
  721. *!*                         " or (h.warn_flg_h = 'Y' and h.ignore_warn = 'Y'))"
  722.  
  723.         lcSQL       = "delete w from zzeipotwhse w join zzeipoth h on w.hfkey = h.pkey " + lcFilter + ;
  724.                         " w.sfkey > 0 and not exists ( select * from " + this.cZzeipots + " td where td.pkey = w.sfkey ) " + ;
  725.                         " and h.errs_flg_h <> 'Y' and (h.warn_flg_h <> 'Y' " + ;
  726.                         " or (h.warn_flg_h = 'Y' and h.ignore_warn = 'Y'))"
  727.         *--- TR 1059065 01-Feb-12 SK
  728.                        
  729.         pcSql       = lcSql
  730.         RETURN llRetVal
  731.    
  732.     ENDPROC
  733.     *=== TechRec 1056973 18-Nov-2011 jisingh ===
  734.        
  735.     *--------------------------------------------------------------------------
  736.  
  737.     PROCEDURE GetDeleteSQL_TransCmt
  738.         LPARAMETERS pcSql
  739.        
  740.         LOCAL llRetVal, lcSQL, lcFilter
  741.        
  742.        
  743.         llRetVal    = .t.
  744.         IF EMPTY(this.cFilterCriteria)
  745.             lcFilter = "Where "
  746.         ELSE
  747.             lcFilter = IIF(AT("WHERE", UPPER(this.cFilterCriteria)) > 0, "","Where") + this.cFilterCriteria + " and "
  748.         ENDIF
  749.         lcSQL       = "delete d from zzeipotc d inner join zzeipoth h on h.pkey = d.fkey " + lcFilter + ;
  750.                         " not exists ( select td.pkey from " + this.cZzeipotc + " td where td.pkey = d.pkey )"
  751.         pcSql       = lcSql
  752.         RETURN llRetVal
  753.    
  754.     ENDPROC
  755.    
  756.     *--------------------------------------------------------------------------
  757.  
  758. *--- TR 1052729 / 1044208 10-Mar-11 SK Procedure was missedout when retrofits the TR 1044208
  759.     PROCEDURE GetDeleteSQL_TransCharges
  760.         LPARAMETERS pcSQL
  761.        
  762.         LOCAL llRetVal, lcSQL, lcFilter
  763.        
  764.         llRetVal    = .t.
  765.         IF EMPTY(this.cFilterCriteria)
  766.             lcFilter = "Where "
  767.         ELSE
  768.             lcFilter = IIF(AT("WHERE", UPPER(this.cFilterCriteria)) > 0, "","Where") + this.cFilterCriteria + " and "
  769.         ENDIF
  770.         lcSQL       = "delete s from zzeipotr s inner join zzeipotd d on d.pkey = s.fkey join zzeipoth h on d.fkey = h.pkey " + lcFilter + ;
  771.                         " not exists ( select * from " + this.cZzeipotr + " td where td.pkey = s.pkey )"
  772.         pcSql       = lcSql
  773.         RETURN llRetVal
  774.        
  775.     ENDPROC
  776.    
  777.     *--------------------------------------------------------------------------
  778. *=== TR 1052729 / 1044208 10-Mar-11 SK
  779.  
  780.     PROCEDURE GetDeleteSQL_TransHdrNotes
  781.         LPARAMETERS pcSql, plExists
  782.        
  783.         LOCAL llRetVal, lcSQL, lcFilter
  784.        
  785.        
  786.         llRetVal    = .t.
  787.         IF EMPTY(this.cFilterCriteria)
  788.             lcFilter = "Where "
  789.         ELSE
  790.             lcFilter = IIF(AT("WHERE", UPPER(this.cFilterCriteria)) > 0, "","Where") + this.cFilterCriteria + " and "
  791.         ENDIF
  792.  
  793.         IF plExists
  794.             lcSQL   = "delete d from sysnotes d inner join " + this.cZzeipoTh_nt + " h on h.pkey = d.pkey and d.table_name = 'ZZEIPOTH' "
  795.         ELSE
  796.             *--- need to join with original transaction header to apply filter
  797.             lcSQL   = "delete d from sysnotes d inner join zzeipoth h on h.pkey = d.fkey and d.table_name = 'ZZEIPOTH' " +;
  798.                              lcFilter + " not exists ( select td.pkey from " + this.cZzeipoTh_nt + " td where td.pkey = d.pkey )"                        
  799.         ENDIF                        
  800.         pcSql       = lcSql
  801.         RETURN llRetVal
  802.    
  803.     ENDPROC
  804.    
  805.     *--------------------------------------------------------------------------
  806.  
  807.     PROCEDURE GetDeleteSQL_TransDtlNotes
  808.         LPARAMETERS pcSql, plExists
  809.        
  810.         LOCAL llRetVal, lcSQL, lcFilter
  811.        
  812.         llRetVal    = .t.
  813.         IF EMPTY(this.cFilterCriteria)
  814.             lcFilter = "Where "
  815.         ELSE
  816.             lcFilter = IIF(AT("WHERE", UPPER(this.cFilterCriteria)) > 0, "","Where") + this.cFilterCriteria + " and "
  817.         ENDIF
  818.  
  819.         IF plExists
  820.             lcSQL       = " delete n from sysnotes n inner join " + this.cZzeipoTd_nt + " td on td.pkey = n.pkey and n.table_name = 'ZZEIPOTD' "
  821.         ELSE  
  822.             lcSQL       = " delete n from sysnotes n inner join zzeipotd td on td.pkey = n.fkey and n.table_name = 'ZZEIPOTD' " + ;
  823.                         " inner join zzeipoth h on h.pkey = td.fkey " +;
  824.                         lcFilter + " not exists ( select td.pkey from " + this.cZzeipoTd_nt + " tn where tn.pkey = n.pkey )"
  825.         ENDIF        
  826.         pcSql           = lcSql
  827.         RETURN llRetVal
  828.    
  829.     ENDPROC
  830.    
  831.     *--------------------------------------------------------------------------
  832.    
  833.     PROCEDURE GetDeleteSql_TransDtl
  834.         LPARAMETERS pcSql
  835.        
  836.         LOCAL llRetVal, lcSQL, lcFilter
  837.        
  838.        
  839.         llRetVal    = .t.
  840.         IF EMPTY(this.cFilterCriteria)
  841.             lcFilter = "Where "
  842.         ELSE
  843.             lcFilter = IIF(AT("WHERE", UPPER(this.cFilterCriteria)) > 0, "","Where") + this.cFilterCriteria + " and "
  844.         ENDIF
  845.         lcSQL       = "delete d from zzeipotd d inner join zzeipoth h on h.pkey = d.fkey " + lcFilter + ;
  846.                         " not exists ( select * from " + this.cZzeipotd + " td where td.pkey = d.pkey )"
  847.         pcSql       = lcSql
  848.         RETURN llRetVal
  849.    
  850.     ENDPROC
  851.    
  852.     *--------------------------------------------------------------------------
  853.    
  854.     PROCEDURE DeleteErrorRecordsFromTransWorkTables
  855.  
  856.         LOCAL llRetVal, lcSql
  857.         llRetVal            = .t.
  858.  
  859. *!*                 *--- 1010206 12/05/2005 Ilya: Add check for the warning flags
  860. *!*                 Select customer, division, po_num, pkey From (pceipoTH) ;
  861. *!*                     WHERE Errs_flg_h= "N" And (warn_flg_h <> 'Y' Or ignore_warn = 'Y') ;
  862. *!*                     ORDER By customer, division, po_num ;
  863. *!*                     INTO Cursor tcOrdList
  864.        
  865.         *--- detail notes
  866.         this.LogEntry("Delete Error Records from Trans started...")
  867.         *--- TechRec 1055684 27-Jul-2011 jjanand === Changed h.ignore_warn = 'Y' to h.ignore_warn = 'N'
  868.         lcSql               = "delete dnt from " + this.cZzeipotd_nt + " dnt inner join " + this.cZzeipoTd + " d " + ;
  869.                                 " on d.pkey = dnt.fkey inner join " + this.cZzeipoTh + " h on h.pkey = d.fkey " + ;
  870.                                 " where (h.Errs_flg_h = 'Y'  or (h.warn_flg_h = 'Y' and h.ignore_warn = 'N'))"
  871.         llRetVal            = llRetVal and v_sqlexec(lcSql)
  872.         this.LogEntry("delete detail notes " + IIF(llRetVal, "completed.","failed."))      
  873.        
  874.         *--- SLN
  875.         *--- TechRec 1055684 27-Jul-2011 jjanand === Changed h.ignore_warn = 'Y' to h.ignore_warn = 'N'
  876.         lcSql               = "delete s from " + this.cZzeipots + " s inner join " + this.cZzeipotd + " d on d.pkey = s.fkey " + ;
  877.                                 " inner join " + this.cZzeipoth + " h ON h.pkey = d.fkey " + ;
  878.                                 " where (h.Errs_flg_h= 'Y' or (h.warn_flg_h = 'Y' and h.ignore_warn = 'N'))"
  879.         llRetVal            = llRetVal and v_sqlexec(lcSql)
  880.         this.LogEntry("delete sln " + IIF(llRetVal, "completed.","failed."))                       
  881.        
  882.        
  883.         *--- detail
  884.         *--- TechRec 1055684 27-Jul-2011 jjanand === Changed h.ignore_warn = 'Y' to h.ignore_warn = 'N'
  885.         lcSql               = "delete d from " + this.cZzeipoTh + " h inner join " + this.cZzeipoTd + " d " + ;
  886.                                 " on h.pkey = d.fkey WHERE (h.Errs_flg_h= 'Y' or (h.warn_flg_h = 'Y' and h.ignore_warn = 'N'))"
  887.         llRetVal            = llRetVal and v_sqlexec(lcSql)
  888.         this.LogEntry("delete details " + IIF(llRetVal, "completed.","failed."))       
  889.        
  890.         *--- address
  891.         *--- TechRec 1055684 27-Jul-2011 jjanand === Changed h.ignore_warn = 'Y' to h.ignore_warn = 'N'
  892.         lcSql               = "delete d from " + this.cZzeipoTh + " h inner join " + this.cZzeipoTa + " d " + ;
  893.                                 " on h.pkey = d.fkey WHERE (h.Errs_flg_h= 'Y' or (h.warn_flg_h = 'Y' and h.ignore_warn = 'N'))"
  894.         llRetVal            = llRetVal and v_sqlexec(lcSql)
  895.         this.LogEntry("delete address " + IIF(llRetVal, "completed.","failed."))       
  896.        
  897.         *--- comment
  898.         *--- TechRec 1055684 27-Jul-2011 jjanand === Changed h.ignore_warn = 'Y' to h.ignore_warn = 'N'
  899.         lcSql               = "delete d from " + this.cZzeipoTh + " h inner join " + this.cZzeipoTc + " d " + ;
  900.                                 " on h.pkey = d.fkey WHERE (h.Errs_flg_h= 'Y' or (h.warn_flg_h = 'Y' and h.ignore_warn = 'N'))"
  901.         llRetVal            = llRetVal and v_sqlexec(lcSql)
  902.         this.LogEntry("delete comments " + IIF(llRetVal, "completed.","failed."))      
  903.        
  904.         *--- header notes
  905.         *--- TechRec 1055684 27-Jul-2011 jjanand === Changed h.ignore_warn = 'Y' to h.ignore_warn = 'N'
  906.         lcSql               = "delete d from " + this.cZzeipoTh + " h inner join " + this.cZzeipoTh_nt + " d " + ;
  907.                                 " on h.pkey = d.fkey WHERE (h.Errs_flg_h= 'Y' or (h.warn_flg_h = 'Y' and h.ignore_warn = 'N'))"
  908.         llRetVal            = llRetVal and v_sqlexec(lcSql)
  909.         this.LogEntry("delete header notes " + IIF(llRetVal, "completed.","failed."))
  910.  
  911.         *--- TR 1059065 23-Jan-12 SK Deleting warning records only h.ignore_warn = 'N' instead of h.ignore_warn = 'Y'
  912.         *--- TechRec 1054619 10-Jun-2011 MANI. ---
  913.         *- TR 1061758 FH - removed join on doc_num, and replace with h.pkey, wh.hfkey          
  914.         *-- Remove error record from ware house transaction  table.
  915.         lcSql               = "delete wh from " + this.cZzeipoTh + " h inner join " + this.czzeipotwhse + " wh " + ;
  916.                                 " on h.pkey = wh.hfkey WHERE (h.Errs_flg_h= 'Y' or (h.warn_flg_h = 'Y' and h.ignore_warn = 'N'))"                                                                                              
  917.         llRetVal            = llRetVal and v_sqlexec(lcSql)
  918.         this.LogEntry("delete Ware house record" + IIF(llRetVal, "completed.","failed."))
  919.         *=== TechRec 1054619 10-Jun-2011 MANI. ===
  920.         *=== TR 1059065 23-Jan-12 SK
  921.  
  922.         *--- multi discount
  923.         *--- TechRec 1055684 27-Jul-2011 jjanand === Changed h.ignore_warn = 'Y' to h.ignore_warn = 'N'
  924.         lcSql               = "delete d from " + this.cZzeipotsac + " d inner join " + this.cZzeipoth + " h on h.pkey = d.hfkey " + ;
  925.                                 " where (h.Errs_flg_h= 'Y' or (h.warn_flg_h = 'Y' and h.ignore_warn = 'N'))"
  926.         llRetVal            = llRetVal and v_sqlexec(lcSql)
  927.         this.LogEntry("delete multi notes " + IIF(llRetVal, "completed.","failed."))
  928.        
  929.         *--- TechRec 1055684 27-Jul-2011 jjanand === Changed h.ignore_warn = 'Y' to h.ignore_warn = 'N'
  930.         lcSql               = "delete h from " + this.cZzeipoTh + " h WHERE (Errs_flg_h = 'Y' or (warn_flg_h = 'Y' and ignore_warn = 'N'))"
  931.         llRetVal            = llRetVal and v_sqlexec(lcSql)
  932.         this.LogEntry("delete header " + IIF(llRetVal, "completed.","failed."))
  933.        
  934.                
  935.         *--- extender - zzzUdfDt
  936.        
  937.        
  938.         RETURN llRetVal
  939.     ENDPROC
  940.    
  941.     *--------------------------------------------------------------------------                                    
  942.    
  943.     PROCEDURE PopulateOrderWorkTables
  944.         LOCAL llRetVal
  945.        
  946.         WITH this
  947.             llRetVal     = .t. 
  948.             llRetVal     = llRetVal and .PopulateOrderWorkHeader()
  949.             llRetVal     = llRetVal and .PopulateOrderWorkDetail()
  950.             llRetVal     = llRetVal and .PopulateOrderWorkAddress()
  951.             llRetVal     = llRetVal and .PopulateOrderWorkComment()
  952.             llRetVal     = llRetVal and .PopulateOrderHeaderWorkNotes()
  953.             llRetVal     = llRetVal and .PopulateOrderDtlWorkNotes()
  954.             llRetVal     = llRetVal and .PopulateOrderExtender()
  955.             llRetVal     = llRetVal and .PopulateOrderCharges()
  956.             llRetVal     = llRetVal and .PopulateOrderDiscount()
  957.             *--- TR 1053708 19-05-2011 RKI ---*
  958.             *--- TR 1056306 09-13-2011 RKI ---*
  959.             *Commented here. Because at this stage div_rate is not updated in order header.        
  960. *!*             llRetVal     = llRetVal and setbased_calculateweightedprodcostorstd(.czzoordrd)
  961.             *=== TR 1056306 09-13-2011 RKI ===*
  962.             *=== TR 1053708 19-05-2011 RKI ===*
  963.  
  964.         ENDWITH
  965.         RETURN llRetVal
  966.     ENDPROC
  967.    
  968.     *--------------------------------------------------------------------------                                        
  969.    
  970.     PROCEDURE PopulateOrderExtender
  971.        
  972.         LOCAL llRetVal, lcSql
  973.         llRetVal    = .t.
  974.        
  975.         *--- this.cZzeipotd.zzoordrd_pkey still have transaction detail pkey - but
  976.         *---     repeated with same pkey for sizebuckets rows those are suppose to be rollup for order detail
  977.         llRetVal    = llRetVal and this.oCopyUtil.GetInsertSql(@lcSql,this.cZzeipoTDExt, this.cZzoordrdExt,,,,.t.,"dex")
  978.         lcSql       = lcSql + " where exists ( " + ;
  979.                         " select d.zzeipotd_pkey from " + this.cZzoordrd + " d " + ;
  980.                         " where d.zzeipotd_pkey = dex.oldDtlFkey )"
  981.         llRetVal    = llRetVal and v_sqlexec(lcSql)    
  982.        
  983.         RETURN llRetVal
  984.    
  985.     ENDPROC
  986.    
  987.     *--------------------------------------------------------------------------                                                                            
  988.    
  989.     PROCEDURE PopulateOrderCharges
  990.    
  991.         LOCAL llRetVal, lcSql
  992.         llRetVal    = .t.
  993.        
  994.         *--- this.cZzeipotd.zzoordrd_pkey still have transaction detail pkey - but
  995.         *---     repeated with same pkey for sizebuckets rows those are suppose to be rollup for order detail
  996.         llRetVal    = llRetVal and this.oCopyUtil.GetInsertSql(@lcSql,this.cZzeipoTR, this.cZzoordrd_chrg,,,,.t.,"ch")
  997.         lcSql       = lcSql + " where exists ( " + ;
  998.                         " select d.zzeipotd_pkey from " + this.cZzoordrd + " d " + ;
  999.                         " where d.zzeipotd_pkey = ch.fkey )"
  1000.        
  1001.         llRetVal    = llRetVal and v_sqlexec(lcSql)    
  1002.    
  1003.         RETURN llRetVal
  1004.    
  1005.     ENDPROC
  1006.    
  1007.     *--------------------------------------------------------------------------                                                                            
  1008.  
  1009.     PROCEDURE PopulateOrderDiscount
  1010.  
  1011.         LOCAL llRetVal, lcSql, lcSql_d
  1012.         llRetVal    = .t.
  1013.         llRetVal    = llRetVal and this.oCopyUtil.GetInsertSql(@lcSql,this.cZzeiPOtsac, this.cZzoordsac,,,,.t.,"md")
  1014.        
  1015.         *--- get header multi discount
  1016.         lcSql_h     = lcSql + " where exists ( " + ;
  1017.                     "   select h.zzeipoth_pkey from " + this.cZzoordrh + " h " + ;
  1018.                     "   where h.zzeipoth_pkey = md.hfkey ) and md.line_seq = 0 "
  1019.         llRetVal    = llRetVal and v_sqlexec(lcSql_h)                  
  1020.        
  1021.         *--- get detail multi discount
  1022.         lcSql_d     = lcSql + " where exists ( " + ;
  1023.                     "   select d.zzeipotd_pkey from " + this.cZzoordrd + " d " + ;
  1024.                     "   where d.zzeipotd_pkey = md.dfkey ) and md.line_seq > 0 "
  1025.         llRetVal    = llRetVal and v_sqlexec(lcSql_d)
  1026.        
  1027.         RETURN llRetVal
  1028.    
  1029.     ENDPROC
  1030.    
  1031.     *--------------------------------------------------------------------------                                                                            
  1032.    
  1033.     PROCEDURE PopulateOrderWorkHeader
  1034.        
  1035.         LOCAL llRetVal, lcSql
  1036.         llRetVal            = .t.
  1037.        
  1038.         WITH this
  1039.             llRetVal     =  llRetVal and this.oCopyUtil.GetInsertSQL(@lcSQL,.cZzeipoTH,.cZzoordrh,"'zzeipoth_pkey'","zzeipoth_pkey","h.pkey")
  1040.             llRetVal     =  llRetVal and v_sqlexec(lcSQL)
  1041.         ENDWITH
  1042.        
  1043.         RETURN llRetVal    
  1044.        
  1045.     ENDPROC
  1046.    
  1047.     *--------------------------------------------------------------------------                                    
  1048.  
  1049.     PROCEDURE CreateOrderWorkTables
  1050.    
  1051.         LOCAL llRetVal, lcSql      
  1052.         llRetVal    = .t.
  1053.        
  1054.         WITH this
  1055.        
  1056.             LOCAL lcUnique
  1057.             lcUnique            = SYS(2015)
  1058.             *--- SO order work tables
  1059.             .cZzoordrh          = "#zzoordrh" + lcUnique
  1060.             *--- create link field between order hearder and transaction header
  1061.             lcSql               = "select *, pkey as zzeipoth_pkey into " + .cZzoordrh + " from zzoordrh where 1=2"
  1062.             llRetVal            = llRetVal and v_sqlexec(lcSql)
  1063.             llRetVal            = llRetVal and this.AddToOrdTbls(.cZzoordrh)
  1064.            
  1065.             lcSql               = "Create Index pkey on " + .cZzoordrh+ " (pkey ) "
  1066.             llRetVal            = llRetVal and v_sqlexec(lcSql)
  1067.  
  1068.             lcSql               = "Create Index zzeipoth_pkey on " + .cZzoordrh+ " (zzeipoth_pkey ) "
  1069.             llRetVal            = llRetVal and v_sqlexec(lcSql)
  1070.            
  1071.            
  1072.             *--- order detail          
  1073.             .cZzoordrd          = "#zzoordrd" + lcUnique
  1074.             lcSql               = "select *, pkey as zzeipotd_pkey into " + .cZzoordrd + " from zzoordrd where 1=2"
  1075.             llRetVal            = llRetVal and v_sqlexec(lcSql)
  1076.             llRetVal            = llRetVal and this.AddToOrdTbls(.cZzoordrd)
  1077.            
  1078.             lcSql               = "Create Index pkey on " + .cZzoordrd + " (pkey) "
  1079.             llRetVal            = llRetVal and v_sqlexec(lcSql)
  1080.  
  1081.             lcSql               = "Create Index fkey on " + .cZzoordrd + " (fkey) "
  1082.             llRetVal            = llRetVal and v_sqlexec(lcSql)
  1083.  
  1084.             lcSql               = "Create Index man_bulk_seq on " + .cZzoordrd + " (man_bulk,bulk_seq ) "
  1085.             llRetVal            = llRetVal and v_sqlexec(lcSql)
  1086.            
  1087.             .cZzoordsp          = "#zzoordsp" + lcUnique
  1088.             lcSql               = "select * into " + .cZzoordsp + " from zzoordsp where 1=2"
  1089.             llRetVal            = llRetVal and v_sqlexec(lcSql)
  1090.             llRetVal            = llRetVal and this.AddToOrdTbls(.cZzoordsp)
  1091.            
  1092.             *--- One time addresses
  1093.             .cZzoordad          = "#zzoordad" + lcUnique
  1094.             lcSql               = "select * into " + .cZzoordad + " from zzoordad where 1=2"
  1095.             llRetVal            = llRetVal and v_sqlexec(lcSql)
  1096.             llRetVal            = llRetVal and this.AddToOrdTbls(.cZzoordad)
  1097.            
  1098.             .cZzootadr          = "#zzootadr" + lcUnique
  1099.             lcSql               = "select * into " + .cZzootadr + " from zzootadr where 1=2"
  1100.             llRetVal            = llRetVal and v_sqlexec(lcSql)
  1101.             llRetVal            = llRetVal and this.AddToOrdTbls(.cZzootadr)
  1102.            
  1103.             .cZzoordcd          = "#zzoordcd" + lcUnique
  1104.             lcSql               = "select * into " + .cZzoordcd + " from zzoordcd where 1=2"
  1105.             llRetVal            = llRetVal and v_sqlexec(lcSql)
  1106.             llRetVal            = llRetVal and this.AddToOrdTbls(.cZzoordcd)
  1107.            
  1108.             *--- Notes 
  1109.             .cZzoordrh_nt       = "#zzoordrh_nt" + lcUnique
  1110.             lcSql               = "select * into " + .cZzoordrh_nt + " from sysnotes where 1=2"
  1111.             llRetVal            = llRetVal and v_sqlexec(lcSql)
  1112.             llRetVal            = llRetVal and this.AddToOrdTbls(.cZzoordrh_nt)
  1113.            
  1114.             .cZzoordrd_nt       = "#zzoordrd_nt" + lcUnique
  1115.             lcSql               = "select * into " + .cZzoordrd_nt + " from sysnotes where 1=2"
  1116.             llRetVal            = llRetVal and v_sqlexec(lcSql)
  1117.             llRetVal            = llRetVal and this.AddToOrdTbls(.cZzoordrd_nt)
  1118.            
  1119.             *--- Extender object
  1120.  
  1121.             *--- trans extender table
  1122.             .cZzoordrdExt       = "#zzzUdfDt_ordrd" + lcUnique
  1123.             lcSql               = "select * into " + .cZzoordrdExt + " from " + .cZzeipoTDExt + " where 1=2"
  1124.             llRetVal            = llRetVal and v_sqlexec(lcSql)
  1125.             llRetVal            = llRetVal and this.AddToOrdTbls(.cZzoordrdExt)
  1126.            
  1127.             *--- charges
  1128.             .cZzoordrd_chrg     = "#zzxChrDS_ordrd" + lcUnique
  1129.             lcSql               = "select * into " + .cZzoordrd_chrg + " from zzxchrds where 1=2"
  1130.             llRetVal            = llRetVal and v_sqlexec(lcSql)
  1131.            
  1132.             llRetVal            = llRetVal AND v_sqlexec("ALTER TABLE " + .cZzoordrd_chrg + " DROP COLUMN pkey  ")
  1133.             llRetVal            = llRetVal AND v_sqlexec("ALTER TABLE " + .cZzoordrd_chrg + " ADD pkey INT ")                      
  1134.            
  1135.             llRetVal            = llRetVal and this.AddToOrdTbls(.cZzoordrd_chrg)
  1136.            
  1137.             *--- multi discount (orig TR 1018184)
  1138.             .cZzoordsac         = "#Zzoordsac" + lcUnique
  1139.             lcSql               = "select *, pkey as hfkey, pkey as dfkey into " + .cZzoordsac + " from zzoordsac where 1=2"
  1140.             llRetVal            = llRetVal and v_sqlexec(lcSql)
  1141.             llRetVal            = llRetVal and this.AddToOrdTbls(.cZzoordsac)
  1142.            
  1143.             *--- Transaction history tables    
  1144.             .cZzeipoHH          = "#zzeipohh" + lcUnique
  1145.             lcSql               = "select * into " + .cZzeipoHH + " from zzeipohh where 1=2"
  1146.             llRetVal            = llRetVal and v_sqlexec(lcSql)
  1147.             llRetVal            = llRetVal and this.AddToOrdTbls(.cZzeipoHH)
  1148.            
  1149.             .cZzeipoHD          = "#zzeipohd" + lcUnique
  1150.             lcSql               = "select * into " + .cZzeipoHD + " from zzeipohd where 1=2"
  1151.             llRetVal            = llRetVal and v_sqlexec(lcSql)
  1152.             llRetVal            = llRetVal and this.AddToOrdTbls(.cZzeipoHD)
  1153.            
  1154.             .cZzeipoHC          = "#zzeipohc" + lcUnique
  1155.             lcSql               = "select * into " + .cZzeipoHC + " from zzeipohc where 1=2"
  1156.             llRetVal            = llRetVal and v_sqlexec(lcSql)
  1157.             llRetVal            = llRetVal and this.AddToOrdTbls(.cZzeipoHC)
  1158.            
  1159.             .cZzeipoHA          = "#zzeipoha" + lcUnique
  1160.             lcSql               = "select * into " + .cZzeipoHA + " from zzeipoha where 1=2"
  1161.             llRetVal            = llRetVal and v_sqlexec(lcSql)
  1162.             llRetVal            = llRetVal and this.AddToOrdTbls(.cZzeipoHA)
  1163.            
  1164.             .cZzeipoHH_nt       = "#zzeipohh_nt" + lcUnique
  1165.             lcSql               = "select * into " + .cZzeipoHH_nt + " from sysnotes where 1=2"
  1166.             llRetVal            = llRetVal and v_sqlexec(lcSql)
  1167.             llRetVal            = llRetVal and this.AddToOrdTbls(.cZzeipoHH_nt)
  1168.            
  1169.             .cZzeipoHD_nt       = "#zzeipohd_nt" + lcUnique
  1170.             lcSql               = "select * into " + .cZzeipoHD_nt + " from sysnotes where 1=2"
  1171.             llRetVal            = llRetVal and v_sqlexec(lcSql)
  1172.             llRetVal            = llRetVal and this.AddToOrdTbls(.cZzeipoHD_nt)
  1173.            
  1174.             .cZzeipoHS          = "#zzeipots" + lcUnique
  1175.             lcSql               = "select * into " + .cZzeipoHS + " from zzeipohs where 1=2"
  1176.             llRetVal            = llRetVal and v_sqlexec(lcSql)
  1177.             llRetVal            = llRetVal and this.AddToOrdTbls(.cZzeipoHS)
  1178.  
  1179.             *--- TechRec 1054619 09-Jun-2011 MANI. ---
  1180.             *--- EDID/EIDH Records czzedatawhse
  1181.             .czzedatawhse           = "#zzedatawhse" + lcUnique
  1182.             lcSql               = "select * into " + .czzedatawhse + " from zzedatawhse where 1=2"
  1183.             llRetVal            = llRetVal and v_sqlexec(lcSql)
  1184.             llRetVal            = llRetVal and this.AddToOrdTbls(.czzedatawhse)
  1185.             *=== TechRec 1054619 09-Jun-2011 MANI. ===
  1186.            
  1187.            
  1188.         ENDWITH
  1189.         RETURN llRetVal
  1190.     ENDPROC
  1191.    
  1192.     *--------------------------------------------------------------------------    
  1193.  
  1194.     PROCEDURE AddToOrdTbls
  1195.         LPARAMETERS pcTblName
  1196.        
  1197.         WITH this
  1198.             .nOrdTbls   = .nOrdTbls + 1
  1199.             DIMENSION .aOrdTbls[.nOrdTbls]
  1200.             .aOrdTbls[.nOrdTbls]= pcTblName
  1201.            
  1202.             .RegisterTable(pcTblName)          
  1203.            
  1204.         ENDWITH
  1205.         RETURN .t.
  1206.     ENDPROC
  1207.    
  1208.     *--------------------------------------------------------------------------        
  1209.  
  1210.     PROCEDURE PopuldateTransHistoryWorkTables
  1211.        
  1212.         LOCAL llRetVal
  1213.         llRetVal            = .t.
  1214.         WITH this      
  1215.            
  1216.             llRetVal        = llRetVal and this.PopulateTransHdrHistory()
  1217.             llRetVal        = llRetVal and this.PopulateTransDtlHistory()
  1218.             llRetVal        = llRetVal AND this.PopulateTransSLNHistory()
  1219.             llRetVal        = llRetVal and this.PopulateTransAddressHistory()
  1220.             llRetVal        = llRetVal and this.PopulateTransCommentHistory()
  1221.             llRetVal        = llRetVal and this.PopulateTransHdrNotesHistory()
  1222.             llRetVal        = llRetVal and this.PopulateTransDtlNotesHistory()
  1223.             *--- update pkey
  1224.             llRetVal        = llRetVal and this.SetPkey_TransHistory()         
  1225.         ENDWITH
  1226.                
  1227.         RETURN llRetVal
  1228.    
  1229.     ENDPROC
  1230.        
  1231.     *--------------------------------------------------------------------------            
  1232.    
  1233.     PROCEDURE PopulateTransHdrHistory
  1234.  
  1235.         LOCAL llRetVal, lcSql
  1236.         LOCAL lcFlds, lcValues,lcExcludeFlds,lcCalFlds,lcCalFldsValues
  1237.         llRetVal    = .t.
  1238.         WITH this
  1239.             llRetVal        =   llRetVal and this.oCopyUtil.GetInsertSql(@lcSql,.cZzeipoTH,.cZzeipoHH,,,,.t.)
  1240.             llRetVal        =   llRetVal and v_sqlexec(lcSQL)
  1241.         ENDWITH
  1242.        
  1243.         RETURN llRetVal    
  1244.     ENDPROC
  1245.    
  1246.     *--------------------------------------------------------------------------
  1247.     PROCEDURE PopulateTransSLNHistory
  1248.    
  1249.         LOCAL llRetVal, lcSql
  1250.         LOCAL lcFlds, lcValues,lcExcludeFlds,lcCalFlds,lcCalFldsValues
  1251.         llRetVal    = .t.
  1252.         WITH this
  1253.             llRetVal        =   llRetVal and v_sqlexec(;
  1254.                 "UPDATE s set line_seq = d.line_seq, dtl_pkey = d.zzoordrd_pkey, ord_num = d.ord_num FROM " + .cZzeipoTS + " s JOIN " + .cZzeipoTD + " d ON s.fkey = d.pkey ")     
  1255.             llRetVal        =   llRetVal and this.oCopyUtil.GetInsertSql(@lcSql,.cZzeipoTS,.cZzeipoHS,,,,.t.)
  1256.             llRetVal        =   llRetVal and v_sqlexec(lcSQL)
  1257.             *--- TechRec 1056973 08-Dec-2011 jisingh ---
  1258.             llRetVal = llRetVal and this.PrepareDataForWhse()
  1259.             *=== TechRec 1056973 08-Dec-2011 jisingh ===
  1260.         ENDWITH
  1261.        
  1262.         RETURN llRetVal
  1263.  
  1264.     ENDPROC
  1265.    
  1266.     *--------------------------------------------------------------------------            
  1267.    
  1268.     PROCEDURE PopulateTransDtlHistory
  1269.    
  1270.         LOCAL llRetVal, lcSql
  1271.         llRetVal    = .t.
  1272.         WITH this
  1273.             llRetVal        =   llRetVal and this.oCopyUtil.GetInsertSql(@lcSql,.cZzeipoTD,.cZzeipoHD,,,,.t.)                                
  1274.             llRetVal        =   llRetVal and v_sqlexec(lcSQL)
  1275.         ENDWITH
  1276.         RETURN llRetVal
  1277.     ENDPROC
  1278.    
  1279.     *--------------------------------------------------------------------------                
  1280.  
  1281.     PROCEDURE PopulateTransAddressHistory
  1282.    
  1283.         LOCAL llRetVal, lcSql
  1284.         LOCAL lcFlds, lcValues,lcExcludeFlds,lcCalFlds,lcCalFldsValues
  1285.         llRetVal    = .t.
  1286.         WITH this
  1287.             lcFlds          =   ""
  1288.             lcValues        =   ""
  1289.             lcExcludeFlds   =   ""
  1290.             lcCalFlds       =   ""
  1291.             lcCalFldsValues =   ""
  1292.             llRetVal        =   llRetVal And .oCopyUtil.GetFldsValues(@lcFlds, @lcValues, .cZzeipoHA, .cZzeipota, ;
  1293.                                     lcExcludeFlds,lcCalFlds,lcCalFldsValues,.f.,'d')
  1294.            
  1295.             lcSql           =   " INSERT INTO " + .cZzeipoHA + " ( " + lcFlds + " ) " + ;
  1296.                                  " SELECT " + lcValues + " FROM " + .cZzeipoTA + " d where ADDR_TYPE in ('OT','BT','SO')"
  1297.             llRetVal        =   llRetVal and v_sqlexec(lcSQL)
  1298.         ENDWITH
  1299.         RETURN llRetVal
  1300.     ENDPROC
  1301.    
  1302.     *--------------------------------------------------------------------------                
  1303.    
  1304.     PROCEDURE PopulateTransCommentHistory
  1305.  
  1306.         LOCAL llRetVal, lcSql
  1307.         llRetVal    = .t.
  1308.         WITH this
  1309.             llRetVal        =   llRetVal and this.oCopyUtil.GetInsertSql(@lcSql,.cZzeipoTC,.cZzeipoHC,,,,.t.)                                                                
  1310.             llRetVal        =   llRetVal and v_sqlexec(lcSQL)
  1311.         ENDWITH
  1312.         RETURN llRetVal
  1313.     ENDPROC
  1314.    
  1315.     *--------------------------------------------------------------------------                    
  1316.    
  1317.     PROCEDURE PopulateTransHdrNotesHistory
  1318.  
  1319.         LOCAL llRetVal, lcSql
  1320.         llRetVal    = .t.
  1321.         WITH this
  1322.             llRetVal        =   llRetVal and this.oCopyUtil.GetInsertSql(@lcSql,.cZzeipoTH_nt,.cZzeipoHH_nt,"'table_name'","table_name","'ZZEIPOHH'",.t.)
  1323.             llRetVal        =   llRetVal and v_sqlexec(lcSQL)
  1324.         ENDWITH
  1325.         RETURN llRetVal
  1326.     ENDPROC
  1327.    
  1328.     *--------------------------------------------------------------------------                        
  1329.    
  1330.     PROCEDURE PopulateTransDtlNotesHistory
  1331.    
  1332.         LOCAL llRetVal, lcSql
  1333.         llRetVal    = .t.
  1334.         WITH this
  1335.             llRetVal        =   llRetVal and this.oCopyUtil.GetInsertSql(@lcSql,.cZzeipoTD_nt,.cZzeipoHD_nt,"'table_name'","table_name","'ZZEIPOHD'",.t.)
  1336.             llRetVal        =   llRetVal and v_sqlexec(lcSQL)
  1337.         ENDWITH
  1338.         RETURN llRetVal
  1339.     ENDPROC
  1340.    
  1341.     *--------------------------------------------------------------------------                        
  1342.    
  1343.     PROCEDURE SetPkey_TransHistory
  1344.        
  1345.         Local llRetVal, lnOldSelect,lcUnique, lcSQL
  1346.         llRetVal            = .T.
  1347.         lnOldSelect         = Select()
  1348.  
  1349.         *--- TR 1043347
  1350.         this.oHostProcess.FixBcSysNumForEDI850()
  1351.         *=== TR 1043347
  1352.  
  1353.         DECLARE laOrdDtls[6,4]
  1354.         laOrdDtls[1,1]  = this.cZzeipohd
  1355.         laOrdDtls[1,2]  = "fkey"    && update field of detail table
  1356.         laOrdDtls[1,3]  = "fkey"    && relation field of Detail table
  1357.         laOrdDtls[1,4]  = "pkey"    && relation field of Header table
  1358.  
  1359.         laOrdDtls[2,1]  = this.cZzeipoha
  1360.         laOrdDtls[2,2]  = "fkey"    && update field of detail table
  1361.         laOrdDtls[2,3]  = "fkey"    && relation field of Detail table
  1362.         laOrdDtls[2,4]  = "pkey"    && relation field of Header table
  1363.  
  1364.         laOrdDtls[3,1]  = this.cZzeipohc
  1365.         laOrdDtls[3,2]  = "fkey"    && update field of detail table
  1366.         laOrdDtls[3,3]  = "fkey"    && relation field of Detail table
  1367.         laOrdDtls[3,4]  = "pkey"    && relation field of Header table
  1368.  
  1369.         laOrdDtls[4,1]  = this.cZzeipohh_nt
  1370.         laOrdDtls[4,2]  = "fkey"    && update field of detail table
  1371.         laOrdDtls[4,3]  = "fkey"    && relation field of Detail table
  1372.         laOrdDtls[4,4]  = "pkey"    && relation field of Header table
  1373.  
  1374.         laOrdDtls[5,1]  = this.cZzeipohd_nt
  1375.         laOrdDtls[5,2]  = "parentKey"   && update field of detail table
  1376.         laOrdDtls[5,3]  = "parentKey"   && relation field of Detail table
  1377.         laOrdDtls[5,4]  = "pkey"        && relation field of Header table
  1378.            
  1379.         laOrdDtls[6,1]  = this.cZzeipohs
  1380.         laOrdDtls[6,2]  = "hfkey"   && update field of detail table
  1381.         laOrdDtls[6,3]  = "hfkey"   && relation field of Detail table
  1382.         laOrdDtls[6,4]  = "pkey"        && relation field of Header table  
  1383.  
  1384.         lcSQL       = "SELECT count(*) newCount from " + this.cZzeipohh
  1385.         llRetVal    = llRetVal and v_sqlexec(lcSQL,"HdrRecord")
  1386.         IF llRetVal AND HdrRecord.newCount > 0
  1387.             lnMaxPkey       = v_nextPkey("ZZEIPOHH",HdrRecord.newCount)        
  1388.             llRetVal        = llRetVal and this.oCopyUtil.HdrDtlNumKeyFldUpdate(this.cZzeipohh,"pkey",@laOrdDtls,lnMaxPkey,HdrRecord.newCount,;
  1389.                                             "pkey_I")
  1390.         ENDIF
  1391.    
  1392.         llRetVal    = llRetVal and this.SetPkey_TransDtlHistory()      
  1393.         llRetVal    = llRetVal and this.SetPkey(this.cZzeipoha,'zzeipoha')
  1394.         llRetVal    = llRetVal and this.SetPkey(this.cZzeipohc,'zzeipohc')
  1395.         llRetVal    = llRetVal and this.SetPkey(this.cZzeipohh_nt,'sysnotes')
  1396.         llRetVal    = llRetVal and this.SetPkey(this.cZzeipohd_nt,'sysnotes')
  1397.        
  1398.         SELECT(lnOldselect)    
  1399.         RETURN llRetVal
  1400.    
  1401.     ENDPROC
  1402.        
  1403.     *--------------------------------------------------------------------------            
  1404.    
  1405.     PROCEDURE SetPkey_TransDtlHistory
  1406.        
  1407.         Local llRetVal, lnOldSelect,lcSQL
  1408.         llRetVal        = .T.
  1409.         lnOldSelect     = Select()
  1410.  
  1411.         DECLARE laOrdDtls[2,4]
  1412.         laOrdDtls[1,1]  = this.cZzeipohd_nt
  1413.         laOrdDtls[1,2]  = "fkey"    && update field of detail table
  1414.         laOrdDtls[1,3]  = "fkey"    && relation field of Detail table
  1415.         laOrdDtls[1,4]  = "pkey"    && relation field of Header table
  1416.  
  1417.         laOrdDtls[2,1]  = this.cZzeipohs
  1418.         laOrdDtls[2,2]  = "fkey"    && update field of detail table
  1419.         laOrdDtls[2,3]  = "fkey"    && relation field of Detail table
  1420.         laOrdDtls[2,4]  = "pkey"    && relation field of Header table
  1421.  
  1422.         lcSQL           = "SELECT count(*) newCount from " + this.cZzeipohd
  1423.         llRetVal        = llRetVal and v_sqlexec(lcSQL,"HdrRecord")
  1424.         IF llRetVal AND HdrRecord.newCount > 0
  1425.             lnMaxPkey   = v_nextPkey("ZZEIPOHD",HdrRecord.newCount)        
  1426.             llRetVal    = llRetVal and this.oCopyUtil.HdrDtlNumKeyFldUpdate(this.cZzeipohd,"pkey",@laOrdDtls,lnMaxPkey,HdrRecord.newCount,;
  1427.                                             "pkey_I")
  1428.         ENDIF
  1429.        
  1430.         SELECT(lnOldSelect)
  1431.         RETURN llRetVal
  1432.        
  1433.     ENDPROC
  1434.    
  1435.     *--------------------------------------------------------------------------            
  1436.    
  1437.        
  1438.     PROCEDURE PopulateOrderWorkDetail
  1439.  
  1440.         LOCAL llRetVal, lcTrnsDtl_hor, lcSQL, lcUnique
  1441.         LOCAL lcFlds, lcValues,lcExcludeFlds,lcCalFlds,lcCalFldsValues
  1442.         llRetVal            = .t.
  1443.        
  1444.         WITH this
  1445.             *--- create trans detail horizontal
  1446.             lcTrnsDtl_hor   =   "" 
  1447.             llRetVal        =   llRetVal and this.GetHorizontalTransDetail(@lcTrnsDtl_hor)         
  1448.             llRetVal        =   llRetVal and this.oCopyUtil.GetInsertSQL(@lcSQL,lcTrnsDtl_hor,.cZzoordrd)
  1449.             llRetVal        =   llRetVal and v_sqlexec(lcSQL)
  1450.         ENDWITH
  1451.        
  1452.         RETURN llRetVal
  1453.        
  1454.     ENDPROC
  1455.    
  1456.     *--------------------------------------------------------------------------                
  1457.    
  1458.     PROCEDURE PopulateOrderWorkAddress
  1459.  
  1460.         LOCAL llRetVal, lcSql
  1461.         LOCAL lcFlds, lcValues,lcExcludeFlds,lcCalFlds,lcCalFldsValues
  1462.         llRetVal            = .t.
  1463.         WITH this
  1464.             lcSql           = ""
  1465.             llRetVal        =   llRetVal and this.oCopyUtil.GetInsertSql(@lcSql,.cZzeipoTA ,.cZzoordad,,,,,"h")
  1466.             lcSQL           =   lcSQL + " and h.addr_type = 'OT'"
  1467.             llRetVal        =   llRetVal and v_sqlexec(lcSQL)
  1468.             llRetVal        =   llRetVal and this.oCopyUtil.GetInsertSql(@lcSql,.cZzeipoTA,.cZzootadr,,,,,"h")
  1469.             *--- TechRec 1056973 22-Nov-2011 jisingh ---
  1470.             *lcSQL          =   lcSQL + " and h.addr_type in ('BT','SO') "
  1471.             lcSQL           =   lcSQL + " and h.addr_type not in ('OT') "
  1472.             *=== TechRec 1056973 22-Nov-2011 jisingh ===
  1473.             llRetVal        =   llRetVal and v_sqlexec(lcSQL)
  1474.  
  1475.             *--- TechRec 1054619 13-Jun-2011 MANI. ---
  1476.             llRetVal        =   llRetVal and this.oCopyUtil.GetInsertSql(@lcSql,.cZzeiPOtWHSE,.czzedatawhse,,,,,"h")
  1477.             llRetVal        =   llRetVal and v_sqlexec(lcSQL)
  1478.             *=== TechRec 1054619 13-Jun-2011 MANI. ===
  1479.  
  1480.  
  1481.             *-TR 1059065 FH        
  1482.             llRetVal        =   llRetVal and .UpdateWarehouseSizebucket(.czzedatawhse, .cZzeipoTD) 
  1483.             llRetVal        =   llRetVal and .UpdateWarehouseSLNSizebucket(.czzedatawhse, .cZzeipoTS)
  1484.  
  1485.             *-TR 1059065 FH                    
  1486.    
  1487.            
  1488.         ENDWITH
  1489.        
  1490.         RETURN llRetVal    
  1491.        
  1492.     ENDPROC
  1493.    
  1494.     *--------------------------------------------------------------------------                    
  1495.    
  1496.     PROCEDURE PopulateOrderWorkComment
  1497.    
  1498.         LOCAL llRetVal, lcSql
  1499.         LOCAL lcFlds, lcValues,lcExcludeFlds,lcCalFlds,lcCalFldsValues
  1500.         llRetVal    = .t.
  1501.         WITH this
  1502.             *--- set line seq for trans comments
  1503.             llRetVal        = llRetVal and this.SetLineSeqInTransComment()
  1504.             llRetVal        = llRetVal and this.oCopyUtil.GetInsertSql(@lcSql,.cZzeipoTC,.cZzoordcd)               
  1505.             llRetVal        = llRetVal and v_sqlexec(lcSQL)
  1506.         ENDWITH
  1507.        
  1508.         RETURN llRetVal    
  1509.        
  1510.     ENDPROC
  1511.    
  1512.     *--------------------------------------------------------------------------                    
  1513.    
  1514.     PROCEDURE PopulateOrderHeaderWorkNotes
  1515.  
  1516.         LOCAL llRetVal, lcSql
  1517.         LOCAL lcFlds, lcValues,lcExcludeFlds,lcCalFlds,lcCalFldsValues
  1518.         llRetVal    = .t.
  1519.         WITH THIS
  1520.             llRetVal        = llRetVal and this.oCopyUtil.GetInsertSql(@lcSql,.cZzeipoTh_nt,.cZzoordrh_nt,"'table_name'","table_name","'ZZOORDRH'")
  1521.             llRetVal        = llRetVal and v_sqlexec(lcSQL)        
  1522.         ENDWITH
  1523.        
  1524.         RETURN llRetVal    
  1525.        
  1526.     ENDPROC
  1527.    
  1528.     *--------------------------------------------------------------------------                    
  1529.    
  1530.     PROCEDURE PopulateOrderDtlWorkNotes
  1531.    
  1532.         LOCAL llRetVal, lcSql
  1533.         LOCAL lcFlds, lcValues,lcExcludeFlds,lcCalFlds,lcCalFldsValues
  1534.         llRetVal    = .t.
  1535.         WITH this
  1536.            
  1537.             *--- First group by field zzoodrd_pkey ( this field currently contain trans detail pkey
  1538.             *---  but can be repeated when multiple trans detail line becomes one order detail line )
  1539.             LOCAL lcZzeipoTd_nt
  1540.             lcZzeipoTd_nt   =   "#Zzeipoth_nt" + SYS(2015)
  1541.             .RegisterTable(lcZzeipoTd_nt)              
  1542.            
  1543.             llRetVal        =   llRetVal and this.RollupTransDtlNotes(@lcZzeipoTd_nt)
  1544.             llRetVal        =   llRetVal and this.SetDtlNotesLineSeqFromOrderDtl(lcZzeipoTd_nt)        
  1545.             llRetVal        =   llRetVal and this.oCopyUtil.GetInsertSql(@lcSql,lcZzeipoTd_nt,.cZzoordrd_nt,"'TABLE_NAME'","TABLE_NAME","'ZZOORDRD'")
  1546.             llRetVal        =   llRetVal and v_sqlexec(lcSQL)
  1547.            
  1548.         ENDWITH
  1549.        
  1550.         RETURN llRetVal    
  1551.        
  1552.     ENDPROC
  1553.    
  1554.     *--------------------------------------------------------------------------                    
  1555.    
  1556.     PROCEDURE RollupTransDtlNotes
  1557.         LPARAMETERS pcZzeipotd_nt
  1558.         *--- First group by field zzoodrd_pkey ( this field currently contain trans detail pkey
  1559.         *---  but can be repeated when multiple trans detail line becomes one order detail line )
  1560.         LOCAL llRetVal, lcZzeipotd_nt_cur, lnOldSelect
  1561.         lnOldSelect         = SELECT()
  1562.         lcZzeipotd_nt       = IIF(EMPTY(pcZzeipotd_nt),"#zzeipotd_nt" + SYS(2015),pcZzeipotd_nt)
  1563.         lcZzeipotd_nt_cur   = "Dtl_nt" + SYS(2015)
  1564.         llRetVal            = .t.
  1565.  
  1566.         *--- bring transaction detail notes to client cursor for rollup on order detail line
  1567.         lcSql               = "select * from " + this.cZzeipotd_nt
  1568.         llRetVal            = llRetVal and v_sqlexec(lcSql, lcZzeipotd_nt_cur)
  1569.        
  1570.         LOCAL lnZzoordrd_pkey, lcNotes, lcZzeipotd_nt_Rolled, lont, lnSeq, lnParentKey, lcNote_type, lontPrev
  1571.         lnZzoordrd_pkey         = 0
  1572.         lcNotes                 = ""
  1573.         lcZzeipotd_nt_Rolled    = "Dtl_ntOut" + SYS(2015)
  1574.         lnSeq                   = 0
  1575.         lnParentKey             = 0
  1576.         lcNote_type             = ""
  1577.        
  1578.         *-- create rolled up holding cursor
  1579.         SELECT * FROM (lcZzeipotd_nt_cur) WHERE 1=2 INTO CURSOR (lcZzeipotd_nt_Rolled) readwrite
  1580.        
  1581.         IF llRetVal
  1582.             SELECT (lcZzeipotd_nt_cur)
  1583.             INDEX on STR(zzoordrd_pkey) + note_type tag ord_pkey
  1584.             SELECT(lcZzeipotd_nt_cur)
  1585.             SCAN
  1586.                 SCATTER NAME lont memo             
  1587.                 IF lnZzoordrd_pkey  = lont.zzoordrd_pkey AND lcNote_Type = lont.Note_Type
  1588.                     *-TR 1078653 FH - lets check if the note does not exist already (non-opt does this)
  1589.                     IF Not (lont.notes $ &lcZzeipotd_nt_Rolled..notes )
  1590.                         replace notes WITH notes + lont.notes IN (lcZzeipotd_nt_Rolled)
  1591.                     endif              
  1592.                 ELSE
  1593.                     lnSeq       = lnSeq + 1
  1594.                     lont.Seq    = lnSeq
  1595.                     lont.fkey   = lont.zzoordrd_pkey   
  1596.                     SELECT(lcZzeipotd_nt_Rolled)       
  1597.                     APPEND BLANK
  1598.                     GATHER name lont memo  
  1599.                 ENDIF
  1600.                 IF lnZzoordrd_pkey  <> lont.zzoordrd_pkey  
  1601.                     lnSeq = 0
  1602.                 ENDIF              
  1603.                 lnZzoordrd_pkey = lont.zzoordrd_pkey
  1604.                 lcNote_Type     = lont.Note_Type
  1605.             ENDSCAN
  1606.         ENDIF
  1607.        
  1608.         *--- send cursor data back to server temp table
  1609.         llRetVal = llRetVal and this.oCopyUtil.SendCursorToSqlTbl(lcZzeipotd_nt_Rolled, lcZzeipotd_nt)     
  1610.        
  1611.         IF EMPTY(pcZzeipotd_nt)
  1612.             pcZzeipotd_nt = lcZzeipotd_nt
  1613.         ENDIF
  1614.        
  1615.         SELECT(lnOldSelect)
  1616.         RETURN llRetval
  1617.     ENDPROC
  1618.    
  1619.     *--------------------------------------------------------------------------                        
  1620.    
  1621.     PROCEDURE SetDtlNotesLineSeqFromOrderDtl
  1622.         LPARAMETERS pcZzeipoTd_nt
  1623.        
  1624.         LOCAL llRetVal, lcSql
  1625.         llRetVal    = .t.
  1626.         *--- get the line_seq from the order detail
  1627.         lcSql       = "Update nt set line_seq = d.line_seq from " + pcZzeipoTd_nt + " nt " + ;
  1628.                         " inner join " + this.cZzoordrd + " d on d.pkey = nt.fkey "
  1629.         llRetVal    = llRetVal and v_sqlexec(lcSql)
  1630.        
  1631.         RETURN llRetVal
  1632.     ENDPROC
  1633.    
  1634.     *--------------------------------------------------------------------------                        
  1635.    
  1636.     PROCEDURE GetHorizontalTransDetail
  1637.         LPARAMETERS pcTrnsDtl_hor
  1638.         *--- TechRec 1062694 26-Jul-2012 jjanand === Added lcSelectLst
  1639.         LOCAL llRetVal, lcTrnsDtl_hor, lcUnique, lcGrpFlds
  1640.         llRetVal        =   .t.
  1641.         lcUnique        =   SYS(2015)
  1642.         lcTrnsDtl_hor   =   "#zzeipotd_hor" + lcUnique
  1643.         lcTrnsDtl_Grp   =   "#zzeipotd_grp" + lcUnique
  1644.        
  1645. *!*                     llNewLine = m.Set_ID # lcSet_ID Or;
  1646. *!*                         llDupeUPCs Or !llMan_bulk_match;
  1647. *!*                         or lcPreviousPO1_SKU # m.po1_sku;
  1648. *!*                         or lcPreviousPO1_UPC # m.po1_upc;
  1649. *!*                         or lnPreviousPrice # m.price;
  1650. *!*                         or lnPreviousOrg_Price # m.org_price;
  1651. *!*                         or lcPreviousStyle # m.Style;
  1652. *!*                         or lcPreviousColor_code # m.color_code;
  1653. *!*                         or lcPreviousLbl_code # m.lbl_code;
  1654. *!*                         or lcPreviousDimension # m.Dimension;
  1655. *!*                         or lcPreviousRng_Style # m.Rng_Style;
  1656. *!*                         or lcPreviousRng_Color # m.Rng_color;
  1657. *!*                         or lcPreviousRng_Lbl # m.Rng_Lbl;
  1658. *!*                         or lcPreviousRng_Pack # m.Rng_pack;
  1659. *!*                         or lcPreviousStart_Date # Dtos(m.start_date);
  1660. *!*                         or lcPreviousEnd_Date # Dtos(m.end_date);
  1661. *!*                         or lcPreviousAssortment # m.Assortment ;
  1662. *!*                         or (This.lExtD And Empty(m.Set_ID) And Reccount("vzzeIPOTE_FKey")>0);
  1663. *!*                         or (This.lHaveChrgs And Reccount("vzzeIPOTR_FKey")>0)
  1664.        
  1665.         *--- Extender logic - if extender exists for zzeipotd and zzeipotd.Set_id field is blank then it is a new order detail line (per sizebucket)
  1666.         *---                - if extender exists for zzeipotd and zzeipotd.Set_id field differ between sizebucket line then it is a new order detail line (per sizebucket)
  1667.         *---                - if extender does not exists then ignore this field
  1668.         LOCAL lcZzeipotd_OrigSet_id
  1669.         llRetVal        = llRetVal and this.SetUniqueSet_id(this.cZzeipoTD, @lcZzeipotd_OrigSet_id)
  1670.  
  1671.         *--- Multi discount - in group by we need to group by on sac_code, sac_rate
  1672.         llRetVal        = llRetVal and this.SetSac_IDandRate(this.cZzeipoTD,this.cZzeiPOtsac)
  1673.  
  1674.         llRetVal        = llRetVal and this.SetDupeUpc(this.cZzeipoTH,this.cZzeipoTD) &&--- TechRec 1063128 04-Sep-2012 jjanand ===
  1675.                
  1676.         *--- max group field limit in sql 2005 is 16 fields    
  1677.         *--- TechRec 1063128 04-Sep-2012 jjanand === Added ,dupeUpcSeq
  1678.         lcGrpFlds       =   "fkey, price, org_price, division, style, color_code, lbl_code, dimension, rng_style" + ;
  1679.                                 ", rng_color, rng_pack, start_date, end_date, Assortment, Set_id, hasCharges, man_bulk, sac_id, sac_rate, dupeUpcSeq "
  1680.         lcGrpFlds       =   STRTRAN(lcGrpFlds, " ", "")
  1681.        
  1682.         * --- TR 1044713 01/07/10 CM
  1683.         *--- TR 1049551 BR - ADDED price, org_price, hasCharges, man_bulk, sac_id
  1684.         *--- TechRec 1063128 04-Sep-2012 jjanand === Added ,dupeUpcSeq
  1685.         lcOrderBy       =   "fkey, division, style, color_code, lbl_code, dimension, rng_style, rng_color, rng_pack" + ;
  1686.                                 ", start_date, end_date, Assortment, Set_id, sac_rate, price, org_price, hasCharges, man_bulk, sac_id, dupeUpcSeq "                
  1687.  
  1688.         *--- TechRec 1062694 26-Jul-2012 jjanand ---       
  1689.         *--- TechRec 1063128 04-Sep-2012 jjanand === Added ,dupeUpcSeq
  1690.         lcSelectLst     =   ", division, style, color_code, lbl_code, dimension, rng_style, rng_color, rng_pack" + ;
  1691.                                 ", start_date, end_date, Assortment, Set_id, sac_rate, price, org_price, hasCharges, man_bulk, sac_id, dupeUpcSeq "                
  1692.         *=== TechRec 1062694 26-Jul-2012 jjanand ===
  1693.        
  1694.         lcOrderBy       =   STRTRAN(lcOrderBy, " ", "")
  1695.         * === TR 1043394 01/07/10 CM       
  1696.  
  1697.         *pcSrcDtl, pcDtl_hor, pcGrpFlds, pcPkeyFld, pcSizebkFld, pcSize_qtyFld, pcTotal_qtyFld
  1698.         * --- TR 1043394 01/07/10 CM --- Changed lcGrpFlds to lcOrderBy    
  1699.         llRetVal        =   llRetVal and this.oCopyUtil.GetHorizontalDetail(this.cZzeipoTD,lcTrnsDtl_Grp,lcOrderBy,"pkey","sizebucket","total_qty","total_qty")
  1700.    
  1701.         lcSql           =   "select d.*, dg.pkey as zzeipotd_pkey " + ;
  1702.                             "   ,dg.size01_qty,dg.size02_qty,dg.size03_qty,dg.size04_qty,dg.size05_qty,dg.size06_qty,dg.size07_qty,dg.size08_qty,dg.size09_qty,dg.size10_qty " + ;
  1703.                             "   ,dg.size11_qty,dg.size12_qty,dg.size13_qty,dg.size14_qty,dg.size15_qty,dg.size16_qty,dg.size17_qty,dg.size18_qty,dg.size19_qty,dg.size20_qty " + ;
  1704.                             "   ,dg.size21_qty,dg.size22_qty,dg.size23_qty,dg.size24_qty " + ;
  1705.                             " into " + lcTrnsDtl_hor + ;
  1706.                             " from " + this.cZzeipoTD + " d inner join " + lcTrnsDtl_Grp + " dg on d.pkey = dg.pkey " + ;
  1707.                             " order by d.fkey, d.division, d.style, d.color_code, d.lbl_code, d.dimension, d.rng_style, d.start_date "
  1708.         llRetval        =   llRetVal and v_sqlexec(lcSql)
  1709.        
  1710.         .RegisterTable(lcTrnsDtl_hor)
  1711.         .RegisterTable(lcTrnsDtl_Grp)      
  1712.        
  1713.         *--- put back original set_id value in trans detail and horizontal trans detail    
  1714.         llRetVal        =   llRetVal and this.ResetOriginalSet_id(lcZzeipotd_OrigSet_id,this.cZzeipoTD)
  1715.         llRetVal        =   llRetVal and this.ResetOriginalSet_id(lcZzeipotd_OrigSet_id,lcTrnsDtl_hor)
  1716.        
  1717.         *--- update grouped pkey field
  1718.         * --- TR 1043394 01/07/10 CM --- Changed lcGrpFlds to lcOrderBy            
  1719.         llRetVal        =   llRetVal and this.SetTransDetailNotesLinkFld(lcTrnsDtl_Grp,lcOrderBy)
  1720.  
  1721.         *--- UPDATE total qty
  1722.         lcSql           =   "update d set total_qty = dg.total_qty from " + lcTrnsDtl_hor + " d inner join " + lcTrnsDtl_Grp + " dg on d.pkey = dg.pkey "
  1723.         llRetval        =   llRetVal and v_sqlexec(lcSql)
  1724.  
  1725.         * --- TR 1043394 01/07/10 CM
  1726.         *llRetVal       =   llRetVal and this.oCopyUtil.SetLineSeq(lcTrnsDtl_hor, "line_seq", "fkey", "fkey, pkey", "d", "")
  1727.         *--- TechRec 1058326 15-Dec-2011 jisingh Added STRTRAN with lcOrderBy (Existing Issue) ===
  1728.         *--- TechRec 1062694 26-Jul-2012 jjanand ==== Added lcSelectLst and set paramater plUseRowNum to .T.
  1729.         llRetVal        =   llRetVal and this.oCopyUtil.SetLineSeq(lcTrnsDtl_hor, "line_seq", "fkey", "fkey, pkey" + lcSelectLst, "d", "", .F., STRTRAN(lcOrderBy,"fkey","d.fkey"),.T.)
  1730.         * === TR 1043394 01/07/10 CM       
  1731.        
  1732.         pcTrnsDtl_hor   =   lcTrnsDtl_hor      
  1733.  
  1734.         *- 1041844 YIK 08/04/09
  1735.         *- Assign Line sequence to this.cZzeipoTD
  1736.         *- Use set of fields from original 850 to determine detail break
  1737. *--         lcipotd_temp = '#impotd_temp' + lcUnique
  1738. *--         lcSQL = 'Select * into ' + lcipotd_temp + ' from ' + this.cZzeipoTD +  ;
  1739. *--                     " order by fkey, division, style, color_code, lbl_code, dimension, rng_style, start_date "
  1740. *--         llRetval        =   llRetVal and v_sqlexec(lcSql)
  1741.  
  1742. *--- TR 1052428 1-Nov-2011 Goutam. Order by must be same to create line seq for transaction detail table and order detail table. Commented following line.
  1743. *       lcOrderBy = 'd.fkey, d.division, d.style, d.color_code, d.lbl_code, d.dimension, d.rng_style, d.start_date'
  1744. *=== TR 1052428 1-Nov-2011 Goutam
  1745.  
  1746.         *- added parameter to select distinct and order by (= .T.)
  1747.         *--- TechRec 1058326 15-Dec-2011 jisingh Added STRTRAN with lcOrderBy (Existing Issue) ===
  1748.         *--- TechRec 1061494 18-May-2012 jjanand === set paramater plUseRowNum to .T.
  1749.         llRetVal        =   llRetVal and this.oCopyUtil.SetLineSeq(this.cZzeipoTD, "line_seq", "fkey", lcGrpFlds, "d", "", .T., STRTRAN(lcOrderBy,"fkey","d.fkey"),.T.)
  1750.         llRetval        =   llRetVal and v_sqlexec(lcSql)
  1751.         *===
  1752.        
  1753.         RETURN llRetVal
  1754.        
  1755.     ENDPROC
  1756.    
  1757.     *--------------------------------------------------------------------------                
  1758.    
  1759.     PROCEDURE SetTransDetailNotesLinkFld
  1760.         LPARAMETERS pcTrnsDtl_Grp,pcGrpFlds
  1761.  
  1762.         LOCAL llRetVal, lcTrnsDtl_Grp,lcGrpFlds
  1763.         llRetVal        = .t.
  1764.         lcTrnsDtl_Grp   = pcTrnsDtl_Grp
  1765.         lcGrpFlds       = pcGrpFlds
  1766.  
  1767.         dimension   laFlds[1]
  1768.         LOCAL lnIndex, lcJoin, lcFld
  1769.         lcJoin          = ""
  1770.         this.oCopyUtil.StringToArray(lcGrpFlds,@laFlds)
  1771.        
  1772.         FOR lnIndex = 1 TO ALEN(laFlds)
  1773.             lcFld       = laFlds[lnIndex]
  1774.             lcJoin      = lcJoin + " d." + lcFld + " = g." + lcFld + " and "
  1775.         ENDFOR
  1776.        
  1777.         lcJoin          =   this.oCopyUtil.RemoveLastChar(lcJoin, " and")
  1778.         lcSql           =   " update d Set zzoordrd_pkey = g.pkey from " + this.cZzeipoTD + " d inner join " + lcTrnsDtl_Grp + " g " + ;
  1779.                                 " on " + lcJoin
  1780.         llRetval        =   llRetVal and v_sqlexec(lcSql)
  1781.  
  1782.         *--- update link pkey field in transaction detail notes
  1783.         lcSql           =   " update dnt Set zzoordrd_pkey = d.zzoordrd_pkey from " + this.cZzeipoTD + " d inner join " + this.cZzeipoTD_nt + " dnt " + ;
  1784.                             " on d.pkey = dnt.fkey "
  1785.         llRetval        =   llRetVal and v_sqlexec(lcSql)
  1786.  
  1787.  
  1788.         RETURN llRetVal
  1789.     ENDPROC
  1790.    
  1791.     *--------------------------------------------------------------------------                
  1792.  
  1793.     *--- TechRec 1063128 04-Sep-2012 jjanand ---
  1794.     PROCEDURE SetDupeUpc
  1795.         LPARAMETERS pcZzeipoTH, pcZzeipoTD
  1796.  
  1797.         LOCAL llRetVal, lcSql
  1798.        
  1799.         llRetVal    = .T.
  1800.  
  1801.         lcSql       = " alter table " + pcZzeipoTD + " add dupe_upc char(1) not null default('N')"
  1802.         llRetval    = llRetVal and v_sqlexec(lcSql)
  1803.  
  1804.         lcSql       = " alter table " + pcZzeipoTD + " add dupeUpcSeq int not null default(0)"
  1805.         llRetval    = llRetVal and v_sqlexec(lcSql)
  1806.  
  1807.         lcSql       = " Update d Set dupe_upc = h. dupe_upc From " + pcZzeipoTH + " h join " + pcZzeipoTD + " d on d.fkey = h.pkey "
  1808.         llRetval    = llRetVal and v_sqlexec(lcSql)
  1809.  
  1810.         lcSql       = " Update " + pcZzeipoTD + " set dupeUpcSeq = case when dupe_upc = 'Y' then line_Seq else 0 end"
  1811.         llRetval    = llRetVal and v_sqlexec(lcSql)
  1812.                
  1813.         RETURN llRetVal
  1814.                
  1815.     ENDPROC
  1816.     *=== TechRec 1063128 04-Sep-2012 jjanand ===                       
  1817.        
  1818.     PROCEDURE PopulateOrderWorkSp
  1819.        
  1820.         LOCAL llRetVal, lcSql
  1821.         LOCAL lcFlds, lcValues,lcExcludeFlds,lcCalFlds,lcCalFldsValues
  1822.         llRetVal    = .t.
  1823.         WITH this
  1824.             lcFlds          =   ""
  1825.             lcValues        =   ""
  1826.             lcExcludeFlds   =   ""
  1827.             lcCalFlds       =   ""
  1828.             lcCalFldsValues =   ""
  1829.            
  1830.             llRetVal        =   llRetVal And .oCopyUtil.GetFldsValues(@lcFlds, @lcValues, .cZzoordsp, .cZzoordrh , ;
  1831.                                     lcExcludeFlds,lcCalFlds,lcCalFldsValues,.f.,'h')
  1832.            
  1833.             lcSql           =   " INSERT INTO " + .cZzoordsp + " ( " + lcFlds + " ) " + ;
  1834.                                  " SELECT " + lcValues + " FROM " + .cZzoordrh + " h "
  1835.             llRetVal        =   llRetVal and v_sqlexec(lcSQL)
  1836.            
  1837.             llRetVal        =   llRetVal and this.SetPkey(.cZzoordsp,"ZZOORDSP")
  1838.  
  1839.             lcSql           =   "Update sp SET cartons  = 'N', carton_seq = 0, carton_cls = 'S', packs = 'N' from " + .cZzoordsp + " sp "
  1840.             llRetVal        =   llRetVal and v_sqlexec(lcSQL)
  1841.            
  1842.         ENDWITH
  1843.                
  1844.         RETURN llRetVal
  1845.    
  1846.     ENDPROC
  1847.    
  1848.     *--------------------------------------------------------------------------                    
  1849.  
  1850.     PROCEDURE PrepareOrderForUpdate
  1851.    
  1852.         LOCAL llRetVal
  1853.         llRetVal    = .t.
  1854.  
  1855.         this.LogEntry("Setting Pkey for all tables...")
  1856.         *--- set new pkey for order header
  1857.         llRetVal    = llRetVal  and this.SetPkey_OrderHeader()
  1858.        
  1859.         *--- set new order number
  1860.         llRetVal    = llRetVal  and this.SetOrderNumber()
  1861.        
  1862.         *--- set registration
  1863.         llRetVal    = llRetval  and this.SetRegistrationNumber()
  1864.  
  1865.         *--- set new pkey for order detail
  1866.         llRetVal    = llRetVal  and this.SetPkey_OrderDetail()
  1867.         llRetVal    = llRetVal  and this.SetPkey(this.cZzoordad,'ZZOORDAD')
  1868.         llRetVal    = llRetVal  and this.SetPkey(this.cZzootadr,'ZZOOTADR')
  1869.         llRetVal    = llRetVal  and this.SetPkey(this.cZzoordcd,'ZZOORDCD')
  1870.         llRetVal    = llRetVal  and this.SetPkey(this.cZzoordrh_nt,'sysnotes')
  1871.         llRetVal    = llRetVal  and this.SetPkey(this.cZzoordrd_nt,'sysnotes')
  1872.         llRetVal    = llRetVal  and this.SetPkey(this.cZzoordsac,'Zzoordsac')
  1873.         llRetVal    = llRetVal  and this.SetPkey(this.cZzoordrd_chrg,'zzxchrds')                   
  1874.  
  1875.         *--- TechRec 1054619 09-Jun-2011 MANI. ---
  1876.         llRetVal    = llRetVal  and this.SetPkey(this.czzedatawhse,'ZZEDATAWHSE')
  1877.         *=== TechRec 1054619 09-Jun-2011 MANI. ===
  1878.  
  1879.         .LogEntry("Setting Pkey for all Tables " + IIF(llRetVal,"completed.","failed."))
  1880.        
  1881.         llRetVal    = llRetVal  and this.PrepareDataAfter_OrderHeader()
  1882.         llRetVal    = llRetVal  and this.PrepareDataAfter_OrderDetail()
  1883.        
  1884.         ASSERT .f.
  1885.         llRetVal    = llRetVal  and this.PopulateOrderWorkSp()
  1886.         llRetVal    = llRetVal  and this.PrepareOrderExtender()
  1887.         llRetVal    = llRetVal  and this.PrepareOrderDiscount()
  1888.        
  1889.         *--- TR 1044208 Used object obulkmatch amd obulk instead of variable.  puts us in better control of destroying it.     
  1890.         *LOCAL loEdiBulkMatch
  1891.         *llRetval   = llRetVal and this.GetInstance_EDIBulkMatching(@loEdiBulkMatch)
  1892.         llRetVal    = llRetVal and .oBulkMatch.RegisterFinalOrdTbls(this.cZzoordrh,this.cZzoordrd)
  1893.         llRetVal    = llRetVal and .oBulkMatch.RegisterBulkCursor("tcBulkMatch")
  1894.         llRetVal    = llRetVal and .oBulkMatch.RegisterCreateOrderObject(this)
  1895.         llRetVal    = llRetVal and .oBulkMatch.PrepareBulkMatch()
  1896.        
  1897.         *LOCAL loBulkNibbler
  1898.         *llRetVal   = llRetVal  and this.GetInstance_BulkNibbler(@loBulkNibbler)
  1899.         llRetVal    = llRetVal  and .oBulk.RegisterOrdTbls(this.cZzoordrh,this.cZzoordrd)
  1900.         llRetVal    = llRetVal  and .oBulk.CreateNibblerQueuesForAddMode()
  1901.         *=== TR 1044208 Used object obulkmatch amd obulk instead of variable.  puts us in better control of destroying it.     
  1902.  
  1903.         llRetVal    = llRetVal  and this.SetUserIdAndTimeStampForAllTbls()
  1904.         RETURN llRetVal
  1905.        
  1906.     ENDPROC
  1907.    
  1908.     *--------------------------------------------------------------------------                    
  1909.  
  1910.     PROCEDURE SetUserIdAndTimeStampForAllTbls
  1911.        
  1912.         LOCAL llRetVal
  1913.         llRetVal    = .t.
  1914.         WITH this
  1915.             LOCAL lnIndex
  1916.             FOR lnIndex = 1 TO .nOrdTbls
  1917.                 llRetVal    = llRetVal and this.SetUserIdAndTimeStamp(.aOrdTbls[lnIndex])
  1918.             ENDFOR
  1919.         ENDWITH
  1920.         RETURN llRetVal
  1921.    
  1922.     ENDPROC
  1923.    
  1924.     *--------------------------------------------------------------------------                        
  1925.    
  1926.     PROCEDURE SetUserIdAndTimeStamp(pcTbl)
  1927.  
  1928.         Local llRetVal, lcSQL
  1929.         llRetVal        = .T.
  1930.         lcSQL           = "UPDATE h SET last_mod = convert(datetime,convert(char,getdate(),120))," + ;
  1931.                             " user_id = '" + goEnv.cUser + "' FROM " + pcTbl + " h "
  1932.         llRetVal        = llRetVal and v_sqlexec(lcSQL)
  1933.        
  1934.         Return llRetVal
  1935.     ENDPROC
  1936.    
  1937.     *--------------------------------------------------------------------------                        
  1938.    
  1939.     PROCEDURE SetPkey_OrderDetail
  1940.        
  1941.         LOCAL llRetVal, lnOldSelect
  1942.         llRetVal        = .t.
  1943.         lnOldSelect     = SELECT()
  1944.        
  1945.         DECLARE laOrdDtls[3,4]
  1946.         laOrdDtls[1,1]  = this.cZzoordrd_nt
  1947.         laOrdDtls[1,2]  = "fkey"    && update field of detail table
  1948.         laOrdDtls[1,3]  = "fkey"    && relation field of Detail table
  1949.         laOrdDtls[1,4]  = "pkey"    && relation field of Header table
  1950.  
  1951.         laOrdDtls[2,1]  = this.cZzoordrd_chrg
  1952.         laOrdDtls[2,2]  = "fkey"    && update field of detail table
  1953.         laOrdDtls[2,3]  = "fkey"    && relation field of Detail table
  1954.         laOrdDtls[2,4]  = "pkey"    && relation field of Header table
  1955.  
  1956.         *--- multi discount
  1957.         laOrdDtls[3,1]  = this.cZzoordsac
  1958.         laOrdDtls[3,2]  = "dFkey"   && update field of detail table
  1959.         laOrdDtls[3,3]  = "dFkey"   && relation field of Detail table
  1960.         laOrdDtls[3,4]  = "pkey"    && relation field of Header table
  1961.  
  1962.         lcSQL       = "SELECT count(*) newCount from " + this.cZzoordrd
  1963.         llRetVal    = llRetVal and v_sqlexec(lcSQL,"HdrRecord")
  1964.         IF llRetVal AND HdrRecord.newCount > 0
  1965.             lnMaxPkey       = v_nextPkey("ZZOORDRD",HdrRecord.newCount)        
  1966.             llRetVal        = llRetVal and this.oCopyUtil.HdrDtlNumKeyFldUpdate(this.cZzoordrd,"pkey",@laOrdDtls,lnMaxPkey,HdrRecord.newCount,;
  1967.                                             "pkey_I")                                  
  1968.         ENDIF
  1969.        
  1970.         SELECT(lnOldSelect)
  1971.         RETURN llRetVal
  1972.        
  1973.     ENDPROC
  1974.  
  1975.     *--------------------------------------------------------------------------                        
  1976.    
  1977.     PROCEDURE CreateFrmCode
  1978.        
  1979.         LOCAL llRetVal
  1980.         llRetVal            = .t.
  1981.        
  1982.        
  1983.        
  1984.         RETURN llRetVal
  1985.        
  1986.     ENDPROC
  1987.    
  1988.     *--------------------------------------------------------------------------                    
  1989.    
  1990.     PROCEDURE SetPkey_OrderHeader
  1991.    
  1992.         Local llRetVal, lnOldSelect,lcUnique, lcSQL
  1993.         llRetVal            = .T.
  1994.         lnOldSelect         = Select()
  1995.  
  1996.         DECLARE laOrdDtls[10,4] &&--- TechRec 1054619 09-Jun-2011 MANI. Changed from 9 to 10 ===
  1997.         laOrdDtls[1,1]  = this.cZzoordrd
  1998.         laOrdDtls[1,2]  = "fkey"    && update field of detail table
  1999.         laOrdDtls[1,3]  = "fkey"    && relation field of Detail table
  2000.         laOrdDtls[1,4]  = "pkey"    && relation field of Header table
  2001.  
  2002.         *--- one time address
  2003.         laOrdDtls[2,1]  = this.cZzoordad
  2004.         laOrdDtls[2,2]  = "fkey"    && update field of detail table
  2005.         laOrdDtls[2,3]  = "fkey"    && relation field of Detail table
  2006.         laOrdDtls[2,4]  = "pkey"    && relation field of Header table
  2007.  
  2008.         laOrdDtls[3,1]  = this.cZzootadr
  2009.         laOrdDtls[3,2]  = "fkey"    && update field of detail table
  2010.         laOrdDtls[3,3]  = "fkey"    && relation field of Detail table
  2011.         laOrdDtls[3,4]  = "pkey"    && relation field of Header table
  2012.  
  2013.         laOrdDtls[4,1]  = this.cZzootadr
  2014.         laOrdDtls[4,2]  = "fkey"    && update field of detail table
  2015.         laOrdDtls[4,3]  = "fkey"    && relation field of Detail table
  2016.         laOrdDtls[4,4]  = "pkey"    && relation field of Header table
  2017.  
  2018.         laOrdDtls[5,1]  = this.cZzoordcd
  2019.         laOrdDtls[5,2]  = "fkey"    && update field of detail table
  2020.         laOrdDtls[5,3]  = "fkey"    && relation field of Detail table
  2021.         laOrdDtls[5,4]  = "pkey"    && relation field of Header table
  2022.  
  2023.  
  2024.         laOrdDtls[6,1]  = this.cZzoordrh_nt
  2025.         laOrdDtls[6,2]  = "fkey"    && update field of detail table
  2026.         laOrdDtls[6,3]  = "fkey"    && relation field of Detail table
  2027.         laOrdDtls[6,4]  = "pkey"    && relation field of Header table
  2028.  
  2029.         *--- detail notes
  2030.         laOrdDtls[7,1]  = this.cZzoordrd_nt
  2031.         laOrdDtls[7,2]  = "Parentkey"   && update field of detail table
  2032.         laOrdDtls[7,3]  = "Parentkey"   && relation field of Detail table
  2033.         laOrdDtls[7,4]  = "pkey"    && relation field of Header table
  2034.  
  2035.         *--- detail charges
  2036.         laOrdDtls[8,1]  = this.cZzoordrd_chrg
  2037.         laOrdDtls[8,2]  = "hdrFkey" && update field of detail table
  2038.         laOrdDtls[8,3]  = "hdrFkey" && relation field of Detail table
  2039.         laOrdDtls[8,4]  = "pkey"    && relation field of Header table
  2040.        
  2041.         *--- multi discount
  2042.         laOrdDtls[9,1]  = this.cZzoordsac
  2043.         laOrdDtls[9,2]  = "hFkey"   && update field of detail table
  2044.         laOrdDtls[9,3]  = "hFkey"   && relation field of Detail table
  2045.         laOrdDtls[9,4]  = "pkey"    && relation field of Header table
  2046.  
  2047.         *--- TechRec 1054619 09-Jun-2011 MANI. ---
  2048.         *--- data Warehouse
  2049.         laOrdDtls[10,1]     = this.czzedatawhse
  2050.         laOrdDtls[10,2]     = "Hfkey"   && update field of detail table
  2051.         laOrdDtls[10,3]     = "Hfkey"   && relation field of Detail table
  2052.         laOrdDtls[10,4]     = "pkey"    && relation field of Header table
  2053.         *=== TechRec 1054619 09-Jun-2011 MANI. ===
  2054.        
  2055.        
  2056.         lcSQL       = "SELECT count(*) newCount from " + this.cZzoordrh
  2057.         llRetVal    = llRetVal and v_sqlexec(lcSQL,"HdrRecord")
  2058.         IF llRetVal AND HdrRecord.newCount > 0
  2059.             lnMaxPkey       = v_nextPkey("ZZOORDRH",HdrRecord.newCount)        
  2060.             llRetVal        = llRetVal and this.oCopyUtil.HdrDtlNumKeyFldUpdate(this.cZzoordrh,"pkey",@laOrdDtls,lnMaxPkey,HdrRecord.newCount,;
  2061.                                             "pkey_I")
  2062.                                            
  2063.         ENDIF
  2064.        
  2065.         Select(lnOldSelect)
  2066.         Return llRetVal
  2067.  
  2068.     ENDPROC
  2069.  
  2070.     *--------------------------------------------------------------------------                        
  2071.    
  2072.     PROCEDURE SetOrderNumber
  2073.  
  2074.         Local llRetVal, lnOldSelect,lcUnique, lcSQL, lnNewOrdCount
  2075.         llRetVal            = .T.
  2076.         lnOldSelect         = Select()
  2077.         lnNewOrdCount       = 0
  2078.  
  2079.         lcSQL               =   " select COUNT(*) as NewOrderCount FROM " + this.cZzoordrh + " h "
  2080.         llRetVal            =   llRetVal and v_sqlexec(lcSQL,"NewOrderCount")
  2081.        
  2082.         IF USED("NewOrderCount")
  2083.             lnNewOrdCount   = NewOrderCount.NewOrderCount
  2084.             USE IN NewOrderCount
  2085.         ENDIF
  2086.  
  2087.         IF NOT (llRetVal AND lnNewOrdCount > 0)
  2088.             SELECT(lnOldSelect)
  2089.             RETURN llRetVal
  2090.         ENDIF
  2091.        
  2092.         *--- update order number in order detail
  2093.         DECLARE laOrdDtls[12,4]     &&--- TechRec 1054619 09-Jun-2011 MANI. Changed from 11 to 12 ===
  2094.         laOrdDtls[1,1]  = this.cZzoordrd
  2095.         laOrdDtls[1,2]  = "ord_num" && update field of detail table
  2096.         laOrdDtls[1,3]  = "fkey"    && relation field of Detail table
  2097.         laOrdDtls[1,4]  = "pkey"    && relation field of Header table
  2098.        
  2099.         *--- update order number in transaction header
  2100.         laOrdDtls[2,1]  = this.cZzeipoTH
  2101.         laOrdDtls[2,2]  = "ord_num" && update field of detail table
  2102.         laOrdDtls[2,3]  = "pkey"    && relation field of Detail table
  2103.         laOrdDtls[2,4]  = "zzeipoth_pkey"   && relation field of Header table
  2104.  
  2105.         *--- update order number in  transaction detail
  2106.         laOrdDtls[3,1]  = this.cZzeipoTD
  2107.         laOrdDtls[3,2]  = "ord_num" && update field of detail table
  2108.         laOrdDtls[3,3]  = "fkey"    && relation field of Detail table
  2109.         laOrdDtls[3,4]  = "zzeipoth_pkey"   && relation field of Header table
  2110.        
  2111.  
  2112.         *--- update order number for  one time address
  2113.         laOrdDtls[4,1]  = this.cZzoordad
  2114.         laOrdDtls[4,2]  = "ord_num" && update field of detail table
  2115.         laOrdDtls[4,3]  = "fkey"    && relation field of Detail table
  2116.         laOrdDtls[4,4]  = "pkey"    && relation field of Header table
  2117.        
  2118.         laOrdDtls[5,1]  = this.cZzootadr
  2119.         laOrdDtls[5,2]  = "ord_num" && update field of detail table
  2120.         laOrdDtls[5,3]  = "fkey"    && relation field of Detail table
  2121.         laOrdDtls[5,4]  = "pkey"    && relation field of Header table
  2122.  
  2123.         *--- update order number in comment
  2124.         laOrdDtls[6,1]  = this.cZzoordcd
  2125.         laOrdDtls[6,2]  = "ord_num" && update field of detail table
  2126.         laOrdDtls[6,3]  = "fkey"    && relation field of Detail table
  2127.         laOrdDtls[6,4]  = "pkey"    && relation field of Header table
  2128.                
  2129.         laOrdDtls[7,1]  = this.cZzoordrh_nt
  2130.         laOrdDtls[7,2]  = "ord_num" && update field of detail table
  2131.         laOrdDtls[7,3]  = "fkey"    && relation field of Detail table
  2132.         laOrdDtls[7,4]  = "pkey"    && relation field of Header table
  2133.  
  2134.  
  2135.         laOrdDtls[8,1]  = this.cZzoordrd_nt
  2136.         laOrdDtls[8,2]  = "ord_num"     && update field of detail table
  2137.         laOrdDtls[8,3]  = "parentkey"   && relation field of Detail table
  2138.         laOrdDtls[8,4]  = "pkey"        && relation field of Header table
  2139.  
  2140.         laOrdDtls[9,1]  = this.cZzoordrd_chrg
  2141.         laOrdDtls[9,2]  = "ord_num"     && update field of detail table
  2142.         laOrdDtls[9,3]  = "hdrfkey"     && relation field of Detail table
  2143.         laOrdDtls[9,4]  = "pkey"        && relation field of Header table
  2144.  
  2145.         *--- multi discount
  2146.         laOrdDtls[10,1]     = this.cZzoordsac
  2147.         laOrdDtls[10,2]     = "ord_num" && update field of detail table
  2148.         laOrdDtls[10,3]     = "hFkey"   && relation field of Detail table
  2149.         laOrdDtls[10,4]     = "pkey"    && relation field of Header table
  2150.  
  2151.         *--- multi discount
  2152.         laOrdDtls[11,1]     = this.cZzeipoTS
  2153.         laOrdDtls[11,2]     = "ord_num" && update field of detail table
  2154.         laOrdDtls[11,3]     = "hFkey"   && relation field of Detail table
  2155.         laOrdDtls[11,4]     = "pkey"    && relation field of Header table
  2156.  
  2157.         *--- TechRec 1054619 09-Jun-2011 MANI. ---
  2158.         *--- update order number for  ware house
  2159.         laOrdDtls[12,1]     = this.czzedatawhse
  2160.         laOrdDtls[12,2]     = "ord_num" && update field of detail table
  2161.         laOrdDtls[12,3]     = "Hfkey"   && relation field of Detail table
  2162.         laOrdDtls[12,4]     = "pkey"    && relation field of Header table
  2163.         *=== TechRec 1054619 09-Jun-2011 MANI. ===
  2164.  
  2165.         *--- Set ord_num to new order header
  2166.         lnMaxOrd_num        =   v_NextId('ZZXCOMPR','ORD_NUM', lnNewOrdCount)
  2167.         llRetVal            =   llRetVal and this.oCopyUtil.HdrDtlNumKeyFldUpdate(this.cZzoordrh,"ord_num",@laOrdDtls,lnMaxOrd_num,lnNewOrdCount,;
  2168.                                             "Ord_Num_I")
  2169.         Select(lnOldSelect)
  2170.         Return llRetVal
  2171.  
  2172.     ENDPROC
  2173.  
  2174.     *--------------------------------------------------------------------------                        
  2175.    
  2176.     PROCEDURE PrepareDataAfter_OrderHeader
  2177.  
  2178.         Local llRetVal, lnOldSelect, lcSQL, lcAssign
  2179.         llRetVal            = .T.
  2180.         lnOldSelect         = Select()
  2181.        
  2182.        
  2183.         llRetVal    = llRetval  and this.SetDefaultDate_OrderHeader()
  2184.         llRetVal    = llRetVal  and this.ClearShippingFields(this.cZzoordrh)
  2185.         * TR 1050538 FH 11/10/10 - UpdateTerms
  2186.         llRetVal    = llRetVal  and this.UpdateTerms(this.cZzoordrh,this.cZzeipoTH, this.cZzeipoCr)
  2187.                
  2188.         *--- set order status
  2189.         lcAssign    = ""
  2190.         lcAssign    = lcAssign + ",ack_prn = 'N'"
  2191.         lcAssign    = lcAssign + ",AR_POST = 'N'"
  2192.         lcAssign    = lcAssign + ",bulk_red = 'Y'"
  2193.         lcAssign    = lcAssign + ",ENT_USER = '" + EDI_USER + "'"
  2194.         lcAssign    = lcAssign + ",Bulk_num = case when h.Conf_type = 'B' THEN h.Ord_num ELSE h.Bulk_num End"
  2195.        
  2196.         *--- TR 1075041 9-12-2013 VKK
  2197.         lcAssign    = lcAssign + ",fappv_amt = case when h.factor like 'CC_%' THEN " + ;
  2198.                                  "                          Coalesce((Select Top 1 ChargeAmt From " + this.czzeipoth + " where pkey = h.zzeipoth_pkey), 0) ELSE h.fappv_amt End"
  2199.         lcAssign    = lcAssign + ",fexpn_date  = case when h.factor like 'CC_%' THEN h.ftran_date  + h.facExpir_days  ELSE h.fexpn_date   End"
  2200.         *=== TR 1075041 9-12-2013 VKK
  2201.        
  2202.         lcSql       = "Update h Set ord_status = 'O'" + lcAssign + ;
  2203.                         " from " + this.cZzoordrh + " h "      
  2204.         llRetVal    = llRetVal and v_sqlexec(lcSql)
  2205.  
  2206.         llRetVal    = llRetVal  and this.SetDefaultsFromCustomerMaster()
  2207.  
  2208.         *--- TechRec 1048804 23-Aug-2010 TShenbagavalli ---
  2209.         llRetVal = llRetVal and this.SetSpsBillOptn()
  2210.         *=== TechRec 1048804 23-Aug-2010 TShenbagavalli ===
  2211.        
  2212.         *--- Set hasnotes field
  2213.         lcSql       = "Update h Set hasnotes = case when hn.pkey is null then 'N' else 'Y' END  from " +;
  2214.                          this.cZzoordrh + " h left outer join " + this.cZzoordrh_nt + " hn " + ;
  2215.                         " on hn.ord_num = h.ord_num and hn.Table_name = 'ZZOORDRH'"
  2216.         llRetVal    = llRetVal and v_sqlexec(lcSql)
  2217.        
  2218.         this.LogEntry("Prepare Order Header " + IIF(llRetVal,"completed.","failed."))
  2219.        
  2220.         Select(lnOldSelect)
  2221.         Return llRetVal
  2222.  
  2223.     ENDPROC
  2224.  
  2225.     *--------------------------------------------------------------------------
  2226.    
  2227.     *--------------------------------------------------------------------------
  2228.     * TR 1050538 FH 11/10/10 - Update terms to be org_terms if control ref, has term_deft=E and accept_TCode <> Y
  2229.     PROCEDURE UpdateTerms
  2230.     LPARAMETERS  pcOrderTbl, pcTranTbl, pcControlTbl
  2231.    
  2232.  
  2233.         LOCAL lcTerms, lcUnique, lcZzeIPOcr, lcSql, llRetVal
  2234.        
  2235.         llRetVal = .T.     
  2236.                
  2237.                     lcSql = "update h "+;
  2238.                             "   set terms = t.org_terms "+;
  2239.                             "  from " + pcOrderTbl + " h"+;
  2240.                             "  join " + pcTranTbl + " t "+;
  2241.                             "    on h.ord_num = t.ord_num"+;
  2242.                             "  join " + pcControlTbl+ " c "+;
  2243.                             "    on c.customer = h.customer "+;
  2244.                             "   and c.division = h.division "+;
  2245.                             " where c.term_deft = 'E' "+;
  2246.                             "   and c.accept_TCode <> 'Y' "    
  2247.                                    
  2248.                     llRetVal            = llRetVal and v_sqlexec(lcSql)    
  2249.        
  2250.        
  2251.         RETURN llRetVal
  2252.            
  2253.     ENDPROC
  2254.     *--------------------------------------------------------------------------            
  2255.    
  2256.     PROCEDURE SetDefaultDate_OrderHeader
  2257.        
  2258.         LOCAL llRetVal, lcSql, lcDefaultDate, lcCurDate
  2259.         llRetVal        = .t.
  2260.         lcDefaultDate   = SQLFormatTS({//})
  2261.         lcCurDate       = SQLFormatTS(TTOD(ServerDatetime()))      
  2262.  
  2263.         *--- TR 1071867 06/24/13 ATHIRUNAVU Set default date for isr_Start_date
  2264.         lcSql           = "Update h Set Act_ship_date = " + lcDefaultDate + ;
  2265.                             ", FEXPN_DATE = " + lcDefaultDate + ;
  2266.                             ", ISSUE_DATE = " + lcDefaultDate + ;
  2267.                             ", POST_DATE = " + lcDefaultDate + ;
  2268.                             ", ROUT_OBT_DATE = " + lcDefaultDate + ;
  2269.                             ", ROUT_REQ_DATE = " + lcDefaultDate + ;
  2270.                             ", SENT_856 = " + lcDefaultDate + ;
  2271.                             ", TRANS_DATE = " + lcDefaultDate + ;
  2272.                             ", ENT_DATE = " + lcCurDate + ;
  2273.                             ", ISR_START_DATE = "  + lcDefaultDate + ;
  2274.                             " FROM " + this.cZzoordrh + " h "
  2275.  
  2276.         llRetVal        = llRetVal and v_sqlexec(lcSql)
  2277.        
  2278.         RETURN llRetVal
  2279.    
  2280.     ENDPROC
  2281.    
  2282.     *--------------------------------------------------------------------------    
  2283.        
  2284.     PROCEDURE SetDefaultsFromCustomerMaster
  2285.  
  2286.         LOCAL llRetVal, lcSql, lcAssign
  2287.         llRetVal    = .t.
  2288.        
  2289.         *--- take any value came through 850 flat file first, only if that is empty then take exempted flag from customer
  2290.         lcAssign    = ", tax_empt = case when h.tax_empt = '' then r.tax_empt else h.tax_empt end"
  2291.  
  2292.         *--- original TR 1029370 - PopulateBkordInvAlloc("Vzzoordrh_iPOproc")
  2293.         lcAssign    = lcAssign  + ",Invc_Bkord_Cncl = r.Invc_Bkord_Cncl"
  2294.         lcAssign    = lcAssign  + ",Invc_Cncl_Type = r.Invc_Cncl_Type"
  2295.         lcAssign    = lcAssign  + ",Invc_Cncl_Rsn = r.Invc_Cncl_Rsn"
  2296.         lcAssign    = lcAssign  + ",Alc_Bkord_Cncl = r.Alc_Bkord_Cncl"
  2297.         lcAssign    = lcAssign  + ",Alc_Cncl_Type = r.Alc_Cncl_Type"
  2298.         lcAssign    = lcAssign  + ",Alc_Cncl_Rsn = r.Alc_Cncl_Rsn"
  2299.        
  2300.         * TR 1070508- only stamp org code if it is empty on source table
  2301.         lcSql       = "Update h Set org_code = CASE WHEN h.org_code = '' THEN r.org_code ELSE h.org_code END " + lcAssign + " from " + this.cZzoordrh + " h inner join zzxcustr r (nolock) " + ;
  2302.                         " on h.customer = r.customer where r.active_ok = 'Y' "
  2303.         llRetVal    = llRetVal and v_sqlexec(lcSql)
  2304.        
  2305.         RETURN llRetVal
  2306.    
  2307.     ENDPROC
  2308.    
  2309.     *--------------------------------------------------------------------------
  2310.    
  2311.     PROCEDURE PrepareDataAfter_OrderDetail
  2312.    
  2313.  
  2314.         Local llRetVal, lnOldSelect, lcSQL
  2315.         llRetVal            = .T.
  2316.         lnOldSelect         = Select()
  2317.  
  2318.         *--- update on hasnotes field
  2319.         lcSql       = " update d Set hasnotes = case when n.pkey is null then 'N' ELSE 'Y' end from " + this.cZzoordrd + " d " + ;
  2320.                         " left outer join " + this.cZzoordrd_nt + " n on n.ord_num = d.ord_num and n.line_seq = d.line_seq "
  2321.  
  2322.         llRetVal    = llRetVal and V_sqlexec(lcSql)                    
  2323.  
  2324.         llRetval    = llRetVal and this.SetOrderDtlLot()       
  2325.        
  2326.         llRetVal    = llRetVal and this.SetDefaultDate_OrderDetail()
  2327.         *--- cascade fields from order header to detail
  2328.         llRetVal    = llRetval and this.CascadeFldsHdrToDtl()
  2329.        
  2330.         llRetVal    = llRetVal and this.SetDefault_OrderDetail()
  2331.         *--- update order header with detail fields
  2332.         llRetVal    = llRetval and this.UpdateHdrFromDtl()
  2333.         this.LogEntry("Prepare Order Detail " + IIF(llRetVal,"completed.","failed."))
  2334.         Select(lnOldSelect)
  2335.         Return llRetVal
  2336.  
  2337.     ENDPROC
  2338.    
  2339.     *---------------------------------------------------------------------------
  2340.    
  2341.     PROCEDURE SetDefault_OrderDetail
  2342.  
  2343.         LOCAL llRetVal, lcSql, lcAssign
  2344.         llRetVal    = .t.
  2345.        
  2346.         lcAssign    = "price = case when r.price_flg = 'E' then d.org_price else d.price end "
  2347.        
  2348.         lcSql       = "Update d Set " + lcAssign + " from " + this.cZzoordrd + " d inner join " + ;
  2349.                         this.cZzoordrh + " h on h.pkey = d.fkey " + ;
  2350.                         " inner join zzeipocr r on r.customer = h.customer and r.division = d.division "
  2351.                        
  2352.         llRetVal    = llRetVal and v_sqlexec(lcSql)
  2353.        
  2354.        
  2355.  
  2356. *!*             Select (pceipoTD)
  2357. *!*             lnPrice= Iif(tceIPOcr.price_flg = "E", org_price, price)
  2358. *!*             *=
  2359. *!*             * --- 1009014 02/05 CB - Resolve the merchandise code on the sales order detail table:
  2360. *!*             If This.lResl_Mchm
  2361. *!*                 lnSelect = Select()
  2362. *!*                 lcSQLString = ;
  2363. *!*                     "SELECT Merch_Code FROM zzxmchmr " + ;
  2364. *!*                     " WHERE " + SQLFormatTS(&pceipoTD..start_date) + ;
  2365. *!*                     " BETWEEN From_Date AND To_Date"
  2366.  
  2367. *!*                 llRetVal = llRetVal And v_sqlExec(lcSQLString, "tcMerchResl")
  2368. *!*                 If Used("tcMerchResl") And Reccount("tcMerchResl") > 0
  2369. *!*                     Replace Merch_Code With tcMerchResl.Merch_Code In (tcOrderDetail)
  2370. *!*                 Endif
  2371.  
  2372. *!*                 Use In Select("tcMerchResl")
  2373. *!*                 Select (lnSelect)
  2374. *!*             Endif
  2375.         lcSql       = "Update d Set Merch_code = r.Merch_Code from " + this.cZzoordrd + " d inner join " + ;
  2376.                         " zzxmchmr r on d.start_date between r.from_date and r.to_date"            
  2377.         llRetVal    = llRetVal and v_sqlexec(lcSql)
  2378.  
  2379.  
  2380.  
  2381. *!*             * --- 1009844 05/05 CB - Moved the following code to clsSlsDf:
  2382. *!*             *!*                         * --- 1009014 02/05 CB - Resolves the Delivery Code from the Season table.
  2383.  
  2384. *!*             *--- TAN 37141 02/14/03 PL & HH - Modify the automated bulk order reduction logic
  2385. *!*             * to allow for correct matching of distro orders
  2386. *!*             Replace fkey With lnCurrOrderHeaderPkey,; && ord_num with tnOrd_num,
  2387. *!*             total_qty With 0, price With lnPrice,; &&not proper price in transdetail
  2388. *!*             line_seq With lnLine_seq, ; &&not proper line_seq when explode range style
  2389. *!*             line_status With "O", ;
  2390. *!*                 bulk_red With "Y" In (tcOrderDetail) && , notes with lcNotes
  2391.             *=== TAN 37141 02/14/03
  2392.            
  2393.             *--- TR 1034971 29-Sep-2008 Partha ---
  2394. *!*             Replace STAGE_UD WITH IIF(EMPTY(lcSTAGE_UD), lcShpst_Code, lcStage_UD) ;
  2395. *!*                  In (tcOrderDetail)
  2396.    
  2397.         Return llRetVal
  2398.  
  2399.     ENDPROC
  2400.    
  2401.     *---------------------------------------------------------------------------
  2402.    
  2403.     PROCEDURE SetOrderDtlLot
  2404.        
  2405.         LOCAL llRetVal, lcSql, llFill_lot
  2406.         llRetval    = .t.
  2407.         IF PEMSTATUS(this.oHostProcess,"lFill_Lot",5)
  2408.             llFill_lot  = this.oHostProcess.lFill_Lot
  2409.         ELSE
  2410.             llFill_Lot = goEnv.SV("850_FILL_LOT", "N") = 'Y'
  2411.         ENDIF
  2412. *!*         If This.lFill_Lot And !Empty(m.UDFOORDD6C)
  2413. *!*             m.Lot = Transform(tnOrd_num, "@L 9999999") +;
  2414. *!*                 Transform(lnLine_seq, "@L 999")
  2415. *!*         Endif
  2416.        
  2417.         IF llFill_Lot
  2418.             lcSql       =   " Update d Set lot = a.lot from " + this.cZzoordrd + " d " + ;
  2419.                             " inner join ( " + ;
  2420.                             "       select pkey, stuff('0000000',7 - len(ord_num) + 1,len(ord_num), ord_num) + " + ;
  2421.                             "           stuff('000', 3 - len(line_seq) + 1, len(line_seq), line_seq) as lot " + ;
  2422.                             "       from ( select pkey, left(ltrim(str(ord_num)),7) ord_num, left(ltrim(str(line_seq)),3) line_seq " + ;
  2423.                             " from " + this.cZzoordrd + " where UDFOORDD6C > '' ) a ) a " + ;
  2424.                             " on a.pkey = d.pkey  "  + ;
  2425.                             " where d.UDFOORDD6C > '' "
  2426.             llRetVal    = llRetVal and v_sqlexec(lcSql)
  2427.         ENDIF          
  2428.         Return llRetVal
  2429.  
  2430.     ENDPROC
  2431.    
  2432.     *---------------------------------------------------------------------------
  2433.    
  2434.     PROCEDURE CascadeFldsHdrToDtl
  2435.  
  2436.         Local llRetVal, lnOldSelect, lcSQL, lcAssign, lcfac
  2437.         llRetVal            = .T.
  2438.         lnOldSelect         = Select()
  2439.  
  2440.         LOCAL lcOrderDtl
  2441.         lcOrderDtl      = "tcOrdDtl" + SYS(2015)
  2442.         lcSql           = " select * from " + this.cZzoordrd + " where 1=2"
  2443.         llRetVal        = llRetVal and v_sqlexec(lcSql, lcOrderDtl)
  2444.        
  2445.         LOCAL lcFlds
  2446.         DIMENSION laFlds[1]
  2447.         AFIELDS(laFlds,lcOrderDtl)
  2448.         lcDeli              = ","
  2449.         lcFlds              = this.GetFldList(@laFlds,lcDeli)
  2450.         lcAssign            = " "
  2451.         lcAssign            = lcAssign + ",discount = case when d.discount > ' ' then d.discount else h.discount end "
  2452.         lcAssign            = lcAssign + ",bulk_red = h.bulk_red "
  2453.  
  2454.         lcfac               = ",facclient_num = h.facclient_num,facExpir_basis = h.facExpir_basis,facExpir_days = h.facExpir_days" + ;
  2455.                                 ",fact_Bulk = h.fact_Bulk, fact_status = h.fact_status, factor = h.factor, factor_ok = h.factor_ok " && TR 1061569 FH - added factor, factor_ok                                                                                    
  2456.         lcAssign            = lcAssign + lcfac     
  2457.        
  2458.         lcAssign            = lcAssign + ",location = case when d.location > ' ' then d.location else h.location end "
  2459.         * --- TR 1040687 6/3/09 CM --- Added hold_rsn,priority, Also Added Slsperson 1-4 and Commission 1-4
  2460.         *lcAssign           = lcAssign + ",hold_code = h.hold_code"
  2461.         *--- TR 1076947 16-May-2014 BNarayanan cascade only when column is empty ---
  2462.         *lcAssign           = lcAssign + ",hold_code = h.hold_code,hold_rsn = h.hold_rsn,priority = h.priority"
  2463.         *lcAssign           = lcAssign + ",Slsperson1 = h.Slsperson1,Slsperson2 = h.Slsperson2,Slsperson3 = h.Slsperson3,Slsperson4 = h.Slsperson4"
  2464.         *lcAssign           = lcAssign + ",Comm1 = h.Comm1,Comm2 = h.Comm2,Comm3 = h.Comm3,Comm4 = h.Comm4"
  2465.  
  2466.         lcAssign            = lcAssign + ",hold_code = h.hold_code,hold_rsn = h.hold_rsn"
  2467.         lcAssign            = lcAssign + ",priority = case when d.priority > ' ' then d.priority else h.priority end "
  2468.  
  2469.         lcAssign            = lcAssign + ",Slsperson1 = case when d.Slsperson1 > ' ' then d.Slsperson1 else h.Slsperson1 end"
  2470.         lcAssign            = lcAssign + ",Slsperson2 = case when d.Slsperson2 > ' ' then d.Slsperson2 else h.Slsperson2 end"
  2471.         lcAssign            = lcAssign + ",Slsperson3 = case when d.Slsperson3 > ' ' then d.Slsperson3 else h.Slsperson3 end"
  2472.         lcAssign            = lcAssign + ",Slsperson4 = case when d.Slsperson4 > ' ' then d.Slsperson4 else h.Slsperson4 end"
  2473.  
  2474.         lcAssign            = lcAssign + ",Comm1 = case when d.Comm1 > ' ' then d.Comm1 else h.Comm1 end"
  2475.         lcAssign            = lcAssign + ",Comm2 = case when d.Comm2 > ' ' then d.Comm2 else h.Comm2 end"
  2476.         lcAssign            = lcAssign + ",Comm3 = case when d.Comm3 > ' ' then d.Comm3 else h.Comm3 end"
  2477.         lcAssign            = lcAssign + ",Comm4 = case when d.Comm4 > ' ' then d.Comm4 else h.Comm4 end"
  2478.         *=== TR 1076947 16-May-2014 BNarayanan ===
  2479.  
  2480.         * === TR 1040687 6/3/09 CM
  2481.         lcAssign            = lcAssign + ",FTRAN_DATE = h.FTRAN_DATE"
  2482. *!*                         Replace ord_num With tnOrd_num,; && make it to live/hist-detail.
  2483. *!*                         bulk_num With Iif(&pceipoTH..conf_type= "B" ,tnOrd_num, 0), ;
  2484. *!*                         comm_calc WITH 'N' ;
  2485. *!*                             IN (pceipoTD)
  2486.  
  2487.         *--- TR 1077221 03/18/14 ATHIRUNAVU Populated PRI_DATE from header if it is empty
  2488.         lcAssign            = lcAssign + ",PRI_DATE = case when d.PRI_DATE > {ts '1900-01-01 00:00:00' } then d.PRI_DATE else h.PRI_DATE end "
  2489.         *=== TR 1077221 03/18/14 ATHIRUNAVU
  2490.  
  2491.         lcAssign            = lcAssign + ",ord_num = h.ord_num "
  2492.         lcAssign            = lcAssign + ",bulk_num = case when h.conf_type = 'B' then h.ord_num  else 0 end "
  2493.         lcAssign            = lcAssign + this.CheckFldAndAssign(lcFlds, "comm_calc","'N'",",")
  2494.  
  2495.        
  2496.         *--- cascaded fields - should we take it from short builder
  2497.        
  2498.         LOCAL lcCasacade
  2499.         this.oCopyUtil.GetCascadedAssignsql(@lcCasacade, this.cZzoordrh, this.cZzoordrd, "_CASCADED",'h')
  2500.         lcAssign            = lcAssign + "," + lcCasacade
  2501.         lcSql               = "Update d set line_status = h.ord_status " + lcAssign + " from " + this.cZzoordrd + ;
  2502.                                 " d inner join " + this.cZzoordrh + " h on h.pkey = d.fkey "
  2503.         llRetVal            = llRetVal  and v_sqlexec(lcSql)
  2504.  
  2505.         *--- TR 1074921 9-JUN-14 Venuk
  2506.         lcSql               = "Update d set orig_ord_type = h.ord_type from " + this.cZzoordrd + ;
  2507.                                 " d inner join " + this.cZzoordrh + " h on h.pkey = d.fkey  " + ;
  2508.                                 " join zzocntrc c on h.division = c.division and c.dtl_ord_type_edit ='Y' "
  2509.         llRetVal            = llRetVal  and v_sqlexec(lcSql)
  2510.         *=== TR 1074921 9-JUN-14 Venuk
  2511.  
  2512.         Select(lnOldSelect)
  2513.         Return llRetVal
  2514.  
  2515.     ENDPROC
  2516.  
  2517.     *--------------------------------------------------------------------------
  2518.  
  2519.     PROCEDURE UpdateHdrFromDtl
  2520.        
  2521.         LOCAL llRetVal, lcSql
  2522.         llRetVal    = .t.  
  2523.         lcSql       = " Update h Set ord_qty = d.ord_qty, next_line = d.next_line + 1 from " + this.cZzoordrh + " h inner join " + ;
  2524.                         " (select di.fkey, SUM(di.total_qty) as ord_qty, MAX(line_seq) as next_line from " + this.cZzoordrd + ;
  2525.                         "           di group by di.fkey) d on h.pkey = d.fkey "
  2526.                        
  2527.         llRetVal    = llRetVal and v_sqlexec(lcSql)
  2528.         Return llRetVal
  2529.  
  2530.     ENDPROC
  2531.  
  2532.     *--------------------------------------------------------------------------
  2533.    
  2534.     PROCEDURE SetRegistrationNumber
  2535.  
  2536.         Local llRetVal, lnOldSelect, lcSQL, lnNewOrdCount
  2537.         llRetVal            = .T.
  2538.         lnOldSelect         = Select()
  2539.  
  2540.         *- FH 1060901 - rewrite how to set registaration number.
  2541.         LOCAL lcUnique, lcTempGroupByPo
  2542.         lcUnique        =   SYS(2015)
  2543.         lcTempGroupByPo     =   "#zzoordrh_GroupByPo" + lcUnique
  2544.  
  2545.        
  2546.         lcSQL               = "SELECT DISTINCT PO_NUM into " + lcTempGroupByPo + " from " + this.cZzoordrh + " WHERE REG_NUM = 0 ORDER BY PO_NUM"
  2547.         llRetVal            =   llRetVal and v_sqlexec(lcSQL)
  2548.        
  2549.         this.oCopyUtil.AddIdentityColumn(lcTempGroupByPo, "Po_Num_I")
  2550.        
  2551.         lnNewOrdCount       = 0
  2552.         lcSQL               =   " select COUNT(*) as NewOrderCount FROM " + lcTempGroupByPo + " h "
  2553.         llRetVal            =   llRetVal and v_sqlexec(lcSQL,"NewOrderCount")
  2554.        
  2555.         IF USED("NewOrderCount")
  2556.             lnNewOrdCount   = NewOrderCount.NewOrderCount
  2557.             USE IN NewOrderCount
  2558.         ENDIF
  2559.        
  2560.         IF NOT (llRetVal AND lnNewOrdCount > 0)
  2561.             SELECT(lnOldSelect)
  2562.             RETURN llRetVal
  2563.         ENDIF
  2564.        
  2565.         LOCAL lnMaxReg_num, lnStartNum
  2566.         lnMaxReg_num        =   v_NextID('ZZXCOMPR', "REG_NUM", lnNewOrdCount)
  2567.         lnStartNum = lnMaxReg_num - lnNewOrdCount
  2568.        
  2569.         lcSQL  = "UPDATE H SET REG_NUM = D.PO_NUM_I + " + STR(LNSTARTNUM) + ;
  2570.                 "  FROM " + this.cZzoordrh + " H "+ ;
  2571.                 "  JOIN " + lcTempGroupByPo + " D "+;
  2572.                 "    ON D.PO_NUM = H.PO_NUM " +;
  2573.                 " WHERE H.REG_NUM = 0 "
  2574.  
  2575.         llRetVal =  llRetVal and v_sqlexec(lcSQL)      
  2576.        
  2577.         *-update this table as well
  2578.         lcSQL  = "UPDATE H SET REG_NUM = D.PO_NUM_I + " + STR(LNSTARTNUM) +;
  2579.                 "  FROM " + this.cZzeipoTH + " H"+;
  2580.                 "  JOIN " + lcTempGroupByPo + " D "+;
  2581.                 "    ON D.PO_NUM = H.PO_NUM "+;
  2582.                 " WHERE H.REG_NUM = 0"
  2583.  
  2584.         llRetVal =  llRetVal and v_sqlexec(lcSQL)          
  2585.        
  2586.        
  2587.                
  2588.                
  2589.                
  2590.                
  2591. *!*         lnNewOrdCount       = 0
  2592. *!*         lcSQL               =   " select COUNT(*) as NewOrderCount FROM " + this.cZzoordrh + " h "
  2593. *!*         llRetVal            =   llRetVal and v_sqlexec(lcSQL,"NewOrderCount")
  2594. *!*        
  2595. *!*         IF USED("NewOrderCount")
  2596. *!*             lnNewOrdCount   = NewOrderCount.NewOrderCount
  2597. *!*             USE IN NewOrderCount
  2598. *!*         ENDIF
  2599. *!*         0
  2600. *!*         IF NOT (llRetVal AND lnNewOrdCount > 0)
  2601. *!*             SELECT(lnOldSelect)
  2602. *!*             RETURN llRetVal
  2603. *!*         ENDIF
  2604.  
  2605. *!*         *--- update order number in order detail
  2606. *!*         DECLARE laOrdDtls[1,4]
  2607. *!*         laOrdDtls[1,1]  = this.cZzeipoTH
  2608. *!*         laOrdDtls[1,2]  = "reg_num" && update field of detail table
  2609. *!*         laOrdDtls[1,3]  = "pkey"    && relation field of Detail table
  2610. *!*         laOrdDtls[1,4]  = "zzeipoth_pkey"   && relation field of Header table
  2611. *!*        
  2612. *!*         LOCAL lnMaxReg_num
  2613. *!*         lnMaxReg_num        =   v_NextID('ZZXCOMPR', "REG_NUM", lnNewOrdCount)
  2614. *!*         llRetVal            =   llRetVal and this.oCopyUtil.HdrDtlNumKeyFldUpdate(this.cZzoordrh,"REG_NUM",@laOrdDtls,lnMaxReg_num,lnNewOrdCount,;
  2615. *!*                                             "Reg_Num_I")
  2616. *-TR 1060901 FH
  2617.  
  2618.         Select(lnOldSelect)
  2619.         Return llRetVal
  2620.  
  2621.     ENDPROC
  2622.    
  2623.     *--------------------------------------------------------------------------                        
  2624.                
  2625.     PROCEDURE UpdateAllTables
  2626.    
  2627.         LOCAL llRetVal, lnOldSelect
  2628.         llRetVal        = .t.
  2629.         lnOldSelect     = SELECT()
  2630.        
  2631.         Local lcInsertSQL_zzoordrh, lcInsertSQL_zzoordrd, lcInsertSQL_zzoordSP
  2632.         Local lcInsertSQL_zzoordad, lcInsertSQL_zzootadr, lcInsertSQL_zzoordcd, lcInsertSQL_zzedatawhse     &&--- TechRec 1054619 09-Jun-2011 MANI. Added lcInsertSQL_zzedatawhse ===
  2633.         LOCAL lcDeleteSQL_zzeipotwhse   && TR 1059065 01-Feb-12 SK
  2634.         LOCAL lcInsertSQL_zzoordrh_nt, lcInsertSQL_zzoordrd_nt
  2635.         Local lcInsertSQL_zzoordwh, lcInsertSQL_zzoordwd, lcInsertSQL_zzxniblr
  2636.         LOCAL lcUpdate_Zzzudfdt
  2637.         LOCAL lcInsert_Zzxchrds, lcInsert_Zzoordsac
  2638.        
  2639.         LOCAL lcInsertSQL_Zzeipohh,lcInsertSQL_Zzeipohd, lcInsertSQL_Zzeipoha, lcInsertSQL_Zzeipohc, lcInsertSQL_Zzeipohh_nt, lcInsertSQL_Zzeipohd_nt
  2640.         Local lcInsertSQL_zzoordrh_cncl, lcInsertSQL_zzoordrd_cncl
  2641.         Local lcDeleteSQL_zzoordrh, lcDeleteSQL_zzoordrd, lcDeleteSQL_zzoordSP
  2642.        
  2643.         LOCAL lcInsertSQL_Zzeipoth,lcInsertSQL_Zzeipotd
  2644.         LOCAL lcUpdateSQL_Zzeipoth,lcUpdateSQL_Zzeipotd
  2645.         LOCAL lcDeleteSQL_Zzeipoth,lcDeleteSQL_Zzeipotd
  2646.         LOCAL lcDeleteSQL_Zzeipota,lcDeleteSQL_Zzeipotc
  2647.         LOCAL lcDeleteSQL_Zzeipoth_nt, lcDeleteSQL_Zzeipotd_nt
  2648.         LOCAL lcDeleteSQL_Zzeipotr, lcDelete_Zzeipotsac
  2649.         LOCAL lcDeleteSQL_Zzeipots,lcInsertSQL_Zzeipohs
  2650.         *--- TR 1075041 9-12-2013 VKK
  2651.         LOCAL lcInsertCreditCardSQL
  2652.         STORE "" TO lcInsertCreditCardSQL
  2653.         *=== TR 1075041 9-12-2013 VKK
  2654.  
  2655.         STORE " " to lcInsertSQL_zzoordrh, lcInsertSQL_zzoordrd, lcInsertSQL_zzoordSP
  2656.         STORE " " TO lcInsertSQL_zzoordad, lcInsertSQL_zzootadr, lcInsertSQL_zzoordcd, lcInsertSQL_zzedatawhse  &&--- TechRec 1054619 09-Jun-2011 MANI. lcInsertSQL_zzedatawhse ===
  2657.         STORE " " TO lcDeleteSQL_zzeipotwhse    && TR 1059065 01-Feb-12 SK
  2658.         STORE " " TO lcInsert_Zzxchrds, lcInsert_Zzoordsac
  2659.         STORE " " TO lcUpdate_Zzzudfdt
  2660.         STORE " " TO lcInsertSQL_Zzeipohh, lcInsertSQL_Zzeipohd, lcInsertSQL_Zzeipoha, lcInsertSQL_Zzeipohc, lcInsertSQL_Zzeipohh_nt, lcInsertSQL_Zzeipohd_nt
  2661.         STORE " " TO lcDeleteSQL_Zzeipotr, lcDelete_Zzeipotsac
  2662.        
  2663.         STORE " " to lcInsertSQL_Zzeipoth, lcInsertSQL_Zzeipotd
  2664.         STORE " " to lcUpdateSQL_Zzeipoth, lcUpdateSQL_Zzeipotd
  2665.         STORE " " to lcDeleteSQL_Zzeipoth, lcDeleteSQL_Zzeipotd
  2666.         STORE " " TO lcDeleteSQL_Zzeipota,lcDeleteSQL_Zzeipotc
  2667.         STORE " " TO lcDeleteSQL_Zzeipoth_nt,lcDeleteSQL_Zzeipotd_nt
  2668.                
  2669.         STORE " " TO lcInsertSQL_zzoordrh_nt, lcInsertSQL_zzoordrd_nt
  2670.         STORE " " TO lcInsertSQL_zzoordwh, lcInsertSQL_zzoordwd, lcInsertSQL_zzxniblr
  2671.        
  2672.         STORE " " TO lcInsertSQL_zzoordrh_cncl, lcInsertSQL_zzoordrd_cncl
  2673.         STORE " " TO lcDeleteSQL_zzoordrh, lcDeleteSQL_zzoordrd, lcDeleteSQL_zzoordSP
  2674.         STORE " " TO lcDeleteSQL_Zzeipots,lcInsertSQL_Zzeipohs
  2675.  
  2676.         *--- TR 1044208    
  2677.         * Do not use this object to a variable.  the object will be a class property now.
  2678.         *LOCAL loAfterUpdateBeforeCommit
  2679.         *loAfterUpdateBeforeCommit  = null
  2680.         *llRetVal = llRetVal and this.GetInstance_AfterUpdateBeforeCommit(@loAfterUpdateBeforeCommit)
  2681. ASSERT .f.     
  2682.         *--- create order sql
  2683.         llRetVal = llRetVal And this.GetOrderInsertSQL(@lcInsertSQL_zzoordrh, @lcInsertSQL_zzoordrd, @lcInsertSQL_zzoordSP ;
  2684.                     , @lcInsertSQL_zzoordad, @lcInsertSQL_zzootadr, @lcInsertSQL_zzoordcd, @lcInsertSQL_zzoordrh_nt, @lcInsertSQL_zzoordrd_nt, @lcInsertSQL_zzedatawhse)        &&--- TechRec 1054619 09-Jun-2011 MANI. Added @lcInsertSQL_zzedatawhse ===
  2685.         *--- create transaction history sql
  2686.         llRetVal = llRetVal And this.GetTransHistoryInsertSQL(@lcInsertSQL_Zzeipohh, @lcInsertSQL_Zzeipohd, @lcInsertSQL_Zzeipoha ;
  2687.                         , @lcInsertSQL_Zzeipohc, @lcInsertSQL_Zzeipohh_nt,@lcInsertSQL_Zzeipohd_nt,@lcInsertSQL_Zzeipohs)      
  2688.        
  2689.         *--- create transaction delete sql
  2690.         llRetVal = llRetVal And this.oCopyUtil.GetDeleteSQL(@lcDeleteSQL_Zzeipoth,this.cZzeipoTH,"zzeipoth")
  2691.         llRetVal = llRetVal And this.oCopyUtil.GetDeleteSQL(@lcDeleteSQL_Zzeipotd,this.cZzeipoTD,"zzeipotd")
  2692.  
  2693.         llRetVal = llRetVal And this.oCopyUtil.GetDeleteSQL(@lcDeleteSQL_Zzeipotc,this.cZzeipoTC,"zzeipotc")
  2694.         llRetVal = llRetVal And this.oCopyUtil.GetDeleteSQL(@lcDeleteSQL_Zzeipota,this.cZzeipoTA,"zzeipota")
  2695.        
  2696.         *--- TR 1059065 01-Feb-12 SK Creating delete string for warehouse table
  2697.         llRetVal = llRetVal And this.oCopyUtil.GetDeleteSQL(@lcDeleteSQL_zzeipotwhse,this.cZzeiPOtWHSE,"zzeipotwhse")
  2698.         *=== TR 1059065 01-Feb-12 SK
  2699.        
  2700.         *llRetVal = llRetVal And this.oCopyUtil.GetDeleteSQL(@lcDeleteSQL_Zzeipotr,this.cZzeipoTR,"zzeipotr")
  2701.         lcDeleteSQL_Zzeipotr = "DELETE FROM zzeipotr where hdrfkey IN (SELECT pkey FROM " + this.cZzeipoTH + ")"       
  2702.        
  2703.         llRetVal = llRetVal And this.oCopyUtil.GetDeleteSQL(@lcDelete_Zzeipotsac,this.cZzeipotsac,"Zzeipotsac")
  2704.         llRetVal = llRetVal And this.oCopyUtil.GetDeleteSQL(@lcDeleteSQL_Zzeipots,this.cZzeipots,"Zzeipots")   
  2705.                
  2706.         llRetVal = llRetVal and this.GetDeleteSQL_TransHdrNotes(@lcDeleteSQL_Zzeipoth_nt,.t.)
  2707.         llRetVal = llRetVal and this.GetDeleteSQL_TransDtlNotes(@lcDeleteSQL_Zzeipotd_nt,.t.)
  2708.        
  2709.         llRetVal = llRetVal and this.GetUpdateSQL_OrderExtender(@lcUpdate_Zzzudfdt)
  2710.        
  2711.         llRetVal = llRetVal and this.oCopyUtil.GetInsertSql(@lcInsert_Zzxchrds,this.cZzoordrd_chrg,'zzxchrds',"'pkey'",,,.t.)
  2712.        
  2713.         llRetVal = llRetVal and this.oCopyUtil.GetInsertSql(@lcInsert_Zzoordsac,this.cZzoordsac,'zzoordsac')
  2714.        
  2715.         llRetVal = llRetVal and this.oCopyUtil.GetInsertSql(@lcInsertSQL_zzxniblr,this.cZzxniblr,"zzxniblr",,,,.t.)
  2716.         *--- 1070384 04/22/13 Ilya: trx_pkey is now identity key, so exclude it from the sql query
  2717.         llRetVal = llRetVal and this.oCopyUtil.GetInsertSql(@lcInsertSQL_zzoordwh,this.cZzoordwh,"zzoordwh","'trx_pkey'",,,.t.)
  2718.         llRetVal = llRetVal and this.oCopyUtil.GetInsertSql(@lcInsertSQL_zzoordwd,this.cZzoordwd,"zzoordwd","'trx_pkey'",,,.t.)
  2719.         *=== 1070384 04/22/13 Ilya.
  2720.        
  2721.        
  2722.         *--- create cancel orders      
  2723.         IF !EMPTY(this.cZzoordrh_cncl)
  2724.             llRetVal = llRetVal and this.oCopyUtil.GetInsertSql(@lcInsertSQL_zzoordrh_cncl,this.cZzoordrh_cncl,"zzoordrh",,,,.t.)
  2725.             lcInsertSQL_zzoordrh_cncl   = lcInsertSQL_zzoordrh_cncl + " where sql_action = 'I'"
  2726.            
  2727.             llRetVal = llRetVal and this.oCopyUtil.GetInsertSql(@lcInsertSQL_zzoordrd_cncl,this.cZzoordrd_cncl,"zzoordrd",,,,.t.)
  2728.             lcInsertSQL_zzoordrd_cncl   = lcInsertSQL_zzoordrd_cncl + " where sql_action = 'I'"
  2729.            
  2730.             *--- delete open orders
  2731.             llRetVal = llRetVal And this.oCopyUtil.GetDeleteSQL(@lcDeleteSQL_Zzoordrh,this.cZzoordrh_del,"zzoordrh")
  2732.             llRetVal = llRetVal And this.oCopyUtil.GetDeleteSQL(@lcDeleteSQL_Zzoordrd,this.cZzoordrd_del,"zzoordrd")
  2733.             llRetVal = llRetVal And this.oCopyUtil.GetDeleteSQL(@lcDeleteSQL_Zzoordsp,this.cZzoordsp_del,"zzoordsp")
  2734.         ENDIF      
  2735.  
  2736.         *--- TR 1075041 9-12-2013 VKK
  2737.         llRetVal = llRetVal AND This.GetInsertSQLForCreditCard(@lcInsertCreditCardSQL)
  2738.         *=== TR 1075041 9-12-2013 VKK
  2739.        
  2740.         LOCAL lcAfterCommitErrorCursor     
  2741.         Local llBeganTransaction,lnTranErrCount,lcTranFailedMsg
  2742.         llBeganTransaction  = This.BeginTransaction()
  2743.         lnTranErrCount      = 0
  2744.         lcTranFailedMsg     = ""
  2745.        
  2746.         If llBeganTransaction And llRetVal         
  2747.             this.SqlExecInTran(lcInsertSQL_zzoordrh, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Create order header failed.")
  2748.             this.SqlExecInTran(lcInsertSQL_zzoordrd, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Create order detail failed.")
  2749.             this.SqlExecInTran(lcInsertSQL_zzoordSP, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Create order carton link records creation failed.")
  2750.             this.SqlExecInTran(lcInsertSQL_zzoordad, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Create order address failed.")
  2751.             this.SqlExecInTran(lcInsertSQL_zzootadr, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Create order one time address failed.")
  2752.             this.SqlExecInTran(lcInsertSQL_zzoordcd, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Create order comment failed.")
  2753.             this.SqlExecInTran(lcInsertSQL_zzoordrh_nt, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Create order header notes failed.")
  2754.             this.SqlExecInTran(lcInsertSQL_zzoordrd_nt, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Create order detail notes failed.")
  2755.             this.SqlExecInTran(lcUpdate_Zzzudfdt, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Create order detail extender failed.")
  2756.             this.SqlExecInTran(lcInsert_Zzxchrds, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Create order detail charges failed.")
  2757.             this.SqlExecInTran(lcInsert_Zzoordsac, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Create order multi discount failed.")        
  2758.  
  2759.             *--- TechRec 1054619 09-Jun-2011 MANI. ---
  2760.             this.SqlExecInTran(lcInsertSQL_zzedatawhse, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Create data warehouse failed.")
  2761.             *=== TechRec 1054619 09-Jun-2011 MANI. ===
  2762.            
  2763.             IF !EMPTY(lcInsertSQL_zzoordrh_cncl)
  2764.                 this.SqlExecInTran(lcInsertSQL_zzoordrh_cncl, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Create cancel order header failed.")
  2765.                 this.SqlExecInTran(lcInsertSQL_zzoordrd_cncl, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Create cancel order detail failed.")
  2766.                
  2767.                 this.SqlExecInTran(lcDeleteSQL_Zzoordrh, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Delete open order header failed.")
  2768.                 this.SqlExecInTran(lcDeleteSQL_Zzoordrd, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Delete open order detail failed.")
  2769.                 this.SqlExecInTran(lcDeleteSQL_Zzoordsp, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Delete open order sp failed.")
  2770.             ENDIF
  2771.            
  2772.             *--- insert transaction data to history
  2773.             this.SqlExecInTran(lcInsertSQL_Zzeipohh, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Insert transaction header history failed.")
  2774.             this.SqlExecInTran(lcInsertSQL_Zzeipohd, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Insert transaction detail history failed.")
  2775.             this.SqlExecInTran(lcInsertSQL_Zzeipoha, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Insert transaction address history failed.")
  2776.             this.SqlExecInTran(lcInsertSQL_Zzeipohc, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Insert transaction comment history failed.")
  2777.             this.SqlExecInTran(lcInsertSQL_Zzeipohh_nt, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Insert transaction header notes history failed.")
  2778.             this.SqlExecInTran(lcInsertSQL_Zzeipohd_nt, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Insert transaction detail notes history failed.")
  2779.             this.SqlExecInTran(lcInsertSQL_Zzeipohs, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Insert transaction detail sln history failed.")
  2780.  
  2781.             *--- TR 1075041 9-12-2013 VKK
  2782.             this.SqlExecInTran(lcInsertCreditCardSQL, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Insert credit card failed.")
  2783.             *=== TR 1075041 9-12-2013 VKK
  2784.  
  2785.             *--- delete transaction that are good, at this point all records in this.cZzeipoTH are good
  2786.             this.SqlExecInTran(lcDeleteSQL_Zzeipoth, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Delete transaction header failed.")
  2787.             this.SqlExecInTran(lcDeleteSQL_Zzeipotd, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Delete transaction detail failed.")
  2788.             this.SqlExecInTran(lcDeleteSQL_Zzeipota, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Delete transaction address failed.")
  2789.             this.SqlExecInTran(lcDeleteSQL_Zzeipotc, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Delete transaction comment failed.")
  2790.             this.SqlExecInTran(lcDeleteSQL_Zzeipoth_nt, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Delete transaction header notes failed.")
  2791.             this.SqlExecInTran(lcDeleteSQL_Zzeipotd_nt, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Delete transaction detail notes failed.")
  2792.             this.SqlExecInTran(lcDeleteSQL_Zzeipotr, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Delete transaction detail charges failed.")
  2793.             this.SqlExecInTran(lcDeleteSQL_Zzeipots, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Delete transaction detail slns failed.")                       
  2794.             this.SqlExecInTran(lcDelete_Zzeipotsac, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Delete transaction SACs failed.")&& TR 1046111                  
  2795.            
  2796.             *-- TR 1059065 01-Feb-12 SK Executing the delete string for warehouse table
  2797.             this.SqlExecInTran(lcDeleteSQL_zzeipotwhse, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Delete transaction warehouse failed.")
  2798.             *=== TR 1059065 01-Feb-12 SK
  2799.            
  2800.             *--- bulk nibbler
  2801.             this.SqlExecInTran(lcInsertSQL_zzxniblr, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Insert nibbler failed.")
  2802.             this.SqlExecInTran(lcInsertSQL_zzoordwh, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Insert nibbler work header failed.")
  2803.             this.SqlExecInTran(lcInsertSQL_zzoordwd, @llRetVal, @lnTranErrCount, @lcTranFailedMsg, "Insert nibbler work detail failed.")
  2804.            
  2805.             *--- TR 1044208
  2806.             *llRetVal   = llRetVal and loAfterUpdateBeforeCommit.AfterUpdateBeforeCommit(@lcAfterCommitErrorCursor)
  2807.             llRetVal    = llRetVal and .oAfterUpdate.AfterUpdateBeforeCommit(@lcAfterCommitErrorCursor)        
  2808.             *=== TR 1044208
  2809.            
  2810.             If llRetVal
  2811.                 This.EndTransaction()
  2812.                 This.LogEntry("Transaction successful.")
  2813.                 *--- increase toProcess vaulue for new transaction records.
  2814.                 *this.IncreaseToProcessCount(lcZzeipcth_new)
  2815.             Else
  2816.                 This.RollbackTransaction()
  2817.                 This.LogEntry("---------------------------------------------")
  2818.                 This.LogEntry(lcTranFailedMsg)
  2819.                 This.LogEntry("Transaction rolled back.")
  2820.                 This.LogEntry("=============================================")
  2821.             ENDIF
  2822.             llRetVal    = llRetVal and this.LogAfterUpdateBeforeCommitError(lcAfterCommitErrorCursor)
  2823.         Else
  2824.             If llRetVal && and not llBeganTransaction
  2825.                 *--- LogEntry : unable to open Transaction
  2826.                 This.LogEntry("Fatal error : Fail to open transaction.")
  2827.             Endif
  2828.         Endif
  2829.        
  2830.        
  2831.         llRetVal    = llRetVal and this.UpdateProcessStatus()
  2832.        
  2833.         Select(lnOldSelect)    
  2834.         RETURN llRetVal
  2835.        
  2836.     ENDPROC
  2837.    
  2838.     *--- TR 1075041 9-12-2013 VKK
  2839. *============================================================
  2840.  
  2841.     FUNCTION GetInsertSQLForCreditCard
  2842.         LPARAMETERS tcInsertCreditCardSQL
  2843.         LOCAL llRetVal, lnSelect
  2844.        
  2845.         llRetVal = true
  2846.         lnSelect = SELECT()
  2847.        
  2848.         WITH This
  2849.             IF vl_compr(,"creditcard_ok") = "Y"
  2850.  
  2851.                 lcEncAlogorithm  = goEnv.sv("CC_XP_CRYPT_ENCRYPTION_MODE")
  2852.  
  2853.                 lcCrypt_password = 'üÇPOôqa?¦d--(ä¦'  && (suggested password)
  2854.                 lcCrypt_key      = '@¡å®–¥§©'         && (suggested key)  
  2855.  
  2856.                    
  2857. *!*                 lcSQLString = " SELECT c.address1, c.address2, c.city, c.state, c.zipcode, c.country, c.customer, c.card_type, " + ;
  2858. *!*                               "        c.card_name, c.cc_number, c.expirationdate AS cc_expirationdate, c.cc_cvv, oh.pkey " + ;
  2859. *!*                               " FROM " + .cZzoordrh + " oh " + ;
  2860. *!*                               " JOIN zzwfactr f " + ;
  2861. *!*                               "   ON f.factor = oh.factor " + ;
  2862. *!*                               "  AND f.ccard_ok = 'Y' " + ;                          
  2863. *!*                               " JOIN zzxccard c " + ;
  2864. *!*                               "   ON c.customer = oh.customer " + ;
  2865. *!*                               "  AND c.card_type = CASE WHEN oh.factor = 'CC_A' THEN 'A' " + ;
  2866. *!*                               "                         WHEN oh.factor = 'CC_V' THEN 'V' " + ;
  2867. *!*                               "                         WHEN oh.factor = 'CC_M' THEN 'M' " + ;
  2868. *!*                               "                         WHEN oh.factor = 'CC_D' THEN 'D' " + ;
  2869. *!*                               "                         WHEN oh.factor = 'CC_O' THEN 'O' END " + ;
  2870. *!*                               "  AND c.pkey = (SELECT MIN(pkey) FROM zzxccard " + ;
  2871. *!*                               "                WHERE customer = c.customer " + ;
  2872. *!*                               "                  AND card_type = c.card_type) " + ;
  2873. *!*                               " WHERE oh.conf_type = 'A' " + ;
  2874. *!*                               "   AND oh.autofact <> 'Y' "
  2875.  
  2876. *!*                 lcTmpTable = SQLTableFromQuery(lcSQLString)
  2877.                                      
  2878.                 IF llRetVal
  2879.                     WITH .oSB
  2880.                         .SB_Reset()
  2881.                         .cSB_Target = "zzocrdcr"
  2882.                         .cSB_Source = this.czzeipoth
  2883.                         .cSB_SourceAlias = "h"
  2884.  
  2885.                         .cSB_FROMExpandedString = " Join " + this.czzoordrh + " ordh " + ;
  2886.                                                   "   on ordh.ord_num = h.ord_num " + ;
  2887.                                                   "  And ordh.division = h.division "
  2888.  
  2889.                         .cSB_WhereString = " NOT EXISTS(SELECT 1 FROM zzocrdcr " + ;
  2890.                                            "            WHERE " + ;
  2891.                                            "              fkey = ordh.pkey) " + ;
  2892.                                            " AND h.cardno > '' "
  2893.                                                                                            
  2894.                         .SB_SetLiteralFieldValue("last_mod",SQLFormatTS(DATETIME()))
  2895.                         .SB_SetLiteralFieldValue("user_id",SQLFormatChar(goEnv.envLogin.cUserName))
  2896.                         .SB_SetLiteralFieldValue("notes",SQLFormatChar(""))
  2897.                        
  2898.                         .SB_KeySetAddKeySpec("pkey","Int","h.pkey")
  2899.                        
  2900.                         .SB_SetLiteralFieldValue("fkey", "ordh.pkey")
  2901.                        
  2902.                         IF This.oHostProcess.lAuthorizeNetMode
  2903.                             .SB_SetLiteralFieldValue("use_chrg_amt", "'N'")
  2904.                             .SB_SetLiteralFieldValue("cc_number", "h.cardno")
  2905.                             .SB_SetLiteralFieldValue("cardno", "h.cardno")
  2906.                             .SB_SetLiteralFieldValue("expirationdate", "h.expirationdate")
  2907.                             .SB_SetLiteralFieldValue("cc_expirationdate", "''")
  2908.                         ELSE
  2909.                             .SB_SetLiteralFieldValue("use_chrg_amt", "'Y'")
  2910.  
  2911.                             lccc_number= " dbo.BCFN_XP_encrypt( h.cardno,'" + ;
  2912.                                                lcEncAlogorithm + "','" + lcCrypt_key + "','" + lcCrypt_password + "')"
  2913.                             .SB_SetLiteralFieldValue("cc_number", lccc_number)
  2914.                             .SB_SetLiteralFieldValue("cardno", "''")
  2915.  
  2916.                             lccc_expdate= " dbo.BCFN_XP_encrypt( h.expirationdate,'" + ;
  2917.                                                lcEncAlogorithm + "','" + lcCrypt_key + "','" + lcCrypt_password + "')"
  2918.                            
  2919.                             .SB_SetLiteralFieldValue("cc_expirationdate", lccc_expdate)
  2920.                             .SB_SetLiteralFieldValue("expirationdate", "''")
  2921.                         ENDIF
  2922.                            
  2923. *                       .SB_SetLiteralFieldValue("cc_cvv", "h.cc_cvv")
  2924.                        
  2925.                         llRetVal = llRetVal AND .SB_GenerateSQLInsertString()
  2926.                         tcInsertCreditCardSQL = .cSQLString
  2927.                     ENDWITH
  2928.  
  2929.                 ENDIF
  2930.             ENDIF  
  2931.        
  2932.         ENDWITH
  2933.                
  2934.         SELECT (lnSelect)
  2935.         RETURN llRetVal
  2936.     ENDFUNC
  2937. *=== TR 1075041 9-12-2013 VKK  
  2938.  
  2939.     *--------------------------------------------------------------------------                    
  2940.    
  2941.     PROCEDURE LogAfterUpdateBeforeCommitError
  2942.         LPARAMETERS pcAfterCommitErrorCursor
  2943.         LOCAL llRetVal
  2944.         llRetVal    = .t.
  2945.        
  2946.        
  2947.         RETURN llRetVal
  2948.     ENDPROC
  2949.    
  2950.     *--------------------------------------------------------------------------                        
  2951.  
  2952.     PROCEDURE GetOrderInsertSQL
  2953.         LPARAMETERS pcInsertSQL_zzoordrh, pcInsertSQL_zzoordrd, pcInsertSQL_zzoordSP, pcInsertSQL_zzoordad ;
  2954.             , pcInsertSQL_zzootadr, pcInsertSQL_zzoordcd, pcInsertSQL_zzoordrh_nt, pcInsertSQL_zzoordrd_nt, ;
  2955.             pcInsertSQL_zzedatawhse
  2956.         &&--- TechRec 1054619 09-Jun-2011 MANI. pcInsertSQL_zzedatawhse ===
  2957.  
  2958.         Local llRetVal, lnOldSelect
  2959.         llRetVal            = .t.
  2960.         lnOldSelect         = Select()
  2961.         llRetVal            = .T.
  2962.        
  2963.         LOCAL lcInsertSQL_zzoordrh, lcInsertSQL_zzoordrd, lcInsertSQL_zzoordSP
  2964.         LOCAL lcInsertSQL_zzoordad, lcInsertSQL_zzootadr, lcInsertSQL_zzoordcd, lcInsertSQL_zzedatawhse &&--- TechRec 1054619 09-Jun-2011 MANI. lcInsertSQL_zzedatawhse ===
  2965.         LOCAL lcInsertSQL_zzoordrh_nt, lcInsertSQL_zzoordrd_nt
  2966.         STORE " " TO lcInsertSQL_zzoordrh, lcInsertSQL_zzoordrd, lcInsertSQL_zzoordSP
  2967.         STORE " " TO lcInsertSQL_zzoordad, lcInsertSQL_zzootadr, lcInsertSQL_zzoordcd, lcInsertSQL_zzedatawhse  &&--- TechRec 1054619 09-Jun-2011 MANI. lcInsertSQL_zzedatawhse ===
  2968.         STORE " " TO pcInsertSQL_zzoordrh_nt, lcInsertSQL_zzoordrd_nt
  2969.        
  2970.         LOCAL lcZzoordrh, lcZzoordrd, lcZzoordsp,lcZzoordad, lcZzootadr, lcZzoordcd, lcZzoordrh_nt, lcZzoordrd_nt, lczzedatawhse    &&--- TechRec 1054619 09-Jun-2011 MANI. Added lczzedatawhse ===
  2971.         lcZzoordrh          = this.cZzoordrh
  2972.         lcZzoordrd          = this.cZzoordrd
  2973.         lcZzoordsp          = this.cZzoordsp
  2974.         lcZzoordad          = this.cZzoordad
  2975.         lcZzootadr          = this.cZzootadr
  2976.         lcZzoordcd          = this.cZzoordcd
  2977.         lcZzoordrh_nt       = this.cZzoordrh_nt
  2978.         lcZzoordrd_nt       = this.cZzoordrd_nt
  2979.  
  2980.         *--- TechRec 1054619 09-Jun-2011 MANI. ---
  2981.         lczzedatawhse       = This.cZzedatawhse
  2982.         *=== TechRec 1054619 09-Jun-2011 MANI. ===
  2983.        
  2984.         Local lcValues, lcExcludeFlds, lcCalFlds, lcCalFldsValues
  2985.         lnOldSelect         = Select()
  2986.         llRetVal            = .T.
  2987.  
  2988.         lcFlds              = ""
  2989.         lcValues            = ""
  2990.         lcExcludeFlds       = ""
  2991.         lcCalFlds           = ""
  2992.         lcCalFldsValues     = ""
  2993.        
  2994.         *--- Order Header insert
  2995.         llRetVal                = llRetVal And This.oCopyUtil.GetFldsValues(@lcFlds, @lcValues, "zzoordrh", lcZzoordrh, ;
  2996.                                                             lcExcludeFlds,lcCalFlds,lcCalFldsValues,.f.,"h.")
  2997.        
  2998.         lcInsertSQL_zzoordrh    = " INSERT INTO zzoordrh (" + lcFlds + " ) SELECT " + lcValues + " FROM " + lcZzoordrh + " h "
  2999.  
  3000.         *--- Order Detail insert
  3001.         lcFlds                  = ""
  3002.         lcValues                = ""       
  3003.         llRetVal                = llRetVal And This.oCopyUtil.GetFldsValues(@lcFlds, @lcValues, "zzoordrd", lcZzoordrd, ;
  3004.                                                             lcExcludeFlds,lcCalFlds,lcCalFldsValues,.f.,"d.")
  3005.        
  3006.         lcInsertSQL_zzoordrd    = " INSERT INTO zzoordrd (" + lcFlds + " ) SELECT " + lcValues + " FROM " + lcZzoordrd + " d "
  3007.  
  3008.  
  3009.         *--- Order sp insert
  3010.         lcFlds                  = ""
  3011.         lcValues                = ""       
  3012.         llRetVal                = llRetVal And This.oCopyUtil.GetFldsValues(@lcFlds, @lcValues, "zzoordsp", lcZzoordsp, ;
  3013.                                                             lcExcludeFlds,lcCalFlds,lcCalFldsValues,.f.,"sp.")
  3014.  
  3015.         lcInsertSQL_zzoordsp    = " INSERT INTO zzoordsp (" + lcFlds + " ) SELECT " + lcValues + " FROM " + lcZzoordsp + " sp "
  3016.  
  3017.         *--- Order address insert
  3018.         lcFlds                  = ""
  3019.         lcValues                = ""       
  3020.         llRetVal                = llRetVal And This.oCopyUtil.GetFldsValues(@lcFlds, @lcValues, "zzoordad", lcZzoordad, ;
  3021.                                                             lcExcludeFlds,lcCalFlds,lcCalFldsValues,.f.,"a")
  3022.  
  3023.         lcInsertSQL_zzoordad    = " INSERT INTO zzoordad (" + lcFlds + " ) SELECT " + lcValues + " FROM " + lcZzoordad + " a "
  3024.        
  3025.         *--- one time address insert
  3026.         lcFlds                  = ""
  3027.         lcValues                = ""       
  3028.         llRetVal                = llRetVal And This.oCopyUtil.GetFldsValues(@lcFlds, @lcValues, "zzootadr", lcZzootadr, ;
  3029.                                                             lcExcludeFlds,lcCalFlds,lcCalFldsValues,.f.,"a")
  3030.  
  3031.         lcInsertSQL_zzootadr    = " INSERT INTO zzootadr (" + lcFlds + " ) SELECT " + lcValues + " FROM " + lcZzootadr + " a "
  3032.  
  3033.  
  3034.         *--- Order comment insert
  3035.         lcFlds                  = ""
  3036.         lcValues                = ""       
  3037.         llRetVal                = llRetVal And This.oCopyUtil.GetFldsValues(@lcFlds, @lcValues, "zzoordcd", lcZzoordcd, ;
  3038.                                                             lcExcludeFlds,lcCalFlds,lcCalFldsValues,.f.,"c")
  3039.  
  3040.         lcInsertSQL_zzoordcd    = " INSERT INTO zzoordcd (" + lcFlds + " ) SELECT " + lcValues + " FROM " + lcZzoordcd + " c "
  3041.  
  3042.        
  3043.        
  3044.  
  3045.         *--- Order Header Notes insert
  3046.         lcFlds                  = ""
  3047.         lcValues                = ""       
  3048.         llRetVal                = llRetVal And This.oCopyUtil.GetFldsValues(@lcFlds, @lcValues, "sysnotes", lcZzoordrh_nt, ;
  3049.                                                             lcExcludeFlds,lcCalFlds,lcCalFldsValues,.f.,"h.")
  3050.        
  3051.         lcInsertSQL_zzoordrh_nt = " INSERT INTO sysnotes (" + lcFlds + " ) SELECT " + lcValues + " FROM " + lcZzoordrh_nt + " h "
  3052.  
  3053.         *--- Order Detail Notes Insert
  3054.  
  3055.         lcFlds                  = ""
  3056.         lcValues                = ""       
  3057.         llRetVal                = llRetVal And This.oCopyUtil.GetFldsValues(@lcFlds, @lcValues, "sysnotes", lcZzoordrd_nt, ;
  3058.                                                             lcExcludeFlds,lcCalFlds,lcCalFldsValues,.f.,"d.")
  3059.        
  3060.         lcInsertSQL_zzoordrd_nt = " INSERT INTO sysnotes (" + lcFlds + " ) SELECT " + lcValues + " FROM " + lcZzoordrd_nt + " d "
  3061.  
  3062.         *--- TechRec 1054619 09-Jun-2011 MANI. ---     
  3063.         *--- Data warehouse insert
  3064.         lcFlds                  = ""
  3065.         lcValues                = ""       
  3066.         llRetVal                = llRetVal And This.oCopyUtil.GetFldsValues(@lcFlds, @lcValues, "zzedatawhse", lcZzedatawhse, ;
  3067.                                                             lcExcludeFlds,lcCalFlds,lcCalFldsValues,.f.,"wh")
  3068.  
  3069.         lcInsertSQL_zzedatawhse     = " INSERT INTO zzedatawhse (" + lcFlds + " ) SELECT " + lcValues + " FROM " + lczzedatawhse+ " wh "
  3070.         *=== TechRec 1054619 09-Jun-2011 MANI. ===
  3071.        
  3072.         pcInsertSQL_zzoordrh    = lcInsertSQL_zzoordrh
  3073.         pcInsertSQL_zzoordrd    = lcInsertSQL_zzoordrd
  3074.         pcInsertSQL_zzoordSP    = lcInsertSQL_zzoordSP
  3075.         pcInsertSQL_zzoordad    = lcInsertSQL_zzoordad
  3076.         pcInsertSQL_zzootadr    = lcInsertSQL_zzootadr
  3077.         pcInsertSQL_zzoordcd    = lcInsertSQL_zzoordcd
  3078.         pcInsertSQL_zzoordrh_nt = lcInsertSQL_zzoordrh_nt
  3079.         pcInsertSQL_zzoordrd_nt = lcInsertSQL_zzoordrd_nt
  3080.  
  3081.         *--- TechRec 1054619 09-Jun-2011 MANI. ---
  3082.         pcInsertSQL_zzedatawhse = lcInsertSQL_zzedatawhse
  3083.         *=== TechRec 1054619 09-Jun-2011 MANI. ===
  3084.        
  3085.         Select(lnOldSelect)
  3086.         Return llRetVal
  3087.        
  3088.  
  3089.         RETURN llRetVal
  3090.        
  3091.     ENDPROC
  3092.    
  3093.     *--------------------------------------------------------------------------                    
  3094.    
  3095.     PROCEDURE GetTransHistoryInsertSQL
  3096.         LPARAMETERS pcInsertSQL_Zzeipohh, pcInsertSQL_Zzeipohd, pcInsertSQL_Zzeipoha, pcInsertSQL_Zzeipohc,;
  3097.              pcInsertSQL_Zzeipohh_nt, pcInsertSQL_Zzeipohd_nt,pcInsertSQL_Zzeipohs
  3098.        
  3099.         LOCAL llRetVal
  3100.         LOCAL lcInsertSQL_Zzeipohh, lcInsertSQL_Zzeipohd, lcInsertSQL_Zzeipoha, lcInsertSQL_Zzeipohc
  3101.         LOCAL lcInsertSQL_Zzeipohh_nt, lcInsertSQL_Zzeipohd_nt,pcInsert_Zzeipohs,lcZZeipohs
  3102.         LOCAL lcZzeipohh, lcZzeipohd, lcZzeipoha, lcZzeipohc, lcZzeipohh_nt, lcZzeipohd_nt
  3103.         STORE " " TO lcInsertSQL_Zzeipohh, lcInsertSQL_Zzeipohd, lcInsertSQL_Zzeipoha, lcInsertSQL_Zzeipohc
  3104.         STORE " " TO lcInsertSQL_Zzeipohh_nt, lcInsertSQL_Zzeipohd_nt,lcInsert_Zzeipohs
  3105.        
  3106.         llRetVal        = .T.
  3107.         lcZzeipohh      = this.cZzeipohh
  3108.         lcZzeipohd      = this.cZzeipohd
  3109.         lcZzeipoha      = this.cZzeipoha
  3110.         lcZzeipohc      = this.cZzeipohc
  3111.         lcZzeipohh_nt   = this.cZzeipohh_nt
  3112.         lcZzeipohd_nt   = this.cZzeipohd_nt
  3113.         lcZZeipohs      = this.cZzeipohs
  3114.        
  3115.         LOCAL lcFlds, lcValues,lcExcludeFlds,lcCalFlds,lcCalFldsValues
  3116.  
  3117.         lcFlds                  =   ""
  3118.         lcValues                =   ""
  3119.         lcExcludeFlds           =   ""
  3120.         lcCalFlds               =   ""
  3121.         lcCalFldsValues         =   ""
  3122.        
  3123.        
  3124.         *--- insert new trans header in history
  3125.         llRetVal                =   llRetVal And this.oCopyUtil.GetFldsValues(@lcFlds, @lcValues, "zzeipohh", lcZzeipohh , ;
  3126.                                     lcExcludeFlds,lcCalFlds,lcCalFldsValues,.f.,'h')
  3127.        
  3128.         lcInsertSQL_Zzeipohh    =   " INSERT INTO zzeipohh ( " + lcFlds + " ) " + ;
  3129.                                     " SELECT " + lcValues + " FROM " + lcZzeipohh + " h "
  3130.  
  3131.         *--- insert new trans detail in history
  3132.         lcFlds                  =   ""
  3133.         lcValues                =   ""
  3134.         llRetVal                =   llRetVal And this.oCopyUtil.GetFldsValues(@lcFlds, @lcValues, "zzeipohd", lcZzeipohd , ;
  3135.                                     lcExcludeFlds,lcCalFlds,lcCalFldsValues,.f.,'d')
  3136.        
  3137.         lcInsertSQL_Zzeipohd    =   " INSERT INTO zzeipohd ( " + lcFlds + " ) " + ;
  3138.                                     " SELECT " + lcValues + " FROM " + lcZzeipohd + " d "
  3139.        
  3140.        
  3141.         *--- insert new trans detail in history
  3142.         lcFlds                  =   ""
  3143.         lcValues                =   ""
  3144.         llRetVal                =   llRetVal And this.oCopyUtil.GetFldsValues(@lcFlds, @lcValues, "zzeipohs", lcZzeipohs , ;
  3145.                                     lcExcludeFlds,lcCalFlds,lcCalFldsValues,.f.,'d')
  3146.                                    
  3147.         lcInsertSQL_Zzeipohs    =   " INSERT INTO zzeipohs ( " + lcFlds + " ) " + ;
  3148.                                     " SELECT " + lcValues + " FROM " + lcZzeipohs + " d "      
  3149.        
  3150.  
  3151.         *--- insert new trans address in history
  3152.         lcFlds                  =   ""
  3153.         lcValues                =   ""
  3154.         llRetVal                =   llRetVal And this.oCopyUtil.GetFldsValues(@lcFlds, @lcValues, "zzeipoha", lcZzeipoha , ;
  3155.                                     lcExcludeFlds,lcCalFlds,lcCalFldsValues,.f.,'a')
  3156.        
  3157.         lcInsertSQL_Zzeipoha    =   " INSERT INTO zzeipoha ( " + lcFlds + " ) " + ;
  3158.                                     " SELECT " + lcValues + " FROM " + lcZzeipoha + " a "
  3159.        
  3160.  
  3161.         *--- insert new trans comment in history
  3162.         lcFlds                  =   ""
  3163.         lcValues                =   ""
  3164.         llRetVal                =   llRetVal And this.oCopyUtil.GetFldsValues(@lcFlds, @lcValues, "zzeipohc", lcZzeipohc , ;
  3165.                                     lcExcludeFlds,lcCalFlds,lcCalFldsValues,.f.,'c')
  3166.        
  3167.         lcInsertSQL_Zzeipohc    =   " INSERT INTO zzeipohc ( " + lcFlds + " ) " + ;
  3168.                                     " SELECT " + lcValues + " FROM " + lcZzeipohc + " c "
  3169.        
  3170.  
  3171.         *--- insert new trans header notes in history
  3172.         lcFlds                  =   ""
  3173.         lcValues                =   ""
  3174.         llRetVal                =   llRetVal And this.oCopyUtil.GetFldsValues(@lcFlds, @lcValues, "sysnotes", lcZzeipohh_nt , ;
  3175.                                     lcExcludeFlds,lcCalFlds,lcCalFldsValues,.f.,'h')
  3176.        
  3177.         lcInsertSQL_Zzeipohh_nt =   " INSERT INTO sysnotes ( " + lcFlds + " ) " + ;
  3178.                                     " SELECT " + lcValues + " FROM " + lcZzeipohh_nt + " h "
  3179.        
  3180.         lcFlds                  =   ""
  3181.         lcValues                =   ""
  3182.         llRetVal                =   llRetVal And this.oCopyUtil.GetFldsValues(@lcFlds, @lcValues, "sysnotes", lcZzeipohd_nt , ;
  3183.                                     lcExcludeFlds,lcCalFlds,lcCalFldsValues,.f.,'h')
  3184.        
  3185.         lcInsertSQL_Zzeipohd_nt =   " INSERT INTO sysnotes ( " + lcFlds + " ) " + ;
  3186.                                     " SELECT " + lcValues + " FROM " + lcZzeipohd_nt + " h "
  3187.  
  3188.         pcInsertSQL_Zzeipohh    = lcInsertSQL_Zzeipohh
  3189.         pcInsertSQL_Zzeipohd    = lcInsertSQL_Zzeipohd
  3190.         pcInsertSQL_Zzeipoha    = lcInsertSQL_Zzeipoha
  3191.         pcInsertSQL_Zzeipohc    = lcInsertSQL_Zzeipohc
  3192.         pcInsertSQL_Zzeipohh_nt = lcInsertSQL_Zzeipohh_nt
  3193.         pcInsertSQL_Zzeipohd_nt = lcInsertSQL_Zzeipohd_nt
  3194.         pcInsertSQL_Zzeipohs    = lcInsertSQL_Zzeipohs
  3195.        
  3196.         RETURN llRetVal
  3197.     ENDPROC
  3198.    
  3199.     *--------------------------------------------------------------------------                        
  3200.  
  3201.     PROCEDURE GetDeleteSQL
  3202.         LPARAMETERS pcDeleteSQL, pcSrc, pcDes, plNotExists
  3203.        
  3204.         LOCAL llRetVal
  3205.         llRetVal    = .T.
  3206.        
  3207.         lcFilter        =   IIF(EMPTY(this.cFilterCriteria)," Where ", this.cFilterCriteria + " and ")
  3208.         lcExistsOrNot   =   IIF(plNotExists, " not ", "") + " exists "
  3209.         pcDeleteSQL     =   "DELETE h from " + pcDes + " h " + lcFilter + lcExistsOrNot + " (select h.pkey from " + pcSrc + ;
  3210.                                         " th where th.pkey = h.pkey ) "
  3211.    
  3212.         RETURN llRetVal
  3213.        
  3214.     ENDPROC
  3215.    
  3216.     *--------------------------------------------------------------------------                        
  3217.    
  3218.     PROCEDURE SqlExecInTran
  3219.         LPARAMETERS pcSql, plRetVal, pnTranErrCount, pcTranFailedMsg, pcErrMsg
  3220.        
  3221.         *--- TR 1060326 25-Feb-13 SK Proceeding only when plRetVal is true (To avoid updating wrong error messages)
  3222.         IF plRetVal
  3223.            
  3224. *!*             plRetVal    = plRetVal And v_SQLExec(pcSql)
  3225.             plRetVal    = v_SQLExec(pcSql)
  3226.            
  3227.             If pnTranErrCount = 0  And Not plRetVal
  3228.                 pcTranFailedMsg = pcErrMsg
  3229.                 lnTranErrCount  = 1
  3230.             Endif
  3231.         ENDIF
  3232.         *=== TR 1060326 25-Feb-13 SK   
  3233.     ENDPROC
  3234.  
  3235.     *--------------------------------------------------------------------------                        
  3236.    
  3237.     PROCEDURE SetPkey
  3238.         LPARAMETERS pcTbl, pcPkeySourceTbl
  3239.  
  3240.         Local llRetVal, lnOldSelect,lcUnique, lcSQL, lnMaxPkey, lcPkeyIdentity
  3241.         llRetVal    = .T.
  3242.         lnOldSelect = Select()
  3243.         lnMaxPkey   = 0
  3244.  
  3245.         lcSQL       = "SELECT count(*) newCount from " + pcTbl
  3246.         llRetVal    = llRetVal AND v_SqlExec(lcSQL,"TblRecord")
  3247.  
  3248.         IF llRetVal AND TblRecord.newCount > 0
  3249.             lcPkeyIdentity  = "pkey_I" + SYS(2015)
  3250.             lnMaxPkey       = v_NextPKey(pcPkeySourceTbl,TblRecord.newCount)
  3251.             llRetVal        = llRetVal and this.oCopyUtil.KeyFldUpdate(pcTbl, "pkey", lnMaxPkey, TblRecord.newCount, ;
  3252.                                             lcPkeyIdentity, "")
  3253.         ENDIF
  3254.        
  3255.         IF USED("TblRecord")
  3256.             USE IN select("TblRecord")
  3257.         ENDIF
  3258.         Select(lnOldSelect)
  3259.         Return llRetVal
  3260.        
  3261.     ENDPROC
  3262.  
  3263.     *--------------------------------------------------------------------------                        
  3264.    
  3265.     PROCEDURE UpdateProcessStatus
  3266.    
  3267.         LOCAL llRetVal, lnOldSelect, lcSql, lcZzoordrh, lcZzoordrd
  3268.         llRetVal        = .t.
  3269.         lnOldSelect     = SELECT()
  3270.        
  3271.         *--- number of order header created
  3272.         lcZzoordrh      = "tcZzoordrh" + SYS(2015)
  3273.         lcSql           = "select COUNT(*) as RecordCount from " + this.cZzoordrh
  3274.         llRetVal        = llRetVal  and v_sqlexec(lcSql,lcZzoordrh)
  3275.         IF llRetVal AND USED(lcZzoordrh)
  3276.             this.oHostProcess.nOrderHeader  = EVALUATE(lcZzoordrh + ".RecordCount")
  3277.             USE IN SELECT(lcZzoordrh)
  3278.         ENDIF
  3279.  
  3280.         *--- number of order detail created
  3281.         lcZzoordrd      = "tcZzoordrd" + SYS(2015)
  3282.         lcSql           = "select COUNT(*) as RecordCount from " + this.cZzoordrd
  3283.         llRetVal        = llRetVal  and v_sqlexec(lcSql,lcZzoordrd)
  3284.         IF llRetVal AND USED(lcZzoordrd)
  3285.             this.oHostProcess.nOrderDetail  = EVALUATE(lcZzoordrd + ".RecordCount")
  3286.             USE IN SELECT(lcZzoordrd)
  3287.         ENDIF
  3288.  
  3289.         Select(lnOldSelect)
  3290.         Return llRetVal
  3291.        
  3292.     ENDPROC
  3293.  
  3294.     *--------------------------------------------------------------------------                        
  3295.    
  3296. *!*     PROCEDURE GetInstance_AfterUpdateBeforeCommit
  3297. *!*         LPARAMETERS poAfterUpdateBeforeCommit
  3298. *!*        
  3299. *!*         LOCAL llRetVal
  3300. *!*         llRetVal                    = .t.
  3301. *!*         poAfterUpdateBeforeCommit   = NEWOBJECT("clsIpoPr_AfterUpdateBeforeCommit","clsIpoPr_AfterUpdateBeforeCommit.fxp",null,this.oHostPROCESS,this)
  3302. *!*         llRetVal                    = (VARTYPE(poAfterUpdateBeforeCommit) == "O")
  3303. *!*         RETURN llRetVal
  3304. *!*     ENDPROC
  3305.    
  3306.     *--------------------------------------------------------------------------
  3307.    
  3308.     PROCEDURE SetLineSeqInTransComment
  3309.        
  3310.         LOCAL llRetVal, lcZzeipotc
  3311.         llRetVal    = .t.
  3312.         lcZzeipotc  = "#zzeipotc" + SYS(2015)
  3313.         *--- move data with line_seq = 0 to temp table
  3314.         lcSql       = "select pkey, fkey, line_num into " + lcZzeipotc + " from " + this.cZzeipotc + " where line_num = 0 "
  3315.         llRetVal    = llRetVal and v_sqlexec(lcSql)
  3316.         THIS.RegisterTable(lcZzeipotc)     
  3317.        
  3318.         *--- set line seq in temp table
  3319.         llRetVal    = llRetVal and this.oCopyUtil.SetLineSeq(lcZzeipotc, "line_num", "fkey", "fkey,pkey", "d")
  3320.         *--- update line seq in work table
  3321.         lcSql       = "Update c Set line_num = d.line_num from " + this.cZzeipotc + " c inner join " + lcZzeipotc + ;
  3322.                         " d on d.pkey = c.pkey "
  3323.         llRetVal    = llRetVal and v_sqlexec(lcSql)
  3324.        
  3325.        
  3326.         RETURN llRetVal
  3327.        
  3328.     ENDPROC
  3329.        
  3330.     *--------------------------------------------------------------------------
  3331.        
  3332.     PROCEDURE CheckFldAndAssign
  3333.         LPARAMETERS pcFlds, pcFld, pcValue, pcPrefixComma
  3334.         LOCAL lcAssign, lcFlds, lcFld, lcDeli
  3335.         lcAssign    = ""
  3336.         lcDeli      = ","
  3337.         lcFlds      = lcDeli + pcFlds + lcDeli
  3338.         lcFld       = lcDeli + UPPER(ALLTRIM(pcFld)) + lcDeli
  3339.         IF at(lcFld,lcFlds) > 0
  3340.             lcAssign    = pcFld + " = " + pcValue
  3341.         ENDIF
  3342.        
  3343.         IF !EMPTY(pcPrefixComma) AND !EMPTY(lcAssign)
  3344.             lcAssign = pcPrefixComma + lcAssign
  3345.         ENDIF
  3346.        
  3347.         RETURN lcAssign
  3348.     ENDPROC
  3349.    
  3350.     *--------------------------------------------------------------------------
  3351.        
  3352.     PROCEDURE ClearShippingFields
  3353.         LPARAMETERS  pcOrderTbl
  3354.        
  3355.         LOCAL llRetVal, lcSql,lcAssign, lnOldSelect
  3356.         LOCAL lcDefaultDate, lcCurDate, lcOrderCur, lcDeli
  3357.  
  3358.         llRetVal        = .t.
  3359.         lnOldSelect     = SELECT()         
  3360.         lcDefaultDate   = SQLFormatTS({//})
  3361.         lcCurDate       = SQLFormatTS(TTOD(ServerDatetime()))      
  3362.         lcOrderCur      = "tcOrderCur" + SYS(2015)
  3363.         lcSql           = " select * from " + pcOrderTbl + " where 1=2"
  3364.         llRetVal        = llRetVal and v_sqlexec(lcSql, lcOrderCur)
  3365.        
  3366.         DIMENSION laFlds[1]
  3367.        
  3368.         AFIELDS(laFlds,lcOrderCur)
  3369.         lcDeli          = ","
  3370.         lcFlds          = this.GetFldList(@laFlds,lcDeli)
  3371.         lcAssign        = ""
  3372.        
  3373.         lcAssign        = lcAssign + ", frgt_amt = case when Over_Frgt <> 'P' then 0 else h.frgt_amt end "     
  3374.         lcAssign        = lcAssign + ", misc_amt = case when Over_Frgt <> 'P' then 0 else h.misc_amt end "     
  3375.         lcAssign        = lcAssign + ", tax_amt = case when Tax_Empt <> 'P' then 0 else h.tax_amt end "        
  3376.         *--- TechRec 1066657 28-Jan-2013 jjanand --- Removed , disc_amt = 0
  3377.         lcAssign        = lcAssign + ", insu_amt = 0 "
  3378.         *=== TechRec 1066657 28-Jan-2013 jjanand ===
  3379.         lcAssign        = lcAssign + ", mani_num = 0, bill_num = '', pro_num = '', consol_num = 0, ship_date = " + lcDefaultDate
  3380.  
  3381.         lcAssign        = lcAssign + ", RRC  = '', Load_Id  = '', stop_seq = 0, Sent_753  = '',Rcvd_754  = ''"
  3382.         lcAssign        = lcAssign + ", first_pack_user = ''"
  3383.        
  3384.         lcAssign        = lcAssign + this.CheckFldAndAssign(lcFlds, "proc_940","''",",")
  3385.         lcAssign        = lcAssign + this.CheckFldAndAssign(lcFlds, "proc_810","''",",")
  3386.         lcAssign        = lcAssign + this.CheckFldAndAssign(lcFlds, "proc_810f","''",",")
  3387.         lcAssign        = lcAssign + this.CheckFldAndAssign(lcFlds, "proc_870","''",",")
  3388.         lcAssign        = lcAssign + this.CheckFldAndAssign(lcFlds, "opks_proc","''",",")
  3389.         lcAssign        = lcAssign + this.CheckFldAndAssign(lcFlds, "pub_ship_date",lcDefaultDate,",")
  3390.         lcAssign        = lcAssign + this.CheckFldAndAssign(lcFlds, "act_ship_date",lcDefaultDate,",")
  3391.        
  3392.         *--- TR 1043347
  3393.         * Do not clear these fields.
  3394.         *lcAssign       = lcAssign + this.CheckFldAndAssign(lcFlds, "ok_to_ship",lcDefaultDate,",")
  3395.  
  3396.         *IF !vl_parmr("SOPRTNSHPR")
  3397.         *   lcAssign    = lcAssign + ",shipper = ''"
  3398.         *ENDIF
  3399.         *=== TR 1043347        
  3400.  
  3401.         lcAssign        = lcAssign + ",AR_PERIOD = '', PRD_YEAR = 0, PRD_MONTH = 0"
  3402.         lcAssign        = lcAssign + ",wave_num = 0, swc_num = 0"
  3403.         lcAssign        = lcAssign + ",Proc_856 = 'N'"
  3404.        
  3405.         *IF This.oHostProcess.lCCVerisignApproval   ??????????
  3406.             lcAssign        = lcAssign + this.CheckFldAndAssign(lcFlds, "appv_num","''",",")
  3407.         *ENDIF     
  3408.  
  3409.         lcSql           = "Update h Set carton = 0, weight = 0, ord_volume = 0 " + lcAssign + ;
  3410.                             " from " + pcOrderTbl + " h "
  3411.         llRetVal        = llRetVal and v_sqlexec(lcSql)
  3412.        
  3413.         SELECT(lnOldSelect)
  3414.         RETURN llRetVal
  3415.     ENDPROC
  3416.  
  3417.     *-----------------------------------------------------------------------------
  3418.    
  3419. *!*     PROCEDURE GetInstance_BulkNibbler
  3420. *!*         LPARAMETERS poBulkNibbler
  3421. *!*         LOCAL llRetVal
  3422. *!*         llRetVal        = .t.
  3423. *!*         poBulkNibbler   = NEWOBJECT("clsIpoPr_BulkNibbler","clsIpopr_BulkNibbler.fxp",null,this.oHostProcess,this)
  3424. *!*         llRetVal        = VARTYPE(poBulkNibbler) == "O"
  3425. *!*         RETURN llRetVal
  3426. *!*     ENDPROC
  3427.    
  3428.     *-----------------------------------------------------------------------------
  3429.  
  3430.     PROCEDURE GetFldList
  3431.         LPARAMETERS paFlds, pcDeli
  3432.         LOCAL lcFlds, lnFld, lnMaxFld, lcDeli
  3433.        
  3434.         lcFlds      = ""
  3435.         lcDeli      = IIF(EMPTY(pcDeli),",",pcDeli)
  3436.         lnMaxFld    = ALEN(paFlds,1)
  3437.         FOR lnFld = 1 TO lnMaxFld
  3438.             lcFlds  = lcFlds + UPPER(ALLTRIM(paFlds[lnFld,1])) + lcDeli
  3439.         ENDFOR
  3440.         IF !EMPTY(lcFlds)
  3441.             lcFlds  = LEFT(lcFlds,LEN(lcFlds) - LEN(lcDeli))
  3442.         ENDIF
  3443.        
  3444.         RETURN lcFlds
  3445.     ENDPROC
  3446.    
  3447.     *--------------------------------------------------------------------------
  3448.  
  3449.     PROCEDURE SetDefaultDate_OrderDetail
  3450.  
  3451.         LOCAL llRetVal, lcSql, lcDefaultDate, lcCurDate
  3452.         llRetVal        = .t.
  3453.         lcDefaultDate   = SQLFormatTS({//})
  3454.         lcCurDate       = SQLFormatTS(TTOD(ServerDatetime()))      
  3455.  
  3456.         *--- TR 1071867 06/24/13 ATHIRUNAVU Set default date for isr_Start_date
  3457.         *--- TR 1077221 03/18/14 ATHIRUNAVU Removed pri_Date update.
  3458.         *lcSql          = "Update d Set CNCL_DATE = " + lcDefaultDate + ;
  3459.                             ", FEXPN_DATE = " + lcDefaultDate + ;
  3460.                             ", PRI_DATE = " + lcCurDate + ;
  3461.                             ", ISR_START_DATE = "  + lcDefaultDate + ;
  3462.                             " FROM " + this.cZzoordrd + " d "
  3463.         lcSql           = "Update d Set CNCL_DATE = " + lcDefaultDate + ;
  3464.                             ", FEXPN_DATE = " + lcDefaultDate + ;
  3465.                             ", ISR_START_DATE = "  + lcDefaultDate + ;
  3466.                             " FROM " + this.cZzoordrd + " d "
  3467.         llRetVal        = llRetVal and v_sqlexec(lcSql)
  3468.        
  3469.         RETURN llRetVal
  3470.    
  3471.     ENDPROC
  3472.    
  3473.     *--------------------------------------------------------------------------        
  3474.    
  3475.     PROCEDURE PrepareOrderExtender
  3476.        
  3477.         LOCAL llRetVal,lcSql
  3478.         llRetVal    = .t.
  3479.    
  3480.         *--- Update three fields - hdrfkey, dtlfkey, GrpFKey in zzzudfdt table.
  3481.         *--- Now, cZzeipotd has final order pkey in zzoordrd_pkey field
  3482.         lcSql       = "Update dex Set hdrfkey = d.fkey, DtlFkey = d.pkey, GrpFKey = dex.Grp_O_Key " + ;
  3483.                         " from " + this.cZzoordrdExt + " dex inner join " + this.cZzoordrd + " d on d.zzeipotd_pkey = dex.oldDtlfkey "
  3484.         llRetVal        = llRetVal and v_sqlexec(lcSql)
  3485.         this.LogEntry("Prepare Order extender " + IIF(llRetVal,"completed.","failed."))
  3486.        
  3487.         RETURN llRetVal
  3488.     ENDPROC
  3489.    
  3490.     *--------------------------------------------------------------------------        
  3491.     PROCEDURE PrepareOrderDiscount
  3492.        
  3493.         LOCAL llRetVal, lcSql, lcDefaultdt
  3494.         *,CUSTOMER,ORD_NUM,PICK_NUM,INV_NUM,ORD_DATE,PO_NUM,PO_DATE,
  3495.         llRetVal        = .t.
  3496.         *, po_date = h.po_date  - h.po_Date not exists
  3497.         lcDefaultdt     = sqlformatTS({})
  3498.        
  3499.         lcSql           = " Update ds Set customer = h.customer, pick_num = 0, inv_num = 0, ord_date = h.ord_date, po_num = h.po_num" + ;
  3500.                             ", po_date = " + lcDefaultdt + ;
  3501.                             " from " + this.cZzoordsac + " ds inner join " + this.cZzoordrh + " h on h.ord_num = ds.ord_num "
  3502.         llRetVal        = llRetVal and v_sqlexec(lcSql)
  3503.        
  3504.         lcSql           = " Update ds Set LINE_SEQ = D.line_seq from " + this.cZzoordsac + " ds inner join " + this.cZzoordrd + " d on d.ord_num = ds.ord_num " + ;
  3505.                             " and d.pkey = ds.dfkey "
  3506.         llRetVal        = llRetVal and v_sqlexec(lcSql)
  3507.        
  3508.         this.LogEntry("Prepare Order Discount " + IIF(llRetVal,"completed.","failed."))
  3509.         RETURN llRetVal
  3510.     ENDPROC
  3511.    
  3512.     *--------------------------------------------------------------------------            
  3513.  
  3514.     PROCEDURE PrepareOrderDiscount
  3515.        
  3516.         LOCAL llRetVal, lcSql, lcDefaultdt
  3517.         *,CUSTOMER,ORD_NUM,PICK_NUM,INV_NUM,ORD_DATE,PO_NUM,PO_DATE,
  3518.         llRetVal        = .t.
  3519.         *, po_date = h.po_date  - h.po_Date not exists
  3520.         lcDefaultdt     = sqlformatTS({})
  3521.        
  3522.         lcSql           = " Update ds Set customer = h.customer, pick_num = 0, inv_num = 0, ord_date = h.ord_date, po_num = h.po_num" + ;
  3523.                             ", po_date = " + lcDefaultdt + ;
  3524.                             " from " + this.cZzoordsac + " ds inner join " + this.cZzoordrh + " h on h.ord_num = ds.ord_num "
  3525.         llRetVal        = llRetVal and v_sqlexec(lcSql)
  3526.        
  3527.         lcSql           = " Update ds Set LINE_SEQ = D.line_seq from " + this.cZzoordsac + " ds inner join " + this.cZzoordrd + " d on d.ord_num = ds.ord_num " + ;
  3528.                             " and d.pkey = ds.dfkey "
  3529.         llRetVal        = llRetVal and v_sqlexec(lcSql)
  3530.        
  3531.         RETURN llRetVal
  3532.     ENDPROC
  3533.    
  3534.     *--------------------------------------------------------------------------            
  3535.  
  3536.     PROCEDURE SetUniqueSet_id(pcZzeipoTD, pcOrigSet_id)
  3537.    
  3538.         LOCAL llRetVal, lcSql, lcOrigSet_id, lcUnique, lcPrefix
  3539.         llRetVal        = .t.
  3540.         lcUnique        = SYS(2015)
  3541.         lcOrigSet_id    = "#Uk_set_id" + lcUnique
  3542.         lcPrefix        = RIGHT(lcUnique,5) + "@"
  3543. *                           ", case when d.set_id > '' then d.set_id else '" + lcPrefix + " + left(ltrim(str(d.line_seq)),3) +' end set_id_new " + ;       
  3544.  
  3545.         *--- create unique set_id for blank set_id
  3546.         lcSql           = "select d.pkey, d.set_id" + ;
  3547.                             ",'" + lcPrefix + "' + left(ltrim(str(d.line_seq)),4) set_id_new " + ;
  3548.                             " into " + lcOrigSet_id + " from " + pcZzeipoTD + " d " + ;
  3549.                             " where d.set_id = '' and exists (select ex.pkey from " + this.cZzeipoTDExt + " ex where ex.oldDtlFkey = d.pkey and ex.oldHdrFkey = d.fkey)"
  3550.         llRetval        = llRetval and v_sqlexec(lcSql)
  3551.        
  3552.         .RegisterTable(lcOrigSet_id)       
  3553.        
  3554.         lcSql           = "Update d Set set_id = u.set_id_new from " + pcZzeipoTD + " d inner join " + lcOrigSet_id + " u on u.pkey = d.pkey "
  3555.         llRetval        = llRetval and v_sqlexec(lcSql)
  3556.         pcOrigSet_id    = lcOrigSet_id
  3557.        
  3558.         RETURN llRetVal
  3559.     ENDPROC
  3560.    
  3561.     *--------------------------------------------------------------------------        
  3562.    
  3563.     PROCEDURE ResetOriginalSet_id(pcZzeipotd_OrigSet_id,pcZzeipoTD)
  3564.        
  3565.         LOCAL llRetVal, lcSql
  3566.        
  3567.         llRetVal    = .t.
  3568.         lcSql       = "update d Set set_id = u.set_id from " + pcZzeipoTD + " d inner join " + pcZzeipotd_OrigSet_id + ;
  3569.                         " u on u.pkey = d.pkey "
  3570.         llRetval    = llRetval and v_sqlexec(lcSql)
  3571.        
  3572.         RETURN llRetVal
  3573.     ENDPROC
  3574.            
  3575.     *--------------------------------------------------------------------------                    
  3576.    
  3577.     PROCEDURE GetUpdateSQL_OrderExtender(pcUpdateSql_zzzudfdt)
  3578.        
  3579.         LOCAL llRetVal, lcSql
  3580.         llRetVal    = .t.
  3581.         llRetVal    = llRetVal and this.oCopyUtil.GetUpdateSql(@pcUpdateSql_zzzudfdt,this.cZzoordrdExt,"zzzudfdt")
  3582.         RETURN llRetVal
  3583.     ENDPROC
  3584.            
  3585.     *--------------------------------------------------------------------------                    
  3586.    
  3587. *!*     PROCEDURE GetInstance_EDIBulkMatching(poEdiBulkMatch)
  3588. *!*        
  3589. *!*         LOCAL llRetVal, lcSql
  3590. *!*         llRetVal    = .t.
  3591. *!*         IF VARTYPE(this.oHostProcess.oPostValidation) == "O" AND VARTYPE(this.oHostProcess.oPostValidation.oEdiBulkMatching) == "O"
  3592. *!*             poEdiBulkMatch  = this.oHostProcess.oPostValidation.oEdiBulkMatching
  3593. *!*         ELSE
  3594. *!*             poEdiBulkMatch  = NEWOBJECT("clsipopr_EdiBulkMatching","clsipopr_EdiBulkMatching.fxp","",null,this.oHostProcess)
  3595. *!*         ENDIF
  3596. *!*         llRetval    = llRetVal and (VARTYPE(poEdiBulkMatch) == "O")
  3597. *!*         RETURN llRetVal
  3598. *!*     ENDPROC
  3599.            
  3600.     *--------------------------------------------------------------------------
  3601.  
  3602.     PROCEDURE SetSac_IDandRate
  3603.         LPARAMETERS pcZzeipoTD, pcZzeiPOtsac
  3604.  
  3605.         LOCAL llRetVal, lcSql
  3606.        
  3607.         llRetVal    = .t.
  3608.         lcSql       = "alter table " + pcZzeipoTD + " add sac_id varchar(250) not null default('')"
  3609.         llRetval    = llRetVal and v_sqlexec(lcSql)
  3610.         lcSql       = "alter table " + pcZzeipoTD + " add sac_rate numeric(7,3) not null default(0)"
  3611.         llRetval    = llRetVal and v_sqlexec(lcSql)
  3612.        
  3613.         TEXT TO lcSql NOSHOW
  3614.             declare curSac  CURSOR FORWARD_ONLY FOR
  3615.             select c.hfkey, c.dfkey
  3616.             FROM zzeipotsac c inner join #zzeipotd d on c.hfkey = d.fkey and c.dfkey = d.pkey order by hfkey, dfkey
  3617.             OPEN curSac
  3618.  
  3619.             declare @sac_id as varchar(250);
  3620.             set @sac_id = ''
  3621.             declare @sac_rate as numeric(7,3);
  3622.             set @sac_rate = 0.0
  3623.             declare @hfkey as int
  3624.             declare @dfkey as int
  3625.  
  3626.             FETCH NEXT FROM curSac INTO @hfkey,@dfkey
  3627.             while @@fetch_status = 0
  3628.             begin
  3629.                 set @sac_id = ''
  3630.                 set @sac_rate = 0.0
  3631.                 select @sac_id = @sac_id + sac_code, @sac_rate = @sac_rate + sac_rate from zzeipotsac a
  3632.                 where hfkey = @hfkey and dfkey = @dfkey order by a.hfkey, a.dfkey, a.sac_code, a.sac_rate
  3633.                 update d set sac_id = @sac_id, sac_rate = @sac_rate from #zzeipotd d where d.pkey = @dfkey and d.fkey = @hfkey
  3634.                 FETCH NEXT FROM curSac INTO @hfkey,@dfkey
  3635.             end
  3636.             close curSac;
  3637.             DEALLOCATE curSac;
  3638.             create index sac_id on #zzeipotd (sac_id,sac_rate)
  3639.         ENDTEXT
  3640.        
  3641.         lcSql       = STRTRAN(lcSql,"#zzeipotd", pcZzeipoTD)
  3642.         lcSql       = STRTRAN(lcSql,"#zzeipotsac", pcZzeiPOtsac)       
  3643.         llRetval    = llRetVal and v_sqlexec(lcSql)
  3644.  
  3645.         RETURN llRetVal
  3646.                
  3647.     ENDPROC
  3648.                            
  3649.     *--------------------------------------------------------------------------                    
  3650.    
  3651.     PROCEDURE AdvanceThermoPlus(l_nThermoCnt, pcCaption)
  3652.         If This.lUserInterface
  3653.             dodefault(@l_nThermoCnt)
  3654.             IF !EMPTY(pcCaption) AND VARTYPE(pcCaption) = "C"
  3655.                 this.UpdateThermoCaption(pcCaption)
  3656.             ENDIF
  3657.         ENDIF
  3658.     ENDPROC
  3659.    
  3660.     *--------------------------------------------------------------------------                        
  3661.  
  3662.     *--- TechRec 1048804 23-Aug-2010 TShenbagavalli ---                        
  3663.     PROCEDURE SetSpsBillOptn
  3664.         LOCAL llRetVal, lcSql
  3665.         llRetVal    = .t.
  3666.  
  3667.  
  3668.         *--- TR 1060588 01-29-2013 RKI ---*
  3669.         *Bill_optn from Shipper Reference
  3670.         lcSQL       = "Update h set bill_optn = r.bill_optn " + ;
  3671.                       " from " + this.cZzoordrh + " h " + ;
  3672.                       " inner join zzxshipr  r (nolock) " + ;
  3673.                       " on h.shipper = r.shipper where h.bill_optn = '' "
  3674.         llRetVal    = llRetVal and v_sqlexec(lcSql)
  3675.         *=== TR 1060588 01-29-2013 RKI ===*
  3676.        
  3677.         *--- TR 1062786 5-8-2012 VKK
  3678.         lcSql       = "Update h Set bill_optn = r.bill_optn " + ;
  3679.                       "from " + this.cZzoordrh + " h " + ;
  3680.                       " inner join zzxcslsr r (nolock) " + ;
  3681.                         " on r.pkey = dbo.bcfn_customer_sales_res(h.customer, h.division, h.store, h.department,h.ord_type) " + ;
  3682.                         " where h.bill_optn = '' "      && TR 1060588 02-12-2013 RKI
  3683.         llRetVal    = llRetVal and v_sqlexec(lcSql)
  3684.         *=== TR 1062786 5-8-2012 VKK
  3685.        
  3686.        
  3687.         lcSql       = "Update h Set bill_optn = r.bill_optn from " + this.cZzoordrh + " h inner join zzxstorr r (nolock) " + ;
  3688.                         " on h.customer = r.customer and h.store = r.store where r.active_ok = 'Y'" + ;
  3689.                         " and h.bill_optn = '' "  && *--- TR 1062786 5-8-2012 VKK
  3690.         llRetVal    = llRetVal and v_sqlexec(lcSql)
  3691.  
  3692.         lcSql       = "Update h Set bill_optn = r.bill_optn from " + this.cZzoordrh + " h inner join zzxcustr r (nolock) " + ;
  3693.                         " on h.customer = r.customer where h.bill_optn = '' "
  3694.         llRetVal    = llRetVal and v_sqlexec(lcSql)
  3695.        
  3696.         RETURN llRetVal
  3697.     ENDPROC
  3698.     *--------------------------------------------------------------------------
  3699.     *=== TechRec 1048804 23-Aug-2010 TShenbagavalli ===                    
  3700.  
  3701.     PROCEDURE RegisterTable
  3702.         LPARAMETERS tcTable
  3703.        
  3704.    
  3705.         IF USED("curTabDrop") AND NOT EMPTY(tcTable)
  3706.             INSERT INTO curTabDrop VALUES (tcTable)
  3707.         ENDIF
  3708.    
  3709.     ENDPROC
  3710.  
  3711.     *--- TechRec 1054619 13-Jun-2011 MANI. ---
  3712.     *==============================================================
  3713.     *-- Function    : PrepareDataForWhse
  3714.     *-- Syopsis     : Update field values to ware house table
  3715.     *--               (zzedatawhse) from oder detail.
  3716.     *==============================================================
  3717.  
  3718.     FUNCTION PrepareDataForWhse
  3719.         LOCAL llRetVal, lcSQL
  3720.         llRetVal = true
  3721.         lcSQL = ""
  3722.  
  3723.         WITH This
  3724.             *--- TechRec 1056973 15-Nov-2011 jisingh ---           
  3725. *!*         lcSQL = "UPDATE wh SET Table_name = (CASE WHEN wh.hfkey = h.pkey and Wh.dfkey = 0 THEN 'ZZOORDRH' ELSE " + ;
  3726. *!*                 "                           (CASE WHEN wh.Dfkey = COALESCE(D.pkey, 0) THEN 'ZZOORDRD'   END) END),  " + ;
  3727. *!*                 "              line_seq   = CASE WHEN wh.Dfkey = COALESCE(D.pkey,0) THEN COALESCE(D.Line_seq,0) ELSE wh.line_seq  END, " + ;
  3728. *!*                 "              sizebucket = CASE WHEN wh.Dfkey = COALESCE(D.pkey,0) THEN COALESCE(D.sizebucket,0) ELSE Wh.sizebucket END, " + ;
  3729. *!*                 "              user_id    = " + SQLFormatchar(EDI_USER) + ;
  3730. *!*                 "FROM " + .cZzeiPOtWHSE + " Wh JOIN " + .cZzeipoth + " h ON h.pkey = wh.Hfkey " + ;
  3731. *!*                 "LEFT OUTER JOIN " + .cZzeipotd + " D ON D.Pkey = wh.dfkey "
  3732.  
  3733. *!*         llRetVal =  v_SQLExec(lcSQL)
  3734.  
  3735.  
  3736.             lcSQL = "UPDATE wh1 SET sfkey = s.pkey " + ;
  3737.                     "FROM " + .cZzedataWHSE + " wh1 " + ;
  3738.                     "JOIN " + .czzeipotwhse + " wh " + ;
  3739.                     "   ON wh.sfkey = wh1.sfkey " + ;
  3740.                     "  AND wh.dfkey = wh1.dfkey " + ;
  3741.                     "JOIN " + .cZzeipots + " s " + ;
  3742.                     "   ON s.doc_num = wh.doc_num " + ;
  3743.                     "  AND s.sln_line = wh.edi_line " + ;
  3744.                     "  AND s.sln01 = wh.sln_line "
  3745.  
  3746.             llRetVal = llRetVal AND v_SQLExec(lcSQL)
  3747.  
  3748.             *--- TechRec 1059747 27-Apr-2012 jisingh ---
  3749.             * EDIS
  3750.             lcSQL = "UPDATE wh1 SET wh1.dfkey = d.pkey " + ;
  3751.                     "              ,wh1.sizebucket = d.sizebucket " + ;
  3752.                     "FROM " + .cZzedataWHSE + " wh1 " + ;
  3753.                     "JOIN " + .czzeipotwhse + " wh " + ;
  3754.                     "   ON wh.sfkey = wh1.sfkey " + ;
  3755.                     "  AND wh.dfkey = wh1.dfkey " + ;
  3756.                     "JOIN " + .cZzeipotd + " d " + ;
  3757.                     "   ON d.doc_num = wh.doc_num " + ;
  3758.                     "  AND d.po1_line = wh.edi_line " + ;
  3759.                     "  AND d.sln01 = wh.sln_line " + ;
  3760.                     "  AND d.store = wh.store " + ;
  3761.                     "WHERE wh.sfkey > 0 "
  3762.  
  3763.             llRetVal = llRetVal AND v_SQLExec(lcSQL)
  3764.            
  3765.             * EDID
  3766.             lcSQL = "UPDATE wh1 SET wh1.dfkey = d.pkey " + ;
  3767.                     "              ,wh1.sizebucket = d.sizebucket " + ;
  3768.                     "FROM " + .cZzedataWHSE + " wh1 " + ;
  3769.                     "JOIN " + .czzeipotwhse + " wh " + ;
  3770.                     "   ON wh.sfkey = wh1.sfkey " + ;
  3771.                     "  AND wh.dfkey = wh1.dfkey " + ;
  3772.                     "JOIN " + .cZzeipotd + " d " + ;
  3773.                     "   ON d.doc_num = wh.doc_num " + ;
  3774.                     "  AND d.po1_line = wh.edi_line " + ;
  3775.                     "  AND d.store = wh.store " + ;
  3776.                     "WHERE wh.sfkey = 0 "
  3777.  
  3778.             llRetVal = llRetVal AND v_SQLExec(lcSQL)
  3779.             *=== TechRec 1059747 27-Apr-2012 jisingh ===
  3780.            
  3781. *!*             *- TR 1059065 FH - why do we need to update dfkey again?
  3782. *!*             *-               - this update doesn't work on SDQ orders where 2 lines have the same doc_num, po1_line, sln01 but belongs to different
  3783. *!*             *-               -  header so it has different  hfkey and dfkey.
  3784. *!*             lcSQL = "UPDATE wh1 SET dfkey = d.pkey " + ;
  3785. *!*                     "FROM " + .cZzedataWHSE + " wh1 " + ;
  3786. *!*                     "JOIN " + .czzeipotwhse + " wh " + ;
  3787. *!*                     "   ON wh.sfkey = wh1.sfkey " + ;
  3788. *!*                     "  AND wh.dfkey = wh1.dfkey " + ;
  3789. *!*                     "JOIN " + .cZzeipotd + " d " + ;
  3790. *!*                     "   ON d.doc_num = wh.doc_num " + ;
  3791. *!*                     "  AND d.po1_line = wh.edi_line " + ;
  3792. *!*                     "  AND d.sln01 = wh.sln_line "
  3793.  
  3794. *!*             llRetVal = llRetVal AND v_SQLExec(lcSQL)       
  3795.  
  3796.  
  3797.             *--- TechRec 1058326 13-Dec-2011 jisingh ---
  3798.             *lcSQL = "UPDATE wh SET table_name = (CASE WHEN wh.sfkey = s.pkey THEN 'ZZEIPOHS' ELSE " + ;
  3799.                     "                           (CASE WHEN wh.hfkey > 0 AND wh.dfkey = 0 THEN 'ZZOORDRH' ELSE " + ;
  3800.                     "                           (CASE WHEN wh.dfkey = COALESCE(d.pkey, 0) THEN 'ZZOORDRD' ELSE wh.table_name END) END) END), " + ;
  3801.                     "              line_seq = CASE WHEN wh.dfkey = COALESCE(d.pkey,0) THEN COALESCE(d.line_seq,0) ELSE wh.line_seq END, " + ;
  3802.                     "              sizebucket = CASE WHEN wh.sfkey = COALESCE(s.pkey,0) THEN COALESCE(s.sizebucket,0) ELSE d.sizebucket END, " + ; 
  3803.                     "              user_id = " + SQLFormatchar(EDI_USER) + ;
  3804.                     "FROM " + .cZzedataWHSE + " wh " + ;
  3805.                     "LEFT OUTER JOIN " + .cZzeipotd + " d ON d.pkey = wh.dfkey " + ;
  3806.                     "LEFT OUTER JOIN " + .cZzeipots + " s ON s.pkey = wh.sfkey "
  3807.  
  3808. *!*             lcSQL = "UPDATE wh SET table_name = (CASE WHEN wh.sfkey > 0 THEN 'ZZEIPOHS' ELSE " + ;
  3809. *!*                     "                           (CASE WHEN wh.hfkey > 0 AND wh.dfkey = 0 THEN 'ZZOORDRH' ELSE " + ;
  3810. *!*                     "                           (CASE WHEN wh.dfkey = COALESCE(d.pkey, 0) THEN 'ZZOORDRD' ELSE wh.table_name END) END) END), " + ;
  3811. *!*                     "              line_seq = (CASE WHEN COALESCE(d.slntodtl,'') IN ('Y','I') THEN COALESCE(d1.line_seq,0) ELSE " + ;
  3812. *!*                     "                         (CASE WHEN wh.dfkey = COALESCE(d.pkey,0) THEN COALESCE(d.line_seq,0) ELSE wh.line_seq END) END), " + ;
  3813. *!*                     "              sizebucket = (CASE WHEN wh.sfkey = COALESCE(s.pkey,0) THEN COALESCE(s.sizebucket,0) ELSE " + ;
  3814. *!*                     "                           (CASE WHEN d.sizebucket IS NOT NULL THEN d.sizebucket ELSE wh.sizebucket END) END), " + ;  
  3815. *!*                     "              user_id = " + SQLFormatchar(EDI_USER) + ;
  3816. *!*                     "FROM " + .cZzedataWHSE + " wh " + ;
  3817. *!*                     "LEFT OUTER JOIN " + .cZzeipotd + " d ON d.pkey = wh.dfkey " + ;
  3818. *!*                     "LEFT OUTER JOIN " + .cZzeipots + " s ON s.pkey = wh.sfkey " + ;
  3819. *!*                     "LEFT OUTER JOIN (SELECT ord_num, assortment , po1_line, MIN(line_seq) AS line_seq " + ;
  3820. *!*                     "                 FROM " + .cZzeipotd + " GROUP BY ord_num, assortment, po1_line) d1 " + ;
  3821. *!*                     "   ON d.ord_num = d1.ord_num AND d.assortment = d1.assortment AND d.po1_line = d1.po1_line "
  3822. *!*                    
  3823.                
  3824.             *=== TechRec 1058326 13-Dec-2011 jisingh ===
  3825.            
  3826.                        
  3827.            
  3828.             *- FH 1074465 - Added zzoordrd_pkey to group by. zzoordrh.line_seq MUST MATCH zzedatawhse.line_seq         
  3829.             *-TR 1059065 FH - took out sizebucket, we updated sizebucket from before
  3830.             lcSQL = "UPDATE wh SET table_name = (CASE WHEN wh.sfkey > 0 THEN 'ZZEIPOHS' ELSE " + ;
  3831.                     "                           (CASE WHEN wh.hfkey > 0 AND wh.dfkey = 0 THEN 'ZZOORDRH' ELSE " + ;
  3832.                     "                           (CASE WHEN wh.dfkey = COALESCE(d.pkey, 0) THEN 'ZZOORDRD' ELSE wh.table_name END) END) END), " + ;
  3833.                     "              line_seq = (CASE WHEN COALESCE(d.slntodtl,'') IN ('Y','I') THEN COALESCE(d1.line_seq,0) ELSE " + ;
  3834.                     "                         (CASE WHEN wh.dfkey = COALESCE(d.pkey,0) THEN COALESCE(d.line_seq,0) ELSE wh.line_seq END) END), " + ;
  3835.                     "              user_id = " + SQLFormatchar(EDI_USER) + ;
  3836.                     "FROM " + .cZzedataWHSE + " wh " + ;
  3837.                     "LEFT OUTER JOIN " + .cZzeipotd + " d ON d.pkey = wh.dfkey " + ;
  3838.                     "LEFT OUTER JOIN " + .cZzeipots + " s ON s.pkey = wh.sfkey " + ;
  3839.                     "LEFT OUTER JOIN (SELECT ord_num, assortment , po1_line, MIN(line_seq) AS line_seq , zzoordrd_pkey " + ; && FH 1074465 zzoordrd_pkey
  3840.                     "                 FROM " + .cZzeipotd + " GROUP BY ord_num, assortment, po1_line, zzoordrd_pkey ) d1 " + ;  && FH 1074465 zzoordrd_pkey
  3841.                     "   ON d.ord_num = d1.ord_num AND d.assortment = d1.assortment AND d.po1_line = d1.po1_line "   +; 
  3842.                     " AND d.zzoordrd_pkey = d1.zzoordrd_pkey "     
  3843.  
  3844.             llRetVal = llRetVal AND v_SQLExec(lcSQL)
  3845.             *=== TechRec 1056973 15-Nov-2011 jisingh ===
  3846.            
  3847.             *-FH 1074465 - If SLNTODTL, we need to set min(sizebucket) for assortment, otherwise set sizebucket to what was set above  
  3848.             *- Moved this below to try and get the min(line_seq) for EDID lines.
  3849.             *- If this was above then line_seq on EDID won't necessary be the min(line_seq) per assortment (set by query above). (outbound won't pick it up)
  3850.             lcSQL = "   Update wh set  " + ;
  3851.                     " sizebucket = (case  " + ;
  3852.                     "           when COALESCE(d.slntodtl, '') in ('Y', 'I') then COALESCE(d1.sizebucket, 0)  " + ;
  3853.                     "           else COALESCE(wh.sizebucket , 0) end)  " + ;               
  3854.                     " , line_seq = (CASE WHEN COALESCE(d.slntodtl,'') IN ('Y','I') THEN COALESCE(d1.line_seq,0) ELSE  " +;
  3855.                     " (CASE WHEN wh.dfkey = COALESCE(d.pkey,0) THEN COALESCE(d.line_seq,0) ELSE wh.line_seq END) END) " +;
  3856.                     " FROM " + .cZzedataWHSE + " wh  " + ;
  3857.                     "  left outer join " + .cZzeipotd + " d  " + ;
  3858.                     "    on d.pkey = wh.dfkey  " + ;
  3859.                     "  left outer join (  " + ;
  3860.                     "       select ord_num  " + ;
  3861.                     "            , assortment  " + ;
  3862.                     "            , po1_line  " + ;
  3863.                     "            , min(sizebucket) as sizebucket  " + ;
  3864.                     "            , MIN(line_seq) as line_seq " +;
  3865.                     "         from " + .cZzeipotd + ;
  3866.                     "        group by ord_num, assortment, po1_line) d1  " + ;
  3867.                     "   on d.ord_num = d1.ord_num  " + ;
  3868.                     "   and d.assortment = d1.assortment  " + ;
  3869.                     "   and d.po1_line = d1.po1_line  " + ;
  3870.                     " where  wh.sfkey = 0 "
  3871.                 llRetVal = llRetVal AND v_SQLExec(lcSQL)
  3872.  
  3873.         ENDWITH
  3874.                
  3875.         RETURN llRetVal
  3876.     ENDFUNC && PrepareDataForWhse
  3877.  
  3878.  
  3879.     *=== TechRec 1054619 13-Jun-2011 MANI. ===
  3880. *-TR 1059065 FH
  3881. PROCEDURE UpdateWarehouseSizebucket
  3882.     LPARAMETERS pcDataWhse, pcSrcDtl
  3883.    
  3884.         LOCAL llRetval, lcSql
  3885.         llRetval = .t.
  3886.        
  3887.         lcSql       = " update wh set wh.sizebucket = d.sizebucket " + ;
  3888.                        " from " + pcDataWhse + " wh " + ;
  3889.                        " join " + pcSrcDtl + " d " + ;
  3890.                        " on wh.dfkey = d.pkey " +;
  3891.                        " where wh.sfkey = 0"
  3892.                        
  3893.         llRetval    = llRetVal and v_sqlexec(lcSql)        
  3894.        
  3895.         RETURN llRetval
  3896.  
  3897. ENDPROC
  3898.  
  3899. PROCEDURE UpdateWarehouseSLNSizebucket
  3900.     LPARAMETERS pcDataWhse, pcSrcDtl
  3901.    
  3902.         LOCAL llRetval, lcSql
  3903.         llRetval = .t.
  3904.        
  3905.         lcSql       = " update wh set wh.sizebucket = s.sizebucket " + ;
  3906.                        " from " + pcDataWhse + " wh " + ;
  3907.                        " join " + pcSrcDtl + " s " + ;
  3908.                        " on wh.sfkey = s.pkey " +;
  3909.                        " where wh.sfkey > 0"
  3910.                        
  3911.         llRetval    = llRetVal and v_sqlexec(lcSql)        
  3912.        
  3913.         RETURN llRetval
  3914.  
  3915. ENDPROC
  3916.    
  3917.    
  3918.    
  3919. *-TR 1059065 FH
  3920.  
  3921.        
  3922. ENDDEFINE
  3923.  
  3924. *==========================================================
  3925. DEFINE CLASS IPO_CopyUtil AS CopyUtil
  3926.  
  3927.     *--------------------------------------------------
  3928.     PROCEDURE SendCursorToSqlTbl
  3929.         Lparam pcSrcDbf, pcDesTable, plDesTableExists
  3930.  
  3931.         LOCAL llRetVal
  3932.         llRetVal = .T.
  3933.        
  3934.         llRetVal = DODEFAULT(pcSrcDbf, pcDesTable, plDesTableExists)
  3935.        
  3936.         IF llRetVal AND USED("curTabDrop") AND NOT EMPTY(pcDesTable)
  3937.             INSERT INTO curTabDrop VALUES (pcDesTable)
  3938.         ENDIF
  3939.  
  3940.     ENDPROC
  3941.  
  3942. ENDDEFINE
RAW Paste Data
Pastebin PRO Autumn Special!
Get 40% OFF on Pastebin PRO accounts!
Top