VIEW: APPS.IEX_CU_STR_UWQ_V

Source

View Text - Preformatted

SELECT summ.ieu_object_function ieu_object_function, REPLACE(summ.ieu_object_parameters, 'IEXTRMAN', 'IEXSTTAB') || ' RETURNED_FROM=IEUSEUWQ MO_DEFAULT_ORG_ID=' || summ.org_id ieu_object_parameters , summ.ieu_media_type_uuid ieu_media_type_uuid , summ.ieu_param_pk_col ieu_param_pk_col , summ.ieu_param_pk_value ieu_param_pk_value , summ.collector_resource_id collector_resource_id , summ.collector_resource_name collector_resource_name , iex_utilities.get_lookup_meaning('IEX_RESOURCE', summ.collector_res_type) collector_resource_type , summ.collector_res_type collector_res_type , summ.wkitem_resource_id resource_id , 'RS_EMPLOYEE' resource_type , summ.party_id party_id , summ.party_name party_name , summ.cust_account_id cust_account_id , summ.account_name account_name , summ.account_number account_number , summ.site_use_id site_use_id , summ.location location , summ.currency currency , summ.op_invoices_count op_invoices_count , summ.op_debit_memos_count op_debit_memos_count , summ.op_deposits_count op_deposits_count , summ.op_bills_receivables_count op_bills_receivables_count , summ.op_chargeback_count op_chargeback_count , summ.op_credit_memos_count op_credit_memos_count , summ.unresolved_cash_count unresolved_cash_count , summ.disputed_inv_count disputed_inv_count , summ.best_current_receivables best_current_receivables , summ.op_invoices_value op_invoices_value , summ.op_debit_memos_value op_debit_memos_value , summ.op_deposits_value op_deposits_value , summ.op_bills_receivables_value op_bills_receivables_value , summ.op_chargeback_value op_chargeback_value , summ.op_credit_memos_value op_credit_memos_value , summ.unresolved_cash_value unresolved_cash_value , summ.receipts_at_risk_value receipts_at_risk_value , summ.inv_amt_in_dispute inv_amt_in_dispute , summ.pending_adj_value pending_adj_value , summ.past_due_inv_value past_due_inv_value , summ.past_due_inv_inst_count past_due_inv_inst_count , summ.last_payment_date last_payment_date , summ.last_payment_amount last_payment_amount , summ.last_payment_amount_curr last_payment_amount_curr , summ.last_payment_number last_payment_number , summ.last_update_date last_update_date , summ.last_updated_by last_updated_by , summ.creation_date creation_date , summ.created_by created_by , summ.last_update_login last_update_login , NULL number_of_strategies , NULL active_strategies , NULL pending_strategies , NULL complete_strategies , stry_temp.strategy_name strategy_name , stry_temp_wkitem.name wkitem_template_name , iex_utilities.get_lookup_meaning('IEX_STRATEGY_WORK_CATEGORY', summ.category_type) category_meaning, summ.category_type category , iex_utilities.get_lookup_meaning('IEX_STRATEGY_WORK_STATUS', summ.status_code) status , iex_utilities.get_lookup_meaning('IEX_STRATEGY_STATUS', summ.str_status) str_status , summ.str_status str_status_code , summ.wkitem_resource_id assigned_to , rs.source_name assignee , summ.start_time start_time , summ.end_time end_time , summ.schedule_start schedule_start , summ.schedule_end schedule_end , summ.work_item_order wkitem_order , summ.work_type work_type , summ.wkitem_escalated_yn wkitem_escalated_yn , ARPT_SQL_FUNC_UTIL.get_lookup_meaning('YES/NNO', summ.wkitem_escalated_yn) wkitem_escalated_meaning , NULL uwq_active_date , NULL uwq_complete_date , summ.number_of_bankruptcies number_of_bankruptcies , summ.org_id org_id , hu.name operating_unit_name , summ.aging_amount1 aging_amount1 , summ.aging_count1 aging_count1 , summ.aging_amount2 aging_amount2 , summ.aging_count2 aging_count2 , summ.aging_amount3 aging_amount3 , summ.aging_count3 aging_count3 , summ.aging_amount4 aging_amount4 , summ.aging_count4 aging_count4 , summ.aging_amount5 aging_amount5 , summ.aging_count5 aging_count5 , summ.aging_amount6 aging_amount6 , summ.aging_count6 aging_count6 , summ.aging_amount7 aging_amount7 , summ.aging_count7 aging_count7 , summ.cases_count cases_count , summ.del_cases_count del_cases_count , summ.contracts_count contracts_count , summ.del_contracts_count del_contracts_count , summ.net_balance net_balance FROM iex_dln_uwq_summary summ , iex_strategy_templates_tl stry_temp , iex_stry_temp_work_items_tl stry_temp_wkitem, jtf_rs_resource_extns rs , hr_operating_units hu WHERE summ.strategy_template_id = stry_temp.strategy_temp_id AND stry_temp.LANGUAGE = userenv('LANG') AND summ.work_item_template_id = stry_temp_wkitem.work_item_temp_id AND stry_temp_wkitem.LANGUAGE = userenv('LANG') /*AND summ.work_type = 'MANUAL'*/ AND TRUNC(sysdate) >= TRUNC(NVL(summ.start_time, summ.schedule_start)) AND summ.wkitem_resource_id = rs.resource_id AND summ.org_id = hu.organization_id AND mo_global.check_access(summ.org_id) = 'Y' AND nvl(summ.number_of_delinquencies,0) > 0

