1 PACKAGE HZ_BES_BO_UTIL_PKG AUTHID CURRENT_USER AS
2 /*$Header: ARHBESUS.pls 120.7 2006/05/01 19:03:42 smattegu noship $ */
3
4 ----------------------------------------------------------------------------
5 -- Purpose: Utility package for all the business events code
6 ----------------------------------------------------------------------------
7 -- Global variable to store the profile value
8 G_BO_PROF_VAL VARCHAR2(30);
9 G_ORG_BO_ENABLED BOOLEAN;
10 G_PER_BO_ENABLED BOOLEAN;
11 G_ORG_CUST_BO_ENABLED BOOLEAN;
12 G_PER_CUST_BO_ENABLED BOOLEAN;
13 -- Global variable to store the creation_date that must be populated in BOT
14 G_CREATION_DATE DATE;
15 ----------------------------------------------------------------------------
16 TYPE NUMBER_TBLTYPE IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
17 TYPE VCHAR2_30_TBLTYPE IS TABLE OF VARCHAR2(30) INDEX BY PLS_INTEGER;
18 ----------------------------------------------------------------------------
19 PROCEDURE set_prof_var;
20 ----------------------------------------------------------------------------
21 FUNCTION get_prof_val RETURN VARCHAR2;
22 ----------------------------------------------------------------------------
23
24 -- this is called from BOD Update API to figure out the BO_Version_number
25
26 PROCEDURE entity_in_bo (
27 p_bo_code IN VARCHAR2,
28 p_ebo_code IN VARCHAR2,
29 p_child_bo_code IN VARCHAR2,
30 p_entity_name IN VARCHAR2,
31 x_return_status OUT NOCOPY BOOLEAN);
32
33 /*
34 FUNCTION entity_in_bo
35 ( p_bo_code IN VARCHAR2,
36 p_child_bo_code IN VARCHAR2,
37 p_entity_name IN VARCHAR2) RETURN BOOLEAN;
38 */
39 ----------------------------------------------------------------------------
40 /*
41 The populate_missing_links procedure is an umbrella procedure on top of all
42 explode entity level procedures.
43 */
44 PROCEDURE populate_missing_links (p_creation_date IN DATE);
45 ------------------------------------------------------------------------------
46 /*
47 Procedure name: upd_bot_evt_id ()
48 Scope: external
49 Purpose: This procedure will update the entire BOT hierarchy with
50 event_id provided. This must be done because, only when the event_id
51 is populated,
52 1. V2 APIs will wriet new record for any given entity
53 2. BO extract API can extract the BO hierarchy
54 Called From: This is called from HZ_BES_BO_RAISE_PKG
55 Called By: bes_main()
56 Input parameters:
57 p_bulk_evt in BOOLEAN, -- TRUE if bulk event was raised by bes_main()
58 In case of bulk event raising, there will be 8 distinct event ids for 8 events.
59 If one event was raised per business object instance, then, event_id
60 is obtained from the HZ_BES_GT table.
61 p_per_ins_evt_id IN NUMBER,
62 p_per_upd_evt_id IN NUMBER,
63 p_org_ins_evt_id IN NUMBER,
64 p_org_upd_evt_id IN NUMBER,
65 p_perc_ins_evt_id IN NUMBER,
66 p_perc_upd_evt_id IN NUMBER,
67 p_orgc_ins_evt_id IN NUMBER,
68 p_orgc_upd_evt_id IN NUMBER
69
70 */
71 PROCEDURE upd_bot_evt_id (
72 p_bulk_evt in BOOLEAN,
73 p_evt_id IN NUMBER,
74 p_child_id IN NUMBER,
75 p_child_bo_code IN VARCHAR2,
76 p_per_ins_evt_id IN NUMBER,
77 p_per_upd_evt_id IN NUMBER,
78 p_org_ins_evt_id IN NUMBER,
79 p_org_upd_evt_id IN NUMBER,
80 p_perc_ins_evt_id IN NUMBER,
81 p_perc_upd_evt_id IN NUMBER,
82 p_orgc_ins_evt_id IN NUMBER,
83 p_orgc_upd_evt_id IN NUMBER);
84 ------------------------------------------------------------------------------
85 /*
86 Procedure name: upd_bot_evtid_dt ()
87 Scope: external
88 Purpose: This procedure will update the entire BOT hierarchy with
89 event_id, creation_date provided. This must be done because,
90 only when the event_id is populated,
91 1. V2 APIs will write new record for any given entity
92 2. BO extract API can extract the BO hierarchy
93 Called From: This is called from HZ_BES_BO_RAISE_PKG
94 Called By: bes_main()
95 Input parameters:
96 p_bulk_evt in BOOLEAN, -- TRUE if bulk event was raised by bes_main()
97 In case of bulk event raising, there will be 8 distinct event ids for 8 events.
98 If one event was raised per business object instance, then, event_id
99 is obtained from the HZ_BES_GT table.
100 p_creation_date IN DATE, -- used to update the creation_date column in BOT
101 p_per_ins_evt_id IN NUMBER,
102 p_per_upd_evt_id IN NUMBER,
103 p_org_ins_evt_id IN NUMBER,
104 p_org_upd_evt_id IN NUMBER,
105 p_perc_ins_evt_id IN NUMBER,
106 p_perc_upd_evt_id IN NUMBER,
107 p_orgc_ins_evt_id IN NUMBER,
108 p_orgc_upd_evt_id IN NUMBER
109
110 */
111 PROCEDURE upd_bot_evtid_dt (
112 p_bulk_evt in BOOLEAN, -- whether bulk event was raised (TRUE) or not (FALSE)
113 p_evt_id IN NUMBER, -- only used for one event per object instance
114 p_child_id IN NUMBER, -- only used for one event per object instance
115 p_child_bo_code IN VARCHAR2, -- only used for one event per object instance
116 p_creation_date IN DATE, -- used to update the creation_date column in BOT
117 p_evt_type IN VARCHAR2, -- this is to pass the event type (Bug4773627)
118 p_commit IN BOOLEAN, -- to control commit or rolllback when called from v3 api(Bug4957408)
119 p_per_ins_evt_id IN NUMBER,
120 p_per_upd_evt_id IN NUMBER,
121 p_org_ins_evt_id IN NUMBER,
122 p_org_upd_evt_id IN NUMBER,
123 p_perc_ins_evt_id IN NUMBER,
124 p_perc_upd_evt_id IN NUMBER,
125 p_orgc_ins_evt_id IN NUMBER,
126 p_orgc_upd_evt_id IN NUMBER);
127 ------------------------------------------------------------------------------
128 /*
129 Procedure name: upd_hzp_bo_ver ()
130 Scope: external
131 Purpose: This procedure will update the hz_parties table with all the
132 latest bo_version_numbers. This must be done to short circuit the
133 process for figuring out event type when the object is touched next time.
134 Called From: This is called from HZ_BES_BO_RAISE_PKG
135 Called By: bes_main()
136 Input parameters:
137 p_per_bo_ver IN NUMBER -- for passing the person bo version number
138 p_org_bo_ver IN NUMBER -- for passing the org bo version number
139 p_perc_bo_ver IN NUMBER -- for passing the person cust bo version number
140 p_orgc_bo_ver IN NUMBER -- for passing the org cust bo version number
141 */
142 PROCEDURE upd_hzp_bo_ver (
143 p_per_bo_ver IN NUMBER, -- for passing the person bo version number
144 p_org_bo_ver IN NUMBER, -- for passing the org bo version number
145 p_perc_bo_ver IN NUMBER, -- for passing the person cust bo version number
146 p_orgc_bo_ver IN NUMBER);-- for passing the org cust bo version number
147 ----------------------------------------------------------------------------
148 /*
149 Procedure name: del_bot()
150 Scope: external
151 Purpose: This procedure will delete the records that were already processed
152 by subscriptions.
153 Called By: Cleanse Concurrent Program
154 Input parameters:
155 p_cutoff_dt IN DATE);-- for passing the cutoff date for deleting the recs in BOT
156 */
157 PROCEDURE del_bot (
158 p_cutoff_dt IN DATE);-- for passing the cutoff date for deleting the recs in BOT
159 ----------------------------------------------------------------------------
160 /*
161 Procedure name: del_duplicate_rows()
162 Scope: external
163 Purpose: This procedure will delete any duplicate rows that were
164 entered by the populate function.
165 It is possible for populate function to enter duplicate rows
166 in cases where TCA APIs are called from concurrent requests that run in parallel.
167 If the parallel running concurrent requests try to create or update the same
168 set of data using the same primary keys, then, populate function running in
169 different thread will end up writing identical rows from each thread.
170 Once the concyurrent requests complete successfully, this will result in
171 duplicate rows in BOT table.
172 Introducing the Unique Index in BOT will not help because, this would result in
173 Concurrent requests to fail.
174 The alternative is to delete the duplicate rows.
175 Called By: Raise Concurrent Program
176 */
177 procedure del_duplicate_rows ;
178 ----------------------------------------------------------------------------
179 /*
180 Procedure name: del_obj_hierarchy()
181 Scope: external
182 Purpose: Given a root object identifier, this procedure
183 will delete the entire hierarchy.
184 This procedure is called from
185 1. party purge concurrent program
186 2. account merge concurrent program with delete option.
187
188 In these two cases, as the entire party and its detailed records are
189 purged, there is no use of maintaining those records in the BOT.
190 If the purged records are left in BOT without deleting:
191 1. There is a chance that an event may be raised for already
192 purged record. Functionally, this would be incorrect.
193 2. The Raise Events concurrent program may error out
194 as it cannot find the party record in TCA Registry.
195 This is needed for updating the party BO_VERSION columns to
196 be updated after raising the event.
197
198 Called By:
199 1. party purge concurrent program
200 2. account merge concurrent program with delete option.
201
202 Input:
203 BO Code: PERSON for Person BO,
204 ORG for Organization BO,
205 PERSON_CUST for Person Customer BO
206 ORG_CUST for Organization Customer BO
207 Object Identifier: Root Object Id (party id).
208
209 */
210 procedure del_obj_hierarchy
211 ( P_OBJ_ID IN NUMBER);
212 ----------------------------------------------------------------------------
213 /*
214 Procedure name: upd_entity_ids()
215 Scope: external
216 Purpose: Given a root object identifier, this procedure
217 will delete the entire hierarchy.
218 This procedure is called from
219 1. party merge concurrent program
220 2. account merge concurrent program
221
222 In these two cases, the entity ids are changed in TCA REgistry by
223 the above concurrent programs.
224 This procedure reflects the id changes in the BOT.
225 This changed ids will enable the BO extraction API to get to the
226 action types of the changed entities in BOT.
227 Note - this method does not handle RA_CUST_RECEIPT_METHODS and
228 IBY_FNDCPT_PAYER_ASSGN_INSTR_V.
229 This method only handles update of identifiers for HZ tables.
230 Called By:
231 1. party merge concurrent program
232 2. account merge concurrent program
233
234 Input:
235 request id: Concurrent Request Identifier
236 */
237 procedure upd_entity_ids
238 ( P_request_id IN NUMBER);
239 ----------------------------------------------------------------------------
240 ----------------------------------------------------------------------------
241 END HZ_BES_BO_UTIL_PKG; -- Package spec