[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
select por.revision_num,poh.vendor_id,poh.vendor_site_id,
poh.po_header_id
into l_revision_num,l_vendor_id ,l_vendor_site_id,l_doc_id
from po_headers_all poh,po_releases_all por
where poh.po_header_id=por.po_header_id
and por.po_release_id = l_doc_rel_id;
select revision_num,vendor_id,vendor_site_id
into l_revision_num,l_vendor_id ,l_vendor_site_id
from po_headers_all
where po_header_id= l_doc_id;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
select segment1, org_id, vendor_id, vendor_site_id, comments, type_lookup_code,creation_date
into l_po_number, l_org_id, l_party_id, l_party_site_id, l_po_desc, l_po_subtype,l_doc_creation_date
from po_headers_all
where po_header_id = l_po_header_id;
select poh.segment1 || ':' || to_char(por.release_num), poh.org_id,
poh.vendor_id, poh.vendor_site_id, poh.comments,poh.creation_date
into l_po_number, l_org_id, l_party_id, l_party_site_id, l_po_desc,
l_doc_creation_date
from po_headers_all poh,po_releases_all por
where poh.po_header_id=por.po_header_id
and por.po_release_id = l_doc_rel_id;
select segment1, org_id, vendor_id, vendor_site_id, comments,creation_date
into l_po_number, l_org_id, l_party_id, l_party_site_id,l_po_desc,
l_doc_creation_date
from po_headers_all
where po_header_id = l_po_header_id;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
select por.revision_num,poh.vendor_id,poh.vendor_site_id,
poh.po_header_id, por.cancel_flag
into l_revision_num,l_vendor_id ,l_vendor_site_id,l_doc_id, l_cancel_flag --B4407795,reading cancel flag
from po_headers_all poh,po_releases_all por
where poh.po_header_id=por.po_header_id
and por.po_release_id = l_doc_rel_id;
select revision_num,vendor_id,vendor_site_id,cancel_flag
into l_revision_num,l_vendor_id ,l_vendor_site_id, l_cancel_flag --B4407795,reading cancel flag
from po_headers_all
where po_header_id= l_doc_id;
select PO_WF_ITEMKEY_S.nextval
into l_wf_item_seq
from dual;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
select por.xml_flag,poh.agent_id into l_xml_flag, l_agent_id
from po_headers_all poh,po_releases_all por
where poh.po_header_id=por.po_header_id
and por.po_release_id = l_doc_rel_id;
select poh.xml_flag, poh.agent_id into l_xml_flag, l_agent_id
from po_headers_all poh
where po_header_id= l_doc_id;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
update po_headers_all
set xml_send_date = sysdate
where
po_header_id = p_header_id and
org_id = p_org_id;
update po_headers_archive_all
set xml_send_date = sysdate
where
po_header_id = p_header_id and
org_id = p_org_id and
revision_num = 0;
update po_headers_all
set xml_change_send_date = sysdate
where
po_header_id = p_header_id and
org_id = p_org_id;
update po_headers_archive_all
set xml_change_send_date = sysdate
where
po_header_id = p_header_id and
org_id = p_org_id and
latest_external_flag = 'Y';
update po_releases_all
set xml_send_date = sysdate
where
po_header_id = p_header_id and
po_release_id = p_release_id and
org_id = p_org_id;
update po_releases_archive_all
set xml_send_date = sysdate
where
po_header_id = p_header_id and
po_release_id = p_release_id and
org_id = p_org_id and
revision_num = 0;
update po_releases_all
set xml_change_send_date = sysdate
where
po_header_id = p_header_id and
po_release_id = p_release_id and
org_id = p_org_id;
update po_releases_archive_all
set xml_change_send_date = sysdate
where
po_header_id = p_header_id and
po_release_id = p_release_id and
org_id = p_org_id and
latest_external_flag = 'Y';
select count(1) into l_count_distinct from (
select distinct(deliver_to_person_id)
from po_distributions_archive_all pda
where pda.po_header_id = p_header_id
and pda.po_line_id = p_line_id
and pda.revision_num = p_revision_num);
select distinct(deliver_to_person_id) into l_agent_id
from po_distributions_archive_all pda
where pda.po_header_id = p_header_id
and pda.po_line_id = p_line_id
and pda.revision_num = p_revision_num;
select full_name into p_requestor from PER_ALL_PEOPLE_F where
person_id = l_agent_id and
effective_end_date >= sysdate;
select count(1) into l_count_distinct from (
select distinct(deliver_to_person_id) from po_distributions_archive_all pda
where pda.po_header_id = p_header_id
and pda.po_line_id = p_line_id
and pda.revision_num = p_revision_num
and pda.po_release_id = p_release_num);
select distinct(deliver_to_person_id) into l_agent_id from po_distributions_archive_all pda
where pda.po_header_id = p_header_id
and pda.po_line_id = p_line_id
and pda.revision_num = p_revision_num
and pda.po_release_id = p_release_num;
select full_name into p_requestor from PER_ALL_PEOPLE_F where
person_id = l_agent_id and
effective_end_date >= sysdate;
select xml_change_send_date, xml_send_date into
l_poco_date, l_pro_date
from po_headers_all
where po_header_id = p_header_id;
select xml_change_send_date, xml_send_date into
l_poco_date, l_pro_date
from po_releases_all
where po_header_id = p_header_id
and po_release_id = p_release_id;
select type_lookup_code into doc_type
from po_headers_all
where po_header_id= p_header_id;
select max(revision_num) into l_one
from po_lines_archive_all
where po_header_id = p_header_id
and po_line_id = p_line_id ;
select max(revision_num) into l_one
from po_lines_archive_all
where po_header_id = p_header_id
and po_line_id = p_line_id
and revision_num <= p_revision_num;
select max(revision_num) into l_line_revision
from po_lines_archive_all
where po_header_id = p_header_id
and po_line_id = p_line_id
and revision_num <= p_revision_num;
select max(revision_num) into l_max_location_revision
from po_line_locations_archive_all
where po_header_id = p_header_id
and po_line_id = p_line_id
and revision_num <= p_revision_num;
select max(revision_num) into l_max_distribution_revision
from po_distributions_archive_all
where po_header_id = p_header_id
and po_line_id = p_line_id
and revision_num <= p_revision_num;
select type_lookup_code into doc_type
from po_headers_all
where po_header_id= p_header_id;
select max(revision_num) into l_one
from po_line_locations_archive_all
where po_header_id = p_header_id
and po_line_id = p_line_id
and line_location_id = p_location_id;
select max(revision_num) into l_one
from po_line_locations_archive_all
where po_header_id = p_header_id
and po_line_id = p_line_id
and line_location_id = p_location_id
and revision_num <= p_revision_num;
select max(revision_num) into l_max_loc_revision
from po_line_locations_archive_all
where po_header_id = p_header_id
and po_line_id = p_line_id
and line_location_id = p_location_id
and revision_num <= p_revision_num;
select max(revision_num) into l_max_dist_revision
from po_distributions_archive_all
where po_header_id = p_header_id
and po_line_id = p_line_id
and line_location_id = p_location_id
and revision_num <= p_revision_num;
select aca.card_number, aca.cardmember_name,aca.card_expiration_date,
acpa.card_brand_lookup_code
into p_card_num, p_card_name, p_card_exp_date, p_card_brand
from ap_cards_all aca, ap_card_programs_all acpa, po_headers_all pha
where pha.po_header_id = p_header_id
and pha.pcard_id = aca.card_id
and aca.card_program_id = acpa.card_program_id;
select aca.card_number, aca.cardmember_name,aca.card_expiration_date,
acpa.card_brand_lookup_code
into p_card_num, p_card_name, p_card_exp_date, p_card_brand
from ap_cards_all aca, ap_card_programs_all acpa, po_releases_all pra
where pra.po_header_id = p_header_id
and pra.po_release_id = p_release_id
and pra.pcard_id = aca.card_id
and aca.card_program_id = acpa.card_program_id;
select count(1)
into is_supplier_pcard
from ap_card_suppliers_all acsa, po_headers_all pha
where acsa.card_id = pha.pcard_id
and po_header_id = p_header_id;
select count(1)
into is_supplier_pcard
from ap_card_suppliers_all acsa, po_releases_all pra
where acsa.card_id = pra.pcard_id
and pra.po_header_id = p_header_id
and pra.po_release_id = p_release_id;
select pva.vendor_name into p_card_name
from po_vendors pva, po_headers_all pha
where pha.po_header_id = p_header_id and
pva.vendor_id = pha.vendor_id;
select count(*) into cnt
from OE_DROP_SHIP_SOURCES
where po_header_id = p_header_id and
line_location_id = p_line_location_id;
select null, HZA.ADDRESS1, HZA.ADDRESS2,
HZA.ADDRESS3, HZA.CITY, HZA.COUNTRY,
HZA.POSTAL_CODE, HZA.STATE,
null, --HZA.TELEPHONE_NUMBER_1,
null, --HZA.TELEPHONE_NUMBER_2,
null -- HZA.TELEPHONE_NUMBER_3
into
p_ECE_TP_LOCATION_CODE, p_ADDRESS_LINE_1, p_ADDRESS_LINE_2,
p_ADDRESS_LINE_3, p_TOWN_OR_CITY, p_COUNTRY,
p_POSTAL_CODE, p_STATE, p_TELEPHONE_NUMBER_1,
p_TELEPHONE_NUMBER_2, p_TELEPHONE_NUMBER_3
from HZ_LOCATIONS HZA
where HZA.LOCATION_ID = p_ship_to_location_id;
select HLA.ECE_TP_LOCATION_CODE, HLA.ADDRESS_LINE_1, HLA.ADDRESS_LINE_2,
HLA.ADDRESS_LINE_3, HLA.TOWN_OR_CITY, HLA.COUNTRY,
HLA.POSTAL_CODE, HLA.REGION_2, HLA.TELEPHONE_NUMBER_1,
HLA.TELEPHONE_NUMBER_2, HLA.TELEPHONE_NUMBER_3
into
p_ECE_TP_LOCATION_CODE, p_ADDRESS_LINE_1, p_ADDRESS_LINE_2,
p_ADDRESS_LINE_3, p_TOWN_OR_CITY, p_COUNTRY,
p_POSTAL_CODE, p_STATE, p_TELEPHONE_NUMBER_1,
p_TELEPHONE_NUMBER_2, p_TELEPHONE_NUMBER_3
from HR_LOCATIONS_ALL HLA
where HLA.LOCATION_ID = p_ship_to_location_id;
select count(*) into cnt
from OE_DROP_SHIP_SOURCES
where po_header_id = p_header_id and
line_location_id = p_line_location_id;
select null, HZA.ADDRESS1, HZA.ADDRESS2,
HZA.ADDRESS3, HZA.CITY, HZA.COUNTRY,
HZA.POSTAL_CODE, HZA.STATE,
null, --HZA.TELEPHONE_NUMBER_1,
null, --HZA.TELEPHONE_NUMBER_2,
null -- HZA.TELEPHONE_NUMBER_3
into
p_ECE_TP_LOCATION_CODE, p_ADDRESS_LINE_1, p_ADDRESS_LINE_2,
p_ADDRESS_LINE_3, p_TOWN_OR_CITY, p_COUNTRY,
p_POSTAL_CODE, p_STATE, p_TELEPHONE_NUMBER_1,
p_TELEPHONE_NUMBER_2, p_TELEPHONE_NUMBER_3
from HZ_LOCATIONS HZA
where HZA.LOCATION_ID = p_ship_to_location_id;
select HLA.ECE_TP_LOCATION_CODE, HLA.ADDRESS_LINE_1, HLA.ADDRESS_LINE_2,
HLA.ADDRESS_LINE_3, HLA.TOWN_OR_CITY, HLA.COUNTRY,
HLA.POSTAL_CODE, HLA.REGION_2, HLA.TELEPHONE_NUMBER_1,
HLA.TELEPHONE_NUMBER_2, HLA.TELEPHONE_NUMBER_3
into
p_ECE_TP_LOCATION_CODE, p_ADDRESS_LINE_1, p_ADDRESS_LINE_2,
p_ADDRESS_LINE_3, p_TOWN_OR_CITY, p_COUNTRY,
p_POSTAL_CODE, p_STATE, p_TELEPHONE_NUMBER_1,
p_TELEPHONE_NUMBER_2, p_TELEPHONE_NUMBER_3
from HR_LOCATIONS_ALL HLA
where HLA.LOCATION_ID = p_ship_to_location_id;
select PO_WF_ITEMKEY_S.nextval
into l_wf_item_seq
from dual;
PO_REQAPPROVAL_INIT1.update_print_count(l_doc_id,l_document_type);
select PHA.SEGMENT1, PRAA.REVISION_NUM,
PRAA.RELEASE_NUM
into l_ponum, l_revision_num, l_release_num
from PO_RELEASES_ARCHIVE_ALL praa, po_headers_all pha
where PHA.PO_HEADER_ID = PRAA.PO_HEADER_ID and
praa.po_release_id = l_release_id and
praa.revision_num = l_revision_num;
select segment1 into l_ponum
from po_headers_archive_all poh
where po_header_id= l_document_id and
revision_num = l_revision_num;
select nvl(pvsa.language, 'AMERICAN') into lang_name
from po_vendor_sites_all pvsa
where vendor_id = p_vendor_id and
vendor_site_id = p_vendor_site_id;
select etd.username, etd.source_tp_location_code
into x_to_domain, x_to_identity
from ecx_tp_details etd, ecx_tp_headers eth, ecx_ext_processes eep
where eth.party_id = p_tp_id and eth.party_site_id = p_tp_site_id
and etd.tp_header_id = eth.tp_header_id and
eep.ext_type = 'ORDER' and eep.ext_subtype = 'REQUEST' and
eep.ext_process_id = etd.ext_process_id;
select distinct(request_origin) into src
from po_change_requests
where change_request_group_id = l_change_request_group_id
and msg_cont_num is not null;
SELECT *
INTO hrloc_rec
FROM hr_locations_all
WHERE location_id = p_location_id;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress
|| ':' || l_user_id || ':' || l_resp_id || ':' || l_appl_id);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress
|| ':' || l_cur_user_id || ':' || l_cur_resp_id
|| ':' || l_cur_appl_id);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
select ship_to_location_id, org_id
into l_location_id, x_partner_id
from po_headers_all
where po_header_id = p_po_header_id;
select distinct
-- hrl.description,
hrl.address_line_1,
hrl.address_line_2,
hrl.address_line_3,
hrl.town_or_city,
hrl.postal_code,
--ftv.territory_short_name,
hrl.country,
nvl(decode(hrl.region_1,
null, hrl.region_2,
decode(flv1.meaning,null, decode(flv2.meaning,null,flv3.meaning,flv2.lookup_code),flv1.lookup_code))
,hrl.region_2),
hrl.TELEPHONE_NUMBER_1,
hrl.TELEPHONE_NUMBER_2,
hrl.TELEPHONE_NUMBER_3,
hrl.ECE_TP_LOCATION_CODE
into
-- l_ship_to_desc,
x_address_line_1,
x_address_line_2,
x_address_line_3,
x_city,
x_postalcode,
x_country,
x_stateprovn,
x_telephone_1,
x_telephone_2,
x_telephone_3,
x_partner_id_x
FROM hr_locations_all hrl,
--fnd_territories_vl ftv,
fnd_lookup_values_vl flv1,
fnd_lookup_values_vl flv2,
fnd_lookup_values_vl flv3
where
hrl.region_1 = flv1.lookup_code (+) and hrl.country || '_PROVINCE' = flv1.lookup_type (+)
and hrl.region_2 = flv2.lookup_code (+) and hrl.country || '_STATE' = flv2.lookup_type (+)
and hrl.region_1 = flv3.lookup_code (+) and hrl.country || '_COUNTY' = flv3.lookup_type (+)
--and hrl.country = ftv.territory_code(+)
and HRL.location_id = l_location_id;
select distinct
-- hrl.description,
hzl.address1,
hzl.address2,
hzl.address3,
hzl.city,
hzl.postal_code,
hzl.country,
hzl.state
into
-- l_ship_to_desc,
x_address_line_1,
x_address_line_2,
x_address_line_3,
x_city,
x_postalcode,
x_country,
x_stateprovn
FROM hz_locations hzl
where HzL.location_id = l_location_id;
SELECT REQUESTOR
FROM PO_CXML_DELIVERTO_ARCH_V
WHERE PO_HEADER_ID = headerid
AND ((PO_RELEASE_ID is null AND releaseid is null)
OR PO_RELEASE_ID = releaseid
);
select count(*)
into l_num_enterprises
from hz_parties hp, hz_code_assignments hca
where hca.owner_table_id = hp.party_id
and hca.owner_table_name = 'HZ_PARTIES'
and hca.class_category = 'POS_PARTICIPANT_TYPE'
and hca.class_code = 'ENTERPRISE'
and hca.status= 'A'
and hp.status= 'A'
and ( hca.end_date_active > sysdate or hca.end_date_active is null );
select hp.party_name
into x_shipto_name
from hz_parties hp, hz_code_assignments hca
where hca.owner_table_id = hp.party_id
and hca.owner_table_name = 'HZ_PARTIES'
and hca.class_category = 'POS_PARTICIPANT_TYPE'
and hca.class_code = 'ENTERPRISE'
and hca.status= 'A'
and hp.status= 'A'
and ( hca.end_date_active > sysdate or hca.end_date_active is null );