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