How to get a computer by computer view of installed software using the MAP toolkit

November 23, 2014

As an Administrator maintaining Windows domains, one of the herculean tasks usually one run into is to make a software asset inventory. There are plenty of excellent software to do the job for you, obviously for some cost.

Here we are suggesting you a cheaper alternative, using Microsoft’s own MAP toolkit. Be ready to sweat a bit, and we are sure you would love the outcome.

The entire write up is copied from Microsoft blog and tested by us for assuring, if you follow the instructions as given, within few hours of time you will have a neat software inventory list.

The original link is here

One of the most frequent questions we get at MAPFDBK@microsoft.com is how to get a list of the software discovered by the MAP toolkit on a computer by computer basis.  Most of the users who ask are using this to help them answer a licensing question but it can be used in a number of other scenarios as well for example Software Asset Management or user profiling for VDI (see http://blogs.technet.com/b/mapblog/archive/2012/07/09/planning-for-desktop-virtualization-with-the-map-toolkit-7-0-4-of-4.aspx).

In MAP 7.0, provided this information through a database view and Microsoft Excel.  The name of the view is InstalledProducts_view.

In MAP 8.0, this view has been renamed to [UT_WinServer_Reporting].[InstalledProductsView].

This view contains several key pieces of information that you can use to do a number of things including:

  • Understand what applications and versions are installed throughout your organization
  • See the Operating Systems on which these apps are running and whether the machine is physical or virtual
  • See who is using the machines on which the apps are running
  • Get important license related information such as processor counts, total cores and logical processor counts

To get started, you will need to open Excel and connect to your local SQL Server database that is storing the MAP data that you want to view.  There are two different ways to connect, depending on the version of SQL Server that you are using.

Using your own SQL Server instance

If you are using your own instance (the non-default MAP install), you will select the Data option on the Excel ribbon and select the ‘From other sources’ option.  Then select ‘From SQL Server’.

image_thumb6

Enter your server name and instance name and click ‘Next’.

image_thumb5

Select the database that contains the data you want and then pick InstalledProducts_view row under ‘Name’ for databases created with MAP 7.0.

For MAP 8.0, use [UT_WinServer_Reporting].[InstalledProductsView].

image_thumb7

You can also add some additional information to help describe the connection.  Then click ‘Finish’ and select the location where you want the query results to populate.

image_thumb9

Using the default (LocalDB) instance

In MAP 7.0, the default database installed moved to SQL Server 2012 LocalDB.  There are a couple of steps that are different than those used in other versions of SQL Server.

First, make sure that you have the SQL Server 2012 Native Client installed.  You can get it from

http://www.microsoft.com/en-us/download/details.aspx?id=29065.

With Excel open and the Data ribbon highlighted, select the ‘From other data sources’ option and select ‘From Data Connection Wizard’

image_thumb12

Select the ‘Other/Advanced’ option.

image_thumb13

Then select the option for SQL Server Native Client 11.0 as highlighted below.  If this option is not available, make sure that you have the native client installed – http://www.microsoft.com/en-us/download/details.aspx?id=29065.

image_thumb15

Next, you enter in the server name.  If you are using the default install the server name will be: (localdb)\maptoolkit.

Set the option in #2 to Use Windows NT Integrated Security

Hit ‘Test Connection’

image_thumb18

If you’ve done it correctly, you will get a success message!

image_thumb21

Then follow the same steps as above where you select the database name and the InstalledProducts_view for 7.0.  For 8.0, use [UT_WinServer_Reporting].[InstalledProductsView].

Populate the results in your spreadsheet!

What do I do next?

Well – that is entirely up to you.  One thing that we like to do is to create a pivot table and drill down into this information. Here is one that I created.  I filtered down the application name to include only those that had SQL Server components.  I could look at this by physical/virtual and by operating system.

Pretty cool – huh!

image_thumb23

As a reminder, here is a link to some valuable MAP community supported content.

Enjoy!


Oracle – a simple function to format numbers for display

February 16, 2014

We have requirements to produce Payment Requests through Oracle E-Business suite custom interface, where the users are allowed to select different currencies based on the payment.

Different currencies means different precisions, US $ 2, Indian Rupees 2, while Kuwait, Bahrain have 3 precisions to maintain…

So the amounts displayed on reports needed to be formatted properly and we were doing some hardcoded formatting like

Now we have more currencies to deal with, hence came up with following simple function, which accepts the amount, precisions as inputs and returns a VARCHAR2 string as formatted number!

Function

CREATE OR REPLACE FUNCTION xx_format_number (P_AMOUNT IN NUMBER,
P_DEC_PREC IN NUMBER)
RETURN VARCHAR2
IS
frm_1 VARCHAR2 (30) := 'fm999999999990.';
new_num VARCHAR2 (30);
BEGIN
SELECT TO_CHAR (P_AMOUNT,
RPAD (frm_1, 15 + P_DEC_PREC, '90000000000000000'))
INTO new_num
FROM DUAL;

RETURN (new_num);

END;

You can check the function like following

SET SERVEROUTPUT ON;

DECLARE
l_new_num VARCHAR2 (30);
BEGIN

l_new_num := xx_format_number(.009,3);

DBMS_OUTPUT.PUT_LINE (l_new_num);
END;

Check it out, and let us know whether it worked for you!

for Windows7bugs

admin


Oracle Application R12, 7 Buckets Supplier Aging SQL

March 6, 2013

 

Select supplier_number, vendor_name,
sum(amount_remaining) amount_remaining,
sum(b0) "<=30",
sum(b1) "31-60",
sum(b2) "61-90",
sum(b3) "91-180",
sum(b4) "181-270",
sum(b5) "271-360",
sum(b6) ">360"
FROM(
SELECT i.invoice_date,
         round(nvl(ps.amount_remaining,0)*nvl(i.exchange_rate, 1),3) amount_remaining,
         i.vendor_id, supp.vendor_name, supp.segment1 supplier_number,
         CASE
         when trunc(trunc(sysdate))-i.invoice_date <= 30 then
         NVL(round(nvl(ps.amount_remaining,0)*nvl(i.exchange_rate, 1),3),0) 
         END b0
         ,
          CASE
         when trunc(sysdate)-i.invoice_date BETWEEN 31 AND 60 then
         NVL(round(nvl(ps.amount_remaining,0)*nvl(i.exchange_rate, 1),3),0) 
         END b1,
             CASE
         when trunc(sysdate)-i.invoice_date BETWEEN 61 AND 90 then
         NVL(round(nvl(ps.amount_remaining,0)*nvl(i.exchange_rate, 1),3),0) 
         END b2,  
             CASE
         when trunc(sysdate)-i.invoice_date BETWEEN 91 AND 180 then
         NVL(round(nvl(ps.amount_remaining,0)*nvl(i.exchange_rate, 1),3),0) 
         END b3  ,   
             CASE
         when trunc(sysdate)-i.invoice_date BETWEEN 181 AND 270 then
         NVL(round(nvl(ps.amount_remaining,0)*nvl(i.exchange_rate, 1),3),0) 
         END b4  
          ,   
             CASE
         when trunc(sysdate)-i.invoice_date BETWEEN 271 AND 360 then
         NVL(round(nvl(ps.amount_remaining,0)*nvl(i.exchange_rate, 1),3),0) 
         END b5     ,   
             CASE
         when trunc(sysdate)-i.invoice_date > 360 then
         NVL(round(nvl(ps.amount_remaining,0)*nvl(i.exchange_rate, 1),3),0) 
         END b6    
             FROM ap_payment_schedules ps, ap_invoices i, ap_suppliers supp
   WHERE     i.invoice_id = ps.invoice_id
         AND ps.org_id = :P_ORG_ID -- Security takes care of this part, only for other reporting reqs
         AND i.vendor_id = supp.vendor_id
         AND i.cancelled_date IS NULL
         AND ps.amount_remaining <> 0
         )
GROUP BY   supplier_number, vendor_name       
ORDER BY  vendor_name

And as usual, the main view is a protected repository, hence you have to initialize the security part in order to fetch data

begin
MO_GLOBAL.SET_POLICY_CONTEXT('S',:P_ORG_ID);
end ;

begin
--fnd_global.apps_initialize(:P_USER_ID,:P_RESP_ID,:P_RESP_APPL_ID);
fnd_global.apps_initialize(1353, 50854, 200);
end;


begin
MO_GLOBAL.INIT('SQLAP'); --Payables
--MO_GLOBAL.INIT('PO');
end;

Now enjoy another quality stuff from us :)

