eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.OTFV_FINANCE_HEADERS

Source

View Text - Preformatted


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 ,party.person_first_name||' '||party.person_last_name contact_name ,fnd.user_name authorized_by ,substrb(CUST_PARTY.PARTY_NAME,1,50) customer_name ,orgT.name organization_name ,LOC.address1 ADDRESS1 ,LOC.address2 ADDRESS2 ,LOC.address3 ADDRESS3 ,LOC.address4 ADDRESS4 ,LOC.city CITY ,LOC.postal_code POSTAL_CODE ,LOC.province PROVINCE ,LOC.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 ,HZ_LOCATIONS loc ,HZ_CUST_ACCT_SITES acct_site ,HZ_PARTY_SITES party_site ,po_vendors ven ,HZ_CUST_ACCOUNT_ROLES ACCT_ROLE ,HZ_PARTIES PARTY ,HZ_RELATIONSHIPS REL ,HZ_ORG_CONTACTS ORG_CONT ,HZ_CUST_ACCOUNTS CUST_ACCT ,HZ_PARTIES CUST_PARTY ,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 = ACCT_ROLE.cust_account_role_id (+) AND ACCT_ROLE.PARTY_ID = REL.PARTY_ID (+) AND ACCT_ROLE.ROLE_TYPE (+) = 'CONTACT' AND ORG_CONT.PARTY_RELATIONSHIP_ID (+) = REL.RELATIONSHIP_ID AND REL.SUBJECT_ID = PARTY.PARTY_ID (+) AND REL.RELATIONSHIP_CODE (+) = 'CONTACT_OF' AND tfh.customer_id = CUST_ACCT.cust_account_id (+) AND CUST_ACCT.PARTY_ID = CUST_PARTY.PARTY_ID (+) AND tfh.address_id = ACCT_SITE.cust_acct_site_id(+) AND PARTY_SITE.location_id = LOC.location_id (+) AND ACCT_SITE.party_site_id = PARTY_SITE.party_site_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 ALL 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 ,party.person_first_name||' '||party.person_last_name contact_name ,fnd.user_name authorized_by ,substrb(CUST_PARTY.PARTY_NAME,1,50) 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 ,po_vendors ven ,HZ_CUST_ACCOUNT_ROLES ACCT_ROLE ,HZ_PARTIES PARTY ,HZ_RELATIONSHIPS REL ,HZ_ORG_CONTACTS ORG_CONT ,HZ_CUST_ACCOUNTS CUST_ACCT ,HZ_PARTIES CUST_PARTY ,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 = ACCT_ROLE.cust_account_role_id (+) AND ACCT_ROLE.PARTY_ID = REL.PARTY_ID (+) AND ACCT_ROLE.ROLE_TYPE (+) = 'CONTACT' AND ORG_CONT.PARTY_RELATIONSHIP_ID (+) = REL.RELATIONSHIP_ID AND REL.SUBJECT_ID = PARTY.PARTY_ID (+) AND REL.RELATIONSHIP_CODE (+) = 'CONTACT_OF' AND tfh.customer_id = CUST_ACCT.cust_account_id (+) AND CUST_ACCT.PARTY_ID = CUST_PARTY.PARTY_ID (+) AND tfh.address_id = pos.vendor_site_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

View Text - HTML Formatted

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
, PARTY.PERSON_FIRST_NAME||' '||PARTY.PERSON_LAST_NAME CONTACT_NAME
, FND.USER_NAME AUTHORIZED_BY
, SUBSTRB(CUST_PARTY.PARTY_NAME
, 1
, 50) CUSTOMER_NAME
, ORGT.NAME ORGANIZATION_NAME
, LOC.ADDRESS1 ADDRESS1
, LOC.ADDRESS2 ADDRESS2
, LOC.ADDRESS3 ADDRESS3
, LOC.ADDRESS4 ADDRESS4
, LOC.CITY CITY
, LOC.POSTAL_CODE POSTAL_CODE
, LOC.PROVINCE PROVINCE
, LOC.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
, HZ_LOCATIONS LOC
, HZ_CUST_ACCT_SITES ACCT_SITE
, HZ_PARTY_SITES PARTY_SITE
, PO_VENDORS VEN
, HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
, HZ_PARTIES PARTY
, HZ_RELATIONSHIPS REL
, HZ_ORG_CONTACTS ORG_CONT
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES CUST_PARTY
, 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 = ACCT_ROLE.CUST_ACCOUNT_ROLE_ID (+)
AND ACCT_ROLE.PARTY_ID = REL.PARTY_ID (+)
AND ACCT_ROLE.ROLE_TYPE (+) = 'CONTACT'
AND ORG_CONT.PARTY_RELATIONSHIP_ID (+) = REL.RELATIONSHIP_ID
AND REL.SUBJECT_ID = PARTY.PARTY_ID (+)
AND REL.RELATIONSHIP_CODE (+) = 'CONTACT_OF'
AND TFH.CUSTOMER_ID = CUST_ACCT.CUST_ACCOUNT_ID (+)
AND CUST_ACCT.PARTY_ID = CUST_PARTY.PARTY_ID (+)
AND TFH.ADDRESS_ID = ACCT_SITE.CUST_ACCT_SITE_ID(+)
AND PARTY_SITE.LOCATION_ID = LOC.LOCATION_ID (+)
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_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 ALL 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
, PARTY.PERSON_FIRST_NAME||' '||PARTY.PERSON_LAST_NAME CONTACT_NAME
, FND.USER_NAME AUTHORIZED_BY
, SUBSTRB(CUST_PARTY.PARTY_NAME
, 1
, 50) 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
, PO_VENDORS VEN
, HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
, HZ_PARTIES PARTY
, HZ_RELATIONSHIPS REL
, HZ_ORG_CONTACTS ORG_CONT
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES CUST_PARTY
, 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 = ACCT_ROLE.CUST_ACCOUNT_ROLE_ID (+)
AND ACCT_ROLE.PARTY_ID = REL.PARTY_ID (+)
AND ACCT_ROLE.ROLE_TYPE (+) = 'CONTACT'
AND ORG_CONT.PARTY_RELATIONSHIP_ID (+) = REL.RELATIONSHIP_ID
AND REL.SUBJECT_ID = PARTY.PARTY_ID (+)
AND REL.RELATIONSHIP_CODE (+) = 'CONTACT_OF'
AND TFH.CUSTOMER_ID = CUST_ACCT.CUST_ACCOUNT_ID (+)
AND CUST_ACCT.PARTY_ID = CUST_PARTY.PARTY_ID (+)
AND TFH.ADDRESS_ID = POS.VENDOR_SITE_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