[Home] [Help]
1: package body OKC_REPORT_PVT as
2: /* $Header: OKCRXPKB.pls 120.3 2005/08/16 16:35:18 jkodiyan noship $ */
3:
4: l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5:
6: --
7: -- Print Contract Process API methods
8: --
154: l_message varchar2(2000);
155: l_token varchar2(240);
156: cursor profile_option_csr(p_profile_option varchar2) is
157: select USER_PROFILE_OPTION_NAME
158: from fnd_profile_options_vl
159: where application_id=510
160: and PROFILE_OPTION_NAME = p_profile_option;
161: begin
162: open profile_option_csr(p_prof_name);
183: select SQL_ID from (
184: select SQL_ID, XST_ID
185: from OKC_REPORT_XST
186: connect by ID = prior XST_ID
187: start with ID = fnd_profile.value('OKC_WEB_REPORT')
188: ) where SQL_ID is not NULL;
189: begin
190: open c_sql_id;
191: fetch c_sql_id into l_sql_id;
206: l_xsl_id number;
207: cursor c_xsl_id is
208: select XSL_ID
209: from OKC_REPORT_XST
210: where ID = fnd_profile.value('OKC_WEB_REPORT');
211: begin
212: open c_xsl_id;
213: fetch c_xsl_id into l_xsl_id;
214: close c_xsl_id;
806: --
807: -- PURPOSE
808: -- call only from JSP apllication (not from forms apps)
809: --
810: -- dinamicly executes
812: -- SQL statements use okc_tree_index.get_root_id as Contact Id
813: --
814: procedure set_env(p_kid varchar2) is
826: and P.code not in ('xid', 'kid', 'vid', 'content_type');
827: l_value varchar2(200);
828: begin
829: okc_tree_index.set_root_id(p_kid);
830: G_xid := fnd_profile.value('OKC_WEB_REPORT');
831: G_kid := p_kid;
832: G_vid := OKC_PARAMETERS_PUB.Get('vid');
833: get_k_version(G_kid, G_vid, G_mid);
834: if (G_vid = -1) then
846: l_dummy := '?';
847: open c1;
848: fetch c1 into l_dummy;
849: close c1;
850: l_value := fnd_profile.value('OKC_WEB_ENVPROC');
851: if (l_value is NULL or upper(l_value) = 'OKC_CONTEXT.SET_OKC_ORG_CONTEXT') then
852: if (l_dummy = '!') then
853: okc_context.set_okc_org_context(p_chr_id => p_kid);
854: else
886: -- if not default report requested
887: --
888: -- call only from JSP apllication (not from forms apps)
889: --
890: -- dinamicly executes
892: -- SQL statements use okc_tree_index.get_root_id as Contact Id
893: --
894: procedure set_env(
896: ,p_xid varchar2
897: ) is
898: begin
899: if (p_xid is not NULL) then
900: fnd_profile.put('OKC_WEB_REPORT',p_xid);
901: end if;
902: set_env(p_kid);
903: exception
904: when others then NULL;
910: -- public set_env
911: --
912: -- PURPOSE - previous overload with request parameter's set
913: --
914: -- dinamicly executes
916: --
917: procedure set_env(p_array in JTF_VARCHAR2_TABLE_2000) is
918: cursor c1(p_kn varchar2) is
944: end if;
945: end if;
946: -- Changes for bugfix 3404142 start
947: -- set_env(kid, OKC_PARAMETERS_PUB.Get('xid'));
948: set_env(kid, fnd_profile.value('OKC_WEB_REPORT'));
949: -- Changes for bugfix 3404142 end
950: exception
951: when others then NULL;
952: end set_env;
986: -- should be called from FROMS application
987: --
988: -- 1. apps_initialize
989: -- 2. dbms_session.set_nls
990: -- 3. exec
992: -- SQL statements use okc_tree_index.get_root_id as Contact Id
993: --
994:
1028: close nls_csr;
1029: if (l_value<>p_nlst) then
1030: sys.dbms_session.set_nls('NLS_TERRITORY',p_nlst);
1031: end if;
1032: fnd_profile.put('OKC_WEB_REPORT',p_xid);
1033: set_env(p_kid);
1034: exception
1035: when others then NULL;
1036: end set_env;
1042: -- PURPOSE
1043: -- returns url for forms apps to raise report
1044: --
1045: -- JSP:
1046: -- fnd_profile.value('APPS_SERVLET_AGENT')||'OA_HTML/okcFormsXmlPrint.jsp' (previously okcxmlkf.jsp)
1047: --
1048: -- parameters:
1049: --
1050: -- event=FRM
1071: l_dbc varchar2(200);
1072: l_url varchar2(1000);
1073: l_value varchar2(200);
1074: begin
1075: l_path := fnd_profile.value('APPS_SERVLET_AGENT');
1076: select lower(host_name)||'_'||lower(instance_name)||'.dbc' into l_dbc
1077: from v$instance;
1078: l_url := l_path;
1079: if ((l_path is not NULL) and (substr(l_path,-1,1) <> '/')) then
1084: l_url := l_url||'&'||'dbc='||l_dbc;
1085: l_url := l_url||'&'||'kid='||to_char(p_chr_id);
1086: l_url := l_url||'&'||'vid='||to_char(p_major_version);
1087: l_url := l_url||'&'||'sid='||to_char(p_scn_id);
1088: l_url := l_url||'&'||'xid='||fnd_profile.value('OKC_WEB_REPORT');
1089: --
1090: l_url := l_url||'&'||'nlsl=';
1091: select wf_mail.URLENCODE(value) into l_value
1092: from NLS_SESSION_PARAMETERS
1136: --1158 p_scn_id in NUMBER
1137: ) is
1138: l_value varchar2(2000);
1139: begin
1140: l_value := fnd_profile.value('OKC_WEB_PRERUN');
1141: if (l_value is NULL) then
1142: x_return_status := 'S';
1143: return;
1144: end if;
1214: --1158 p_scn_id in NUMBER
1215: ) is
1216: l_value varchar2(2000);
1217: begin
1218: l_value := fnd_profile.value('OKC_WEB_LOG_RUN');
1219: if (l_value is NULL) then
1220: x_return_status := 'S';
1221: return;
1222: end if;
1468: from
1469: ( select XSL_ID,SQL_ID
1470: from OKC_REPORT_XST
1471: connect by ID = prior XST_ID
1472: start with ID = fnd_profile.value('OKC_WEB_REPORT')) T,
1473: OKC_REPORT_XSL_V S
1474: where T.XSL_ID = S.ID
1475: and not(sysdate between S.START_DATE and NVL(S.END_DATE,sysdate))
1476: union all
1480: from
1481: ( select XSL_ID,SQL_ID
1482: from OKC_REPORT_XST
1483: connect by ID = prior XST_ID
1484: start with ID = fnd_profile.value('OKC_WEB_REPORT')) T,
1485: OKC_REPORT_SQL_V Q
1486: where T.SQL_ID = Q.ID
1487: and not(sysdate between Q.START_DATE and NVL(Q.END_DATE,sysdate))
1488: ;
1487: and not(sysdate between Q.START_DATE and NVL(Q.END_DATE,sysdate))
1488: ;
1489: cursor profile_options_csr is
1490: select '"'||USER_PROFILE_OPTION_NAME||'"'
1491: from fnd_profile_options_vl
1492: where application_id=510
1493: and PROFILE_OPTION_NAME = 'OKC_WEB_REPORT'
1494: ;
1495:
1496: l_dummy varchar2(1):='?';
1497: cursor report_exists is
1498: select '!'
1499: from OKC_REPORT_XST T, OKC_REPORT_XSL_V L
1500: where T.ID = fnd_profile.value('OKC_WEB_REPORT')
1501: and T.XSL_ID = L.ID;
1502:
1503: begin
1504: if (fnd_profile.value('OKC_WEB_REPORT') is NULL) then
1500: where T.ID = fnd_profile.value('OKC_WEB_REPORT')
1501: and T.XSL_ID = L.ID;
1502:
1503: begin
1504: if (fnd_profile.value('OKC_WEB_REPORT') is NULL) then
1505: -- open profile_options_csr;
1506: -- fetch profile_options_csr into l_token3;
1507: -- close profile_options_csr;
1508: fnd_message.clear;
1851: procedure set_sql_id is
1852: cursor c_sql_id is
1853: select T.SQL_ID
1854: from OKC_REPORT_XST T, OKC_REPORT_XSL_V L
1855: where T.ID = fnd_profile.value('OKC_WEB_REPORT')
1856: and T.XSL_ID = L.ID;
1857: begin
1858: G_sql_id := null;
1859: if (fnd_profile.value('OKC_WEB_REPORT') is NULL) then
1855: where T.ID = fnd_profile.value('OKC_WEB_REPORT')
1856: and T.XSL_ID = L.ID;
1857: begin
1858: G_sql_id := null;
1859: if (fnd_profile.value('OKC_WEB_REPORT') is NULL) then
1860: OKC_API.SET_MESSAGE(p_app_name => 'OKC'
1861: ,p_msg_name => 'OKC_INACTIVE_TRANSF'
1862: );
1863: return;
2054: cursor c1 is
2055: select replace(prompt,'<',fnd_global.local_chr(38)||'lt;'), lov_query
2056: from okc_report_prm_v PRM, okc_report_xst_v XST
2057: where PRM.sql_id = XST.sql_id
2058: and XST.id = fnd_profile.value('OKC_WEB_REPORT')
2059: and PRM.code = OKC_PARAMETERS_PUB.get('__param_code');
2060: cursor c2 is
2061: select replace(MEANING,'<',fnd_global.local_chr(38)||'lt;') from fnd_lookups
2062: where LOOKUP_TYPE='OKS_ITEM_DISPLAY_PREFERENCE'
2079: close c2;
2080: open c3;
2081: fetch c3 into x_description;
2082: close c3;
2083: if (fnd_profile.value('OKC_WEB_REPORT') is NULL) then
2084: fnd_message.clear;
2085: fnd_message.set_name(APPLICATION=>'CN',NAME=>'CN_NO_RECS_FOUND');
2086: x_message := fnd_message.get;
2087: return;
2146:
2147: P_RECIPIENT varchar2(100) := OKC_PARAMETERS_PUB.get('P_RECIPIENT');
2148: P_SUBJECT varchar2(2000) := OKC_PARAMETERS_PUB.get('P_SUBJECT');
2149: P_BODY varchar2(2000) := OKC_PARAMETERS_PUB.get('P_BODY');
2150: P_XID varchar2(40) := fnd_profile.value('OKC_WEB_REPORT');
2151:
2152: l_dummy varchar2(1) := '?';
2153:
2154: cursor c1(k number) is