eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.OKC_TERMS_UTIL_PVT dependencies on OKC_ARTICLE_VERSIONS

Line 1798: l_display_name okc_article_versions.display_name%TYPE;

1794: Function Get_latest_art_version(p_article_id IN NUMBER,
1795: p_org_id IN NUMBER,
1796: p_eff_date IN DATE)
1797: RETURN Varchar2 IS
1798: l_display_name okc_article_versions.display_name%TYPE;
1799: l_global_org_id number;
1800: CURSOR ver_csr IS
1801: SELECT nvl(ver.display_name,art.article_title) name
1802: FROM okc_articles_all art,

Line 1803: okc_article_versions ver

1799: l_global_org_id number;
1800: CURSOR ver_csr IS
1801: SELECT nvl(ver.display_name,art.article_title) name
1802: FROM okc_articles_all art,
1803: okc_article_versions ver
1804: WHERE art.org_id = p_org_id
1805: AND art.article_id = p_article_id
1806: AND art.article_id = ver.article_id
1807: AND ver.start_date <= nvl(p_eff_date,sysdate)

Line 1809: from okc_article_versions ver1

1805: AND art.article_id = p_article_id
1806: AND art.article_id = ver.article_id
1807: AND ver.start_date <= nvl(p_eff_date,sysdate)
1808: AND ver.start_date = (select max(start_date)
1809: from okc_article_versions ver1
1810: where ver1.article_id = ver.article_id
1811: and ver1.start_date <= nvl(p_eff_date,sysdate)
1812: and ver1.article_status = ver.article_status)
1813: AND (ver.article_status = 'APPROVED' OR

Line 1815: from okc_article_versions ver2

1811: and ver1.start_date <= nvl(p_eff_date,sysdate)
1812: and ver1.article_status = ver.article_status)
1813: AND (ver.article_status = 'APPROVED' OR
1814: not exists (select 1
1815: from okc_article_versions ver2
1816: where ver2.article_id = art.article_id
1817: and ver2.start_date <= nvl(p_eff_date,sysdate)
1818: and ver2.article_status = 'APPROVED'));
1819:

Line 1831: l_article_version_id okc_article_versions.article_version_id%TYPE;

1827: /* Modified Cursor for Bug 4956969 */
1828: Function Get_latest_tmpl_art_version_id(p_article_id IN NUMBER,
1829: p_eff_date IN DATE)
1830: RETURN NUMBER IS
1831: l_article_version_id okc_article_versions.article_version_id%TYPE;
1832: CURSOR ver_csr IS
1833: SELECT ver.article_version_id
1834: FROM okc_articles_all art,
1835: okc_article_versions ver

Line 1835: okc_article_versions ver

