CMR_ALL_PERIOD_CLOSE_TXNS_V

Details

• Schema: FUSION

• Object owner: CMR

• Object type: VIEW

Columns

Name
TRANSACTION_ID SOURCE_TABLE BILL_TO_BUSINESS_UNIT_ID INVENTORY_ORG_ID ITEM_ORGANIZATION_ID TRANSACTION_TYPE DOCUMENT_TYPE_CODE TRANSACTION_NUMBER TRANSACTION_DATE TRANSACTION_LINE_NUMBER DESTINATION_TYPE_CODE DISTRIBUTION_TYPE INVENTORY_ITEM_ID TRANSACTION_QTY TRANSACTION_UOM_CODE TRANSACTION_AMT CURRENCY_CODE CONVERT_TO_LETZ ERROR_CODE

Query

SQL_Statement

SELECT transaction_id, source_table, bill_to_business_unit_id, inventory_org_id, item_organization_id, transaction_type, document_type_code, transaction_number, transaction_date, transaction_line_number, destination_type_code, distribution_type, inventory_item_id, transaction_qty, transaction_uom_code, transaction_amt, currency_code, convert_to_letz, CAST( DECODE(DOCUMENT_TYPE_CODE,'Interface Receipts', 'Unprocessed Interfaced Receipts', 'Interface Invoices','Unprocessed Interfaced Invoices', 'Interface Trade Events','Unprocessed Interfaced Trade Events', DOCUMENT_TYPE_CODE) AS VARCHAR2(100)) error_code /* CAST('Unimported/Unprocessed Interface' AS VARCHAR2(100)) error_code - added the above logic to give exact details to user */ FROM cmr_all_intf_txns_v UNION ALL SELECT transaction_id, source_table, bill_to_business_unit_id, inventory_org_id, item_organization_id, transaction_type, document_type_code, transaction_number, transaction_date, transaction_line_number, destination_type_code, invoice_distribution_type distribution_type, inventory_item_id, transaction_qty, transaction_uom_code, transaction_amt, CAST(NULL AS VARCHAR2(15)) currency_code, convert_to_letz, DECODE( source_table, 'CMR_TRANSACTIONS','Unprocessed Receipts', 'CST_TRADE_EVENTS','Unprocessed Trade Events', 'CMR_AP_INVOICE_DTLS', 'Unprocessed Invoices', 'CMR_ACCRUAL_CLR_AMTS','Unprocessed Accrual Clearing', 'CMR_RCV_EVENTS',DECODE(transaction_type, 'Period End Accrual','Unprocess Period End Accruals','Unprocessed Retro Price Adjustment' ) ) error_code FROM cmr_all_transactions_v WHERE posted_flag in ('N','E') and NVL(RA_INVOICE_TRANSACTION_FLAG,'Y') = 'Y' /* RA_INVOICE_TRANSACTION_FLAG = Y means Invoice variances are processed in CMR and other values are not processed as accounting txns. NULL means non-invoices data and it will be NULL */ UNION ALL SELECT accounting_event_id transaction_id, CAST('CMR_RCV_EVENTS_DIST' AS VARCHAR2(30)) source_table, business_unit_id bill_to_business_unit_id, inventory_org_id, inventory_org_id item_organization_id, transaction_type_code transaction_type, /* added correct txn type column in CRE instead of event_type_code - */ ( select substr(meaning,1,40) from fnd_lookups fl where fl.LOOKUP_TYPE='ORA_CMR_DOC_FLOW_TYPES' and fl.lookup_code = cre.event_source) document_type_code, /* this coulmn in this 40 length so resticting to 40 characters */ NVL(sla_transaction_number,source_doc_number) transaction_number, gl_date transaction_date, CAST(0 AS NUMBER) transaction_line_number, destination_type_code, CAST(NULL AS VARCHAR2(30)) distribution_type, inventory_item_id, transaction_qty, transaction_uom_code, transaction_amt, CAST(NULL AS VARCHAR2(15)) currency_code, CAST(0 AS NUMBER) convert_to_letz, DECODE(cre.accounted_flag,'P','Pending Distributions', 'E', DECODE(NVL(cre.error_code,'CMR_RA_NO_OPEN_GL_PERIOD'), 'CMR_RA_NO_SLA_EVENT','SLA event not created', 'CMR_RA_NO_OPEN_GL_PERIOD','GL period not opened', cre.error_code), NVl(cre.error_code,'Unaccounted Distribution Entries')) FROM cmr_rcv_events cre WHERE accounted_flag not in ( 'F','X') /*X denotes excluded fromaccounting. This is for BCEA enabled Deliver to Inventory PO flows and pre 13 they will be moved CRE even though no BCEA */