VIEW: APPS.OKL_AM_QUOTES_UV

Source

View Text - Preformatted

SELECT OTQ.ID ID, OTQ.QUOTE_NUMBER QUOTE_NUMBER, OTQ.QTP_CODE QUOTE_TYPE, FL2.MEANING QUOTE_TYPE_DESCRIPTION, OTQ.QST_CODE QUOTE_STATUS, FL1.MEANING QUOTE_STATUS_DESCRIPTION, OTQ.QRS_CODE QUOTE_REASON, FL3.MEANING QUOTE_REASON_DESCRIPTION, OTQ.DATE_EFFECTIVE_FROM DATE_EFFECTIVE_FROM, OTQ.DATE_EFFECTIVE_TO DATE_EFFECTIVE_TO, OTQ.EARLY_TERMINATION_YN EARLY_TERMINATION_YN, OTQ.APPROVED_YN APPROVED_YN, OTQ.ACCEPTED_YN ACCEPTED_YN, OTQ.SUMMARY_FORMAT_YN SUMMARY_FORMAT_YN, OTQ.PREPROCEEDS_YN PREPROCEEDS_YN, OTQ.PAYMENT_RECEIVED_YN PAYMENT_RECEIVED_YN, OTQ.COMMENTS COMMENTS, OKF.ID KHR_ID, OKF.PDT_ID PRODUCT_TYPE_ID, OKF.CONTRACT_NUMBER CONTRACT_NUMBER, OKF.START_DATE CONTRACT_START_DATE, OKF.END_DATE CONTRACT_END_DATE, OKF.STS_CODE CONTRACT_STATUS_CODE, OSV.MEANING CONTRACT_STATUS_MEANING, QPT.ID QUOTE_PARTY_ID, QPT.QPT_CODE QUOTE_PARTY_ROLE, FL4.MEANING QUOTE_PARTY_DESCRIPTION, QPT.DATE_SENT DATE_SENT, KPR.ID CONTRACT_PARTY_ID, KPR.RLE_CODE CONTRACT_PARTY_ROLE, FL5.MEANING CONTRACT_PARTY_DESCRIPTION, NVL2 (QPT.CPL_ID, KPR.OBJECT1_ID1, QPT.PARTY_OBJECT1_ID1) RECIPIENT_ID, NVL2 (QPT.CPL_ID, okl_am_util_pvt.get_jtf_object_name (kpr.jtot_object1_code, kpr.object1_id1, kpr.object1_id2), okl_am_util_pvt.get_jtf_object_name (qpt.party_jtot_object1_code, qpt.party_object1_id1,qpt.party_object1_id2)) RECIPIENT_NAME, NVL2 (QPT.CPL_ID, okl_am_util_pvt.get_jtf_object_column ('PARTY_NUMBER', kpr.jtot_object1_code, kpr.object1_id1, kpr.object1_id2), okl_am_util_pvt.get_jtf_object_column ('PARTY_NUMBER', qpt.party_jtot_object1_code, qpt.party_object1_id1,qpt.party_object1_id2)) RECIPIENT_NUMBER, okl_am_util_pvt.get_trx_msgs_yn ('OKL_TRX_QUOTES_V', OTQ.ID) MESSAGES_YN, okl_am_util_pvt.get_quote_amount (OTQ.ID) AMOUNT, OTQ.CONSOLIDATED_QTE_ID CONSOLIDATED_QTE_ID, OTQ.CONSOLIDATED_YN CONSOLIDATED_YN, OTQ.ORG_ID QUOTE_ORG_ID, KPR2.ID LESSEE_KPR_ID, HZP.party_id LESSEE_PARTY_ID, HZP.PARTY_NAME LESSEE_PARTY_NAME, OTQ.CURRENCY_CODE CURRENCY_CODE, OTQ.CURRENCY_CONVERSION_CODE CURRENCY_CONVERSION_CODE, OTQ.CURRENCY_CONVERSION_TYPE CURRENCY_CONVERSION_TYPE, OTQ.CURRENCY_CONVERSION_RATE CURRENCY_CONVERSION_RATE, OTQ.CURRENCY_CONVERSION_DATE CURRENCY_CONVERSION_DATE, OTQ.PARTIAL_YN PARTIAL_YN, OTQ.CREATION_DATE QUOTE_CREATION_DATE, okl_am_util_pvt.get_anticipated_bill(OTQ.ID) ANTICIPATED_BILL_AMOUNT FROM OKL_TRX_QUOTES_V OTQ,OKL_K_HEADERS_FULL_V OKF,OKC_STATUSES_V OSV,OKL_QUOTE_PARTIES QPT,OKC_K_PARTY_ROLES_B KPR,FND_LOOKUPS FL1,FND_LOOKUPS FL2,FND_LOOKUPS FL3,FND_LOOKUPS FL4,FND_LOOKUPS FL5,HZ_PARTIES HZP,OKC_K_PARTY_ROLES_B KPR2 WHERE OTQ.KHR_ID = OKF.ID AND OKF.STS_CODE = OSV.CODE AND OTQ.ID = QPT.QTE_ID(+) AND QPT.QPT_CODE(+) = 'RECIPIENT' AND QPT.CPL_ID = KPR.ID(+) AND FL1.LOOKUP_TYPE = 'OKL_QUOTE_STATUS' AND FL2.LOOKUP_TYPE = 'OKL_QUOTE_TYPE' AND FL3.LOOKUP_TYPE = 'OKL_QUOTE_REASON' AND FL4.LOOKUP_TYPE(+) = 'OKL_QUOTE_PARTY_TYPE' AND FL5.LOOKUP_TYPE(+) = 'OKC_ROLE' AND FL1.LOOKUP_CODE = OTQ.QST_CODE AND FL2.LOOKUP_CODE = OTQ.QTP_CODE AND FL3.LOOKUP_CODE = OTQ.QRS_CODE AND FL4.LOOKUP_CODE(+) = QPT.QPT_CODE AND FL5.LOOKUP_CODE(+) = KPR.RLE_CODE AND HZP.party_id = KPR2.object1_id1 AND KPR2.CHR_ID = OKF.ID AND KPR2.JTOT_OBJECT1_CODE = 'OKX_PARTY' AND KPR2.RLE_CODE = 'LESSEE'

