Friday, May 22, 2009

Slow moving Inventory report JD edwards


I create Slow moving Inventory report from F4111 - Cardex as following

CREATE proc pinvrep as
DROP TABLE JDE_RK.DBO.TEMPSALES4111
select ILITM,ILMCU,ILGLPT,IMSRP1,IMSRP2,IMSRP3,ILKCO,max(ILUKID)ILUKID INTO JDE_RK.DBO.TEMPSALES4111
from JDE_PRODUCTION.proddta.f4111
LEFT OUTER JOIN JDE_PRODUCTION.PRODDTA.F4101 on ILitm=imitm
--LEFT OUTER JOIN JDE_PRODUCTION.PRODDTA.F41021 on ILitm=liitm and ilmcu=limcu and ILGLPT=liglpt
where ildct='RI' and ildcto in ('SO','SA','SF','SR','SI')
GROUP BY ILITM,ILMCU,ILGLPT,IMSRP1,IMSRP2,IMSRP3,ILKCO
order BY ILITM,ILMCU,ILGLPT,IMSRP1,IMSRP2,IMSRP3,ILKCO

DROP TABLE JDE_RK.DBO.TEMPRECVS4111
select ILITM,ILMCU,ILGLPT,IMSRP1,IMSRP2,IMSRP3,ILKCO,max(ILUKID)ILUKID INTO JDE_RK.DBO.TEMPRECVS4111
from JDE_PRODUCTION.proddta.f4111
LEFT OUTER JOIN JDE_PRODUCTION.PRODDTA.F4101 on ILitm=imitm
--LEFT OUTER JOIN JDE_PRODUCTION.PRODDTA.F41021 on ILitm=liitm and ilmcu=limcu and ILGLPT=liglpt
where ildct='OV' and ildcto in ('O6','OW','OP','OH')
GROUP BY ILITM,ILMCU,ILGLPT,IMSRP1,IMSRP2,IMSRP3,ILKCO
order BY ILITM,ILMCU,ILGLPT,IMSRP1,IMSRP2,IMSRP3,ILKCO

--select count(*) from JDE_RK.DBO.TEMPSALES4111
drop table JDE_RK.DBO.TEMPSALES4111_1
select a.*, iltrqt,iltrdj into JDE_RK.DBO.TEMPSALES4111_1 from JDE_RK.DBO.TEMPSALES4111  a
left outer join  JDE_PRODUCTION.proddta.f4111 b on a.ilukid=b.ilukid
drop table JDE_RK.DBO.TEMPRECVS4111_1
select a.*,iltrqt,iltrdj  into JDE_RK.DBO.TEMPRECVS4111_1 from JDE_RK.DBO.TEMPRECVS4111  a
left outer join  JDE_PRODUCTION.proddta.f4111 b on a.ilukid=b.ilukid
--SELECT TOP 10 * FROM  JDE_RK.DBO.TEMPRECVS4111_1
       declare @y as varchar(10)
       declare @dy as varchar(10)       
       declare @date1 as datetime       
       declare @szupmj as varchar(10)
       declare @szupmjINT as int
       set @date1= getdate()
 set @y =(datepart(yy,@date1) - 1900) * 1000;
 set @dy = datepart(dy,@date1);
 set @szupmj = @y + cast(@dy as int);
        set @szupmjINT= cast(@szupmj as int)

DROP TABLE ZDEV.DBO.INV_REPORT

