[Home] [Help]
PACKAGE BODY: APPS.ECX_TP_SYNCH
Source
1 package body ECX_TP_SYNCH as
2 -- $Header: ECXTPSYNB.pls 120.10 2006/10/11 06:19:48 gsingh noship $
3 procedure synch_ecx_tp
4
5 IS
6
7 TYPE t_party_id_tl is TABLE of ecx_tp_headers.party_id%type;
8 TYPE t_orig_system_id_tl is TABLE of wf_local_roles.orig_system_id%type;
9 TYPE t_party_site_id_tl is TABLE of ecx_tp_headers.party_site_id%type;
10
11 v_party_id_tl t_party_id_tl;
12
13 V_PARTY_SITE_ID_TL ecx_tp_headers.party_site_id%type;
14 V_PARTY_TYPE_TL ecx_tp_headers.party_type%type;
15
16 P_COMPANY_EMAIL_ADDR varchar2(320);
17 l_event_name varchar2(250);
18
19 party_name_params wf_parameter_list_t;
20 site_name_params wf_parameter_list_t;
21 org_table_name varchar2(350);
22 party_name varchar2(350);
23 org_site_table_name varchar2(350);
24 site_name varchar2(350);
25
26 cursor get_party_id is
27 select party_id from ecx_tp_headers;
28
29 cursor get_mail(p_party_id in ecx_tp_headers.party_id%type) is
30 select company_admin_email from ecx_tp_headers where party_id=p_party_id;
31
32 cursor get_party_type(p_party_id in ecx_tp_headers.party_id%type) is
33 select party_type from ecx_tp_headers where party_id=p_party_id;
34
35 cursor get_party_site_id(p_party_id in ecx_tp_headers.party_id%type) is
36 select party_site_id from ecx_tp_headers where party_id=p_party_id;
37
38 cursor internal_party_name(v_party_id varchar) is
39 select LOCATION_CODE from hr_locations where LOCATION_ID=v_party_id ;
40
41 cursor internal_site_name(v_party_id varchar) is
42 select ADDRESS_LINE_1||ADDRESS_LINE_2||ADDRESS_LINE_3 ||town_or_city||country||postal_code from hr_locations
43 where location_id =v_party_id ;
44
45 cursor bank_party_name(v_party_id varchar) is
46 select BANK_NAME from CE_BANK_BRANCHES_V where BRANCH_PARTY_ID=v_party_id;
47
48 cursor bank_site_name(v_party_id varchar) is
49 select address_line1||' '||address_line2||' '||address_line3||' '||CITY||' '||ZIP from CE_BANK_BRANCHES_V where BRANCH_PARTY_ID=v_party_id;
50
51 cursor supplier_party_name(v_party_id varchar) is
52 select p.vendor_name from PO_VENDORS p where p.vendor_ID =v_party_id;
53
54 cursor supplier_site_name(v_party_id varchar,v_party_site_id varchar) is
55 select p1.ADDRESS_LINE1||' '||p1.ADDRESS_LINE2||' '||p1.ADDRESS_LINE3||' '||p1.CITY||p1.ZIP from PO_VENDOR_SITES_ALL p1
56 where p1.VENDOR_SITE_ID =v_party_site_id and p1.VENDOR_ID=v_party_id;
57
58
59 cursor customer_party_name(v_party_id varchar) is
60 select PARTY_NAME from hz_parties where party_id=v_party_id;
61
62 cursor customer_site_name(v_party_id varchar,v_party_site_id varchar) is
63 select ADDRESS1||' '||ADDRESS2||' ' || ADDRESS3 ||' '|| ADDRESS4||' ' ||CITY||' ' ||POSTAL_CODE||' ' ||
64 STATE ||' '||PROVINCE ||' '||COUNTY||' '||COUNTRY from hz_locations
65 where location_id =(select location_id from hz_party_sites where party_id=v_party_id and party_site_id=v_party_site_id);
66
67 cursor org_table(v_party_id varchar) is
68 select decode(party_type,'C','HZ_PARTIES','EXCHANGE','HZ_PARTIES','CARRIER','HZ_PARTIES','S','PO_VENDORS','I','HR_LOCATIONS','B','CE_BANK_BRANCHES_V') from ecx_tp_headers where party_id =v_party_id ;
69
70 cursor org_table_site(v_party_id varchar) is
71 select decode(party_type,'C','HZ_PARTY_SITES','EXCHANGE','HZ_PARTY_SITES','CARRIER','HZ_PARTY_SITES','S','PO_VENDOR_SITES_ALL','I','HR_LOCATIONS_SITES','B','CE_BANK_BRANCHES_SITE') from ecx_tp_headers where party_id =v_party_id ;
72
73
74 begin
75 OPEN get_party_id;
76 FETCH get_party_id BULK COLLECT INTO v_party_id_tl;
77 CLOSE get_party_id;
78
79
80
81
82
83 for i in 1..v_party_id_tl.count loop
84 OPEN get_party_type(v_party_id_tl(i));
85 FETCH get_party_type into v_party_type_tl;
86 CLOSE get_party_type;
87
88 OPEN get_party_site_id(v_party_id_tl(i));
89 FETCH get_party_site_id into V_PARTY_SITE_ID_TL;
90 CLOSE get_party_site_id;
91
92 party_name_params := wf_parameter_list_t();
93 site_name_params := wf_parameter_list_t();
94
95 if (v_party_type_tl='I') then
96 open internal_party_name (v_party_id_tl(i));
97 fetch internal_party_name into party_name;
98 close internal_party_name;
99
100 open internal_site_name (v_party_id_tl(i));
101 fetch internal_site_name into site_name;
102 close internal_site_name;
103 end if;
104
105 if(v_party_type_tl='S') then
106 open supplier_party_name(v_party_id_tl(i));
107 fetch supplier_party_name into party_name;
108 close supplier_party_name;
109
110 open supplier_site_name(v_party_id_tl(i),V_PARTY_SITE_ID_TL);
111 fetch supplier_site_name into site_name;
112 close supplier_site_name;
113 end if;
114
115 if(v_party_type_tl='B') then
116 open bank_party_name(v_party_id_tl(i));
117 fetch bank_party_name into party_name;
118 close bank_party_name;
119
120 open bank_site_name(v_party_id_tl(i));
121 fetch bank_site_name into site_name;
122 close bank_site_name;
123 end if;
124
125 if(v_party_type_tl='C' OR v_party_type_tl='CARRIER' OR v_party_type_tl='EXCHANGE' ) then
126 open customer_party_name(v_party_id_tl(i));
127 fetch customer_party_name into party_name;
128 close customer_party_name;
129
130 open customer_site_name(v_party_id_tl(i),V_PARTY_SITE_ID_TL);
131 fetch customer_site_name into site_name;
132 close customer_site_name;
133 end if;
134
135 open get_mail(v_party_id_tl(i));
136 fetch get_mail into p_company_email_addr;
137 close get_mail;
138
139 open org_table(v_party_id_tl(i));
140 fetch org_table into org_table_name;
141 close org_table;
142
143 open org_table_site(v_party_id_tl(i));
144 fetch org_table_site into org_site_table_name;
145 close org_table_site;
146
147
148
149
150 wf_event.addParameterToList(
151 p_name => 'USER_NAME',
152 p_value => org_table_name ||':'||v_party_id_tl(i),
153 p_parameterlist => party_name_params);
154
155 wf_event.addParameterToList(
156 p_name => 'DisplayName',
157 p_value => party_name,
158 p_parameterlist => party_name_params);
159
160 wf_event.addParameterToList(
161 p_name => 'mail',
162 p_value => p_company_email_addr,
163 p_parameterlist => party_name_params);
164
165 wf_event.addParameterToList(
166 p_name => 'USER_NAME',
167 p_value => org_site_table_name||':'||V_PARTY_SITE_ID_TL,
168 p_parameterlist => site_name_params);
169
170 wf_event.addParameterToList(
171 p_name => 'DisplayName',
172 p_value => site_name,
173 p_parameterlist => site_name_params);
174
175 wf_event.addParameterToList(
176 p_name => 'mail',
177 p_value => p_company_email_addr,
178 p_parameterlist => site_name_params);
179
180
181
182 wf_local_synch.propagate_role(
183 p_orig_system => org_table_name,
184 p_orig_system_id =>v_party_id_tl(i) ,
185 p_attributes => party_name_params,
186 p_start_date => sysdate
187 );
188 wf_local_synch.propagate_role(
189 p_orig_system => org_site_table_name,
190 p_orig_system_id =>V_PARTY_SITE_ID_TL ,
191 p_attributes => site_name_params,
192 p_start_date => sysdate
193 );
194
195 end loop;
196
197
198
199 End;
200 End;