1831: l_article_version_id okc_article_versions.article_version_id%TYPE;
1832: CURSOR ver_csr IS
1833: SELECT ver.article_version_id
1834: FROM okc_articles_all art,
1835: okc_article_versions ver
1836: WHERE art.article_id = p_article_id
1837: AND art.article_id = ver.article_id
1838: AND ver.start_date <= nvl(p_eff_date,sysdate)
1839: AND ver.start_date = (select max(start_date)

Line 1840: from okc_article_versions ver1

1836: WHERE art.article_id = p_article_id
1837: AND art.article_id = ver.article_id
1838: AND ver.start_date <= nvl(p_eff_date,sysdate)
1839: AND ver.start_date = (select max(start_date)
1840: from okc_article_versions ver1
1841: where ver1.article_id = ver.article_id
1842: and ver1.start_date <= nvl(p_eff_date,sysdate)
1843: and ver1.article_status = ver.article_status)
1844: AND (ver.article_status = 'APPROVED' OR

Line 1846: from okc_article_versions ver2

1842: and ver1.start_date <= nvl(p_eff_date,sysdate)
1843: and ver1.article_status = ver.article_status)
1844: AND (ver.article_status = 'APPROVED' OR
1845: not exists (select 1
1846: from okc_article_versions ver2
1847: where ver2.article_id = art.article_id
1848: and ver2.start_date <= nvl(p_eff_date,sysdate)
1849: and ver2.article_status = 'APPROVED'));
1850: BEGIN

Line 1961: okc_article_versions ver

1957:
1958: CURSOR alt_csr IS
1959: SELECT 1
1960: FROM okc_article_relatns_all reln,
1961: okc_article_versions ver
1962: WHERE reln.source_article_id = p_article_id
1963: AND reln.relationship_type = 'ALTERNATE'
1964: AND reln.target_article_id = ver.article_id
1965: AND NVL(p_eff_date,SYSDATE) BETWEEN ver.start_date AND NVL(ver.end_date, nvl(p_eff_date,SYSDATE))

Line 2002: okc_article_versions ver,

1998:
1999: CURSOR alt_csr(cp_effective_date DATE) IS
2000: SELECT 1
2001: FROM okc_article_relatns_all reln,
2002: okc_article_versions ver,
2003: okc_articles_all art
2004: WHERE reln.source_article_id = p_article_id
2005: AND reln.relationship_type = 'ALTERNATE'
2006: AND reln.target_article_id = art.article_id

Line 2243: FROM okc_article_versions

2239: FUNCTION get_article_version_number(p_art_version_id IN NUMBER)
2240: RETURN Varchar2 IS
2241: CURSOR csr_art_ver IS
2242: SELECT article_version_number
2243: FROM okc_article_versions
2244: WHERE article_version_id = p_art_version_id;
2245:
2246: l_article_version_number VARCHAR2(240);
2247:

Line 2355: FROM okc_article_versions a

2351: WHERE a.article_id = p_article_id;
2352:
2353: CURSOR csr_article_display_name IS
2354: SELECT a.display_name
2355: FROM okc_article_versions a
2356: WHERE a.article_version_id = p_article_version_id;
2357:
2358: l_article_title VARCHAR2(450) :='';
2359: l_article_number VARCHAR2(450) :='';

Line 2441: select default_section from okc_article_versions where article_version_id = l_article_version_id);

2437: l_name FND_LOOKUPS.MEANING%TYPE := null;
2438: l_article_version_id NUMBER;
2439: cursor get_default_section_name(l_article_version_id NUMBER) is
2440: select meaning from fnd_lookups where lookup_type = 'OKC_ARTICLE_SECTION' and lookup_code = (
2441: select default_section from okc_article_versions where article_version_id = l_article_version_id);
2442:
2443: cursor get_unassigned_section_name is
2444: select meaning from fnd_lookups where lookup_type = 'OKC_ARTICLE_SECTION' and
2445: lookup_code = 'UNASSIGNED';