SELECT S.MCCO,LIITM,IMDSC1,LIMCU,LIGLPT,SUM(LIPQOH)LIPQOH,ISNULL(SUM(LIPQOH*B.COUNCS/10000),0)TOTCOST21, 
ISNULL(SUM(LIPQOH*C.COUNCS/10000),0)TOTCOST25,ISNULL(SUM(LIPQOH*D.COUNCS/10000),0)TOTCOST02,
R.IMSRP1,R.IMSRP2,R.IMSRP3,ISNULL(AVG(B.COUNCS)/10000,0) COST21,ISNULL(AVG(C.COUNCS)/10000,0) COST25,
ISNULL(AVG(D.COUNCS),0) COST02,
ISNULL(SUM(-E.ILTRQT),0) SALQTY,
--ISNULL(MAX(E.ILTRDJ),0) SALDAT,
DATEADD(dy, cast(MAX(E.ILTRDJ) as varchar(10)) % 1000, DATEADD(yy, cast(MAX(E.ILTRDJ) as varchar(10)) / 1000,-1)) SALDAT,
--@szupmjINT-ISNULL(MAX(E.ILTRDJ),0) SALDATAGA,
ISNULL(SUM(F.ILTRQT),0) RECQTY,
--ISNULL(MAX(F.ILTRDJ),0) RECDAT,
DATEADD(dy, cast(MAX(F.ILTRDJ) as varchar(10)) % 1000, DATEADD(yy, cast(MAX(F.ILTRDJ) as varchar(10)) / 1000,-1)) RECDAT
--@szupmjINT-ISNULL(MAX(F.ILTRDJ),0) RECDATAGA
into ZDEV.DBO.INV_REPORT
FROM JDE_PRODUCTION.PRODDTA.F41021 A
LEFT OUTER JOIN JDE_PRODUCTION.PRODDTA.F4105 B ON LIITM=B.COITM AND B.COMCU=LIMCU AND B.COLEDG='21'
LEFT OUTER JOIN JDE_PRODUCTION.PRODDTA.F4105 C ON LIITM=C.COITM AND C.COMCU=LIMCU AND C.COLEDG='25'
LEFT OUTER JOIN JDE_PRODUCTION.PRODDTA.F4105 D ON LIITM=D.COITM AND D.COMCU=LIMCU AND D.COLEDG='02'
LEFT OUTER JOIN JDE_PRODUCTION.PRODDTA.F4101 R on liitm=imitm
LEFT OUTER JOIN JDE_PRODUCTION.PRODDTA.F0006 S on limcu=S.mcmcu
LEFT OUTER JOIN JDE_RK.DBO.TEMPSALES4111_1 E on A.LIITM=E.ILITM AND A.LIMCU=E.ILMCU AND A.LIGLPT=E.ILGLPT  AND E.ILKCO=S.MCCO
LEFT OUTER JOIN JDE_RK.DBO.TEMPRECVS4111_1 F on A.LIITM=F.ILITM AND A.LIMCU=F.ILMCU AND A.LIGLPT=F.ILGLPT  AND F.ILKCO=S.MCCO
WHERE LIPQOH>0 GROUP BY LIITM,LIMCU,LIGLPT,R.IMSRP1,R.IMSRP2,R.IMSRP3,S.MCCO,IMDSC1

--select * from  ZDEV.INV_REPORT where saldat=0
--update ZDEV.INV_REPORT  set SALDATAGA=0 where saldat is null
--update ZDEV.INV_REPORT  set RECDATAGA=0 where RECDAT  is null
--SELECT *, ISNULL(DATEDIFF(dd,RECDAT,getdate()),0) RECDATAGA,ISNULL(DATEDIFF(dd,SALDAT,getdate()),0) SALDATAGA FROM ZDEV.INV_REPORT

 

GO

Wednesday, May 20, 2009

Date Subtraction sql-server

select DATEDIFF(dd,'01/01/2009','01/01/2008')
select DATEDIFF(dd,getdate(),RECDAT)

Monday, May 18, 2009

Apostrophe's and Quotation Marks in where condition Like in SQL Server

Apostrophe
's and Quotation Marks in where condition Like in SQL Server
put '' two double single quote

select

description,* from genres where description like '%''s %'

 

Saturday, May 16, 2009

Csv file import Bulk insert

BULK

INSERT Full_Affiliate_Feed_CJ

FROM 'C:\sites\Full_Affiliate_Feed_CJ.csv'

WITH

(

FIELDTERMINATOR = ',',

ROWTERMINATOR = '\n'

)

Friday, May 15, 2009

Create IDENTITY column in select into statement

SELECT IDENTITY(INT, 10, 1) AS id1,[Product URL] AS ProductURL, CITY, DESCRIPTION, EVENTDATE, EVENTDAY, EVENTMONTH, EVENTMONTH2,
EVENTYEAR, EVENTZTIME, GENRE, GENREGRANDPARENT, GENREPARENT, ID, IMAGE_URL, REGION, STATE, VENCONFIG,
 VENUE, VENUE_ID, ZIP, GenType.GENREType AS GenType into Full_Affiliate_Feed
