[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT oilv.area_code into l_area_code -- , oilv.order_by_col
FROM (
SELECT iilv.area_code, iilv.order_by_col
FROM (
SELECT phon.phone_area_code area_code
, 0 order_by_col
FROM
HZ_CONTACT_POINTS phon, AR_LOOKUPS look
WHERE phon.owner_table_name(+) = 'HZ_PARTY_SITES'
and phon.primary_flag(+) = 'Y'
and phon.status(+) <> 'I'
and phon.phone_line_type = look.lookup_code(+)
and look.lookup_type(+) = 'PHONE_LINE_TYPE'
and phon.CONTACT_POINT_TYPE = 'PHONE'
and phon.owner_table_id(+) = l_party_site_id
UNION
SELECT phon.phone_area_code area_code
, 1 order_by_col
FROM
HZ_CONTACT_POINTS phon, AR_LOOKUPS look
WHERE phon.owner_table_name(+) = 'HZ_PARTIES'
and phon.primary_flag(+) = 'Y'
and phon.status(+) <> 'I'
and phon.phone_line_type = look.lookup_code(+)
and look.lookup_type(+) = 'PHONE_LINE_TYPE'
and phon.CONTACT_POINT_TYPE = 'PHONE'
and phon.owner_table_id(+) = l_party_id
UNION
Select '' area_code
, 2 order_by_col
from dual
) iilv
--WHERE iilv.area_code IS NOT NULL -- empty string value permitted
-- eihsu bug 2170096
ORDER BY iilv.order_by_col
) oilv
WHERE rownum < 2;
SELECT
party.party_id party_id,
loc.location_id location_id,
site.party_site_id party_site_id,
0000 party_site_use_id,
WF_NOTIFICATION.SubstituteSpecialChars(party.party_name) party_name,
--'NO_CODE' category_code,
WF_NOTIFICATION.SubstituteSpecialChars(loc.address1) || ' '||
WF_NOTIFICATION.SubstituteSpecialChars(loc.address2) || ' '||
WF_NOTIFICATION.SubstituteSpecialChars(loc.address3) || ' '||
WF_NOTIFICATION.SubstituteSpecialChars(loc.address4) address,
WF_NOTIFICATION.SubstituteSpecialChars(loc.city) city,
WF_NOTIFICATION.SubstituteSpecialChars(loc.state) state,
WF_NOTIFICATION.SubstituteSpecialChars(loc.province) province,
WF_NOTIFICATION.SubstituteSpecialChars(loc.postal_code) postal_code,
'' area_code, --NEW
WF_NOTIFICATION.SubstituteSpecialChars(loc.county) county,
WF_NOTIFICATION.SubstituteSpecialChars(loc.country) country,
party.employees_total employees_total, --NEW
party.category_code category_code, --NEW
party.sic_code sic_code, --NEW
'X' primary_flag, --NEEDED??
'X' status, --NEEDED??
'No_type' address_type, --NEEDED??
WF_NOTIFICATION.SubstituteSpecialChars(arlu.meaning) property1,
'' property2,
'' property3,
'' property4,
'' property5
from HZ_PARTY_SITES site,
HZ_LOCATIONS loc,
HZ_PARTIES party,
AR_LOOKUPS arlu
WHERE site.location_id = loc.location_id(+)
and party.party_id = site.party_id(+)
AND site.status = 'A'
and party.party_type = 'ORGANIZATION'
AND party.status = 'A'
and ( UPPER(loc.state) = lc_state OR lc_state IS NULL )
and ( UPPER(loc.country) = lc_country OR lc_country IS NULL )
and ( UPPER(loc.postal_code) = lc_postal_code OR lc_postal_code IS NULL )
and ( party.certification_level = lc_certification_level or lc_certification_level IS NULL)
and party.certification_level = arlu.lookup_code(+)
and 'HZ_PARTY_CERT_LEVEL' = arlu.lookup_type(+)
and UPPER(party.party_name) LIKE lc_search_name
order by arlu.lookup_code /*, party.party_name*/ ;
SELECT count(*) into l_total_rows
from HZ_PARTY_SITES site,
HZ_LOCATIONS loc,
HZ_PARTIES party
WHERE site.location_id = loc.location_id(+)
and party.party_id = site.party_id(+)
AND site.status = 'A'
and party.party_type = 'ORGANIZATION'
AND party.status = 'A'
and ( UPPER(loc.state) = p_state OR p_state IS NULL )
and ( UPPER(loc.country) = p_country OR p_country IS NULL )
and ( UPPER(loc.postal_code) = p_postal_code OR p_postal_code IS NULL )
and ( party.certification_level = p_attribute1 OR p_attribute1 IS NULL )
and UPPER(party.party_name) LIKE l_search_name;
DELETE jty_terr_1001_account_trans_gt;
INSERT INTO jty_terr_1001_account_trans_gt (
TRANS_OBJECT_ID
,TRANS_DETAIL_OBJECT_ID
,COMP_NAME_RANGE
,POSTAL_CODE
,COUNTRY
,CITY
,STATE
,PROVINCE
,COUNTY
,INTEREST_TYPE_ID
,PARTY_ID
,PARTY_SITE_ID
,AREA_CODE
,PARTNER_ID
,NUM_OF_EMPLOYEES
,CATEGORY_CODE
,PARTY_RELATIONSHIP_ID
,SIC_CODE
,SQUAL_NUM06
,CAR_CURRENCY_CODE
,ATTRIBUTE5
,SQUAL_CHAR11
,txn_date
)
VALUES (
-1001
,-1002
,lp_trans_rec.SQUAL_CHAR01(i) -- comp_name_range
,lp_trans_rec.SQUAL_CHAR06(i) -- postal code
,lp_trans_rec.SQUAL_CHAR07(i) -- country
,lp_trans_rec.SQUAL_CHAR02(i) -- city
,lp_trans_rec.SQUAL_CHAR04(i) -- state
,lp_trans_rec.SQUAL_CHAR05(i) -- province
,lp_trans_rec.SQUAL_CHAR03(i) -- county
,lp_trans_rec.SQUAL_NUM07(i) --INTEREST_TYPE_ID
,lp_trans_rec.SQUAL_NUM01(i) --PARTY_ID
,lp_trans_rec.SQUAL_NUM02(i)--PARTY_SITE_ID
,lp_trans_rec.SQUAL_CHAR08(i) --AREA_CODE
,lp_trans_rec.SQUAL_NUM03(i) --PARTNER_ID
,lp_trans_rec.SQUAL_NUM05(i)--NUM_OF_EMPLOYEES
,lp_trans_rec.SQUAL_CHAR09(i) --CATEGORY_CODE
,NULL--PARTY_RELATIONSHIP_ID
,lp_trans_rec.SQUAL_CHAR10(i)--SIC_CODE
,lp_trans_rec.SQUAL_NUM06(i)--SQUAL_NUM06
,NULL--CAR_CURRENCY_CODE
,NULL--ATTRIBUTE5
,lp_trans_rec.SQUAL_CHAR11(i)--SQUAL_CHAR11
,sysdate
);