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.
PACKAGE BODY: APPS.HZ_IMP_LOAD_BATCH_COUNTS_PKG

Source


1 PACKAGE BODY HZ_IMP_LOAD_BATCH_COUNTS_PKG AS
2 /*$Header: ARHLBCB.pls 120.13 2008/10/27 09:38:56 idali ship $*/
3 /*
4   Procedure: pre_import_counts()
5   These are the counts of each entity before running
6    the dataload process. This procedure also initializes
7    the post count columns to zero.
8   This procedure is called from
9     hz_imp_batch_summary_v2pub.activate_batch().
10   The above API is called from teh last stage of DNB adapter.
11 */
12 
13 PROCEDURE pre_import_counts
14  ( P_BATCH_ID    IN HZ_IMP_BATCH_SUMMARY.BATCH_ID%TYPE,
15    P_ORIGINAL_SYSTEM IN HZ_IMP_BATCH_SUMMARY.ORIGINAL_SYSTEM%TYPE) IS
16     -- Declare the variables
17   l_ADDRESSUSES_IN_BATCH  NUMBER;
18   l_ADDRESSES_IN_BATCH  NUMBER;
19   l_FINNUMBERS_IN_BATCH  NUMBER;
20   l_CODEASSIGNS_IN_BATCH  NUMBER;
21   l_RELATIONSHIPS_IN_BATCH  NUMBER;
22   l_CONTACTROLES_IN_BATCH  NUMBER;
23   l_CONTACTS_IN_BATCH  NUMBER;
24   l_CONTACTPOINTS_IN_BATCH  NUMBER;
25   l_CREDITRATINGS_IN_BATCH  NUMBER;
26   l_FINREPORTS_IN_BATCH  NUMBER;
27   l_PARTIES_IN_BATCH  NUMBER;
28    l_total_batch_records number;
29 
30 BEGIN
31   SELECT  count(INT.BATCH_ID) into l_ADDRESSUSES_IN_BATCH
32     FROM HZ_IMP_ADDRESSUSES_INT INT
33     WHERE INT.BATCH_ID = P_BATCH_ID AND
34        INT.PARTY_ORIG_SYSTEM = P_ORIGINAL_SYSTEM ;
35   SELECT  count(INT.BATCH_ID) into l_ADDRESSES_IN_BATCH
36     FROM HZ_IMP_ADDRESSES_INT INT
37     WHERE INT.BATCH_ID = P_BATCH_ID AND
38        INT.PARTY_ORIG_SYSTEM = P_ORIGINAL_SYSTEM ;
39   SELECT  count(INT.BATCH_ID) into l_FINNUMBERS_IN_BATCH
40     FROM HZ_IMP_FINNUMBERS_INT INT
41     WHERE INT.BATCH_ID = P_BATCH_ID AND
42        INT.PARTY_ORIG_SYSTEM = P_ORIGINAL_SYSTEM ;
43   SELECT  count(INT.BATCH_ID) into l_CODEASSIGNS_IN_BATCH
44     FROM HZ_IMP_CLASSIFICS_INT INT
45     WHERE INT.BATCH_ID = P_BATCH_ID AND
46        INT.PARTY_ORIG_SYSTEM = P_ORIGINAL_SYSTEM ;
47   SELECT  count(INT.BATCH_ID) into l_RELATIONSHIPS_IN_BATCH
48     FROM HZ_IMP_RELSHIPS_INT INT
49     WHERE INT.BATCH_ID = P_BATCH_ID AND
50        INT.SUB_ORIG_SYSTEM = P_ORIGINAL_SYSTEM ;
51   SELECT  count(INT.BATCH_ID) into l_CONTACTROLES_IN_BATCH
52     FROM HZ_IMP_CONTACTROLES_INT INT
53     WHERE INT.BATCH_ID = P_BATCH_ID AND
54        INT.CONTACT_ORIG_SYSTEM = P_ORIGINAL_SYSTEM ;
55   SELECT  count(INT.BATCH_ID) into l_CONTACTS_IN_BATCH
56     FROM HZ_IMP_CONTACTS_INT INT
57     WHERE INT.BATCH_ID = P_BATCH_ID AND
58        INT.CONTACT_ORIG_SYSTEM = P_ORIGINAL_SYSTEM ;
59   SELECT  count(INT.BATCH_ID) into l_CONTACTPOINTS_IN_BATCH
60     FROM HZ_IMP_CONTACTPTS_INT INT
61     WHERE INT.BATCH_ID = P_BATCH_ID AND
62        INT.PARTY_ORIG_SYSTEM = P_ORIGINAL_SYSTEM ;
63   SELECT  count(INT.BATCH_ID) into l_CREDITRATINGS_IN_BATCH
64     FROM HZ_IMP_CREDITRTNGS_INT INT
65     WHERE INT.BATCH_ID = P_BATCH_ID AND
66        INT.PARTY_ORIG_SYSTEM = P_ORIGINAL_SYSTEM ;
67   SELECT  count(INT.BATCH_ID) into l_FINREPORTS_IN_BATCH
68     FROM HZ_IMP_FINREPORTS_INT INT
69     WHERE INT.BATCH_ID = P_BATCH_ID AND
70        INT.PARTY_ORIG_SYSTEM = P_ORIGINAL_SYSTEM ;
71   SELECT  count(INT.BATCH_ID) into l_PARTIES_IN_BATCH
72     FROM HZ_IMP_PARTIES_INT INT
73     WHERE INT.BATCH_ID = P_BATCH_ID AND
74        INT.PARTY_ORIG_SYSTEM = P_ORIGINAL_SYSTEM ;
75   l_total_batch_records :=
76    l_ADDRESSUSES_IN_BATCH +
77    l_ADDRESSES_IN_BATCH +
78    l_FINNUMBERS_IN_BATCH +
79    l_CODEASSIGNS_IN_BATCH +
80    l_RELATIONSHIPS_IN_BATCH +
81    l_CONTACTROLES_IN_BATCH +
82    l_CONTACTS_IN_BATCH +
83    l_CONTACTPOINTS_IN_BATCH +
84    l_CREDITRATINGS_IN_BATCH +
85    l_FINREPORTS_IN_BATCH +
86    l_PARTIES_IN_BATCH ;
87 
88  UPDATE HZ_IMP_BATCH_SUMMARY SET
89    ADDRESSUSES_IN_BATCH = l_ADDRESSUSES_IN_BATCH,
90    ADDRESSES_IN_BATCH = l_ADDRESSES_IN_BATCH,
91    FINNUMBERS_IN_BATCH = l_FINNUMBERS_IN_BATCH,
92    CODEASSIGNS_IN_BATCH = l_CODEASSIGNS_IN_BATCH,
93    RELATIONSHIPS_IN_BATCH = l_RELATIONSHIPS_IN_BATCH,
94    CONTACTROLES_IN_BATCH = l_CONTACTROLES_IN_BATCH,
95    CONTACTS_IN_BATCH = l_CONTACTS_IN_BATCH,
96    CONTACTPOINTS_IN_BATCH = l_CONTACTPOINTS_IN_BATCH,
97    CREDITRATINGS_IN_BATCH = l_CREDITRATINGS_IN_BATCH,
98    FINREPORTS_IN_BATCH = l_FINREPORTS_IN_BATCH,
99    PARTIES_IN_BATCH = l_PARTIES_IN_BATCH,
100    TOTAL_BATCH_RECORDS =  l_total_batch_records,
101   ADDRESSUSES_INSERTED = 0,
102   ADDRESSUSES_UPDATED = 0,
103   ADDRESSUSES_IMPORTED = 0,
104   ADDRESSES_INSERTED = 0,
105   ADDRESSES_UPDATED = 0,
106   ADDRESSES_IMPORTED = 0,
107   ADDRESS_MERGE_REQUESTS = 0,
108   FINNUMBERS_INSERTED = 0,
109   FINNUMBERS_UPDATED = 0,
110   FINNUMBERS_IMPORTED = 0,
111   CODEASSIGNS_INSERTED = 0,
112   CODEASSIGNS_UPDATED = 0,
113   CODEASSIGNS_IMPORTED = 0,
114   RELATIONSHIPS_INSERTED = 0,
115   RELATIONSHIPS_UPDATED = 0,
116   RELATIONSHIPS_IMPORTED = 0,
117   CONTACTROLES_INSERTED = 0,
118   CONTACTROLES_UPDATED = 0,
119   CONTACTROLES_IMPORTED = 0,
120   CONTACTS_INSERTED = 0,
121   CONTACTS_UPDATED = 0,
122   CONTACTS_IMPORTED = 0,
123   CONTACT_MERGE_REQUESTS = 0,
124   CONTACTPOINTS_INSERTED = 0,
125   CONTACTPOINTS_UPDATED = 0,
126   CONTACTPOINTS_IMPORTED = 0,
127   CONTACTPOINT_MERGE_REQUESTS = 0,
128   CREDITRATINGS_INSERTED = 0,
129   CREDITRATINGS_UPDATED = 0,
130   CREDITRATINGS_IMPORTED = 0,
131   FINREPORTS_INSERTED = 0,
132   FINREPORTS_UPDATED = 0,
133   FINREPORTS_IMPORTED = 0,
134   PARTIES_INSERTED = 0,
135   PARTIES_UPDATED = 0,
136   PARTIES_IMPORTED = 0,
137   PARTY_MERGE_REQUESTS = 0,
138   PARTIES_AUTO_MERGED    = 0,
139   TOTAL_MERGE_REQUESTS   = 0,
140   TOTAL_ERRORS           = 0,
141   TOTAL_RECORDS_IMPORTED = 0
142  WHERE BATCH_ID = P_BATCH_ID;
143 END pre_import_counts;
144 
145 /*
146   Procedure: post_import_counts()
147   These are the counts after populating the TCA registry.
148   These counts are cumulative i.e., per batch (not per run).
149   This procedure is called from within the dataload process.
150 */
151 
152 procedure post_import_counts
153      (P_BATCH_ID    IN HZ_IMP_BATCH_SUMMARY.BATCH_ID%TYPE,
154        P_ORIGINAL_SYSTEM IN HZ_IMP_BATCH_SUMMARY.ORIGINAL_SYSTEM%TYPE,
155        P_BATCH_MODE_FLAG in VARCHAR2,
156        P_REQUEST_ID      IN NUMBER,
157        P_RERUN_FLAG IN VARCHAR2) IS -- N for First Run any other value is rerun
158 
159 -- Declare the variables
160 
161  l_ADDRESSUSES_INSERTED NUMBER;
162  l_ADDRESSES_INSERTED NUMBER;
163  l_FINNUMBERS_INSERTED NUMBER;
164  l_CODEASSIGNS_INSERTED NUMBER;
165  l_RELATIONSHIPS_INSERTED NUMBER;
166  l_CONTACTROLES_INSERTED NUMBER;
167  l_CONTACTS_INSERTED NUMBER;
168  l_CONTACTPOINTS_INSERTED NUMBER;
169  l_CREDITRATINGS_INSERTED NUMBER;
170  l_FINREPORTS_INSERTED NUMBER;
171  l_PARTIES_INSERTED NUMBER;
172 
173  l_ADDRESSUSES_UPDATED NUMBER;
174  l_ADDRESSES_UPDATED NUMBER;
175  l_FINNUMBERS_UPDATED NUMBER;
176  l_CODEASSIGNS_UPDATED NUMBER;
177  l_RELATIONSHIPS_UPDATED NUMBER;
178  l_CONTACTROLES_UPDATED NUMBER;
179  l_CONTACTS_UPDATED NUMBER;
180  l_CONTACTPOINTS_UPDATED NUMBER;
181  l_CREDITRATINGS_UPDATED NUMBER;
182  l_FINREPORTS_UPDATED NUMBER;
183  l_PARTIES_UPDATED NUMBER;
184 
185  l_ADDRESSUSES_ERRORED NUMBER;
186  l_ADDRESSES_ERRORED NUMBER;
187  l_FINNUMBERS_ERRORED NUMBER;
188  l_CODEASSIGNS_ERRORED NUMBER;
189  l_RELATIONSHIPS_ERRORED NUMBER;
190  l_CONTACTROLES_ERRORED NUMBER;
191  l_CONTACTS_ERRORED NUMBER;
192  l_CONTACTPOINTS_ERRORED NUMBER;
193  l_CREDITRATINGS_ERRORED NUMBER;
194  l_FINREPORTS_ERRORED NUMBER;
195  l_PARTIES_ERRORED NUMBER;
196 
197  l_ADDRESSUSES_IMPORTED NUMBER;
198  l_ADDRESSES_IMPORTED NUMBER;
199  l_FINNUMBERS_IMPORTED NUMBER;
200  l_CODEASSIGNS_IMPORTED NUMBER;
201  l_RELATIONSHIPS_IMPORTED NUMBER;
202  l_CONTACTROLES_IMPORTED NUMBER;
203  l_CONTACTS_IMPORTED NUMBER;
204  l_CONTACTPOINTS_IMPORTED NUMBER;
205  l_CREDITRATINGS_IMPORTED NUMBER;
206  l_FINREPORTS_IMPORTED NUMBER;
207  l_PARTIES_IMPORTED NUMBER;
208 
209  l_total_errors number;
210  l_total_records_imported number;
211 
212 l_bool BOOLEAN;
213 l_status_owner VARCHAR2(255);
214 l_ar_schema_name VARCHAR2(255);
215 l_tmp           VARCHAR2(2000);
216 
217 l_sst_flag            HZ_ORIG_SYSTEMS_B.sst_flag%TYPE := 'N';
218 l_rels_updated  NUMBER := 0;
219 
220 BEGIN
221 
222 -- Bug 3872618
223 l_bool := fnd_installation.GET_APP_INFO('AR',l_status_owner,l_tmp,l_ar_schema_name);
224 
225 /*
226   IF ( p_rerun_flag = 'N') THEN
227     -- compute total_errors in first run
228       l_total_errors :=
229     l_ADDRESSUSES_ERRORED +
230     l_ADDRESSES_ERRORED +
231     l_FINNUMBERS_ERRORED +
232     l_CODEASSIGNS_ERRORED +
233     l_RELATIONSHIPS_ERRORED +
234     l_CONTACTROLES_ERRORED +
235     l_CONTACTS_ERRORED +
236     l_CONTACTPOINTS_ERRORED +
237     l_CREDITRATINGS_ERRORED +
238     l_FINREPORTS_ERRORED +
239     l_PARTIES_ERRORED ;
240   ELSE
241     -- FOR RE-RUN CASES total_errors = total_errors - newly imported recs
242     l_total_errors :=   l_total_records_imported;
243   END IF;
244 */
245 
246 select ct, c01, c02, c03, c04, c05, c06, c07, c08, c09, c10, c11, c12,
247        c13, c14, c15, c16, c17, c18, c19, c20, c21, c22, c23
248   into l_total_errors, l_relationships_inserted, l_relationships_updated,
249        l_finnumbers_inserted, l_finnumbers_updated, l_finreports_inserted,
250        l_finreports_updated, l_contactpoints_inserted,
251        l_contactpoints_updated, l_addresses_inserted, l_addresses_updated,
252        l_parties_inserted, l_parties_updated, l_contactroles_inserted,
253        l_contactroles_updated, l_addressuses_inserted,
254        l_addressuses_updated, l_contacts_inserted, l_contacts_updated,
255        l_creditratings_inserted, l_creditratings_updated,
256        l_codeassigns_inserted, l_codeassigns_updated, l_total_records_imported
257   from (
258 select ct, rank() over (order by a) r,
259        lead(ct, 1) over (order by a) c01,
260        lead(ct, 2) over (order by a) c02,
261        lead(ct, 3) over (order by a) c03,
262        lead(ct, 4) over (order by a) c04,
263        lead(ct, 5) over (order by a) c05,
264        lead(ct, 6) over (order by a) c06,
265        lead(ct, 7) over (order by a) c07,
266        lead(ct, 8) over (order by a) c08,
267        lead(ct, 9) over (order by a) c09,
268        lead(ct, 10) over (order by a) c10,
269        lead(ct, 11) over (order by a) c11,
270        lead(ct, 12) over (order by a) c12,
271        lead(ct, 13) over (order by a) c13,
272        lead(ct, 14) over (order by a) c14,
273        lead(ct, 15) over (order by a) c15,
274        lead(ct, 16) over (order by a) c16,
275        lead(ct, 17) over (order by a) c17,
276        lead(ct, 18) over (order by a) c18,
277        lead(ct, 19) over (order by a) c19,
278        lead(ct, 20) over (order by a) c20,
279        lead(ct, 21) over (order by a) c21,
280        lead(ct, 22) over (order by a) c22,
281        sum(ct) over (order by a rows between 1 following and 22 following) c23
282   from (
283 select x.a, nvl(y.ct, 0) ct from (
284 select multiplier a from gl_row_multipliers
285  where rownum < 24) x, (
286 select a, count(*) ct from (
287 select /*+ full(sg) parallel(sg) */ nvl2(e.int_row_id,
288        1, nvl2(NVL(rel.relationship_id,hzint.BATCH_ID),decode(sg.action_flag, 'I', 2, 3),24)) a
289   from hz_imp_tmp_errors e, hz_imp_relships_sg sg,hz_relationships rel, HZ_IMP_RELSHIPS_INT hzint
290  where sg.batch_mode_flag = p_batch_mode_flag
291    and sg.batch_id = p_batch_id
292    and sg.sub_orig_system = p_original_system
293    and e.int_row_id (+) = sg.int_row_id
294    and e.request_id (+) = p_request_id
295    and e.interface_table_name (+) = 'HZ_IMP_RELSHIPS_INT'
296    and rel.relationship_id (+) = sg.relationship_id
297    and rel.request_id (+) = p_request_id
298    and rel.directional_flag(+) = 'F'
299    and sg.int_row_id = hzint.rowid (+)
300    and hzint.interface_status(+) = 'D'
301    union all
302   select /*+ full(sg) parallel(sg) */ nvl2(e.int_row_id,
303          1, nvl2(NVL(fnn.financial_number_id,hzint.BATCH_ID),decode(sg.action_flag, 'I', 4, 5),24)) a
304     from hz_imp_tmp_errors e, hz_imp_finnumbers_sg sg,hz_financial_numbers fnn, HZ_IMP_FINNUMBERS_INT hzint
305    where sg.batch_mode_flag = p_batch_mode_flag
306      and sg.batch_id = p_batch_id
307      and sg.party_orig_system = p_original_system
308      and e.int_row_id (+) = sg.int_row_id
309      and e.request_id (+) = p_request_id
310      and e.interface_table_name (+) = 'HZ_IMP_FINNUMBERS_INT'
311      and fnn.financial_number_id (+) = sg.financial_number_id
312      and fnn.request_id(+) = p_request_id
313      and sg.int_row_id = hzint.rowid (+)
314      and hzint.interface_status(+) = 'D'
315    union all
316   select /*+ full(sg) parallel(sg) */ nvl2(e.int_row_id,
317          1, nvl2(NVL(fnr.financial_report_id,hzint.BATCH_ID),decode(sg.action_flag, 'I', 6, 7),24)) a
318     from hz_imp_tmp_errors e, hz_imp_finreports_sg sg,hz_financial_reports fnr, HZ_IMP_FINREPORTS_INT hzint
319    where sg.batch_mode_flag = p_batch_mode_flag
320      and sg.batch_id = p_batch_id
321      and sg.party_orig_system = p_original_system
322      and e.int_row_id (+) = sg.int_row_id
323      and e.request_id (+) = p_request_id
324      and e.interface_table_name (+) = 'HZ_IMP_FINREPORTS_INT'
325      and fnr.financial_report_id(+) = sg.financial_report_id
326      and fnr.request_id(+) = p_request_id
327      and sg.int_row_id = hzint.rowid (+)
328      and hzint.interface_status(+) = 'D'
329    union all
330   select /*+ full(sg) parallel(sg) */ nvl2(e.int_row_id,
331          1, nvl2(NVL(hcp.contact_point_id,hzint.BATCH_ID),decode(sg.action_flag, 'I', 8, 9),24)) a
332     from hz_imp_tmp_errors e, hz_imp_contactpts_sg sg,hz_contact_points hcp, HZ_IMP_CONTACTPTS_INT hzint
333    where sg.batch_mode_flag = p_batch_mode_flag
334      and sg.batch_id = p_batch_id
335      and sg.party_orig_system = p_original_system
336      and e.int_row_id (+) = sg.int_row_id
337      and e.request_id (+) = p_request_id
338      and e.interface_table_name (+) = 'HZ_IMP_CONTACTPTS_INT'
339      and hcp.contact_point_id (+) = sg.contact_point_id
340      and hcp.request_id (+) = p_request_id
341      and sg.int_row_id = hzint.rowid (+)
342      and hzint.interface_status(+) = 'D'
343    union all
344    select /*+ full(sg) parallel(sg) */ nvl2(e.int_row_id,
345          1, nvl2(NVL(loc.location_id,hzint.BATCH_ID),decode(sg.action_flag, 'I', 10, 11),24)) a
346     from hz_imp_tmp_errors e, hz_imp_addresses_sg sg,hz_party_sites hps,
347          hz_orig_sys_references hosr,hz_locations loc, HZ_IMP_ADDRESSES_INT hzint
348    where sg.batch_mode_flag = p_batch_mode_flag
349      and sg.batch_id = p_batch_id
350      and sg.party_orig_system = p_original_system
351      and hosr.orig_system(+) =sg.party_orig_system
352      and hosr.orig_system_reference(+) =sg.site_orig_system_reference
353      and hosr.owner_table_name(+)  = 'HZ_PARTY_SITES'
354      and hosr.status(+) = 'A'
355      and hosr.owner_table_id = hps.party_site_id(+)
356      and hps.location_id = loc.location_id(+)
357      and e.int_row_id (+) = sg.int_row_id
358      and e.request_id (+) = p_request_id
359      and e.interface_table_name (+) = 'HZ_IMP_ADDRESSES_INT'
363      and hzint.interface_status(+) = 'D'
360      --and hps.party_site_id (+) = sg.party_site_id
361      and loc.request_id (+) = p_request_id
362      and sg.int_row_id = hzint.rowid (+)
364    union all
365   select /*+ full(sg) parallel(sg) */ nvl2(e.int_row_id,
366          1, nvl2(NVL(hp.party_id,hzint.BATCH_ID),decode(sg.action_flag, 'I', 12, 13),24)) a
367     from hz_imp_tmp_errors e, hz_imp_parties_sg sg,hz_parties hp, HZ_IMP_PARTIES_INT hzint
368    where sg.batch_mode_flag = p_batch_mode_flag
369      and sg.batch_id = p_batch_id
370      and sg.party_orig_system = p_original_system
371      and e.int_row_id (+) = sg.int_row_id
372      and e.request_id (+) = p_request_id
373      and e.interface_table_name (+) = 'HZ_IMP_PARTIES_INT'
374      and hp.party_id (+) = sg.party_id
375      and hp.request_id (+) = p_request_id
376      and sg.int_row_id = hzint.rowid (+)
377      and hzint.interface_status(+) = 'D'
378    union all
379   select /*+ full(sg) parallel(sg) */ nvl2(e.int_row_id,
380          1, nvl2(NVL(hocr.org_contact_role_id,hzint.BATCH_ID),decode(sg.action_flag, 'I', 14, 15),24)) a
381     from hz_imp_tmp_errors e, hz_imp_contactroles_sg sg,hz_org_contact_roles hocr, HZ_IMP_CONTACTROLES_INT hzint
382    where sg.batch_mode_flag = p_batch_mode_flag
383      and sg.batch_id = p_batch_id
384      and sg.sub_orig_system = p_original_system
385      and e.int_row_id (+) = sg.int_row_id
386      and e.request_id (+) = p_request_id
387      and e.interface_table_name (+) = 'HZ_IMP_CONTACTROLES_INT'
388      and hocr.org_contact_role_id (+) = sg.contact_role_id
389 --     and hocr.request_id (+) = p_request_id
390      and sg.int_row_id = hzint.rowid (+)
391      and hzint.interface_status(+) = 'D'
392    union all
393   select /*+ full(sg) parallel(sg) */ nvl2(e.int_row_id,
394          1, nvl2(NVL(hpsu.party_site_use_id,hzint.BATCH_ID),decode(sg.action_flag, 'I', 16, 17),24)) a
395     from hz_imp_tmp_errors e, hz_imp_addressuses_sg sg, hz_party_site_uses hpsu, HZ_IMP_ADDRESSUSES_INT hzint
396    where sg.batch_mode_flag = p_batch_mode_flag
397      and sg.batch_id = p_batch_id
398      and sg.party_orig_system = p_original_system
399      and e.int_row_id (+) = sg.int_row_id
400      and e.request_id (+) = p_request_id
401      and e.interface_table_name (+) = 'HZ_IMP_ADDRESSUSES_INT'
402      and hpsu.party_site_use_id (+) = sg.party_site_use_id
403 --     and hpsu.request_id (+) = p_request_id
404      and sg.int_row_id = hzint.rowid (+)
405      and hzint.interface_status(+) = 'D'
406    union all
407   select /*+ full(sg) parallel(sg) */ nvl2(e.int_row_id,
408          1, nvl2(NVL(hoc.org_contact_id,hzint.BATCH_ID),decode(sg.action_flag, 'I', 18, 19),24)) a
409     from hz_imp_tmp_errors e, hz_imp_contacts_sg sg, hz_org_contacts hoc, HZ_IMP_CONTACTS_INT hzint
410    where sg.batch_mode_flag = p_batch_mode_flag
411      and sg.batch_id = p_batch_id
412      and sg.contact_orig_system = p_original_system
413      and e.int_row_id (+) = sg.int_row_id
414      and e.request_id (+) = p_request_id
415      and e.interface_table_name (+) = 'HZ_IMP_CONTACTS_INT'
416      and hoc.org_contact_id (+) = sg.contact_id
417      and hoc.request_id (+) = p_request_id
418      and sg.int_row_id = hzint.rowid (+)
419      and hzint.interface_status(+) = 'D'
420    union all
421   select /*+ full(sg) parallel(sg) */ nvl2(e.int_row_id,
422          1, nvl2(NVL(hcr.credit_rating_id,hzint.BATCH_ID),decode(sg.action_flag, 'I', 20, 21),24)) a
423     from hz_imp_tmp_errors e, hz_imp_creditrtngs_sg sg,hz_credit_ratings hcr, HZ_IMP_CREDITRTNGS_INT hzint
424    where sg.batch_mode_flag = p_batch_mode_flag
425      and sg.batch_id = p_batch_id
426      and sg.party_orig_system = p_original_system
427      and e.int_row_id (+) = sg.int_row_id
428      and e.request_id (+) = p_request_id
429      and e.interface_table_name (+) = 'HZ_IMP_CREDITRTNGS_INT'
430      and hcr.credit_rating_id(+) = sg.credit_rating_id
431      and hcr.request_id (+) = p_request_id
432      and sg.int_row_id = hzint.rowid (+)
433      and hzint.interface_status(+) = 'D'
434  union all
435 select /*+ full(sg) parallel(sg) */ nvl2(e.int_row_id,
436        1, nvl2(NVL(hca.code_assignment_id,hzint.BATCH_ID),decode(sg.action_flag, 'I', 22, 23),24)) a
437   from hz_imp_tmp_errors e, hz_imp_classifics_sg sg,hz_code_assignments hca, HZ_IMP_CLASSIFICS_INT hzint
438  where sg.batch_mode_flag = p_batch_mode_flag
439    and sg.batch_id = p_batch_id
440    and sg.party_orig_system = p_original_system
441    and e.int_row_id (+) = sg.int_row_id
442    and e.request_id (+) = p_request_id
443    and e.interface_table_name (+) = 'HZ_IMP_CLASSIFICS_INT'
444    and hca.code_assignment_id (+) = sg.code_assignment_id
445    and hca.request_id (+) = p_request_id
446    and sg.int_row_id = hzint.rowid (+)
447    and hzint.interface_status(+) = 'D'
448   )
449  group by a) y
450  where x.a = y.a (+)))
451  where r = 1;
452 
453 
454 IF p_original_system <> 'USER_ENTERED'
455 THEN
456 select sst_flag
457 into l_sst_flag
458 from hz_orig_systems_b
459 where orig_system = p_original_system
460 and status='A';
461 
462 IF l_sst_flag='Y'
463 THEN
464   select count(*)
465   into l_rels_updated
466   from hz_imp_relships_sg
467   where batch_id=p_batch_id
468   and action_flag='U'
469   and relationship_type in
470       ('HEADQUARTERS/DIVISION','PARENT/SUBSIDIARY','DOMESTIC_ULTIMATE','GLOBAL_ULTIMATE')
471   and sub_orig_system = p_original_system
472   and batch_mode_flag=p_batch_mode_flag;
473 END IF;
474 END IF;
475 
476   update hz_imp_batch_summary set
480     addresses_inserted = addresses_inserted + l_addresses_inserted,
477     addressuses_inserted = addressuses_inserted + l_addressuses_inserted,
478     addressuses_updated = addressuses_updated + l_addressuses_updated,
479     addressuses_imported = addressuses_imported + l_addressuses_inserted + l_addressuses_updated,
481     addresses_updated = addresses_updated + l_addresses_updated,
482     addresses_imported = addresses_imported + l_addresses_inserted + l_addresses_updated,
483     address_merge_requests = 0, --l_addresses_merge_requests,
484     finnumbers_inserted = finnumbers_inserted + l_finnumbers_inserted,
485     finnumbers_updated = finnumbers_updated + l_finnumbers_updated,
486     finnumbers_imported = finnumbers_imported + l_finnumbers_inserted + l_finnumbers_updated,
487     codeassigns_inserted = codeassigns_inserted + l_codeassigns_inserted,
488     codeassigns_updated = codeassigns_updated + l_codeassigns_updated,
489     codeassigns_imported = codeassigns_imported + l_codeassigns_inserted + l_codeassigns_updated,
490     relationships_inserted = relationships_inserted + l_relationships_inserted,
491     relationships_updated = relationships_updated + l_relationships_updated + l_rels_updated,
492     relationships_imported = relationships_imported + l_relationships_inserted + l_relationships_updated + l_rels_updated,
493     contactroles_inserted = contactroles_inserted + l_contactroles_inserted,
494     contactroles_updated = contactroles_updated + l_contactroles_updated,
495     contactroles_imported = contactroles_imported + l_contactroles_inserted + l_contactroles_updated,
496     contacts_inserted = contacts_inserted + l_contacts_inserted,
497     contacts_updated = contacts_updated + l_contacts_updated,
498     contacts_imported = contacts_imported + l_contacts_inserted + l_contacts_updated,
499     contact_merge_requests = 0, --l_contacts_merge_requests,
500     contactpoints_inserted = contactpoints_inserted + l_contactpoints_inserted,
501     contactpoints_updated = contactpoints_updated + l_contactpoints_updated,
502     contactpoints_imported = contactpoints_imported + l_contactpoints_inserted + l_contactpoints_updated,
503     contactpoint_merge_requests = 0, --l_contactpoints_merge_requests,
504     creditratings_inserted = creditratings_inserted + l_creditratings_inserted,
505     creditratings_updated = creditratings_updated + l_creditratings_updated,
506     creditratings_imported = creditratings_imported + l_creditratings_inserted + l_creditratings_updated,
507     finreports_inserted = finreports_inserted + l_finreports_inserted,
508     finreports_updated = finreports_updated + l_finreports_updated,
509     finreports_imported = finreports_imported + l_finreports_inserted + l_finreports_updated,
510     parties_inserted = parties_inserted + l_parties_inserted,
511     parties_updated = parties_updated + l_parties_updated,
512     parties_imported = parties_imported + l_parties_inserted + l_parties_updated,
513     total_errors  = decode(p_rerun_flag, 'N', l_total_errors, decode (total_errors, 0, l_total_errors, (total_errors - l_total_records_imported))),
514     total_records_imported = total_records_imported + l_total_records_imported + l_rels_updated
515   where batch_id = p_batch_id;
516 /*
517   FND_FILE.PUT_LINE(FND_FILE.LOG, ' l_ADDRESSUSES_INSERTED'|| l_ADDRESSUSES_INSERTED);
518   FND_FILE.PUT_LINE(FND_FILE.LOG, ' l_ADDRESSES_INSERTED'|| l_ADDRESSES_INSERTED);
519   FND_FILE.PUT_LINE(FND_FILE.LOG, ' l_FINNUMBERS_INSERTED'|| l_FINNUMBERS_INSERTED);
520   FND_FILE.PUT_LINE(FND_FILE.LOG, ' l_CODEASSIGNS_INSERTED'|| l_CODEASSIGNS_INSERTED);
521   FND_FILE.PUT_LINE(FND_FILE.LOG, ' l_RELATIONSHIPS_INSERTED'|| l_RELATIONSHIPS_INSERTED);
522   FND_FILE.PUT_LINE(FND_FILE.LOG, ' l_CONTACTROLES_INSERTED'|| l_CONTACTROLES_INSERTED);
523   FND_FILE.PUT_LINE(FND_FILE.LOG, ' l_CONTACTS_INSERTED'|| l_CONTACTS_INSERTED);
524   FND_FILE.PUT_LINE(FND_FILE.LOG, ' l_CONTACTPOINTS_INSERTED'|| l_CONTACTPOINTS_INSERTED);
525   FND_FILE.PUT_LINE(FND_FILE.LOG, ' l_CREDITRATINGS_INSERTED'|| l_CREDITRATINGS_INSERTED);
526   FND_FILE.PUT_LINE(FND_FILE.LOG, ' l_FINREPORTS_INSERTED'|| l_FINREPORTS_INSERTED);
527   FND_FILE.PUT_LINE(FND_FILE.LOG, ' l_PARTIES_INSERTED'|| l_PARTIES_INSERTED);
528   FND_FILE.PUT_LINE(FND_FILE.LOG, ' l_ADDRESSUSES_UPDATED'|| l_ADDRESSUSES_UPDATED);
529   FND_FILE.PUT_LINE(FND_FILE.LOG, ' l_ADDRESSES_UPDATED'|| l_ADDRESSES_UPDATED);
530   FND_FILE.PUT_LINE(FND_FILE.LOG, ' l_FINNUMBERS_UPDATED'|| l_FINNUMBERS_UPDATED);
531   FND_FILE.PUT_LINE(FND_FILE.LOG, ' l_CODEASSIGNS_UPDATED'|| l_CODEASSIGNS_UPDATED);
532   FND_FILE.PUT_LINE(FND_FILE.LOG, ' l_RELATIONSHIPS_UPDATED'|| l_RELATIONSHIPS_UPDATED);
533   FND_FILE.PUT_LINE(FND_FILE.LOG, ' l_CONTACTROLES_UPDATED'|| l_CONTACTROLES_UPDATED);
534   FND_FILE.PUT_LINE(FND_FILE.LOG, ' l_CONTACTS_UPDATED'|| l_CONTACTS_UPDATED);
535   FND_FILE.PUT_LINE(FND_FILE.LOG, ' l_CONTACTPOINTS_UPDATED'|| l_CONTACTPOINTS_UPDATED);
536   FND_FILE.PUT_LINE(FND_FILE.LOG, ' l_CREDITRATINGS_UPDATED'|| l_CREDITRATINGS_UPDATED);
537   FND_FILE.PUT_LINE(FND_FILE.LOG, ' l_FINREPORTS_UPDATED'|| l_FINREPORTS_UPDATED);
538   FND_FILE.PUT_LINE(FND_FILE.LOG, ' l_PARTIES_UPDATED'|| l_PARTIES_UPDATED);
539   FND_FILE.PUT_LINE(FND_FILE.LOG, 'P_BATCH_MODE_FLAG:'||P_BATCH_MODE_FLAG);
540   FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_total_errors :='||l_total_errors);
541   FND_FILE.PUT_LINE(FND_FILE.LOG, '  l_ADDRESSUSES_ERRORED:'||l_ADDRESSUSES_ERRORED);
542   FND_FILE.PUT_LINE(FND_FILE.LOG, '  l_ADDRESSES_ERRORED +'||l_ADDRESSES_ERRORED );
543   FND_FILE.PUT_LINE(FND_FILE.LOG, '  l_FINNUMBERS_ERRORED +'||l_FINNUMBERS_ERRORED);
544   FND_FILE.PUT_LINE(FND_FILE.LOG, '  l_CODEASSIGNS_ERRORED +'||l_CODEASSIGNS_ERRORED);
545   FND_FILE.PUT_LINE(FND_FILE.LOG, '  l_RELATIONSHIPS_ERRORED +'||l_RELATIONSHIPS_ERRORED);
546   FND_FILE.PUT_LINE(FND_FILE.LOG, '  l_CONTACTROLES_ERRORED +'||l_CONTACTROLES_ERRORED);
550   FND_FILE.PUT_LINE(FND_FILE.LOG, '  l_FINREPORTS_ERRORED +'||l_FINREPORTS_ERRORED);
547   FND_FILE.PUT_LINE(FND_FILE.LOG, '  l_CONTACTS_ERRORED +'||l_CONTACTS_ERRORED);
548   FND_FILE.PUT_LINE(FND_FILE.LOG, '  l_CONTACTPOINTS_ERRORED +'||l_CONTACTPOINTS_ERRORED);
549   FND_FILE.PUT_LINE(FND_FILE.LOG, '  l_CREDITRATINGS_ERRORED +'||l_CREDITRATINGS_ERRORED);
551   FND_FILE.PUT_LINE(FND_FILE.LOG, '  l_PARTIES_ERRORED:'||l_PARTIES_ERRORED) ;
552 */
553 
554 /*
555  -- updating the batch summary table
556  -- with all the counts. Also, initializing the merge counts to zero so that
557  -- if and when DQM populates them, the merge counts will have appropriate values
558  -- otherwise, they will show zeros.
559 
560  UPDATE HZ_IMP_BATCH_SUMMARY SET
561   ADDRESSUSES_INSERTED = ADDRESSUSES_INSERTED + l_ADDRESSUSES_INSERTED,
562   ADDRESSUSES_UPDATED = ADDRESSUSES_UPDATED + l_ADDRESSUSES_UPDATED,
563   ADDRESSUSES_IMPORTED = ADDRESSUSES_IMPORTED + l_ADDRESSUSES_IMPORTED,
564   ADDRESSES_INSERTED = ADDRESSES_INSERTED + l_ADDRESSES_INSERTED,
565   ADDRESSES_UPDATED = ADDRESSES_UPDATED + l_ADDRESSES_UPDATED,
566   ADDRESSES_IMPORTED = ADDRESSES_IMPORTED + l_ADDRESSES_IMPORTED,
567   ADDRESS_MERGE_REQUESTS = 0, --l_ADDRESSES_MERGE_REQUESTS,
568   FINNUMBERS_INSERTED = FINNUMBERS_INSERTED + l_FINNUMBERS_INSERTED,
569   FINNUMBERS_UPDATED = FINNUMBERS_UPDATED + l_FINNUMBERS_UPDATED,
570   FINNUMBERS_IMPORTED = FINNUMBERS_IMPORTED + l_FINNUMBERS_IMPORTED,
571   CODEASSIGNS_INSERTED = CODEASSIGNS_INSERTED + l_CODEASSIGNS_INSERTED,
572   CODEASSIGNS_UPDATED = CODEASSIGNS_UPDATED + l_CODEASSIGNS_UPDATED,
573   CODEASSIGNS_IMPORTED = CODEASSIGNS_IMPORTED + l_CODEASSIGNS_IMPORTED,
574   RELATIONSHIPS_INSERTED = RELATIONSHIPS_INSERTED + l_RELATIONSHIPS_INSERTED,
575   RELATIONSHIPS_UPDATED = RELATIONSHIPS_UPDATED + l_RELATIONSHIPS_UPDATED,
576   RELATIONSHIPS_IMPORTED = RELATIONSHIPS_IMPORTED + l_RELATIONSHIPS_IMPORTED,
577   CONTACTROLES_INSERTED = CONTACTROLES_INSERTED + l_CONTACTROLES_INSERTED,
578   CONTACTROLES_UPDATED = CONTACTROLES_UPDATED + l_CONTACTROLES_UPDATED,
579   CONTACTROLES_IMPORTED = CONTACTROLES_IMPORTED + l_CONTACTROLES_IMPORTED,
580   CONTACTS_INSERTED = CONTACTS_INSERTED + l_CONTACTS_INSERTED,
581   CONTACTS_UPDATED = CONTACTS_UPDATED + l_CONTACTS_UPDATED,
582   CONTACTS_IMPORTED = CONTACTS_IMPORTED + l_CONTACTS_IMPORTED,
583   CONTACT_MERGE_REQUESTS = 0, --l_CONTACTS_MERGE_REQUESTS,
584   CONTACTPOINTS_INSERTED = CONTACTPOINTS_INSERTED + l_CONTACTPOINTS_INSERTED,
585   CONTACTPOINTS_UPDATED = CONTACTPOINTS_UPDATED + l_CONTACTPOINTS_UPDATED,
586   CONTACTPOINTS_IMPORTED = CONTACTPOINTS_IMPORTED + l_CONTACTPOINTS_IMPORTED,
587   CONTACTPOINT_MERGE_REQUESTS = 0, --l_CONTACTPOINTS_MERGE_REQUESTS,
588   CREDITRATINGS_INSERTED = CREDITRATINGS_INSERTED + l_CREDITRATINGS_INSERTED,
589   CREDITRATINGS_UPDATED = CREDITRATINGS_UPDATED + l_CREDITRATINGS_UPDATED,
590   CREDITRATINGS_IMPORTED = CREDITRATINGS_IMPORTED + l_CREDITRATINGS_IMPORTED,
591   FINREPORTS_INSERTED = FINREPORTS_INSERTED + l_FINREPORTS_INSERTED,
592   FINREPORTS_UPDATED = FINREPORTS_UPDATED + l_FINREPORTS_UPDATED,
593   FINREPORTS_IMPORTED = FINREPORTS_IMPORTED + l_FINREPORTS_IMPORTED,
594   PARTIES_INSERTED = PARTIES_INSERTED + l_PARTIES_INSERTED,
595   PARTIES_UPDATED = PARTIES_UPDATED + l_PARTIES_UPDATED,
596   PARTIES_IMPORTED = PARTIES_IMPORTED + l_PARTIES_IMPORTED,
597   PARTY_MERGE_REQUESTS = 0, -- l_PARTIES_MERGE_REQUESTS,
598   PARTIES_AUTO_MERGED    = 0, --l_parties_auto_merged,
599   TOTAL_MERGE_REQUESTS   = 0, --l_total_merge_requests,
600   TOTAL_ERRORS  = DECODE (p_rerun_flag, 'N', l_total_errors, (TOTAL_ERRORS - l_total_errors)),
601   TOTAL_RECORDS_IMPORTED = TOTAL_RECORDS_IMPORTED + l_total_records_imported
602  WHERE BATCH_ID = P_BATCH_ID;
603 */
604 END post_import_counts;
605 
606 /*
607   Procedure: what_if_import_counts()
608   These are the counts of potential entries that might
609   happen if the dataload process is allowed to go through to
610   completion.  These are per  per run counts.
611   This procedure is called from stage 2 of teh dataload process only
612   when teh what_IF option is chosen.
613 */
614 
615   PROCEDURE what_if_import_counts
616      ( P_BATCH_ID    IN HZ_IMP_BATCH_SUMMARY.BATCH_ID%TYPE,
617        P_ORIGINAL_SYSTEM IN HZ_IMP_BATCH_SUMMARY.ORIGINAL_SYSTEM%TYPE) IS
618 -- Declare the variables
619  l_NEW_UNIQUE_ADDRESSUSES NUMBER;
620  l_NEW_UNIQUE_ADDRESSES NUMBER;
621  l_NEW_UNIQUE_FINNUMBERS NUMBER;
622  l_NEW_UNIQUE_CODEASSIGNS NUMBER;
623  l_NEW_UNIQUE_RELATIONSHIPS NUMBER;
624  l_NEW_UNIQUE_CONTACTROLES NUMBER;
625  l_NEW_UNIQUE_CONTACTS NUMBER;
626  l_NEW_UNIQUE_CONTACTPOINTS NUMBER;
627  l_NEW_UNIQUE_CREDITRATINGS NUMBER;
628  l_NEW_UNIQUE_FINREPORTS NUMBER;
629  l_NEW_UNIQUE_PARTIES NUMBER;
630  l_EXISTING_ADDRESSUSES NUMBER;
631  l_EXISTING_ADDRESSES NUMBER;
632  l_EXISTING_FINNUMBERS NUMBER;
633  l_EXISTING_CODEASSIGNS NUMBER;
634  l_EXISTING_RELATIONSHIPS NUMBER;
635  l_EXISTING_CONTACTROLES NUMBER;
636  l_EXISTING_CONTACTS NUMBER;
637  l_EXISTING_CONTACTPOINTS NUMBER;
638  l_EXISTING_CREDITRATINGS NUMBER;
639  l_EXISTING_FINREPORTS NUMBER;
640  l_EXISTING_PARTIES NUMBER;
641  l_REMOVED_ADDRESSUSES NUMBER;
642  l_REMOVED_ADDRESSES NUMBER;
643  l_REMOVED_FINNUMBERS NUMBER;
644  l_REMOVED_CODEASSIGNS NUMBER;
645  l_REMOVED_RELATIONSHIPS NUMBER;
646  l_REMOVED_CONTACTROLES NUMBER;
647  l_REMOVED_CONTACTS NUMBER;
648  l_REMOVED_CONTACTPOINTS NUMBER;
649  l_REMOVED_CREDITRATINGS NUMBER;
650  l_REMOVED_FINREPORTS NUMBER;
651  l_REMOVED_PARTIES NUMBER;
652  l_removed_by_user NUMBER;
653 
654 BEGIN
658    FROM HZ_IMP_ADDRESSUSES_SG  INT
655   -- number of new and unique recs
656      -- for l_NEW_UNIQUE_ADDRESSUSES
657   SELECT  count(INT.BATCH_ID) into  l_NEW_UNIQUE_ADDRESSUSES
659   WHERE INT.BATCH_ID = P_BATCH_ID AND
660        INT.PARTY_ORIG_SYSTEM = P_ORIGINAL_SYSTEM
661     AND INT.ACTION_FLAG = 'I' ;
662      -- for l_NEW_UNIQUE_ADDRESSES
663   SELECT  count(INT.BATCH_ID) into  l_NEW_UNIQUE_ADDRESSES
664    FROM HZ_IMP_ADDRESSES_INT INT, HZ_IMP_ADDRESSES_SG  SG
665   WHERE INT.BATCH_ID = P_BATCH_ID AND
666      INT.PARTY_ORIG_SYSTEM = P_ORIGINAL_SYSTEM AND
667      INT.BATCH_ID = SG.BATCH_ID AND
668      INT.PARTY_ORIG_SYSTEM = SG.PARTY_ORIG_SYSTEM AND
669      INT.rowid = sg.int_row_id
670   AND INT.DQM_ACTION_FLAG IS NULL  AND SG.ACTION_FLAG = 'I' ;
671     -- for l_NEW_UNIQUE_FINNUMBERS
672   SELECT  count(INT.BATCH_ID) into  l_NEW_UNIQUE_FINNUMBERS
673    FROM HZ_IMP_FINNUMBERS_SG  INT
674   WHERE INT.BATCH_ID = P_BATCH_ID AND
675        INT.PARTY_ORIG_SYSTEM = P_ORIGINAL_SYSTEM
676     AND INT.ACTION_FLAG = 'I' ;
677     -- for l_NEW_UNIQUE_CODEASSIGNS
678   SELECT  count(INT.BATCH_ID) into  l_NEW_UNIQUE_CODEASSIGNS
679    FROM HZ_IMP_CLASSIFICS_SG INT
680   WHERE INT.BATCH_ID = P_BATCH_ID AND
681        INT.PARTY_ORIG_SYSTEM = P_ORIGINAL_SYSTEM
682     AND INT.ACTION_FLAG = 'I' ;
683       -- for l_NEW_UNIQUE_RELATIONSHIPS
684   SELECT  count(INT.BATCH_ID) into  l_NEW_UNIQUE_RELATIONSHIPS
685    FROM HZ_IMP_RELSHIPS_SG INT
686   WHERE INT.BATCH_ID = P_BATCH_ID AND
687        INT.SUB_ORIG_SYSTEM = P_ORIGINAL_SYSTEM
688     AND INT.ACTION_FLAG = 'I' ;
689       -- for l_NEW_UNIQUE_CONTACTROLES
690   SELECT  count(INT.BATCH_ID) into  l_NEW_UNIQUE_CONTACTROLES
691    FROM HZ_IMP_CONTACTROLES_SG INT
692   WHERE INT.BATCH_ID = P_BATCH_ID AND
693        INT.SUB_ORIG_SYSTEM = P_ORIGINAL_SYSTEM
694     AND INT.ACTION_FLAG = 'I' ;
695       -- for l_NEW_UNIQUE_CONTACTS
696   SELECT  count(INT.BATCH_ID) into  l_NEW_UNIQUE_CONTACTS
697    FROM HZ_IMP_CONTACTS_INT INT, HZ_IMP_CONTACTS_SG  SG
698   WHERE INT.BATCH_ID = P_BATCH_ID AND
699        INT.SUB_ORIG_SYSTEM = P_ORIGINAL_SYSTEM AND
700      INT.BATCH_ID = SG.BATCH_ID AND
701      INT.SUB_ORIG_SYSTEM = SG.SUB_ORIG_SYSTEM AND
702      INT.rowid = sg.int_row_id
703   AND INT.DQM_ACTION_FLAG IS NULL  AND SG.ACTION_FLAG = 'I' ;
704     -- for l_NEW_UNIQUE_CONTACTPOINTS
705   SELECT  count(INT.BATCH_ID) into  l_NEW_UNIQUE_CONTACTPOINTS
706    FROM HZ_IMP_CONTACTPTS_INT INT, HZ_IMP_CONTACTPTS_SG  SG
707   WHERE INT.BATCH_ID = P_BATCH_ID AND
708        INT.PARTY_ORIG_SYSTEM = P_ORIGINAL_SYSTEM AND
709      INT.BATCH_ID = SG.BATCH_ID AND
710      INT.PARTY_ORIG_SYSTEM = SG.PARTY_ORIG_SYSTEM AND
711      INT.rowid = sg.int_row_id
712   AND INT.DQM_ACTION_FLAG IS NULL  AND SG.ACTION_FLAG = 'I' ;
713     -- for l_NEW_UNIQUE_CREDITRATINGS
714   SELECT  count(INT.BATCH_ID) into  l_NEW_UNIQUE_CREDITRATINGS
715    FROM HZ_IMP_CREDITRTNGS_SG INT
716   WHERE INT.BATCH_ID = P_BATCH_ID AND
717        INT.PARTY_ORIG_SYSTEM = P_ORIGINAL_SYSTEM
718     AND INT.ACTION_FLAG = 'I' ;
719     -- for l_NEW_UNIQUE_FINREPORTS
720   SELECT  count(INT.BATCH_ID) into  l_NEW_UNIQUE_FINREPORTS
721    FROM HZ_IMP_FINREPORTS_SG INT
722   WHERE INT.BATCH_ID = P_BATCH_ID AND
723        INT.PARTY_ORIG_SYSTEM = P_ORIGINAL_SYSTEM
724     AND INT.ACTION_FLAG = 'I' ;
725     -- for l_NEW_UNIQUE_PARTIES
726   SELECT  count(INT.BATCH_ID) into  l_NEW_UNIQUE_PARTIES
727    FROM HZ_IMP_PARTIES_INT INT, HZ_IMP_PARTIES_SG  SG
728   WHERE INT.BATCH_ID = P_BATCH_ID AND
729        INT.PARTY_ORIG_SYSTEM = P_ORIGINAL_SYSTEM AND
730      INT.BATCH_ID = SG.BATCH_ID AND
731      INT.PARTY_ORIG_SYSTEM = SG.PARTY_ORIG_SYSTEM AND
732      INT.rowid = sg.int_row_id
733   AND INT.DQM_ACTION_FLAG IS NULL  AND SG.ACTION_FLAG = 'I' ;
734   -- counts of existing entity recs
735    -- for l_EXISTING_ADDRESSUSES
736   SELECT  count(INT.BATCH_ID) into  l_EXISTING_ADDRESSUSES
737    FROM HZ_IMP_ADDRESSUSES_SG INT
738   WHERE INT.BATCH_ID = P_BATCH_ID AND
739        INT.PARTY_ORIG_SYSTEM = P_ORIGINAL_SYSTEM
740     AND INT.ACTION_FLAG = 'U' ;
741     -- for l_EXISTING_ADDRESSES
742   SELECT  count(INT.BATCH_ID) into  l_EXISTING_ADDRESSES
743    FROM HZ_IMP_ADDRESSES_SG INT
744   WHERE INT.BATCH_ID = P_BATCH_ID AND
745        INT.PARTY_ORIG_SYSTEM = P_ORIGINAL_SYSTEM
746     AND INT.ACTION_FLAG = 'U' ;
747     -- for l_EXISTING_FINNUMBERS
748   SELECT  count(INT.BATCH_ID) into  l_EXISTING_FINNUMBERS
749    FROM HZ_IMP_FINNUMBERS_SG INT
750   WHERE INT.BATCH_ID = P_BATCH_ID AND
751        INT.PARTY_ORIG_SYSTEM = P_ORIGINAL_SYSTEM
752     AND INT.ACTION_FLAG = 'U' ;
753     -- for l_EXISTING_CODEASSIGNS
754   SELECT  count(INT.BATCH_ID) into  l_EXISTING_CODEASSIGNS
755    FROM HZ_IMP_CLASSIFICS_SG INT
756   WHERE INT.BATCH_ID = P_BATCH_ID AND
757        INT.PARTY_ORIG_SYSTEM = P_ORIGINAL_SYSTEM
758     AND INT.ACTION_FLAG = 'U' ;
759     -- for l_EXISTING_RELATIONSHIPS
760   SELECT  count(INT.BATCH_ID) into  l_EXISTING_RELATIONSHIPS
761    FROM HZ_IMP_RELSHIPS_SG INT
762   WHERE INT.BATCH_ID = P_BATCH_ID AND
763        INT.SUB_ORIG_SYSTEM = P_ORIGINAL_SYSTEM
764     AND INT.ACTION_FLAG = 'U' ;
765     -- for l_EXISTING_CONTACTROLES
766   SELECT  count(INT.BATCH_ID) into  l_EXISTING_CONTACTROLES
767    FROM HZ_IMP_CONTACTROLES_SG INT
768   WHERE INT.BATCH_ID = P_BATCH_ID AND
772   SELECT  count(INT.BATCH_ID) into  l_EXISTING_CONTACTS
769        INT.SUB_ORIG_SYSTEM = P_ORIGINAL_SYSTEM
770     AND INT.ACTION_FLAG = 'U' ;
771     -- for l_EXISTING_CONTACTS
773    FROM HZ_IMP_CONTACTS_SG INT
774   WHERE INT.BATCH_ID = P_BATCH_ID AND
775        INT.CONTACT_ORIG_SYSTEM = P_ORIGINAL_SYSTEM
776     AND INT.ACTION_FLAG = 'U' ;
777     -- for l_EXISTING_CONTACTPOINTS
778   SELECT  count(INT.BATCH_ID) into  l_EXISTING_CONTACTPOINTS
779    FROM HZ_IMP_CONTACTPTS_SG INT
780   WHERE INT.BATCH_ID = P_BATCH_ID AND
781        INT.PARTY_ORIG_SYSTEM = P_ORIGINAL_SYSTEM
782     AND INT.ACTION_FLAG = 'U' ;
783     -- for l_EXISTING_CREDITRATINGS
784   SELECT  count(INT.BATCH_ID) into  l_EXISTING_CREDITRATINGS
785    FROM HZ_IMP_CREDITRTNGS_SG INT
786   WHERE INT.BATCH_ID = P_BATCH_ID AND
787        INT.PARTY_ORIG_SYSTEM = P_ORIGINAL_SYSTEM
788     AND INT.ACTION_FLAG = 'U' ;
789     -- for l_EXISTING_FINREPORTS
790   SELECT  count(INT.BATCH_ID) into  l_EXISTING_FINREPORTS
791    FROM HZ_IMP_FINREPORTS_SG INT
792   WHERE INT.BATCH_ID = P_BATCH_ID AND
793        INT.PARTY_ORIG_SYSTEM = P_ORIGINAL_SYSTEM
794     AND INT.ACTION_FLAG = 'U' ;
795     -- for l_EXISTING_PARTIES
796   SELECT  count(INT.BATCH_ID) into  l_EXISTING_PARTIES
797    FROM HZ_IMP_PARTIES_SG INT
798   WHERE INT.BATCH_ID = P_BATCH_ID AND
799        INT.PARTY_ORIG_SYSTEM = P_ORIGINAL_SYSTEM
800     AND INT.ACTION_FLAG = 'U' ;
801 
802   -- counts of removed entity recs
803   SELECT  count(INT.BATCH_ID) into  l_REMOVED_ADDRESSUSES
804    FROM HZ_IMP_ADDRESSUSES_INT INT
805   WHERE INT.BATCH_ID = P_BATCH_ID AND
806        INT.PARTY_ORIG_SYSTEM = P_ORIGINAL_SYSTEM
807     AND INT.INTERFACE_STATUS = 'R' ;
808   SELECT  count(INT.BATCH_ID) into  l_REMOVED_ADDRESSES
809    FROM HZ_IMP_ADDRESSES_INT INT
810   WHERE INT.BATCH_ID = P_BATCH_ID AND
811        INT.PARTY_ORIG_SYSTEM = P_ORIGINAL_SYSTEM
812     AND INT.INTERFACE_STATUS = 'R' ;
813   SELECT  count(INT.BATCH_ID) into  l_REMOVED_FINNUMBERS
814    FROM HZ_IMP_FINNUMBERS_INT INT
815   WHERE INT.BATCH_ID = P_BATCH_ID AND
816        INT.PARTY_ORIG_SYSTEM = P_ORIGINAL_SYSTEM
817     AND INT.INTERFACE_STATUS = 'R' ;
818   SELECT  count(INT.BATCH_ID) into  l_REMOVED_CODEASSIGNS
819    FROM HZ_IMP_CLASSIFICS_INT INT
820   WHERE INT.BATCH_ID = P_BATCH_ID AND
821        INT.PARTY_ORIG_SYSTEM = P_ORIGINAL_SYSTEM
822     AND INT.INTERFACE_STATUS = 'R' ;
823   SELECT  count(INT.BATCH_ID) into  l_REMOVED_RELATIONSHIPS
824    FROM HZ_IMP_RELSHIPS_INT INT
825   WHERE INT.BATCH_ID = P_BATCH_ID AND
826        INT.SUB_ORIG_SYSTEM = P_ORIGINAL_SYSTEM
827     AND INT.INTERFACE_STATUS = 'R' ;
828   SELECT  count(INT.BATCH_ID) into  l_REMOVED_CONTACTROLES
829    FROM HZ_IMP_CONTACTROLES_INT INT
830   WHERE INT.BATCH_ID = P_BATCH_ID AND
831        INT.CONTACT_ORIG_SYSTEM = P_ORIGINAL_SYSTEM
832     AND INT.INTERFACE_STATUS = 'R' ;
833   SELECT  count(INT.BATCH_ID) into  l_REMOVED_CONTACTS
834    FROM HZ_IMP_CONTACTS_INT INT
835   WHERE INT.BATCH_ID = P_BATCH_ID AND
836        INT.CONTACT_ORIG_SYSTEM = P_ORIGINAL_SYSTEM
837     AND INT.INTERFACE_STATUS = 'R' ;
838   SELECT  count(INT.BATCH_ID) into  l_REMOVED_CONTACTPOINTS
839    FROM HZ_IMP_CONTACTPTS_INT INT
840   WHERE INT.BATCH_ID = P_BATCH_ID AND
841        INT.PARTY_ORIG_SYSTEM = P_ORIGINAL_SYSTEM
842     AND INT.INTERFACE_STATUS = 'R' ;
843   SELECT  count(INT.BATCH_ID) into  l_REMOVED_CREDITRATINGS
844    FROM HZ_IMP_CREDITRTNGS_INT INT
845   WHERE INT.BATCH_ID = P_BATCH_ID AND
846        INT.PARTY_ORIG_SYSTEM = P_ORIGINAL_SYSTEM
847     AND INT.INTERFACE_STATUS = 'R' ;
848   SELECT  count(INT.BATCH_ID) into  l_REMOVED_FINREPORTS
849    FROM HZ_IMP_FINREPORTS_INT INT
850   WHERE INT.BATCH_ID = P_BATCH_ID AND
851        INT.PARTY_ORIG_SYSTEM = P_ORIGINAL_SYSTEM
852     AND INT.INTERFACE_STATUS = 'R' ;
853   SELECT  count(INT.BATCH_ID) into  l_REMOVED_PARTIES
854    FROM HZ_IMP_PARTIES_INT INT
855   WHERE INT.BATCH_ID = P_BATCH_ID AND
856        INT.PARTY_ORIG_SYSTEM = P_ORIGINAL_SYSTEM
857     AND INT.INTERFACE_STATUS = 'R' ;
858 
859   -- compute total number of removed recs
860   l_removed_by_user :=
861     l_REMOVED_ADDRESSUSES +
862     l_REMOVED_ADDRESSES +
863     l_REMOVED_FINNUMBERS +
864     l_REMOVED_CODEASSIGNS +
865     l_REMOVED_RELATIONSHIPS +
866     l_REMOVED_CONTACTROLES +
867     l_REMOVED_CONTACTS +
868     l_REMOVED_CONTACTPOINTS +
869     l_REMOVED_CREDITRATINGS +
870     l_REMOVED_FINREPORTS +
871     l_REMOVED_PARTIES ;
872 
873    -- update stmt
874  UPDATE HZ_IMP_BATCH_SUMMARY SET
875   NEW_UNIQUE_ADDRESSUSES = l_NEW_UNIQUE_ADDRESSUSES,
876   NEW_UNIQUE_ADDRESSES = l_NEW_UNIQUE_ADDRESSES,
877   NEW_UNIQUE_FINNUMBERS = l_NEW_UNIQUE_FINNUMBERS,
878   NEW_UNIQUE_CODEASSIGNS = l_NEW_UNIQUE_CODEASSIGNS,
879   NEW_UNIQUE_RELATIONSHIPS = l_NEW_UNIQUE_RELATIONSHIPS,
880   NEW_UNIQUE_CONTACTROLES = l_NEW_UNIQUE_CONTACTROLES,
881   NEW_UNIQUE_CONTACTS = l_NEW_UNIQUE_CONTACTS,
882   NEW_UNIQUE_CONTACTPOINTS = l_NEW_UNIQUE_CONTACTPOINTS,
883   NEW_UNIQUE_CREDITRATINGS = l_NEW_UNIQUE_CREDITRATINGS,
884   NEW_UNIQUE_FINREPORTS = l_NEW_UNIQUE_FINREPORTS,
885   NEW_UNIQUE_PARTIES = l_NEW_UNIQUE_PARTIES,
886   EXISTING_ADDRESSUSES = l_EXISTING_ADDRESSUSES,
887   EXISTING_ADDRESSES = l_EXISTING_ADDRESSES,
888   EXISTING_FINNUMBERS = l_EXISTING_FINNUMBERS,
889   EXISTING_CODEASSIGNS = l_EXISTING_CODEASSIGNS,
890   EXISTING_RELATIONSHIPS = l_EXISTING_RELATIONSHIPS,
891   EXISTING_CONTACTROLES = l_EXISTING_CONTACTROLES,
892   EXISTING_CONTACTS = l_EXISTING_CONTACTS,
893   EXISTING_CONTACTPOINTS = l_EXISTING_CONTACTPOINTS,
894   EXISTING_CREDITRATINGS = l_EXISTING_CREDITRATINGS,
895   EXISTING_FINREPORTS = l_EXISTING_FINREPORTS,
896   EXISTING_PARTIES = l_EXISTING_PARTIES,
897     removed_by_user = l_removed_by_user
898  WHERE BATCH_ID = P_BATCH_ID;
899 END what_if_import_counts;
900 
901 END HZ_IMP_LOAD_BATCH_COUNTS_PKG;