[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_user_id IN po_lines.last_updated_by%TYPE,
p_login_id IN po_lines.last_update_login%TYPE ,
p_sequence IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2)
IS
d_api_name CONSTANT VARCHAR2(30) := 'qty_rec_grt_ord_chk.';
INSERT INTO PO_ONLINE_REPORT_TEXT
(ONLINE_REPORT_ID,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE,
LINE_NUM,
SHIPMENT_NUM,
DISTRIBUTION_NUM,
SEQUENCE,
TEXT_LINE,
transaction_id,
transaction_type)
(SELECT
p_online_report_id,
p_login_id,
p_user_id,
SYSDATE,
p_user_id,
SYSDATE,
POL.LINE_NUM,
poll.SHIPMENT_NUM,
0,
p_sequence + ROWNUM,
PO_CORE_S.get_translated_text
('PO_CAN_SHIP_REC_GRT_ORD',
'LINE_SHIP_DIST_NUM', l_line_token||pol.LINE_NUM||','||l_ship_token||poll.SHIPMENT_NUM,
'AMT_QTY_TOKEN', DECODE(poll.matching_basis, 'AMOUNT',l_amt_token,l_qty_token),
'QTY1', DECODE(poll.matching_basis, 'AMOUNT', Nvl(poll.amount_received, 0), Nvl(poll.quantity_received, 0)),
'QTY2', DECODE(poll.matching_basis, 'AMOUNT', Nvl(poll.amount, 0), Nvl(poll.quantity, 0)) ,
'DOC_NUM',gt.char6),
gt.num1,
gt.char3
FROM
po_line_locations poll,
po_lines pol ,
po_session_gt gt
WHERE gt.key=p_key
AND poll.line_location_id = gt.index_num1 -- lowestentityid i.e.
AND poll.po_line_id = pol.po_line_id
AND gt.char1 <> po_document_cancel_pvt.c_doc_type_PA
AND gt.char2 <> po_document_cancel_pvt.c_doc_subtype_PLANNED
AND ((POLL.matching_basis ='QUANTITY'
AND round(nvl(POLL.quantity_received,0),5) >0
AND round(nvl(POLL.quantity_received,0),5) > = round(nvl(POLL.quantity,0),5))
OR
(POLL.matching_basis ='AMOUNT'
AND round(nvl(POLL.amount_received,0),5) >0
AND round(nvl(POLL.amount_received,0),5) >= round(nvl(POLL.amount,0),5)))
);
p_user_id IN po_lines.last_updated_by%TYPE,
p_login_id IN po_lines.last_update_login%TYPE ,
p_sequence IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2)
IS
d_api_name CONSTANT VARCHAR2(30) := 'line_price_chk.';
INSERT INTO PO_ONLINE_REPORT_TEXT(
ONLINE_REPORT_ID,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE,
LINE_NUM,
SHIPMENT_NUM,
DISTRIBUTION_NUM,
SEQUENCE,
TEXT_LINE,
transaction_id,
transaction_type)
(SELECT
p_online_report_id,
p_login_id,
p_user_id,
SYSDATE,
p_user_id,
SYSDATE,
POL.LINE_NUM,
0,
0,
p_sequence + ROWNUM,
PO_CORE_S.get_translated_text(
'PO_CAN_PRICE_LIMIT_LT_PRICE',
'LINE_SHIP_DIST_NUM',
l_line_token||pol.LINE_NUM,
'AMT_PRICE_TOKEN',
DECODE(pol.amount, NULL,l_price_token,l_amt_token),
'PRICE1',
DECODE(pol.amount, NULL,pol.unit_price,pol.amount),
'PRICE2',
pol.not_to_exceed_price,
'DOC_NUM',gt.char6),
gt.num1,
gt.char3
FROM
po_lines pol,
po_session_gt gt
WHERE
gt.key = p_key
AND gt.char1 = po_document_cancel_pvt.c_doc_type_PA
AND gt.char2 <>po_document_cancel_pvt.c_doc_subtype_contract
AND pol.po_line_id=gt.index_num1
AND nvl(POL.allow_price_override_flag, 'N') = 'Y'
AND POL.not_to_exceed_price IS NOT NULL
AND ((POL.unit_price IS NOT NULL and POL.not_to_exceed_price < POL.unit_price)
or
(POL.amount IS NOT NULL and POL.not_to_exceed_price < POL.amount)));
p_user_id IN po_lines.last_updated_by%TYPE,
p_login_id IN po_lines.last_update_login%TYPE ,
p_sequence IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2)
IS
d_api_name CONSTANT VARCHAR2(30) := 'line_ship_qty_chk.';
INSERT INTO PO_ONLINE_REPORT_TEXT(
ONLINE_REPORT_ID,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE,
LINE_NUM,
SHIPMENT_NUM,
DISTRIBUTION_NUM,
SEQUENCE,
TEXT_LINE,
transaction_id,
transaction_type)
(SELECT
p_online_report_id,
p_login_id,
p_user_id,
SYSDATE,
p_user_id,
SYSDATE,
POL.LINE_NUM,
0,
0,
p_sequence + ROWNUM,
PO_CORE_S.get_translated_text(
'PO_CAN_PO_LINE_NE_SHIP_AMT',
'LINE_SHIP_DIST_NUM',
l_line_token||pol.LINE_NUM,
'AMT_QTY_TOKEN',
DECODE(pol.amount, NULL,l_qty_token,l_amt_token),
'QTY1',
DECODE(pol.amount, null, pol.quantity,pol.amount),
'QTY2',
DECODE(
pol.amount,
null,
(SELECT Sum(poll.quantity - nvl(poll.quantity_cancelled,0))
FROM po_line_locations poll
WHERE poll.po_line_id = pol.po_line_id ),
(SELECT sum(poll.amount - nvl(poll.amount_cancelled,0))
FROM po_line_locations poll
WHERE poll.po_line_id = pol.po_line_id )),
'DOC_NUM',(SELECT segment1 FROM po_headers WHERE po_header_id=pol.po_header_id)),
(SELECT gt.num1
FROM po_session_gt gt
WHERE gt.KEY=p_key
AND gt.num1 IN
(SELECT pol1.po_line_id
FROM po_lines pol1
WHERE pol1.po_line_id=pol.po_line_id
AND gt.char3=po_document_cancel_pvt.c_entity_level_LINE
UNION ALL
SELECT pol1.po_header_id
FROM po_lines pol1
WHERE pol1.po_line_id=pol.po_line_id
AND gt.char3=po_document_cancel_pvt.c_entity_level_HEADER)),
(SELECT gt.char3
FROM po_session_gt gt
WHERE
gt.KEY=p_key
AND gt.num1 IN
(SELECT pol1.po_line_id
FROM po_lines pol1
WHERE pol1.po_line_id=pol.po_line_id
AND gt.char3=po_document_cancel_pvt.c_entity_level_LINE
UNION ALL
SELECT pol1.po_header_id
FROM po_lines pol1
WHERE pol1.po_line_id=pol.po_line_id
AND gt.char3=po_document_cancel_pvt.c_entity_level_HEADER))
FROM
po_lines pol
WHERE
pol.po_line_id IN
(SELECT DISTINCT po_line_id
FROM po_line_locations,
po_session_gt gt
WHERE gt.KEY=p_key
AND line_location_id= gt.index_num1 -- lowestentityid)
AND gt.char1 = po_document_cancel_pvt.c_doc_type_PO
AND gt.char3<> po_document_cancel_pvt.c_entity_level_SHIPMENT)
AND ((POL.quantity IS NOT null
AND pol.quantity <> (SELECT Sum(poll.quantity - nvl(poll.quantity_cancelled,0))
FROM po_line_locations poll
WHERE poll.po_line_id = pol.po_line_id))
OR
(POL.amount IS NOT null
AND pol.amount <> (SELECT sum(poll.amount- nvl(poll.amount_cancelled,0))
FROM po_line_locations poll
WHERE poll.po_line_id = pol.po_line_id )))
);
p_user_id IN po_lines.last_updated_by%TYPE,
p_login_id IN po_lines.last_update_login%TYPE ,
p_sequence IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2)
IS
d_api_name CONSTANT VARCHAR2(30) := 'line_ship_price_chk';
INSERT INTO PO_ONLINE_REPORT_TEXT(
ONLINE_REPORT_ID,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE,
LINE_NUM,
SHIPMENT_NUM,
DISTRIBUTION_NUM,
SEQUENCE,
TEXT_LINE,
transaction_id,
transaction_type)
(SELECT
p_online_report_id,
p_login_id,
p_user_id,
SYSDATE,
p_user_id,
SYSDATE,
POL.LINE_NUM,
poll.SHIPMENT_NUM,
0,
p_sequence + ROWNUM,
PO_CORE_S.get_translated_text(
'PO_CAN_SHIP_PRICE_NE_LINE',
'LINE_SHIP_DIST_NUM',
l_line_token||pol.LINE_NUM||','||l_ship_token||poll.SHIPMENT_NUM,
'PRICE1',
pol.unit_price,
'PRICE2',
poll.price_override,
'DOC_NUM',
gt.char6),
gt.num1,
gt.char3
FROM
po_line_locations poll,
po_lines pol ,
po_session_gt gt
WHERE
gt.key=p_key
AND poll.line_location_id = gt.index_num1 -- lowestentityid i.e.
AND poll.po_line_id = pol.po_line_id
AND gt.char1 <> po_document_cancel_pvt.c_doc_type_PA
AND pol.unit_price <> poll.price_override
AND poll.shipment_type in ('STANDARD','PLANNED')
AND po_control_action_validations.is_complex_work_po(poll.po_header_id) ='N'
);
p_user_id IN po_lines.last_updated_by%TYPE,
p_login_id IN po_lines.last_update_login%TYPE ,
p_sequence IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2)
IS
d_api_name CONSTANT VARCHAR2(30) := 'ship_dist_qty_chk.';
INSERT INTO PO_ONLINE_REPORT_TEXT(
ONLINE_REPORT_ID,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE,
LINE_NUM,
SHIPMENT_NUM,
DISTRIBUTION_NUM,
SEQUENCE,
TEXT_LINE,
transaction_id,
transaction_type)
(SELECT
p_online_report_id,
p_login_id,
p_user_id,
SYSDATE,
p_user_id,
SYSDATE,
POL.LINE_NUM,
poll.SHIPMENT_NUM,
0,
p_sequence + ROWNUM,
PO_CORE_S.get_translated_text(
'PO_CAN_PO_SHIP_NE_DIST_AMT',
'LINE_SHIP_DIST_NUM',
l_line_token||pol.LINE_NUM||','||l_ship_token||poll.SHIPMENT_NUM,
'AMT_QTY_TOKEN',
DECODE(poll.matching_basis, 'AMOUNT',l_amt_token,l_qty_token),
'QTY1',
DECODE(poll.matching_basis, 'AMOUNT', Nvl(poll.amount, 0), Nvl(poll.quantity, 0)),
'QTY2',
DECODE(
poll.matching_basis,
'AMOUNT',
(SELECT sum(POD2.amount_ordered)
FROM PO_DISTRIBUTIONS_ALL POD2
WHERE POD2.line_location_id = poll.line_location_id ),
(SELECT sum(POD2.quantity_ordered)
FROM PO_DISTRIBUTIONS_ALL POD2
WHERE POD2.line_location_id = poll.line_location_id )),
'DOC_NUM',
gt.char6
),
gt.num1,
gt.char3
FROM
po_line_locations poll,
po_lines pol ,
po_session_gt gt
WHERE
gt.key=p_key
AND poll.line_location_id = gt.index_num1 -- lowestentityid i.e.
AND poll.po_line_id = pol.po_line_id
AND gt.char1 <> po_document_cancel_pvt.c_doc_type_PA
AND ((POLL.quantity IS NOT null
AND POLL.quantity <> (SELECT sum(POD2.quantity_ordered)
FROM PO_DISTRIBUTIONS_ALL POD2
WHERE POD2.line_location_id = poll.line_location_id ))
OR
(POLL.amount IS NOT null
AND POLL.amount <> (SELECT sum(POD2.amount_ordered)
FROM PO_DISTRIBUTIONS_ALL POD2
WHERE POD2.line_location_id = poll.line_location_id )))
);
p_user_id IN po_lines.last_updated_by%TYPE,
p_login_id IN po_lines.last_update_login%TYPE ,
p_sequence IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2)
IS
d_api_name CONSTANT VARCHAR2(30) := 'lcm_match_option_chk.';
INSERT INTO PO_ONLINE_REPORT_TEXT(
ONLINE_REPORT_ID,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE,
LINE_NUM,
SHIPMENT_NUM,
DISTRIBUTION_NUM,
SEQUENCE,
TEXT_LINE,
transaction_id,
transaction_type)
(SELECT
p_online_report_id,
p_login_id,
p_user_id,
SYSDATE,
p_user_id,
SYSDATE,
POL.LINE_NUM,
poll.SHIPMENT_NUM,
0,
p_sequence + ROWNUM,
PO_CORE_S.get_translated_text(
'PO_CAN_SHIP_INV_MATCH_NE_R',
'LINE_SHIP_DIST_NUM',
l_line_token||pol.LINE_NUM||','||l_ship_token||poll.SHIPMENT_NUM,
'DOC_NUM',
gt.char6
),
gt.num1,
gt.char3
FROM
po_line_locations poll,
po_lines pol ,
po_session_gt gt
WHERE
gt.key=p_key
AND poll.line_location_id = gt.index_num1 -- i.e lowestentityid .
AND poll.po_line_id = pol.po_line_id
AND gt.char1 <> po_document_cancel_pvt.c_doc_type_PA
AND Nvl(poll.LCM_FLAG,'N') = 'Y'
AND Nvl(poll.match_option,'P') <> 'R');
p_user_id IN po_lines.last_updated_by%TYPE,
p_login_id IN po_lines.last_update_login%TYPE ,
p_sequence IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2)
IS
d_api_name CONSTANT VARCHAR2(30) := 'lcm_dest_type_chk.';
INSERT INTO PO_ONLINE_REPORT_TEXT(
ONLINE_REPORT_ID,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE,
LINE_NUM,
SHIPMENT_NUM,
DISTRIBUTION_NUM,
SEQUENCE,
TEXT_LINE,
transaction_id,
transaction_type)
(SELECT
p_online_report_id,
p_login_id,
p_user_id,
SYSDATE,
p_user_id,
SYSDATE,
POL.LINE_NUM,
poll.SHIPMENT_NUM,
pod.distribution_num,
p_sequence + ROWNUM,
PO_CORE_S.get_translated_text(
'PO_CAN_DIST_DEST_TYPE_NE_I',
'LINE_SHIP_DIST_NUM',
l_line_token||pol.LINE_NUM||','||l_ship_token||poll.SHIPMENT_NUM||','||l_dist_token||pod.distribution_num,
'DOC_NUM',
gt.char6
),
gt.num1,
gt.char3
FROM
po_distributions_all pod,
po_line_locations poll,
po_lines pol ,
po_session_gt gt
WHERE
gt.key=p_key
AND poll.line_location_id = gt.index_num1 -- i.e lowestentityid .
AND poll.po_line_id = pol.po_line_id
AND poll.line_location_id=pod.line_location_id
AND gt.char1 <> po_document_cancel_pvt.c_doc_type_PA
AND Nvl(poll.LCM_FLAG,'N') = 'Y'
AND pod.DESTINATION_TYPE_CODE <> 'INVENTORY');
p_user_id IN po_lines.last_updated_by%TYPE,
p_login_id IN po_lines.last_update_login%TYPE ,
p_sequence IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2)
IS
d_api_name CONSTANT VARCHAR2(30) := 'qty_del_grt_ord_chk.';
INSERT INTO PO_ONLINE_REPORT_TEXT(
ONLINE_REPORT_ID,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE,
LINE_NUM,
SHIPMENT_NUM,
DISTRIBUTION_NUM,
SEQUENCE,
TEXT_LINE,
transaction_id,
transaction_type)
(SELECT p_online_report_id,
p_login_id,
p_user_id,
SYSDATE,
p_user_id,
SYSDATE,
POL.LINE_NUM,
poll.SHIPMENT_NUM,
pod.DISTRIBUTION_NUM,
p_sequence + ROWNUM,
PO_CORE_S.get_translated_text
('PO_CAN_DIST_DEL_GRT_ORD'
, 'LINE_SHIP_DIST_NUM', l_line_token||pol.LINE_NUM||','||l_ship_token||poll.SHIPMENT_NUM||','||l_dist_token||pod.DISTRIBUTION_NUM
, 'AMT_QTY_TOKEN', DECODE(poll.matching_basis, 'AMOUNT',l_amt_token,l_qty_token)
, 'QTY1', DECODE(poll.matching_basis, 'AMOUNT',Round(Nvl(pod.amount_delivered, 0), 5), Round(Nvl(pod.quantity_delivered, 0), 5))
, 'QTY2', DECODE(poll.matching_basis, 'AMOUNT',Round(Nvl(pod.amount_ordered, 0), 5), Round(Nvl(pod.quantity_ordered, 0), 5))
, 'DOC_NUM', gt.char6
),
gt.num1,
gt.char3
FROM
po_distributions pod,
po_line_locations poll,
po_lines pol,
po_session_gt gt
WHERE
gt.key=p_key
AND pod.line_location_id = gt.index_num1
AND pod.line_location_id = poll.line_location_id
AND pol.po_line_id = poll.po_line_id
AND gt.char1 <> po_document_cancel_pvt.c_doc_type_PA
AND gt.char2 <> po_document_cancel_pvt.c_doc_subtype_PLANNED
AND ((POLL.matching_basis ='QUANTITY'
AND nvl(pod.quantity_delivered,0)> 0
AND round(nvl(pod.quantity_delivered,0),5) > round(nvl(pod.quantity_ordered,0),5)) --Bug15869000
OR
(POLL.matching_basis ='AMOUNT'
AND nvl(pod.amount_delivered,0)> 0
AND round(nvl(pod.amount_delivered,0),5) > round(nvl(pod.amount_ordered,0),5))) ); --Bug15869000
p_user_id IN po_lines.last_updated_by%TYPE,
p_login_id IN po_lines.last_update_login%TYPE ,
p_sequence IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2)
IS
d_api_name CONSTANT VARCHAR2(30) := 'qty_bill_grt_ord_chk.';
INSERT INTO PO_ONLINE_REPORT_TEXT(
ONLINE_REPORT_ID,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE,
LINE_NUM,
SHIPMENT_NUM,
DISTRIBUTION_NUM,
SEQUENCE,
TEXT_LINE,
transaction_id,
transaction_type)
(SELECT p_online_report_id,
p_login_id,
p_user_id,
SYSDATE,
p_user_id,
SYSDATE,
POL.LINE_NUM,
poll.SHIPMENT_NUM,
pod.DISTRIBUTION_NUM,
p_sequence + ROWNUM,
PO_CORE_S.get_translated_text
('PO_CAN_DIST_BILL_GRT_ORD'
, 'LINE_SHIP_DIST_NUM', l_line_token||pol.LINE_NUM||','||l_ship_token||poll.SHIPMENT_NUM||','||l_dist_token||pod.DISTRIBUTION_NUM
, 'AMT_QTY_TOKEN', DECODE(poll.matching_basis, 'AMOUNT',l_amt_token,l_qty_token)
, 'QTY1', DECODE(poll.matching_basis, 'AMOUNT',
Round(Nvl(DECODE(POD.distribution_type,
'PREPAYMENT', POD.amount_financed,
POD.amount_billed), 0), 5),
Round(Nvl(DECODE(POD.distribution_type,
'PREPAYMENT',
POD.quantity_financed,
POD.quantity_billed), 0), 5))
, 'QTY2', DECODE(poll.matching_basis, 'AMOUNT',
Round(Nvl(pod.amount_ordered, 0), 5),
Round(Nvl(pod.quantity_ordered, 0), 5))
, 'DOC_NUM', gt.char6
),
gt.num1,
gt.char3
FROM
po_distributions pod,
po_line_locations poll,
po_lines pol,
po_session_gt gt
WHERE
gt.key=p_key
AND pod.line_location_id = gt.index_num1
AND pod.line_location_id = poll.line_location_id
AND pol.po_line_id = poll.po_line_id
AND gt.char1 <> po_document_cancel_pvt.c_doc_type_PA
AND gt.char2 <> po_document_cancel_pvt.c_doc_subtype_PLANNED
AND ((POLL.matching_basis ='QUANTITY'
AND nvl(DECODE(POD.distribution_type,
'PREPAYMENT',
POD.quantity_financed,
POD.quantity_billed
),
0) >0
AND Round(nvl(DECODE(POD.distribution_type,
'PREPAYMENT',
POD.quantity_financed,
POD.quantity_billed
),
0),
5) > round(nvl(pod.quantity_ordered,0),5)) --Bug15869000
OR
(POLL.matching_basis ='AMOUNT'
AND nvl(DECODE(POD.distribution_type,
'PREPAYMENT',
POD.amount_financed,
POD.amount_billed
),
0) >0
AND Round(nvl(DECODE(POD.distribution_type,
'PREPAYMENT',
POD.amount_financed,
POD.amount_billed
),
0),
5) > round(nvl(pod.amount_ordered,0),5)))); --Bug15869000
p_user_id IN po_lines.last_updated_by%TYPE,
p_login_id IN po_lines.last_update_login%TYPE ,
p_sequence IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2)
IS
d_api_name CONSTANT VARCHAR2(30) := 'qty_bill_grt_rec_chk.';
INSERT INTO PO_ONLINE_REPORT_TEXT(
ONLINE_REPORT_ID,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE,
LINE_NUM,
SHIPMENT_NUM,
DISTRIBUTION_NUM,
SEQUENCE,
TEXT_LINE,
transaction_id,
transaction_type)
(SELECT
p_online_report_id,
p_login_id,
p_user_id,
SYSDATE,
p_user_id,
SYSDATE,
POL.LINE_NUM,
poll.SHIPMENT_NUM,
0,
p_sequence + ROWNUM,
PO_CORE_S.get_translated_text
('PO_CAN_SHIP_BILL_GRT_REC'
, 'LINE_SHIP_DIST_NUM', l_line_token||pol.LINE_NUM||','||l_ship_token||poll.SHIPMENT_NUM
, 'AMT_QTY_TOKEN', DECODE(poll.matching_basis, 'AMOUNT',l_amt_token,l_qty_token)
, 'QTY1', DECODE(poll.matching_basis, 'AMOUNT',
Round(Nvl(DECODE(POLL.shipment_type, 'PREPAYMENT',
poll.amount_financed, poll.amount_billed), 0), 5),
Round(Nvl(DECODE(POLL.shipment_type, 'PREPAYMENT',
poll.quantity_financed, poll.quantity_billed), 0), 5))
, 'QTY2', DECODE(poll.matching_basis, 'AMOUNT',
Round(Nvl(poll.amount_received, 0), 5),
Round(Nvl(poll.quantity_received, 0), 5))
, 'DOC_NUM', gt.char6
),
gt.num1,
gt.char3
FROM
po_line_locations poll,
po_lines pol,
po_session_gt gt
WHERE gt.key=p_key
AND poll.line_location_id = gt.index_num1
AND pol.po_line_id = poll.po_line_id
AND nvl(POLL.receipt_required_flag, 'Y')<> 'N'
AND gt.char1 <> po_document_cancel_pvt.c_doc_type_PA
AND gt.char2 <> po_document_cancel_pvt.c_doc_subtype_PLANNED
AND ((POLL.matching_basis ='QUANTITY'
AND nvl(DECODE(poll.shipment_type,
'PREPAYMENT',
poll.quantity_financed,
poll.quantity_billed),
0)>0
AND Round(nvl(DECODE(poll.shipment_type,
'PREPAYMENT',
poll.quantity_financed,
poll.quantity_billed),
0),
5) > round(nvl(poll.quantity_received,0),5)) --bug#15971932
OR
(POLL.matching_basis ='AMOUNT'
AND nvl(DECODE(poll.shipment_type,
'PREPAYMENT',
poll.amount_financed,
poll.amount_billed),
0)>0
AND Round(nvl(DECODE(poll.shipment_type,
'PREPAYMENT',
poll.amount_financed,
poll.amount_billed),
0),
5) > round(nvl(poll.amount_received,0),5)))); --bug#15971932
p_user_id IN po_lines.last_updated_by%TYPE,
p_login_id IN po_lines.last_update_login%TYPE ,
p_sequence IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2)
IS
d_api_name CONSTANT VARCHAR2(30) := 'qty_bill_grt_del_chk.';
INSERT INTO PO_ONLINE_REPORT_TEXT(
ONLINE_REPORT_ID,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE,
LINE_NUM,
SHIPMENT_NUM,
DISTRIBUTION_NUM,
SEQUENCE,
TEXT_LINE,
transaction_id,
transaction_type)
(SELECT p_online_report_id,
p_login_id,
p_user_id,
SYSDATE,
p_user_id,
SYSDATE,
POL.LINE_NUM,
poll.SHIPMENT_NUM,
pod.DISTRIBUTION_NUM,
p_sequence + ROWNUM,
PO_CORE_S.get_translated_text
('PO_CAN_DIST_BILL_GRT_DEL'
,'LINE_SHIP_DIST_NUM',l_line_token||pol.LINE_NUM||','||l_ship_token||poll.SHIPMENT_NUM||','||l_dist_token||pod.DISTRIBUTION_NUM
,'AMT_QTY_TOKEN',DECODE(poll.matching_basis, 'AMOUNT',l_amt_token,l_qty_token)
,'QTY1',DECODE(poll.matching_basis, 'AMOUNT',
Round(Nvl(DECODE(POD.distribution_type, 'PREPAYMENT', POD.amount_financed, POD.amount_billed), 0), 5),
Round(Nvl(DECODE(POD.distribution_type, 'PREPAYMENT', POD.quantity_financed, POD.quantity_billed), 0), 5))
, 'QTY2',DECODE(poll.matching_basis, 'AMOUNT',Round(Nvl(pod.amount_delivered, 0), 5), Round(Nvl(pod.quantity_delivered, 0), 5))
, 'DOC_NUM', gt.char6
),
gt.num1,
gt.char3
FROM
po_distributions pod,
po_line_locations poll,
po_lines pol,
po_session_gt gt
WHERE
gt.key=p_key
AND pod.line_location_id = gt.index_num1
AND pod.line_location_id = poll.line_location_id
AND pol.po_line_id = poll.po_line_id
AND nvl(POLL.receipt_required_flag, 'Y')<> 'N'
AND gt.char1 <> po_document_cancel_pvt.c_doc_type_PA
AND gt.char2 <> po_document_cancel_pvt.c_doc_subtype_PLANNED
AND ((POLL.matching_basis ='QUANTITY'
AND nvl(DECODE(pod.distribution_type,
'PREPAYMENT',
pod.quantity_financed,
pod.quantity_billed)
,0) >0
AND Round(nvl(DECODE(pod.distribution_type,
'PREPAYMENT',
pod.quantity_financed,
pod.quantity_billed)
,0)
,5)> round(nvl(pod.quantity_delivered,0),5))
OR
(POLL.matching_basis ='AMOUNT'
AND nvl(DECODE(pod.distribution_type,
'PREPAYMENT',
pod.quantity_financed,
pod.quantity_billed)
,0) >0
AND Round(nvl(DECODE(pod.distribution_type,
'PREPAYMENT',
pod.amount_financed,
pod.amount_billed)
,0)
, 5) > round(nvl(pod.amount_delivered,0),5))) );
p_user_id IN po_lines.last_updated_by%TYPE,
p_login_id IN po_lines.last_update_login%TYPE ,
p_sequence IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2)
IS
d_api_name CONSTANT VARCHAR2(30) := 'qty_rec_not_del_chk.';
INSERT INTO po_online_report_text(
ONLINE_REPORT_ID,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE,
LINE_NUM,
SHIPMENT_NUM,
DISTRIBUTION_NUM,
SEQUENCE,
TEXT_LINE,
transaction_id,
transaction_type)
(SELECT
p_online_report_id,
p_login_id,
p_user_id,
SYSDATE,
p_user_id,
SYSDATE,
POL.LINE_NUM,
poll.SHIPMENT_NUM,
null,
p_sequence + ROWNUM,
PO_CORE_S.get_translated_text(
'PO_CAN_SHIP_REC_NOT_DEL'
, 'LINE_SHIP_DIST_NUM', l_line_token||pol.LINE_NUM||','||l_ship_token||poll.SHIPMENT_NUM
, 'AMT_QTY_TOKEN', DECODE(poll.matching_basis, 'AMOUNT',l_amt_token,l_qty_token)
, 'QTY1', DECODE(poll.matching_basis, 'AMOUNT',
Nvl(poll.amount_received, 0),
Nvl(poll.quantity_received, 0))
, 'QTY2', (SELECT Sum(Decode(poll.matching_basis, 'AMOUNT',
Nvl(pod.amount_delivered, 0),
Nvl(pod.quantity_delivered, 0)))
FROM po_distributions_all pod
WHERE pod.line_location_id=poll.line_location_id)
, 'DOC_NUM', gt.char6
),
gt.num1,
gt.char3
FROM
po_line_locations poll,
po_lines pol,
po_session_gt gt
WHERE gt.KEY =p_key
AND poll.line_location_id =gt.index_num1
AND pol.po_line_id = poll.po_line_id
AND gt.char1 <> po_document_cancel_pvt.c_doc_type_PA
AND gt.char2 <> po_document_cancel_pvt.c_doc_subtype_PLANNED
AND ((poll.matching_basis = 'AMOUNT'
AND nvl(poll.amount_received, 0) <>(SELECT Sum(Nvl(amount_delivered, 0))
FROM po_distributions_all
WHERE line_location_id=poll.line_location_id))
OR (poll.matching_basis <>'AMOUNT'
AND Nvl(poll.quantity_received, 0) <> (SELECT Sum(Nvl(quantity_delivered, 0))
FROM po_distributions_all
WHERE line_location_id=poll.line_location_id))));
p_user_id IN po_lines.last_updated_by%TYPE,
p_login_id IN po_lines.last_update_login%TYPE ,
p_sequence IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2)
IS
d_api_name CONSTANT VARCHAR2(30) := 'pending_rcv_trx_chk.';
INSERT INTO po_online_report_text(
ONLINE_REPORT_ID,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE,
LINE_NUM,
SHIPMENT_NUM,
DISTRIBUTION_NUM,
SEQUENCE,
TEXT_LINE,
transaction_id,
transaction_type)
(SELECT
p_online_report_id,
p_login_id,
p_user_id,
SYSDATE,
p_user_id,
SYSDATE,
POL.LINE_NUM,
poll.SHIPMENT_NUM,
0,
p_sequence + ROWNUM,
PO_CORE_S.get_translated_text
('PO_CAN_SHIP_WITH_RCV_TRX'
, 'LINE_NUM', pol.LINE_NUM
, 'SHIP_NUM', poll.SHIPMENT_NUM
, 'DOC_NUM', gt.char6
),
gt.num1,
gt.char3
FROM
po_line_locations poll,
po_lines pol,
po_session_gt gt
WHERE
gt.key=p_key
AND poll.line_location_id =gt.index_num1
AND pol.po_line_id = poll.po_line_id
AND gt.char1 <> po_document_cancel_pvt.c_doc_type_PA
AND gt.char2 <> po_document_cancel_pvt.c_doc_subtype_PLANNED
AND EXISTS
(SELECT 'Pending Transaction'
FROM RCV_TRANSACTIONS_INTERFACE RTI
WHERE RTI.processing_status_code = 'PENDING'
AND RTI.po_line_location_id =poll.line_location_id));
p_user_id IN po_lines.last_updated_by%TYPE,
p_login_id IN po_lines.last_update_login%TYPE ,
p_sequence IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2)
IS
d_api_name CONSTANT VARCHAR2(30) := 'pending_asn_chk.';
INSERT INTO po_online_report_text(
ONLINE_REPORT_ID,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE,
LINE_NUM,
SHIPMENT_NUM,
DISTRIBUTION_NUM,
SEQUENCE,
TEXT_LINE,
transaction_id,
transaction_type)
(SELECT p_online_report_id,
p_login_id,
p_user_id,
SYSDATE,
p_user_id,
SYSDATE,
POL.LINE_NUM,
poll.SHIPMENT_NUM,
0,
p_sequence + ROWNUM,
PO_CORE_S.get_translated_text
('PO_CAN_SHIP_WITH_ASN'
, 'LINE_NUM', pol.LINE_NUM
, 'SHIP_NUM', poll.SHIPMENT_NUM
, 'DOC_NUM', gt.char6
),
gt.num1,
gt.char3
FROM
po_line_locations poll,
po_lines pol,
po_session_gt gt
WHERE gt.key=p_key
AND poll.line_location_id =gt.index_num1
AND pol.po_line_id = poll.po_line_id
AND gt.char1 <> po_document_cancel_pvt.c_doc_type_PA
AND gt.char2 <> po_document_cancel_pvt.c_doc_subtype_PLANNED
AND POLL.payment_type IS NULL
AND EXISTS
(SELECT 'ASN outstanding'
FROM RCV_SHIPMENT_LINES RSL
WHERE RSL.po_line_location_id = poll.line_location_id
AND NVL(RSL.quantity_shipped,0) > NVL(RSL.quantity_received,0)
AND NVL(RSL.ASN_LINE_FLAG,'N') = 'Y'
AND NVL(RSL.SHIPMENT_LINE_STATUS_CODE,'EXPECTED') <> 'CANCELLED'));
p_user_id IN po_lines.last_updated_by%TYPE,
p_login_id IN po_lines.last_update_login%TYPE ,
p_sequence IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2)
IS
d_api_name CONSTANT VARCHAR2(30) := 'invalid_budget_acct_chk.';
--unnecessarily inserting values. Removed the to_date wrap around p_action_date.
INSERT INTO po_online_report_text(
ONLINE_REPORT_ID,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE,
LINE_NUM,
SHIPMENT_NUM,
DISTRIBUTION_NUM,
SEQUENCE,
TEXT_LINE,
transaction_id,
transaction_type)
(SELECT
p_online_report_id,
p_login_id,
p_user_id,
SYSDATE,
p_user_id,
SYSDATE,
POL.LINE_NUM,
poll.SHIPMENT_NUM,
pod.distribution_num,
p_sequence + ROWNUM,
PO_CORE_S.get_translated_text
('PO_CAN_DIST_INV_BUDGET_ACCT'
, 'LINE_NUM', pol.LINE_NUM
, 'SHIP_NUM', poll.SHIPMENT_NUM
, 'DIST_NUM', pod.distribution_num
, 'DOC_NUM', gt.char6
),
gt.num1,
gt.char3
FROM
po_distributions pod,
po_line_locations poll,
po_lines pol,
po_session_gt gt,
FINANCIALS_SYSTEM_PARAMETERS FSP,
gl_code_combinations gcc
WHERE gt.key=p_key
AND pod.line_location_id=gt.index_num1
AND POD.line_location_id = POLL.line_location_id
AND POL.po_line_id = POLL.po_line_id
AND POLL.shipment_type in ('STANDARD', 'PLANNED','PREPAYMENT')
AND GCC.code_combination_id = POD.BUDGET_ACCOUNT_ID
AND gt.char1 <> po_document_cancel_pvt.c_doc_type_PA
AND fsp.purch_encumbrance_flag = 'Y'
AND (GCC.enabled_flag <> 'Y' OR
nvl(p_action_date,trunc(sysdate)) not between
nvl(GCC.start_date_active, nvl(p_action_date,trunc(sysdate)-1))
AND NVL(GCC.end_date_active, nvl(p_action_date,trunc(sysdate)+1))));
p_user_id IN po_lines.last_updated_by%TYPE,
p_login_id IN po_lines.last_update_login%TYPE ,
p_sequence IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2)
IS
d_api_name CONSTANT VARCHAR2(30) := 'invalid_charge_acct_chk.';
--unnecessarily inserting values. Removed the to_date wrap around p_action_date.
INSERT INTO po_online_report_text(
ONLINE_REPORT_ID,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE,
LINE_NUM,
SHIPMENT_NUM,
DISTRIBUTION_NUM,
SEQUENCE,
TEXT_LINE,
transaction_id,
transaction_type)
(SELECT
p_online_report_id,
p_login_id,
p_user_id,
SYSDATE,
p_user_id,
SYSDATE,
POL.LINE_NUM,
poll.SHIPMENT_NUM,
pod.distribution_num,
p_sequence + ROWNUM,
PO_CORE_S.get_translated_text
('PO_CAN_DIST_INV_CHARGE_ACCT'
, 'LINE_NUM', pol.LINE_NUM
, 'SHIP_NUM', poll.SHIPMENT_NUM
, 'DIST_NUM', pod.distribution_num
, 'DOC_NUM', gt.char6
),
gt.num1,
gt.char3
FROM
po_distributions pod,
po_line_locations poll,
po_lines pol,
gl_code_combinations gcc,
po_session_gt gt
WHERE gt.key=p_key
AND pod.line_location_id=gt.index_num1
AND POD.line_location_id = POLL.line_location_id
AND POL.po_line_id = POLL.po_line_id
AND GCC.code_combination_id = POD.code_combination_id
AND gt.char1 <> po_document_cancel_pvt.c_doc_type_PA
AND (Nvl(GCC.enabled_flag,'N') <> 'Y' OR
nvl(p_action_date,trunc(sysdate)) not between
nvl(GCC.start_date_active, nvl(p_action_date,trunc(sysdate)-1))
AND NVL(GCC.end_date_active, nvl(p_action_date,trunc(sysdate)+1))));
p_user_id IN po_lines.last_updated_by%TYPE,
p_login_id IN po_lines.last_update_login%TYPE ,
p_sequence IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2)
IS
d_api_name CONSTANT VARCHAR2(30) := 'ga_with_open_std_ref_chk';
INSERT INTO po_online_report_text(
ONLINE_REPORT_ID,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE,
LINE_NUM,
SHIPMENT_NUM,
DISTRIBUTION_NUM,
SEQUENCE,
TEXT_LINE,
transaction_id,
transaction_type)
(SELECT
p_online_report_id,
p_login_id,
p_user_id,
SYSDATE,
p_user_id,
SYSDATE,
POL.LINE_NUM,
0,
0,
p_sequence + ROWNUM,
PO_CORE_S.get_translated_text
('PO_CAN_GA_WITH_OPEN_STD_REF'
, 'LINE_NUM', pol.LINE_NUM
, 'DOC_NUM', gt.char6),
gt.num1,
gt.char3
FROM
po_lines pol,
po_headers poh,
po_session_gt gt
WHERE gt.key=p_key
AND poh.po_header_id=pol.po_header_id
AND pol.po_line_id =gt.index_num1
AND gt.char1 = po_document_cancel_pvt.c_doc_type_PA
AND poh.global_agreement_flag = 'Y'
AND EXISTS
(SELECT 'Uncancelled std PO lines ref this ga line Exist'
FROM po_lines POL1
WHERE POL1.from_line_id = POL.po_line_id
AND nvl(POL1.cancel_flag,'N') = 'N'
AND nvl(POL1.closed_code, 'OPEN') <> 'FINALLY CLOSED'));
p_user_id IN po_lines.last_updated_by%TYPE,
p_login_id IN po_lines.last_update_login%TYPE ,
p_sequence IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2)
IS
d_api_name CONSTANT VARCHAR2(30) := 'po_pa_WITH_OPEN_REL_chk.';
INSERT INTO po_online_report_text(
ONLINE_REPORT_ID,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE,
LINE_NUM,
SHIPMENT_NUM,
DISTRIBUTION_NUM,
SEQUENCE,
TEXT_LINE,
transaction_id,
transaction_type)
(SELECT
p_online_report_id,
p_login_id,
p_user_id,
SYSDATE,
p_user_id,
SYSDATE,
POL.LINE_NUM,
0,
0,
p_sequence + ROWNUM,
PO_CORE_S.get_translated_text
('PO_CAN_PA_WITH_OPEN_REL'
, 'LINE_NUM', pol.LINE_NUM
, 'DOC_NUM',gt.char6),
gt.num1,
gt.char3
FROM po_lines pol,
po_session_gt gt
WHERE gt.key=p_key
AND pol.po_line_id =gt.index_num1
-- bug 16174863
AND gt.char1 = po_document_cancel_pvt.c_doc_type_PA
AND EXISTS
(SELECT 'Uncancelled Releases Exist'
FROM PO_LINE_LOCATIONS PLL
WHERE PLL.po_line_id = POL.po_line_id
AND PLL.shipment_type in ('BLANKET')
AND nvl(PLL.cancel_flag,'N') = 'N'
AND nvl(PLL.closed_code, 'OPEN') <> 'FINALLY CLOSED'
UNION
SELECT 'Uncancelled Releases Exist'
FROM po_releases por
WHERE POR.po_header_id = pol.po_header_id
-- bug 16590732: Do this check if it is Header level cancel
AND gt.char3 = po_document_cancel_pvt.c_entity_level_header
AND nvl(POR.cancel_flag,'N') = 'N'
AND nvl(POR.closed_code,'OPEN') <> 'FINALLY CLOSED')
);
INSERT INTO po_online_report_text(
ONLINE_REPORT_ID,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE,
LINE_NUM,
SHIPMENT_NUM,
DISTRIBUTION_NUM,
SEQUENCE,
TEXT_LINE,
transaction_id,
transaction_type)
(SELECT
p_online_report_id,
p_login_id,
p_user_id,
SYSDATE,
p_user_id,
SYSDATE,
POL.LINE_NUM,
POLL.SHIPMENT_NUM,
0,
p_sequence + ROWNUM,
-- bug 16525950 : Constructing the token value to display shipment number also
PO_CORE_S.get_translated_text
('PO_CAN_PA_WITH_OPEN_REL'
, 'LINE_NUM', pol.LINE_NUM || l_ship_token || poll.SHIPMENT_NUM
, 'DOC_NUM',gt.char6),
gt.num1,
gt.char3
FROM
po_lines pol,
po_line_locations poll,
po_session_gt gt
WHERE gt.key=p_key
AND poll.LINE_LOCATION_ID =gt.index_num1
AND pol.po_line_id =poll.po_line_id
AND gt.char2 = po_document_cancel_pvt.c_doc_subtype_PLANNED
AND EXISTS(
SELECT 'Uncancelled Releases Exist'
FROM PO_LINE_LOCATIONS PLL
WHERE
PLL.SOURCE_SHIPMENT_ID = POLL.LINE_LOCATION_ID
AND PLL.shipment_type in ('SCHEDULED')
AND nvl(PLL.cancel_flag,'N') = 'N'
AND nvl(PLL.closed_code, 'OPEN') <> 'FINALLY CLOSED'
UNION
SELECT 'Uncancelled Releases Exist'
FROM po_releases por
WHERE POR.po_header_id = pol.po_header_id
-- bug 16590732: Do this check if it is Header level cancel
AND gt.char3 = po_document_cancel_pvt.c_entity_level_header
AND nvl(POR.cancel_flag,'N') = 'N'
AND nvl(POR.closed_code,'OPEN') <> 'FINALLY CLOSED')
);
p_user_id IN po_lines.last_updated_by%TYPE,
p_login_id IN po_lines.last_update_login%TYPE ,
p_sequence IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2)
IS
d_api_name CONSTANT VARCHAR2(30) := 'cga_with_open_std_ref_chk.';
INSERT INTO po_online_report_text(
ONLINE_REPORT_ID,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE,
LINE_NUM,
SHIPMENT_NUM,
DISTRIBUTION_NUM,
SEQUENCE,
TEXT_LINE,
transaction_id,
transaction_type)
(SELECT p_online_report_id,
p_login_id,
p_user_id,
SYSDATE,
p_user_id,
SYSDATE,
gt.num1,
0,
0,
p_sequence + ROWNUM,
PO_CORE_S.get_translated_text
('PO_CAN_CGA_WITH_OPEN_STD_REF'
, 'DOC_NUM',gt.char6),
gt.num1,
gt.char3
FROM
po_session_gt gt
WHERE gt.key=p_key
AND gt.char1 = po_document_cancel_pvt.c_doc_type_PA
AND EXISTS
(SELECT 'Has open std Po lines ref this contract'
FROM po_lines POL
WHERE POL.contract_id = gt.index_num1
AND NVL(POL.cancel_flag, 'N') = 'N'
AND NVL(POL.closed_code, 'OPEN') <> 'FINALLY CLOSED') );
p_user_id IN po_lines.last_updated_by%TYPE,
p_login_id IN po_lines.last_update_login%TYPE ,
p_sequence IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2)
IS
d_api_name CONSTANT VARCHAR2(30) := 'cancel_custom_validation.';
PROCEDURE update_gt_with_low_entity(
p_entity_rec_tbl IN po_document_action_pvt.entity_dtl_rec_type_tbl,
p_key IN OUT NOCOPY po_session_gt.key%TYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2)
IS
d_api_name CONSTANT VARCHAR2(30) := 'update_gt_with_low_entity.';
INSERT INTO PO_session_gt gt(
key,
index_num1,
num1,
char1,
char2,
char3,
char4,
char5,
char6,
date1)
(SELECT
l_new_key,
line_location_id,
num1,
char1,
char2,
char3,
char4,
'Y',
Decode (
pgt.char1,
po_document_cancel_pvt.c_doc_type_RELEASE,
(SELECT poh.segment1||'-'|| por.release_num
FROM po_releases por,
po_headers poh
WHERE por.po_release_id=poll.po_release_id
AND por.po_header_id=poh.po_header_id),
(SELECT segment1
FROM po_headers
WHERE po_header_id=poll.po_header_id)
),
date1
FROM po_line_locations poll,
po_session_gt pgt
WHERE pgt.key = l_old_key
AND nvl(poll.cancel_flag,'N') = 'N'
AND nvl(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND ( ( pgt.char3 = po_document_cancel_pvt.c_entity_level_shipment
AND pgt.num1 = line_location_id )
OR( pgt.char3 = po_document_cancel_pvt.c_entity_level_line
AND pgt.char1 <> po_document_cancel_pvt.c_doc_type_PA
-- bug 16525950 : consider only the PO shipment and not release
-- In case of PPO, the release shipment will also be considered
-- if we do not add this condition, and as this is for level =Line
-- it is not applicable for releases
AND poll.po_release_id IS NULL
AND pgt.num1 = po_line_id )
OR(pgt.char1 = po_document_cancel_pvt.c_doc_type_PO
AND pgt.char3 = po_document_cancel_pvt.c_entity_level_header
-- bug 16525950
AND poll.po_release_id IS NULL
AND pgt.num1 = po_header_id )
OR (pgt.char1 = po_document_cancel_pvt.c_doc_type_RELEASE
AND pgt.char3 = po_document_cancel_pvt.c_entity_level_header
AND pgt.num1 = po_release_id ) ));
INSERT INTO po_session_gt gt(
key,
num1,
index_num1,
char1,
char2,
char3,
char4,
char5,
char6,
date1)
(SELECT
l_new_key,
num1,
po_line_id,
char1,
char2,
char3,
char4,
'Y',
(SELECT segment1
FROM po_headers
WHERE po_header_id=pol.po_header_id
),
date1
FROM
po_lines pol,
po_session_gt pgt
WHERE pgt.key = l_old_key
AND nvl(pol.cancel_flag,'N') = 'N'
AND nvl(pol.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND (( pgt.char3 = po_document_cancel_pvt.c_entity_level_line
AND pgt.char1 = po_document_cancel_pvt.c_doc_type_PA
AND pgt.num1 = po_line_id )
OR(pgt.char1 = po_document_cancel_pvt.c_doc_type_PA
AND pgt.char3 = po_document_cancel_pvt.c_entity_level_header
AND pgt.num1 = pol.po_header_id ) ));
INSERT INTO po_session_gt gt(
key,
num1,
index_num1,
char1,
char2,
char3,
char4,
char5,
char6,
date1)
(SELECT
l_new_key,
num1,
num1,
char1,
char2,
char3,
char4,
'Y',
(SELECT segment1
FROM po_headers
WHERE po_header_id=num1
),
date1
FROM po_session_gt pgt
WHERE pgt.key = l_old_key
AND pgt.char1 = po_document_cancel_pvt.c_doc_type_PA
AND pgt.char2 = po_document_cancel_pvt.c_doc_subtype_contract);
END update_gt_with_low_entity;
p_login_id IN po_lines.last_update_login%TYPE,
p_user_id IN po_lines.last_updated_by%TYPE,
p_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
p_key IN po_session_gt.key%TYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2)
IS
d_api_name CONSTANT VARCHAR2(30) := 'validate_set.';
p_login_id IN po_lines.last_update_login%TYPE,
p_user_id IN po_lines.last_updated_by%TYPE,
p_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
p_online_report_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_return_msg OUT NOCOPY VARCHAR2)
IS
d_api_name CONSTANT VARCHAR2(30) := 'po_cancel_action_checks';
p_user_id IN po_lines.last_updated_by%TYPE,
p_login_id IN po_lines.last_update_login%TYPE,
p_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
p_agent_id IN PO_HEADERS.agent_id%TYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_return_msg OUT NOCOPY VARCHAR2)
IS
d_api_name CONSTANT VARCHAR2(30) := 'val_security_check.';
INSERT INTO PO_online_report_text(
ONLINE_REPORT_ID,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE,
LINE_NUM,
SHIPMENT_NUM,
DISTRIBUTION_NUM,
SEQUENCE,
TEXT_LINE,
transaction_id,
transaction_type)
(SELECT
p_online_report_id,
p_login_id,
p_user_id,
SYSDATE,
p_user_id,
SYSDATE,
0,
0,
0,
p_sequence + ROWNUM,
PO_CORE_S.get_translated_text('PO_CAN_CONTROL_SECURITY_FAILED',
'DOC_NUM', gt.char6),
gt.num1,
gt.char3
FROM po_headers poh,
po_session_gt gt
WHERE gt.key=p_key
AND poh.po_header_id = gt.char4
AND gt.char1 <> po_document_cancel_pvt.c_doc_type_RELEASE
AND (po_control_action_validations.val_doc_security(
poh.agent_id,
p_agent_id,
gt.char1,
gt.char2) <>'Y' )) ;
INSERT INTO PO_online_report_text(
ONLINE_REPORT_ID,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE,
LINE_NUM,
SHIPMENT_NUM,
DISTRIBUTION_NUM,
SEQUENCE,
TEXT_LINE,
transaction_id,
transaction_type)
(SELECT
p_online_report_id,
p_login_id,
p_user_id,
SYSDATE,
p_user_id,
SYSDATE,
0,
0,
0,
p_sequence + ROWNUM,
PO_CORE_S.get_translated_text('PO_CAN_CONTROL_SECURITY_FAILED',
'DOC_NUM',gt.char6),
gt.num1,
gt.char3
FROM
po_releases prh,
po_session_gt gt
WHERE gt.key=p_key
AND prh.po_release_id = gt.char4
AND gt.char1 = po_document_cancel_pvt.c_doc_type_RELEASE
AND(po_control_action_validations.val_doc_security(
prh.agent_id,
p_agent_id,
gt.char1,
gt.char2) <>'Y' )) ;
p_user_id IN po_lines.last_updated_by%TYPE,
p_login_id IN po_lines.last_update_login%TYPE,
p_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
p_source IN VARCHAR2 DEFAULT NULL,
p_key IN po_session_gt.key%TYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_return_msg OUT NOCOPY VARCHAR2)
IS
d_api_name CONSTANT VARCHAR2(30) := 'val_doc_state_check.';
INSERT INTO po_online_report_text(
ONLINE_REPORT_ID,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE,
LINE_NUM,
SHIPMENT_NUM,
DISTRIBUTION_NUM,
SEQUENCE,
TEXT_LINE,
transaction_id,
transaction_type) VALUES
(p_online_report_id,
p_login_id,
p_user_id,
SYSDATE,
p_user_id,
SYSDATE,
0,
0,
0,
p_sequence + 1,
PO_CORE_S.get_translated_text(
'PO_CAN_CONTROL_INVALID_ACTION',
'DOC_NUM',
Decode(p_entity_rec_tbl(i).document_type,
po_document_cancel_pvt.c_doc_type_RELEASE,
(SELECT poh.segment1||'-'|| por.release_num
FROM po_releases por,
po_headers poh
WHERE por.po_release_id=p_entity_rec_tbl(i).doc_id
AND por.po_header_id=poh.po_header_id),
(SELECT segment1
FROM po_headers
WHERE po_header_id=p_entity_rec_tbl(i).doc_id)
),
'ACTION',
'CANCEL',
'ENTITY_LEVEL',
p_entity_rec_tbl(i).entity_level),
p_entity_rec_tbl(i).entity_id,
p_entity_rec_tbl(i).entity_level);
p_user_id IN po_lines.last_updated_by%TYPE,
p_login_id IN po_lines.last_update_login%TYPE,
p_key IN po_session_gt.key%TYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2)
IS
d_api_name CONSTANT VARCHAR2(30) := 'revert_pending_changes';
SELECT Nvl(Max(sequence) ,0)
INTO l_sequence
FROM PO_ONLINE_REPORT_TEXT
WHERE online_report_id=p_online_report_id;
UPDATE po_distributions_all pod
SET (amount_ordered ,
quantity_ordered )=
(SELECT
amount_ordered,
quantity_ordered
FROM po_distributions_archive_all poad
WHERE poad.po_distribution_id =pod.po_distribution_id
AND poad.latest_external_flag ='Y')
WHERE pod.line_location_id IN
(SELECT poll.line_location_id
FROM po_session_gt gt,
po_line_locations poll,
po_line_locations_archive_all poall
WHERE gt.key=p_key
AND gt.char1 <> PO_Document_Cancel_PVT.c_doc_type_PA
AND nvl(gt.char5,'Y') <> 'N'
AND poall.line_location_id =poll.line_location_id
AND poall.latest_external_flag ='Y'
AND (Nvl(poll.price_override,0) <> Nvl(poall.price_override,0)
OR Nvl(poll.quantity,0) <> Nvl(poall.quantity,0)
OR Nvl(poll.amount,0) <> Nvl(poall.amount,0)
OR Nvl(poll.promised_date,sysdate) <> Nvl(poall.promised_date,sysdate)
OR Nvl(poll.need_by_date,sysdate) <> Nvl(poall.need_by_date,sysdate))
AND (( poll.line_location_id=gt.num1
AND gt.char3=PO_Document_Cancel_PVT.c_entity_level_SHIPMENT)
OR (poll.po_line_id=gt.num1
AND gt.char3=PO_Document_Cancel_PVT.c_entity_level_LINE)
OR (poll.po_header_id=gt.num1
AND gt.char1<>PO_Document_Cancel_PVT.c_doc_type_RELEASE
AND gt.char3=PO_Document_Cancel_PVT.c_entity_level_HEADER)
OR (poll.po_release_id=gt.num1
AND gt.char1=PO_Document_Cancel_PVT.c_doc_type_RELEASE
AND gt.char3=PO_Document_Cancel_PVT.c_entity_level_HEADER)
))
RETURNING line_location_id,po_line_id
BULK COLLECT INTO l_line_loc_id_tbl,l_line_id_tbl;
PO_DEBUG.debug_var(d_module,l_progress,'Updated distributions -Line Loc Count',l_line_loc_id_tbl.count);
PO_DEBUG.debug_var(d_module,l_progress,'Updated distributions-Line Count',l_line_id_tbl.count);
UPDATE po_line_locations poll
SET (price_override,
quantity,
amount,
need_by_date,
promised_date) =
(SELECT
price_override,
quantity,
amount,
need_by_date,
promised_date
FROM
po_line_locations_archive_all poall
WHERE poall.line_location_id =poll.line_location_id
AND poall.latest_external_flag ='Y')
WHERE line_location_id IN
(SELECT *
FROM TABLE (CAST (l_line_loc_id_tbl AS po_tbl_number)));
UPDATE po_lines pol
SET (amount , quantity )=
(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))
)),
SUM(NVL(poll.quantity,0)
- NVL(poll.quantity_cancelled, 0))
FROM po_line_locations POLL
WHERE poll.po_line_id = pol.po_line_id)
WHERE po_line_id IN (SELECT *
FROM TABLE (CAST (l_line_id_tbl AS po_tbl_number)));
UPDATE po_lines pol
SET pol.unit_price =
(SELECT unit_price
FROM po_lines_archive_all
WHERE po_line_id = pol.po_line_id
AND latest_external_flag='Y')
WHERE po_line_id IN (SELECT po_line_id
FROM po_line_locations poll
WHERE poll.payment_type IS NULL
AND line_location_id IN
(SELECT *
FROM TABLE (CAST (l_line_loc_id_tbl AS po_tbl_number)))
);
UPDATE po_lines pol
SET pol.unit_price =
(SELECT SUM(price_override)
FROM po_line_locations
WHERE po_line_id = pol.po_line_id)
WHERE pol.order_type_lookup_code IN ('QUANTITY', 'AMOUNT')
AND po_line_id IN (SELECT po_line_id
FROM po_line_locations poll
WHERE poll.payment_type IS NOT NULL
AND line_location_id IN
(SELECT *
FROM TABLE (CAST (l_line_loc_id_tbl AS po_tbl_number)))
);
INSERT INTO PO_ONLINE_REPORT_TEXT(
ONLINE_REPORT_ID,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE,
LINE_NUM,
SHIPMENT_NUM,
DISTRIBUTION_NUM,
SEQUENCE,
TEXT_LINE,
message_type,
transaction_id,
transaction_type)
(SELECT
p_online_report_id,
p_login_id,
p_user_id,
SYSDATE,
p_user_id,
SYSDATE,
POL.LINE_NUM,
poll.SHIPMENT_NUM,
0,
l_sequence + ROWNUM,
PO_CORE_S.get_translated_text(
'PO_CHANGED_CANT_CANCEL_INFO',
'DOC_LINE_SHIP_DIST_NUM',l_doc_token||gt.char6||','|| l_line_token||pol.LINE_NUM||','||l_ship_token||poll.SHIPMENT_NUM,
'PRICE_TOKEN',poll.price_override||l_to_token || (SELECT price_override
FROM po_line_locations_archive_all
WHERE latest_external_flag ='Y'
AND line_location_id =poll.line_location_id),
'AMT_QTY_TOKEN', DECODE(poll.amount,NULL, 'AMOUNT',l_amt_token,l_qty_token),
'QTY_AMT',Decode(poll.amount,NULL,
poll.quantity||l_to_token || (SELECT quantity
FROM po_line_locations_archive_all
WHERE latest_external_flag ='Y'
AND line_location_id =poll.line_location_id),
poll.amount||l_to_token || (SELECT amount
FROM po_line_locations_archive_all
WHERE latest_external_flag ='Y'
AND line_location_id =poll.line_location_id)
),
'NEED_BY_PRM_DATE', Decode(poll.promised_date,NULL,
poll.need_by_date||l_to_token || (SELECT need_by_date
FROM po_line_locations_archive_all
WHERE latest_external_flag ='Y'
AND line_location_id =poll.line_location_id),
poll.promised_date||l_to_token || (SELECT promised_date
FROM po_line_locations_archive_all
WHERE latest_external_flag ='Y'
AND line_location_id =poll.line_location_id)
)),
'I',
gt.num1,
gt.char3
FROM
po_line_locations poll,
po_lines pol,
po_session_gt gt
WHERE
gt.key=p_key
AND gt.char1 <> PO_Document_Cancel_PVT.c_doc_type_PA
AND poll.po_line_id = pol.po_line_id
AND poll.line_location_id IN
(SELECT line_location_id
FROM TABLE (CAST (l_line_loc_id_tbl AS po_tbl_number)))
AND ( (poll.line_location_id=gt.num1
AND gt.char3=PO_Document_Cancel_PVT.c_entity_level_SHIPMENT)
OR(poll.po_line_id=gt.num1
AND gt.char3=PO_Document_Cancel_PVT.c_entity_level_LINE)
OR(poll.po_header_id=gt.num1
AND gt.char1<>PO_Document_Cancel_PVT.c_doc_type_RELEASE
AND gt.char3=PO_Document_Cancel_PVT.c_entity_level_HEADER)
OR (poll.po_release_id=gt.num1
AND gt.char1=PO_Document_Cancel_PVT.c_doc_type_RELEASE
AND gt.char3=PO_Document_Cancel_PVT.c_entity_level_HEADER)
));
p_user_id IN po_lines.last_updated_by%TYPE,
p_login_id IN po_lines.last_update_login%TYPE,
p_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
p_source IN VARCHAR2 DEFAULT NULL,
p_low_level_key IN po_session_gt.key%TYPE,
p_entity_level_key IN po_session_gt.key%TYPE,
p_po_enc_flag IN FINANCIALS_SYSTEM_PARAMETERS.purch_encumbrance_flag%TYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_return_msg OUT NOCOPY VARCHAR2)
IS
d_api_name CONSTANT VARCHAR2(30) := 'check_revert_pending_changes.';
INSERT INTO PO_ONLINE_REPORT_TEXT(
ONLINE_REPORT_ID,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE,
LINE_NUM,
SHIPMENT_NUM,
DISTRIBUTION_NUM,
SEQUENCE,
TEXT_LINE,
message_type,
transaction_id,
transaction_type)
(SELECT
p_online_report_id,
p_login_id,
p_user_id,
SYSDATE,
p_user_id,
SYSDATE,
POL.LINE_NUM,
poll.SHIPMENT_NUM,
0,
p_sequence + ROWNUM,
PO_CORE_S.get_translated_text(
'PO_CANT_REVERT_PENDING_CHG',
'DOC_LINE_SHIP_DIST_NUM',l_doc_token||gt.char6||','|| l_line_token||pol.LINE_NUM||','||l_ship_token||poll.SHIPMENT_NUM
),
'E',
gt.num1,
gt.char3
FROM
po_distributions_all pod,
po_line_locations poll,
po_lines pol ,
po_session_gt gt
WHERE
gt.key=p_low_level_key
AND pod.line_location_id=poll.line_location_id
AND poll.line_location_id = gt.index_num1 -- lowestentityid i.e.
AND poll.po_line_id = pol.po_line_id
AND gt.char1 <> po_document_cancel_pvt.c_doc_type_PA
AND Nvl(poll.approved_flag,'N')<>'Y'
-- <13503748: Edit without unreserve ER START>
-- Throw an error if the encumbered flag at PO distributions is N for
-- encumbered enabled environment
AND (p_po_enc_flag = 'Y' AND pod.encumbered_flag = 'N')
-- <13503748: Edit without unreserve ER END>
AND (p_po_enc_flag ='Y'
OR NOT EXISTS (SELECT 'exists archive'
FROM po_distributions_archive_all
WHERE po_distribution_id =pod.po_distribution_id)
)
);
INSERT INTO PO_ONLINE_REPORT_TEXT(
ONLINE_REPORT_ID,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE,
LINE_NUM,
SHIPMENT_NUM,
DISTRIBUTION_NUM,
SEQUENCE,
TEXT_LINE,
message_type,
transaction_id,
transaction_type)
(SELECT
p_online_report_id,
p_login_id,
p_user_id,
SYSDATE,
p_user_id,
SYSDATE,
POL.LINE_NUM,
poll.SHIPMENT_NUM,
0,
p_sequence + ROWNUM,
PO_CORE_S.get_translated_text(
'PO_CHANGED_CANT_CANCEL_ERR',
'DOC_LINE_SHIP_DIST_NUM',l_doc_token||gt.char6||','|| l_line_token||pol.LINE_NUM||','||l_ship_token||poll.SHIPMENT_NUM,
'PRICE_TOKEN', (SELECT price_override
FROM po_line_locations_archive_all
WHERE latest_external_flag ='Y'
AND line_location_id =poll.line_location_id)||l_to_token || poll.price_override,
'AMT_QTY_TOKEN', DECODE(poll.amount,NULL, 'AMOUNT',l_amt_token,l_qty_token),
'QTY_AMT',Decode(poll.amount,NULL,
(SELECT quantity
FROM po_line_locations_archive_all
WHERE latest_external_flag ='Y'
AND line_location_id =poll.line_location_id) ||l_to_token || poll.quantity,
(SELECT amount
FROM po_line_locations_archive_all
WHERE latest_external_flag ='Y'
AND line_location_id =poll.line_location_id) ||l_to_token || poll.amount
),
'NEED_BY_PRM_DATE', Decode(poll.promised_date,NULL,
(SELECT need_by_date
FROM po_line_locations_archive_all
WHERE latest_external_flag ='Y'
AND line_location_id =poll.line_location_id)||l_to_token||poll.need_by_date ,
(SELECT promised_date
FROM po_line_locations_archive_all
WHERE latest_external_flag ='Y'
AND line_location_id =poll.line_location_id)||l_to_token||poll.promised_date
)),
'E',
gt.num1,
gt.char3
FROM
po_distributions_all pod,
po_line_locations poll,
po_lines pol ,
po_session_gt gt
WHERE
gt.key=p_low_level_key
AND pod.line_location_id=poll.line_location_id
AND poll.line_location_id = gt.index_num1 -- lowestentityid i.e.
AND poll.po_line_id = pol.po_line_id
AND gt.char1 <> po_document_cancel_pvt.c_doc_type_PA
AND ((NOT EXISTS (SELECT 'exists archive'
FROM po_distributions_archive_all
WHERE po_distribution_id =pod.po_distribution_id)
AND Nvl(poll.approved_flag,'N')<>'Y')
OR EXISTS (SELECT 'change exists'
FROM po_line_locations_archive_all poall
WHERE poall.line_location_id =poll.line_location_id
AND poall.latest_external_flag ='Y'
AND (Nvl(poll.price_override,0) <> Nvl(poall.price_override,0)
OR Nvl(poll.quantity,0) <> Nvl(poall.quantity,0)
OR Nvl(poll.amount,0) <> Nvl(poall.amount,0)
OR Nvl(poll.promised_date,sysdate) <> Nvl(poall.promised_date,sysdate)
OR Nvl(poll.need_by_date,sysdate) <> Nvl(poall.need_by_date,sysdate))
)
)
);
p_user_id IN po_lines.last_updated_by%TYPE,
p_login_id IN po_lines.last_update_login%TYPE,
p_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
x_cancel_reqs_flag IN OUT NOCOPY VARCHAR2,
p_online_report_id IN NUMBER,
p_doc_type IN VARCHAR2,
p_doc_id IN NUMBER,
p_po_encumbrance_flag IN VARCHAR2,
p_req_encumbrance_flag IN VARCHAR2)
IS
d_api_name CONSTANT VARCHAR2(30) := 'check_cancel_reqs_flag.';
SELECT cancel_reqs_on_po_cancel_flag
INTO l_cancel_reqs_sys_val
FROM po_system_parameters;
INSERT INTO po_online_report_text(
ONLINE_REPORT_ID,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE,
LINE_NUM,
SHIPMENT_NUM,
DISTRIBUTION_NUM,
SEQUENCE,
TEXT_LINE,
transaction_id,
transaction_type) VALUES
(p_online_report_id,
p_login_id,
p_user_id,
SYSDATE,
p_user_id,
SYSDATE,
0,
0,
0,
p_sequence + 1,
PO_CORE_S.get_translated_text('PO_INVALID_CANCEL_REQS_FLAG',
'USER_VALUE',l_cancel_reqs_flag,
'SYSTEM_VALUE',l_cancel_reqs_sys_val),
0,
0
);
p_user_id IN po_lines.last_updated_by%TYPE,
p_login_id IN po_lines.last_update_login%TYPE,
p_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_return_msg OUT NOCOPY VARCHAR2)
IS
d_api_name CONSTANT VARCHAR2(30) := 'validate_doc_params.';
INSERT INTO PO_ONLINE_REPORT_TEXT(
ONLINE_REPORT_ID,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE,
LINE_NUM,
SHIPMENT_NUM,
DISTRIBUTION_NUM,
SEQUENCE,
TEXT_LINE,
transaction_id,
transaction_type)
(SELECT
p_online_report_id,
p_login_id,
p_user_id,
SYSDATE,
p_user_id,
SYSDATE,
0,
0,
0,
p_sequence + ROWNUM,
PO_CORE_S.get_translated_text
('PO_INVALID_DOC_TYPE_SUBTYPE'
, 'TYPE', gt.char1
, 'SUBTYPE', gt.char2
),
gt.num1,
gt.char3
FROM
po_session_gt gt
WHERE gt.key=p_key
AND (gt.char1 IS NULL
OR gt.char2 IS NULL
OR gt.char1 NOT IN (po_document_cancel_pvt.c_doc_type_PO,
po_document_cancel_pvt.c_doc_type_PA,
po_document_cancel_pvt.c_doc_type_RELEASE)
OR (gt.char1=po_document_cancel_pvt.c_doc_type_PO
AND gt.char2 NOT IN(po_document_cancel_pvt.c_doc_subtype_STANDARD,
po_document_cancel_pvt.c_doc_subtype_PLANNED))
OR (gt.char1=po_document_cancel_pvt.c_doc_type_PA
AND gt.char2 NOT IN (po_document_cancel_pvt.c_doc_subtype_BLANKET,
po_document_cancel_pvt.c_doc_subtype_contract))
OR (gt.char1=po_document_cancel_pvt.c_doc_type_RELEASE
AND gt.char2 NOT IN (po_document_cancel_pvt.c_doc_subtype_BLANKET,
po_document_cancel_pvt.c_doc_subtype_SCHEDULED))
));
INSERT INTO PO_ONLINE_REPORT_TEXT(
ONLINE_REPORT_ID,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE,
LINE_NUM,
SHIPMENT_NUM,
DISTRIBUTION_NUM,
SEQUENCE,
TEXT_LINE,
transaction_id,
transaction_type)
(SELECT
p_online_report_id,
p_login_id,
p_user_id,
SYSDATE,
p_user_id,
SYSDATE,
0,
0,
0,
p_sequence + ROWNUM,
PO_CORE_S.get_translated_text
('PO_INVALID_DOC_IDS',
'DOC_ID',
gt.num1),
gt.num1,
gt.char3
FROM
po_session_gt gt
WHERE gt.key=p_key
AND gt.char3 =po_document_cancel_pvt.c_entity_level_SHIPMENT
AND NOT EXISTS (SELECT '1'
FROM po_line_locations poll
WHERE poll.line_location_id = gt.num1
));
INSERT INTO PO_ONLINE_REPORT_TEXT(
ONLINE_REPORT_ID,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE,
LINE_NUM,
SHIPMENT_NUM,
DISTRIBUTION_NUM,
SEQUENCE,
TEXT_LINE,
transaction_id,
transaction_type)
(SELECT
p_online_report_id,
p_login_id,
p_user_id,
SYSDATE,
p_user_id,
SYSDATE,
0,
0,
0,
p_sequence + ROWNUM,
PO_CORE_S.get_translated_text
('PO_INVALID_DOC_IDS',
'DOC_ID',
gt.num1),
gt.num1,
gt.char3
FROM
po_session_gt gt
WHERE gt.key=p_key
AND gt.char3 =po_document_cancel_pvt.c_entity_level_LINE
AND NOT EXISTS(SELECT '1'
FROM po_lines pol
WHERE pol.po_line_id = gt.num1
));
INSERT INTO PO_ONLINE_REPORT_TEXT(
ONLINE_REPORT_ID,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE,
LINE_NUM,
SHIPMENT_NUM,
DISTRIBUTION_NUM,
SEQUENCE,
TEXT_LINE,
transaction_id,
transaction_type)
(SELECT p_online_report_id,
p_login_id,
p_user_id,
SYSDATE,
p_user_id,
SYSDATE,
0,
0,
0,
p_sequence + ROWNUM,
PO_CORE_S.get_translated_text
('PO_INVALID_DOC_IDS',
'DOC_ID',
gt.num1),
gt.num1,
gt.char3
FROM
po_session_gt gt
WHERE gt.key=p_key
AND gt.char3 =po_document_cancel_pvt.c_entity_level_HEADER
AND gt.char1<>po_document_cancel_pvt.c_doc_type_RELEASE
AND NOT EXISTS (SELECT '1'
FROM po_headers poh
WHERE poh.po_header_id = gt.num1
));
INSERT INTO PO_ONLINE_REPORT_TEXT(
ONLINE_REPORT_ID,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE,
LINE_NUM,
SHIPMENT_NUM,
DISTRIBUTION_NUM,
SEQUENCE,
TEXT_LINE,
transaction_id,
transaction_type)
(SELECT
p_online_report_id,
p_login_id,
p_user_id,
SYSDATE,
p_user_id,
SYSDATE,
0,
0,
0,
p_sequence + ROWNUM,
PO_CORE_S.get_translated_text
('PO_INVALID_DOC_IDS',
'DOC_ID',
gt.num1),
gt.num1,
gt.char3
FROM
po_session_gt gt
WHERE gt.key=p_key
AND gt.char3 =po_document_cancel_pvt.c_entity_level_HEADER
AND gt.char1 =po_document_cancel_pvt.c_doc_type_RELEASE
AND NOT EXISTS (SELECT '1'
FROM po_releases poh
WHERE poh.po_release_id = gt.num1 ));
INSERT INTO PO_ONLINE_REPORT_TEXT(
ONLINE_REPORT_ID,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE,
LINE_NUM,
SHIPMENT_NUM,
DISTRIBUTION_NUM,
SEQUENCE,
TEXT_LINE,
transaction_id,
transaction_type)
(SELECT
p_online_report_id,
p_login_id,
p_user_id,
SYSDATE,
p_user_id,
SYSDATE,
0,
0,
0,
p_sequence + ROWNUM,
PO_CORE_S.get_translated_text
('PO_INVALID_DOC_IDS',
'DOC_ID',
gt.char4),
gt.num1,
gt.char3
FROM
po_session_gt gt
WHERE gt.key=p_key
AND GT.char3 <> PO_Document_Cancel_PVT.c_entity_level_HEADER
AND NOT EXISTS (SELECT '1'
FROM po_headers poh
WHERE poh.po_header_id = gt.char4
UNION ALL
SELECT '1'
FROM po_releases prh
WHERE prh.po_release_id = gt.char4));--validate doc_id
SELECT Count(DISTINCT OPERATING_UNIT)
INTO l_org_count
FROM po_headers,
org_organization_definitions ood
WHERE ood.organization_id=org_id
AND po_header_id IN
(SELECT pol.po_header_id
FROM po_lines pol,
po_session_gt gt
WHERE gt.KEY=p_key
AND gt.num1=pol.po_line_id
AND gt.char3=PO_Document_Cancel_PVT.c_entity_level_LINE
UNION ALL
SELECT poll.po_header_id
FROM po_line_locations poll,
po_session_gt gt
WHERE gt.KEY=p_key
AND gt.num1=poll.line_location_id
AND gt.char3=PO_Document_Cancel_PVT.c_entity_level_SHIPMENT
UNION ALL
SELECT gt.num1
FROM po_session_gt gt
WHERE gt.KEY=p_key
AND gt.char3=PO_Document_Cancel_PVT.c_entity_level_HEADER
) ;
INSERT INTO PO_ONLINE_REPORT_TEXT(
ONLINE_REPORT_ID,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE,
LINE_NUM,
SHIPMENT_NUM,
DISTRIBUTION_NUM,
SEQUENCE,
TEXT_LINE,
transaction_id,
transaction_type)
VALUES
(p_online_report_id,
p_login_id,
p_user_id,
SYSDATE,
p_user_id,
SYSDATE,
0,
0,
0,
p_sequence + 1,
PO_CORE_S.get_translated_text('PO_CAN_DIFF_OU_DOCS'),
0,
0
);
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,
p_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_return_msg OUT NOCOPY VARCHAR2)
IS
d_api_name CONSTANT VARCHAR2(30) := 'validate_cancel_action_params.';
INSERT INTO po_online_report_text(
ONLINE_REPORT_ID,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE,
LINE_NUM,
SHIPMENT_NUM,
DISTRIBUTION_NUM,
SEQUENCE,
TEXT_LINE,
transaction_id,
transaction_type)
(SELECT
p_online_report_id,
p_login_id,
p_user_id,
SYSDATE,
p_user_id,
SYSDATE,
0,
0,
0,
p_sequence + ROWNUM,
PO_CORE_S.get_translated_text('PO_ACTION_DATE_INVALID',
'DOC_NUM',
gt.char6,
'ACTION_DATE',
l_action_date),
gt.num1,
gt.char3
FROM
po_session_gt gt
WHERE gt.key=p_key
AND gt.char4 =l_entity_rec_tbl(i).doc_id
);
UPDATE po_session_gt
SET date1= l_action_date
WHERE KEY=p_key
AND char4= l_entity_rec_tbl(i).doc_id;
PO_DEBUG.debug_var(d_module,l_progress,'update row count',SQL%ROWCOUNT);
UPDATE po_session_gt
SET char5 ='N'
WHERE KEY=p_key
AND EXISTS(SELECT 'error record exists'
FROM po_online_report_text
WHERE transaction_id=num1
AND transaction_type=char3
AND Nvl(message_type,'E') = 'E'
AND online_report_id =p_online_report_id);
PO_DEBUG.debug_var(d_module,l_progress,'records updated in po_session_gt',l_count);
SELECT DISTINCT char4,
char1,
char2,
num1,
char3,
date1,
char5,
'N'
BULK COLLECT INTO
p_entity_rec_tbl
FROM
po_session_gt
WHERE KEY=p_key
ORDER BY char4;
PO_DEBUG.debug_var(d_module,l_progress,'records updated into p_entity_rec_tbl',l_count);
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,
x_return_code OUT NOCOPY VARCHAR2)
IS
l_online_report_id NUMBER;
SELECT PO_ONLINE_REPORT_TEXT_S.nextval
INTO l_online_report_id
FROM sys.dual;
update_gt_with_low_entity(
p_entity_rec_tbl=>p_da_call_rec.entity_dtl_record_tbl,
p_key =>l_temp_key,
x_return_status =>x_return_status,
x_msg_data =>x_msg_data);
DELETE FROM po_session_gt WHERE KEY=l_temp_key;