[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
blanket_number,
blanket_line_number
FROM
OE_ORDER_LINES_ALL
WHERE
top_model_line_id = p_top_model_line_id;
SELECT booked_flag INTO l_booked_flag
FROM oe_order_headers
WHERE header_id = l_header_rec.header_id;
SELECT /*MOAC_SQL_CHANGES*/ b.site_use_id, a.cust_acct_site_id
INTO l_site_use_id, l_address_id
FROM hz_cust_site_uses b, hz_cust_acct_sites_all a
WHERE a.cust_acct_site_id = b.cust_acct_site_id
AND a.cust_account_id = l_header_rec.sold_to_org_id
AND b.site_use_code = 'SOLD_TO'
AND b.primary_flag = 'Y'
AND b.status = 'A'
AND b.org_id=a.org_id
AND a.status = 'A';
l_old_line_tbl(I).operation = Oe_Globals.G_OPR_INSERT OR
l_old_line_tbl(I).operation = Oe_Globals.G_OPR_CREATE
)
OR ( l_line_tbl(I).first_ack_code is null AND
l_header_rec.first_ack_code is not null )--bug7207426
THEN
-- Set local variable to continue Acknowledgment processing
-- And Exit from Loop
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'LINE DATA HAS CHANGED' ) ;
SELECT b.cust_acct_site_id, a.ece_tp_location_code,b.location
INTO l_ship_to_addr_id, l_ship_to_location_code,l_ship_to_addr_code
FROM hz_cust_acct_sites_all a, hz_cust_site_uses_all b
WHERE a.cust_acct_site_id = b.cust_acct_site_id
AND b.site_use_id = l_line_tbl(I).ship_to_org_id
AND b.site_use_code='SHIP_TO';
SELECT a.person_last_name, a.person_first_name
INTO l_line_val_tbl(I).ship_to_contact_last_name,
l_line_val_tbl(I).ship_to_contact_first_name
FROM hz_parties a, hz_relationships b, hz_cust_account_roles c
WHERE c.cust_account_role_id = l_header_rec.sold_to_contact_id
AND c.party_id=b.party_id
AND b.subject_id=a.party_id
AND c.CUST_ACCOUNT_ID = l_header_rec.sold_to_org_id
AND b.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND b.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND b.DIRECTIONAL_FLAG = 'F';
SELECT concatenated_segments
INTO l_concatenated_segments
FROM mtl_system_items_vl
WHERE inventory_item_id = l_line_tbl(I).inventory_item_id
AND organization_id = l_validation_org_id;
select hu.location_id,hl.ece_tp_location_code, hl.location_code
into l_ship_from_addr_id, l_ship_from_location_code,l_ship_from_addr_code
from hr_all_organization_units hu,
hr_locations hl
where hl.location_id = hu.location_id
AND hu.organization_id = l_line_tbl(I).ship_from_org_id;
select hu.location_id,hl.ece_tp_location_code, hl.location_code
into l_ship_from_addr_id, l_ship_from_location_code,l_ship_from_addr_code
from hr_all_organization_units hu,
hr_locations hl
where hl.location_id = hu.location_id
AND hu.organization_id = l_header_rec.ship_from_org_id;
SELECT b.cust_acct_site_id, a.ece_tp_location_code,b.location
INTO l_bill_to_addr_id, l_bill_to_location_code,l_bill_to_addr_code
FROM hz_cust_acct_sites_all a, hz_cust_site_uses_all b
WHERE a.cust_acct_site_id = b.cust_acct_site_id
AND b.site_use_id = l_header_rec.invoice_to_org_id
AND b.site_use_code='BILL_TO';
SELECT b.cust_acct_site_id, a.ece_tp_location_code,b.location
INTO l_ship_to_addr_id, l_ship_to_location_code,l_ship_to_addr_code
FROM hz_cust_acct_sites_all a, hz_cust_site_uses_all b
WHERE a.cust_acct_site_id = b.cust_acct_site_id
AND b.site_use_id = l_header_rec.ship_to_org_id
AND b.site_use_code='SHIP_TO';
SELECT b.cust_acct_site_id, a.ece_tp_location_code,b.location
INTO l_sold_to_addr_id, l_sold_to_location_code,l_sold_to_addr_code
FROM hz_cust_acct_sites_all a, hz_cust_site_uses_all b
WHERE a.cust_acct_site_id = b.cust_acct_site_id
AND b.site_use_id = l_header_rec.sold_to_org_id
AND b.site_use_code='SOLD_TO'
AND a.cust_account_id = l_header_rec.sold_to_org_id;
SELECT a.person_last_name, a.person_first_name
INTO l_header_val_rec.sold_to_contact_last_name, l_header_val_rec.sold_to_contact_first_name
FROM hz_parties a, hz_relationships b, hz_cust_account_roles c
WHERE c.cust_account_role_id = l_header_rec.sold_to_contact_id
AND c.party_id=b.party_id
AND b.subject_id=a.party_id
AND c.CUST_ACCOUNT_ID = l_header_rec.sold_to_org_id
AND b.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND b.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND b.DIRECTIONAL_FLAG = 'F';
SELECT a.person_last_name, a.person_first_name
INTO l_header_val_rec.ship_to_contact_last_name, l_header_val_rec.ship_to_contact_first_name
FROM hz_parties a, hz_relationships b, hz_cust_account_roles c
WHERE c.cust_account_role_id = l_header_rec.ship_to_contact_id
AND c.party_id=b.party_id
AND b.subject_id=a.party_id
AND c.CUST_ACCOUNT_ID = l_header_rec.sold_to_org_id
AND b.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND b.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND b.DIRECTIONAL_FLAG = 'F';
SELECT a.person_last_name, a.person_first_name
INTO l_header_val_rec.invoice_to_contact_last_name, l_header_val_rec.invoice_to_contact_first_name
FROM hz_parties a, hz_relationships b, hz_cust_account_roles c
WHERE c.cust_account_role_id = l_header_rec.invoice_to_contact_id
AND c.party_id=b.party_id
AND b.subject_id=a.party_id
AND c.CUST_ACCOUNT_ID = l_header_rec.sold_to_org_id
AND b.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND b.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND b.DIRECTIONAL_FLAG = 'F';
acknowledgment records, start inserting the records based on
the l_ack_req_flag value.
-------------------------------------------------------------
*/
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'L_ACK_REQ_FLAG = '||L_ACK_REQ_FLAG , 3 ) ;
oe_debug_pub.add( 'BEFORE INSERTING HEADER ACKNOWLEDGMENT RECORD' , 3 ) ;
OE_Header_Ack_Util.Insert_Row
( p_header_rec => l_header_rec
, p_header_val_rec => l_header_val_rec
, p_old_header_rec => l_old_header_rec
, p_old_header_val_rec => l_old_header_val_rec
, p_reject_order => p_reject_order
, x_return_status => l_return_status
);
oe_debug_pub.add( 'BEFORE INSERTING LINE ACKNOWLEDGMENT RECORD' , 3 ) ;
OE_Line_Ack_Util.Insert_Row
(p_line_tbl => l_line_tbl
, p_line_val_tbl => l_line_val_tbl
, p_old_line_tbl => l_old_line_tbl
, p_old_line_val_tbl => l_old_line_val_tbl
, p_reject_order => p_reject_order
, x_return_status => l_return_status
);
oe_debug_pub.add( 'BEFORE INSERTING LINE LOTSERIAL ACKNOWLEDGMENT RECORD' , 3 ) ;
OE_Lots_Ack_Util.Insert_Row
( p_lot_serial_tbl => l_lot_serial_tbl
, p_old_lot_serial_tbl => l_old_lot_serial_tbl
, p_lot_serial_val_tbl => l_lot_serial_val_tbl
, p_old_lot_serial_val_tbl => l_old_lot_serial_val_tbl
, p_line_tbl => l_line_tbl
, p_old_line_tbl => l_old_line_tbl
, p_reject_order => p_reject_order
, x_return_status => l_return_status
);
-- Insert rejected lines and lotserials only if got any rejected records
IF l_reject_line_tbl.COUNT > 0 THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'BEFORE INSERTING REJECTED LINES' , 3 ) ;
OE_Line_Ack_Util.Insert_Row
(p_line_tbl => l_reject_line_tbl
,p_old_line_tbl => l_reject_line_tbl
,p_line_val_tbl => l_reject_line_val_tbl
,p_old_line_val_tbl => l_reject_line_val_tbl
,p_buyer_seller_flag => 'B'
,p_reject_order => l_create_rejects
,x_return_status => l_return_status
);
oe_debug_pub.add( 'BEFORE INSERTING REJECTED LINE LOTSERIALS' , 3 ) ;
OE_Lots_Ack_Util.Insert_Row
(p_lot_serial_tbl => l_reject_lot_serial_tbl
,p_lot_serial_val_tbl => l_reject_lot_serial_val_tbl
,p_old_lot_serial_tbl => l_reject_lot_serial_tbl
,p_old_lot_serial_val_tbl => l_reject_lot_serial_val_tbl
,p_line_tbl => l_reject_line_tbl
,p_old_line_tbl => l_reject_line_tbl
,p_reject_order => l_create_rejects
,x_return_status => l_return_status
);
Select booked_flag,xml_message_id
Into l_booked_flag,l_xml_message_id
From oe_order_headers
Where header_id = l_header_rec.header_id;
Select Oe_Xml_Message_Seq_S.nextval
Into l_xml_message_id
From dual;
Oe_Debug_Pub.Add('before select of sold to org id');
Select /*MOAC_SQL_CHANGES*/ b.site_use_id, a.cust_acct_site_id
Into l_site_use_id, l_address_id
From hz_cust_site_uses b, hz_cust_acct_sites_all a
Where a.cust_acct_site_id = b.cust_acct_site_id
And a.cust_account_id = l_header_rec.sold_to_org_id
And b.site_use_code = 'SOLD_TO'
And b.primary_flag = 'Y'
And b.status = 'A'
And a.org_id=b.org_id
And a.status = 'A';
l_old_line_tbl(I).operation = Oe_Globals.G_OPR_INSERT OR
l_old_line_tbl(I).operation = Oe_Globals.G_OPR_CREATE
)
OR ( l_line_tbl(I).first_ack_code is null AND
l_header_rec.first_ack_code is not null )--bug7207426
Then
l_ack_req_flag := 'B';
OE_Header_Ack_Util.Insert_Row
(p_header_rec => l_header_rec,
x_ack_type => l_ack_type,
x_return_status => l_return_status);
OE_Line_Ack_Util.Insert_Row
(p_line_tbl => l_line_tbl,
p_old_line_tbl => l_old_line_tbl,
x_return_status => l_return_status);
OE_Line_Ack_Util.Insert_Row
(p_line_tbl => l_reject_line_tbl
,p_old_line_tbl => l_reject_line_tbl
,p_line_val_tbl => l_reject_line_val_tbl
,p_old_line_val_tbl => l_reject_line_val_tbl
,p_buyer_seller_flag => 'B'
,p_reject_order => 'Y'
,x_return_status => l_return_status
);
OE_Update_Ack_Util.Raise_Derive_Ack_Data_event
(p_transaction_type => 'ONT',
p_header_id => l_header_rec.header_id,
p_org_id => l_header_rec.org_id,
p_orig_sys_document_ref => l_header_rec.orig_sys_document_ref,
p_change_sequence => l_header_rec.change_sequence,
p_sold_to_org_id => l_header_rec.sold_to_org_id,
p_order_number => l_header_rec.order_number,
p_order_source_id => l_header_rec.order_source_id,
p_transaction_subtype => l_ack_type,
p_order_type_id => l_header_rec.order_type_id,
p_xml_msg_id => l_header_rec.xml_message_id, --l_xml_message_id,
x_return_status => l_return_status);