16 Replies Latest reply: Sep 15, 2016 4:55 AM by Omotayo Olokede RSS

    Aging Report

    Omotayo Olokede

      Below is my attchment @sunny t

        • Re: Aging Report
          Sunny Talwar

          I guess don't create new threads to post samples (Report on Aging Item in Stock). Look here to learn how to add samples to existing threads

          Uploading a Sample

            • Re: Aging Report
              Omotayo Olokede

              still returning an empty table.

              What else can i do

                • Re: Aging Report
                  Sunny Talwar

                  Can you try to run the attached application?

                    • Re: Aging Report
                      Sunny Talwar

                      It should give you something like this

                       

                      Capture.PNG

                        • Re: Aging Report
                          Omotayo Olokede

                          woow. This is just the result i wanted. If you are getting this with the same script i sent to you, then what do u think is wrong with my script because i am not getting anything like you.

                           

                          Please i need your Assistance on this Sunny T

                            • Re: Aging Report
                              Sunny Talwar

                              Open this link to see this attachment:

                              Re: Aging Report

                                • Re: Aging Report
                                  Omotayo Olokede

                                  Good Day,

                                   

                                  @SunnyT what do we do now?

                                  i cant view the project.i will need you to please enlighten me more cos i dont knwo what to do again

                                    • Re: Aging Report
                                      Sunny Talwar

                                      Script:

                                       

                                      SET toDate = TO_DATE('31/DEC/2020','dd/mon/yyyy');

                                      SET subsidiaries = (1);

                                       

                                      OLEDB CONNECT32 TO [Provider=OraOLEDB.Oracle.1;Persist Security Info=True;User ID=reportuser;Data Source=rproods;Extended Properties=""] (XPassword is LTWLEZVMQbbEHaZE);

                                       

                                      LOAD *,

                                        If(Days < 30, Dual('<30', 1),

                                        If(Days < 60, Dual('>=30<60', 2),

                                        If(Days < 90, Dual('>=60<90', 3),

                                        If(Days < 120, Dual('>=90<120', 4), Dual('>=120', 5))))) as DayRange;

                                      LOAD *,

                                        Today() - LAST_RCVD_DATE as Days;

                                      SQL SELECT

                                        TO_CHAR(n.ITEM_SID) AS "ITEM_SID",

                                        n.ITEM_NO AS "ITEM_NO",

                                        n.SBS_NO AS "SBS_NO",

                                          n.ACTIVE AS "ITEM_ACTIVE",

                                        n.VEND_CODE AS "VEND_CODE",

                                        n.DCS_CODE AS "DCS_CODE",

                                        n.DESCRIPTION1 AS "ITEM_DESCRIPTION1",

                                        n.DESCRIPTION2 AS "ITEM_DESCRIPTION2",

                                        n.DESCRIPTION3 AS "ITEM_DESCRIPTION3",

                                        n.DESCRIPTION4 AS "ITEM_DESCRIPTION4",

                                        n.ATTR AS "ITEM_ATTR",

                                        n.SIZ AS "ITEM_SIZ",

                                        n.ALU AS "ITEM_ALU",

                                        u.UPC AS "ITEM_UPC",

                                        n.UDF1_DATE AS "UDF1_DATE",

                                        n.UDF2_VALUE AS "UDF2_VALUE",

                                        v.VEND_NAME AS "VEND_NAME",

                                        v.FIRST_NAME AS "VEND_FIRST_NAME",

                                        v.LAST_NAME AS "VEND_LAST_NAME",

                                        SUBSTR(d.DCS_CODE,1,3) AS "D_CODE",

                                        SUBSTR(d.DCS_CODE,4,3) AS "C_CODE",

                                        SUBSTR(d.DCS_CODE,7,3) AS "S_CODE",

                                        d.D_NAME AS "D_NAME",

                                        d.C_NAME AS "C_NAME",

                                        d.S_NAME AS "S_NAME",

                                        d.D_LONG_NAME AS "D_LONG_NAME",

                                        d.C_LONG_NAME AS "C_LONG_NAME",

                                        d.S_LONG_NAME AS "S_LONG_NAME",

                                        d.ACTIVE AS "DCS_ACTIVE",

                                        n.COST AS "ITEM_COST",

                                        TO_CHAR(n.FST_RCVD_DATE, 'DD-MM-YYYY') AS "FIRST_RCVD_DATE",

                                        TO_CHAR(n.FST_RCVD_DATE, 'YYYY') AS "FIRST_RCVD_YEAR",

                                        TO_CHAR(n.FST_RCVD_DATE, 'MM') AS "FIRST_RCVD_MONTH",

                                        TO_CHAR(n.FST_RCVD_DATE, 'DD') AS "FIRST_RCVD_DAY",

                                        TO_CHAR(n.LST_RCVD_DATE, 'DD-MM-YYYY') AS "LAST_RCVD_DATE",

                                        TO_CHAR(n.LST_RCVD_DATE, 'YYYY') AS "LAST_RCVD_YEAR",

                                        TO_CHAR(n.LST_RCVD_DATE, 'MM') AS "LAST_RCVD_MONTH",

                                        TO_CHAR(n.LST_RCVD_DATE, 'DD') AS "LAST_RCVD_DAY"

                                      FROM INVN_SBS n

                                      LEFT JOIN INVENTORY u ON n.ITEM_SID = u.ITEM_SID

                                      LEFT JOIN VENDOR v ON v.VEND_CODE = n.VEND_CODE AND v.SBS_NO = n.SBS_NO

                                      LEFT JOIN DCS d ON d.DCS_CODE = n.DCS_CODE AND d.SBS_NO = n.SBS_NO

                                      WHERE n.SBS_NO in $(subsidiaries);

                                        • Re: Aging Report
                                          Omotayo Olokede

                                          Ohhh thank you, my bad. Error from me. DaysRange showing but its only showing DaysRange which is just >=120, nothing is displaying in the Days itself. knowing fully well that thats what will be used to calculate the Correct Days Range

                                            • Re: Aging Report
                                              Omotayo Olokede

                                              res.PNG

                                              This is my Result now. i believe something is still wrong

                                                • Re: Aging Report
                                                  Sunny Talwar

                                                  I think the date isn't read correctly is the problem here:

                                                   

                                                  Try this in your variable overview:

                                                  SET ThousandSep=',';

                                                  SET DecimalSep='.';

                                                  SET MoneyThousandSep=',';

                                                  SET MoneyDecimalSep='.';

                                                  SET MoneyFormat='$#,##0.00;($#,##0.00)';

                                                  SET TimeFormat='h:mm:ss TT';

                                                  SET DateFormat='DD-MM-YYYY';

                                                  SET TimestampFormat='DD-MM-YYYY h:mm:ss[.fff] TT';

                                                  SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

                                                  SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

                                                  SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

                                                  SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

                                                  SET FirstWeekDay=6;

                                                  SET BrokenWeeks=1;

                                                  SET ReferenceDay=0;

                                                  SET FirstMonthOfYear=1;

                                                  SET CollationLocale='en-US';

                                      • Re: Aging Report
                                        Omotayo Olokede

                                        i tried opening it but its telling me i have opened the project several time. Which is not giving me chance to open it. dont know what to do again.

                                         

                                        can you help me send the full script u used and screen shot me the output again.

                                         

                                        Thank you

                                • Re: Aging Report
                                  Omotayo Olokede

                                  Good Morning,

                                  its not giving me anything and of which i cannot see the attachment u were talking about

                                  Below is what i am getting nothing in the Output.

                                  can u share the script u edited may be i am getting something wrong.

                                   

                                  And i even want a report that will Contain the following;

                                  1. Item name

                                  2. Size

                                  3. Attribute

                                  4. if item falls into any of these Aging Condition it should return the On hand quantity left

                                   

                                  Any luck please

                                  aging.png