View Text - HTML Formatted

SELECT SUMM.IEU_OBJECT_FUNCTION IEU_OBJECT_FUNCTION
, REPLACE(SUMM.IEU_OBJECT_PARAMETERS
, 'IEXTRMAN'
, 'IEXSTTAB') || ' RETURNED_FROM=IEUSEUWQ MO_DEFAULT_ORG_ID=' || SUMM.ORG_ID IEU_OBJECT_PARAMETERS
, SUMM.IEU_MEDIA_TYPE_UUID IEU_MEDIA_TYPE_UUID
, SUMM.IEU_PARAM_PK_COL IEU_PARAM_PK_COL
, SUMM.IEU_PARAM_PK_VALUE IEU_PARAM_PK_VALUE
, SUMM.COLLECTOR_RESOURCE_ID COLLECTOR_RESOURCE_ID
, SUMM.COLLECTOR_RESOURCE_NAME COLLECTOR_RESOURCE_NAME
, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_RESOURCE'
, SUMM.COLLECTOR_RES_TYPE) COLLECTOR_RESOURCE_TYPE
, SUMM.COLLECTOR_RES_TYPE COLLECTOR_RES_TYPE
, SUMM.WKITEM_RESOURCE_ID RESOURCE_ID
, 'RS_EMPLOYEE' RESOURCE_TYPE
, SUMM.PARTY_ID PARTY_ID
, SUMM.PARTY_NAME PARTY_NAME
, SUMM.CUST_ACCOUNT_ID CUST_ACCOUNT_ID
, SUMM.ACCOUNT_NAME ACCOUNT_NAME
, SUMM.ACCOUNT_NUMBER ACCOUNT_NUMBER
, SUMM.SITE_USE_ID SITE_USE_ID
, SUMM.LOCATION LOCATION
, SUMM.CURRENCY CURRENCY
, SUMM.OP_INVOICES_COUNT OP_INVOICES_COUNT
, SUMM.OP_DEBIT_MEMOS_COUNT OP_DEBIT_MEMOS_COUNT
, SUMM.OP_DEPOSITS_COUNT OP_DEPOSITS_COUNT
, SUMM.OP_BILLS_RECEIVABLES_COUNT OP_BILLS_RECEIVABLES_COUNT
, SUMM.OP_CHARGEBACK_COUNT OP_CHARGEBACK_COUNT
, SUMM.OP_CREDIT_MEMOS_COUNT OP_CREDIT_MEMOS_COUNT
, SUMM.UNRESOLVED_CASH_COUNT UNRESOLVED_CASH_COUNT
, SUMM.DISPUTED_INV_COUNT DISPUTED_INV_COUNT
, SUMM.BEST_CURRENT_RECEIVABLES BEST_CURRENT_RECEIVABLES
, SUMM.OP_INVOICES_VALUE OP_INVOICES_VALUE
, SUMM.OP_DEBIT_MEMOS_VALUE OP_DEBIT_MEMOS_VALUE
, SUMM.OP_DEPOSITS_VALUE OP_DEPOSITS_VALUE
, SUMM.OP_BILLS_RECEIVABLES_VALUE OP_BILLS_RECEIVABLES_VALUE
, SUMM.OP_CHARGEBACK_VALUE OP_CHARGEBACK_VALUE
, SUMM.OP_CREDIT_MEMOS_VALUE OP_CREDIT_MEMOS_VALUE
, SUMM.UNRESOLVED_CASH_VALUE UNRESOLVED_CASH_VALUE
, SUMM.RECEIPTS_AT_RISK_VALUE RECEIPTS_AT_RISK_VALUE
, SUMM.INV_AMT_IN_DISPUTE INV_AMT_IN_DISPUTE
, SUMM.PENDING_ADJ_VALUE PENDING_ADJ_VALUE
, SUMM.PAST_DUE_INV_VALUE PAST_DUE_INV_VALUE
, SUMM.PAST_DUE_INV_INST_COUNT PAST_DUE_INV_INST_COUNT
, SUMM.LAST_PAYMENT_DATE LAST_PAYMENT_DATE
, SUMM.LAST_PAYMENT_AMOUNT LAST_PAYMENT_AMOUNT
, SUMM.LAST_PAYMENT_AMOUNT_CURR LAST_PAYMENT_AMOUNT_CURR
, SUMM.LAST_PAYMENT_NUMBER LAST_PAYMENT_NUMBER
, SUMM.LAST_UPDATE_DATE LAST_UPDATE_DATE
, SUMM.LAST_UPDATED_BY LAST_UPDATED_BY
, SUMM.CREATION_DATE CREATION_DATE
, SUMM.CREATED_BY CREATED_BY
, SUMM.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, NULL NUMBER_OF_STRATEGIES
, NULL ACTIVE_STRATEGIES
, NULL PENDING_STRATEGIES
, NULL COMPLETE_STRATEGIES
, STRY_TEMP.STRATEGY_NAME STRATEGY_NAME
, STRY_TEMP_WKITEM.NAME WKITEM_TEMPLATE_NAME
, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_STRATEGY_WORK_CATEGORY'
, SUMM.CATEGORY_TYPE) CATEGORY_MEANING
, SUMM.CATEGORY_TYPE CATEGORY
, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_STRATEGY_WORK_STATUS'
, SUMM.STATUS_CODE) STATUS
, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_STRATEGY_STATUS'
, SUMM.STR_STATUS) STR_STATUS
, SUMM.STR_STATUS STR_STATUS_CODE
, SUMM.WKITEM_RESOURCE_ID ASSIGNED_TO
, RS.SOURCE_NAME ASSIGNEE
, SUMM.START_TIME START_TIME
, SUMM.END_TIME END_TIME
, SUMM.SCHEDULE_START SCHEDULE_START
, SUMM.SCHEDULE_END SCHEDULE_END
, SUMM.WORK_ITEM_ORDER WKITEM_ORDER
, SUMM.WORK_TYPE WORK_TYPE
, SUMM.WKITEM_ESCALATED_YN WKITEM_ESCALATED_YN
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NNO'
, SUMM.WKITEM_ESCALATED_YN) WKITEM_ESCALATED_MEANING
, NULL UWQ_ACTIVE_DATE
, NULL UWQ_COMPLETE_DATE
, SUMM.NUMBER_OF_BANKRUPTCIES NUMBER_OF_BANKRUPTCIES
, SUMM.ORG_ID ORG_ID
, HU.NAME OPERATING_UNIT_NAME
, SUMM.AGING_AMOUNT1 AGING_AMOUNT1
, SUMM.AGING_COUNT1 AGING_COUNT1
, SUMM.AGING_AMOUNT2 AGING_AMOUNT2
, SUMM.AGING_COUNT2 AGING_COUNT2
, SUMM.AGING_AMOUNT3 AGING_AMOUNT3
, SUMM.AGING_COUNT3 AGING_COUNT3
, SUMM.AGING_AMOUNT4 AGING_AMOUNT4
, SUMM.AGING_COUNT4 AGING_COUNT4
, SUMM.AGING_AMOUNT5 AGING_AMOUNT5
, SUMM.AGING_COUNT5 AGING_COUNT5
, SUMM.AGING_AMOUNT6 AGING_AMOUNT6
, SUMM.AGING_COUNT6 AGING_COUNT6
, SUMM.AGING_AMOUNT7 AGING_AMOUNT7
, SUMM.AGING_COUNT7 AGING_COUNT7
, SUMM.CASES_COUNT CASES_COUNT
, SUMM.DEL_CASES_COUNT DEL_CASES_COUNT
, SUMM.CONTRACTS_COUNT CONTRACTS_COUNT
, SUMM.DEL_CONTRACTS_COUNT DEL_CONTRACTS_COUNT
, SUMM.NET_BALANCE NET_BALANCE
FROM IEX_DLN_UWQ_SUMMARY SUMM
, IEX_STRATEGY_TEMPLATES_TL STRY_TEMP
, IEX_STRY_TEMP_WORK_ITEMS_TL STRY_TEMP_WKITEM
, JTF_RS_RESOURCE_EXTNS RS
, HR_OPERATING_UNITS HU
WHERE SUMM.STRATEGY_TEMPLATE_ID = STRY_TEMP.STRATEGY_TEMP_ID
AND STRY_TEMP.LANGUAGE = USERENV('LANG')
AND SUMM.WORK_ITEM_TEMPLATE_ID = STRY_TEMP_WKITEM.WORK_ITEM_TEMP_ID
AND STRY_TEMP_WKITEM.LANGUAGE = USERENV('LANG') /*AND SUMM.WORK_TYPE = 'MANUAL'*/
AND TRUNC(SYSDATE) >= TRUNC(NVL(SUMM.START_TIME
, SUMM.SCHEDULE_START))
AND SUMM.WKITEM_RESOURCE_ID = RS.RESOURCE_ID
AND SUMM.ORG_ID = HU.ORGANIZATION_ID
AND MO_GLOBAL.CHECK_ACCESS(SUMM.ORG_ID) = 'Y'
AND NVL(SUMM.NUMBER_OF_DELINQUENCIES
, 0) > 0