[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT OE_XML_MESSAGE_SEQ_S.NEXTVAL
INTO l_em_message_id
FROM DUAL;
Select order_source_id, orig_sys_document_ref, request_id
into l_order_source_id, l_orig_sys_document_ref, l_request_id
From oe_header_acks
Where header_id = p_header_id
And first_ack_date is null
And rownum = 1;
Delete oe_lines_interface
Where order_source_id = l_order_source_id
And orig_sys_document_ref = l_orig_sys_document_ref
And request_id = l_request_id
And rejected_flag = 'Y';
Delete oe_headers_interface
Where order_source_id = l_order_source_id
And orig_sys_document_ref = l_orig_sys_document_ref
And request_id = l_request_id;
SELECT header_id
INTO l_header_id
FROM oe_order_headers_all
WHERE header_id = p_header_id
FOR UPDATE nowait;
UPDATE oe_order_headers_all
SET first_ack_code = l_ack_code
, first_ack_date = sysdate
, lock_control = lock_control + 1
, xml_message_id = l_em_message_id
WHERE header_id = p_header_id;
UPDATE oe_header_acks
SET acknowledgment_flag = 'Y'
, first_ack_date = sysdate
WHERE header_id = p_header_id
AND first_ack_date is null;
END Update_Header_Ack_first;
PROCEDURE Update_Header_Ack_Last(
p_header_id IN NUMBER
,p_ack_code IN VARCHAR2
) is
l_api_name CONSTANT VARCHAR2(30) := 'Update_Header_Ack_Last';
SELECT OE_XML_MESSAGE_SEQ_S.NEXTVAL
INTO l_em_message_id
FROM DUAL;
select last_ack_code
into l_ack_code
from oe_header_acks
where header_id = p_header_id
and first_ack_date is not null
and last_ack_code is null
and rownum = 1;
SELECT header_id
INTO l_header_id
FROM oe_order_headers_all
WHERE header_id = p_header_id
FOR UPDATE nowait;
UPDATE oe_order_headers_all
SET last_ack_code = l_ack_code
, last_ack_date = sysdate
, lock_control = lock_control + 1
, xml_message_id = l_em_message_id
WHERE header_id = p_header_id;
UPDATE oe_header_acks
SET acknowledgment_flag = 'Y'
, last_ack_date = sysdate
WHERE header_id = p_header_id
AND first_ack_date is not null
AND last_ack_date is null;
END Update_Header_Ack_last;
PROCEDURE Update_Line_Ack_First(
p_header_id IN NUMBER
,p_line_id IN NUMBER
,p_ack_code IN VARCHAR2
) is
l_api_name CONSTANT VARCHAR2(30) := 'Update_Line_Ack_First';
select first_ack_code
into l_ack_code
from oe_line_acks
where header_id = p_header_id
and line_id = p_line_id
and (first_ack_date is null
or first_ack_date = l_first_ack_date)
and rownum = 1;
SELECT header_id
INTO l_header_id
FROM oe_order_lines_all
WHERE line_id = p_line_id
FOR UPDATE nowait;
UPDATE oe_order_lines_all
SET first_ack_code = l_ack_code
, first_ack_date = sysdate
, lock_control = lock_control + 1
WHERE line_id = p_line_id;
UPDATE oe_line_acks
SET acknowledgment_flag = 'Y'
, first_ack_date = sysdate
WHERE header_id = p_header_id
AND line_id = p_line_id
AND (first_ack_date is null
OR first_ack_date = l_first_ack_date);
UPDATE oe_line_acks
SET acknowledgment_flag = 'Y'
, first_ack_date = sysdate
WHERE header_id = p_header_id
AND line_id is null
AND (first_ack_date is null
OR first_ack_date = l_first_ack_date);
END Update_Line_Ack_first;
PROCEDURE Update_Line_Ack_Last(
p_header_id IN NUMBER
,p_line_id IN NUMBER
,p_ack_code IN VARCHAR2
) is
l_api_name CONSTANT VARCHAR2(30) := 'Update_Line_Ack_Last';
select last_ack_code
into l_ack_code
from oe_line_acks
where header_id = p_header_id
and line_id = p_line_id
and first_ack_date is not null
and last_ack_date is null
and rownum = 1;
SELECT header_id
INTO l_header_id
FROM oe_order_lines_all
WHERE line_id = p_line_id
FOR UPDATE nowait;
UPDATE oe_order_lines_all
SET last_ack_code = l_ack_code
, last_ack_date = sysdate
, lock_control = lock_control + 1
, first_ack_code = nvl(first_ack_code, l_ack_code)
, first_ack_date = nvl(first_ack_date,sysdate)
WHERE line_id = p_line_id;
UPDATE oe_line_acks
SET acknowledgment_flag = 'Y'
, last_ack_date = sysdate
WHERE header_id = p_header_id
AND line_id = p_line_id
AND first_ack_date is not null
AND last_ack_date is null;
END Update_Line_Ack_Last;
Select Header_Id,
Org_Id,
Sold_To_Org_Id,
Ship_To_Org_Id,
Invoice_To_Org_Id,
Sold_To_Contact_Id,
Ship_To_Contact_Id,
Ship_From_Org_Id,
Order_Type_Id,
Price_List_Id,
Payment_Term_Id,
Salesrep_Id,
Fob_Point_Code,
Freight_Terms_Code,
Agreement_Id,
Conversion_Type_Code,
Tax_Exempt_Reason_Code,
Tax_Point_Code,
Invoicing_Rule_Id,
End_Customer_Id,
End_Customer_Contact_Id,
End_Customer_Site_Use_Id
From Oe_Header_Acks
Where Header_Id = p_header_id
And Acknowledgment_Flag Is Null
For Update;
Select line_id,
ship_to_org_id,
invoice_to_org_id,
invoice_to_contact_id,
ship_from_org_id,
agreement_id,
price_list_id,
arrival_set_id,
accounting_rule_id,
fob_point_code,
freight_terms_code,
fulfillment_set_id,
inventory_item_id,
invoice_set_id,
invoicing_rule_id,
line_type_id,
order_source_id,
payment_term_id,
project_id,
salesrep_id,
ship_set_id,
ship_to_contact_id,
shipping_method_code,
task_id,
tax_code,
tax_exempt_reason_code,
tax_point_code,
line_type,
ship_to_address1,
ship_to_address2,
ship_to_address3,
ship_to_address4,
ship_to_country,
ship_to_state,
ship_to_postal_code,
ship_to_city,
ship_to_address_code,
ship_to_edi_location_code,
ship_to_org,
ship_from_address_1,
ship_from_address_2,
ship_from_address_3,
ship_from_city,
ship_from_postal_code,
ship_from_country,
ship_from_org,
ship_from_edi_location_code,
invoice_to_org,
invoice_city,
invoice_address_code,
agreement,
price_list,
arrival_set_name,
accounting_rule,
fob_point,
freight_terms,
fulfillment_set_name,
inventory_item,
invoice_set_name,
invoicing_rule,
payment_term,
project,
salesrep,
ship_set_name,
ship_to_contact,
ship_to_contact_first_name,
ship_to_contact_last_name,
shipping_method,
fob_point_code,
freight_terms_code,
shipping_method_code,
tax_code,
tax_point_code,
tax_exempt_reason_code,
task,
error_flag,
End_Customer_Id,
End_Customer_Contact_Id,
End_Customer_Site_Use_Id,
End_Customer_Name,
End_Customer_Number,
End_Customer_Contact,
End_Customer_Address1,
End_Customer_Address2,
End_Customer_Address3,
End_Customer_Address4,
End_Customer_City,
End_Customer_State,
End_Customer_Postal_Code,
End_Customer_Country
From Oe_Line_Acks
Where Header_id = p_header_id
And Acknowledgment_Flag Is Null
For Update;
l_line_acks_rec OE_Update_Ack_Util.Line_Rec_Type;
Oe_Debug_Pub.Add('Entering OE_Update_Ack_Util.write_ack_data_values');
Select /* MOAC_SQL_CHANGE*/ b.site_use_id
Into l_sold_to_site_use_id
From hz_cust_acct_sites a, hz_cust_site_uses_all b
Where a.cust_acct_site_id = b.cust_acct_site_id
And a.cust_account_id = l_header_acks_rec.sold_to_org_id
And b.org_id = a.org_id
And b.site_use_code = 'SOLD_TO'
And b.primary_flag = 'Y'
And b.status = 'A';
Update Oe_Line_Acks
Set line_type = l_line_acks_rec.line_type(i),
price_list = l_line_acks_rec.price_list(i),
salesrep = l_line_acks_rec.salesrep(i),
fob_point = l_line_acks_rec.fob_point(i),
freight_terms = l_line_acks_rec.freight_terms(i),
Agreement = l_line_acks_rec.Agreement(i),
payment_term = l_line_acks_rec.payment_term(i),
ship_to_address1 = l_line_acks_rec.ship_to_address1(i),
ship_to_address2 = l_line_acks_rec.ship_to_address2(i),
ship_to_address3 = l_line_acks_rec.ship_to_address3(i),
ship_to_address4 = l_line_acks_rec.ship_to_address4(i),
ship_to_city = l_line_acks_rec.ship_to_city(i),
ship_to_state = l_line_acks_rec.ship_to_state(i),
ship_to_country = l_line_acks_rec.ship_to_country(i),
ship_to_postal_code = l_line_acks_rec.ship_to_postal_code(i),
ship_to_edi_location_code = l_line_acks_rec.ship_to_edi_location_code(i),
ship_to_address_code = l_line_acks_rec.ship_to_address_code(i),
ship_to_contact_first_name = l_line_acks_rec.ship_to_contact_first_name(i),
ship_to_contact_last_name = l_line_acks_rec.ship_to_contact_last_name(i),
invoice_to_org = l_line_acks_rec.invoice_to_org(i),
invoice_city = l_line_acks_rec.invoice_city(i) ,
end_customer_name = l_line_acks_rec.end_customer_name(i),
end_customer_number = l_line_acks_rec.end_customer_number(i),
end_customer_contact = l_line_acks_rec.end_customer_contact(i),
end_customer_address1 = l_line_acks_rec.end_customer_address1(i),
end_customer_address2 = l_line_acks_rec.end_customer_address2(i),
end_customer_address3 = l_line_acks_rec.end_customer_address3(i),
end_customer_address4 = l_line_acks_rec.end_customer_address4(i),
end_customer_city = l_line_acks_rec.end_customer_city(i),
end_customer_state = l_line_acks_rec.end_customer_state(i),
end_customer_postal_code = l_line_acks_rec.end_customer_postal_code(i),
end_customer_country = l_line_acks_rec.end_customer_country(i)
Where Header_Id = p_header_id
And Acknowledgment_flag Is Null;
Update Oe_Header_Acks
Set sold_to_address1 = l_header_acks_rec.sold_to_address1,
sold_to_address2 = l_header_acks_rec.sold_to_address2,
sold_to_address3 = l_header_acks_rec.sold_to_address3,
sold_to_address4 = l_header_acks_rec.sold_to_address4,
sold_to_city = l_header_acks_rec.sold_to_city,
sold_to_state = l_header_acks_rec.sold_to_state,
sold_to_country = l_header_acks_rec.sold_to_country,
sold_to_postal_code = l_header_acks_rec.sold_to_postal_code,
sold_to_edi_location_code = l_header_acks_rec.sold_to_edi_location_code,
sold_to_org = l_header_acks_rec.sold_to_org,
sold_to_contact_first_name = l_header_acks_rec.sold_to_contact_first_name,
sold_to_contact_last_name = l_header_acks_rec.sold_to_contact_last_name,
ship_to_address_code = l_header_acks_rec.ship_to_address_code,
ship_to_address_1 = l_header_acks_rec.ship_to_address_1,
ship_to_address_2 = l_header_acks_rec.ship_to_address_2,
ship_to_address_3 = l_header_acks_rec.ship_to_address_3,
ship_to_address_4 = l_header_acks_rec.ship_to_address_4,
ship_to_city = l_header_acks_rec.ship_to_city,
ship_to_state = l_header_acks_rec.ship_to_state,
ship_to_country = l_header_acks_rec.ship_to_country,
ship_to_postal_code = l_header_acks_rec.ship_to_postal_code,
ship_to_edi_location_code = l_header_acks_rec.ship_to_edi_location_code,
ship_to_org = l_header_acks_rec.ship_to_org,
ship_to_contact_first_name = l_header_acks_rec.ship_to_contact_first_name,
ship_to_contact_last_name = l_header_acks_rec.ship_to_contact_last_name,
invoice_address_1 = l_header_acks_rec.invoice_address_1,
invoice_address_2 = l_header_acks_rec.invoice_address_2,
invoice_address_3 = l_header_acks_rec.invoice_address_3,
invoice_address_4 = l_header_acks_rec.invoice_address_4,
invoice_city = l_header_acks_rec.invoice_city,
invoice_state = l_header_acks_rec.invoice_state,
invoice_country = l_header_acks_rec.invoice_country,
invoice_postal_code = l_header_acks_rec.invoice_postal_code,
bill_to_edi_location_code = l_header_acks_rec.bill_to_edi_location_code,
invoice_to_org = l_header_acks_rec.invoice_to_org,
invoice_to_contact_first_name = l_header_acks_rec.invoice_to_contact_first_name,
invoice_to_contact_last_name = l_header_acks_rec.invoice_to_contact_last_name,
ship_from_address_1 = l_header_acks_rec.ship_from_address_1,
ship_from_address_2 = l_header_acks_rec.ship_from_address_2,
ship_from_address_3 = l_header_acks_rec.ship_from_address_3,
ship_from_city = l_header_acks_rec.ship_from_city,
ship_from_country = l_header_acks_rec.ship_from_country,
ship_from_postal_code = l_header_acks_rec.ship_from_postal_code,
ship_from_edi_location_code = l_header_acks_rec.ship_from_edi_location_code,
ship_from_org = l_header_acks_rec.ship_from_org,
order_type = l_header_acks_rec.order_type,
price_list = l_header_acks_rec.price_list,
payment_term = l_header_acks_rec.payment_term,
salesrep = l_header_acks_rec.salesrep,
fob_point = l_header_acks_rec.fob_point,
freight_terms = l_header_acks_rec.freight_terms,
agreement = l_header_acks_rec.agreement,
conversion_type = l_header_acks_rec.conversion_type,
tax_exempt_reason = l_header_acks_rec.tax_exempt_reason,
tax_point = l_header_acks_rec.tax_point,
invoicing_rule = l_header_acks_rec.invoicing_rule ,
end_customer_name = l_header_acks_rec.end_customer_name,
end_customer_number = l_header_acks_rec.end_customer_number,
end_customer_contact = l_header_acks_rec.end_customer_contact,
end_customer_address1 = l_header_acks_rec.end_customer_address1,
end_customer_address2 = l_header_acks_rec.end_customer_address2,
end_customer_address3 = l_header_acks_rec.end_customer_address3,
end_customer_address4 = l_header_acks_rec.end_customer_address4,
end_customer_city = l_header_acks_rec.end_customer_city,
end_customer_state = l_header_acks_rec.end_customer_state,
end_customer_postal_code = l_header_acks_rec.end_customer_postal_code,
end_customer_country = l_header_acks_rec.end_customer_country,
ship_to_customer = l_header_acks_rec.ship_to_customer,
invoice_customer = l_header_acks_rec.invoice_customer
Where Header_Id = p_header_id
And Acknowledgment_flag Is Null;
Oe_Debug_Pub.Add('Exiting OE_Update_Ack_Util.write_ack_data_values');
WF_CORE.Context('OE_UPDATE_ACK_UTIL', 'DERIVE_ACK_VALUES',
p_itemtype, p_itemkey, p_actid, p_funcmode);
Select Oe_Xml_Message_Seq_S.nextval
Into l_itemkey
From dual;
l_parameter_list.DELETE;
Procedure Oe_Edi_Selector
( p_itemtype in varchar2,
p_itemkey in varchar2,
p_actid in number,
p_funcmode in varchar2,
p_x_result in out Nocopy varchar2
)
Is
l_debug_level Constant Number := oe_debug_pub.g_debug_level;
End Oe_Edi_Selector;