/// <summary> /// FOR CATRON AREA INVENTORY INFORMATION /// </summary> /// <param name="cartonArea"></param> /// <returns></returns> public IList <CartonAreaInventory> GetCartonAreaInventory(string cartonArea) { Contract.Assert(_db != null); const string QUERY_CARTON_AREA_INVENTORY_DETAIL = @" SELECT COUNT(CTN.CARTON_ID) AS CARTON_COUNT, COUNT(DISTINCT CTNDET.SKU_ID) AS DISTINCT_SKU, MS.LABEL_ID AS LABEL_ID, SUM(CTNDET.QUANTITY) AS QUANTITY FROM <proxy />SRC_CARTON CTN LEFT OUTER JOIN <proxy />SRC_CARTON_DETAIL CTNDET ON CTN.CARTON_ID = CTNDET.CARTON_ID LEFT OUTER JOIN <proxy />Master_Sku msku ON ctndet.sku_id = msku.sku_id LEFT OUTER JOIN <proxy />MASTER_STYLE MS ON msku.style = MS.STYLE WHERE CTN.CARTON_STORAGE_AREA = :CARTON_STORAGE_AREA GROUP BY CTN.CARTON_STORAGE_AREA, MS.LABEL_ID HAVING SUM(CTNDET.QUANTITY) > 0 ORDER BY SUM(CTNDET.QUANTITY) DESC, MS.LABEL_ID "; var binder = SqlBinder.Create(row => new CartonAreaInventory { CartonCount = row.GetInteger("CARTON_COUNT"), DistinctSKUs = row.GetInteger("DISTINCT_SKU"), LabelId = row.GetString("LABEL_ID"), SKUQuantity = row.GetInteger("QUANTITY") }).Parameter("CARTON_STORAGE_AREA", cartonArea); return(_db.ExecuteReader(QUERY_CARTON_AREA_INVENTORY_DETAIL, binder)); }
public IList <BucketBase> GetRecentCreatedBucket(int maxRows) { const string QUERY = @"SELECT BK.BUCKET_ID AS BUCKET_ID, MIN(BK.NAME) AS BUCKET_NAME, MIN(BK.DATE_CREATED) AS DATE_CREATED, MIN(BK.CREATED_BY) AS CREATED_BY, MIN(BK.date_modified) as date_modified FROM BUCKET BK INNER JOIN PS PS ON BK.BUCKET_ID = PS.BUCKET_ID GROUP BY BK.BUCKET_ID ORDER BY MIN(BK.DATE_CREATED) DESC NULLS LAST"; var binder = SqlBinder.Create(row => new BucketBase { BucketId = row.GetInteger("BUCKET_ID").Value, CreatedBy = row.GetString("CREATED_BY"), CreationDate = row.GetDate("DATE_CREATED"), ModifyDate = row.GetDate("date_modified"), BucketName = row.GetString("BUCKET_NAME") }); return(_db.ExecuteReader(QUERY, binder, maxRows)); }
// /// <summary> // /// count location for passed sku // /// </summary> // /// <param name="areaId"></param> // /// <param name="skuId"></param> // /// <returns></returns> // public int GetAssignedLocationCount(string areaId, int skuId) // { // const string QUERY = @" // SELECT COUNT(*) AS COUNT // FROM <proxy/>MASTER_STORAGE_LOCATION MSL // WHERE MSL.STORAGE_AREA = :areaId // AND MSL.ASSIGNED_SKU_ID = :skuId // "; // var binder = SqlBinder.Create(row => row.GetInteger("COUNT").Value) // .Parameter("areaId", areaId) // .Parameter("skuId", skuId); // return _db.ExecuteSingle(QUERY, binder); // } public IEnumerable <CartonArea> GetCartonAreas() { const string QUERY = @" SELECT T.INVENTORY_STORAGE_AREA AS INVENTORY_STORAGE_AREA, T.WAREHOUSE_LOCATION_ID AS BUILDING_ID, T.SHORT_NAME AS SHORT_NAME, T.LOCATION_NUMBERING_FLAG AS IS_NUMBERED_AREA, T.IS_RECEIVING_AREA AS IS_RECEIVING_AREA, T.IS_spotcheck_area as IS_SPOTCHECK_AREA, t.description AS description FROM <proxy />TAB_INVENTORY_AREA T WHERE T.STORES_WHAT = 'CTN' "; var binder = SqlBinder.Create(row => new CartonArea() { BuildingId = row.GetString("BUILDING_ID"), ShortName = row.GetString("SHORT_NAME"), AreaId = row.GetString("INVENTORY_STORAGE_AREA"), Description = row.GetString("description"), IsNumberedArea = row.GetString("IS_NUMBERED_AREA") == "Y", IsReceivingArea = row.GetString("IS_RECEIVING_AREA") == "Y", IsSpotCheckArea = row.GetString("IS_SPOTCHECK_AREA") == "Y" }); return(_db.ExecuteReader(QUERY, binder)); }
/// <summary> /// List of Sku returned. /// </summary> /// <returns></returns> public IList <SkuHeadline> GetSkuList() { Contract.Assert(_db != null); const string QUERY = @" SELECT MAX(M.SKU_ID) AS SKU_ID, MAX(M.STYLE) AS STYLE, MAX(M.COLOR) AS COLOR, MAX(M.DIMENSION) AS DIMENSION, MAX(M.SKU_SIZE) AS SKU_SIZE, M.UPC_CODE AS UPC_CODE, MAX(PS.PICKSLIP_IMPORT_DATE) AS PICKSLIP_IMPORT_DATE FROM <proxy/>PS PS INNER JOIN <proxy/>PSDET PD ON PS.PICKSLIP_ID = PD.PICKSLIP_ID INNER JOIN <proxy/>MASTER_SKU M ON M.UPC_CODE = PD.UPC_CODE GROUP BY M.UPC_CODE ORDER BY MAX(PS.PICKSLIP_IMPORT_DATE) DESC NULLS LAST "; var binder = SqlBinder.Create(row => new SkuHeadline { SkuId = row.GetInteger("SKU_ID").Value, Style = row.GetString("STYLE"), Color = row.GetString("COLOR"), Dimension = row.GetString("DIMENSION"), SkuSize = row.GetString("SKU_SIZE"), Upc = row.GetString("UPC_CODE"), PickslipOrderDate = row.GetDate("PICKSLIP_IMPORT_DATE") }); return(_db.ExecuteReader(QUERY, binder, 200)); }
/// <summary> /// Removed passed pickslip from bucket /// Sharad Sir and Shiva : Delete Bucket when last pickslip is removed. /// </summary> /// <param name="pickslips"></param> /// <param name="bucketId"></param> public void RemovePickslipFromBucket(IList <long> pickslips, int bucketId) { const string QUERY = @" DECLARE LPICKSLIP_COUNT NUMBER; BEGIN <proxy />PKG_BUCKET.DELETE_PS_CTNRESV(APICKSLIP_ID => :APICKSLIP_ID); <proxy />PKG_DATA_EXCHANGE.REVERT_PICKSLIP(APICKSLIP_ID => :APICKSLIP_ID); SELECT COUNT(P.PICKSLIP_ID) INTO LPICKSLIP_COUNT FROM <proxy />PS P WHERE P.BUCKET_ID = :BUCKET_ID AND P.TRANSFER_DATE IS NULL; IF LPICKSLIP_COUNT = 0 THEN DELETE FROM <proxy />BUCKET BKT WHERE BKT.BUCKET_ID = :BUCKET_ID; END IF; END; "; var binder = SqlBinder.Create(pickslips.Count); binder.Parameter("APICKSLIP_ID", pickslips) .Parameter("BUCKET_ID", Enumerable.Repeat(bucketId, pickslips.Count)); _db.ExecuteDml(QUERY, binder); }
public PickWave GetPickWave(int bucketId) { if (bucketId == 0) { throw new ArgumentNullException("bucketId"); } const string QUERY = @" SELECT COUNT(PS.PICKSLIP_ID) AS PICKSLIP_COUNT, MAX(T.SHORT_NAME) AS PULL_AREA, MAX(I.SHORT_NAME) AS PITCH_AREA, B.BUCKET_ID AS BUCKET_ID FROM <proxy />BUCKET B LEFT OUTER JOIN <proxy />PS PS ON PS.BUCKET_ID = B.BUCKET_ID AND PS.TRANSFER_DATE IS NULL LEFT OUTER JOIN <proxy />TAB_INVENTORY_AREA T ON T.INVENTORY_STORAGE_AREA = B.PULL_CARTON_AREA LEFT OUTER JOIN <proxy />IA I ON I.IA_ID = B.PITCH_IA_ID WHERE B.BUCKET_ID = :BUCKET_ID GROUP BY B.BUCKET_ID"; var binder = SqlBinder.Create(row => new PickWave { BucketId = row.GetInteger("BUCKET_ID") ?? 0, PullAreaShortName = row.GetString("PULL_AREA"), PickslipCount = row.GetInteger("PICKSLIP_COUNT") ?? 0, PitchAreaShortName = row.GetString("PITCH_AREA") }) .Parameter("BUCKET_ID", bucketId); return(_db.ExecuteSingle(QUERY, binder)); }
public IList <CustomerSkuLabel> GetPrivateLabelBarCodesOfSku(int skuId) { Contract.Assert(_db != null); const string QUERY = @" SELECT M.CUSTOMER_ID AS CUSTOMER_ID, C.NAME AS NAME, NVL(M.SCANNED_BAR_CODE, m.customer_sku_id) AS SCANNED_BAR_CODE FROM <proxy />MASTER_CUSTOMER_SKU M LEFT OUTER JOIN <proxy />CUST C ON M.CUSTOMER_ID = C.CUSTOMER_ID WHERE M.SKU_ID = :SKU_ID AND M.INACTIVE_FLAG IS NULL "; var binder = SqlBinder.Create(row => new CustomerSkuLabel { CustomerId = row.GetString("CUSTOMER_ID"), CustomerName = row.GetString("NAME"), //InsertedBy = row.GetString("INSERTED_BY"), //ModifiedBy = row.GetString("MODIFIED_BY"), ScannedBarCode = row.GetString("SCANNED_BAR_CODE") //InsetDate = row.GetDate("INSERT_DATE"), //ModifiedDate = row.GetDate("MODIFIED_DATE") }).Parameter("SKU_ID", skuId); return(_db.ExecuteReader(QUERY, binder)); }
/// <summary> /// This method prints the Carton Ticket /// </summary> /// <param name="cartonId"></param> /// <param name="printerId"></param> public void PrintCartonTicket(string scanText, string printerId) { const string QUERY = @" DECLARE TYPE CARTON_TABLE_T IS TABLE OF SRC_CARTON.CARTON_ID%TYPE; CARTON_LIST CARTON_TABLE_T; BEGIN SELECT CARTON_ID BULK COLLECT INTO CARTON_LIST FROM <proxy />SRC_CARTON S WHERE S.CARTON_ID = :scanText OR S.PALLET_ID = :scanText; IF CARTON_LIST.COUNT > 0 THEN FOR I IN 1 .. CARTON_LIST.COUNT LOOP <proxy />PKG_JF_SRC_2.PKG_JF_SRC_CTN_TKT(ACARTON_ID => CARTON_LIST(I), APRINTER_NAME => :aprinter_name); END LOOP; END IF; END;"; var binder = SqlBinder.Create().Parameter("scanText", scanText) .Parameter("aprinter_name", printerId); _db.ExecuteNonQuery(QUERY, binder); }
/// <summary> /// Get areas to transfer irregular and sample pieces /// </summary> /// <param name="pieceFlag"></param> /// <returns></returns> public IEnumerable <SkuArea> GetTransferAreas(PiecesRemoveFlag pieceFlag) { const string QUERY = @" SELECT TIA.INVENTORY_STORAGE_AREA AS INVENTORY_STORAGE_AREA, TIA.DESCRIPTION AS DESCRIPTION, TIA.SHORT_NAME AS SHORT_NAME FROM <proxy />TAB_INVENTORY_AREA TIA WHERE TIA.STORES_WHAT = 'SKU' <if c='$samples'> AND TIA.UNUSABLE_INVENTORY='Y' </if> <if c='$irregular'> AND TIA.CONSOLIDATED_UPC_CODE IS NOT NULL </if> ORDER BY TIA.INVENTORY_STORAGE_AREA "; var binder = SqlBinder.Create(row => new SkuArea() { AreaId = row.GetString("INVENTORY_STORAGE_AREA"), Description = row.GetString("DESCRIPTION"), ShortName = row.GetString("SHORT_NAME") }); binder.ParameterXPath("samples", pieceFlag == PiecesRemoveFlag.Samples); binder.ParameterXPath("irregular", pieceFlag == PiecesRemoveFlag.Irregular); return(_db.ExecuteReader(QUERY, binder)); }
/// <summary> /// This function returns audit of SKU assignment and unassignment on particular location. /// </summary> /// <param name="locationId"></param> /// <returns></returns> public IList <LocationAudit> GetLocAssignUnassignAudit(string locationId) { const string QUERY = @" SELECT ACTION_PERFORMED AS ACTION_PERFORMED, DATE_CREATED AS DATE_CREATED, MODULE_CODE AS MODULE_CODE, CREATED_BY AS CREATED_BY, IACONTENT_ID AS IACONTENT_ID FROM <proxy />LOCATION_AUDIT WHERE ACTION_PERFORMED IN ('SKU_UNASSIGNED','SKU_ASSIGNED', 'SKUCHANGED') AND LOCATION_ID = :LOCATION_ID ORDER BY DATE_CREATED DESC "; var binder = SqlBinder.Create(row => new LocationAudit { CreatedBy = row.GetString("CREATED_BY"), ActionPerformed = row.GetString("ACTION_PERFORMED"), DateCreated = row.GetDate("DATE_CREATED"), ModuleCode = row.GetString("MODULE_CODE"), UpcCode = row.GetString("IACONTENT_ID") }).Parameter("LOCATION_ID", locationId); return(_db.ExecuteReader(QUERY, binder, 500)); }
/// <summary> /// This method updates the carton storage and pallet id of a carton /// </summary> /// <param name="carton"></param> /// <param name="moveFlags"></param> public void MoveCarton(Carton carton, CartonUpdateFlags moveFlags) { const string QUERY = @" BEGIN <if c='$carton_area'> UPDATE <proxy />SRC_CARTON SC SET SC.SUSPENSE_DATE = NULL, SC.CARTON_STORAGE_AREA = :carton_area ,SC.location_id = :location_id WHERE SC.CARTON_ID = :carton_id; </if> <if c='$pallet_id'> UPDATE <proxy />SRC_CARTON SC SET SC.SUSPENSE_DATE = NULL, SC.PALLET_ID = :pallet_id WHERE SC.CARTON_ID = :carton_id; </if> <if c='$removePallet'> UPDATE <proxy />SRC_CARTON SC SET SC.SUSPENSE_DATE = NULL, SC.PALLET_ID = NULL WHERE SC.CARTON_ID = :carton_id; </if> <if c='$updateLocation'> UPDATE <proxy />SRC_CARTON SC SET SC.SUSPENSE_DATE = NULL, SC.location_id = :location_id WHERE SC.CARTON_ID = :carton_id; </if> END; "; var binder = SqlBinder.Create().Parameter("carton_id", carton.CartonId) .Parameter("carton_area", moveFlags.HasFlag(CartonUpdateFlags.Area) ? carton.CartonArea.AreaId : null) .Parameter("pallet_id", moveFlags.HasFlag(CartonUpdateFlags.Pallet) ? carton.PalletId : null) .Parameter("location_id", moveFlags.HasFlag(CartonUpdateFlags.Location) ? carton.LocationId : null); binder.ParameterXPath("removePallet", moveFlags.HasFlag(CartonUpdateFlags.RemovePallet)); binder.ParameterXPath("updateLocation", moveFlags.HasFlag(CartonUpdateFlags.Location)); _db.ExecuteNonQuery(QUERY, binder); }
/// <summary> /// This function gets number of boxes per pallet on sku location /// </summary> /// <param name="locationId"></param> /// <returns></returns> public IList <SkuLocationPallet> GetPalletsOfSkuLocation(string locationId) { Contract.Assert(_db != null); const string QUERY = @" SELECT B.PALLET_ID AS PALLET_ID, COUNT(DISTINCT B.UCC128_ID) AS TOTALBOXES, C.CUSTOMER_ID AS CUSTOMER_ID, MAX(C.NAME) AS CUSTOMER_NAME FROM <proxy />BOX B INNER JOIN <proxy />BOXDET BD ON B.UCC128_ID = BD.UCC128_ID LEFT OUTER JOIN <proxy />PS P ON B.PICKSLIP_ID = P.PICKSLIP_ID LEFT OUTER JOIN <proxy />CUST C ON P.CUSTOMER_ID = C.CUSTOMER_ID WHERE B.LOCATION_ID = :LOCATION_ID and b.stop_process_date is null AND B.PALLET_ID IS NOT NULL GROUP BY B.PALLET_ID,C.CUSTOMER_ID ORDER BY B.PALLET_ID "; var binder = SqlBinder.Create(row => new SkuLocationPallet { TotalBoxes = row.GetInteger("TOTALBOXES") ?? 0, PalletId = row.GetString("PALLET_ID"), CustomerId = row.GetString("CUSTOMER_ID"), CustomerName = row.GetString("CUSTOMER_NAME") }).Parameter("LOCATION_ID", locationId); return(_db.ExecuteReader(QUERY, binder)); }
public IList <ReturnReceiptHeadline> GetReturnList() { const string QUERY = @"SELECT DR.RETURNS_AUTHORIZATION_NUMBER AS RETURNS_AUTHORIZATION_NUMBER, COUNT(DR.RECEIPT_NUMBER) AS TOTAL_RECEIPT_NUMBER, SUM(DR.NO_OF_CARTONS) AS TOTAL_CARTONS, SUM(DR.EXPECTED_PIECES) AS TOTAL_EXPECTED_PIECES, MIN(DR.RECEIVED_DATE) AS RECEIVED_DATE, COUNT(UNIQUE DR.CUSTOMER_ID) AS CUSTOMER_COUNT, MAX(MC.CUSTOMER_ID) AS CUSTOMER_ID, MAX(MC.NAME) AS CUSTOMER_NAME FROM DEM_RETURNS DR LEFT OUTER JOIN MASTER_CUSTOMER MC ON MC.CUSTOMER_ID = DR.CUSTOMER_ID GROUP BY DR.RETURNS_AUTHORIZATION_NUMBER ORDER BY MIN(DR.RECEIVED_DATE) DESC "; var binder = SqlBinder.Create(row => new ReturnReceiptHeadline { ReturnNumber = row.GetString("RETURNS_AUTHORIZATION_NUMBER"), CustomerId = row.GetString("CUSTOMER_ID"), CustomerName = row.GetString("CUSTOMER_NAME"), TotalReceipts = row.GetInteger("TOTAL_RECEIPT_NUMBER").Value, NoOfCartons = row.GetInteger("TOTAL_CARTONS"), ExpectedPieces = row.GetInteger("TOTAL_EXPECTED_PIECES"), ReceivedDate = row.GetDate("RECEIVED_DATE").Value, CustomerCount = row.GetInteger("CUSTOMER_COUNT") }); return(_db.ExecuteReader(QUERY, binder, 200)); }
/// <summary> /// Function return the PO info. /// </summary> /// <param name="poId"></param> /// <param name="customerId"></param> /// <param name="iteration"></param> /// <returns></returns> public PurchaseOrder GetPo(string poId, string customerId, int iteration) { Contract.Assert(_db != null); const string QUERY_PO_INFO = @" SELECT PO.PO_ID AS PO_ID, PO.CUSTOMER_ID AS CUSTOMER_ID, PO.ITERATION AS ITERATION, MAX(CUST.NAME) AS NAME, MAX(PO.ORDER_DATE) AS ORDER_DATE, MIN(PO.START_DATE) AS START_DATE, MIN(PO.CANCEL_DATE) AS CANCEL_DATE, MIN(PO.DC_CANCEL_DATE) AS DC_CANCEL_DATE, COUNT(DISTINCT CASE WHEN B.LAST_UCC_PRINT_DATE IS NOT NULL THEN B.UCC128_ID END) AS NUMBER_OF_UCC_PRINTED, COUNT(DISTINCT CASE WHEN B.LAST_CCL_PRINT_DATE IS NOT NULL THEN B.UCC128_ID END) AS NUMBER_OF_CCL_PRINTED, COUNT(DISTINCT B.UCC128_ID) AS TOTAL_BOXES, COUNT(UNIQUE PO.ITERATION) OVER() AS COUNT_ITERATIONS FROM <proxy />PO PO LEFT OUTER JOIN <proxy />PS PS ON PS.PO_ID = PO.PO_ID AND PS.CUSTOMER_ID = PO.CUSTOMER_ID AND PS.ITERATION = PO.ITERATION LEFT OUTER JOIN <proxy />BOX B ON PS.PICKSLIP_ID = B.PICKSLIP_ID INNER JOIN <proxy />CUST CUST ON CUST.CUSTOMER_ID = PO.CUSTOMER_ID WHERE PO.PO_ID = :PO_ID AND PO.CUSTOMER_ID = :CUSTOMER_ID --AND PO.ITERATION = :ITERATION GROUP BY PO.CUSTOMER_ID, PO.PO_ID, PO.ITERATION ORDER BY case when po.iteration = :ITERATION THEN :ITERATION END NULLS LAST "; var binder = SqlBinder.Create(row => new PurchaseOrder { PoId = row.GetString("PO_ID"), //PSCount = row.GetInteger("PS_COUNT"), OrderDate = row.GetDate("ORDER_DATE"), StartDate = row.GetDate("START_DATE"), CancelDate = row.GetDate("CANCEL_DATE"), DcCancelDate = row.GetDate("DC_CANCEL_DATE"), CustomerId = row.GetString("CUSTOMER_ID"), CustomerName = row.GetString("NAME"), Iteration = row.GetInteger("ITERATION") ?? 0, CountOfCclPrinted = row.GetInteger("NUMBER_OF_CCL_PRINTED") ?? 0, CountOfUccPrinted = row.GetInteger("NUMBER_OF_UCC_PRINTED") ?? 0, TotalBoxes = row.GetInteger("TOTAL_BOXES") ?? 0, CountIterations = row.GetInteger("COUNT_ITERATIONS") ?? 0 }).Parameter("PO_ID", poId) .Parameter("CUSTOMER_ID", customerId) .Parameter("ITERATION", iteration); return(_db.ExecuteSingle(QUERY_PO_INFO, binder)); }
/// <summary> /// Prints the pallet passed /// We pass 'ACOPIES' parameter as null in 'PKG_BOXEXPEDITE.PRINT_PALLET' so that default documents are printed. /// </summary> /// <param name="palletId"></param> /// <param name="printer"></param> public void Print(string palletId, string printer) { if (string.IsNullOrEmpty(palletId)) { throw new ArgumentNullException("Pallet is null"); } if (string.IsNullOrEmpty(printer)) { throw new ArgumentNullException("Printer is null"); } const string QUERY = @" BEGIN <proxy />PKG_BOXEXPEDITE.PRINT_PALLET(APALLET_ID => :APALLET_ID, APRINTER_NAME => :APRINTER_NAME, ACOPIES => NULL); END; "; var binder = SqlBinder.Create() .Parameter("APALLET_ID", palletId) .Parameter("APRINTER_NAME", printer); _db.ExecuteNonQuery(QUERY, binder); }
/// <summary> /// This method returns all value in SKuModel. /// </summary> /// <param name="style"></param> /// <param name="color"></param> /// <param name="dimension"></param> /// <param name="skuSize"></param> /// <returns> /// Return style,color,dimension,SKuSize and SKuId. /// </returns> public Sku GetSku(string style, string color, string dimension, string skuSize) { const string QUERY = @" SELECT MSKU.SKU_ID AS SKU_ID, MSKU.STYLE AS STYLE, MSKU.COLOR AS COLOR, MSKU.DIMENSION AS DIMENSION, MSKU.SKU_SIZE AS SKU_SIZE FROM <proxy />MASTER_SKU MSKU WHERE MSKU.STYLE = :STYLE AND MSKU.COLOR = :COLOR AND MSKU.DIMENSION = :DIMENSION AND MSKU.SKU_SIZE = :SKU_SIZE"; var binder = SqlBinder.Create(row => new Sku { Style = row.GetString("STYLE"), Color = row.GetString("COLOR"), Dimension = row.GetString("DIMENSION"), SkuSize = row.GetString("SKU_SIZE"), SkuId = row.GetInteger("SKU_ID").Value }).Parameter("STYLE", style) .Parameter("COLOR", color) .Parameter("DIMENSION", dimension) .Parameter("SKU_SIZE", skuSize); return(_db.ExecuteSingle(QUERY, binder)); }
/// <summary> /// Create bucket /// </summary> /// <param name="bucket"></param> /// <returns></returns> public int CreateDefaultWave() { const string QUERY = @" INSERT INTO <proxy />BUCKET BKT (BKT.BUCKET_ID, BKT.NAME, BKT.PRIORITY, BKT.FREEZE) VALUES (<proxy />BUCKET_SEQUENCE.NEXTVAL, 'Automatically Created', '1', 'Y') RETURNING BUCKET_ID INTO :BUCKET_ID "; var binder = SqlBinder.Create(); //binder.Parameter("PITCH_TYPE", "BOX") // .Parameter("NAME", bucket.BucketName) // .Parameter("PITCH_IA_ID", bucket.PitchAreaId) // .Parameter("PRIORITY", bucket.PriorityId) // .Parameter("PULL_CARTON_AREA", bucket.PullAreaId) // .Parameter("QUICK_PITCH_FLAG", bucket.QuickPitch ? "Y" : null) // .Parameter("PULL_TO_DOCK", bucket.PullingBucket) // ; var bucketId = 0; binder.OutParameter("BUCKET_ID", val => bucketId = val.Value); _db.ExecuteDml(QUERY, binder); return(bucketId); }
/// <summary> /// Returns all carton areas /// 25-1-2012: Removing conversion area.Now conversion can be done in any area. /// </summary> /// <returns></returns> public IEnumerable <CartonArea> GetCartonAreas(string areaId) { const string QUERY = @" SELECT TIA.INVENTORY_STORAGE_AREA AS INVENTORY_STORAGE_AREA, TIA.DESCRIPTION AS DESCRIPTION, TIA.SHORT_NAME AS SHORT_NAME, TIA.STORES_WHAT AS STORES_WHAT, TIA.LOCATION_NUMBERING_FLAG AS LOCATION_NUMBERING_FLAG, TIA.WAREHOUSE_LOCATION_ID AS WAREHOUSE_LOCATION_ID, TIA.UNUSABLE_INVENTORY AS UNUSABLE_INVENTORY FROM <proxy />TAB_INVENTORY_AREA TIA <if> WHERE TIA.INVENTORY_STORAGE_AREA = :INVENTORY_STORAGE_AREA </if> ORDER BY TIA.INVENTORY_STORAGE_AREA "; var binder = SqlBinder.Create(row => new CartonArea { AreaId = row.GetString("INVENTORY_STORAGE_AREA"), Description = row.GetString("DESCRIPTION"), ShortName = row.GetString("SHORT_NAME"), BuildingId = row.GetString("WAREHOUSE_LOCATION_ID"), LocationNumberingFlag = row.GetString("LOCATION_NUMBERING_FLAG") == "Y", UnusableInventory = row.GetString("UNUSABLE_INVENTORY") == "Y", IsCartonArea = row.GetString("STORES_WHAT") == "CTN" }).Parameter("INVENTORY_STORAGE_AREA", areaId); return(_db.ExecuteReader(QUERY, binder)); }
/// <summary> /// It will create a pallet for building and area specially for ADR pulling /// </summary> public int CreateADRPallet(string palletId, string buildingId, string area) { if (string.IsNullOrEmpty(palletId)) { throw new ArgumentNullException("PalletId is null"); } if (string.IsNullOrEmpty(buildingId)) { throw new ArgumentNullException("Building is null"); } const string QUERY = @" begin :result := <proxy />pkg_boxexpedite.create_adr_pallet(apallet_id => :apallet_id, abuilding => :abuilding, adestination_area => :adestination_area); end; "; int rowsAffected = 0; var binder = SqlBinder.Create().Parameter("apallet_id", palletId) .Parameter("abuilding", buildingId) .Parameter("adestination_area", area) .OutParameter("result", m => rowsAffected = m ?? 0); _db.ExecuteNonQuery(QUERY, binder); return(rowsAffected); }
/// <summary> /// This function returns a list of customer preferred SKU cases. /// </summary> /// <returns></returns> public IEnumerable <CustomerSkuCase> GetCustomerSkuCaseList() { const string QUERY = @" SELECT SCC.CUSTOMER_ID AS CUSTOMER_ID, CUST.NAME AS NAME, SCC.CASE_ID AS CASE_ID, SCASE.SHORT_DESCRIPTION AS SHORT_DESCRIPTION, SCASE.MAX_CONTENT_VOLUME AS MAX_CONTENT_VOLUME, SCASE.OUTER_CUBE_VOLUME AS OUTER_CUBE_VOLUME, SCASE.EMPTY_WT AS EMPTY_WT, SCC.COMMENTS AS COMMENTS FROM <proxy />CUSTSKUCASE_CONSTRAINTS SCC INNER JOIN <proxy />SKUCASE SCASE ON SCASE.CASE_ID = SCC.CASE_ID LEFT OUTER JOIN <proxy />CUST CUST ON SCC.CUSTOMER_ID = CUST.CUSTOMER_ID WHERE SCASE.MAX_CONTENT_VOLUME IS NOT NULL AND SCASE.MAX_CONTENT_VOLUME > 0.0 ORDER BY SCC.CUSTOMER_ID,SCASE.MAX_CONTENT_VOLUME DESC "; var binder = SqlBinder.Create(row => new CustomerSkuCase { CaseId = row.GetString("CASE_ID"), CustomerId = row.GetString("CUSTOMER_ID"), CustomerName = row.GetString("NAME"), EmptyWeight = row.GetDecimal("EMPTY_WT"), MaxContentVolume = row.GetDecimal("MAX_CONTENT_VOLUME"), OuterCubeVolume = row.GetDecimal("OUTER_CUBE_VOLUME"), Comment = row.GetString("COMMENTS"), CaseDescription = row.GetString("SHORT_DESCRIPTION") }); return(_db.ExecuteReader(QUERY, binder)); }
/// <summary> /// Retrieve Scanned style information /// </summary> /// <param name="style"></param> /// <returns></returns> public Style GetStyleInfo(string style) { Contract.Assert(_db != null); const string QUERY = @" with q1 AS (SELECT MS.STYLE AS STYLE, MS.DESCRIPTION AS DESCRIPTION, msc.color, MS.LABEL_ID AS LABEL_ID, MSC.COUNTRY_ID AS COUNTRY_ID, tc.name as country_name FROM <proxy/>MASTER_STYLE MS LEFT OUTER JOIN <proxy/>MASTER_STYLE_COLOR MSC ON MS.STYLE = MSC.STYLE LEFT OUTER JOIN <proxy/>TAB_COUNTRY TC ON MSC.COUNTRY_ID = TC.COUNTRY_ID WHERE MS.STYLE = :style ) select STYLE, DESCRIPTION, LABEL_ID, cast(country_id_xml as varchar2(4000)) as country_id_xml from q1 pivot XML(count(distinct q1.color) as count_colors, max(q1.country_name) as country_name for country_id in(ANY)) "; var binder = SqlBinder.Create(row => new Style { StyleId = row.GetString("STYLE"), Description = row.GetString("DESCRIPTION"), LabelId = row.GetString("LABEL_ID"), CountryOfOrigins = MapCountryXml(row.GetString("country_id_xml")) }).Parameter("STYLE", style); var result = _db.ExecuteSingle(QUERY, binder); return(result); }
/// <summary> /// This function returns a list of SKU cases. /// </summary> /// <returns></returns> public IEnumerable <SkuCase> GetSkuCaseList(string skuCaseId) { const string QUERY = @" SELECT S.CASE_ID AS CASE_ID, S.SHORT_DESCRIPTION AS SHORT_DESCRIPTION, S.EMPTY_WT AS EMPTY_WT, S.MAX_CONTENT_VOLUME AS MAX_CONTENT_VOLUME, S.UNAVAILABILITY_FLAG AS UNAVAILABILITY_FLAG, S.OUTER_CUBE_VOLUME AS OUTER_CUBE_VOLUME FROM <proxy />SKUCASE S WHERE 1 = 1 <if>AND UPPER(S.CASE_ID) = UPPER(:CASE_ID)</if> ORDER BY S.UNAVAILABILITY_FLAG NULLS FIRST, S.MAX_CONTENT_VOLUME DESC "; var binder = SqlBinder.Create(row => new SkuCase { CaseId = row.GetString("CASE_ID"), Description = row.GetString("SHORT_DESCRIPTION"), EmptyWeight = row.GetDecimal("EMPTY_WT"), IsAvailable = string.IsNullOrEmpty(row.GetString("UNAVAILABILITY_FLAG")), MaxContentVolume = row.GetDecimal("MAX_CONTENT_VOLUME"), OuterCubeVolume = row.GetDecimal("OUTER_CUBE_VOLUME") }).Parameter("CASE_ID", skuCaseId); return(_db.ExecuteReader(QUERY, binder)); }
public int CreateBol(string customerId, ICollection <Tuple <string, int, string> > poList) { const string QUERY = @" DECLARE LEDI_ID EDI_753_754.EDI_ID%TYPE; LBOL_COUNT NUMBER(3); BEGIN LEDI_ID := PKG_EDI_2.CREATE_EDI(ACUSTOMER_ID => :CUSTOMER_ID); PKG_EDI_2.ADD_PO_TO_EDI(AEDI_ID => LEDI_ID, APO_LIST => :PO_LIST, AITERATION_LIST => :ITERATION_LIST, ADC_LIST => :DC_LIST, AATS_DATE => SYSDATE); :LBOL_COUNT := PKG_EDI_2.CREATE_BOL_FOR_EDI(AEDI_ID => LEDI_ID); END;"; int bolCount = 0; var binder = SqlBinder.Create(); binder.ParameterAssociativeArray("PO_LIST", poList.Select(p => p.Item1).ToArray()); binder.ParameterAssociativeArray("ITERATION_LIST", poList.Select(p => (int?)p.Item2).ToArray()); binder.ParameterAssociativeArray("DC_LIST", poList.Select(p => p.Item3).ToArray()); binder.Parameter("CUSTOMER_ID", customerId); binder.OutParameter("LBOL_COUNT", val => bolCount = val.Value); _db.ExecuteNonQuery(QUERY, binder); return(bolCount); }
/// <summary> /// For Customer autocomplete /// </summary> /// <param name="term"></param> /// <returns></returns> public IEnumerable<Customer> CustomerAutoComplete(string term) { const string QUERY = @" SELECT CUST.CUSTOMER_ID AS CUSTOMER_ID, CUST.NAME AS CUSTOMER_NAME FROM <proxy />CUST CUST WHERE 1 = 1 <if c='$TERM'> AND (UPPER(CUST.CUSTOMER_ID) LIKE '%' || UPPER(:TERM) ||'%' OR UPPER(CUST.NAME) LIKE '%' || UPPER(:TERM) ||'%') </if> AND CUST.INACTIVE_FLAG IS NULL AND ROWNUM < 40 ORDER BY CUST.CUSTOMER_ID "; Contract.Assert(_db != null); var binder = SqlBinder.Create(row => new Customer { CustomerId = row.GetString("CUSTOMER_ID"), CustomerName = row.GetString("CUSTOMER_NAME") }).Parameter("TERM", term); return _db.ExecuteReader(QUERY, binder); }
public IEnumerable <CartonLocation> RetrieveCartonLocationsForPallet(string palletId) { if (string.IsNullOrEmpty(palletId)) { throw new ArgumentNullException("PalletId is null"); } const string QUERY = @" begin :result := <proxy />pkg_boxpick.get_pallet_carton_locations(apallet_id => :apallet_id); end; "; var binder = SqlBinder.Create(row => new CartonLocation { CartonLocationId = row.GetString("carton_location_id"), CartonStorageArea = row.GetString("carton_storage_area"), CountCartonsToPick = row.GetInteger("count_cartons_to_pick").Value, PiecesPerCarton = row.GetInteger("pieces_per_carton").Value, SkuToPick = new Sku { Style = row.GetString("style"), Color = row.GetString("color"), Dimension = row.GetString("dimension"), SkuSize = row.GetString("sku_size"), SkuId = row.GetInteger("sku_id").Value } }).Parameter("apallet_id", palletId) .OutRefCursorParameter("result"); return(_db.ExecuteReader(QUERY, binder)); }
/// <summary> /// Queries the passed text against the database. /// </summary> /// <param name="scan"></param> /// <returns></returns> internal IList <SearchQueryResult> ParseScan(string scan) { long intParam = 0; //var b = !scan.StartsWith("0") && int.TryParse(scan, out intParam); var isNumericScan = !scan.StartsWith("0") && long.TryParse(scan, out intParam); //var binder = new SqlBinder<ScanInfo>("Scan Type", true); var binder = SqlBinder.Create(row => new SearchQueryResult { RoutingId = row.GetInteger(0) ?? 0, Id = row.GetString(1), ScanDescription = row.GetString(2), PrimaryKey1 = row.GetString(3), PrimaryKey2 = row.GetString(4), }); binder.Parameter("search_text", scan); binder.Parameter("int_value", isNumericScan ? intParam : (long?)null); var queries = from routing in SearchRoutingList let isNumericQuery = System.Globalization.CultureInfo.InvariantCulture.CompareInfo.IndexOf(routing.SearchAttr.Query, ":int_value", CompareOptions.IgnoreCase) >= 0 where isNumericScan || !isNumericQuery select string.Format(routing.SearchAttr.Query, routing.SearchAttr.RoutingId); var queryText = string.Join(" UNION ALL ", queries); var results = _db.ExecuteReader(queryText, binder, 1000); return(results); // query.ToArray(); }
/// <summary> /// Update the info for the passed process /// </summary> /// <param name="info"></param> public void UpdateProcess(ReceivingProcess info) { const string QUERY = @" UPDATE <proxy />SRC_CARTON_PROCESS SCP SET SCP.PRO_NUMBER= :PRO_NUMBER, SCP.EXPECTED_CARTON = :EXPECTED_CARTON, SCP.PRO_DATE= :PRO_DATE, SCP.PALLET_LIMIT = :PALLET_LIMIT, SCP.CARRIER_ID= :CARRIER_ID, SCP.PRICE_SEASON_CODE = :PRICE_SEASON_CODE, SCP.RECEIVING_AREA_ID = :RECEIVING_AREA_ID, SCP.SPOT_CHECK_AREA_ID = :SPOT_CHECK_AREA_ID WHERE SCP.PROCESS_ID= :PROCESS_ID "; var binder = SqlBinder.Create() .Parameter("PRO_NUMBER", info.ProNumber) .Parameter("PROCESS_ID", info.ProcessId) .Parameter("EXPECTED_CARTON", info.ExpectedCartons) .Parameter("PALLET_LIMIT", info.PalletLimit) .Parameter("PRO_DATE", info.ProDate) .Parameter("CARRIER_ID", info.CarrierId) .Parameter("PRICE_SEASON_CODE", info.PriceSeasonCode) .Parameter("RECEIVING_AREA_ID", info.ReceivingAreaId) .Parameter("SPOT_CHECK_AREA_ID", info.SpotCheckAreaId); _db.ExecuteNonQuery(QUERY, binder); }
/// <summary> /// This function returns pallets history from box productivity table /// </summary> /// <param name="palletId"></param> /// <returns></returns> public IList <BoxPalletHistory> GetBoxPalletHistory(string palletId) { Contract.Assert(_db != null); const string QUERY = @" SELECT B.OPERATION_START_DATE AS OPERATION_START_DATE, B.OPERATOR AS OPERATOR, B.OUTCOME AS OUTCOME, B.MODULE_CODE AS MODULE_CODE, B.OPERATION_CODE AS OPERATION_CODE FROM <proxy />BOX_PRODUCTIVITY B WHERE B.TO_PALLET = :PALLET_ID OR B.FROM_PALLET = :PALLET_ID order by B.OPERATION_START_DATE desc "; var binder = SqlBinder.Create(row => new BoxPalletHistory { OperationStartDate = row.GetDate("OPERATION_START_DATE").Value, Operator = row.GetString("OPERATOR"), OutCome = row.GetString("OUTCOME"), ModuleCode = row.GetString("MODULE_CODE"), Operation = row.GetString("OPERATION_CODE") }).Parameter("PALLET_ID", palletId); return(_db.ExecuteReader(QUERY, binder)); }
/// <summary> /// This method disables the VAS configuration for passed customer and VAS, on basis of all/selective non-validated orders . /// </summary> /// <param name="customerId"></param> /// <param name="vasId"></param> /// <param name="regExp"> </param> /// <param name="currentOrdersOnly"> /// True: method will disable configuration from Current orders only. /// False: method will disable configuration from All orders excluding Current orders. /// Null: method will disable configuration from All orders. /// </param> internal void DisableVasConfiguration(string customerId, string vasId, string regExp, bool?currentOrdersOnly) { const string QUERY = @" BEGIN <if c= 'not($currentOrdersOnly) or $allOrders'> UPDATE <proxy />MASTER_CUSTOMER_VAS MCV SET MCV.INACTIVE_FLAG = 'Y' WHERE MCV.CUSTOMER_ID = :CUSTOMER_ID AND MCV.VAS_ID = :VAS_ID; </if> <if c= '$currentOrdersOnly or $allOrders'> DELETE <proxy />PS_VAS T WHERE T.PICKSLIP_ID IN (SELECT DISTINCT(P.PICKSLIP_ID) AS PICKSLIP_ID FROM <proxy />PS P INNER JOIN <proxy />MASTER_CUSTOMER_VAS MCV ON P.CUSTOMER_ID = MCV.CUSTOMER_ID WHERE MCV.CUSTOMER_ID = :CUSTOMER_ID AND MCV.VAS_ID = :VAS_ID AND P.TRANSFER_DATE IS NULL AND P.PICKSLIP_CANCEL_DATE IS NULL AND REGEXP_LIKE(P.PO_ID || '@' || P.LABEL_ID, NVL(:REGEX_PATTERN,'.'))); </if> END; "; var binder = SqlBinder.Create() .Parameter("CUSTOMER_ID", customerId) .Parameter("VAS_ID", vasId) .Parameter("REGEX_PATTERN", regExp); binder.ParameterXPath("currentOrdersOnly", currentOrdersOnly != null && currentOrdersOnly.Value); binder.ParameterXPath("allOrders", currentOrdersOnly == null); _db.ExecuteNonQuery(QUERY, binder); }
/// <summary> /// Based on the passed string, returns the context in which picking must happen /// </summary> /// <param name="scan">Must be either building or area</param> /// <returns></returns> public PickContext GetPickContext(string scan) { if (string.IsNullOrEmpty(scan)) { throw new ArgumentNullException("BuildingId is null"); } const string QUERY = @" SELECT TIA.INVENTORY_STORAGE_AREA AS INVENTORY_STORAGE_AREA, TIA.WAREHOUSE_LOCATION_ID AS BUILDING_ID, TIA.SHORT_NAME AS SHORT_NAME FROM <proxy />TAB_INVENTORY_AREA TIA WHERE TIA.SHORT_NAME = :SCAN UNION ALL SELECT NULL AS INVENTORY_STORAGE_AREA, TBL.WAREHOUSE_LOCATION_ID AS BUILDING_ID, NULL AS SHORT_NAME FROM <proxy />TAB_WAREHOUSE_LOCATION TBL WHERE TBL.WAREHOUSE_LOCATION_ID = :SCAN ORDER BY INVENTORY_STORAGE_AREA NULLS LAST "; var binder = SqlBinder.Create(row => new PickContext { BuildingId = row.GetString("BUILDING_ID"), SourceArea = row.GetString("INVENTORY_STORAGE_AREA"), SourceAreaShortName = row.GetString("SHORT_NAME") }).Parameter("SCAN", scan); return(_db.ExecuteSingle(QUERY, binder)); }