[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_select1 VARCHAR2(32000);
l_select2 VARCHAR2(32000);
l_opp_status VARCHAR2(5000); -- multi select
* BIL_TX_MEASURE30 = updated date
* BIL_TX_MEASURE31 = updated by
* BIL_TX_MEASURE32 = Opportunity Number
* BIL_TX_MEASURE33 = Weighted Amount
* BIL_TX_MEASURE34 = Sales Channel
* BIL_TX_URL1 = Link to Opportunity Name
* BIL_TX_URL2 = Link to Customer
*******************************************************************************/
-- SELECT SECTION
l_select1 := 'SELECT ascd.lead_id BIL_TX_MEASURE1 '||
' ,ascd.OPP_DESCRIPTION BIL_TX_MEASURE2 '||
' ,ascd.CUSTOMER_ID BIL_TX_MEASURE3 '||
' ,hzpt1.party_name BIL_TX_MEASURE4 ';
l_select1 := l_select1 ||
' ,aca.sales_group_id BIL_TX_MEASURE5 ';
l_select1 := l_select1 ||
' ,ascd.sales_group_id BIL_TX_MEASURE5 ';
l_select1 := l_select1 ||
' ,jrgt.GROUP_NAME BIL_TX_MEASURE6 '||
' ,jrre.SOURCE_NAME BIL_TX_MEASURE7 '||
' ,decode( greatest(SYSDATE, ascd.DECISION_DATE), ascd.DECISION_DATE, to_char(trunc(ascd.DECISION_DATE)-trunc(SYSDATE)),NULL) BIL_TX_MEASURE8 '||
' ,ascd.WIN_PROBABILITY BIL_TX_MEASURE9 '||
' ,( 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_MEASURE10 '||
' ,( 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_MEASURE33 '||
' ,asst.meaning BIL_TX_MEASURE11 '||
' ,ascd.DECISION_DATE BIL_TX_MEASURE12 '||
' ,ascd.PRODUCT_CATEGORY_ID BIL_TX_MEASURE13 '||
' ,ascd.ITEM_ID BIL_TX_MEASURE14 '||
' ,NVL(msit.description,mct.description) BIL_TX_MEASURE15 '||
' ,( CASE WHEN (ascd.currency_code = &CURRENCY+CURR) THEN (DECODE(m.CONVERSION_RATE, NULL, ascd.SALES_CREDIT_AMOUNT, ascd.SALES_CREDIT_AMOUNT)) '||
' ELSE ( DECODE(m.CONVERSION_RATE, NULL, TO_NUMBER(NULL), ascd.SALES_CREDIT_AMOUNT*m.CONVERSION_RATE)) END ) BIL_TX_MEASURE16 '||
' ,ascd.FORECAST_DATE BIL_TX_MEASURE17 '||
' ,( CASE WHEN (ascd.currency_code = &CURRENCY+CURR) THEN (DECODE(m.CONVERSION_RATE, NULL ,NVL(ascd.OPP_BEST_FORECAST_AMOUNT,0) , ascd.OPP_BEST_FORECAST_AMOUNT)) '||
' ELSE ( DECODE(m.CONVERSION_RATE, NULL, TO_NUMBER(NULL), ascd.OPP_BEST_FORECAST_AMOUNT*m.CONVERSION_RATE)) END ) BIL_TX_MEASURE18 '||
' ,( CASE WHEN (ascd.currency_code = &CURRENCY+CURR) THEN (DECODE(m.CONVERSION_RATE, NULL ,NVL(ascd.OPP_FORECAST_AMOUNT,0) , ascd.OPP_FORECAST_AMOUNT)) '||
' ELSE ( DECODE(m.CONVERSION_RATE, NULL, TO_NUMBER(NULL), ascd.OPP_FORECAST_AMOUNT*m.CONVERSION_RATE)) END ) BIL_TX_MEASURE19 '||
' ,( CASE WHEN (ascd.currency_code = &CURRENCY+CURR) THEN (DECODE(m.CONVERSION_RATE, NULL ,NVL(ascd.OPP_WORST_FORECAST_AMOUNT,0) , ascd.OPP_WORST_FORECAST_AMOUNT)) '||
' ELSE ( DECODE(m.CONVERSION_RATE, NULL, TO_NUMBER(NULL), ascd.OPP_WORST_FORECAST_AMOUNT*m.CONVERSION_RATE)) END ) BIL_TX_MEASURE20 '||
' ,ascd.CLOSE_COMPETITOR_ID BIL_TX_MEASURE21 '||
' ,hzpt.party_name BIL_TX_MEASURE22 '||
' ,acpt.COMPETITOR_PRODUCT_NAME BIL_TX_MEASURE23 '||
' ,INITCAP(alcp.WIN_LOSS_STATUS) BIL_TX_MEASURE24 '||
' ,flvl2.meaning BIL_TX_MEASURE26 '||
' ,ascd.OPPORTUNITY_CREATION_DATE BIL_TX_MEASURE27 '||
' ,JTF_COMMON_PVT.GetUserInfo(ascd.OPPORTUNITY_CREATED_BY) BIL_TX_MEASURE28 '||
' ,flvl3.meaning BIL_TX_MEASURE29 '||
' ,ascd.LAST_UPDATE_DATE BIL_TX_MEASURE30 '||
' ,JTF_COMMON_PVT.GetUserInfo(ascd.OPPORTUNITY_LAST_UPDATED_BY) BIL_TX_MEASURE31 '||
' ,ascd.LEAD_NUMBER BIL_TX_MEASURE32 '||
' ,flvl1.meaning BIL_TX_MEASURE34 '|| --ascd.CHANNEL_CODE
' ,ascd.credit_type_id BIL_TX_MEASURE44 ';
l_select2 := ' , ''' ||l_cur_conv_missing || ''' BIL_TX_MEASURE46 ' ||
' , ''' ||l_cur_conv_missing || ''' BIL_TX_MEASURE47 ' ||
' , ''' ||l_cur_conv_missing || ''' BIL_TX_MEASURE48 ' ||
' , ''' ||l_cur_conv_missing || ''' BIL_TX_MEASURE49 ' ||
' , ''' ||l_cur_conv_missing || ''' BIL_TX_MEASURE50 ' ||
' , ''' ||l_cur_conv_missing || ''' BIL_TX_MEASURE45 ' ||
', '||l_oppty_url1||' BIL_TX_URL1'||
', '||l_customer_url2||' BIL_TX_URL2 ';
' ,( 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 ';
x_custom_sql := l_custom_sql||l_select1||l_select2||l_from||l_where_clause||l_order_by ;
x_custom_sql := l_custom_sql||l_select1||l_select2||l_from||l_dummy_where_clause ;