AR_CREDIT_MEMO_H_V

Details

• Schema: FUSION

• Object owner: AR

• Object type: VIEW

Columns

Name

CM_ATTR1 CM_ATTR10 CM_ATTR11 CM_ATTR12 CM_ATTR13 CM_ATTR14 CM_ATTR15 CM_ATTR2 CM_ATTR3 CM_ATTR4 CM_ATTR5 CM_ATTR6 CM_ATTR7 CM_ATTR8 CM_ATTR9 CM_ATTR_CAT CM_CUSTOMER_TRX_ID CM_DEF_USSGL_TRX CM_DEF_USSGL_TRX_CODE_CT CM_DOC_SEQUENCE_ID CM_DOC_SEQUENCE_VALUE CM_DOC_SEQUENCE_CATEGORY CM_FOB_POINT CM_GLOBAL_ATTR1 CM_GLOBAL_ATTR10 CM_GLOBAL_ATTR11 CM_GLOBAL_ATTR12 CM_GLOBAL_ATTR13 CM_GLOBAL_ATTR14 CM_GLOBAL_ATTR15 CM_GLOBAL_ATTR16 CM_GLOBAL_ATTR17 CM_GLOBAL_ATTR18 CM_GLOBAL_ATTR19 CM_GLOBAL_ATTR2 CM_GLOBAL_ATTR20 CM_GLOBAL_ATTR3 CM_GLOBAL_ATTR4 CM_GLOBAL_ATTR5 CM_GLOBAL_ATTR6 CM_GLOBAL_ATTR7 CM_GLOBAL_ATTR8 CM_GLOBAL_ATTR9 CM_GLOBAL_ATTR_CAT CM_INTERFACE_HEADER_ATTR1 CM_INTERFACE_HEADER_ATTR10 CM_INTERFACE_HEADER_ATTR11 CM_INTERFACE_HEADER_ATTR12 CM_INTERFACE_HEADER_ATTR13 CM_INTERFACE_HEADER_ATTR14 CM_INTERFACE_HEADER_ATTR15 CM_INTERFACE_HEADER_ATTR2 CM_INTERFACE_HEADER_ATTR3 CM_INTERFACE_HEADER_ATTR4 CM_INTERFACE_HEADER_ATTR5 CM_INTERFACE_HEADER_ATTR6 CM_INTERFACE_HEADER_ATTR7 CM_INTERFACE_HEADER_ATTR8 CM_INTERFACE_HEADER_ATTR9 CM_INTERFACE_HEADER_CT CM_INVOICE_CURRENCY_CODE CM_INVOICING_RULE_ID CM_PRINTING_OPTION CM_PURCHASE_ORDER CM_PURCHASE_ORDER_DATE CM_PURCHASE_ORDER_REVISION CM_SHIP_DATE_ACTUAL CM_STATUS_TRX CM_TERM_DUE_DATE CM_TRX_DATE CM_TRX_NUMBER CM_WAYBILL_NUMBER CM_TRX_TYPE_CUST_TRX_TYPE_ID CM_TRX_TYPE_DESCRIPTION CM_TRX_TYPE_DRAWEE_ISSUED_FLAG CM_TRX_TYPE_GLOBAL_ATTR1 CM_TRX_TYPE_GLOBAL_ATTR10 CM_TRX_TYPE_GLOBAL_ATTR2 CM_TRX_TYPE_GLOBAL_ATTR3 CM_TRX_TYPE_GLOBAL_ATTR4 CM_TRX_TYPE_GLOBAL_ATTR5 CM_TRX_TYPE_GLOBAL_ATTR6 CM_TRX_TYPE_GLOBAL_ATTR7 CM_TRX_TYPE_GLOBAL_ATTR_CAT CM_TRX_TYPE_MAG_FORMAT_CODE CM_TRX_TYPE_NAME CM_TRX_TYPE_POST_TO_GL CM_TRX_TYPE_CLASS EVENT_ID LEDGER_ID CM_OPER_UNIT CM_RECEIVABLE_CCID XLA_PARTY_TYPE CM_XLA_APPLIED_TO_APP_ID CM_ENTITY_CODE CM_PRV_TRX_ID CM_RECEIPT_METHOD_ID CM_RECEIPT_METHOD_NAME CM_LINE_REASON_CODE CM_REV_REC_APPLICATION CM_REV_CONTRACT_CLEARING_CCID CM_DOCUMENT_TYPE_CODE CM_LEGAL_ENTITY_ID CM_CT_REFERENCE