FROM [Full_Affiliate_Feed_CJ]
LEFT outer JOIN GenType ON GENREGRANDPARENT=GenType.GenName

Thursday, May 14, 2009

How to reset an AutoNumber field value in Access

How to reset an AutoNumber field value in Access

Fixing RB batch Error in JD Edwards

select ryaap,rypost,RYPSTQ,* from proddta.f03b13 where rypyid=199829.0
select rzpost,rzpaap,* from proddta.f03b14 where rzicu=406690


If there is a out of balance amount find that amount in F03b14 file and make POST field "D"
and then try to post.




Tuesday, May 12, 2009

Amazon Product Advertising API, HMAC-SHA256 signature,asp.net, C# Working Code Sample

Amazon Product Advertising API, HMAC-SHA256 signature,asp.net 2.0, C# Working Code Sample
 
using System:
using System.Collections.Generic;
using System.Text;
using System.Net;
using System.IO;
using System.Xml;
using System.Xml.XPath;
using System.Collections.Generic;
using System.Web;
using System.Security.Cryptography;

public partial class _Default : System.Web.UI.Page
{
    private const string MY_AWS_ACCESS_KEY_ID = "xxxxxxxxxxx";
    private const string MY_AWS_SECRET_KEY = "asasasasasasasasasasasasasasasasas";
    private const string DESTINATION = "ecs.amazonaws.com";

    private const string NAMESPACE = "http://webservices.amazon.com/AWSECommerceService/2009-03-31";
    private const string ITEM_ID = "B000ERVMI8";


    private string endPoint;
    private string akid;
    private byte[] secret;
    private HMAC signer;

    private const string REQUEST_URI = "/onca/xml";
    private const string REQUEST_METHOD = "GET";


    protected void Page_Load(object sender, EventArgs e)
    {
       // SignedRequestHelper helper = new SignedRequestHelper(MY_AWS_ACCESS_KEY_ID, MY_AWS_SECRET_KEY, DESTINATION);

        SignedRequestHelper(MY_AWS_ACCESS_KEY_ID, MY_AWS_SECRET_KEY, DESTINATION);

        string requestString = "Service=AWSECommerceService&Version=2009-03-31&Operation=ItemLookup&ResponseGroup=Small&ItemId=" + ITEM_ID;
        string requestUrl = Sign(requestString);
        string title = FetchTitle(requestUrl);
        Response.Write("Method 2: Query String form.");
        Response.Write("Title is \"" + title + "\"");
        //System.Console.WriteLine();

        //System.Console.WriteLine("Hit Enter to end");
        //System.Console.ReadLine();
    }
    private static string FetchTitle(string url)
    {
        try
        {
            WebRequest request = HttpWebRequest.Create(url);
            WebResponse response = request.GetResponse();
            XmlDocument doc = new XmlDocument();
            doc.Load(response.GetResponseStream());
            XmlNode titleNode = doc.GetElementsByTagName("Title", NAMESPACE).Item(0);
            string title = titleNode.InnerText;
            return title;
        }
        catch (Exception e)
        {
            System.Console.WriteLine("Caught Exception: " + e.Message);
            System.Console.WriteLine("Stack Trace: " + e.StackTrace);
           
        }

        return null;
    }

     public void SignedRequestHelper(string awsAccessKeyId, string awsSecretKey, string destination)
        {
            this.endPoint = destination.ToLower();
            this.akid = awsAccessKeyId;
            this.secret = Encoding.UTF8.GetBytes(awsSecretKey);
            this.signer = new HMACSHA256(this.secret);
        }

