VIEW: APPS.OKL_LA_ROLLOVER_FEE_UV

Source

View Text - Preformatted

SELECT QTE.ID QTE_ID, QTE.QUOTE_NUMBER ROLLOVER_QUOTE, CHR.CONTRACT_NUMBER CONTRACT_NUMBER, SUM(TQL.AMOUNT) ROLLOVER_AMOUNT, CHR.CUST_ACCT_ID CUST_ACCT_ID, QTE.DATE_EFFECTIVE_FROM DATE_EFFECTIVE_FROM, QTE.DATE_EFFECTIVE_TO DATE_EFFECTIVE_TO, CHR.CURRENCY_CODE CURRENCY_CODE, KHR.KHR_ID KHR_ID, KHR.LEGAL_ENTITY_ID LEGAL_ENTITY_ID FROM OKL_TRX_QUOTES_ALL_B QTE,OKL_TXL_QTE_LINES_ALL_B TQL,OKC_K_HEADERS_B CHR,OKL_K_HEADERS KHR,OKC_RULE_GROUPS_B RGP,OKC_RULES_B RUL WHERE CHR.ID = QTE.KHR_ID AND QTE.QST_CODE = 'APPROVED' AND QTE.CONSOLIDATED_YN = 'N' AND QTE.QTP_CODE IN ('TER_ROLL_PURCHASE', 'TER_ROLL_WO_PURCHASE') AND QTE.ID = TQL.QTE_ID AND TQL.QLT_CODE NOT IN ('AMCFIA', 'AMCTAX', 'AMYOUB', 'BILL_ADJST') AND CHR.ID = KHR.ID AND KHR.KHR_ID = RGP.DNZ_CHR_ID (+) AND RGP.CLE_ID (+) IS NULL AND RGP.RGD_CODE (+) = 'LAFEE' AND RGP.ID = RUL.RGP_ID (+) AND RUL.RULE_INFORMATION_CATEGORY (+) = 'LARFEE' AND RUL.RULE_INFORMATION1(+) = 'Y' GROUP BY QTE.ID, QTE.QUOTE_NUMBER, CHR.CONTRACT_NUMBER, CHR.CUST_ACCT_ID, QTE.DATE_EFFECTIVE_FROM, QTE.DATE_EFFECTIVE_TO, CHR.CURRENCY_CODE, KHR.KHR_ID,KHR.LEGAL_ENTITY_ID ORDER BY CHR.CONTRACT_NUMBER, QTE.QUOTE_NUMBER

View Text - HTML Formatted

SELECT QTE.ID QTE_ID
, QTE.QUOTE_NUMBER ROLLOVER_QUOTE
, CHR.CONTRACT_NUMBER CONTRACT_NUMBER
, SUM(TQL.AMOUNT) ROLLOVER_AMOUNT
, CHR.CUST_ACCT_ID CUST_ACCT_ID
, QTE.DATE_EFFECTIVE_FROM DATE_EFFECTIVE_FROM
, QTE.DATE_EFFECTIVE_TO DATE_EFFECTIVE_TO
, CHR.CURRENCY_CODE CURRENCY_CODE
, KHR.KHR_ID KHR_ID
, KHR.LEGAL_ENTITY_ID LEGAL_ENTITY_ID
FROM OKL_TRX_QUOTES_ALL_B QTE
, OKL_TXL_QTE_LINES_ALL_B TQL
, OKC_K_HEADERS_B CHR
, OKL_K_HEADERS KHR
, OKC_RULE_GROUPS_B RGP
, OKC_RULES_B RUL
WHERE CHR.ID = QTE.KHR_ID
AND QTE.QST_CODE = 'APPROVED'
AND QTE.CONSOLIDATED_YN = 'N'
AND QTE.QTP_CODE IN ('TER_ROLL_PURCHASE'
, 'TER_ROLL_WO_PURCHASE')
AND QTE.ID = TQL.QTE_ID
AND TQL.QLT_CODE NOT IN ('AMCFIA'
, 'AMCTAX'
, 'AMYOUB'
, 'BILL_ADJST')
AND CHR.ID = KHR.ID
AND KHR.KHR_ID = RGP.DNZ_CHR_ID (+)
AND RGP.CLE_ID (+) IS NULL
AND RGP.RGD_CODE (+) = 'LAFEE'
AND RGP.ID = RUL.RGP_ID (+)
AND RUL.RULE_INFORMATION_CATEGORY (+) = 'LARFEE'
AND RUL.RULE_INFORMATION1(+) = 'Y' GROUP BY QTE.ID
, QTE.QUOTE_NUMBER
, CHR.CONTRACT_NUMBER
, CHR.CUST_ACCT_ID
, QTE.DATE_EFFECTIVE_FROM
, QTE.DATE_EFFECTIVE_TO
, CHR.CURRENCY_CODE
, KHR.KHR_ID
, KHR.LEGAL_ENTITY_ID ORDER BY CHR.CONTRACT_NUMBER
, QTE.QUOTE_NUMBER