View: OKC_K_SALES_AGRMNTS_V

Product:	OKC - Contracts Core
Description:	View for the contracts of category Sales Agreement
Implementation/DBA Data:	APPS.OKC_K_SALES_AGRMNTS_V

View Text

SELECT /*+ORDERED*/ K.ROW_ID
, K.ID
, K.OBJECT_VERSION_NUMBER
, K.SFWT_FLAG
, K.CHR_ID_RESPONSE
, K.CHR_ID_AWARD
, K.CHR_ID_RENEWED
, K.INV_ORGANIZATION_ID
, K.STS_CODE
, K.QCL_ID
, K.SCS_CODE
, K.CONTRACT_NUMBER
, K.CURRENCY_CODE
, K.CONTRACT_NUMBER_MODIFIER
, K.ARCHIVED_YN
, K.DELETED_YN
, K.CUST_PO_NUMBER_REQ_YN
, K.PRE_PAY_REQ_YN
, K.CUST_PO_NUMBER
, K.SHORT_DESCRIPTION
, K.COMMENTS
, K.DESCRIPTION
, K.DPAS_RATING
, K.COGNOMEN
, K.TEMPLATE_YN
, K.TEMPLATE_USED
, K.DATE_APPROVED
, K.DATETIME_CANCELLED
, K.AUTO_RENEW_DAYS
, K.DATE_ISSUED
, K.DATETIME_RESPONDED
, K.NON_RESPONSE_REASON
, K.NON_RESPONSE_EXPLAIN
, K.RFP_TYPE
, K.CHR_TYPE
, K.KEEP_ON_MAIL_LIST
, K.SET_ASIDE_REASON
, K.SET_ASIDE_PERCENT
, K.RESPONSE_COPIES_REQ
, K.DATE_CLOSE_PROJECTED
, K.DATETIME_PROPOSED
, K.DATE_SIGNED
, K.DATE_TERMINATED
, K.DATE_RENEWED
, K.TRN_CODE
, K.START_DATE
, K.END_DATE
, K.AUTHORING_ORG_ID
, K.BUY_OR_SELL
, K.ISSUE_OR_RECEIVE
, K.ESTIMATED_AMOUNT
, K.USER_ACCESS_LEVEL
, K.ATTRIBUTE_CATEGORY
, K.ATTRIBUTE1
, K.ATTRIBUTE2
, K.ATTRIBUTE3
, K.ATTRIBUTE4
, K.ATTRIBUTE5
, K.ATTRIBUTE6
, K.ATTRIBUTE7
, K.ATTRIBUTE8
, K.ATTRIBUTE9
, K.ATTRIBUTE10
, K.ATTRIBUTE11
, K.ATTRIBUTE12
, K.ATTRIBUTE13
, K.ATTRIBUTE14
, K.ATTRIBUTE15
, K.CREATED_BY
, K.CREATION_DATE
, K.LAST_UPDATED_BY
, K.LAST_UPDATE_DATE
, K.LAST_UPDATE_LOGIN
, STS.STE_CODE STATUS_TYPE
, R_BTO.JTOT_OBJECT1_CODE BILL_TO_OBJ_CODE
, R_BTO.OBJECT1_ID1 BILL_TO_ID1
, R_BTO.OBJECT1_ID2 BILL_TO_ID2
, R_CAN.JTOT_OBJECT1_CODE CUSTOMER_ACCOUNT_OBJ_CODE
, R_CAN.OBJECT1_ID1 CUSTOMER_ACCOUNT_ID1
, R_CAN.OBJECT1_ID2 CUSTOMER_ACCOUNT_ID2
, R_PTR.JTOT_OBJECT1_CODE PAYMENT_TERM_OBJ_CODE
, R_PTR.OBJECT1_ID1 PAYMENT_TERM_ID1
, R_PTR.OBJECT1_ID2 PAYMENT_TERM_ID2
, R_PRE.JTOT_OBJECT1_CODE PRICE_LIST_OBJ_CODE
, R_PRE.OBJECT1_ID1 PRICE_LIST_ID1
, R_PRE.OBJECT1_ID2 PRICE_LIST_ID2
, R_ARL.JTOT_OBJECT1_CODE ACCOUNTING_RULE_OBJ_CODE
, R_ARL.OBJECT1_ID1 ACCOUNTING_RULE_ID1
, R_ARL.OBJECT1_ID2 ACCOUNTING_RULE_ID2
, R_IRE.JTOT_OBJECT1_CODE INVOICE_RULE_OBJ_CODE
, R_IRE.OBJECT1_ID1 INVOICE_RULE_ID1
, R_IRE.OBJECT1_ID2 INVOICE_RULE_ID2
, 3 SHIP_METHOD_APPL_ID
, 'SHIP_METHOD' SHIP_METHOD_TYPE
, R_SMD.RULE_INFORMATION1 SHIP_METHOD_CODE
, 666 FREIGHT_TERM_APPL_ID
, 'FREIGHT_TERMS' FREIGHT_TERM_TYPE
, R_FRT.RULE_INFORMATION1 FREIGHT_TERM_CODE
, C.JTOT_OBJECT1_CODE CUSTOMER_OBJ_CODE
, C.OBJECT1_ID1 CUSTOMER_ID1
, C.OBJECT1_ID2 CUSTOMER_ID2
, V.JTOT_OBJECT1_CODE VENDOR_OBJ_CODE
, V.OBJECT1_ID1 VENDOR_ID1
, V.OBJECT1_ID2 VENDOR_ID2
, BUYER.JTOT_OBJECT1_CODE BUYER_OBJ_CODE
, BUYER.OBJECT1_ID1 BUYER_ID1
, BUYER.OBJECT1_ID2 BUYER_ID2
, PAYER.JTOT_OBJECT1_CODE PAYER_OBJ_CODE
, PAYER.OBJECT1_ID1 PAYER_ID1
, PAYER.OBJECT1_ID2 PAYER_ID2
, ADMIN.JTOT_OBJECT1_CODE ADMIN_OBJ_CODE
, ADMIN.OBJECT1_ID1 ADMIN_ID1
, ADMIN.OBJECT1_ID2 ADMIN_ID2
, SALESPERSON.JTOT_OBJECT1_CODE SALESPERSON_OBJ_CODE
, SALESPERSON.OBJECT1_ID1 SALESPERSON_ID1
, SALESPERSON.OBJECT1_ID2 SALESPERSON_ID2
FROM OKC_K_HEADERS_V K
, OKC_STATUSES_V STS
, OKC_RULE_GROUPS_V RG
, OKC_RULES_V R_BTO
, OKC_RULES_V R_CAN
, OKC_RULES_V R_PTR
, OKC_RULES_V R_PRE
, OKC_RULES_V R_ARL
, OKC_RULES_V R_IRE
, OKC_RULES_V R_SMD
, OKC_RULES_V R_FRT
, OKC_K_PARTY_ROLES_V C
, OKC_K_PARTY_ROLES_V V
, OKC_CONTACTS_V BUYER
, OKC_CONTACTS_V PAYER
, OKC_CONTACTS_V ADMIN
, OKC_CONTACTS_V SALESPERSON
WHERE /* ONLY SALES_AGREEMENT SUBCLASS */ K.SCS_CODE='SALES_AGREEMENT' /* GET THE STATUS TYPE */
AND STS.CODE = K.STS_CODE /* GET RULES INFORMATION */
AND K.ID = RG.CHR_ID
AND RG.RGD_CODE = 'SALES_AGREEMENT'
AND R_BTO.RGP_ID (+)= RG.ID
AND R_BTO.RULE_INFORMATION_CATEGORY (+)= 'BTO'
AND R_CAN.RGP_ID (+)= RG.ID
AND R_CAN.RULE_INFORMATION_CATEGORY (+)= 'CAN' /* PTR IS MANDATORY */
AND R_PTR.RGP_ID = RG.ID
AND R_PTR.RULE_INFORMATION_CATEGORY = 'PTR' /* PRE IS MANDATORY */
AND R_PRE.RGP_ID = RG.ID
AND R_PRE.RULE_INFORMATION_CATEGORY = 'PRE'
AND R_ARL.RGP_ID (+)= RG.ID
AND R_ARL.RULE_INFORMATION_CATEGORY (+)= 'ARL'
AND R_IRE.RGP_ID (+)= RG.ID
AND R_IRE.RULE_INFORMATION_CATEGORY (+)= 'IRE'
AND R_SMD.RGP_ID (+)= RG.ID
AND R_SMD.RULE_INFORMATION_CATEGORY (+)= 'SMD'
AND R_FRT.RGP_ID (+)= RG.ID
AND R_FRT.RULE_INFORMATION_CATEGORY (+)= 'FRT' /* CUSTOMER
AND VENDOR MANDATORY */
AND C.CHR_ID = K.ID
AND C.RLE_CODE = 'CUSTOMER'
AND V.CHR_ID = K.ID
AND V.RLE_CODE = 'VENDOR' /* CONTACTS ARE NOT MANDATORY */
AND BUYER.CPL_ID (+)= C.ID
AND BUYER.CRO_CODE = 'BUYER'
AND PAYER.CPL_ID (+)= C.ID
AND PAYER.CRO_CODE (+) = 'PAYER'
AND ADMIN.CPL_ID (+)= V.ID
AND ADMIN.CRO_CODE (+) = 'ADMIN'
AND SALESPERSON.CPL_ID (+)= V.ID
AND SALESPERSON.CRO_CODE (+) = 'SALESPERSON'

Columns