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.

[Home] [Help]

VIEW: APPS.AP_ENC_GL_PAY_V

Source

View Text - Preformatted


SELECT r.je_header_id je_header_id,
  r.je_line_num je_line_num,
  200 application_id,
  D.set_of_books_id SET_OF_BOOKS_ID ,
  D.org_id ORG_ID ,
  'PAY' trx_class,
  NULL trx_class_name,
  ipmv.payment_method_name trx_type_name,
  c.payment_method_lookup_code trx_type_c,
  c.check_number trx_number_n,
  TO_CHAR(c.check_number) trx_number_displayed,
  r.subledger_doc_sequence_id doc_sequence_id,
  NULL doc_sequence_name,
  r.subledger_doc_sequence_value doc_sequence_value,
  jel.accounted_cr accounted_cr,
  jel.accounted_dr accounted_dr,
  jel.entered_cr entered_cr,
  jel.entered_dr entered_dr,
  to_date(null) accounting_date,
  NULL acct_line_type,
  NULL acct_line_type_name,
  to_number(NULL) aeh_id,
  to_number(NULL) ael_id,
  NULL AEL_TABLE,
  NULL ae_line_reference,
  jel.code_combination_id code_combination_id,
  jeh.currency_code currency_code,
  jeh.currency_conversion_date currency_conversion_date,
  jeh.currency_conversion_rate currency_conversion_rate,
  jeh.currency_conversion_type currency_conversion_type,
  NULL currency_user_conversion_type,
  NULL gl_transfer_status,
  NULL gl_transfer_status_name,
  NULL transfer_status_detail_name,
  to_number(NULL) source_id,
  NULL source_table,
  AT.name tax_code,
  'SUPPLIER' THIRD_PARTY_TYPE,
  to_number(NULL) third_party_id,
  v.segment1 third_party_number,
  v.vendor_name third_party_name,
  NULL third_party_sub_id,
  NULL third_party_sub_name,
  c.check_date trx_date,
  c.check_id trx_hdr_id,
  'APC' trx_hdr_table,
  jeh.je_category je_category,
  NULL user_je_category_name,
  c.last_update_date last_update_date,
  c.last_updated_by last_updated_by,
  c.creation_date creation_date,
  c.created_by created_by,
  c.last_update_login last_update_login,
  d.request_id request_id,
  d.program_application_id program_application_id,
  d.program_id program_id,
  d.program_update_date program_update_date,
  to_number(NULL) accounting_event_number, 
  NULL accounting_event_type,
  NULL accounting_event_type_name,
  to_number(NULL) accounting_line_number,
  jeh.description comments,
  I.INVOICE_TYPE_LOOKUP_CODE TRX_TYPE_C_I ,
  I.invoice_num applied_to_trx_hdr_number_disp,
  I.invoice_num applied_to_trx_hdr_number_c ,
  NULL aeh_accounting_error_code,
  NULL aeh_accounting_error_name,
  NULL ael_accounting_error_code,
  NULL ael_accounting_error_name,
  c.currency_code trx_hdr_currency,
  /*---------------------------------------------------------------*/
  /* AP Payment Specific columns */
  /*---------------------------------------------------------------*/
  c.bank_account_name bank_account_name,
  'AP_INVOICES' applied_to_trx_hdr_table,
  i.invoice_id applied_to_trx_hdr_id,
  i.invoice_date applied_to_trx_date,
  i.invoice_currency_code applied_to_trx_hdr_currency,
  /*---------------------------------------------------------------*/
  /* Columns for cash basis accounting */
  /*---------------------------------------------------------------*/
  d.old_dist_line_number trx_line_number,
  d.line_type_lookup_code trx_line_type,
  l.displayed_field trx_line_type_name,
  ph.segment1 po_order_number,
  to_number(NULL) po_order_release_num,
  to_number(NULL) rcv_receipt_num,
  /*---------------------------------------------------------------*/
  /* AP Reconciliation Specific columns */
  /*---------------------------------------------------------------*/
  c.cleared_date payment_cleared_date,
  sh.statement_number bank_statement_number,
  NULL payment_recon_currency,
  NULL bank_statement_line_number,
  sh.doc_sequence_id bank_statement_doc_seq_id,
  NULL bank_statement_doc_seq_name,
  sh.doc_sequence_value bank_statement_doc_seq_value
 FROM gl_lookups l2,
  ap_lookup_codes l,
  po_headers_all ph,
  po_distributions_all pd,
  po_vendors v,
  ap_invoice_distributions_all d,
  ap_invoices_all i,
  gl_je_headers jeh,
  gl_je_lines jel,
  gl_import_references r,
  iby_payment_methods_vl ipmv,
  ap_tax_codes_all at,
  ap_checks_all c,
  ce_statement_lines sl,
  ce_statement_headers sh,
  ce_statement_reconciliations csr,
  ap_encumbrance_lines_all ael
