View: OTFV_FINANCE_LINES

Product:	OTA - Learning Management
Description:	Business view template from which the flexfield view is generated.
Implementation/DBA Data:	APPS.OTFV_FINANCE_LINES

View Text

SELECT TFL.FINANCE_LINE_ID LINE_ID
, HR_BIS.BIS_DECODE_LOOKUP('FINANCE_LINE_TYPE'
, TFL.LINE_TYPE) LINE_TYPE
, TFL.SEQUENCE_NUMBER SEQUENCE
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, TFL.CANCELLED_FLAG) LINE_CANCELLED
, OTA_GENERAL.FND_CURRENCY_NAME(TFL.CURRENCY_CODE) CURRENCY
, TFL.STANDARD_AMOUNT STANDARD_AMOUNT
, TFL.MONEY_AMOUNT MONEY_AMOUNT
, TFL.UNITARY_AMOUNT UNITARY_AMOUNT
, TFL.DATE_RAISED DATE_RAISED
, TFL.COMMENTS COMMENTS
, HR_BIS.BIS_DECODE_LOOKUP('GL_TRANSFER_STATUS'
, TFL.TRANSFER_STATUS) TRANSFER_STATUS
, TFL.TRANSFER_DATE TRANSFER_DATE
, TFL.TRANSFER_MESSAGE TRANSFER_MESSAGE
, HR_BIS.BIS_DECODE_LOOKUP('FINANCE_HEADER_TYPE'
, TFH.TYPE) HEADER_TYPE
, TFH.RECEIVABLE_TYPE HEADER_SUB_TYPE
, TFH.DATE_RAISED DATE_HEADER_RAISED
, TBD.NAME ENROLLMENT_AGREEMENT
, DECODE(TBD.TYPE
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('BOOKING_DEAL_TYPE'
, TBD.TYPE)) AGREEMENT_TYPE
, TBD.DISCOUNT_PERCENTAGE AGREEMENT_DISCOUNT
, DECODE(TPL.TRAINING_UNIT_TYPE
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('TRAINING_UNIT'
, TPL.TRAINING_UNIT_TYPE)) TRAINING_UNIT
, DECODE(TBD.CUSTOMER_ID
, NULL
, 'I'
, 'E') ENROLLMENT_TYPE
, DECODE(TBD.CUSTOMER_ID
, NULL
, PER.FULL_NAME
, OTA_TDB_BUS.GET_FULL_NAME(STU.LAST_NAME
, TTL.MEANING
, STU.FIRST_NAME
, TDB.BUSINESS_GROUP_ID
, STU.LAST_NAME_ALT
, STU.FIRST_NAME_ALT) ) STUDENT_NAME
, ORGT.NAME ORGANIZATION_NAME
, CUS.CUSTOMER_NAME CUSTOMER_NAME
, DECODE(TDB.CUSTOMER_ID
, NULL
, SPN.FULL_NAME
, OTA_TDB_BUS.GET_FULL_NAME(CON.LAST_NAME
, CTI_LOOK.MEANING
, CON.FIRST_NAME
, TDB.BUSINESS_GROUP_ID
, CON.LAST_NAME_ALT
, CON.FIRST_NAME_ALT)) CONTACT_NAME
, EVT.TITLE EVENT_TITLE
, TAV.VERSION_NAME ACTIVITY_NAME
, EVT.COURSE_START_DATE EVENT_START_DATE
, TO_CHAR(NULL) RESOURCE_NAME
, TO_CHAR(NULL) RESOURCE_TYPE
, TO_DATE(NULL) RESOURCE_BOOKING_FROM
, TO_DATE(NULL) RESOURCE_BOOKING_TO
, TO_CHAR(NULL) SUPPLIER_NAME
, '_DF:OTA:OTA_FINANCE_LINES:TFL'
, '_DF:OTA:OTA_TFL_TRANSFER:TFL'
, TFL.FINANCE_HEADER_ID HEADER_ID
, TFL.BOOKING_DEAL_ID ENROLLMENT_AGREEMENT_ID
, TFL.BOOKING_ID ENROLLMENT_ID
, TO_NUMBER(NULL) VENDOR_ID
, DECODE(TDB.CUSTOMER_ID
, NULL
, TDB.DELEGATE_PERSON_ID
, TDB.DELEGATE_CONTACT_ID) STUDENT_ID
, TDB.ORGANIZATION_ID ORGANIZATION_ID
, TDB.CUSTOMER_ID CUSTOMER_ID
, EVT.EVENT_ID EVENT_ID
, TAV.ACTIVITY_VERSION_ID ACTIVITY_VERSION_ID
, TO_NUMBER(NULL) RESOURCE_BOOKING_ID
, TFL.RESOURCE_ALLOCATION_ID RESOURCE_ALLOCATION_ID
, TAV.RCO_ID RCO_ID
, EVT.OFFERING_ID OFFERING_ID
FROM PER_ALL_PEOPLE_F PER
, PER_ALL_PEOPLE_F SPN
, HR_ALL_ORGANIZATION_UNITS_TL ORGT
, RA_CUSTOMERS CUS
, RA_CONTACTS CON
, AR_LOOKUPS CTI_LOOK
, AR_LOOKUPS TTL
, RA_CONTACTS STU
, OTA_ACTIVITY_VERSIONS TAV
, OTA_EVENTS EVT
, OTA_DELEGATE_BOOKINGS TDB
, OTA_PRICE_LISTS TPL
, OTA_BOOKING_DEALS TBD
, OTA_FINANCE_LINES TFL
, OTA_FINANCE_HEADERS TFH
WHERE TFL.LINE_TYPE = 'E'
AND TDB.BOOKING_ID = TFL.BOOKING_ID
AND TBD.BOOKING_DEAL_ID (+) = TFL.BOOKING_DEAL_ID
AND TPL.PRICE_LIST_ID (+) = TBD.PRICE_LIST_ID
AND TDB.DELEGATE_CONTACT_ID = STU.CONTACT_ID (+)
AND TDB.EVENT_ID = EVT.EVENT_ID
AND TDB.CUSTOMER_ID = CUS.CUSTOMER_ID (+)
AND TAV.ACTIVITY_VERSION_ID (+) = EVT.ACTIVITY_VERSION_ID
AND CON.CONTACT_ID (+) = TDB.CONTACT_ID
AND CTI_LOOK.LOOKUP_TYPE (+) = 'CONTACT_TITLE'
AND CTI_LOOK.LOOKUP_CODE (+) = CON.TITLE
AND TTL.LOOKUP_CODE (+) = STU.TITLE
AND TTL.LOOKUP_TYPE (+) = 'CONTACT_TITLE'
AND TDB.ORGANIZATION_ID = ORGT.ORGANIZATION_ID (+)
AND DECODE(ORGT.ORGANIZATION_ID
, NULL
, 'X'
, ORGT.LANGUAGE) = DECODE(ORGT.ORGANIZATION_ID
, NULL
, 'X'
, USERENV('LANG'))
AND TDB.SPONSOR_PERSON_ID = SPN.PERSON_ID (+)
AND TDB.DATE_BOOKING_PLACED BETWEEN NVL(SPN.EFFECTIVE_START_DATE
, TDB.DATE_BOOKING_PLACED)
AND NVL(SPN.EFFECTIVE_END_DATE
, TDB.DATE_BOOKING_PLACED)
AND TDB.DELEGATE_PERSON_ID = PER.PERSON_ID (+)
AND NVL(EVT.COURSE_START_DATE
, TDB.DATE_BOOKING_PLACED) BETWEEN NVL(PER.EFFECTIVE_START_DATE
, NVL(EVT.COURSE_START_DATE
, TDB.DATE_BOOKING_PLACED))
AND NVL(PER.EFFECTIVE_END_DATE
, NVL(EVT.COURSE_START_DATE
, TDB.DATE_BOOKING_PLACED))
AND TFL.FINANCE_HEADER_ID = TFH.FINANCE_HEADER_ID UNION SELECT TFL.FINANCE_LINE_ID LINE_ID
, HR_BIS.BIS_DECODE_LOOKUP('FINANCE_LINE_TYPE'
, TFL.LINE_TYPE) LINE_TYPE
, TFL.SEQUENCE_NUMBER SEQUENCE
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, TFL.CANCELLED_FLAG) LINE_CANCELLED
, OTA_GENERAL.FND_CURRENCY_NAME(TFL.CURRENCY_CODE) CURRENCY
, TFL.STANDARD_AMOUNT STANDARD_AMOUNT
, TFL.MONEY_AMOUNT MONEY_AMOUNT
, TFL.UNITARY_AMOUNT UNITARY_AMOUNT
, TFL.DATE_RAISED DATE_RAISED
, TFL.COMMENTS COMMENTS
, HR_BIS.BIS_DECODE_LOOKUP('GL_TRANSFER_STATUS'
, TFL.TRANSFER_STATUS) TRANSFER_STATUS
, TFL.TRANSFER_DATE TRANSFER_DATE
, TFL.TRANSFER_MESSAGE TRANSFER_MESSAGE
, HR_BIS.BIS_DECODE_LOOKUP('FINANCE_HEADER_TYPE'
, TFH.TYPE) HEADER_TYPE
, TFH.RECEIVABLE_TYPE HEADER_SUB_TYPE
, TFH.DATE_RAISED DATE_HEADER_RAISED
, TDB.NAME ENROLLMENT_AGREEMENT
, DECODE(TDB.TYPE
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('BOOKING_DEAL_TYPE'
, TDB.TYPE)) AGREEMENT_TYPE
, TDB.DISCOUNT_PERCENTAGE AGREEMENT_DISCOUNT
, DECODE(TPL.TRAINING_UNIT_TYPE
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('TRAINING_UNIT'
, TPL.TRAINING_UNIT_TYPE)) TRAINING_UNIT
, TO_CHAR(NULL) ENROLLMENT_TYPE
, TO_CHAR(NULL) STUDENT_NAME
, TO_CHAR(NULL) ORGANIZATION_NAME
, RC.CUSTOMER_NAME CUSTOMER_NAME
, TO_CHAR(NULL) CONTACT_NAME
, TO_CHAR(NULL) EVENT_TITLE
, TO_CHAR(NULL) ACTIVITY_NAME
, TO_DATE(NULL) EVENT_START_DATE
, TO_CHAR(NULL) RESOURCE_NAME
, TO_CHAR(NULL) RESOURCE_TYPE
, TO_DATE(NULL) RESOURCE_BOOKING_FROM
, TO_DATE(NULL) RESOURCE_BOOKING_TO
, TO_CHAR(NULL) SUPPLIER_NAME
, '_DF:OTA:OTA_FINANCE_LINES:TFL'
, '_DF:OTA:OTA_TFL_TRANSFER:TFL'
, TFL.FINANCE_HEADER_ID HEADER_ID
, TFL.BOOKING_DEAL_ID ENROLLMENT_AGREEEMENT_ID
, TFL.BOOKING_ID ENROLLMENT_ID
, TO_NUMBER(NULL) VENDOR_ID
, TO_NUMBER(NULL) STUDENT_ID
, TO_NUMBER(NULL) ORGANIZATION_ID
, RC.CUSTOMER_ID CUSTOMER_ID
, TO_NUMBER(NULL) EVENT_ID
, TO_NUMBER(NULL) ACTIVITY_VERSION_ID
, TO_NUMBER(NULL) RESOURCE_BOOKING_ID
, TFL.RESOURCE_ALLOCATION_ID RESOURCE_ALLOCATION_ID
, TO_NUMBER(NULL) RCO_ID
, TO_NUMBER(NULL) OFFERING_ID
FROM OTA_FINANCE_LINES TFL
, RA_CUSTOMERS RC
, OTA_BOOKING_DEALS_V TDB
, OTA_PRICE_LISTS TPL
, OTA_FINANCE_HEADERS TFH
WHERE TFL.LINE_TYPE = 'P'
AND RC.CUSTOMER_ID = TDB.CUSTOMER_ID
AND TDB.BOOKING_DEAL_ID (+) = TFL.BOOKING_DEAL_ID
AND TPL.PRICE_LIST_ID (+) = TDB.PRICE_LIST_ID
AND TFL.FINANCE_HEADER_ID = TFH.FINANCE_HEADER_ID UNION SELECT TFL.FINANCE_LINE_ID LINE_ID
, HR_BIS.BIS_DECODE_LOOKUP('FINANCE_LINE_TYPE'
, TFL.LINE_TYPE) LINE_TYPE
, TFL.SEQUENCE_NUMBER SEQUENCE
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, TFL.CANCELLED_FLAG) LINE_CANCELLED
, OTA_GENERAL.FND_CURRENCY_NAME(TFL.CURRENCY_CODE) CURRENCY
, TFL.STANDARD_AMOUNT STANDARD_AMOUNT
, TFL.MONEY_AMOUNT MONEY_AMOUNT
, TFL.UNITARY_AMOUNT UNITARY_AMOUNT
, TFL.DATE_RAISED DATE_RAISED
, TFL.COMMENTS COMMENTS
, HR_BIS.BIS_DECODE_LOOKUP('GL_TRANSFER_STATUS'
, TFL.TRANSFER_STATUS) TRANSFER_STATUS
, TFL.TRANSFER_DATE TRANSFER_DATE
, TFL.TRANSFER_MESSAGE TRANSFER_MESSAGE
, HR_BIS.BIS_DECODE_LOOKUP('FINANCE_HEADER_TYPE'
, TFH.TYPE) HEADER_TYPE
, TFH.RECEIVABLE_TYPE HEADER_SUB_TYPE
, TFH.DATE_RAISED DATE_HEADER_RAISED
, TO_CHAR(NULL) ENROLLMENT_AGREEMENT
, TO_CHAR(NULL) AGREEMENT_TYPE
, TO_NUMBER(NULL) AGREEMENT_DISCOUNT
, TO_CHAR(NULL) TRAINING_UNIT
, TO_CHAR(NULL) ENROLLMENT_TYPE
, TO_CHAR(NULL) STUDENT_NAME
, TO_CHAR(NULL) ORGANIZATION_ID
, TO_CHAR(NULL) CUSTOMER_NAME
, TO_CHAR(NULL) CONTACT_NAME
, EVT.TITLE EVENT_TITLE
, TAV.VERSION_NAME ACTIVITY_NAME
, EVT.COURSE_START_DATE COURSE_START_DATE
, TSR.NAME RESOURCE_NAME
, DECODE(TSR.RESOURCE_TYPE
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('RESOURCE_TYPE'
, TSR.RESOURCE_TYPE)) RESOURCE_TYPE
, TRB.REQUIRED_DATE_FROM RESOURCE_BOOKING_FROM
, TRB.REQUIRED_DATE_TO RESOURCE_BOOKING_TO
, VEN.VENDOR_NAME SUPPLIER_NAME
, '_DF:OTA:OTA_FINANCE_LINES:TFL'
, '_DF:OTA:OTA_TFL_TRANSFER:TFL'
, TFL.FINANCE_HEADER_ID HEADER_ID
, TFL.BOOKING_DEAL_ID ENROLLMENT_AGREEMENT_ID
, TO_NUMBER(NULL) ENROLLMENT_ID
, TSR.VENDOR_ID VENDOR_ID
, TO_NUMBER(NULL) STUDENT_ID
, TO_NUMBER(NULL) ORGANIZATION_ID
, TO_NUMBER(NULL) CUSTOMER_ID
, EVT.EVENT_ID EVENT_ID
, TAV.ACTIVITY_VERSION_ID ACTIVITY_VERSION_ID
, TFL.RESOURCE_BOOKING_ID RESOURCE_BOOKING_ID
, TFL.RESOURCE_ALLOCATION_ID RESOURCE_ALLOCATION_ID
, TAV.RCO_ID RCO_ID
, EVT.OFFERING_ID OFFERING_ID
FROM OTA_FINANCE_LINES TFL
, OTA_RESOURCE_BOOKINGS TRB
, OTA_EVENTS EVT
, OTA_ACTIVITY_VERSIONS TAV
, OTA_SUPPLIABLE_RESOURCES TSR
, OTA_FINANCE_HEADERS TFH
, PO_VENDORS VEN
WHERE TFL.FINANCE_HEADER_ID = TFH.FINANCE_HEADER_ID
AND TFL.LINE_TYPE = 'V'
AND EVT.EVENT_ID (+) = TRB.EVENT_ID
AND TRB.RESOURCE_BOOKING_ID = TFL.RESOURCE_BOOKING_ID
AND TAV.ACTIVITY_VERSION_ID (+) = EVT.ACTIVITY_VERSION_ID
AND TSR.SUPPLIED_RESOURCE_ID (+) = TRB.SUPPLIED_RESOURCE_ID
AND TSR.VENDOR_ID = VEN.VENDOR_ID (+) UNION SELECT TFL.FINANCE_LINE_ID LINE_ID
, HR_BIS.BIS_DECODE_LOOKUP('FINANCE_LINE_TYPE'
, TFL.LINE_TYPE) LINE_TYPE
, TFL.SEQUENCE_NUMBER SEQUENCE
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, TFL.CANCELLED_FLAG) LINE_CANCELLED
, OTA_GENERAL.FND_CURRENCY_NAME(TFL.CURRENCY_CODE) CURRENCY
, TFL.STANDARD_AMOUNT STANDARD_AMOUNT
, TFL.MONEY_AMOUNT MONEY_AMOUNT
, TFL.UNITARY_AMOUNT UNITARY_AMOUNT
, TFL.DATE_RAISED DATE_RAISED
, TFL.COMMENTS COMMENTS
, HR_BIS.BIS_DECODE_LOOKUP('GL_TRANSFER_STATUS'
, TFL.TRANSFER_STATUS) TRANSFER_STATUS
, TFL.TRANSFER_DATE TRANSFER_DATE
, TFL.TRANSFER_MESSAGE TRANSFER_MESSAGE
, HR_BIS.BIS_DECODE_LOOKUP('FINANCE_HEADER_TYPE'
, TFH.TYPE) HEADER_TYPE
, TFH.RECEIVABLE_TYPE HEADER_SUB_TYPE
, TFH.DATE_RAISED DATE_HEADER_RAISED
, TO_CHAR(NULL) ENROLLMENT_AGREEMENT
, TO_CHAR(NULL) AGREEMENT_TYPE
, TO_NUMBER(NULL) AGREEMENT_DISCOUNT
, TO_CHAR(NULL) TRAINING_UNIT
, TO_CHAR(NULL) ENROLLMENT_TYPE
, TO_CHAR(NULL) STUDENT_NAME
, TO_CHAR(NULL) ORGANIZATION_NAME
, TO_CHAR(NULL) CUSTOMER_NAME
, TO_CHAR(NULL) CONTACT_NAME
, TO_CHAR(NULL) EVENT_TITLE
, TO_CHAR(NULL) ACTIVITY_NAME
, TO_DATE(NULL) EVENT_START_DATE
, TO_CHAR(NULL) RESOURCE_NAME
, TO_CHAR(NULL) RESOURCE_TYPE
, TO_DATE(NULL) RESOURCE_REQUIRED_FROM
, TO_DATE(NULL) RESOURCE_REQUIRED_TO
, TO_CHAR(NULL) SUPPLIER_NAME
, '_DF:OTA:OTA_FINANCE_LINES:TFL'
, '_DF:OTA:OTA_TFL_TRANSFER:TFL'
, TFL.FINANCE_HEADER_ID HEADER_ID
, TFL.BOOKING_DEAL_ID ENROLLMENT_AGREEMENT_ID
, TO_NUMBER(NULL) ENROLLMENT_ID
, TO_NUMBER(NULL) VENDOR_ID
, TO_NUMBER(NULL) STUDENT_ID
, TO_NUMBER(NULL) ORGANIZATION_ID
, TO_NUMBER(NULL) CUSTOMER_ID
, TO_NUMBER(NULL) EVENT_ID
, TO_NUMBER(NULL) ACTIVITY_VERSION_ID
, TFL.RESOURCE_BOOKING_ID RESOURCE_BOOKING_ID
, TFL.RESOURCE_ALLOCATION_ID RESOURCE_ALLOCATION_ID
, TO_NUMBER(NULL) RCO_ID
, TO_NUMBER(NULL) OFFERING_ID
FROM OTA_FINANCE_LINES TFL
, OTA_FINANCE_HEADERS TFH
WHERE TFL.FINANCE_HEADER_ID = TFH.FINANCE_HEADER_ID
AND TFL.LINE_TYPE NOT IN ('E'
, 'R'
, 'V'
, 'P') WITH READ ONLY

Columns