[Home] [Help]
41: AND HOU.ORGANIZATION_ID = c_object_value_num;
42:
43: CURSOR c_get_cus_name(c_object_value_num IN NUMBER) IS
44: select hp.party_name
45: from hz_parties hp ,
46: hz_cust_accounts hcas
47: where hcas.cust_account_id = c_object_value_num
48: and hcas.party_id = hp.party_id;
49:
48: and hcas.party_id = hp.party_id;
49:
50: CURSOR c_get_car_name(c_object_value_num IN NUMBER) IS
51: select hp.party_name
52: from hz_parties hp , wsh_carriers wc
53: where wc.carrier_id = c_object_value_num
54: and wc.carrier_id = hp.party_id;
55:
56: -- Once a constraint for a supplier has been defined,
57: -- knowing party_id is enough to get the supplier name ?
58:
59: CURSOR c_get_sup_name(c_object_value_num IN NUMBER) IS
60: select hp.party_name
61: from hz_parties hp,
62: po_vendors po,
63: hz_relationships rel
64: where hp.party_id = c_object_value_num
65: AND rel.relationship_type = 'POS_VENDOR_PARTY'
63: hz_relationships rel
64: where hp.party_id = c_object_value_num
65: AND rel.relationship_type = 'POS_VENDOR_PARTY'
66: and rel.object_id = hp.party_id
67: and rel.object_table_name = 'HZ_PARTIES'
68: and rel.object_type = 'ORGANIZATION'
69: and rel.subject_table_name = 'PO_VENDORS'
70: and rel.subject_id = po.vendor_id
71: and rel.subject_type = 'POS_VENDOR';
94: hp.party_name company_name,
95: 'CUSTOMER' company_type,
96: nvl(nvl(flp.facility_code,wl.location_code),to_char(wl.wsh_location_id)) facility_code,
97: nvl(flp.description,wl.ui_location_code) description
98: from hz_parties hp , hz_party_sites hps,
99: hz_cust_acct_sites_all hcas,
100: wsh_locations wl,
101: fte_location_parameters flp
102: where hcas.party_site_id = hps.party_site_id
129: hp.party_name company_name,
130: 'SUPPLIER' company_type,
131: nvl(nvl(flp.facility_code,wl.location_code),to_char(wl.wsh_location_id)) facility_code,
132: nvl(flp.description,wl.ui_location_code) description
133: FROM hz_parties hp ,
134: po_vendors po,
135: hz_relationships rel,
136: hz_party_sites hps,
137: wsh_locations wl,
138: fte_location_parameters flp
139: WHERE hps.party_id = hp.party_id
140: AND rel.relationship_type = 'POS_VENDOR_PARTY'
141: and rel.object_id = hp.party_id
142: and rel.object_table_name = 'HZ_PARTIES'
143: and rel.object_type = 'ORGANIZATION'
144: and rel.subject_table_name = 'PO_VENDORS'
145: and rel.subject_id = po.vendor_id
146: and rel.subject_type = 'POS_VENDOR'
442: union all
443: select hcas.cust_account_id company_id, hp.party_name company_name,
444: hp.party_name||'/'||nvl(hps.party_site_name,hps.party_site_number)
445: site, 'CUSTOMER' company_type
446: from wsh_locations wl, hz_party_sites hps, hz_parties hp,
447: hz_cust_acct_sites_all hcas
448: where wl.source_location_id=hps.location_id
449: and hps.party_id=hp.party_id
450: and wl.location_source_code='HZ'
454: union all
455: select hp.party_id company_id, hp.party_name company_name,
456: hp.party_name||'/'||nvl(hps.party_site_name,hps.party_site_number)
457: site, 'SUPPLIER' company_type
458: FROM hz_parties hp ,
459: po_vendors po,
460: hz_relationships rel,
461: hz_party_sites hps,
462: wsh_locations wl
463: where wl.source_location_id=hps.location_id
464: and hps.party_id=hp.party_id
465: AND rel.relationship_type = 'POS_VENDOR_PARTY'
466: and rel.object_id = hp.party_id
467: and rel.object_table_name = 'HZ_PARTIES'
468: and rel.object_type = 'ORGANIZATION'
469: and rel.subject_table_name = 'PO_VENDORS'
470: and rel.subject_id = po.vendor_id
471: and rel.subject_type = 'POS_VENDOR'