eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.OKL_INS_QUOTES_UV

Source

View Text - Preformatted


SELECT IPYB.ROWID ROW_ID, IPYB.ID ID, IPYB.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER, IPYT.SFWT_FLAG SFWT_FLAG, IPYB.IPF_CODE IPF_CODE, DECODE(NVL(IPYB.IPY_ID,NULL),NULL,DECODE(NVL(IPYB.DATE_QUOTE_EXPIRY,NULL),NULL,'ACTIVE',DECODE(SIGN(MONTHS_BETWEEN(IPYB.DATE_QUOTE_EXPIRY,SYSDATE)),1,'ACTIVE',DECODE(SIGN(MONTHS_BETWEEN(IPYB.DATE_QUOTE_EXPIRY,SYSDATE)),-1,'EXPIRED','CONVERTED'))),'CONVERTED') STATUS, IPYB.KHR_ID KHR_ID, IPYB.CALCULATED_PREMIUM CALCULATED_PREMIUM, FLPF.MEANING, IPYB.INT_ID INT_ID, IPYB.ISU_ID ISU_ID, INPR.PARTY_NAME , IPYB.IPT_ID IPT_ID, IPTT.NAME, IPYB.IPY_ID IPY_ID, IPYB.CRX_CODE CRX_CODE, IPYB.ISS_CODE ISS_CODE, FLPS.MEANING, IPYB.KLE_ID KLE_ID, IPYB.IPY_TYPE IPY_TYPE, FLPT.MEANING, IPYB.POLICY_NUMBER QUOTE_NUMBER, IPYB2.POLICY_NUMBER POLICY_NUMBER, IPYB.QUOTE_YN QUOTE_YN, IPYT.ENDORSEMENT ENDORSEMENT, FIFC.MEANING INSURANCE_FACTOR, IPYB.FACTOR_CODE FACTOR_CODE, IPYB.COVERED_AMOUNT COVERED_AMOUNT, IPYB.FACTOR_VALUE FACTOR_VALUE, IPYB.DATE_QUOTED DATE_QUOTED, IPYB.DATE_PROOF_REQUIRED DATE_PROOF_REQUIRED, IPYB.DATE_QUOTE_EXPIRY DATE_QUOTE_EXPIRY, IPYB.DEDUCTIBLE DEDUCTIBLE, IPYB.PAYMENT_FREQUENCY PAYMENT_FREQUENCY, IPYB.DATE_PROOF_PROVIDED DATE_PROOF_PROVIDED, IPYB.DATE_FROM DATE_FROM, IPYB.NAME_OF_INSURED NAME_OF_INSURED, IPYB.DATE_TO DATE_TO, IPYT.DESCRIPTION DESCRIPTION, IPYB.ON_FILE_YN ON_FILE_YN, IPYB.PREMIUM PREMIUM, IPYT.COMMENTS COMMENTS, IPYB.ACTIVATION_DATE ACTIVATION_DATE, IPYB.PRIVATE_LABEL_YN PRIVATE_LABEL_YN, IPYB.LESSOR_INSURED_YN LESSOR_INSURED_YN, IPYB.LESSOR_PAYEE_YN LESSOR_PAYEE_YN, IPYB.CANCELLATION_DATE CANCELLATION_DATE, IPYT.CANCELLATION_COMMENT CANCELLATION_COMMENT, IPYB.AGENT_YN AGENT_YN, IPYB.ADJUSTMENT, IPYB.AGENCY_NUMBER AGENCY_NUMBER, IPYB.AGENCY_SITE_ID AGENCY_SITE_ID, IPYB.SALES_REP_ID SALES_REP_ID, IPYB.AGENT_SITE_ID AGENT_SITE_ID, IPYB.ADJUSTED_BY_ID ADJUSTED_BY_ID, IPYB.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY, IPYB.ATTRIBUTE1 ATTRIBUTE1, IPYB.ATTRIBUTE2 ATTRIBUTE2, IPYB.ATTRIBUTE3 ATTRIBUTE3, IPYB.ATTRIBUTE4 ATTRIBUTE4, IPYB.ATTRIBUTE5 ATTRIBUTE5, IPYB.ATTRIBUTE6 ATTRIBUTE6, IPYB.ATTRIBUTE7 ATTRIBUTE7, IPYB.ATTRIBUTE8 ATTRIBUTE8, IPYB.ATTRIBUTE9 ATTRIBUTE9, IPYB.ATTRIBUTE10 ATTRIBUTE10, IPYB.ATTRIBUTE11 ATTRIBUTE11, IPYB.ATTRIBUTE12 ATTRIBUTE12, IPYB.ATTRIBUTE13 ATTRIBUTE13, IPYB.ATTRIBUTE14 ATTRIBUTE14, IPYB.ATTRIBUTE15 ATTRIBUTE15, IPYB.ORG_ID ORG_ID, IPYB.REQUEST_ID REQUEST_ID, IPYB.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID, IPYB.PROGRAM_ID PROGRAM_ID, IPYB.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE, IPYB.CREATED_BY CREATED_BY, IPYB.CREATION_DATE CREATION_DATE, IPYB.LAST_UPDATED_BY LAST_UPDATED_BY, IPYB.LAST_UPDATE_DATE LAST_UPDATE_DATE, IPYB.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN ,SLRF.NAME ,IPYB.TERRITORY_CODE TERRITORY_CODE ,CONV.TERRITORY_SHORT_NAME COUNTRY ,FNDU.USER_NAME ADJUSTED_BY FROM FND_USER FNDU,OKL_INS_POLICIES_B IPYB,OKL_INS_POLICIES_ALL_B IPYB2,OKL_INS_POLICIES_TL IPYT,FND_LOOKUPS FLPF,FND_LOOKUPS FLPS,FND_LOOKUPS FLPT,FND_LOOKUPS FIFC,OKL_INS_PRODUCTS_V IPTT,OKX_INS_PROVIDER_V INPR,JTF_RS_SALESREPS SLRF,FND_TERRITORIES_TL CONV WHERE IPYB.ID = IPYT.ID AND IPYB.IPY_ID = IPYB2.ID (+) AND IPYT.LANGUAGE = USERENV('LANG') AND IPYB.IPF_CODE = FLPF.LOOKUP_CODE AND FLPF.LOOKUP_TYPE = 'OKL_INS_PAYMENT_FREQUENCY' AND IPYB.ISS_CODE = FLPS.LOOKUP_CODE AND FLPS.LOOKUP_TYPE = 'OKL_INSURANCE_STATUS' AND IPYB.IPY_TYPE = FLPT.LOOKUP_CODE AND FLPT.LOOKUP_TYPE = 'OKL_INSURANCE_TYPE' AND IPTT.ID = IPYB.IPT_ID AND INPR.PARTY_ID = IPYB.ISU_ID AND CONV.LANGUAGE = USERENV('LANG') AND CONV.TERRITORY_CODE = IPYB.TERRITORY_CODE AND IPYB.SALES_REP_ID = SLRF.SALESREP_ID(+) AND IPYB.ISS_CODE = 'QUOTE' AND IPYB.FACTOR_CODE = FIFC.LOOKUP_CODE AND FIFC.LOOKUP_TYPE = 'OKL_INSURANCE_FACTOR' AND FNDU.USER_ID (+) = IPYB.ADJUSTED_BY_ID

