|
sxschech (TechnicalUser) |
30 May 12 16:52 |
Something like this? CODE --> WITH ADDON AS
(
SELECT DISTINCT AON_MAIN_POLICY,
AON_LINK_POLICY
FROM ADDONS
INNER JOIN GENPOLICY
ON AON_MAIN_POLICY = POLICY_KEY
)
,
DRVTBL AS
(
SELECT ITM_POLICY_CDE,
DECODE(QSD_EDI_SCHEME_CODE,'FC','PMC','PMY','PMY') SCHEMECODE
FROM POLICY_ITEMS
INNER JOIN RISKHISTORY
ON (RKH_POLICY_CDE=ITM_POLICY_CDE
AND RKH_VERSION = ITM_VERSION)
INNER JOIN QUOTE_RESULTS_HEADER
ON QRH_POLICY_ITEMS_CDE = POLICY_ITEMS_key
INNER JOIN QUOTE_OUTPUT_HEADER
ON QOH_QUOTE_RESULT_CDE=QUOTE_RESULTS_HEADER_KEY
INNER JOIN QUOTE_SOFTWARE_DETAILS
ON QUOTE_OUTPUT_HEADER_KEY = QSD_QUOTE_OUTPUT_CDE
WHERE RKH_LIVE = 'Y'
AND RKH_DESCRIPTION IN ('Inception','Renewed')
)
,
P AS
(
SELECT MAX(POLICY_ITEMS_KEY) POLICY_ITEMS_KEY,
POLICY_ITEMS.ITM_POLICY_CDE,
ITM_VERSION,
MAX (ITM_ITEM_SEQ) ITM_ITEM_SEQ,
MAX(ITM_END_DATE) ITM_END_DATE
,SCHEMECODE
FROM POLICY_ITEMS
LEFT JOIN DRVTBL
ON DRVTBL.ITM_POLICY_CDE = POLICY_ITEMS.ITM_POLICY_CDE
WHERE ITM_ITEM_TYPE = 0
GROUP BY POLICY_ITEMS.ITM_POLICY_CDE, ITM_VERSION, SCHEMECODE
)
SELECT DISTINCT -- List of fields and various case statements
.....
FROM ACCOUNTTRANSACTION
INNER JOIN ACCOUNTHEADER
ON ACN_ACCOUNTHEADER_CDE = ACCOUNTHEADER_KEY
INNER JOIN ACCOUNTTRANSACTIONSET
ON ACCOUNTTRANSACTIONSET_CDE = ACCOUNTTRANSACTIONSET_KEY
LEFT OUTER JOIN RISKHISTORY
ON ACCOUNTTRANSACTIONSET_KEY = RKH_ACCOUNTTRANSACTIONSET_CDE
AND ATA_REFERENCE_TRANSACTION = RKH_POLICY_CDE
AND TRUNC (ATA_POSTING_DATE) >= TO_DATE ( '21/04/2011', 'DD/MM/YYYY' )
INNER JOIN PARTY
ON ACH_PARTY_CDE = PARTY_KEY
LEFT OUTER JOIN PAYMETHREF
ON ATA_PAYMETHREF_CDE = PAYMETHREF_KEY
INNER JOIN DISBURSEMENTTYPE
ON ACN_DISBURSEMENTTYPE_CDE = DISBURSEMENTTYPE_KEY
INNER JOIN TRANSACTIONTYPE
ON ATA_TRANSACTIONTYPE_CDE = TRANSACTIONTYPE_KEY
LEFT OUTER JOIN PAYMENTCARDACCTRANS PC
ON ACN_ACCOUNTHEADER_CDE = PAT_ACCHEADCDE
AND ACCOUNTTRANSACTIONSET_CDE = PAT_ACCTRANSETCDE
LEFT OUTER JOIN GENPOLICY MAIN_POL
ON ATA_REFERENCE_TRANSACTION = MAIN_POL.POLICY_KEY
LEFT OUTER JOIN ADDON
ON ATA_REFERENCE_TRANSACTION = ADDONS.AON_LINK_POLICY
LEFT OUTER JOIN GENPOLICY AON_POL
ON ADDONS.AON_MAIN_POLICY = AON_POL.POLICY_KEY
INNER JOIN PMIUSER
ON ATA_UPD_USER = PMIUSER_KEY
LEFT OUTER JOIN REVENUETYPE
ON ATA_REVENUE_CDE = REVENUETYPE_KEY
LEFT OUTER JOIN P
ON RKH_POLICY_CDE = p.ITM_POLICY_CDE
AND RKH_VERSION = p.ITM_VERSION
LEFT OUTER JOIN GENERAL_MOTOR GM
ON P.POLICY_ITEMS_KEY = ITM_POLICY_ITEMS_CDE
LEFT OUTER JOIN EXT_QTE_PREM E
ON P.ITM_POLICY_CDE = E.EQP_POLICY_CDE
AND p.ITM_VERSION = E.EQP_VERSION
AND P.ITM_ITEM_SEQ = E.EQP_ITEM_SEQ
WHERE (
CASE
WHEN ATA_TRANSACTION_AMOUNT = 0
THEN 1
ELSE ACN_TOTAL_AMOUNT
END) != 0
AND TRUNC (ATA_POSTING_DATE) <= SYSDATE - 1
AND TRUNC (RKH_UPD_DATE) >= SYSDATE - 31 |
|
Talk To Other Members
Read More Threads Like This One