Product: | OTA - Learning Management |
---|---|
Description: | Business view template from which the flexfield view is generated. |
Implementation/DBA Data: | APPS.OTFV_FINANCE_HEADERS |
SELECT TFH.FINANCE_HEADER_ID HEADER_ID
, TFH.SUPERCEDING_HEADER_ID HEADER_SUPERSEDES
, TFH.COMMENTS FINANCE_HEADER_COMMENTS
, TFH.DATE_RAISED DATE_RAISED
, HR_BIS.BIS_DECODE_LOOKUP('FINANCE_HEADER_TYPE'
, TFH.TYPE) HEADER_TYPE
, TFH.RECEIVABLE_TYPE HEADER_SUB_TYPE
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, TFH.CANCELLED_FLAG) CANCELLED_FLAG
, OTA_GENERAL.FND_CURRENCY_NAME(TFH.CURRENCY_CODE) CURRENCY
, TFH.ADMINISTRATOR ADMINISTRATOR
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, TFH.PAYMENT_STATUS_FLAG) HEADER_CANCELLED
, DECODE(TFH.PAYMENT_METHOD
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('PAYMENT_METHOD'
, TFH.PAYMENT_METHOD)) HEADER_PAID
, HR_BIS.BIS_DECODE_LOOKUP('GL_TRANSFER_STATUS'
, TFH.TRANSFER_STATUS) TRANSFER_STATUS
, TFH.TRANSFER_DATE TRANSFER_DATE
, TFH.EXTERNAL_REFERENCE EXTERNAL_REFERENCE
, TFH.TRANSFER_MESSAGE TRANSFER_MESSAGE
, TFH.PAYING_COST_CENTER PAYING_COST_CENTER
, TFH.RECEIVING_COST_CENTER RECEIVING_COST_CENTER
, TFH.INVOICE_CONTACT INVOICE_CONTACT
, TFH.INVOICE_ADDRESS INVOICE_ADDRESS
, OTA_TFH_API_BUSINESS_RULES2.INVOICE_FULL_AMOUNT(TFH.FINANCE_HEADER_ID
, TFH.CURRENCY_CODE) INVOICE_FULL_AMOUNT
, VEN.VENDOR_NAME SUPPLIER_NAME
, CON.FIRST_NAME||' '||CON.LAST_NAME CONTACT_NAME
, FND.USER_NAME AUTHORIZED_BY
, CUS.CUSTOMER_NAME CUSTOMER_NAME
, ORGT.NAME ORGANIZATION_NAME
, RAA.ADDRESS1 ADDRESS_LINE1
, RAA.ADDRESS2 ADDRESS_LINE2
, RAA.ADDRESS3 ADDRESS_LINE3
, RAA.ADDRESS4 ADDRESS_LINE4
, RAA.CITY CITY
, RAA.POSTAL_CODE POSTAL_CODE
, RAA.PROVINCE PROVINCE
, RAA.COUNTY COUNTY
, SOB.NAME SET_OF_BOOKS_FROM
, SOB1.NAME SET_OF_BOOKS_TO
, '_KF:SQLGL:GL#:GCC'
, '_KF:SQLGL:GL#:GCC1'
, '_DF:OTA:OTA_FINANCE_HEADERS:TFH'
, TFH.TRANSFER_FROM_SET_OF_BOOKS_ID TRANSFER_FROM_SET_OF_BOOKS_ID
, TFH.TRANSFER_TO_SET_OF_BOOKS_ID TRANSFER_TO_SET_OF_BOOKS_ID
, TFH.TRANSFER_FROM_CC_ID TRANSFER_FROM_CC_ID
, TRANSFER_TO_CC_ID TRANSFER_TO_CC_ID
, TFH.ADDRESS_ID ADDRESS_ID
, TFH.AUTHORIZER_PERSON_ID APPROVED_BY_ID
, TFH.CONTACT_ID CONTACT_ID
, TFH.VENDOR_ID SUPPLIER_ID
, TFH.CUSTOMER_ID CUSTOMER_ID
, TFH.ORGANIZATION_ID ORGANIZATION_ID
FROM OTA_FINANCE_HEADERS TFH
, RA_ADDRESSES_ALL RAA
, RA_CUSTOMERS CUS
, PO_VENDORS VEN
, RA_CONTACTS CON
, FND_USER FND
, GL_CODE_COMBINATIONS GCC
, GL_CODE_COMBINATIONS GCC1
, GL_SETS_OF_BOOKS SOB
, GL_SETS_OF_BOOKS SOB1
, HR_ALL_ORGANIZATION_UNITS_TL ORGT
WHERE TFH.ORGANIZATION_ID = ORGT.ORGANIZATION_ID
AND ORGT.LANGUAGE = USERENV('LANG')
AND TFH.VENDOR_ID = VEN.VENDOR_ID (+)
AND TFH.CONTACT_ID = CON.CONTACT_ID (+)
AND TFH.ADDRESS_ID = RAA.ADDRESS_ID (+)
AND TFH.CUSTOMER_ID = CUS.CUSTOMER_ID (+)
AND TFH.AUTHORIZER_PERSON_ID = FND.USER_ID (+)
AND TFH.TRANSFER_FROM_CC_ID = GCC.CODE_COMBINATION_ID (+)
AND TFH.TRANSFER_TO_CC_ID = GCC1.CODE_COMBINATION_ID (+)
AND TFH.TRANSFER_FROM_SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID (+)
AND TFH.TRANSFER_TO_SET_OF_BOOKS_ID = SOB1.SET_OF_BOOKS_ID (+)
AND TFH.TYPE IN ('R'
, 'CT'
, 'C') UNION SELECT TFH.FINANCE_HEADER_ID HEADER_ID
, TFH.SUPERCEDING_HEADER_ID HEADER_SUPERSEDES
, TFH.COMMENTS FINANCE_HEADER_COMMENTS
, TFH.DATE_RAISED DATE_RAISED
, HR_BIS.BIS_DECODE_LOOKUP('FINANCE_HEADER_TYPE'
, TFH.TYPE) HEADER_TYPE
, TFH.RECEIVABLE_TYPE HEADER_SUB_TYPE
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, TFH.CANCELLED_FLAG) CANCELLED_FLAG
, OTA_GENERAL.FND_CURRENCY_NAME(TFH.CURRENCY_CODE) CURRENCY
, TFH.ADMINISTRATOR ADMINISTRATOR
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, TFH.PAYMENT_STATUS_FLAG) HEADER_CANCELLED
, DECODE(TFH.PAYMENT_METHOD
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('PAYMENT_METHOD'
, TFH.PAYMENT_METHOD)) HEADER_PAID
, HR_BIS.BIS_DECODE_LOOKUP('GL_TRANSFER_STATUS'
, TFH.TRANSFER_STATUS) TRANSFER_STATUS
, TFH.TRANSFER_DATE TRANSFER_DATE
, TFH.EXTERNAL_REFERENCE EXTERNAL_REFERENCE
, TFH.TRANSFER_MESSAGE TRANSFER_MESSAGE
, TFH.PAYING_COST_CENTER PAYING_COST_CENTER
, TFH.RECEIVING_COST_CENTER RECEIVING_COST_CENTER
, TFH.INVOICE_CONTACT INVOICE_CONTACT
, TFH.INVOICE_ADDRESS INVOICE_ADDRESS
, OTA_TFH_API_BUSINESS_RULES2.INVOICE_FULL_AMOUNT(TFH.FINANCE_HEADER_ID
, TFH.CURRENCY_CODE) INVOICE_FULL_AMOUNT
, VEN.VENDOR_NAME SUPPLIER_NAME
, CON.FIRST_NAME||' '||CON.LAST_NAME CONTACT_NAME
, FND.USER_NAME AUTHORIZED_BY
, CUS.CUSTOMER_NAME CUSTOMER_NAME
, ORGT.NAME ORGANIZATION_NAME
, POS.ADDRESS_LINE1 ADDRESS_LINE1
, POS.ADDRESS_LINE2 ADDRESS_LINE2
, POS.ADDRESS_LINE3 ADDRESS_LINE3
, POS.ADDRESS_LINES_ALT ADDRESS_LINE4
, POS.CITY CITY
, POS.ZIP POSTAL_CODE
, POS.STATE PROVINCE
, POS.COUNTY COUNTY
, SOB.NAME SET_OF_BOOKS_FROM
, SOB1.NAME SET_OF_BOOKS_TO
, '_KF:SQLGL:GL#:GCC'
, '_KF:SQLGL:GL#:GCC1'
, '_DF:OTA:OTA_FINANCE_HEADERS:TFH'
, TFH.TRANSFER_FROM_SET_OF_BOOKS_ID TRANSFER_FROM_SET_OF_BOOKS_ID
, TFH.TRANSFER_TO_SET_OF_BOOKS_ID TRANSFER_TO_SET_OF_BOOKS_ID
, TFH.TRANSFER_FROM_CC_ID TRANSFER_FROM_CC_ID
, TRANSFER_TO_CC_ID TRANSFER_TO_CC_ID
, TFH.ADDRESS_ID ADDRESS_ID
, TFH.AUTHORIZER_PERSON_ID APPROVED_BY_ID
, TFH.CONTACT_ID CONTACT_ID
, TFH.VENDOR_ID SUPPLIER_ID
, TFH.CUSTOMER_ID CUSTOMER_ID
, TFH.ORGANIZATION_ID ORGANIZATION_ID
FROM OTA_FINANCE_HEADERS TFH
, PO_VENDOR_SITES_ALL POS
, RA_CUSTOMERS CUS
, PO_VENDORS VEN
, RA_CONTACTS CON
, FND_USER FND
, GL_CODE_COMBINATIONS GCC
, GL_CODE_COMBINATIONS GCC1
, GL_SETS_OF_BOOKS SOB
, GL_SETS_OF_BOOKS SOB1
, HR_ALL_ORGANIZATION_UNITS_TL ORGT
WHERE TFH.ORGANIZATION_ID = ORGT.ORGANIZATION_ID
AND ORGT.LANGUAGE = USERENV('LANG')
AND TFH.VENDOR_ID = VEN.VENDOR_ID (+)
AND TFH.CONTACT_ID = CON.CONTACT_ID (+)
AND TFH.ADDRESS_ID = POS.VENDOR_SITE_ID (+)
AND TFH.CUSTOMER_ID = CUS.CUSTOMER_ID (+)
AND TFH.AUTHORIZER_PERSON_ID = FND.USER_ID (+)
AND TFH.TRANSFER_FROM_CC_ID = GCC.CODE_COMBINATION_ID(+)
AND TFH.TRANSFER_TO_CC_ID = GCC1.CODE_COMBINATION_ID(+)
AND TFH.TRANSFER_FROM_SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID (+)
AND TFH.TRANSFER_TO_SET_OF_BOOKS_ID = SOB1.SET_OF_BOOKS_ID (+)
AND TFH.TYPE IN ('P') WITH READ ONLY