[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select REQUISITION_HEADER_ID,DESCRIPTION,AUTHORIZATION_STATUS,
TYPE_LOOKUP_CODE,PREPARER_ID,SEGMENT1,CLOSED_CODE,EMERGENCY_PO_NUM,
NVL(CONTRACTOR_REQUISITION_FLAG, 'N'),
NVL(CONTRACTOR_STATUS, 'NULL'), NOTE_TO_AUTHORIZER
from po_requisition_headers_all
where REQUISITION_HEADER_ID = p_requisition_header_id;
PROCEDURE InsertActionHistSubmit(itemtype varchar2, itemkey varchar2,
p_doc_id number, p_doc_type varchar2,
p_doc_subtype varchar2, p_employee_id number,
p_action varchar2, p_note varchar2, p_path_id number);
PROCEDURE UpdateActionHistory(p_doc_id IN number,
p_doc_type IN varchar2,
p_doc_subtype IN varchar2,
p_action IN varchar2
) ;
Procedure Insert_Acc_Rejection_Row(itemtype in varchar2,
itemkey in varchar2,
actid in number,
flag in varchar2);
* This procedure basically checks if archive_on_print option is selected, and if yes
* call procedure PO_ARCHIVE_PO_SV.ARCHIVE_PO to archive the PO
*************************************************************************************/
procedure archive_po(p_document_id in number,
p_document_type in varchar2,
p_document_subtype in varchar2);
'select distinct fu.user_name
from fnd_user fu
where fu.user_name in ('|| p_super_set || ')
and fu.user_name not in (' || p_subset || ')';
SELECT role_name
FROM (
SELECT role_name
FROM wf_user_roles
WHERE role_name IN
(SELECT role_name
FROM wf_user_roles
WHERE user_name in (SELECT user_name FROM po_wf_user_tmp)
AND role_name like 'ADHOC%'
AND NVL(EXPIRATION_DATE,SYSDATE+1) > SYSDATE
GROUP BY role_name
HAVING count(role_name) = p_num_users
)
GROUP BY role_name
HAVING COUNT(role_name) = p_num_users
)
WHERE ROWNUM < 2;
DELETE po_wf_user_tmp; -- delete rows in the global temp table
INSERT INTO po_wf_user_tmp (user_name) VALUES (l_user_name);
DELETE po_wf_user_tmp;
SELECT function_id
FROM fnd_form_functions
WHERE function_name = p_function_name;
'select distinct fu.user_name
from fnd_user fu, fnd_user_resp_groups furg
where fu.user_id = furg.user_id
and furg.responsibility_id in
(
SELECT
responsibility_id
FROM fnd_responsibility fr
WHERE menu_id in
( SELECT fme.menu_id
FROM fnd_menu_entries fme
START WITH fme.function_id ='|| p_function_id ||'
CONNECT BY PRIOR menu_id = sub_menu_id
)
and (end_date is null or end_date > sysdate) '||
' and fr.responsibility_id not in (select responsibility_id from fnd_resp_functions
where action_id= '|| p_function_id ||
' and rule_type=' || l_f || ' )' ||
' )
and fu.user_name in (' || p_namelist || ')
and (furg.end_date is null or furg.end_date > sysdate )' ;
UpdateSourcingRule VARCHAR2,
MassUpdateReleases VARCHAR2,
RetroactivePriceChange VARCHAR2,
OrgAssignChange VARCHAR2, -- GA FPI
CommunicatePriceChange VARCHAR2, --
p_Background_Flag VARCHAR2 default 'N', --
p_Initiator VARCHAR2 default NULL,
p_xml_flag VARCHAR2 default NULL,
/* Bug6708182 FPDS-NG ER. */
/* Added */
FpdsngFlag VARCHAR2 default 'N' ,
p_source_type_code VARCHAR2 DEFAULT null
/* End Added*/
)
IS
l_responsibility_id number;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
select to_char(PO_WF_ITEMKEY_S.NEXTVAL)
into l_seq_for_item_key
from sys.dual;
select count(*)
into x_wf_created
from wf_items
where item_type = l_itemtype
and item_key = l_itemkey;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,x_progress);
UPDATE po_releases_all
SET xml_flag = 'Y',
edi_processed_flag = 'N'
WHERE po_release_id = DocumentID;
UPDATE po_headers_all
SET xml_flag = 'Y',
edi_processed_flag = 'N'
WHERE po_header_id = DocumentID;
UPDATE po_releases_all
SET xml_flag = 'N'
WHERE po_release_id = DocumentID;
update po_headers_all
SET xml_flag = 'N'
WHERE po_header_id = DocumentID;
PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,x_progress);
, aname => 'INSERT_ACTION_HIST_FLAG'
, avalue => 'N'
);
, aname => 'INSERT_ACTION_HIST_FLAG'
, avalue => 'Y'
);
SELECT poh.acceptance_required_flag,
poh.acceptance_due_date,
poh.agent_id
into x_acceptance_required_flag,
x_acceptance_due_date,
x_agent_id
from po_headers poh
where poh.po_header_id = DocumentID;
SELECT por.acceptance_required_flag,
por.acceptance_due_date, por.agent_id
into x_acceptance_required_flag,
x_acceptance_due_date,
x_agent_id
from po_releases por,
po_headers_all poh --
where por.po_release_id = DocumentID
and por.po_header_id = poh.po_header_id;
select global_agreement_flag
into l_ga_flag
from po_headers_all
where po_header_id = DocumentID;
select revision_num,
DECODE(TYPE_LOOKUP_CODE,
'BLANKET',FND_MESSAGE.GET_STRING('POS','POS_POTYPE_BLKT'),
'CONTRACT',FND_MESSAGE.GET_STRING('POS','POS_POTYPE_CNTR'),
'STANDARD',FND_MESSAGE.GET_STRING('POS','POS_POTYPE_STD'),
'PLANNED',FND_MESSAGE.GET_STRING('POS','POS_POTYPE_PLND')),
NVL(CONTERMS_EXIST_FLAG,'N'), --
segment1 -- Bug 3284628
into l_revision_num,
l_doc_display_name,
l_conterms_exist_flag, --
l_document_number -- Bug 3284628
from po_headers
where po_header_id = DocumentID;
select POR.revision_num,
POR.release_num,
DECODE(POR.release_type,
'BLANKET', FND_MESSAGE.GET_STRING('POS','POS_POTYPE_BLKTR'),
'SCHEDULED',FND_MESSAGE.GET_STRING('POS','POS_POTYPE_PLNDR')),
POH.segment1 -- Bug 3284628
into l_revision_num,
l_release_num,
l_doc_display_name,
l_document_number -- Bug 3284628
from po_releases POR,
po_headers_all POH --
where POR.po_release_id = DocumentID
and POR.po_header_id = POH.po_header_id; -- JOIN
SELECT PRH.segment1
INTO l_document_number
FROM po_requisition_headers PRH
WHERE PRH.requisition_header_id = DocumentID;
aname => 'UPDATE_SOURCING_RULE',
avalue => updatesourcingrule);
aname => 'MASSUPDATE_RELEASES',
avalue => MassUpdateReleases);
select 'Y'
into l_drop_ship_flag
from dual
where exists
(select 'Release DropShip Shipment Exists'
from po_line_locations
where po_release_id = DocumentId
and drop_ship_flag = 'Y');
select 'Y'
into l_drop_ship_flag
from dual
where exists
(select 'PO DropShip Shipment Exists'
from po_line_locations
where po_header_id = DocumentId
and drop_ship_flag = 'Y');
select NVL(consigned_consumption_flag, 'N') -- Bug 3318625
into l_consigned_flag
from po_releases_all
where po_release_id = DocumentId;
select NVL(consigned_consumption_flag, 'N')
into l_consigned_flag
from po_headers_all
where po_header_id = DocumentId;
select pov.vendor_name,
pvs.vendor_site_code
into l_vendor,
l_vendor_site_code
from po_vendors pov,po_headers poh,po_vendor_sites_all pvs
where pov.vendor_id = poh.vendor_id
and poh.po_header_id = DocumentId
and poh.vendor_site_id = pvs.vendor_site_id;
select pov.vendor_name,
pvs.vendor_site_code
into l_vendor,
l_vendor_site_code
from po_releases por,po_headers poh,
po_vendors pov,po_vendor_sites_all pvs
where por.po_release_id = DocumentId
and por.po_header_id = poh.po_header_id
and poh.vendor_id = pov.vendor_id
and poh.vendor_site_id = pvs.vendor_site_id;
SELECT hou.name
into l_operating_unit
FROM
hr_organization_units hou
WHERE
hou.organization_id = x_orgid;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,x_progress);
update po_releases
set AUTHORIZATION_STATUS = 'IN PROCESS',
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
last_update_date = sysdate
where po_release_id = DocumentID;
update po_headers
set AUTHORIZATION_STATUS = 'IN PROCESS',
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
last_update_date = sysdate
where po_header_id = DocumentID;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,l_itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,l_itemkey,x_progress);
l_update_req_url varchar2(1000);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
SELECT CAN_APPROVER_MODIFY_DOC_FLAG
INTO l_can_modify_flag
FROM po_document_types
WHERE DOCUMENT_TYPE_CODE = l_document_type
AND DOCUMENT_SUBTYPE = l_doc_subtype;
SELECT style_id
INTO l_style_id
FROM po_headers_all
WHERE po_header_id = l_document_id;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
p_mode => 'update');
x_progress := 'PO_REQAPPROVAL_INIT1.get_po_url update' || 'l_edit_po_url ::'|| l_edit_po_url;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
** Set the URL to VIEW/UPDATE web requisitions
*/
-- The support for icx 3.0 is removed.
IF (fnd_profile.value('POR_SSP4_INSTALLED') = 'Y' AND
l_document_type = 'REQUISITION' and
po_core_s.get_product_install_status('ICX') = 'I') THEN
--Bug#3147435
--Set the values for workflow attribute
--VIEW_REQ_DTLS_URL and EDIT_REQ_URL
l_view_req_dtls_url := 'JSP:/OA_HTML/OA.jsp?OAFunc=ICX_POR_LAUNCH_IP' || '&' ||
'porMode=viewReq' || '&' ||
'porReqHeaderId=' || to_char(l_document_id) || '&' ||
'_OrgId=' || to_char(x_orgid) || '&' ||
'addBreadCrumb=Y';
l_update_req_url := l_edit_req_url;
aname => 'REQ_UPDATE_URL' ,
avalue => l_update_req_url);
/*SELECT CAN_APPROVER_MODIFY_DOC_FLAG
INTO l_can_modify_flag
FROM po_document_types
WHERE DOCUMENT_TYPE_CODE = l_document_type
AND DOCUMENT_SUBTYPE = l_doc_subtype;*/
select AUTHORIZATION_STATUS
into l_authorization_status
from po_requisition_headers_all
where REQUISITION_HEADER_ID = l_document_id;
select AUTHORIZATION_STATUS, NVL(REVISION_NUM,0)
into l_authorization_status, l_po_revision
from po_headers_all
where PO_HEADER_ID = l_document_id;
select AUTHORIZATION_STATUS, NVL(REVISION_NUM,0)
into l_authorization_status, l_po_revision
from po_releases_all
where PO_RELEASE_ID = l_document_id;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
** we want to set it to IN PROCESS and update the ITEMTYPE/ITEMKEY columns.
** If this is an upgrade to R11, then we need to update the ITEMTYPE/ITEMKEY columns
** Note that we only pickup docs is IN PROCESS or PRE-APPROVED in the upgrade step.
*/
IF ( NVL(l_authorization_stat, 'INCOMPLETE') NOT IN ('IN PROCESS', 'PRE-APPROVED') )
OR
( l_note = 'UPGRADE_TO_R11' ) THEN
IF l_doc_type = 'REQUISITION' THEN
SetReqAuthStat(l_document_id, itemtype,itemkey,l_note, 'IN PROCESS');
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
select NVL(authorization_status, 'INCOMPLETE') into l_auth_stat
from PO_REQUISITION_HEADERS
where requisition_header_id = l_doc_id;
select NVL(authorization_status,'INCOMPLETE') into l_auth_stat
from PO_HEADERS
where po_header_id = l_doc_id;
select NVL(authorization_status,'INCOMPLETE') into l_auth_stat
from PO_RELEASES
where po_release_id = l_doc_id;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
x_progress := 'PO_REQAPPROVAL_INIT1.set_doc_to_originalstat: 03' || 'Update Action History'
|| 'Action Code = No Action';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
UpdateActionHistory(p_doc_id => l_doc_id,
p_doc_type => l_doc_type,
p_doc_subtype => l_doc_subtype,
p_action => 'NO ACTION'
) ;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
select NVL(authorization_status, 'INCOMPLETE') into l_auth_stat
from PO_REQUISITION_HEADERS
where requisition_header_id = l_doc_id;
select NVL(authorization_status,'INCOMPLETE') into l_auth_stat
from PO_HEADERS
where po_header_id = l_doc_id;
select NVL(authorization_status,'INCOMPLETE') into l_auth_stat
from PO_RELEASES
where po_release_id = l_doc_id;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
** then the action history would be wrongly updated.
**
** Modifying the parameter l_employee_id to be passed to
** InsertActionHistSubmit() from PREPARER_ID to
** APPROVER_EMPID.
**
** Also modified the SetReqHdrAttributes() to also set the
** PREPARER_USER_NAME and PREPARER_DISPLAY_NAME.
**
*/
l_employee_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
itemkey => itemkey,
aname => 'APPROVER_EMPID');
PO_REQAPPROVAL_INIT1.InsertActionHistSubmit(itemtype,itemkey,l_doc_id, l_doc_type,
l_doc_subtype, l_employee_id, 'SUBMIT', l_note, l_path_id);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
select nvl(fsp.reserve_at_completion_flag,'N') into l_reserve_at_compl
from financials_system_parameters fsp;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
select wf_item_key
from po_headers
where po_header_id= p_header_id;
select wf_item_key
from po_requisition_headers
where requisition_header_id= p_header_id;
select wf_item_key
from po_releases
where po_release_id= p_header_id;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
SELECT acceptance_required_flag
INTO l_acceptance_flag
FROM po_headers_all
WHERE po_header_Id = l_document_id;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
l_select boolean;
select max(need_by_date)+180
into l_expiration_date
from po_line_locations
where po_header_id = to_number(p_document_id)
and cancel_flag = 'N';
select max(need_by_date)+180
into l_expiration_date
from po_line_locations
where po_release_id = to_number(p_document_id)
and cancel_flag = 'N';
select vendor_name
into l_role_display_name
from po_vendors
where vendor_id=l_vendor_id;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
select po_header_id into l_po_header_id
from po_releases
where po_release_id = l_document_id;
select poh.vendor_site_id, pvs.vendor_site_code, pvs.language
into l_vendor_site_id, l_vendor_site_code, l_vendor_site_lang
from po_headers poh, po_vendor_sites pvs
where pvs.vendor_site_id = poh.vendor_site_id
and poh.po_header_id = l_po_header_id;
SELECT wfl.nls_language, wfl.nls_territory INTO l_adhocuser_lang, l_adhocuser_territory
FROM wf_languages wfl, fnd_languages_vl flv
WHERE wfl.code = flv.language_code AND flv.nls_language = l_vendor_site_lang;
SELECT wfl.nls_language, wfl.nls_territory into l_adhocuser_lang, l_adhocuser_territory
FROM wf_languages wfl, fnd_languages_vl flv
WHERE wfl.code = flv.language_code AND flv.installed_flag = 'B';
select count(*) into l_performer_exists
from wf_users where name = l_po_email_performer;
select count(*) into l_performer_exists
from wf_users where name = l_po_email_performer_prof;
update_print_count(l_document_id,l_document_type);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
select NVL(authorization_status, 'INCOMPLETE') into l_auth_stat
from PO_REQUISITION_HEADERS
where requisition_header_id = l_doc_id;
select NVL(authorization_status,'INCOMPLETE') into l_auth_stat
from PO_HEADERS
where po_header_id = l_doc_id;
select NVL(authorization_status,'INCOMPLETE') into l_auth_stat
from PO_RELEASES
where po_release_id = l_doc_id;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress );
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
SELECT default_rate_type
INTO l_rate_type
FROM po_system_parameters;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name = l_approver_user_name;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
select DISPLAYED_FIELD
from po_lookup_codes
where lookup_type='AUTHORIZATION STATUS'
and lookup_code = p_auth_stat;
select DISPLAYED_FIELD
from po_lookup_codes
where lookup_type='DOCUMENT STATE'
and lookup_code = p_closed_code;
select type_name
from po_document_types
where document_type_code = p_doc_type
and document_subtype = p_doc_subtype;
select DISPLAYED_FIELD
from po_lookup_codes
where lookup_type='REQUISITION TYPE'
and lookup_code = p_doc_subtype;
SELECT nvl(SUM(round(decode(order_type_lookup_code,
'RATE', amount,
'FIXED PRICE', amount,
quantity * unit_price),l_precision)) ,0)
FROM po_requisition_lines
WHERE requisition_header_id = p_doc_id
AND NVL(cancel_flag,'N') = 'N'
AND NVL(modified_by_agent_flag, 'N') = 'N';
SELECT nvl(sum(nonrecoverable_tax), 0)
FROM po_requisition_lines rl,
po_req_distributions_all rd --
WHERE rl.requisition_header_id = p_doc_id
AND rd.requisition_line_id = rl.requisition_line_id
AND NVL(rl.cancel_flag,'N') = 'N'
AND NVL(rl.modified_by_agent_flag, 'N') = 'N';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
select count(1)
into l_num_attachments
from fnd_attached_documents
where pk1_value = to_char(ReqHdr_rec.requisition_header_id)
and entity_name = 'REQ_HEADERS';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
** or 'PRE-APPROVED', therefore we should update the status to IN PROCESS.
*/
/* Bug# 1894960: kagarwal
** Desc: Requisitons Upgraded from 10.7 fails to set the status of Requisiton
** to Pre-Approved.
**
** Reason being that when the procedure SetReqAuthStat() is called to set the
** Requisiton status to Pre-Approved, the conditon
** "IF (note = 'UPGRADE_TO_R11')" do not set the authorization status causes
** the Requisiton to remain in the existing status.
** Hence the Upgraded Requisitons can never be set to 'Pre-Approved' status and
** the approval process will always return the Req with Notification
** "No Approver Found".
**
** Whereas the reason for this condition was to not set the status of upgrade
** Reqs to IN PROCESS as it could have been PRE-APPROVED.
**
** Changed the procedure SetReqAuthStat().
**
** Modified the clause IF note = 'UPGRADE_TO_R11'
**
** TO:
**
** IF (note = 'UPGRADE_TO_R11' and p_auth_status = 'IN PROCESS') THEN
**
** Now when the approval process will call the procedure SetReqAuthStat()
** to set the Requisiton to 'Pre-Approved' status then it will go to the
** else part and set its authorization status to 'Pre-Approved'.
*/
IF (note = 'UPGRADE_TO_R11' and p_auth_status = 'IN PROCESS') THEN
update po_requisition_headers set
WF_ITEM_TYPE = itemtype,
WF_ITEM_KEY = itemkey,
active_shopping_cart_flag = NULL,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
last_update_date = sysdate
where requisition_header_id = l_requisition_header_id;
update po_requisition_headers set
AUTHORIZATION_STATUS = p_auth_status,
WF_ITEM_TYPE = itemtype,
WF_ITEM_KEY = itemkey,
active_shopping_cart_flag = NULL,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
last_update_date = sysdate
where requisition_header_id = l_requisition_header_id;
** or 'PRE-APPROVED', therefore we should update the status to IN PROCESS.
*/
IF note = 'UPGRADE_TO_R11' THEN
update po_headers set
WF_ITEM_TYPE = itemtype,
WF_ITEM_KEY = itemkey,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
last_update_date = sysdate
where po_header_id = l_po_header_id;
update po_headers set
AUTHORIZATION_STATUS = p_auth_status,
WF_ITEM_TYPE = itemtype,
WF_ITEM_KEY = itemkey,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
last_update_date = sysdate
,submit_date = decode(p_auth_status,
'INCOMPLETE', to_date(null),submit_date) --
where po_header_id = l_po_header_id;
** or 'PRE-APPROVED', therefore we should update the status to IN PROCESS.
*/
IF note = 'UPGRADE_TO_R11' THEN
update po_releases set
WF_ITEM_TYPE = itemtype,
WF_ITEM_KEY = itemkey,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
last_update_date = sysdate
where po_release_id = l_Release_header_id;
update po_releases set
AUTHORIZATION_STATUS = p_auth_status,
WF_ITEM_TYPE = itemtype,
WF_ITEM_KEY = itemkey,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
last_update_date = sysdate
,submit_date = decode(p_auth_status,
'INCOMPLETE', to_date(null),submit_date) --
where po_release_id = l_Release_header_id;
update po_requisition_headers set
WF_ITEM_TYPE = itemtype,
WF_ITEM_KEY = itemkey,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
last_update_date = sysdate
where requisition_header_id = p_doc_id;
update po_headers set
WF_ITEM_TYPE = itemtype,
WF_ITEM_KEY = itemkey,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
last_update_date = sysdate
,submit_date = sysdate --
where po_header_id = p_doc_id;
update po_releases set
WF_ITEM_TYPE = itemtype,
WF_ITEM_KEY = itemkey,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
last_update_date = sysdate
,submit_date = sysdate --
where po_release_id = p_doc_id;
select NVL(can_preparer_approve_flag,'N')
from po_document_types
where document_type_code = p_document_type_code
and document_subtype = p_document_subtype;
select org_id
from po_requisition_headers_all
where requisition_header_id = document_id;
select org_id
from po_headers_all
where po_header_id = document_id;
select org_id
from po_releases_all
where po_release_id = document_id;
select ORIG_SYSTEM_ID
from wf_users WF
where WF.name = p_username
and ORIG_SYSTEM NOT IN ('HZ_PARTY', 'POS', 'ENG_LIST', 'CUST_CONT');
PROCEDURE InsertActionHistSubmit(itemtype varchar2, itemkey varchar2,
p_doc_id number, p_doc_type varchar2,
p_doc_subtype varchar2, p_employee_id number,
p_action varchar2, p_note varchar2,
p_path_id number) is
pragma AUTONOMOUS_TRANSACTION;
select max(sequence_num)
from po_action_history
where object_id= doc_id and
object_type_code = doc_type;
select action_code
from po_action_history
where object_id = doc_id and
object_type_code = doc_type and
sequence_num = seq_num;
** First insert a row with a SUBMIT action.
** Then insert a row with a NULL ACTION_CODE to simulate the forward-to
** since the doc status has been changed to IN PROCESS.
*/
x_progress := '001';
select NVL(authorization_status, 'INCOMPLETE') into l_auth_stat
from PO_REQUISITION_HEADERS
where requisition_header_id = p_doc_id;
select NVL(authorization_status,'INCOMPLETE'),revision_num
into l_auth_stat, l_revision_num
from PO_HEADERS
where po_header_id = p_doc_id;
select NVL(authorization_status,'INCOMPLETE'),revision_num
into l_auth_stat, l_revision_num
from PO_RELEASES
where po_release_id = p_doc_id;
INSERT into PO_ACTION_HISTORY
(object_id,
object_type_code,
object_sub_type_code,
sequence_num,
last_update_date,
last_updated_by,
creation_date,
created_by,
action_code,
action_date,
employee_id,
note,
object_revision_num,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
approval_path_id,
offline_code)
VALUES
(p_doc_id,
p_doc_type,
p_doc_subtype,
l_sequence_num,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
p_action,
decode(p_action, '',to_date(null), sysdate),
p_employee_id,
p_note,
l_revision_num,
fnd_global.login_id,
0,
0,
0,
'',
l_approval_path_id,
'' );
UPDATE PO_ACTION_HISTORY
set object_id = p_doc_id,
object_type_code = p_doc_type,
object_sub_type_code = p_doc_subtype,
sequence_num = l_sequence_num,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
creation_date = sysdate,
created_by = fnd_global.user_id,
action_code = p_action,
action_date = decode(p_action, '',to_date(null), sysdate),
employee_id = p_employee_id,
note = p_note,
object_revision_num = l_revision_num,
last_update_login = fnd_global.login_id,
request_id = 0,
program_application_id = 0,
program_id = 0,
program_update_date = '',
approval_path_id = l_approval_path_id,
offline_code = ''
WHERE
object_id= p_doc_id and
object_type_code = p_doc_type and
sequence_num = l_sequence_num;
INSERT into PO_ACTION_HISTORY
(object_id,
object_type_code,
object_sub_type_code,
sequence_num,
last_update_date,
last_updated_by,
creation_date,
created_by,
action_code,
action_date,
employee_id,
note,
object_revision_num,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
approval_path_id,
offline_code)
VALUES
(p_doc_id,
p_doc_type,
p_doc_subtype,
l_sequence_num + 1,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
NULL, -- ACTION_CODE
decode(p_action, '',to_date(null), sysdate),
p_employee_id,
NULL,
l_revision_num,
fnd_global.login_id,
0,
0,
0,
'',
0,
'' );
wf_core.context('PO_REQAPPROVAL_INIT1','InsertActionHistSubmit',x_progress);
END InsertActionHistSubmit;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
select nvl(apps_source_code, 'PO')
into l_apps_source_code
from po_requisition_headers_all
where requisition_header_id=l_document_id;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
select st.DISPLAYED_FIELD || ' ' ||
ty.DISPLAYED_FIELD || ' ' ||
hd.SEGMENT1
into l_doc_string
from po_headers hd,
po_lookup_codes ty,
po_lookup_codes st
where hd.po_header_id = l_document_id
and ty.lookup_type = 'DOCUMENT TYPE'
and ty.lookup_code = l_document_type
and st.lookup_type = 'DOCUMENT SUBTYPE'
and st.lookup_code = hd.TYPE_LOOKUP_CODE;
select st.DISPLAYED_FIELD || ' ' ||
ty.DISPLAYED_FIELD || ' ' ||
hd.SEGMENT1
into l_doc_string
from po_requisition_headers hd,
po_lookup_codes ty,
po_lookup_codes st
where hd.requisition_header_id = l_document_id
and ty.lookup_type = 'DOCUMENT TYPE'
and ty.lookup_code = l_document_type
and st.lookup_type = 'REQUISITION TYPE'
and st.lookup_code = hd.TYPE_LOOKUP_CODE;
select st.DISPLAYED_FIELD || ' ' ||
ty.DISPLAYED_FIELD || ' ' ||
hd.SEGMENT1 || '-' ||
rl.RELEASE_NUM
into l_doc_string
from po_headers hd,
po_releases rl,
po_lookup_codes ty,
po_lookup_codes st
where rl.po_release_id = l_document_id
and rl.po_header_id = hd.po_header_id
and ty.lookup_type = 'DOCUMENT TYPE'
and ty.lookup_code = l_document_type
and st.lookup_type = 'DOCUMENT SUBTYPE'
and st.lookup_code = rl.RELEASE_TYPE;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
select to_char(PO_WF_ITEMKEY_S.NEXTVAL) into l_seq from sys.dual;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
update wf_notifications set status = 'CLOSED'
where notification_id in (
select ias.notification_id
from wf_item_activity_statuses ias,
wf_notifications ntf
where ias.item_type = itemtype
and ias.item_key = itemkey
and ntf.notification_id = ias.notification_id);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Select count(*)
into l_error_count
from wf_items
where parent_item_type=l_item_type
and parent_item_key = l_item_key;
select type_name
from po_document_types
where document_type_code = p_doc_type
and document_subtype = p_doc_subtype;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
This may not be correct since acceptance_requried_flag may be updated in the DB.
Thus, we shall query acceptance_required_flag from po_headers/po_releases view.
*/
x_progress := '001';
select acceptance_required_flag
into l_acceptance_flag
from po_headers_all --bug 4764963
where po_header_Id = l_document_id;
select acceptance_required_flag
into l_acceptance_flag
from po_releases_all --bug 4764963
where po_release_Id = l_document_id;
select revision_num
into l_revision_num
from po_headers_all
where po_header_id = l_document_id;
select count(*)
into l_responded_shipments
from PO_ACCEPTANCES
where po_header_id = l_document_id and
revision_num = l_revision_num;
select revision_num
into l_revision_num
from po_releases_all
where po_release_id = l_document_id;
select count(*)
into l_responded_shipments
from PO_ACCEPTANCES
where po_release_id = l_document_id and
revision_num = l_revision_num;
select pov.vendor_name,poh.revision_num
into x_vendor,x_revision_num -- RDP
from po_vendors pov,po_headers poh
where pov.vendor_id = poh.vendor_id
and poh.po_header_id=x_document_id;
select pov.vendor_name, poh.po_header_id, por.revision_num --RDP
into x_vendor,x_po_header_id,x_revision_num
from po_releases por,
po_headers_all poh, --
po_vendors pov
where por.po_release_id = x_document_id
and por.po_header_id = poh.po_header_id
and poh.vendor_id = pov.vendor_id;
select a.notification_id, a.recipient_role
INTO l_nid, l_ntf_role_name
from wf_notifications a,
wf_item_activity_statuses wa
where itemkey=wa.item_key
and itemtype=wa.item_type
and a.message_name in ('PO_EMAIL_PO_WITH_RESPONSE', 'PO_EMAIL_PO_PDF_WITH_RESPONSE')
and a.notification_id=wa.notification_id and a.status = 'CLOSED';
select a.notification_id, a.recipient_role
INTO l_nid, l_ntf_role_name
from wf_notifications a,
wf_item_activity_statuses wa
where itemkey=wa.item_key
and itemtype=wa.item_type
and a.message_name in ('PO_EMAIL_PO_WITH_RESPONSE', 'PO_EMAIL_PO_PDF_WITH_RESPONSE')
and a.notification_id=wa.notification_id and a.status = 'CLOSED';
Insert_Acc_Rejection_Row(itemtype, itemkey, actid, 'Y');
select
pov.vendor_name,
poh.revision_num
into
x_vendor,
l_revision_num
from po_vendors pov,po_headers poh
where pov.vendor_id = poh.vendor_id
and poh.po_header_id=x_document_id;
select
pov.vendor_name,
por.revision_num
into
x_vendor,
l_revision_num
from po_releases por,
po_headers_all poh, --
po_vendors pov
where por.po_release_id = x_document_id
and por.po_header_id = poh.po_header_id
and poh.vendor_id = pov.vendor_id;
select a.notification_id, a.recipient_role
INTO l_nid, l_ntf_role_name
from wf_notifications a,
wf_item_activity_statuses wa
where itemkey=wa.item_key
and itemtype=wa.item_type
and a.message_name in ('PO_EMAIL_PO_WITH_RESPONSE', 'PO_EMAIL_PO_PDF_WITH_RESPONSE')
and a.notification_id=wa.notification_id and a.status = 'CLOSED';
select a.notification_id, a.recipient_role
INTO l_nid, l_ntf_role_name
from wf_notifications a,
wf_item_activity_statuses wa
where itemkey=wa.item_key
and itemtype=wa.item_type
and a.message_name in ('PO_EMAIL_PO_WITH_RESPONSE', 'PO_EMAIL_PO_PDF_WITH_RESPONSE')
and a.notification_id=wa.notification_id and a.status = 'CLOSED';
Insert_Acc_Rejection_Row(itemtype, itemkey, actid, 'N');
Procedure Insert_Acc_Rejection_Row(itemtype in varchar2,
itemkey in varchar2,
actid in number,
flag in varchar2)
is
x_row_id varchar2(30);
l_Last_Update_Login PO_ACCEPTANCES.last_update_login%TYPE;
l_Last_Update_Date PO_ACCEPTANCES.last_update_date%TYPE;
l_Last_Updated_By PO_ACCEPTANCES.last_updated_by%TYPE;
select revision_num,agent_id
into x_revision_num,x_employee_id
from po_headers
where po_header_id = x_document_id;
select po_header_id, revision_num,agent_id
into x_Po_Header_Id, x_revision_num,x_employee_id
from po_releases
where po_release_id = x_document_id;
select user_id into l_Last_Updated_By
from fnd_user
where user_name = upper(l_rspndr_usr_name);
l_Last_Updated_By := x_created_by;
l_Last_Update_Login := l_Last_Updated_By;
PO_ACCEPTANCES_INS_PVT.insert_row(
x_rowid => l_rowid,
x_acceptance_id => l_acceptance_id,
x_Last_Update_Date => l_Last_Update_Date,
x_Last_Updated_By => l_Last_Updated_By,
x_Last_Update_Login => l_Last_Update_Login,
p_creation_date => x_Creation_Date,
p_created_by => l_Last_Updated_By,
p_po_header_id => l_acc_po_header_id,
p_po_release_id => x_Po_Release_Id,
p_action => x_Action,
p_action_date => x_Action_Date,
p_employee_id => null,
p_revision_num => x_Revision_Num,
p_accepted_flag => x_Accepted_Flag,
p_note => x_acceptance_note,
p_accepting_party => l_accepting_party
);
update po_releases
set acceptance_required_flag = 'N',
LAST_UPDATE_DATE = SYSDATE,
acceptance_due_date = ''
where po_release_id = x_po_release_id;
update po_headers
set acceptance_required_flag = 'N',
LAST_UPDATE_DATE = SYSDATE,
acceptance_due_date = ''
where po_header_id = x_po_header_id;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
procedure MassUpdate_Releases_Yes_No( itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out NOCOPY varchar2 ) IS
l_orgid number;
l_massupdate_releases varchar2(2);
l_progress := 'PO_REQAPPROVAL_INIT1.MassUpdate_Releases_Yes_No: 01';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
l_massupdate_releases := PO_WF_UTIL_PKG.GetItemAttrText (itemtype => itemtype,
itemkey => itemkey,
aname => 'MASSUPDATE_RELEASES');
IF (nvl(l_massupdate_releases,'N') <> 'Y') THEN
l_massupdate_releases := 'N';
l_massupdate_releases := 'Y';
l_massupdate_releases := 'N';
resultout := wf_engine.eng_completed || ':' || l_massupdate_releases;
l_progress := 'PO_REQAPPROVAL_INIT1.MassUpdate_Releases_Yes_No: 02. Result= ' || l_massupdate_releases;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
l_massupdate_releases := 'N';
resultout := wf_engine.eng_completed || ':' || l_massupdate_releases;
END MassUpdate_Releases_Yes_No;
procedure MassUpdate_Releases_Workflow( itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out NOCOPY varchar2 ) is
l_document_id po_headers_all.po_header_id%type;
l_update_releases varchar2(1) := 'Y';
l_communicate_update varchar2(30); -- Bug 3574895. Length same as that on the form field PO_APPROVE.COMMUNICATE_UPDATES
l_progress := 'PO_REQAPPROVAL_INIT1.MassUpdate_Releases_Workflow: 01';
select poh.vendor_id, poh.vendor_site_id
into l_vendor_id, l_vendor_site_id
from po_headers poh
where poh.po_header_id = l_document_id;
SELECT structure_id
INTO l_category_struct_id
FROM mtl_default_sets_view
WHERE functional_area_id = 2 ;
l_progress := 'PO_REQAPPROVAL_INIT1.MassUpdate_Releases_Workflow: Could not find Category Structure Id';
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
l_communicate_update := PO_WF_UTIL_PKG.GetItemAttrText (itemtype => itemtype,
itemkey => itemkey,
aname => 'CO_H_RETROACTIVE_SUPPLIER_COMM');
PO_RETROACTIVE_PRICING_PVT. MassUpdate_Releases
( p_api_version => 1.0,
p_validation_level => 100,
p_vendor_id => l_vendor_id,
p_vendor_site_id => l_vendor_site_id ,
p_po_header_id => l_document_id,
p_category_struct_id => l_category_struct_id, -- Bug 3592705
p_category_from => null,
p_category_to => null,
p_item_from => null,
p_item_to => null,
p_date => null,
p_communicate_update => l_communicate_update, --Bug 3574895
x_return_status => l_return_status);
l_update_releases := 'N';
l_progress := ': 02. Result= ' || l_update_releases;
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
resultout := wf_engine.eng_completed || ':' || l_update_releases;
l_update_releases := 'N';
l_progress := 'PO_REQAPPROVAL_INIT1.MassUpdate_Releases_Workflow: 03.'||
' Result= ' || l_update_releases;
resultout := wf_engine.eng_completed || ':' || l_update_releases;
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
END MassUpdate_Releases_Workflow;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
* This procedure basically checks if archive_on_print option is selected, and if yes
* call procedure PO_ARCHIVE_PO_SV.ARCHIVE_PO to archive the PO
*************************************************************************************/
procedure archive_po(p_document_id in number,
p_document_type in varchar2,
p_document_subtype in varchar2)
IS
--
l_return_status varchar2(1) ;
* Effects: This procedure updates invoice adjustment flag, and calls Costing
* and Inventory APIs.
*/
PROCEDURE Retro_Invoice_Release_WF( itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
resultout OUT NOCOPY VARCHAR2)
IS
l_retro_change VARCHAR2(1);
l_update_releases VARCHAR2(1) := 'Y';
l_retroactive_update VARCHAR2(30) := 'NEVER';
l_reset_retro_update BOOLEAN := FALSE;
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
resultout := wf_engine.eng_completed || ':' || l_update_releases;
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
l_retro_change := PO_RETROACTIVE_PRICING_PVT.Is_Retro_Update(
p_document_id => l_document_id,
p_document_type => l_document_type);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
l_retroactive_update := PO_RETROACTIVE_PRICING_PVT.Get_Retro_Mode;
'l_retroactive_update = ' || l_retroactive_update;
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
l_reset_retro_update := TRUE;
IF (l_retroactive_update = 'NEVER') THEN
l_retro_change := 'N';
END IF; /*IF (l_retroactive_update = 'NEVER')*/
'l_retroactive_update = ' || l_retroactive_update ||
'l_retro_change = ' || l_retro_change ;
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
IF (l_retro_change = 'Y' AND l_retroactive_update = 'ALL_RELEASES') THEN
l_progress := 'PO_REQAPPROVAL_INIT1.Retro_Invoice_Release_WF: 06. Calling ' ||
'PO_RETROACTIVE_PRICING_PVT.Retro_Invoice_Release';
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
l_update_releases := 'N';
l_update_releases;
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
END IF; /*IF (l_retro_change = 'Y' AND l_retroactive_update = 'ALL_RELEASES')*/
IF (l_reset_retro_update) THEN
l_progress := 'PO_REQAPPROVAL_INIT1.Retro_Invoice_Release_WF: 08. Reset_Retro_Update';
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
PO_RETROACTIVE_PRICING_PVT.Reset_Retro_Update(
p_document_id => l_document_id,
p_document_type => l_document_type);
END IF; /*IF (l_reset_retro_update)*/
resultout := wf_engine.eng_completed || ':' || l_update_releases;
l_update_releases := 'N';
' Result= ' || l_update_releases;
resultout := wf_engine.eng_completed || ':' || l_update_releases;
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Update the Action History with a note ICX_POR_NOTIF_TIMEOUT in approvers
language
*/
PROCEDURE UpdateActionHistory(p_doc_id IN number,
p_doc_type IN varchar2,
p_doc_subtype IN varchar2,
p_action IN varchar2
) is
pragma AUTONOMOUS_TRANSACTION;
SELECT pah.employee_id, pah.ROWID
INTO l_emp_id, l_rowid
FROM po_action_history pah
WHERE pah.object_id = p_doc_id
AND pah.object_type_code = p_doc_type
AND pah.object_sub_type_code = p_doc_subtype
AND pah.sequence_num = (SELECT Max(sequence_num)
FROM po_action_history pah1
WHERE pah1.object_id = p_doc_id
AND pah1.object_type_code = p_doc_type
AND pah1.object_sub_type_code = p_doc_subtype)
AND pah.action_code is NULL;
SELECT message_text
INTO l_note
FROM fnd_new_messages fm,
fnd_languages fl
WHERE fm.message_name = 'ICX_POR_NOTIF_TIMEOUT'
AND fm.language_code = fl.language_code
AND fl.nls_language = l_language;
UPDATE po_action_history pah
SET pah.action_code = p_action,
pah.action_date = SYSDATE,
pah.Note = l_note,
pah.last_updated_by = fnd_global.user_id,
pah.last_update_login = fnd_global.login_id,
pah.last_update_date = SYSDATE
WHERE ROWID = l_rowid;
PO_WF_DEBUG_PKG.insert_debug(p_item_type,p_item_key,l_progress);
PO_WF_DEBUG_PKG.insert_debug(p_item_type,p_item_key,'Item Attribute value for CATALOG_ADMIN_USER_NAME='|| l_cat_admin_user_name);
PO_WF_DEBUG_PKG.insert_debug(p_item_type,p_item_key,l_progress);
PO_WF_DEBUG_PKG.insert_debug(p_item_type,p_item_key,l_progress);
select lock_owner_user_id, lock_owner_role
into l_lock_owner_user_id, l_lock_owner_role
from po_headers_all
where po_header_id = p_doc_id;
PO_WF_DEBUG_PKG.insert_debug(p_item_type,p_item_key,l_progress);
select user_name into l_user_name
from fnd_user
where user_id = l_lock_owner_user_id;
PO_WF_DEBUG_PKG.insert_debug(p_item_type,p_item_key,l_progress);
'poHideUpdate=Y'|| '&' ||
'poCallingNotifId=-&#NID-'|| '&' ||
'retainAM=Y' || '&' || 'addBreadCrumb=Y' ;
* the POREQ_SELECTOR know we should be in the supplier's context
* and not reset to the buyer's context
* Requires:
* IN PARAMETERS:
* Usual workflow attributes.
* Modifies: Sets the workflow attribute IS_SUPPLIER_CONTEXT to Y
*/
-- Commenting this code. Most likely will not be required with our context Setting fix.
/* procedure set_is_supplier_context_y(p_item_type in varchar2,
p_item_key in varchar2,
p_act_id in number,
p_func_mode in varchar2,
x_result_out out NOCOPY varchar2) is
l_progress VARCHAR2(300);
PO_WF_DEBUG_PKG.insert_debug(p_item_type, p_item_key, l_progress || 'Begin');
PO_WF_DEBUG_PKG.insert_debug(p_item_type, p_item_key, l_progress || 'End');
PO_WF_DEBUG_PKG.insert_debug(p_item_type, p_item_key, l_progress || 'Unexpected error');
* the POREQ_SELECTOR know we are no longer in the suppliers
* context.
* Requires:
* IN PARAMETERS:
* Usual workflow attributes.
* Modifies: Sets the workflow attribute IS_SUPPLIER_CONTEXT to N
*/
/* procedure set_is_supplier_context_n(p_item_type in varchar2,
p_item_key in varchar2,
p_act_id in number,
p_func_mode in varchar2,
x_result_out out NOCOPY varchar2) is
l_progress VARCHAR2(300);
PO_WF_DEBUG_PKG.insert_debug(p_item_type, p_item_key, l_progress || 'Begin');
PO_WF_DEBUG_PKG.insert_debug(p_item_type, p_item_key, l_progress || 'End');
PO_WF_DEBUG_PKG.insert_debug(p_item_type, p_item_key, l_progress || 'Unexpected error');
PROCEDURE update_print_count( p_doc_id NUMBER,
p_doc_type VARCHAR2 )
IS PRAGMA AUTONOMOUS_TRANSACTION;
UPDATE po_releases_all pr
SET pr.printed_date = sysdate, pr.print_count = nvl(pr.print_count,0) + 1
WHERE pr.po_release_id = p_doc_id ;
UPDATE po_headers_all ph
SET ph.printed_date = sysdate, ph.print_count = nvl(ph.print_count,0) + 1
WHERE ph.po_header_id = p_doc_id ;
** Public Procedure: Update_Action_History_TimeOut
** Requires:
** IN PARAMETERS:
** Usual workflow attributes.
** Modifies: Action History
** Effects: Actoin History is updated with No Action if the approval
** notification is TimedOut.
*/
PROCEDURE Update_Action_History_Timeout (Itemtype IN VARCHAR2,
Itemkey IN VARCHAR2,
Actid IN NUMBER,
Funcmode IN VARCHAR2,
Resultout OUT NOCOPY VARCHAR2) IS
L_Doc_Id NUMBER;
UpdateActionHistory ( p_doc_id => L_Doc_Id,
p_doc_type => L_Doc_Type,
p_doc_subtype => L_Doc_Subtype,
p_action => 'NO ACTION'
);
END Update_Action_History_Timeout;
* the POREQ_SELECTOR know we should be in the supplier's context
* and not reset to the buyer's context
* Requires:
* IN PARAMETERS:
* Usual workflow attributes.
* Modifies: Sets the workflow attribute IS_SUPPLIER_CONTEXT to Y
*/
procedure set_is_supplier_context_y(p_item_type in varchar2,
p_item_key in varchar2,
p_act_id in number,
p_func_mode in varchar2,
x_result_out out NOCOPY varchar2) is
l_progress VARCHAR2(300);
PO_WF_DEBUG_PKG.insert_debug(p_item_type, p_item_key, l_progress || 'Begin');
PO_WF_DEBUG_PKG.insert_debug(p_item_type, p_item_key, l_progress || 'End');
PO_WF_DEBUG_PKG.insert_debug(p_item_type, p_item_key, l_progress || 'Unexpected error');
* the POREQ_SELECTOR know we are no longer in the suppliers
* context.
* Requires:
* IN PARAMETERS:
* Usual workflow attributes.
* Modifies: Sets the workflow attribute IS_SUPPLIER_CONTEXT to N
*/
procedure set_is_supplier_context_n(p_item_type in varchar2,
p_item_key in varchar2,
p_act_id in number,
p_func_mode in varchar2,
x_result_out out NOCOPY varchar2) is
l_progress VARCHAR2(300);
PO_WF_DEBUG_PKG.insert_debug(p_item_type, p_item_key, l_progress || 'Begin');
PO_WF_DEBUG_PKG.insert_debug(p_item_type, p_item_key, l_progress || 'End');
PO_WF_DEBUG_PKG.insert_debug(p_item_type, p_item_key, l_progress || 'Unexpected error');