APPS.BIL_TX_OPTY_AGE_RPT_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 20

    l_outer_select              VARCHAR2(4000);

Line: 21

    l_outer_select0              VARCHAR2(4000);

Line: 22

    l_select_1                    VARCHAR2(15000);

Line: 24

    l_insert_stmnt              VARCHAR2(4000);

Line: 109

       l_select_1        := NULL;

Line: 204

OPPORTUNITY_LAST_UPDATE_DATE BIL_TX_MEASURE26
OPPORTUNITY_LAST_UPDATED_NAME BIL_TX_MEASURE27
OPPORTUNITY_CREATION_DATE BIL_TX_MEASURE28
OPPORTUNITY_CREATED_NAME BIL_TX_MEASURE29
CUSTOMER_CATEGORY BIL_TX_MEASURE30
WEIGHTED_AMOUNT BIL_TX_MEASURE31
CLOSE_REASON_MEANING BIL_TX_MEASURE32
Missing Currency Bubble Text  BIL_TX_MEASURE33
Missing Currency Bubble Text  BIL_TX_MEASURE34
*/


-- This method was suggested by Seema. Getting the same results as the oroginal method.

l_asn_Table := BIS_MAP_TBL();

Line: 237

  l_outer_select := 'SELECT BIL_TX_MEASURE1, BIL_TX_MEASURE2, BIL_TX_MEASURE3, '||
                        ' BIL_TX_MEASURE4, BIL_TX_MEASURE5, BIL_TX_MEASURE6, '||
                        ' BIL_TX_MEASURE7, BIL_TX_MEASURE8, BIL_TX_MEASURE9, BIL_TX_MEASURE10, ' ||
                        ' BIL_TX_MEASURE11, BIL_TX_MEASURE13 , '||
                        ' BIL_TX_MEASURE14, BIL_TX_MEASURE15 ' ||l_measure_outer||
                        ','||l_total_days||' BIL_TX_MEASURE21,'||
                        ' BIL_TX_MEASURE24, BIL_TX_MEASURE25, BIL_TX_MEASURE26, BIL_TX_MEASURE27, '||
                        ' BIL_TX_MEASURE28, BIL_TX_MEASURE29,'||
                    ' BIL_TX_MEASURE30, BIL_TX_MEASURE31, BIL_TX_MEASURE32,  BIL_TX_MEASURE33,BIL_TX_MEASURE34,  BIL_TX_URL1, BIL_TX_URL2 FROM';

