[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
,x_last_update_date OUT NOCOPY DATE)
IS
CURSOR c_gethdrstatus(p_header_id NUMBER) IS
SELECT flow_status_code,last_update_date FROM oe_order_headers_all WHERE header_id = p_header_id;
x_last_update_date := null;
x_last_update_date := l_gethdrstatus.last_update_date;
IBE_ACTIVE_QUOTES_ALL_PKG.Update_row(
X_OBJECT_VERSION_NUMBER => 1,
X_ORDER_HEADER_ID => null ,
X_PARTY_ID => p_party_id,
X_CUST_ACCOUNT_ID => p_cust_account_id,
X_CURRENCY_CODE => p_currency_code,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => fnd_global.user_id,
X_LAST_UPDATE_LOGIN => 1,
X_RECORD_TYPE => 'ORDER');
select aq.order_header_id,aq.party_id
from ibe_active_quotes aq
where cust_account_id = c_cust_account_id
and party_id = c_party_id
and currency_code = c_currency_code
and record_type = 'ORDER';
IBE_ACTIVE_QUOTES_ALL_PKG.Insert_row(
X_OBJECT_VERSION_NUMBER => 1,
X_ORDER_HEADER_ID => p_order_header_id,
X_PARTY_ID => p_party_id,
X_CUST_ACCOUNT_ID => p_cust_account_id,
X_LAST_UPDATE_DATE => sysdate,
X_CREATION_DATE => sysdate,
X_CREATED_BY => fnd_global.USER_ID,
X_LAST_UPDATED_BY => fnd_global.USER_ID,
X_LAST_UPDATE_LOGIN => fnd_global.conc_login_id,
X_CURRENCY_CODE => p_currency_code,
X_RECORD_TYPE => 'ORDER',
X_ORG_ID => mo_global.GET_CURRENT_ORG_ID());
IBE_ACTIVE_QUOTES_ALL_PKG.update_row(
X_OBJECT_VERSION_NUMBER => 1,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => fnd_global.user_id,
X_LAST_UPDATE_LOGIN => 1,
X_PARTY_ID => p_party_id,
X_CUST_ACCOUNT_ID => p_cust_account_id,
X_ORDER_HEADER_ID => p_order_header_id,
X_CURRENCY_CODE => p_currency_code,
X_RECORD_TYPE => 'ORDER');
select invoice_to_org_id from oe_order_lines_all where line_id = l_lineId;
l_tmp_query := 'SELECT csu.site_use_id '||
'FROM hz_cust_site_uses_all csu '||
'WHERE csu.status = ''A'' AND site_use_code = :siteusecode '||
'AND csu.site_use_id IN ('||l_line_id_query||')';
select * from oe_order_lines_all
where header_id = l_rethdrId
and return_attribute2 = l_lineid
and return_attribute1 = l_ordhdrId
and line_category_code= 'RETURN';
x_line_rec.operation := OE_Globals.G_OPR_UPDATE;
select default_inbound_line_type_id from oe_transaction_types_all
where transaction_type_id = header_typeid;
SELECT salesrep_id from OE_ORDER_LINES_ALL WHERE line_id = l_lineid;
select order_type_id from oe_order_headers_all where header_id = c_ordhdr_typeid;
select ordered_quantity
from oe_order_lines_all
where line_id= c_origqtyLineId;
END IF; -- x_orderheader_rec.operation = update
l_order_line_tbl(i).operation := OE_GLOBALS.G_OPR_UPDATE;
IF (p_order_header_rec.operation = OE_GLOBALS.G_OPR_UPDATE) THEN
MergeLines(p_header_id => p_order_header_rec.HEADER_ID,
p_order_line_rec => l_order_line_tbl(i),
x_line_rec => lx_line_rec);
the user is trying to add an item to it. So it will be 'UPDATE' at header level and
'CREATE' at line level */
IF (l_order_line_tbl(i).operation = OE_GLOBALS.G_OPR_CREATE) THEN
l_order_line_tbl(i).line_category_code := 'RETURN';
ELSE -- headerrec.operation.operation ='update'
/* This flow will be executed when both HEader and Line level are 'CREATE'.
ie first time creation of a Return */
IF (IBE_UTIL.G_DEBUGON = l_true) THEN
ibe_util.Debug('Inside Line Values Defaulting flow');
END IF; -- headerrec.operation.operation ='update'
l_order_line_tbl(i).operation := OE_GLOBALS.G_OPR_DELETE;
SELECT aq.order_header_id FROM ibe_active_quotes aq, oe_order_headers_all oh
WHERE aq.party_id = partyId
AND aq.cust_account_id = custAcctId
AND aq.currency_code = currencyCode
AND aq.record_type = 'ORDER'
AND aq.order_header_id = oh.header_id
AND oh.flow_status_code IN ('ENTERED','WORKING');
select order_type_id from oe_order_headers_all where header_id = c_ordhdr_id;
l_last_update_date DATE;
,x_last_update_date => l_last_update_date);
Ibe_util.Debug('last_update_date sent from ui: '||
to_char(x_order_header_rec.last_update_date,'dd-mm-yyyy hh:mi:ss'));
Ibe_util.Debug('last_update_date from db: '||
to_char(l_last_update_date,'dd-mm-yyyy hh:mi:ss'));
OR l_last_update_date > x_order_header_rec.last_update_date)
THEN
FND_Message.Set_Name('IBE', 'IBE_ERR_OT_REFRESH_RETURN');
x_order_header_rec.operation := OE_Globals.G_OPR_UPDATE;
select SALESREP_ID into l_salesrep_id from JTF_RS_SALESREPS where SALESREP_NUMBER = l_salesrep_number and ORG_ID = l_user_orgid;
IBE_Util.Debug('Inside Header Record update defaulting flow');
x_order_header_rec.operation := OE_Globals.G_OPR_UPDATE;
select flow_status_code, order_category_code from oe_order_headers_all where header_id = l_hrdId;
x_order_header_rec.operation := OE_Globals.G_OPR_DELETE;
x_order_header_rec.operation := OE_Globals.G_OPR_UPDATE;
select invoice_to_org_id,ship_to_org_id from oe_order_lines_all where line_id = l_lineId;
select party_type from HZ_PARTIES where party_id = cpt_party_id;
select hca.cust_acct_site_id, hca.cust_account_id,hps.party_id
from hz_cust_acct_sites hca,hz_cust_site_uses hcu,hz_party_sites hps
where
hcu.site_use_id = lin_custacct_siteuse_id
and hcu.site_use_code = lin_siteuse_type
and hcu.cust_acct_site_id = hca.cust_acct_site_id
and hca.party_site_id = hps.party_site_id;
select ship_to_org_id,invoice_to_org_id from oe_order_headers_all where header_id = cl_hdr_id;
IBE_Util.Debug('No. of lines to be updated: '|| l_linetbl_count);
l_linetmp_qry := 'select line_id from oe_order_lines_all '||
'where header_id= :1 and '||
'line_id NOT IN('|| l_all_lineids_query ||')';
l_order_line_tbl(l_linetbl_count).operation := OE_Globals.G_OPR_UPDATE;
select line_id,ordered_quantity,line_category_code,header_id
from oe_order_lines_all
where ('ORDER',return_attribute1,return_attribute2) in
(select line_category_code,header_id,line_id
from oe_order_lines_all
where ('RETURN',header_id, top_model_line_id) in
(select line_category_code,return_attribute1,return_attribute2
from oe_order_lines_all
where line_id= c_mdlop_lineid)
and link_to_line_id is not null)
and header_id = c_modop_hdr_id;
select ordered_quantity from oe_order_lines_all
where line_id = l_mdlop_line_id;
Select item_type_code from oe_order_lines_all where line_id =
(Select return_attribute2 from oe_order_lines_all where line_id = c_linetmp_id);
l_order_line_tbl(l_linetbl_count).OPERATION := OE_Globals.G_OPR_DELETE;
l_order_line_tbl(l_linetbl_count).OPERATION := OE_Globals.G_OPR_UPDATE;
elsif p_context_type='UPDATELINES' then
for k in 1..l_order_line_tbl.count
loop
-- Check whether this line is a model parent
for c_linetmp_rec in c_linetmp_cur(l_order_line_tbl(k).line_id)
loop
l_line_type := c_linetmp_rec.ITEM_TYPE_CODE;
l_order_line_tbl(l_linetbl_count).OPERATION := OE_Globals.G_OPR_UPDATE;
select created_by from oe_order_headers_all where header_id=l_acc_hdr_id;
SELECT Line_id,inventory_item_id,reference_line_id,ordered_quantity,
line_number,return_attribute1,return_attribute2
FROM oe_order_lines_all
WHERE header_id=qtychk_hdrid
ORDER BY line_number;
l_qty_tmp_query := 'SELECT OEH.order_number,msi.concatenated_segments,msi.DESCRIPTION,'||
'OEL.line_id,OEL.item_type_code '||
'FROM oe_order_lines_all OEL,'||
'oe_order_headers_all OEH,'||
'mtl_system_items_vl msi '||
'WHERE OEL.header_id = OEH.header_id '||
'AND OEL.inventory_item_id = msi.inventory_item_Id '||
'AND msi.organization_id = oe_profile.value(''OE_ORGANIZATION_ID'', OEL.org_id) '||
'AND OEL.line_id IN('||
'SELECT nvl(top_model_line_id,line_id) '||
'FROM oe_order_lines_all '||
'WHERE line_id IN('||
'SELECT return_attribute2 '||
'FROM oe_order_lines_all '||
'WHERE line_id in('||l_tmp_error_lineIds||')))';
l_addr_validate_qry:='SELECT LINES.LINE_ID '||
'FROM HZ_CUST_SITE_USES SITE, '||
'HZ_CUST_ACCT_SITES ACCT_SITE, '||
'OE_ORDER_LINES_ALL LINES '||
'WHERE LINES.HEADER_ID = :InvChkHdrId '||
'AND SITE.SITE_USE_ID = LINES.invoice_to_org_id '||
'AND SITE.SITE_USE_CODE = ''BILL_TO'' '||
'AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID '||
'AND (SITE.STATUS = ''I'' OR ACCT_SITE.STATUS = ''I'') '||
'AND '||
'ACCT_SITE.CUST_ACCOUNT_ID in ('||
'SELECT LINES.sold_to_org_id FROM DUAL '||
'UNION '||
'SELECT CUST_ACCOUNT_ID '||
'FROM HZ_CUST_ACCT_RELATE '||
'WHERE '||
'RELATED_CUST_ACCOUNT_ID = LINES.sold_to_org_id '||
'and bill_to_flag = ''Y'' '||
'and status=''A'')';
l_addr_validate_qry :='SELECT LINES.LINE_ID '||
'FROM HZ_CUST_SITE_USES SITE, '||
'HZ_CUST_ACCT_SITES ACCT_SITE, '||
'OE_ORDER_LINES_ALL LINES '||
'WHERE LINES.HEADER_ID = :shpChkHdrId '||
'AND SITE.SITE_USE_ID = LINES.ship_to_org_id '||
'AND SITE.SITE_USE_CODE = ''SHIP_TO'' '||
'AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID '||
'AND (SITE.STATUS = ''I'' OR ACCT_SITE.STATUS = ''I'') '||
'AND '||
'ACCT_SITE.CUST_ACCOUNT_ID in ('||
'SELECT LINES.sold_to_org_id FROM DUAL '||
'UNION '||
'SELECT CUST_ACCOUNT_ID '||
'FROM HZ_CUST_ACCT_RELATE '||
'WHERE '||
'RELATED_CUST_ACCOUNT_ID = LINES.sold_to_org_id '||
'and ship_to_flag = ''Y'' '||
'and status=''A'')';
l_addr_validate_qry := 'SELECT HDR.HEADER_ID '||
'FROM HZ_CUST_SITE_USES SITE, '||
'HZ_CUST_ACCT_SITES ACCT_SITE, '||
'OE_ORDER_HEADERS_ALL HDR '||
'WHERE HDR.HEADER_ID = :invChkHdrId '||
'AND SITE.SITE_USE_ID = HDR.invoice_to_org_id '||
'AND SITE.SITE_USE_CODE = ''BILL_TO'' '||
'AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID '||
'AND (SITE.STATUS = ''I'' OR ACCT_SITE.STATUS = ''I'') '||
'AND '||
'ACCT_SITE.CUST_ACCOUNT_ID in ('||
'SELECT HDR.sold_to_org_id FROM DUAL '||
'UNION '||
'SELECT CUST_ACCOUNT_ID '||
'FROM HZ_CUST_ACCT_RELATE '||
'WHERE '||
'RELATED_CUST_ACCOUNT_ID = HDR.sold_to_org_id '||
'and bill_to_flag = ''Y'' '||
'and status=''A'')';
l_addr_validate_qry := 'SELECT HDR.HEADER_ID '||
'FROM HZ_CUST_SITE_USES SITE, '||
'HZ_CUST_ACCT_SITES ACCT_SITE, '||
'OE_ORDER_HEADERS_ALL HDR '||
'WHERE HDR.HEADER_ID = :shpChkHdrId '||
'AND SITE.SITE_USE_ID = HDR.ship_to_org_id '||
'AND SITE.SITE_USE_CODE = ''SHIP_TO'' '||
'AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID '||
'AND (SITE.STATUS = ''I'' OR ACCT_SITE.STATUS = ''I'') '||
'AND '||
'ACCT_SITE.CUST_ACCOUNT_ID in ('||
'SELECT HDR.sold_to_org_id FROM DUAL '||
'UNION '||
'SELECT CUST_ACCOUNT_ID '||
'FROM HZ_CUST_ACCT_RELATE '||
'WHERE '||
'RELATED_CUST_ACCOUNT_ID = HDR.sold_to_org_id '||
'and ship_to_flag = ''Y'' '||
'and status=''A'')';
l_contact_validate_qry:='SELECT LINES.LINE_ID '||
'FROM OE_ORDER_LINES_ALL LINES, '||
'HZ_CUST_ACCOUNT_ROLES ACCT_ROLE, '||
'HZ_CUST_SITE_USES_ALL INV, '||
'HZ_CUST_ACCT_SITES_ALL ADDR '||
'WHERE LINES.HEADER_ID = :InvCntChkHdrId '||
'AND ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = LINES.invoice_to_contact_id '||
'AND ACCT_ROLE.CUST_ACCOUNT_ID = ADDR.CUST_ACCOUNT_ID '||
'AND ACCT_ROLE.ROLE_TYPE = ''CONTACT'' '||
'AND ADDR.CUST_ACCT_SITE_ID = INV.CUST_ACCT_SITE_ID '||
'AND INV.SITE_USE_ID = LINES.invoice_to_org_id '||
'AND INV.STATUS = ''I'' '||
'AND ACCT_ROLE.STATUS = ''I''';
l_contact_validate_qry:='SELECT LINES.LINE_ID '||
'FROM OE_ORDER_LINES_ALL LINES, '||
'HZ_CUST_ACCOUNT_ROLES ACCT_ROLE, '||
'HZ_CUST_SITE_USES_ALL INV, '||
'HZ_CUST_ACCT_SITES_ALL ADDR '||
'WHERE LINES.HEADER_ID = :InvCntChkHdrId '||
'AND ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = LINES.ship_to_contact_id '||
'AND ACCT_ROLE.CUST_ACCOUNT_ID = ADDR.CUST_ACCOUNT_ID '||
'AND ACCT_ROLE.ROLE_TYPE = ''CONTACT'' '||
'AND ADDR.CUST_ACCT_SITE_ID = INV.CUST_ACCT_SITE_ID '||
'AND INV.SITE_USE_ID = LINES.ship_to_org_id '||
'AND INV.STATUS = ''I'' '||
'AND ACCT_ROLE.STATUS = ''I''';
,x_last_update_date OUT NOCOPY DATE
,X_failed_line_ids OUT NOCOPY JTF_VARCHAR2_TABLE_300
)
IS
cursor c_holdid is
select hold_id from oe_hold_definitions where name = 'STORE_HOLD';
select party_type from hz_parties where party_id = cr_prtyid;
l_last_update_date DATE;
(l_order_header_rec.operation = OE_Globals.G_OPR_UPDATE) AND
(p_submit_control_rec.cancel_flag <> 'Y'))
THEN
IF (IBE_UTIL.G_DEBUGON = l_true) then
Ibe_Util.DEBUG('calling SetLine ids API');
END IF; -- if operation = update
/************ Hard Delete logic for Pending Return ****/
IF (IBE_UTIL.G_DEBUGON = l_true) THEN
IBE_Util.Debug('ibe_order_save_pvt:cancel_flag ' ||p_submit_control_rec.cancel_flag);
IF(l_order_header_rec.operation = OE_Globals.G_OPR_DELETE) THEN
l_cancel_flow := FND_API.G_TRUE;
IF (l_order_header_rec.operation = OE_GLOBALS.G_OPR_UPDATE AND
(p_submit_control_rec.submit_flag is null
OR p_submit_control_rec.submit_flag = FND_API.G_MISS_CHAR))
THEN
IF l_order_header_rec.order_category_code = 'RETURN' then
SaveMDLRelatedOperations(p_context_type => 'SAVE',
p_order_line_tbl => l_order_line_tbl,
p_order_header_id => l_order_header_rec.header_id,
p_save_type => p_save_type,
x_order_line_tbl => lx_line_tbl
);
/***************** Delete The Order When Last Item is Deleted bug#3272947 **************/
IF (l_order_header_rec.order_category_code = 'RETURN'
AND p_save_type = SAVE_REMOVEITEMS) THEN
IF (IBE_UTIL.G_DEBUGON = l_true) THEN
IBE_Util.debug('Inside Last Item Removal-Hard Delete If Loop');
if(l_order_line_tbl(lineIdx).operation=OE_GLOBALS.G_OPR_DELETE) THEN
p_dl_line_ids := p_dl_line_ids || ','||l_order_line_tbl(lineIdx).LINE_ID;
l_dl_tmp_qry := 'SELECT count(*) from oe_order_lines_all '||
'WHERE header_id= :dl_header_id '||
'AND line_id NOT IN('||l_dl_line_id_qry||')';
IF (No_Of_ExistingLines = 0) THEN -- No More Lines in The Return Order so can be hard deleted.
CancelOrder(l_order_header_rec,lx_order_header_rec);
IF(l_order_header_rec.operation = OE_Globals.G_OPR_DELETE) THEN
l_cancel_flow := FND_API.G_TRUE;
,x_last_update_date => l_last_update_date);
(l_order_header_rec.operation = OE_Globals.G_OPR_UPDATE) AND
(p_submit_control_rec.cancel_flag <> 'Y')) THEN
l_ProcessOrder_Flow := FND_API.G_TRUE;
(l_order_header_rec.operation = OE_Globals.G_OPR_UPDATE) AND
(p_submit_control_rec.cancel_flag <> 'Y')) THEN
/* bug 8303137, scnagara, Added this condition for a b2b user so that when quantity
is updated in Review and Submit return page, and Submit Return is clicked, quantity needs
to be saved to db */
IF (IBE_UTIL.G_DEBUGON = l_true) THEN
IBE_Util.Debug('B2b user - require update to be performed');
x_last_update_date := lx_order_header_rec.last_update_date;
select order_number,last_update_date,flow_status_code
from oe_order_headers_all where header_id=sh_hdr_id;
fetch c_submit_hdrattr into x_order_number,x_last_update_date,x_flow_status_code;
,x_last_update_date OUT NOCOPY DATE
)
IS
/*********** API Flow ***********/
-- Refer Bug# 2988993 For Details
-- Process_Order() would be called twice
-- First Call, would create a Return Header without lines.
-- Now the second call would use this SAme Header_id created in the first call.
-- Next, set the Control Record and a new Header Record(l_retplcy_orderhdr_rec)
-- with HEaderId created above and opcode 'UPDATE".
-- Call Process_Order() API again, with l_retplcy_orderhdr_rec, ControlRec and
-- Line_Table.
-- This second call would set Api_Service_Level param as 'Check_Security_Only'.
-- Second call check the Return Policy and send the failing LineIds back.
-- Later when OM release bug# 2988993, then these 2 Porcess_Order() API call
-- could be removed and HeaderRecord, LineTable and ControlRecord could be sent
-- together in a Single Call.
l_order_header_rec OE_Order_PUB.Header_Rec_Type := OE_Order_PUB.G_MISS_HEADER_REC;
select * from oe_order_lines_all where line_id = l_lineId;
select SALESREP_ID into l_salesrep_id from JTF_RS_SALESREPS where SALESREP_NUMBER = l_salesrep_number and ORG_ID = l_user_orgid;
l_retplcy_orderhdr_rec.operation := OE_Globals.G_OPR_UPDATE;
PROCEDURE UpdateLineShippingBilling(
p_api_version_number IN NUMBER
,p_init_msg_list IN VARCHAR2
,p_commit IN VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_order_header_id IN NUMBER
,p_order_line_id IN NUMBER
,p_billto_party_id IN NUMBER
,p_billto_cust_acct_id IN NUMBER
,p_billto_party_site_id IN NUMBER
,p_shipto_party_id IN NUMBER
,p_shipto_cust_acct_id IN NUMBER
,p_shipto_party_site_id IN NUMBER
,p_last_update_date IN DATE
)
IS
--l_acct_siteuse_id NUMBER := null;
l_api_name VARCHAR2(30) := 'ORDER_UPDATELINEBILL';
l_last_update_date DATE;
IBE_Util.Debug('Begin IBE_ORDER_SAVE_PVT:UpdateLinebilling()');
,x_last_update_date => l_last_update_date);
Ibe_util.Debug('last_update_date sent from ui: '||p_last_update_date);
Ibe_util.Debug('last_update_date from db: '||l_last_update_date);
OR l_last_update_date > p_last_update_date)
THEN
FND_Message.Set_Name('IBE', 'IBE_ERR_OT_REFRESH_RETURN');
IBE_Util.Debug('UpdateLinebilling-l_siteuse_billto: '||l_siteuse_billto||' : l_siteuse_shipto: '||l_siteuse_shipto);
IBE_Util.Debug('UpdateLinebilling- Get_Cust_Account_Site_Use() fails raise exception');
IBE_Util.Debug('UpdateLinebilling-l_acct_siteuse_id: ' || l_billto_acct_siteuse_id);
IBE_Util.Debug('UpdateLinebilling- Get_Cust_Account_Site_Use() fails raise exception');
IBE_Util.Debug('UpdateLinebilling-l_acct_siteuse_id: ' || l_shipto_acct_siteuse_id);
l_order_header_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
l_order_line_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
SaveMDLRelatedOperations(p_context_type => 'UPDATELINES',
p_order_line_tbl => l_order_line_tbl,
p_order_header_id => p_order_header_id,
x_order_line_tbl => lx_line_tbl
);
IBE_Util.Debug('UpdateLine Linetbl cnt b4 processOrder '||l_order_line_tbl.count);
IBE_UTIL.DEBUG('Return status from OE updatelinebill: ' || x_return_status);
IBE_UTIL.DEBUG('header id from OE updatelinebill: ' || lx_order_header_rec.header_id);
IBE_UTIL.DEBUG('Message from OE update line bill: ' || x_msg_data );
IBE_Util.Debug('End IBE_ORDER_SAVE_PVT:UpdateLinebilling()');
IBE_Util.Debug('Error IBE_ORDER_SAVE_PVT:UpdateLineShippingBilling()'|| sqlerrm);
IBE_Util.Debug('UNEXPECTEDErr IBE_ORDER_SAVE_PVT:UpdateLineShippingBilling()' || sqlerrm);
IBE_Util.Debug('OtherExc IBE_ORDER_SAVE_PVT:UpdateLineShippingBilling()' || sqlerrm);
END UpdateLineShippingBilling;