[Home] [Help]
PACKAGE BODY: APPS.OEP_CMERGE_OESET
Source
1 PACKAGE BODY OEP_CMERGE_OESET AS
2 /* $Header: oesetpb.pls 115.1 99/07/16 08:28:03 porting shi $ */
3
4 /*---------------------------- PRIVATE VARIABLES ----------------------------*/
5 g_count NUMBER := 0;
6
7
8 /*--------------------------- PRIVATE ROUTINES ------------------------------*/
9
10 PROCEDURE oe_ag (req_id NUMBER, set_num NUMBER, process_mode VARCHAR2) is
11
12 CURSOR c1 is
13 select agreement_id
14 from so_agreements
15 where invoice_to_site_use_id in (select m.duplicate_site_id
16 from ra_customer_merges m
17 where m.process_flag = 'N'
18 and m.request_id = req_id
19 and m.set_number = set_num)
20 for update nowait;
21
22 CURSOR c2 is
23 select agreement_id
24 from so_agreements
25 where customer_id in (select m.duplicate_id
26 from ra_customer_merges m
27 where m.process_flag = 'N'
28 and m.request_id = req_id
29 and m.set_number = set_num)
30 for update nowait;
31
32 BEGIN
33
34 arp_message.set_line( 'OEP_CMERGE_OESET.OE_AG()+' );
35
36 /*-----------------------------+
37 | SO_AGREEMENTS |
38 +-----------------------------*/
39 /* both customer and site level */
40
41 IF( process_mode = 'LOCK' ) THEN
42
43 arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
44 arp_message.set_token( 'TABLE_NAME', 'SO_AGREEMENTS', FALSE );
45
46 open c1;
47 close c1;
48
49 open c2;
50 close c2;
51
52 ELSE
53
54
55 /* site level update */
56 arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
57 arp_message.set_token( 'TABLE_NAME', 'SO_AGREEMENTS', FALSE );
58
59 UPDATE SO_AGREEMENTS a
60 set (invoice_to_site_use_id) = (select distinct m.customer_site_id
61 from ra_customer_merges m
62 where a.invoice_to_site_use_id =
63 m.duplicate_site_id
64 and m.request_id = req_id
65 and m.process_flag = 'N'
66 and m.set_number = set_num),
67 last_update_date = sysdate,
68 last_updated_by = arp_standard.profile.user_id,
69 last_update_login = arp_standard.profile.last_update_login
70 where invoice_to_site_use_id in (select m.duplicate_site_id
71 from ra_customer_merges m
72 where m.process_flag = 'N'
73 and m.request_id = req_id
74 and m.set_number = set_num);
75
76 g_count := sql%rowcount;
77
78 arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
79 arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
80
81 /* customer level update */
82 arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
83 arp_message.set_token( 'TABLE_NAME', 'SO_AGREEMENTS', FALSE );
84
85 UPDATE SO_AGREEMENTS a
86 set customer_id = (select distinct m.customer_id
87 from ra_customer_merges m
88 where a.customer_id =
89 m.duplicate_id
90 and m.process_flag = 'N'
91 and m.request_id = req_id
92 and m.set_number = set_num),
93 last_update_date = sysdate,
94 last_updated_by = arp_standard.profile.user_id,
95 last_update_login = arp_standard.profile.last_update_login
96 where customer_id in (select m.duplicate_id
97 from ra_customer_merges m
98 where m.process_flag = 'N'
99 and m.request_id = req_id
100 and m.set_number = set_num);
101
102 g_count := sql%rowcount;
103
104 arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
105 arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
106
107 END IF;
108
109 arp_message.set_line( 'OEP_CMERGE_OESET.OE_AG()-' );
110
111
112 EXCEPTION
113 when others then
114 arp_message.set_error( 'OEP_CMERGE_OESET.OE_AG');
115 raise;
116
117 END;
118
119
120 /*---------------------------- PUBLIC ROUTINES ------------------------------*/
121
122 PROCEDURE MERGE (REQ_ID NUMBER, SET_NUM NUMBER, PROCESS_MODE VARCHAR2) IS
123 BEGIN
124
125 arp_message.set_line( 'OEP_CMERGE_OESET.MERGE()+' );
126
127 oe_ag( req_id, set_num, process_mode );
128
129 arp_message.set_line( 'OEP_CMERGE_OESET.MERGE()-' );
130
131 EXCEPTION
132 when others then
133 raise;
134
135 END merge;
136 END OEP_CMERGE_OESET;