VIEW: APPS.OKC_K_SALES_AGRMNTS_V

Source

View Text - Preformatted

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'

View Text - HTML Formatted

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'