View Text - HTML Formatted

SELECT OTQ.ID ID
, OTQ.QUOTE_NUMBER QUOTE_NUMBER
, OTQ.QTP_CODE QUOTE_TYPE
, FL2.MEANING QUOTE_TYPE_DESCRIPTION
, OTQ.QST_CODE QUOTE_STATUS
, FL1.MEANING QUOTE_STATUS_DESCRIPTION
, OTQ.QRS_CODE QUOTE_REASON
, FL3.MEANING QUOTE_REASON_DESCRIPTION
, OTQ.DATE_EFFECTIVE_FROM DATE_EFFECTIVE_FROM
, OTQ.DATE_EFFECTIVE_TO DATE_EFFECTIVE_TO
, OTQ.EARLY_TERMINATION_YN EARLY_TERMINATION_YN
, OTQ.APPROVED_YN APPROVED_YN
, OTQ.ACCEPTED_YN ACCEPTED_YN
, OTQ.SUMMARY_FORMAT_YN SUMMARY_FORMAT_YN
, OTQ.PREPROCEEDS_YN PREPROCEEDS_YN
, OTQ.PAYMENT_RECEIVED_YN PAYMENT_RECEIVED_YN
, OTQ.COMMENTS COMMENTS
, OKF.ID KHR_ID
, OKF.PDT_ID PRODUCT_TYPE_ID
, OKF.CONTRACT_NUMBER CONTRACT_NUMBER
, OKF.START_DATE CONTRACT_START_DATE
, OKF.END_DATE CONTRACT_END_DATE
, OKF.STS_CODE CONTRACT_STATUS_CODE
, OSV.MEANING CONTRACT_STATUS_MEANING
, QPT.ID QUOTE_PARTY_ID
, QPT.QPT_CODE QUOTE_PARTY_ROLE
, FL4.MEANING QUOTE_PARTY_DESCRIPTION
, QPT.DATE_SENT DATE_SENT
, KPR.ID CONTRACT_PARTY_ID
, KPR.RLE_CODE CONTRACT_PARTY_ROLE
, FL5.MEANING CONTRACT_PARTY_DESCRIPTION
, NVL2 (QPT.CPL_ID
, KPR.OBJECT1_ID1
, QPT.PARTY_OBJECT1_ID1) RECIPIENT_ID
, NVL2 (QPT.CPL_ID
, OKL_AM_UTIL_PVT.GET_JTF_OBJECT_NAME (KPR.JTOT_OBJECT1_CODE
, KPR.OBJECT1_ID1
, KPR.OBJECT1_ID2)
, OKL_AM_UTIL_PVT.GET_JTF_OBJECT_NAME (QPT.PARTY_JTOT_OBJECT1_CODE
, QPT.PARTY_OBJECT1_ID1
, QPT.PARTY_OBJECT1_ID2)) RECIPIENT_NAME
, NVL2 (QPT.CPL_ID
, OKL_AM_UTIL_PVT.GET_JTF_OBJECT_COLUMN ('PARTY_NUMBER'
, KPR.JTOT_OBJECT1_CODE
, KPR.OBJECT1_ID1
, KPR.OBJECT1_ID2)
, OKL_AM_UTIL_PVT.GET_JTF_OBJECT_COLUMN ('PARTY_NUMBER'
, QPT.PARTY_JTOT_OBJECT1_CODE
, QPT.PARTY_OBJECT1_ID1
, QPT.PARTY_OBJECT1_ID2)) RECIPIENT_NUMBER
, OKL_AM_UTIL_PVT.GET_TRX_MSGS_YN ('OKL_TRX_QUOTES_V'
, OTQ.ID) MESSAGES_YN
, OKL_AM_UTIL_PVT.GET_QUOTE_AMOUNT (OTQ.ID) AMOUNT
, OTQ.CONSOLIDATED_QTE_ID CONSOLIDATED_QTE_ID
, OTQ.CONSOLIDATED_YN CONSOLIDATED_YN
, OTQ.ORG_ID QUOTE_ORG_ID
, KPR2.ID LESSEE_KPR_ID
, HZP.PARTY_ID LESSEE_PARTY_ID
, HZP.PARTY_NAME LESSEE_PARTY_NAME
, OTQ.CURRENCY_CODE CURRENCY_CODE
, OTQ.CURRENCY_CONVERSION_CODE CURRENCY_CONVERSION_CODE
, OTQ.CURRENCY_CONVERSION_TYPE CURRENCY_CONVERSION_TYPE
, OTQ.CURRENCY_CONVERSION_RATE CURRENCY_CONVERSION_RATE
, OTQ.CURRENCY_CONVERSION_DATE CURRENCY_CONVERSION_DATE
, OTQ.PARTIAL_YN PARTIAL_YN
, OTQ.CREATION_DATE QUOTE_CREATION_DATE
, OKL_AM_UTIL_PVT.GET_ANTICIPATED_BILL(OTQ.ID) ANTICIPATED_BILL_AMOUNT
FROM OKL_TRX_QUOTES_V OTQ
, OKL_K_HEADERS_FULL_V OKF
, OKC_STATUSES_V OSV
, OKL_QUOTE_PARTIES QPT
, OKC_K_PARTY_ROLES_B KPR
, FND_LOOKUPS FL1
, FND_LOOKUPS FL2
, FND_LOOKUPS FL3
, FND_LOOKUPS FL4
, FND_LOOKUPS FL5
, HZ_PARTIES HZP
, OKC_K_PARTY_ROLES_B KPR2
WHERE OTQ.KHR_ID = OKF.ID
AND OKF.STS_CODE = OSV.CODE
AND OTQ.ID = QPT.QTE_ID(+)
AND QPT.QPT_CODE(+) = 'RECIPIENT'
AND QPT.CPL_ID = KPR.ID(+)
AND FL1.LOOKUP_TYPE = 'OKL_QUOTE_STATUS'
AND FL2.LOOKUP_TYPE = 'OKL_QUOTE_TYPE'
AND FL3.LOOKUP_TYPE = 'OKL_QUOTE_REASON'
AND FL4.LOOKUP_TYPE(+) = 'OKL_QUOTE_PARTY_TYPE'
AND FL5.LOOKUP_TYPE(+) = 'OKC_ROLE'
AND FL1.LOOKUP_CODE = OTQ.QST_CODE
AND FL2.LOOKUP_CODE = OTQ.QTP_CODE
AND FL3.LOOKUP_CODE = OTQ.QRS_CODE
AND FL4.LOOKUP_CODE(+) = QPT.QPT_CODE
AND FL5.LOOKUP_CODE(+) = KPR.RLE_CODE
AND HZP.PARTY_ID = KPR2.OBJECT1_ID1
AND KPR2.CHR_ID = OKF.ID
AND KPR2.JTOT_OBJECT1_CODE = 'OKX_PARTY'
AND KPR2.RLE_CODE = 'LESSEE'