1 PACKAGE BODY IGI_CIS2007_UTIL_PKG AS
2 -- $Header: igiputlb.pls 120.2.12010000.4 2009/09/17 12:43:03 gaprasad ship $
3
4 /*
5 * Type: Procedure
6 * Access: Public API
7 *
8 * Description: This function calls the certificate insertion logic
9 * for the supplier if CIS is enabled
10 *
11 * Note: This procedure is called from package AP_VENDORS_PKG (apvndhrb.pls)
12 * Care should be taken while modifying this package
13 */
14 PROCEDURE SUPPLIER_UPDATE(
15 p_vendor_id IN ap_suppliers.vendor_id%TYPE,
16 p_tax_grp_id IN ap_awt_group_taxes_all.group_id%TYPE,
17 p_pay_tax_grp_id IN ap_awt_group_taxes_all.group_id%TYPE /* Bug 7218825 */
18 )
19 IS
20 l_cis_installed number;
21 BEGIN
22 l_cis_installed := 0;
23 BEGIN
24 SELECT count(1)
25 INTO l_cis_installed
26 FROM igi_gcc_inst_options_all
27 WHERE option_name = 'CIS'
28 AND status_flag = 'Y';
29 EXCEPTION
30 WHEN OTHERS THEN
31 l_cis_installed := 0;
32 END;
33 IF l_cis_installed > 0 THEN
34 IGI_CIS2007_TAX_EFF_DATE.main (
35 p_vendor_id => p_vendor_id,
36 p_vendor_site_id => NULL,
37 p_tax_grp_id => p_tax_grp_id,
38 p_pay_tax_grp_id => p_pay_tax_grp_id, /* Bug 7218825 */
39 p_source => 'VENDOR FORM',
40 p_effective_date => sysdate
41 );
42 END IF;
43
44 EXCEPTION
45 WHEN OTHERS THEN
46 NULL;
47 END;
48
49
50 /*
51 * Type: Procedure
52 * Access: Public API
53 *
54 * Description: This function calls the certificate insertion logic
55 * for supplier site if CIS is enabled
56 *
57 * Note: This procedure is called from package AP_VENDOR_SITES_PKG (apvndsib.pls)
58 * Care should be taken while modifying this package
59 */
60 PROCEDURE SUPPLIER_SITE_UPDATE(
61 p_vendor_id IN ap_suppliers.vendor_id%TYPE,
62 p_vendor_site_id IN ap_supplier_sites_all.vendor_site_id%TYPE,
63 p_tax_grp_id IN ap_awt_group_taxes_all.group_id%TYPE,
64 p_pay_tax_grp_id IN ap_awt_group_taxes_all.group_id%TYPE /* Bug 7218825 */
65 )
66 IS
67 BEGIN
68 IF igi_gen.is_req_installed('CIS',mo_global.get_current_org_id) = 'Y' THEN
69 IGI_CIS2007_TAX_EFF_DATE.main (
70 p_vendor_id => p_vendor_id,
71 p_vendor_site_id => p_vendor_site_id,
72 p_tax_grp_id => p_tax_grp_id,
73 p_pay_tax_grp_id => p_pay_tax_grp_id, /* Bug 7218825 */
74 p_source => 'VENDOR SITE FORM',
75 p_effective_date => sysdate
76 );
77 END IF;
78 EXCEPTION
79 WHEN OTHERS THEN
80 NULL;
81 END;
82
83 /* This Function Added for CIS Bug 7218825 */
84
85 FUNCTION get_payables_option_based_awt(l_vendor_id NUMBER,l_vendor_site_id NUMBER,l_tax_grp_id NUMBER,l_pay_tax_grp_id NUMBER)
86 RETURN NUMBER IS
87
88 l_org_id ap_supplier_sites_all.org_id%TYPE;
89 l_awt_group_id_po ap_suppliers.awt_group_id%TYPE;
90 l_pay_awt_group_id_po ap_suppliers.pay_awt_group_id%TYPE;
91 l_awt_group_id_pvs ap_supplier_sites_all.awt_group_id%TYPE;
92 l_pay_awt_group_id_pvs ap_supplier_sites_all.pay_awt_group_id%TYPE;
93 l_create_awt_dists_type ap_system_parameters_all.CREATE_AWT_DISTS_TYPE%TYPE;
94
95 BEGIN
96
97 IF (l_vendor_id IS NOT NULL) AND (l_vendor_site_id IS NOT NULL) THEN /* FOR VENDOR SITE */
98
99 IF (l_tax_grp_id IS NULL) and (l_pay_tax_grp_id IS NULL) THEN
100
101 select org_id,awt_group_id,pay_awt_group_id
102 into l_org_id,l_awt_group_id_pvs,l_pay_awt_group_id_pvs
103 from ap_supplier_sites_all
104 where
105 vendor_id = l_vendor_id and
106 vendor_site_id = l_vendor_site_id and
107 upper(allow_awt_flag) = 'Y';
108
109 select CREATE_AWT_DISTS_TYPE
110 into l_create_awt_dists_type
111 from ap_system_parameters_all
112 where
113 org_id = l_org_id and
114 upper(allow_awt_flag) = 'Y';
115
116 If l_create_awt_dists_type = 'APPROVAL'
117 then
118 RETURN l_awt_group_id_pvs;
119 elsif l_create_awt_dists_type = 'PAYMENT'
120 then
121 RETURN l_pay_awt_group_id_pvs;
122 elsif l_create_awt_dists_type = 'BOTH'
123 then
124 RETURN NULL;
125 else
126 RETURN NULL;
127 end if;
128
129 ELSIF (l_tax_grp_id IS NOT NULL) and (l_pay_tax_grp_id IS NULL) THEN
130
131 RETURN l_tax_grp_id;
132
133 ELSIF (l_tax_grp_id IS NULL) and (l_pay_tax_grp_id IS NOT NULL) THEN
134
135 RETURN l_pay_tax_grp_id;
136
137 ELSIF (l_tax_grp_id IS NOT NULL) and (l_pay_tax_grp_id IS NOT NULL) AND
138 (l_tax_grp_id = l_pay_tax_grp_id) THEN
139
140 RETURN l_tax_grp_id;
141
142 ELSIF (l_tax_grp_id IS NOT NULL) and (l_pay_tax_grp_id IS NOT NULL) AND
143 (l_tax_grp_id <> l_pay_tax_grp_id) THEN
144
145 RETURN NULL;
146
147 END IF;
148
149
150 END IF; /* FOR VENDOR SITE */
151
152 IF (l_vendor_id IS NOT NULL) AND (l_vendor_site_id IS NULL) THEN /* FOR VENDOR */
153
154 IF (l_tax_grp_id IS NULL) and (l_pay_tax_grp_id IS NULL) THEN
155
156 select awt_group_id,pay_awt_group_id
157 into l_awt_group_id_po,l_pay_awt_group_id_po
158 from ap_suppliers
159 where
160 vendor_id = l_vendor_id;
161
162 If (l_awt_group_id_po IS NOT NULL) and (l_pay_awt_group_id_po IS NOT NULL) and
163 (l_awt_group_id_po = l_pay_awt_group_id_po)
164 then
165 RETURN l_awt_group_id_po;
166 elsif (l_awt_group_id_po IS NOT NULL) and (l_pay_awt_group_id_po IS NOT NULL) and
167 (l_awt_group_id_po <> l_pay_awt_group_id_po)
168 then
169 RETURN NULL;
170 elsif (l_awt_group_id_po IS NOT NULL) and (l_pay_awt_group_id_po IS NULL)
171 then
172 RETURN l_awt_group_id_po;
173 elsif (l_awt_group_id_po IS NULL) and (l_pay_awt_group_id_po IS NOT NULL)
174 then
175 RETURN l_pay_awt_group_id_po;
176 end if;
177
178 ELSIF (l_tax_grp_id IS NOT NULL) and (l_pay_tax_grp_id IS NULL) THEN
179
180 RETURN l_tax_grp_id;
181
182 ELSIF (l_tax_grp_id IS NULL) and (l_pay_tax_grp_id IS NOT NULL) THEN
183
184 RETURN l_pay_tax_grp_id;
185
186 ELSIF (l_tax_grp_id IS NOT NULL) and (l_pay_tax_grp_id IS NOT NULL) AND
187 (l_tax_grp_id = l_pay_tax_grp_id) THEN
188
189 RETURN l_tax_grp_id;
190
191 ELSIF (l_tax_grp_id IS NOT NULL) and (l_pay_tax_grp_id IS NOT NULL) AND
192 (l_tax_grp_id <> l_pay_tax_grp_id) THEN
193
194 RETURN NULL;
195
196 END IF;
197
198 END IF; /* FOR VENDOR */
199
200 IF (l_vendor_id IS NULL) AND (l_vendor_site_id IS NULL) THEN /* FOR INVOICE AND PAY TAX GROUP IDS */
201
202 IF (l_tax_grp_id IS NOT NULL) and (l_pay_tax_grp_id IS NULL) THEN
203
204 RETURN l_tax_grp_id;
205
206 ELSIF (l_tax_grp_id IS NULL) and (l_pay_tax_grp_id IS NOT NULL) THEN
207
208 RETURN l_pay_tax_grp_id;
209
210 ELSIF (l_tax_grp_id IS NOT NULL) and (l_pay_tax_grp_id IS NOT NULL) AND
211 (l_tax_grp_id = l_pay_tax_grp_id) THEN
212
213 RETURN l_tax_grp_id;
214
215 ELSIF (l_tax_grp_id IS NOT NULL) and (l_pay_tax_grp_id IS NOT NULL) AND
216 (l_tax_grp_id <> l_pay_tax_grp_id) THEN
217
218 RETURN NULL;
219
220 ELSIF (l_tax_grp_id IS NULL) and (l_pay_tax_grp_id IS NULL) THEN
221
222 RETURN NULL;
223
224
225 END IF;
226
227 END IF; /* FOR INVOICE AND PAY TAX GROUP IDS */
228
229 END;
230
231 END IGI_CIS2007_UTIL_PKG;
232
[Home] [Help]