WHERE l2.lookup_type = 'SUBLDGR_DRILLDOWN_TRANS_TYPE'
AND l2.lookup_code = NVL(r.reference_10,'CASH')
AND l.lookup_type = 'INVOICE DISTRIBUTION TYPE'
AND l.lookup_code = d.line_type_lookup_code
AND ipmv.payment_method_code = c.payment_method_code
AND ph.po_header_id(+) = pd.po_header_id
AND pd.po_distribution_id(+) = d.po_distribution_id
AND i.invoice_id = d.invoice_id
AND i.vendor_id = v.vendor_id
and jeh.je_source ='Payables'
AND jeh.je_header_id = jel.je_header_id
AND jel.je_header_id = r.je_header_id
AND jel.je_line_num = r.je_line_num
AND AT.tax_id(+) = d.tax_code_id
AND sl.statement_header_id = sh.statement_header_id(+)
AND csr.statement_line_id = sl.statement_line_id(+)
AND c.check_id = csr.reference_id(+)
AND csr.reference_type(+) = 'PAYMENT'
AND csr.current_record_flag(+) = 'Y'
AND AEL.INVOICE_DISTRIBUTION_ID = d.old_distribution_id
AND R.GL_SL_LINK_ID = AEL.GL_SL_LINK_ID
AND to_char(c.check_id) = r.reference_3
AND to_char(i.invoice_id) = r.reference_2

View Text - HTML Formatted

