Radu Marinache - Tech Hub

Miscellaneous

SQL Split Opportunity Revenue by Calendaristic Months

rvtechblogrvtechblog 0Comments

SELECT OPPORTUNITY_REVENUE.OPTY_ID, OPPORTUNITY_REVENUE.OPTY_NUMBER, OPPORTUNITY_REVENUE.NAME, OPPORTUNITY_REVENUE.LOCATIONS, OPPORTUNITY_REVENUE.REVENUE_DRIVER, OPPORTUNITY_REVENUE.CETA, (CASE WHEN OPPORTUNITY_REVENUE.ACCOUNT_PARTY_ID IS NULL THEN 'NO ACCOUNT' ELSE (SELECT HZ_PARTIES.PARTY_NAME FROM FUSION.HZ_PARTIES WHERE HZ_PARTIES.PARTY_ID = OPPORTUNITY_REVENUE.ACCOUNT_PARTY_ID) END) AS ACCOUNT_NAME, (CASE WHEN OPPORTUNITY_REVENUE.CONTACT_PARTY_ID…

Miscellaneous

BI Publisher – SQL Get Profile Options Values

rvtechblogrvtechblog 0Comments

Get information on a profile option SELECT *FROM FND_PROFILE_OPTIONS_BWHERE PROFILE_OPTION_NAME = '<profile option name>' Get information on the levels set for a profile option SELECT *FROM FND_PROFILE_OPTION_LEVELSWHERE PROFILE_OPTION_ID =(SELECT PROFILE_OPTION_IDFROM FND_PROFILE_OPTIONS_BWHERE PROFILE_OPTION_NAME = '<profile option name>') Get information on the values of a specific profile option   SELECT *FROM FND_PROFILE_OPTION_VALUESWHERE PROFILE_OPTION_ID =(SELECT PROFILE_OPTION_IDFROM FND_PROFILE_OPTIONS_BWHERE PROFILE_OPTION_NAME = '<profile option name>')…

Miscellaneous

BI Publisher – SQL for Opportunities

rvtechblogrvtechblog 0Comments

Get Sales Method SELECT*FROM MOO_SALES_METHOD_TLWHERE name LIKE '%<Sales Method Name>%'SELECT DISTINCTSALES_METHOD_ID,NAMEFROM MOO_SALES_METHOD_TL Get Sales Stages select * from MOO_STG_B SELECT A.SALES_METHOD_ID,TL.NAME as METHOD_NAME,B.STG_ID as SALES_STAGE_ID,T.NAME as SALES_STAGE_NAMEFROM (SELECT DISTINCT STG_ID FROM MOO_STG_B) B INNER JOIN MOO_STG_B A ON (B.STG_ID = A.STG_ID)INNER JOIN MOO_STG_TL TON (B.STG_ID = T.STG_ID)INNER JOIN MOO_SALES_METHOD_TL TLON (A.SALES_METHOD_ID = TL.SALES_METHOD_ID)ORDER BY A.SALES_METHOD_ID,T.NAME SELECTSALES_METHOD_ID,STG_ID,PHASE_CD,STAGE_STATUS_CD,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATEFROM MOO_STG_BWHERE SALES_METHOD_ID IN (SELECTSALES_METHOD_IDFROM MOO_SALES_METHOD_TLWHERE name LIKE '%<Sales Method Name>%') Get Opportunities SELECT * FROM MOO_OPTY Get Data Comparison   SELECTCOUNT(*)FROM MOO_OPTYWHERE STATUS_CD IN ('OPEN', 'In Progress', 'Waiting on Customer')AND (EFFECTIVE_DATE BETWEEN to_date('2014-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND to_date('2021-01-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) Opportunity Assignment SELECT*FROM MOW_BATCH_ASGN_JOB_ITEMSWHERE ESS_REQUEST_ID = <REQUEST ID>AND rownum < 10AND JOB_ITEM_STATUS = '-1' SELECTCOUNT(*)FROM MOW_BATCH_ASGN_JOB_ITEMSWHERE ESS_REQUEST_ID = 147720AND JOB_ITEM_STATUS = '-1'AND ERROR_MESSAGE LIKE '%Restrict_Upd_opty_Summary_Page_%'

Miscellaneous

BI Publisher – SQL For Collecting info about an Account

rvtechblogrvtechblog 0Comments

SELECT hz_organization_profiles.party_id, hz_party_sites.location_id, hz_organization_profiles.party_number, hz_organization_profiles.organization_name, hz_locations.address1, hz_locations.city, hz_locations.country, hz_party_sites.END_DATE_ACTIVE, hz_party_sites.STATUS, ( CASE WHEN hz_organization_profiles.preferred_contact_person_id IS NULL THEN 0 ELSE hz_organization_profiles.preferred_contact_person_id END ) AS ACCOUNT_CONTACT, ( CASE WHEN (SELECT hz_parties.party_name FROM…

Miscellaneous

BI Publisher – SQL For Account with Details about Opty and SRs

rvtechblogrvtechblog 0Comments

SELECT HZ_ORGANIZATION_PROFILES.PARTY_ID, HZ_PARTY_SITES.LOCATION_ID, HZ_ORGANIZATION_PROFILES.PARTY_NUMBER, HZ_ORGANIZATION_PROFILES.ORGANIZATION_NAME, HZ_LOCATIONS.ADDRESS1, HZ_LOCATIONS.CITY, HZ_LOCATIONS.COUNTRY, (SELECT COUNT(*) FROM FUSION.MOO_OPTY WHERE MOO_OPTY.CUST_PARTY_ID = HZ_ORGANIZATION_PROFILES.PARTY_ID AND MOO_OPTY.STATUS_CD = 'WON') as OPTY_WON, (SELECT COUNT(*) FROM FUSION.MOO_OPTY WHERE MOO_OPTY.CUST_PARTY_ID = HZ_ORGANIZATION_PROFILES.PARTY_ID…

Miscellaneous

BI Publisher – Get Opportunity Info With account and contact name and phone

rvtechblogrvtechblog 0Comments

SELECT o.NAME as Opty_Name, o.OPTY_ID, o.CUST_PARTY_ID, o.PR_CON_PARTY_ID, (select PARTY_NAME FROM HZ_PARTIES WHERE PARTY_ID = o.CUST_PARTY_ID) as AccountName, (select PARTY_NAME FROM HZ_PARTIES WHERE PARTY_ID = o.PR_CON_PARTY_ID) as ContactName, (select EMAIL_ADDRESS FROM HZ_PARTIES WHERE PARTY_ID = o.PR_CON_PARTY_ID) as Email, (select PRIMARY_PHONE_COUNTRY_CODE FROM HZ_PARTIES WHERE PARTY_ID = o.PR_CON_PARTY_ID) as cc, (select PRIMARY_PHONE_AREA_CODE FROM HZ_PARTIES WHERE PARTY_ID = o.PR_CON_PARTY_ID) as ar, (select PRIMARY_PHONE_NUMBER FROM HZ_PARTIES WHERE PARTY_ID = o.PR_CON_PARTY_ID) as phone, (select PRIMARY_PHONE_EXTENSION FROM HZ_PARTIES WHERE PARTY_ID = o.PR_CON_PARTY_ID) as ext, (select CITY FROM HZ_PARTIES WHERE PARTY_ID = o.PR_CON_PARTY_ID) as CITY, r.INVENTORY_ITEM_ID, (select DESCRIPTION FROM QSC_PRODUCTS_TL WHERE INVENTORY_ITEM_ID = r.INVENTORY_ITEM_ID AND LANGUAGE = 'US' AND rownum = 1) as Product, r.QTY, r.UNIT_PRICE, CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT('(',(select PRIMARY_PHONE_COUNTRY_CODE FROM HZ_PARTIES WHERE PARTY_ID = o.PR_CON_PARTY_ID)),')'),' ('),(select PRIMARY_PHONE_AREA_CODE FROM HZ_PARTIES WHERE PARTY_ID = o.PR_CON_PARTY_ID)), ') '), (select PRIMARY_PHONE_NUMBER FROM HZ_PARTIES WHERE PARTY_ID =…

« Previous Page — Next Page »

You Missed

Miscellaneous

VBCS – Add OPA interview as iframe

Miscellaneous

VBCS – Use KeyEnter event without using action chain

Miscellaneous

VBCS – Use onInput event without using action chain

Miscellaneous

VBCS – How to run a JS function without using action chain