VIEW: APPS.OTFV_ENROLLMENT_AGREEMENTS

Source

View Text - Preformatted

SELECT bgrT.name business_group_name ,tbd.name agreement_name ,DECODE(tbd.type,NULL,NULL, hr_bis.bis_decode_lookup('BOOKING_DEAL_TYPE',tbd.type)) agreement_type ,tbd.comments agreement_comments ,tbd.description description ,tbd.category category ,tbd.start_date agreement_start_date ,tbd.end_date agreement_end_date ,tbd.discount_percentage discount_percent ,DECODE(tbd.limit_each_event_flag,NULL,NULL, hr_bis.bis_decode_lookup('YES_NO',tbd.limit_each_event_flag)) limit_each_event ,tbd.number_of_places maximum_places ,tbd.overdraft_limit overdraft_limit ,decode(tbd.type,'P' ,ota_tbd_api.tfl_purchased(tbd.booking_deal_id,tpl.price_list_type)) total_purchased ,decode(tbd.type,'P' ,ota_tbd_api.tfl_balance(tbd.booking_deal_id,tpl.price_list_type)) balance ,substrb(CUST_PARTY.PARTY_NAME,1,50) customer_name ,ett.title event_title ,tvt.version_name activity_name ,tdt.name activity_type ,fnd.user_name approved_by ,tpl.name price_list_name ,DECODE(tpl.price_list_type,NULL,NULL, hr_bis.bis_decode_lookup('PRICE_LIST_TYPE',tpl.price_list_type)) price_list_type ,DECODE(tpl.default_flag,NULL,NULL, hr_bis.bis_decode_lookup('YES_NO',tpl.default_flag)) default_price_list ,tpl.description price_list_description ,tpl.training_unit_type training_units ,tpl.single_unit_price unit_price ,ota_general.fnd_currency_name(tpl.currency_code) price_list_currency ,tpl.start_date price_list_date_from ,tpl.end_date price_list_date_to ,'_DF:OTA:OTA_BOOKING_DEALS:tbd' ,tbd.business_group_id business_group_id ,tbd.customer_id customer_id ,evt.event_id event_id ,tav.activity_version_id activity_version_id ,tad.activity_id activity_id ,tbd.approved_by_person_id approved_by_person_id ,tpl.price_list_id price_list_id ,tbd.booking_deal_id agreement_id ,tav.rco_id rco_id ,evt.parent_offering_id offering_id FROM hr_all_organization_units_tl bgrT ,ota_activity_definitions tad ,ota_activity_versions tav ,ota_events evt ,ota_activity_definitions_tl tdt ,ota_activity_versions_tl tvt ,ota_events_tl ett ,ota_price_lists tpl ,ota_booking_deals tbd /* Start replacement of RA_CUSTOMERS view with table access */ ,HZ_CUST_ACCOUNTS CUST_ACCT ,HZ_PARTIES CUST_PARTY ,fnd_user fnd WHERE tbd.business_group_id = bgrT.organization_id AND bgrT.language = userenv('LANG') AND tbd.activity_version_id = tav.activity_version_id (+) AND tav.activity_version_id = tvt.activity_version_id (+) AND tvt.language (+) = userenv('LANG') AND tav.activity_id = tad.activity_id(+) AND tad.activity_id = tdt.activity_id(+) AND tdt.language (+) = userenv('LANG') AND tbd.event_id = evt.event_id (+) AND evt.event_id = ett.event_id (+) AND ett.language (+) = userenv('LANG') AND tbd.price_list_id = tpl.price_list_id (+) /* replacement of RA_CUSTOMERS view with table access */ AND tbd.customer_id = CUST_ACCT.cust_account_id (+) AND CUST_ACCT.PARTY_ID = CUST_PARTY.PARTY_ID (+) AND tbd.approved_by_person_id = fnd.user_id (+) AND tbd.business_group_id = NVL(ota_general.get_business_group_id,tbd.business_group_id) WITH READ ONLY

View Text - HTML Formatted

