2 Replies Latest reply on Sep 9, 2015 6:21 AM by 2747866

    Sales Module - Major table details

    2747866

      Hi

       

      We are using  Oracle Sales Module (R12.1.3) . We are using this module  to create LEAD, Opportunity and make quote.   I would like to know the major table of this module and their  details . I tried from back end with ASM schema and able to identify few tables such as:

       

      1.   AS_SALES_LEADS
      2. AS_LEADS_ALL
      3. AS_SALES_LEAD_OPPORTUNITY

       

      I am getting the basic information. Lead Number, Opportunity Number.  But would like to know who converted  my LEAD to Opportunity .

       

      Thanks in advance.

        • 1. Re: Sales Module - Major table details
          SoundariyaKumar

          Hi,

               I guess quote creation is the process of converting the lead to opportunity, if yes following query might help you,

          SELECT aqh.quote_header_id, quote_name, quote_number, quote_version,fu.user_name,

                       aqs.status_code, al.description descr,al.lead_id

                  FROM aso_quote_headers_all aqh,

                       aso_quote_related_objects aqr,

                       as_leads_all al,

                       aso_quote_statuses_b aqs,

          fnd_user fu

                 WHERE 1 = 1

                   AND aqh.quote_header_id = aqr.quote_object_id

          and aqh.created_by=fu.user_id

                   AND aqr.object_id = al.lead_id

                   AND aqh.quote_status_id = aqs.quote_status_id

                   AND aqr.relationship_type_code = 'OPP_QUOTE'

                   AND aqh.max_version_flag = 'Y';

           

          Regards,

          SoundariyaKumar.R

          • 2. Re: Sales Module - Major table details
            2747866

            Hi Kumar,

             

            Thanks for your reply.

             

            For quote part we are using a CUSTOM application.So far i am using below query to get lead details and is working okay for me.

             

            Here is my query... And any idea about reports in this SALES module. !!!... Thanks  G Selvam

             

             

              SELECT a.sales_lead_id,

                     a.creation_date,

                     a.customer_id,

                     d.party_name,

                     c.opportunity_id,

                     b.user_id,

                     b.user_name,

                     a.description,

                     a.country

                FROM as_sales_leads a,

                     fnd_user b,

                     AS_SALES_LEAD_OPPORTUNITY c,

                     HZ_parties d

               WHERE     a.created_by = b.user_id

                     AND a.sales_lead_id = c.sales_lead_id(+)

                     AND a.customer_id = d.party_id

            ORDER BY a.creation_date