[Home] [Help]
1858: --Open Issue 2 should they match if they are null?
1859: IF p_party_site_id IS NOT NULL THEN
1860: SELECT hl.location_id
1861: INTO x_location_id
1862: FROM HZ_Locations hl, HZ_Party_Sites hps,
1863: fnd_languages fl
1864: WHERE hl.language = fl.language_code(+) AND
1865: nvl(upper(hl.country), 'dummy') =
1866: nvl(upper(p_country), 'dummy') AND
1888:
1889: SELECT hl.location_id, hps.party_site_id
1890: INTO x_location_id, p_party_site_id
1891: FROM HZ_Locations hl,
1892: HZ_Party_Sites hps,
1893: po_vendors pv,
1894: fnd_languages fl
1895: WHERE nvl(upper(hl.country), 'dummy') =
1896: nvl(upper(p_country), 'dummy') AND
2408: x_valid := TRUE;
2409:
2410: SELECT Count(*)
2411: INTO l_dummy
2412: FROM hz_locations hl, hz_party_sites hps
2413: WHERE hl.location_id = p_location_id
2414: AND hl.location_id = hps.location_id
2415: AND hps.party_site_id = nvl(p_party_site_id, hps.party_site_id);
2416:
2437: x_valid := TRUE;
2438:
2439: SELECT Count(*)
2440: INTO l_dummy
2441: FROM hz_party_sites hps
2442: WHERE ( hps.location_id = nvl(p_location_id, hps.location_id)
2443: AND hps.party_site_id = p_party_site_id);
2444:
2445: IF nvl(l_dummy,0) = 0 THEN
2720: x_valid OUT NOCOPY BOOLEAN) IS
2721:
2722: l_org_id HR_OPERATING_UNITS.organization_id%TYPE;
2723: l_org_name HR_OPERATING_UNITS.name%TYPE;
2724: l_party_site_name hz_party_sites.party_site_name%TYPE;
2725: l_api_name CONSTANT VARCHAR2(30) := 'Check_org_id_party_site_name';
2726: BEGIN
2727: x_valid := TRUE;
2728:
2729: IF (p_org_id IS NOT NULL AND p_party_site_name IS NOT NULL) THEN
2730: BEGIN
2731: /*If party_site_id is null then we derive it based on the
2732: party_site_name field provided. But in case of Upgraded records there
2733: can be multiple records in hz_party_sites with same party_site_name
2734: for the same party. We can link the ap_supplier_sites_all table
2735: to get unique record based on the input info*/
2736:
2737: if(p_party_site_id is not null) then
2736:
2737: if(p_party_site_id is not null) then
2738: SELECT hzps.party_site_name, hzps.party_site_id
2739: INTO l_party_site_name,p_party_site_id
2740: FROM hz_party_sites hzps, ap_suppliers aps
2741: WHERE hzps.party_site_name = p_party_site_name
2742: AND hzps.party_site_id = p_party_site_id
2743: AND aps.vendor_id = p_vendor_id;
2744: else
2743: AND aps.vendor_id = p_vendor_id;
2744: else
2745: SELECT hzps.party_site_name, hzps.party_site_id
2746: INTO l_party_site_name,p_party_site_id
2747: FROM hz_party_sites hzps, ap_supplier_sites_all aps
2748: WHERE aps.org_id = p_org_id
2749: AND hzps.party_site_name = p_party_site_name
2750: AND hzps.party_site_id = aps.party_site_id
2751: AND aps.vendor_id = p_vendor_id;
2766:
2767: BEGIN
2768: SELECT hzps.party_site_name, hzps.party_site_id
2769: INTO l_party_site_name,p_party_site_id
2770: FROM hz_party_sites hzps, ap_supplier_sites_all aps,
2771: HR_OPERATING_UNITS ORG
2772: WHERE ORG.name = p_org_name
2773: AND aps.org_id = ORG.organization_id
2774: AND hzps.party_site_name = p_party_site_name
7561: --as we want the duns number to be imported on
7562: --supplier sites(bug6053476)
7563: l_party_site_rec.duns_number_c := l_vendor_site_rec.duns_number;
7564: --Bug5896973
7565: --Added code to populate City + State + Country in party_site_name field of hz_party_sites table
7566: --l_party_site_rec.party_site_name :=
7567: -- nvl(l_vendor_site_rec.city,'')||' '
7568: -- ||nvl(l_vendor_site_rec.state,'')||' '
7569: -- ||nvl(l_vendor_site_rec.country,'');
12268: SELECT hps.party_id,
12269: hps.location_id
12270: INTO x_org_party_id,
12271: x_location_id
12272: FROM HZ_Party_Sites hps, po_vendor_sites_all pvs
12273: WHERE pvs.vendor_site_id = p_vendor_contact_rec.vendor_site_id
12274: AND pvs.party_site_id = hps.party_site_id;
12275:
12276: SELECT party_site_id
12360: SELECT hps.party_id,
12361: hps.location_id
12362: INTO x_org_party_id,
12363: x_location_id
12364: FROM HZ_Party_Sites hps
12365: WHERE hps.party_site_id =
12366: p_vendor_contact_rec.org_party_site_id;
12367: END if;
12368: --open issue 12, no way to populate vendor_site_id