PS for Kuwait requirements, we have rounded the figures to 3 digits, alter the script to suite your reporting requirements.

regards,

admin


Oracle ERP 12.0.6, Customer Aging SQL Query

March 3, 2013

 

Update:07th March 2013

A much simpler script

Select customer_id, customer_number,customer_name,
overall_credit_limit, currency_code,
customer_type,
 Balance,
cr_balance,
 &quot;&lt;=30&quot;,
 &quot;31-60&quot;,
 &quot;61-90&quot;,
 &quot;91-180&quot;,
 &quot;181-270&quot;,
&quot;271-360&quot;,
&quot;&gt;360&quot;
from(
Select customer_id, customer_number,customer_name,
xx_customer_credit_limit_f(:P_ORG_ID,a.customer_id) overall_credit_limit, NULL currency_code,
decode(a.CUSTOMER_TYPE,'R','Ex','I','In') customer_type,
nvl(omscustbalance_f (a.customer_id, :P_ORG_ID ,0,0,0),0) Balance,
0 cr_balance,
omscustbalance_f (a.customer_id, :P_ORG_ID,1,0,0) &quot;&lt;=30&quot;,
omscustbalance_f (a.customer_id, :P_ORG_ID,2,31,60) &quot;31-60&quot;,
omscustbalance_f (a.customer_id, :P_ORG_ID,3,61,90) &quot;61-90&quot;,
omscustbalance_f (a.customer_id, :P_ORG_ID,4,91,180) &quot;91-180&quot;,
omscustbalance_f (a.customer_id, :P_ORG_ID,5,181,270) &quot;181-270&quot;,
omscustbalance_f (a.customer_id, :P_ORG_ID,6,271,360) &quot;271-360&quot;,
omscustbalance_f (a.customer_id, :P_ORG_ID,7,0,0) &quot;&gt;360&quot;
from ar_customers a
where 
1=1
and a.customer_id = NVL(:P_CUSTOMER_ID, a.customer_id)
and decode(a.CUSTOMER_TYPE,'R','Ex','I','In') = NVL(:P_CUST_TYPE, decode(a.CUSTOMER_TYPE,'R','Ex','I','In') )
and customer_id IN (Select customer_id from ar_payment_schedules_v)
and a.status = 'A'
)
where balance &lt;&gt; 0
ORDER BY 3

