VIEW: APPS.IEX_PROMISES_UWQ_V

Source

View Text - Preformatted

SELECT objb.object_function ieu_object_function, objb.object_parameters || ' RETURNED_FROM=IEUSEUWQ MO_DEFAULT_ORG_ID=' || del.org_id ieu_object_parameters, '' ieu_media_type_uuid, 'PROMISE_DETAIL_ID' ieu_param_pk_col, to_char(prd.promise_detail_id) ieu_param_pk_value, prd.resource_id resource_id, decode(res.category, 'EMPLOYEE', 'RS_INDIVIDUAL', 'PARTNER', 'RS_PARTNER', NULL) resource_type, prd.cust_account_id cust_account_id, hca.account_number account_number, prd.delinquency_id delinquency_id, del.payment_schedule_id payment_schedule_id, psa.customer_trx_id customer_trx_id, psa.trx_number trx_number, psa.trx_date trx_date, psa.terms_sequence_number terms_sequence_number, psa.due_date due_date, prd.promise_date promise_date, prd.promise_amount promise_amount, prd.promise_payment_method promise_payment_method, decode(prd.promise_payment_method, lkpv1.lookup_code, lkpv1.meaning, NULL) payment_method, prd.amount_due_remaining amount_due_remaining, prd.status status, prd.account account, prd.promise_item_number promise_item_number, prd.currency_code currency_code, ams.source_code source_code, party.party_name party_name, party1.person_first_name first_name, party1.person_last_name last_name, party.party_id party_id, prd.creation_date creation_date, prd.last_updated_by last_updated_by, prd.last_update_date last_update_date, rs.source_name last_updated_byname, prd.uwq_status uwq_status, prd.uwq_active_date uwq_active_date, prd.uwq_complete_date uwq_complete_date, del.org_id org_id, hu.name operating_unit_name, task.task_name task_name, task.task_number task_number, status.name task_status FROM iex_promise_details prd, iex_delinquencies del, hr_operating_units hu, ar_payment_schedules_all psa, hz_cust_accounts hca, iex_lookups_v lkpv1, jtf_rs_resource_extns res, jtf_rs_resource_extns rs, hz_parties party, hz_parties party1, ams_source_codes ams, jtf_objects_b objb, jtf_tasks_vl task, jtf_task_statuses_vl status WHERE prd.delinquency_id IS NOT NULL AND del.org_id = hu.organization_id AND prd.delinquency_id = del.delinquency_id AND prd.cust_account_id = hca.cust_account_id(+) AND del.payment_schedule_id = psa.payment_schedule_id AND psa.status = 'OP' AND hca.party_id = party.party_id AND lkpv1.lookup_type = 'IEX_PAYMENT_TYPES' AND prd.resource_id = res.resource_id AND rs.user_id = prd.last_updated_by AND prd.state = 'BROKEN_PROMISE' AND prd.status IN('COLLECTABLE', 'PENDING') AND del.status IN('DELINQUENT', 'PREDELINQUENT') AND objb.object_code = 'IEX_PROMISE' AND(lkpv1.lookup_code = prd.promise_payment_method OR prd.promise_payment_method IS NULL) AND prd.campaign_sched_id = ams.source_code_id(+) AND prd.promise_made_by = party1.party_id(+) AND prd.promise_detail_id = task.source_object_id(+) AND task.task_status_id = status.task_status_id(+) UNION SELECT objb.object_function ieu_object_function, objb.object_parameters ieu_object_parameters, '' ieu_media_type_uuid, 'PROMISE_DETAIL_ID' ieu_param_pk_col, to_char(prd.promise_detail_id) ieu_param_pk_value, prd.resource_id resource_id, decode(res.category, 'EMPLOYEE', 'RS_INDIVIDUAL', 'PARTNER', 'RS_PARTNER', NULL) resource_type, prd.cust_account_id cust_account_id, hca.account_number account_number, to_number(NULL) delinquency_id, to_number(NULL) payment_schedule_id, to_number(NULL) customer_trx_id, NULL trx_number, to_date(NULL) trx_date, to_number(NULL) terms_sequence_number, to_date(NULL) due_date, prd.promise_date promise_date, prd.promise_amount promise_amount, prd.promise_payment_method promise_payment_method, decode(prd.promise_payment_method, lkpv1.lookup_code, lkpv1.meaning, NULL) payment_method, prd.amount_due_remaining amount_due_remaining, prd.status status, prd.account account, prd.promise_item_number promise_item_number, prd.currency_code currency_code, ams.source_code source_code, party.party_name party_name, party1.person_first_name first_name, party1.person_last_name last_name, party.party_id party_id, prd.creation_date creation_date, prd.last_updated_by updated_by, prd.last_update_date last_update_date, rs.source_name last_updated_byname, prd.uwq_status uwq_status, prd.uwq_active_date uwq_active_date, prd.uwq_complete_date uwq_complete_date, to_number(NULL) org_id, to_char(NULL) operating_unit_name, to_char(NULL) task_name, to_char(NULL) task_number, to_char(NULL) task_status FROM iex_promise_details prd, hz_cust_accounts hca, iex_lookups_v lkpv1, jtf_rs_resource_extns res, jtf_rs_resource_extns rs, hz_parties party, hz_parties party1, jtf_objects_b objb, ams_source_codes ams, okc_k_headers_b okch WHERE prd.delinquency_id IS NULL AND prd.contract_id = okch.id AND prd.cust_account_id = hca.cust_account_id AND hca.party_id = party.party_id AND lkpv1.lookup_type = 'IEX_PAYMENT_TYPES' AND prd.resource_id = res.resource_id AND rs.user_id = prd.last_updated_by AND prd.state = 'BROKEN_PROMISE' AND prd.status IN('COLLECTABLE', 'PENDING') AND objb.object_code = 'IEX_PROMISE' AND(lkpv1.lookup_code = prd.promise_payment_method OR prd.promise_payment_method IS NULL) AND prd.campaign_sched_id = ams.source_code_id(+) AND prd.promise_made_by = party1.party_id(+)

