VIEW: APPS.OKL_AM_ASSET_RETURNS_UV

Source

View Text - Preformatted

SELECT KLE.NAME ASSET_NUMBER, KLE.ITEM_DESCRIPTION ITEM_DESCRIPTION, OART.COMMENTS COMMENTS, KLE.CHR_ID CHR_ID, OKC.CONTRACT_NUMBER CONTRACT_NUMBER, OKC.STS_CODE CHR_STS_CODE, STS.MEANING CHR_STS_MEANING, KLE.STS_CODE CLE_STS_CODE, STS2.MEANING CLE_STS_MEANING, KLE.ID KLE_ID, OAR.COMMMERCIALLY_REAS_SALE_YN COMMMERCIALLY_REAS_SALE_YN, OAR.DATE_HOLD_UNTIL DATE_HOLD_UNTIL, OAR.DATE_REPOSSESSION_ACTUAL DATE_REPOSSESSION_ACTUAL, OAR.DATE_REPOSSESSION_REQUIRED DATE_REPOSSESSION_REQUIRED, OAR.DATE_RETURNED DATE_RETURNED, OAR.DATE_RETURN_DUE DATE_RETURN_DUE, OAR.DATE_RETURN_NOTIFIED DATE_RETURN_NOTIFIED, OAR.RELOCATE_ASSET_YN RELOCATE_ASSET_YN, OAR.VOLUNTARY_YN VOLUNTARY_YN, OAR.RMR_ID RMR_ID, OAR.ARS_CODE ARS_CODE, FND.MEANING ASSET_RETURN_STATUS, OAR.ART1_CODE ART1_CODE, FND2.MEANING ASSET_RETURN_TYPE, OAR.ID ID, OAR.DATE_TITLE_RETURNED DATE_TITLE_RECEIVED, JFFUV.DISPLAY_NAME REMARKETER_NAME, OVV.ID1 AGENT_ID, OVV.NAME AGENT_NAME, OAR.IMR_ID IMR_ID, OAR.SECURITY_DEP_TRX_AP_ID SECURITY_DEP_TRX_AP_ID, OAR.ASSET_RELOCATED_YN ASSET_RELOCATED_YN, OAR.REPURCHASE_AGMT_YN REPURCHASE_AGMT_YN, OART.NEW_ITEM_DESCRIPTION NEW_ITEM_DESCRIPTION, OAR.NEW_ITEM_PRICE NEW_ITEM_PRICE, MSI.SEGMENT1 INVENTORY_ITEM_NUMBER, CIM.NUMBER_OF_ITEMS QUANTITY, OAR.FLOOR_PRICE FLOOR_PRICE, OKC.END_DATE END_DATE, OAR.CREATED_BY, MSI.DESCRIPTION MTL_DESCRIPTION, MSI.ORGANIZATION_ID MTL_ORGANIZATION_ID, KLE2.PRICE_UNIT PRICE_UNIT, KLE.OEC OEC, KLE.RESIDUAL_VALUE RESIDUAL_VALUE, okl_am_util_pvt.get_rule_field_value ('LAAFLG','LAFLTL',KLE.CHR_ID,KLE.ID,'OBJECT2') TITLE_CUSTODIAN, OAR.LIKE_KIND_YN LIKE_KIND_YN, OAR.ORG_ID ORG_ID, KPR.ID LESSEE_KPR_ID, HZP.party_id LESSEE_PARTY_ID, HZP.PARTY_NAME LESSEE_PARTY_NAME, OAR.NEW_ITEM_NUMBER NEW_ITEM_NUMBER, OAR.LEGAL_ENTITY_ID LEGAL_ENTITY_ID, OAR.ASSET_FMV_AMOUNT ASSET_FMV_AMOUNT FROM OKL_K_LINES_FULL_V KLE, OKC_K_HEADERS_ALL_B OKC, OKL_ASSET_RETURNS_ALL_B OAR, FND_LOOKUPS FND, FND_LOOKUPS FND2, OKL_ASSET_RETURNS_TL OART, OKL_AM_REMARKET_TEAMS_UV JFFUV, OKX_VENDORS_V OVV, MTL_SYSTEM_ITEMS_B MSI, OKC_K_LINES_B KLE2, OKC_LINE_STYLES_B LSE, OKC_K_ITEMS CIM, OKC_STATUSES_V STS, OKC_STATUSES_V STS2, HZ_PARTIES HZP, OKC_K_PARTY_ROLES_V KPR, OKL_SYSTEM_PARAMS OSP WHERE OKC.ID = KLE.CHR_ID AND OAR.KLE_ID = KLE.ID AND OAR.ARS_CODE = FND.LOOKUP_CODE AND FND.LOOKUP_TYPE = 'OKL_ASSET_RETURN_STATUS' AND OAR.ART1_CODE = FND2.LOOKUP_CODE AND FND2.LOOKUP_TYPE = 'OKL_ASSET_RETURN_TYPE' AND OAR.ID = OART.ID AND OART.LANGUAGE = USERENV('LANG') AND OAR.RMR_ID = JFFUV.ORIG_SYSTEM_ID (+) AND OAR.RNA_ID = OVV.ID1 (+) AND OAR.IMR_ID = MSI.INVENTORY_ITEM_ID (+) AND MSI.ORGANIZATION_ID = OSP.REMK_ORGANIZATION_ID(+) and msi.source_type(+) = 1 AND KLE.ID = KLE2.CLE_ID AND KLE2.LSE_ID = LSE.ID AND LSE.LTY_CODE = 'ITEM' AND KLE2.ID = CIM.CLE_ID AND OKC.STS_CODE = STS.CODE AND KLE.STS_CODE = STS2.CODE AND HZP.PARTY_ID = KPR.OBJECT1_ID1 AND KPR.CHR_ID = OKC.ID AND KPR.JTOT_OBJECT1_CODE = 'OKX_PARTY' AND KPR.RLE_CODE = 'LESSEE'

