[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE NAME: Select_Unapprove_docs
DESCRIPTION: This server procedure is defined as a concurrent
PL/SQL executable program and is scheduled to run
from the Concurrent Manager at a regular intervals
(e.g. every day).
CHANGE HISTORY: WLAU 7/15/1997 Created
===========================================================================*/
PROCEDURE Select_Unapprove_docs IS
l_ItemType VARCHAR2(100) := 'APVRMDER';
l_progress := 'PO_APPROVAL_REMINDER_SV.Select_Unapprove_docs: 01 - BEGIN ';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
l_progress := 'PO_APPROVAL_REMINDER_SV.Select_Unapprove_docs: 900 - END ';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
wf_core.context ('PO_APPROVAL_REMINDER_SV','Select_Unapprove_docs ' || l_progress);
l_progress := 'PO_APPROVAL_REMINDER_SV.Select_Unapprove_docs: 990 - ' ||
'EXCEPTION sql error: ' || sqlcode;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
END Select_Unapprove_docs;
- Open cursor PO_REQUISITION_HEADERS table to select
Incomplete or Requires_reapproval documents.
- For each unapprove document, initiate the
PO Approval Reminder workflow notification.
CHANGE HISTORY: WLAU 7/15/1997 Created
===========================================================================*/
PROCEDURE Process_unapprove_reqs IS
-- Define cursor for selecting unapprove document to start the Purchasing
-- Approval Reminder workflow process.
--
CURSOR unapprove_req IS
SELECT Requisition_Header_ID, Segment1, Type_Lookup_Code, Preparer_ID
FROM PO_REQUISITION_HEADERS prh
WHERE NVL(authorization_status,'INCOMPLETE') IN
('INCOMPLETE','REJECTED','REQUIRES REAPPROVAL', 'RETURNED')
AND NVL(cancel_flag,'N') = 'N'
AND NVL(closed_code,'OPEN') <> 'FINALLY CLOSED'
-- Bug fix 14083505 - START: Cancelled lines and the lines which are already part of a PO can be ignored
AND EXISTS
(
SELECT 1
FROM po_requisition_lines prl
WHERE
prl.REQUISITION_HEADER_ID = prh.REQUISITION_HEADER_ID
AND Nvl(prl.CANCEL_FLAG,'N') <>'Y'
AND NVL(prl.closed_code,'OPEN') <> 'FINALLY CLOSED' --Bug 14363353 fix
AND prl.LINE_LOCATION_ID IS NULL
);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
-- /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
- Open cursor PO_HEADERS table to select
Incomplete or Requires_reapproval documents.
- For each unapprove document, initiate the
PO Approval Reminder workflow notification.
CHANGE HISTORY: WLAU 7/15/1997 Created
===========================================================================*/
PROCEDURE Process_unapprove_pos IS
-- Define cursor for selecting unapprove document to start the Purchasing
-- Approval Reminder workflow process.
--
CURSOR unapprove_PO IS
SELECT PO_Header_ID, Segment1, Type_Lookup_Code, Agent_ID
FROM PO_HEADERS
WHERE NVL(authorization_status,'INCOMPLETE') IN
('INCOMPLETE','REJECTED','REQUIRES REAPPROVAL')
-- AND WF_ITEM_TYPE = NULL
-- AND WF_ITEM_KEY = NULL
AND type_lookup_code in ('STANDARD','PLANNED','BLANKET','CONTRACT')
AND NVL(cancel_flag,'N') = 'N'
AND NVL(closed_code,'OPEN') <> 'FINALLY CLOSED';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
-- /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
- Open cursor PO_RELEASES table to select
Incomplete or Requires_reapproval documents.
- For each unapprove document, initiate the
PO Approval Reminder workflow notification.
CHANGE HISTORY: WLAU 7/15/1997 Created
===========================================================================*/
PROCEDURE Process_unapprove_releases IS
-- Define cursor for selecting unapprove document to start the Purchasing
-- Approval Reminder workflow process.
--
CURSOR unapprove_REL IS
SELECT PORH.PO_release_ID, POH.Segment1, PORH.release_num,
POH.Type_Lookup_Code, PORH.Agent_ID
FROM PO_RELEASES_ALL PORH, --
PO_HEADERS POH
WHERE NVL(PORH.authorization_status,'INCOMPLETE') IN
('INCOMPLETE','REJECTED','REQUIRES REAPPROVAL')
-- AND WF_ITEM_TYPE = NULL
-- AND WF_ITEM_KEY = NULL
AND NVL(PORH.cancel_flag,'N') = 'N'
AND NVL(PORH.closed_code,'OPEN') <> 'FINALLY CLOSED'
AND POH.PO_HEADER_ID = PORH.PO_HEADER_ID;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
-- /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
- For each selected document, initiate the
PO Approval Reminder workflow notification
CHANGE HISTORY: WLAU 11/15/1997 Created
===========================================================================*/
PROCEDURE Process_po_acceptance IS
-- Define cursor for selecting approved POs with acceptance required
/* Bug# 1595348: kagarwal
** Desc: If the PO/Rel is accepted using Web Supplier Portal, the acceptance
** is registered in the PO Acceptances table. Hence we need to check the PO
** Acceptances table also.
*/
-- Bug 5074128(forward fix4772820)
-- poh.acceptance_required_flag can be Y or D
CURSOR PO_acceptance IS
SELECT poh.PO_Header_ID, poh.Segment1, poh.Type_Lookup_Code,
poh.Agent_ID, NVL(poh.Acceptance_Due_Date, SYSDATE)
FROM PO_HEADERS poh
WHERE NVL(poh.authorization_status,'INCOMPLETE') = 'APPROVED'
AND NVL(poh.acceptance_required_flag,'N') in ('Y','D')
AND poh.type_lookup_code in
('STANDARD','PLANNED','BLANKET','CONTRACT')
AND NVL(poh.cancel_flag,'N') = 'N'
AND NVL(poh.closed_code,'OPEN') <> 'FINALLY CLOSED'
AND not exists (
SELECT poa.ACCEPTANCE_ID
FROM PO_ACCEPTANCES poa
WHERE NVL(poa.accepted_flag, 'N') = 'Y'
AND poa.po_header_id = poh.po_header_id
AND nvl(poa.revision_num,0) = nvl(poh.revision_num,0));
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
select wfn.MESSAGE_NAME
into l_message_name
from wf_item_activity_statuses wias, wf_notifications wfn
where wias.notification_id = wfn.group_id
and wias.item_type = 'APVRMDER'
and wias.item_key = l_ItemKey ;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
select wfn.MESSAGE_NAME
into l_message_name
from wf_item_activity_statuses wias, wf_notifications wfn
where wias.notification_id = wfn.group_id
and wias.item_type = 'APVRMDER'
and wias.item_key = l_ItemKey ;
PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
- For each selected document, initiate the
PO Approval Reminder workflow notification
CHANGE HISTORY: WLAU 11/15/1997 Created
===========================================================================*/
PROCEDURE Process_rel_acceptance IS
-- Define cursor for selecting approved releases with acceptance required
/* Bug# 1595348: kagarwal
** Desc: If the PO/Rel is accepted using Web Supplier Portal, the acceptance
** is registered in the PO Acceptances table. Hence we need to check the PO
** Acceptances table also.
*/
/* Bug# 2633688: kagarwal
** Desc: When accepting releases from ISP, the po header id is
** left null in the po acceptances table (See Bug 2188005) hence
** removing the condition 'poa.po_header_id = poh.po_header_id'
*/
CURSOR REL_acceptance IS
SELECT PORH.PO_release_ID, POH.Segment1, PORH.release_num,
POH.Type_Lookup_Code, PORH.Agent_ID,
NVL(PORH.Acceptance_Due_Date, SYSDATE)
FROM PO_RELEASES_ALL PORH, --
PO_HEADERS POH
WHERE NVL(PORH.authorization_status,'INCOMPLETE') = 'APPROVED'
AND NVL(PORH.acceptance_required_flag,'N')= 'Y'
AND NVL(PORH.cancel_flag,'N') = 'N'
AND NVL(PORH.closed_code,'OPEN') <> 'FINALLY CLOSED'
AND POH.PO_HEADER_ID = PORH.PO_HEADER_ID
AND not exists (
SELECT poa.ACCEPTANCE_ID
FROM PO_ACCEPTANCES poa
WHERE NVL(poa.accepted_flag, 'N') = 'Y'
/* AND poa.po_header_id = poh.po_header_id */
AND porh.po_release_id = poa.po_release_id
AND nvl(poa.revision_num,0) = nvl(porh.revision_num,0));
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
select wfn.MESSAGE_NAME
into l_message_name
from wf_item_activity_statuses wias, wf_notifications wfn
where wias.notification_id = wfn.group_id
and wias.item_type = 'APVRMDER'
and wias.item_key = l_ItemKey ;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
select wfn.MESSAGE_NAME
into l_message_name
from wf_item_activity_statuses wias, wf_notifications wfn
where wias.notification_id = wfn.group_id
and wias.item_type = 'APVRMDER'
and wias.item_key = l_ItemKey ;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
- Open cursor PO_HEADERS table to select
RFQ and Quote documents.
- For each selected document, initiate the
PO Approval Reminder workflow notification.
CHANGE HISTORY: WLAU 11/15/1997 Created
===========================================================================*/
PROCEDURE Process_rfq_quote IS
-- Define cursor for selecting RFQ and Quote documents to start the Purchasing
-- Approval Reminder workflow process.
--
/* Bug# 1541123: kagarwal
** Desc: If the End_date for RFQ or Quotation is null
** then it means that the RFQ or Quote does not expire
** hence we need to change the nvl value of End_date
** to SYSDATE + 1.
**
** Also changing for Reply_date and RFQ_close_date
*/
/* Bug# 1764388: kagarwal
** Desc: If the End_date for Quotation is null then it means that the Quote
** does not expire. In this case we should not consider the Quote_warning_delay
** in the CURSOR RFQ_QUOTE.
*/
CURSOR RFQ_QUOTE IS
SELECT PO_Header_ID,
Segment1,
Type_Lookup_Code,
Quote_type_lookup_code,
Agent_id,
Status_lookup_code,
NVL(Reply_date,SYSDATE + 1),
NVL(RFQ_close_date,SYSDATE + 1),
NVL(End_date,SYSDATE + 1),
decode(End_date, NULL, 0, NVL(Quote_warning_delay,0))
Quote_warning_delay
FROM PO_HEADERS
WHERE NVL(Status_lookup_code,'I') IN ('I','A','P')
AND type_lookup_code in ('RFQ','QUOTATION');
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
--/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
--/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
--/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
--/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
--/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
-- /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
select authorization_status into l_req_status
from po_requisition_headers
where requisition_header_id = l_doc_header_id;
SELECT NVL(acceptance_due_date,SYSDATE)
INTO l_acceptance_due_date
FROM PO_HEADERS
WHERE po_header_id = l_doc_header_id;
SELECT NVL(acceptance_due_date, SYSDATE)
INTO l_acceptance_due_date
FROM PO_RELEASES
WHERE po_release_id = l_doc_header_id;
SELECT NVL(Reply_date,SYSDATE),
NVL(RFQ_close_date,SYSDATE),
NVL(End_date,SYSDATE),
NVL(Quote_warning_delay,0),
Status_lookup_code,
Quote_type_lookup_code
INTO l_rfq_reply_date,
l_rfq_close_date,
l_quote_end_date_active,
l_quote_warning_delay,
l_status_lookup_code,
l_quote_type_lookup_code
FROM PO_HEADERS
WHERE PO_HEADER_ID = l_doc_header_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,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 user_id
from fnd_user
where employee_id = p_PreparerID;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug('APVRMDER',NULL,l_progress);
select to_char(PO_WF_ITEMKEY_S.nextval) into l_seq from sys.dual;
PO_WF_DEBUG_PKG.insert_debug('POXWARMB',NULL,l_progress);
* Deleted the sql query which selected wf_approval_itemtype and
* wf_approval_process from PO_DOCUMENT_TYPES. Instead call the
* new overloaded procedure po_approve_sv.get_document_types.
* We call this here even though this is called in start_wf_process
* since we need these values to set some workflow attributes
* before we call start_Wf_process.
*/
l_progress := 'PO_APPROVAL_REMINDER_SV.SetUpWorkFlow: 03 ' ||
p_DocumentTypeCode || p_DocumentSubtype ||
p_DocumentNumber;
PO_WF_DEBUG_PKG.insert_debug('POXWARMB',NULL,l_progress);
PO_WF_DEBUG_PKG.insert_debug('POXWARMB',NULL,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug('APVRMDER',l_itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug('APVRMDER',l_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,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
select wias.activity_status_code, wias.item_key
from wf_item_activity_statuses_v wias, wf_items_v wi
where wias.item_type = 'APVRMDER'
and wias.item_key like l_itemkey||'%'
and wias.item_type = wi.item_type
and wias.item_key = wi.item_key
and wias.activity_name = wi.root_activity;
select agent_id
into l_agent_id
from po_headers_all /*Bug6632095: using base table instead of view */
where po_header_id = p_DocumentID;
select agent_id
into l_agent_id
from po_releases_all /*Bug6632095: using base table instead of view */
where po_release_id = p_DocumentID;
select agent_id
into l_agent_id
from po_headers_all /*Bug6632095: using base table instead of view */
where po_header_id = p_DocumentID;
select preparer_id
into l_agent_id
from po_requisition_headers
where requisition_header_id = p_DocumentID;
select agent_id
into l_agent_id
from po_releases_all /*Bug6632095: using base table instead of view */
where po_release_id = p_DocumentID;
select type_lookup_code
into l_doc_subtype
from po_headers_all /*Bug6632095: using base table instead of view */
where po_header_id = p_DocumentID;
select poh.type_lookup_code
into l_doc_subtype
from po_headers_all poh, --
po_releases_all por /*Bug6632095: using base table instead of view */
where por.po_header_id = poh.po_header_id and
por.po_release_id = p_DocumentId;
PO_WF_DEBUG_PKG.insert_debug(null,null,x_progress);
PO_WF_DEBUG_PKG.insert_debug(null,null,x_progress);
-- /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_progress);
SELECT 'Y', WI.end_date
INTO p_item_exist, p_item_end_date
FROM WF_ITEMS_V WI
WHERE WI.ITEM_TYPE = p_ItemType
AND WI.ITEM_KEY = p_ItemKey;
-- /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_progress);
SELECT POL2.po_line_id,
POL2.svc_amount_notif_sent,
POL2.amount,
SUM_DATA.total_amount_billed amount_billed,
POL2.svc_completion_notif_sent,
POL2.expiration_date,
POL2.contractor_first_name,
POL2.contractor_last_name,
PJ.name job_name
FROM po_lines_all POL2,
per_jobs_vl PJ,
( SELECT PLL.po_line_id
, SUM(PLL.amount_billed) total_amount_billed
FROM po_line_locations PLL
, po_lines_all POL
, po_headers_all poh
WHERE poh.type_lookup_code = 'STANDARD'
AND poh.po_header_id = POL.po_header_id
AND POL.purchase_basis = 'TEMP LABOR'
AND POL.po_line_id = PLL.po_line_id
AND NVL(PLL.approved_flag, 'N') = 'Y'
AND NVL(PLL.cancel_flag, 'N') = 'N'
AND NVL(PLL.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND ( PLL.payment_type IS NULL
OR PLL.payment_type NOT IN ('DELIVERY','ADVANCE')
)
GROUP BY PLL.po_line_id
) SUM_DATA
WHERE POL2.po_line_id = SUM_DATA.po_line_id
AND PJ.job_id = POL2.job_id
AND ( ( p_amount_threshold IS NOT NULL
AND SUM_DATA.total_amount_billed
>= POL2.amount * p_amount_threshold / 100
)
OR ( p_completion_threshold IS NOT NULL
AND TRUNC(sysdate) >= TRUNC(POL2.expiration_date) - p_completion_threshold
)
);
SELECT user_profile_option_name
INTO l_user_profile_name
FROM FND_PROFILE_OPTIONS_VL
WHERE profile_option_name = l_profile_name;
UPDATE po_lines_all
SET svc_amount_notif_sent = 'Y',
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
WHERE po_line_id = l_tl_line_rec.po_line_id;
UPDATE po_lines_all
SET svc_completion_notif_sent = 'Y',
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
WHERE po_line_id = l_tl_line_rec.po_line_id;
select POH.po_header_id,
POH.segment1
INTO l_po_header_id,
l_document_number
FROM po_lines POL,
po_headers_all POH
WHERE POL.po_line_id = p_po_line_id
AND POL.po_header_id = POH.po_header_id;
select PRL.requisition_header_id
INTO l_req_header_id
FROM po_lines POL,
po_line_locations_all PLL,
po_requisition_lines_all PRL
WHERE POL.po_line_id = p_po_line_id
AND POL.po_line_id = PLL.po_line_id
AND PLL.line_location_id = PRL.line_location_id;