Line 2477: and lookup_code = (select default_section from okc_article_versions

2473:
2474: cursor get_article_section_name(l_article_version_id NUMBER) is
2475: select meaning from fnd_lookups
2476: where lookup_type = 'OKC_ARTICLE_SECTION'
2477: and lookup_code = (select default_section from okc_article_versions
2478: where article_version_id = l_article_version_id);
2479:
2480: cursor get_expert_section_name(l_template_id NUMBER) is
2481: select meaning from fnd_lookups

Line 2596: FROM okc_article_versions

2592:
2593: CURSOR l_get_latest_article_csr(p_article_effective_date IN DATE) IS
2594: SELECT article_version_id ,
2595: article_version_number
2596: FROM okc_article_versions
2597: WHERE article_id= p_article_id
2598: AND article_status in ('ON_HOLD','APPROVED')
2599: AND nvl(p_article_effective_date,sysdate) >= Start_date
2600: AND nvl(p_article_effective_date,sysdate) <= nvl(end_date, nvl(p_article_effective_date,sysdate) +1)

Line 2605: FROM okc_article_versions

2601: AND p_document_type <> 'TEMPLATE'
2602: UNION ALL
2603: SELECT article_version_id ,
2604: article_version_number
2605: FROM okc_article_versions
2606: WHERE article_id= p_article_id
2607: AND nvl(p_article_effective_date,sysdate) >= Start_date
2608: AND nvl(p_article_effective_date,sysdate) <= nvl(end_date, nvl(p_article_effective_date,sysdate) +1)
2609: AND p_document_type = 'TEMPLATE'

Line 2616: FROM OKC_ARTICLE_VERSIONS VERS,

2612: CURSOR l_get_local_article_csr(p_article_effective_date IN DATE, b_local_org_id IN NUMBER) IS
2613: SELECT ADP.LOCAL_ARTICLE_VERSION_ID LOCAL_ARTICLE_VERSION_ID,
2614: ADP.ADOPTION_TYPE,
2615: VERS1.ARTICLE_ID
2616: FROM OKC_ARTICLE_VERSIONS VERS,
2617: OKC_ARTICLE_ADOPTIONS ADP,
2618: OKC_ARTICLE_VERSIONS VERS1
2619: WHERE ADP.GLOBAL_ARTICLE_VERSION_ID = VERS.ARTICLE_VERSION_ID
2620: AND VERS.ARTICLE_ID = p_article_id

Line 2618: OKC_ARTICLE_VERSIONS VERS1

2614: ADP.ADOPTION_TYPE,
2615: VERS1.ARTICLE_ID
2616: FROM OKC_ARTICLE_VERSIONS VERS,
2617: OKC_ARTICLE_ADOPTIONS ADP,
2618: OKC_ARTICLE_VERSIONS VERS1
2619: WHERE ADP.GLOBAL_ARTICLE_VERSION_ID = VERS.ARTICLE_VERSION_ID
2620: AND VERS.ARTICLE_ID = p_article_id
2621: AND nvl(p_article_effective_date,sysdate) >= VERS.START_DATE
2622: AND nvl(p_article_effective_date,sysdate) <= nvl(VERS.end_date, nvl(p_article_effective_date,sysdate) +1)

Line 2633: FROM OKC_ARTICLE_VERSIONS VERS,

2629: UNION ALL
2630: SELECT ADP.GLOBAL_ARTICLE_VERSION_ID LOCAL_ARTICLE_VERSION_ID,
2631: ADP.ADOPTION_TYPE,
2632: VERS.ARTICLE_ID
2633: FROM OKC_ARTICLE_VERSIONS VERS,
2634: OKC_ARTICLE_ADOPTIONS ADP
2635: WHERE ADP.GLOBAL_ARTICLE_VERSION_ID = VERS.ARTICLE_VERSION_ID
2636: AND VERS.ARTICLE_ID = p_article_id
2637: AND nvl(p_article_effective_date,sysdate) >= VERS.START_DATE

Line 2648: FROM OKC_ARTICLE_VERSIONS VERS,

2644: UNION ALL
2645: SELECT ADP.LOCAL_ARTICLE_VERSION_ID LOCAL_ARTICLE_VERSION_ID,
2646: ADP.ADOPTION_TYPE,
2647: VERS1.ARTICLE_ID
2648: FROM OKC_ARTICLE_VERSIONS VERS,
2649: OKC_ARTICLE_ADOPTIONS ADP,
2650: OKC_ARTICLE_VERSIONS VERS1
2651: WHERE ADP.GLOBAL_ARTICLE_VERSION_ID = VERS.ARTICLE_VERSION_ID
2652: AND VERS.ARTICLE_ID = p_article_id

Line 2650: OKC_ARTICLE_VERSIONS VERS1

2646: ADP.ADOPTION_TYPE,
2647: VERS1.ARTICLE_ID
2648: FROM OKC_ARTICLE_VERSIONS VERS,
2649: OKC_ARTICLE_ADOPTIONS ADP,
2650: OKC_ARTICLE_VERSIONS VERS1
2651: WHERE ADP.GLOBAL_ARTICLE_VERSION_ID = VERS.ARTICLE_VERSION_ID
2652: AND VERS.ARTICLE_ID = p_article_id
2653: AND nvl(p_article_effective_date,sysdate) >= VERS.START_DATE
2654: AND nvl(p_article_effective_date,sysdate) <= nvl(VERS.end_date, nvl(p_article_effective_date,sysdate) +1)

Line 2664: FROM OKC_ARTICLE_VERSIONS VERS,

2660: UNION ALL
2661: SELECT ADP.GLOBAL_ARTICLE_VERSION_ID LOCAL_ARTICLE_VERSION_ID,
2662: ADP.ADOPTION_TYPE,
2663: VERS.ARTICLE_ID
2664: FROM OKC_ARTICLE_VERSIONS VERS,
2665: OKC_ARTICLE_ADOPTIONS ADP
2666: WHERE ADP.GLOBAL_ARTICLE_VERSION_ID = VERS.ARTICLE_VERSION_ID
2667: AND VERS.ARTICLE_ID = p_article_id
2668: AND nvl(p_article_effective_date,sysdate) >= VERS.START_DATE

Line 2683: FROM okc_article_versions

2679: WHERE article_id = p_article_id;
2680:
2681: CURSOR l_article_number (b_article__version_id IN NUMBER) IS
2682: SELECT article_version_number
2683: FROM okc_article_versions
2684: WHERE article_version_id= b_article__version_id;
2685: l_api_name CONSTANT VARCHAR2(30) := 'get_latest_article_details';
2686: l_effective_date DATE := null;
2687: l_article_version_id NUMBER;

Line 3558: l_article_version_id okc_article_versions.article_version_id%TYPE;

3554: p_status_code IN VARCHAR2,
3555: p_doc_type IN VARCHAR2 DEFAULT NULL,
3556: p_doc_id IN NUMBER DEFAULT NULL) RETURN NUMBER
3557: IS
3558: l_article_version_id okc_article_versions.article_version_id%TYPE;
3559: l_effective_date DATE;
3560: l_article_org_id NUMBER;
3561: l_current_org_id NUMBER;
3562: l_api_name CONSTANT VARCHAR2(30) := 'get_latest_tmpl_art_version_id';

