[Home] [Help]
SELECT cpl.cle_id contract_line_id ,cpl.chr_id contract_id ,cpl.parent_cle_id parent_line_id ,cpl.scs_code ,cpl.contract_number contract_number ,cpl.contract_number_modifier contract_number_modifier ,cpl.complete_contract_number complete_contract_number ,cpl.line_number line_number ,cpl.start_date start_date ,cpl.end_date end_date ,cpl.duration ,cpl.period ,cpl.sts_code sts_code ,sts.meaning status_meaning ,cpl.ste_code ,ste.meaning ,cpl.trn_code trn_code ,trn.meaning termination_meaning ,cpl.date_terminated date_terminated ,cpl.date_renewed date_renewed ,cpl.number_of_items number_of_items ,cpl.uom_code uom_code ,cpl.unit_price ,cpl.unit_price_percent ,cpl.price_negotiated price_negotiated ,cpl.currency_code currency_code ,cpl.price_negotiated_renewed price_negotiated_renewed ,cpl.currency_code_renewed currency_code_renewed ,cpl.sob_price_unit ,cpl.sob_price_negotiated ,cpl.sob_price_negotiated_renewed ,cpl.sob_currency_code ,cpl.base_price_unit ,cpl.base_price_negotiated ,cpl.base_price_negotiated_renewed ,cpl.base_currency_code ,cpl.service_item ,cpl.covered_product_id ,null ,cpl.concatenated_segments ,cpl.serial_number ,cpl.reference_number ,cpl.cov_prod_quantity ,cpl.cov_prod_uom ,cpl.installation_date ,cpl.cov_prod_order_date ,cpl.cov_prod_order_number ,cpl.cov_prod_order_line ,cpl.cov_prod_net_amount ,cpl.cov_prod_order_line_id ,cpl.system_id ,cst.name ,cpl.product_agreement_id ,cpl.cov_prod_bill_to_site_id ,cpl.cov_prod_ship_to_site_id ,cpl.cov_prod_install_site_id ,cpl.cov_prod_bill_to_contact_id ,cpl.cov_prod_ship_to_contact_id ,cpl.creation_date ,cpl.last_update_date ,cpl.attribute_category attribute_category ,cpl.attribute1 attribute1 ,cpl.attribute2 attribute2 ,cpl.attribute3 attribute3 ,cpl.attribute4 attribute4 ,cpl.attribute5 attribute5 ,cpl.attribute6 attribute6 ,cpl.attribute7 attribute7 ,cpl.attribute8 attribute8 ,cpl.attribute9 attribute9 ,cpl.attribute10 attribute10 ,cpl.attribute11 attribute11 ,cpl.attribute12 attribute12 ,cpl.attribute13 attribute13 ,cpl.attribute14 attribute14 ,cpl.attribute15 attribute15 ,cpl.pricing_attribute1 pricing_attribute1 ,cpl.pricing_attribute2 pricing_attribute2 ,cpl.pricing_attribute3 pricing_attribute3 ,cpl.pricing_attribute4 pricing_attribute4 ,cpl.pricing_attribute5 pricing_attribute5 ,cpl.pricing_attribute6 pricing_attribute6 ,cpl.pricing_attribute7 pricing_attribute7 ,cpl.pricing_attribute8 pricing_attribute8 ,cpl.pricing_attribute9 pricing_attribute9 ,cpl.pricing_attribute10 pricing_attribute10 ,cpl.customer_product_item_id ,cpl.inventory_organization_id FROM OKI_COV_PRD_LINES CPL,CSI_SYSTEMS_TL CST,FND_LOOKUPS TRN,FND_LOOKUPS STE,OKC_STATUSES_V STS WHERE sts.code = cpl.sts_code and ste.lookup_code = cpl.ste_code and ste.lookup_type = 'OKC_STATUS_TYPE' and trn.lookup_code(+) = cpl.trn_code and trn.lookup_type(+) = 'OKC_TERMINATION_REASON' and cst.system_id(+) = cpl.system_id and cst.language(+) = userenv('LANG') and okc_util.get_k_access_level(cpl.chr_id, cpl.scs_code) in ('R','U')
SELECT CPL.CLE_ID CONTRACT_LINE_ID
, CPL.CHR_ID CONTRACT_ID
, CPL.PARENT_CLE_ID PARENT_LINE_ID
, CPL.SCS_CODE
, CPL.CONTRACT_NUMBER CONTRACT_NUMBER
, CPL.CONTRACT_NUMBER_MODIFIER CONTRACT_NUMBER_MODIFIER
, CPL.COMPLETE_CONTRACT_NUMBER COMPLETE_CONTRACT_NUMBER
, CPL.LINE_NUMBER LINE_NUMBER
, CPL.START_DATE START_DATE
, CPL.END_DATE END_DATE
, CPL.DURATION
, CPL.PERIOD
, CPL.STS_CODE STS_CODE
, STS.MEANING STATUS_MEANING
, CPL.STE_CODE
, STE.MEANING
, CPL.TRN_CODE TRN_CODE
, TRN.MEANING TERMINATION_MEANING
, CPL.DATE_TERMINATED DATE_TERMINATED
, CPL.DATE_RENEWED DATE_RENEWED
, CPL.NUMBER_OF_ITEMS NUMBER_OF_ITEMS
, CPL.UOM_CODE UOM_CODE
, CPL.UNIT_PRICE
, CPL.UNIT_PRICE_PERCENT
, CPL.PRICE_NEGOTIATED PRICE_NEGOTIATED
, CPL.CURRENCY_CODE CURRENCY_CODE
, CPL.PRICE_NEGOTIATED_RENEWED PRICE_NEGOTIATED_RENEWED
, CPL.CURRENCY_CODE_RENEWED CURRENCY_CODE_RENEWED
, CPL.SOB_PRICE_UNIT
, CPL.SOB_PRICE_NEGOTIATED
, CPL.SOB_PRICE_NEGOTIATED_RENEWED
, CPL.SOB_CURRENCY_CODE
, CPL.BASE_PRICE_UNIT
, CPL.BASE_PRICE_NEGOTIATED
, CPL.BASE_PRICE_NEGOTIATED_RENEWED
, CPL.BASE_CURRENCY_CODE
, CPL.SERVICE_ITEM
, CPL.COVERED_PRODUCT_ID
, NULL
, CPL.CONCATENATED_SEGMENTS
, CPL.SERIAL_NUMBER
, CPL.REFERENCE_NUMBER
, CPL.COV_PROD_QUANTITY
, CPL.COV_PROD_UOM
, CPL.INSTALLATION_DATE
, CPL.COV_PROD_ORDER_DATE
, CPL.COV_PROD_ORDER_NUMBER
, CPL.COV_PROD_ORDER_LINE
, CPL.COV_PROD_NET_AMOUNT
, CPL.COV_PROD_ORDER_LINE_ID
, CPL.SYSTEM_ID
, CST.NAME
, CPL.PRODUCT_AGREEMENT_ID
, CPL.COV_PROD_BILL_TO_SITE_ID
, CPL.COV_PROD_SHIP_TO_SITE_ID
, CPL.COV_PROD_INSTALL_SITE_ID
, CPL.COV_PROD_BILL_TO_CONTACT_ID
, CPL.COV_PROD_SHIP_TO_CONTACT_ID
, CPL.CREATION_DATE
, CPL.LAST_UPDATE_DATE
, CPL.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, CPL.ATTRIBUTE1 ATTRIBUTE1
, CPL.ATTRIBUTE2 ATTRIBUTE2
, CPL.ATTRIBUTE3 ATTRIBUTE3
, CPL.ATTRIBUTE4 ATTRIBUTE4
, CPL.ATTRIBUTE5 ATTRIBUTE5
, CPL.ATTRIBUTE6 ATTRIBUTE6
, CPL.ATTRIBUTE7 ATTRIBUTE7
, CPL.ATTRIBUTE8 ATTRIBUTE8
, CPL.ATTRIBUTE9 ATTRIBUTE9
, CPL.ATTRIBUTE10 ATTRIBUTE10
, CPL.ATTRIBUTE11 ATTRIBUTE11
, CPL.ATTRIBUTE12 ATTRIBUTE12
, CPL.ATTRIBUTE13 ATTRIBUTE13
, CPL.ATTRIBUTE14 ATTRIBUTE14
, CPL.ATTRIBUTE15 ATTRIBUTE15
, CPL.PRICING_ATTRIBUTE1 PRICING_ATTRIBUTE1
, CPL.PRICING_ATTRIBUTE2 PRICING_ATTRIBUTE2
, CPL.PRICING_ATTRIBUTE3 PRICING_ATTRIBUTE3
, CPL.PRICING_ATTRIBUTE4 PRICING_ATTRIBUTE4
, CPL.PRICING_ATTRIBUTE5 PRICING_ATTRIBUTE5
, CPL.PRICING_ATTRIBUTE6 PRICING_ATTRIBUTE6
, CPL.PRICING_ATTRIBUTE7 PRICING_ATTRIBUTE7
, CPL.PRICING_ATTRIBUTE8 PRICING_ATTRIBUTE8
, CPL.PRICING_ATTRIBUTE9 PRICING_ATTRIBUTE9
, CPL.PRICING_ATTRIBUTE10 PRICING_ATTRIBUTE10
, CPL.CUSTOMER_PRODUCT_ITEM_ID
, CPL.INVENTORY_ORGANIZATION_ID
FROM OKI_COV_PRD_LINES CPL
, CSI_SYSTEMS_TL CST
, FND_LOOKUPS TRN
, FND_LOOKUPS STE
, OKC_STATUSES_V STS
WHERE STS.CODE = CPL.STS_CODE
AND STE.LOOKUP_CODE = CPL.STE_CODE
AND STE.LOOKUP_TYPE = 'OKC_STATUS_TYPE'
AND TRN.LOOKUP_CODE(+) = CPL.TRN_CODE
AND TRN.LOOKUP_TYPE(+) = 'OKC_TERMINATION_REASON'
AND CST.SYSTEM_ID(+) = CPL.SYSTEM_ID
AND CST.LANGUAGE(+) = USERENV('LANG')
AND OKC_UTIL.GET_K_ACCESS_LEVEL(CPL.CHR_ID
, CPL.SCS_CODE) IN ('R'
, 'U')
|
|
|
|