[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT location_id,location_id
INTO addresscode,location
FROM po_user_preferences
WHERE functional_area = p_functional_area
AND address_type= l_addressType
AND FND_USER_ID = fnd_global.user_id;
SELECT contact_id
INTO contact
FROM po_user_preferences
WHERE functional_area = p_functional_area
AND address_type= l_addressType
AND FND_USER_ID = fnd_global.user_id;
SELECT LOCATION_CODE,ADDRESS_LINE_1,ADDRESS_LINE_2,ADDRESS_LINE_3,TOWN_OR_CITY,REGION_1,
REGION_2,REGION_3,TELEPHONE_NUMBER_1,TELEPHONE_NUMBER_2,POSTAL_CODE,COUNTRY
INTO l_LOCATION_CODE,l_ADDRESS_LINE_1,l_ADDRESS_LINE_2,l_ADDRESS_LINE_3,l_TOWN_OR_CITY,l_REGION_1,
l_REGION_2,l_REGION_3,l_TELEPHONE_NUMBER_1,l_TELEPHONE_NUMBER_2,l_POSTAL_CODE,l_COUNTRY
FROM HR_LOCATIONS_ALL
WHERE LOCATION_ID = location;
SELECT DISTINCT LEI_INFORMATION1
into l_loc_doddac
FROM HR_LOCATION_EXTRA_INFO
where information_type = 'CLM_OFFICE_CODE'
AND 'Y' = po_uda_data_util.is_location_valid (location, HiddenAddType )
AND LOCATION_ID = location;
'Select XMLELEMENT ( ' || HiddenAddType || '_ADDR , XMLFOREST ('''||
l_LOCATION_CODE ||''' ' || HiddenAddType || '_' || 'LOCNAME , ''' ||
l_loc_doddac ||''' ' || HiddenAddType || '_' || 'LOCCODE , ''' ||
l_ADDRESS_LINE_1 || ''' ' || HiddenAddType || '_' || 'ADDRL1, ''' ||
l_ADDRESS_LINE_2 || ''' ' || HiddenAddType || '_' || 'ADDRL2 ,''' ||
l_ADDRESS_LINE_3 || ''' ' || HiddenAddType || '_' || 'ADDRL3 ,''' ||
l_TOWN_OR_CITY || ''' ' || HiddenAddType || '_' || 'CITY, ''' ||
l_REGION_1 || ''' '|| HiddenAddType || '_' || 'REGION1, ''' ||
l_REGION_2 || ''' ' || HiddenAddType || '_' || 'REGION2, ''' ||
l_REGION_3 || ''' ' || HiddenAddType || '_' || 'REGION3, ''' ||
l_TELEPHONE_NUMBER_1 || ''' ' || HiddenAddType || '_' || 'PHONE, ''' ||
l_TELEPHONE_NUMBER_2 || ''' '|| HiddenAddType || '_' || 'FAX, ''' ||
l_POSTAL_CODE || ''' ' || HiddenAddType || '_' || 'ZIPCODE, ''' ||
l_COUNTRY || ''' ' || HiddenAddType || '_' || 'COUNTRY, ''' ||
l_addr_concat_s || ''' ' || HiddenAddType || '_' || 'SADDR, ''' ||
l_addr_concat_l || ''' ' || HiddenAddType || '_' || 'LADDR ' ||
' ))FROM DUAL';
SELECT PERSON_ID,FIRST_NAME,LAST_NAME,TITLE,FULL_NAME,EMAIL_ADDRESS,
(SELECT PHONE_NUMBER FROM PER_PHONES WHERE parent_table = 'PER_ALL_PEOPLE_F' and phone_type = 'W1'
and parent_id = person_id and SYSDATE BETWEEN Nvl(date_from,SYSDATE) AND Nvl(date_to,SYSDATE )) WORK_TELEPHONE
INTO l_PERSON_ID,l_FIRST_NAME,l_LAST_NAME,l_TITLE,l_FULL_NAME,l_EMAIL_ADDRESS, l_WORK_TELEPHONE
FROM PER_ALL_PEOPLE_F
WHERE PERSON_ID = contact
AND SYSDATE BETWEEN Nvl(EFFECTIVE_START_DATE,SYSDATE) AND Nvl(EFFECTIVE_END_DATE,SYSDATE );
SELECT PEI_INFORMATION1
INTO l_POSITION
FROM PER_PEOPLE_EXTRA_INFO
WHERE INFORMATION_TYPE = 'CLM_CONTACT_TITLE'
AND person_id = contact;
'Select XMLELEMENT ( ' || HiddenAddType || '_CNT , XMLFOREST (''' || l_POSITION ||''' '|| HiddenAddType || '_' || 'CTPOSITION , ''' ||
l_PERSON_ID ||''' '|| HiddenAddType || '_' || 'CTID, ''' ||
l_FIRST_NAME ||''' '|| HiddenAddType || '_' || 'CTFNAME, ''' ||
l_LAST_NAME ||''' '|| HiddenAddType || '_' || 'CTLNAME, ''' ||
l_TITLE ||''' '|| HiddenAddType || '_' || 'CTTITLE, ''' ||
l_FULL_NAME ||''' '|| HiddenAddType || '_' || 'CTNAME, ''' ||
l_WORK_TELEPHONE ||''' '|| HiddenAddType || '_' || 'CTPHONE, ''' ||
l_EMAIL_ADDRESS ||''' '|| HiddenAddType || '_' || 'CTEMAIL, ''' ||
l_cnt_concat_s ||''' '|| HiddenAddType || '_' || 'SCONTACT, ''' ||
l_cnt_concat_l ||''' '|| HiddenAddType || '_' || 'LCONTACT' ||
' )) FROM DUAL';
SELECT displayed_field
INTO l_AddTypeDispField
FROM po_lookup_codes
WHERE lookup_type like '%PR_UDA_ADDRESS_TYPES'
AND lookup_code = p_HiddenAddType
AND rownum =1 ;
SELECT displayed_field
INTO l_AddTypeDispField
FROM po_lookup_codes
WHERE lookup_type = 'PO_MOD_UDA_ADDRESS_TYPES'
AND lookup_code = p_HiddenAddType;
UPDATE PO_REQUISITION_HEADERS_ALL
SET CLM_ISSUING_OFFICE = x_location,
CLM_COTR_CONTACT = x_contact_id
WHERE REQUISITION_HEADER_ID = p_header_id;
UPDATE PO_REQUISITION_HEADERS_ALL
SET CLM_COTR_OFFICE = x_location,
CLM_COTR_CONTACT = x_contact_id
WHERE REQUISITION_HEADER_ID = p_header_id;
UPDATE PO_REQUISITION_HEADERS_ALL
SET CLM_REQ_OFFICE = x_location,
CLM_REQ_CONTACT = x_contact_id
WHERE REQUISITION_HEADER_ID = p_header_id;
UPDATE PO_REQUISITION_HEADERS_ALL
SET CLM_ASSIST_OFFICE = x_location,
CLM_ASSIST_CONTACT = x_contact_id
WHERE REQUISITION_HEADER_ID = p_header_id;
UPDATE PO_HEADERS_DRAFT_ALL
SET CLM_ISSUING_OFFICE = x_location
WHERE PO_HEADER_ID = p_header_id
AND DRAFT_ID = XPD_DRAFT_ID;
UPDATE PO_HEADERS_DRAFT_ALL
SET CLM_COTR_OFFICE = x_location,
CLM_COTR_CONTACT = x_contact_id
WHERE PO_HEADER_ID = p_header_id
AND DRAFT_ID = XPD_DRAFT_ID;
UPDATE PO_HEADERS_DRAFT_ALL
SET CLM_ISSUING_OFFICE = x_location
WHERE PO_HEADER_ID = p_header_id
AND DRAFT_ID = XPD_DRAFT_ID;
UPDATE PO_HEADERS_DRAFT_ALL
SET CLM_COTR_OFFICE = x_location,
CLM_COTR_CONTACT = x_contact_id
WHERE PO_HEADER_ID = p_header_id
AND DRAFT_ID = XPD_DRAFT_ID;
UPDATE PO_HEADERS_DRAFT_ALL
SET CLM_MOD_ADMIN_OFFICE = x_location
WHERE PO_HEADER_ID = p_header_id
AND DRAFT_ID = XPD_DRAFT_ID;
UPDATE PO_HEADERS_DRAFT_ALL
SET CLM_MOD_ISSUING_OFFICE = x_location
WHERE PO_HEADER_ID = p_header_id
AND DRAFT_ID = XPD_DRAFT_ID;
UPDATE PO_HEADERS_DRAFT_ALL
SET CLM_REQ_OFFICE = x_location
WHERE PO_HEADER_ID = p_header_id
AND DRAFT_ID = XPD_DRAFT_ID;
SELECT distinct att_assoc.ATTR_GROUP_TYPE || att_assoc.ATTR_GROUP_ID || attrs.attr_id || 'LOVDisp' Attr_Bean_Name
FROM ego_obj_attr_grp_assocs_v att_assoc , EGO_ATTRS_v attrs WHERE
attrs.ATTR_GROUP_TYPE = att_assoc.ATTR_GROUP_TYPE
AND attrs.ATTR_GROUP_NAME = att_assoc.ATTR_GROUP_NAME
AND attrs.ATTR_GROUP_NAME = 'addresses'
AND ATTR_NAME = p_attr_name;
SELECT DESCRIPTION
INTO x_cust_proj_text
FROM PO_LOOKUP_CODES
WHERE LOOKUP_TYPE = 'PO_CLM_CUSTOMER_PROJECT_CODE'
AND LOOKUP_CODE = p_cust_proj_code;
SELECT DESCRIPTION
INTO x_cust_proj_text
FROM PO_LOOKUP_CODES
WHERE LOOKUP_TYPE = 'PO_CLM_CUSTOMER_PROJECT_CODE'
AND LOOKUP_CODE = p_cust_proj_code;
UPDATE PO_REQUISITION_HEADERS_ALL
SET CLM_PRIORITY_CODE = p_priority
WHERE REQUISITION_HEADER_ID = p_header_id;
UPDATE PO_HEADERS_DRAFT_ALL
SET CLM_PRIORITY_CODE = p_priority
WHERE PO_HEADER_ID = p_header_id
AND Draft_id = XPD_DRAFT_ID;
SELECT DESCRIPTION
INTO x_cust_proj_text
FROM PO_LOOKUP_CODES
WHERE LOOKUP_TYPE = 'PO_CLM_CUSTOMER_PROJECT_CODE'
AND LOOKUP_CODE = p_cust_proj_code;
UPDATE PO_LINES_DRAFT_ALL
SET CLM_MDAPS_MAIS = p_mdaps_mais,
CLM_NAICS = p_naics,
CLM_FSC_PSC = p_fsc_psc
WHERE PO_LINE_ID = p_line_id
AND Draft_id = XPD_DRAFT_ID;
SELECT LONG_DESCRIPTION
INTO x_item_long_desc
FROM MTL_SYSTEM_ITEMS_TL
WHERE INVENTORY_ITEM_ID = p_item_id
AND ORGANIZATION_ID = p_org_id
AND language = userenv('LANG');
SELECT LONG_DESCRIPTION
INTO x_item_long_desc
FROM MTL_SYSTEM_ITEMS_TL
WHERE INVENTORY_ITEM_ID = p_item_id
AND ORGANIZATION_ID = p_org_id
AND language = userenv('LANG');
SELECT ADDRESS_LINE_1, ADDRESS_LINE_2, TOWN_OR_CITY, REGION_2, STYLE, POSTAL_CODE,COUNTRY
INTO l_addr_line_1, l_addr_line_2, l_town_or_city, l_region2, l_style, l_postal_code,p_hiddenCountry
FROM HR_LOCATIONS
WHERE LOCATION_ID = p_ship_to_loc_id;
l_ship_addr_xml_sql := 'SELECT XMLFOREST(HLC.LOCATION_ID ship_to_location_id, HLC.LOCATION_CODE location_name,
HLC.ADDRESS_LINE_1 address_line_1,
HLC.ADDRESS_LINE_2 address_line_2,
HLC.ADDRESS_LINE_3 address_line_3,
hlc.TOWN_OR_CITY city,
hlc.region_1, nvl2(fcl1.meaning, fcl1.meaning, fcl3.meaning) region1_desc,
hlc.region_2, fcl2.meaning region2_desc,
HLC.COUNTRY, NVL(FTE.TERRITORY_SHORT_NAME,HLC.COUNTRY) country_name,
hlc.postal_code,
HLC.TELEPHONE_NUMBER_1 contact_phone, HLC.TELEPHONE_NUMBER_2 contact_fax).getStringVal()
FROM HR_LOCATIONS HLC,
FND_TERRITORIES_TL FTE,
FND_LOOKUP_VALUES FCL1,
FND_LOOKUP_VALUES FCL2,
FND_LOOKUP_VALUES FCL3
WHERE HLC.LOCATION_ID = :1
AND HLC.COUNTRY = FTE.TERRITORY_CODE (+)
AND DECODE(FTE.TERRITORY_CODE, NULL, ''1'', FTE.LANGUAGE) = DECODE(FTE.TERRITORY_CODE, NULL, ''1'', USERENV(''LANG''))
AND HLC.REGION_1 = FCL1.LOOKUP_CODE (+)
AND HLC.COUNTRY
|| ''_PROVINCE'' = FCL1.LOOKUP_TYPE (+)
AND DECODE(FCL1.LOOKUP_CODE, NULL, ''1'', FCL1.SECURITY_GROUP_ID) = DECODE(FCL1.LOOKUP_CODE, NULL, ''1'',FND_GLOBAL.LOOKUP_SECURITY_GROUP(FCL1.LOOKUP_TYPE, FCL1.VIEW_APPLICATION_ID))
AND DECODE(FCL1.LOOKUP_CODE, NULL, ''1'', FCL1.VIEW_APPLICATION_ID) = DECODE(FCL1.LOOKUP_CODE, NULL, ''1'', 3)
AND DECODE (FCL1.LOOKUP_CODE, NULL, ''1'', FCL1.LANGUAGE) = DECODE(FCL1.LOOKUP_CODE, NULL, ''1'', USERENV(''LANG''))
AND HLC.REGION_2 = FCL2.LOOKUP_CODE (+)
AND HLC.COUNTRY
|| ''_STATE'' = FCL2.LOOKUP_TYPE (+)
AND DECODE(FCL2.LOOKUP_CODE, NULL, ''1'', FCL2.SECURITY_GROUP_ID) = DECODE(FCL2.LOOKUP_CODE, NULL, ''1'',FND_GLOBAL.LOOKUP_SECURITY_GROUP(FCL2.LOOKUP_TYPE, FCL2.VIEW_APPLICATION_ID))
AND DECODE(FCL2.LOOKUP_CODE, NULL, ''1'', FCL2.VIEW_APPLICATION_ID) = DECODE(FCL2.LOOKUP_CODE, NULL, ''1'', 3)
AND DECODE(FCL2.LOOKUP_CODE, NULL, ''1'', FCL2.LANGUAGE) = DECODE(FCL2.LOOKUP_CODE, NULL, ''1'', USERENV(''LANG''))
AND HLC.REGION_1 = FCL3.LOOKUP_CODE (+)
AND HLC.COUNTRY
|| ''_COUNTY'' = FCL3.LOOKUP_TYPE (+)
AND DECODE(FCL3.LOOKUP_CODE, NULL, ''1'', FCL3.SECURITY_GROUP_ID) = DECODE(FCL3.LOOKUP_CODE, NULL, ''1'',FND_GLOBAL.LOOKUP_SECURITY_GROUP(FCL3.LOOKUP_TYPE, FCL3.VIEW_APPLICATION_ID))
AND DECODE(FCL3.LOOKUP_CODE, NULL, ''1'', FCL3.VIEW_APPLICATION_ID) = DECODE(FCL3.LOOKUP_CODE, NULL, ''1'', 3)
AND DECODE(FCL3.LOOKUP_CODE, NULL, ''1'', FCL3.LANGUAGE) = DECODE(FCL3.LOOKUP_CODE, NULL, ''1'', USERENV(''LANG''))';
SELECT ADDRESS_LINE_1, ADDRESS_LINE_2, TOWN_OR_CITY, REGION_2, STYLE, POSTAL_CODE,COUNTRY
INTO l_addr_line_1, l_addr_line_2, l_town_or_city, l_region2, l_style, l_postal_code,p_hiddenCountry
FROM HR_LOCATIONS
WHERE LOCATION_ID = p_ship_to_loc_id;
l_ship_addr_xml_sql := 'SELECT XMLFOREST(HLC.LOCATION_ID ship_to_location_id, HLC.LOCATION_CODE location_name,
HLC.ADDRESS_LINE_1 address_line_1,
HLC.ADDRESS_LINE_2 address_line_2,
HLC.ADDRESS_LINE_3 address_line_3,
hlc.TOWN_OR_CITY city,
hlc.region_1, nvl2(fcl1.meaning, fcl1.meaning, fcl3.meaning) region1_desc,
hlc.region_2, fcl2.meaning region2_desc,
HLC.COUNTRY, NVL(FTE.TERRITORY_SHORT_NAME,HLC.COUNTRY) country_name,
hlc.postal_code,
HLC.TELEPHONE_NUMBER_1 contact_phone, HLC.TELEPHONE_NUMBER_2 contact_fax).getStringVal()
FROM HR_LOCATIONS HLC,
FND_TERRITORIES_TL FTE,
FND_LOOKUP_VALUES FCL1,
FND_LOOKUP_VALUES FCL2,
FND_LOOKUP_VALUES FCL3
WHERE HLC.LOCATION_ID = :1
AND HLC.COUNTRY = FTE.TERRITORY_CODE (+)
AND DECODE(FTE.TERRITORY_CODE, NULL, ''1'', FTE.LANGUAGE) = DECODE(FTE.TERRITORY_CODE, NULL, ''1'', USERENV(''LANG''))
AND HLC.REGION_1 = FCL1.LOOKUP_CODE (+)
AND HLC.COUNTRY
|| ''_PROVINCE'' = FCL1.LOOKUP_TYPE (+)
AND DECODE(FCL1.LOOKUP_CODE, NULL, ''1'', FCL1.SECURITY_GROUP_ID) = DECODE(FCL1.LOOKUP_CODE, NULL, ''1'',FND_GLOBAL.LOOKUP_SECURITY_GROUP(FCL1.LOOKUP_TYPE, FCL1.VIEW_APPLICATION_ID))
AND DECODE(FCL1.LOOKUP_CODE, NULL, ''1'', FCL1.VIEW_APPLICATION_ID) = DECODE(FCL1.LOOKUP_CODE, NULL, ''1'', 3)
AND DECODE (FCL1.LOOKUP_CODE, NULL, ''1'', FCL1.LANGUAGE) = DECODE(FCL1.LOOKUP_CODE, NULL, ''1'', USERENV(''LANG''))
AND HLC.REGION_2 = FCL2.LOOKUP_CODE (+)
AND HLC.COUNTRY
|| ''_STATE'' = FCL2.LOOKUP_TYPE (+)
AND DECODE(FCL2.LOOKUP_CODE, NULL, ''1'', FCL2.SECURITY_GROUP_ID) = DECODE(FCL2.LOOKUP_CODE, NULL, ''1'',FND_GLOBAL.LOOKUP_SECURITY_GROUP(FCL2.LOOKUP_TYPE, FCL2.VIEW_APPLICATION_ID))
AND DECODE(FCL2.LOOKUP_CODE, NULL, ''1'', FCL2.VIEW_APPLICATION_ID) = DECODE(FCL2.LOOKUP_CODE, NULL, ''1'', 3)
AND DECODE(FCL2.LOOKUP_CODE, NULL, ''1'', FCL2.LANGUAGE) = DECODE(FCL2.LOOKUP_CODE, NULL, ''1'', USERENV(''LANG''))
AND HLC.REGION_1 = FCL3.LOOKUP_CODE (+)
AND HLC.COUNTRY
|| ''_COUNTY'' = FCL3.LOOKUP_TYPE (+)
AND DECODE(FCL3.LOOKUP_CODE, NULL, ''1'', FCL3.SECURITY_GROUP_ID) = DECODE(FCL3.LOOKUP_CODE, NULL, ''1'',FND_GLOBAL.LOOKUP_SECURITY_GROUP(FCL3.LOOKUP_TYPE, FCL3.VIEW_APPLICATION_ID))
AND DECODE(FCL3.LOOKUP_CODE, NULL, ''1'', FCL3.VIEW_APPLICATION_ID) = DECODE(FCL3.LOOKUP_CODE, NULL, ''1'', 3)
AND DECODE(FCL3.LOOKUP_CODE, NULL, ''1'', FCL3.LANGUAGE) = DECODE(FCL3.LOOKUP_CODE, NULL, ''1'', USERENV(''LANG''))';
UPDATE PO_REQUISITION_HEADERS_ALL
SET SUGGESTED_AWARD_NO = p_suggest_aw_no
WHERE REQUISITION_HEADER_ID = p_header_id;
x_msg_data := 'When the Set Aside Status is ''Set Aside'', you must select a value for the set aside type.';
fnd_message.set_name(d_appln_short_name,'PO_SET_ASIDE_SELECT_TYPE');
x_msg_data := 'When the Set Aside Status is ''Set Aside'' you must select a value for the NAICS ';
SELECT pvc.first_name, pvc.email_address,
DECODE(TRUNC(LENGTH(pvc.area_code || ' ' ||pvc.phone) / 26),0,pvc.area_code ||DECODE(pvc.area_code,NULL,NULL,
DECODE(pvc.phone,NULL,NULL, ' ')) ||pvc.phone, pvc.area_code ||pvc.phone),
pvc.vendor_contact_id,
pvc.last_name ||NVL2(pvc.first_name,', ' ||NVL2(pvc.prefix,prefix ||' ',NULL) ||pvc.first_name,NULL),
pvc.vendor_site_id,
pvc.fax_area_code ||NVL2(pvc.fax_area_code,NVL2(pvc.fax,' ',NULL), NULL) ||pvc.fax,
pvc.last_name,
fu.user_name,
pvc.title INTO l_vendor_contact_first_name, l_vendor_contact_email_address, l_vendor_contact_phone, l_vendor_contact_id, l_vendor_contact_full_name,
l_vendor_site_id, l_vendor_contact_fax, l_vendor_contact_last_name, l_user_name, l_vendor_contact_title
FROM po_vendor_contacts pvc,
fnd_user fu
WHERE pvc.per_party_id = fu.person_party_id(+) AND vendor_contact_id = p_contact_id and vendor_site_id = p_supplier_site_id;
l_contact_xml_sql := 'Select XMLFOREST (''' ||
l_vendor_contact_first_name || '''VENDOR_CONTACT_FIRST_NAME , ''' ||
l_vendor_contact_email_address || '''VENDOR_CONTACT_EMAIL_ADDRESS , ''' ||
l_vendor_contact_phone || '''VENDOR_CONTACT_PHONE , ''' ||
l_vendor_contact_id || '''VENDOR_CONTACT_ID , ''' ||
l_vendor_contact_full_name || '''VENDOR_CONTACT_FULL_NAME , ''' ||
l_vendor_site_id || '''VENDOR_SITE_ID , ''' ||
l_vendor_contact_fax || '''VENDOR_CONTACT_FAX , ''' ||
l_vendor_contact_last_name || '''VENDOR_CONTACT_LAST_NAME , ''' ||
l_user_name || '''USER_NAME, ''' ||
l_vendor_contact_title || '''VENDOR_CONTACT_TITLE, ''' ||
l_vendor_cnt_concat || '''VENDOR_FCONTACT ' ||
' )FROM DUAL';
SELECT vsa.LEGAL_BUSINESS_NAME,
vsa.DOING_BUS_AS_NAME,
pvn.VENDOR_NAME,
vsa.vendor_site_id,
vsa.vendor_site_code,
vsa.address_line1,
vsa.address_line2,
vsa.address_line3,
vsa.city,
nvl2(vsa.PROVINCE, vsa.PROVINCE, vsa.COUNTY),
nvl2(fcl1.meaning, fcl1.meaning, fcl3.meaning),
nvl2(vsa.STATE, vsa.STATE, vsa.PROVINCE),
fcl2.meaning,
vsa.Country,
NVL(fte.TERRITORY_SHORT_NAME,vsa.COUNTRY),
vsa.zip INTO l_legal_buss_name, l_doing_bus_name, l_vendor_name,l_VENDOR_SITE_ID, l_vendor_site_code, l_vendor_addr_line1, l_vendor_addr_line2, l_vendor_addr_line3,
l_vendor_city, l_vendor_province_code, l_vendor_province, l_vendor_state_code, l_vendor_state, l_vendor_country_code,
l_vendor_country,l_vendor_zip
from PO_VENDOR_SITES_ALL vsa, po_vendors pvn,
fnd_territories_tl fte,
FND_LOOKUP_VALUES FCL1,
FND_LOOKUP_VALUES FCL2,
FND_LOOKUP_VALUES FCL3
where vsa.vendor_site_id = p_supplier_site_id
and vsa.vendor_id = pvn.vendor_id
and vsa.country = fte.territory_code (+)
AND DECODE(FTE.TERRITORY_CODE, NULL, '1', FTE.LANGUAGE) = DECODE(FTE.TERRITORY_CODE, NULL, '1', USERENV('LANG'))
AND vsa.province = FCL1.LOOKUP_CODE (+)
AND vsa.country || '_PROVINCE' = FCL1.LOOKUP_TYPE (+)
AND DECODE(FCL1.LOOKUP_CODE, NULL, '1', FCL1.SECURITY_GROUP_ID)=
DECODE(FCL1.LOOKUP_CODE, NULL, '1',FND_GLOBAL.LOOKUP_SECURITY_GROUP(FCL1.LOOKUP_TYPE, FCL1.VIEW_APPLICATION_ID))
AND DECODE(FCL1.LOOKUP_CODE, NULL, '1', FCL1.VIEW_APPLICATION_ID) = DECODE(FCL1.LOOKUP_CODE, NULL, '1', 3)
AND DECODE (FCL1.LOOKUP_CODE, NULL, '1', FCL1.LANGUAGE) = DECODE(FCL1.LOOKUP_CODE, NULL, '1', USERENV('LANG'))
AND vsa.state = FCL2.LOOKUP_CODE (+)
AND vsa.country || '_STATE' = FCL2.LOOKUP_TYPE (+)
AND DECODE(FCL2.LOOKUP_CODE, NULL, '1', FCL2.SECURITY_GROUP_ID)=
DECODE(FCL2.LOOKUP_CODE, NULL, '1',FND_GLOBAL.LOOKUP_SECURITY_GROUP(FCL2.LOOKUP_TYPE, FCL2.VIEW_APPLICATION_ID))
AND DECODE(FCL2.LOOKUP_CODE, NULL, '1', FCL2.VIEW_APPLICATION_ID) = DECODE(FCL2.LOOKUP_CODE, NULL, '1', 3)
AND DECODE(FCL2.LOOKUP_CODE, NULL, '1', FCL2.LANGUAGE)= DECODE(FCL2.LOOKUP_CODE, NULL, '1', USERENV('LANG'))
AND vsa.county = FCL3.LOOKUP_CODE (+)
AND vsa.country ||'_COUNTY' = FCL3.LOOKUP_TYPE (+)
AND DECODE(FCL3.LOOKUP_CODE, NULL, '1', FCL3.SECURITY_GROUP_ID) =
DECODE(FCL3.LOOKUP_CODE, NULL, '1',FND_GLOBAL.LOOKUP_SECURITY_GROUP(FCL3.LOOKUP_TYPE, FCL3.VIEW_APPLICATION_ID))
AND DECODE(FCL3.LOOKUP_CODE, NULL, '1', FCL3.VIEW_APPLICATION_ID) = DECODE(FCL3.LOOKUP_CODE, NULL, '1', 3)
AND DECODE(FCL3.LOOKUP_CODE, NULL, '1', FCL3.LANGUAGE) = DECODE(FCL3.LOOKUP_CODE, NULL, '1', USERENV('LANG'));
l_address_xml_sql := 'Select XMLFOREST (''' ||
l_vendor_site_name || '''VENDOR_SITE_NAME , ''' ||
l_vendor_site_id || '''VENDOR_SITE_ID , ''' ||
l_vendor_site_code || '''VENDOR_SITE_CODE , ''' ||
l_vendor_addr_line1 || '''VENDOR_ADDRESS_LINE1 , ''' ||
l_vendor_addr_line2 || '''VENDOR_ADDRESS_LINE2 , ''' ||
l_vendor_addr_line3 || '''VENDOR_ADDRESS_LINE3 , ''' ||
l_vendor_city || '''VENDOR_CITY , ''' ||
l_vendor_province_code || '''VENDOR_PROVINCE_CODE , ''' ||
l_vendor_province || '''VENDOR_PROVINCE , ''' ||
l_vendor_state_code || '''VENDOR_STATE_CODE , ''' ||
l_vendor_state || '''VENDOR_STATE , ''' ||
l_vendor_country_code || '''VENDOR_COUNTRY_CODE , ''' ||
l_vendor_country || '''VENDOR_COUNTRY , ''' ||
l_vendor_zip || '''VENDOR_ZIP , ''' ||
l_vendor_concat_l || '''VENDOR_FADDR ' ||
' )FROM DUAL';
SELECT vendor_name
INTO p_orig_splr_name
FROM po_vendors
WHERE vendor_id = x_supplier_id;
SELECT DOING_BUS_AS_NAME,
CAGE_CODE,
DUNS_NUMBER,
SMALL_BUSINESS_CODE,
ADDRESS_LINE1,
ADDRESS_LINE2,
ADDRESS_LINE3,
CITY,
STATE,
ZIP,
PROVINCE,
COUNTRY
INTO l_doing_bsns_as,
l_cage_code,
l_duns_num,
l_suplr_size,
l_addressLine1,
l_addressLine2,
l_addressLine3,
l_city,
l_state,
l_province,
l_country,
l_zipcode
FROM AP_SUPPLIER_SITES_ALL
WHERE vendor_id = x_supplier_id
AND vendor_site_id = x_supplier_site_id;
SELECT VENDOR_SITE_CODE,
VENDOR_SITE_ID,
ADDRESS_LINE1,
ADDRESS_LINE2,
ADDRESS_LINE3,
CITY,
STATE,
ZIP,
PROVINCE,
COUNTRY
INTO l_addressCode,
p_remit_to_addrs,
l_addressLine1,
l_addressLine2,
l_addressLine3,
l_city,
l_state,
l_province,
l_country,
l_zipcode
from AP_SUPPLIER_SITES_ALL
where pay_site_flag = 'Y'
and vendor_id = x_supplier_id
AND vendor_site_id = x_supplier_site_id;
SELECT MEANING
INTO p_ccr_reg_status
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE='FV_CCR_REG_STATUS'
AND LOOKUP_CODE = l_ccr_reg_status
AND LANGUAGE = UserEnv('Lang');
SELECT party_id
INTO l_party_id
FROM po_vendors
WHERE vendor_id = x_supplier_id;
SELECT flv.meaning
BULK COLLECT INTO l_meanings_table
FROM fnd_lookup_values_vl flv,
POS_BUS_CLASS_ATTR pca
WHERE flv.enabled_flag='Y'
AND flv.start_date_active < sysdate
AND (flv.end_date_active > sysdate or flv.end_date_active is null)
AND flv.lookup_type = 'POS_BUSINESS_CLASSIFICATIONS'
AND flv.lookup_type = pca.lookup_type
AND flv.lookup_code = pca.lookup_code
AND pca.party_id = l_party_id
AND ( pca.end_date_active is null or trunc(pca.end_date_active) > sysdate )
AND pca.status='A'
AND pca.class_status = 'APPROVED'
AND pca.classification_id not in
(
SELECT classification_id
FROM pos_bus_class_reqs pbcr, pos_supplier_mappings psm
WHERE psm.party_id = pca.party_id
AND psm.mapping_id = pbcr.mapping_id
AND pbcr.request_status = 'PENDING'
AND pbcr.request_type in ( 'ADD', 'UPDATE' )
AND pbcr.classification_id is not null );
SELECT Taxpayer_id
INTO l_tin_num
FROM FV_CCR_VENDORS
WHERE vendor_id = x_supplier_id;
SELECT jgzz_fiscal_code
INTO l_tin_num
FROM HZ_Parties
WHERE PARTY_ID = l_party_id;
SELECT phone,email_address,
Nvl(PREFIX,PREFIX ||' ')|| Nvl(FIRST_NAME,FIRST_NAME ||' ') || Nvl(MIDDLE_NAME,MIDDLE_NAME||' ') || LAST_NAME
INTO l_phone_num,
l_email,
l_supplr_contact
FROM PO_VENDOR_CONTACTS
WHERE vendor_contact_id = x_contact_id
AND vendor_site_id = x_supplier_site_id;
x_msg_data := 'When EFT does not apply, an EFT exclusion reason must be selected.';
fnd_message.set_name(d_appln_short_name, 'PO_NO_EFT_REASON_SELECTED');
SELECT VENDOR_SITE_CODE,
ADDRESS_LINE1,
ADDRESS_LINE2,
ADDRESS_LINE3,
CITY,
STATE,
ZIP,
PROVINCE,
COUNTRY
INTO l_addresscode,
l_addressLine1,
l_addressLine2,
l_addressLine3,
l_city,
l_state,
l_province,
l_country,
l_zipcode
from AP_SUPPLIER_SITES_ALL
where pay_site_flag = 'Y'
and vendor_id = x_supplier_id
AND VENDOR_SITE_ID = p_remit_to_addrs;
SELECT vendor_name
INTO p_orig_splr_name
FROM po_vendors
WHERE vendor_id = x_supplier_id;
SELECT vendor_id,vendor_site_id,vendor_contact_id
INTO l_supplier_id,l_supplier_site_id,l_contact_id
FROM po_headers_all
WHERE po_header_id = l_po_header_id;
SELECT vendor_id,vendor_site_id,vendor_contact_id
INTO l_supplier_id,l_supplier_site_id,l_contact_id
FROM po_headers_draft_all
WHERE po_header_id = l_po_header_id
AND draft_id = draft_id;
SELECT vendor_name
INTO p_orig_splr_name
FROM po_vendors
WHERE vendor_id = l_supplier_id;
SELECT DOING_BUS_AS_NAME,
CAGE_CODE,
DUNS_NUMBER,
SMALL_BUSINESS_CODE,
ADDRESS_LINE1,
ADDRESS_LINE2,
ADDRESS_LINE3,
CITY,
STATE,
ZIP,
PROVINCE,
COUNTRY
INTO l_doing_bsns_as,
l_cage_code,
l_duns_num,
l_suplr_size,
l_addressLine1,
l_addressLine2,
l_addressLine3,
l_city,
l_state,
l_province,
l_country,
l_zipcode
FROM AP_SUPPLIER_SITES_ALL
WHERE vendor_id = l_supplier_id
AND vendor_site_id = l_supplier_site_id;
SELECT phone,email_address,
Nvl(PREFIX,PREFIX ||' ')|| Nvl(FIRST_NAME,FIRST_NAME ||' ') || Nvl(MIDDLE_NAME,MIDDLE_NAME||' ') || LAST_NAME
INTO l_phone_num,
l_email,
l_supplr_contact
FROM PO_VENDOR_CONTACTS
WHERE vendor_contact_id = l_contact_id
AND vendor_site_id = l_supplier_site_id;
SELECT MEANING
INTO p_ccr_reg_status
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE='FV_CCR_REG_STATUS'
AND LOOKUP_CODE = l_ccr_reg_status
AND LANGUAGE = UserEnv('Lang');
SELECT clm_special_contract_type
INTO l_clm_special_cont_type
FROM po_headers_all poh
WHERE poh.po_header_id = l_po_header_id;
SELECT party_id
INTO l_party_id
FROM po_vendors
WHERE vendor_id = l_supplier_id;
SELECT flv.meaning
BULK COLLECT INTO l_meanings_table
FROM fnd_lookup_values_vl flv,
POS_BUS_CLASS_ATTR pca
WHERE flv.enabled_flag='Y'
AND flv.start_date_active < sysdate
AND (flv.end_date_active > sysdate or flv.end_date_active is null)
AND flv.lookup_type = 'POS_BUSINESS_CLASSIFICATIONS'
AND flv.lookup_type = pca.lookup_type
AND flv.lookup_code = pca.lookup_code
AND pca.party_id = l_party_id
AND ( pca.end_date_active is null or trunc(pca.end_date_active) > sysdate )
AND pca.status='A'
AND pca.class_status = 'APPROVED'
AND pca.classification_id not in
(
SELECT classification_id
FROM pos_bus_class_reqs pbcr, pos_supplier_mappings psm
WHERE psm.party_id = pca.party_id
AND psm.mapping_id = pbcr.mapping_id
AND pbcr.request_status = 'PENDING'
AND pbcr.request_type in ( 'ADD', 'UPDATE' )
AND pbcr.classification_id is not null
);
SELECT Taxpayer_id
INTO l_tin_num
FROM FV_CCR_VENDORS
WHERE vendor_id = l_supplier_id;
SELECT jgzz_fiscal_code
INTO l_tin_num
FROM HZ_Parties
WHERE PARTY_ID = l_party_id;
SELECT SHIP_TO_LOCATION_ID
INTO l_ship_to_loc_id
FROM po_line_locations_all
WHERE LINE_LOCATION_ID = p_line_loc_id;
SELECT ADDRESS_LINE_1, ADDRESS_LINE_2, TOWN_OR_CITY, REGION_2, STYLE, POSTAL_CODE,COUNTRY
INTO l_addr_line_1, l_addr_line_2, l_town_or_city, l_region2, l_style, l_postal_code,p_hiddenCountry
FROM HR_LOCATIONS
WHERE LOCATION_ID = l_ship_to_loc_id;
l_ship_addr_xml_sql := 'SELECT XMLFOREST(HLC.LOCATION_ID ship_to_location_id, HLC.LOCATION_CODE location_name,
HLC.ADDRESS_LINE_1 address_line_1,
HLC.ADDRESS_LINE_2 address_line_2,
HLC.ADDRESS_LINE_3 address_line_3,
hlc.TOWN_OR_CITY city,
hlc.region_1, nvl2(fcl1.meaning, fcl1.meaning, fcl3.meaning) region1_desc,
hlc.region_2, fcl2.meaning region2_desc,
HLC.COUNTRY, NVL(FTE.TERRITORY_SHORT_NAME,HLC.COUNTRY) country_name,
hlc.postal_code,
HLC.TELEPHONE_NUMBER_1 contact_phone, HLC.TELEPHONE_NUMBER_2 contact_fax).getStringVal()
FROM HR_LOCATIONS HLC,
FND_TERRITORIES_TL FTE,
FND_LOOKUP_VALUES FCL1,
FND_LOOKUP_VALUES FCL2,
FND_LOOKUP_VALUES FCL3
WHERE HLC.LOCATION_ID = :1
AND HLC.COUNTRY = FTE.TERRITORY_CODE (+)
AND DECODE(FTE.TERRITORY_CODE, NULL, ''1'', FTE.LANGUAGE) = DECODE(FTE.TERRITORY_CODE, NULL, ''1'', USERENV(''LANG''))
AND HLC.REGION_1 = FCL1.LOOKUP_CODE (+)
AND HLC.COUNTRY
|| ''_PROVINCE'' = FCL1.LOOKUP_TYPE (+)
AND DECODE(FCL1.LOOKUP_CODE, NULL, ''1'', FCL1.SECURITY_GROUP_ID) = DECODE(FCL1.LOOKUP_CODE, NULL, ''1'',FND_GLOBAL.LOOKUP_SECURITY_GROUP(FCL1.LOOKUP_TYPE, FCL1.VIEW_APPLICATION_ID))
AND DECODE(FCL1.LOOKUP_CODE, NULL, ''1'', FCL1.VIEW_APPLICATION_ID) = DECODE(FCL1.LOOKUP_CODE, NULL, ''1'', 3)
AND DECODE (FCL1.LOOKUP_CODE, NULL, ''1'', FCL1.LANGUAGE) = DECODE(FCL1.LOOKUP_CODE, NULL, ''1'', USERENV(''LANG''))
AND HLC.REGION_2 = FCL2.LOOKUP_CODE (+)
AND HLC.COUNTRY
|| ''_STATE'' = FCL2.LOOKUP_TYPE (+)
AND DECODE(FCL2.LOOKUP_CODE, NULL, ''1'', FCL2.SECURITY_GROUP_ID) = DECODE(FCL2.LOOKUP_CODE, NULL, ''1'',FND_GLOBAL.LOOKUP_SECURITY_GROUP(FCL2.LOOKUP_TYPE, FCL2.VIEW_APPLICATION_ID))
AND DECODE(FCL2.LOOKUP_CODE, NULL, ''1'', FCL2.VIEW_APPLICATION_ID) = DECODE(FCL2.LOOKUP_CODE, NULL, ''1'', 3)
AND DECODE(FCL2.LOOKUP_CODE, NULL, ''1'', FCL2.LANGUAGE) = DECODE(FCL2.LOOKUP_CODE, NULL, ''1'', USERENV(''LANG''))
AND HLC.REGION_1 = FCL3.LOOKUP_CODE (+)
AND HLC.COUNTRY
|| ''_COUNTY'' = FCL3.LOOKUP_TYPE (+)
AND DECODE(FCL3.LOOKUP_CODE, NULL, ''1'', FCL3.SECURITY_GROUP_ID) = DECODE(FCL3.LOOKUP_CODE, NULL, ''1'',FND_GLOBAL.LOOKUP_SECURITY_GROUP(FCL3.LOOKUP_TYPE, FCL3.VIEW_APPLICATION_ID))
AND DECODE(FCL3.LOOKUP_CODE, NULL, ''1'', FCL3.VIEW_APPLICATION_ID) = DECODE(FCL3.LOOKUP_CODE, NULL, ''1'', 3)
AND DECODE(FCL3.LOOKUP_CODE, NULL, ''1'', FCL3.LANGUAGE) = DECODE(FCL3.LOOKUP_CODE, NULL, ''1'', USERENV(''LANG''))';
SELECT distinct att_assoc.ATTR_GROUP_TYPE || att_assoc.ATTR_GROUP_ID
|| attrs.attr_id || 'LOVDisp' Attr_Bean_Name
FROM ego_obj_attr_grp_assocs_v att_assoc , EGO_ATTRS_v attrs
WHERE attrs.ATTR_GROUP_TYPE = att_assoc.ATTR_GROUP_TYPE
AND attrs.ATTR_GROUP_NAME = att_assoc.ATTR_GROUP_NAME
AND attrs.ATTR_GROUP_NAME = 'INSPECT_INFO'
AND ATTR_NAME = p_attr_name;
SELECT LOCATION_CODE,ADDRESS_LINE_1,ADDRESS_LINE_2,ADDRESS_LINE_3,TOWN_OR_CITY,REGION_1,
REGION_2,REGION_3,TELEPHONE_NUMBER_1,TELEPHONE_NUMBER_2,POSTAL_CODE,COUNTRY
INTO l_LOCATION_CODE,l_ADDRESS_LINE_1,l_ADDRESS_LINE_2,l_ADDRESS_LINE_3,l_TOWN_OR_CITY,l_REGION_1,
l_REGION_2,l_REGION_3,l_TELEPHONE_NUMBER_1,l_TELEPHONE_NUMBER_2,l_POSTAL_CODE,l_COUNTRY
FROM HR_LOCATIONS_ALL
WHERE LOCATION_ID = p_insp_location;
SELECT LOCATION_CODE,ADDRESS_LINE_1,ADDRESS_LINE_2,ADDRESS_LINE_3,TOWN_OR_CITY,REGION_1,
REGION_2,REGION_3,TELEPHONE_NUMBER_1,TELEPHONE_NUMBER_2,POSTAL_CODE,COUNTRY
INTO l_LOCATION_CODE,l_ADDRESS_LINE_1,l_ADDRESS_LINE_2,l_ADDRESS_LINE_3,l_TOWN_OR_CITY,l_REGION_1,
l_REGION_2,l_REGION_3,l_TELEPHONE_NUMBER_1,l_TELEPHONE_NUMBER_2,l_POSTAL_CODE,l_COUNTRY
FROM HR_LOCATIONS_ALL
WHERE LOCATION_ID = p_accp_location;
x_msg_data := 'Enter a value for Amount if you have selected a value in Funds Information.' ;
SELECT Nvl(clm_mipr_obligation_type, 'NONE') INTO l_line_obli_type
FROM po_requisition_lines_all
WHERE requisition_header_id = p_header_id AND ROWNUM = 1;
select nvl(clm_mipr_type, 'NONE') into l_mipr_type
FROM po_requisition_headers_all
WHERE requisition_header_id = p_header_id AND ROWNUM = 1;
SELECT Count(1)
INTO l_count
FROM ap_invoice_distributions_all aid,
ap_invoices_all aia,
po_distributions_all pod,
po_line_locations_all poll,
po_headers_all poh
WHERE poh.po_header_id = l_po_header_id
and nvl(poh.authorization_status, 'INCOMPLETE') = 'APPROVED'
and poll.po_header_id = poh.po_header_id
AND nvl(poll.closed_code,'OPEN') <> 'CLOSED FOR INVOICE'
and pod.po_header_id = poll.po_header_id
and aid.po_distribution_id = pod.po_distribution_id
AND aia.invoice_id = aid.invoice_id
AND aia.payment_status_flag <> 'Y';
select check_number, check_date
into l_payment_no, l_payment_date
from
(select pay.check_number, pay.check_date
from ap_checks_all pay, ap_invoice_payments_all invpay
where pay.check_id = invpay.check_id
and exists ( select 1
from ap_invoice_distributions_all invdist , po_distributions_all podist
where podist.po_distribution_id = invdist.po_distribution_id
and invdist.invoice_id = invpay.invoice_id
and podist.po_header_id = l_po_header_id
)
order by pay.check_date desc
)
where rownum = 1;
SELECT decode(nvl(amendment_number,0),0,'N','Y')
INTO l_is_amendment
FROM pon_auction_headers_all
WHERE auction_Header_id = p_auction_header_id;
x_msg_data := 'When the Set Aside Status is ''Set Aside'', you must select a value for the set aside type.';
fnd_message.set_name(d_appln_short_name,'PO_SET_ASIDE_SELECT_TYPE');
x_msg_data := 'When the Set Aside Status is ''Set Aside'' you must select a value for the NAICS ';