Oracle says they have the best business practices :P, however our “Accountant” geeks believe the opposite.

They are not happy with the aging buckets, provided as standard and recently IT was challenged with a requirement to produce some output which will a total of 7 buckets!!!

Hence here it is, the script

 

SELECT  su.org_id organization_id, acct.cust_account_id customer_id, acct.account_number customer_number,  PARTY.PARTY_NAME customer_name, overall_credit_limit,cl.currency_code,
decode(acct.CUSTOMER_TYPE,'R','Ex','I','In') customer_type,
omscustbalance_f (acct.cust_account_id, su.org_id,0,0,0) Balance,
(nvl(overall_credit_limit,0)-nvl(omscustbalance_f (acct.cust_account_id, su.org_id,0,0,0),0)) cr_balance,
omscustbalance_f (acct.cust_account_id, su.org_id,1,0,0) &quot;&lt;=30&quot;,
omscustbalance_f (acct.cust_account_id, su.org_id,2,31,60) &quot;31-60&quot;,
omscustbalance_f (acct.cust_account_id, su.org_id,3,61,90) &quot;61-90&quot;,
omscustbalance_f (acct.cust_account_id, su.org_id,4,91,180) &quot;91-180&quot;,
omscustbalance_f (acct.cust_account_id, su.org_id,5,181,270) &quot;181-270&quot;,
omscustbalance_f (acct.cust_account_id, su.org_id,6,271,360) &quot;271-360&quot;,
omscustbalance_f (acct.cust_account_id, su.org_id,7,0,0) &quot;&gt;360&quot;
       FROM   hz_cust_profile_amts cl,
            hz_cust_site_uses_all su,
            hz_cust_accounts acct,
            HZ_PARTIES PARTY,
            OMS_GL_ORG_HOOKUP_V ood
    WHERE       su.org_id = :P_ORG_ID
    and ood.organization_id = su.org_id
            AND acct.cust_account_id = NVL(:P_CUSTOMER_ID, acct.cust_account_id)
            AND party.party_id = acct.party_id
            AND cl.cust_account_id = acct.cust_account_id
            AND cl.site_use_id = su.site_use_id
            AND cl.currency_code = ood.currency_code --'KWD'
            AND su.status = 'A'
order by su.org_id, PARTY.PARTY_NAME

With this query we are referring couple of custom objects like

omscustbalance_f – function calculating the outstanding balance

&

a custom view OMS_GL_ORG_HOOKUP_V, which links both ORG_ORGANIZATION_DEFINITIONS table and GL ledger information

Code for omscustbalance_f

CREATE OR REPLACE FUNCTION APPS.omscustbalance_f (cust_id         IN NUMBER,
                                                  p_org_id        IN NUMBER,
                                                  bucket_number   IN NUMBER,
                                                  AGE1            IN NUMBER,
                                                  AGE2            IN NUMBER)
   RETURN NUMBER
AS
   total_due    NUMBER := 0;
   att_string   VARCHAR2 (2000);
