[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE manual_update_closed_status(
p_document_id IN NUMBER
, p_document_type IN VARCHAR2
, p_document_subtype IN VARCHAR2
, p_action IN VARCHAR2
, p_calling_mode IN VARCHAR2
, p_line_id IN NUMBER
, p_shipment_id IN NUMBER
, p_user_id IN NUMBER
, p_login_id IN NUMBER
, p_employee_id IN NUMBER
, p_reason IN VARCHAR2
, p_enc_flag IN BOOLEAN
, x_return_status OUT NOCOPY VARCHAR2
);
PROCEDURE auto_update_closed_status(
p_document_id IN NUMBER
, p_document_type IN VARCHAR2
, p_calling_mode IN VARCHAR2
, p_line_id IN NUMBER
, p_shipment_id IN NUMBER
, p_employee_id IN NUMBER
, p_user_id IN NUMBER --bug4964600
, p_login_id IN NUMBER --bug4964600
, p_reason IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
);
UPDATE po_line_locations poll
SET closed_code = 'CLOSED'
WHERE poll.line_location_id = p_action_ctl_rec.shipment_id
AND poll.closed_code = 'FINALLY CLOSED';
manual_update_closed_status(
p_document_id => p_action_ctl_rec.document_id
, p_document_type => p_action_ctl_rec.document_type
, p_document_subtype => p_action_ctl_rec.document_subtype
, p_action => p_action_ctl_rec.action
, p_calling_mode => p_action_ctl_rec.calling_mode
, p_line_id => p_action_ctl_rec.line_id
, p_shipment_id => p_action_ctl_rec.shipment_id
, p_user_id => l_user_id
, p_login_id => l_login_id
, p_employee_id => l_emp_id
, p_reason => p_action_ctl_rec.note
, p_enc_flag => l_enc_flag
, x_return_status => l_ret_sts
);
l_reason := FND_MESSAGE.GET_STRING('PO', 'PO_UPDATE_CLOSE_ROLLUP');
auto_update_closed_status(
p_document_id => p_action_ctl_rec.document_id
, p_document_type => p_action_ctl_rec.document_type
, p_calling_mode => p_action_ctl_rec.calling_mode
, p_line_id => p_action_ctl_rec.line_id
, p_shipment_id => p_action_ctl_rec.shipment_id
, p_user_id => l_user_id --bug4964600
, p_login_id => l_login_id --bug4964600
, p_employee_id => l_emp_id
, p_reason => l_reason
, x_return_status => l_ret_sts
);
SELECT count(*)
INTO l_line_finally_closed
FROM po_line_locations_all poll
, po_lines_all pol
, po_releases_all por
, po_line_locations_all ppo_ll
WHERE poll.line_location_id = p_shipment_id
AND pol.po_line_id = poll.po_line_id
AND por.po_release_id(+) = poll.po_release_id
AND ppo_ll.line_location_id(+) = poll.source_shipment_id
AND ( pol.closed_code = PO_DOCUMENT_ACTION_PVT.g_doc_closed_sts_FIN_CLOSED
OR por.closed_code = PO_DOCUMENT_ACTION_PVT.g_doc_closed_sts_FIN_CLOSED
OR ppo_ll.closed_code = PO_DOCUMENT_ACTION_PVT.g_doc_closed_sts_FIN_CLOSED
)
;
SELECT NVL(poh.encumbrance_required_flag, 'N')
INTO l_bpa_enc_required
FROM po_headers_all poh
WHERE poh.po_header_id = p_document_id;
PROCEDURE manual_update_closed_status(
p_document_id IN NUMBER
, p_document_type IN VARCHAR2
, p_document_subtype IN VARCHAR2
, p_action IN VARCHAR2
, p_calling_mode IN VARCHAR2
, p_line_id IN NUMBER
, p_shipment_id IN NUMBER
, p_user_id IN NUMBER
, p_login_id IN NUMBER
, p_employee_id IN NUMBER
, p_reason IN VARCHAR2
, p_enc_flag IN BOOLEAN
, x_return_status OUT NOCOPY VARCHAR2
)
IS
d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_ACTION_CLOSE.manual_update_close_status';
SELECT pol.po_line_id
BULK COLLECT INTO l_id_tbl
FROM po_lines pol
WHERE pol.po_line_id = p_line_id;
SELECT pol.po_line_id
BULK COLLECT INTO l_id_tbl
FROM po_lines pol
WHERE pol.po_header_id = p_document_id;
UPDATE po_lines pol
SET pol.last_update_date = SYSDATE
, pol.last_updated_by = p_user_id
, pol.last_update_login = p_login_id
, pol.closed_date = DECODE(p_action,
'CLOSE', SYSDATE,
'FINALLY CLOSE', SYSDATE, -- Bug 4369988
NULL)
, pol.closed_by = p_employee_id
, pol.closed_reason = p_reason
, pol.closed_code = DECODE(p_action,
'CLOSE', 'CLOSED',
'FINALLY CLOSE', 'FINALLY CLOSED',
'OPEN', 'OPEN')
WHERE pol.po_line_id = l_id_tbl(i)
AND NVL(pol.closed_code, 'OPEN') <> 'FINALLY CLOSED';
UPDATE po_headers poh
SET poh.last_update_date = SYSDATE
, poh.last_updated_by = p_user_id
, poh.last_update_login = p_login_id
, poh.closed_date = DECODE(p_action,
'CLOSE', SYSDATE,
'FINALLY CLOSE', SYSDATE, -- Bug 4369988
NULL)
, poh.closed_code = DECODE(p_action,
'CLOSE', 'CLOSED',
'FINALLY CLOSE', 'FINALLY CLOSED',
'OPEN', 'OPEN')
WHERE poh.po_header_id = p_document_id
AND NVL(poh.closed_code, 'OPEN') <> 'FINALLY CLOSED';
SELECT poll.line_location_id
BULK COLLECT INTO l_id_tbl
FROM po_line_locations poll
WHERE poll.line_location_id = p_shipment_id;
SELECT poll.line_location_id
BULK COLLECT INTO l_id_tbl
FROM po_line_locations poll
WHERE poll.po_line_id = p_line_id;
SELECT poll.line_location_id
BULK COLLECT INTO l_id_tbl
FROM po_line_locations poll
WHERE poll.po_release_id = p_document_id;
SELECT poll.line_location_id
BULK COLLECT INTO l_id_tbl
FROM po_line_locations poll
WHERE poll.po_header_id = p_document_id;
UPDATE po_line_locations poll
SET poll.last_update_date = SYSDATE
, poll.last_updated_by = p_user_id
, poll.last_update_login = p_login_id
, poll.closed_date = DECODE(p_action,
'CLOSE', DECODE(NVL(poll.closed_code, 'OPEN'),
'CLOSED', poll.closed_date,
SYSDATE), --
'FINALLY CLOSE', SYSDATE,
'INVOICE CLOSE', DECODE(NVL(poll.closed_code, 'OPEN'),
'CLOSED FOR RECEIVING', SYSDATE,
NULL),
'RECEIVE CLOSE', DECODE(NVL(poll.closed_code, 'OPEN'),
'CLOSED FOR INVOICE', SYSDATE,
NULL)
)
, poll.closed_by = DECODE(p_calling_mode,
'AP', DECODE(p_action, 'INVOICE OPEN', NULL, p_employee_id),
p_employee_id
)
, poll.closed_reason = DECODE(p_calling_mode,
'AP', DECODE(p_action, 'INVOICE OPEN', NULL, p_reason),
p_reason
)
, poll.closed_code = DECODE(p_action,
'CLOSE', 'CLOSED',
'FINALLY CLOSE', 'FINALLY CLOSED',
'INVOICE CLOSE', DECODE(NVL(poll.closed_code, 'OPEN'),
'CLOSED FOR RECEIVING', 'CLOSED',
'OPEN', 'CLOSED FOR INVOICE',
poll.closed_code), --
'RECEIVE CLOSE', DECODE(NVL(poll.closed_code, 'OPEN'),
'CLOSED FOR INVOICE', 'CLOSED',
'OPEN', 'CLOSED FOR RECEIVING',
poll.closed_code), --
'OPEN', DECODE(poll.consigned_flag,
'Y', 'CLOSED FOR INVOICE',
'OPEN'),
'INVOICE OPEN', DECODE(poll.consigned_flag,
'Y', poll.closed_code,
DECODE(NVL(poll.closed_code, 'OPEN'),
'CLOSED FOR INVOICE', 'OPEN',
'CLOSED', 'CLOSED FOR RECEIVING',
poll.closed_code)), --
'RECEIVE OPEN', DECODE(NVL(poll.closed_code, 'OPEN'),
'CLOSED FOR RECEIVING', 'OPEN',
'CLOSED', 'CLOSED FOR INVOICE',
poll.closed_code) --
)
, poll.shipment_closed_date = DECODE(p_action,
'CLOSE', SYSDATE,
'INVOICE CLOSE', DECODE(NVL(poll.closed_code, 'OPEN'),
'CLOSED FOR RECEIVING', SYSDATE,
poll.shipment_closed_date), --
'RECEIVE CLOSE', DECODE(NVL(poll.closed_code, 'OPEN'),
'CLOSED FOR INVOICE', SYSDATE,
poll.shipment_closed_date), --
'OPEN', NULL,
'INVOICE OPEN', NULL,
'RECEIVE OPEN', NULL,
'FINALLY CLOSE', NVL(poll.shipment_closed_date, SYSDATE)
)
, poll.closed_for_invoice_date = DECODE(p_action,
'CLOSE', DECODE(NVL(poll.closed_code, 'OPEN'),
'CLOSED FOR RECEIVING', SYSDATE,
'OPEN', SYSDATE,
poll.closed_for_invoice_date),
'INVOICE CLOSE', SYSDATE,
'OPEN', NULL,
'INVOICE OPEN', NULL,
'FINALLY CLOSE', NVL(poll.closed_for_invoice_date, SYSDATE),
poll.closed_for_invoice_date
)
, poll.closed_for_receiving_date = DECODE(p_action,
'CLOSE', DECODE(NVL(poll.closed_code, 'OPEN'),
'CLOSED FOR INVOICE', SYSDATE,
'OPEN', SYSDATE,
poll.closed_for_receiving_date),
'RECEIVE CLOSE', SYSDATE,
'OPEN', NULL,
'RECEIVE OPEN', NULL,
'FINALLY CLOSE', NVL(poll.closed_for_receiving_date, SYSDATE),
poll.closed_for_receiving_date
)
WHERE poll.line_location_id = l_id_tbl(i)
AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND poll.shipment_type =
DECODE(p_document_type,
-- : STANDARD doc subtype no longer implies
-- 'STANDARD' shipptype; it can also be PREPAYMENT.
PO_LOG.stmt(d_module, d_progress, 'Updated' || SQL%ROWCOUNT || ' closed code rows.' );
SELECT pod.po_distribution_id
BULK COLLECT INTO l_id_tbl
FROM po_distributions pod
WHERE pod.line_location_id = p_shipment_id;
SELECT pod.po_distribution_id
BULK COLLECT INTO l_id_tbl
FROM po_distributions pod
WHERE pod.po_line_id = p_line_id
AND pod.po_release_id IS NULL;
SELECT pod.po_distribution_id
BULK COLLECT INTO l_id_tbl
FROM po_distributions pod
WHERE pod.po_release_id = p_document_id;
SELECT pod.po_distribution_id
BULK COLLECT INTO l_id_tbl
FROM po_distributions pod
WHERE pod.po_header_id = p_document_id
AND pod.po_release_id IS NULL;
UPDATE po_distributions pod
SET pod.gl_closed_date = DECODE(p_action, 'FINALLY CLOSE', SYSDATE, NULL)
WHERE pod.po_distribution_id = l_id_tbl(i)
;
PO_LOG.stmt(d_module, d_progress, 'Updated' || SQL%ROWCOUNT || ' distribution gl_closed_dates' );
END manual_update_closed_status;
/*bug 16432524 The status of the Order document can be updated to "Closed for Invoicing"/"Closed" only
if the Quantity billed is equal to the ordered Quantity.*/
PROCEDURE auto_update_closed_status(
p_document_id IN NUMBER
, p_document_type IN VARCHAR2
, p_calling_mode IN VARCHAR2
, p_line_id IN NUMBER
, p_shipment_id IN NUMBER
, p_employee_id IN NUMBER
, p_user_id IN NUMBER --bug4964600
, p_login_id IN NUMBER --bug4964600
, p_reason IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
)
IS
d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_ACTION_CLOSE.auto_update_close_status';
UPDATE po_lines pol
SET pol.last_update_date = SYSDATE
, pol.last_updated_by = p_user_id
, pol.last_update_login = p_login_id
, pol.closed_date = SYSDATE
, pol.closed_by = p_employee_id
, pol.closed_reason = p_reason
, pol.closed_code = 'CLOSED'
WHERE pol.po_line_id = p_line_id
AND pol.cancel_flag = 'Y'
AND NVL(pol.closed_code, 'OPEN') NOT IN ('FINALLY CLOSED','CLOSED');
UPDATE po_lines pol
SET pol.last_update_date = SYSDATE
, pol.last_updated_by = p_user_id
, pol.last_update_login = p_login_id
, pol.closed_date = SYSDATE
, pol.closed_by = p_employee_id
, pol.closed_reason = p_reason
, pol.closed_code = 'CLOSED'
WHERE pol.po_header_id = p_document_id
AND pol.cancel_flag = 'Y'
AND NVL(pol.closed_code, 'OPEN') NOT IN ('FINALLY CLOSED','CLOSED');
SELECT poll.line_location_id,poll.closed_code
BULK COLLECT INTO l_id_tbl,l_closed_code_tbl
FROM po_line_locations poll
WHERE poll.line_location_id = p_shipment_id;
SELECT poll.line_location_id,poll.closed_code
BULK COLLECT INTO l_id_tbl,l_closed_code_tbl
FROM po_line_locations poll
WHERE poll.po_line_id = p_line_id
AND poll.po_release_id IS NULL;
SELECT poll.line_location_id,poll.closed_code
BULK COLLECT INTO l_id_tbl,l_closed_code_tbl
FROM po_line_locations poll
WHERE poll.po_release_id = p_document_id;
SELECT poll.line_location_id,poll.closed_code
BULK COLLECT INTO l_id_tbl,l_closed_code_tbl
FROM po_line_locations poll
WHERE poll.po_header_id = p_document_id
AND poll.po_release_id IS NULL;
SELECT NVL(authorization_status, 'INCOMPLETE')
INTO l_authorization_status
FROM po_releases_all
WHERE po_release_id = p_document_id;
SELECT Nvl(authorization_status, 'INCOMPLETE')
INTO l_authorization_status
FROM po_headers_all
WHERE po_header_id = p_document_id;
UPDATE po_line_locations poll
SET poll.closed_code =
(
SELECT DECODE(poll.matching_basis,
'AMOUNT',
DECODE(
DECODE(sign(
((poll.amount - NVL(poll.amount_cancelled, 0))
* (1 - NVL(poll.invoice_close_tolerance,
NVL(posp.invoice_close_tolerance, 0))/100))
- NVL(poll.amount_billed, 0)),--bug 16432524
1, 'OPEN',
'CLOSED FOR INVOICE'),
'CLOSED FOR INVOICE',
DECODE(
DECODE(sign(
((poll.amount - NVL(poll.amount_cancelled, 0))
* (1 - NVL(poll.receive_close_tolerance,
NVL(posp.receive_close_tolerance, 0))/100))
- DECODE(posp.receive_close_code,
'ACCEPTED', NVL(poll.amount_accepted, 0),
'DELIVERED', sum(NVL(pod.amount_delivered, 0)),
NVL(poll.amount_received, 0))),
1, 'OPEN',
'CLOSED FOR RECEIVING'),
'CLOSED FOR RECEIVING', 'CLOSED',
decode(nvl(l_AUTO_CLOSE_TWO_WAY,'N'),'Y', -- Bug 11730977 start
decode(nvl(poll.receipt_required_flag,'N'),'N',decode(nvl(poll.inspection_required_flag,'N'),'N','CLOSED','CLOSED FOR
INVOICE')),'N','CLOSED FOR INVOICE')),-- Bug 11730977 end
'OPEN',
DECODE(
DECODE(sign(
((poll.amount - NVL(poll.amount_cancelled, 0))
* (1 - NVL(poll.receive_close_tolerance,
NVL(posp.receive_close_tolerance, 0))/100))
- DECODE(posp.receive_close_code,
'ACCEPTED', NVL(poll.amount_accepted, 0),
'DELIVERED', sum(NVL(pod.amount_delivered, 0)),
NVL(poll.amount_received, 0))),
1, 'OPEN',
'CLOSED FOR RECEIVING'),
'CLOSED FOR RECEIVING', 'CLOSED FOR RECEIVING',
'OPEN')),
-- else QUANTITY BASIS
DECODE(
DECODE(sign(
((poll.quantity - NVL(poll.quantity_cancelled, 0))
* (1 - NVL(poll.invoice_close_tolerance,
NVL(posp.invoice_close_tolerance, 0))/100))
- NVL(poll.quantity_billed, 0)),--bug 16432524
1, 'OPEN',
'CLOSED FOR INVOICE'),
'CLOSED FOR INVOICE',
DECODE(
DECODE(sign(
((poll.quantity - NVL(poll.quantity_cancelled, 0))
* (1 - NVL(poll.receive_close_tolerance,
NVL(posp.receive_close_tolerance, 0))/100))
- DECODE(posp.receive_close_code,
'ACCEPTED', NVL(poll.quantity_accepted, 0),
'DELIVERED', sum(NVL(pod.quantity_delivered, 0)),
NVL(poll.quantity_received, 0))),
1, 'OPEN',
'CLOSED FOR RECEIVING'),
'CLOSED FOR RECEIVING', 'CLOSED',
decode(nvl(l_AUTO_CLOSE_TWO_WAY,'N'),'Y', -- Bug 11730977 start
decode(nvl(poll.receipt_required_flag,'N'),'N',decode(nvl(poll.inspection_required_flag,'N'),'N','CLOSED','CLOSED FOR
INVOICE')),'N','CLOSED FOR INVOICE')), -- Bug 11730977 end
'OPEN',
DECODE(
DECODE(sign(
((poll.quantity - NVL(poll.quantity_cancelled, 0))
* (1 - NVL(poll.receive_close_tolerance,
NVL(posp.receive_close_tolerance, 0))/100))
- DECODE(posp.receive_close_code,
'ACCEPTED', NVL(poll.quantity_accepted, 0),
'DELIVERED', sum(NVL(pod.quantity_delivered, 0)),
NVL(poll.quantity_received, 0))),
1, 'OPEN',
'CLOSED FOR RECEIVING'),
'CLOSED FOR RECEIVING', 'CLOSED FOR RECEIVING',
'OPEN')))
FROM po_distributions pod
, po_system_parameters posp
WHERE poll.line_location_id = l_id_tbl(i)
AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND pod.line_location_id = poll.line_location_id
GROUP BY poll.quantity
, poll.quantity_cancelled
, poll.quantity_billed
, poll.quantity_financed
, poll.quantity_accepted
, poll.quantity_received
, poll.amount
, poll.amount_cancelled
, poll.amount_billed
, poll.amount_financed
, poll.amount_accepted
, poll.amount_received
, poll.matching_basis
, poll.invoice_close_tolerance
, poll.receive_close_tolerance
, posp.receive_close_code
, posp.receive_close_tolerance
, posp.invoice_close_tolerance
)
WHERE poll.line_location_id = l_id_tbl(i)
AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND (Nvl(poll.approved_flag,'N') <> 'Y'
OR (Nvl(poll.approved_flag,'N') ='Y' AND Nvl(poll.cancel_flag,'N') = 'Y'));
UPDATE po_line_locations poll
SET poll.closed_code =
(
SELECT DECODE(poll.matching_basis,
'AMOUNT',
DECODE(
DECODE(sign(
((poll.amount - NVL(poll.amount_cancelled, 0))
* (1 - NVL(poll.invoice_close_tolerance,
NVL(posp.invoice_close_tolerance, 0))/100))
- NVL(poll.amount_billed, 0)),--bug 16432524
1, 'OPEN',
'CLOSED FOR INVOICE'),
'CLOSED FOR INVOICE',
DECODE(
DECODE(sign(
((poll.amount - NVL(poll.amount_cancelled, 0))
* (1 - NVL(poll.receive_close_tolerance,
NVL(posp.receive_close_tolerance, 0))/100))
- DECODE(posp.receive_close_code,
'ACCEPTED', NVL(poll.amount_accepted, 0),
'DELIVERED', sum(NVL(pod.amount_delivered, 0)),
NVL(poll.amount_received, 0))),
1, 'OPEN',
'CLOSED FOR RECEIVING'),
'CLOSED FOR RECEIVING', 'CLOSED',
'CLOSED FOR INVOICE'),
'OPEN',
DECODE(
DECODE(sign(
((poll.amount - NVL(poll.amount_cancelled, 0))
* (1 - NVL(poll.receive_close_tolerance,
NVL(posp.receive_close_tolerance, 0))/100))
- DECODE(posp.receive_close_code,
'ACCEPTED', NVL(poll.amount_accepted, 0),
'DELIVERED', sum(NVL(pod.amount_delivered, 0)),
NVL(poll.amount_received, 0))),
1, 'OPEN',
'CLOSED FOR RECEIVING'),
'CLOSED FOR RECEIVING', 'CLOSED FOR RECEIVING',
'OPEN')),
-- else QUANTITY BASIS
DECODE(
DECODE(sign(
((poll.quantity - NVL(poll.quantity_cancelled, 0))
* (1 - NVL(poll.invoice_close_tolerance,
NVL(posp.invoice_close_tolerance, 0))/100))
- NVL(poll.quantity_billed, 0)),--bug 16432524
1, 'OPEN',
'CLOSED FOR INVOICE'),
'CLOSED FOR INVOICE',
DECODE(
DECODE(sign(
((poll.quantity - NVL(poll.quantity_cancelled, 0))
* (1 - NVL(poll.receive_close_tolerance,
NVL(posp.receive_close_tolerance, 0))/100))
- DECODE(posp.receive_close_code,
'ACCEPTED', NVL(poll.quantity_accepted, 0),
'DELIVERED', sum(NVL(pod.quantity_delivered, 0)),
NVL(poll.quantity_received, 0))),
1, 'OPEN',
'CLOSED FOR RECEIVING'),
'CLOSED FOR RECEIVING', 'CLOSED',
'CLOSED FOR INVOICE'),
'OPEN',
DECODE(
DECODE(sign(
((poll.quantity - NVL(poll.quantity_cancelled, 0))
* (1 - NVL(poll.receive_close_tolerance,
NVL(posp.receive_close_tolerance, 0))/100))
- DECODE(posp.receive_close_code,
'ACCEPTED', NVL(poll.quantity_accepted, 0),
'DELIVERED', sum(NVL(pod.quantity_delivered, 0)),
NVL(poll.quantity_received, 0))),
1, 'OPEN',
'CLOSED FOR RECEIVING'),
'CLOSED FOR RECEIVING', 'CLOSED FOR RECEIVING',
'OPEN')))
FROM po_distributions pod
, po_system_parameters posp
WHERE poll.line_location_id = l_id_tbl(i)
AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND pod.line_location_id = poll.line_location_id
GROUP BY poll.quantity
, poll.quantity_cancelled
, poll.quantity_billed
, poll.quantity_financed
, poll.quantity_accepted
, poll.quantity_received
, poll.amount
, poll.amount_cancelled
, poll.amount_billed
, poll.amount_financed
, poll.amount_accepted
, poll.amount_received
, poll.matching_basis
, poll.invoice_close_tolerance
, poll.receive_close_tolerance
, posp.receive_close_code
, posp.receive_close_tolerance
, posp.invoice_close_tolerance
)
WHERE poll.line_location_id = l_id_tbl(i)
AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED';
UPDATE po_line_locations poll
SET poll.closed_code =
(
SELECT DECODE(poll.matching_basis,
'AMOUNT',
DECODE(
DECODE(sign(
((poll.amount - NVL(poll.amount_cancelled, 0))
* (1 - NVL(poll.receive_close_tolerance,
NVL(posp.receive_close_tolerance, 0))/100))
- DECODE(posp.receive_close_code,
'ACCEPTED', NVL(poll.amount_accepted, 0),
'DELIVERED', sum(NVL(pod.amount_delivered, 0)),
NVL(poll.amount_received, 0))),
1, 'OPEN',
'CLOSED FOR RECEIVING'),
'CLOSED FOR RECEIVING',
DECODE(NVL(poll.closed_code, 'OPEN'),
'OPEN', 'CLOSED FOR RECEIVING',
'CLOSED FOR INVOICE', 'CLOSED',
poll.closed_code),
'OPEN',
DECODE(poll.closed_code,
'CLOSED', decode(nvl(l_AUTO_CLOSE_TWO_WAY,'N'),'Y', -- Bug 11730977 start
decode(nvl(poll.receipt_required_flag,'N'),'N',decode(nvl(poll.inspection_required_flag,'N'),'N',
'CLOSED','CLOSED FOR INVOICE')),'N','CLOSED FOR INVOICE'), ---- Bug 11730977 end
'CLOSED FOR RECEIVING', 'OPEN',
poll.closed_code)),
-- else QUANTITY BASIS
DECODE(
DECODE(sign(
((poll.quantity - NVL(poll.quantity_cancelled, 0))
* (1 - NVL(poll.receive_close_tolerance,
NVL(posp.receive_close_tolerance, 0))/100))
- DECODE(posp.receive_close_code,
'ACCEPTED', NVL(poll.quantity_accepted, 0),
'DELIVERED', sum(NVL(pod.quantity_delivered, 0)),
NVL(poll.quantity_received, 0))),
1, 'OPEN',
'CLOSED FOR RECEIVING'),
'CLOSED FOR RECEIVING',
DECODE(NVL(poll.closed_code, 'OPEN'),
'OPEN', 'CLOSED FOR RECEIVING',
'CLOSED FOR INVOICE', 'CLOSED',
poll.closed_code),
'OPEN',
DECODE(poll.closed_code,
'CLOSED',
decode(nvl(l_AUTO_CLOSE_TWO_WAY,'N'),'Y', -- Bug 11730977 start
decode(nvl(poll.receipt_required_flag,'N'),'N',decode(nvl(poll.inspection_required_flag,'N'),'N',
'CLOSED','CLOSED FOR INVOICE')),'N','CLOSED FOR INVOICE'), -- Bug 11730977 end
'CLOSED FOR RECEIVING', 'OPEN',
poll.closed_code)))
FROM po_distributions pod
, po_system_parameters posp
WHERE poll.line_location_id = l_id_tbl(i)
AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND pod.line_location_id = poll.line_location_id
GROUP BY poll.quantity
, poll.quantity_cancelled
, poll.quantity_accepted
, poll.quantity_received
, poll.amount
, poll.amount_cancelled
, poll.amount_accepted
, poll.amount_received
, poll.matching_basis
, poll.receive_close_tolerance
, posp.receive_close_code
, poll.closed_code
, posp.receive_close_tolerance
)
WHERE poll.line_location_id = l_id_tbl(i)
AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED';
UPDATE po_line_locations poll
SET poll.closed_code =
(
SELECT DECODE(poll.matching_basis,
'AMOUNT',
DECODE(
DECODE(sign(
((poll.amount - NVL(poll.amount_cancelled, 0))
* (1 - NVL(poll.invoice_close_tolerance,
NVL(posp.invoice_close_tolerance, 0))/100))
- NVL(poll.amount_billed, 0)),--bug 16432524
1, 'OPEN',
'CLOSED FOR INVOICE'),
'CLOSED FOR INVOICE',
DECODE(NVL(poll.closed_code, 'OPEN'),'OPEN',-- 11730977 start
decode(nvl(l_AUTO_CLOSE_TWO_WAY,'N'),'Y',
decode(nvl(poll.receipt_required_flag,'N'),'N',decode(nvl(poll.inspection_required_flag,'N'),'N','CLOSED','CLOSED FOR
INVOICE')),'N','CLOSED FOR INVOICE'),
'CLOSED FOR RECEIVING',
'CLOSED',
poll.closed_code), -- 11730977 end
'OPEN',
DECODE(poll.closed_code,
'CLOSED', 'CLOSED FOR RECEIVING',
'CLOSED FOR INVOICE', 'OPEN',
poll.closed_code)),
-- else QUANTITY BASIS
DECODE(
DECODE(sign(
((poll.quantity - NVL(poll.quantity_cancelled, 0))
* (1 - NVL(poll.invoice_close_tolerance,
NVL(posp.invoice_close_tolerance, 0))/100))
- NVL(poll.quantity_billed, 0)),--bug 16432524
1, 'OPEN',
'CLOSED FOR INVOICE'),
'CLOSED FOR INVOICE',
DECODE(NVL(poll.closed_code, 'OPEN'),'OPEN', -- 11730977 start
decode(nvl(l_AUTO_CLOSE_TWO_WAY,'N'),'Y',
decode(nvl(poll.receipt_required_flag,'N'),'N',decode(nvl(poll.inspection_required_flag,'N'),'N','CLOSED','CLOSED FOR
INVOICE')),'N','CLOSED FOR INVOICE'),
'CLOSED FOR RECEIVING',
'CLOSED',
poll.closed_code), -- 11730977 end
'OPEN',
DECODE(poll.closed_code,
'CLOSED', 'CLOSED FOR RECEIVING',
'CLOSED FOR INVOICE', 'OPEN',
poll.closed_code)))
FROM po_distributions pod
, po_system_parameters posp
WHERE poll.line_location_id = l_id_tbl(i)
AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND pod.line_location_id = poll.line_location_id
GROUP BY poll.quantity
, poll.quantity_cancelled
, poll.quantity_billed
, poll.quantity_financed
, poll.amount
, poll.amount_cancelled
, poll.amount_billed
, poll.amount_financed
, poll.matching_basis
, poll.invoice_close_tolerance
, poll.closed_code
, posp.invoice_close_tolerance
)
WHERE poll.line_location_id = l_id_tbl(i)
AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED';
UPDATE po_line_locations poll
SET poll.closed_date = DECODE(NVL(poll.closed_code, 'OPEN'), 'CLOSED', SYSDATE, NULL)
, poll.closed_reason = DECODE(NVL(poll.closed_code, 'OPEN'), 'CLOSED', p_reason, NULL)
, poll.closed_by = DECODE(NVL(poll.closed_code, 'OPEN'), 'CLOSED', p_employee_id, NULL)
WHERE POLL.LINE_LOCATION_ID = L_ID_TBL(I)
AND poll.closed_code <> l_closed_code_tbl(i)
AND NVL(poll.closed_code, 'OPEN') IN ('CLOSED','OPEN','CLOSED FOR INVOICE', 'CLOSED FOR RECEIVING');
UPDATE po_line_locations poll
SET poll.shipment_closed_date = DECODE(poll.closed_code,
'CLOSED', NVL(poll.shipment_closed_date,
PO_ACTIONS.get_closure_dates('CLOSE', poll.line_location_id)),
NULL)
, poll.closed_for_receiving_date = DECODE(poll.closed_code,
'CLOSED FOR RECEIVING', NVL(poll.closed_for_receiving_date,
PO_ACTIONS.get_closure_dates('RECEIVE CLOSE', poll.line_location_id)),
'CLOSED FOR INVOICE', NULL,
'CLOSED', NVL(poll.closed_for_receiving_date,
PO_ACTIONS.get_closure_dates('RECEIVE CLOSE', poll.line_location_id)),
'OPEN', NULL)
, poll.closed_for_invoice_date = DECODE(poll.closed_code,
'CLOSED FOR RECEIVING', NULL,
'CLOSED FOR INVOICE', NVL(poll.closed_for_invoice_date,
PO_ACTIONS.get_closure_dates('INVOICE CLOSE', poll.line_location_id)),
'CLOSED', NVL(poll.closed_for_invoice_date,
PO_ACTIONS.get_closure_dates('INVOICE CLOSE', poll.line_location_id)),
'OPEN', NULL)
, poll.last_update_date = SYSDATE --bug4964600
, poll.last_updated_by = p_user_id --bug4964600
, poll.last_update_login = p_login_id --bug4964600
WHERE poll.line_location_id = l_id_tbl(i)
AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED';
END auto_update_closed_status;
l_update_action_hist BOOLEAN;
SELECT 'OPEN'
FROM po_line_locations poll
WHERE poll.po_release_id = p_rel_id
AND NVL(poll.closed_code, 'OPEN') IN ('OPEN', 'CLOSED FOR INVOICE', 'CLOSED FOR RECEIVING')
AND rownum = 1;
SELECT 'CLOSED'
FROM po_line_locations poll
WHERE poll.po_release_id = p_rel_id
AND NVL(poll.closed_code, 'CLOSED') = 'CLOSED'
AND rownum = 1;
SELECT pol.po_line_id
BULK COLLECT INTO l_lineid_tbl
FROM po_lines pol
WHERE pol.po_line_id =
( SELECT poll.po_line_id
FROM po_line_locations poll
WHERE poll.line_location_id = p_shipment_id)
;
SELECT pol.po_line_id
BULK COLLECT INTO l_lineid_tbl
FROM po_lines pol
WHERE pol.po_line_id = p_line_id;
SELECT pol.po_line_id
BULK COLLECT INTO l_lineid_tbl
FROM po_lines pol
WHERE pol.po_header_id = p_document_id;
SELECT DECODE(max(DECODE(poll.closed_code,
'CLOSED', 2,
'FINALLY CLOSED', 1,
3)),
3, 'OPEN',
2, l_none_open_one_closed,
1, l_all_finally_closed )
INTO l_rollup_code
FROM po_line_locations poll
WHERE poll.po_line_id = l_lineid_tbl(i)
AND poll.po_release_id IS NULL
AND poll.shipment_type <> 'PREPAYMENT'; --
UPDATE po_lines pol
SET pol.closed_code = l_rollup_code
, pol.last_update_date = SYSDATE
, pol.last_updated_by = p_user_id
, pol.last_update_login = p_login_id
, pol.closed_by = p_employee_id
, pol.closed_date = DECODE(l_rollup_code,
'CLOSED', SYSDATE,
'FINALLY CLOSED', SYSDATE,
NULL)
, pol.closed_reason = DECODE(p_shipment_id, NULL, p_reason, l_rollup_msg)
WHERE pol.po_line_id = l_lineid_tbl(i)
AND NVL(pol.closed_code, 'OPEN') <> l_rollup_code
AND (((p_action = 'INVOICE OPEN') AND (p_calling_mode = 'AP'))
OR (NVL(pol.closed_code, 'OPEN') <> 'FINALLY CLOSED'));
SELECT DECODE(max(DECODE(pol.closed_code,
'CLOSED', 2,
'FINALLY CLOSED', 1,
3)),
3, 'OPEN',
2, l_none_open_one_closed,
1, l_all_finally_closed)
INTO l_rollup_code
FROM po_lines pol
WHERE pol.po_header_id = p_document_id;
UPDATE po_headers poh
SET poh.closed_code = l_rollup_code
, poh.last_update_date = SYSDATE
, poh.last_updated_by = p_user_id
, poh.last_update_login = p_login_id
, poh.closed_date = decode(l_rollup_code,
'CLOSED', SYSDATE,
'FINALLY CLOSED', SYSDATE,
NULL)
WHERE poh.po_header_id = p_document_id
AND NVL(poh.closed_code, 'OPEN') <> l_rollup_code;
l_update_action_hist := TRUE;
l_update_action_hist := FALSE;
UPDATE po_releases por
SET por.closed_code = l_rollup_code
, por.last_update_date = SYSDATE
, por.last_updated_by = p_user_id
, por.last_update_login = p_login_id
WHERE por.po_release_id = p_document_id
AND NVL(por.closed_code, 'OPEN') <> l_rollup_code;
l_update_action_hist := TRUE;
l_update_action_hist := FALSE;
l_update_action_hist := TRUE;
PO_LOG.stmt(d_module, d_progress, 'l_update_action_hist', l_update_action_hist);
IF (l_update_action_hist)
THEN
d_progress := 410;
SELECT poll.line_location_id, poll.closed_code
BULK COLLECT INTO l_ship_id_tbl, l_closed_code_tbl
FROM po_line_locations poll
WHERE poll.line_location_id = p_shipment_id
AND NVL(poll.approved_flag, 'N') = 'Y'
AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED';
SELECT poll.line_location_id, poll.closed_code
BULK COLLECT INTO l_ship_id_tbl, l_closed_code_tbl
FROM po_line_locations poll
WHERE poll.po_line_id = p_line_id
AND NVL(poll.approved_flag, 'N') = 'Y'
AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED';
SELECT poll.line_location_id, poll.closed_code
BULK COLLECT INTO l_ship_id_tbl, l_closed_code_tbl
FROM po_line_locations poll
WHERE poll.po_release_id = p_document_id
AND NVL(poll.approved_flag, 'N') = 'Y'
AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED';
SELECT poll.line_location_id, poll.closed_code
BULK COLLECT INTO l_ship_id_tbl, l_closed_code_tbl
FROM po_line_locations poll
WHERE poll.po_header_id = p_document_id
AND NVL(poll.approved_flag, 'N') = 'Y'
AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED';