CMR_B_PERIOD_CLOSE_ACTIONS_V

Details

• Schema: FUSION

• Object owner: CST

• Object type: VIEW

Columns

Name

BU_ID PERIOD_SET_NAME LEDGER_ID LEGAL_ENTITY_ID PERIOD_TYPE PERIOD_NAME PERIOD_NUM PERIOD_YEAR START_DATE END_DATE LE_TIME_OFFSET SERVER_START_DATE SERVER_END_DATE PENDING_RECEIPT_COUNT PENDING_TRA_COUNT PENDING_INVOICE_VAR_COUNT PENDING_ACCR_CLR_COUNT PENDING_PERIOD_ACCR_COUNT PENDING_RPA_COUNT PENDING_RECEIPT_INTF_COUNT PENDING_INVOICE_INTF_COUNT PENDING_TRA_INTF_COUNT UNIMPORTED_RECEIPT_COUNT UNIMPORTED_INVOICE_COUNT PENDING_DIST_COUNT

Query

SQL_Statement

SELECT cps.bu_id, cps.period_set_name, cps.ledger_id, cps.legal_entity_id, cps.period_type, cps.period_name, cps.period_num, cps.period_year, cps.start_date, cps.end_date, cps.le_time_offset, cps.server_start_date, cps.server_end_date, SUM(DECODE(capct.source_table, 'CMR_TRANSACTIONS', 1, 0)) pending_receipt_count, SUM(DECODE(capct.source_table, 'CST_TRADE_EVENTS', 1, 0)) pending_tra_count, SUM(DECODE(capct.source_table, 'CMR_AP_INVOICE_DTLS', 1, 0)) pending_invoice_var_count, SUM(DECODE(capct.source_table, 'CMR_ACCRUAL_CLR_AMTS', 1, 0)) pending_accr_clr_count, SUM(DECODE(capct.transaction_type, 'PERIOD_END_ACCRUAL', 1, 0)) pending_period_accr_count, SUM(DECODE(capct.transaction_type, 'Retroactive Price Adjustment', 1, 0)) pending_rpa_count, SUM(DECODE(capct.source_table, 'CMR_I_RCV_TRANSACTIONS', 1, 0)) pending_receipt_intf_count, SUM(DECODE(capct.source_table, 'CMR_I_AP_INVOICE_DTLS', 1, 0)) pending_invoice_intf_count, SUM(DECODE(capct.source_table, 'CST_I_TRADE_EVENTS', 1, 0)) pending_tra_intf_count, SUM(DECODE(capct.source_table, 'RCV_TRANSACTIONS', 1, 0)) unimported_receipt_count, SUM(DECODE(capct.source_table, 'AP_INVOICE_DISTRIBUTIONS_ALL', 1, 0)) unimported_invoice_count, SUM(DECODE(capct.source_table, 'CMR_RCV_EVENTS_DIST', 1, 0)) pending_dist_count FROM cmr_period_statuses_v cps, cmr_all_period_close_txns_v capct WHERE capct.bill_to_business_unit_id = cps.bu_id AND TRUNC(capct.transaction_date + capct.convert_to_letz*le_time_offset) <= cps.end_date GROUP BY cps.bu_id, cps.period_set_name, cps.ledger_id, cps.legal_entity_id, cps.period_type, cps.period_name, cps.period_num, cps.period_year, cps.start_date, cps.end_date, cps.le_time_offset, cps.server_start_date, cps.server_end_date