VIEW: APPS.IEX_STRATEGIES_UWQ_V

Source

View Text - Preformatted

SELECT objb.object_function ieu_object_function, objb.object_parameters || ' DISPLAYCBO=IEXSTTAB RETURNED_FROM=IEUSEUWQ MO_DEFAULT_ORG_ID=' || dln.org_id ieu_object_parameters, '' ieu_media_type_uuid, decode(strat.strategy_level,10,'IEX_CUSTOMER',20,'IEX_ACCOUNT',30,'IEX_BILLTO','IEX_DELINQUENCY') ieu_param_pk_col, decode(strat.strategy_level,10,to_char(strat.party_id),20,to_char(strat.cust_account_id),30,to_char(strat.customer_site_use_id),to_char(strat.delinquency_id)) ieu_param_pk_value, wkitem.resource_id resource_id, strat.party_id customer_id, party.party_name customer_name, party.person_first_name first_name, party.person_last_name last_name, 'RS_INDIVIDUAL' resource_type, wkitem.strategy_id strategy_id, wkitem.strategy_temp_id strategy_template_id, stry_temp.strategy_name strategy_name, wkitem.work_item_order wkitem_order, wkitem.work_item_id wkitem_id, wkitem.work_item_template_id wkitem_template_id, stry_temp_wkitem.name wkitem_template_name, lookup1.meaning category_meaning, stry_temp_wkitem.category_type category, wkitem.execute_start start_time, wkitem.execute_end end_time, wkitem.resource_id assigned_to, res.source_name assignee, stry_temp_wkitem.work_type work_type, stry_temp_wkitem.escalate_yn escalate_yn, item.display_name workflow_item_type, lookup.meaning status, 0 strategy_user_item_id, wkitem.creation_date creation_date, wkitem.created_by created_by, wkitem.last_update_date last_update_date, wkitem.last_updated_by last_updated_by, wkitem.schedule_start schedule_start, wkitem.schedule_end schedule_end, strat.jtf_object_type jtf_object_type, strat.jtf_object_id jtf_object_id, strat.strategy_level strategy_level, wkitem.uwq_status uwq_status, wkitem.uwq_active_date uwq_active_date, wkitem.uwq_complete_date uwq_complete_date, ps.trx_number trx_number, ps.amount_due_original amount_due_original, ps.amount_due_remaining amount_due_remaining, ps.invoice_currency_code currency_code, ps.due_date due_date, '' aging_bucket_name, '' bucket_line_desc, '' trx_creation_date, strat.delinquency_id delinquency_id, dln.org_id org_id, hu.name operating_unit_name FROM iex_strategy_work_items wkitem, iex_stry_temp_work_items_vl stry_temp_wkitem, iex_strategy_templates_vl stry_temp, iex_lookups_v lookup, iex_lookups_v lookup1, wf_item_types_vl item, jtf_objects_b objb, jtf_rs_resource_extns res, iex_strategies strat, hz_parties party, ar_payment_schedules ps, iex_delinquencies dln, hr_operating_units hu WHERE wkitem.work_item_template_id = stry_temp_wkitem.work_item_temp_id and stry_temp_wkitem.category_type = lookup1.lookup_code and lookup1.lookup_type = 'IEX_STRATEGY_WORK_CATEGORY' and wkitem.status_code = lookup.lookup_code and lookup.lookup_type = 'IEX_STRATEGY_WORK_STATUS' and stry_temp_wkitem.WORKFLOW_ITEM_TYPE = item.name(+) and wkitem.resource_id = res.resource_id and wkitem.strategy_temp_id = stry_temp.strategy_temp_id and trunc(SYSDATE) >= trunc(wkitem.execute_start) and objb.object_code = decode(strat.strategy_level,10,'IEX_CUSTOMER',20,'IEX_ACCOUNT',30,'IEX_BILLTO','IEX_DELINQUENCY') and wkitem.status_code = 'OPEN' and wkitem.strategy_id = strat.strategy_id and strat.status_code = 'OPEN' and strat.checklist_yn <> 'Y' and strat.party_id = party.party_id and strat.delinquency_id is not null AND STRAT.DELINQUENCY_ID = DLN.DELINQUENCY_ID AND DLN.PAYMENT_SCHEDULE_ID = PS.PAYMENT_SCHEDULE_ID(+) AND PS.STATUS(+) = 'OP' AND DLN.ORG_ID = HU.ORGANIZATION_ID UNION ALL SELECT objb.object_function ieu_object_function, objb.object_parameters || ' DISPLAYCBO=IEXSTTAB RETURNED_FROM=IEUSEUWQ MO_DEFAULT_ORG_ID=' || csu.org_id ieu_object_parameters, '' ieu_media_type_uuid, decode(strat.strategy_level,10,'IEX_CUSTOMER',20,'IEX_ACCOUNT',30,'IEX_BILLTO','IEX_DELINQUENCY') ieu_param_pk_col, decode(strat.strategy_level,10,to_char(strat.party_id),20,to_char(strat.cust_account_id),30,to_char(strat.customer_site_use_id),to_char(strat.delinquency_id)) ieu_param_pk_value, wkitem.resource_id resource_id, strat.party_id customer_id, party.party_name customer_name, party.person_first_name first_name, party.person_last_name last_name, 'RS_INDIVIDUAL' resource_type, wkitem.strategy_id strategy_id, wkitem.strategy_temp_id strategy_template_id, stry_temp.strategy_name strategy_name, wkitem.work_item_order wkitem_order, wkitem.work_item_id wkitem_id, wkitem.work_item_template_id wkitem_template_id, stry_temp_wkitem.name wkitem_template_name, lookup1.meaning category_meaning, stry_temp_wkitem.category_type category, wkitem.execute_start start_time, wkitem.execute_end end_time, wkitem.resource_id assigned_to, res.source_name assignee, stry_temp_wkitem.work_type work_type, stry_temp_wkitem.escalate_yn escalate_yn, item.display_name workflow_item_type, lookup.meaning status, 0 strategy_user_item_id, wkitem.creation_date creation_date, wkitem.created_by created_by, wkitem.last_update_date last_update_date, wkitem.last_updated_by last_updated_by, wkitem.schedule_start schedule_start, wkitem.schedule_end schedule_end, strat.jtf_object_type jtf_object_type, strat.jtf_object_id jtf_object_id, strat.strategy_level strategy_level, wkitem.uwq_status uwq_status, wkitem.uwq_active_date uwq_active_date, wkitem.uwq_complete_date uwq_complete_date, to_char(null) trx_number, to_number(null) amount_due_original, to_number(null) amount_due_remaining, to_char(null) currency_code, to_date(null) due_date, to_char(null) aging_bucket_name, to_char(null) bucket_line_desc, to_char(null) trx_creation_date, to_number(null) delinquency_id, csu.org_id org_id, hu.name operating_unit_name FROM iex_strategy_work_items wkitem, iex_stry_temp_work_items_vl stry_temp_wkitem, iex_strategy_templates_vl stry_temp, iex_lookups_v lookup, iex_lookups_v lookup1, wf_item_types_vl item, jtf_objects_b objb, jtf_rs_resource_extns res, iex_strategies strat, hz_parties party, hz_cust_site_uses_all csu, hr_operating_units hu WHERE wkitem.work_item_template_id = stry_temp_wkitem.work_item_temp_id AND stry_temp_wkitem.category_type = lookup1.lookup_code AND lookup1.lookup_type = 'IEX_STRATEGY_WORK_CATEGORY' AND wkitem.status_code = lookup.lookup_code AND lookup.lookup_type = 'IEX_STRATEGY_WORK_STATUS' AND stry_temp_wkitem.workflow_item_type = item.name(+) AND wkitem.resource_id = res.resource_id AND wkitem.strategy_temp_id = stry_temp.strategy_temp_id AND TRUNC(sysdate) >= TRUNC(wkitem.execute_start) AND objb.object_code = decode(strat.strategy_level,10,'IEX_CUSTOMER',20,'IEX_ACCOUNT',30,'IEX_BILLTO','IEX_DELINQUENCY') AND wkitem.status_code = 'OPEN' AND wkitem.strategy_id = strat.strategy_id AND strat.status_code = 'OPEN' AND strat.checklist_yn <> 'Y' AND strat.party_id = party.party_id AND strat.delinquency_id is NULL AND strat.jtf_object_type in ('IEX_BANKRUPTCY','IEX_WRITEOFF','IEX_REPOSSESSION','IEX_LITIGATION') AND strat.customer_site_use_id = csu.site_use_id AND csu.org_id = hu.organization_id