Query

SQL_Statement

SELECT /*+INDEX(he ar_xla_lines_extract_n1)*/ ctat.attribute1 cm_attr1 , ctat.attribute10 cm_attr10 , ctat.attribute11 cm_attr11 , ctat.attribute12 cm_attr12 , ctat.attribute13 cm_attr13 , ctat.attribute14 cm_attr14 , ctat.attribute15 cm_attr15 , ctat.attribute2 cm_attr2 , ctat.attribute3 cm_attr3 , ctat.attribute4 cm_attr4 , ctat.attribute5 cm_attr5 , ctat.attribute6 cm_attr6 , ctat.attribute7 cm_attr7 , ctat.attribute8 cm_attr8 , ctat.attribute9 cm_attr9 , ctat.attribute_category cm_attr_cat , ctat.customer_trx_id cm_customer_trx_id , ctat.default_ussgl_transaction_code cm_def_ussgl_trx , ctat.default_ussgl_trx_code_context cm_def_ussgl_trx_code_ct , ctat.doc_sequence_id cm_doc_sequence_id , ctat.doc_sequence_value cm_doc_sequence_value , DECODE(ctat.doc_sequence_value, NULL, NULL, cttat.name) CM_DOC_SEQUENCE_CATEGORY , ctat.fob_point cm_fob_point , ctat.global_attribute1 cm_global_attr1 , ctat.global_attribute10 cm_global_attr10 , ctat.global_attribute11 cm_global_attr11 , ctat.global_attribute12 cm_global_attr12 , ctat.global_attribute13 cm_global_attr13 , ctat.global_attribute14 cm_global_attr14 , ctat.global_attribute15 cm_global_attr15 , ctat.global_attribute16 cm_global_attr16 , ctat.global_attribute17 cm_global_attr17 , ctat.global_attribute18 cm_global_attr18 , ctat.global_attribute19 cm_global_attr19 , ctat.global_attribute2 cm_global_attr2 , ctat.global_attribute20 cm_global_attr20 , ctat.global_attribute3 cm_global_attr3 , ctat.global_attribute4 cm_global_attr4 , ctat.global_attribute5 cm_global_attr5 , ctat.global_attribute6 cm_global_attr6 , ctat.global_attribute7 cm_global_attr7 , ctat.global_attribute8 cm_global_attr8 , ctat.global_attribute9 cm_global_attr9 , ctat.global_attribute_category cm_global_attr_cat , ctat.interface_header_attribute1 cm_interface_header_attr1 , ctat.interface_header_attribute10 cm_interface_header_attr10 , ctat.interface_header_attribute11 cm_interface_header_attr11 , ctat.interface_header_attribute12 cm_interface_header_attr12 , ctat.interface_header_attribute13 cm_interface_header_attr13 , ctat.interface_header_attribute14 cm_interface_header_attr14 , ctat.interface_header_attribute15 cm_interface_header_attr15 , ctat.interface_header_attribute2 cm_interface_header_attr2 , ctat.interface_header_attribute3 cm_interface_header_attr3 , ctat.interface_header_attribute4 cm_interface_header_attr4 , ctat.interface_header_attribute5 cm_interface_header_attr5 , ctat.interface_header_attribute6 cm_interface_header_attr6 , ctat.interface_header_attribute7 cm_interface_header_attr7 , ctat.interface_header_attribute8 cm_interface_header_attr8 , ctat.interface_header_attribute9 cm_interface_header_attr9 , ctat.interface_header_context cm_interface_header_ct , ctat.invoice_currency_code cm_invoice_currency_code , ctat.invoicing_rule_id cm_invoicing_rule_id , ctat.printing_option cm_printing_option , ctat.purchase_order cm_purchase_order , ctat.purchase_order_date cm_purchase_order_date , ctat.purchase_order_revision cm_purchase_order_revision , ctat.ship_date_actual cm_ship_date_actual , ctat.status_trx cm_status_trx , ctat.term_due_date cm_term_due_date , ctat.trx_date cm_trx_date , ctat.trx_number cm_trx_number , ctat.waybill_number cm_waybill_number , cttat.cust_trx_type_id cm_trx_type_cust_trx_type_id , cttat.description cm_trx_type_description , cttat.drawee_issued_flag cm_trx_type_drawee_issued_flag , cttat.global_attribute1 cm_trx_type_global_attr1 , cttat.global_attribute10 cm_trx_type_global_attr10 , cttat.global_attribute2 cm_trx_type_global_attr2 , cttat.global_attribute3 cm_trx_type_global_attr3 , cttat.global_attribute4 cm_trx_type_global_attr4 , cttat.global_attribute5 cm_trx_type_global_attr5 , cttat.global_attribute6 cm_trx_type_global_attr6 , cttat.global_attribute7 cm_trx_type_global_attr7 , cttat.global_attribute_category cm_trx_type_global_attr_cat , cttat.magnetic_format_code cm_trx_type_mag_format_code , cttat.name cm_trx_type_name , cttat.post_to_gl cm_trx_type_post_to_gl , cttat.type cm_trx_type_class , he.event_id event_id , he.ledger_id ledger_id , ctat.org_id cm_oper_unit , he.paired_ccid cm_receivable_ccid , 'C' xla_party_type , 222 cm_xla_applied_to_app_id , 'TRANSACTIONS' cm_entity_code , ctat.previous_customer_trx_id cm_prv_trx_id, ctat.receipt_method_id cm_receipt_method_id, rm.name cm_receipt_method_name, lkp.meaning cm_line_reason_code, ctat.REV_REC_APPLICATION CM_REV_REC_APPLICATION, case when (ctat.trx_date BETWEEN nvl(vnas.start_date,ctat.trx_date) AND nvl(vnas.end_date,ctat.trx_date) ) then vnas.CONTRACT_CLG_DEF_ACCOUNT_CCID else NULL end CM_REV_CONTRACT_CLEARING_CCID, case when (ctat.trx_date BETWEEN nvl(vnas.start_date,ctat.trx_date) AND nvl(vnas.end_date,ctat.trx_date) ) then vsdt.DOCUMENT_TYPE_CODE else NULL end CM_DOCUMENT_TYPE_CODE, ctat.legal_entity_id cm_legal_entity_id, ctat.ct_reference cm_ct_reference FROM ra_customer_trx_all ctat , ra_cust_trx_types_all cttat , ra_rules rarht , ar_xla_lines_extract he, ar_receipt_methods rm, ar_lookups lkp, VRM_NEW_APP_SOURCES vnas, VRM_SOURCE_DOC_TYPES_B vsdt WHERE ctat.customer_trx_id = he.customer_trx_id AND he.level_flag = 'H' AND ctat.invoicing_rule_id = rarht.rule_id (+) AND ctat.receipt_method_id = rm.receipt_method_id(+) AND ctat.cust_trx_type_seq_id = cttat.cust_trx_type_seq_id (+) AND he.event_type_code IN ( 'CM_CREATE' , 'CM_UPDATE') AND lkp.lookup_code (+) = ctat.reason_code AND lkp.lookup_type (+) = 'CREDIT_MEMO_REASON' AND ctat.document_type_id = vsdt.document_type_id(+) AND ctat.set_of_books_id = vnas.ledger_id(+) AND ctat.document_type_id = vnas.document_type_id(+)