采购术语PR、PO、RFQ、RFI、SOW、BOM、JIT、VMI、MRO 是什么意思_中华文本库
第1页/共1页
采购术语PR、PO、RFQ、RFI、SOW、BOM、JIT、VMI、MRO是什么意思
如：PR、PO、RFQ、RFI、SOW、BOM、JIT、VMI、MRO
PR (Purchase Requirent) 请购单，采购申请单，代表企业内部的申请需求
PO (Purchanse Order) 采购订单，客户下给供应商的
RFQ (Request For Quotation) 报价请求
RFI (Request For Information)
信息邀请书，用来取得产品，服务，或供应商一般资讯的请求文件 SOW (Statement Of Work)工作说明书，是对所要提供的产品或服务的叙述性的描述
BOM (Bill Of Material) 物料清单
JIT (Just In Time) 准时化供应（生产）
VMI (Vendor Manage Inventory) 供应商库存管理，即客户的原料属于供应商，并由供应商管理MRO(Maintenance, Repair and Operating) 指维修与作业耗材,也可解释为非生产性物资。
第1页/共1页
寻找更多 ""1435人阅读
OAF开发（8）
想在notification頁面把供應商的稅捐代碼帶出來，添在如下紅框中
PO_WF_PO_NOTIFICATION
head information:get_po_approve_msg
line information:get_po_lines_details
函數稍微修改一下，就OK
CREATE OR REPLACE PACKAGE BODY PO_WF_PO_NOTIFICATION AS
/* $Header: POXWPA7B.pls 115.38.5/03/22 10:15:55 nipagarw ship $ */
g_pkg_name VARCHAR2(30) := 'PO_WF_PO_NOTIFICATION'; -- &BUG 3607009&
PROCEDURE get_po_approve_msg(document_id
in varchar2,
display_type
in varchar2,
in out NOCOPY varchar2,
document_type in out NOCOPY varchar2) IS
l_item_type wf_items.item_type%TYPE;
l_item_key
wf_items.item_key%TYPE;
l_document_id
po_headers.po_header_id%TYPE;
po_headers.org_id%TYPE;
l_currency_code
fnd_currencies.CURRENCY_CODE%TYPE;
l_header_msg
VARCHAR2(500);
l_po_amount
VARCHAR2(30);
l_tax_amount
VARCHAR2(30);
l_description
ments%TYPE;
l_forwarded_from per_all_people_f.full_name%TYPE;
l_preparer
per_all_people_f.full_name%TYPE;
--&UTF-8 FPI START&
VARCHAR2(480);
/* & UTF8 FPI - changed from VARCHAR2(240) & */
l_note po_action_history.note%TYPE;
--&UTF-8 FPI END&
l_document VARCHAR2(32000) := '';
/* Start Bug# 3972475 */
X_min_acct_
/* End Bug# 3972475*/
l_supplier po_vendors.vendor_name% --Bug 4115777
/**--customize the po_approve_message,added by leone
--l_supplier_site_id po_vendor_sites_all.vendor_site_id%
l_tax_code varchar2(10);
/*****************************end*****************************/
l_supplier_site po_vendor_sites_all.vendor_site_code% --Bug 4115777
NL VARCHAR2(1) := fnd_global.
l_item_type := substr(document_id, 1, instr(document_id, ':') - 1);
l_item_key
:= substr(document_id,
instr(document_id, ':') + 1,
length(document_id) - 2);
l_document_id := wf_engine.GetItemAttrNumber(itemtype =& l_item_type,
=& l_item_key,
=& 'DOCUMENT_ID');
l_org_id := wf_engine.GetItemAttrNumber(itemtype =& l_item_type,
=& l_item_key,
=& 'ORG_ID');
fnd_client_info.set_org_context(to_char(l_org_id));
l_currency_code := wf_engine.GetItemAttrText(itemtype =& l_item_type,
=& l_item_key,
=& 'FUNCTIONAL_CURRENCY');
l_po_amount := wf_engine.GetItemAttrText(itemtype =& l_item_type,
=& l_item_key,
=& 'PO_AMOUNT_DSP');
l_tax_amount := wf_engine.GetItemAttrText(itemtype =& l_item_type,
=& l_item_key,
=& 'TAX_AMOUNT_DSP');
l_description := wf_engine.GetItemAttrText(itemtype =& l_item_type,
=& l_item_key,
=& 'PO_DESCRIPTION');
l_forwarded_from := wf_engine.GetItemAttrText(itemtype =& l_item_type,
=& l_item_key,
=& 'FORWARD_FROM_DISP_NAME');
l_preparer := wf_engine.GetItemAttrText(itemtype =& l_item_type,
=& l_item_key,
=& 'PREPARER_DISPLAY_NAME');
l_note := wf_engine.GetItemAttrText(itemtype =& l_item_type,
=& l_item_key,
=& 'NOTE');
--&Bug 4115777 Start& Show supplier and supplier site for
-- approval notifications
l_supplier := PO_WF_UTIL_PKG.GetItemAttrText(itemtype =& l_item_type,
=& l_item_key,
=& 'SUPPLIER');
l_supplier_site := PO_WF_UTIL_PKG.GetItemAttrText(itemtype =& l_item_type,
=& l_item_key,
=& 'SUPPLIER_SITE');
--&Bug 4115777 End&
/*in order to add tax code the message,customize the po_approve_message,added by leone */
select nvl(psa.vat_code, 'no_tax')
into l_tax_code
from po_vendors pv, po_vendor_sites_all psa
where pv.vendor_id = psa.vendor_id
and pv.vendor_name = l_supplier
and psa.vendor_site_code = l_supplier_
/**---end---**/
fnd_currency.get_info(l_currency_code,
X_precision,
X_ext_precision,
X_min_acct_unit);
/*Start Bug# 3972475 - replaced the below sql to get the tax amount
to account for canceled QTY. Also accounted for new order types introduced
in 11i10 that use amount instead of quantity (where quantity_ordered is null).
Since we are performing divide and multiply by operations we need rounding
logic based on the currency.
If we are using minimum accountable unit we apply:
rounded tax = round(tax/mau)*mau, otherwise
rounded tax = round(tax, precision)
Old tax select:
SELECT nvl(sum(nonrecoverable_tax), 0)
INTO l_tax_amt
FROM po_lines pol,
po_distributions pod
WHERE pol.po_header_id = l_document_id
AND pod.po_line_id = pol.po_line_
IF (x_min_acct_unit IS NOT NULL) AND (x_min_acct_unit && 0) THEN
SELECT sum(round(POD.nonrecoverable_tax *
decode(quantity_ordered,
(nvl(POD.amount_ordered, 0) -
nvl(POD.amount_cancelled, 0)) /
nvl(POD.amount_ordered, 1),
(nvl(POD.quantity_ordered, 0) -
nvl(POD.quantity_cancelled, 0)) /
nvl(POD.quantity_ordered, 1)) /
X_min_acct_unit) * X_min_acct_unit)
INTO l_tax_amt
FROM po_lines pol, po_distributions pod
WHERE pol.po_header_id = l_document_id
AND pod.po_line_id = pol.po_line_
SELECT sum(round(POD.nonrecoverable_tax *
decode(quantity_ordered,
(nvl(POD.amount_ordered, 0) -
nvl(POD.amount_cancelled, 0)) /
nvl(POD.amount_ordered, 1),
(nvl(POD.quantity_ordered, 0) -
nvl(POD.quantity_cancelled, 0)) /
nvl(POD.quantity_ordered, 1)),
X_precision))
INTO l_tax_amt
FROM po_lines pol, po_distributions pod
WHERE pol.po_header_id = l_document_id
AND pod.po_line_id = pol.po_line_
if (display_type = 'text/html') then
l_document := NL || NL || '&!-- PO_APPROVE_MSG --&' || NL || NL ||
'&P&';
l_document := l_document || '&/P&' || NL;
l_document := l_document ||
'&P&&TABLE border=0 cellpadding=0 cellspacing=0 SUMMARY=&&&&TR&&TD align=right &' || NL ||
fnd_message.get_string('PO', 'PO_WF_NOTIF_PO_AMOUNT') ||
'  &/TD&' || NL;
/*before modified
l_document := l_document || '&TD align=left&' || l_currency_code || ' ' ||
l_po_amount || '&/TD&&/TR&' || NL;*/
l_document := l_document || '&TD align=left&' || l_currency_code || ' ' ||
if l_tax_amt & 0 then
l_document := l_document || '(?祙)' || '&/TD&&/TR&' || NL; --new added
l_document := l_document || '&TR&&TD align=right&' ||
fnd_message.get_string('PO', 'PO_WF_NOTIF_TAX_AMOUNT') ||
'  &/TD&' || NL;
l_document := l_document || '&TD align=left&' || l_currency_code || ' ' ||
l_tax_amount || '(' || l_tax_code || ')' ||
'&/TD&&/TR&&/TABLE&&/P&' || NL;
l_document := l_document || '(ぃ?祙)' || '&/TD&&/TR&' || NL;--new added
l_document := l_document || '&/TABLE&&/P&' || NL || NL;
--&Bug 4115777 Start& Show supplier and supplier site for
-- approval notifications
l_document := l_document || '&P&' || NL;
l_document := l_document ||
fnd_message.get_string('PO', 'PO_FO_VENDOR') || ' ' ||
l_supplier || NL;
l_document := l_document || '&BR&' || NL;
l_document := l_document ||
fnd_message.get_string('PO',
'PO_WF_NOTIF_SUPPLIER_SITE') || ' ' ||
l_supplier_site || NL;
l_document := l_document || '&/P&' || NL;
--&Bug 4115777 End&
if l_description is not null then
l_document := l_document || '&P&' || NL;
l_document := l_document ||
fnd_message.get_string('PO',
'PO_WF_NOTIF_DOC_DESCRIPTION') || NL ||
'&BR&';
l_document := l_document || l_
l_document := l_document || '&BR&&/P&' || NL;
-- plain text notification is defined in the WF.
document := l_
PROCEDURE get_po_lines_details(document_id
in varchar2,
display_type
in varchar2,
in out NOCOPY varchar2,
document_type in out NOCOPY varchar2) IS
l_item_type wf_items.item_type%TYPE;
l_item_key
wf_items.item_key%TYPE;
l_document_id
po_lines.po_header_id%TYPE;
po_lines.org_id%TYPE;
l_document_type VARCHAR2(25);
l_document VARCHAR2(32000) := '';
l_currency_code fnd_currencies.currency_code%TYPE;
-- Bug 3668188: added new local var. note: the length of this
-- varchar was determined based on the length in POXWPA1B.pls,
-- which is the other place 'OPEN_FORM_COMMAND' attribute is used
l_open_form_command VARCHAR2(200);
NL VARCHAR2(1) := fnd_global.
NUMBER := 0;
max_lines_dsp
NUMBER := 20;
l_line_count
NUMBER := 0; -- &BUG 3616816& # lines/shipments on document
l_num_records_to_display NUMBER; -- &BUG 3616816& actual # of records to be displayed in table
VARCHAR2(240);
NUMBER := 0;
NUMBER := 0;
-- po lines cursor
-- &BUG 3616816 START& Declare TABLEs for each column that is selected
-- from po_line_csr and po_line_loc_csr.
TYPE line_num_tbl_type IS TABLE OF PO_LINES.line_num%TYPE;
TYPE shipment_num_tbl_type IS TABLE OF PO_LINE_LOCATIONS.shipment_num%TYPE;
TYPE item_num_tbl_type IS TABLE OF MTL_SYSTEM_ITEMS_KFV.concatenated_segments%TYPE;
TYPE item_revision_tbl_type IS TABLE OF PO_LINES.item_revision%TYPE;
TYPE item_desc_tbl_type IS TABLE OF PO_LINES.item_description%TYPE;
TYPE uom_tbl_type IS TABLE OF MTL_UNITS_OF_MEASURE.unit_of_measure_tl%TYPE;
TYPE quantity_tbl_type IS TABLE OF PO_LINES.quantity%TYPE;
TYPE unit_price_tbl_type IS TABLE OF PO_LINES.unit_price%TYPE;
TYPE amount_tbl_type IS TABLE OF PO_LINES.amount%TYPE;
TYPE location_tbl_type IS TABLE OF HR_LOCATIONS.location_code%TYPE;
TYPE organization_name_tbl_type IS TABLE OF ORG_ORGANIZATION_DEFINITIONS.organization_name%TYPE;
TYPE need_by_date_tbl_type IS TABLE OF PO_LINE_LOCATIONS.need_by_date%TYPE;
TYPE promised_date_tbl_type IS TABLE OF PO_LINE_LOCATIONS.promised_date%TYPE;
TYPE shipment_type_tbl_type IS TABLE OF PO_LINE_LOCATIONS.shipment_type%TYPE;
l_line_num_tbl
line_num_tbl_
l_shipment_num_tbl
shipment_num_tbl_
l_item_num_tbl
item_num_tbl_
l_item_revision_tbl item_revision_tbl_
l_item_desc_tbl
item_desc_tbl_
l_quantity_tbl
quantity_tbl_
l_unit_price_tbl
unit_price_tbl_
l_amount_tbl
amount_tbl_
l_location_tbl
location_tbl_
l_org_name_tbl
organization_name_tbl_
l_need_by_date_tbl
need_by_date_tbl_
l_promised_date_tbl promised_date_tbl_
l_shipment_type_tbl shipment_type_tbl_
-- &BUG 3616816 END&
/* Bug# 1419139: kagarwal
** Desc: The where clause pol.org_id = msi.organization_id(+) in the
** PO lines cursor, po_line_csr, is not correct as the pol.org_id
** is the operating unit which is not the same as the inventory
** organization_id.
** We need to use the financials_system_parameter table for the
** inventory organization_id.
** Also did the similar changes for the Release cursor,po_line_loc_csr.
/* Bug 2401933: sktiwari
Modifying cursor po_line_csr to return the translated UOM value
instead of unit_meas_lookup_code.
CURSOR po_line_csr(v_document_id NUMBER) IS
SELECT pol.line_num,
msi.concatenated_segments,
pol.item_revision,
pol.item_description,
pol.unit_meas_lookup_code, -- bug 2401933.remove
nvl(muom.unit_of_measure_tl, pol.unit_meas_lookup_code), -- bug 2401933.add
pol.quantity,
pol.unit_price,
nvl(pol.amount, pol.quantity * pol.unit_price)
FROM po_lines
mtl_system_items_kfv
mtl_units_of_measure
muom, -- bug 2401933.add
financials_system_parameters fsp
WHERE pol.po_header_id = v_document_id
AND pol.item_id = msi.inventory_item_id(+)
AND NVL(msi.organization_id, fsp.inventory_organization_id) =
fsp.inventory_organization_id
/* Bug 2299484 fixed. prevented the canceled lines to be displayed
in notifications.
AND NVL(pol.cancel_flag, 'N') = 'N'
AND muom.unit_of_measure(+) = pol.unit_meas_lookup_code -- bug 2401933.add
ORDER BY pol.line_
-- release shipments cursor
/* Bug# 1530303: kagarwal
** Desc: We need to change the where clause as the item
** may not be an inventory item. For this case we should
** have an outer join with the mtl_system_items_kfv.
** Changed the condition:
** pol.item_id = msi.inventory_item_id
** to pol.item_id = msi.inventory_item_id(+)
/* Bug# 1718725: kagarwal
** Desc: The unit of measure may be null at the shipment level
** hence in this case we need to get the uom from line level.
** Changed nvl(pll.unit_meas_lookup_code, pol.unit_meas_lookup_code)
/* Bug# 1770951: kagarwal
** Desc: For Releases we should consider the price_override on the shipments
** and not the price on the Blanket PO line as the shipment price could be
** different if the price override is enabled on the Blanket.
/* Bug 2401933: sktiwari
Modifying cursor po_line_loc_csr to return the translated UOM value
instead of unit_meas_lookup_code.
CURSOR po_line_loc_csr(v_document_id NUMBER) IS
SELECT pll.shipment_num,
msi.concatenated_segments,
pol.item_revision,
pol.item_description,
-- Bug 2401933.start
nvl(pll.unit_meas_lookup_code, pol.unit_meas_lookup_code)
unit_meas_lookup_code,
nvl(muom.unit_of_measure_tl, pol.unit_meas_lookup_code),
-- Bug 2401933.end
pll.quantity,
nvl(pll.price_override, pol.unit_price) unit_price,
hrl.location_code,
ood.organization_name,
pll.need_by_date,
pll.promised_date,
pll.shipment_type
FROM po_lines
po_line_locations
mtl_system_items_kfv
hr_locations_all
hz_locations
org_organization_definitions ood,
mtl_units_of_measure
muom, -- Bug 2401933.add
financials_system_parameters fsp
where PLL.PO_RELEASE_ID = v_document_id
and PLL.po_line_id = POL.po_line_id
and PLL.ship_to_location_id = HRL.location_id(+)
and PLL.ship_to_location_id = HZ.location_id(+)
and PLL.ship_to_organization_id = OOD.organization_id
and pol.item_id = msi.inventory_item_id(+)
and NVL(msi.organization_id, fsp.inventory_organization_id) =
fsp.inventory_organization_id
/* Bug 2299484 fixed. prevented the canceled shipments to be displayed
in notifications.
AND NVL(PLL.cancel_flag, 'N') = 'N'
AND muom.unit_of_measure(+) = pol.unit_meas_lookup_code -- Bug 2401933.add
order by Shipment_
l_item_type := substr(document_id, 1, instr(document_id, ':') - 1);
l_item_key
:= substr(document_id,
instr(document_id, ':') + 1,
length(document_id) - 2);
/* Bug# 2353153
** Setting application context
PO_REQAPPROVAL_INIT1.Set_doc_mgr_context(l_item_type, l_item_key);
l_document_id := wf_engine.GetItemAttrNumber(itemtype =& l_item_type,
=& l_item_key,
=& 'DOCUMENT_ID');
l_org_id := wf_engine.GetItemAttrNumber(itemtype =& l_item_type,
=& l_item_key,
=& 'ORG_ID');
l_document_type := wf_engine.GetItemAttrText(itemtype =& l_item_type,
=& l_item_key,
=& 'DOCUMENT_TYPE');
fnd_client_info.set_org_context(to_char(l_org_id));
/* Bug# 1686066: kagarwal
** Desc: Use the functional currency of the PO for the precision of
** line amounts.
l_currency_code := wf_engine.GetItemAttrText(itemtype =& l_item_type,
=& l_item_key,
=& 'FUNCTIONAL_CURRENCY');
-- Bug 3668188
l_open_form_command := wf_engine.GetItemAttrText(itemtype =& l_item_type,
=& l_item_key,
=& 'OPEN_FORM_COMMAND');
/* Bug# 2668222: kagarwal
** Desc: Using profile PO_NOTIF_LINES_LIMIT to get the maximum
** number of PO lines to be displayed in Approval notification.
** The same profile is also used for Requisitions.
max_lines_dsp := to_number(fnd_profile.value('PO_NOTIF_LINES_LIMIT'));
if max_lines_dsp is NULL then
max_lines_dsp := 20;
-- &BUG 3616816 START& Fetch Release Shipments/PO Lines data into Tables.
IF (l_document_type = 'RELEASE') THEN
OPEN po_line_loc_csr(l_document_id);
FETCH po_line_loc_csr BULK COLLECT
INTO l_shipment_num_tbl, l_item_num_tbl, l_item_revision_tbl, l_item_desc_tbl, l_uom_tbl, l_quantity_tbl, l_unit_price_tbl, l_location_tbl, l_org_name_tbl, l_need_by_date_tbl, l_promised_date_tbl, l_shipment_type_
l_line_count := po_line_loc_csr%ROWCOUNT; -- Get # of records fetched.
CLOSE po_line_loc_
OPEN po_line_csr(l_document_id);
FETCH po_line_csr BULK COLLECT
INTO l_line_num_tbl, l_item_num_tbl, l_item_revision_tbl, l_item_desc_tbl, l_uom_tbl, l_quantity_tbl, l_unit_price_tbl, l_amount_
l_line_count := po_line_csr%ROWCOUNT; -- Get # of records fetched.
CLOSE po_line_
-- &BUG 3616816 END&
-- &BUG 3616816 START& Determine the actual number of records to display
-- in the table.
IF (l_line_count & max_lines_dsp) THEN
l_num_records_to_display := max_lines_
l_num_records_to_display := l_line_
-- &BUG 3616816 END&
if (display_type = 'text/html') then
if (nvl(l_document_type, 'PO') && 'RELEASE') then
l_document := NL || NL || '&!-- PO_LINE_DETAILS --&' || NL || NL ||
'&P&&B&';
l_document := l_document ||
fnd_message.get_string('PO',
'PO_WF_NOTIF_PO_LINE_DETAILS');
l_document := l_document || '&/B&' || NL || '&P&'; -- &BUG 3616816&
-- &BUG 3616816 START& Only display message if # of actual lines is
-- greater than maximum limit.
IF (l_line_count & max_lines_dsp) THEN
-- Bug 3668188: changed the code check (originally created
-- in bug 3607009) that determines which message to show
-- based on whether Open Document icon is shown in the notif.
-- The value of WF attribute 'OPEN_FORM_COMMAND' is set in a
-- previous node, using the get_po_user_msg_attribute procedure.
IF (l_open_form_command IS NULL) THEN
-- &The first [COUNT] Purchase Order lines are summarized
-- below. For information on additional lines, please click
-- the Open Document icon.&
FND_MESSAGE.set_name('PO', 'PO_WF_NOTIF_PO_LINE_MESG');
-- &The first [COUNT] Purchase Order lines are summarized below.&
FND_MESSAGE.set_name('PO', 'PO_WF_NOTIF_PO_LINE_MESG_TRUNC');
FND_MESSAGE.set_token('COUNT', to_char(max_lines_dsp));
:= FND_MESSAGE.
l_document := l_document || line_mesg || '&P&';
-- &BUG 3616816 END&
l_document := l_document || NL ||
'&TABLE border=1 cellpadding=2 cellspacing=1 summary=&' ||
fnd_message.get_string('ICX',
'ICX_POR_TBL_PO_TO_APPROVE_SUM') ||
'&& ' || NL;
l_document := l_document || '&TR&' || NL;
l_document := l_document || '&TH
id=&lineNum_1&&' ||
fnd_message.get_string('PO',
'PO_WF_NOTIF_LINE_NUMBER') ||
'&/TH&' || NL;
l_document := l_document || '&TH
id=&itemNum_1&&' ||
fnd_message.get_string('PO',
'PO_WF_NOTIF_ITEM_NUMBER') ||
'&/TH&' || NL;
l_document := l_document || '&TH
id=&itemRev_1&&' ||
fnd_message.get_string('PO',
'PO_WF_NOTIF_ITEM_REVISION') ||
'&/TH&' || NL;
l_document := l_document || '&TH
id=&itemDesc_1&&' ||
fnd_message.get_string('PO', 'PO_WF_NOTIF_ITEM_DESC') ||
'&/TH&' || NL;
l_document := l_document || '&TH
id=&uom_1&&' ||
fnd_message.get_string('PO', 'PO_WF_NOTIF_UOM') ||
'&/TH&' || NL;
l_document := l_document || '&TH
id=&quant_1&&' ||
fnd_message.get_string('PO', 'PO_WF_NOTIF_QUANTITY') ||
'&/TH&' || NL;
l_document := l_document || '&TH
id=&unitPrice_1&&' ||
fnd_message.get_string('PO', 'PO_WF_NOTIF_UNIT_PRICE') ||
'&/TH&' || NL;
l_document := l_document || '&TH
id=&lineAmt_1&&' ||
fnd_message.get_string('PO',
'PO_WF_NOTIF_LINE_AMOUNT') ||
'&/TH&' || NL;
l_document := l_document || '&/TR&' || NL;
:= lengthb(l_document);
prior_len := curr_
FOR i IN 1 .. l_num_records_to_display LOOP
-- &BUG 3616816&
/* Exit the cursor if the current document length and 2 times the
** length added in prior line exceeds 32000 char */
if (curr_len + (2 * (curr_len - prior_len))) &= 32000 then
prior_len := curr_
l_document := l_document || '&TR&' || NL;
l_document := l_document ||
'&TD nowrap align=center headers=&lineNum_1&&' ||
nvl(to_char(l_line_num_tbl(i)), ' ') || '&/TD&' || NL;
l_document := l_document || '&TD nowrap headers=&itemNum_1&&' ||
nvl(l_item_num_tbl(i), ' ') || '&/TD&' || NL;
l_document := l_document || '&TD nowrap headers=&itemRev_1&&' ||
nvl(l_item_revision_tbl(i), ' ') || '&/TD&' || NL;
l_document := l_document || '&TD nowrap headers=&itemDesc_1&&' ||
nvl(l_item_desc_tbl(i), ' ') || '&/TD&' || NL;
l_document := l_document || '&TD nowrap headers=&uom_1&&' ||
nvl(l_uom_tbl(i), ' ') || '&/TD&' || NL;
l_document := l_document ||
'&TD nowrap align=right headers=&quant_1&&' ||
nvl(to_char(l_quantity_tbl(i)), ' ') || '&/TD&' || NL;
/* Bug 2868931: kagarwal
** We will not format the unit price on the lines in notifications
-- Bug 3547777. Added the nvl clauses to unit_price and line_
-- amount so that box is still displayed even if value is null.
l_document := l_document ||
'&TD nowrap align=right headers=&unitPrice_1&&' ||
nvl(TO_CHAR(l_unit_price_tbl(i)), ' ') ||
'&/TD&' || NL;
l_document := l_document ||
'&TD nowrap align=right headers=&lineAmt_1&&' ||
nvl(TO_CHAR(l_amount_tbl(i),
FND_CURRENCY.GET_FORMAT_MASK(l_currency_code,
' ') || '&/TD&' || NL;
l_document := l_document || '&/TR&' || NL;
curr_len := lengthb(l_document);
-- release
l_document := NL || NL || '&!-- RELEASE_SHIPMENT_DETAILS --&' || NL || NL ||
'&P&&B&';
l_document := l_document ||
fnd_message.get_string('PO',
'PO_WF_NOTIF_SHIP_DETAILS');
l_document := l_document || '&/B&' || NL || '&P&';
-- &BUG 3616816 START& Only display message if # of actual lines is
-- greater than maximum limit.
IF (l_line_count & max_lines_dsp) THEN
FND_MESSAGE.set_name('PO', 'PO_WF_NOTIF_PO_REL_SHIP_MESG');
FND_MESSAGE.set_token('COUNT', to_char(max_lines_dsp));
:= FND_MESSAGE.
l_document := l_document || line_mesg || '&P&';
-- &BUG 3616816 END&
l_document := l_document ||
'&TABLE border=1 cellpadding=2 cellspacing=1 summary=&' ||
fnd_message.get_string('ICX',
'ICX_POR_TBL_BL_TO_APPROVE_SUM') ||
'&& ' || NL;
l_document := l_document || '&TR&' || NL;
l_document := l_document || '&TH
id=&shipNum_2&&' ||
fnd_message.get_string('PO',
'PO_WF_NOTIF_SHIP_NUMBER') ||
'&/TH&' || NL;
l_document := l_document || '&TH
id=&itemNum_2&&' ||
fnd_message.get_string('PO',
'PO_WF_NOTIF_ITEM_NUMBER') ||
'&/TH&' || NL;
l_document := l_document || '&TH
id=&itemRev_2&&' ||
fnd_message.get_string('PO',
'PO_WF_NOTIF_ITEM_REVISION') ||
'&/TH&' || NL;
l_document := l_document || '&TH
id=&itemDesc_2&&' ||
fnd_message.get_string('PO', 'PO_WF_NOTIF_ITEM_DESC') ||
'&/TH&' || NL;
l_document := l_document || '&TH
id=&uom_2&&' ||
fnd_message.get_string('PO', 'PO_WF_NOTIF_UOM') ||
'&/TH&' || NL;
l_document := l_document || '&TH
id=&quant_2&&' ||
fnd_message.get_string('PO', 'PO_WF_NOTIF_QUANTITY') ||
'&/TH&' || NL;
l_document := l_document || '&TH
id=&unitPrice_2&&' ||
fnd_message.get_string('PO', 'PO_WF_NOTIF_UNIT_PRICE') ||
'&/TH&' || NL;
l_document := l_document || '&TH
id=&location_2&&' ||
fnd_message.get_string('PO', 'PO_WF_NOTIF_LOCATION') ||
'&/TH&' || NL;
l_document := l_document || '&TH
id=&shipToOrg_2&&' ||
fnd_message.get_string('PO', 'POA_SHIP_TO_ORG') ||
'&/TH&' || NL;
l_document := l_document || '&TH
id=&needByDate_2&&' ||
fnd_message.get_string('PO',
'PO_WF_NOTIF_NEED_BY_DATE') ||
'&/TH&' || NL;
l_document := l_document || '&/TR&' || NL;
:= lengthb(l_document);
prior_len := curr_
FOR i IN 1 .. l_num_records_to_display LOOP
-- &BUG 3616816&
/* Exit the cursor if the current document length and 2 times the
** length added in prior line exceeds 32000 char */
if (curr_len + (2 * (curr_len - prior_len))) &= 32000 then
prior_len := curr_
l_document := l_document || '&TR&' || NL;
l_document := l_document ||
'&TD nowrap align=center headers=&shipNum_2&&' ||
nvl(to_char(l_shipment_num_tbl(i)), ' ') ||
'&/TD&' || NL;
l_document := l_document || '&TD nowrap
headers=&itemNum_2&&' ||
nvl(l_item_num_tbl(i), ' ') || '&/TD&' || NL;
l_document := l_document || '&TD nowrap
headers=&itemRev_2&&' ||
nvl(l_item_revision_tbl(i), ' ') || '&/TD&' || NL;
l_document := l_document || '&TD nowrap
headers=&itemDesc_2&&' ||
nvl(l_item_desc_tbl(i), ' ') || '&/TD&' || NL;
l_document := l_document || '&TD nowrap
headers=&uom_2&&' ||
nvl(l_uom_tbl(i), ' ') || '&/TD&' || NL;
l_document := l_document ||
'&TD nowrap align=right
headers=&quant_2&&' ||
nvl(to_char(l_quantity_tbl(i)), ' ') || '&/TD&' || NL;
/* Bug 2868931: kagarwal
** We will not format the unit price on the lines in notifications
l_document := l_document ||
'&TD nowrap align=right
headers=&unitPrice_2&&' ||
TO_CHAR(l_unit_price_tbl(i)) || '&/TD&' || NL;
l_document := l_document || '&TD nowrap
headers=&location_2&&' ||
nvl(l_location_tbl(i), ' ') || '&/TD&' || NL;
l_document := l_document || '&TD nowrap
headers=&shipToOrg_2&&' ||
nvl(l_org_name_tbl(i), ' ') || '&/TD&' || NL;
l_document := l_document || '&TD nowrap
headers=&needByDate_2&&' ||
to_char(l_need_by_date_tbl(i)) || '&/TD&' || NL;
l_document := l_document || '&/TR&' || NL;
curr_len := lengthb(l_document);
l_document := l_document || '&/TABLE&&/P&' || NL;
document := l_
elsif (display_type = 'text/plain') then
if (nvl(l_document_type, 'PO') && 'RELEASE') then
l_document := l_document ||
fnd_message.get_string('PO',
'PO_WF_NOTIF_PO_LINE_DETAILS') || NL || NL;
-- &BUG 3616816 START& Only display message if # of actual lines is
-- greater than maximum limit.
IF (l_line_count & max_lines_dsp) THEN
-- Bug 3668188: changed the code check (originally created
-- in bug 3607009) that determines which message to show
-- based on whether Open Document icon is shown in then notif.
-- The value of WF attribute 'OPEN_FORM_COMMAND' is set in a
-- previous node, using the get_po_user_msg_attribute procedure.
IF (l_open_form_command IS NULL) THEN
-- &The first [COUNT] Purchase Order lines are summarized
-- below. For information on additional lines, please click
-- the Open Document icon.&
FND_MESSAGE.set_name('PO', 'PO_WF_NOTIF_PO_LINE_MESG');
-- &The first [COUNT] Purchase Order lines are summarized below.&
FND_MESSAGE.set_name('PO', 'PO_WF_NOTIF_PO_LINE_MESG_TRUNC');
FND_MESSAGE.set_token('COUNT', to_char(max_lines_dsp));
:= FND_MESSAGE.
l_document := l_document || line_mesg || NL || NL;
-- &BUG 3616816 END&
:= lengthb(l_document);
prior_len := curr_
FOR i IN 1 .. l_num_records_to_display LOOP
-- &BUG 3616816&
/* Exit the cursor if the current document length and 2 times the
** length added in prior line exceeds 32000 char */
if (curr_len + (2 * (curr_len - prior_len))) &= 32000 then
prior_len := curr_
l_document := l_document ||
fnd_message.get_string('PO',
'PO_WF_NOTIF_LINE_NUMBER') || ':' ||
to_char(l_line_num_tbl(i)) || NL;
l_document := l_document ||
fnd_message.get_string('PO',
'PO_WF_NOTIF_ITEM_NUMBER') || ': ' ||
l_item_num_tbl(i) || NL;
l_document := l_document ||
fnd_message.get_string('PO',
'PO_WF_NOTIF_ITEM_REVISION') || ': ' ||
l_item_revision_tbl(i) || NL;
l_document := l_document ||
fnd_message.get_string('PO',
'PO_WF_NOTIF_ITEM_DESC') || ': ' ||
l_item_desc_tbl(i) || NL;
l_document := l_document ||
fnd_message.get_string('PO', 'PO_WF_NOTIF_UOM') || ': ' ||
l_uom_tbl(i) || NL;
l_document := l_document ||
fnd_message.get_string('PO', 'PO_WF_NOTIF_QUANTITY') || ': ' ||
to_char(l_quantity_tbl(i)) || NL;
/* Bug 2868931: kagarwal
** We will not format the unit price on the lines in notifications
l_document := l_document ||
fnd_message.get_string('PO',
'PO_WF_NOTIF_UNIT_PRICE') || ': ' ||
to_char(l_unit_price_tbl(i)) || NL;
l_document := l_document ||
fnd_message.get_string('PO',
'PO_WF_NOTIF_LINE_AMOUNT') || ': ' ||
to_char(l_amount_tbl(i),
FND_CURRENCY.GET_FORMAT_MASK(l_currency_code,
30)) || NL || NL;
curr_len := lengthb(l_document);
-- release
l_document := l_document ||
fnd_message.get_string('PO',
'PO_WF_NOTIF_SHIP_DETAILS') || NL || NL || NL;
-- &BUG 3616816 START& Only display message if # of actual lines is
-- greater than maximum limit.
IF (l_line_count & max_lines_dsp) THEN
FND_MESSAGE.set_name('PO', 'PO_WF_NOTIF_PO_REL_SHIP_MESG');
FND_MESSAGE.set_token('COUNT', to_char(max_lines_dsp));
:= FND_MESSAGE.
l_document := l_document || line_mesg || NL || NL;
-- &BUG 3616816 END&
:= lengthb(l_document);
prior_len := curr_
FOR i IN 1 .. l_num_records_to_display LOOP
-- &BUG 3616816&
/* Exit the cursor if the current document length and 2 times the
** length added in prior line exceeds 32000 char */
if (curr_len + (2 * (curr_len - prior_len))) &= 32000 then
prior_len := curr_
l_document := l_document ||
fnd_message.get_string('PO',
'PO_WF_NOTIF_SHIP_NUMBER') || ': ' ||
to_char(l_shipment_num_tbl(i)) || NL;
l_document := l_document ||
fnd_message.get_string('PO',
'PO_WF_NOTIF_ITEM_NUMBER') || ': ' ||
l_item_num_tbl(i) || NL;
l_document := l_document ||
fnd_message.get_string('PO',
'PO_WF_NOTIF_ITEM_REVISION') || ': ' ||
l_item_revision_tbl(i) || NL;
l_document := l_document ||
fnd_message.get_string('PO',
'PO_WF_NOTIF_ITEM_DESC') || ': ' ||
l_item_desc_tbl(i) || NL;
l_document := l_document ||
fnd_message.get_string('PO', 'PO_WF_NOTIF_UOM') || ': ' ||
l_uom_tbl(i) || NL;
l_document := l_document ||
fnd_message.get_string('PO', 'PO_WF_NOTIF_QUANTITY') || ': ' ||
to_char(l_quantity_tbl(i)) || NL;
/* Bug 2868931: kagarwal
** We will not format the unit price on the lines in notifications
l_document := l_document ||
fnd_message.get_string('PO',
'PO_WF_NOTIF_UNIT_PRICE') || ': ' ||
to_char(l_unit_price_tbl(i)) || NL;
l_document := l_document ||
fnd_message.get_string('PO',
'PO_WF_NOTIF_LINE_AMOUNT') || ': ' ||
to_char(l_amount_tbl(i),
FND_CURRENCY.GET_FORMAT_MASK(l_currency_code,
30)) || NL;
l_document := l_document ||
fnd_message.get_string('PO', 'PO_WF_NOTIF_LOCATION') || ': ' ||
l_location_tbl(i) || NL;
l_document := l_document ||
fnd_message.get_string('PO', 'POA_SHIP_TO_ORG') || ': ' ||
l_org_name_tbl(i) || NL;
l_document := l_document ||
fnd_message.get_string('PO',
'PO_WF_NOTIF_NEED_BY_DATE') || ': ' ||
to_char(l_need_by_date_tbl(i)) || NL || NL;
curr_len := lengthb(l_document);
document := l_ -- Bug 2462005
END get_po_lines_
PROCEDURE get_action_history(document_id
in varchar2,
display_type
in varchar2,
in out NOCOPY varchar2,
document_type in out NOCOPY varchar2) IS
l_item_type wf_items.item_type%TYPE;
l_item_key
wf_items.item_key%TYPE;
l_document_id
po_lines.po_header_id%TYPE;
po_lines.org_id%TYPE;
l_doc_type_code VARCHAR2(80);
l_document VARCHAR2(32000) := '';
-- Bug 3668188: added new local var. note: the length of this
-- varchar was determined based on the length in POXWPA1B.pls,
-- which is the other place 'OPEN_FORM_COMMAND' attribute is used
l_open_form_command VARCHAR2(200);
NL VARCHAR2(1) := fnd_global.
-- &BUG 3616816 START& Declare TABLEs for each column that is selected
-- from history_csr cursor.
TYPE sequence_num_tbl_type IS TABLE OF PO_ACTION_HISTORY.sequence_num%TYPE;
TYPE full_name_tbl_type IS TABLE OF PER_ALL_PEOPLE_F.full_name%TYPE;
TYPE displayed_field_tbl_type IS TABLE OF PO_LOOKUP_CODES.displayed_field%TYPE;
TYPE action_date_tbl_type IS TABLE OF PO_ACTION_HISTORY.action_date%TYPE;
TYPE note_tbl_type IS TABLE OF PO_ACTION_HISTORY.note%TYPE;
TYPE object_revision_num_tbl_type IS TABLE OF PO_ACTION_HISTORY.object_revision_num%TYPE;
TYPE employee_id_tbl_type IS TABLE OF PO_ACTION_HISTORY.employee_id%TYPE;
TYPE created_by_tbl_type IS TABLE OF PO_ACTION_HISTORY.created_by%TYPE;
l_sequence_num_tbl
sequence_num_tbl_
l_employee_name_tbl
full_name_tbl_
l_action_tbl
displayed_field_tbl_
l_action_date_tbl
action_date_tbl_
l_note_tbl
l_object_revision_num_tbl object_revision_num_tbl_
l_employee_id_tbl
employee_id_tbl_
l_created_by_tbl
created_by_tbl_
-- &BUG 3616816 END&
--SQL What: Query action history which is updated by both buyer and vendor
--SQL Why:
Since vendor doesn't have employee id,
CURSOR history_csr(v_document_id NUMBER, v_doc_type_code VARCHAR2) IS
SELECT poh.SEQUENCE_NUM,
per.FULL_NAME,
polc.DISPLAYED_FIELD,
poh.ACTION_DATE,
poh.OBJECT_REVISION_NUM,
poh.employee_id, /* bug 2788683 */
poh.created_by /* bug 2788683 */
from po_action_history poh,
per_all_people_f
per, -- Bug 3404451
po_lookup_codes
where OBJECT_TYPE_CODE = v_doc_type_code
and poh.action_code = polc.lookup_code
and POLC.LOOKUP_TYPE IN ('APPROVER ACTIONS', 'CONTROL ACTIONS')
and per.person_id(+) = poh.employee_id /* bug 2788683 */
and trunc(sysdate) between per.effective_start_date(+) and
per.effective_end_date(+)
and OBJECT_ID = v_document_id
SELECT poh.SEQUENCE_NUM,
per.FULL_NAME,
poh.ACTION_DATE,
poh.OBJECT_REVISION_NUM,
poh.employee_id, /* bug 2788683 */
poh.created_by /* bug 2788683 */
from po_action_history poh, per_all_people_f per -- Bug 3404451
where OBJECT_TYPE_CODE = v_doc_type_code
and poh.action_code is null
and per.person_id(+) = poh.employee_id /* bug 2788683 */
and trunc(sysdate) between per.effective_start_date(+) and
per.effective_end_date(+)
and OBJECT_ID = v_document_id
order by 1
NUMBER := 0;
max_actions_dsp
NUMBER := 20;
l_action_count
NUMBER; -- &BUG 3616816& # of action history records
l_num_records_to_display NUMBER; -- &BUG 3616816& actual # of records to display in table
action_mesg
VARCHAR2(240);
NUMBER := 0;
NUMBER := 0;
/* Bug 2788683 start */
l_user_name
fnd_user.user_name%TYPE;
l_vendor_name hz_parties.party_name%TYPE;
l_party_name
hz_parties.party_name%TYPE;
/* Bug 2788683 end */
l_item_type := substr(document_id, 1, instr(document_id, ':') - 1);
l_item_key
:= substr(document_id,
instr(document_id, ':') + 1,
length(document_id) - 2);
l_document_id := wf_engine.GetItemAttrNumber(itemtype =& l_item_type,
=& l_item_key,
=& 'DOCUMENT_ID');
l_org_id := wf_engine.GetItemAttrNumber(itemtype =& l_item_type,
=& l_item_key,
=& 'ORG_ID');
l_doc_type_code := wf_engine.GetItemAttrText(itemtype =& l_item_type,
=& l_item_key,
=& 'DOCUMENT_TYPE');
fnd_client_info.set_org_context(to_char(l_org_id));
-- Bug 3668188
l_open_form_command := wf_engine.GetItemAttrText(itemtype =& l_item_type,
=& l_item_key,
=& 'OPEN_FORM_COMMAND');
/* Bug# 2577478: kagarwal
** Desc: Added a new attribute ACT_HST_IN_NTF in wf definition for
** users to specify the number of PO actions to be displayed in a
** notification.
** If the attribute does not exist or is null, then we would use default
** value of 20.
max_actions_dsp := PO_WF_UTIL_PKG.GetItemAttrNumber(itemtype =& l_item_type,
=& l_item_key,
=& 'ACT_HST_IN_NTF');
if max_actions_dsp is NULL then
max_actions_dsp := 20;
-- &BUG 3616816 START& Fetch all Action History data into Tables.
OPEN history_csr(l_document_id, l_doc_type_code);
FETCH history_csr BULK COLLECT
INTO l_sequence_num_tbl, l_employee_name_tbl, l_action_tbl, l_action_date_tbl, l_note_tbl, l_object_revision_num_tbl, l_employee_id_tbl, l_created_by_
l_action_count := history_csr%ROWCOUNT; -- Get # of records fetched.
CLOSE history_
-- &BUG 3616816 END&
-- &BUG 3616816 START& Only display message if # of actual Action History
-- records is greater than maximum limit.
IF (l_action_count & max_actions_dsp) THEN
l_num_records_to_display := max_actions_
-- Bug 3668188: changed the code check (originally created
-- in bug 3607009) that determines which message to show
-- based on whether Open Document icon is shown in then notif.
-- The value of WF attribute 'OPEN_FORM_COMMAND' is set in a
-- previous node, using the get_po_user_msg_attribute procedure.
IF (l_open_form_command IS NULL) THEN
-- &The last [COUNT] Approval History details are summarized below.
-- For information on additional Approval History, please click the
-- Open Document icon.&
FND_MESSAGE.set_name('PO', 'PO_WF_NOTIF_PO_ACT_MESG');
-- &The last [COUNT] Approval History details are summarized below.&
FND_MESSAGE.set_name('PO', 'PO_WF_NOTIF_PO_ACT_MESG_TRUNC');
FND_MESSAGE.set_token('COUNT', to_char(max_actions_dsp));
action_mesg := FND_MESSAGE.
l_num_records_to_display := l_action_
action_mesg
-- &BUG 3616816 END&
if (display_type = 'text/html') then
l_document := NL || NL || '&!-- ACTION_HISTORY --&' || NL || NL ||
'&P&&B&';
l_document := l_document ||
fnd_message.get_string('PO',
'PO_WF_NOTIF_ACTION_HISTORY') || NL;
l_document := l_document || '&/B&' || NL || NL || '&P&'; -- &BUG 3616816&
-- &BUG 3616816 START& Action History message may be NULL. Only append it
-- and corresponding line breaks if there is a message to display.
IF (action_mesg IS NOT NULL) THEN
l_document := l_document || action_mesg || '&P&' || NL;
-- &BUG 3616816 END&
l_document := l_document ||
'&TABLE border=1 cellpadding=2 cellspacing=1 summary=&' ||
fnd_message.get_string('ICX',
'ICX_POR_TBL_OF_APPROVERS') || '&&' || NL;
l_document := l_document || '&TR&';
l_document := l_document || '&TH id=&seqNum_1&&' ||
fnd_message.get_string('PO', 'PO_WF_NOTIF_SEQ_NUM') ||
'&/TH&' || NL;
l_document := l_document || '&TH id=&employee_1&&' ||
fnd_message.get_string('PO', 'PO_WF_NOTIF_EMPLOYEE') ||
'&/TH&' || NL;
l_document := l_document || '&TH id=&action_1&&' ||
fnd_message.get_string('PO', 'PO_WF_NOTIF_ACTION') ||
'&/TH&' || NL;
l_document := l_document || '&TH id=&date_1&&' ||
fnd_message.get_string('PO', 'PO_WF_NOTIF_DATE') ||
'&/TH&' || NL;
l_document := l_document || '&TH id=&actionNote_1&&' ||
fnd_message.get_string('PO', 'PO_WF_NOTIF_ACTION_NOTE') ||
'&/TH&' || NL;
l_document := l_document || '&/TR&' || NL;
:= lengthb(l_document);
prior_len := curr_
FOR i IN 1 .. l_num_records_to_display LOOP
-- &BUG 3616816&
/* Exit the cursor if the current document length and 2 times the
** length added in prior line exceeds 32000 char */
if (curr_len + (2 * (curr_len - prior_len))) &= 32000 then
prior_len := curr_
l_document := l_document || '&TR&' || NL;
l_document := l_document ||
'&TD nowrap align=center headers=&seqNum_1&&' ||
nvl(to_char(l_sequence_num_tbl(i)), ' ') ||
'&/TD&' || NL;
/* Bug 2788683 start */
/* if action history is updated by vendor
show vendor true name(vendor name)
* else action history is updated by buyer
show buyer's true name
IF l_employee_id_tbl(i) IS NULL THEN
SELECT fu.user_name, hp.party_name
INTO l_user_name, l_party_name
FROM fnd_user fu, hz_parties hp
WHERE hp.party_id = fu.customer_id
AND fu.user_id = l_created_by_tbl(i);
po_inq_sv.get_vendor_name(l_user_name
=& l_user_name,
x_vendor_name =& l_vendor_name);
l_document := l_document || '&TD nowrap headers=&employee_1&&' ||
l_party_name || '(' || l_vendor_name || ')' ||
'&/TD&' || NL;
l_document := l_document || '&TD nowrap headers=&employee_1&&' ||
nvl(l_employee_name_tbl(i), ' ') || '&/TD&' || NL;
/* Bug 2788683 end */
l_document := l_document || '&TD nowrap headers=&action_1&&' ||
nvl(l_action_tbl(i), ' ') || '&/TD&' || NL;
l_document := l_document || '&TD nowrap headers=&date_1&&' ||
nvl(to_char(l_action_date_tbl(i)), ' ') ||
'&/TD&' || NL;
l_document := l_document || '&TD nowrap headers=&actionNote_1&&' ||
nvl(l_note_tbl(i), ' ') || '&/TD&' || NL;
l_document := l_document || '&/TR&' || NL;
curr_len := lengthb(l_document);
l_document := l_document || '&/TABLE&&/P&' || NL;
document := l_
elsif (display_type = 'text/plain') then
l_document := l_document ||
fnd_message.get_string('PO',
'PO_WF_NOTIF_ACTION_HISTORY') || NL;
-- &BUG 3616816 START& Action History message may be NULL. Only append it
-- and corresponding line breaks if there is a message to display.
IF (action_mesg IS NOT NULL) THEN
l_document := l_document || action_mesg || NL || NL;
-- &BUG 3616816 END&
:= lengthb(l_document);
prior_len := curr_
FOR i IN 1 .. l_num_records_to_display LOOP
-- &BUG 3616816&
/* Exit the cursor if the current document length and 2 times the
** length added in prior line exceeds 32000 char */
if (curr_len + (2 * (curr_len - prior_len))) &= 32000 then
prior_len := curr_
l_document := l_document || NL;
/* Bug 2462005 sktiwari:
** Added a ':' between the prompt and the data. Modified the following lines.
l_document := l_document ||
fnd_message.get_string('PO', 'PO_WF_NOTIF_SEQ_NUM') || ': ' ||
to_char(l_sequence_num_tbl(i)) || NL;
/* Bug 2788683 start */
/* if action history is updated by vendor
show vendor true name(vendor name)
* else action history is updated by buyer
show buyer's true name
IF l_employee_id_tbl(i) IS NULL THEN
SELECT fu.user_name, hp.party_name
INTO l_user_name, l_party_name
FROM fnd_user fu, hz_parties hp
WHERE hp.party_id = fu.customer_id
AND fu.user_id = l_created_by_tbl(i);
po_inq_sv.get_vendor_name(l_user_name
=& l_user_name,
x_vendor_name =& l_vendor_name);
l_document := l_document ||
fnd_message.get_string('PO', 'PO_WF_NOTIF_EMPLOYEE') || ': ' ||
l_party_name || '(' || l_vendor_name || ')' || NL;
l_document := l_document ||
fnd_message.get_string('PO', 'PO_WF_NOTIF_EMPLOYEE') || ': ' ||
l_employee_name_tbl(i) || NL;
/* Bug 2788683 end */
l_document := l_document ||
fnd_message.get_string('PO', 'PO_WF_NOTIF_ACTION') || ': ' ||
l_action_tbl(i) || NL;
l_document := l_document ||
fnd_message.get_string('PO', 'PO_WF_NOTIF_DATE') || ': ' ||
to_char(l_action_date_tbl(i)) || NL;
l_document := l_document ||
fnd_message.get_string('PO',
'PO_WF_NOTIF_ACTION_NOTE') || ': ' ||
l_note_tbl(i) || NL;
l_document := l_document || NL;
curr_len := lengthb(l_document);
l_document := l_
document := l_
PROCEDURE post_approval_notif(itemtype
in varchar2,
in varchar2,
in number,
in varchar2,
resultout in out NOCOPY varchar2) is
resultout := wf_engine.eng_completed || ':' || wf_engine.eng_
-- Don't allow transfer
if (funcmode = 'TRANSFER') then
fnd_message.set_name('PO', 'PO_WF_NOTIF_NO_TRANSFER');
app_exception.raise_
-- end if for funcmode = 'TRANSFER'
end post_approval_
/* Bug# 2616433: kagarwal
** Desc: Added new procedure to set notification subject token in
** user language.
procedure Get_po_user_msg_attribute(itemtype
in varchar2,
in varchar2,
in number,
in varchar2,
resultout out NOCOPY varchar2) IS
l_progress
VARCHAR2(100) := '000';
l_doc_string
varchar2(200);
l_user_name
varchar2(100);
l_preparer_user_name varchar2(100);
l_notification_type
varchar2(15); --bug 3668188
-- &Start Word Integration 11.5.10+&
l_okc_doc_type
varchar2(20);
l_conterms_exist_flag PO_HEADERS_ALL.conterms_exist_flag%TYPE;
l_document_id
l_document_subtype
PO_HEADERS_ALL.type_lookup_code%TYPE;
-- &End Word Integration 11.5.10+&
-- Do nothing in cancel or timeout mode
if (funcmode && wf_engine.eng_run) then
resultout := wf_engine.eng_
l_progress := 'Get_po_user_msg_attribute:001: actid: ' ||
/* DEBUG */
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, l_progress);
l_orgid := wf_engine.GetItemAttrNumber(itemtype =& itemtype,
=& itemkey,
=& 'ORG_ID');
IF l_orgid is NOT NULL THEN
fnd_client_info.set_org_context(to_char(l_orgid));
l_progress := 'Get_po_user_msg_attribute:010: orgid: ' || l_
/* DEBUG */
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, l_progress);
l_user_name := wf_engine.GetActivityAttrText(itemtype
=& itemtype,
=& itemkey,
=& 'NTF_USER_NAME',
ignore_notfound =& TRUE);
PO_WF_PO_NOTIFICATION.GetDisplayValue(itemtype, itemkey, l_user_name);
l_progress := 'Get_po_user_msg_attribute:015: username: ' ||
/* DEBUG */
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, l_progress);
-- Bug 3668188: added the NTF_USER_ROLE Activity Attribute, which is
-- a constant with value 'SUBMITTER' or 'APPROVER'.
This distinguishes
-- between the 3 different notification-functions that share this procedure.
l_notification_type := wf_engine.GetActivityAttrText(itemtype
=& itemtype,
=& itemkey,
=& 'NTF_USER_ROLE',
ignore_notfound =& TRUE);
l_progress := 'Get_po_user_msg_attribute:020: notif type: ' ||
l_notification_
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, l_progress);
-- Bug 3668188: Removed old logic from bug 3564727 and replaced it
-- with a call to is_open_document_allowed, which has updated logic.
(PO_WF_PO_NOTIFICATION.is_open_document_allowed(p_itemtype
=& itemtype,
=& itemkey,
p_notification_type =& l_notification_type)) THEN
l_progress := 'Get_po_approver_msg_attribute: 040: NULL open form';
/* DEBUG */
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, l_progress);
wf_engine.SetItemAttrText(itemtype =& itemtype,
=& itemkey,
=& 'OPEN_FORM_COMMAND',
=& '');
-- &Start Word Integration 11.5.10+&
l_conterms_exist_flag := PO_WF_UTIL_PKG.GetItemAttrText(itemtype =& itemtype,
=& itemkey,
=& 'CONTERMS_EXIST_FLAG');
l_document_subtype := PO_WF_UTIL_PKG.GetItemAttrText(itemtype =& itemtype,
=& itemkey,
=& 'DOCUMENT_SUBTYPE');
l_document_id := PO_WF_UTIL_PKG.GetItemAttrNumber(itemtype =& itemtype,
=& itemkey,
=& 'DOCUMENT_ID');
/* Set or reset the okc doc attachment attribute */
IF (l_conterms_exist_flag = 'Y') THEN
l_okc_doc_type := PO_CONTERMS_UTL_GRP.get_po_contract_doctype(l_document_subtype);
IF (('STRUCTURED' &&
OKC_TERMS_UTIL_GRP.get_contract_source_code(p_document_type =& l_okc_doc_type,
p_document_id
=& l_document_id)) AND
('N' =
OKC_TERMS_UTIL_GRP.is_primary_terms_doc_mergeable(P_document_type =& l_okc_doc_type,
p_document_id
=& l_document_id)) AND
(PO_COMMUNICATION_PVT.PO_COMMUNICATION_PROFILE = 'T')) THEN
PO_WF_UTIL_PKG.SetItemAttrText(itemtype =& itemtype,
=& itemkey,
=& 'OKC_DOC_ATTACHMENT',
=& 'PLSQLBLOB:PO_COMMUNICATION_PVT.OKC_DOC_ATTACH/' ||
itemtype || ':' ||
/* Contract terms are structured, or attached document is mergeable.
* All contract
no need for other okc doc attachment.
PO_WF_UTIL_PKG.SetItemAttrText(itemtype =& itemtype,
=& itemkey,
=& 'OKC_DOC_ATTACHMENT',
=& '');
END IF /* not structured and not mergeable */
END IF; /* l_conterms_exist_flag = 'Y' */
-- &End Word Integration 11.5.10+&
l_progress := 'Get_po_approver_msg_attribute: 999';
/* DEBUG */
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, l_progress);
resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED';
WHEN OTHERS THEN
l_doc_string
:= PO_REQAPPROVAL_INIT1.get_error_doc(itemType,
l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType,
wf_core.context('PO_WF_PO_NOTIFICATION',
'Get_req_approval_msg_attribute',
l_progress);
PO_REQAPPROVAL_INIT1.send_error_notif(itemType,
l_preparer_user_name,
l_doc_string,
'PO_WF_PO_NOTIFICATION.Get_po_approver_msg_attribute');
END Get_po_user_msg_
/* Bug# 2616433: kagarwal
** Desc: Added new procedure to set doc type display according to the
** default language of approver or preparer.
procedure GetDisplayValue(itemtype
in varchar2,
in varchar2,
in varchar2,
in varchar2,
docsubtype in varchar2) IS
l_progress
VARCHAR2(400) := '000';
l_doc_type
varchar2(25);
l_doc_subtype varchar2(25);
l_doc_disp
varchar2(240);
varchar2(1) :=
l_display_name
varchar2(240);
l_email_address
varchar2(240);
l_notification_preference varchar2(240);
l_language
varchar2(240);
l_territory
varchar2(240);
l_msg_text
varchar2(2000) := NULL; -- Bug 3430545
cursor c_lookup_value_user(p_doc_type varchar2, p_doc_subtype varchar2, p_language varchar2) is
select type_name
from po_document_types_tl tl, FND_LANGUAGES fl
where fl.nls_language = p_language
and tl.LANGUAGE = fl.language_code
and tl.document_type_code = p_doc_type
and tl.document_subtype = p_doc_
cursor c_lookup_value_doc(p_doc_type varchar2, p_doc_subtype varchar2) is
select type_name
from po_document_types
where document_type_code = p_doc_type
and document_subtype = p_doc_
l_progress := 'PO_WF_PO_NOTIFICATION.GetDisplayValue: 001, user name: ' ||
/* DEBUG */
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, l_progress);
if ((doctype is NULL) or (docsubtype is null)) then
l_doc_type := PO_WF_UTIL_PKG.GetItemAttrText(itemtype =& itemtype,
=& itemkey,
=& 'DOCUMENT_TYPE');
l_doc_subtype := PO_WF_UTIL_PKG.GetItemAttrText(itemtype =& itemtype,
=& itemkey,
=& 'DOCUMENT_SUBTYPE');
l_doc_type
l_doc_subtype :=
IF l_doc_type = 'PA' AND l_doc_subtype = 'BLANKET' THEN
l_ga_flag := PO_WF_UTIL_PKG.GetItemAttrText(itemtype =& itemtype,
=& itemkey,
=& 'GLOBAL_AGREEMENT_FLAG');
/* Bug 3430545: Modified the code to get the translated values for the wf
notification attribute 'REQUIRES_APPROVAL_MSG' and 'PO_GA_TYPE'.
Deleted the previous code and revamped it.
IF username is NULL THEN
l_progress := 'PO_WF_PO_NOTIFICATION.GetDisplayValue: 050';
/* DEBUG */
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, l_progress);
IF l_ga_flag = 'Y' THEN
l_doc_disp := FND_MESSAGE.GET_STRING('PO', 'PO_GA_TYPE');
OPEN c_lookup_value_doc(l_doc_type, l_doc_subtype);
FETCH c_lookup_value_doc
into l_doc_
CLOSE c_lookup_value_
END IF; /* l_ga_flag = 'Y' */
l_progress := 'PO_WF_PO_NOTIFICATION.GetDisplayValue: 060';
/* DEBUG */
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, l_progress);
WF_DIRECTORY.GETROLEINFO(username,
l_display_name,
l_email_address,
l_notification_preference,
l_language,
l_territory);
l_progress := 'PO_WF_PO_NOTIFICATION.GetDisplayValue: 080, language: ' ||
/* DEBUG */
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, l_progress);
IF l_ga_flag = 'Y' THEN
select message_text
into l_doc_disp
from fnd_new_messages fm, fnd_languages fl
where fm.message_name = 'PO_GA_TYPE'
and fm.language_code = fl.language_code
and fl.nls_language = l_language
and fm.application_id = 201; --&BUG 3712124& Include application_id to better use PK index
WHEN OTHERS THEN
l_doc_disp := FND_MESSAGE.GET_STRING('PO', 'PO_GA_TYPE');
OPEN c_lookup_value_user(l_doc_type, l_doc_subtype, l_language);
FETCH c_lookup_value_user
into l_doc_
CLOSE c_lookup_value_
END IF; /* l_ga_flag = 'Y' */
END IF; /* if username is null
l_progress := 'PO_WF_PO_NOTIFICATION.GetDisplayValue: 100, type disp: ' ||
/* DEBUG */
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, l_progress);
PO_WF_UTIL_PKG.SetItemAttrText(itemtype =& itemtype,
=& itemkey,
=& 'DOCUMENT_TYPE_DISP',
=& l_doc_disp);
select message_text
into l_msg_text
from fnd_new_messages fm, fnd_languages fl
where fm.message_name = 'PO_WF_NOTIF_REQUIRES_APPROVAL'
and fm.language_code = fl.language_code
and fl.nls_language = l_language
and fm.application_id = 201; --&BUG 3712124& Include application_id to better use PK index
WHEN OTHERS THEN
l_msg_text := PO_WF_UTIL_PKG.GetItemAttrText(itemtype =& itemtype,
=& itemkey,
=& 'REQUIRES_APPROVAL_MSG');
PO_WF_UTIL_PKG.SetItemAttrText(itemtype =& itemtype,
=& itemkey,
=& 'REQUIRES_APPROVAL_MSG',
=& l_msg_text);
WHEN OTHERS THEN
l_progress := 'PO_WF_PO_NOTIFICATION.GetDisplayValue: sql err: ' ||
/* DEBUG */
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, l_progress);
IF (c_lookup_value_user%ISOPEN) THEN
CLOSE c_lookup_value_
IF (c_lookup_value_doc%ISOPEN) THEN
CLOSE c_lookup_value_
END GetDisplayV
------------------------------------------------------------------&BUG 3607009&
-------------------------------------------------------------------------------
--Start of Comments
--Name: is_open_document_allowed
--Pre-reqs:
--Modifies:
--Function:
Determines if the Open Document icon should be shown in the
PO Approval Notification. The Open Document should not be shown if...
(1) the document is in 'Pre-Approved' state and
(2) document signature is required.
--Parameters:
--p_itemtype
Standard parameter to be used in a workflow procedure
--p_itemkey
Standard parameter to be used in a workflow procedure
--p_notification_type
Specifies whether this notification is for the Preparer/Submitter
or an Approver/Reviewer
The value is derived from the WF Function attribute NTF_USER_ROLE
in the GET_&&_NOTIFICATION_ATTRIBUTE functions in POAPPRV workflow
Added for bug 3668188
--Returns:
--resultout
A BOOLEAN TRUE if the Open Document icon should be shown, FALSE otherwise.
--Testing:
--End of Comments
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
FUNCTION is_open_document_allowed(p_itemtype
IN VARCHAR2,
IN VARCHAR2,
p_notification_type IN VARCHAR2 --bug 3668188
) RETURN BOOLEAN IS
l_api_name VARCHAR2(30) := 'is_open_document_allowed';
l_log_head VARCHAR2(100) := g_pkg_name || '.' || l_api_
l_progress VARCHAR2(3);
l_authorization_status PO_HEADERS_ALL.authorization_status%TYPE;
BOOLEAN := TRUE;
l_progress := '000';
PO_WF_DEBUG_PKG.insert_debug(p_itemtype,
p_itemkey,
l_log_head || ':' || l_progress ||
'Notification Type = ' ||
p_notification_type);
-- Get the Authorization Status (e.g. 'PRE-APPROVED','APPROVED',
-- 'INCOMPLETE', etc.) of the document.
l_authorization_status := wf_engine.GetItemAttrText(itemtype =& p_itemtype,
=& p_itemkey,
=& 'AUTHORIZATION_STATUS');
l_progress := '010';
PO_WF_DEBUG_PKG.insert_debug(p_itemtype,
p_itemkey,
l_log_head || ':' || l_progress ||
' Authorization Status = ' ||
l_authorization_status);
-- bug 3668188: changed the Open Doc allowed logic.
-- If document is ('Pre-Approved' or 'In Process') and the
-- notification is going back to the Submitter, then that
-- user should not be able to open the document for edit.
IF (l_authorization_status IN ('PRE-APPROVED', 'IN PROCESS') AND
nvl(p_notification_type, 'SUBMITTER') = 'SUBMITTER') THEN
l_progress := '020';
PO_WF_DEBUG_PKG.insert_debug(p_itemtype,
p_itemkey,
l_log_head || ':' || l_progress ||
':FALSE');
l_result := FALSE;
l_progress := '030';
PO_WF_DEBUG_PKG.insert_debug(p_itemtype,
p_itemkey,
l_log_head || ':' || l_progress || ':TRUE');
return(l_result);
WHEN OTHERS THEN
PO_WF_DEBUG_PKG.insert_debug(p_itemtype,
p_itemkey,
l_log_head || ':' || SQLERRM);
END is_open_document_
END PO_WF_PO_NOTIFICATION;
来自：http://www.itpub.net/thread--19.html
参考知识库
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：762256次
积分：11440
积分：11440
排名：第1012名
原创：346篇
转载：236篇
评论：16条
Mail：xingyun.