Script to update Contract Header, Contract Lines and Cascading Attributes

DECLARE
  l_api_version        CONSTANT NUMBER := 1.0;
  l_init_msg_list       CONSTANT    VARCHAR2(1) := 'T'; 
  l_return_status      VARCHAR2(1);
  l_msg_count          NUMBER;
  l_msg_data           VARCHAR2(2000);
  l_chrv_tbl_in        okc_contract_pub.chrv_tbl_type;
  l_chrv_tbl_out       okc_contract_pub.chrv_tbl_type;
  l_clev_tbl_in        okc_contract_pub.clev_tbl_type;
  l_clev_tbl_out       okc_contract_pub.clev_tbl_type;
  l_cle_tbl_in         okc_contract_pub.clev_tbl_type;
  l_cle_tbl_out        okc_contract_pub.clev_tbl_type;
  i                    NUMBER  := 1;
  
  l_count number :=0;
  l_id okc_k_headers_all_b.id%TYPE;
  l_end_date okc_k_headers_all_b.end_date%TYPE;
  
  CURSOR header_cur IS
  SELECT id,
    contract_number,
    start_date,
    end_date
  FROM okc_k_headers_all_b
  WHERE contract_number = 'CJ-10123';
 -- where authoring_org_id = <ORG_ID>;
  
  CURSOR line_cur (l_header_id NUMBER)IS
  SELECT id,
    line_number
  FROM okc_k_lines_b
  WHERE chr_id = l_header_id;
  
  CURSOR subline_cur (l_header_id NUMBER)IS
  SELECT id
  FROM okc_k_lines_b
  WHERE dnz_chr_id = l_header_id;
  
BEGIN  
FOR header_rec IN header_cur LOOP
l_chrv_tbl_in(1).ID := header_rec.id;
l_chrv_tbl_in(1).start_date := header_rec.start_date;
l_chrv_tbl_in(1).end_date    := LAST_DAY(header_rec.start_date);

 okc_contract_pub.update_contract_header 
              (
                p_api_version       => l_api_version,
                p_init_msg_list     => FND_API.G_TRUE,
                x_return_status     => l_return_status,
                x_msg_count         => l_msg_count,
                x_msg_data          => l_msg_data,
                p_restricted_update => 'N',
                p_chrv_tbl          => l_chrv_tbl_in,
                x_chrv_tbl          => l_chrv_tbl_out
               );
  IF (l_return_status <>  'S') THEN
     dbms_output.put_line ('Error while updating the Contract Header for Contract '||header_rec.contract_number);
     dbms_output.put_line ('Error: '||l_msg_data);
  ELSE
    --init_contract_line (l_clev_tbl_in);
  --init_contract_oks_line (l_klnv_tbl_in);
    FOR line_rec IN line_cur(header_rec.id) LOOP
      l_clev_tbl_in(i).id                := line_rec.id;
      l_clev_tbl_in(i).start_date        := header_rec.start_date;
      l_clev_tbl_in(i).end_date            := LAST_DAY(header_rec.start_date);
      i := i+1;
    END LOOP;
    
    IF (i > 1) THEN
      okc_contract_pub.update_contract_line 
            (p_api_version          => l_api_version,
             p_init_msg_list        => l_init_msg_list,
             x_return_status        => l_return_status,
             x_msg_count            => l_msg_count,
             x_msg_data             => l_msg_data,
             p_restricted_update    => 'F',
             p_clev_tbl             => l_clev_tbl_in,
             x_clev_tbl             => l_clev_tbl_out);
      IF l_return_status <> 'S' THEN
         dbms_output.put_line ('Error while updating the Contract Line for Contract '||header_rec.contract_number);
         dbms_output.put_line ('Error: '||l_msg_data);
      ELSE
         FOR line_rec IN line_cur(header_rec.id) LOOP
           oks_coverages_pvt.update_coverage_effectivity(
                  p_api_version     => l_api_version,
                  p_init_msg_list   => l_init_msg_list,
                  x_return_status   => l_return_status,
                  x_msg_count        => l_msg_count,
                  x_msg_data        => l_msg_data,
                  p_service_Line_Id => line_rec.id,
                  p_New_Start_Date  => header_rec.start_date,
                  p_New_End_Date    => LAST_DAY(header_rec.start_date));
                  
          IF l_return_status <> 'S' THEN
            dbms_output.put_line ('Error while updating the Contract Coverage for Contract '||header_rec.contract_number);
            dbms_output.put_line ('Error: '||l_msg_data);
          ELSE
            oks_pm_programs_pvt.adjust_pm_program_schedule
                 (p_api_version          => l_api_version,
                  p_init_msg_list        => l_init_msg_list,
                  p_contract_line_id     => line_rec.id,
                  p_new_start_date       => header_rec.start_date,
                  p_new_end_date         => LAST_DAY(header_rec.start_date),
                  x_return_status        => l_return_status,
                  x_msg_count            => l_msg_count,
                  x_msg_data             => l_msg_data
                  );
            IF l_return_status <> 'S' THEN
              dbms_output.put_line ('Error while updating the PM Schedule for Contract '||header_rec.contract_number);
              dbms_output.put_line ('Error: '||l_msg_data);
            ELSE
              i :=1;
              --init_contract_line (l_cle_tbl_in);
            --init_contract_oks_line (l_kln_tbl_in);
              FOR subline_rec IN subline_cur(header_rec.id) LOOP
                l_cle_tbl_in(i).id                    := subline_rec.id;
                l_cle_tbl_in(i).start_date            := header_rec.start_date;
                l_cle_tbl_in(i).end_date                := LAST_DAY(header_rec.start_date);
              --l_cle_tbl_in(i).object_version_number := l_object_version_number; 
                i := i+1;
              END LOOP;
                okc_contract_pub.update_contract_line 
                      (p_api_version          => l_api_version,
                       p_init_msg_list        => l_init_msg_list,
                       x_return_status        => l_return_status,
                       x_msg_count            => l_msg_count,
                       x_msg_data             => l_msg_data,
                       p_restricted_update    => 'F',
                       p_clev_tbl             => l_cle_tbl_in,
                       x_clev_tbl             => l_cle_tbl_out);
              IF l_return_status <> 'S' THEN
                dbms_output.put_line ('Error while updating the Sub Line for Contract '||header_rec.contract_number);
                dbms_output.put_line ('Error: '||l_msg_data);
              ELSE
                dbms_output.put_line ('Sript Updated the END Date successfully for Contract '||header_rec.contract_number);
               COMMIT;
              END IF;
            END IF;
          END IF;
         END LOOP;
      END IF;
    END IF;
  END IF;
END LOOP;
END;
/