View Text - HTML Formatted

SELECT IPYB.ROWID ROW_ID
, IPYB.ID ID
, IPYB.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER
, IPYT.SFWT_FLAG SFWT_FLAG
, IPYB.IPF_CODE IPF_CODE
, DECODE(NVL(IPYB.IPY_ID
, NULL)
, NULL
, DECODE(NVL(IPYB.DATE_QUOTE_EXPIRY
, NULL)
, NULL
, 'ACTIVE'
, DECODE(SIGN(MONTHS_BETWEEN(IPYB.DATE_QUOTE_EXPIRY
, SYSDATE))
, 1
, 'ACTIVE'
, DECODE(SIGN(MONTHS_BETWEEN(IPYB.DATE_QUOTE_EXPIRY
, SYSDATE))
, -1
, 'EXPIRED'
, 'CONVERTED')))
, 'CONVERTED') STATUS
, IPYB.KHR_ID KHR_ID
, IPYB.CALCULATED_PREMIUM CALCULATED_PREMIUM
, FLPF.MEANING
, IPYB.INT_ID INT_ID
, IPYB.ISU_ID ISU_ID
, INPR.PARTY_NAME
, IPYB.IPT_ID IPT_ID
, IPTT.NAME
, IPYB.IPY_ID IPY_ID
, IPYB.CRX_CODE CRX_CODE
, IPYB.ISS_CODE ISS_CODE
, FLPS.MEANING
, IPYB.KLE_ID KLE_ID
, IPYB.IPY_TYPE IPY_TYPE
, FLPT.MEANING
, IPYB.POLICY_NUMBER QUOTE_NUMBER
, IPYB2.POLICY_NUMBER POLICY_NUMBER
, IPYB.QUOTE_YN QUOTE_YN
, IPYT.ENDORSEMENT ENDORSEMENT
, FIFC.MEANING INSURANCE_FACTOR
, IPYB.FACTOR_CODE FACTOR_CODE
, IPYB.COVERED_AMOUNT COVERED_AMOUNT
, IPYB.FACTOR_VALUE FACTOR_VALUE
, IPYB.DATE_QUOTED DATE_QUOTED
, IPYB.DATE_PROOF_REQUIRED DATE_PROOF_REQUIRED
, IPYB.DATE_QUOTE_EXPIRY DATE_QUOTE_EXPIRY
, IPYB.DEDUCTIBLE DEDUCTIBLE
, IPYB.PAYMENT_FREQUENCY PAYMENT_FREQUENCY
, IPYB.DATE_PROOF_PROVIDED DATE_PROOF_PROVIDED
, IPYB.DATE_FROM DATE_FROM
, IPYB.NAME_OF_INSURED NAME_OF_INSURED
, IPYB.DATE_TO DATE_TO
, IPYT.DESCRIPTION DESCRIPTION
, IPYB.ON_FILE_YN ON_FILE_YN
, IPYB.PREMIUM PREMIUM
, IPYT.COMMENTS COMMENTS
, IPYB.ACTIVATION_DATE ACTIVATION_DATE
, IPYB.PRIVATE_LABEL_YN PRIVATE_LABEL_YN
, IPYB.LESSOR_INSURED_YN LESSOR_INSURED_YN
, IPYB.LESSOR_PAYEE_YN LESSOR_PAYEE_YN
, IPYB.CANCELLATION_DATE CANCELLATION_DATE
, IPYT.CANCELLATION_COMMENT CANCELLATION_COMMENT
, IPYB.AGENT_YN AGENT_YN
, IPYB.ADJUSTMENT
, IPYB.AGENCY_NUMBER AGENCY_NUMBER
, IPYB.AGENCY_SITE_ID AGENCY_SITE_ID
, IPYB.SALES_REP_ID SALES_REP_ID
, IPYB.AGENT_SITE_ID AGENT_SITE_ID
, IPYB.ADJUSTED_BY_ID ADJUSTED_BY_ID
, IPYB.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, IPYB.ATTRIBUTE1 ATTRIBUTE1
, IPYB.ATTRIBUTE2 ATTRIBUTE2
, IPYB.ATTRIBUTE3 ATTRIBUTE3
, IPYB.ATTRIBUTE4 ATTRIBUTE4
, IPYB.ATTRIBUTE5 ATTRIBUTE5
, IPYB.ATTRIBUTE6 ATTRIBUTE6
, IPYB.ATTRIBUTE7 ATTRIBUTE7
, IPYB.ATTRIBUTE8 ATTRIBUTE8
, IPYB.ATTRIBUTE9 ATTRIBUTE9
, IPYB.ATTRIBUTE10 ATTRIBUTE10
, IPYB.ATTRIBUTE11 ATTRIBUTE11
, IPYB.ATTRIBUTE12 ATTRIBUTE12
, IPYB.ATTRIBUTE13 ATTRIBUTE13
, IPYB.ATTRIBUTE14 ATTRIBUTE14
, IPYB.ATTRIBUTE15 ATTRIBUTE15
, IPYB.ORG_ID ORG_ID
, IPYB.REQUEST_ID REQUEST_ID
, IPYB.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID
, IPYB.PROGRAM_ID PROGRAM_ID
, IPYB.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE
, IPYB.CREATED_BY CREATED_BY
, IPYB.CREATION_DATE CREATION_DATE
, IPYB.LAST_UPDATED_BY LAST_UPDATED_BY
, IPYB.LAST_UPDATE_DATE LAST_UPDATE_DATE
, IPYB.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, SLRF.NAME
, IPYB.TERRITORY_CODE TERRITORY_CODE
, CONV.TERRITORY_SHORT_NAME COUNTRY
, FNDU.USER_NAME ADJUSTED_BY
FROM FND_USER FNDU
, OKL_INS_POLICIES_B IPYB
, OKL_INS_POLICIES_ALL_B IPYB2
, OKL_INS_POLICIES_TL IPYT
, FND_LOOKUPS FLPF
, FND_LOOKUPS FLPS
, FND_LOOKUPS FLPT
, FND_LOOKUPS FIFC
, OKL_INS_PRODUCTS_V IPTT
, OKX_INS_PROVIDER_V INPR
, JTF_RS_SALESREPS SLRF
, FND_TERRITORIES_TL CONV
WHERE IPYB.ID = IPYT.ID
AND IPYB.IPY_ID = IPYB2.ID (+)
AND IPYT.LANGUAGE = USERENV('LANG')
AND IPYB.IPF_CODE = FLPF.LOOKUP_CODE
AND FLPF.LOOKUP_TYPE = 'OKL_INS_PAYMENT_FREQUENCY'
AND IPYB.ISS_CODE = FLPS.LOOKUP_CODE
AND FLPS.LOOKUP_TYPE = 'OKL_INSURANCE_STATUS'
AND IPYB.IPY_TYPE = FLPT.LOOKUP_CODE
AND FLPT.LOOKUP_TYPE = 'OKL_INSURANCE_TYPE'
AND IPTT.ID = IPYB.IPT_ID
AND INPR.PARTY_ID = IPYB.ISU_ID
AND CONV.LANGUAGE = USERENV('LANG')
AND CONV.TERRITORY_CODE = IPYB.TERRITORY_CODE
AND IPYB.SALES_REP_ID = SLRF.SALESREP_ID(+)
AND IPYB.ISS_CODE = 'QUOTE'
AND IPYB.FACTOR_CODE = FIFC.LOOKUP_CODE
AND FIFC.LOOKUP_TYPE = 'OKL_INSURANCE_FACTOR'
AND FNDU.USER_ID (+) = IPYB.ADJUSTED_BY_ID