1 PACKAGE BODY POS_SUPPLIER_PUB_JOB_PKG AS
2 /* $Header: POSSPPBJB.pls 120.2.12020000.2 2012/07/25 05:42:58 snelloru ship $ */
3
4 PROCEDURE publish_supp_event_job(ERRBUFF OUT NOCOPY VARCHAR2,
5 RETCODE OUT NOCOPY NUMBER,
6 p_from_date IN VARCHAR2,
7 p_to_date IN VARCHAR2,
8 p_hours IN NUMBER ) AS
9
10 partyid_list pos_tbl_number;
11 p_publication_event_id NUMBER;
12 p_published_by NUMBER := fnd_global.user_id;
13 p_publish_detail VARCHAR2(25) := fnd_global.login_id;
14 l_from_date DATE;
15 l_to_date DATE;
16 l_event_key NUMBER := NULL;
17
18 BEGIN
19
20 -- Input Parameters Check
21 IF p_hours IS NULL THEN
22 IF p_from_date IS NOT NULL THEN
23 IF p_to_date IS NOT NULL THEN
24 -- FROM and TO dates are not null
25 l_from_date := to_date(p_from_date,'yyyy/mm/dd hh24:mi:ss');
26 l_to_date := to_date(p_to_date,'yyyy/mm/dd hh24:mi:ss');
27 ELSE
28 -- TO date is null
29 l_from_date := to_date(p_from_date,'yyyy/mm/dd hh24:mi:ss');
30 l_to_date := SYSDATE;
31 END IF;
32 ELSE
33 -- FROM Date is null
34 IF p_to_date IS NOT NULL THEN
35 l_from_date := to_date('01/01/1900 01:00:00','mm/dd/yyyy hh24:mi:ss');
36 l_to_date := to_date(p_to_date,'yyyy/mm/dd hh24:mi:ss');
37 ELSE
38 select actual_completion_date INTO l_from_date from (
39 select actual_completion_date from fnd_concurrent_requests req,
40 FND_CONCURRENT_PROGRAMS prg where req.concurrent_program_id = prg.concurrent_program_id
41 and prg.concurrent_program_name = 'POSSUPPUBJOB' and actual_completion_date is not null
42 order by actual_completion_date desc) where rownum=1;
43 l_to_date := SYSDATE;
44 fnd_file.put_line(fnd_file.log,'FROM Date is taken from the Last Run Date of the Concurrent Program');
45 END IF;
46 END IF;
47 ELSE
48 -- HOURS parameter is not null
49 l_from_date := SYSDATE - p_hours / 24;
50 l_to_date := SYSDATE;
51 fnd_file.put_line(fnd_file.log,'HOURS parameter is Not Null: Fetching all the Parties modified within '||p_hours||' hour(s)');
52 END IF;
53
54 l_from_date := to_date(to_char(l_from_date,'MM/DD/YYYY HH24:MI:SS'),'MM/DD/YYYY HH24:MI:SS');
55 l_to_date := to_date(to_char(l_to_date,'MM/DD/YYYY HH24:MI:SS'),'MM/DD/YYYY HH24:MI:SS');
56
57 fnd_file.put_line(fnd_file.log,'Parameters passed to the Program are as below:');
58 fnd_file.put_line(fnd_file.log,'-----------------------------------------------');
59 fnd_file.put_line(fnd_file.log,'FROM DATE:'||to_char(l_from_date,'MM/DD/YYYY HH24:MI:SS'));
60 fnd_file.put_line(fnd_file.log,'TO DATE:'||to_char(l_to_date,'MM/DD/YYYY HH24:MI:SS'));
61
62 -- Begin Bug 13833924/12765249
63 SELECT party_id
64 BULK COLLECT
65 INTO partyid_list
66 FROM (SELECT party_id
67 FROM ap_suppliers
68 WHERE last_update_date BETWEEN l_from_date AND l_to_date
69 UNION
70 SELECT ap.party_id
71 FROM hz_parties hz,
72 ap_suppliers ap
73 WHERE hz.party_id = ap.party_id
74 AND hz.last_update_date BETWEEN l_from_date AND l_to_date
75 UNION
76 SELECT ap.party_id
77 FROM ap_supplier_sites_all aps,
78 ap_suppliers ap
79 WHERE aps.vendor_id = ap.vendor_id
80 AND aps.last_update_date BETWEEN l_from_date AND l_to_date
81 UNION
82 SELECT ap.party_id
83 FROM hz_locations hz,
84 ap_suppliers ap,
85 ap_supplier_sites_all ss
86 WHERE ss.vendor_id = ap.vendor_id
87 AND hz.location_id = ss.location_id
88 AND hz.last_update_date BETWEEN l_from_date AND l_to_date
89 UNION
90 SELECT ap.party_id
91 FROM pos_bus_class_attr pbca,
92 ap_suppliers ap
93 WHERE pbca.vendor_id = ap.vendor_id
94 AND pbca.last_update_date BETWEEN l_from_date AND l_to_date
95 UNION
96 SELECT ap.party_id
97 FROM iby_pmt_instr_uses_all instr,
98 iby_external_payees_all payee,
99 ap_suppliers ap
100 WHERE instr.ext_pmt_party_id = payee.ext_payee_id
101 AND payee.payee_party_id = ap.party_id
102 AND instr.last_update_date BETWEEN l_from_date AND l_to_date
103 UNION
104 SELECT ap.party_id
105 FROM iby_external_payees_all payee,
106 ap_suppliers ap
107 WHERE payee.payee_party_id = ap.party_id
108 AND payee.last_update_date BETWEEN l_from_date AND l_to_date
109 UNION
110 SELECT ap.party_id
111 FROM zx_party_tax_profile tax,
112 ap_suppliers ap
113 WHERE tax.party_id = ap.party_id
114 AND tax.last_update_date BETWEEN l_from_date AND l_to_date
115 UNION
116 SELECT ap.party_id
117 FROM ap_supplier_contacts apsc,
118 ap_suppliers ap,
119 ap_supplier_sites_all sa
120 WHERE sa.vendor_id = ap.vendor_id
121 AND apsc.vendor_site_id = sa.vendor_site_id
122 AND apsc.last_update_date BETWEEN l_from_date AND l_to_date
123 UNION
124 SELECT ap.party_id
125 FROM pos_sup_products_services ps,
126 ap_suppliers ap
127 WHERE ps.vendor_id = ap.vendor_id
128 AND ps.last_update_date BETWEEN l_from_date AND l_to_date
129
130 UNION
131 SELECT ap.party_id
132 FROM hz_organization_profiles bapr,
133 hz_organization_profiles brpr,
134 hz_parties bp,
135 hz_party_sites s,
136 iby_account_owners ow,
137 hz_parties br,
138 ap_suppliers ap,
139 iby_ext_bank_accounts eb,
140 hz_code_assignments branchca,
141 hz_contact_points branchcp
142 WHERE eb.bank_id = bp.party_id(+)
143 AND eb.bank_id = bapr.party_id(+)
144 AND eb.branch_id = br.party_id(+)
145 AND eb.branch_id = brpr.party_id(+)
146 AND eb.ext_bank_account_id = ow.ext_bank_account_id
147 AND ow.primary_flag(+) = 'Y'
148 AND nvl(ow.end_date, SYSDATE + 10) > SYSDATE
149 AND ow.account_owner_party_id = ap.party_id
150 AND (br.party_id = s.party_id(+))
151 AND (s.identifying_address_flag(+) = 'Y')
152 AND (branchcp.owner_table_name(+) = 'HZ_PARTIES')
153 AND (branchcp.owner_table_id(+) = eb.branch_id)
154 AND (branchcp.contact_point_type(+) = 'EFT')
155 AND (nvl(branchcp.status(+), 'A') = 'A')
156 AND (branchca.class_category(+) =
157 'BANK_INSTITUTION_TYPE')
158 AND (branchca.owner_table_name(+) = 'HZ_PARTIES')
159 AND (branchca.owner_table_id(+) = eb.branch_id)
160 AND eb.last_update_date BETWEEN l_from_date AND l_to_date
161
162 UNION
163 SELECT ap.party_id
164 FROM ap_suppliers ap,
165 pos_supp_prof_ext_b ext
166 WHERE ap.party_id = ext.party_id
167 AND ext.last_update_date BETWEEN l_from_date AND l_to_date);
168
169 -- End Bug 13833924/12765249
170
171 if partyid_list.count>0 then
172
173 fnd_file.put_line(fnd_file.log,'Total Number of Published Parties: Count:'||partyid_list.count);
174 p_publication_event_id := get_curr_supp_pub_event_id;
175 fnd_file.put_line(fnd_file.log,'Publication event Id:'||p_publication_event_id);
176 --Calling the Supplier Publish Package
177 pos_supp_pub_raise_event_pkg.get_bo_and_insert(partyid_list,
178 p_publication_event_id,
179 p_published_by,
180 p_publish_detail);
181
182 --Calling the workflow section to raise the workflow event
183 l_event_key := pos_supp_pub_raise_event_pkg.raise_publish_supplier_event(p_publication_event_id);
184 else
185 fnd_file.put_line(fnd_file.log,'-------------------------------------------------------------------------------');
186 fnd_file.put_line(fnd_file.log,'MESSAGE:** No Party IDs are available to Publish in the given date range **');
187 fnd_file.put_line(fnd_file.log,'-------------------------------------------------------------------------------');
188
189 end if;
190
191 EXCEPTION
192 WHEN OTHERS THEN
193 fnd_file.put_line(fnd_file.log,'EXCEPTION :' || SQLCODE ||'Error Message :'|| SQLERRM);
194
195 END publish_supp_event_job;
196 ----------------------------------------------
197 FUNCTION get_curr_supp_pub_event_id RETURN NUMBER IS
198 BEGIN
199 SELECT pos_supp_pub_event_s.nextval
200 INTO g_curr_supp_publish_event_id
201 FROM dual;
202
203 RETURN g_curr_supp_publish_event_id;
204
205 EXCEPTION
206 WHEN OTHERS THEN
207 RETURN - 1;
208 END;
209
210 ------------------------------------------------
211 END pos_supplier_pub_job_pkg;
[Home] [Help]