[Home] [Help]
23: G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
24: G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
25:
26: -- validate whether the partner is having member type 'GLOBAL'
27: -- IN .. partner_id of the global partner from pv_partner_profiles table
28:
29: FUNCTION is_global_valid
30: ( p_global_partner_id IN NUMBER
31: )RETURN VARCHAR2
60:
61: cursor is_global_cur(p_ptr_id NUMBER) IS
62: SELECT 'Y'
63: FROM hz_relationships rel
64: , pv_partner_profiles prof
65: WHERE rel.status='A'
66: AND prof.partner_id=p_ptr_id
67: AND relationship_type = 'PARTNER_HIERARCHY'
68: AND rel.subject_id = prof.partner_party_id
192:
193: l_is_valid VARCHAR2(1):=null;
194: CURSOR is_partner_cur(ptr_id NUMBER) IS
195: SELECT 'Y'
196: FROM pv_partner_profiles
197: WHERE partner_id=ptr_id
198: AND STATUS='A';
199:
200: BEGIN
232: -- p_new_memb_type IN VARCHAR2.
233: -- pass GLOBAL,SUBSIDIARY or STANDARD if you want to register a new member type(also validated).
234: -- if you want to terminate the relationship pass null.
235: -- p_global_ptr_id. IN NUMBER DEFAULT NULL
236: -- if the new member type is SUBSIDIARY, pass the global's partner id from pv_partner_profiles table
237: -- this is validated only if the new member type is SUBSIDIARY
238:
239: -- HISTORY
240: -- 15-SEP-2003 pukken CREATION
271: AND latest_flag='Y';
272:
273: CURSOR c_partner_party_id_cur ( p_ptr_id NUMBER ) IS
274: SELECT partner_party_id
275: FROM pv_partner_profiles
276: WHERE partner_id=p_ptr_id;
277:
278: --to get all the subsidiaries of the given global partner
279: CURSOR sub_cur( p_ptr_id NUMBER) IS
283: , rel.relationship_id
284: , rel.object_version_number
285: , rel.status
286: , rel.start_date
287: FROM pv_partner_profiles subs_prof
288: , pv_partner_profiles global_prof
289: , pv_enty_attr_values subs_enty_val
290: , hz_relationships rel
291: WHERE
284: , rel.object_version_number
285: , rel.status
286: , rel.start_date
287: FROM pv_partner_profiles subs_prof
288: , pv_partner_profiles global_prof
289: , pv_enty_attr_values subs_enty_val
290: , hz_relationships rel
291: WHERE
292: global_prof.partner_id = p_ptr_id
313: , rel.relationship_id
314: , rel.object_version_number
315: , rel.status
316: , rel.start_date
317: FROM pv_partner_profiles subs_prof
318: , pv_partner_profiles global_prof
319: , pv_enty_attr_values subs_enty_val
320: , pv_enty_attr_values global_enty_val
321: , hz_relationships rel
314: , rel.object_version_number
315: , rel.status
316: , rel.start_date
317: FROM pv_partner_profiles subs_prof
318: , pv_partner_profiles global_prof
319: , pv_enty_attr_values subs_enty_val
320: , pv_enty_attr_values global_enty_val
321: , hz_relationships rel
322: WHERE global_enty_val.entity = 'PARTNER'
344: SELECT rel.relationship_id relationship_id
345: , rel.start_date
346: , rel.object_version_number object_version_number
347: , subs_prof.partner_party_id partner_party_id
348: FROM pv_partner_profiles subs_prof
349: , hz_relationships rel
350: WHERE rel.subject_id=subs_prof.partner_party_id
351: AND rel.relationship_code = 'SUBSIDIARY_OF'
352: AND rel.relationship_type = 'PARTNER_HIERARCHY'
363: , rel.start_date
364: , rel.object_version_number object_version_number
365: , subs_prof.partner_party_id partner_party_id
366: , subs_enty_val.version version
367: FROM pv_partner_profiles subs_prof
368: , pv_enty_attr_values subs_enty_val
369: , hz_relationships rel
370: WHERE rel.subject_id=subs_prof.partner_party_id
371: AND rel.relationship_code = 'SUBSIDIARY_OF'
395:
396: CURSOR get_party_csr( p_partner_id IN NUMBER ) IS
397: SELECT party_name
398: FROM hz_parties party
399: ,pv_partner_profiles prof
400: WHERE prof.partner_id=p_partner_id
401: AND prof.partner_party_id=party.party_id;
402:
403: l_api_name CONSTANT VARCHAR2(30) := 'Register_term_ptr_memb_type';
1187: IS
1188:
1189: cursor cv_validate_global_orgzn(cv_orgzn_number VARCHAR2) IS
1190: select 1 from dual where exists
1191: (select 1 from hz_parties hzp, pv_partner_profiles pvpp, pv_enty_attr_values pvev
1192: where hzp.party_number= cv_orgzn_number
1193: and hzp.party_id = pvpp.partner_party_id
1194: and pvpp.status = 'A'
1195: and pvpp.partner_id = pvev.entity_id
1225: IS
1226: l_global_prtnr_id NUMBER := NULL ;
1227:
1228: cursor cv_get_global_prtnr_id(cv_orgzn_number VARCHAR2) IS
1229: select pvpp.partner_id from hz_parties hzp, pv_partner_profiles pvpp, pv_enty_attr_values pvev
1230: where hzp.party_number= cv_orgzn_number
1231: and hzp.party_id = pvpp.partner_party_id
1232: and pvpp.status = 'A'
1233: and pvpp.partner_id = pvev.entity_id
1299:
1300: CURSOR c_get_partner_id ( rel_id NUMBER ) IS
1301: SELECT partner_id
1302:
1303: FROM pv_partner_profiles prof
1304: , hz_relationships rel
1305: , pv_enty_attr_values enty
1306: WHERE rel.relationship_id = rel_id
1307: AND rel.relationship_type= 'PARTNER_HIERARCHY'
1315: CURSOR c_get_sub_glob ( rel_id NUMBER ) IS
1316: SELECT sprof.partner_id subsidiary_partner_id
1317: , gprof.partner_id global_partner_id
1318: , enty.attr_value attr_value
1319: FROM pv_partner_profiles sprof
1320: , pv_partner_profiles gprof
1321: , hz_relationships rel
1322: , pv_enty_attr_values enty
1323: WHERE rel.relationship_id = rel_id
1316: SELECT sprof.partner_id subsidiary_partner_id
1317: , gprof.partner_id global_partner_id
1318: , enty.attr_value attr_value
1319: FROM pv_partner_profiles sprof
1320: , pv_partner_profiles gprof
1321: , hz_relationships rel
1322: , pv_enty_attr_values enty
1323: WHERE rel.relationship_id = rel_id
1324: AND rel.relationship_type= 'PARTNER_HIERARCHY'
1586:
1587: CURSOR get_party_csr( p_partner_id IN NUMBER ) IS
1588: SELECT party_name
1589: FROM hz_parties party
1590: ,pv_partner_profiles prof
1591: WHERE prof.partner_id=p_partner_id
1592: AND prof.partner_party_id=party.party_id;
1593:
1594: BEGIN