OKS Service Contracts Bookings

Description
Categories: BI Publisher, Service
Application: Service Contracts
Source: Service Contracts Bookings Report (XML)
Short Name: OKSCOBKS_XML
DB package: OKS_OKSCOBKS_XMLP_PKG
SELECT
                 okc_hdr.currency_code
                 ,salesrep.name
                 ,hzp.party_name
                 ,to_char(okc_hdr.date_approved,'DD-MON-YY') date_approved
                 ,okc_hdr.contract_number
                 ,okc_hdr.contract_number_modifier
                 ,oks_extwar_util_pvt.round_currency_amt(okc_hdr.estimated_amount,okc_hdr.currency_code) "estimated_amount"
                 ,okc_hdr.start_date
                 ,okc_hdr.id
            FROM
                  okc_k_headers_b         okc_hdr
                 ,okc_k_party_roles_b   party
                 ,okc_k_party_roles_b   party1
                 ,okc_contacts               contact
                 ,jtf_rs_salesreps          salesrep
                 ,hz_parties                    hzp
                 ,okx_organization_defs_v org
                 &p_contract_group_from
            WHERE
                      okc_hdr.sts_code             IN ('ACTIVE','APPROVED','SIGNED')
            AND   okc_hdr.scs_code             IN ('SERVICE','WARRANTY')
            AND   okc_hdr.id                         =   party.dnz_chr_id
            AND   party.cle_id is NULL
            AND   party.rle_code                  =   'CUSTOMER'
            AND   okc_hdr.id                         =   party1.dnz_chr_id
            AND   party1.cle_id IS NULL
            AND   contact.cpl_id                     =   party1.id
            AND  contact.CRO_CODE  IN ('SUP_SALES','SALESPERSON')
            AND  contact.ID                         = OKS_EXTWAR_UTIL_PVT.active_salesrep(okc_hdr.ID,party1.ID, okc_hdr.authoring_org_id)
            AND   party1.rle_code                =   'VENDOR'
            AND   hzp.party_id                     =  TO_NUMBER(party.object1_id1)
            AND   salesrep.salesrep_id         =  TO_NUMBER(contact.object1_id1)
            AND   salesrep.org_id                 =  okc_hdr.authoring_org_id
            AND   okc_hdr.authoring_org_id  =   org.organization_id
            AND   org.organization_type        =   'OPERATING_UNIT'
            AND   org.information_type          =   'Operating Unit Information'
            &p_approved_date_where
            &p_start_date_where
            &p_org_where
            &p_salesrep_where
            &p_value_where
            &p_contract_group_where
            &p_order_by_order
Parameter Name SQL text Validation
Sort By
 
LOV Oracle
Value Exceeding
 
Number
Approved Date To
 
Date
Approved Date From
 
Date
Start Date To
 
Date
Start Date From
 
Date
Sales Rep
 
LOV Oracle
Operating Unit
 
LOV Oracle
Contract Group
 
LOV Oracle