View Text - HTML Formatted

SELECT KLE.NAME ASSET_NUMBER
, KLE.ITEM_DESCRIPTION ITEM_DESCRIPTION
, OART.COMMENTS COMMENTS
, KLE.CHR_ID CHR_ID
, OKC.CONTRACT_NUMBER CONTRACT_NUMBER
, OKC.STS_CODE CHR_STS_CODE
, STS.MEANING CHR_STS_MEANING
, KLE.STS_CODE CLE_STS_CODE
, STS2.MEANING CLE_STS_MEANING
, KLE.ID KLE_ID
, OAR.COMMMERCIALLY_REAS_SALE_YN COMMMERCIALLY_REAS_SALE_YN
, OAR.DATE_HOLD_UNTIL DATE_HOLD_UNTIL
, OAR.DATE_REPOSSESSION_ACTUAL DATE_REPOSSESSION_ACTUAL
, OAR.DATE_REPOSSESSION_REQUIRED DATE_REPOSSESSION_REQUIRED
, OAR.DATE_RETURNED DATE_RETURNED
, OAR.DATE_RETURN_DUE DATE_RETURN_DUE
, OAR.DATE_RETURN_NOTIFIED DATE_RETURN_NOTIFIED
, OAR.RELOCATE_ASSET_YN RELOCATE_ASSET_YN
, OAR.VOLUNTARY_YN VOLUNTARY_YN
, OAR.RMR_ID RMR_ID
, OAR.ARS_CODE ARS_CODE
, FND.MEANING ASSET_RETURN_STATUS
, OAR.ART1_CODE ART1_CODE
, FND2.MEANING ASSET_RETURN_TYPE
, OAR.ID ID
, OAR.DATE_TITLE_RETURNED DATE_TITLE_RECEIVED
, JFFUV.DISPLAY_NAME REMARKETER_NAME
, OVV.ID1 AGENT_ID
, OVV.NAME AGENT_NAME
, OAR.IMR_ID IMR_ID
, OAR.SECURITY_DEP_TRX_AP_ID SECURITY_DEP_TRX_AP_ID
, OAR.ASSET_RELOCATED_YN ASSET_RELOCATED_YN
, OAR.REPURCHASE_AGMT_YN REPURCHASE_AGMT_YN
, OART.NEW_ITEM_DESCRIPTION NEW_ITEM_DESCRIPTION
, OAR.NEW_ITEM_PRICE NEW_ITEM_PRICE
, MSI.SEGMENT1 INVENTORY_ITEM_NUMBER
, CIM.NUMBER_OF_ITEMS QUANTITY
, OAR.FLOOR_PRICE FLOOR_PRICE
, OKC.END_DATE END_DATE
, OAR.CREATED_BY
, MSI.DESCRIPTION MTL_DESCRIPTION
, MSI.ORGANIZATION_ID MTL_ORGANIZATION_ID
, KLE2.PRICE_UNIT PRICE_UNIT
, KLE.OEC OEC
, KLE.RESIDUAL_VALUE RESIDUAL_VALUE
, OKL_AM_UTIL_PVT.GET_RULE_FIELD_VALUE ('LAAFLG'
, 'LAFLTL'
, KLE.CHR_ID
, KLE.ID
, 'OBJECT2') TITLE_CUSTODIAN
, OAR.LIKE_KIND_YN LIKE_KIND_YN
, OAR.ORG_ID ORG_ID
, KPR.ID LESSEE_KPR_ID
, HZP.PARTY_ID LESSEE_PARTY_ID
, HZP.PARTY_NAME LESSEE_PARTY_NAME
, OAR.NEW_ITEM_NUMBER NEW_ITEM_NUMBER
, OAR.LEGAL_ENTITY_ID LEGAL_ENTITY_ID
, OAR.ASSET_FMV_AMOUNT ASSET_FMV_AMOUNT
FROM OKL_K_LINES_FULL_V KLE
, OKC_K_HEADERS_ALL_B OKC
, OKL_ASSET_RETURNS_ALL_B OAR
, FND_LOOKUPS FND
, FND_LOOKUPS FND2
, OKL_ASSET_RETURNS_TL OART
, OKL_AM_REMARKET_TEAMS_UV JFFUV
, OKX_VENDORS_V OVV
, MTL_SYSTEM_ITEMS_B MSI
, OKC_K_LINES_B KLE2
, OKC_LINE_STYLES_B LSE
, OKC_K_ITEMS CIM
, OKC_STATUSES_V STS
, OKC_STATUSES_V STS2
, HZ_PARTIES HZP
, OKC_K_PARTY_ROLES_V KPR
, OKL_SYSTEM_PARAMS OSP
WHERE OKC.ID = KLE.CHR_ID
AND OAR.KLE_ID = KLE.ID
AND OAR.ARS_CODE = FND.LOOKUP_CODE
AND FND.LOOKUP_TYPE = 'OKL_ASSET_RETURN_STATUS'
AND OAR.ART1_CODE = FND2.LOOKUP_CODE
AND FND2.LOOKUP_TYPE = 'OKL_ASSET_RETURN_TYPE'
AND OAR.ID = OART.ID
AND OART.LANGUAGE = USERENV('LANG')
AND OAR.RMR_ID = JFFUV.ORIG_SYSTEM_ID (+)
AND OAR.RNA_ID = OVV.ID1 (+)
AND OAR.IMR_ID = MSI.INVENTORY_ITEM_ID (+)
AND MSI.ORGANIZATION_ID = OSP.REMK_ORGANIZATION_ID(+)
AND MSI.SOURCE_TYPE(+) = 1
AND KLE.ID = KLE2.CLE_ID
AND KLE2.LSE_ID = LSE.ID
AND LSE.LTY_CODE = 'ITEM'
AND KLE2.ID = CIM.CLE_ID
AND OKC.STS_CODE = STS.CODE
AND KLE.STS_CODE = STS2.CODE
AND HZP.PARTY_ID = KPR.OBJECT1_ID1
AND KPR.CHR_ID = OKC.ID
AND KPR.JTOT_OBJECT1_CODE = 'OKX_PARTY'
AND KPR.RLE_CODE = 'LESSEE'