Line 3570: OKC_ARTICLE_VERSIONS ARTV

3566: -- new cursor to get draft/rejected versions from table OKC_TMPL_DRAFT_CLAUSES
3567: CURSOR l_draft_selected_ver_csr IS
3568: SELECT ARTV.article_version_id
3569: FROM OKC_TMPL_DRAFT_CLAUSES TMPLC,
3570: OKC_ARTICLE_VERSIONS ARTV
3571: WHERE TMPLC.template_id = p_doc_id
3572: AND TMPLC.article_id = p_article_id
3573: AND TMPLC.selected_yn = 'Y'
3574: AND ARTV.article_id = TMPLC.article_id

Line 3586: okc_article_versions ver

3582: -- modify this cursor to exclude versions from the the table OKC_DRAFT_CLAUSES
3583: CURSOR l_draft_ver_csr(cp_effective_date DATE) IS
3584: SELECT ver.article_version_id
3585: FROM okc_articles_all art,
3586: okc_article_versions ver
3587: WHERE art.article_id = p_article_id
3588: AND art.article_id = ver.article_id
3589: AND cp_effective_date BETWEEN ver.start_date AND NVL(ver.end_date,cp_effective_date+1)
3590: -- begin change

Line 3604: okc_article_versions ver

3600: -- last effort to get a clause for template in draft status
3601: CURSOR l_draft_latest_ver_csr(cp_effective_date DATE) IS
3602: SELECT ver.article_version_id
3603: FROM okc_articles_all art,
3604: okc_article_versions ver
3605: WHERE art.article_id = p_article_id
3606: AND art.article_id = ver.article_id
3607: -- Bugs 4018610, 4018467, the start date of draft clause can be
3608: -- changed to a future date, making this cursor return nothing

