[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'Lock header'
INTO l_lock_row
FROM po_headers poh
WHERE poh.po_header_id = p_doc_id
FOR UPDATE NOWAIT;
SELECT 'Lock release'
INTO l_lock_row
FROM po_releases por
WHERE por.po_release_id = p_doc_id
FOR UPDATE NOWAIT;
SELECT 'Lock req'
INTO l_lock_row
FROM po_requisition_headers porh
WHERE porh.requisition_header_id = p_doc_id
FOR UPDATE NOWAIT;
SELECT authorization_status
INTO l_auth_status
FROM po_headers_all
WHERE po_header_id=p_doc_id;
SELECT authorization_status
INTO l_auth_status
FROM po_releases_all
WHERE po_release_id=p_doc_id;
SELECT podt.default_approval_path_id
INTO l_approval_path_id
FROM po_document_types podt
WHERE podt.document_type_code = p_doc_type
AND podt.document_subtype = p_doc_subtype;
SELECT authorization_status
INTO l_old_auth_status
FROM po_headers_all
WHERE po_header_id=p_doc_id;
SELECT authorization_status
INTO l_old_auth_status
FROM po_releases_all
WHERE po_release_id=p_doc_id;
PO_DEBUG.debug_stmt(d_module,l_progress,'Before PO_DELREC_PVT.create_update_delrec Call');
PO_DELREC_PVT.create_update_delrec(
p_api_version => 1.0,
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_action => 'CANCEL',
p_doc_type => p_doc_type,
p_doc_subtype => p_doc_subtype,
p_doc_id => p_doc_id,
p_line_id => p_doc_line_id,
p_line_location_id => p_doc_line_loc_id);
PO_DEBUG.debug_stmt(d_module,l_progress,'After PO_DELREC_PVT.create_update_delrec Call');
SELECT polc.displayed_field,
polc.lookup_code
FROM po_lookup_codes polc,
po_headers poh
WHERE poh.po_header_id = p_doc_id
AND polc.lookup_type = 'CONTROL ACTIONS'
AND NVL(poh.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND ( NVL(poh.cancel_flag, 'N') IN ('N','I')
OR polc.lookup_code = 'FINALLY CLOSE'
) /** FC of cancelled PO **/
/** Bug 3231524 Removed restrictions for drop ship PO. **/
AND ( ( ( ( polc.lookup_code = 'FREEZE'
AND NVL(poh.frozen_flag, 'N') = 'N'
)
OR ( polc.lookup_code = 'UNFREEZE'
AND poh.frozen_flag = 'Y'
)
)
AND NVL(poh.user_hold_flag, 'N') = 'N'
AND NVL(poh.authorization_status, 'INCOMPLETE') = 'APPROVED'
AND NVL(poh.consigned_consumption_flag, 'N') <> 'Y' /* CONSIGNED FPI */
)
OR ( polc.lookup_code = 'HOLD'
AND NVL(poh.user_hold_flag, 'N') = 'N'
AND NVL(poh.frozen_flag, 'N') = 'N'
AND NVL(poh.consigned_consumption_flag, 'N') <> 'Y' /* CONSIGNED FPI */
)
OR ( polc.lookup_code = 'RELEASE HOLD'
AND poh.user_hold_flag = 'Y'
AND NVL(poh.consigned_consumption_flag, 'N') <> 'Y' /* CONSIGNED FPI */
)
OR ( polc.lookup_code = 'CANCEL PO'
AND NVL(poh.consigned_consumption_flag, 'N') <> 'Y' /* CONSIGNED FPI */
AND NVL(poh.user_hold_flag, 'N') = 'N'
AND NVL(poh.closed_code, 'OPEN') NOT IN ('FINALLY CLOSED', 'CLOSED')
AND poh.approved_date IS NOT NULL -- bug 12347143
AND NVL(poh.authorization_status, 'INCOMPLETE') NOT IN
/* 'REQUIRES REAPPROVAL', */
('IN PROCESS', 'PRE-APPROVED', 'INCOMPLETE')
-- AND NVL(poh.conterms_exist_flag, 'N') <> 'Y' /* */ --Bug 7309989
AND ( (p_agent_id IS NULL)
OR (poh.agent_id = p_agent_id)
OR EXISTS (SELECT 'security_level is full'
FROM po_document_types podt
WHERE podt.document_type_code IN ('PO', 'PA')
AND podt.document_subtype = p_doc_subtype
AND podt.access_level_code = 'FULL')
)
)
OR ( poh.approved_flag = 'Y'
AND ( ( polc.lookup_code = 'CLOSE'
AND NVL(poh.closed_code, 'OPEN') <> 'CLOSED'
)
OR ( polc.lookup_code = 'FINALLY CLOSE'
AND ( (p_agent_id IS NULL)
OR (poh.agent_id = p_agent_id)
OR EXISTS (SELECT 'security_level = full'
FROM po_document_types podt
WHERE podt.document_type_code IN ('PO', 'PA')
AND podt.document_subtype = p_doc_subtype
AND podt.access_level_code = 'FULL')
)
)
OR ( polc.lookup_code = 'OPEN'
/* CONSIGNED FPI START */
AND NVL(poh.consigned_consumption_flag, 'N') <> 'Y'
AND ( ( poh.type_lookup_code IN ('BLANKET', 'CONTRACT') /* */
AND NVL(poh.closed_code, 'OPEN') <> 'OPEN'
)
OR ( poh.type_lookup_code NOT IN ('BLANKET', 'CONTRACT')
AND EXISTS (SELECT 'Ship exists not OPEN'
FROM po_line_locations poll
WHERE poll.po_header_id = p_doc_id
AND NVL(poll.consigned_flag, 'N') <> 'Y' /* CONSIGNED FPI */
AND NVL(poll.closed_code, 'OPEN') <> 'OPEN')
)
)
)
/* CONSIGNED FPI END */
OR ( polc.lookup_code = 'RECEIVE CLOSE'
AND NVL(poh.consigned_consumption_flag, 'N') <> 'Y' /* CONSIGNED FPI */
AND poh.type_lookup_code NOT IN ('BLANKET', 'CONTRACT')
AND EXISTS (SELECT 'Ships exist OPEN'
FROM po_line_locations poll
WHERE poll.po_header_id = p_doc_id
AND NVL(poll.closed_code, 'OPEN') NOT IN ('FINALLY CLOSED', 'CLOSED', 'CLOSED FOR RECEIVING')) --Bug l
)
OR ( polc.lookup_code = 'INVOICE CLOSE'
AND NVL(poh.consigned_consumption_flag, 'N') <> 'Y' /* CONSIGNED FPI */
AND poh.type_lookup_code NOT IN ('BLANKET', 'CONTRACT')
AND EXISTS(SELECT 'Ships exist OPEN'
FROM po_line_locations poll
WHERE poll.po_header_id = p_doc_id
AND NVL(poll.closed_code, 'OPEN') NOT IN ('FINALLY CLOSED', 'CLOSED', 'CLOSED FOR INVOICE')) --Bug 954
)
OR ( polc.lookup_code = 'RECEIVE OPEN'
AND NVL(poh.consigned_consumption_flag, 'N') <> 'Y' /* CONSIGNED FPI */
AND poh.type_lookup_code NOT IN ('BLANKET', 'CONTRACT')
AND EXISTS (SELECT 'Ships exist RCLOSED'
FROM po_line_locations poll
WHERE poll.po_header_id = p_doc_id
AND poll.closed_code IN ('CLOSED FOR RECEIVING', 'CLOSED'))
)
OR ( polc.lookup_code = 'INVOICE OPEN'
AND poh.type_lookup_code NOT IN ('BLANKET', 'CONTRACT')
AND EXISTS (SELECT 'Ships exits IC/CLOSED'
FROM po_line_locations poll
WHERE poll.po_header_id = p_doc_id
AND NVL(poll.consigned_flag, 'N') <> 'Y' /* CONSIGNED FPI */
AND poll.closed_code IN ('CLOSED FOR INVOICE', 'CLOSED'))
)
)
)
)
ORDER BY polc.displayed_field;
SELECT polc.displayed_field,
polc.lookup_code
FROM po_lookup_codes polc,
po_lines pol,
po_headers poh
WHERE pol.po_line_id = p_doc_line_id
AND pol.po_header_id = poh.po_header_id
AND polc.lookup_type = 'CONTROL ACTIONS'
AND NVL(pol.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND ( NVL(pol.cancel_flag, 'N') IN ('N','I')
OR polc.lookup_code = 'FINALLY CLOSE'
) /** FC of cancelled PO **/
/** Bug 3231524 Removed restrictions for drop ship PO. **/
AND ( ( polc.lookup_code = 'CANCEL PO LINE'
AND NVL(poh.consigned_consumption_flag, 'N') <> 'Y' /* CONSIGNED FPI */
AND NVL(poh.user_hold_flag, 'N') = 'N'
AND NVL(poh.closed_code, 'OPEN') NOT IN ('FINALLY CLOSED', 'CLOSED')
AND poh.approved_date IS NOT NULL -- bug 12347143
AND NVL(poh.authorization_status, 'INCOMPLETE') NOT IN
/* ('REQUIRES REAPPROVAL', */
('INCOMPLETE', 'IN PROCESS', 'PRE-APPROVED')
-- AND NVL(poh.conterms_exist_flag, 'N') <> 'Y' /* */ --Bug 7309989*/
AND ( (p_agent_id IS NULL)
OR (poh.agent_id = p_agent_id)
OR EXISTS (SELECT 'security_level is full'
FROM po_document_types podt
WHERE podt.document_type_code IN ('PO', 'PA')
AND podt.document_subtype = p_doc_subtype
AND podt.access_level_code = 'FULL')
)
)
OR ( poh.approved_flag = 'Y'
AND ( ( polc.lookup_code = 'CLOSE'
AND NVL(pol.closed_code, 'OPEN') <> 'CLOSED'
)
OR ( polc.lookup_code = 'FINALLY CLOSE'
AND ( (p_agent_id IS NULL)
OR (poh.agent_id = p_agent_id)
OR EXISTS (SELECT 'security_level is= full'
FROM po_document_types podt
WHERE podt.document_type_code IN ('PO', 'PA')
AND podt.document_subtype = p_doc_subtype
AND podt.access_level_code = 'FULL')
)
)
OR ( polc.lookup_code = 'OPEN'
AND NVL(poh.consigned_consumption_flag, 'N') <> 'Y' /* CONSIGNED FPI */
AND ( (poh.type_lookup_code = 'BLANKET')
OR ( poh.type_lookup_code NOT IN ('BLANKET', 'CONTRACT')
AND EXISTS (SELECT 'Ships exist not OPEN'
FROM po_line_locations poll
WHERE poll.po_line_id = p_doc_line_id
AND NVL(poll.consigned_flag, 'N') <> 'Y' /* CONSIGNED FPI */
AND NVL(poll.closed_code, 'OPEN') <> 'OPEN')
)
)
)
OR ( polc.lookup_code = 'RECEIVE CLOSE'
AND NVL(poh.consigned_consumption_flag, 'N') <> 'Y' /* CONSIGNED FPI */
AND poh.type_lookup_code NOT IN ('BLANKET', 'CONTRACT')
AND EXISTS (SELECT 'Ships exist that are OPEN'
FROM po_line_locations poll
WHERE poll.po_line_id = p_doc_line_id
AND NVL(poll.closed_code, 'OPEN') NOT IN ('FINALLY CLOSED', 'CLOSED', 'CLOSED FOR RECEIVING')) --Bug 5113609
)
OR ( polc.lookup_code = 'INVOICE CLOSE'
AND NVL(poh.consigned_consumption_flag, 'N') <> 'Y' /* CONSIGNED FPI */
AND poh.type_lookup_code NOT IN ('BLANKET', 'CONTRACT')
AND EXISTS (SELECT 'Ships exist OPEN'
FROM po_line_locations poll
WHERE poll.po_line_id = p_doc_line_id
AND NVL(poll.closed_code, 'OPEN') NOT IN ('FINALLY CLOSED', 'CLOSED', 'CLOSED FOR INVOICE')) --Bug 5113609
)
OR ( polc.lookup_code = 'RECEIVE OPEN'
AND NVL(poh.consigned_consumption_flag, 'N') <> 'Y' /* CONSIGNED FPI */
AND poh.type_lookup_code NOT IN ('BLANKET', 'CONTRACT')
AND EXISTS (SELECT 'Ships exist RCLOSED'
FROM po_line_locations poll
WHERE poll.po_line_id = p_doc_line_id
AND poll.closed_code IN ('CLOSED FOR RECEIVING', 'CLOSED'))
)
OR ( polc.lookup_code = 'INVOICE OPEN'
AND poh.type_lookup_code NOT IN ('BLANKET', 'CONTRACT')
AND EXISTS (SELECT 'Ships exits IC/CLOSED'
FROM po_line_locations poll
WHERE poll.po_line_id = p_doc_line_id
AND NVL(poll.consigned_flag, 'N') <> 'Y' /* CONSIGNED FPI */
AND poll.closed_code IN ('CLOSED FOR INVOICE', 'CLOSED'))
)
)
)
)
ORDER BY polc.displayed_field;--< Bug 3194665 End >
SELECT polc.displayed_field,
polc.lookup_code
FROM po_lookup_codes polc,
po_line_locations_all poll,
po_headers_all poh
WHERE poll.line_location_id = p_doc_line_loc_id
AND poll.po_header_id = poh.po_header_id
AND polc.lookup_type = 'CONTROL ACTIONS'
AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND ( NVL(poll.cancel_flag, 'N') IN ('N','I')
OR polc.lookup_code = 'FINALLY CLOSE'
) /** FC of cancelled PO **/
AND poll.shipment_type <> 'PRICE BREAK' /**/
/** Bug 3231524 Removed restrictions for drop ship PO. **/
AND ( ( polc.lookup_code = 'CANCEL PO SHIPMENT'
AND NVL(poh.consigned_consumption_flag, 'N') <> 'Y' /* CONSIGNED FPI */
AND NVL(poh.user_hold_flag, 'N') = 'N'
AND poh.approved_date IS NOT NULL -- bug 12347143
AND NVL(poh.authorization_status, 'INCOMPLETE') NOT IN
/* 'REQUIRES REAPPROVAL', */
('INCOMPLETE', 'IN PROCESS', 'PRE-APPROVED')
--
/*
* Bug 12334616 : Allowing the Cancel action on Po shipments even if there are contract
* terms associated with the PO [Partial Fix of :7309989]
* Condition(p_mode = 'UPDATE' OR NVL(poh.conterms_exist_flag,'N')<> 'Y') means the cancel action on Po shipments
* is allowed from the Details/Entry pages[p_mode=Update]
* and from summary page[p_mode=summary] only if there are no contract terms associated with the PO.
* So on allowing Cancel action on Po shipments even if there are contract terms associated with the PO
* will enable cancel action on Summary/Update page both,hence commenting the entire condition.
*
*/
/* AND ( p_mode = 'UPDATE'
OR NVL(poh.conterms_exist_flag,'N')<> 'Y')*/
--AND NVL(poh.conterms_exist_flag, 'N') <> 'Y' /* */
--
AND NVL(poll.closed_code, 'OPEN') NOT IN ('FINALLY CLOSED', 'CLOSED')
AND ( (p_agent_id IS NULL)
OR (poh.agent_id = p_agent_id)
OR EXISTS (SELECT 'security_level is full'
FROM po_document_types_all_b podt
WHERE podt.document_type_code = p_doc_type
AND podt.document_subtype = p_doc_subtype
AND podt.access_level_code = 'FULL'
AND podt.org_id = poh.org_id)
)
--: Can not cancel a Milestone Pay Item
--if it has been executed against (cancel line/header instead)
AND( NVL(poll.payment_type, 'NULL') <> 'MILESTONE'
OR
(coalesce(poll.quantity_billed, poll.amount_billed,
poll.quantity_financed, poll.amount_financed,
poll.quantity_shipped, poll.amount_shipped,
poll.quantity_received, poll.amount_received, 0) = 0
)
)
)
OR ( poh.approved_flag = 'Y'
AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND ( ( polc.lookup_code = 'CLOSE'
AND NVL(poll.closed_code, 'OPEN') <> 'CLOSED'
)
OR ( polc.lookup_code = 'FINALLY CLOSE'
AND ( (p_agent_id IS NULL)
OR (poh.agent_id = p_agent_id)
OR EXISTS (SELECT 'security_level is full'
FROM po_document_types_all_b podt
WHERE podt.document_type_code = p_doc_type
AND podt.document_subtype = p_doc_subtype
AND podt.access_level_code = 'FULL'
AND podt.org_id = poh.org_id)
)
-- : Can't FC with open recoup/retain balance.
AND (
NVL(poll.retainage_released_amount, 0) >=
NVL(poll.retainage_withheld_amount, 0)
)
AND (
(poll.shipment_type <> 'PREPAYMENT')
OR (coalesce(poll.quantity_recouped,
poll.amount_recouped, 0) >=
coalesce(poll.quantity_financed,
poll.amount_financed, 0))
)
--
)
OR ( polc.lookup_code = 'OPEN'
AND NVL(poh.consigned_consumption_flag, 'N') <> 'Y' /* CONSIGNED FPI */
AND NVL(poll.consigned_flag, 'N') <> 'Y' /* CONSIGNED FPI */
AND NVL(poll.closed_code, 'OPEN') <> 'OPEN'
)
OR ( polc.lookup_code = 'INVOICE CLOSE'
--
SELECT polc.displayed_field,
polc.lookup_code
FROM po_lookup_codes polc,
po_releases por
WHERE por.po_release_id = p_doc_id
AND polc.lookup_type = 'CONTROL ACTIONS'
AND NVL(por.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND ( NVL(por.cancel_flag, 'N') IN ('N','I')
OR polc.lookup_code = 'FINALLY CLOSE'
) /** FC of cancelled Rel **/
/** Bug 3231524 Removed restrictions for drop ship release. **/
AND ( ( ( ( polc.lookup_code = 'FREEZE'
AND NVL(por.consigned_consumption_flag, 'N') <> 'Y' /* CONSIGNED FPI */
AND NVL(por.frozen_flag, 'N') = 'N'
)
OR ( polc.lookup_code = 'UNFREEZE'
AND NVL(por.consigned_consumption_flag, 'N') <> 'Y' /* CONSIGNED FPI */
AND por.frozen_flag = 'Y'
)
)
AND NVL(por.hold_flag, 'N') = 'N'
AND NVL(por.authorization_status, 'INCOMPLETE') = 'APPROVED'
)
OR ( polc.lookup_code = 'HOLD'
AND NVL(por.consigned_consumption_flag, 'N') <> 'Y' /* CONSIGNED FPI */
AND NVL(por.hold_flag, 'N') = 'N'
)
OR ( polc.lookup_code = 'RELEASE HOLD'
AND NVL(por.consigned_consumption_flag, 'N') <> 'Y' /* CONSIGNED FPI */
AND por.hold_flag = 'Y'
)
OR ( polc.lookup_code = 'CANCEL REL'
AND NVL(por.consigned_consumption_flag, 'N') <> 'Y' /* CONSIGNED FPI */
AND NVL(por.hold_flag, 'N') = 'N'
AND NVL(por.closed_code, 'OPEN') NOT IN ('FINALLY CLOSED', 'CLOSED')
AND por.approved_date IS NOT NULL -- bug 12347143
AND NVL(por.authorization_status, 'INCOMPLETE') NOT IN
/* 'REQUIRES REAPPROVAL', */
('IN PROCESS', 'PRE-APPROVED', 'INCOMPLETE')
AND ( (p_agent_id IS NULL)
OR (por.agent_id = p_agent_id)
OR EXISTS (SELECT 'security_level is full'
FROM po_document_types podt
WHERE podt.document_type_code = 'RELEASE'
AND podt.document_subtype = p_doc_subtype
AND podt.access_level_code = 'FULL')
)
)
OR ( por.approved_flag = 'Y'
AND ( ( polc.lookup_code = 'CLOSE'
AND NVL(por.closed_code, 'OPEN') <> 'CLOSED'
)
OR ( polc.lookup_code = 'FINALLY CLOSE'
AND ( (p_agent_id IS NULL)
OR (por.agent_id = p_agent_id)
OR EXISTS (SELECT 'security_level is full'
FROM po_document_types podt
WHERE podt.document_type_code = 'RELEASE'
AND podt.document_subtype = p_doc_subtype
AND podt.access_level_code = 'FULL')
)
)
OR ( polc.lookup_code = 'OPEN'
AND NVL(por.consigned_consumption_flag, 'N') <> 'Y' /* CONSIGNED FPI */
AND NVL(por.closed_code, 'OPEN') <> 'OPEN'
)
OR ( polc.lookup_code = 'RECEIVE CLOSE'
AND NVL(por.consigned_consumption_flag, 'N') <> 'Y' /* CONSIGNED FPI */
AND EXISTS (SELECT 'Ships exist that are OPEN'
FROM po_line_locations poll
WHERE poll.po_release_id = p_doc_id
AND NVL(poll.closed_code, 'OPEN') NOT IN ('FINALLY CLOSED', 'CLOSED', 'CLOSED FOR RECEIVING')) --Bug 5113609
)
OR ( polc.lookup_code = 'INVOICE CLOSE'
AND NVL(por.consigned_consumption_flag, 'N') <> 'Y' /* CONSIGNED FPI */
AND EXISTS (SELECT 'Ships exist that are OPEN'
FROM po_line_locations poll
WHERE poll.po_release_id = p_doc_id
AND NVL(poll.closed_code, 'OPEN') NOT IN ('FINALLY CLOSED', 'CLOSED', 'CLOSED FOR INVOICE')) --Bug 5113609
)
OR ( polc.lookup_code = 'RECEIVE OPEN'
AND NVL(por.consigned_consumption_flag, 'N') <> 'Y' /* CONSIGNED FPI */
AND EXISTS (SELECT 'Ships exist that are RCLOSED'
FROM po_line_locations poll
WHERE poll.po_release_id = p_doc_id
AND poll.closed_code IN ('CLOSED FOR RECEIVING', 'CLOSED'))
)
OR ( polc.lookup_code = 'INVOICE OPEN'
AND EXISTS (SELECT 'Ships exits that are IC/CLOSED'
FROM po_line_locations poll
WHERE poll.po_release_id = p_doc_id
AND poll.closed_code IN ('CLOSED FOR INVOICE', 'CLOSED'))
)
)
)
)
ORDER BY polc.displayed_field;
SELECT polc.displayed_field,
polc.lookup_code
FROM po_lookup_codes polc,
po_line_locations poll,
po_releases por
WHERE poll.line_location_id = p_doc_line_loc_id
AND poll.po_release_id = por.po_release_id
AND polc.lookup_type = 'CONTROL ACTIONS'
AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND ( NVL(poll.cancel_flag, 'N') IN ('N','I')
OR polc.lookup_code = 'FINALLY CLOSE'
) /** FC of cancelled Rel **/
/** Bug 3231524 Removed restrictions for drop ship release. **/
AND ( ( polc.lookup_code = 'CANCEL REL SHIPMENT'
AND NVL(por.consigned_consumption_flag, 'N') <> 'Y' /* CONSIGNED FPI */
AND NVL(por.hold_flag, 'N') = 'N'
AND por.approved_date IS NOT NULL -- bug 12347143
/* Bug no 5388496 cancelling is allowed for releases with status 'REQUIRES REAPPROVAL'*/
AND NVL(por.authorization_status, 'INCOMPLETE') NOT IN ('INCOMPLETE', 'IN PROCESS', 'PRE-APPROVED')
AND NVL(poll.closed_code, 'OPEN') NOT IN ('FINALLY CLOSED', 'CLOSED')
AND ( (p_agent_id IS NULL)
OR (por.agent_id = p_agent_id)
OR EXISTS (SELECT 'security_level is full'
FROM po_document_types podt
WHERE podt.document_type_code = 'RELEASE'
AND podt.document_subtype = p_doc_subtype
AND podt.access_level_code = 'FULL')
)
)
OR ( por.approved_flag = 'Y'
AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND ( ( polc.lookup_code = 'CLOSE'
AND NVL(poll.closed_code, 'OPEN') <> 'CLOSED'
)
OR ( polc.lookup_code = 'FINALLY CLOSE'
AND ( (p_agent_id IS NULL)
OR (por.agent_id = p_agent_id)
OR EXISTS(SELECT 'security_level is full'
FROM po_document_types podt
WHERE podt.document_type_code = 'RELEASE'
AND podt.document_subtype = p_doc_subtype
AND podt.access_level_code = 'FULL')
)
)
OR ( polc.lookup_code = 'OPEN'
AND NVL(por.consigned_consumption_flag, 'N') <> 'Y' /* CONSIGNED FPI */
AND NVL(poll.closed_code, 'OPEN') <> 'OPEN'
)
OR ( polc.lookup_code = 'INVOICE CLOSE'
AND NVL(por.consigned_consumption_flag, 'N') <> 'Y' /* CONSIGNED FPI */
AND NVL(poll.closed_code, 'OPEN') NOT IN ('CLOSED', 'CLOSED FOR INVOICE')
)
OR ( polc.lookup_code = 'RECEIVE CLOSE'
AND NVL(por.consigned_consumption_flag, 'N') <> 'Y' /* CONSIGNED FPI */
AND NVL(poll.closed_code, 'OPEN') NOT IN ('CLOSED', 'CLOSED FOR RECEIVING')
)
OR ( polc.lookup_code = 'INVOICE OPEN'
AND NVL(poll.closed_code, 'OPEN') NOT IN ('OPEN', 'CLOSED FOR RECEIVING') /* : bug2749001 */
)
OR ( polc.lookup_code = 'RECEIVE OPEN'
AND NVL(por.consigned_consumption_flag, 'N') <> 'Y' /* CONSIGNED FPI */
AND NVL(poll.closed_code, 'OPEN') NOT IN ('OPEN', 'CLOSED FOR INVOICE') /* : bug2749001 */
)
)
)
)
ORDER BY polc.displayed_field;
SELECT poh.wf_item_type, poh.wf_item_key
FROM po_headers poh
WHERE poh.po_header_id = p_doc_id;
SELECT por.po_release_id, poh.type_lookup_code,
por.wf_item_type, por.wf_item_key
FROM po_releases por,
po_headers poh
WHERE por.po_header_id = p_doc_id AND
por.po_header_id = poh.po_header_id AND
NVL(por.authorization_status,'INCOMPLETE') IN
('INCOMPLETE','REJECTED','REQUIRES REAPPROVAL') AND
NVL(por.cancel_flag,'N') = 'N' AND
NVL(por.closed_code,'OPEN') <> 'FINALLY CLOSED';
SELECT por.wf_item_type, por.wf_item_key
FROM po_releases por
WHERE por.po_release_id = p_doc_id;
* Public Procedure: update_note_to_vendor
* Requires: API message list has been initialized if p_init_msg_list is false.
* Modifies: note_to_vendor in PO_HEADERS, PO_LINES, or PO_RELEASES. API message
* list.
* Effects: Updates the note_to_vendor column of PO_HEADERS, PO_LINES, or
* PO_RELEASES depending upon p_doc_type. If p_doc_line_id is not NULL and the
* document is not a RELEASE, then updates PO_LINES. All changes will be
* committed upon success if p_commit is FND_API.G_TRUE. Appends to API
* message list on error.
* Returns:
* x_return_status - FND_API.G_RET_STS_SUCCESS if the update was successful
* FND_API.G_RET_STS_ERROR if no update was made
* FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
*/
PROCEDURE update_note_to_vendor
(p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
p_doc_type IN PO_DOCUMENT_TYPES.document_type_code%TYPE,
p_doc_id IN NUMBER,
p_doc_line_id IN NUMBER,
p_note_to_vendor IN PO_HEADERS.note_to_vendor%TYPE)
IS
l_api_name CONSTANT VARCHAR2(30) := 'update_note_to_vendor';
SAVEPOINT update_note_to_vendor_PVT;
UPDATE po_lines pol
SET pol.note_to_vendor = p_note_to_vendor
WHERE pol.po_line_id = p_doc_line_id AND
pol.po_header_id = p_doc_id;
/* Bug 2781710: We should update the note_to_vendor column
in the archive table also. */
UPDATE po_lines_archive pla
SET pla.note_to_vendor = p_note_to_vendor
WHERE pla.po_line_id = p_doc_line_id AND
pla.po_header_id = p_doc_id AND
pla.revision_num = (SELECT poh.revision_num
FROM po_headers poh
WHERE poh.po_header_id = p_doc_id);
UPDATE po_headers poh
SET poh.note_to_vendor = p_note_to_vendor
WHERE poh.po_header_id = p_doc_id;
/* Bug 2781710: We should update the note_to_vendor column
in the archive table also. */
UPDATE po_headers_archive pha
SET pha.note_to_vendor = p_note_to_vendor
WHERE pha.po_header_id = p_doc_id AND
pha.revision_num = (SELECT poh.revision_num
FROM po_headers poh
WHERE poh.po_header_id = p_doc_id);
UPDATE po_releases por
SET por.note_to_vendor = p_note_to_vendor
WHERE por.po_release_id = p_doc_id;
/* Bug 2781710: We should update the note_to_vendor column
in the archive table also. */
UPDATE po_releases_archive pra
SET pra.note_to_vendor = p_note_to_vendor
WHERE pra.po_release_id = p_doc_id AND
pra.revision_num = (SELECT por.revision_num
FROM po_releases por
WHERE por.po_release_id = p_doc_id);
ROLLBACK TO update_note_to_vendor_PVT;
ROLLBACK TO update_note_to_vendor_PVT;
ROLLBACK TO update_note_to_vendor_PVT;
END update_note_to_vendor;
SELECT porh.preparer_id
INTO l_doc_agent_id
FROM po_requisition_headers porh
WHERE porh.requisition_header_id = p_doc_id;
SELECT por.agent_id
INTO l_doc_agent_id
FROM po_releases por
WHERE por.po_release_id = p_doc_id;
SELECT poh.agent_id
INTO l_doc_agent_id
FROM po_headers poh
WHERE poh.po_header_id = p_doc_id;
SELECT 'Has shipments'
FROM po_line_locations poll
WHERE poll.po_header_id = p_doc_id AND
poll.shipment_type IN
('STANDARD', 'PLANNED', 'BLANKET', 'PREPAYMENT') AND
NVL(poll.cancel_flag, 'N') = 'N' AND
NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED';
SELECT 'Has shipments'
FROM po_line_locations poll
WHERE poll.po_release_id = p_doc_id AND
poll.shipment_type IN ('STANDARD', 'PLANNED', 'BLANKET') AND
NVL(poll.cancel_flag, 'N') = 'N' AND
NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED';
SELECT 'In open GL period'
FROM gl_period_statuses gl_ps,
gl_period_statuses po_ps,
financials_system_parameters fsp
WHERE gl_ps.application_id = 101 AND
gl_ps.set_of_books_id = fsp.set_of_books_id AND
-- bug 5498063
(( (l_validate_gl_period = 'Y' OR l_validate_gl_period = 'R')--Bug15874392
and GL_PS.closing_status IN ('O', 'F'))
OR
(l_validate_gl_period = 'N')) AND
-- gl_ps.closing_status IN ('O','F') AND
-- bug 5498063
gl_ps.period_name = po_ps.period_name AND
gl_ps.adjustment_period_flag = 'N' AND
(TRUNC(p_date) BETWEEN
TRUNC(gl_ps.start_date) AND TRUNC(gl_ps.end_date)) AND
po_ps.application_id = 201 AND
po_ps.closing_status = 'O' AND
po_ps.adjustment_period_flag = 'N' AND
po_ps.set_of_books_id = fsp.set_of_books_id;
SELECT poort.text_line
FROM po_online_report_text poort
WHERE poort.online_report_id = p_online_report_id;
SELECT displayed_field
INTO l_control_action_disp_name
FROM PO_LOOKUP_CODES
WHERE lookup_type = 'CONTROL ACTIONS'
AND lookup_code = p_control_action;
IGC_CBC_PO_GRP.update_cbc_acct_date(
p_api_version => 1.0
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_validation_level => 100
,x_return_status => x_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_document_id => p_doc_header_id
,p_document_type => p_doc_type
,p_document_sub_type => p_doc_subtype
,p_cbc_acct_date => p_action_date);
select conterms_exist_flag, start_date,
end_date, decode(cancel_flag,'I',null,cancel_flag)
into l_conterms_exist_flag, l_document_start_date,
l_document_end_date, l_cancel_flag
from po_headers_all
where po_header_id = p_doc_header_id;
select shipment_type
into l_doc_subtype
from po_line_locations_all
where line_location_id = p_doc_line_loc_id;
SELECT 'Backing Temp Labor/Expense Req'
FROM po_distributions_all pod
, po_requisition_lines_all prl
, po_req_distributions_all prd
WHERE pod.line_location_id = p_line_location_id -- For each PO Distribution
AND pod.req_distribution_id = prd.distribution_id -- join to backing Req Distribution
AND prd.requisition_line_id = prl.requisition_line_id -- and then up to the Req Line.
AND ( ( prl.labor_req_line_id IS NOT NULL ) -- That Req Line must be an Expense line
OR ( EXISTS ( SELECT 'Parent Temp Labor Req Line' -- or a parent Temp Labor line
FROM po_requisition_lines_all prl2 -- of some Expense line.
WHERE prl2.labor_req_line_id = prl.requisition_line_id
)
)
);
SELECT 'Backing Temp Labor/Expense Req'
FROM po_distributions_all pod
, po_requisition_lines_all prl
, po_req_distributions_all prd
WHERE pod.po_line_id = p_po_line_id -- For each PO Distribution
AND pod.req_distribution_id = prd.distribution_id -- join to backing Req Distribution
AND prd.requisition_line_id = prl.requisition_line_id -- and then up to the Req Line.
AND ( ( prl.labor_req_line_id IS NOT NULL ) -- That Req Line must be an Expense line
OR ( EXISTS ( SELECT 'Parent Temp Labor Req Line' -- or a parent Temp Labor line
FROM po_requisition_lines_all prl2 -- of some Expense line.
WHERE prl2.labor_req_line_id = prl.requisition_line_id
)
)
);
SELECT type_lookup_code
INTO l_doc_subtype
FROM po_headers_all
WHERE po_header_id = p_doc_header_id;
SELECT item_id
INTO l_item_id
FROM po_lines_merge_v -- Replaced po_lines_all
WHERE po_line_id = p_doc_level_id
AND draft_id = p_draft_id;
SELECT 'N'
INTO l_ship_invalid_for_ctrl_actn
FROM DUAL
WHERE EXISTS(
SELECT 1
FROM po_line_locations_all poll1,
po_line_locations_all poll2
WHERE poll1.line_location_id = p_doc_level_id
AND poll1.po_line_id = poll2.po_line_id
AND NVL(poll2.cancel_flag,'N') <> 'Y'
AND NVL(poll2.payment_type, 'NULL') NOT IN ('ADVANCE', 'DELIVERY') --
AND NVL(poll2.closed_code, PO_DOCUMENT_ACTION_PVT.g_doc_action_OPEN)
<> PO_DOCUMENT_ACTION_PVT.g_doc_closed_sts_FIN_CLOSED
AND poll2.line_location_id <> p_doc_level_id);
IF(l_mode = 'UPDATE'
AND NOT (l_current_action LIKE 'CANCEL%'
OR l_current_action LIKE '%HOLD%')) THEN
NULL;
ELSIF(l_mode = 'UPDATE' AND l_current_action = 'CANCEL PO') THEN
IF(p_doc_type = PO_CORE_S.g_doc_type_PO) THEN
-- For Update mode we Show Cancel Action at Header as Cancel Order
d_pos := 100;
PO_LOG.stmt(d_module,d_pos,l_current_action || 'is replaced by CANCEL ORDER for update mode');
PO_LOG.stmt(d_module,d_pos,l_current_action || 'is replaced by CANCEL AGREEMENT for update mode');
END IF; --l_mode = 'UPDATE'
SELECT Nvl(conterms_exist_flag,'N'),
authorization_status,
revision_num
INTO l_conterms_exist_flag,
l_auth_status,
l_revision_num
FROM po_headers_all
WHERE po_header_id = p_doc_id;
select 'N',
authorization_status,
revision_num
INTO l_conterms_exist_flag,
l_auth_status,
l_revision_num
FROM po_releases_all
WHERE po_release_id = p_doc_id;
SELECT Count(1)
INTO l_archive_count
FROM po_headers_archive_all
WHERE po_header_id = p_doc_id;
SELECT Count(1)
INTO l_archive_count
FROM po_releases_archive_all
WHERE po_release_id = p_doc_id;