View Text - HTML Formatted

SELECT OBJB.OBJECT_FUNCTION IEU_OBJECT_FUNCTION
, OBJB.OBJECT_PARAMETERS || ' RETURNED_FROM=IEUSEUWQ MO_DEFAULT_ORG_ID=' || DEL.ORG_ID IEU_OBJECT_PARAMETERS
, '' IEU_MEDIA_TYPE_UUID
, 'PROMISE_DETAIL_ID' IEU_PARAM_PK_COL
, TO_CHAR(PRD.PROMISE_DETAIL_ID) IEU_PARAM_PK_VALUE
, PRD.RESOURCE_ID RESOURCE_ID
, DECODE(RES.CATEGORY
, 'EMPLOYEE'
, 'RS_INDIVIDUAL'
, 'PARTNER'
, 'RS_PARTNER'
, NULL) RESOURCE_TYPE
, PRD.CUST_ACCOUNT_ID CUST_ACCOUNT_ID
, HCA.ACCOUNT_NUMBER ACCOUNT_NUMBER
, PRD.DELINQUENCY_ID DELINQUENCY_ID
, DEL.PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID
, PSA.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
, PSA.TRX_NUMBER TRX_NUMBER
, PSA.TRX_DATE TRX_DATE
, PSA.TERMS_SEQUENCE_NUMBER TERMS_SEQUENCE_NUMBER
, PSA.DUE_DATE DUE_DATE
, PRD.PROMISE_DATE PROMISE_DATE
, PRD.PROMISE_AMOUNT PROMISE_AMOUNT
, PRD.PROMISE_PAYMENT_METHOD PROMISE_PAYMENT_METHOD
, DECODE(PRD.PROMISE_PAYMENT_METHOD
, LKPV1.LOOKUP_CODE
, LKPV1.MEANING
, NULL) PAYMENT_METHOD
, PRD.AMOUNT_DUE_REMAINING AMOUNT_DUE_REMAINING
, PRD.STATUS STATUS
, PRD.ACCOUNT ACCOUNT
, PRD.PROMISE_ITEM_NUMBER PROMISE_ITEM_NUMBER
, PRD.CURRENCY_CODE CURRENCY_CODE
, AMS.SOURCE_CODE SOURCE_CODE
, PARTY.PARTY_NAME PARTY_NAME
, PARTY1.PERSON_FIRST_NAME FIRST_NAME
, PARTY1.PERSON_LAST_NAME LAST_NAME
, PARTY.PARTY_ID PARTY_ID
, PRD.CREATION_DATE CREATION_DATE
, PRD.LAST_UPDATED_BY LAST_UPDATED_BY
, PRD.LAST_UPDATE_DATE LAST_UPDATE_DATE
, RS.SOURCE_NAME LAST_UPDATED_BYNAME
, PRD.UWQ_STATUS UWQ_STATUS
, PRD.UWQ_ACTIVE_DATE UWQ_ACTIVE_DATE
, PRD.UWQ_COMPLETE_DATE UWQ_COMPLETE_DATE
, DEL.ORG_ID ORG_ID
, HU.NAME OPERATING_UNIT_NAME
, TASK.TASK_NAME TASK_NAME
, TASK.TASK_NUMBER TASK_NUMBER
, STATUS.NAME TASK_STATUS
FROM IEX_PROMISE_DETAILS PRD
, IEX_DELINQUENCIES DEL
, HR_OPERATING_UNITS HU
, AR_PAYMENT_SCHEDULES_ALL PSA
, HZ_CUST_ACCOUNTS HCA
, IEX_LOOKUPS_V LKPV1
, JTF_RS_RESOURCE_EXTNS RES
, JTF_RS_RESOURCE_EXTNS RS
, HZ_PARTIES PARTY
, HZ_PARTIES PARTY1
, AMS_SOURCE_CODES AMS
, JTF_OBJECTS_B OBJB
, JTF_TASKS_VL TASK
, JTF_TASK_STATUSES_VL STATUS
WHERE PRD.DELINQUENCY_ID IS NOT NULL
AND DEL.ORG_ID = HU.ORGANIZATION_ID
AND PRD.DELINQUENCY_ID = DEL.DELINQUENCY_ID
AND PRD.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID(+)
AND DEL.PAYMENT_SCHEDULE_ID = PSA.PAYMENT_SCHEDULE_ID
AND PSA.STATUS = 'OP'
AND HCA.PARTY_ID = PARTY.PARTY_ID
AND LKPV1.LOOKUP_TYPE = 'IEX_PAYMENT_TYPES'
AND PRD.RESOURCE_ID = RES.RESOURCE_ID
AND RS.USER_ID = PRD.LAST_UPDATED_BY
AND PRD.STATE = 'BROKEN_PROMISE'
AND PRD.STATUS IN('COLLECTABLE'
, 'PENDING')
AND DEL.STATUS IN('DELINQUENT'
, 'PREDELINQUENT')
AND OBJB.OBJECT_CODE = 'IEX_PROMISE' AND(LKPV1.LOOKUP_CODE = PRD.PROMISE_PAYMENT_METHOD OR PRD.PROMISE_PAYMENT_METHOD IS NULL)
AND PRD.CAMPAIGN_SCHED_ID = AMS.SOURCE_CODE_ID(+)
AND PRD.PROMISE_MADE_BY = PARTY1.PARTY_ID(+)
AND PRD.PROMISE_DETAIL_ID = TASK.SOURCE_OBJECT_ID(+)
AND TASK.TASK_STATUS_ID = STATUS.TASK_STATUS_ID(+) UNION SELECT OBJB.OBJECT_FUNCTION IEU_OBJECT_FUNCTION
, OBJB.OBJECT_PARAMETERS IEU_OBJECT_PARAMETERS
, '' IEU_MEDIA_TYPE_UUID
, 'PROMISE_DETAIL_ID' IEU_PARAM_PK_COL
, TO_CHAR(PRD.PROMISE_DETAIL_ID) IEU_PARAM_PK_VALUE
, PRD.RESOURCE_ID RESOURCE_ID
, DECODE(RES.CATEGORY
, 'EMPLOYEE'
, 'RS_INDIVIDUAL'
, 'PARTNER'
, 'RS_PARTNER'
, NULL) RESOURCE_TYPE
, PRD.CUST_ACCOUNT_ID CUST_ACCOUNT_ID
, HCA.ACCOUNT_NUMBER ACCOUNT_NUMBER
, TO_NUMBER(NULL) DELINQUENCY_ID
, TO_NUMBER(NULL) PAYMENT_SCHEDULE_ID
, TO_NUMBER(NULL) CUSTOMER_TRX_ID
, NULL TRX_NUMBER
, TO_DATE(NULL) TRX_DATE
, TO_NUMBER(NULL) TERMS_SEQUENCE_NUMBER
, TO_DATE(NULL) DUE_DATE
, PRD.PROMISE_DATE PROMISE_DATE
, PRD.PROMISE_AMOUNT PROMISE_AMOUNT
, PRD.PROMISE_PAYMENT_METHOD PROMISE_PAYMENT_METHOD
, DECODE(PRD.PROMISE_PAYMENT_METHOD
, LKPV1.LOOKUP_CODE
, LKPV1.MEANING
, NULL) PAYMENT_METHOD
, PRD.AMOUNT_DUE_REMAINING AMOUNT_DUE_REMAINING
, PRD.STATUS STATUS
, PRD.ACCOUNT ACCOUNT
, PRD.PROMISE_ITEM_NUMBER PROMISE_ITEM_NUMBER
, PRD.CURRENCY_CODE CURRENCY_CODE
, AMS.SOURCE_CODE SOURCE_CODE
, PARTY.PARTY_NAME PARTY_NAME
, PARTY1.PERSON_FIRST_NAME FIRST_NAME
, PARTY1.PERSON_LAST_NAME LAST_NAME
, PARTY.PARTY_ID PARTY_ID
, PRD.CREATION_DATE CREATION_DATE
, PRD.LAST_UPDATED_BY UPDATED_BY
, PRD.LAST_UPDATE_DATE LAST_UPDATE_DATE
, RS.SOURCE_NAME LAST_UPDATED_BYNAME
, PRD.UWQ_STATUS UWQ_STATUS
, PRD.UWQ_ACTIVE_DATE UWQ_ACTIVE_DATE
, PRD.UWQ_COMPLETE_DATE UWQ_COMPLETE_DATE
, TO_NUMBER(NULL) ORG_ID
, TO_CHAR(NULL) OPERATING_UNIT_NAME
, TO_CHAR(NULL) TASK_NAME
, TO_CHAR(NULL) TASK_NUMBER
, TO_CHAR(NULL) TASK_STATUS
FROM IEX_PROMISE_DETAILS PRD
, HZ_CUST_ACCOUNTS HCA
, IEX_LOOKUPS_V LKPV1
, JTF_RS_RESOURCE_EXTNS RES
, JTF_RS_RESOURCE_EXTNS RS
, HZ_PARTIES PARTY
, HZ_PARTIES PARTY1
, JTF_OBJECTS_B OBJB
, AMS_SOURCE_CODES AMS
, OKC_K_HEADERS_B OKCH
WHERE PRD.DELINQUENCY_ID IS NULL
AND PRD.CONTRACT_ID = OKCH.ID
AND PRD.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
AND HCA.PARTY_ID = PARTY.PARTY_ID
AND LKPV1.LOOKUP_TYPE = 'IEX_PAYMENT_TYPES'
AND PRD.RESOURCE_ID = RES.RESOURCE_ID
AND RS.USER_ID = PRD.LAST_UPDATED_BY
AND PRD.STATE = 'BROKEN_PROMISE'
AND PRD.STATUS IN('COLLECTABLE'
, 'PENDING')
AND OBJB.OBJECT_CODE = 'IEX_PROMISE' AND(LKPV1.LOOKUP_CODE = PRD.PROMISE_PAYMENT_METHOD OR PRD.PROMISE_PAYMENT_METHOD IS NULL)
AND PRD.CAMPAIGN_SCHED_ID = AMS.SOURCE_CODE_ID(+)
AND PRD.PROMISE_MADE_BY = PARTY1.PARTY_ID(+)