[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
708: WHERE id=b_id;
709:
710: CURSOR l_doc_scn_csr(b_id NUMBER) IS
711: SELECT DOCUMENT_TYPE,DOCUMENT_ID
712: FROM OKC_SECTIONS_B
713: WHERE id=b_id;
714:
715: BEGIN
716:
972: l_query1 VARCHAR2(3900);
973: l_query2 VARCHAR2(3900);
974: l_article_effective_date DATE;
975: l_scn_seq NUMBER;
976: l_scn_id OKC_SECTIONS_B.ID%TYPE;
977: l_art_id OKC_K_ARTICLES_B.ID%TYPE;
978: l_art_seq NUMBER;
979: l_articles_dropped NUMBER :=0;
980: l_max_sec_seq NUMBER;
1018: WHERE article_id= b_article_id
1019: AND article_status in ('ON_HOLD','APPROVED') );
1020:
1021: CURSOR l_get_scn_csr(b_scn_code VARCHAR2) IS
1022: SELECT id FROM OKC_SECTIONS_B
1023: WHERE document_type=p_doc_type
1024: AND document_id =p_doc_id
1025: AND scn_code = b_scn_code
1026: AND nvl(AMENDMENT_OPERATION_CODE,'?')<>'DELETED'
1027: AND nvl(SUMMARY_AMEND_OPERATION_CODE,'?')<>'DELETED'
1028: AND rownum=1 ;
1029:
1030: CURSOR l_get_section_seq_csr IS
1031: SELECT nvl(max(section_sequence),0)+10 FROM OKC_SECTIONS_B
1032: WHERE document_type = p_doc_type
1033: AND document_id = p_doc_id
1034: AND NVL(scn_code,'XWY') <> 'UNASSIGNED'
1035: AND scn_id IS NULL;
1041: AND SCN_ID = b_scn_id;
1042:
1043: --Bug#5160892 added below cursor
1044: CURSOR l_get_sec_seq_csr(b_scn_id NUMBER) IS
1045: SELECT nvl(max(section_sequence),0)+10 FROM OKC_SECTIONS_B
1046: WHERE document_type=p_doc_type
1047: AND document_id =p_doc_id
1048: AND SCN_ID = b_scn_id;
1049:
2112:
2113: scn_child_tbl scn_child_tbl_type;
2114:
2115: CURSOR l_get_max_seq_csr(b_scn_id NUMBER) is
2116: SELECT nvl(max(SECTION_SEQUENCE),0) FROM OKC_SECTIONS_B
2117: WHERE document_type=p_doc_type
2118: AND document_id=p_doc_id
2119: AND ( (b_scn_id is Null and scn_id is Null)
2120: OR
2132: CURSOR l_get_scn_csr(b_cat_id NUMBER) IS
2133: SELECT SCN_ID FROM OKC_K_ARTICLES_B WHERE Id=b_cat_id;
2134:
2135: CURSOR l_get_parent_scn_csr(b_scn_id NUMBER) IS
2136: SELECT SCN_ID FROM OKC_SECTIONS_B WHERE Id=b_scn_id;
2137:
2138: CURSOR l_get_parents_csr(b_id NUMBER) IS
2139: SELECT ID FROM OKC_SECTIONS_B
2140: where id<>b_id
2135: CURSOR l_get_parent_scn_csr(b_scn_id NUMBER) IS
2136: SELECT SCN_ID FROM OKC_SECTIONS_B WHERE Id=b_scn_id;
2137:
2138: CURSOR l_get_parents_csr(b_id NUMBER) IS
2139: SELECT ID FROM OKC_SECTIONS_B
2140: where id<>b_id
2141: AND document_type=p_doc_type
2142: AND document_id=p_doc_id
2143: start with Id=b_id
2145:
2146:
2147: CURSOR l_get_scn_child(b_scn_id NUMBER) IS
2148: SELECT ID,SECTION_SEQUENCE DISPLAY_SEQ,'SECTION' obj_type
2149: FROM OKC_SECTIONS_B
2150: WHERE document_type = p_doc_type
2151: AND document_id = p_doc_id
2152: AND ( (b_scn_id is Null and scn_id is Null)
2153: OR