        public string Sign(IDictionary<string, string> request)
        {
            ParamComparer pc = new ParamComparer();
            SortedDictionary<string, string> sortedMap = new SortedDictionary<string, string>(request, pc);

            sortedMap["AWSAccessKeyId"] = this.akid;
            sortedMap["Timestamp"] = this.GetTimestamp();

            // Get the canonical query string
            string canonicalQS = this.ConstructCanonicalQueryString(sortedMap);

            // Derive the bytes needs to be signed.
            StringBuilder builder = new StringBuilder();
            builder.Append(REQUEST_METHOD)
                .Append("\n")
                .Append(this.endPoint)
                .Append("\n")
                .Append(REQUEST_URI)
                .Append("\n")
                .Append(canonicalQS);

            string stringToSign = builder.ToString();
            byte[] toSign = Encoding.UTF8.GetBytes(stringToSign);
            // Compute the signature and convert to Base64.
            byte[] sigBytes = signer.ComputeHash(toSign);
            string signature = Convert.ToBase64String(sigBytes);
            // now construct the complete URL and return to caller.
            StringBuilder qsBuilder = new StringBuilder();
            qsBuilder.Append("http://")
                .Append(this.endPoint)
                .Append(REQUEST_URI)
                .Append("?")
                .Append(canonicalQS)
                .Append("&Signature=")
                .Append(this.PercentEncodeRfc3986(signature));
            return qsBuilder.ToString();
        }
        public string Sign(string queryString)
        {
            IDictionary<string, string> request = this.CreateDictionary(queryString);
            return this.Sign(request);
        }

        private string GetTimestamp()
        {
            DateTime currentTime = DateTime.UtcNow;
            string timestamp = currentTime.ToString("yyyy-MM-ddTHH:mm:ssZ");
            return timestamp;
        }
        private string PercentEncodeRfc3986(string str)
        {
            str = HttpUtility.UrlEncode(str, System.Text.Encoding.UTF8);
            str.Replace("'", "%27").Replace("(", "%28").Replace(")", "%29").Replace("*", "%2A").Replace("!", "%21").Replace("%7e", "~");

            StringBuilder sbuilder = new StringBuilder(str);
            for (int i = 0; i < sbuilder.Length; i++)
            {
                if (sbuilder[i] == '%')
                {
                    if (Char.IsDigit(sbuilder[i + 1]) && Char.IsLetter(sbuilder[i + 2]))
                    {
                        sbuilder[i + 2] = Char.ToUpper(sbuilder[i + 2]);
                    }
                }
            }
            return sbuilder.ToString();
        }

        private IDictionary<string, string> CreateDictionary(string queryString)
        {
            Dictionary<string, string> map = new Dictionary<string, string>();

            string[] requestParams = queryString.Split('&');

            for (int i = 0; i < requestParams.Length; i++)
            {
                if (requestParams[i].Length < 1)
                {
                    continue;
                }

                char[] sep = { '=' };
                string[] param = requestParams[i].Split(sep, 2);
                for (int j = 0; j < param.Length; j++)
                {
                    param[j] = HttpUtility.UrlDecode(param[j], System.Text.Encoding.UTF8);
                }
                switch (param.Length)
                {
                    case 1:
                        {
                            if (requestParams[i].Length >= 1)
                            {
                                if (requestParams[i].ToCharArray()[0] == '=')
                                {
                                    map[""] = param[0];
                                }
                                else
                                {
                                    map[param[0]] = "";
                                }
                            }
                            break;
                        }
                    case 2:
                        {
                            if (!string.IsNullOrEmpty(param[0]))
                            {
                                map[param[0]] = param[1];
                            }
                        }
                        break;
                }
            }

            return map;
        }

        private string ConstructCanonicalQueryString(SortedDictionary<string, string> sortedParamMap)
        {
            StringBuilder builder = new StringBuilder();

            if (sortedParamMap.Count == 0)
            {
                builder.Append("");
                return builder.ToString();
            }

            foreach (KeyValuePair<string, string> kvp in sortedParamMap)
            {

                builder.Append(this.PercentEncodeRfc3986(kvp.Key));
                builder.Append("=");
                builder.Append(this.PercentEncodeRfc3986(kvp.Value));
                builder.Append("&");
            }
            string canonicalString = builder.ToString();
            canonicalString = canonicalString.Substring(0, canonicalString.Length - 1);
            return canonicalString;
        }
    }

class ParamComparer : IComparer<string>
{
    public int Compare(string p1, string p2)
    {
        return string.CompareOrdinal(p1, p2);
    }
}

Friday, May 8, 2009

Image background for a link button asp.net 2.0

Image background for a link button asp.net

put in css
.linkNext

{

background-image:url('images/next.gif');

}

<asp:LinkButton CssClass="linkNext" ID="LinkButton2" runat="server">LinkButton</asp:LinkButton>