[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT site_use_id
INTO l_site_use_id
FROM hz_cust_site_uses_all a, hz_cust_acct_sites_all b
WHERE a.cust_acct_site_id = b.cust_acct_site_id
AND a.cust_acct_site_id = p_address_id
AND a.site_use_code = 'SHIP_TO'
AND a.status = 'A'
AND b.status ='A'; --bug 2752321
SELECT site_use_id
INTO l_site_use_id
FROM hz_cust_site_uses_all a, hz_cust_acct_sites_all b
WHERE a.cust_acct_site_id = b.cust_acct_site_id
AND a.cust_acct_site_id = p_address_id
AND a.site_use_code = 'BILL_TO'
AND a.status = 'A'
AND b.status ='A';--bug 2752321
SELECT cust_account_id
INTO l_sold_to_org_id
FROM hz_cust_acct_sites_all
WHERE cust_acct_site_id = p_address_id
AND status = 'A';
Select /* MOAC_SQL_CHANGE */ a.ece_tp_location_code, c.party_name
Into x_edi_location_code, x_sold_to_name
From hz_cust_acct_sites a, hz_cust_site_uses_all b,
hz_parties c, hz_cust_accounts d
Where a.cust_acct_site_id = b.cust_acct_site_id
And a.cust_account_id = p_sold_to_org_id
And b.site_use_code = 'SOLD_TO'
And b.primary_flag = 'Y'
And b.status = 'A'
AND a.status = 'A' --bug 2752321
And a.cust_account_id = d.cust_account_id
And d.party_id = c.party_id;
Select hl.Location_Code,
hl.Address_Line_1,
hl.Address_Line_2,
hl.Address_Line_3,
hl.Town_Or_City,
hl.Country,
hl.postal_code,
hl.ece_tp_location_code,
hu.name
Into x_location,
x_address1,
x_address2,
x_address3,
x_city,
x_country,
x_postal_code,
x_edi_location_code,
x_customer_name
From hr_all_organization_units hu, hr_locations hl
Where hl.location_id = hu.location_id
And hu.organization_id = p_site_use_id;
Select /* MOAC_SQL_CHANGE */ Site.Location,
Loc.Address1,
Loc.Address2,
Loc.Address3,
Loc.Address4,
Loc.City,
Loc.State,
Loc.Country,
Loc.Postal_Code,
Acct_Site.ece_tp_location_code,
Party.Party_Name
Into x_location,
x_address1,
x_address2,
x_address3,
x_address4,
x_city,
x_state,
x_country,
x_postal_code,
x_edi_location_code,
x_customer_name
From Hz_Cust_Site_Uses Site,
Hz_Party_Sites Party_Site,
Hz_Locations Loc,
Hz_Cust_Acct_Sites_All Acct_Site,
Hz_Parties Party,
Hz_Cust_Accounts Cust_Accts
Where Site.Site_Use_Code = p_site_use_code
And Site.Cust_Acct_Site_Id = Acct_Site.Cust_Acct_Site_Id
And Acct_Site.Party_Site_Id = Party_Site.Party_Site_Id
And Party_Site.Location_Id = Loc.Location_Id
And Site.Site_Use_Id = p_site_use_id
And Acct_Site.Cust_Account_id = Cust_Accts.Cust_Account_id
And Party.Party_Id = Cust_Accts.Party_Id;
Select a.person_first_name, a.person_last_name
Into x_first_name, x_last_name
From hz_parties a, hz_relationships b,
hz_cust_account_roles c
Where c.cust_account_role_id = p_contact_id
And c.party_id = b.party_id
And b.subject_id = a.party_id
And b.subject_table_name = 'HZ_PARTIES'
And b.object_table_name = 'HZ_PARTIES'
And c.cust_account_id = p_cust_acct_id
And b.directional_flag = 'F';
Select hl.ece_tp_location_code
Into x_edi_location_code
From hr_all_organization_units hu, hr_locations hl
Where hl.location_id = hu.location_id
And hu.organization_id = p_ship_from_org_id;
SELECT /*+ INDEX (a,OE_PROCESSING_MSGS_N2)
USE_NL (a b) */
a.order_source_id
, a.original_sys_document_ref
, b.message_text
FROM oe_processing_msgs a, oe_processing_msgs_tl b
WHERE a.request_id = p_request_id
AND a.order_source_id = p_order_source_id
AND a.original_sys_document_ref = p_orig_sys_document_ref
AND (a.org_id is null or a.org_id = p_org_id)
AND a.original_sys_document_line_ref is null
AND a.transaction_id = b.transaction_id
AND b.language = oe_globals.g_lang;
SELECT /*+ INDEX (a,OE_PROCESSING_MSGS_N2)
USE_NL (a b) */
a.order_source_id
, a.original_sys_document_ref
, a.original_sys_document_line_ref
, b.message_text
FROM oe_processing_msgs a, oe_processing_msgs_tl b
WHERE a.request_id = p_request_id
AND a.order_source_id = p_order_source_id
AND a.original_sys_document_ref = p_orig_sys_document_ref
AND a.original_sys_document_line_ref = p_orig_sys_line_ref
AND (a.org_id is null or a.org_id = p_org_id)
AND a.transaction_id = b.transaction_id
AND b.language = oe_globals.g_lang;
select ordered_quantity
into x_ordered_quantity
from oe_order_lines_all
where orig_sys_document_ref = p_orig_sys_document_ref
and orig_sys_line_ref = p_orig_sys_line_ref
and orig_sys_shipment_ref = p_orig_sys_shipment_ref
and order_source_id = p_order_source_id
and decode(l_customer_key_profile, 'Y',
nvl(sold_to_org_id, -999), 1)
= decode(l_customer_key_profile, 'Y',
nvl(p_sold_to_org_id, -999), 1);
select order_quantity_uom
into x_ordered_quantity_uom
from oe_order_lines_all
where orig_sys_document_ref = p_orig_sys_document_ref
and orig_sys_line_ref = p_orig_sys_line_ref
and orig_sys_shipment_ref = p_orig_sys_shipment_ref
and order_source_id = p_order_source_id
and decode(l_customer_key_profile, 'Y',
nvl(sold_to_org_id, -999), 1)
= decode(l_customer_key_profile, 'Y',
nvl(p_sold_to_org_id, -999), 1);
update oe_headers_iface_all
set cancelled_flag='Y'
where orig_sys_document_ref = p_orig_sys_document_ref
and order_source_id = p_order_source_id
and decode(l_customer_key_profile, 'Y',
nvl(sold_to_org_id, -999), 1)
= decode(l_customer_key_profile, 'Y',
nvl(p_sold_to_org_id, -999), 1)
and nvl(change_sequence, ' ')
= nvl(p_change_sequence, ' ')
and xml_transaction_type_code = p_transaction_type
and org_id = p_org_id
and xml_message_id = p_xml_message_id;
Delete from OE_HEADER_ACKS
Where orig_sys_document_ref = p_orig_sys_document_ref
And acknowledgment_type = p_ack_type
And decode(l_customer_key_profile, 'Y',
nvl(sold_to_org_id, -999), 1)
= decode(l_customer_key_profile, 'Y',
nvl(p_sold_to_org_id, -999), 1)
And nvl(change_sequence, ' ')
= nvl(p_change_sequence, ' ')
And request_id = p_request_id;
oe_debug_pub.add( 'DELETED OE_HEADER_ACKS ENTRIES FOR ORIG_SYS_DOCUMENT_REF => ' || P_ORIG_SYS_DOCUMENT_REF ||
' AND ACKNOWLEDGMENT_TYPE => ' || P_ACK_TYPE || ' AND REQUEST_ID => ' || P_REQUEST_ID ||
' AND SOLD_TO_ORG_ID => ' || P_SOLD_TO_ORG_ID ||
' AND CHANGE_SEQUENCE => ' || P_CHANGE_SEQUENCE);
Delete from OE_LINE_ACKS
Where orig_sys_document_ref = p_orig_sys_document_ref
And acknowledgment_type = p_ack_type
And decode(l_customer_key_profile, 'Y',
nvl(sold_to_org_id, -999), 1)
= decode(l_customer_key_profile, 'Y',
nvl(p_sold_to_org_id, -999), 1)
And nvl(change_sequence, ' ')
= nvl(p_change_sequence, ' ')
And request_id = p_request_id;
oe_debug_pub.add( 'DELETED OE_LINE_ACKS ENTRIES FOR ORIG_SYS_DOCUMENT_REF => ' || P_ORIG_SYS_DOCUMENT_REF ||
' AND ACKNOWLEDGMENT_TYPE => ' || P_ACK_TYPE || ' AND REQUEST_ID => ' || P_REQUEST_ID ||
' AND SOLD_TO_ORG_ID => ' || P_SOLD_TO_ORG_ID ||
' AND CHANGE_SEQUENCE => ' || P_CHANGE_SEQUENCE);
Select orig_sys_document_ref
Into l_dummy
From oe_order_lines_all
Where orig_sys_document_ref = p_orig_sys_document_ref
And orig_sys_line_ref = p_orig_sys_line_ref
And orig_sys_shipment_ref = p_orig_sys_shipment_ref
And order_source_id = p_order_source_id
And decode(l_customer_key_profile, 'Y',
nvl(sold_to_org_id, -999), 1)
= decode(l_customer_key_profile, 'Y',
nvl(p_sold_to_org_id, -999), 1)
And org_id = p_org_id;
x_operation_code := 'UPDATE';
x_insert_flag Out Nocopy Varchar2)
IS
BEGIN
x_insert_flag := 'Y';
x_insert_flag := 'N';
x_insert_flag := 'N';
x_insert_flag := 'N';
x_insert_level Out Nocopy Varchar2,
x_raised_event Out Nocopy Varchar2)
IS
l_response_profile varchar2(10) := nvl(FND_PROFILE.VALUE('ONT_3A7_RESPONSE_REQUIRED'),'N');
l_insert_level varchar2(1) := 'Y';
x_insert_level := 'Y';
x_insert_flag => l_insert_level);
IF l_insert_level = 'N' THEN
l_raise_event := 'Y';
IF l_insert_level = 'N'
AND p_event_raised_flag = 'N' THEN
l_raise_event := 'Y';
Select order_number, order_type_id, header_id
Into l_order_number, l_order_type_id, l_header_id
From oe_order_headers_all
Where orig_sys_document_ref = p_orig_sys_document_ref
And order_source_id = 20
And decode(l_customer_key_profile, 'Y',
nvl(sold_to_org_id, -999), 1)
= decode(l_customer_key_profile, 'Y',
nvl(p_sold_to_org_id, -999), 1)
And org_id = p_org_id;
x_insert_level := l_insert_level;