[Home] [Help]
72: into l_mapping_id
73: from pos_supplier_mappings
74: where party_id = p_party_id;
75:
76: insert into pos_bus_class_reqs
77: (
78: bus_class_request_id, mapping_id,
79: request_type, request_status,
80: classification_id, lookup_type, lookup_code,
132: /*
133: * Adding the condition to reject the pending request, if the 'Applicable Flag'
134: * is unchecked. Please refer the bug 7415073 for more information.
135: **/
136: update pos_bus_class_reqs
137: set request_status = 'REJECTED',
138: last_update_date = sysdate,
139: last_updated_by = fnd_global.user_id,
140: last_update_login = fnd_global.login_id
140: last_update_login = fnd_global.login_id
141: where bus_class_request_id = p_request_id;
142:
143: else
144: update pos_bus_class_reqs
145: set request_status = 'REJECTED',
146: last_update_date = sysdate,
147: last_updated_by = fnd_global.user_id,
148: last_update_login = fnd_global.login_id
169: where classification_id = p_classification_id;
170:
171: -- If a request exists, make it approved.
172: if (p_request_id is not null and p_request_id > 0) then
173: update pos_bus_class_reqs
174: set request_status = 'APPROVED',
175: classification_id = l_class_id,
176: last_updated_by = fnd_global.user_id,
177: last_update_login = fnd_global.login_id,
205: p_party_id, BUSINESS_CLASSIFICATION, p_lookup_code,sysdate
206: , p_vendor_id
207: );
208:
209: update pos_bus_class_reqs
210: set request_status = 'APPROVED',
211: classification_id = l_class_id,
212: last_updated_by = fnd_global.user_id,
213: last_update_login = fnd_global.login_id,
251: from pos_supplier_mappings
252: where party_id = p_party_id;
253:
254: -- bug 11803346 - rejecting all other existing pending requests for the same lookup code
255: UPDATE pos_bus_class_reqs
256: SET request_status = 'REJECTED'
257: WHERE mapping_id = l_mapping_id
258: AND lookup_type = BUSINESS_CLASSIFICATION
259: AND lookup_code = p_lookup_code;
257: WHERE mapping_id = l_mapping_id
258: AND lookup_type = BUSINESS_CLASSIFICATION
259: AND lookup_code = p_lookup_code;
260:
261: insert into pos_bus_class_reqs
262: (
263: bus_class_request_id, mapping_id,
264: request_type, request_status,
265: classification_id, lookup_type, lookup_code,
276: p_exp_date, fnd_global.user_id, sysdate,
277: fnd_global.user_id, sysdate, fnd_global.login_id
278: );
279: else
280: update pos_bus_class_reqs
281: set
282: request_type = 'ADD',
283: request_status = 'PENDING',
284: certification_no= p_cert_num,
328: FROM pos_supplier_mappings psm
329: WHERE psm.party_id = p_party_id;
330:
331: SELECT request_status INTO req_status
332: FROM pos_bus_class_reqs
333: WHERE lookup_type = p_lkp_type_tbl(i)
334: AND lookup_code = p_lkp_code_tbl(i)
335: AND mapping_id = l_map_id
336: AND last_update_date = (SELECT Max(last_update_date)
333: WHERE lookup_type = p_lkp_type_tbl(i)
334: AND lookup_code = p_lkp_code_tbl(i)
335: AND mapping_id = l_map_id
336: AND last_update_date = (SELECT Max(last_update_date)
337: FROM pos_bus_class_reqs
338: WHERE lookup_type = p_lkp_type_tbl(i)
339: AND lookup_code = p_lkp_code_tbl(i)
340: AND mapping_id = l_map_id);
341:
359: AND class_status = 'APPROVED'
360: AND party_site_id IS NULL
361: AND vendor_site_id IS NULL
362: AND classification_id not in ( select classification_id
363: from pos_bus_class_reqs pbcr,
364: pos_supplier_mappings psm
365: where psm.party_id = p_party_id
366: and psm.mapping_id = pbcr.mapping_id
367: and pbcr.request_status = 'PENDING'
393:
394: UNION
395:
396: SELECT lookup_code
397: FROM pos_bus_class_reqs pbcr, pos_supplier_mappings psm
398: WHERE psm.party_id = p_party_id
399: AND psm.mapping_id = pbcr.mapping_id
400: AND pbcr.lookup_type = p_lkp_type_tbl(i)
401: AND pbcr.lookup_code = p_lkp_code_tbl(i)