1 PACKAGE BODY qa_tca_pkg AS
2 /* $Header: qatcab.pls 120.0.12020000.2 2012/07/03 14:40:18 ntungare ship $ */
3
4
5 PROCEDURE party_merge(
6 p_entity_name IN VARCHAR2,
7 p_from_id IN NUMBER,
8 x_to_id IN OUT NOCOPY NUMBER,
9 p_from_fk_id IN NUMBER,
10 p_to_fk_id IN NUMBER,
11 p_parent_entity_name IN VARCHAR2,
12 p_batch_id IN NUMBER,
13 p_batch_party_id IN NUMBER,
14 x_return_status OUT NOCOPY VARCHAR2) IS
15
16 --
17 -- This is Oracle Quality's party merge API provided to TCA.
18 -- See Bug 3741531
19 --
20 -- p_entity_name should always be 'QA_RESULTS'.
21 --
22 -- p_from_id is NULL for a bulk party merge.
23 --
24 -- x_to_id is the new ID for merging purpose. Since we are
25 -- doing transfer, returning the same p_from_id.
26 --
27 -- p_from_fk_id is the party_id to be changed.
28 --
29 -- p_to_fk_id is the new party_id.
30 --
31 -- p_parent_entity_name should be 'HZ_PARTIES' since we are
32 -- only have PARTY_ID as a foreign key.
33 --
34 -- p_batch_id is an internal TCA ID, unused in this procedure.
35 --
36 -- p_batch_party_id is an internal TCA ID, unused in this procedure.
37 --
38 -- x_return_status will be
39 -- fnd_api.g_ret_sts_success if operation successfully completed.
40 -- fnd_api.g_ret_sts_unexp_error if there is any exception.
41 --
42
43 CURSOR c IS
44 SELECT qpc.plan_id
45 FROM qa_plan_chars qpc, qa_plans qp
46 WHERE qpc.plan_id = qp.plan_id AND
47 qpc.char_id = qa_ss_const.party_name AND
48 qp.organization_id <> 0;
49 l_dummy NUMBER;
50
51 BEGIN
52
53 x_to_id := p_from_id;
54 x_return_status := fnd_api.g_ret_sts_success;
55
56 --
57 -- We shall handle only HZ_PARTIES in QA_RESULTS.
58 --
59 IF p_entity_name = 'QA_RESULTS' AND
60 p_parent_entity_name = 'HZ_PARTIES' AND
61 p_from_fk_id <> p_to_fk_id THEN
62
63 --
64 -- This is an optimization to quickly return if there is
65 -- no party element being used in any real collection plan.
66 -- Since Party is a very obscure element, 99% of the time,
67 -- this RETURN will be executed, guaranteeing good performance.
68 --
69 -- Should customer uses Party element and the quality result
70 -- table is big, customer is adviced to create a custom index
71 -- on qa_results.party_id. This statement is also documented
72 -- in TCA's party merge implementation guide.
73 -- See bug 3741531 text.
74 --
75 OPEN c;
76 FETCH c INTO l_dummy;
77 IF c%notfound THEN
78 CLOSE c;
79 RETURN;
80 END IF;
81 CLOSE c;
82
83 UPDATE qa_results qr
84 SET qr.party_id = p_to_fk_id,
85 qr.last_update_date = sysdate,
86 qr.qa_last_update_date = sysdate,
87 qr.last_update_login = hz_utility_pub.last_update_login,
88 qr.last_updated_by = hz_utility_pub.user_id,
89 qr.qa_last_updated_by = hz_utility_pub.user_id,
90 qr.request_id = hz_utility_pub.request_id,
91 qr.program_application_id = hz_utility_pub.program_application_id,
92 qr.program_id = hz_utility_pub.program_id,
93 qr.program_update_date = sysdate
94 WHERE qr.party_id = p_from_fk_id;
95 END IF;
96
97 EXCEPTION
98 WHEN OTHERS THEN
99 fnd_message.set_name('AR', 'HZ_MERGE_SQL_ERROR');
100 fnd_message.set_token('ERROR', sqlerrm);
101 fnd_msg_pub.add;
102 x_return_status := fnd_api.g_ret_sts_unexp_error;
103
104 END party_merge;
105
106
107 END qa_tca_pkg;
[Home] [Help]