daily pastebin goal
50%
SHARE
TWEET

Untitled

a guest Oct 12th, 2017 68 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
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top