BEGIN
   --1 &lt;= 30
   --2 31-60
   --3 61-90
   --4 91-180
   --5 181-270
   --6 271-360
   --7 &gt;360
   --0 balance

   IF bucket_number = 0
   THEN
      SELECT SUM (b.amount_due_remaining)
        INTO total_due
        FROM ar_payment_schedules_v b
       WHERE     b.customer_id = cust_id
             AND b.AL_STATUS_MEANING = 'Open'
             AND b.cust_trx_type_id IN (SELECT cust_trx_type_id trx_type
                                          FROM xxksalestypes
                                         WHERE org_id = p_org_id
                                        UNION ALL
                                        SELECT credit_memo_type_id trx_type
                                          FROM xxksalestypes
                                         WHERE org_id = p_org_id)
             AND b.org_id = p_org_id;
   ELSIF bucket_number = 1
   THEN
      SELECT SUM (b.amount_due_remaining)
        INTO total_due
        FROM ar_payment_schedules_v b
       WHERE     b.customer_id = cust_id
             AND b.AL_STATUS_MEANING = 'Open'
             AND b.cust_trx_type_id IN (SELECT cust_trx_type_id trx_type
                                          FROM xxksalestypes
                                         WHERE org_id = p_org_id
                                        UNION ALL
                                        SELECT credit_memo_type_id trx_type
                                          FROM xxksalestypes
                                         WHERE org_id = p_org_id)
             AND b.org_id = p_org_id
             AND b.days_past_due &lt;= 30;
   ELSIF bucket_number = 7
   THEN
      SELECT SUM (b.amount_due_remaining)
        INTO total_due
        FROM ar_payment_schedules_v b
       WHERE     b.customer_id = cust_id
             AND b.AL_STATUS_MEANING = 'Open'
             AND b.cust_trx_type_id IN (SELECT cust_trx_type_id trx_type
                                          FROM xxksalestypes
                                         WHERE org_id = p_org_id
                                        UNION ALL
                                        SELECT credit_memo_type_id trx_type
                                          FROM xxksalestypes
                                         WHERE org_id = p_org_id)
             AND b.org_id = p_org_id
             AND b.days_past_due &gt; 360;
   ELSE
      SELECT SUM (b.amount_due_remaining)
        INTO total_due
        FROM ar_payment_schedules_v b
       WHERE     b.customer_id = cust_id
             AND b.AL_STATUS_MEANING = 'Open'
             AND 
--you may avoid the following lines---custom requirement---
b.cust_trx_type_id IN (SELECT cust_trx_type_id trx_type
                                          FROM xxksalestypes
                                         WHERE org_id = p_org_id
                                        UNION ALL
                                        SELECT credit_memo_type_id trx_type
                                          FROM xxksalestypes
                                         WHERE org_id = p_org_id)
--you may avoid the code until here---custom requirement---
             AND b.org_id = p_org_id
             AND b.days_past_due BETWEEN AGE1 AND AGE2;
   END IF;


   RETURN TOTAL_DUE;
END omscustbalance_f;
/

and finally the script for view “OMS_GL_ORG_HOOKUP_V”

 

DROP VIEW APPS.OMS_GL_ORG_HOOKUP_V;

/* Formatted on 3/3/2013 8:55:56 AM (QP5 v5.163.1008.3004) */
CREATE OR REPLACE FORCE VIEW APPS.OMS_GL_ORG_HOOKUP_V
(
   LEDGER_ID,
   NAME,
   CURRENCY_CODE,
   ORGANIZATION_NAME,
   ORGANIZATION_ID,
   ORGANIZATION_CODE,
   OPERATING_UNIT
)
AS
     SELECT gll.ledger_id,
            gll.name,
            gll.currency_code,
            ood.organization_name,
            ood.organization_id,
            ood.organization_code,
            ood.operating_unit
       FROM GL_LEDGERS gll, org_organization_definitions ood
      WHERE ood.set_of_books_id = gll.ledger_id
   ORDER BY 1;

 

Are you done yet? Not really. If you have to run the query from a PL/SQL node, you have to initialize the security using the following

Begin
begin
MO_GLOBAL.SET_POLICY_CONTEXT('S',:P_ORG_ID);
end ;

begin
--fnd_global.apps_initialize(:P_USER_ID,:P_RESP_ID,:P_RESP_APPL_ID);
fnd_global.apps_initialize(1353, 50595, 222);
end;


begin
MO_GLOBAL.INIT('AR'); -- Receivables
end;

return (TRUE);
end;

Enjoy another quality solution from us!

regards,

admin


Quest toad or Oracle Sql Developer?

November 9, 2011

There were times when Quest Toad used to desert us with plain statements like “There was an error” especially while compiling procedures or funcations @ database level. We assume it was mostly because of the older versions of toad being used against latest versions of Oracle database. Well usually no firms want to upgrade to latest versions of Toad, as it involves the biting factor, money!

So, we started using Oracle’s own sql developer. The look and feel of Oracle’s SQL developer is so so, nothing compared to the neat and organized look of Quest toad, at the same time it is the most affordable tool for a developer, as it costs nothing for one to download and start using!
Not just that, if you have the latest version of Sql Developer, it does show you the exact error messages under almost all possible scenarios.
A quick post from a very satisfied Oracle’s SQL Developer suite!

for Windows7bugs,
admin