[Home] [Help]
PACKAGE BODY: APPS.OZF_GENERATE_XML_CLOB_PVT
Source
1 PACKAGE BODY ozf_generate_xml_clob_pvt AS
2 /* $Header: ozfvxmlb.pls 115.2 2004/03/22 21:23:51 mgudivak noship $ */
3 -- Start of Comments
4 -- Package name : ozf_generate_xml_clob_pvt
5 -- Purpose :
6 -- History : 09-OCT-2003 vansub Created
7 -- NOTE :
8 -- End of Comments
9
10
11
12 FUNCTION generate_offer_clob (p_event_name in varchar2,
13 p_event_key in varchar2,
14 p_parameter_list in wf_parameter_list_t default null)
15 RETURN CLOB
16 IS
17 l_api_name CONSTANT VARCHAR2(30) := 'generate_offer_clob';
18 l_api_version_number CONSTANT NUMBER := 1.0;
19
20 l_ctx NUMBER;
21 l_clob CLOB;
22
23 l_list_header_id VARCHAR2(30);
24 l_sql VARCHAR2(32000);
25
26 BEGIN
27
28 l_list_header_id := wf_event.GetValueForParameter('P_OFFER_ID',p_parameter_list);
29
30 l_sql := 'select offer from ozf_offer_clob_v where list_header_id = :list_header_id';
31
32 dbms_lob.createtemporary(l_clob,true,dbms_lob.session);
33
34 l_ctx := dbms_xmlquery.newContext(l_sql);
35
36 dbms_xmlquery.setbindvalue( l_ctx, 'list_header_id', l_list_header_id );
37 dbms_xmlquery.setRaiseNoRowsException( l_ctx,false );
38
39 l_clob := dbms_xmlquery.getXml(l_ctx);
40
41 dbms_xmlquery.closeContext(l_ctx);
42
43 return l_clob ;
44
45 EXCEPTION
46 WHEN others THEN
47 dbms_xmlquery.closeContext(l_ctx);
48 raise;
49 END generate_offer_clob;
50
51 FUNCTION generate_quota_clob (p_event_name in varchar2,
52 p_event_key in varchar2,
53 p_parameter_list in wf_parameter_list_t default null)
54 RETURN CLOB
55 IS
56 l_api_name CONSTANT VARCHAR2(30) := 'generate_offer_clob';
57 l_api_version_number CONSTANT NUMBER := 1.0;
58
59 l_ctx NUMBER;
60 l_clob CLOB;
61
62 l_fund_id VARCHAR2(30);
63 l_sql VARCHAR2(32000);
64
65 BEGIN
66
67 l_fund_id := wf_event.GetValueForParameter('P_FUND_ID',p_parameter_list);
68
69 l_sql := 'select quota from ozf_quota_clob_v where fund_id = :fund_id';
70
71 dbms_lob.createtemporary(l_clob,true,dbms_lob.session);
72
73 l_ctx := dbms_xmlquery.newContext(l_sql);
74
75 dbms_xmlquery.setbindvalue( l_ctx, 'fund_id', l_fund_id );
76 dbms_xmlquery.setRaiseNoRowsException( l_ctx,false );
77
78 l_clob := dbms_xmlquery.getXml(l_ctx);
79
80 dbms_xmlquery.closeContext(l_ctx);
81
82 return l_clob;
83
84 EXCEPTION
85 WHEN others THEN
86 dbms_xmlquery.closeContext(l_ctx);
87 raise;
88 END generate_quota_clob;
89
90 FUNCTION generate_target_clob (p_event_name in varchar2,
91 p_event_key in varchar2,
92 p_parameter_list in wf_parameter_list_t default null)
93 RETURN CLOB
94 IS
95 l_api_name CONSTANT VARCHAR2(30) := 'generate_target_clob';
96 l_api_version_number CONSTANT NUMBER := 1.0;
97
98 l_ctx NUMBER;
99 l_clob CLOB;
100
101 l_account_allocation_id VARCHAR2(30);
102 l_sql VARCHAR2(32000);
103
104 BEGIN
105
106 l_account_allocation_id := wf_event.GetValueForParameter('P_ACCOUNT_ALLOCATION_ID',p_parameter_list);
107
108 l_sql := 'select target from ozf_target_clob_v where account_allocation_id = :account_allocation_id';
109
110 dbms_lob.createtemporary(l_clob,true,dbms_lob.session);
111
112 l_ctx := dbms_xmlquery.newContext(l_sql);
113
114 dbms_xmlquery.setbindvalue( l_ctx, 'account_allocation_id', l_account_allocation_id );
115 dbms_xmlquery.setRaiseNoRowsException( l_ctx,false );
116
117 l_clob := dbms_xmlquery.getXml(l_ctx);
118
119 dbms_xmlquery.closeContext(l_ctx);
120
121 return l_clob;
122
123 EXCEPTION
124 WHEN others THEN
125 dbms_xmlquery.closeContext(l_ctx);
126 raise;
127 END generate_target_clob;
128
129 FUNCTION test(p_subscription_guid in raw,
130 p_event in out nocopy wf_event_t) return varchar2
131 IS
132 l_parameter_list wf_parameter_list_t;
133 l_offer_id VARCHAR2(30);
134
135 l_event_id VARCHAR2(30);
136 l_msg CLOB;
137 l_out NUMBER;
138
139 BEGIN
140
141 --Get parameters:
142
143 l_parameter_list := WF_PARAMETER_LIST_T();
144 l_parameter_list := p_event.GetParameterList;
145
146 l_offer_id := wf_event.GetValueForParameter('P_OFFER_ID',l_parameter_list);
147
148 l_out := dbms_lob.istemporary(p_event.GetEventData());
149
150 l_msg := p_event.GetEventData();
151
152
153
154
155
156 -- insert into ozf_events values(l_offer_id, sysdate, l_msg);
157
158
159 return 'SUCCESS';
160 /*
161 EXCEPTION
162 when others then
163 WF_CORE.CONTEXT('aml_import_event', 'test', p_event.getEventName(), p_subscription_guid);
164 WF_EVENT.setErrorInfo(p_event, 'ERROR');
165 raise;
166 return 'ERROR';
167 */
168 END test;
169
170 END ozf_generate_xml_clob_pvt;