Line 3613: FROM okc_article_versions ver1

3609: -- The draft clause status can also change to pending approval
3610: -- or an approved clause can be put on hold after including in the template
3611: -- AND ver.start_date <= cp_effective_date
3612: AND ver.start_date = (SELECT max(start_date)
3613: FROM okc_article_versions ver1
3614: WHERE ver1.article_id = ver.article_id
3615: --AND ver1.start_date <= cp_effective_date
3616: --AND ver1.article_status = 'APPROVED'
3617: );

Line 3623: OKC_ARTICLE_VERSIONS ARTV

3619: -- new cursor to get draft/rejected versions from table OKC_TMPL_DRAFT_CLAUSES
3620: CURSOR l_pen_app_selected_ver_csr IS
3621: SELECT ARTV.article_version_id
3622: FROM OKC_TMPL_DRAFT_CLAUSES TMPLC,
3623: OKC_ARTICLE_VERSIONS ARTV
3624: WHERE TMPLC.template_id = p_doc_id
3625: AND TMPLC.article_id = p_article_id
3626: AND TMPLC.selected_yn = 'Y'
3627: AND ARTV.article_id = TMPLC.article_id

Line 3639: okc_article_versions ver

3635:
3636: CURSOR l_approved_ver_csr(cp_effective_date DATE) IS
3637: SELECT ver.article_version_id
3638: FROM okc_articles_all art,
3639: okc_article_versions ver
3640: WHERE art.article_id = p_article_id
3641: AND art.article_id = ver.article_id
3642: AND ver.article_status IN ('APPROVED','EXPIRED','ON_HOLD')
3643: AND cp_effective_date BETWEEN ver.start_date AND NVL(ver.end_date,cp_effective_date+1);

Line 3648: okc_article_versions ver

3644:
3645: CURSOR l_approved_latest_ver_csr(cp_effective_date DATE) IS
3646: SELECT ver.article_version_id
3647: FROM okc_articles_all art,
3648: okc_article_versions ver
3649: WHERE art.article_id = p_article_id
3650: AND art.article_id = ver.article_id
3651: AND ver.start_date <= cp_effective_date
3652: AND ver.article_status IN ('APPROVED','EXPIRED','ON_HOLD')

Line 3654: FROM okc_article_versions ver1

3650: AND art.article_id = ver.article_id
3651: AND ver.start_date <= cp_effective_date
3652: AND ver.article_status IN ('APPROVED','EXPIRED','ON_HOLD')
3653: AND ver.start_date = (SELECT max(start_date)
3654: FROM okc_article_versions ver1
3655: WHERE ver1.article_id = ver.article_id
3656: AND ver1.start_date <= cp_effective_date
3657: AND ver1.article_status IN ('APPROVED','EXPIRED','ON_HOLD'));
3658:

Line 3670: OKC_ARTICLE_VERSIONS VER

3666: -- cursor to get latest adopted article version id for global article
3667: CURSOR l_get_max_adopted_article_csr(b_article_id IN NUMBER, b_current_org_id IN NUMBER) IS
3668: SELECT ADP.GLOBAL_ARTICLE_VERSION_ID
3669: FROM OKC_ARTICLE_ADOPTIONS ADP,
3670: OKC_ARTICLE_VERSIONS VER
3671: WHERE ADP.GLOBAL_ARTICLE_VERSION_ID = VER.article_version_id
3672: AND VER.article_id = b_article_id
3673: AND ADP.LOCAL_ORG_ID = b_current_org_id
3674: --AND ADP.adoption_status IN ( 'APPROVED', 'ON_HOLD')

Line 3824: FROM okc_article_Versions oav,

