[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| so using the value in last_updated_by column
| to identify the appropriate recipient of
| the cancel order notification.
| 06/18/02 mannamra Bugfix: 2417011 - Obsoleted getfirstname_for_quote API because
| getfirstname solves the purpose.
| 08/20/02 mannamra Bugfix: 2426274 - Orders with KIT items should not show 'INCLUDED'
| items, though their prices should be rolled up to
| the KIT level.
| 09/06/02 mannamra Bugfix: 2552417 - Notification shows two lines for a single line in
| order
| 09/27/02 batoleti Added Notify_End_Working notification procedure
| 10/01/02 batoleti Added Notify_Finish_Sharing notification procedure
| 10/04/02 batoleti Added Notify_Shared_Cart notification procedure
| 10/07/02 batoleti Added Notify_Access_Change notification procedure
| 12/12/02 SCHAK Bug # 2691704 Modified for NOCOPY Changes.
| 01/16/03 mannamra Enh : 2745338 - Added API set_item_attributes and identify_cart_quote
| Also included a fix to send access_change notif to B2C
| users.
| 07/24/03 batoleti Added NotifyReturnOrderStatus notification procedure
/ 8/26/03 abhandar changed getUserType(),Get_Name_Details()and NotifyRegistration()
/ Added Generate_Approval_Msg()
| 12/11/2003 3192506 IBE_USE_WORKFLOW profile Obsoletion
| 12/23/03 batoleti Bug#3313522 Commented the IF (display_type = 'text/plain' ) checking conditions.
| 12/23/03 batoleti Bug#3334542 Added billing address for B2C return order notifications.
| 12/29/03 batoleti Bug#3325710 Added view Netprice token to order confirmation notification
| sent to sharee.
| 12/30/03 mannamra Bug#3319902 View shared cart should display 'owner' as a role.
| 01/14/04 batoleti Bug#3342929 Added IBE_UTIL.nls_number_format function call for quantity and amount values
| in istore workflow notification procedures.
| 01/19/04 batoleti Bug#3378966 In Return order notification, the INCLUDED items are not shown.
| 01/29/04 batoleti Bug#3348583 In Return order notification, order number should not be shown for the
| child items.
| 02/05/04 mannamra Bug#3316860 HTML enabled tokens with PL/SQL callbacks.
| 30/11/04 Knachiap Bug#4031180 SetOrderId for OrderNotBooked Notification
| 06/12/04 Knachiap Bug#4031180 OrderConf Notification to Recipient
| 07/12/04 abairy Bug#4049509 Customised Approval Message Keys for Customised UserTypes
| 02/27/05 abairy Bug #4184705 removed wrong usage of organization party number as orgId
| 01/May/05 Knachiap MACD Notification Change for Cart/Checkout
| 06/02/05 abairy Added Generate_Credential_Msg procedure
| 07/18/05 banatara Added the m_site_type='I' check for the ibe_msites table cursors
| 07/21/05 Knachiap MOAC Fix
| 07/29/05 banatara Removed the m_site_type='I' check for the ibe_msites table cursors
| 19/08/05 Knachiap MOAC Changes
| 24/08/05 Knachiap MOAC Changes - Client Info
| 09/06/05 abairy Setting party number as the value for ORGNUM token in NotifyRegistration
| Changed Generate_credential_msg to only ignore password. Username shall always be shown.
| 14/Nov/05 Knachiap Line Type for Quote
| 16/Nov/05 Knachiap MACD Footer fixes
| 14/Dec/05 Knachiap 4774306 - SQL Perf Fixes
| 06/Jun/06 aannamal 5260544 - Fix to have negative amounts within angular brackets
| 19/Sep/06 aannamal 5480501 - Made changes to display correct line amount in Order confirmation Notification
| 24/Apr/08 ukalaiah 6877589 - WRONG WORKFLOW NOTIFICATION BEING FIRED FOR CREDIT CARD ORDERS
| 26/Jun/09 scnagara 8337371 - For NotifyReturnOrderStatus, passed the return order minisite id to
| IBE_WF_MSG_MAPPING_PVT.Retrieve_Msg_Mapping
| 1/Jul/09 scnagara 7720550 - For NotifyReturnOrderStatus and Notify_cancel_order,
| passed the current org Id to Retrieve_Msg_Mapping
| 23-AUG-2010 amaheshw 10016159 Replace IBY_TRXN_EXTENSIONS_V with IBY_EXTN_INSTR_DETAILS_VREM
| 23-MAY-2011 ytian 12573026 Modified buildDocument function to increase the VARCHAR2 size to 495.
| 07-MAY-2012 scnagara 13767382 procedure get_sales_assist_rsn_meaning - changed to use aso_lookups view
| 30-AUG-2012 avitiwar 12851105 - Istore Registration Page Application Error
==============================================================================================
*/
l_true VARCHAR2(1) := FND_API.G_TRUE;
Select Meaning
from oe_ship_methods_v
Where Lookup_code = pCode;
SELECT oh.ordered_date,
oh.order_number,
sold_to_party.party_name customer_name,
oe_totals_grp.Get_Order_Total(oh.header_id,null,'ALL')order_total,
oe_totals_grp.Get_Order_Total(oh.header_id,null,'CHARGES')charges_total,
oe_totals_grp.Get_Order_Total(oh.header_id,null,'TAXES')taxes_total,
oe_totals_grp.Get_Order_Total(oh.header_id,null,'LINES')lines_total,
sold_to_party.party_id,
oh.payment_type_code,
oh.cust_po_number,
substr(oh.credit_card_number,(length(oh.credit_card_number)-3),4)credit_card_number,
oh.orig_sys_document_ref web_confirm_number,
oh.shipping_method_code,
oh.minisite_id, -- bug 8337371, scnagara
shipaddr.address1 ship_to_address1,
shipaddr.address2 ship_to_address2,
shipaddr.address3 ship_to_address3,
shipaddr.address4 ship_to_address4,
shipaddr.city ship_to_city,
shipaddr.state ship_to_state,
shipaddr.postal_code ship_to_postal_code,
shipaddr.country ship_to_country,
invaddr.address1 bill_to_address1,
invaddr.address2 bill_to_address2,
invaddr.address3 bill_to_address3,
invaddr.address4 bill_to_address4,
invaddr.city bill_to_city,
invaddr.state bill_to_state,
invaddr.postal_code bill_to_postal_code,
invaddr.country bill_to_country,
oh.transactional_curr_code
from oe_order_headers_all oh,
hz_parties sold_to_party,
hz_cust_accounts sold_to_account,
hz_locations shipaddr,
hz_party_sites shp_party_site,
hz_cust_acct_sites_all shp_acct_site,
hz_cust_site_uses_all shp_site_use,
hz_locations invaddr,
hz_party_sites inv_party_site,
hz_cust_acct_sites_all inv_acct_site,
hz_cust_site_uses_all inv_site_use
where oh.header_id=c_order_id
and sold_to_party.party_id= sold_to_account.party_id
and sold_to_account.cust_account_id = oh.sold_to_org_id
and oh.ship_to_org_id=shp_site_use.site_use_id(+)
and shp_site_use.cust_acct_site_id=shp_acct_site.cust_acct_site_id(+)
and shp_acct_site.party_site_id=shp_party_site.party_site_id(+)
and shp_party_site.location_id=shipaddr.location_id(+)
and oh.invoice_to_org_id=inv_site_use.site_use_id(+)
and inv_site_use.cust_acct_site_id=inv_acct_site.cust_acct_site_id(+)
and inv_acct_site.party_site_id=inv_party_site.party_site_id(+)
and inv_party_site.location_id=invaddr.location_id(+);
SELECT ol.line_id,
ol.item_type_code,
ol.top_model_line_id,
ol.link_to_line_id,
msi.description item_description,
ol.ordered_quantity,
oe_totals_grp.Get_Order_Total(ol.header_id,ol.line_id,'LINES') lines_total,
oe_totals_grp.Get_Order_Total(ol.header_id,ol.line_id,'TAXES') taxes_total,
oe_totals_grp.Get_Order_Total(ol.header_id,ol.line_id,'CHARGES') charges_total,
oe_totals_grp.Get_Order_Total(ol.header_id,ol.line_id,'ALL') extended_price
FROM oe_order_lines_all ol, mtl_system_items_tl msi
WHERE ol.header_id = p_order_id
and ol.inventory_item_id = msi.inventory_item_id
and msi.organization_id = oe_profile.value('OE_ORGANIZATION_ID', ol.org_id)
and msi.language = userenv('LANG')
ORDER BY line_number, shipment_number,nvl(option_number,-1), nvl(component_number,-1), nvl(service_number,-1);
Select ORG_INFORMATION1 Contract_Rep,
ORG_INFORMATION2 Sales_Rep,
ORG_INFORMATION3 CustCare_Rep
From hr_organization_information
where org_information_context = 'DEFAULT_NOTIFICATION_USER'
And organization_id = p_org_id;
Select Contract_number,Contract_number_modifier
From okc_k_headers_b
Where ID = p_id;
SELECT org_id,
party_id,
quote_name,
quote_number,
quote_version,
quote_password,
cust_account_id,
invoice_to_party_id,
invoice_to_party_site_id,
quote_header_id,
ordered_date,
order_id,
total_list_price,
total_shipping_charge,
total_tax,
total_quote_price,
invoice_to_cust_account_id,
total_adjusted_amount,
currency_code,
resource_id
FROM aso_quote_headers_all
WHERE quote_header_id = p_quote_id;
SELECT Inventory_item_id,
Organization_id,
Quantity,
Line_quote_price,
currency_code
FROM Aso_quote_lines_all
WHERE quote_header_id = p_quote_id
ORDER BY line_number;
SELECT Payment_type_code
FROM Aso_Payments
WHERE quote_header_id = p_quote_id;
SELECT Party_Name,
Person_First_Name,
Person_Middle_Name,
Person_Last_name,
party_type,
Person_title
FROM hz_parties
WHERE party_id = c_party_id;
SELECT Contact_Point_type,
Primary_flag,
Phone_line_type,
Phone_Country_code,
Phone_area_code,
Phone_number,
Email_address
FROM hz_contact_points
WHERE owner_table_name = 'HZ_PARTIES'
AND owner_table_id = p_party_id;
select transactional_curr_code
from oe_order_headers_all
where header_id = c_order_id;
SELECT fc.symbol
FROM FND_CURRENCIES fc
WHERE fc.currency_code = p_currCode;
select quote_source_code
from aso_quote_headers_all
where quote_header_id = p_quote_id;
SELECT name,
notification_preference
FROM wf_roles
WHERE name = c_user;
select b.usertype_key
from jtf_um_usertype_reg a,jtf_um_usertypes_b b, fnd_user c , hz_parties d
where a.usertype_id=b.usertype_id and c.user_id = a.user_id
and d.party_id = c.customer_id and b.application_id=671
and d.party_id = c_party_id;
select d.party_type,c.user_name
from fnd_user c , hz_parties d
where d.party_id=c.customer_id
and d.party_id=c_party_id;
select resource_id
from aso_quote_headers_all
where quote_header_id = c_quote_header_id;
L_temp_update_str Varchar2(2000);
l_temp_update_str := 'IBE_PRMT_UPDATE_CART';
aname => 'UPDATEMSG_CODE',
avalue => l_temp_update_str);
IBE_UTIL.DEBUG('Set_item_attributes:Done setItemAttribute for UPDATEMSG');
SELECT person_first_name, person_last_name
FROM hz_parties
WHERE party_id = p_party_id;
SELECT p.person_first_name,
p.person_last_name,
p.party_id
FROM hz_relationships l,
hz_parties p
WHERE l.party_id = p_party_id
AND l.subject_id = p.party_id
AND l.subject_type = 'PERSON'
AND l.object_type = 'ORGANIZATION';
SELECT party_type, person_first_name, person_last_name,party_id
FROM hz_parties
WHERE party_id = p_party_id;
SELECT p.person_first_name,
p.person_last_name,
p.party_id
FROM hz_relationships l,
hz_parties p
WHERE l.party_id = p_party_id
AND l.subject_id = p.party_id
AND l.subject_type = 'PERSON'
AND l.object_type = 'ORGANIZATION';
Select USR.CUSTOMER_ID Name
From FND_USER USR
Where USR.EMPLOYEE_ID is null
and user_name = c_login_name;
Select SOURCE_FIRST_NAME, SOURCE_LAST_NAME
From JTF_RS_RESOURCE_EXTNS
Where RESOURCE_ID = c_resource_id;
Select USR.CUSTOMER_ID Name
From FND_USER USR
Where USR.EMPLOYEE_ID is null
and user_name = c_login_name;
select approval_id from jtf_um_usertypes_b
where usertype_key=c_usertype and (effective_end_date > sysdate or effective_end_date is null)
and rownum= 1 and application_id=671;
select b.party_number
from hz_parties a,hz_parties b,hz_relationships c
where a.party_id= c.party_id
and b.party_id= c.object_id
and c.subject_table_name = 'HZ_PARTIES'
and c.object_table_name = 'HZ_PARTIES'
and c.directional_flag = 'F'
and a.party_id=c_party_id;
SELECT USR.CUSTOMER_ID Name
FROM FND_USER USR
WHERE USR.EMPLOYEE_ID IS NULL
AND user_name = c_login_name;
SELECT msite_name
FROM ibe_msites_vl
WHERE msite_id = p_msite;
SELECT itev.card_number FROM IBY_EXTN_INSTR_DETAILS_V itev
WHERE itev.order_id = trim(to_char(l_order_id)) and instrument_type = 'CREDITCARD';
Select p.party_id Person_Party_id,
l.party_id contact_party_id,
p.person_first_name,
p.person_last_name,
p.party_type,
o.sold_to_contact_id
from oe_order_headers_all o,
hz_cust_Account_roles r,
hz_relationships l,
hz_parties p
where o.header_id = c_order_id
and o.sold_to_contact_id = r.cust_account_role_id
and r.party_id = l.party_id
and l.subject_id = p.party_id
and l.subject_type = 'PERSON'
and l.object_type = 'ORGANIZATION';
select p.party_id,
p.party_type,
p.person_first_name,
p.person_last_name,
p.person_middle_name
from hz_cust_accounts a,
oe_order_headers_all o,
hz_parties p
where o.sold_to_org_id = a.cust_account_id
and a.party_id = p.party_id
and o.header_id = c_order_id;
cursor c_last_updated_by(c_order_id number) is
select f.customer_id ,
o.sold_to_contact_id,
o.last_updated_by ,
p.person_first_name ,
p.person_middle_name,
p.person_last_name
from hz_parties p,
oe_order_headers_all o,
fnd_user f,
hz_relationships r
where o.last_updated_by = f.user_id
and f.customer_id = r.party_id
and r.subject_id = p.party_id
and r.subject_type = 'PERSON'
and r.object_type = 'ORGANIZATION'
and o.header_id = c_order_id;
rec_last_updated_by c_last_updated_by%rowtype;
FOR rec_last_updated_by in c_last_updated_by(p_order_id) LOOP
l_contact_party_id := rec_last_updated_by.customer_id;
l_contact_first_name := rec_last_updated_by.person_first_name;
l_contact_mid_name := rec_last_updated_by.person_middle_name;
l_contact_last_name := rec_last_updated_by.person_last_name;
EXIT when c_last_updated_by%NOTFOUND;
select order_number into l_order_num
from oe_order_headers_all i
where header_id = p_order_id;
l_cart_date ibe_quote_headers_v.last_update_date%TYPE;
select customer_id
from FND_USER
where user_id = FND_GLOBAL.USER_ID;
SELECT Employee_ID,user_name
INTO l_employee_id,l_user_name
FROM FND_USER
WHERE USER_ID = p_salesrep_user_id;
SELECT msite_name
FROM ibe_msites_vl
WHERE msite_id = p_msite;
select owner_table_name
from hz_contact_points
where contact_point_id = p_contact_point_id;
SELECT msite_name
FROM ibe_msites_vl
WHERE msite_id = p_msite;
select quote_name
from aso_quote_headers_all
where quote_header_id = (select quote_header_id
from ibe_sh_quote_access
where quote_sharee_number = c_retrieval_number);
select recipient_name
from ibe_sh_quote_access
where quote_sharee_number = c_retrieval_number;
Owner has deleted the Cart
Owner has placed the order
Owner has revoked the sharing of the cart
Owner has transferred the access of this shared cart.
*********************************************************/
PROCEDURE Notify_Finish_Sharing(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_quote_access_rec IN IBE_QUOTE_SAVESHARE_pvt.QUOTE_ACCESS_REC_TYPE, --of the recepient
p_minisite_id IN NUMBER,
p_url IN VARCHAR2,
p_context_code IN VARCHAR2,
p_shared_by_partyid IN NUMBER := FND_API.G_MISS_NUM,
p_notes IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_event_type VARCHAR2(20) := 'STOPWORK';
SELECT message_text
FROM fnd_new_messages
WHERE message_name = c_context_code
AND application_id = 671
AND language_code = userenv('LANG');
l_temp_update_str VARCHAR2(1000);
/* IF (p_quote_access_rec.UPDATE_PRIVILEGE_TYPE_CODE = 'A') THEN
l_accesslevel := 'Administrator';
ELSIF (p_quote_access_rec.UPDATE_PRIVILEGE_TYPE_CODE = 'F' OR
p_quote_access_rec.UPDATE_PRIVILEGE_TYPE_CODE = 'U' ) THEN
l_accesslevel := 'Participant';
,p_access_level => p_quote_access_rec.UPDATE_PRIVILEGE_TYPE_CODE
,p_recipient_number => p_quote_access_rec.quote_sharee_number
,p_first_name => l_sharedby_first_name
,p_last_name => l_sharedby_last_name
,p_url => p_url
,p_minisite_id => p_minisite_id
,p_cart_name => l_cart_name
,p_adhoc_role => l_adhoc_role
,p_notes => p_notes
,p_notif_context => l_notif_context);
l_temp_update_str VARCHAR2(1000);
select email_address
from HZ_CONTACT_POINTS
where contact_point_id = c_contact_point_id;
fnd_message.set_name('IBE','IBE_PRMT_UPDATE_CART');
l_temp_update_str := FND_API.G_MISS_CHAR;
l_temp_update_str := fnd_message.get;
,p_access_level => l_quote_access_rec.UPDATE_PRIVILEGE_TYPE_CODE
,p_old_access_level => P_old_accesslevel
,p_first_name => l_sharedby_first_name
,p_last_name => l_sharedby_last_name
,p_url => l_url
,p_minisite_id => p_minisite_id
,p_cart_name => l_cart_name
,p_adhoc_role => l_adhoc_role
,p_notes => p_notes
,p_notif_context => l_notif_context);
select quote_name
from aso_quote_headers_all
where quote_header_id = c_qte_header_id;
select email_address
from HZ_CONTACT_POINTS
where contact_point_id = c_contact_point_id;
l_access_code := p_quote_access_rec.UPDATE_PRIVILEGE_TYPE_CODE;
IBE_UTIL.DEBUG('Notify_view_shared_cart:ACCESSCODE: '||p_quote_access_rec.UPDATE_PRIVILEGE_TYPE_CODE);
aname => 'UPDATEMSG_CODE',
avalue => 'IBE_PRMT_UPDATE_CART');
Select p.party_id Person_Party_id,
l.party_id contact_party_id,
p.person_first_name,
p.person_last_name,
p.party_type
from oe_order_headers_all o,
hz_cust_Account_roles r,
hz_relationships l,
hz_parties p
where o.header_id = pOrder
and o.sold_to_contact_id = r.cust_account_role_id
and r.party_id = l.party_id
and l.subject_id = p.party_id
and l.subject_type = 'PERSON'
and l.object_type = 'ORGANIZATION';
SELECT msi.shippable_item_flag
FROM oe_order_lines_all line,
OE_SYSTEM_PARAMETERS_ALL osp,
mtl_system_items_kfv msi
WHERE line.line_id = p_line_id
AND line.org_id = osp.org_id
AND osp.master_organization_id = msi.organization_id
AND line.inventory_item_id = msi.inventory_item_id;
SELECT m.description,
u.unit_of_measure,
q.quote_line_id,
q.Quantity,
q.item_type_code,
q.Line_quote_price,
q.currency_code,
uom.unit_of_measure_tl charge_periodicity_desc,
m.shippable_item_flag,
sum(t.tax_amount) tax_amount,
qld.config_instance_name,
tran.name action,
qld.config_delta
FROM Aso_quote_lines_all q,
mtl_system_items_vl m,
aso_tax_details t,
mtl_units_of_measure u,
Aso_quote_line_details qld,
oe_transaction_types_tl tran,
mtl_units_of_measure_tl uom
WHERE q.inventory_item_id = m.inventory_item_id
and q.organization_id = m.organization_id
and t.quote_line_id(+) = q.quote_line_id
and u.uom_code = q.uom_code
and q.quote_header_id = p_quote_header_id --2548--6399
and q.item_type_code <> 'CFG'
and qld.quote_line_id(+) = q.quote_line_id
and tran.TRANSACTION_TYPE_ID(+) = q.order_line_type_id
and tran.language(+) = userenv('lang')
and uom.uom_code(+) = q.charge_periodicity_code
and uom.language(+) = userenv('lang')
and qld.ref_line_id is null
GROUP BY q.quote_line_id,
q.line_number,
m.description,
u.unit_of_measure,
q.Quantity,
q.item_type_code,
q.Line_quote_price,
q.charge_periodicity_code,
q.currency_code,
m.shippable_item_flag,
qld.config_instance_name,
tran.name,
uom.unit_of_measure_tl,
qld.config_delta
ORDER BY q.line_number;
SELECT m.description description,
u.unit_of_measure,
q.Quantity,
q.item_type_code,
q.Line_quote_price,
uom.unit_of_measure_tl charge_periodicity_desc,
m.shippable_item_flag,
m.bom_item_type,
m.config_model_type,
sum(t.tax_amount) tax_amount,
qld.config_instance_name,
tran.name action,
qld.config_delta
FROM ( SELECT related_quote_line_id,LEVEL depth
FROM aso_line_relationships
START WITH quote_line_id = p_parent_line_id
CONNECT BY quote_line_id =
PRIOR related_quote_line_id ) ALR,
Aso_quote_lines_all q,
mtl_system_items_vl m,
aso_tax_details t,
mtl_units_of_measure u,
Aso_quote_line_details qld,
oe_transaction_types_tl tran,
mtl_units_of_measure_tl uom
WHERE q.quote_line_id = ALR.related_quote_line_id and
q.inventory_item_id = m.inventory_item_id
and q.organization_id = m.organization_id
and t.quote_line_id(+) = q.quote_line_id
and u.uom_code = q.uom_code
and q.quote_header_id = p_quote_header_id -- 2548--6399
and qld.quote_line_id(+) = q.quote_line_id
and tran.TRANSACTION_TYPE_ID(+) = q.order_line_type_id
and tran.language(+) = userenv('lang')
and uom.uom_code(+) = q.charge_periodicity_code
and uom.language(+) = userenv('lang')
GROUP BY q.quote_line_id,
q.line_number,
m.description,
u.unit_of_measure,
q.Quantity,
q.item_type_code,
q.Line_quote_price,
q.charge_periodicity_code,
m.shippable_item_flag,
m.bom_item_type,
m.config_model_type,
qld.config_instance_name,
tran.name,
uom.unit_of_measure_tl,
qld.config_delta
ORDER BY q.line_number;
SELECT ol.item_type_code,
ol.top_model_line_id,
ol.link_to_line_id,
msi.description description,
cfgdtl.name config_instance_name,
linetyp.name action,
ol.order_quantity_uom unit_of_measure,
ol.ordered_quantity quantity,
msit1.UNIT_OF_MEASURE charge_periodicity_desc,
oe_totals_grp.Get_Order_Total(ol.header_id,ol.line_id,'LINES') lines_total,
oe_totals_grp.Get_Order_Total(ol.header_id,ol.line_id,'TAXES') taxes_total,
ol.shippable_flag,
decode(msi.config_model_type,'N','Y','N') model_container_flag,
cfgdtl.config_delta config_delta_flag
FROM oe_order_lines_all ol,
mtl_system_items_vl msi,
CZ_CONFIG_ITEMS cfgdtl,
mtl_units_of_measure_tl msit1,
oe_transaction_types_tl linetyp
WHERE ol.header_id = p_order_id
AND decode(ol.top_model_line_id,null,'1',decode(ol.top_model_line_id,ol.line_id,decode(ol.link_to_line_id,null,'1','2'),'2'))= '1'
and ol.inventory_item_id = msi.inventory_item_Id
and msit1.language(+) = userenv('LANG')
and ol.charge_periodicity_code = msit1.uom_code(+)
and msi.organization_id = oe_profile.value('OE_ORGANIZATION_ID', ol.org_id)
and ol.config_header_id = cfgdtl.config_hdr_id(+)
and ol.config_rev_nbr = cfgdtl.config_rev_nbr (+)
and ol.configuration_id = cfgdtl.config_item_id(+)
AND ol.line_type_id = linetyp.transaction_type_id
AND linetyp.language = userenv('LANG')
ORDER BY ol.line_number,
shipment_number,
nvl( option_number,-1),
nvl( component_number,-1),
nvl( service_number,-1);
SELECT ol.item_type_code,
ol.top_model_line_id,
ol.link_to_line_id,
msi.description description,
cfgdtl.name config_instance_name,
linetyp.name action,
ol.order_quantity_uom unit_of_measure,
ol.ordered_quantity quantity,
msit1.UNIT_OF_MEASURE charge_periodicity_desc,
oe_totals_grp.Get_Order_Total(ol.header_id,ol.line_id,'LINES') lines_total,
oe_totals_grp.Get_Order_Total(ol.header_id,ol.line_id,'TAXES') taxes_total,
ol.shippable_flag,
decode(msi.config_model_type,'N','Y','N') model_container_flag,
cfgdtl.config_delta config_delta_flag
FROM oe_order_lines_all ol,
mtl_system_items_vl msi,
CZ_CONFIG_ITEMS cfgdtl,
mtl_units_of_measure_tl msit1,
oe_transaction_types_tl linetyp
WHERE ol.header_id = p_order_id
and link_to_line_id is not null
and ( top_model_line_id = p_mdl_top_line_id)
and ol.inventory_item_id = msi.inventory_item_Id
and msit1.language(+) = userenv('LANG')
and ol.charge_periodicity_code = msit1.uom_code(+)
and msi.organization_id = oe_profile.value('OE_ORGANIZATION_ID', ol.org_id)
and ol.config_header_id = cfgdtl.config_hdr_id(+)
and ol.config_rev_nbr = cfgdtl.config_rev_nbr (+)
and ol.configuration_id = cfgdtl.config_item_id(+)
AND ol.line_type_id = linetyp.transaction_type_id
AND linetyp.language = userenv('LANG')
ORDER BY ol.line_number,
shipment_number,
nvl( option_number,-1),
nvl( component_number,-1),
nvl( service_number,-1);
SELECT source_line_id,
item_type_code,
orig_top_model_line_id,
orig_link_to_line_id,
item_description,
item_number,
source_order_number,
return_reason_code,
returned_quantity,
lines_total,
taxes_total,
charges_total
FROM ibe_return_detail_v
WHERE header_id = p_order_id
AND (orig_top_model_line_id is null or (orig_top_model_line_id = source_line_id and orig_link_to_line_id is null))
ORDER BY line_number,
shipment_number,
nvl( option_number,-1),
nvl( component_number,-1),
nvl( service_number,-1);
SELECT source_line_id,
item_type_code,
orig_top_model_line_id,
orig_link_to_line_id,
orig_item_type_code,
item_description,
item_number,
source_order_number,
return_reason_code,
returned_quantity,
lines_total,
taxes_total,
charges_total
FROM ibe_return_detail_v
WHERE header_id = p_order_id
and orig_link_to_line_id is not null and ( orig_top_model_line_id = to_number(p_mdl_source_line_id))
ORDER BY line_number,
shipment_number,
nvl( option_number,-1),
nvl( component_number,-1),
nvl( service_number,-1);
SELECT meaning
FROM ar_lookups
WHERE lookup_type = 'CREDIT_MEMO_REASON'
AND lookup_code = l_return_reason_code;
SELECT msi.shippable_item_flag
FROM oe_order_lines_all line, OE_SYSTEM_PARAMETERS_ALL osp,
mtl_system_items_kfv msi
WHERE line.line_id = p_line_id
AND line.org_id = osp.org_id
AND osp.master_organization_id = msi.organization_id
AND line.inventory_item_id = msi.inventory_item_id;
Select oe_totals_grp.Get_PayNow_Total(oh.header_id,null,'LINES') PayNow_lines_total,
oe_totals_grp.Get_PayNow_Total(oh.header_id,null,'CHARGES') PayNow_charges_total,
oe_totals_grp.Get_PayNow_Total(oh.header_id,null,'TAXES') PayNow_taxes_total,
oe_totals_grp.Get_PayNow_Total(oh.header_id,null,'ALL') PayNow_order_total,
oh.transactional_curr_code,
oe_totals_grp.Get_Order_Total(oh.header_id,null,'ALL') order_total,
oh.order_number
from oe_order_headers_all oh
where oh.header_id = c_order_id;
SELECT rtrim(address1) || ' ' || rtrim(address2) || ' ' || rtrim(address3) || ' ' || rtrim(address4) loc_address,
rtrim(city) loc_city,
rtrim(state)||'/' || rtrim(province) loc_state,
rtrim(postal_code) loc_zip,
rtrim(country) loc_country
FROM hz_locations
WHERE location_id = ( SELECT location_id
FROM hz_party_sites
WHERE party_site_id = p_loc_site_id);
SELECT ship_to_cust_account_id, ship_to_party_site_id, ship_to_party_id, ship_method_code
FROM aso_shipments
WHERE quote_header_id = p_quote_id
AND quote_line_id IS NULL
AND rownum = 1;
SELECT hc.party_id,hp.Party_Name,hp.Person_First_Name,hp.Person_Middle_Name,hp.Person_Last_name,hp.party_type
FROM hz_cust_accounts hc, hz_parties hp
WHERE cust_account_id = p_cust_account_id
AND hc.party_id = hp.party_id;
SELECT shippable_item_flag, rtrim(description) Description
FROM mtl_system_items_kfv
WHERE inventory_item_id = p_inv_item_id
AND organization_id = p_org_id;
select uom.unit_of_measure_tl charge_periodicity_desc,nvl(sum(ql.line_quote_price*ql.quantity),'0.00') rec_subtotal
from aso_quote_lines_all ql,mtl_units_of_measure_tl uom
where ql.quote_header_id = c_quote_id
and ql.charge_periodicity_code is not null
and uom.uom_code(+) = ql.charge_periodicity_code
and uom.language(+) = userenv('lang')
group by uom.unit_of_measure_tl;
select nvl(sum(ql.line_paynow_subtotal),'0.00')paynow_lines_total,nvl(sum(ql.line_paynow_charges),'0.00')paynow_charges_total,nvl(sum(ql.line_paynow_tax),'0.00')paynow_taxes_total,
nvl(sum(ql.line_paynow_subtotal + ql.line_paynow_charges + ql.line_paynow_tax),'0.00')paynow_order_total,
qh.total_quote_price quote_total,qh.currency_code
from aso_quote_lines_all ql,aso_quote_headers_all qh
where qh.quote_header_id = c_quote_id
and ql.quote_header_id = qh.quote_header_id
group by ql.quote_header_id,qh.total_quote_price,qh.currency_code;
PROCEDURE Selector(
itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
result OUT NOCOPY VARCHAR2
) IS
l_event_type VARCHAR2(50);
ibe_util.debug('Selector - Inside RUN- '||l_event_type);
ibe_util.debug('Selector - Inside order confirmation selection ');
ibe_util.debug('Selector - Inside order fax selection ');
ibe_util.debug('Selector - Inside order error selection ');
ibe_util.debug('Selector - Inside order cust quote selection ');
ibe_util.debug('Selector - Inside order sales quote selection ');
ibe_util.debug('Selector - Inside cust assist selection ');
ibe_util.debug('Selector - Inside sales assist selection ');
ibe_util.debug('Selector - Inside Term Apporved selection ');
ibe_util.debug('Selector - Inside Term Rejected selection ');
ibe_util.debug('Selector - Inside Term Cancelled selection ');
END Selector;
Select p.party_id Person_Party_id,
l.party_id contact_party_id,
p.person_first_name,
p.person_last_name,
p.party_type
from oe_order_headers_all o,
hz_cust_Account_roles r,
hz_relationships l,
hz_parties p
where o.header_id = c_order_id
and o.sold_to_contact_id = r.cust_account_role_id
and r.party_id = l.party_id
and l.subject_id = p.party_id
and l.subject_type = 'PERSON'
and l.object_type = 'ORGANIZATION';
Select p.person_first_name,
p.person_last_name,
p.person_title,
p.party_type
from hz_relationships l,
hz_parties p
where l.party_id = pPartyId
and l.subject_id = p.party_id
and l.subject_type = 'PERSON'
and l.object_type = 'ORGANIZATION';
Select p.party_id Person_Party_id,
l.party_id contact_party_id,
p.person_first_name,
p.person_last_name,
p.party_type
from oe_order_headers_all o,
hz_cust_Account_roles r,
hz_relationships l,
hz_parties p
where o.header_id = c_Order_id
and o.sold_to_contact_id = r.cust_account_role_id
and r.party_id = l.party_id
and l.subject_id = p.party_id
and l.subject_type = 'PERSON'
and l.object_type = 'ORGANIZATION';
Select p.person_first_name,
p.person_last_name,
p.person_title,
p.party_type
from hz_relationships l,
hz_parties p
where l.party_id = pPartyId
and l.subject_id = p.party_id
and l.subject_type = 'PERSON'
and l.object_type = 'ORGANIZATION';
Select p.party_id Person_Party_id,
l.party_id contact_party_id,
p.person_first_name,
p.person_last_name,
p.party_type,
p.person_title
from oe_order_headers_all o,
hz_cust_Account_roles r,
hz_relationships l,
hz_parties p
where o.header_id = c_order_id
and o.sold_to_contact_id = r.cust_account_role_id
and r.party_id = l.party_id
and l.subject_id = p.party_id
and l.subject_type = 'PERSON'
and l.object_type = 'ORGANIZATION';
Select p.person_first_name,
p.person_last_name,
p.person_title,
p.party_type
from hz_relationships l,
hz_parties p
where l.party_id = pPartyId
and l.subject_id = p.party_id
and l.subject_type = 'PERSON'
and l.object_type = 'ORGANIZATION';
SELECT DISTINCT language, territory, notification_preference, email_address
INTO l_lang_pref, l_terr_pref, l_notif_pref, l_email_addr
FROM wf_users
WHERE name = l_wf_user;
SELECT DISTINCT value
INTO l_sess_lang
FROM nls_session_parameters
WHERE parameter = 'NLS_LANGUAGE';
SELECT DISTINCT value
INTO l_sess_terr
FROM nls_session_parameters
WHERE parameter = 'NLS_TERRITORY';
SELECT DISTINCT value
INTO l_sess_lang
FROM nls_session_parameters
WHERE parameter = 'NLS_LANGUAGE';
SELECT DISTINCT value
INTO l_sess_terr
FROM nls_session_parameters
WHERE parameter = 'NLS_TERRITORY';
SELECT msite_name
FROM ibe_msites_vl
WHERE msite_id = c_msite_id;
select meaning
from fnd_lookups
where lookup_type = 'IBE_QUOTE_UPDATE_PRIVILEGE_WF'
and lookup_code = c_fnd_code;
select meaning
from aso_lookups /* Bug 13767382, scnagara - changed from fnd_lookups to aso_lookups*/
where lookup_type = 'ASO_SALESREP_ASSISTANCE_REASON'
and lookup_code = c_fnd_code;