[Home] [Help]
48: p_validate_commit IN VARCHAR2,
49: p_validation_string IN VARCHAR2,
50: p_commit IN VARCHAR2 ,
51: p_ref_type IN VARCHAR2 := 'SECTION', -- 'ARTICLE' or 'SECTION'
52: p_ref_id IN NUMBER, -- Id of okc_sections_b or okc_articles_b depending upon ref type
53: p_doc_type IN VARCHAR2,
54: p_doc_id IN NUMBER,
55:
56: p_kart_tbl IN kart_tbl_type,
72:
73:
74: CURSOR l_get_scn_child_csr(b_scn_id NUMBER) IS
75: SELECT ID,SECTION_SEQUENCE DISPLAY_SEQ,'SECTION' obj_type
76: FROM OKC_SECTIONS_B
77: WHERE document_type = p_doc_type
78: AND document_id = p_doc_id
79: AND scn_id=b_scn_id
80: UNION ALL
710: WHERE id=b_id;
711:
712: CURSOR l_doc_scn_csr(b_id NUMBER) IS
713: SELECT DOCUMENT_TYPE,DOCUMENT_ID
714: FROM OKC_SECTIONS_B
715: WHERE id=b_id;
716:
717: BEGIN
718:
975: l_query1 VARCHAR2(3900);
976: l_query2 VARCHAR2(3900);
977: l_article_effective_date DATE;
978: l_scn_seq NUMBER;
979: l_scn_id OKC_SECTIONS_B.ID%TYPE;
980: l_art_id OKC_K_ARTICLES_B.ID%TYPE;
981: l_art_seq NUMBER;
982: l_articles_dropped NUMBER :=0;
983: l_max_sec_seq NUMBER;
1021: WHERE article_id= b_article_id
1022: AND article_status in ('ON_HOLD','APPROVED') );
1023:
1024: CURSOR l_get_scn_csr(b_scn_code VARCHAR2) IS
1025: SELECT id FROM OKC_SECTIONS_B
1026: WHERE document_type=p_doc_type
1027: AND document_id =p_doc_id
1028: AND scn_code = b_scn_code
1029: AND nvl(AMENDMENT_OPERATION_CODE,'?')<>'DELETED'
1030: AND nvl(SUMMARY_AMEND_OPERATION_CODE,'?')<>'DELETED'
1031: AND rownum=1 ;
1032:
1033: CURSOR l_get_section_seq_csr IS
1034: SELECT nvl(max(section_sequence),0)+10 FROM OKC_SECTIONS_B
1035: WHERE document_type = p_doc_type
1036: AND document_id = p_doc_id
1037: AND NVL(scn_code,'XWY') <> 'UNASSIGNED'
1038: AND scn_id IS NULL;
1044: AND SCN_ID = b_scn_id;
1045:
1046: --Bug#5160892 added below cursor
1047: CURSOR l_get_sec_seq_csr(b_scn_id NUMBER) IS
1048: SELECT nvl(max(section_sequence),0)+10 FROM OKC_SECTIONS_B
1049: WHERE document_type=p_doc_type
1050: AND document_id =p_doc_id
1051: AND SCN_ID = b_scn_id;
1052:
2225:
2226: scn_child_tbl scn_child_tbl_type;
2227:
2228: CURSOR l_get_max_seq_csr(b_scn_id NUMBER) is
2229: SELECT nvl(max(SECTION_SEQUENCE),0) FROM OKC_SECTIONS_B
2230: WHERE document_type=p_doc_type
2231: AND document_id=p_doc_id
2232: AND ( (b_scn_id is Null and scn_id is Null)
2233: OR
2245: CURSOR l_get_scn_csr(b_cat_id NUMBER) IS
2246: SELECT SCN_ID FROM OKC_K_ARTICLES_B WHERE Id=b_cat_id;
2247:
2248: CURSOR l_get_parent_scn_csr(b_scn_id NUMBER) IS
2249: SELECT SCN_ID FROM OKC_SECTIONS_B WHERE Id=b_scn_id;
2250:
2251: CURSOR l_get_parents_csr(b_id NUMBER) IS
2252: SELECT ID FROM OKC_SECTIONS_B
2253: where id<>b_id
2248: CURSOR l_get_parent_scn_csr(b_scn_id NUMBER) IS
2249: SELECT SCN_ID FROM OKC_SECTIONS_B WHERE Id=b_scn_id;
2250:
2251: CURSOR l_get_parents_csr(b_id NUMBER) IS
2252: SELECT ID FROM OKC_SECTIONS_B
2253: where id<>b_id
2254: AND document_type=p_doc_type
2255: AND document_id=p_doc_id
2256: start with Id=b_id
2258:
2259:
2260: CURSOR l_get_scn_child(b_scn_id NUMBER) IS
2261: SELECT ID,SECTION_SEQUENCE DISPLAY_SEQ,'SECTION' obj_type
2262: FROM OKC_SECTIONS_B
2263: WHERE document_type = p_doc_type
2264: AND document_id = p_doc_id
2265: AND ( (b_scn_id is Null and scn_id is Null)
2266: OR