SELECT R.JE_HEADER_ID JE_HEADER_ID
, R.JE_LINE_NUM JE_LINE_NUM
, 200 APPLICATION_ID
, D.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, D.ORG_ID ORG_ID
, 'PAY' TRX_CLASS
, NULL TRX_CLASS_NAME
, IPMV.PAYMENT_METHOD_NAME TRX_TYPE_NAME
, C.PAYMENT_METHOD_LOOKUP_CODE TRX_TYPE_C
, C.CHECK_NUMBER TRX_NUMBER_N
, TO_CHAR(C.CHECK_NUMBER) TRX_NUMBER_DISPLAYED
, R.SUBLEDGER_DOC_SEQUENCE_ID DOC_SEQUENCE_ID
, NULL DOC_SEQUENCE_NAME
, R.SUBLEDGER_DOC_SEQUENCE_VALUE DOC_SEQUENCE_VALUE
, JEL.ACCOUNTED_CR ACCOUNTED_CR
, JEL.ACCOUNTED_DR ACCOUNTED_DR
, JEL.ENTERED_CR ENTERED_CR
, JEL.ENTERED_DR ENTERED_DR
, TO_DATE(NULL) ACCOUNTING_DATE
, NULL ACCT_LINE_TYPE
, NULL ACCT_LINE_TYPE_NAME
, TO_NUMBER(NULL) AEH_ID
, TO_NUMBER(NULL) AEL_ID
, NULL AEL_TABLE
, NULL AE_LINE_REFERENCE
, JEL.CODE_COMBINATION_ID CODE_COMBINATION_ID
, JEH.CURRENCY_CODE CURRENCY_CODE
, JEH.CURRENCY_CONVERSION_DATE CURRENCY_CONVERSION_DATE
, JEH.CURRENCY_CONVERSION_RATE CURRENCY_CONVERSION_RATE
, JEH.CURRENCY_CONVERSION_TYPE CURRENCY_CONVERSION_TYPE
, NULL CURRENCY_USER_CONVERSION_TYPE
, NULL GL_TRANSFER_STATUS
, NULL GL_TRANSFER_STATUS_NAME
, NULL TRANSFER_STATUS_DETAIL_NAME
, TO_NUMBER(NULL) SOURCE_ID
, NULL SOURCE_TABLE
, AT.NAME TAX_CODE
, 'SUPPLIER' THIRD_PARTY_TYPE
, TO_NUMBER(NULL) THIRD_PARTY_ID
, V.SEGMENT1 THIRD_PARTY_NUMBER
, V.VENDOR_NAME THIRD_PARTY_NAME
, NULL THIRD_PARTY_SUB_ID
, NULL THIRD_PARTY_SUB_NAME
, C.CHECK_DATE TRX_DATE
, C.CHECK_ID TRX_HDR_ID
, 'APC' TRX_HDR_TABLE
, JEH.JE_CATEGORY JE_CATEGORY
, NULL USER_JE_CATEGORY_NAME
, C.LAST_UPDATE_DATE LAST_UPDATE_DATE
, C.LAST_UPDATED_BY LAST_UPDATED_BY
, C.CREATION_DATE CREATION_DATE
, C.CREATED_BY CREATED_BY
, C.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, D.REQUEST_ID REQUEST_ID
, D.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID
, D.PROGRAM_ID PROGRAM_ID
, D.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE
, TO_NUMBER(NULL) ACCOUNTING_EVENT_NUMBER
, NULL ACCOUNTING_EVENT_TYPE
, NULL ACCOUNTING_EVENT_TYPE_NAME
, TO_NUMBER(NULL) ACCOUNTING_LINE_NUMBER
, JEH.DESCRIPTION COMMENTS
, I.INVOICE_TYPE_LOOKUP_CODE TRX_TYPE_C_I
, I.INVOICE_NUM APPLIED_TO_TRX_HDR_NUMBER_DISP
, I.INVOICE_NUM APPLIED_TO_TRX_HDR_NUMBER_C
, NULL AEH_ACCOUNTING_ERROR_CODE
, NULL AEH_ACCOUNTING_ERROR_NAME
, NULL AEL_ACCOUNTING_ERROR_CODE
, NULL AEL_ACCOUNTING_ERROR_NAME
, C.CURRENCY_CODE TRX_HDR_CURRENCY
, /*---------------------------------------------------------------*/ /* AP PAYMENT SPECIFIC COLUMNS */ /*---------------------------------------------------------------*/ C.BANK_ACCOUNT_NAME BANK_ACCOUNT_NAME
, 'AP_INVOICES' APPLIED_TO_TRX_HDR_TABLE
, I.INVOICE_ID APPLIED_TO_TRX_HDR_ID
, I.INVOICE_DATE APPLIED_TO_TRX_DATE
, I.INVOICE_CURRENCY_CODE APPLIED_TO_TRX_HDR_CURRENCY
, /*---------------------------------------------------------------*/ /* COLUMNS FOR CASH BASIS ACCOUNTING */ /*---------------------------------------------------------------*/ D.OLD_DIST_LINE_NUMBER TRX_LINE_NUMBER
, D.LINE_TYPE_LOOKUP_CODE TRX_LINE_TYPE
, L.DISPLAYED_FIELD TRX_LINE_TYPE_NAME
, PH.SEGMENT1 PO_ORDER_NUMBER
, TO_NUMBER(NULL) PO_ORDER_RELEASE_NUM
, TO_NUMBER(NULL) RCV_RECEIPT_NUM
, /*---------------------------------------------------------------*/ /* AP RECONCILIATION SPECIFIC COLUMNS */ /*---------------------------------------------------------------*/ C.CLEARED_DATE PAYMENT_CLEARED_DATE
, SH.STATEMENT_NUMBER BANK_STATEMENT_NUMBER
, NULL PAYMENT_RECON_CURRENCY
, NULL BANK_STATEMENT_LINE_NUMBER
, SH.DOC_SEQUENCE_ID BANK_STATEMENT_DOC_SEQ_ID
, NULL BANK_STATEMENT_DOC_SEQ_NAME
, SH.DOC_SEQUENCE_VALUE BANK_STATEMENT_DOC_SEQ_VALUE
FROM GL_LOOKUPS L2
, AP_LOOKUP_CODES L
, PO_HEADERS_ALL PH
, PO_DISTRIBUTIONS_ALL PD
, PO_VENDORS V
, AP_INVOICE_DISTRIBUTIONS_ALL D
, AP_INVOICES_ALL I
, GL_JE_HEADERS JEH
, GL_JE_LINES JEL
, GL_IMPORT_REFERENCES R
, IBY_PAYMENT_METHODS_VL IPMV
, AP_TAX_CODES_ALL AT
, AP_CHECKS_ALL C
, CE_STATEMENT_LINES SL
, CE_STATEMENT_HEADERS SH
, CE_STATEMENT_RECONCILIATIONS CSR
, AP_ENCUMBRANCE_LINES_ALL AEL WHERE L2.LOOKUP_TYPE = 'SUBLDGR_DRILLDOWN_TRANS_TYPE' AND L2.LOOKUP_CODE = NVL(R.REFERENCE_10
, 'CASH') AND L.LOOKUP_TYPE = 'INVOICE DISTRIBUTION TYPE' AND L.LOOKUP_CODE = D.LINE_TYPE_LOOKUP_CODE AND IPMV.PAYMENT_METHOD_CODE = C.PAYMENT_METHOD_CODE AND PH.PO_HEADER_ID(+) = PD.PO_HEADER_ID AND PD.PO_DISTRIBUTION_ID(+) = D.PO_DISTRIBUTION_ID AND I.INVOICE_ID = D.INVOICE_ID AND I.VENDOR_ID = V.VENDOR_ID AND JEH.JE_SOURCE ='PAYABLES' AND JEH.JE_HEADER_ID = JEL.JE_HEADER_ID AND JEL.JE_HEADER_ID = R.JE_HEADER_ID AND JEL.JE_LINE_NUM = R.JE_LINE_NUM AND AT.TAX_ID(+) = D.TAX_CODE_ID AND SL.STATEMENT_HEADER_ID = SH.STATEMENT_HEADER_ID(+) AND CSR.STATEMENT_LINE_ID = SL.STATEMENT_LINE_ID(+) AND C.CHECK_ID = CSR.REFERENCE_ID(+) AND CSR.REFERENCE_TYPE(+) = 'PAYMENT' AND CSR.CURRENT_RECORD_FLAG(+) = 'Y' AND AEL.INVOICE_DISTRIBUTION_ID = D.OLD_DISTRIBUTION_ID AND R.GL_SL_LINK_ID = AEL.GL_SL_LINK_ID AND TO_CHAR(C.CHECK_ID) = R.REFERENCE_3 AND TO_CHAR(I.INVOICE_ID) = R.REFERENCE_2