1 PACKAGE HZ_BES_BO_GEN_PKG AS
2 /*$Header: ARHBESGS.pls 120.4 2006/06/28 07:10:55 smattegu noship $ */
3
4 /* following are the variables used for generating SQL for event type SQL
5 root node.
6 */
7
8 G_EVT_RT_NODE_0 VARCHAR2(125) := 'UPDATE hz_bes_gt SET event_type_flag = ''U''
9 WHERE event_type_flag IS NULL AND bo_code = ';
10
11 G_EVT_RT_NODE_1 VARCHAR2(200) := ' AND party_id IN ( SELECT P_PARTY_ID FROM HZ_PARTIES P,
12 (SELECT t.party_id P_PARTY_ID, NVL(MIN(t.last_update_date),SYSDATE) P_CUTOFF_DT
13 FROM (SELECT connect_by_root';
14
15 G_EVT_RT_NODE_2 VARCHAR2(255) := ' tr.child_id party_id, tr.last_update_date
16 FROM hz_bus_obj_tracking tr START WITH tr.parent_bo_code IS NULL
17 AND EVENT_ID IS NULL AND tr.child_bo_code = ';
18
19 G_EVT_RT_NODE_3 VARCHAR2(255) := ' AND tr.child_id IN (SELECT gt.party_id
20 FROM hz_bes_gt gt WHERE gt.event_type_flag IS NULL AND gt.bo_code = ';
21
22 G_EVT_RT_NODE_4 VARCHAR2(255) := ' )
23 CONNECT BY tr.parent_bo_code = PRIOR tr.child_bo_code AND
24 tr.parent_id = PRIOR tr.child_id) t
25 GROUP BY party_id) t
26 WHERE P.PARTY_ID = P_PARTY_ID
27 AND CREATION_DATE < P_CUTOFF_DT ';
28
29 G_LUD VARCHAR2(50) := ' AND CREATION_DATE < P_CUTOFF_DT ';
30 /*
31 The G_RT_NODE1, 2 are used only for Person and Org Cust BO.
32 For Per and Org, the delete statement must also delete the corresponding
33 Cust business objects provided the gt.evet_type_flag is null.
34 This can be acheived with following variables.
35 Some of these variable names end with PO to denote that this is used for Person and Org.
36 G_RT_NODE_1_PO
37 G_RT_NODE_2_PO
38 G_RT_NODE_3_PO
39 G_RT_NODE_4_PO
40 G_RT_NODE_2
41 G_RT_NODE_BOCODE
42 */
43 G_RT_NODE_1_PO VARCHAR2(80) := ' DELETE FROM HZ_BES_GT T1 WHERE T1.EVENT_TYPE_FLAG IS NULL
44 AND (T1.BO_CODE = ';
45 G_RT_NODE_2_PO VARCHAR2(20) := ' OR (T1.BO_CODE = ';
46 G_RT_NODE_3_PO VARCHAR2(100) := ' AND EXISTS ( SELECT 1 FROM HZ_BES_GT T2 WHERE
47 T2.PARTY_ID = T1.PARTY_ID AND T2.BO_CODE = ';
48 G_RT_NODE_4_PO VARCHAR2(40) := ' AND T2.EVENT_TYPE_FLAG IS NULL )))';
49
50 G_RT_NODE_1 VARCHAR2(80) := ' DELETE FROM HZ_BES_GT WHERE EVENT_TYPE_FLAG IS NULL ';
51 G_RT_NODE_BOCODE VARCHAR2(35) := ' AND BO_CODE = ';
52 G_RT_NODE_BOCODE2 VARCHAR2(35) := ' AND BO_CODE IN( ';
53 G_RT_NODE_2 VARCHAR2(120) := ' AND PARTY_ID NOT IN (
54 SELECT P.PARTY_ID
55 FROM HZ_BES_GT P
56 WHERE P.EVENT_TYPE_FLAG IS NULL ';
57
58 G_HZ_CERT_P VARCHAR2(200) :=
59 ' AND EXISTS
60 (SELECT 1
61 FROM HZ_CERTIFICATIONS CER
62 WHERE CER.PARTY_ID = P.PARTY_ID';
63
64 G_HZ_CITIZEN_P VARCHAR2(200) :=
65 ' AND EXISTS
66 (SELECT 1
67 FROM HZ_CITIZENSHIP CIT
68 WHERE CIT.PARTY_ID = P.PARTY_ID';
69 -- HZ_CODE_ASSIGNMENTS can have Org or Person as parents
70 G_HZ_CODE_ASSIGN_P VARCHAR2(200) := ' AND EXISTS
71 (SELECT 1
72 FROM HZ_CODE_ASSIGNMENTS CA
73 WHERE CA.OWNER_TABLE_NAME = ''HZ_PARTIES'' AND
74 CA.OWNER_TABLE_ID = P.PARTY_ID';
75
76 -- contact point types are
77 -- EDI, EFT, EMAIL, PHONE, WEB, TLX, SMS
78
79 G_CP_EFT VARCHAR2(15) := '''EFT''';
80 G_CP_EDI VARCHAR2(15) := '''EDI''';
81 G_CP_EMAIL VARCHAR2(15) := '''EMAIL''';
82 G_CP_PHONE VARCHAR2(15) := '''PHONE''';
83 G_CP_WEB VARCHAR2(15) := '''WEB''';
84 G_CP_TLX VARCHAR2(15) := '''TLX''';
85 G_CP_SMS VARCHAR2(15) := '''SMS''';
86
87 -- following are the variables for various contact points
88 -- where parent is org or person
89
90 -- CP for party
91 G_CP_P1 VARCHAR2(200) := ' AND EXISTS
92 (SELECT 1
93 FROM HZ_CONTACT_POINTS CP
94 WHERE CP.OWNER_TABLE_NAME = ''HZ_PARTIES'' AND
95 CP.OWNER_TABLE_ID = P.PARTY_ID AND
96 CP.CONTACT_POINT_TYPE = ';
97
98 G_CP_P1_ET1 VARCHAR2(220) := ' AND EXISTS
99 (SELECT 1
100 FROM HZ_CONTACT_POINTS CP
101 WHERE CP.OWNER_TABLE_NAME = ''HZ_PARTIES'' AND
102 CP.CREATION_DATE <P_CUTOFF_DT AND CP.OWNER_TABLE_ID = P.PARTY_ID AND
103 CP.CONTACT_POINT_TYPE = ';
104
105 -- CP for org contact
106 G_CP_REL1 VARCHAR2(250) := ' AND EXISTS
107 (SELECT 1
108 FROM HZ_CONTACT_POINTS CP, HZ_RELATIONSHIPS R
109 WHERE CP.OWNER_TABLE_NAME = ''HZ_PARTIES'' AND R.RELATIONSHIP_ID = OC.PARTY_RELATIONSHIP_ID
110 AND CP.OWNER_TABLE_ID = R.PARTY_ID AND
111 CP.CONTACT_POINT_TYPE = ';
112
113 G_CP_REL1_ET1 VARCHAR2(200) := ' AND EXISTS
114 (SELECT 1
115 FROM HZ_CONTACT_POINTS CP, HZ_RELATIONSHIPS R
116 WHERE CP.OWNER_TABLE_NAME = ''HZ_PARTIES'' AND
117 R.RELATIONSHIP_ID = OC.PARTY_RELATIONSHIP_ID';
118 G_CP_REL1_ET2 VARCHAR2(150) := ' AND CP.OWNER_TABLE_ID = R.PARTY_ID AND
119 CP.CREATION_DATE <P_CUTOFF_DT AND CP.CONTACT_POINT_TYPE = ';
120
121 -- CP for PS
122 G_CP_PS1 VARCHAR2(200) := ' AND EXISTS
123 (SELECT 1
124 FROM HZ_CONTACT_POINTS CP
125 WHERE CP.OWNER_TABLE_NAME = ''HZ_PARTY_SITES'' AND
126 CP.OWNER_TABLE_ID = PS.PARTY_SITE_ID AND CP.CONTACT_POINT_TYPE = ';
127
128 G_CP_PS1_ET1 VARCHAR2(225) := ' AND EXISTS
129 (SELECT 1
130 FROM HZ_CONTACT_POINTS CP
131 WHERE CP.OWNER_TABLE_NAME = ''HZ_PARTY_SITES'' AND
132 CP.OWNER_TABLE_ID = PS.PARTY_SITE_ID AND CP.CREATION_DATE <P_CUTOFF_DT
133 AND CP.CONTACT_POINT_TYPE = ';
134
135 -- HZ_CONTACT_PREFERENCES can have Org, Person, Contact, PS, CP as parents
136 -- for person, org as parents - use the following
137
138 G_HZ_CONT_PREF_P VARCHAR2(175) := ' AND EXISTS
139 (SELECT 1
140 FROM HZ_CONTACT_PREFERENCES CONTP
141 WHERE CONTP.CONTACT_LEVEL_TABLE = ''HZ_PARTIES'' AND
142 CONTP.CONTACT_LEVEL_TABLE_ID = P.PARTY_ID';
143
144 -- for Contact - use the following
145 G_HZ_CONT_PREF_REL VARCHAR2(250) := ' AND EXISTS
146 (SELECT 1
147 FROM HZ_CONTACT_PREFERENCES CONTP, HZ_RELATIONSHIPS R
148 WHERE CONTP.CONTACT_LEVEL_TABLE = ''HZ_PARTIES'' AND
149 CONTP.CONTACT_LEVEL_TABLE_ID = R.PARTY_ID AND
150 R.RELATIONSHIP_ID = OC.PARTY_RELATIONSHIP_ID';
151
152 G_HZ_CONT_PREF_REL_ET1 VARCHAR2(200) := ' AND EXISTS
153 (SELECT 1
154 FROM HZ_CONTACT_PREFERENCES CONTP, HZ_RELATIONSHIPS R
155 WHERE CONTP.CONTACT_LEVEL_TABLE = ''HZ_PARTIES'' AND
156 CONTP.CONTACT_LEVEL_TABLE_ID = R.PARTY_ID AND';
157
158
159 G_HZ_CONT_PREF_REL_ET2 VARCHAR2(100) := '
160 R.RELATIONSHIP_ID = OC.PARTY_RELATIONSHIP_ID AND
161 CONTP.CREATION_DATE <P_CUTOFF_DT ';
162
163
164 -- for PS - use the following
165 G_HZ_CONT_PREF_PS VARCHAR2(200) := ' AND EXISTS
166 (SELECT 1
167 FROM HZ_CONTACT_PREFERENCES CONTP
168 WHERE CONTP.CONTACT_LEVEL_TABLE = ''HZ_PARTY_SITES'' AND
169 CONTP.CONTACT_LEVEL_TABLE_ID = PS.PARTY_SITE_ID';
170
171 -- for CP - use the following
172 G_HZ_CONT_PREF_CP VARCHAR2(200) := ' AND EXISTS
173 (SELECT 1
174 FROM HZ_CONTACT_PREFERENCES contp
175 WHERE contp.CONTACT_LEVEL_TABLE = ''HZ_CONTACT_POINTS'' AND
176 contp.CONTACT_LEVEL_TABLE_ID = CP.CONTACT_POINT_ID';
177
178 -- HZ_CREDIT_RATINGS can have only Org as parent
179 G_HZ_CREDIT_RATINGS_P VARCHAR2(100) := ' AND EXISTS
180 (SELECT 1
181 FROM HZ_CREDIT_RATINGS CR
182 WHERE CR.party_id = P.PARTY_ID';
183
184 -- HZ_CUST_ACCOUNT_ROLES Can have Account, Account Site as parent
185 -- for Account as Parent use
186 G_HZ_CUST_ACCT_ROLES_A VARCHAR2(150) := ' AND EXISTS
187 (SELECT 1
188 FROM HZ_CUST_ACCOUNT_ROLES ACR
189 WHERE ACR.CUST_ACCOUNT_ID = AC.CUST_ACCOUNT_ID';
190
191 -- for Account Site as parent
192 G_HZ_CUST_ACCT_ROLES_AS VARCHAR2(150) := ' AND EXISTS
193 (SELECT 1
194 FROM HZ_CUST_ACCOUNT_ROLES ACR
195 WHERE ACR.CUST_ACCT_SITE_ID = ASA.CUST_ACCT_SITE_ID';
196
197 -- HZ_CUST_ACCOUNTS can have only Org or Person as parent
198 G_HZ_CUST_ACCTS_P VARCHAR2(100) := ' AND EXISTS
199 (SELECT 1
200 FROM HZ_CUST_ACCOUNTS AC
201 WHERE AC.party_id = P.PARTY_ID';
202
203 -- HZ_CUST_ACCT_RELATE_ALL can only have cust account as parent
204 G_HZ_CUST_ACCT_REL_P VARCHAR2(150) := ' AND EXISTS
205 (SELECT 1
206 FROM HZ_CUST_ACCT_RELATE_ALL ACREL
207 WHERE ACREL.CUST_ACCOUNT_ID = AC.CUST_ACCOUNT_ID';
208
209 -- HZ_CUST_ACCT_SITES_ALL have Cust Account as parent
210 G_HZ_CUST_ACCT_SITES_A VARCHAR2(150) := ' AND EXISTS
211 (SELECT 1
212 FROM HZ_CUST_ACCT_SITES_ALL ASA
213 WHERE ASA.CUST_ACCOUNT_ID = AC.CUST_ACCOUNT_ID';
214
215 -- HZ_CUST_PROFILE_AMTS Can have Account Profile as parent
216 G_HZ_CUST_PROF_AMTS_AP VARCHAR2(150) := ' AND EXISTS
217 (SELECT 1
218 FROM HZ_CUST_PROFILE_AMTS CPA
219 WHERE CPA.CUST_ACCOUNT_PROFILE_ID = CAP.CUST_ACCOUNT_PROFILE_ID';
220
221 -- HZ_CUST_SITE_USES_ALL Can have Account Site as parent
222 -- for Account Site as parent
223 G_HZ_CUST_SITE_USES_AS VARCHAR2(150) := ' AND EXISTS
224 (SELECT 1
225 FROM HZ_CUST_SITE_USES_ALL ACSU
226 WHERE ACSU.CUST_ACCT_SITE_ID = ASA.CUST_ACCT_SITE_ID';
227
228 -- HZ_CUSTOMER_PROFILES Can have Cust Account, Account Site Use as parents
229 -- for Cust Account as parent
230 G_HZ_CUST_PROF_A VARCHAR2(150) := ' AND EXISTS
231 (SELECT 1
232 FROM HZ_CUSTOMER_PROFILES CAP
233 WHERE CAP.CUST_ACCOUNT_ID = AC.CUST_ACCOUNT_ID';
234
235 -- for Cust Account Site Use as parent
236 G_HZ_CUST_PROF_ASU VARCHAR2(150) := ' AND EXISTS
237 (SELECT 1
238 FROM HZ_CUSTOMER_PROFILES CAP
242 -- for Person as parent
239 WHERE CAP.SITE_USE_ID = ACSU.SITE_USE_ID';
240
241 -- HZ_EDUCATION Can have Person as parent
243 G_HZ_EDU_P VARCHAR2(100) := ' AND EXISTS
244 (SELECT 1
245 FROM HZ_EDUCATION E
246 WHERE E.PARTY_ID = P.PARTY_ID';
247
248 -- HZ_EMPLOYMENT_HISTORY Can have Person as parent
249 -- for Person as parent
250 G_HZ_EMP_HIST_P VARCHAR2(100) := ' AND EXISTS
251 (SELECT 1
252 FROM HZ_EMPLOYMENT_HISTORY EH
253 WHERE EH.PARTY_ID = P.PARTY_ID';
254
255 -- HZ_FINANCIAL_NUMBERS Can have Financial reports as parent
256 -- for Financial reports as parent
257 G_HZ_FIN_NUM_FR VARCHAR2(150) := ' AND EXISTS
258 (SELECT 1
259 FROM HZ_FINANCIAL_NUMBERS FN
260 WHERE FN.FINANCIAL_REPORT_ID = FR.FINANCIAL_REPORT_ID';
261
262 -- HZ_FINANCIAL_PROFILE can have Person,Org as parent
263 -- for person or org as parent
264 G_HZ_FIN_PROF_P VARCHAR2(100) := ' AND EXISTS
265 (SELECT 1
266 FROM HZ_FINANCIAL_PROFILE FP
267 WHERE FP.PARTY_ID = P.PARTY_ID';
268
269 -- HZ_FINANCIAL_REPORTS can only have Org as parent
270 -- for org as parent
271 G_HZ_FIN_REP_P VARCHAR2(100) := ' AND EXISTS
272 (SELECT 1
273 FROM HZ_FINANCIAL_REPORTS FR
274 WHERE FR.PARTY_ID = P.PARTY_ID';
275
276 -- HZ_LOCATIONS can only have PS as parent
277 -- for PS as parent
278 G_HZ_LOC_PS VARCHAR2(100) := ' AND EXISTS
279 (SELECT 1
280 FROM HZ_LOCATIONS L
281 WHERE L.LOCATION_ID = PS.LOCATION_ID';
282
283 -- HZ_ORG_CONTACT_ROLES can only have Org Contact as parent
284 -- for Org Contact as parent
285 G_HZ_ORG_CONT_ROLE_OC VARCHAR2(150) := ' AND EXISTS
286 (SELECT 1
287 FROM HZ_ORG_CONTACT_ROLES OCR
288 WHERE OCR.ORG_CONTACT_ID = OC.ORG_CONTACT_ID';
289
290 -- HZ_ORG_CONTACTS Can have Org,Person, Cust Acct Contact as parents
291 -- for Org as parent. Please note that SQL is split into 3 variables.
292 -- This is because of an inherent limitation on underlying HZ_GEN_PLSQL pkg.
293 G_HZ_ORG_CONT_P1 VARCHAR2(235) := ' AND EXISTS
294 (SELECT 1
295 FROM HZ_ORG_CONTACTS OC, HZ_RELATIONSHIPS R
296 WHERE OC.PARTY_RELATIONSHIP_ID = R.RELATIONSHIP_ID
297 AND R.SUBJECT_TYPE =''PERSON'' AND R.SUBJECT_TABLE_NAME = R.OBJECT_TABLE_NAME ';
298 G_HZ_ORG_CONT_P1_ORG VARCHAR2(50) := ' AND R.OBJECT_TYPE = ''ORGANIZATION''';
299 G_HZ_ORG_CONT_P1_PER VARCHAR2(50) := ' AND R.OBJECT_TYPE = ''PERSON''';
300
301 G_HZ_ORG_CONT_P2 VARCHAR2(150) := ' AND R.OBJECT_TABLE_NAME = ''HZ_PARTIES''
302 AND R.OBJECT_ID = P.PARTY_ID ';
303 G_HZ_ORG_CONT_P2_ET1 VARCHAR2(200) :=' AND R.OBJECT_TABLE_NAME = ''HZ_PARTIES'' AND
304 R.OBJECT_ID = P.PARTY_ID AND OC.CREATION_DATE <P_CUTOFF_DT ';
305 /*
306 G_HZ_ORG_CONT_P2 VARCHAR2(225) := ' AND R.OBJECT_TABLE_NAME = ''HZ_PARTIES''
307 AND R.OBJECT_ID = P.PARTY_ID AND EXISTS (SELECT 1 FROM
308 HZ_CODE_ASSIGNMENTS CODE, HZ_RELATIONSHIP_TYPES RELTYPE
309 WHERE RELTYPE.RELATIONSHIP_TYPE = R.RELATIONSHIP_TYPE ';
310
311 G_HZ_ORG_CONT_P2_ET1 VARCHAR2(253) :=' AND R.OBJECT_TABLE_NAME = ''HZ_PARTIES'' AND
312 R.OBJECT_ID = P.PARTY_ID AND OC.CREATION_DATE <P_CUTOFF_DT AND EXISTS
313 (SELECT 1 FROM HZ_CODE_ASSIGNMENTS CODE,HZ_RELATIONSHIP_TYPES RELTYPE
314 WHERE RELTYPE.RELATIONSHIP_TYPE = R.RELATIONSHIP_TYPE ';
315
316 G_HZ_ORG_CONT_P3 VARCHAR2(250) := ' AND CODE.OWNER_TABLE_ID = RELTYPE.RELATIONSHIP_TYPE_ID
317 AND CODE.CLASS_CATEGORY = ''RELATIONSHIP_TYPE_GROUP''
318 AND CODE.CLASS_CODE = ''PARTY_REL_GRP_CONTACTS''
319 AND CODE.OWNER_TABLE_NAME = ''HZ_RELATIONSHIP_TYPES'')';
320 */
321 -- for Cust Account Contact as parent
322 G_HZ_ORG_CONT_AC1 VARCHAR2(250) := ' AND EXISTS
323 (SELECT 1
324 FROM HZ_ORG_CONTACTS OC, HZ_RELATIONSHIPS R, HZ_CUST_ACCOUNTS AC
325 WHERE OC.PARTY_RELATIONSHIP_ID = R.RELATIONSHIP_ID AND R.PARTY_ID = ACR.PARTY_ID
326 AND AC.CUST_ACCOUNT_ID = ACR.CUST_ACCOUNT_ID AND R.SUBJECT_TYPE = ''PERSON'' ';
327
328 G_HZ_ORG_CONT_AC2 VARCHAR2(225) := ' AND R.OBJECT_TYPE = ''ORGANIZATION''
329 AND R.SUBJECT_TABLE_NAME = R.OBJECT_TABLE_NAME AND R.OBJECT_TABLE_NAME = ''HZ_PARTIES''
330 AND EXISTS (SELECT 1
331 FROM HZ_CODE_ASSIGNMENTS CODE, HZ_RELATIONSHIP_TYPES RELTYPE';
332
333 G_HZ_ORG_CONT_AC2_ET1 VARCHAR2(250) :=' AND R.OBJECT_TYPE = ''ORGANIZATION''
334 AND R.SUBJECT_TABLE_NAME = R.OBJECT_TABLE_NAME AND R.OBJECT_TABLE_NAME = ''HZ_PARTIES''
335 AND OC.CREATION_DATE <P_CUTOFF_DT AND EXISTS (SELECT 1
336 FROM HZ_CODE_ASSIGNMENTS CODE, HZ_RELATIONSHIP_TYPES RELTYPE';
337
338 G_HZ_ORG_CONT_AC3 VARCHAR2(235) := ' WHERE RELTYPE.RELATIONSHIP_TYPE = R.RELATIONSHIP_TYPE
339 AND CODE.OWNER_TABLE_ID = RELTYPE.RELATIONSHIP_TYPE_ID
340 AND CODE.CLASS_CATEGORY = ''RELATIONSHIP_TYPE_GROUP''
341 AND CODE.CLASS_CODE = ''PARTY_REL_GRP_CONTACTS''';
342
343 G_HZ_ORG_CONT_AC4 VARCHAR2(60) := ' AND CODE.OWNER_TABLE_NAME = ''HZ_RELATIONSHIP_TYPES'')';
344
345 -- HZ_ORGANIZATION_PROFILES can only have Org as parent
346 -- for Org as parent
347 G_HZ_ORG_PROF_P VARCHAR2(100) := ' AND EXISTS
348 (SELECT 1
349 FROM HZ_ORGANIZATION_PROFILES OP
350 WHERE OP.PARTY_ID=P.PARTY_ID';
351
352 -- HZ_PARTIES is the parent - in the context of Person and Org
353 -- P_PARTY_ID is the parameter that would be passed by the caller
354 G_HZ_PARTIES VARCHAR2(100) := ' SELECT P.PARTY_ID
355 FROM HZ_PARTIES P
359 -- P_PARTY_ID is the parameter that would be passed by the caller
356 WHERE P.PARTY_ID = P_PARTY_ID';
357
358 -- HZ_PARTIES is the child enetiy for Person Customer BO
360 G_HZ_PARTIES_PCUST VARCHAR2(100) := ' AND EXISTS
361 (SELECT 1
362 FROM HZ_PARTIES P
366 -- P_PARTY_ID is the parameter that would be passed by the caller
363 WHERE P.PARTY_ID = P_PARTY_ID';
364
365 -- HZ_PARTIES is the child enetiy for Org Customer BO
367 G_HZ_PARTIES_OCUST VARCHAR2(100) := ' AND EXISTS
368 (SELECT 1
369 FROM HZ_PARTIES P
370 WHERE P.PARTY_ID = P_PARTY_ID';
371
372 -- HZ_PARTIES (person) as the child of Org Contact
373 -- for Org Contact as the parent
374 G_HZ_PARTIES_OC VARCHAR2(200) := ' AND EXISTS
375 (SELECT 1
376 FROM HZ_PARTIES P, HZ_RELATIONSHIPS R
377 WHERE P.PARTY_ID = R.SUBJECT_ID AND R.subject_type = ''PERSON''
378 AND OC.PARTY_RELATIONSHIP_ID = R.RELATIONSHIP_ID';
379
380 G_HZ_PARTIES_OC_ET1 VARCHAR2(225) := ' AND EXISTS
381 (SELECT 1
382 FROM HZ_PARTIES P, HZ_RELATIONSHIPS R
383 WHERE P.PARTY_ID = R.SUBJECT_ID AND R.subject_type = ''PERSON''
387
384 AND OC.PARTY_RELATIONSHIP_ID = R.RELATIONSHIP_ID AND
385 P.CREATION_DATE <P_CUTOFF_DT';
386
388 -- HZ_PARTY_PREFERENCES Org and Person as parents
389 -- for Org and Person as parents
390 G_HZ_PARTY_PREF_P VARCHAR2(100) := ' AND EXISTS
391 (SELECT 1
392 FROM HZ_PARTY_PREFERENCES PP
393 WHERE PP.PARTY_ID = P.PARTY_ID';
394
395 -- HZ_PARTY_SITE_USES can have only Party Site as parent
396 -- for Party Site as parent
397 G_HZ_PS_USE_PS VARCHAR2(150) := ' AND EXISTS
398 (SELECT 1
399 FROM HZ_PARTY_SITE_USES PSU
400 WHERE PSU.PARTY_SITE_ID = PS.PARTY_SITE_ID';
401
402 -- HZ_PARTY_SITES Can have Org, Person, Contact and Account Site as parents
403 -- for Org, Person as parent
404 G_HZ_PS_P VARCHAR2(100) := ' AND EXISTS
405 (SELECT 1
406 FROM HZ_PARTY_SITES PS
407 WHERE PS.PARTY_ID = P.PARTY_ID';
408
409 -- for Org Contact as parent
410 G_HZ_PS_OC VARCHAR2(175) := ' AND EXISTS
411 (SELECT 1
412 FROM HZ_PARTY_SITES PS, HZ_RELATIONSHIPS R
413 WHERE PS.PARTY_ID = R.PARTY_ID AND OC.PARTY_RELATIONSHIP_ID = R.RELATIONSHIP_ID';
414
415 G_HZ_PS_OC_ET1 VARCHAR2(200) := ' AND EXISTS
416 (SELECT 1
417 FROM HZ_PARTY_SITES PS, HZ_RELATIONSHIPS R
418 WHERE PS.PARTY_ID = R.PARTY_ID AND OC.PARTY_RELATIONSHIP_ID = R.RELATIONSHIP_ID
419 AND PS.CREATION_DATE <P_CUTOFF_DT';
420
421 -- for Acct Site as parent
422 G_HZ_PS_AS VARCHAR2(150) := ' AND EXISTS
423 (SELECT 1
424 FROM HZ_PARTY_SITES PS
425 WHERE PS.PARTY_SITE_ID = ASA.CUST_ACCT_SITE_ID';
426
427 -- HZ_PERSON_INTEREST Can have Person as parent
428 -- for Person as parent
429 G_HZ_PER_INT_P VARCHAR2(100) := ' AND EXISTS
430 (SELECT 1
431 FROM HZ_PERSON_INTEREST PI
432 WHERE PI.PARTY_ID = P.PARTY_ID';
433
434 -- HZ_PERSON_LANGUAGE Can have Person as parent
435 -- for Person as parent
436 G_HZ_PER_LANG_P VARCHAR2(100) := ' AND EXISTS
437 (SELECT 1
438 FROM HZ_PERSON_LANGUAGE PL
439 WHERE PL.PARTY_ID = P.PARTY_ID';
440
441 -- HZ_PERSON_PROFILES Can have Person as parent
442 -- for Person as parent
443 G_HZ_PER_PROF_P VARCHAR2(100) := ' AND EXISTS
444 (SELECT 1
445 FROM HZ_PERSON_PROFILES PP
446 WHERE PP.PARTY_ID = P.PARTY_ID';
447
448 -- HZ_RELATIONSHIPS Can have Org, Person, Org Contact, Cust Acct Contact as parents
449 -- for Org, Person as parent
450 G_HZ_REL_P VARCHAR2(150) := ' AND EXISTS
451 (SELECT 1
452 FROM HZ_RELATIONSHIPS R
453 WHERE R.SUBJECT_ID = P.PARTY_ID
454 AND R.OBJECT_TYPE IN (''PERSON'',''ORGANIZATION'')';
455 -- for Org Contact as parent
456 G_HZ_REL_OC VARCHAR2(150) := ' AND EXISTS
457 (SELECT 1
458 FROM HZ_RELATIONSHIPS R
459 WHERE R.RELATIONSHIP_ID = OC.PARTY_RELATIONSHIP_ID ';
460 -- for Customer Account Contact as parent
461 G_HZ_REL_ACONT VARCHAR2(150) := ' AND EXISTS
462 (SELECT 1 FROM HZ_RELATIONSHIPS R
463 WHERE R.PARTY_ID = ACR.PARTY_ID ';
464
465 -- HZ_ROLE_RESPONSIBILITY can only have customer account contact as parent
466 G_HZ_ROLE_RESP_AC VARCHAR2(150) := ' AND EXISTS
467 (SELECT 1
468 FROM HZ_ROLE_RESPONSIBILITY RR
469 WHERE RR.CUST_ACCOUNT_ROLE_ID = ACR.CUST_ACCOUNT_ROLE_ID';
470
471 -- HZ_WORK_CLASS Can have Employement History as parent
472 -- for Employement History as parent
473 G_HZ_WORK_CLASS_EH VARCHAR2(150) := ' AND EXISTS
474 (SELECT 1
475 FROM HZ_WORK_CLASS W
476 WHERE W.EMPLOYMENT_HISTORY_ID = EH.EMPLOYMENT_HISTORY_ID';
477
478 -- RA_CUST_RECEIPT_METHODS can have Cust Account and Account Site Use as parents
479 -- For cust account as parent
480 G_CUST_RECEIPT_METHODS_AC VARCHAR2(200) := ' AND EXISTS
481 (SELECT 1
482 FROM RA_CUST_RECEIPT_METHODS CRM
483 WHERE CRM.CUSTOMER_ID = AC.CUST_ACCOUNT_ID';
484
485 -- For account site use as parent
489 WHERE CRM.SITE_USE_ID = ACSU.SITE_USE_ID';
486 G_CUST_RECEIPT_METHODS_ASU VARCHAR2(200) := ' AND EXISTS
487 (SELECT 1
488 FROM RA_CUST_RECEIPT_METHODS CRM
490
491 -- IBY_FNDCPT_PAYER_ASSGN_INSTR_V can have Cust Account and Account Site Use as parents
492 -- For cust account as parent
493 G_BANK_ACCT_USE_AC VARCHAR2(200) := ' AND EXISTS
494 (SELECT 1
495 FROM IBY_FNDCPT_PAYER_ASSGN_INSTR_V BAU
496 WHERE BAU.CUST_ACCOUNT_ID = AC.CUST_ACCOUNT_ID';
497
498 -- For account site use as parent
499 G_BANK_ACCT_USE_ASU VARCHAR2(200) := ' AND EXISTS
500 (SELECT 1
501 FROM IBY_FNDCPT_PAYER_ASSGN_INSTR_V BAU
502 WHERE BAU.ACCT_SITE_USE_ID = ACSU.SITE_USE_ID';
503
504 -- ER 5055998 start
505 -- HZ_PARTY_USG_ASSIGNMENTS can have Person, Org as parents.
506 G_HZ_PARTY_USG_ASSIN_P VARCHAR2(120) := ' AND EXISTS
507 (SELECT 1
508 FROM HZ_PARTY_USG_ASSIGNMENTS PUA
509 WHERE PUA.PARTY_ID = P.PARTY_ID';
513
510 -- ER 5055998 end
511
512 -- types that are used in this package.
514
515 -- TYPE NUMBER_COLUMN IS TABLE OF NUMBER;
516 -- TYPE BO_CODE IS TABLE OF HZ_BUS_OBJ_DEFINITIONS.BUSINESS_OBJECT_CODE%TYPE;
517 TYPE VAR106_TYPE IS TABLE OF VARCHAR2(106);
518 TYPE VAR30_TYPE IS TABLE OF VARCHAR2(30);
519 -- TYPE ROOT_NODE_FLAG IS TABLE OF HZ_BUS_OBJ_DEFINITIONS.ROOT_NODE_FLAG%TYPE;
520
521
522 TYPE node_row_type IS RECORD
523 (
524 level_no NUMBER,
525 BO_CODE HZ_BUS_OBJ_DEFINITIONS.BUSINESS_OBJECT_CODE%TYPE,
526 RNF HZ_BUS_OBJ_DEFINITIONS.ROOT_NODE_FLAG%TYPE,
527 ENTITY_NAME HZ_BUS_OBJ_DEFINITIONS.ENTITY_NAME%TYPE,
528 PARENT_BO_CODE HZ_BUS_OBJ_DEFINITIONS.BUSINESS_OBJECT_CODE%TYPE
529 );
530
531 -- Define a type to store the right paranthesis , bo_code, parent_bo_code
532 TYPE RP_TYPE IS RECORD
533 ( node NUMBER,
534 RP VARCHAR2(3),
535 BO_CODE HZ_BUS_OBJ_DEFINITIONS.BUSINESS_OBJECT_CODE%TYPE,
536 PARENT_BO_CODE HZ_BUS_OBJ_DEFINITIONS.BUSINESS_OBJECT_CODE%TYPE
537 );
538
539 TYPE NODE_TBL_TYPE IS TABLE OF NODE_ROW_TYPE INDEX BY PLS_INTEGER;
540 TYPE RP_TBL_TYPE IS TABLE OF RP_TYPE INDEX BY PLS_INTEGER;
541 TYPE var106_tbl_type IS TABLE OF VAR106_TYPE INDEX BY PLS_INTEGER;
542 TYPE var30_tbl_type IS TABLE OF VAR30_TYPE INDEX BY PLS_INTEGER;
543
544 /*
545 Scope: Internal procedure.
546 Purpose: To write the cursor for completeness check procedure.
547 Called By: gen_bo_complete_proc()
548 Called From:
549 */
550 PROCEDURE genBOSQL(
551 P_BO_CODE IN VARCHAR2, -- holds the BO code.
552 P_SQL_FOR IN VARCHAR2, -- conveys if the sql is for completeness check or to figure out the event type.
553 -- this will take two values, COMPLETE, EVENT
554 P_STATUS OUT NOCOPY BOOLEAN );-- holds the return status
555 /*
556 Scope: Internal Procedure
560 */
557 Purpose: To create the package body.
558 Called by: some conc program procedure
559 Called from: HZ_BES_BO_GEN_PKG
561 PROCEDURE gen_pkg_body
562 ( P_STATUS OUT NOCOPY BOOLEAN );-- holds the return status
563
564 PROCEDURE genCompletenessProc;
565 PROCEDURE genEvtTypeProc;
566
567 /*
568 Scope: Public Procedure
569 Purpose: To generate package body specific to BO definitions at the customer site.
570 Called by: Concurrent Program UI
571 */
572 PROCEDURE gen_pkg_main (
573 errbuf OUT NOCOPY VARCHAR2,
574 retcode OUT NOCOPY VARCHAR2);
575
576 END HZ_BES_BO_GEN_PKG; -- Package spec
[Home] [Help]