[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_outer_select VARCHAR2(4000);
* BIL_TX_MEASURE15 = Updated date
* BIL_TX_MEASURE16 = Win Probability
* BIL_TX_MEASURE17 = Sales Stage
* BIL_TX_MEASURE18 = Updated By
*******************************************************************************/
l_custom_sql := ' SELECT OpportunityEO.description BIL_TX_MEASURE1, '||
' flv.meaning BIL_TX_MEASURE2, '||
' FLV2.MEANING BIL_TX_MEASURE3, '||
' OpportunityEO.lead_id BIL_TX_MEASURE4, '||
' OpportunityEO.total_amount BIL_TX_MEASURE5, '||
' OpportunityEO.decision_date BIL_TX_MEASURE6, '||
' hp.party_name BIL_TX_MEASURE7, '||
' (SELECT SUM(opp_forecast_amount) FROM as_sales_credits ascs '||
' WHERE ascs.lead_id = OpportunityEO.lead_id AND ascs.credit_type_id = :l_credit_type_id ) BIL_TX_MEASURE8, '||
' OpportunityEO.creation_date BIL_TX_MEASURE9, '||
' hz_format_pub.format_address(hl.location_id, null, null, '', '', null, null,null, null) || decode(ftt.territory_short_name, null, null, '', ''||ftt.territory_short_name) BIL_TX_MEASURE10, '||
' fc.name BIL_TX_MEASURE11, '||
' JTF_COMMON_PVT.GetUserInfo(OpportunityEO.CREATED_BY ) BIL_TX_MEASURE12, '||
' astl.meaning BIL_TX_MEASURE13, '||
' asmt.sales_methodology_name BIL_TX_MEASURE14, '||
' OpportunityEO.LAST_UPDATE_DATE BIL_TX_MEASURE15, '||
' OpportunityEO.win_probability BIL_TX_MEASURE16, '||
' asst.name BIL_TX_MEASURE17, '||
' JTF_COMMON_PVT.GetUserInfo(OpportunityEO.LAST_UPDATE_LOGIN) BIL_TX_MEASURE18 ';
l_outer_select VARCHAR2(4000);
l_custom_sql := ' SELECT OpportunityEO.attribute1 BIL_TX_MEASURE1, '||
' OpportunityEO.attribute2 BIL_TX_MEASURE2, '||
' OpportunityEO.attribute3 BIL_TX_MEASURE3, '||
' OpportunityEO.attribute4 BIL_TX_MEASURE4, '||
' OpportunityEO.attribute5 BIL_TX_MEASURE5, '||
' OpportunityEO.attribute6 BIL_TX_MEASURE6, '||
' OpportunityEO.attribute7 BIL_TX_MEASURE7, '||
' OpportunityEO.attribute8 BIL_TX_MEASURE8, '||
' OpportunityEO.attribute9 BIL_TX_MEASURE9, '||
' OpportunityEO.attribute10 BIL_TX_MEASURE10, '||
' OpportunityEO.attribute11 BIL_TX_MEASURE11, '||
' OpportunityEO.attribute12 BIL_TX_MEASURE12, '||
' OpportunityEO.attribute13 BIL_TX_MEASURE13, '||
' OpportunityEO.attribute14 BIL_TX_MEASURE14, '||
' OpportunityEO.attribute15 BIL_TX_MEASURE15, '||
' OpportunityEO.attribute_category BIL_TX_MEASURE16 '||
' FROM as_leads_all OpportunityEO ' ||
' WHERE OpportunityEO.lead_id = :l_lead_id ';
l_outer_select VARCHAR2(4000);
l_custom_sql := ' SELECT ascd.lead_id BIL_TX_MEASURE1 '||
' ,ascd.PRODUCT_CATEGORY_ID BIL_TX_MEASURE2 '||
' ,ascd.ITEM_ID BIL_TX_MEASURE3 '||
' ,NVL(msit.description, mct.description) BIL_TX_MEASURE4 '||
' ,mumt.DESCRIPTION BIL_TX_MEASURE5 '||
' ,ascd.QUANTITY BIL_TX_MEASURE6 '||
' ,ascd.SALES_CREDIT_AMOUNT BIL_TX_MEASURE7 '||
' ,ascd.sales_group_id BIL_TX_MEASURE8 '||
' ,jrgt.GROUP_NAME BIL_TX_MEASURE9 '||
' ,jrre.SOURCE_NAME BIL_TX_MEASURE10 '||
' ,osct.name BIL_TX_MEASURE11 '||
' ,ascd.OPP_BEST_FORECAST_AMOUNT BIL_TX_MEASURE12 '||
' ,ascd.OPP_FORECAST_AMOUNT BIL_TX_MEASURE13 '||
' ,ascd.OPP_WORST_FORECAST_AMOUNT BIL_TX_MEASURE14 '||
' ,ascd.CLOSE_COMPETITOR_ID BIL_TX_MEASURE15 '||
' ,hzpt.party_name BIL_TX_MEASURE16 '||
' ,acpt.COMPETITOR_PRODUCT_NAME BIL_TX_MEASURE17 '||
' ,INITCAP(alcp.WIN_LOSS_STATUS) BIL_TX_MEASURE18 '||
' FROM as_sales_credits_denorm ascd '||
' ,as_lead_comp_products alcp '||
' ,ams_competitor_products_tl acpt '||
' ,oe_sales_credit_types osct '||
' ,mtl_system_items_tl msit '||
' ,mtl_categories_tl mct '||
' ,mtl_units_of_measure_tl mumt '||
' ,ams_competitor_products_b acpb '||
' ,hz_parties hzpt '||
' ,jtf_rs_groups_tl jrgt '||
' ,jtf_rs_resource_extns jrre '||
' WHERE ascd.LEAD_ID = :l_lead_id '||
' AND ascd.lead_id = alcp.LEad_id(+) '||
' AND ascd.lead_line_id = alcp.lead_line_id(+) '||
' AND alcp.competitor_product_id = acpt.competitor_product_id(+) '||
' AND acpt.language(+) = USERENV( ''LANG'' ) '||
' AND acpt.competitor_product_id = acpb.competitor_product_id(+) '||
' AND TRUNC(NVL(acpb.start_date, SYSDATE)) <= TRUNC(SYSDATE) '||
' AND TRUNC(NVL(acpb.end_date, SYSDATE)) >= TRUNC(SYSDATE) '||
' AND osct.SALES_CREDIT_TYPE_ID = ascd.CREDIT_TYPE_ID '||
' AND osct.ENABLED_FLAG = ''Y'' '||
' AND ascd.product_category_id = mct.category_id '||
' AND ascd.item_id = msit.inventory_item_id(+) '||
' AND ascd.organization_id = msit.organization_id(+) '||
' AND msit.language(+) = USERENV( ''LANG'' ) '||
' AND mct.language = USERENV( ''LANG'' ) '||
' AND acpb.competitor_party_ID = hzpt.party_id(+) '||
' AND jrgt.GROUP_ID = ascd.sales_group_id '||
' AND jrgt.LANGUAGE = USERENV( ''LANG'' ) '||
' AND jrre.resource_id = ascd.SALESFORCE_ID '||
' AND ascd.UOM_CODE = mumt.UOM_CODE(+) '||
' AND mumt.LANGUAGE(+) = USERENV( ''LANG'' ) '||
' ORDER BY ascd.PRODUCT_CATEGORY_ID ' ;
l_outer_select VARCHAR2(4000);
l_custom_sql := ' SELECT jrt.resource_name BIL_TX_MEASURE1, '||
' jrb.source_job_title BIL_TX_MEASURE2, '||
' jrgt.group_name BIL_TX_MEASURE3 ,'||
' jrb.source_phone BIL_TX_MEASURE4, '||
' jrb.source_email BIL_TX_MEASURE5, '||
' DECODE(OpportunityAccessEO.owner_flag, ''Y'', ''bischeck.gif'',NULL) BIL_TX_MEASURE6, '||
' DECODE(OpportunityAccessEO.contributor_flag, ''Y'', ''bischeck.gif'',NULL) BIL_TX_MEASURE7 '||
' FROM jtf_rs_resource_extns jrb, '||
' jtf_rs_resource_extns_tl jrt, '||
' jtf_rs_groups_tl jrgt, '||
' as_accesses_all OpportunityAccessEO '||
' WHERE OpportunityAccessEO.salesforce_id = jrb.resource_id '||
' AND jrb.resource_id = jrt.resource_id '||
' AND jrb.category = jrt.category '||
' AND jrt.language = USERENV(''LANG'') '||
' AND OpportunityAccessEO.sales_lead_id IS NULL '||
' AND OpportunityAccessEO.lead_id IS NOT NULL '||
' AND jrb.category = ''EMPLOYEE'' '||
' AND OpportunityAccessEO.sales_group_id = jrgt.GROUP_ID '||
' AND jrgt.language = USERENV(''LANG'') '||
' AND OpportunityAccessEO.lead_id = :l_lead_id ' ||
' ORDER BY BIL_TX_MEASURE1 ' ;
l_outer_select VARCHAR2(4000);
l_custom_sql := ' SELECT PvExternalSalesteamEO.lead_id BIL_TX_MEASURE1, '||
' PvExternalSalesteamEO.PARTNER_CUSTOMER_ID BIL_TX_MEASURE2, '||
' PARTNER.PARTY_NAME BIL_TX_MEASURE3, '||
' ARP_ADDR_LABEL_PKG.FORMAT_ADDRESS_LABEL(Null, HZL.ADDRESS1, HZL.ADDRESS2, HZL.ADDRESS3, HZL.ADDRESS4, HZL.CITY, HZL.COUNTY, '||
' HZL.STATE, HZL.PROVINCE, HZL.POSTAL_CODE, HZL.COUNTRY, HZL.COUNTRY, Null, Null, Null, Null, Null, Null, NULL, NULL, 2000, '||
' 1, 1) BIL_TX_MEASURE4, '||
' T.DESCRIPTION BIL_TX_MEASURE5, '||
' PV_MATCH_V3_PUB.get_partner_types(PvExternalSalesteamEO.PARTNER_CUSTOMER_ID) BIL_TX_MEASURE6, '||
' to_char(PVPP.oppty_last_offered_date, FND_PROFILE.VALUE(''ICX_DATE_FORMAT_MASK'')) BIL_TX_MEASURE7, '||
' DECODE( PV_MATCH_V3_PUB.pref_partner_flag(PvExternalSalesteamEO.LEAD_ID, PvExternalSalesteamEO.PARTNER_CUSTOMER_ID), ''Y'', ''bischeck.gif'',NULL) BIL_TX_MEASURE8, '||
' Decode(PARTNER.primary_phone_country_code,NULL, '''',PARTNER.primary_phone_country_code||''-'')|| Decode(PARTNER.primary_phone_area_code, '||
' NULL, '''',PARTNER.primary_phone_area_code||''-'')|| DECODE(PARTNER.primary_phone_number, NULL, '''', '||
' PARTNER.primary_phone_number||''-'')|| DECODE(PARTNER.primary_phone_extension, NULL, '''',PARTNER.primary_phone_extension) BIL_TX_MEASURE9, '||
' PV_MATCH_V3_PUB.get_assign_status_meaning( PvExternalSalesteamEO.lead_id, PvExternalSalesteamEO.PARTNER_CUSTOMER_ID) BIL_TX_MEASURE10 '||
' FROM AS_ACCESSES_ALL PvExternalSalesteamEO, '||
' HZ_PARTIES PARTNER, '||
' PV_PARTNER_PROFILES PVPP, '||
' HZ_PARTY_SITES HZPS, '||
' HZ_LOCATIONS HZL, '||
' FND_LOOKUP_VALUES fndlv , '||
' PV_ATTRIBUTE_CODES_TL T , '||
' PV_ATTRIBUTE_CODES_B B '||
' WHERE PVPP.PARTNER_ID = PvExternalSalesteamEO.PARTNER_CUSTOMER_ID '||
' AND PVPP.PARTNER_RESOURCE_ID = PvExternalSalesteamEO.SALESFORCE_ID '||
' AND PVPP.PARTNER_PARTY_ID = PARTNER.party_id '||
' AND HZPS.party_site_id(+) = PvExternalSalesteamEO.PARTNER_ADDRESS_ID '||
' AND HZPS.location_id = HZL.location_id (+) '||
' AND fndlv.lookup_code(+) = PARTNER.certification_level '||
' AND fndlv.lookup_type(+) = ''HZ_PARTY_CERT_LEVEL'' '||
' AND fndlv.LANGUAGE(+) = USERENV(''LANG'') '||
' AND PvExternalSalesteamEO.PERSON_ID IS NULL '||
' AND PvExternalSalesteamEO.PARTNER_CONT_PARTY_ID IS NULL '||
' AND PvExternalSalesteamEO.LEAD_ID = :l_lead_id '||
' AND PVPP.partner_level = T.ATTR_CODE_ID(+) '|| -- changed code
' AND B.ATTR_CODE_ID(+) = T.ATTR_CODE_ID '||
' AND T.LANGUAGE(+) = userenv(''LANG'') '||
' AND B.attribute_id(+) = 19 '||
' AND B.ENABLED_FLAG(+) = ''Y'' '||
' ORDER BY BIL_TX_MEASURE3 ' ;
l_outer_select VARCHAR2(4000);
l_custom_sql := ' SELECT hzpt.party_id BIL_TX_MEASURE1, '||
' hzpt.party_name BIL_TX_MEASURE2, '||
' hoc1.job_title BIL_TX_MEASURE3, '||
' DECODE(hoc.phone_country_code,NULL,'''', hoc.phone_country_code || ''-'') '||
' || DECODE(hoc.phone_area_code,NULL,'''',hoc.phone_area_code|| ''-'') '||
' || DECODE(hoc.phone_number,NULL,'''',hoc.phone_number) '||
' || DECODE(hoc.phone_extension,NULL,'''',''x'' ||hoc.phone_extension) BIL_TX_MEASURE4, '||
' hzpt.EMAIL_ADDRESS BIL_TX_MEASURE5, '||
' fndl.meaning BIL_TX_MEASURE6, '||
' hr.subject_id BIL_TX_MEASURE7, '||
' hr.object_id BIL_TX_MEASURE8, '||
' hr.party_id BIL_TX_MEASURE9, '||
' hr.relationship_id BIL_TX_MEASURE10 '||
' FROM as_lead_contacts_all alca, '||
' hz_contact_points hoc, '||
' hz_relationships hr, '||
' hz_parties hzpt, '||
' hz_org_contacts hoc1, '||
' fnd_lookups fndl '||
' WHERE alca.contact_party_id = hoc.owner_table_id(+) '||
' AND hoc.owner_table_name(+) = ''HZ_PARTIES'' '||
' AND hoc.primary_flag(+) = ''Y'' '||
' AND hoc.contact_point_type(+) = ''PHONE'' '||
' AND alca.contact_party_id = hr.party_id '||
' AND alca.customer_id = hr.object_id '||
' AND hr.object_table_name = ''HZ_PARTIES'' '||
' AND hzpt.party_id = alca.contact_party_id '||
' AND hr.relationship_id = hoc1.party_relationship_id (+) '||
' AND alca.rank = fndl.lookup_code (+) '||
' AND fndl.lookup_type(+) = ''ASN_CONTACT_ROLE'' '||
' AND alca.lead_id = :l_lead_id '||
' ORDER BY BIL_TX_MEASURE2 ';
l_outer_select VARCHAR2(4000);
l_custom_sql := ' SELECT pp.proposal_id BIL_TX_MEASURE1, '||
' pp.proposal_name BIL_TX_MEASURE2, '||
' pp.proposal_desc BIL_TX_MEASURE3, '||
' jrt.resource_name BIL_TX_MEASURE4, '||
' pp.due_date BIL_TX_MEASURE5, '||
' fl.meaning BIL_TX_MEASURE6 '||
' FROM prp_proposals pp, '||
' prp_proposal_objects ppo, '||
' fnd_lookups fl, '||
' jtf_rs_resource_extns jrb, '||
' jtf_rs_resource_extns_tl jrt '||
' WHERE pp.proposal_status = fl.lookup_code '||
' AND fl.lookup_type = ''PRP_PROPOSAL_STATUS'' '||
' AND ppo.object_type = ''OPPORTUNITY'' '||
' AND ppo.proposal_id = pp.proposal_id '||
' AND pp.user_id = jrb.user_id(+) '||
' AND jrb.category = jrt.category(+) '||
' AND jrb.resource_id = jrt.resource_id (+) '||
' AND jrt.language(+) = USERENV(''LANG'') '||
' AND ppo.object_id = :l_lead_id ' ||
' ORDER BY BIL_TX_MEASURE2 ';
l_outer_select VARCHAR2(4000);
l_custom_sql := ' SELECT aqha.quote_name BIL_TX_MEASURE1, '||
' aqha.quote_header_id BIL_TX_MEASURE2, '||
' aqha.quote_number BIL_TX_MEASURE3, '||
' jrst.resource_name BIL_TX_MEASURE4, '||
' aqst.meaning BIL_TX_MEASURE5 , '||
' aqha.creation_date BIL_TX_MEASURE6, '||
' aqha.quote_expiration_date BIL_TX_MEASURE7, '||
' aqha.total_quote_price BIL_TX_MEASURE8, '||
' aqha.currency_code BIL_TX_MEASURE9, '||
' fct.name BIL_TX_MEASURE10, '||
' aqha.order_id BIL_TX_MEASURE11 '||
' FROM aso_quote_related_objects aqro, '||
' aso_quote_headers_all aqha, '||
' aso_quote_statuses_tl aqst, '||
' jtf_rs_resource_extns_tl jrst, '||
' fnd_currencies_tl fct '||
'WHERE aqro.quote_object_type_code = ''HEADER'' '||
' AND aqro.relationship_type_code = ''OPP_QUOTE'' '||
' AND aqro.quote_object_id = aqha.quote_header_id '||
' AND aqha.quote_status_id = aqst.quote_status_id '||
' AND aqst.language = USERENV(''LANG'') '||
' AND aqha.resource_id = jrst.resource_id '||
' AND jrst.language = aqst.language '||
' AND aqha.resource_id IS NOT NULL '||
' AND NVL(aqha.quote_type, ''Q'') = ''Q'' '||
' AND NVL(aqha.max_version_flag,''Y'') = ''Y'' '||
' AND aqha.currency_code = fct.currency_code '||
' AND fct.language = aqst.language '||
-- ' AND NVL(aqha.org_id, NVL(TO_NUMBER(DECODE(SUBSTRB '||
-- ' (USERENV(''CLIENT_INFO''), 1 , 1), '''', NULL, '||
-- ' SUBSTRB(USERENV(''CLIENT_INFO''), 1, 10))), -99)) = '||
-- ' NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV(''CLIENT_INFO''), 1, 1), '''', NULL, '||
-- ' SUBSTRB(USERENV(''CLIENT_INFO''), 1, 10))), -99) '||
' AND aqro.object_id = :l_lead_id ' ||
' ORDER BY BIL_TX_MEASURE1 ';
l_outer_select VARCHAR2(4000);
l_custom_sql := ' SELECT ppa.segment1 BIL_TX_MEASURE1, '||
' ppa.name BIL_TX_MEASURE2, '||
' ppa.project_id BIL_TX_MEASURE3, '||
' ppa.carrying_out_organization_id BIL_TX_MEASURE4, '||
' hou.name BIL_TX_MEASURE5, '||
' ppp.person_id BIL_TX_MEASURE6, '||
' ppf.full_name BIL_TX_MEASURE7, '||
' ppa.start_date BIL_TX_MEASURE8, '||
' ppa.completion_date BIL_TX_MEASURE9, '||
' ppa.project_type BIL_TX_MEASURE10, '||
' ppa.project_status_code BIL_TX_MEASURE11, '||
' pps.project_status_name BIL_TX_MEASURE12 '||
' FROM pa_projects_all ppa , '||
' hr_all_organization_units_tl hou , '||
' pa_project_statuses pps, '||
' pa_project_players ppp , '||
' per_people_f ppf '||
' WHERE ppa.carrying_out_organization_id = hou.organization_id '||
' AND hou.language = userenv(''lang'') '||
' AND ppa.project_status_code = pps.project_status_code '||
' AND ppa.project_id = ppp.project_id '||
' AND ppp.project_role_type = ''PROJECT MANAGER'' '||
' AND ppp.person_id = ppf.person_id '||
' AND (trunc(sysdate) >= ppf.effective_start_date '||
' AND trunc(sysdate) <= ppf.effective_end_date) '||
' AND ppa.project_id in ( SELECT object_id_to1 '||
' FROM ( SELECT object_type_to, '||
' object_id_to1 '||
' FROM pa_object_relationships '||
' WHERE relationship_type = ''A'' '||
' AND relationship_subtype = ''PROJECT_REQUEST'' '||
' START WITH (object_type_from = ''AS_LEADS'' '||
' AND object_id_from1 = :l_lead_id) '||
' CONNECT BY (prior object_id_to1 = object_id_from1 '||
' AND prior object_type_to = object_type_from '||
' AND prior object_id_from1 <> object_id_to1)) a '||
' WHERE a.object_type_to = ''PA_PROJECTS'') ' ||
' ORDER BY BIL_TX_MEASURE2 ';
l_outer_select VARCHAR2(4000);
l_custom_sql := ' SELECT TaskEO.TASK_ID BIL_TX_MEASURE1, '||
' TaskEO.SOURCE_OBJECT_ID BIL_TX_MEASURE2, '||
' jtl.TASK_NAME BIL_TX_MEASURE3, '||
' TaskEO.PLANNED_END_DATE BIL_TX_MEASURE4, '||
' tt.NAME BIL_TX_MEASURE5, '||
' ts.NAME BIL_TX_MEASURE6, '||
' Rscreator.SOURCE_NAME BIL_TX_MEASURE7, '||
' JTF_TASK_UTL.get_owner(TaskEO.OWNER_TYPE_CODE, TaskEO.OWNER_ID) BIL_TX_MEASURE8, '||
' jtl.DESCRIPTION BIL_TX_MEASURE9, '||
' JTF_TASK_UTL.get_owner(Assign.RESOURCE_TYPE_CODE, Assign.RESOURCE_ID) BIL_TX_MEASURE10 '||
' FROM jtf_tasks_b TaskEO, '||
' jtf_task_statuses_tl ts, '||
' jtf_task_types_tl tt, '||
' jtf_tasks_tl jtl, '||
' JTF_RS_RESOURCE_EXTNS Rscreator, '||
' JTF_TASK_ALL_ASSIGNMENTS Assign '||
' WHERE Assign.task_id (+) = TaskEO.task_id '||
' AND TaskEO.created_by = Rscreator.user_id(+) '||
' AND TaskEO.entity = ''TASK'' '||
' AND TaskEO.source_object_type_code in (select object_code from jtf_objects_b where enter_from_task = ''Y'') '||
' AND NVL(TaskEO.deleted_flag,''N'') = ''N'' '||
' AND TaskEO.task_id = jtl.task_id '||
' AND jtl.language = USERENV(''LANG'') '||
' AND TaskEO.task_status_id = ts.task_status_id '||
' AND ts.language = userenv(''LANG'') '||
' AND TaskEO.task_type_id = tt.task_type_id '||
' AND tt.language = userenv(''LANG'') '||
' AND TaskEO.SOURCE_OBJECT_ID = :l_lead_id ' ||
' ORDER BY BIL_TX_MEASURE3 ';
l_outer_select VARCHAR2(4000);
* BIL_TX_MEASURE7 = Updated By
* BIL_TX_MEASURE8 = Updated Date
* BIL_TX_MEASURE9 = Usage
*******************************************************************************/
l_custom_sql := ' SELECT ad.ATTACHED_DOCUMENT_ID BIL_TX_MEASURE1, '||
' ad.DOCUMENT_ID BIL_TX_MEASURE2, '||
' decode(d.FILE_NAME, null, (select message_text from fnd_new_messages '||
' where message_name = ''FND_UNDEFINED'' '||
' and application_id = 0 and language_code = userenv(''LANG'')), D.FILE_NAME) BIL_TX_MEASURE3, '||
' dL.DESCRIPTION BIL_TX_MEASURE4, '||
' DD.USER_NAME BIL_TX_MEASURE5, '||
' cl.user_name BIL_TX_MEASURE6, '||
' u.USER_NAME BIL_TX_MEASURE7, '||
' ad.LAST_UPDATE_DATE BIL_TX_MEASURE8 , '||
' L.MEANING BIL_TX_MEASURE9 '||
' FROM FND_DOCUMENTS D , '||
' FND_DOCUMENTS_TL DL, '||
' FND_DOCUMENT_DATATYPES DD, '||
' FND_LOOKUP_VALUES L, '||
' FND_ATTACHED_DOCUMENTS ad, '||
' FND_USER u, '||
' FND_DOCUMENT_CATEGORIES_TL cl '||
' WHERE ad.DOCUMENT_ID = d.DOCUMENT_ID '||
' and ad.LAST_UPDATED_BY = u.USER_ID(+) '||
' and cl.language = userenv(''LANG'') '||
' and cl.category_id = decode(ad.category_id, null, d.category_id, ad.category_id) '||
' and ad.ENTITY_NAME = ''AS_OPPORTUNITY_ATTCH'' '||
' and D.DOCUMENT_ID = DL.DOCUMENT_ID '||
' AND DL.LANGUAGE= USERENV(''LANG'') '||
' AND D.DATATYPE_ID = DD.DATATYPE_ID '||
' AND DD.LANGUAGE = USERENV(''LANG'') '||
' AND D.USAGE_TYPE = L.LOOKUP_CODE '||
' AND L.LANGUAGE = USERENV(''LANG'') '||
' AND L.LOOKUP_TYPE = ''ATCHMT_DOCUMENT_TYPE'' '||
' and ad.PK1_VALUE = :l_lead_id '||
' ORDER BY BIL_TX_MEASURE3 ';
l_outer_select VARCHAR2(4000);
l_custom_sql := ' SELECT JNB.ENTERED_DATE BIL_TX_MEASURE1, '||
' JTF_COMMON_PVT.GetUserInfo(JNB.ENTERED_BY) BIL_TX_MEASURE2, '||
' FLS.MEANING BIL_TX_MEASURE3, '||
' FLP.MEANING BIL_TX_MEASURE4, '||
' JNT.NOTES BIL_TX_MEASURE5 '||
' FROM JTF_NOTES_B JNB , '||
' JTF_NOTES_TL JNT , '||
' FND_LOOKUPS FLS , '||
' FND_LOOKUPS FLP '||
' WHERE JNB.JTF_NOTE_ID = JNT.JTF_NOTE_ID '||
' AND JNT.LANGUAGE = USERENV(''LANG'') '||
' AND FLS.LOOKUP_TYPE(+) = ''JTF_NOTE_TYPE'' '||
' AND FLS.LOOKUP_CODE(+) = JNB.NOTE_TYPE '||
' AND FLP.lookup_type = ''JTF_NOTE_STATUS'' '||
' AND FLP.lookup_code = JNB.note_status '||
' AND JNB.SOURCE_OBJECT_CODE = ''OPPORTUNITY'' '||
' AND JNB.SOURCE_OBJECT_ID = :l_lead_id '||
' ORDER BY BIL_TX_MEASURE1 ';