Line: 248

 l_select_1 := ' ( SELECT  ascd.lead_id BIL_TX_MEASURE1 '||
               ' ,ascd.OPP_DESCRIPTION BIL_TX_MEASURE2 '||
               ' ,ascd.customer_id BIL_TX_MEASURE3 ,party.party_name BIL_TX_MEASURE4 ,'||
               ' ascd.sales_group_id BIL_TX_MEASURE5 ,jrgst.GROUP_NAME BIL_TX_MEASURE6 ,'||
               '  jrret.RESOURCE_NAME   BIL_TX_MEASURE7 ,ascd.win_probability BIL_TX_MEASURE8 ,'||
 	       ' (CASE WHEN (ascd.currency_code = &CURRENCY+CURR)  THEN (DECODE(m.CONVERSION_RATE, NULL ,ascd.TOTAL_AMOUNT , ascd.TOTAL_AMOUNT)) '||
                 ' ELSE ( DECODE(m.CONVERSION_RATE, NULL, TO_NUMBER(NULL), ascd.TOTAL_AMOUNT*m.CONVERSION_RATE))  END ) BIL_TX_MEASURE9, '||
              ' status.meaning BIL_TX_MEASURE10 ,ascd.decision_date  BIL_TX_MEASURE11 ,'||
              ' decode( greatest(SYSDATE, ascd.DECISION_DATE), ascd.DECISION_DATE, to_char(trunc(ascd.DECISION_DATE)-trunc(SYSDATE)),0) BIL_TX_MEASURE13 ,'||
              ' (TRUNC(SYSDATE)-trunc(ascd.OPPORTUNITY_CREATION_DATE)) BIL_TX_MEASURE14 ,'||
              ' decode( greatest(SYSDATE, ascd.DECISION_DATE), SYSDATE, to_char(trunc(SYSDATE)-trunc(ascd.DECISION_DATE)),0) BIL_TX_MEASURE15 ' ||
              l_status_days ||
              ' ,0  BIL_TX_MEASURE21 '||
              ' , ascd.LEAD_NUMBER BIL_TX_MEASURE24 ,flvl2.meaning BIL_TX_MEASURE25 ,'||
              ' ascd.OPPORTUNITY_LAST_UPDATE_DATE BIL_TX_MEASURE26, '||
              ' JTF_COMMON_PVT.GetUserInfo(ascd.OPPORTUNITY_LAST_UPDATED_BY) BIL_TX_MEASURE27, '||
              ' ascd.OPPORTUNITY_CREATION_DATE BIL_TX_MEASURE28, '||
              ' JTF_COMMON_PVT.GetUserInfo(ascd.OPPORTUNITY_CREATED_BY) BIL_TX_MEASURE29, '||
              ' flvl1.meaning BIL_TX_MEASURE30, '||
	      ' ( CASE WHEN (ascd.currency_code = &CURRENCY+CURR)  THEN '||
              	' (DECODE(m.CONVERSION_RATE, NULL ,ROUND(ascd.TOTAL_AMOUNT*ascd.win_probability/100),ROUND(ascd.TOTAL_AMOUNT*ascd.win_probability/100))) '||
              	' ELSE ( DECODE(m.CONVERSION_RATE, NULL, TO_NUMBER(NULL), ROUND(ascd.TOTAL_AMOUNT*ascd.win_probability/100) *m.CONVERSION_RATE))  END ) BIL_TX_MEASURE31, '||
              ' flvl3.meaning BIL_TX_MEASURE32, '||
	      '  ''' || l_cur_conv_missing || '''  BIL_TX_MEASURE33, ' ||
	      '  ''' || l_cur_conv_missing || '''  BIL_TX_MEASURE34, ' ||
              l_oppty_url1||' BIL_TX_URL1 ,'||
              l_customer_url2||'  BIL_TX_URL2  ';

Line: 429

      ' ,( SELECT acc.lead_id ,   '||
      '  partner.party_id party_id  '||
      '  FROM HZ_PARTIES PARTNER,  '||
      '      AS_ACCESSES_ALL ACC,  '||
      '     JTF_RS_RESOURCE_EXTNS EXT,  '||
      '     hz_organization_profiles HZOP,  '||
      '     hz_relationships HZR  '||
      '    WHERE hzr.PARTY_ID = ACC.PARTNER_CUSTOMER_ID  '||
      '      AND EXT.RESOURCE_ID = ACC.SALESFORCE_ID  '||
      '      AND PARTNER.status IN (''A'' , ''I'')  '||
      '      AND HZR.subject_table_name = ''HZ_PARTIES''  '||
      '      AND HZR.object_table_name = ''HZ_PARTIES''  '||
      '      AND HZR.object_id = HZOP.party_id  '||
      '       AND HZOP.internal_flag = ''Y''  '||
      '       AND NVL(HZOP.status, ''A'') = ''A''  '||
      '       AND NVL(HZOP.effective_end_date, SYSDATE) >= SYSDATE  '||
      '       AND HZR.party_id = EXT.source_id  '||
      '       AND EXT.category = ''PARTNER''  '||
      '       AND HZR.subject_id = PARTNER.party_id  ) partner ';

Line: 468

,ascd.OPPORTUNITY_LAST_UPDATE_DATE
,JTF_COMMON_PVT.GetUserInfo(ascd.OPPORTUNITY_LAST_UPDATED_BY)
,ascd.OPPORTUNITY_CREATION_DATE
, JTF_COMMON_PVT.GetUserInfo(ascd.OPPORTUNITY_CREATED_BY)
,flvl1.meaning
, ( CASE WHEN (ascd.currency_code = &CURRENCY+CURR)  THEN
        (DECODE(m.CONVERSION_RATE, NULL ,ROUND(ascd.TOTAL_AMOUNT*ascd.win_probability/100),ROUND(ascd.TOTAL_AMOUNT*ascd.win_probability/100)))
            ELSE ( DECODE(m.CONVERSION_RATE, NULL, TO_NUMBER(NULL), ROUND(ascd.TOTAL_AMOUNT*ascd.win_probability/100) *m.CONVERSION_RATE))  END )
, flvl3.meaning
,''' || l_cur_conv_missing || '''
,''' || l_cur_conv_missing || '''  ' ;

Line: 492

x_custom_sql :=  l_outer_select ||l_select_1|| l_from||  l_where_clause_1||
     l_where_clause_2||l_group_by;