QUERY TO GET LINE LEVEL DETAILS OF SERVICE CONTRACT FORM

SELECT cleb.ID "OLD_CP_LINE_ID",
      OKLB.LINE_NUMBER "LS_LINE_REFERENCE_NUMBER",
      OKHB.CONTRACT_NUMBER "LS_CONTRACT_NUMBER",
      OKHB.CONTRACT_NUMBER_MODIFIER "CONTRACT_NUMBER_MODIFIER",
      CSI.SERIAL_NUMBER "LS_SERIAL_NUMBER",
      CLEB.STS_CODE "LS_STS_CODE",
      MSIB1.SEGMENT1 "LS_ITEM_NUMBER",
      LSET.name "LS_LEVEL" ,
      CLEB.PRICE_NEGOTIATED "LS_PRICE_NEGOTIATED",
      CLEB.START_DATE "LS_START_DATE" ,
      CLEB.END_DATE "LS_END_DATE" ,
      RR1.NAME "ACCT_RULE_CODE",
      OKLT.INVOICE_TEXT "LS_INVOICE_TEXT",
      OKI.NUMBER_OF_ITEMS "LS_QTY",
      OKI.UOM_CODE "LS_UOM",--MSIB.PRIMARY_UOM_CODE "LS_UOM",
      CLEB.PRICE_UNIT "LS_UNIT_PRICE",
      CLEB.PRICE_NEGOTIATED "LS_EXTENDED_PRICE",
      NVL(KLN.TAX_AMOUNT,0.00) "LS_TAX" ,
      CLEB.PRICE_NEGOTIATED "LS_TOTAL",
      OKLB.LINE_RENEWAL_TYPE_CODE "LS_RENEWAL_TYPE_CODE",
      MSIB1.segment1 "LS_SERVICE_PROGRAM_NAME",
      CSI.INSTANCE_NUMBER "LS_INSTANCE_NUMBER" ,
      OKLB.BILL_TO_SITE_USE_ID "LS_BILL_TO_SITE_USE_ID",
      OKLB.SHIP_TO_SITE_USE_ID "LS_SHIP_TO_SITE_USE_ID",
      OKLB.CURRENCY_CODE "LS_CURRENCY_CODE",
      'HOLXIL_UK2_OU' "LS_OPERATING_UNIT",
      'SL' "TABLE_TYPE",
      DECODE(KLN.tax_classification_code,'UKSTD','GB STANDARD','UKEXEMPT', 'GB EXEMPT','UK0','GB ZERO') "LS_TAX_CLASSIFICATION_CODE"
    FROM OKC_K_HEADERS_B OKHB,
      OKS_K_HEADERS_B KHR,
      OKC_K_LINES_B OKLB ,
      OKC_K_LINES_B CLEB,
      OKC_LINE_STYLES_B LSEB,
      OKC_LINE_STYLES_TL LSET ,
      RA_RULES RR1 ,
      OKC_K_LINES_TL OKLTL ,
      OKS_K_LINES_B KLN ,
      OKS_K_LINES_TL OKLT ,
      OKC_K_ITEMS OKI,
      OKC_K_ITEMS OKI1,
      CSI_ITEM_INSTANCES CSI,
      MTL_SYSTEM_ITEMS_B MSIB,
      MTL_SYSTEM_ITEMS_B MSIB1,
      HR_OPERATING_UNITS HOU,
      HZ_CUST_SITE_USES_ALL HCSUA,
      HZ_CUST_SITE_USES_ALL HCSUAS
    WHERE 1     = 1
    AND OKHB.ID = OKLB.CHR_ID
    AND OKHB.ID = KHR.CHR_ID
    AND OKLB.id = CLEB.CLE_ID
      --OR OKHB.ID                      =CLEB.DNZ_CHR_ID
    AND oki1.cle_id                  = oklb.id
    AND MSIB1.INVENTORY_ITEM_ID      = TO_NUMBER(OKI1.OBJECT1_ID1)
    AND msib1.organization_id        = TO_NUMBER(OKI1.OBJECT1_ID2)
    AND LSEB.id                      = CLEB.LSE_ID
    AND RR1.RULE_ID                  = KHR.ACCT_RULE_ID
    AND OKLB.ID                      = KLN.CLE_ID
    AND KLN.ID                       = OKLT.ID
    AND OKLB.ID                      = OKLTL.ID
    AND OKLTL.LANGUAGE               = USERENV('LANG')
    AND OKLT.LANGUAGE                = OKLTL.LANGUAGE
    AND lset.language                = USERENV('LANG')
    AND KLN.ID                       = OKLT.ID
    AND LSEB.ID                      = LSET.ID
    AND OKHB.ID                      = CLEB.DNZ_CHR_ID
    AND OKHB.ID                      = OKI.DNZ_CHR_ID
    AND CLEB.ID                      = OKI.CLE_ID
    AND CSI.LAST_VLD_ORGANIZATION_ID = MSIB.ORGANIZATION_ID
    AND TO_NUMBER(OKI.OBJECT1_ID1)   = CSI.INSTANCE_ID
    AND CSI.INVENTORY_ITEM_ID        = MSIB.INVENTORY_ITEM_ID
    AND OKHB.STS_CODE               IN ('QA_HOLD', 'ENTERED', 'ACTIVE', 'HOLD', 'SIGNED')
    AND OKLB.STS_CODE               IN ('BOOKED', 'AGREEMENT', 'AMENDED', 'ENTERED', 'ACTIVE' , 'HOLD', 'SIGNED')
    AND CLEB.STS_CODE               IN ('BOOKED', 'AGREEMENT', 'AMENDED', 'ENTERED', 'ACTIVE' , 'HOLD', 'SIGNED')
    AND TRUNC(sysdate) BETWEEN TRUNC(okhb.START_DATE) AND TRUNC(NVL( okhb.END_DATE, sysdate))
    AND TRUNC(sysdate) BETWEEN TRUNC(OKLB.START_DATE) AND TRUNC(NVL( OKLB.END_DATE, sysdate))
    AND TRUNC(sysdate) BETWEEN TRUNC(CLEB.START_DATE) AND TRUNC(NVL( CLEB.END_DATE, sysdate))
    AND okhb.DATE_TERMINATED    IS NULL
    AND okhb.DATETIME_CANCELLED IS NULL
      --AND OKLB.DATE_TERMINATED    IS NULL
      --and OKLB.DATE_CANCELLED     is null
    AND ( HCSUA.SITE_USE_ID  = OKLB.BILL_TO_SITE_USE_ID
    AND hcsua.site_use_code  ='BILL_TO' )
    AND ( HCSUAS.SITE_USE_ID = OKLB.SHIP_TO_SITE_USE_ID
    AND hcsuas.site_use_code ='SHIP_TO' )
    AND HCSUA.ORG_ID         = HOU.ORGANIZATION_ID
    AND HOU.ORGANIZATION_ID  = 'g_uk_org_id'--GIVE YOUR ORGANIZATION NAME TO GET THE DATA FOR THAT ORGANIZATION
    AND (OKHB.CONTRACT_NUMBER = 'p_contract_number' or p_contract_number is NULL) --GIVE YOUR CONTRACT NUMBER HERE
  UNION
  SELECT OKLB.ID "OLD_CP_LINE_ID",
    OKLB.LINE_NUMBER "LS_LINE_REFERENCE_NUMBER",
    OKHB.CONTRACT_NUMBER "LS_CONTRACT_NUMBER",
    OKHB.CONTRACT_NUMBER_MODIFIER "CONTRACT_NUMBER_MODIFIER",
    NULL "LS_SERIAL_NUMBER",
    oklb.STS_CODE "LS_STS_CODE",
    MSIB1.SEGMENT1 "LS_ITEM_NUMBER",
    LSET.name "LS_LEVEL" ,
    oklb.PRICE_NEGOTIATED "LS_PRICE_NEGOTIATED",
    oklb.START_DATE "LS_START_DATE" ,
    oklb.END_DATE "LS_END_DATE" ,
    RR1.NAME "ACCT_RULE_CODE",
    OKLT.INVOICE_TEXT "LS_INVOICE_TEXT",
    NULL "LS_QTY",
    NULL "LS_UOM",--MSIB.PRIMARY_UOM_CODE "LS_UOM",
    NULL "LS_UNIT_PRICE",
    oklb.PRICE_NEGOTIATED "LS_EXTENDED_PRICE",
    NVL(KLN.TAX_AMOUNT,0.00) "LS_TAX" ,
    oklb.PRICE_NEGOTIATED "LS_TOTAL",
    OKLB.LINE_RENEWAL_TYPE_CODE "LS_RENEWAL_TYPE_CODE",
    MSIB1.segment1 "LS_SERVICE_PROGRAM_NAME",
    --CSI.INSTANCE_NUMBER
    NULL "LS_INSTANCE_NUMBER" ,
    OKLB.BILL_TO_SITE_USE_ID "LS_BILL_TO_SITE_USE_ID",
    OKLB.SHIP_TO_SITE_USE_ID "LS_SHIP_TO_SITE_USE_ID",
    OKLB.CURRENCY_CODE "LS_CURRENCY_CODE",
    'HOLXIL_UK2_OU' "LS_OPERATING_UNIT",
    'L' "TABLE_TYPE",
    DECODE(KLN.tax_classification_code,'UKSTD','GB STANDARD','UKEXEMPT', 'GB EXEMPT','UK0','GB ZERO') "LS_TAX_CLASSIFICATION_CODE"
  FROM OKC_K_HEADERS_B OKHB,
    OKS_K_HEADERS_B KHR,
    OKC_K_LINES_B OKLB ,
    --OKC_K_LINES_B CLEB,
    OKC_LINE_STYLES_B LSEB,
    OKC_LINE_STYLES_TL LSET ,
    RA_RULES RR1 ,
    OKC_K_LINES_TL OKLTL ,
    OKS_K_LINES_B KLN ,
    OKS_K_LINES_TL OKLT ,
    --OKC_K_ITEMS OKI,
    OKC_K_ITEMS OKI1,
    --CSI_ITEM_INSTANCES CSI,
    --MTL_SYSTEM_ITEMS_B MSIB,
    MTL_SYSTEM_ITEMS_B MSIB1,
    HR_OPERATING_UNITS HOU,
    HZ_CUST_SITE_USES_ALL HCSUA,
    HZ_CUST_SITE_USES_ALL HCSUAS
  WHERE 1     = 1
  AND OKHB.ID = OKLB.CHR_ID
  AND OKHB.ID = KHR.CHR_ID
    --and OKLB.id                      = CLEB.CLE_ID
    --OR OKHB.ID                      =CLEB.DNZ_CHR_ID
  AND oklb.cle_id            IS NULL
  AND oki1.cle_id             = oklb.id
  AND MSIB1.INVENTORY_ITEM_ID = TO_NUMBER(OKI1.OBJECT1_ID1)
  AND msib1.organization_id   = TO_NUMBER(OKI1.OBJECT1_ID2)
  AND LSEB.id                 = oklb.LSE_ID
  AND RR1.RULE_ID             = KHR.ACCT_RULE_ID
  AND OKLB.ID                 = KLN.CLE_ID
  AND KLN.ID                  = OKLT.ID
  AND OKLB.ID                 = OKLTL.ID
  AND OKLTL.LANGUAGE          = USERENV('LANG')
  AND OKLT.LANGUAGE           = OKLTL.LANGUAGE
  AND lset.language           = USERENV('LANG')
  AND KLN.ID                  = OKLT.ID
  AND LSEB.ID                 = LSET.ID
    --AND OKHB.ID                      = CLEB.DNZ_CHR_ID
    --AND OKHB.ID                      = OKI.DNZ_CHR_ID
    --AND CLEB.ID                      = OKI.CLE_ID
    --AND CSI.LAST_VLD_ORGANIZATION_ID = MSIB.ORGANIZATION_ID
    --AND TO_NUMBER(OKI.OBJECT1_ID1)   = CSI.INSTANCE_ID
    --and CSI.INVENTORY_ITEM_ID        = MSIB.INVENTORY_ITEM_ID
  AND OKHB.STS_CODE IN ('QA_HOLD', 'ENTERED', 'ACTIVE', 'HOLD', 'SIGNED')
  AND OKLB.STS_CODE IN ('BOOKED', 'AGREEMENT', 'AMENDED', 'ENTERED', 'ACTIVE', 'HOLD', 'SIGNED')
    --and CLEB.STS_CODE in ('BOOKED', 'AGREEMENT', 'AMENDED', 'ENTERED', '
    -- ACTIVE', 'HOLD', 'SIGNED')
  AND TRUNC(sysdate) BETWEEN TRUNC(okhb.START_DATE) AND TRUNC(NVL(okhb.END_DATE , sysdate))
  AND TRUNC(sysdate) BETWEEN TRUNC(OKLB.START_DATE) AND TRUNC(NVL(OKLB.END_DATE , sysdate))
    --and TRUNC(sysdate) between TRUNC(CLEB.START_DATE) and TRUNC(NVL(
    -- CLEB.END_DATE, sysdate))
  AND okhb.DATE_TERMINATED    IS NULL
  AND okhb.DATETIME_CANCELLED IS NULL
  AND OKLB.DATE_TERMINATED    IS NULL
  AND OKLB.DATE_CANCELLED     IS NULL
  AND ( HCSUA.SITE_USE_ID      = OKLB.BILL_TO_SITE_USE_ID
  AND hcsua.site_use_code      ='BILL_TO' )
  AND ( HCSUAS.SITE_USE_ID     = OKLB.SHIP_TO_SITE_USE_ID
  AND hcsuas.site_use_code     ='SHIP_TO' )
  AND HCSUA.ORG_ID             = HOU.ORGANIZATION_ID
  AND HOU.ORGANIZATION_ID      = 'g_uk_org_id'-- GIVE YOUR ORGANIZATION NAME TO GET THE DATA FOR THAT ORGANIZATION
  AND (OKHB.CONTRACT_NUMBER = 'p_contract_number' or p_contract_number is NULL);--GIVE YOUR CONTRACT NUMBER HERE

QUERY TO GET THE HEADER LEVEL FEILDS OF SERVICE CONTRACTS FORM

SELECT chrb.id "CHRB_ID" ,
      chrb.contract_number "CONTRACT_NUMBER" ,
      CHRB.CONTRACT_NUMBER_MODIFIER "LS_MODIFIER_NUMBER" ,
      CHRB.STS_CODE "LS_STS_CODE" ,
      CHRB.START_DATE "START_DATE" ,
      CHRB.END_DATE "END_DATE" ,
      CHRT.SHORT_DESCRIPTION "SHORT_DESCRIPTION" ,
      CHRB.ESTIMATED_AMOUNT "ESTIMATED_AMOUNT" ,
      CHRT.COGNOMEN "KNOWN_AS" ,
      CHRB.AUTHORING_ORG_ID "AUTHORING_ORG_ID" ,
      CHRB.CURRENCY_CODE "CURRENCY_CODE" ,
      RR1.name "ACCT_RULE_CODE" ,
      CHRB.CUST_PO_NUMBER "CUST_PO_NUMBER" ,
      CHRB.CUST_PO_NUMBER_REQ_YN "CUST_PO_NUMBER_REQ_YN" ,
      CHRB.PAYMENT_INSTRUCTION_TYPE "PAYMENT_INSTRUCTION_TYPE" ,
      KHR.SERVICE_PO_NUMBER "PO_NUMBER" ,
      NVL(KHR.AR_INTERFACE_YN,'Y') "AR_INT_FLAG" ,
      CHRB.BILL_TO_SITE_USE_ID "BILL_TO_SITE_USE_ID" ,
      CHRB.SHIP_TO_SITE_USE_ID "SHIP_TO_SITE_USE_ID" ,
      'HOLX_UK2_OU' "LS_OPERATING_UNIT" ,
      (SELECT PRICE_LIST_ID
      FROM OE_PRICE_LISTS
      WHERE UPPER(name) = 'UK2 SUBSIDIARY PRICE LIST (GBP)'--GIVE YOUR PRICE LIST NAME HERE
      ) "PRICE_LIST_ID" ,
    CHRB.INV_RULE_ID "INVOICING_RULE_ID" ,
    CHRB.PAYMENT_TERM_ID "PAYMENT_TERM_ID" ,
    OKP.PDF_ID "APPROVAL_WORKFLOW_ID" ,
    CHRB.QCL_ID "QCL_ID" ,
    CHRB.RENEWAL_TYPE_CODE "RENEWAL_TYPE_CODE" ,
    (SELECT NAME
    FROM RA_CUST_TRX_TYPES_ALL RCTA
    WHERE cust_trx_type_id=KHR.INV_TRX_TYPE
    AND rcta.org_id       ='g_uk_org_id'--GIVE YOUR ORGANIZATION NAME HERE
    ) "TRANSACTION_TYPE_CODE" ,
    KHR.INV_TRX_TYPE "TRANSACTION_TYPE_ID" ,
    (SELECT OKGT.ID
    FROM OKC_K_GRPINGS OKG ,
      OKC_K_GROUPS_TL OKGT
    WHERE OKG.INCLUDED_CHR_ID = CHRB.ID
    AND OKGT.ID               =OKG.CGP_PARENT_ID
    AND OKGT.LANGUAGE         =USERENV('LANG')
    ) "GRP_ID" ,
    chrb.scs_code "SCS_CODE" ,
    okprb.object1_id1 "HDR_PARTY_ID" ,
    (SELECT xsv.SALESREP_ID
    FROM XXHOL_SALESREPS_V xsv
    WHERE upper(ltrim(rtrim(xsv.salesrep_name))) IN
      (SELECT upper(ltrim(rtrim(xsalesrepv.salesrep_name)))
      FROM XXHOL_SALESREPS_V xsalesrepv,
        OKS_K_SALES_CREDITS B
      WHERE B.CHR_ID = CHRB.ID
      AND B.CTC_ID   = xsalesrepv.SALESREP_ID
--      AND B.CLE_ID IS NULL
      )
    AND org_id  = g_uk2_org_id
    AND rownum <= 1
    ) "SALESREP_ID"
    --;select distinct OKLB.*
  FROM okc_k_headers_all_b chrb,
    okc_k_party_roles_b okprb,
    -- OKC_K_LINES_B OKLB ,
    OKS_K_HEADERS_B KHR,
    OKC_K_HEADERS_TL CHRT ,
    OKC_K_PROCESSES OKP ,
    RA_RULES RR1 ,
    HR_OPERATING_UNITS HOU,
    HZ_CUST_SITE_USES_ALL HCSUA,
    HZ_CUST_SITE_USES_ALL HCSUAS
  WHERE 1 =1
    --AND chrb.ID             = OKLB.CHR_ID
  AND CHRB.ID         = CHRT.ID
  AND CHRB.ID         = KHR.CHR_ID
  AND OKP.CHR_ID      =CHRB.id
  AND RR1.RULE_ID     =KHR.ACCT_RULE_ID
  AND okprb.dnz_chr_id=chrb.id
  AND OKPRB.rle_code  ='CUSTOMER'
  AND CHRB.STS_CODE  IN ('QA_HOLD', 'ENTERED', 'ACTIVE', 'HOLD', 'SIGNED')
  AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(CHRB.START_DATE,SYSDATE)) AND TRUNC(NVL( CHRB.END_DATE, SYSDATE))
  AND TRUNC(NVL(CHRB.DATE_TERMINATED, SYSDATE))    >= TRUNC(SYSDATE)
  AND TRUNC(NVL(CHRB.DATETIME_CANCELLED, SYSDATE)) >= TRUNC(SYSDATE)
  AND CHRT.LANGUAGE                                 =USERENV('LANG')
  AND ( HCSUA.SITE_USE_ID                           = CHRB.BILL_TO_SITE_USE_ID
  AND hcsua.site_use_code                           ='BILL_TO' )
  AND ( HCSUAS.SITE_USE_ID                          = CHRB.SHIP_TO_SITE_USE_ID
  AND hcsuas.site_use_code                          ='SHIP_TO' )
  AND HCSUA.ORG_ID                                  = HOU.ORGANIZATION_ID
--  AND HOU.ORGANIZATION_ID                           = 'g_org_id'--GIVE YOUR ORGANIZAITON NAME HERE
  AND chrb.org_id          =hou.organization_id
  AND (chrb.CONTRACT_NUMBER = 'p_contract_number');--GIVE YOUR SERVICE CONTRACT NUMBER IN PLACE OF p_contract_number