3820: oav.article_version_id,
3821: oav.article_id,
3822: oav.start_date,
3823: oav.end_date
3824: FROM okc_article_Versions oav,
3825: okc_articles_all oaa
3826: WHERE oav.article_id = oaa.article_id
3827: AND oaa.org_id = cp_org_id
3828: AND oav.article_status IN ('DRAFT','REJECTED')

Line 3849: FROM okc_article_Versions oav,

3845: CURSOR draft_articles_csr (cp_org_id NUMBER, cp_effective_date DATE,
3846: cp_template_id NUMBER)IS
3847: SELECT oav.article_version_id,
3848: oav.article_id
3849: FROM okc_article_Versions oav,
3850: okc_articles_all oaa
3851: WHERE oav.article_id = oaa.article_id
3852: AND oaa.org_id = cp_org_id
3853: AND oav.article_status IN ('DRAFT','REJECTED')

Line 3864: SELECT 'Y' from okc_Article_versions

3860: AND oka.document_type='TEMPLATE');
3861:
3862: CURSOR valid_ver_csr (cp_article_id NUMBER, cp_article_version_id NUMBER,
3863: cp_template_effective_date DATE) IS
3864: SELECT 'Y' from okc_Article_versions
3865: WHERE article_id = cp_article_id
3866: AND article_version_id <> cp_article_version_id
3867: AND article_status = 'APPROVED'
3868: AND start_date <= cp_template_effective_date

Line 4161: okc_article_versions ver

4157: lc_org_id NUMBER) IS
4158: SELECT 'Y'
4159: FROM okc_terms_templates_all tmpl,
4160: okc_k_articles_b kart,
4161: okc_article_versions ver
4162: WHERE tmpl.template_id = lc_tmpl_id
4163: AND kart.document_id = tmpl.template_id
4164: AND kart.document_type = 'TEMPLATE'
4165: AND ver.article_id = kart.sav_sae_id

Line 4169: okc_article_versions ver1

4165: AND ver.article_id = kart.sav_sae_id
4166: AND ver.global_yn = 'Y'
4167: AND NOT EXISTS (SELECT 1
4168: FROM okc_article_adoptions adp,
4169: okc_article_versions ver1
4170: WHERE adp.global_article_version_id = ver1.article_version_id
4171: AND ver1.article_id = ver.article_id
4172: AND adp.local_org_id = lc_org_id
4173: AND adp.adoption_status IN ( 'APPROVED', 'ON_HOLD')

Line 5080: l_article_version_id OKC_ARTICLE_VERSIONS.ARTICLE_VERSION_ID%TYPE;

5076: p_document_id IN NUMBER ) RETURN NUMBER
5077: IS
5078:
5079: l_article_version_number OKC_ARTICLES_ALL.ARTICLE_NUMBER%TYPE;
5080: l_article_version_id OKC_ARTICLE_VERSIONS.ARTICLE_VERSION_ID%TYPE;
5081: l_local_article_id OKC_K_ARTICLES_B.ORIG_ARTICLE_ID%TYPE;
5082: l_adoption_type OKC_ARTICLE_VERSIONS.ADOPTION_TYPE%TYPE;
5083: l_ref_article_id OKC_K_ARTICLES_B.REF_ARTICLE_ID%TYPE;
5084:

Line 5082: l_adoption_type OKC_ARTICLE_VERSIONS.ADOPTION_TYPE%TYPE;

5078:
5079: l_article_version_number OKC_ARTICLES_ALL.ARTICLE_NUMBER%TYPE;
5080: l_article_version_id OKC_ARTICLE_VERSIONS.ARTICLE_VERSION_ID%TYPE;
5081: l_local_article_id OKC_K_ARTICLES_B.ORIG_ARTICLE_ID%TYPE;
5082: l_adoption_type OKC_ARTICLE_VERSIONS.ADOPTION_TYPE%TYPE;
5083: l_ref_article_id OKC_K_ARTICLES_B.REF_ARTICLE_ID%TYPE;
5084:
5085:
5086: -- Fix for bug# 5235082. Changed the query to use id instead of sav_sae_id