View Text - HTML Formatted

SELECT OBJB.OBJECT_FUNCTION IEU_OBJECT_FUNCTION
, OBJB.OBJECT_PARAMETERS || ' DISPLAYCBO=IEXSTTAB RETURNED_FROM=IEUSEUWQ MO_DEFAULT_ORG_ID=' || DLN.ORG_ID IEU_OBJECT_PARAMETERS
, '' IEU_MEDIA_TYPE_UUID
, DECODE(STRAT.STRATEGY_LEVEL
, 10
, 'IEX_CUSTOMER'
, 20
, 'IEX_ACCOUNT'
, 30
, 'IEX_BILLTO'
, 'IEX_DELINQUENCY') IEU_PARAM_PK_COL
, DECODE(STRAT.STRATEGY_LEVEL
, 10
, TO_CHAR(STRAT.PARTY_ID)
, 20
, TO_CHAR(STRAT.CUST_ACCOUNT_ID)
, 30
, TO_CHAR(STRAT.CUSTOMER_SITE_USE_ID)
, TO_CHAR(STRAT.DELINQUENCY_ID)) IEU_PARAM_PK_VALUE
, WKITEM.RESOURCE_ID RESOURCE_ID
, STRAT.PARTY_ID CUSTOMER_ID
, PARTY.PARTY_NAME CUSTOMER_NAME
, PARTY.PERSON_FIRST_NAME FIRST_NAME
, PARTY.PERSON_LAST_NAME LAST_NAME
, 'RS_INDIVIDUAL' RESOURCE_TYPE
, WKITEM.STRATEGY_ID STRATEGY_ID
, WKITEM.STRATEGY_TEMP_ID STRATEGY_TEMPLATE_ID
, STRY_TEMP.STRATEGY_NAME STRATEGY_NAME
, WKITEM.WORK_ITEM_ORDER WKITEM_ORDER
, WKITEM.WORK_ITEM_ID WKITEM_ID
, WKITEM.WORK_ITEM_TEMPLATE_ID WKITEM_TEMPLATE_ID
, STRY_TEMP_WKITEM.NAME WKITEM_TEMPLATE_NAME
, LOOKUP1.MEANING CATEGORY_MEANING
, STRY_TEMP_WKITEM.CATEGORY_TYPE CATEGORY
, WKITEM.EXECUTE_START START_TIME
, WKITEM.EXECUTE_END END_TIME
, WKITEM.RESOURCE_ID ASSIGNED_TO
, RES.SOURCE_NAME ASSIGNEE
, STRY_TEMP_WKITEM.WORK_TYPE WORK_TYPE
, STRY_TEMP_WKITEM.ESCALATE_YN ESCALATE_YN
, ITEM.DISPLAY_NAME WORKFLOW_ITEM_TYPE
, LOOKUP.MEANING STATUS
, 0 STRATEGY_USER_ITEM_ID
, WKITEM.CREATION_DATE CREATION_DATE
, WKITEM.CREATED_BY CREATED_BY
, WKITEM.LAST_UPDATE_DATE LAST_UPDATE_DATE
, WKITEM.LAST_UPDATED_BY LAST_UPDATED_BY
, WKITEM.SCHEDULE_START SCHEDULE_START
, WKITEM.SCHEDULE_END SCHEDULE_END
, STRAT.JTF_OBJECT_TYPE JTF_OBJECT_TYPE
, STRAT.JTF_OBJECT_ID JTF_OBJECT_ID
, STRAT.STRATEGY_LEVEL STRATEGY_LEVEL
, WKITEM.UWQ_STATUS UWQ_STATUS
, WKITEM.UWQ_ACTIVE_DATE UWQ_ACTIVE_DATE
, WKITEM.UWQ_COMPLETE_DATE UWQ_COMPLETE_DATE
, PS.TRX_NUMBER TRX_NUMBER
, PS.AMOUNT_DUE_ORIGINAL AMOUNT_DUE_ORIGINAL
, PS.AMOUNT_DUE_REMAINING AMOUNT_DUE_REMAINING
, PS.INVOICE_CURRENCY_CODE CURRENCY_CODE
, PS.DUE_DATE DUE_DATE
, '' AGING_BUCKET_NAME
, '' BUCKET_LINE_DESC
, '' TRX_CREATION_DATE
, STRAT.DELINQUENCY_ID DELINQUENCY_ID
, DLN.ORG_ID ORG_ID
, HU.NAME OPERATING_UNIT_NAME
FROM IEX_STRATEGY_WORK_ITEMS WKITEM
, IEX_STRY_TEMP_WORK_ITEMS_VL STRY_TEMP_WKITEM
, IEX_STRATEGY_TEMPLATES_VL STRY_TEMP
, IEX_LOOKUPS_V LOOKUP
, IEX_LOOKUPS_V LOOKUP1
, WF_ITEM_TYPES_VL ITEM
, JTF_OBJECTS_B OBJB
, JTF_RS_RESOURCE_EXTNS RES
, IEX_STRATEGIES STRAT
, HZ_PARTIES PARTY
, AR_PAYMENT_SCHEDULES PS
, IEX_DELINQUENCIES DLN
, HR_OPERATING_UNITS HU
WHERE WKITEM.WORK_ITEM_TEMPLATE_ID = STRY_TEMP_WKITEM.WORK_ITEM_TEMP_ID
AND STRY_TEMP_WKITEM.CATEGORY_TYPE = LOOKUP1.LOOKUP_CODE
AND LOOKUP1.LOOKUP_TYPE = 'IEX_STRATEGY_WORK_CATEGORY'
AND WKITEM.STATUS_CODE = LOOKUP.LOOKUP_CODE
AND LOOKUP.LOOKUP_TYPE = 'IEX_STRATEGY_WORK_STATUS'
AND STRY_TEMP_WKITEM.WORKFLOW_ITEM_TYPE = ITEM.NAME(+)
AND WKITEM.RESOURCE_ID = RES.RESOURCE_ID
AND WKITEM.STRATEGY_TEMP_ID = STRY_TEMP.STRATEGY_TEMP_ID
AND TRUNC(SYSDATE) >= TRUNC(WKITEM.EXECUTE_START)
AND OBJB.OBJECT_CODE = DECODE(STRAT.STRATEGY_LEVEL
, 10
, 'IEX_CUSTOMER'
, 20
, 'IEX_ACCOUNT'
, 30
, 'IEX_BILLTO'
, 'IEX_DELINQUENCY')
AND WKITEM.STATUS_CODE = 'OPEN'
AND WKITEM.STRATEGY_ID = STRAT.STRATEGY_ID
AND STRAT.STATUS_CODE = 'OPEN'
AND STRAT.CHECKLIST_YN <> 'Y'
AND STRAT.PARTY_ID = PARTY.PARTY_ID
AND STRAT.DELINQUENCY_ID IS NOT NULL
AND STRAT.DELINQUENCY_ID = DLN.DELINQUENCY_ID
AND DLN.PAYMENT_SCHEDULE_ID = PS.PAYMENT_SCHEDULE_ID(+)
AND PS.STATUS(+) = 'OP'
AND DLN.ORG_ID = HU.ORGANIZATION_ID UNION ALL SELECT OBJB.OBJECT_FUNCTION IEU_OBJECT_FUNCTION
, OBJB.OBJECT_PARAMETERS || ' DISPLAYCBO=IEXSTTAB RETURNED_FROM=IEUSEUWQ MO_DEFAULT_ORG_ID=' || CSU.ORG_ID IEU_OBJECT_PARAMETERS
, '' IEU_MEDIA_TYPE_UUID
, DECODE(STRAT.STRATEGY_LEVEL
, 10
, 'IEX_CUSTOMER'
, 20
, 'IEX_ACCOUNT'
, 30
, 'IEX_BILLTO'
, 'IEX_DELINQUENCY') IEU_PARAM_PK_COL
, DECODE(STRAT.STRATEGY_LEVEL
, 10
, TO_CHAR(STRAT.PARTY_ID)
, 20
, TO_CHAR(STRAT.CUST_ACCOUNT_ID)
, 30
, TO_CHAR(STRAT.CUSTOMER_SITE_USE_ID)
, TO_CHAR(STRAT.DELINQUENCY_ID)) IEU_PARAM_PK_VALUE
, WKITEM.RESOURCE_ID RESOURCE_ID
, STRAT.PARTY_ID CUSTOMER_ID
, PARTY.PARTY_NAME CUSTOMER_NAME
, PARTY.PERSON_FIRST_NAME FIRST_NAME
, PARTY.PERSON_LAST_NAME LAST_NAME
, 'RS_INDIVIDUAL' RESOURCE_TYPE
, WKITEM.STRATEGY_ID STRATEGY_ID
, WKITEM.STRATEGY_TEMP_ID STRATEGY_TEMPLATE_ID
, STRY_TEMP.STRATEGY_NAME STRATEGY_NAME
, WKITEM.WORK_ITEM_ORDER WKITEM_ORDER
, WKITEM.WORK_ITEM_ID WKITEM_ID
, WKITEM.WORK_ITEM_TEMPLATE_ID WKITEM_TEMPLATE_ID
, STRY_TEMP_WKITEM.NAME WKITEM_TEMPLATE_NAME
, LOOKUP1.MEANING CATEGORY_MEANING
, STRY_TEMP_WKITEM.CATEGORY_TYPE CATEGORY
, WKITEM.EXECUTE_START START_TIME
, WKITEM.EXECUTE_END END_TIME
, WKITEM.RESOURCE_ID ASSIGNED_TO
, RES.SOURCE_NAME ASSIGNEE
, STRY_TEMP_WKITEM.WORK_TYPE WORK_TYPE
, STRY_TEMP_WKITEM.ESCALATE_YN ESCALATE_YN
, ITEM.DISPLAY_NAME WORKFLOW_ITEM_TYPE
, LOOKUP.MEANING STATUS
, 0 STRATEGY_USER_ITEM_ID
, WKITEM.CREATION_DATE CREATION_DATE
, WKITEM.CREATED_BY CREATED_BY
, WKITEM.LAST_UPDATE_DATE LAST_UPDATE_DATE
, WKITEM.LAST_UPDATED_BY LAST_UPDATED_BY
, WKITEM.SCHEDULE_START SCHEDULE_START
, WKITEM.SCHEDULE_END SCHEDULE_END
, STRAT.JTF_OBJECT_TYPE JTF_OBJECT_TYPE
, STRAT.JTF_OBJECT_ID JTF_OBJECT_ID
, STRAT.STRATEGY_LEVEL STRATEGY_LEVEL
, WKITEM.UWQ_STATUS UWQ_STATUS
, WKITEM.UWQ_ACTIVE_DATE UWQ_ACTIVE_DATE
, WKITEM.UWQ_COMPLETE_DATE UWQ_COMPLETE_DATE
, TO_CHAR(NULL) TRX_NUMBER
, TO_NUMBER(NULL) AMOUNT_DUE_ORIGINAL
, TO_NUMBER(NULL) AMOUNT_DUE_REMAINING
, TO_CHAR(NULL) CURRENCY_CODE
, TO_DATE(NULL) DUE_DATE
, TO_CHAR(NULL) AGING_BUCKET_NAME
, TO_CHAR(NULL) BUCKET_LINE_DESC
, TO_CHAR(NULL) TRX_CREATION_DATE
, TO_NUMBER(NULL) DELINQUENCY_ID
, CSU.ORG_ID ORG_ID
, HU.NAME OPERATING_UNIT_NAME
FROM IEX_STRATEGY_WORK_ITEMS WKITEM
, IEX_STRY_TEMP_WORK_ITEMS_VL STRY_TEMP_WKITEM
, IEX_STRATEGY_TEMPLATES_VL STRY_TEMP
, IEX_LOOKUPS_V LOOKUP
, IEX_LOOKUPS_V LOOKUP1
, WF_ITEM_TYPES_VL ITEM
, JTF_OBJECTS_B OBJB
, JTF_RS_RESOURCE_EXTNS RES
, IEX_STRATEGIES STRAT
, HZ_PARTIES PARTY
, HZ_CUST_SITE_USES_ALL CSU
, HR_OPERATING_UNITS HU
WHERE WKITEM.WORK_ITEM_TEMPLATE_ID = STRY_TEMP_WKITEM.WORK_ITEM_TEMP_ID
AND STRY_TEMP_WKITEM.CATEGORY_TYPE = LOOKUP1.LOOKUP_CODE
AND LOOKUP1.LOOKUP_TYPE = 'IEX_STRATEGY_WORK_CATEGORY'
AND WKITEM.STATUS_CODE = LOOKUP.LOOKUP_CODE
AND LOOKUP.LOOKUP_TYPE = 'IEX_STRATEGY_WORK_STATUS'
AND STRY_TEMP_WKITEM.WORKFLOW_ITEM_TYPE = ITEM.NAME(+)
AND WKITEM.RESOURCE_ID = RES.RESOURCE_ID
AND WKITEM.STRATEGY_TEMP_ID = STRY_TEMP.STRATEGY_TEMP_ID
AND TRUNC(SYSDATE) >= TRUNC(WKITEM.EXECUTE_START)
AND OBJB.OBJECT_CODE = DECODE(STRAT.STRATEGY_LEVEL
, 10
, 'IEX_CUSTOMER'
, 20
, 'IEX_ACCOUNT'
, 30
, 'IEX_BILLTO'
, 'IEX_DELINQUENCY')
AND WKITEM.STATUS_CODE = 'OPEN'
AND WKITEM.STRATEGY_ID = STRAT.STRATEGY_ID
AND STRAT.STATUS_CODE = 'OPEN'
AND STRAT.CHECKLIST_YN <> 'Y'
AND STRAT.PARTY_ID = PARTY.PARTY_ID
AND STRAT.DELINQUENCY_ID IS NULL
AND STRAT.JTF_OBJECT_TYPE IN ('IEX_BANKRUPTCY'
, 'IEX_WRITEOFF'
, 'IEX_REPOSSESSION'
, 'IEX_LITIGATION')
AND STRAT.CUSTOMER_SITE_USE_ID = CSU.SITE_USE_ID
AND CSU.ORG_ID = HU.ORGANIZATION_ID