SELECT BGRT.NAME BUSINESS_GROUP_NAME
, TBD.NAME AGREEMENT_NAME
, DECODE(TBD.TYPE
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('BOOKING_DEAL_TYPE'
, TBD.TYPE)) AGREEMENT_TYPE
, TBD.COMMENTS AGREEMENT_COMMENTS
, TBD.DESCRIPTION DESCRIPTION
, TBD.CATEGORY CATEGORY
, TBD.START_DATE AGREEMENT_START_DATE
, TBD.END_DATE AGREEMENT_END_DATE
, TBD.DISCOUNT_PERCENTAGE DISCOUNT_PERCENT
, DECODE(TBD.LIMIT_EACH_EVENT_FLAG
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, TBD.LIMIT_EACH_EVENT_FLAG)) LIMIT_EACH_EVENT
, TBD.NUMBER_OF_PLACES MAXIMUM_PLACES
, TBD.OVERDRAFT_LIMIT OVERDRAFT_LIMIT
, DECODE(TBD.TYPE
, 'P'
, OTA_TBD_API.TFL_PURCHASED(TBD.BOOKING_DEAL_ID
, TPL.PRICE_LIST_TYPE)) TOTAL_PURCHASED
, DECODE(TBD.TYPE
, 'P'
, OTA_TBD_API.TFL_BALANCE(TBD.BOOKING_DEAL_ID
, TPL.PRICE_LIST_TYPE)) BALANCE
, SUBSTRB(CUST_PARTY.PARTY_NAME
, 1
, 50) CUSTOMER_NAME
, ETT.TITLE EVENT_TITLE
, TVT.VERSION_NAME ACTIVITY_NAME
, TDT.NAME ACTIVITY_TYPE
, FND.USER_NAME APPROVED_BY
, TPL.NAME PRICE_LIST_NAME
, DECODE(TPL.PRICE_LIST_TYPE
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('PRICE_LIST_TYPE'
, TPL.PRICE_LIST_TYPE)) PRICE_LIST_TYPE
, DECODE(TPL.DEFAULT_FLAG
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, TPL.DEFAULT_FLAG)) DEFAULT_PRICE_LIST
, TPL.DESCRIPTION PRICE_LIST_DESCRIPTION
, TPL.TRAINING_UNIT_TYPE TRAINING_UNITS
, TPL.SINGLE_UNIT_PRICE UNIT_PRICE
, OTA_GENERAL.FND_CURRENCY_NAME(TPL.CURRENCY_CODE) PRICE_LIST_CURRENCY
, TPL.START_DATE PRICE_LIST_DATE_FROM
, TPL.END_DATE PRICE_LIST_DATE_TO
, '_DF:OTA:OTA_BOOKING_DEALS:TBD'
, TBD.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, TBD.CUSTOMER_ID CUSTOMER_ID
, EVT.EVENT_ID EVENT_ID
, TAV.ACTIVITY_VERSION_ID ACTIVITY_VERSION_ID
, TAD.ACTIVITY_ID ACTIVITY_ID
, TBD.APPROVED_BY_PERSON_ID APPROVED_BY_PERSON_ID
, TPL.PRICE_LIST_ID PRICE_LIST_ID
, TBD.BOOKING_DEAL_ID AGREEMENT_ID
, TAV.RCO_ID RCO_ID
, EVT.PARENT_OFFERING_ID OFFERING_ID
FROM HR_ALL_ORGANIZATION_UNITS_TL BGRT
, OTA_ACTIVITY_DEFINITIONS TAD
, OTA_ACTIVITY_VERSIONS TAV
, OTA_EVENTS EVT
, OTA_ACTIVITY_DEFINITIONS_TL TDT
, OTA_ACTIVITY_VERSIONS_TL TVT
, OTA_EVENTS_TL ETT
, OTA_PRICE_LISTS TPL
, OTA_BOOKING_DEALS TBD /* START REPLACEMENT OF RA_CUSTOMERS VIEW WITH TABLE ACCESS */
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES CUST_PARTY
, FND_USER FND
WHERE TBD.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND TBD.ACTIVITY_VERSION_ID = TAV.ACTIVITY_VERSION_ID (+)
AND TAV.ACTIVITY_VERSION_ID = TVT.ACTIVITY_VERSION_ID (+)
AND TVT.LANGUAGE (+) = USERENV('LANG')
AND TAV.ACTIVITY_ID = TAD.ACTIVITY_ID(+)
AND TAD.ACTIVITY_ID = TDT.ACTIVITY_ID(+)
AND TDT.LANGUAGE (+) = USERENV('LANG')
AND TBD.EVENT_ID = EVT.EVENT_ID (+)
AND EVT.EVENT_ID = ETT.EVENT_ID (+)
AND ETT.LANGUAGE (+) = USERENV('LANG')
AND TBD.PRICE_LIST_ID = TPL.PRICE_LIST_ID (+) /* REPLACEMENT OF RA_CUSTOMERS VIEW WITH TABLE ACCESS */
AND TBD.CUSTOMER_ID = CUST_ACCT.CUST_ACCOUNT_ID (+)
AND CUST_ACCT.PARTY_ID = CUST_PARTY.PARTY_ID (+)
AND TBD.APPROVED_BY_PERSON_ID = FND.USER_ID (+)
AND TBD.BUSINESS_GROUP_ID = NVL(OTA_GENERAL.GET_BUSINESS_GROUP_ID
, TBD.BUSINESS_GROUP_ID) WITH READ ONLY