[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;
p_update_sign VARCHAR2 DEFAULT 'N');
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,
p_draft_id NUMBER DEFAULT -1); -- Mod Project
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);
OPEN l_refcur FOR '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;
OPEN l_refcur FOR '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*/
,
DraftId NUMBER DEFAULT -1 -- Mod Project
,p_bypass_checks_flag VARCHAR2 DEFAULT 'N' /*Bug 11727653: BYPASSING MULTIPLE SUBMISSION CHECKS IN WORKFLOW*/
)
IS
l_responsibility_id NUMBER;
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;
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;
SELECT draft_type
INTO l_draft_type
FROM po_drafts
WHERE draft_id = DraftID;
PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,x_progress);
SELECT decode(authorization_status,
'REJECTED', 0,
ame_approval_id),
ame_transaction_type
INTO l_ame_approval_id,
l_ame_transaction_type
FROM po_headers_all
WHERE po_header_id = DocumentID;
select po_ame_approvals_s.nextval into l_ame_approval_id from dual;
update po_headers_all
set ame_approval_id = l_ame_approval_id,
ame_transaction_type = l_ame_transaction_type
where po_header_id = DocumentID;
SELECT decode(pd.status,
'REJECTED', 0,
phda.ame_approval_id),
phda.ame_transaction_type
INTO l_ame_approval_id,
l_ame_transaction_type
FROM po_headers_draft_all phda,
po_drafts pd
WHERE phda.po_header_id = DocumentID
AND phda.draft_id = DraftID
AND phda.draft_id = pd.draft_id;
select po_ame_approvals_s.nextval into l_ame_approval_id from dual;
update po_headers_draft_all
set ame_approval_id = l_ame_approval_id
where po_header_id = DocumentID
and draft_id = DraftID;
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 MULTI_MOD_REQUEST_ID
INTO l_multi_mod_req_id
FROM PO_MULTI_MOD_DOCS
WHERE DRAFT_ID = DraftID;
SELECT MULTI_MOD_REQUEST_TYPE
INTO l_multi_mod_req_type
FROM PO_MULTI_MOD_REQUESTS
WHERE MULTI_MOD_REQUEST_ID = l_multi_mod_req_id;
SELECT pv.vendor_name, pvs.vendor_site_code
INTO l_new_vendor, l_new_vendor_site_code
FROM po_headers_draft_all phda,
po_vendors pv,
po_vendor_sites_all pvs
WHERE draft_id = DraftID
AND phda.vendor_id = pv.vendor_id
AND phda.vendor_site_id = pvs.vendor_site_id;
PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,x_progress);
PO_WF_UTIL_PKG.SetItemAttrText( itemtype => l_itemtype , itemkey => l_itemkey , aname => 'INSERT_ACTION_HIST_FLAG' , avalue => 'N' );
PO_WF_UTIL_PKG.SetItemAttrText( itemtype => l_itemtype , itemkey => l_itemkey , aname => 'INSERT_ACTION_HIST_FLAG' , avalue => 'Y' );
PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,x_progress);
SELECT PRH.segment1
INTO l_document_number
FROM po_requisition_headers PRH
WHERE PRH.requisition_header_id = DocumentID;
SELECT clm_document_number
INTO l_clm_document_number
FROM po_headers_all
WHERE po_header_id = DocumentID;
SELECT modification_number
INTO l_modification_number
FROM po_drafts
WHERE draft_id = DraftID;
PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,'l_modification_number: '||l_modification_number);
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;
Added setItemAttrNumber() for 'BUYER_USER_ID' to be used later in PO_Mass_Update_PO_PVT.
*/
BEGIN
select user_id
into emp_user_id
from fnd_user
where employee_id = x_agent_id
and rownum = 1
and sysdate < nvl(end_date, sysdate + 1);
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 0,
FND_MESSAGE.GET_STRING('PO','PO_MODIFICATION'),
NVL(CONTERMS_EXIST_FLAG,'N'),
segment1
INTO l_revision_num,
l_doc_display_name,
l_conterms_exist_flag,
l_document_number
FROM po_headers_draft_all
WHERE po_header_id = DocumentID
AND draft_id = DraftId;
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 org_id
INTO l_org_id
FROM po_headers_all
WHERE po_header_id = DocumentID;
SELECT CLM_CONTRACT_OFFICER
INTO l_clm_contract_officer
FROM po_headers_all
WHERE po_header_id = DocumentID;
SELECT CLM_CONTRACT_OFFICER
INTO l_clm_contract_officer
FROM po_drafts
WHERE draft_id = DraftID;
PO_WF_UTIL_PKG.SetItemAttrText ( itemtype => l_itemtype, itemkey => l_itemkey, aname => 'UPDATE_SOURCING_RULE', avalue => updatesourcingrule);
PO_WF_UTIL_PKG.SetItemAttrText ( itemtype => l_itemtype, itemkey => l_itemkey, 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 PRH.segment1,
PRH.federal_flag,
PRH.revision_num,
PRH.conformed_header_id
INTO l_document_number,
l_federal_flag,
l_revision_num,
l_conformed_header_id
FROM po_requisition_headers PRH
WHERE PRH.requisition_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;
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;
UPDATE po_drafts
SET STATUS = 'IN PROCESS',
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
last_update_date = sysdate
WHERE draft_id = DraftID;
PO_WF_DEBUG_PKG.insert_debug(itemtype,l_itemkey,x_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype,l_itemkey,x_progress);
l_update_req_url VARCHAR2(1000);
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;
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
l_edit_po_url := get_mod_url(p_po_header_id => l_document_id,p_draft_id => l_draft_id, p_doc_subtype => l_doc_subtype, p_mode => 'update');
l_edit_po_url := get_po_url(p_po_header_id => l_document_id, p_doc_subtype => l_doc_subtype, p_mode => 'update');
x_progress := 'PO_REQAPPROVAL_INIT1.get_po_url update' || 'l_edit_po_url ::'|| l_edit_po_url;
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
l_update_req_url := l_edit_req_url;
wf_engine.SetItemAttrText ( itemtype => itemType, itemkey => itemkey, aname => 'REQ_UPDATE_URL' , avalue => l_update_req_url);
SELECT AUTHORIZATION_STATUS
INTO l_authorization_status
FROM po_requisition_headers_all
WHERE REQUISITION_HEADER_ID = l_document_id;
SELECT DECODE(draft_id, -1, AUTHORIZATION_STATUS, STATUS),
NVL(REVISION_NUM,0)
INTO l_authorization_status,
l_po_revision
FROM po_headers_merge_v
WHERE PO_HEADER_ID = l_document_id
AND draft_id = l_draft_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;
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);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
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') ) THEN
IF l_doc_type = 'REQUISITION' THEN
SetReqAuthStat(l_document_id, itemtype,itemkey,l_note, 'IN PROCESS');
SetModUpdateAfterCDGenFlag(l_draft_id);
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);
SELECT NVL(authorization_status, 'INCOMPLETE')
INTO l_auth_stat
FROM PO_REQUISITION_HEADERS
WHERE requisition_header_id = l_doc_id;
SELECT NVL(status,'DRAFT')
INTO l_auth_stat
FROM po_drafts
WHERE draft_id = l_draft_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;
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';
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);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
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;
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
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, l_draft_id);
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);
SELECT NVL(fsp.reserve_at_completion_flag,'N')
INTO l_reserve_at_compl
FROM financials_system_parameters fsp;
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,'l_reserve_at_compl=' || l_reserve_at_compl );
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
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_headers_merge_v
WHERE po_header_id= p_header_id
AND draft_id = p_draft_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;
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);
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);
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);
SELECT acceptance_required_flag
INTO l_acceptance_flag
FROM po_headers_all
WHERE po_header_Id = l_document_id;
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);
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;
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, pv.vendor_name
INTO l_vendor_site_id,
l_vendor_site_code,
l_vendor_site_lang, l_vendor_name
FROM po_headers poh,
po_vendor_sites pvs, po_vendors pv
WHERE pvs.vendor_site_id = poh.vendor_site_id
AND pv.vendor_id = poh.vendor_id
AND poh.po_header_id = l_po_header_id;
SELECT wfl.nls_language,
wfl.nls_territory,
wfl.code
INTO l_adhocuser_lang,
l_adhocuser_territory,
l_lang_code
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,
wfl.code
INTO l_adhocuser_lang,
l_adhocuser_territory,
l_lang_code
FROM wf_languages wfl,
fnd_languages_vl flv
WHERE wfl.code = flv.language_code
AND flv.installed_flag = 'B';
SELECT message_text
INTO l_note
FROM fnd_new_messages fm,
fnd_languages fl
WHERE fm.message_name = 'PO_PDF_EMAIL_TEXT'
AND fm.language_code = fl.language_code
AND fl.nls_language = l_vendor_site_lang;
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);
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);
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;
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);
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);
SELECT default_rate_type INTO l_rate_type FROM po_system_parameters;
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;
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
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';
SELECT clm_mipr_type
FROM po_requisition_headers
WHERE requisition_header_id = p_doc_id
AND NVL(cancel_flag,'N') = 'N';
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);
UPDATE po_headers_all
SET comm_rev_num = l_po_revision_num_curr
WHERE po_header_id = l_po_header_id;
UPDATE po_releases_all
SET comm_rev_num = l_po_revision_num_curr
WHERE po_release_id = l_po_header_id;
** 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;
p_update_sign VARCHAR2 DEFAULT 'N')
IS
pragma AUTONOMOUS_TRANSACTION;
** or 'PRE-APPROVED', therefore we should update the status to IN PROCESS.
*/
IF p_update_sign = 'Y' AND p_auth_status = 'PRE-APPROVED' THEN
l_pending_signature_flag := 'Y';
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) --
,
pending_signature_flag = DECODE(l_pending_signature_flag, 'Y', DECODE(acceptance_required_flag, 'S', 'Y', 'N'), pending_signature_flag)
WHERE po_header_id = l_po_header_id;
UPDATE Po_Headers_Draft_all
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 = DECODE(p_auth_status, 'INCOMPLETE', to_date(NULL),submit_date) ,
pending_signature_flag = DECODE(l_pending_signature_flag, 'Y', DECODE(acceptance_required_flag, 'S', 'Y', 'N'), pending_signature_flag)
WHERE po_header_id = l_po_header_id
AND draft_id = l_draft_id;
UPDATE Po_Drafts SET STATUS = p_auth_status WHERE draft_id = l_draft_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 ,
p_draft_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 MAX(sequence_num)
FROM po_action_history
WHERE object_id = draft_id
AND object_type_code = doc_type
AND object_sub_type_code IN ('MODIFICATION', 'POST_AWARD_REQUEST'); -- PAR Approval
SELECT action_code
FROM po_action_history
WHERE object_id = doc_id
AND object_type_code = doc_type
AND sequence_num = seq_num;
SELECT action_code
FROM po_action_history
WHERE object_id = draft_id
AND object_type_code = doc_type
AND object_sub_type_code IN ('MODIFICATION', 'POST_AWARD_REQUEST') -- PAR Approval
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,
ame_transaction_type
INTO l_auth_stat,
l_revision_num,
l_transaction_type
FROM PO_HEADERS
WHERE po_header_id = p_doc_id;
SELECT NVL(status,'INCOMPLETE'),
0,
ame_transaction_type,
draft_type -- PAR Approval
INTO l_auth_stat,
l_revision_num,
l_transaction_type,
l_draft_type
FROM PO_HEADERS_MERGE_V
WHERE po_header_id = p_doc_id
AND draft_id = l_draft_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
(
DECODE(l_draft_id,-1,l_doc_id,l_draft_id), --Mod Project
p_doc_type,
l_doc_subtype, --Mod Project
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 = DECODE(l_draft_id,-1,l_doc_id,l_draft_id), --Mod Project
object_type_code = p_doc_type,
object_sub_type_code = l_doc_subtype, --Mod Project
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 = DECODE(l_draft_id,-1,l_doc_id,l_draft_id) --Mod Project
AND object_type_code = p_doc_type
AND object_sub_type_code = l_doc_subtype --Mod Project
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
(
DECODE(l_draft_id,-1,l_doc_id,l_draft_id), --Mod Project
p_doc_type,
l_doc_subtype, --Mod Project
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,
''
);
'PO_REQAPPROVAL_INIT1','InsertActionHistSubmit',x_progress
)
;
END InsertActionHistSubmit;
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
)
;
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
)
;
SELECT NVL(apps_source_code, 'PO')
INTO l_apps_source_code
FROM po_requisition_headers_all
WHERE requisition_header_id=l_document_id;
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
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;
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
SELECT TO_CHAR(PO_WF_ITEMKEY_S.NEXTVAL) INTO l_seq FROM sys.dual;
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);
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
);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_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,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;
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
PRAGMA AUTONOMOUS_TRANSACTION; --
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;
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
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';
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;
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;
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_progress := 'PO_REQAPPROVAL_INIT1.Retro_Invoice_Release_WF: 04' || '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';
/*IF (l_retroactive_update = 'NEVER')*/
END IF;
l_progress := 'PO_REQAPPROVAL_INIT1.Retro_Invoice_Release_WF: 05' || '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_progress := 'PO_REQAPPROVAL_INIT1.Retro_Invoice_Release_WF: 07. Result= ' || l_update_releases;
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/*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);
/*IF (l_reset_retro_update)*/
resultout := wf_engine.eng_completed || ':' || l_update_releases;
l_update_releases := 'N';
l_progress := 'PO_REQAPPROVAL_INIT1.Retro_Invoice_Release_WF: 09.'|| ' 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' ;
'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
BEGIN
IF (p_doc_type = 'RELEASE') THEN
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');
SELECT draft_type
INTO l_draft_type
FROM po_drafts
WHERE document_id = DocumentId
AND draft_id = DraftId;
SELECT ds.wf_approval_itemtype,
ds.wf_approval_process,
DECODE(l_draft_type,
'MOD', ds.mod_ame_transaction_type,
'PAR', ds.par_ame_transaction_type,
ds.ame_transaction_type)
INTO l_itemtype,
l_workflow_process,
l_ame_transaction_type
FROM po_doc_style_headers ds,
po_headers_merge_v phm
WHERE phm.po_header_id = DocumentId
AND NVL(phm.draft_id, -1) = DraftId
AND phm.style_id = ds.style_id
AND ds.wf_approval_itemtype IS NOT NULL
AND ds.wf_approval_process IS NOT NULL;
PROCEDURE SetModUpdateAfterCDGenFlag
(
p_draft_id IN NUMBER) -- SGD Project
IS
pragma AUTONOMOUS_TRANSACTION;
mod_last_update_date PO_DRAFTS.last_update_date%type;
SELECT cd_generated_date
INTO changedesc_gen_date
FROM po_drafts
WHERE draft_id = p_draft_id;
mod_last_update_date := PO_CORE_S.get_last_update_date_for_mod(p_draft_id);
IF( mod_last_update_date > changedesc_gen_date) THEN
UPDATE po_drafts
SET modupdated_aftercdgenerated = 'Y'
WHERE draft_id = p_draft_id;
UPDATE po_drafts
SET modupdated_aftercdgenerated = 'N'
WHERE draft_id = p_draft_id;
wf_core.context('PO_REQAPPROVAL_INIT1','SetModUpdateAfterCDGenFlag',x_progress);
END SetModUpdateAfterCDGenFlag;
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, l_progress || 'Unexpected error');
PROCEDURE update_supplier_com_rev_num(itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN VARCHAR2,
funcmode IN VARCHAR2,
resultout OUT NOCOPY VARCHAR2) IS
l_po_header_id NUMBER;
UPDATE po_headers_all
SET comm_rev_num = l_po_revision_num_curr
WHERE po_header_id = l_po_header_id;
UPDATE po_releases_all
SET comm_rev_num = l_po_revision_num_curr
WHERE po_release_id = l_po_header_id;
l_progress := 'PO_REQAPPROVAL_INIT1.update_supplier_com_rev_num: Current PO Rev Number = '||l_po_revision_num_curr;
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, l_progress || 'Unexpected error');
END update_supplier_com_rev_num;