[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select orig_sys_document_ref
Into l_orig_sys_document_ref
From oe_order_headers
Where header_id = p_header_id;
Select orig_sys_document_ref
Into l_orig_sys_document_ref
From oe_order_lines
Where line_id = p_line_id;
Select header_id
Into l_header_id
From oe_order_headers
Where order_source_id = G_XML_ORDER_SOURCE_ID
And orig_sys_document_ref = p_orig_sys_document_ref
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 rownum = 1;
Select header_id
Into l_header_id
From oe_order_lines
Where line_id = p_line_id
And order_source_id = G_XML_ORDER_SOURCE_ID
And rownum = 1;
Select Oe_Xml_Message_Seq_S.nextval
Into l_itemkey
From dual;
l_parameter_list.DELETE;
select OE_XML_MESSAGE_SEQ_S.nextval
into l_itemkey
from dual;
l_parameter_list.DELETE;
PROCEDURE Insert_Header
( p_header_rec IN OE_Order_Pub.Header_Rec_Type,
p_header_status IN Varchar2,
p_ack_type IN Varchar2,
p_itemkey IN Number,
x_return_status OUT NOCOPY /* file.sql.39 change */ Varchar2
)
IS
l_header_status varchar2(30);
oe_debug_pub.add( 'ENTERNING: OEXPACK PROCEDURE INSERT_HEADER' ) ;
Insert Into OE_HEADER_ACKS (header_id, acknowledgment_type, last_ack_code, request_id, sold_to_org_id, change_sequence)
Values (p_header_rec.header_id, p_ack_type, l_header_status, p_itemkey, --p_header_rec.request_id
p_header_rec.sold_to_org_id, p_header_rec.change_sequence);
oe_debug_pub.add( 'INSERTED HEADER_ID => ' || P_HEADER_REC.HEADER_ID ) ;
oe_debug_pub.add( 'NOT INSERTED HEADER_ID => ' || P_HEADER_REC.HEADER_ID ) ;
oe_debug_pub.add( 'EXITING: OEXPACK PROCEDURE INSERT_HEADER' ) ;
FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'insert_header');
END Insert_Header;
PROCEDURE Insert_Line
( p_line_rec IN OE_Order_Pub.Line_Rec_Type,
p_line_status IN Varchar2,
p_ack_type IN Varchar2,
p_itemkey IN Number,
x_return_status OUT NOCOPY /* file.sql.39 change */ Varchar2
)
IS
--
l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
oe_debug_pub.add( 'ENTERNING: OEXPACK PROCEDURE INSERT_LINE' ) ;
Insert Into OE_LINE_ACKS (header_id, line_id,
acknowledgment_type, last_ack_code, request_id,
sold_to_org_id, change_sequence)
Values (p_line_rec.header_id, p_line_rec.line_id,
p_ack_type, p_line_status,
p_itemkey, --p_line_rec.request_id
p_line_rec.sold_to_org_id,
p_line_rec.change_sequence
);
oe_debug_pub.add( 'INSERTED LINE_ID => ' || P_LINE_REC.LINE_ID ) ;
oe_debug_pub.add( 'NOT INSERTED LINE_ID => ' || P_LINE_REC.LINE_ID ) ;
oe_debug_pub.add( 'EXITING: OEXPACK PROCEDURE INSERT_LINE' ) ;
FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'insert_line');
END Insert_Line;
SELECT order_source_id , orig_sys_document_ref
, change_sequence , booked_flag
, customer_number , customer_po_number
, freight_terms_code , freight_terms
, fob_point_code , fob_point
, invoice_to_org_id , invoice_to_org
, invoice_address1 , invoice_address2
, invoice_address3 , invoice_city
, invoice_state , invoice_postal_code
, invoice_county , invoice_country
, ship_from_org_id , ship_from_org
-- ?? Should we add all the ship from address columns??
, ship_to_org_id , ship_to_org
, ship_to_address1 , ship_to_address2
, ship_to_address3 , ship_to_city
, ship_to_state , ship_to_postal_code
, ship_to_county , ship_to_country
-- ?? Should we add all the sold to address columns??
, sold_to_org_id , sold_to_org
, org_id , request_id
, xml_message_id , payment_term
FROM oe_headers_interface
WHERE order_source_id = p_order_source_id
AND orig_sys_document_ref = p_orig_sys_document_ref
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 nvl(request_id, -999)
= nvl(p_request_id, -999)
AND xml_transaction_type_code = p_xml_transaction_type_code
AND error_flag = 'Y'
-- FOR UPDATE NOWAIT
;
SELECT order_source_id , orig_sys_document_ref
, customer_item_name , customer_item_id
, customer_po_number , orig_sys_line_ref
, ordered_quantity , order_quantity_uom
, request_date , orig_sys_shipment_ref
, org_id , request_id
, change_sequence , sold_to_org_id
, customer_line_number , customer_shipment_number
FROM oe_lines_interface
WHERE order_source_id = p_order_source_id
AND orig_sys_document_ref = p_orig_sys_document_ref
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 nvl(request_id, -999)
= nvl(p_request_id, -999)
AND xml_transaction_type_code = p_xml_transaction_type_code
-- FOR UPDATE NOWAIT
ORDER BY orig_sys_line_ref, orig_sys_shipment_ref;
Select Line_Id
From oe_line_acks
Where header_id = l_header_id
And acknowledgment_type = l_acknowledgment_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);
Select Line_Id
From oe_order_lines
Where request_id = l_request_id
And header_id = l_header_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 Line_Id, Last_Ack_Code
From oe_line_acks
Where header_id = l_header_id
And acknowledgment_type = l_acknowledgment_type
And request_id = l_request_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 orig_sys_document_ref
into l_orig_sys_document_ref
From oe_headers_interface
Where order_source_id = G_XML_ORDER_SOURCE_ID
And orig_sys_document_ref = p_orig_sys_document_ref
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 request_id = p_request_id;
oe_debug_pub.add( 'OEXPACKB: OTHERS IN SELECT FROM OE_HEADERS_INTERFACE' ) ;
Select header_id
into l_header_id
From oe_order_headers
Where order_source_id = G_XML_ORDER_SOURCE_ID
And orig_sys_document_ref = p_orig_sys_document_ref
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);
OE_MSG_PUB.update_msg_context(
p_header_id => l_header_id
);
oe_debug_pub.add( 'OEXPACKB: OTHERS IN SELECT FROM OE_ORDER_HEADERS' ) ;
Select last_ack_code
into l_header_last_ack_code
from oe_header_acks
where acknowledgment_type = p_transaction_type
and header_id = l_header_id
and request_id = l_request_id;
Select cancelled_flag
Into l_cancelled_flag
From oe_order_headers
Where header_id = l_header_id;
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_header_rec
, p_old_header_val_rec => l_header_val_rec
, p_reject_order => l_reject_order
, p_ack_type => l_acknowledgment_type
, x_return_status => l_return_status
);
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_line_tbl
, p_old_line_val_tbl => l_line_val_tbl
, p_buyer_seller_flag => 'B'
, p_reject_order => l_reject_order
, p_ack_type => l_acknowledgment_type
, x_return_status => l_return_status
);
Select /* MOAC_SQL_CHANGE */ a.cust_acct_site_id, a.party_site_id, c.party_id
Into l_cust_acct_site_id, l_party_site_id, l_party_id
From hz_cust_acct_sites_all a, hz_cust_site_uses_all b, hz_cust_accounts c
Where a.cust_acct_site_id = b.cust_acct_site_id
And a.cust_account_id = p_customer_id
And a.cust_account_id = c.cust_account_id
/* And NVL(a.org_id,NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),
1,1),' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)) =
NVL(TO_NUMBER(DECODE( SUBSTRB(USERENV('CLIENT_INFO'),1,1),
' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99) */
And a.org_id = l_org_id
And b.site_use_code = 'SOLD_TO'
And b.primary_flag = 'Y'
And b.status = 'A'
And a.status ='A'; --bug 2752321
l_insert_sync_line VARCHAR2(1); -- := 'N';
l_sync_line_inserted boolean := false; --Bug# 12879272
select OE_XML_MESSAGE_SEQ_S.nextval
into l_itemkey_sso
from dual;
select OE_XML_MESSAGE_SEQ_S.nextval
into l_itemkey_cso
from dual;
l_sync_line_inserted := FALSE; --Bug# 12879272
OR (l_line_tbl(i).operation = Oe_Globals.G_OPR_UPDATE)
)
THEN
-- Bug 13008311 : End
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'OEXPACKB: LINE OPERATIONS IS UPDATE' ) ;
l_insert_sync_line := 'Y';
l_insert_sync_line := 'Y';
l_insert_sync_line := 'Y';
l_insert_sync_line := 'Y';
l_insert_sync_line := 'Y';
l_insert_sync_line := 'Y';
oe_debug_pub.add( 'Genesis: l_insert_sync_line '|| l_insert_sync_line);
IF l_insert_sync_line = 'Y' and
(OE_GENESIS_UTIL.source_aia_enabled(l_header_rec.order_source_id)) THEN
-- Bug# 12879272
-- Checking if the Acknowledgement is already inserted
-- When a line is created through Process Order API, and Booking and Scheduling of line
-- happens in the same call, then Acknowledgement is inserted TWICE, to avoid that
-- we are checking the flag l_sync_line_inserted
IF NOT l_sync_line_inserted THEN
OE_SYNC_ORDER_PVT.INSERT_SYNC_lINE(P_LINE_rec => l_line_tbl(i),
p_change_type => l_change_type,
p_req_id => l_itemkey_sso,
X_RETURN_STATUS => L_RETURN_STATUS);
l_sync_line_inserted := TRUE;
oe_debug_pub.add('l_sync_line_inserted: TRUE');
l_insert_sync_line := 'N';
oe_debug_pub.add( 'Genesis: after insert :l_insert_sync_line '|| l_insert_sync_line);
end If; -- Update operation
( p_old_line_tbl(j).operation = Oe_Globals.G_OPR_INSERT or
p_old_line_tbl(j).operation = Oe_Globals.G_OPR_CREATE or
(l_line_exists ='N' and p_line_tbl(j).booked_flag ='Y' -- Added Condition for bug 9685021
and NOT OE_GENESIS_UTIL.source_aia_enabled(l_header_rec.order_source_id) ) -- bug 11078158
) then
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'OEXPACKB: LINE OPERATIONS IS INSERT' ) ;
-- Checking if the Acknowledgement is already inserted during the UPDATE operation check above.
-- When a line is created through Process Order API, and Booking and Scheduling of line
-- happens in the same call, then Acknowledgement is inserted TWICE, to avoid this
-- we are checking the flag l_sync_line_inserted
IF NOT l_sync_line_inserted THEN
OE_SYNC_ORDER_PVT.INSERT_SYNC_lINE(P_LINE_rec => l_line_tbl(i),
p_change_type => l_change_type,
p_req_id => l_itemkey_sso,
X_RETURN_STATUS => L_RETURN_STATUS);
l_sync_line_inserted := TRUE;
oe_debug_pub.add('l_sync_line_inserted: TRUE');
oe_debug_pub.add('No need to insert an acknowledgement, since one was already inserted');
l_line_req_cso := 'N'; -- don't insert the line
Insert_Header ( p_header_rec => l_header_rec,
p_header_status => l_header_status,
p_ack_type => l_ack_type, -- GENESIS G_TRANSACTION_SSO,
p_itemkey => l_itemkey_sso,
x_return_status => l_return_status
);
oe_debug_pub.add( 'INSERTING LINE RECORD WITH INDEX = ' || I ) ;
Insert_Line ( p_line_rec => l_line_rec,
p_line_status => l_line_status,
p_ack_type => l_ack_type, -- GENESIS G_TRANSACTION_SSO,
p_itemkey => l_itemkey_sso,
x_return_status => l_return_status
);
Insert_Header ( p_header_rec => l_header_rec,
p_header_status => l_header_status_cso,
p_ack_type => G_TRANSACTION_CSO,
p_itemkey => l_itemkey_cso,
x_return_status => l_return_status
);
oe_debug_pub.add( 'INSERTING LINE RECORD WITH INDEX = ' || I ) ;
Insert_Line ( p_line_rec => l_line_rec,
p_line_status => l_line_status_cso,
p_ack_type => G_TRANSACTION_CSO,
p_itemkey => l_itemkey_cso,
x_return_status => l_return_status
);
Insert_Header ( p_header_rec => l_header_rec,
p_header_status => l_header_status,
p_ack_type => l_ack_type, -- GENESIS G_TRANSACTION_SSO,
p_itemkey => l_itemkey_sso,
x_return_status => l_return_status
);
Insert_Header ( p_header_rec => l_header_rec,
p_header_status => l_header_status_cso,
p_ack_type => G_TRANSACTION_CSO,
p_itemkey => l_itemkey_cso,
x_return_status => l_return_status
);
SELECT header_id, sold_to_org_id, order_number, orig_sys_document_ref, order_source_id, change_sequence,org_id
FROM oe_order_headers
WHERE sold_to_org_id = p_customer_id
-- AND open_flag = 'Y' -- only open orders are supported currently
AND open_flag IN ('Y', l_open_flag)
AND order_source_id = G_XML_ORDER_SOURCE_ID
AND order_number BETWEEN nvl(p_so_number_from,order_number) AND nvl(p_so_number_to,order_number)
AND ordered_date BETWEEN nvl(l_so_date_from,ordered_date) AND nvl(l_so_date_to + 1,ordered_date + 1)
AND nvl(cust_po_number, -99) BETWEEN nvl(p_customer_po_no_from,nvl(cust_po_number, -99)) AND nvl(p_customer_po_no_to,nvl(cust_po_number, -99))
AND org_id = nvl(p_operating_unit,org_id);
SELECT OE_XML_MESSAGE_SEQ_S.nextval
INTO l_eventkey
FROM dual;
l_parameter_list.DELETE;
/* SELECT TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ',
NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))) into l_org_id from DUAL; */
Select order_number, order_type_id, header_id
Into l_document_num, l_order_type_id, l_header_id
From oe_order_headers
Where orig_sys_document_ref = p_partner_document_num
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);
l_parameter_list.DELETE;
l_parameter_list.DELETE;
SELECT MEANING
INTO l_transaction_type
FROM OE_LOOKUPS
WHERE LOOKUP_CODE = p_txn_code
AND LOOKUP_TYPE = 'ONT_ELECMSGS_TYPES';
SELECT OE_XML_MESSAGE_SEQ_S.nextval
INTO l_eventkey
FROM dual;
l_parameter_list.DELETE;
l_parameter_list.DELETE;
SELECT 'X' INTO temp FROM oe_order_lines_all WHERE line_id=p_line_id;