[Home] [Help]
269: x_valid := TRUE;
270:
271: SELECT COUNT(*)
272: INTO l_count
273: FROM hz_party_sites hps, ap_suppliers aps
274: WHERE aps.vendor_id = p_vendor_id
275: AND hps.party_id = aps.party_id
276: AND UPPER(hps.party_site_name) = UPPER(p_vendor_site_code);
277:
2141: --Open Issue 2 should they match if they are null?
2142: IF p_party_site_id IS NOT NULL THEN
2143: SELECT hl.location_id
2144: INTO x_location_id
2145: FROM HZ_Locations hl, HZ_Party_Sites hps,
2146: fnd_languages fl
2147: WHERE hl.language = fl.language_code(+) AND
2148: nvl(upper(hl.country), 'dummy') =
2149: nvl(upper(p_country), 'dummy') AND
2172:
2173: SELECT hl.location_id, hps.party_site_id
2174: INTO x_location_id, p_party_site_id
2175: FROM HZ_Locations hl,
2176: HZ_Party_Sites hps,
2177: po_vendors pv,
2178: fnd_languages fl
2179: WHERE nvl(upper(hl.country), 'dummy') =
2180: nvl(upper(p_country), 'dummy') AND
2719: x_valid := TRUE;
2720:
2721: SELECT Count(*)
2722: INTO l_dummy
2723: FROM hz_locations hl, hz_party_sites hps
2724: WHERE hl.location_id = p_location_id
2725: AND hl.location_id = hps.location_id
2726: AND hps.party_site_id = nvl(p_party_site_id, hps.party_site_id);
2727:
2748: BEGIN
2749: x_valid := TRUE;
2750: SELECT Count(*)
2751: INTO l_dummy
2752: FROM hz_party_sites hps,
2753: ap_suppliers aps /* Bug 12590128 */
2754: WHERE ( hps.location_id = nvl(p_location_id, hps.location_id)
2755: AND hps.party_site_id = p_party_site_id)
2756: AND aps.party_id = hps.party_id
3113: x_valid OUT NOCOPY BOOLEAN) IS
3114:
3115: l_org_id HR_OPERATING_UNITS.organization_id%TYPE;
3116: l_org_name HR_OPERATING_UNITS.name%TYPE;
3117: l_party_site_name hz_party_sites.party_site_name%TYPE;
3118: l_api_name CONSTANT VARCHAR2(30) := 'Check_org_id_party_site_name';
3119: l_match_sitename_cnt NUMBER;
3120: BEGIN
3121: x_valid := TRUE;
3125: IF ( p_party_site_name IS NOT NULL) THEN
3126: if(p_party_site_id is not null) then
3127:
3128: select count(party_site_id) into l_match_sitename_cnt
3129: from hz_party_sites hzps,
3130: ap_suppliers aps
3131: where hzps.party_id = aps.party_id
3132: and aps.vendor_id = p_vendor_id
3133: AND hzps.party_site_id = p_party_site_id
3135:
3136: IF l_match_sitename_cnt = 1 THEN
3137: select hzps.party_site_name, hzps.party_site_id
3138: INTO l_party_site_name,p_party_site_id
3139: from hz_party_sites hzps,
3140: ap_suppliers aps
3141: where hzps.party_id = aps.party_id
3142: and aps.vendor_id = p_vendor_id
3143: AND hzps.party_site_id = p_party_site_id
3144: and hzps.party_site_name = p_party_site_name;
3145: END IF;
3146: elsif (p_party_site_id is null) then
3147: select count(party_site_id) into l_match_sitename_cnt
3148: from hz_party_sites hps,
3149: ap_suppliers aps
3150: where hps.party_id = aps.party_id
3151: and aps.vendor_id = p_vendor_id
3152: and hps.party_site_name = p_party_site_name;
3153:
3154: IF l_match_sitename_cnt = 1 THEN
3155: select hps.party_site_name, hps.party_site_id
3156: INTO l_party_site_name,p_party_site_id
3157: from hz_party_sites hps,
3158: ap_suppliers aps
3159: where hps.party_id = aps.party_id
3160: and aps.vendor_id = p_vendor_id
3161: and hps.party_site_name = p_party_site_name;
3170: IF (p_org_id IS NOT NULL AND p_party_site_name IS NOT NULL) THEN
3171: BEGIN
3172: /*If party_site_id is null then we derive it based on the
3173: party_site_name field provided. But in case of Upgraded records there
3174: can be multiple records in hz_party_sites with same party_site_name
3175: for the same party. We can link the ap_supplier_sites_all table
3176: to get unique record based on the input info*/
3177:
3178: if(p_party_site_id is not null) then
3177:
3178: if(p_party_site_id is not null) then
3179: SELECT hzps.party_site_name, hzps.party_site_id
3180: INTO l_party_site_name,p_party_site_id
3181: FROM hz_party_sites hzps, ap_suppliers aps
3182: WHERE hzps.party_site_name = p_party_site_name
3183: AND hzps.party_site_id = p_party_site_id
3184: AND aps.vendor_id = p_vendor_id;
3185: else
3184: AND aps.vendor_id = p_vendor_id;
3185: else
3186: SELECT hzps.party_site_name, hzps.party_site_id
3187: INTO l_party_site_name,p_party_site_id
3188: FROM hz_party_sites hzps, ap_supplier_sites_all aps
3189: WHERE aps.org_id = p_org_id
3190: AND hzps.party_site_name = p_party_site_name
3191: AND hzps.party_site_id = aps.party_site_id
3192: AND aps.vendor_id = p_vendor_id;
3207:
3208: BEGIN
3209: SELECT hzps.party_site_name, hzps.party_site_id
3210: INTO l_party_site_name,p_party_site_id
3211: FROM hz_party_sites hzps, ap_supplier_sites_all aps,
3212: HR_OPERATING_UNITS ORG
3213: WHERE ORG.name = p_org_name
3214: AND aps.org_id = ORG.organization_id
3215: AND hzps.party_site_name = p_party_site_name
8586: --as we want the duns number to be imported on
8587: --supplier sites(bug6053476)
8588: l_party_site_rec.duns_number_c := l_vendor_site_rec.duns_number;
8589: --Bug5896973
8590: --Added code to populate City + State + Country in party_site_name field of hz_party_sites table
8591: --l_party_site_rec.party_site_name :=
8592: -- nvl(l_vendor_site_rec.city,'')||' '
8593: -- ||nvl(l_vendor_site_rec.state,'')||' '
8594: -- ||nvl(l_vendor_site_rec.country,'');
15121: SELECT hps.party_id,
15122: hps.location_id
15123: INTO x_org_party_id,
15124: x_location_id
15125: FROM HZ_Party_Sites hps, po_vendor_sites_all pvs
15126: WHERE pvs.vendor_site_id = p_vendor_contact_rec.vendor_site_id
15127: AND pvs.party_site_id = hps.party_site_id;
15128:
15129: SELECT party_site_id
15155: SELECT hps.party_id,
15156: hps.location_id
15157: INTO x_org_party_id,
15158: x_location_id
15159: FROM HZ_Party_Sites hps, po_vendor_sites_all pvs
15160: WHERE pvs.vendor_site_id = p_vendor_contact_rec.vendor_site_id
15161: AND pvs.party_site_id = hps.party_site_id;
15162:
15163: SELECT party_site_id
15239: SELECT hps.party_id,
15240: hps.location_id
15241: INTO x_org_party_id,
15242: x_location_id
15243: FROM HZ_Party_Sites hps
15244: WHERE hps.party_site_id =
15245: p_vendor_contact_rec.org_party_site_id;
15246: END if;
15247: --open issue 12, no way to populate vendor_site_id