[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
FUNCTION isDropShipWithUpdateableSO(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_entity_level IN VARCHAR2,
p_entity_id IN NUMBER,
p_document_type IN VARCHAR2)
RETURN BOOLEAN
IS
d_api_version CONSTANT NUMBER := 1.0;
d_api_name CONSTANT VARCHAR2(30) := 'isDropShipWithUpdateableSO.';
SELECT line_location_id
FROM po_line_locations
WHERE NVL(drop_ship_flag, 'N') = 'Y'
AND ((p_entity_level = c_entity_level_HEADER
AND p_document_type <> c_doc_type_RELEASE
AND po_header_id = p_entity_id)
OR
(p_entity_level = c_entity_level_HEADER
AND p_document_type = c_doc_type_RELEASE
AND po_release_id = p_entity_id)
OR
(p_entity_level = c_entity_level_LINE
AND po_line_id = p_entity_id)
OR
(p_entity_level = c_entity_level_SHIPMENT
AND line_location_id = p_entity_id)
);
END isDropShipWithUpdateableSO;
SELECT 1
INTO l_partial_rcv_bld
FROM DUAL
WHERE EXISTS (SELECT 1
FROM po_line_locations
WHERE ( (p_document_type <>c_doc_type_RELEASE
AND po_header_id = p_entity_id)
OR(p_document_type = c_doc_type_RELEASE
AND po_release_id = p_entity_id)
)
AND NVL(cancel_flag, 'N') <> 'Y'
AND NVL(closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND (quantity_received > 0
OR quantity_billed > 0 ));
SELECT 1
INTO l_partial_rcv_bld
FROM DUAL
WHERE EXISTS (SELECT 1
FROM po_line_locations
WHERE po_line_id = p_entity_id
AND NVL(cancel_flag, 'N') <> 'Y'
AND NVL(closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND(quantity_received > 0
OR quantity_billed > 0 ));
SELECT 1
INTO l_partial_rcv_bld
FROM DUAL
WHERE EXISTS(SELECT 1
FROM po_line_locations
WHERE line_location_id = p_entity_id
AND NVL(cancel_flag, 'N') <> 'Y'
AND NVL(closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND ( quantity_received > 0 OR quantity_billed > 0));
IF isDropShipWithUpdateableSO(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_entity_level=>p_entity_dtl(i).entity_level,
p_entity_id=>p_entity_dtl(i).entity_id,
p_document_type=>p_entity_dtl(i).document_type)
THEN
l_progress := '013';
END IF; -- if isDropShipWithUpdateableSO
UPDATE po_line_locations POLL
SET (POLL.quantity_billed, POLL.quantity_received) =
(SELECT
SUM( NVL(RELS.quantity_billed, 0) ),
SUM( NVL(RELS.quantity_received, 0) )
FROM
PO_LINE_LOCATIONS RELS
WHERE
RELS.source_shipment_id = POLL.line_location_id )
WHERE POLL.shipment_type = 'PLANNED'
AND POLL.line_location_id IN
(SELECT num1
FROM po_session_gt
WHERE char3 = c_entity_level_SHIPMENT
AND char2 = c_doc_subtype_PLANNED
UNION
SELECT line_location_id
FROM po_line_locations,
po_session_gt
WHERE num1 = po_line_id
AND char3 = c_entity_level_LINE
AND char2 = c_doc_subtype_PLANNED
AND NVL(cancel_flag, 'N') = 'N'
AND NVL(closed_code, 'OPEN') <> 'FINALLY CLOSED'
UNION
SELECT line_location_id
FROM po_line_locations,
po_session_gt
WHERE num1 = po_header_id
AND char3 = c_entity_level_HEADER
AND char2 = c_doc_subtype_PLANNED
AND NVL(cancel_flag, 'N') = 'N'
AND NVL(closed_code, 'OPEN') <> 'FINALLY CLOSED'
);
PO_DEBUG.debug_var(d_module, l_progress, 'Rows Updated the PO line locations table', SQL%ROWCOUNT);
UPDATE PO_DISTRIBUTIONS_ALL POD
SET (POD.quantity_billed,POD.quantity_delivered) =
( SELECT SUM( NVL(RELD.quantity_billed, 0) ),
SUM( NVL(RELD.quantity_delivered, 0) )
FROM PO_DISTRIBUTIONS RELD
WHERE RELD.source_distribution_id = POD.po_distribution_id )
WHERE POD.line_location_id IN
( SELECT num1
FROM po_session_gt
WHERE char3= c_entity_level_SHIPMENT
AND char2 = c_doc_subtype_PLANNED
AND char5 = 'Y'
UNION
SELECT line_location_id
FROM po_line_locations,
po_session_gt
WHERE num1 = po_line_id
AND shipment_type = 'PLANNED'
AND char3 = c_entity_level_LINE
AND char2 = c_doc_subtype_PLANNED
AND NVL(cancel_flag, 'N') = 'N'
AND char5 = 'Y'
AND NVL(closed_code, 'OPEN') <> 'FINALLY CLOSED'
UNION
SELECT line_location_id
FROM po_line_locations,
po_session_gt
WHERE num1 = po_header_id
AND shipment_type = 'PLANNED'
AND char3 = c_entity_level_HEADER
AND char2 = c_doc_subtype_PLANNED
AND NVL(cancel_flag, 'N') = 'N'
AND char5 = 'Y'
AND NVL(closed_code, 'OPEN') <> 'FINALLY CLOSED') ;
PO_DEBUG.debug_var(d_module, l_progress, 'Rows Updated the PO Distributions table', SQL%ROWCOUNT);
PROCEDURE update_dist_cancel(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_fc_level IN VARCHAR2,
p_action_date IN DATE,
p_entity_level IN VARCHAR2,
p_entity_id IN NUMBER,
p_document_type IN VARCHAR2,
p_recreate_demand IN VARCHAR2,
p_user_id IN po_lines.last_updated_by%TYPE,
p_login_id IN po_lines.last_update_login%TYPE,
x_return_status OUT NOCOPY VARCHAR2)
IS
d_api_name CONSTANT VARCHAR2(30) := 'update_dist_cancel';
UPDATE
PO_DISTRIBUTIONS_ALL POD
SET
pod.quantity_cancelled = pod.quantity_ordered-greatest(
NVL(quantity_delivered,0),
NVL(quantity_financed,0),
NVL(quantity_billed,0)),
pod.amount_cancelled = pod.amount_ordered-greatest(
NVL(amount_delivered,0),
NVL(amount_financed,0),
NVL(amount_billed,0)),
pod.last_update_date = SYSDATE ,
pod.last_updated_by = p_user_id ,
pod.last_update_login = p_login_id
WHERE pod.line_location_id IN
( SELECT line_location_id
FROM po_line_locations
WHERE line_location_id = p_entity_id
AND p_entity_level = c_entity_level_SHIPMENT
UNION ALL
SELECT line_location_id
FROM po_line_locations
WHERE po_line_id = p_entity_id
AND p_entity_level = c_entity_level_LINE
UNION ALL
SELECT line_location_id
FROM po_line_locations
WHERE po_header_id = p_entity_id
AND p_document_type <> c_doc_type_RELEASE
AND p_entity_level = c_entity_level_HEADER
UNION ALL
SELECT line_location_id
FROM po_line_locations
WHERE po_release_id = p_entity_id
AND p_document_type = c_doc_type_RELEASE
AND p_entity_level = c_entity_level_HEADER);
UPDATE PO_DISTRIBUTIONS_ALL POD
SET pod.gl_cancelled_date = p_action_date,
pod.req_distribution_id = Decode(
p_recreate_demand,
'Y',
DECODE(
greatest(
NVL(pod.quantity_delivered, 0),
NVL(pod.quantity_billed, 0)),
0,
NULL,
pod.req_distribution_id),
pod.req_distribution_id),
pod.last_update_date = SYSDATE ,
pod.last_updated_by = p_user_id,
pod.last_update_login = p_login_id
WHERE pod.line_location_id IN
( SELECT line_location_id
FROM po_line_locations
WHERE line_location_id = p_entity_id
AND p_entity_level = c_entity_level_SHIPMENT
UNION ALL
SELECT line_location_id
FROM po_line_locations
WHERE po_line_id = p_entity_id
AND p_entity_level = c_entity_level_LINE
UNION ALL
SELECT line_location_id
FROM po_line_locations
WHERE po_header_id = p_entity_id
AND p_document_type <> c_doc_type_RELEASE
AND p_entity_level = c_entity_level_HEADER
UNION ALL
SELECT line_location_id
FROM po_line_locations
WHERE po_release_id = p_entity_id
AND p_document_type = c_doc_type_RELEASE
AND p_entity_level = c_entity_level_HEADER);
PO_DEBUG.debug_var(d_module, l_progress, 'Rows Updated in PO Distributions table', SQL%ROWCOUNT);
END update_dist_cancel;
PROCEDURE update_ship_cancel(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_fc_level IN VARCHAR2,
p_cancel_reason IN VARCHAR2,
p_entity_level IN VARCHAR2,
p_entity_id IN NUMBER,
p_action_date IN DATE,
p_document_type IN VARCHAR2,
p_user_id IN po_lines.last_updated_by%TYPE,
p_login_id IN po_lines.last_update_login%TYPE,
x_return_status IN OUT NOCOPY VARCHAR2)
IS
d_api_name CONSTANT VARCHAR2(30) := 'update_ship_cancel';
UPDATE po_line_locations POLL
SET POLL.cancel_flag = 'I' ,
--Bug 16575765: CANCEL_DATE is always stamped(SHIPMENT) as sysdate.
--POLL.cancel_date = p_action_date ,
POLL.cancel_date = SYSDATE ,
POLL.cancel_reason = p_cancel_reason,
POLL.cancelled_by = l_emp_id ,
POLL.last_update_date = SYSDATE ,
POLL.last_updated_by = p_user_id ,
POLL.last_update_login = p_login_id ,
POLL.request_id = DECODE(l_request_id,
NULL,
request_id,
-1,
request_id,
l_request_id) ,
POLL.quantity_cancelled = (SELECT SUM(NVL(POD.quantity_cancelled,0))
FROM PO_DISTRIBUTIONS_ALL POD
WHERE POD.line_location_id=POLL.line_location_id),
POLL.amount_cancelled = (SELECT SUM(NVL(POD.amount_cancelled, 0))
FROM PO_DISTRIBUTIONS_ALL POD
WHERE POD.line_location_id=POLL.line_location_id)
WHERE NVL(poll.cancel_flag, 'N') = 'N'
AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND POLL.line_location_id IN
( SELECT line_location_id
FROM po_line_locations
WHERE line_location_id = p_entity_id
AND p_entity_level = c_entity_level_SHIPMENT
UNION ALL
SELECT line_location_id
FROM po_line_locations
WHERE po_line_id = p_entity_id
AND p_entity_level = c_entity_level_LINE
UNION ALL
SELECT line_location_id
FROM po_line_locations
WHERE po_header_id = p_entity_id
AND p_document_type <> c_doc_type_RELEASE
AND p_entity_level = c_entity_level_HEADER
UNION ALL
SELECT line_location_id
FROM po_line_locations
WHERE po_release_id = p_entity_id
AND p_document_type = c_doc_type_RELEASE
AND p_entity_level = c_entity_level_HEADER);
UPDATE po_line_locations POLL
SET POLL.cancel_flag = 'Y'
WHERE NVL(poll.cancel_flag, 'N') = 'I'
AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND POLL.line_location_id IN
( SELECT line_location_id
FROM po_line_locations
WHERE line_location_id = p_entity_id
AND p_entity_level = c_entity_level_SHIPMENT
UNION ALL
SELECT line_location_id
FROM po_line_locations
WHERE po_line_id = p_entity_id
AND p_entity_level = c_entity_level_LINE
UNION ALL
SELECT line_location_id
FROM po_line_locations
WHERE po_header_id = p_entity_id
AND p_document_type <> c_doc_type_RELEASE
AND p_entity_level = c_entity_level_HEADER
UNION ALL
SELECT line_location_id
FROM po_line_locations
WHERE po_release_id = p_entity_id
AND p_document_type = c_doc_type_RELEASE
AND p_entity_level = c_entity_level_HEADER);
'Rows Updated in PO LineLocations table',
SQL%ROWCOUNT);
END update_ship_cancel;
PROCEDURE update_line_qty_price_amt(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_ship_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2)
IS
d_api_version CONSTANT NUMBER := 1.0;
d_api_name CONSTANT VARCHAR2(30) := 'update_line_qty_price_amt';
UPDATE po_lines pol
SET pol.quantity =(SELECT SUM(
NVL(poll.quantity,0)
- NVL(poll.quantity_cancelled, 0))
FROM po_line_locations POLL
WHERE poll.po_line_id = pol.po_line_id
AND poll.shipment_type IN ('STANDARD','PLANNED'))
WHERE pol.po_line_id=(SELECT po_line_id
FROM po_line_locations
WHERE line_location_id = p_ship_id)
AND pol.order_type_lookup_code IN ('QUANTITY','AMOUNT')
AND NOT EXISTS (SELECT
'PO Line has Qty Milestone Pay Items'
FROM po_line_locations poll2
WHERE poll2.po_line_id=pol.po_line_id
AND poll2.payment_type IS NOT NULL);
PO_DEBUG.debug_var(d_module, l_progress, 'Quanity Updated in PO Lines table', SQL%ROWCOUNT);
UPDATE po_lines pol
SET pol.unit_price =
(SELECT SUM(poll.price_override)
FROM po_line_locations POLL
WHERE poll.po_line_id = pol.po_line_id
AND poll.shipment_type = 'STANDARD'
AND nvl(poll.cancel_flag, 'N') = 'N'
AND nvl(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED')
WHERE pol.po_line_id=(SELECT po_line_id
FROM po_line_locations
WHERE line_location_id= p_ship_id)
AND pol.order_type_lookup_code IN ('QUANTITY', 'AMOUNT')
AND EXISTS
(SELECT 'PO Line has open Qty Milestone Pay Items'
FROM po_line_locations poll2
WHERE poll2.po_line_id = pol.po_line_id
AND poll2.payment_type IS NOT NULL
AND nvl(poll2.cancel_flag, 'N') = 'N'
AND nvl(poll2.closed_code, 'OPEN') <> 'FINALLY CLOSED');
PO_DEBUG.debug_var(d_module, l_progress, 'Price Updated in PO Lines table', SQL%ROWCOUNT);
UPDATE po_lines pol
SET pol.amount= (SELECT SUM(DECODE(
POLL.amount,
NULL,
--Quantity or Amount Line Locations
((NVL(poll.quantity,0) - NVL(poll.quantity_cancelled,0))
* POLL.price_override),
-- Fixed Price or Rate Line Locations
(NVL(poll.amount, 0) - NVL(poll.amount_cancelled,0))
))
FROM po_line_locations POLL
WHERE poll.po_line_id = pol.po_line_id
AND poll.shipment_type IN ('STANDARD','PLANNED'))
WHERE pol.po_line_id =(SELECT po_line_id
FROM po_line_locations
WHERE line_location_id= p_ship_id)
AND pol.order_type_lookup_code in ('FIXED PRICE', 'RATE');
PO_DEBUG.debug_var(d_module, l_progress, 'Amount Updated in PO Lines table', SQL%ROWCOUNT);
END update_line_qty_price_amt;
PROCEDURE update_line_cancel(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_fc_level IN VARCHAR2,
p_cancel_reason IN VARCHAR2,
p_entity_level IN VARCHAR2,
p_entity_id IN NUMBER,
p_action_date IN DATE,
p_user_id IN po_lines.last_updated_by%TYPE,
p_login_id IN po_lines.last_update_login%TYPE,
p_note_to_vendor IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2)
IS
d_api_name CONSTANT VARCHAR2(30) := 'update_line_cancel';
UPDATE po_lines pol
SET pol.cancel_flag = 'I',
--Bug 16575765: CANCEL_DATE is always stamped(LINE) as sysdate.
--pol.cancel_date = p_action_date,
pol.cancel_date = SYSDATE,
pol.cancel_reason = p_cancel_reason,
pol.cancelled_by = l_emp_id,
pol.last_update_date = sysdate,
pol.last_updated_by = p_user_id,
pol.last_update_login = p_login_id,
pol.note_to_vendor =p_note_to_vendor ,
pol.request_id = DECODE(l_request_id,
NULL,
request_id,
-1,
request_id,
l_request_id) ,
pol.quantity =
DECODE(pol.quantity,
NULL,
pol.quantity,
(SELECT SUM(NVL(poll.quantity,0) -NVL(poll.quantity_cancelled,0))
FROM po_line_locations POLL
WHERE poll.po_line_id = pol.po_line_id
AND poll.shipment_type IN('STANDARD','PLANNED'))
),
pol.amount =
DECODE(pol.amount,
NULL,
pol.amount,
(SELECT SUM(DECODE(POLL.amount,
NULL,
((NVL(poll.quantity,0) -NVL(poll.quantity_cancelled,0))
* POLL.price_override),
(NVL(poll.amount, 0) -NVL(poll.amount_cancelled,0))
)
)
FROM po_line_locations POLL
WHERE poll.po_line_id = pol.po_line_id
AND poll.shipment_type IN ('STANDARD','PLANNED')) )
WHERE nvl(pol.cancel_flag,'N') = 'N'
AND nvl(pol.closed_code,'OPEN') <> 'FINALLY CLOSED'
AND pol.po_line_id IN (
SELECT po_line_id
FROM po_lines
WHERE po_line_id= p_entity_id
AND p_entity_level=c_entity_level_LINE
UNION ALL
SELECT po_line_id
FROM po_lines
WHERE po_header_id= p_entity_id
AND p_entity_level=c_entity_level_HEADER);
UPDATE po_lines POl
SET pol.cancel_flag = 'Y'
WHERE nvl(pol.cancel_flag,'N') = 'I'
AND nvl(pol.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND pol.po_line_id IN (
SELECT po_line_id
FROM po_lines
WHERE po_line_id= p_entity_id
AND p_entity_level=c_entity_level_LINE
UNION ALL
SELECT po_line_id
FROM po_lines
WHERE po_header_id= p_entity_id
AND p_entity_level=c_entity_level_HEADER);
PO_DEBUG.debug_var(d_module, l_progress, 'Rows Updated in PO Lines table', SQL%ROWCOUNT);
END update_line_cancel;
PROCEDURE update_po_header_cancel(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_fc_level IN VARCHAR2,
p_entity_id IN NUMBER,
p_action_date IN DATE,
p_user_id IN po_lines.last_updated_by%TYPE,
p_login_id IN po_lines.last_update_login%TYPE,
p_note_to_vendor IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2)
IS
d_api_name CONSTANT VARCHAR2(30) := 'update_po_header_cancel';
UPDATE po_headers poh
SET poh.cancel_flag = 'I' ,
poh.last_update_date = SYSDATE ,
poh.note_to_vendor =p_note_to_vendor ,
poh.last_updated_by = p_user_id,
poh.last_update_login = p_login_id,
poh.acceptance_required_flag =NULL,
poh.request_id = DECODE(l_request_id,
NULL,
request_id,
-1,
request_id,
l_request_id)
WHERE poh.po_header_id= p_entity_id;
UPDATE po_headers poh
SET poh.cancel_flag = 'Y' ,
poh.closed_code = 'CLOSED',
--Bug 16575765: CLOSED_DATE is always stamped(HEADER) as sysdate.
--poh.closed_date = p_action_date
poh.closed_date = sysdate
WHERE NVL(poh.cancel_flag, 'N') = 'I'
AND poh.po_header_id= p_entity_id;
PO_DEBUG.debug_var(d_module, l_progress, 'Rows Updated in PO Headers table', SQL%ROWCOUNT);
END update_po_header_cancel;
PROCEDURE update_rel_header_cancel(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_entity_id IN NUMBER,
p_fc_level IN VARCHAR2,
p_action_date IN DATE,
p_cancel_reason IN VARCHAR2,
p_note_to_vendor IN VARCHAR2,
p_user_id IN po_lines.last_updated_by%TYPE,
p_login_id IN po_lines.last_update_login%TYPE,
x_return_status OUT NOCOPY VARCHAR2)
IS
d_api_name CONSTANT VARCHAR2(30) := 'update_rel_header_cancel';
UPDATE po_releases por
SET por.cancel_flag = 'I' ,
por.cancel_reason = p_cancel_reason,
por.cancelled_by = l_emp_id ,
--Bug 16575765: CANCEL_DATE is always stamped(RELEASE) as sysdate.
--por.cancel_date = p_action_date ,
por.cancel_date = sysdate,
por.note_to_vendor = p_note_to_vendor,
por.last_update_date = SYSDATE ,
por.last_updated_by = p_user_id ,
por.last_update_login = p_login_id,
por.request_id = DECODE(l_request_id,
NULL,
request_id,
-1,
request_id,
l_request_id)
WHERE por.po_release_id = p_entity_id;
UPDATE po_releases por
SET por.cancel_flag = 'Y'
WHERE NVL(por.cancel_flag, 'N') = 'I'
AND por.po_release_id = p_entity_id;
PO_DEBUG.debug_var(d_module, l_progress, 'Rows Updated in Release Headers table', SQL%ROWCOUNT);
END update_rel_header_cancel;
PROCEDURE update_document(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_entity_level IN VARCHAR2,
p_action_date IN DATE,
p_entity_id IN NUMBER,
p_document_type IN VARCHAR2,
p_doc_subtype IN VARCHAR2,
p_cancel_reason IN VARCHAR2,
p_fc_level IN VARCHAR2,
p_recreate_demand IN VARCHAR2,
p_note_to_vendor IN VARCHAR2,
p_user_id IN po_lines.last_updated_by%TYPE,
p_login_id IN po_lines.last_update_login%TYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2)
IS
d_api_name CONSTANT VARCHAR2(30) := 'update_document';
update_dist_cancel(
p_api_version=> 1.0,
p_init_msg_list=>FND_API.G_FALSE,
p_fc_level => p_fc_level,
p_action_date => p_action_date,
p_entity_level => p_entity_level,
p_entity_id => p_entity_id,
p_document_type=>p_document_type,
p_recreate_demand =>p_recreate_demand,
p_user_id => p_user_id,
p_login_id => p_login_id,
x_return_status => x_return_status);
update_ship_cancel(
p_api_version=> 1.0,
p_init_msg_list=>FND_API.G_FALSE,
p_fc_level => p_fc_level,
p_cancel_reason => p_cancel_reason,
p_entity_level => p_entity_level,
p_entity_id => p_entity_id,
p_action_date => p_action_date,
p_document_type=>p_document_type,
p_user_id => p_user_id,
p_login_id => p_login_id,
x_return_status => x_return_status);
update_line_qty_price_amt(
p_api_version=> 1.0,
p_init_msg_list=>FND_API.G_FALSE,
p_ship_id=>p_entity_id,
x_return_status=>x_return_status);
update_line_cancel(
p_api_version=> 1.0,
p_init_msg_list=>FND_API.G_FALSE,
p_fc_level => p_fc_level,
p_cancel_reason =>p_cancel_reason,
p_entity_level => p_entity_level,
p_entity_id => p_entity_id,
p_action_date => p_action_date,
p_user_id => p_user_id,
p_login_id => p_login_id,
p_note_to_vendor =>p_note_to_vendor,
x_return_status => x_return_status);
update_rel_header_cancel(
p_api_version=> 1.0,
p_init_msg_list=>FND_API.G_FALSE,
p_fc_level => p_fc_level,
p_entity_id => p_entity_id,
p_action_date => p_action_date,
p_cancel_reason =>p_cancel_reason,
p_user_id =>p_user_id,
p_login_id =>p_login_id,
p_note_to_vendor =>p_note_to_vendor,
x_return_status =>x_return_status);
update_po_header_cancel(
p_api_version=> 1.0,
p_init_msg_list=>FND_API.G_FALSE,
p_fc_level => p_fc_level,
p_entity_id => p_entity_id,
p_action_date => p_action_date,
p_user_id =>p_user_id,
p_login_id =>p_login_id,
p_note_to_vendor =>p_note_to_vendor,
x_return_status =>x_return_status);
END update_document;
SELECT DISTINCT(prd.requisition_line_id)
FROM po_req_distributions_all prd,
po_line_locations poll ,
po_distributions_all pod
WHERE pod.line_location_id = poll.line_location_id
AND pod.req_distribution_id = prd.distribution_id
AND NVL(poll.cancel_flag, 'N') = 'I'
AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND poll.shipment_type IN(
'STANDARD',
'PLANNED' ,
'BLANKET' ,
'PREPAYMENT')
AND ((p_entity_level = c_entity_level_HEADER
AND p_document_type <> c_doc_type_RELEASE
AND pod.po_header_id = p_entity_id)
OR(p_entity_level = c_entity_level_HEADER
AND p_document_type = c_doc_type_RELEASE
AND pod.po_release_id = p_entity_id)
OR(p_entity_level = c_entity_level_LINE
AND pod.po_line_id = p_entity_id)
OR(p_entity_level = c_entity_level_SHIPMENT
AND pod.line_location_id = p_entity_id));
SELECT DISTINCT(prl.requisition_line_id)
FROM po_requisition_lines_all prl,
po_line_locations poll
WHERE prl.line_location_id= (-1 * poll.line_location_id)
AND NVL(poll.cancel_flag, 'N') = 'Y'
AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND poll.shipment_type IN(
'STANDARD',
'PLANNED' ,
'BLANKET' ,
'PREPAYMENT')
AND ((p_entity_level = c_entity_level_HEADER
AND p_document_type <> c_doc_type_RELEASE
AND poll.po_header_id = p_entity_id)
OR(p_entity_level = c_entity_level_HEADER
AND p_document_type = c_doc_type_RELEASE
AND poll.po_release_id = p_entity_id)
OR(p_entity_level = c_entity_level_LINE
AND poll.po_line_id = p_entity_id)
OR(p_entity_level = c_entity_level_SHIPMENT
AND poll.line_location_id = p_entity_id));
SELECT PO_UOM_S.PO_UOM_CONVERT_P(
POL.UNIT_MEAS_LOOKUP_CODE,
PORL.UNIT_MEAS_LOOKUP_CODE,
PORL.ITEM_ID)
INTO x_uom_conv
FROM PO_REQUISITION_LINES_ALL PORL,
po_line_locations POLL ,
po_lines POL
WHERE PORL.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID
AND POLL.PO_LINE_ID = POL.PO_LINE_ID
AND PORL.REQUISITION_LINE_ID = p_req_line_id ;
SELECT PO_UOM_S.PO_UOM_CONVERT_P(
POL.UNIT_MEAS_LOOKUP_CODE,
PORL.UNIT_MEAS_LOOKUP_CODE,
PORL.ITEM_ID)
INTO x_uom_conv
FROM PO_REQUISITION_LINES_ALL PORL,
po_line_locations POLL ,
po_lines POL
WHERE (-1)*PORL.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID
AND POLL.PO_LINE_ID = POL.PO_LINE_ID
and porl.requisition_line_id = p_req_line_id ;
SELECT DECODE(
SIGN(SUM(PORD.req_line_quantity
- p_uom_conv *greatest(NVL(POD.quantity_delivered, 0),
NVL(POD.quantity_billed, 0)
)
)
),
-1,
0,
SUM(PORD.req_line_quantity
- p_uom_conv *greatest(NVL(POD.quantity_delivered, 0),
NVL(POD.quantity_billed, 0)
)
)
)
INTO x_qty_cancelled
FROM PO_DISTRIBUTIONS_ALL POd ,
po_req_distributions_all pord,
po_line_locations POLL
WHERE pord.requisition_line_id = p_req_line_id
AND pord.distribution_id = pod.req_distribution_id
AND POLL.line_location_id = POD.line_location_id
AND POLL.shipment_type IN(
'STANDARD',
'PLANNED' ,
'BLANKET');
PROCEDURE update_req_details_after_fc(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_req_line_id IN NUMBER,
p_recreate_demand_flag IN VARCHAR2,
p_user_id IN po_lines.last_updated_by%TYPE,
p_login_id IN po_lines.last_update_login%TYPE,
p_is_new_req_line IN VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2)
IS
d_api_name CONSTANT VARCHAR2(30) := 'update_req_details_after_fc';
UPDATE PO_REQ_DISTRIBUTIONS PORD
SET PORD.req_line_quantity =
(SELECT DECODE(SIGN(PORD.req_line_quantity
- SUM(l_uom_conv * greatest(NVL(POD.quantity_delivered, 0),
NVL(POD.quantity_billed, 0)
)
)
),
-1,
PORD.req_line_quantity,
0,
PORD.req_line_quantity,
SUM(l_uom_conv * (greatest(NVL(POD.quantity_delivered, 0),
NVL(POD.quantity_billed, 0)) )) )
FROM po_line_locations POLL,
PO_DISTRIBUTIONS_ALL POD
WHERE POD.req_distribution_id = PORD.distribution_id
AND POLL.line_location_id = POD.line_location_id
AND POLL.shipment_type IN ('STANDARD',
'PLANNED' ,
'BLANKET') )
WHERE PORD.requisition_line_id = p_req_line_id ;
PO_DEBUG.debug_var(d_module, l_progress, 'Rows Updated in PO_REQ_DISTRIBUTIONS table', SQL%ROWCOUNT);
UPDATE PO_REQUISITION_LINES_ALL PORL
SET PORL.cancel_flag = 'Y' ,
PORL.contractor_status = NULL ,
PORL.cancel_date = SYSDATE ,
PORL.line_location_id = -1 * PORL.line_location_id,
PORL.quantity_cancelled = l_qty_cancelled
WHERE PORL.requisition_line_id = p_req_line_id;
PO_DEBUG.debug_var(d_module, l_progress, 'Rows Updated in PO_REQUISITION_LINES_ALL table', SQL%ROWCOUNT);
UPDATE PO_REQ_DISTRIBUTIONS_ALL PORD
SET PORD.req_line_quantity = (SELECT Least(PORD.req_line_quantity ,
sum(l_uom_conv *
Greatest(NVL(POD.quantity_delivered, 0),
NVL(POD.quantity_billed, 0))
)
)
FROM po_line_locations POLL,
PO_DISTRIBUTIONS_ALL POD
WHERE POD.req_distribution_id = PORD.distribution_id
AND POLL.line_location_id = POD.line_location_id
AND POLL.shipment_type IN ('STANDARD' ,
'PLANNED' ,
'BLANKET') ),
PORD.last_update_date = SYSDATE ,
PORD.last_updated_by = p_user_id,
PORD.last_update_login = p_login_id
WHERE PORD.distribution_id IN (
SELECT PORD1.distribution_id
FROM PO_REQUISITION_LINES_ALL PORL_NEW,
PO_REQUISITION_LINES_ALL PORL_OLD,
PO_REQ_DISTRIBUTIONS_ALL PORD1 ,
PO_DISTRIBUTIONS POD
WHERE PORL_NEW.requisition_line_id = p_req_line_id
AND PORL_OLD.requisition_line_id = (-1) * PORL_NEW.parent_req_line_id
AND PORD1.requisition_line_id = PORL_OLD.requisition_line_id
AND POD.req_distribution_id = PORD1.distribution_id
AND NVL(POD.quantity_cancelled, 0) > 0 );
PO_DEBUG.debug_var(d_module, l_progress, 'Rows Updated in PO_REQ_DISTRIBUTIONS_ALL table', SQL%ROWCOUNT);
SELECT SUM(PORD.req_line_quantity)
INTO l_qty_cancelled
FROM PO_REQ_DISTRIBUTIONS_ALL PORD
WHERE PORD.distribution_id IN (
SELECT PORD1.distribution_id
FROM PO_REQUISITION_LINES_ALL PORL_NEW,
PO_REQUISITION_LINES_ALL PORL_OLD,
PO_REQ_DISTRIBUTIONS_ALL PORD1
WHERE PORL_NEW.requisition_line_id= p_req_line_id
AND PORL_OLD.requisition_line_id= (-1) * PORL_NEW.parent_req_line_id
AND PORD1.requisition_line_id = PORL_OLD.requisition_line_id );
UPDATE PO_REQUISITION_LINES_ALL PORL
SET(
PORL.QUANTITY ,
PORL.LAST_UPDATE_DATE,
PORL.LAST_UPDATED_BY ,
PORL.LAST_UPDATE_LOGIN )
= (SELECT DECODE(porl.order_type_lookup_code,
'RATE', NULL,
'FIXED PRICE', NULL,
l_qty_cancelled),
SYSDATE ,
p_user_id ,
p_login_id
FROM po_line_locations POLL,
po_lines POL
WHERE PORL.line_location_id = POLL.line_location_id
AND POLL.po_line_id = POL.po_line_id)
WHERE PORL.requisition_line_id = (
SELECT ((-1) * PORL_NEW.parent_req_line_id)
FROM PO_REQUISITION_LINES_ALL PORL_NEW
WHERE PORL_NEW.requisition_line_id = p_req_line_id );
PO_DEBUG.debug_var(d_module, l_progress, 'Rows Updated in PO_REQUISITION_LINES_ALL table', SQL%ROWCOUNT);
SELECT PORL_OLD.PARENT_REQ_LINE_ID,
PRH.AUTHORIZATION_STATUS
INTO l_parent_req_line_id,
l_auth_status
FROM PO_REQUISITION_LINES_ALL PORL_OLD,
PO_REQUISITION_LINES_ALL PORL_NEW,
PO_REQUISITION_HEADERS_ALL PRH
WHERE PORL_OLD.requisition_line_id = (-1) * PORL_NEW.parent_req_line_id
AND PRH.requisition_header_id = PORL_NEW.requisition_header_id
AND PORL_NEW.requisition_line_id = p_req_line_id;
UPDATE PO_REQUISITION_LINES_ALL PORL
SET PORL.LINE_LOCATION_ID = NULL,
PORL.PARENT_REQ_LINE_ID = DECODE(l_parent_req_line_id,
-9999,
NULL,
l_parent_req_line_id),
LAST_UPDATE_DATE = SYSDATE ,
LAST_UPDATED_BY = p_user_id ,
LAST_UPDATE_LOGIN = p_login_id ,
PORL.REQS_IN_POOL_FLAG = DECODE(l_auth_status,
PO_DOCUMENT_ACTION_PVT.g_doc_status_APPROVED,
'Y',
'N')
WHERE PORL.requisition_line_id = p_req_line_id;
'Rows Updated in PO_REQUISITION_LINES_ALL table',
SQL%ROWCOUNT);
SELECT PRH.AUTHORIZATION_STATUS
INTO l_auth_status
FROM PO_REQUISITION_LINES_ALL PORL,
PO_REQUISITION_HEADERS_ALL PRH
WHERE PRH.requisition_header_id = PORL.requisition_header_id
AND PORL.requisition_line_id = p_req_line_id;
UPDATE PO_REQUISITION_LINES_ALL PORL
SET PORL.LINE_LOCATION_ID = NULL,
LAST_UPDATE_DATE = SYSDATE ,
LAST_UPDATED_BY = p_user_id ,
LAST_UPDATE_LOGIN = p_login_id ,
PORL.REQS_IN_POOL_FLAG = DECODE(l_auth_status,
PO_DOCUMENT_ACTION_PVT.g_doc_status_APPROVED,
'Y',
'N')
WHERE PORL.requisition_line_id = p_req_line_id;
'Rows Updated in PO_REQUISITION_LINES_ALL table',
SQL%ROWCOUNT);
END update_req_details_after_fc;
SELECT SUM(greatest(pod.quantity_delivered,
pod.quantity_billed))
INTO l_qzero
FROM PO_DISTRIBUTIONS_ALL POD,
PO_REQ_DISTRIBUTIONS_ALL PORD
WHERE POD.req_distribution_id = PORD.distribution_id
AND PORD.requisition_line_id = p_req_line_id;
p_user_id IN po_lines.last_updated_by%TYPE,
p_login_id IN po_lines.last_update_login%TYPE,
x_new_line_id OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2)
IS
d_api_name CONSTANT VARCHAR2(30) := 'create_req_line';
SELECT NVL(MAX(porl.line_num), 0) + 1
INTO l_next_line_num
FROM po_requisition_lines_all porl
WHERE porl.requisition_header_id
=(SELECT requisition_header_id
FROM po_requisition_lines_all
WHERE requisition_line_id = p_old_line_id);
SELECT PO_REQUISITION_LINES_S.NEXTVAL
INTO x_new_line_id
FROM SYS.DUAL;
INSERT INTO PO_REQUISITION_LINES_ALL(
REQUISITION_LINE_ID ,
REQUISITION_HEADER_ID ,
LINE_NUM ,
LINE_TYPE_ID ,
CATEGORY_ID ,
ITEM_DESCRIPTION ,
UNIT_MEAS_LOOKUP_CODE ,
UNIT_PRICE ,
QUANTITY ,
DELIVER_TO_LOCATION_ID ,
TO_PERSON_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
SOURCE_TYPE_CODE ,
LAST_UPDATE_LOGIN ,
CREATION_DATE ,
CREATED_BY ,
ITEM_ID ,
ITEM_REVISION ,
QUANTITY_DELIVERED ,
SUGGESTED_BUYER_ID ,
RFQ_REQUIRED_FLAG ,
NEED_BY_DATE ,
LINE_LOCATION_ID ,
MODIFIED_BY_AGENT_FLAG ,
PARENT_REQ_LINE_ID ,
JUSTIFICATION ,
NOTE_TO_AGENT ,
NOTE_TO_RECEIVER ,
PURCHASING_AGENT_ID ,
BLANKET_PO_HEADER_ID ,
BLANKET_PO_LINE_NUM ,
SUGGESTED_VENDOR_NAME ,
SUGGESTED_VENDOR_LOCATION ,
SUGGESTED_VENDOR_CONTACT ,
SUGGESTED_VENDOR_PHONE ,
SUGGESTED_VENDOR_PRODUCT_CODE,
UN_NUMBER_ID ,
HAZARD_CLASS_ID ,
MUST_USE_SUGG_VENDOR_FLAG ,
REFERENCE_NUM ,
ON_RFQ_FLAG ,
URGENT_FLAG ,
CANCEL_FLAG ,
SOURCE_ORGANIZATION_ID ,
SOURCE_SUBINVENTORY ,
DESTINATION_TYPE_CODE ,
DESTINATION_ORGANIZATION_ID ,
DESTINATION_SUBINVENTORY ,
QUANTITY_CANCELLED ,
CANCEL_DATE ,
CANCEL_REASON ,
CLOSED_CODE ,
AGENT_RETURN_NOTE ,
CHANGED_AFTER_RESEARCH_FLAG ,
VENDOR_ID ,
VENDOR_SITE_ID ,
VENDOR_CONTACT_ID ,
RESEARCH_AGENT_ID ,
ON_LINE_FLAG ,
WIP_ENTITY_ID ,
WIP_LINE_ID ,
WIP_REPETITIVE_SCHEDULE_ID ,
WIP_OPERATION_SEQ_NUM ,
WIP_RESOURCE_SEQ_NUM ,
BOM_RESOURCE_ID ,
ATTRIBUTE_CATEGORY ,
DESTINATION_CONTEXT ,
INVENTORY_SOURCE_CONTEXT ,
VENDOR_SOURCE_CONTEXT ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
CURRENCY_CODE ,
CURRENCY_UNIT_PRICE ,
DOCUMENT_TYPE_CODE ,
RATE ,
RATE_DATE ,
RATE_TYPE ,
TAX_CODE_ID ,
TAX_USER_OVERRIDE_FLAG ,
TAX_STATUS_INDICATOR ,
ORG_ID ,
ORDER_TYPE_LOOKUP_CODE ,
PURCHASE_BASIS ,
MATCHING_BASIS ,
BASE_UNIT_PRICE ,
DROP_SHIP_FLAG ,
CATALOG_TYPE ,
CATALOG_SOURCE
)
SELECT
x_new_line_id ,
PORL.REQUISITION_HEADER_ID ,
l_next_line_num ,
PORL.LINE_TYPE_ID ,
PORL.CATEGORY_ID ,
PORL.ITEM_DESCRIPTION ,
PORL.UNIT_MEAS_LOOKUP_CODE ,
PORL.unit_price ,
p_line_quantity ,
PORL.DELIVER_TO_LOCATION_ID ,
PORL.TO_PERSON_ID ,
SYSDATE ,
p_user_id ,
PORL.SOURCE_TYPE_CODE ,
p_login_id ,
PORL.CREATION_DATE ,
p_user_id ,
PORL.ITEM_ID ,
PORL.ITEM_REVISION ,
0 ,
PORL.SUGGESTED_BUYER_ID ,
PORL.RFQ_REQUIRED_FLAG ,
PORL.NEED_BY_DATE ,
(-1 * PORL.LINE_LOCATION_ID) ,
PORL.MODIFIED_BY_AGENT_FLAG ,
(-1 * PORL.REQUISITION_LINE_ID) ,
PORL.JUSTIFICATION ,
PORL.NOTE_TO_AGENT ,
PORL.NOTE_TO_RECEIVER ,
PORL.PURCHASING_AGENT_ID ,
PORL.BLANKET_PO_HEADER_ID ,
PORL.BLANKET_PO_LINE_NUM ,
PORL.SUGGESTED_VENDOR_NAME ,
PORL.SUGGESTED_VENDOR_LOCATION ,
PORL.SUGGESTED_VENDOR_CONTACT ,
PORL.SUGGESTED_VENDOR_PHONE ,
PORL.SUGGESTED_VENDOR_PRODUCT_CODE,
PORL.UN_NUMBER_ID ,
PORL.HAZARD_CLASS_ID ,
PORL.MUST_USE_SUGG_VENDOR_FLAG ,
PORL.REFERENCE_NUM ,
PORL.ON_RFQ_FLAG ,
PORL.URGENT_FLAG ,
PORL.CANCEL_FLAG ,
PORL.SOURCE_ORGANIZATION_ID ,
PORL.SOURCE_SUBINVENTORY ,
PORL.DESTINATION_TYPE_CODE ,
PORL.DESTINATION_ORGANIZATION_ID ,
PORL.DESTINATION_SUBINVENTORY ,
PORL.QUANTITY_CANCELLED ,
PORL.CANCEL_DATE ,
PORL.CANCEL_REASON ,
PORL.CLOSED_CODE ,
PORL.AGENT_RETURN_NOTE ,
PORL.CHANGED_AFTER_RESEARCH_FLAG ,
PORL.VENDOR_ID ,
PORL.VENDOR_SITE_ID ,
PORL.VENDOR_CONTACT_ID ,
PORL.RESEARCH_AGENT_ID ,
PORL.ON_LINE_FLAG ,
PORL.WIP_ENTITY_ID ,
PORL.WIP_LINE_ID ,
PORL.WIP_REPETITIVE_SCHEDULE_ID ,
PORL.WIP_OPERATION_SEQ_NUM ,
PORL.WIP_RESOURCE_SEQ_NUM ,
PORL.BOM_RESOURCE_ID ,
PORL.ATTRIBUTE_CATEGORY ,
PORL.DESTINATION_CONTEXT ,
PORL.INVENTORY_SOURCE_CONTEXT ,
PORL.VENDOR_SOURCE_CONTEXT ,
PORL.ATTRIBUTE1 ,
PORL.ATTRIBUTE2 ,
PORL.ATTRIBUTE3 ,
PORL.ATTRIBUTE4 ,
PORL.ATTRIBUTE5 ,
PORL.ATTRIBUTE6 ,
PORL.ATTRIBUTE7 ,
PORL.ATTRIBUTE8 ,
PORL.ATTRIBUTE9 ,
PORL.ATTRIBUTE10 ,
PORL.ATTRIBUTE11 ,
PORL.ATTRIBUTE12 ,
PORL.ATTRIBUTE13 ,
PORL.ATTRIBUTE14 ,
PORL.ATTRIBUTE15 ,
PORL.CURRENCY_CODE ,
PORL.CURRENCY_UNIT_PRICE ,
PORL.DOCUMENT_TYPE_CODE ,
PORL.RATE ,
PORL.RATE_DATE ,
PORL.RATE_TYPE ,
PORL.TAX_CODE_ID ,
PORL.TAX_USER_OVERRIDE_FLAG ,
PORL.TAX_STATUS_INDICATOR ,
PORL.ORG_ID ,
PORL.ORDER_TYPE_LOOKUP_CODE ,
PORL.PURCHASE_BASIS ,
PORL.MATCHING_BASIS ,
PORL.BASE_UNIT_PRICE ,
PORL.DROP_SHIP_FLAG ,
PORL.CATALOG_TYPE ,
PORL.CATALOG_SOURCE
FROM
PO_REQUISITION_LINES_ALL PORL,
po_line_locations POLL ,
po_lines POL
WHERE
PORL.REQUISITION_LINE_ID = p_old_line_id
AND PORL.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID
AND POLL.PO_LINE_ID = POL.PO_LINE_ID;
X_last_updated_by=>p_user_id,
X_last_update_login=> p_login_id);
p_user_id IN po_lines.last_updated_by%TYPE,
p_login_id IN po_lines.last_update_login%TYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2)
IS
d_api_name CONSTANT VARCHAR2(30) := 'create_req_dist';
INSERT INTO PO_REQ_DISTRIBUTIONS_ALL (
distribution_id ,
last_update_date ,
last_updated_by ,
requisition_line_id ,
set_of_books_id ,
code_combination_id ,
req_line_quantity ,
last_update_login ,
creation_date ,
created_by ,
distribution_num ,
gl_encumbered_date ,
gl_encumbered_period_name ,
gl_cancelled_date ,
failed_funds_lookup_code ,
source_req_distribution_id ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
ATTRIBUTE_CATEGORY ,
ACCRUAL_ACCOUNT_ID ,
BUDGET_ACCOUNT_ID ,
VARIANCE_ACCOUNT_ID ,
government_context ,
project_id ,
task_id ,
expenditure_type ,
project_accounting_context ,
expenditure_organization_id,
project_related_flag ,
expenditure_item_date ,
RECOVERY_RATE ,
TAX_RECOVERY_OVERRIDE_FLAG ,
NONRECOVERABLE_TAX ,
ORG_ID ,
prevent_encumbrance_flag
)
SELECT
PO_REQ_DISTRIBUTIONS_S.NEXTVAL,
SYSDATE ,
p_user_id ,
p_req_line_id ,
PORD.set_of_books_id ,
PORD.code_combination_id ,
(PORD.req_line_quantity
- l_uom_conv * greatest(NVL(POD.quantity_delivered, 0),
NVL(POD.quantity_billed, 0))
) ,
p_login_id ,
SYSDATE ,
p_user_id ,
rownum ,
PORD.gl_encumbered_date ,
PORD.gl_encumbered_period_name ,
PORD.gl_cancelled_date ,
PORD.failed_funds_lookup_code ,
PORD.distribution_id ,
PORD.ATTRIBUTE1 ,
PORD.ATTRIBUTE2 ,
PORD.ATTRIBUTE3 ,
PORD.ATTRIBUTE4 ,
PORD.ATTRIBUTE5 ,
PORD.ATTRIBUTE6 ,
PORD.ATTRIBUTE7 ,
PORD.ATTRIBUTE8 ,
PORD.ATTRIBUTE9 ,
PORD.ATTRIBUTE10 ,
PORD.ATTRIBUTE11 ,
PORD.ATTRIBUTE12 ,
PORD.ATTRIBUTE13 ,
PORD.ATTRIBUTE14 ,
PORD.ATTRIBUTE15 ,
PORD.ATTRIBUTE_CATEGORY ,
PORD.ACCRUAL_ACCOUNT_ID ,
PORD.BUDGET_ACCOUNT_ID ,
PORD.VARIANCE_ACCOUNT_ID ,
PORD.government_context ,
PORD.project_id ,
PORD.task_id ,
PORD.expenditure_type ,
PORD.project_accounting_context ,
PORD.expenditure_organization_id ,
PORD.project_related_flag ,
PORD.expenditure_item_date ,
PORD.RECOVERY_RATE ,
PORD.TAX_RECOVERY_OVERRIDE_FLAG ,
(DECODE(
SIGN(PORD.req_line_quantity
- l_uom_conv * greatest(NVL(POD.quantity_delivered, 0),
NVL(POD.quantity_billed, 0)
)
),
-1,
0,
(PORD.req_line_quantity
- l_uom_conv * greatest(NVL(POD.quantity_delivered, 0),
NVL(POD.quantity_billed, 0)
)
)
) / PORD.req_line_quantity) * PORD.nonrecoverable_tax,
PORL_NEW.ORG_ID ,
NVL(DECODE( POD.prevent_encumbrance_flag,
'Y',
'Y',
DECODE(
PORL_NEW.org_id,
BLANKET.org_id,
BLANKET.encumbrance_required_flag,
NULL )
),
'N')
FROM
PO_REQ_DISTRIBUTIONS_ALL PORD ,
PO_REQUISITION_LINES_ALL PORL_OLD,
PO_REQUISITION_LINES_ALL PORL_NEW,
PO_DISTRIBUTIONS_ALL POD ,
PO_LINE_LOCATIONS PLL ,
po_headers BLANKET
WHERE
PORL_NEW.requisition_line_id = p_req_line_id
AND PORL_OLD.requisition_line_id = (-1) * PORL_NEW.parent_req_line_id
AND PORD.requisition_line_id = PORL_OLD.requisition_line_id
AND POD.req_distribution_id = PORD.distribution_id
AND POD.line_location_id = PLL.line_location_id
AND PLL.shipment_type IN (
'STANDARD',
'PLANNED' ,
'BLANKET')
AND NVL(POD.quantity_cancelled, 0) >= 0
AND PORL_NEW.blanket_po_header_id = BLANKET.po_header_id(+)
AND (PORD.req_line_quantity
- l_uom_conv * Greatest(NVL(POD.quantity_delivered, 0),
NVL(POD.quantity_billed, 0))
> 0) ;
PROCEDURE update_requisitions(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_entity_level IN VARCHAR2,
p_action_date IN DATE,
p_entity_id IN NUMBER,
p_document_type IN VARCHAR2,
p_doc_subtype IN VARCHAR2,
p_cancel_reason IN VARCHAR2,
p_fc_level IN VARCHAR2,
p_recreate_demand IN VARCHAR2,
p_req_enc_flag IN VARCHAR2,
p_user_id IN po_lines.last_updated_by%TYPE,
p_login_id IN po_lines.last_update_login%TYPE,
x_is_new_line IN OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
x_req_line_id_tbl OUT NOCOPY PO_ReqChangeRequestWF_PVT.ReqLineID_tbl_type)
IS
d_api_name CONSTANT VARCHAR2(30) := 'update_requisitions';
UPDATE PO_REQUISITION_LINES_ALL PORL
SET PORL.cancel_flag = 'I' ,
PORL.LAST_UPDATE_DATE = SYSDATE ,
PORL.LAST_UPDATED_BY = p_user_id ,
PORL.LAST_UPDATE_LOGIN = p_login_id,
PORL.line_location_id = -1 * PORL.line_location_id
WHERE PORL.requisition_line_id = x_req_line_id_tbl(i);
PO_DEBUG.debug_var(d_module, l_progress, 'update_req lines before_fc', SQL%ROWCOUNT);
update_req_details_after_fc(
p_api_version=> 1.0,
p_init_msg_list=>FND_API.G_FALSE,
p_req_line_id=>x_req_line_id_tbl(i),
p_recreate_demand_flag=>p_recreate_demand,
p_user_id=>p_user_id,
p_login_id=>p_login_id,
p_is_new_req_line=>x_is_new_line,
x_msg_data =>x_msg_data,
x_return_status =>x_return_status);
UPDATE PO_REQ_DISTRIBUTIONS_ALL PORD
SET PORD.last_update_date = SYSDATE ,
PORD.last_updated_by = p_user_id ,
PORD.last_update_login = p_login_id,
PORD.prevent_encumbrance_flag =
(SELECT NVL(DECODE(
POD.prevent_encumbrance_flag,
'Y',
'Y',
DECODE(PORL.org_id,
BLANKET.org_id,
BLANKET.encumbrance_required_flag,
NULL)),
'N')
FROM PO_REQUISITION_LINES_ALL PORL,
PO_DISTRIBUTIONS_ALL POD ,
po_line_locations PLL ,
po_headers BLANKET
WHERE PORL.requisition_line_id = PORD.requisition_line_id
AND POD.req_distribution_id = PORD.distribution_id
AND POD.line_location_id = PLL.line_location_id
AND PLL.shipment_type IN (
'STANDARD',
'PLANNED' ,
'BLANKET')
AND PORL.blanket_po_header_id = BLANKET.po_header_id)
WHERE PORD.requisition_line_id= x_req_line_id_tbl(i)
AND EXISTS (SELECT 1
FROM po_requisition_lines_all porl
WHERE porl.requisition_line_id = PORD.requisition_line_id
AND porl.blanket_po_header_id IS NOT NULL);
PO_DEBUG.debug_var(d_module, l_progress, 'update_req Dsitributions before_fc', SQL%ROWCOUNT);
-- The prevent encumbrance flag is updated to 'Y' when the main document is reserved
-- and is backed by an encumbered BPA
-- The prevent encumbrance flag to 'N' on all the req distributions
-- which were processed during Reserve action
UPDATE PO_REQ_DISTRIBUTIONS_ALL PORD
SET PORD.last_update_date = SYSDATE,
PORD.last_updated_by = p_user_id,
PORD.last_update_login = p_login_id,
PORD.prevent_encumbrance_flag =
DECODE(
p_req_enc_flag,
'Y',
'N',
pord.prevent_encumbrance_flag)
WHERE PORD.requisition_line_id = x_req_line_id_tbl(i)
AND EXISTS (SELECT 1
FROM po_requisition_lines_all porl
WHERE porl.requisition_line_id = PORD.requisition_line_id
AND porl.blanket_po_header_id IS NULL)
AND EXISTS (SELECT 1
FROM po_distributions_all poda,
po_lines pol,
po_distributions_all pod
WHERE poda.po_header_id = pol.from_header_id
AND pol.po_line_id = pod.po_line_id
AND pod.req_distribution_id = pord.distribution_id
AND pod.distribution_type = c_doc_subtype_STANDARD
UNION
SELECT 1
FROM po_distributions_all poda,
po_distributions_all pod
WHERE pod.req_distribution_id = pord.distribution_id
AND pod.distribution_type = c_doc_subtype_BLANKET
AND pod.po_header_id = poda.po_header_id
AND poda.distribution_type = 'AGREEMENT');
SELECT SUM(PORD.REQ_LINE_QUANTITY)
INTO l_quantity
FROM PO_REQ_DISTRIBUTIONS_ALL PORD
WHERE PORD.REQUISITION_LINE_ID = x_req_line_id_tbl(i);
UPDATE PO_REQUISITION_LINES_ALL PORL
SET(
PORL.LINE_LOCATION_ID,
PORL.QUANTITY ,
PORL.LAST_UPDATE_DATE,
PORL.LAST_UPDATED_BY ,
PORL.LAST_UPDATE_LOGIN) =
(
SELECT
(-1 * PORL.LINE_LOCATION_ID),
DECODE(porl.order_type_lookup_code,
'RATE', NULL,
'FIXED PRICE',NULL,
l_quantity),
SYSDATE ,
p_user_id ,
p_login_id
FROM po_line_locations POLL,
po_lines POL
WHERE PORL.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID
AND POLL.PO_LINE_ID = POL.PO_LINE_ID
)
WHERE PORL.REQUISITION_LINE_ID = x_req_line_id_tbl(i);
PO_DEBUG.debug_var(d_module, l_progress, 'update_req Lines before_fc', SQL%ROWCOUNT);
UPDATE PO_REQUISITION_LINES_ALL PORL
SET PORL.cancel_flag = 'Y',--doubt
PORL.contractor_status = NULL ,
PORL.cancel_date = SYSDATE ,
PORL.last_update_date = SYSDATE ,
PORL.last_updated_by = p_user_id,
PORL.last_update_login = p_login_id
WHERE PORL.requisition_line_id = x_req_line_id_tbl(i);
UPDATE PO_REQ_DISTRIBUTIONS_ALL PORD
SET PORD.last_update_date = SYSDATE ,
PORD.last_updated_by = p_user_id,
PORD.last_update_login = p_login_id
WHERE PORD.requisition_line_id = x_req_line_id_tbl(i);
update_req_details_after_fc(
p_api_version=> 1.0,
p_init_msg_list=>FND_API.G_FALSE,
p_req_line_id=>x_req_line_id_tbl(i),
p_recreate_demand_flag=>p_recreate_demand,
p_user_id=>p_user_id,
p_login_id=>p_login_id,
p_is_new_req_line=>x_is_new_line,
x_return_status =>x_return_status,
x_msg_data =>x_msg_data);
END update_requisitions;
PROCEDURE update_req_po_after_fc(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_entity_level IN VARCHAR2,
p_action_date IN DATE,
p_entity_id IN NUMBER,
p_doc_id IN NUMBER,
p_document_type IN VARCHAR2,
p_doc_subtype IN VARCHAR2,
p_cancel_reason IN VARCHAR2,
p_recreate_flag IN VARCHAR2,
p_note_to_vendor IN VARCHAR2,
p_req_enc_flag IN VARCHAR2,
p_source IN VARCHAR2,
p_user_id IN po_lines.last_updated_by%TYPE,
p_login_id IN po_lines.last_update_login%TYPE,
x_is_new_line IN OUT NOCOPY VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2,
x_msg_data IN OUT NOCOPY VARCHAR2)
IS
d_api_name CONSTANT VARCHAR2(30) := 'update_req_po_after_fc';
update_document(
p_api_version=> 1.0,
p_init_msg_list=>FND_API.G_FALSE,
p_entity_level =>p_entity_level,
p_action_date =>p_action_date,
p_entity_id =>p_entity_id,
p_document_type =>p_document_type,
p_doc_subtype =>p_doc_subtype,
p_cancel_reason =>p_cancel_reason,
p_recreate_demand =>p_recreate_flag,
p_fc_level =>l_fc_level,
p_note_to_vendor =>p_note_to_vendor,
p_user_id =>p_user_id,
p_login_id => p_login_id,
x_return_status =>x_return_status,
x_msg_data =>x_msg_data);
update_requisitions(
p_api_version=> 1.0,
p_init_msg_list=>FND_API.G_FALSE,
p_entity_level =>p_entity_level,
p_action_date =>p_action_date,
p_entity_id =>p_entity_id,
p_document_type =>p_document_type,
p_doc_subtype =>p_doc_subtype,
p_cancel_reason =>p_cancel_reason,
p_recreate_demand =>p_recreate_flag,
p_req_enc_flag =>p_req_enc_flag,
p_fc_level =>l_fc_level,
p_user_id =>p_user_id,
p_login_id =>p_login_id,
x_is_new_line =>x_is_new_line,
x_return_status =>x_return_status,
x_msg_data =>x_msg_data,
x_req_line_id_tbl =>l_req_line_id_tbl);
END update_req_po_after_fc;
PROCEDURE update_req_po_before_fc(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_entity_level IN VARCHAR2,
p_action_date IN DATE,
p_entity_id IN NUMBER,
p_document_type IN VARCHAR2,
p_doc_subtype IN VARCHAR2,
p_cancel_reason IN VARCHAR2,
p_recreate_demand IN VARCHAR2,
p_req_enc_flag IN VARCHAR2,
p_note_to_vendor IN VARCHAR2,
p_user_id IN po_lines.last_updated_by%TYPE,
p_login_id IN po_lines.last_update_login%TYPE,
x_is_new_line IN OUT NOCOPY VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2,
x_msg_data IN OUT NOCOPY VARCHAR2)
IS
d_api_name CONSTANT VARCHAR2(30) := 'update_req_po_before_fc';
update_document(
p_api_version=> 1.0,
p_init_msg_list=>FND_API.G_FALSE,
p_entity_level =>p_entity_level,
p_action_date =>p_action_date,
p_entity_id =>p_entity_id,
p_document_type =>p_document_type,
p_doc_subtype =>p_doc_subtype,
p_cancel_reason =>p_cancel_reason,
p_recreate_demand=>p_recreate_demand,
p_fc_level =>l_fc_level,
p_note_to_vendor =>p_note_to_vendor,
p_user_id =>p_user_id,
p_login_id => p_login_id,
x_return_status =>x_return_status,
x_msg_data =>x_msg_data);
update_requisitions(
p_api_version=> 1.0,
p_init_msg_list=>FND_API.G_FALSE,
p_entity_level =>p_entity_level,
p_action_date =>p_action_date,
p_entity_id =>p_entity_id,
p_document_type =>p_document_type,
p_doc_subtype =>p_doc_subtype,
p_cancel_reason =>p_cancel_reason,
p_recreate_demand =>p_recreate_demand,
p_req_enc_flag=>p_req_enc_flag,
p_fc_level =>l_fc_level,
p_user_id =>p_user_id,
p_login_id =>p_login_id,
x_is_new_line=>x_is_new_line,
x_return_status =>x_return_status,
x_msg_data =>x_msg_data,
x_req_line_id_tbl =>l_req_line_id_tbl);
END update_req_po_before_fc;
l_user_id po_lines.last_updated_by%TYPE := -1;
l_login_id po_lines.last_update_login%TYPE := -1;
PO_DOCUMENT_ACTION_UTIL.update_doc_auth_status(
p_document_id => p_entity_id,
p_document_type => p_document_type,
p_document_subtype => p_doc_subtype,
p_new_status => PO_DOCUMENT_ACTION_PVT.g_doc_status_APPROVED,
p_user_id => l_user_id,
p_login_id => l_login_id,
x_return_status => x_return_status
);
UPDATE po_line_locations
SET approved_flag = 'Y',
approved_date = SYSDATE
WHERE ( (line_location_id = p_entity_id
AND p_entity_level = c_entity_level_SHIPMENT)
OR(po_line_id = p_entity_id
AND p_entity_level = c_entity_level_LINE));
PO_DEBUG.debug_var(d_module, l_progress, 'Rows Updated the PO line locations table', SQL%ROWCOUNT);
PROCEDURE update_po_rev_archive(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_entity_level IN VARCHAR2,
p_entity_id IN NUMBER,
p_doc_id IN NUMBER,
p_document_type IN VARCHAR2,
p_doc_subtype IN VARCHAR2,
p_action_date IN DATE,
p_reason IN VARCHAR2,
p_user_id IN po_lines.last_updated_by%TYPE,
p_login_id IN po_lines.last_update_login%TYPE,
p_caller IN VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2,
x_msg_data IN OUT NOCOPY VARCHAR2)
IS
d_api_name CONSTANT VARCHAR2(30) := 'update_po_rev_archive';
SELECT revision_num,
authorization_status
INTO l_orig_revision_num,
l_head_auth_status
FROM po_releases
WHERE PO_RELEASE_ID = p_doc_id;
SELECT revision_num,
authorization_status
INTO l_orig_revision_num,
l_head_auth_status
FROM po_headers
WHERE PO_HEADER_ID = p_doc_id;
SELECT COUNT(1)
INTO l_count
FROM po_line_locations
WHERE NVL(approved_flag, 'N') = 'R'
AND ( (line_location_id = p_entity_id
AND p_entity_level = c_entity_level_LINE
AND p_document_type <> c_doc_type_PA)
OR(line_location_id = p_entity_id
AND p_entity_level = c_entity_level_SHIPMENT)) ;
UPDATE PO_RELEASES_ALL
SET REVISION_NUM = l_revision_num,
REVISED_DATE = DECODE (
revision_num,
l_revision_num,
REVISED_DATE,
SYSDATE),
LAST_UPDATE_DATE = SYSDATE ,
LAST_UPDATED_BY = p_user_id ,
LAST_UPDATE_LOGIN = p_login_id
WHERE PO_RELEASE_ID = p_doc_id;
PO_DEBUG.debug_var(d_module, l_progress, 'Rows updated in PO Releases', SQL%ROWCOUNT);
UPDATE po_headers
SET REVISION_NUM = l_revision_num,
REVISED_DATE = DECODE(
revision_num,
l_revision_num,
REVISED_DATE,
SYSDATE),
LAST_UPDATE_DATE = SYSDATE ,
LAST_UPDATED_BY = p_user_id ,
LAST_UPDATE_LOGIN = p_login_id
WHERE PO_HEADER_ID = p_doc_id;
PO_DEBUG.debug_var(d_module, l_progress, 'Rows updated in PO Headers', SQL%ROWCOUNT);
SELECT MAX(sequence_num)
INTO l_sequence_num
FROM PO_ACTION_HISTORY
WHERE object_type_code= p_document_type --'PO'
AND object_sub_type_code = p_doc_subtype --'STANDARD'
AND object_id = p_doc_id;
SELECT HR.employee_id
INTO l_employee_id
FROM FND_USER FND,
HR_EMPLOYEES_CURRENT_V HR
WHERE FND.user_id = p_user_id
AND FND.employee_id = HR.employee_id ;
po_forward_sv1.insert_action_history(
x_object_id=> p_doc_id,
x_object_type_code =>p_document_type,
x_object_sub_type_code =>p_doc_subtype,
x_sequence_num =>l_sequence_num+1,
x_action_code =>'CANCEL',
x_action_date =>p_action_date,
x_employee_id =>l_employee_id,
x_approval_path_id =>NULL,
x_note =>p_reason,
x_object_revision_num =>l_revision_num,
x_offline_code =>NULL,
x_request_id =>NULL,
x_program_application_id =>NULL,
x_program_id =>NULL,
x_program_date =>SYSDATE,
x_user_id =>p_user_id,
x_login_id =>p_login_id) ;
END update_po_rev_archive;
PROCEDURE update_closed_code(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_entity_level IN VARCHAR2,
p_entity_id IN NUMBER,
p_doc_id IN NUMBER,
p_document_type IN VARCHAR2,
p_doc_subtype IN VARCHAR2,
p_user_id IN po_lines.last_updated_by%TYPE,
p_login_id IN po_lines.last_update_login%TYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2)
IS
d_api_name CONSTANT VARCHAR2(30) := 'update_closed_code';
PO_DOCUMENT_ACTION_PVT.auto_update_close_state(
p_document_id => p_doc_id,
p_document_type => p_document_type,
p_document_subtype => p_doc_subtype,
p_line_id => l_line_id,
p_shipment_id => l_ship_id,
p_calling_mode => 'PO',
p_called_from_conc => FALSE,
x_return_status => x_return_status,
x_return_code => l_return_code,
x_exception_msg => x_msg_data );
END update_closed_code;
p_user_id IN po_lines.last_updated_by%TYPE,
p_login_id IN po_lines.last_update_login%TYPE,
p_po_enc_flag IN FINANCIALS_SYSTEM_PARAMETERS.purch_encumbrance_flag%TYPE,
p_req_enc_flag IN FINANCIALS_SYSTEM_PARAMETERS.req_encumbrance_flag%TYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2)
IS
d_api_name CONSTANT VARCHAR2(30) := 'process_cancel_action';
update_req_po_before_fc(
p_api_version=> 1.0,
p_init_msg_list=>FND_API.G_FALSE,
p_entity_level =>l_entity_rec_tbl(i).entity_level,
p_action_date =>l_entity_rec_tbl(i).entity_action_date,
p_entity_id =>l_entity_rec_tbl(i).entity_id,
p_document_type =>l_entity_rec_tbl(i).document_type,
p_doc_subtype =>l_entity_rec_tbl(i).document_subtype,
p_cancel_reason =>l_cancel_reason,
p_recreate_demand => l_entity_rec_tbl(i).recreate_demand_flag,
p_note_to_vendor =>p_da_call_rec.note_to_vendor,
p_req_enc_flag =>p_req_enc_flag,
x_is_new_line=>l_is_new_req_line,
p_user_id =>p_user_id,
p_login_id => p_login_id,
x_return_status =>x_return_status,
x_msg_data =>x_msg_data);
SELECT Nvl(encumbrance_required_flag,'N')
INTO l_enc_req_flag
FROM po_headers
WHERE po_header_id=l_entity_rec_tbl(i).entity_id;
update_req_po_after_fc(
p_api_version=> 1.0,
p_init_msg_list=>FND_API.G_FALSE,
p_entity_level =>l_entity_rec_tbl(i).entity_level,
p_action_date =>l_entity_rec_tbl(i).entity_action_date,
p_entity_id =>l_entity_rec_tbl(i).entity_id,
p_doc_id =>l_entity_rec_tbl(i).doc_id,
p_document_type =>l_entity_rec_tbl(i).document_type,
p_doc_subtype =>l_entity_rec_tbl(i).document_subtype,
p_cancel_reason =>l_cancel_reason,
p_recreate_flag =>l_entity_rec_tbl(i).recreate_demand_flag,
p_req_enc_flag =>p_req_enc_flag,
p_note_to_vendor =>p_da_call_rec.note_to_vendor,
p_source =>p_da_call_rec.caller,
x_is_new_line=>l_is_new_req_line,
p_user_id =>p_user_id,
p_login_id => p_login_id,
x_return_status =>x_return_status,
x_msg_data =>x_msg_data);
update_closed_code(
p_api_version=> 1.0,
p_init_msg_list=>FND_API.G_FALSE,
p_entity_level =>c_entity_level_HEADER,
p_entity_id =>l_entity_rec_tbl(i).doc_id,
p_doc_id => l_entity_rec_tbl(i).doc_id,
p_document_type =>l_entity_rec_tbl(i).document_type,
p_doc_subtype =>l_entity_rec_tbl(i).document_subtype,
p_user_id =>p_user_id,
p_login_id => p_login_id,
x_return_status =>x_return_status,
x_msg_data =>x_msg_data);
update_po_rev_archive(
p_api_version=> 1.0,
p_init_msg_list=>FND_API.G_FALSE,
p_entity_level =>l_entity_rec_tbl(i).entity_level,
p_entity_id =>l_entity_rec_tbl(i).entity_id,
p_doc_id => l_entity_rec_tbl(i).doc_id,
p_document_type =>l_entity_rec_tbl(i).document_type,
p_doc_subtype =>l_entity_rec_tbl(i).document_subtype,
p_reason =>l_cancel_reason,
p_action_date =>l_entity_rec_tbl(i).entity_action_date,
p_user_id =>p_user_id,
p_login_id =>p_login_id,
p_caller =>p_da_call_rec.caller,
x_return_status => x_return_status,
x_msg_data =>x_msg_data);
INSERT INTO po_session_gt(
KEY ,
num1 ,
char1,
char2,
char3,
char4,
char5)
VALUES(
x_key ,
P_entity_rec_tbl(i).entity_id ,
P_entity_rec_tbl(i).document_type ,
P_entity_rec_tbl(i).document_subtype,
P_entity_rec_tbl(i).entity_level ,
P_entity_rec_tbl(i).doc_id ,
'Y');
l_user_id po_lines.last_updated_by%TYPE := -1;
l_login_id po_lines.last_update_login%TYPE := -1;
SELECT NVL(fsp.purch_encumbrance_flag, 'N'),
NVL(fsp.req_encumbrance_flag, 'N')
INTO l_po_encumbrance_flag,
l_req_encumbrance_flag
FROM financials_system_parameters fsp;
SELECT COUNT(*)
INTO l_cto_order
FROM po_requisition_headers_all PRH,
po_requisition_lines_all PRL,
po_line_locations POLL
WHERE PRH.interface_source_code = 'CTO'
AND PRH.requisition_header_id = PRL.requisition_header_id
AND PRL.line_location_id = POLL.line_location_id
AND ( ((p_doc_type = 'PO') AND (POLL.po_header_id = p_doc_id)) OR
((p_doc_type = 'RELEASE') AND (POLL.po_release_id = p_doc_id)));
l_user_id po_lines.last_updated_by%TYPE := -1;
l_login_id po_lines.last_update_login%TYPE := -1;
SELECT line_location_id
FROM po_line_locations
WHERE line_location_id = p_line_location_id
AND p_line_location_id IS NOT NULL
UNION ALL
SELECT line_location_id
FROM po_line_locations
WHERE po_line_id = p_line_id
AND p_line_location_id IS NULL
AND p_line_id IS NOT NULL
UNION ALL
SELECT line_location_id
FROM po_line_locations
WHERE po_header_id = p_doc_header_id
AND p_line_location_id IS NULL
AND p_line_id IS NULL
AND p_doc_header_id IS NOT NULL
AND p_document_type <> c_doc_type_RELEASE
UNION ALL
SELECT line_location_id
FROM po_line_locations
WHERE po_release_id = p_doc_header_id
AND p_line_location_id IS NULL
AND p_line_id IS NULL
AND p_doc_header_id IS NOT NULL
AND p_document_type = c_doc_type_RELEASE;
UPDATE
PO_DISTRIBUTIONS_ALL POD
SET
pod.quantity_cancelled = pod.quantity_ordered-greatest(
NVL(quantity_delivered,0),
NVL(quantity_financed,0),
NVL(quantity_billed,0)),
pod.amount_cancelled = pod.amount_ordered-greatest(
NVL(amount_delivered,0),
NVL(amount_financed,0),
NVL(amount_billed,0)),
pod.last_update_date = p_action_date,
pod.last_updated_by = l_user_id,
pod.last_update_login = l_login_id
WHERE pod.line_location_id =l_line_loc_tbl(i);
PO_DEBUG.debug_var(d_module, l_progress, 'Rows Updated in PO Distributions table', SQL%ROWCOUNT);
UPDATE po_line_locations POLL
SET POLL.last_update_date = p_action_date,
POLL.last_updated_by = l_user_id,
POLL.last_update_login = l_login_id,
POLL.quantity_cancelled = (SELECT SUM(NVL(POD.quantity_cancelled,0))
FROM PO_DISTRIBUTIONS_ALL POD
WHERE POD.line_location_id=POLL.line_location_id),
POLL.amount_cancelled = (SELECT SUM(NVL(POD.amount_cancelled, 0))
FROM PO_DISTRIBUTIONS_ALL POD
WHERE POD.line_location_id=POLL.line_location_id)
WHERE poll.line_location_id=l_line_loc_tbl(i);
PO_DEBUG.debug_var(d_module, l_progress, 'Rows Updated in PO Shipments table', SQL%ROWCOUNT);
UPDATE po_lines pol
SET pol.last_update_date = p_action_date,
pol.last_updated_by = l_user_id,
pol.last_update_login = l_login_id,
pol.quantity =
DECODE(pol.quantity,
NULL,
pol.quantity,
(SELECT SUM(NVL(poll.quantity,0) -NVL(poll.quantity_cancelled,0))
FROM po_line_locations POLL
WHERE poll.po_line_id = pol.po_line_id
AND poll.shipment_type IN('STANDARD','PLANNED'))
),
pol.amount =
DECODE(pol.amount,
NULL,
pol.amount,
(SELECT SUM(DECODE(POLL.amount,
NULL,
((NVL(poll.quantity,0) -NVL(poll.quantity_cancelled,0))
* POLL.price_override),
(NVL(poll.amount, 0) -NVL(poll.amount_cancelled,0))
)
)
FROM po_line_locations POLL
WHERE poll.po_line_id = pol.po_line_id
AND poll.shipment_type IN ('STANDARD','PLANNED')) )
WHERE pol.po_line_id IN(SELECT DISTINCT po_line_id
FROM po_line_locations
WHERE line_location_id=l_line_loc_tbl(i)
AND shipment_type IN ('STANDARD','PLANNED'));
PO_DEBUG.debug_var(d_module, l_progress, 'Rows Updated in PO Lines table', SQL%ROWCOUNT);