[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT revision_num
,start_date
,end_date
FROM po_headers_gt
WHERE po_header_id = p_document_id;
INSERT INTO po_online_report_text_gt
(online_report_id
,last_updated_by
,last_update_date
,created_by
,creation_date
,line_num
,shipment_num
,distribution_num
,sequence
,text_line
,message_name
,message_type
)
VALUES
(p_online_report_id
,p_login_id
,sysdate
,p_user_id
,sysdate
,0 ,0 ,0
,p_sequence+1
,l_qa_result_tbl(l_row_index).problem_details
,l_qa_result_tbl(l_row_index).message_name
,l_qa_result_tbl(l_row_index).error_severity
);
* Modifies: Inserts error msgs in online_report_text table, uses global_temp
* tables for processing
* Effects: This procedure runs the document submission checks on passed in
* document.
* Returns:
* x_return_status: FND_API.G_RET_STS_SUCCESS if API succeeds
* FND_API.G_RET_STS_ERROR if API fails
* FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
* x_sub_check_status: FND_API.G_RET_STS_SUCCESS if document passes all
* submission checks, even if warnings are found
* FND_API.G_RET_STS_ERROR if document fails atleast one
* submission check
* x_has_warnings: FND_API.G_TRUE if submission check returns warnings
* FND_API.G_FALSE if no warnings are found
* x_msg_data: Contains error msg in case x_return_status returned
* FND_API.G_RET_STS_ERROR or FND_API.G_RET_STS_UNEXP_ERROR
* x_online_report_id: This id can be used to get all submission check errors
* for given document from online_report_text table
* x_doc_check_error_record: If x_sub_check_status returned G_RET_STS_ERROR
* then this object of tables will contain information about
* all submission check errors for given document including
* message_name and text_line.
* NOTE: This package does no validation of INPUT parameters. That is taken
* care of in Group package PO_DOCUMENT_CHECKS_GRP
*/
PROCEDURE po_submission_check(
p_api_version IN NUMBER
, p_action_requested IN VARCHAR2
, p_document_type IN VARCHAR2
, p_document_subtype IN VARCHAR2
-- Replaced p_document_id with doc_level, doc_level_id
, p_document_level IN VARCHAR2
, p_document_level_id IN NUMBER
-- Renamed the type to PO_CHANGES_REC_TYPE:
, p_requested_changes IN PO_CHANGES_REC_TYPE
, p_check_asl IN BOOLEAN -- bug 2757450
, p_req_chg_initiator IN VARCHAR2 -- bug 4957243
, p_origin_doc_id IN NUMBER := NULL --Bug#5462677
, x_return_status OUT NOCOPY VARCHAR2
, x_sub_check_status OUT NOCOPY VARCHAR2
, x_has_warnings OUT NOCOPY VARCHAR2 -- bug3574165
, x_msg_data OUT NOCOPY VARCHAR2
, x_online_report_id OUT NOCOPY NUMBER
, x_doc_check_error_record OUT NOCOPY doc_check_Return_Type
)
IS
l_api_name CONSTANT varchar2(30) := 'PO_SUBMISSION_CHECK';
l_user_id po_lines.last_updated_by%TYPE := -1;
l_login_id po_lines.last_update_login%TYPE := -1;
|| l_progress,'update requested, calling updating global tables');
update_global_temp_tables(p_document_type,
p_document_subtype,
l_document_id,
p_requested_changes,
l_return_status);
SELECT PO_ONLINE_REPORT_TEXT_S.nextval
INTO l_online_report_id
FROM sys.dual;
SELECT conterms_exist_flag
INTO l_conterms_exist_flag
FROM po_headers_gt
WHERE po_header_id = l_document_id;
SELECT po_header_id
INTO l_po_header_id
FROM PO_RELEASES_ALL
WHERE po_release_id = l_document_id;
SELECT count(text_line),
COUNT(DECODE(message_type, 'W', 'W', NULL))
INTO l_num_messages,
l_num_warnings
FROM po_online_report_text_gt
WHERE online_report_id = l_online_report_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) --
SELECT 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 --
FROM po_online_report_text_gt
WHERE online_report_id = x_online_report_id;
select count(*) into l_num_messages from po_online_report_text_gt where online_report_id = x_online_report_id;
SELECT online_report_id, sequence, text_line,
line_num, shipment_num, distribution_num, message_name, message_type
BULK COLLECT INTO x_doc_check_error_record.online_report_id,
x_doc_check_error_record.sequence_num,
x_doc_check_error_record.text_line,
x_doc_check_error_record.line_num,
x_doc_check_error_record.shipment_num,
x_doc_check_error_record.distribution_num,
x_doc_check_error_record.message_name,
x_doc_check_error_record.message_type --
FROM po_online_report_text_gt
WHERE online_report_id = x_online_report_id;
FOR ship_rec in (select line_location_id from po_line_locations_gt)
LOOP
set_lcm_flag(ship_rec.line_location_id,'AFTER',l_return_status);
l_user_id PO_LINES.last_updated_by%TYPE := -1;
l_login_id PO_LINES.last_update_login%TYPE := -1;
SELECT PO_ONLINE_REPORT_TEXT_S.nextval
INTO l_online_report_id
FROM sys.dual;
SELECT count(text_line)
INTO l_num_errors
FROM po_online_report_text_gt
WHERE online_report_id = l_online_report_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)
SELECT 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
FROM po_online_report_text_gt
WHERE online_report_id = x_online_report_id;
* p_online_report_id: Id used to INSERT INTO online_report_text table
* p_user_id: User performing the action
* p_login_id: Last update login_id
* IN OUT PARAMETERS
* p_sequence: Sequence number of last reported error
* Modifies: Updates the PO_REQUISITION_LINES table with RATE information.
* Inserts error msgs in online_report_text_gt table, uses global_temp
* tables for processing
* Effects: This procedure runs the document submission checks for
* REQUISITIONS
* Returns:
* p_sequence: This parameter contains the current count of number of error
* messages inserted
*/
PROCEDURE check_requisitions(p_document_id IN NUMBER,
p_online_report_id IN NUMBER,
p_user_id IN NUMBER,
p_login_id IN NUMBER,
p_sequence IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2) IS
l_textline po_online_report_text.text_line%TYPE := NULL;
INSERT INTO po_online_report_text_gt(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_name)
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
0,
0,
0,
p_sequence + ROWNUM,
substr(l_textline,1,240),
'PO_SUB_REQ_HEADER_NO_LINES'
FROM PO_REQ_HEADERS_GT PRH
WHERE PRH.requisition_header_id = p_document_id
AND NOT EXISTS (SELECT 'Lines Exist'
FROM PO_REQ_LINES_GT PRL
WHERE PRL.requisition_header_id = PRH.requisition_header_id
AND nvl(PRL.cancel_flag,'N') = 'N');
INSERT INTO po_online_report_text_gt(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_name)
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
PRL.line_num,
0,
0,
p_sequence + ROWNUM,
substr(g_linemsg||g_delim||PRL.line_num||g_delim||l_textline,1,240),
'PO_SUB_REQ_LINE_NO_DIST'
FROM PO_REQ_LINES_GT PRL
WHERE PRL.requisition_header_id = p_document_id AND
nvl(PRL.cancel_flag,'N') = 'N' AND
nvl(PRL.closed_code,'OPEN') <> 'FINALLY CLOSED' AND
nvl(PRL.modified_by_agent_flag,'N') = 'N' AND
NOT EXISTS (SELECT 'Dist Exist'
FROM PO_REQ_DISTRIBUTIONS_GT PRD
WHERE PRD.requisition_line_id = PRL.requisition_line_id);
SELECT
PRL.line_num
, PLT.order_type_lookup_code --
, PRL.quantity
, PRL.amount --
, sum(nvl(PRD.req_line_quantity, 0)) --
, sum(nvl(PRD.req_line_amount, 0)) --
BULK COLLECT INTO
l_line_num
, l_value_basis_tbl --
, l_line_qty_tbl --
, l_line_amt_tbl --
, l_dist_qty_tbl --
, l_dist_amt_tbl --
FROM
PO_REQ_DISTRIBUTIONS_GT PRD
, PO_REQ_LINES_GT PRL
, PO_LINE_TYPES_B PLT --
WHERE
PRL.requisition_line_id = PRD.requisition_line_id
AND PRL.requisition_header_id = p_document_id
AND nvl(PRL.cancel_flag,'N') = 'N'
AND nvl(PRL.closed_code,'OPEN') <> 'FINALLY CLOSED'
AND nvl(PRL.modified_by_agent_flag,'N') = 'N'
AND PRL.line_type_id = PLT.line_type_id --
AND --
( ( ( PLT.order_type_lookup_code IN ('QUANTITY','AMOUNT')
AND ( PRL.quantity <> ( SELECT nvl(sum(PRD2.req_line_quantity),0)
FROM PO_REQ_DISTRIBUTIONS_GT PRD2
WHERE PRD2.requisition_line_id = PRL.requisition_line_id ) ) )
OR ( ( PLT.order_type_lookup_code IN ('RATE','FIXED PRICE') )
AND ( PRL.amount <> ( SELECT nvl(sum(PRD2.req_line_amount),0)
FROM PO_REQ_DISTRIBUTIONS_GT PRD2
WHERE PRD2.requisition_line_id = PRL.requisition_line_id ) ) ) )
)
GROUP BY
PRL.line_num
, PLT.order_type_lookup_code --
, PRL.quantity
, PRL.amount; --
INSERT INTO po_online_report_text_gt (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_name)
VALUES(
p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
l_line_num(i),
NULL, --
NULL, --
p_sequence+l_rowCount(i),
decode ( l_value_basis_tbl(i) --
, 'RATE' , PO_CORE_S.get_translated_text
( 'PO_SUB_REQ_LINE_NE_DIST_AMT'
, 'LINE_NUM', l_line_num(i)
, 'LINE_AMT', l_line_amt_tbl(i)
, 'DIST_AMT', l_dist_amt_tbl(i)
)
, 'FIXED PRICE' , PO_CORE_S.get_translated_text
( 'PO_SUB_REQ_LINE_NE_DIST_AMT'
, 'LINE_NUM', l_line_num(i)
, 'LINE_AMT', l_line_amt_tbl(i)
, 'DIST_AMT', l_dist_amt_tbl(i)
)
, PO_CORE_S.get_translated_text
( 'PO_SUB_REQ_LINE_NE_DIST_QTY'
, 'LINE_NUM', l_line_num(i)
, 'LINE_QTY', l_line_qty_tbl(i)
, 'DIST_QTY', l_dist_qty_tbl(i)
)
),
decode ( l_value_basis_tbl(i) --
, 'RATE' , 'PO_SUB_REQ_LINE_NE_DIST_AMT'
, 'FIXED PRICE' , 'PO_SUB_REQ_LINE_NE_DIST_AMT'
, 'PO_SUB_REQ_LINE_NE_DIST_QTY'
)
);
INSERT INTO po_online_report_text_gt(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_name)
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
PRL.line_num,
0,
0,
p_sequence + ROWNUM,
substr(g_linemsg||g_delim||PRL.line_num||g_delim||l_textline,1,240),
'PO_SUB_REQ_SOURCE_ONE_DIST'
FROM PO_REQ_LINES_GT PRL
WHERE PRL.requisition_header_id = p_document_id AND
PRL.source_type_code = 'INVENTORY' AND
nvl(PRL.cancel_flag,'N') = 'N' AND
nvl(PRL.closed_code, 'OPEN') <> 'FINALLY CLOSED' AND
1 < (SELECT count(PRD.requisition_line_id)
FROM PO_REQ_DISTRIBUTIONS_GT PRD
WHERE PRD.requisition_line_id = PRL.requisition_line_id);
INSERT INTO po_online_report_text_gt(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_name)
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
PRL.line_num,
0,
0,
p_sequence + ROWNUM,
substr(g_linemsg||g_delim||PRL.line_num||g_delim||l_textline,1,240),
'PO_SUB_REQ_RATE_NULL'
FROM PO_REQ_LINES_GT PRL, FINANCIALS_SYSTEM_PARAMETERS FSP,
GL_SETS_OF_BOOKS SOB
WHERE PRL.requisition_header_id = p_document_id AND
nvl(PRL.cancel_flag, 'N') = 'N' AND
nvl(PRL.closed_code, 'OPEN') <> 'FINALLY CLOSED' AND
SOB.set_of_books_id = FSP.set_of_books_id AND
SOB.currency_code <> PRL.currency_code AND
(PRL.rate is NULL OR
PRL.rate_type is NULL OR
(PRL.rate_type <> 'User' AND PRL.rate_date is NULL));
INSERT INTO po_online_report_text_gt(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_name)
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
PRL.line_num,
0,
0,
p_sequence + ROWNUM,
substr(g_linemsg||g_delim||PRL.line_num||g_delim||l_textline,1,240),
'PO_ATO_ITEM_NA'
FROM PO_REQ_LINES_GT PRL, FINANCIALS_SYSTEM_PARAMETERS FSP,
MTL_SYSTEM_ITEMS MSI
WHERE PRL.requisition_header_id = p_document_id AND
nvl(PRL.cancel_flag, 'N') = 'N' AND
nvl(PRL.closed_code, 'OPEN') <> 'FINALLY CLOSED' AND
PRL.item_id is not null AND
PRL.item_id = MSI.inventory_item_id AND
FSP.inventory_organization_id = MSI.organization_id AND
MSI.bom_item_type in (1,2);
INSERT INTO po_online_report_text_gt
(
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_name,
message_type
)
VALUES
(
p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
po_tax_interface_pvt.G_TAX_ERRORS_TBL.line_num(i),
po_tax_interface_pvt.G_TAX_ERRORS_TBL.shipment_num(i),
po_tax_interface_pvt.G_TAX_ERRORS_TBL.distribution_num(i),
p_sequence + ROWNUM,
l_tax_message || po_tax_interface_pvt.G_TAX_ERRORS_TBL.message_text(i),
'PO_TAX_CALCULATION_FAILED',
'E'
);
* p_online_report_id: Id used to INSERT INTO online_report_text table
* p_user_id: User performing the action
* p_login_id: Last update login_id
* IN OUT PARAMETERS
* p_sequence: Sequence number of last reported error
* Modifies: Inserts error msgs in online_report_text_gt table, uses
* global_temp tables for processing
* Effects: This procedure runs the document submission checks for RELEASES
* Returns:
* p_sequence: This parameter contains the current count of number of error
* messages inserted
*/
PROCEDURE check_releases(p_document_id IN NUMBER,
p_online_report_id IN NUMBER,
p_user_id IN NUMBER,
p_login_id IN NUMBER,
p_sequence IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2) IS
l_textline po_online_report_text.text_line%TYPE := NULL;
INSERT INTO po_online_report_text_gt(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_name)
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
0,0,0,
p_sequence + ROWNUM,
substr(l_textline,1,240),
'PO_SUB_REL_PA_APPROVED'
FROM PO_RELEASES_GT POR,PO_HEADERS_GT POH
WHERE POR.po_header_id = POH.po_header_id
AND POR.po_release_id = p_document_id
AND NVL(POH.approved_flag, 'N') <> 'Y'
AND NVL(POH.user_hold_flag, 'N') <> 'Y'; --< Bug 3422733 >
INSERT INTO po_online_report_text_gt(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_name)
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
0,
0,
0,
p_sequence + ROWNUM,
substr(l_textline,1,240),
'PO_SUB_REL_PA_EXPIRED' -- bug3199869
FROM PO_RELEASES_GT POR,PO_HEADERS_GT POH
WHERE POR.po_header_id = POH.po_header_id
AND POR.po_release_id = p_document_id
AND POR.approved_date IS NULL -- bug3199869
AND TRUNC(SYSDATE) > -- bug3199869
TRUNC(NVL(POH.end_date, SYSDATE + 1)); -- bug3199869
INSERT INTO po_online_report_text_gt(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_name)
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
0,
0,
0,
p_sequence + ROWNUM,
substr(l_textline,1,240),
'PO_SUB_REL_PA_ON_HOLD'
FROM PO_RELEASES_GT POR,PO_HEADERS_GT POH
WHERE POR.po_header_id = POH.po_header_id
AND POR.po_release_id = p_document_id
AND nvl(POH.user_hold_flag, 'N') = 'Y';
INSERT INTO po_online_report_text_gt(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_name)
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
0,
0,
0,
p_sequence + ROWNUM,
substr(l_textline,1,240),
'PO_SUB_REL_VENDOR_ON_HOLD'
FROM PO_RELEASES_GT POR,PO_HEADERS_GT POH,PO_VENDORS POV,
PO_SYSTEM_PARAMETERS PSP
WHERE POR.po_header_id = POH.po_header_id
AND POV.vendor_id = POH.vendor_id
AND POR.po_release_id = p_document_id
AND nvl(PSP.ENFORCE_VENDOR_HOLD_FLAG,'N') = 'Y'
AND nvl(POV.hold_flag,'N') = 'Y';
INSERT INTO po_online_report_text_gt(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_name)
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
0,
0,
0,
p_sequence + ROWNUM,
substr(l_textline,1,240),
'PO_SUB_REL_NO_SHIP'
FROM PO_RELEASES_GT POR
WHERE POR.po_release_id = p_document_id
AND NOT EXISTS
(SELECT 'Shipment Exist'
FROM PO_LINE_LOCATIONS_GT PLL
WHERE PLL.po_release_id = POR.po_release_id);
SELECT POLG.shipment_num,
HLT.location_code
BULK COLLECT INTO
l_line_num,
l_ship_to_location_tbl
FROM PO_LINE_LOCATIONS_GT POLG,
HR_LOCATIONS_ALL HLA,
HR_LOCATIONS_ALL_TL HLT
WHERE POLG.po_release_id = p_document_id
AND POLG.ship_to_location_id=HLA.location_id
AND nvl(POLG.cancel_flag,'N') = 'N'
AND nvl(POLG.closed_code,'OPEN') <> 'FINALLY CLOSED'
AND NVL (TRUNC (HLA.INACTIVE_DATE), TRUNC (SYSDATE)+1 )<= TRUNC (SYSDATE)
AND HLA.location_id=HLT.location_id
AND HLT.language=USERENV('LANG');
INSERT INTO po_online_report_text_gt (
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_name)
VALUES(
p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
0,
0,
0, --
p_sequence+l_rowCount(i),
substr(PO_CORE_S.get_translated_text
( 'PO_SUB_REL_INVALID_SHIP_TO',
'LINE_NUM',
l_line_num(i),
'SHIP_TO_LOC',
l_ship_to_location_tbl(i)
),1,240),
'PO_SUB_REL_INVALID_SHIP_TO'
);
INSERT INTO po_online_report_text_gt(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_name)
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
0,
PLL.shipment_num,
0,
p_sequence + ROWNUM,
decode ( POL.order_type_lookup_code --
, 'FIXED PRICE' , PO_CORE_S.get_translated_text
( 'PO_SUB_REL_PRICE_GT_LIMIT'
, 'SHIP_NUM' , PLL.shipment_num
, 'SHIP_PRICE' , nvl( PLL.price_override,PLL.amount)
, 'LINE_PRICE' , nvl ( POL.not_to_exceed_price
, PLL.price_override )
)
, PO_CORE_S.get_translated_text
( 'PO_SUB_REL_AMT_GT_LIMIT'
, 'SHIP_NUM' , PLL.shipment_num
, 'SHIP_AMT' , PLL.amount
, 'LINE_AMT' , nvl ( POL.not_to_exceed_price
, PLL.amount )
)
),
decode ( POL.order_type_lookup_code --
, 'FIXED PRICE' , 'PO_SUB_REL_SHIP_PRICE_GT_LIMIT'
, 'PO_SUB_REL_SHIP_AMT_GT_LIMIT'
)
FROM PO_LINE_LOCATIONS_GT PLL,PO_LINES POL
WHERE PLL.po_line_id = POL.po_line_id
AND PLL.po_release_id = p_document_id
AND nvl(PLL.cancel_flag,'N')= 'N'
AND nvl(PLL.closed_code,'OPEN') <> 'FINALLY CLOSED'
AND POL.allow_price_override_flag = 'Y' -- Bug 3177525
AND ( --
( ( POL.order_type_lookup_code IN ('QUANTITY','AMOUNT','RATE'))--
AND ( PLL.price_override > nvl ( POL.not_to_exceed_price
, PLL.price_override ) )
)
OR
( ( POL.order_type_lookup_code IN ('FIXED PRICE') ) --
AND ( PLL.amount > nvl ( POL.not_to_exceed_price, PLL.amount ) ) )
)
;
SELECT
0
, PLL.shipment_num
, 0
, PLL.quantity
, PLL.amount --
, nvl(sum(POD.quantity_ordered),0) --
, nvl(sum(POD.amount_ordered),0) --
BULK COLLECT INTO
l_line_num
, l_shipment_num
, l_dist_num
, l_ship_qty_tbl --
, l_ship_amt_tbl --
, l_dist_qty_tbl --
, l_dist_amt_tbl --
FROM
PO_DISTRIBUTIONS_GT POD --
, PO_LINE_LOCATIONS_GT PLL
WHERE
PLL.line_location_id = POD.line_location_id
AND PLL.po_release_id = p_document_id
AND nvl(PLL.cancel_flag,'N') = 'N'
AND nvl(PLL.closed_code,'OPEN') <> 'FINALLY CLOSED'
AND ( --
( ( PLL.quantity IS NOT NULL )
AND ( PLL.quantity <> ( SELECT sum(POD2.quantity_ordered)
FROM PO_DISTRIBUTIONS_GT POD2
WHERE POD2.line_location_id = PLL.line_location_id ) ) )
OR ( ( PLL.amount IS NOT NULL )
AND ( PLL.amount <> ( SELECT sum(POD2.amount_ordered)
FROM PO_DISTRIBUTIONS_GT POD2
WHERE POD2.line_location_id = PLL.line_location_id ) ) )
)
GROUP BY
PLL.shipment_num
, PLL.quantity
, PLL.amount; --
INSERT INTO po_online_report_text_gt(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_name)
VALUES(
p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
NULL, --
l_shipment_num(i),
NULL, --
p_sequence+l_rowCount(i),
decode ( l_ship_qty_tbl(i) --
, NULL , PO_CORE_S.get_translated_text
( 'PO_SUB_REL_SHIP_NE_DIST_AMT'
, 'SHIP_NUM', l_shipment_num(i)
, 'SHIP_AMT', l_ship_amt_tbl(i)
, 'DIST_AMT', l_dist_amt_tbl(i)
)
, PO_CORE_S.get_translated_text
( 'PO_SUB_REL_SHIP_NE_DIST_QTY'
, 'SHIP_NUM', l_shipment_num(i)
, 'SHIP_QTY', l_ship_qty_tbl(i)
, 'DIST_QTY', l_dist_qty_tbl(i)
)
),
decode ( l_ship_qty_tbl(i) --
, NULL , 'PO_SUB_REL_SHIP_NE_DIST_AMT'
, 'PO_SUB_REL_SHIP_NE_DIST_QTY'
)
);
INSERT INTO po_online_report_text_gt(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_name)
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
0,
PLL.shipment_num,
0,
p_sequence + ROWNUM,
substr(g_shipmsg||g_delim||PLL.shipment_num||g_delim
||l_textline,1,240),
'PO_SUB_REL_SHIP_NO_DIST'
FROM PO_LINE_LOCATIONS_GT PLL
WHERE PLL.po_release_id = p_document_id
AND nvl(PLL.cancel_flag,'N') = 'N'
AND nvl(PLL.closed_code,'OPEN') <> 'FINALLY CLOSED'
AND NOT EXISTS
(SELECT 'Distribution Exists'
FROM PO_DISTRIBUTIONS_GT POD --
WHERE POD.line_location_id = PLL.line_location_id);
INSERT INTO po_online_report_text_gt(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_name)
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
0,
POLL.shipment_num,
POD.distribution_num,
p_sequence + ROWNUM,
substr(g_shipmsg||g_delim||POLL.shipment_num||g_delim
||g_distmsg||g_delim||POD.distribution_num||g_delim
||l_textline,1,240),
'PO_SUB_REL_RATE_NULL'
FROM PO_DISTRIBUTIONS_GT POD, --
PO_LINE_LOCATIONS_GT POLL,PO_LINES POL,
PO_RELEASES_GT POR,PO_HEADERS_GT POH,GL_SETS_OF_BOOKS SOB,
FINANCIALS_SYSTEM_PARAMETERS FSP
WHERE POLL.po_release_id = POR.po_release_id
AND POD.line_location_id = POLL.line_location_id
AND POLL.po_line_id = POL.po_line_id
AND POH.po_header_id = POR.po_header_id
AND POR.po_release_id = p_document_id
AND SOB.set_of_books_id = FSP.set_of_books_id
AND nvl(POLL.cancel_flag, 'N') = 'N'
AND nvl(POLL.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND SOB.currency_code <> POH.currency_code
AND POD.rate is null;
INSERT INTO po_online_report_text_gt(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_name)
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
0,
0,
0,
p_sequence + ROWNUM,
substr(l_textline,1,240),
'PO_SUB_REL_AMT_LESS_MINREL_AMT'
FROM PO_HEADERS_GT POH,PO_RELEASES_GT POR
WHERE POR.po_release_id = p_document_id
AND POH.po_header_id = POR.po_header_id
AND POH.min_release_amount IS NOT NULL
AND POH.min_release_amount > --
( SELECT decode ( sum( decode ( PLL2.quantity
, NULL , ( PLL2.amount - nvl(PLL2.amount_cancelled,0) )
, ( PLL2.quantity - nvl(PLL2.quantity_cancelled,0) )
)
)
, 0 , POH.min_release_amount
, sum ( decode ( PLL2.quantity
, NULL , ( PLL2.amount - nvl(PLL2.amount_cancelled,0) )
, ( ( PLL2.quantity - nvl(PLL2.quantity_cancelled,0) )
* PLL2.price_override )
)
)
)
FROM PO_LINE_LOCATIONS_GT PLL2
WHERE PLL2.po_release_id = POR.po_release_id
AND PLL2.shipment_type IN ('BLANKET', 'SCHEDULED')
);
SELECT --
sum ( decode ( PLL1.quantity
, NULL , ( PLL1.amount
- nvl(PLL1.amount_cancelled,0) )
, ( ( PLL1.quantity
- nvl(PLL1.quantity_cancelled,0) )
* PLL1.price_override ) ) )
INTO l_this_rel_amount
FROM PO_LINE_LOCATIONS_GT PLL1, PO_RELEASES_GT POR1, PO_HEADERS_GT POH
WHERE POR1.po_release_id = p_document_id
AND POR1.po_header_id = POH.po_header_id -- Bug 7188760
AND POH.amount_limit is Not Null -- Bug 7188760
AND PLL1.po_release_id = POR1.po_release_id;
SELECT --
/* FULL(POH)*/
nvl ( sum ( decode ( PLL2.quantity
, NULL , ( PLL2.amount
- nvl(PLL2.amount_cancelled,0) )
, ( ( PLL2.quantity
- nvl(PLL2.quantity_cancelled,0) )
* PLL2.price_override ) ) )
, 0 )
INTO l_previous_rel_amount
FROM PO_LINE_LOCATIONS PLL2, PO_RELEASES POR2, PO_HEADERS_GT POH
WHERE PLL2.po_release_id = POR2.po_release_id
AND POR2.po_header_id = POH.po_header_id
AND nvl(POR2.approved_flag, 'N') = 'Y'
AND POH.amount_limit is Not Null -- Bug 7188760
AND POR2.po_release_id <> p_document_id;
INSERT INTO po_online_report_text_gt (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_name)
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
0,
0,
0,
p_sequence + ROWNUM,
substr(l_textline,1,240),
'PO_SUB_REL_AMT_GRT_LIMIT_AMT'
FROM PO_HEADERS_GT POH,PO_RELEASES_GT POR
WHERE POR.po_release_id = p_document_id
AND POH.po_header_id = POR.po_header_id
AND POH.amount_limit is not null
--
--Compare it to total of current release amount and
--already released amount calculated above the INSERT statement
AND POH.amount_limit < l_total_rel_amount;
SELECT
POL.line_num,
0,
0,
POL.min_release_amount,
0
BULK COLLECT INTO
l_line_num,
l_shipment_num,
l_dist_num,
l_quantity1,
l_quantity2
FROM PO_LINES_ALL POL,PO_RELEASES_GT POR,PO_LINE_LOCATIONS_GT PLL
WHERE PLL.po_release_id = POR.po_release_id
AND PLL.po_release_id = p_document_id
AND POL.po_line_id = PLL.po_line_id
AND POL.min_release_amount is not null
AND POL.min_release_amount >
( SELECT
decode ( sum ( decode ( PLL2.quantity /*Bug 5028960 pol.quantity */
, NULL , PLL2.amount - nvl(PLL2.amount_cancelled,0)
, PLL2.quantity - nvl(PLL2.quantity_cancelled,0)
)
)
, 0 , POL.min_release_amount
, sum ( decode ( PLL2.quantity /*Bug 5028960 pol.quantity */
, NULL , PLL2.amount - nvl(PLL2.amount_cancelled,0)
, ( ( PLL2.quantity - nvl(PLL2.quantity_cancelled,0) )
* PLL2.price_override )
)
)
)
--
--Change the table in from clause from PO_LINE_LOCATIONS to PO_LINE_LOCATIONS_GT
FROM PO_LINE_LOCATIONS_GT PLL2
WHERE PLL2.po_line_id = POL.po_line_id
AND PLL2.po_release_id = POR.po_release_id
AND PLL2.shipment_type in ('BLANKET', 'SCHEDULED')
)
GROUP BY POL.line_num,POL.min_release_amount;
INSERT INTO po_online_report_text_gt(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_name)
VALUES(
p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
0,0,0,
p_sequence+l_rowCount(i),
substr(l_textline||g_delim||l_quantity1(i),1,240),
'PO_SUB_REL_SHIPAMT_LESS_MINREL');
INSERT INTO po_online_report_text_gt(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_name)
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
0,
POLL.shipment_num,
0,
p_sequence + ROWNUM,
substr(g_shipmsg||g_delim||POLL.shipment_num||g_delim||l_textline||
MTL1.uom_class||' , '||MTL2.uom_class,1,240),
'PO_SUB_UOM_CLASS_CONVERSION'
FROM MTL_UOM_CLASS_CONVERSIONS MOU, PO_LINE_LOCATIONS_GT POLL,
PO_LINES POL, MTL_UOM_CLASSES_TL MTL1,
MTL_UOM_CLASSES_TL MTL2
WHERE MOU.inventory_item_id = POL.item_id
AND (NVL(MOU.disable_date, TRUNC(SYSDATE)) + 1) < TRUNC(SYSDATE)
AND POL.po_line_id = POLL.po_line_id
AND POLL.po_release_id = p_document_id
AND MOU.from_uom_class = MTL1.uom_class
AND MOU.to_uom_class = MTL2.uom_class
AND EXISTS
(SELECT 'uom conversion exists'
FROM MTL_UNITS_OF_MEASURE MUM
WHERE POL.unit_meas_lookup_code = MUM.unit_of_measure
AND MOU.to_uom_class = MUM.uom_class);
INSERT INTO po_online_report_text_gt(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_name)
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
POL.line_num, --
PLL.shipment_num, --
0,
p_sequence + ROWNUM,
--
substr(g_linemsg||g_delim||POL.line_num||g_delim
||g_shipmsg||g_delim||PLL.shipment_num||g_delim
||l_textline,1,240),
--
'PO_SUB_ITEM_NOT_APPROVED_REL'
FROM MTL_SYSTEM_ITEMS MSI, PO_LINE_LOCATIONS_GT PLL,
PO_RELEASES_GT POR,PO_LINES POL, PO_HEADERS_GT POH,
FINANCIALS_SYSTEM_PARAMETERS FSP
WHERE POR.po_release_id = p_document_id
AND POR.po_header_id = POH.po_header_id
AND POR.po_header_id = POL.po_header_id
AND POL.po_line_id = PLL.po_line_id
AND POR.po_release_id = PLL.po_release_id
AND MSI.organization_id = PLL.SHIP_TO_ORGANIZATION_id
AND MSI.inventory_item_id = POL.item_id
AND POL.item_id is not null
AND nvl(PLL.closed_code,'OPEN') <> 'FINALLY CLOSED'
AND nvl(POL.cancel_flag,'N') = 'N'
AND nvl(PLL.cancel_flag,'N') = 'N'
AND nvl(MSI.must_use_approved_vendor_flag,'N') = 'Y'
AND not exists
(SELECT 1
FROM PO_APPROVED_SUPPLIER_LIS_VAL_V ASL, PO_ASL_STATUS_RULES ASR
WHERE ASL.using_organization_id in (PLL.ship_to_organization_id, -1)
AND ASL.vendor_id = POH.vendor_id
AND nvl(ASL.vendor_site_id, POH.vendor_site_id) = POH.vendor_site_id
AND ASL.item_id = POL.item_id
AND ASL.asl_status_id = ASR.status_id
AND ASR.business_rule = '1_PO_APPROVAL'
AND ASR.allow_action_flag = 'Y' --Bug5597639
UNION ALL
SELECT 1
FROM PO_APPROVED_SUPPLIER_LIS_VAL_V ASL, PO_ASL_STATUS_RULES ASR
WHERE ASL.using_organization_id in (PLL.ship_to_organization_id , -1)
AND ASL.vendor_id = POH.vendor_id
AND nvl(ASL.vendor_site_id, POH.vendor_site_id) = POH.vendor_site_id
AND ASL.item_id is NULL
AND not exists
(SELECT ASL1.ASL_ID
FROM PO_APPROVED_SUPPLIER_LIS_VAL_V ASL1
WHERE ASL1.ITEM_ID = POL.item_id
AND ASL1.using_organization_id in (PLL.ship_to_organization_id, -1))
AND ASL.category_id in
(SELECT MIC.category_id
FROM MTL_ITEM_CATEGORIES MIC
WHERE MIC.inventory_item_id = POL.item_id
AND MIC.organization_id = PLL.ship_to_organization_id)
AND ASL.asl_status_id = ASR.status_id
AND ASR.business_rule = '1_PO_APPROVAL'
AND ASR.allow_action_flag = 'Y') ; --Bug5597639
INSERT INTO po_online_report_text_gt(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_name)
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
POL.line_num, --
PLL.shipment_num, --
0,
p_sequence + ROWNUM,
--
substr(g_linemsg||g_delim||POL.line_num||g_delim
||g_shipmsg||g_delim||PLL.shipment_num||g_delim
||l_textline,1,240),
--
'PO_SUB_ITEM_ASL_DEBARRED_REL'
FROM PO_LINE_LOCATIONS_GT PLL,
PO_RELEASES_GT POR,PO_LINES POL, PO_HEADERS_GT POH,
FINANCIALS_SYSTEM_PARAMETERS FSP
WHERE POR.po_release_id = p_document_id
AND POR.po_header_id = POH.po_header_id
AND POR.po_header_id = POL.po_header_id
AND POL.po_line_id = PLL.po_line_id
AND POR.po_release_id = PLL.po_release_id
AND nvl(PLL.closed_code,'OPEN') <> 'FINALLY CLOSED'
AND nvl(POL.cancel_flag,'N') = 'N'
AND nvl(PLL.cancel_flag,'N') = 'N'
AND exists
(SELECT 1
FROM PO_APPROVED_SUPPLIER_LIS_VAL_V ASL, PO_ASL_STATUS_RULES ASR,
MTL_SYSTEM_ITEMS MSI --Bug5597639
WHERE ASL.using_organization_id in (PLL.ship_to_organization_id, -1)
/*Bug5553138 Adding the below three conditions */
AND MSI.organization_id = FSP.inventory_organization_id
AND MSI.inventory_item_id = POL.item_id
AND POL.item_id is not null
AND ASL.vendor_id = POH.vendor_id
AND nvl(ASL.vendor_site_id, POH.vendor_site_id) = POH.vendor_site_id
AND ASL.item_id = POL.item_id
AND ASL.asl_status_id = ASR.status_id
AND ASR.business_rule = '1_PO_APPROVAL'
AND ASR.allow_action_flag <> 'Y' --Bug5597639
UNION ALL
SELECT 1
FROM PO_APPROVED_SUPPLIER_LIS_VAL_V ASL, PO_ASL_STATUS_RULES ASR
WHERE ASL.using_organization_id in (PLL.ship_to_organization_id , -1)
AND ASL.vendor_id = POH.vendor_id
AND nvl(ASL.vendor_site_id, POH.vendor_site_id) = POH.vendor_site_id
AND ASL.item_id is NULL
AND ASL.category_id = POL.category_id --Bug5597639
AND ASL.asl_status_id = ASR.status_id
AND ASR.business_rule = '1_PO_APPROVAL'
AND ASR.allow_action_flag <> 'Y' ); --Bug5597639
INSERT INTO po_online_report_text_gt(
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_name
)
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
POL.line_num,
PLL.shipment_num,
0,
p_sequence + ROWNUM,
substr(g_linemsg||g_delim||POL.line_num||g_delim
||g_shipmsg||g_delim||PLL.shipment_num||g_delim
||l_textline,1,240),
'PO_ATO_ITEM_NA'
FROM po_lines POL,
po_line_locations_gt PLL,
financials_system_parameters FSP,
mtl_system_items MSI
WHERE PLL.po_release_id = p_document_id
AND PLL.po_line_id = POL.po_line_id
AND POL.item_id is not null
AND nvl(POL.cancel_flag, 'N') = 'N' --Bug5353423
AND nvl(POL.closed_code, 'OPEN') <> 'FINALLY CLOSED' --Bug5353423
AND POL.item_id = MSI.inventory_item_id
AND MSI.organization_id = FSP.inventory_organization_id
AND MSI.bom_item_type in (1,2);
INSERT INTO po_online_report_text_gt(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_name)
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
0,
pll.shipment_num,
0,
p_sequence + ROWNUM,
substr(g_shipmsg||g_delim||pll.shipment_num||g_delim||l_textline,1,240),
'PO_ALL_NO_ITEM'
from po_releases_gt por,po_lines pl,po_line_locations_gt pll,mtl_system_items itm,po_line_types_b plt
where itm.inventory_item_id = pl.item_id
and pl.item_id is not null
and pl.po_line_id = pll.po_line_id
and itm.organization_id = pll.ship_to_organization_id
and itm.purchasing_enabled_flag = 'N'
and pll.po_release_id = por.po_release_id
and por.po_release_id = p_document_id
and pll.po_release_id is not null
and pl.line_type_id = plt.line_type_id
and nvl(plt.outside_operation_flag,'N')=nvl(itm.outside_operation_flag,'N')
and (pll.creation_date >= nvl(por.approved_date,pll.creation_date));
INSERT INTO po_online_report_text_gt(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_name)
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
0,
0,
0,
p_sequence + ROWNUM,
substr(l_textline,1,240),
'PO_ON_HOLD_CANNOT_APPROVE'
FROM PO_RELEASES_GT POR
WHERE POR.po_release_id = p_document_id
AND nvl(POR.HOLD_FLAG,'N') = 'Y';
select poh.po_header_id,poh.vendor_id, poh.vendor_site_id, poh.vendor_contact_id
into l_agreement_id, l_vendor_id,l_vendor_site_id,l_vendor_contact_id
from po_headers_gt poh, po_releases_gt por
where poh.po_header_id = por.po_header_id
and por.po_release_id = p_document_id;
INSERT INTO po_online_report_text_gt(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_name)
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
0,
0,
0,
p_sequence + ROWNUM,
substr(l_textline,1,240),
'PO_PDOI_INVALID_VENDOR'
FROM dual
where not exists (select 'Y'
from PO_HEADERS_GT POH, po_vendors pov
WHERE POH.po_header_id = l_agreement_id
AND pov.vendor_id = poh.vendor_id
AND pov.enabled_flag = 'Y'
AND SYSDATE BETWEEN nvl(pov.start_date_active, SYSDATE-1)
AND nvl(pov.end_date_active, SYSDATE+1));
INSERT INTO po_online_report_text_gt(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_name)
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
0,
0,
0,
p_sequence + ROWNUM,
substr(l_textline,1,240),
'PO_PDOI_INVALID_VENDOR_SITE'
FROM dual
where not exists (select 'Y'
from PO_HEADERS_GT POH, po_vendor_sites povs
WHERE POH.po_header_id = l_agreement_id
AND povs.vendor_site_id = poh.vendor_site_id
AND nvl(povs.rfq_only_site_flag,'N') <> 'Y'
AND povs.purchasing_site_flag = 'Y'
AND SYSDATE < nvl(povs.inactive_date, SYSDATE + 1));
INSERT INTO po_online_report_text_gt(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_name)
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
0,
0,
0,
p_sequence + ROWNUM,
substr(l_textline,1,240),
'PO_PDOI_INVALID_VDR_CNTCT'
FROM dual
WHERE NOT EXISTS (SELECT 'Y'
FROM PO_VENDOR_CONTACTS pvc, po_headers_gt poh
WHERE POH.po_header_id = l_agreement_id
AND pvc.vendor_contact_id =poh.vendor_contact_id
AND SYSDATE < nvl(pvc.inactive_date, SYSDATE+1));
INSERT INTO po_online_report_text_gt
(
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_name,
message_type
)
VALUES
(
p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
po_tax_interface_pvt.G_TAX_ERRORS_TBL.line_num(i),
po_tax_interface_pvt.G_TAX_ERRORS_TBL.shipment_num(i),
po_tax_interface_pvt.G_TAX_ERRORS_TBL.distribution_num(i),
p_sequence + ROWNUM,
l_tax_message || po_tax_interface_pvt.G_TAX_ERRORS_TBL.message_text(i),
'PO_TAX_CALCULATION_FAILED',
'E'
);
INSERT INTO po_online_report_text_gt (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_name)
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
0,
PLL.shipment_num,
0,
p_sequence + ROWNUM,
substr (g_shipmsg||g_delim||PLL.shipment_num||g_delim||l_textline,1,240),
'PO_SUB_REL_SHIP_INV_MATCH_NE_R'
FROM PO_RELEASES_GT POR,
PO_LINE_LOCATIONS_GT PLL
WHERE POR.po_release_id = PLL.po_release_id
AND POR.po_release_id = p_document_id
AND Nvl(PLL.LCM_FLAG,'N') = 'Y'
AND Nvl(PLL.match_option,'P') <> 'R';
INSERT INTO po_online_report_text_gt (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_name)
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
0,
PLL.shipment_num,
POD.distribution_num,
p_sequence + ROWNUM,
substr (g_shipmsg||g_delim||PLL.shipment_num||g_delim||g_distmsg||g_delim||
POD.distribution_num||g_delim||l_textline, 1,240),
'PO_SUB_REL_DIST_DEST_TYPE_NE_I'
FROM PO_RELEASES_GT POR,
PO_LINE_LOCATIONS_GT PLL,
PO_DISTRIBUTIONS_GT POD
WHERE POR.po_release_id = POD.po_release_id
AND POD.line_location_id = PLL.line_location_id
AND POR.po_release_id = p_document_id
AND Nvl(POD.LCM_FLAG,'N') = 'Y'
AND POD.DESTINATION_TYPE_CODE <> 'INVENTORY';
* p_online_report_id: Id used to INSERT INTO online_report_text table
* p_user_id: User performing the action
* p_login_id: Last update login_id
* IN OUT PARAMETERS
* p_sequence: Sequence number of last reported error
* Modifies: Inserts error msgs in online_report_text_gt table, uses
* global_temp tables for processing
* Effects: This procedure runs the document submission checks for PO and
* RELEASES. This procedure compares the price of the PO or Release
* Shipment to the price of the Requisition Line. The Shipment
* Price should be within the tolerance of the Requisition Line
* Returns:
* p_sequence: This parameter contains the current count of number of error
* messages inserted
*/
PROCEDURE check_po_rel_reqprice(p_document_type IN VARCHAR2,
p_document_id IN NUMBER,
p_online_report_id IN NUMBER,
p_user_id IN NUMBER,
p_login_id IN NUMBER,
p_sequence IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2) IS
l_textline po_online_report_text.text_line%TYPE := NULL;
** Setup the PO select cursor
** Select shipment price and convert it to base currency.
** this is done by taking the distribution rate and applying
** it evenly over all distributions. Additionally get the
** shipment unit of measure, quantity, and item_id to be
** passed to the UomC function.
*/
/*Bug4302950 :The shipments were updated with wrong price when supplier submits
change request from ISP to split shipment quantity between two individual shipments.
As the ISP doesnot handle change in distributions,replacing the shipments
quantity with the sum of the distributions quantity in the calculation
of price override so that the shipment price will reflect the correct value.*/
CURSOR po_shipment_cursor (p_document_id NUMBER) IS
SELECT nvl(max(POLL.price_override ) *
sum(decode(plt.order_type_lookup_code,'AMOUNT',1,nvl(POD.rate,1))*
(POD.quantity_ordered -
nvl(POD.quantity_cancelled, 0))) /
/* (max(POLL.quantity) -
nvl(max(POLL.quantity_cancelled),0)), -1) Price, */ --Bug4302950
(sum(POD.quantity_ordered -
nvl(POD.quantity_cancelled,0))), -1) Price,
POL.unit_meas_lookup_code uom,
nvl(POLL.shipment_num,0) ship_num,
nvl(POL.line_num,0) line_num,
nvl(POLL.quantity,0) quantity,
nvl(POL.item_id,-1) item_id,
nvl( POLL.line_location_id,0) line_loc_id
FROM PO_LINE_LOCATIONS_GT POLL,
PO_LINE_TYPES_B PLT, -- bug3413891
PO_LINES_GT POL,
PO_DISTRIBUTIONS_GT POD
WHERE POLL.po_line_id = POL.po_line_id
AND POLL.line_location_id = POD.line_location_id
AND POLL.po_header_id = p_document_id
AND POL.line_type_id = PLT.line_type_id
AND nvl(POLL.cancel_flag,'N') <> 'Y'
AND nvl(POLL.closed_code,'OPEN') <> 'FINALLY CLOSED'
AND POLL.shipment_type in ('PLANNED', 'STANDARD')
GROUP BY POL.unit_meas_lookup_code, nvl(POLL.shipment_num,0),
nvl(POL.line_num,0), nvl(POLL.quantity,0),
nvl(POL.item_id,-1), POLL.price_override,
nvl(POLL.line_location_id,0);
/* Setup the Complex Work PO select cursor
** Select Complex Work PO Line information and the
** line loc ID for the first STANDARD pay item for
** that line
*/
CURSOR po_pay_item_cursor (p_document_id NUMBER) IS
SELECT (POL.unit_price
* (sum(POD.rate
* (POD.quantity_ordered - nvl(POD.quantity_cancelled,0)) ))
/ POLL.quantity - nvl(POLL.quantity_cancelled, 0)
) price,
POL.unit_meas_lookup_code uom,
POLL.shipment_num ship_num,
POL.line_num line_num,
POL.quantity quantity,
nvl(POL.item_id,-1) item_id,
POLL.line_location_id line_loc_id
FROM PO_LINE_LOCATIONS_GT POLL,
PO_LINES_GT POL,
PO_DISTRIBUTIONS_GT POD
WHERE POL.po_header_id = p_document_id
AND POD.line_location_id = POLL.line_location_id
AND POLL.line_location_id =
(SELECT min(POLL2.line_location_id)
FROM PO_LINE_LOCATIONS_GT POLL2
WHERE POLL2.po_line_id = POL.po_line_id
AND POLL2.shipment_type = 'STANDARD'
)
GROUP BY POL.unit_price, POLL.quantity, nvl(POLL.quantity_cancelled, 0),
POL.unit_meas_lookup_code,
POLL.shipment_num,
POL.line_num,
POL.quantity,
nvl(POL.item_id,-1),
POLL.line_location_id;
** Setup the Release select cursor
** Select shipment price and convert it to base currency.
** this is done by taking the distribution rate and applying
** it evenly over all distributions. Additionally get the
** shipment unit of measure, quantity, and item_id to be
** passed to the UomC function. Get the shipment_num and
** line_num to be passed to the pooinsingle function.
*/
CURSOR rel_shipment_cursor (p_document_id NUMBER) IS
SELECT /*+ FULL(POLL) */ -- bug3413891
nvl(max(POLL.price_override) *
sum(decode(plt.order_type_lookup_code,'AMOUNT',1,nvl(POD.rate,1))*
(POD.quantity_ordered -
nvl(POD.quantity_cancelled, 0))) /
/* (max(POLL.quantity) -
nvl(max(POLL.quantity_cancelled),0)), -1) Price, */ --Bug4302950
(sum(POD.quantity_ordered -
nvl(POD.quantity_cancelled,0))), -1) Price,
POL.unit_meas_lookup_code uom,
nvl(POLL.shipment_num,0) ship_num,
nvl(POL.line_num,0) line_num,
nvl(POLL.quantity,0) quantity,
nvl(POL.item_id,0) item_id,
nvl( POLL.line_location_id,0) line_loc_id
FROM PO_LINE_LOCATIONS_GT POLL,
PO_LINE_TYPES_B PLT, -- bug3413891
PO_LINES POL,
PO_DISTRIBUTIONS_GT POD --
WHERE POLL.po_line_id = POL.po_line_id
AND POLL.line_location_id = POD.line_location_id
AND POLL.po_release_id = p_document_id
AND POL.line_type_id = PLT.line_type_id
AND nvl(POLL.cancel_flag,'N') <> 'Y'
AND nvl(POLL.closed_code,'OPEN') <> 'FINALLY CLOSED'
GROUP BY POL.unit_meas_lookup_code, nvl(POLL.shipment_num,0),
nvl(POL.line_num,0), nvl(POLL.quantity,0),
nvl(POL.item_id,0), POLL.price_override,
nvl(POLL.line_location_id,0);
SELECT min(PRL.unit_price),
PRL.unit_meas_lookup_code,
min(POL.line_num),
min(POLL.shipment_num),
min(PRL.org_id) --
FROM PO_REQUISITION_LINES_ALL PRL, --
PO_LINE_LOCATIONS_GT POLL,
PO_LINES POL
WHERE PRL.line_location_id = POLL.line_location_id
AND POLL.line_location_id = p_line_location_id
AND PRL.unit_price >= 0
AND POLL.po_line_id = POL.po_line_id
GROUP BY PRL.unit_meas_LOOKUP_code;
SELECT min(PRL.unit_price),
PRL.unit_meas_lookup_code,
sum(PD.quantity_ordered),
min(POL.line_num),
min(POLL.shipment_num),
min(PRL.org_id) --
FROM PO_REQUISITION_LINES_ALL PRL, --
PO_LINE_LOCATIONS_GT POLL,
PO_LINES POL,
PO_DISTRIBUTIONS PD,
PO_REQ_DISTRIBUTIONS_ALL PRD --
WHERE POLL.line_location_id = p_line_location_id
AND POLL.po_line_id = POL.po_line_id
AND PRL.unit_price >= 0
AND POLL.line_location_id = PD.line_location_id
AND PD.req_distribution_id = PRD.distribution_id
AND PRD.requisition_line_id = PRL.requisition_line_id
GROUP BY PRL.requisition_line_id, PRL.unit_meas_lookup_code;
SELECT nvl(enforce_price_change_allowance, 'N'),
nvl(enforce_price_change_amount, 'N'),
nvl(price_change_amount, -1)
INTO l_enforce_price_tolerance,
l_enforce_price_amount,
l_amount_tolerance
FROM po_system_parameters;
SELECT FND.currency_code, nvl(FND.extended_precision,5)
INTO l_pou_func_curr, l_pou_func_curr_ext_precn
FROM fnd_currencies FND, financials_system_parameters FSP,
gl_sets_of_books SOB
WHERE FSP.set_of_books_id = SOB.set_of_books_id
AND SOB.currency_code = FND.currency_code;
SELECT POH.currency_code, nvl(trunc(POH.rate_date), trunc(POLL.creation_date))
INTO l_po_curr, l_rate_date
FROM PO_HEADERS POH, PO_LINE_LOCATIONS_GT POLL
WHERE POLL.line_location_id = l_line_location_id(shipment_line)
AND POLL.po_header_id = POH.po_header_id;
SELECT SOB.currency_code
INTO l_req_ou_func_curr
FROM financials_system_params_all FSP, gl_sets_of_books SOB
WHERE FSP.set_of_books_id = SOB.set_of_books_id
AND NVL(FSP.org_id, -99) = NVL(l_requesting_org_id(req_line),-99);
SELECT default_rate_type
INTO l_rate_type
FROM po_system_parameters;
SELECT NVL(MSI.price_tolerance_percent/100,
NVL(POSP.price_change_allowance/100,-1))
INTO l_price_tolerance_allowed
FROM MTL_SYSTEM_ITEMS MSI,
PO_SYSTEM_PARAMETERS POSP,
FINANCIALS_SYSTEM_PARAMETERS FSP
WHERE msi.inventory_item_id(+) = l_item_id(shipment_line)
AND MSI.organization_id(+) = FSP.inventory_organization_id;
** insert into the Online Report Text Table.
**
** The following formula will cost precision erro when the
** increase equals to the tolerance.
** Patched as part of bug 432746.
**
**if ((((ship_price_in_base_curr * rate) /
** req_line_unit_price[i]) -1) <= tolerance)
*/
/* Bug 638073
the formula for tolerance check should be
ship_price_in_base_curr/ req_line_unit_pric e[i] *rate
since rate is the conversion from shipment uom to req uom
*/
/* svaidyan 09/10/98 726568 Modified the price tolerance
to check against tolerance + 1.000001. This is because,
the reqs sourced to a blanket store the unit price rounded
to 5 decimal places and hence we compare only upto the 5th
decimal place.
*/
/* Bug 3262304, 3266272 mbhargav Using the req price to
the ext_precn of the currency. Also replacing the division
by multiplication on the other side
IF (((l_ship_price_ext_precn) /
(l_req_line_unit_price(req_line) *
l_shipment_to_req_rate ))
> (l_price_tolerance_allowed + 1.000001))
THEN
*/
--
IF (l_ship_price_ext_precn >
( (l_req_line_price_ext_precn * l_shipment_to_req_rate )
* (l_price_tolerance_allowed + 1.000001)
)
) THEN
--
l_progress := '009';
INSERT into po_online_report_text_gt(
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_name)
VALUES ( p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
l_po_req_line_num(req_line),
l_po_req_ship_num(req_line),
0,
p_sequence +1,
substr(g_linemsg||g_delim||
l_po_req_line_num(req_line)||g_delim||
g_shipmsg||g_delim||l_po_req_ship_num(req_line)
||g_delim||l_textline,1,240),
'PO_SUB_REQ_PRICE_TOL_EXCEED');
SELECT SOB.currency_code
INTO l_req_ou_func_curr
FROM financials_system_params_all FSP, gl_sets_of_books SOB
WHERE FSP.set_of_books_id = SOB.set_of_books_id
AND NVL(FSP.org_id, -99) = NVL(l_requesting_org_id(req_line),-99);
SELECT default_rate_type
INTO l_rate_type
FROM po_system_parameters;
INSERT into po_online_report_text_gt(
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_name)
VALUES ( p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
l_po_req_line_num(req_line),
l_po_req_ship_num(req_line),
0,
p_sequence +1,
substr(g_linemsg||g_delim||
l_po_req_line_num(req_line)||g_delim||
g_shipmsg||g_delim||l_po_req_ship_num(req_line)
||g_delim||l_textline,1,240),
'PO_SUB_REQ_AMT_TOL_EXCEED');
* p_online_report_id: Id used to INSERT INTO online_report_text table
* p_user_id: User performing the action
* p_login_id: Last update login_id
* IN OUT PARAMETERS
* p_sequence: Sequence number of last reported error
* Modifies: Inserts error msgs in online_report_text_gt table, uses
* global_temp tables for processing
* Effects: This procedure runs the document submission checks for HEADERS
* of POs and PAs
* Returns:
* p_sequence: This parameter contains the current count of number of error
* messages inserted
*/
PROCEDURE check_po_pa_header(p_document_id IN NUMBER,
p_online_report_id IN NUMBER,
p_user_id IN NUMBER,
p_login_id IN NUMBER,
p_sequence IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2) IS
l_textline po_online_report_text.text_line%TYPE := NULL;
INSERT INTO po_online_report_text_gt(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_name)
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
0,
0,
0,
p_sequence + ROWNUM,
substr(l_textline,1,240),
'PO_SUB_VENDOR_ON_HOLD'
FROM PO_HEADERS_GT POH, PO_VENDORS POV, PO_SYSTEM_PARAMETERS PSP
WHERE POV.vendor_id = POH.vendor_id
AND POH.po_header_id = p_document_id
AND nvl(PSP.ENFORCE_VENDOR_HOLD_FLAG,'N') = 'Y'
AND nvl(POV.hold_flag,'N') = 'Y';
SELECT POH.vendor_id,
POH.vendor_site_id,
POH.vendor_contact_id,
POH.ship_to_location_id,
POH.bill_to_location_id,
POH.currency_code,
SOB.currency_code,
POH.rate_type,
POH.rate,
POH.rate_date
INTO l_vendor_id,
l_vendor_site_id,
l_vendor_contact_id,
l_ship_to_location_id,
l_bill_to_location_id,
l_currency_code,
l_sob_currency_code,
l_rate_type,
l_rate,
l_rate_date
FROM PO_HEADERS_GT POH,
GL_SETS_OF_BOOKS SOB,
FINANCIALS_SYSTEM_PARAMETERS FSP
WHERE POH.po_header_id = p_document_id
AND SOB.set_of_books_id = FSP.set_of_books_id;
INSERT INTO po_online_report_text_gt(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_name)
VALUES (p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
0,
0,
0,
p_sequence + 1,
substr(l_textline,1,240),
'PO_SUB_SYSTEM_SETUP');
INSERT INTO po_online_report_text_gt(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_name)
VALUES (p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
0,
0,
0,
p_sequence + 1,
substr(l_textline,1,240),
'PO_SUB_ENTER_VENDOR');
INSERT INTO po_online_report_text_gt(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_name)
VALUES (p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
0,
0,
0,
p_sequence + 1,
substr(l_textline,1,240),
'PO_SUB_ENTER_VENDOR_SITE');
INSERT INTO po_online_report_text_gt(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_name)
VALUES (p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
0,
0,
0,
p_sequence + 1,
substr(l_textline,1,240),
'PO_SUB_NO_SHIP_TO_LOC_ID');
SELECT hlt.location_code
INTO l_invalid_location
FROM hr_locations_all hla,
hr_locations_all_tl hlt
WHERE hla.location_id = l_ship_to_location_id
AND NVL(TRUNC(hla.inactive_date), TRUNC(SYSDATE)+1 ) <= TRUNC(SYSDATE)
AND hlt.location_id=hla.location_id
AND hlt.language=USERENV('LANG');
INSERT INTO po_online_report_text_gt(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_name)
VALUES
(
p_online_report_id,
p_login_id,
p_user_id,
SYSDATE,
p_user_id,
SYSDATE,
0,
0,
0,
p_sequence + 1,
substr(PO_CORE_S.get_translated_text
( 'PO_SUB_INVALID_SHIP_TO_LOC',
'SHIP_TO_LOC',
l_invalid_location),1,240),
'PO_SUB_INVALID_SHIP_TO_LOC'
);
INSERT INTO po_online_report_text_gt(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_name)
VALUES (p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
0,
0,
0,
p_sequence + 1,
substr(l_textline,1,240),
'PO_SUB_NO_BILL_TO_LOC_ID');
SELECT hlt.location_code
INTO l_invalid_location
FROM hr_locations_all hla,
hr_locations_all_tl hlt
WHERE hla.location_id = l_bill_to_location_id
AND NVL(TRUNC(hla.inactive_date), TRUNC(SYSDATE)+1 ) <= TRUNC(SYSDATE)
AND hlt.location_id=hla.location_id
AND hlt.language=USERENV('LANG');
INSERT INTO po_online_report_text_gt(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_name)
VALUES
(
p_online_report_id,
p_login_id,
p_user_id,
SYSDATE,
p_user_id,
SYSDATE,
0,
0,
0,
p_sequence + 1,
substr(PO_CORE_S.get_translated_text('PO_SUB_INVALID_BILL_TO_LOC','BILL_TO_LOC',l_invalid_location),1,240),
'PO_SUB_INVALID_BILL_TO_LOC'
);
INSERT INTO po_online_report_text_gt(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_name)
VALUES (p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
0,
0,
0,
p_sequence + 1,
substr(l_textline,1,240),
'PO_SUB_NO_CURRENCY_CODE');
INSERT INTO po_online_report_text_gt(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_name)
VALUES (p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
0,
0,
0,
p_sequence + 1,
substr(l_textline,1,240),
'PO_SUB_NO_RATE_TYPE');
INSERT INTO po_online_report_text_gt
( 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_name
)
SELECT p_online_report_id
, p_login_id
, p_user_id
, sysdate
, p_user_id
, sysdate
, NULL
, NULL
, NULL
, p_sequence + ROWNUM
, l_textline
, 'PO_SUB_USER_RATE_TYPE'
FROM dual
WHERE exists ( SELECT 'Rate-based lines exist'
FROM po_lines_gt POL
, po_line_types_b PLT
WHERE p_document_id = POL.po_header_id
AND POL.line_type_id = PLT.line_type_id
AND PLT.order_type_lookup_code = 'RATE'
);
INSERT INTO po_online_report_text_gt(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_name)
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
0,
0,
0,
p_sequence + ROWNUM,
substr(l_textline,1,240),
'PO_ON_HOLD_CANNOT_APPROVE'
FROM PO_HEADERS_GT POH
WHERE POH.po_header_id = p_document_id
AND nvl(POH.USER_HOLD_FLAG,'N') = 'Y';
INSERT INTO po_online_report_text_gt(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_name)
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
0,
0,
0,
p_sequence + ROWNUM,
substr(l_textline,1,240),
'PO_PDOI_INVALID_VENDOR'
FROM dual
where not exists (select 'Y'
from PO_HEADERS_GT POH, po_vendors pov
WHERE POH.po_header_id = p_document_id
AND pov.vendor_id = poh.vendor_id
AND pov.enabled_flag = 'Y'
AND SYSDATE BETWEEN nvl(pov.start_date_active, SYSDATE-1)
AND nvl(pov.end_date_active, SYSDATE+1));
INSERT INTO po_online_report_text_gt(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_name)
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
0,
0,
0,
p_sequence + ROWNUM,
substr(l_textline,1,240),
'PO_PDOI_INVALID_VENDOR_SITE'
FROM dual
where not exists (select 'Y'
from PO_HEADERS_GT POH, po_vendor_sites povs
WHERE POH.po_header_id = p_document_id
AND povs.vendor_site_id = poh.vendor_site_id
AND nvl(povs.rfq_only_site_flag,'N') <> 'Y'
AND povs.purchasing_site_flag = 'Y'
AND SYSDATE < nvl(povs.inactive_date, SYSDATE + 1));
INSERT INTO po_online_report_text_gt(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_name)
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
0,
0,
0,
p_sequence + ROWNUM,
substr(l_textline,1,240),
'PO_PDOI_INVALID_VDR_CNTCT'
FROM dual
WHERE NOT EXISTS (SELECT 'Y'
FROM PO_VENDOR_CONTACTS pvc, po_headers_gt poh
WHERE POH.po_header_id = p_document_id
AND pvc.vendor_contact_id =poh.vendor_contact_id
AND SYSDATE < nvl(pvc.inactive_date, SYSDATE+1));
* p_online_report_id: Id used to INSERT INTO online_report_text table
* p_user_id: User performing the action
* p_login_id: Last update login_id
* p_check_asl: Determines whether to perform ASL checks...
* PO_SUB_ITEM_NOT_APPROVED, PO_SUB_ITEM_ASL_DEBARRED
* IN OUT PARAMETERS
* p_sequence: Sequence number of last reported error
* Modifies: Updates PO_DISTRIBUTIONS table with RATE information.
* Inserts error msgs in online_report_text_gt table, uses
* global_temp tables for processing
* Effects: This procedure runs the document submission checks for POs
* Returns:
* p_sequence: This parameter contains the current count of number of error
* messages inserted
*/
PROCEDURE check_po(p_document_id IN NUMBER,
p_online_report_id IN NUMBER,
p_user_id IN NUMBER,
p_login_id IN NUMBER,
p_sequence IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2) IS
l_textline po_online_report_text.text_line%TYPE := NULL;
INSERT INTO po_online_report_text_gt(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_name)
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
0, 0, 0,
p_sequence + ROWNUM,
substr(l_textline,1,240),
'PO_SUB_HEADER_NO_LINES'
FROM PO_HEADERS_GT POH
WHERE POH.po_header_id = p_document_id AND
NOT EXISTS (SELECT 'Lines Exist'
FROM PO_LINES_GT POL
WHERE POL.po_header_id = POH.po_header_id
--Bug 3289638 Check for any line to exist irrespective of cancel_flag
--AND nvl(POL.cancel_flag,'N') = 'N'
);
INSERT INTO po_online_report_text_gt(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_name)
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
POL.line_num,
0,
0,
p_sequence + ROWNUM,
substr(g_linemsg||g_delim||POL.line_num||g_delim||l_textline,1,240),
'PO_SUB_LINE_NO_SHIP'
FROM PO_LINES_GT POL
WHERE POL.po_header_id = p_document_id AND
nvl(POL.cancel_flag,'N') = 'N' AND
nvl(POL.closed_code,'OPEN') <> 'FINALLY CLOSED' AND
NOT EXISTS (SELECT 'Shipments Exist'
FROM PO_LINE_LOCATIONS_GT PLL
WHERE PLL.po_line_id = POL.po_line_id AND
PLL.shipment_type in ('STANDARD','PLANNED'));
INSERT INTO po_online_report_text_gt(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_name)
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
POL.line_num,
PLL.shipment_num,
0,
p_sequence + ROWNUM,
substr(g_linemsg||g_delim||POL.line_num||g_delim||
g_shipmsg||g_delim||PLL.shipment_num||g_delim||l_textline,1,240),
'PO_SUB_SHIP_NO_DIST'
FROM PO_LINE_LOCATIONS_GT PLL,PO_LINES_GT POL
WHERE PLL.po_line_id = POL.po_line_id AND
PLL.po_header_id = p_document_id AND
nvl(PLL.cancel_flag, 'N') = 'N' AND
nvl(POL.cancel_flag, 'N') = 'N' AND
nvl(PLL.closed_code, 'OPEN') <> 'FINALLY CLOSED' AND
PLL.shipment_type in ('STANDARD', 'PLANNED', 'PREPAYMENT') --
AND NOT EXISTS (SELECT 'Distribution Exists'
FROM PO_DISTRIBUTIONS_GT POD
WHERE POD.line_location_id = PLL.line_location_id);
INSERT INTO po_online_report_text_gt(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_name)
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
POL.line_num,
PLL.shipment_num,
POD.distribution_num,
p_sequence + ROWNUM,
substr(g_linemsg||g_delim||POL.line_num||g_delim
||g_shipmsg||g_delim||PLL.shipment_num||g_delim
||g_distmsg||g_delim||POD.distribution_num||g_delim
||l_textline,1,240),
'PO_SUB_DIST_RATE_NULL'
FROM PO_DISTRIBUTIONS_GT POD,PO_LINE_LOCATIONS_GT PLL,PO_LINES_GT POL,
PO_HEADERS_GT POH,GL_SETS_OF_BOOKS SOB,FINANCIALS_SYSTEM_PARAMETERS FSP
WHERE POD.po_header_id = POH.po_header_id
AND POD.line_location_id = PLL.line_location_id
AND PLL.po_line_id = POL.po_line_id
AND POH.po_header_id = p_document_id
AND nvl(PLL.cancel_flag,'N') = 'N'
AND nvl(POL.cancel_flag,'N') = 'N'
AND nvl(PLL.closed_code,'OPEN') <> 'FINALLY CLOSED'
AND PLL.shipment_type in ('STANDARD', 'PLANNED')
AND SOB.set_of_books_id = FSP.set_of_books_id
AND SOB.currency_code <> POH.currency_code
AND (POD.rate is null
OR (POH.rate_type <> 'User'
AND POD.rate_date is null));
INSERT INTO po_online_report_text_gt(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_name)
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
POL.line_num,
PLL.shipment_num,
POD.distribution_num,
p_sequence + ROWNUM,
substr(g_linemsg||g_delim||POL.line_num||g_delim
||g_shipmsg||g_delim||PLL.shipment_num||g_delim
||g_distmsg||g_delim||POD.distribution_num||g_delim
||l_textline,1,240),
'PO_SUB_DIST_RATE_NOT_NULL'
FROM PO_DISTRIBUTIONS_GT POD,PO_LINE_LOCATIONS_GT PLL,PO_LINES_GT POL,
PO_HEADERS_GT POH,GL_SETS_OF_BOOKS SOB,FINANCIALS_SYSTEM_PARAMETERS FSP
WHERE POD.po_header_id = POH.po_header_id
AND POD.line_location_id = PLL.line_location_id
AND PLL.po_line_id = POL.po_line_id
AND POH.po_header_id = p_document_id
AND nvl(PLL.cancel_flag,'N') = 'N'
AND nvl(POL.cancel_flag,'N') = 'N'
AND nvl(PLL.closed_code,'OPEN') <> 'FINALLY CLOSED'
AND PLL.shipment_type in ('STANDARD', 'PLANNED')
AND SOB.set_of_books_id = FSP.set_of_books_id
AND SOB.currency_code = POH.currency_code
AND POD.rate is not null;
INSERT INTO po_online_report_text_gt(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_name)
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
0,
0,
0,
p_sequence + ROWNUM,
substr(l_textline,1,240),
'PO_SUB_STD_CONTRACT_AMT_LIMIT'
FROM DUAL;
INSERT INTO po_online_report_text_gt(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_name)
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
POL.line_num,
0,
0,
p_sequence + ROWNUM,
substr(g_linemsg||g_delim||POL.line_num||g_delim||l_textline,1,240),
'PO_SUB_REF_UNAPPROVED_CONTRACT'
FROM PO_LINES_GT POL,
PO_HEADERS_ALL POC -- : use all table
WHERE POL.po_header_id = p_document_id
AND POL.contract_id = POC.po_header_id --
AND POC.type_lookup_code = 'CONTRACT'
/* R12 GCPA
+ If Profile ALLOW_REFERENCING_CPA_UNDER_AMENDMENT is Y, then we can refer any Contract Which is approved Once
+ Else Contract should be in APPROVED state */
AND ( (NVL(FND_PROFILE.VALUE('ALLOW_REFERENCING_CPA_UNDER_AMENDMENT'),'N') = 'Y'
AND POC.Approved_Date Is Null
)
or ( NVL(FND_PROFILE.VALUE('ALLOW_REFERENCING_CPA_UNDER_AMENDMENT'),'N') = 'N'
and nvl(POC.APPROVED_FLAG,'N') <> 'Y')
)
AND NVL(POC.user_hold_flag, 'N') <> 'Y'; --< Bug 3422733 >
INSERT INTO po_online_report_text_gt(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_name)
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
POL.line_num,
0,
0,
p_sequence + ROWNUM,
substr(g_linemsg||g_delim||POL.line_num||g_delim||l_textline,1,240),
'PO_SUB_LINE_CONTRACT_MISMATCH'
FROM PO_LINES_GT POL,
PO_HEADERS_ALL POC, -- : Use _ALL table
PO_HEADERS_GT POH
WHERE POH.po_header_id = p_document_id
AND POL.po_header_id = POH.po_header_id
AND POL.contract_id = POC.po_header_id --
AND POC.type_lookup_code = 'CONTRACT'
AND nvl(POC.cancel_flag , 'N') = 'N'
AND POC.vendor_id <> POH.vendor_id;
INSERT INTO po_online_report_text_gt(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_name)
SELECT /*+ FULL(POL) */ -- bug3413891
p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
POL.line_num,
0,
0,
p_sequence + ROWNUM,
substr(g_linemsg||g_delim||POL.line_num||g_delim||l_textline||
MTL1.uom_class||' , '||MTL2.uom_class,1,240),
'PO_SUB_UOM_CLASS_CONVERSION'
FROM MTL_UOM_CLASS_CONVERSIONS MOU, PO_LINES_GT POL,
MTL_UOM_CLASSES_TL MTL1, MTL_UOM_CLASSES_TL MTL2
WHERE POL.item_id = MOU.inventory_item_id
AND (nvl(MOU.disable_date, TRUNC(SYSDATE))+1) < TRUNC(SYSDATE)
AND POL.po_header_id = p_document_id
AND MOU.from_uom_class = MTL1.uom_class
AND MOU.to_uom_class = MTL2.uom_class
AND EXISTS
(SELECT 'uom conversion exists'
FROM MTL_UNITS_OF_MEASURE MUM
WHERE POL.unit_meas_lookup_code = MUM.unit_of_measure
AND MOU.to_uom_class = MUM.uom_class);
INSERT INTO po_online_report_text_gt(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_name)
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
POL.line_num,
PLL.shipment_num,
0,
p_sequence + ROWNUM,
substr(g_linemsg||g_delim||POL.line_num||g_delim||
g_shipmsg||g_delim||PLL.shipment_num||g_delim
||l_textline,1,240),
'PO_SUB_ITEM_NOT_APPROVED'
FROM MTL_SYSTEM_ITEMS MSI, PO_LINE_LOCATIONS_GT PLL,
PO_LINES_GT POL, PO_HEADERS_GT POH,
FINANCIALS_SYSTEM_PARAMETERS FSP
WHERE POH.po_header_id = p_document_id
AND POH.po_header_id = POL.po_header_id
AND PLL.po_line_id(+) = POL.po_line_id
AND PLL.po_release_id IS NULL
AND MSI.organization_id = PLL.ship_to_organization_id
AND MSI.inventory_item_id = POL.item_id
AND POL.item_id is not null
AND nvl(PLL.closed_code,'OPEN') <> 'FINALLY CLOSED'
AND nvl(POL.cancel_flag,'N') = 'N'
AND nvl(PLL.cancel_flag,'N') = 'N'
AND nvl(MSI.must_use_approved_vendor_flag,'N') = 'Y'
AND NOT exists
(SELECT 1
FROM PO_APPROVED_SUPPLIER_LIS_VAL_V ASL, PO_ASL_STATUS_RULES ASR
WHERE ASL.using_organization_id in (PLL.ship_to_organization_id, -1)
AND ASL.vendor_id = POH.vendor_id
AND nvl(ASL.vendor_site_id, POH.vendor_site_id) = POH.vendor_site_id
AND ASL.item_id = POL.item_id
AND ASL.asl_status_id = ASR.status_id
AND ASR.business_rule = '1_PO_APPROVAL'
AND ASR.allow_action_flag = 'Y' --Bug5597639
UNION ALL
SELECT 1
FROM PO_APPROVED_SUPPLIER_LIS_VAL_V ASL, PO_ASL_STATUS_RULES ASR
WHERE ASL.using_organization_id in (PLL.ship_to_organization_id, -1)
AND ASL.vendor_id = POH.vendor_id
AND nvl(ASL.vendor_site_id, POH.vendor_site_id) = POH.vendor_site_id
AND ASL.item_id is NULL
AND not exists
(SELECT ASL1.ASL_ID
FROM PO_APPROVED_SUPPLIER_LIS_VAL_V ASL1
WHERE ASL1.ITEM_ID = POL.item_id
AND ASL1.using_organization_id in
(PLL.ship_to_organization_id, -1))
AND ASL.category_id in
(SELECT MIC.category_id
FROM MTL_ITEM_CATEGORIES MIC
WHERE MIC.inventory_item_id = POL.item_id
AND MIC.organization_id = PLL.ship_to_organization_id)
AND ASL.asl_status_id = ASR.status_id
AND ASR.business_rule = '1_PO_APPROVAL'
AND ASR.allow_action_flag = 'Y'); --Bug5597639
INSERT INTO po_online_report_text_gt(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_name)
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
POL.line_num,
PLL.shipment_num,
0,
p_sequence + ROWNUM,
substr(g_linemsg||g_delim||POL.line_num||g_delim
||g_shipmsg||g_delim||PLL.shipment_num||g_delim
||l_textline,1,240),
'PO_SUB_ITEM_ASL_DEBARRED'
FROM PO_LINE_LOCATIONS_GT PLL,
PO_LINES_GT POL, PO_HEADERS_GT POH,
FINANCIALS_SYSTEM_PARAMETERS FSP
WHERE POH.po_header_id = p_document_id
AND POH.po_header_id = POL.po_header_id
AND PLL.po_line_id(+) = POL.po_line_id
AND PLL.po_release_id IS NULL
AND nvl(PLL.closed_code,'OPEN') <> 'FINALLY CLOSED'
AND nvl(POL.cancel_flag,'N') = 'N'
AND nvl(PLL.cancel_flag,'N') = 'N'
AND exists
(SELECT 1
FROM PO_APPROVED_SUPPLIER_LIS_VAL_V ASL, PO_ASL_STATUS_RULES ASR,
MTL_SYSTEM_ITEMS MSI --Bug5597639
WHERE ASL.using_organization_id in (PLL.ship_to_organization_id, -1)
/*Bug5597639 Adding the below three conditions */
AND MSI.organization_id = FSP.inventory_organization_id
AND MSI.inventory_item_id = POL.item_id
AND POL.item_id is not null
AND ASL.vendor_id = POH.vendor_id
AND nvl(ASL.vendor_site_id, POH.vendor_site_id) = POH.vendor_site_id
AND ASL.item_id = POL.item_id
AND ASL.asl_status_id = ASR.status_id
AND ASR.business_rule = '1_PO_APPROVAL'
AND ASR.allow_action_flag <> 'Y' -- Bug 5724696
UNION ALL
SELECT 1
FROM PO_APPROVED_SUPPLIER_LIS_VAL_V ASL, PO_ASL_STATUS_RULES ASR
WHERE ASL.using_organization_id in (PLL.ship_to_organization_id, -1)
AND ASL.vendor_id = POH.vendor_id
AND nvl(ASL.vendor_site_id, POH.vendor_site_id) = POH.vendor_site_id
AND ASL.item_id is NULL
AND POL.category_id = ASL.category_id --Bug5597639
AND ASL.asl_status_id = ASR.status_id
AND ASR.business_rule = '1_PO_APPROVAL'
AND ASR.allow_action_flag <> 'Y' ); --Bug5597639
INSERT INTO po_online_report_text_gt(
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_name
)
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
POL.line_num,
0,
0,
p_sequence + ROWNUM,
SUBSTR (g_linemsg || g_delim || POL.line_num ||g_delim ||
l_textline,1,240),
'PO_SUB_LINE_CONTRACT_HOLD'
FROM po_lines_gt POL,
po_headers_gt POH,
po_headers_all POC
WHERE POH.po_header_id = p_document_id
AND NVL(POH.authorization_status, 'INCOMPLETE') = 'INCOMPLETE'
AND POL.po_header_id = POH.po_header_id
AND POC.po_header_id = POL.contract_id
AND POC.user_hold_flag = 'Y';
INSERT INTO po_online_report_text_gt(
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_name
)
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
POL.line_num,
0,
0,
p_sequence + ROWNUM,
SUBSTR (g_linemsg || g_delim || POL.line_num ||g_delim ||
l_textline,1,240),
'PO_SUB_LINE_CONTRACT_EXP'
FROM po_lines_gt POL,
po_headers_gt POH,
po_headers_all POC
WHERE POH.po_header_id = p_document_id
AND NVL(POH.authorization_status, 'INCOMPLETE') = 'INCOMPLETE'
AND POL.po_header_id = POH.po_header_id
AND POC.po_header_id = POL.contract_id
AND TRUNC(POL.creation_date) NOT BETWEEN NVL(TRUNC(POC.start_date),
POL.creation_date-1)
AND NVL(TRUNC(POC.end_date),
POL.creation_date+1);
INSERT INTO po_online_report_text_gt(
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_name
)
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
POL.line_num,
0,
0,
p_sequence + ROWNUM,
SUBSTR (g_linemsg || g_delim || POL.line_num ||g_delim ||
l_textline,1,240),
'PO_ATO_ITEM_NA'
FROM po_lines_gt POL,
po_headers_gt POH,
financials_system_parameters FSP,
mtl_system_items MSI
WHERE POH.po_header_id = p_document_id
AND POL.po_header_id = POH.po_header_id
AND POL.item_id is not null
AND nvl(POL.cancel_flag, 'N') = 'N' --5353423
AND nvl(POL.closed_code, 'OPEN') <> 'FINALLY CLOSED' --5353423
AND POL.item_id = MSI.inventory_item_id
AND MSI.organization_id = FSP.inventory_organization_id
AND MSI.bom_item_type in (1,2);
INSERT INTO po_online_report_text_gt(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_name)
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
pl.line_num,
0,
0,
p_sequence + ROWNUM,
substr(g_linemsg||g_delim||pl.line_num||g_delim||l_textline,1,240),
'PO_ALL_NO_ITEM'
from po_headers_gt ph, po_lines_gt pl,mtl_system_items itm,financials_system_parameters fsp,po_line_types_b plt
where itm.inventory_item_id = pl.item_id
and pl.item_id is not null
and itm.organization_id = fsp.inventory_organization_id
and itm.purchasing_enabled_flag = 'N'
and ph.po_header_id = p_document_id
and pl.po_header_id = ph.po_header_id
and pl.line_type_id = plt.line_type_id
and nvl(plt.outside_operation_flag,'N') = nvl(itm.outside_operation_flag,'N')
and (pl.creation_date >= nvl(ph.approved_date ,pl.creation_date));
INSERT INTO po_online_report_text_gt(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_name)
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
pl.line_num,
pll.shipment_num,
0,
p_sequence + ROWNUM,
substr(g_linemsg||g_delim||pl.line_num||g_delim||
g_shipmsg||g_delim||pll.shipment_num||g_delim||l_textline,1,240),
'PO_ALL_NO_ITEM'
from po_headers_gt ph,po_lines_gt pl,po_line_locations_gt pll,mtl_system_items itm,po_line_types_b plt
where itm.inventory_item_id = pl.item_id
and pl.item_id is not null
and itm.organization_id = pll.ship_to_organization_id
and itm.purchasing_enabled_flag = 'N'
and pl.po_line_id = pll.po_line_id
and ph.po_header_id = pll.po_header_id
and ph.po_header_id = p_document_id
and pl.po_header_id = ph.po_header_id
and pl.line_type_id = plt.line_type_id
and nvl(plt.outside_operation_flag,'N') = nvl(itm.outside_operation_flag,'N')
and (pl.creation_date >= nvl(ph.approved_date,pl.creation_date))
and pll.shipment_type <> 'PREPAYMENT' --
and pll.po_release_id is null;
INSERT INTO po_online_report_text_gt(
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_name
)
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
POL.line_num,
PLL.shipment_num,
0,
p_sequence + ROWNUM,
SUBSTR (g_linemsg || g_delim || POL.line_num ||g_delim ||
g_shipmsg || g_delim || PLL.shipment_num ||g_delim || l_textline,1,240),
'PO_PO_PLANNED_ITEM_DATE_REQ'
FROM po_lines_gt POL,
po_headers_gt POH,
po_line_locations_gt PLL,
financials_system_parameters FSP,
mtl_system_items MSI
WHERE POH.po_header_id = p_document_id
AND POL.po_header_id = POH.po_header_id
AND POL.po_line_id = PLL.po_line_id
AND PLL.po_header_id = POH.po_header_id
AND POL.item_id is not null
AND PLL.need_by_date is null
AND PLL.promised_date is null
AND PLL.shipment_type <> 'PREPAYMENT' --bug 4997671
AND POL.item_id = MSI.inventory_item_id
AND MSI.organization_id = FSP.inventory_organization_id
AND (MSI.mrp_planning_code IN (3,4,7,8,9) OR
MSI.inventory_planning_code IN (1,2) );
SELECT pol.line_num,
poll.shipment_num,
hlat.location_code
BULK COLLECT INTO
l_line_num,
l_shipment_num,
l_ship_to_location_tbl
FROM
po_lines_gt pol,
po_line_locations_gt poll,
hr_locations_all hla,
hr_locations_all_tl hlat
WHERE poll.po_header_id=p_document_id
and pol.po_Header_id=p_document_id
and pol.po_line_id = poll.po_line_id
and poll.ship_to_location_id = hla.location_id
and nvl(poll.cancel_flag,'N') = 'N'
and nvl(poll.closed_code,'OPEN') <> 'FINALLY CLOSED'
and hla.location_id = hlat.location_id
and nvl (trunc (hla.inactive_date), trunc (sysdate)+1 )<= trunc (sysdate)
and hlat.language=userenv('lang');
INSERT INTO po_online_report_text_gt (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_name)
VALUES(
p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
l_line_num(i),
l_shipment_num(i),
NULL, --
p_sequence+l_rowCount(i),
PO_CORE_S.get_translated_text
( 'PO_SUB_SHIPTO_LOC_INVALID'
, 'LINE_NUM', l_line_num(i)
, 'SHIPMENT_NUM', l_shipment_num(i)
, 'SHIP_TO_LOC', l_ship_to_location_tbl(i)
),
'PO_SUB_PO_SHIPTO_LOC_INVALID'
);
INSERT INTO po_online_report_text_gt
(
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_name,
message_type
)
VALUES
(
p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
po_tax_interface_pvt.G_TAX_ERRORS_TBL.line_num(i),
po_tax_interface_pvt.G_TAX_ERRORS_TBL.shipment_num(i),
po_tax_interface_pvt.G_TAX_ERRORS_TBL.distribution_num(i),
p_sequence + ROWNUM,
l_tax_message || po_tax_interface_pvt.G_TAX_ERRORS_TBL.message_text(i),
'PO_TAX_CALCULATION_FAILED',
'E'
);
INSERT INTO po_online_report_text_gt(
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_name,
message_type
)
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
POL.line_num,
PLL.shipment_num,
0,
p_sequence + ROWNUM,
SUBSTR (g_linemsg || POL.line_num || g_delim ||
g_shipmsg || PLL.shipment_num || g_delim ||
l_textline,
1,240),
'PO_SUB_VMI_ASL_EXISTS',
'W'
FROM PO_LINES_GT POL,
PO_HEADERS_GT POH,
PO_LINE_LOCATIONS_GT PLL,
PO_APPROVED_SUPPLIER_LIS_VAL_V PASL,
PO_ASL_ATTRIBUTES PAA,
PO_ASL_STATUS_RULES_V PASR
WHERE POH.po_header_id = p_document_id
AND POL.po_header_id = POH.po_header_id
AND PLL.po_header_id = POH.po_header_id
AND PLL.po_line_id = POL.po_line_id
-- item is not null
AND POL.item_id IS NOT NULL
-- Document is standard PO
AND POH.type_lookup_code = 'STANDARD'
--VMI is enabled
AND PASL.item_id = POL.item_id
AND PASL.vendor_id = POH.vendor_id
AND nvl(PASL.vendor_site_id,-1) = nvl(POH.vendor_site_id,-1)
AND PASL.using_organization_id IN (PLL.ship_to_organization_id, -1)
AND PASR.status_id = PASL.asl_status_id
AND PASR.business_rule = '2_SOURCING'
AND PASR.allow_action_flag = 'Y'
AND PASL.asl_id = PAA.asl_id
AND PAA.enable_vmi_flag = 'Y'
AND PAA.using_organization_id =
(SELECT max(paa2.using_organization_id)
FROM po_asl_attributes paa2
WHERE paa2.asl_id = pasl.asl_id
AND paa2.using_organization_id IN (-1, PLL.ship_to_organization_id));
INSERT INTO po_online_report_text_gt
(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_name)
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_INVALID_OKE_CONTRACT_VER_ID',
'LINE_NUM',pol.line_num),
'PO_INVALID_OKE_CONTRACT_VER_ID'
FROM po_lines_gt pol
WHERE pol.oke_contract_version_id IS NOT NULL
AND pol.oke_contract_version_id NOT IN (SELECT major_version
FROM oke_k_vers_numbers_v
WHERE chr_id = pol.oke_contract_header_id
UNION
SELECT major_version
FROM okc_k_vers_numbers_h
WHERE chr_id = pol.oke_contract_header_id);
INSERT INTO po_online_report_text_gt
(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_name)
SELECT p_online_report_id,
p_login_id,
p_user_id,
SYSDATE,
p_user_id,
SYSDATE,
pol.line_num,
pos.shipment_num,
pod.distribution_num,
p_sequence + ROWNUM,
po_core_s.GET_TRANSLATED_TEXT('PO_INVALID_OKE_CONTRACT_LNE_ID',
'LINE_NUM',pol.line_num,
'SHIP_NUM',pos.shipment_num,
'DIST_NUM',pod.distribution_num),
'PO_INVALID_OKE_CONTRACT_LNE_ID'
FROM po_distributions_gt pod,
po_line_locations_gt pos,
po_lines_gt pol
WHERE pod.po_line_id = pol.po_line_id
AND pod.line_location_id = pos.line_location_id
AND pod.oke_contract_line_id IS NOT NULL
AND pod.oke_contract_line_id NOT IN (SELECT id
FROM okc_k_lines_b
WHERE dnz_chr_id = pol.oke_contract_header_id
--
-- Look for contract lines in oke_deliverables_b also (DTS flow)
UNION
SELECT deliverable_id
FROM oke_deliverables_b
WHERE source_header_id = pol.oke_contract_header_id);
INSERT INTO po_online_report_text_gt
(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_name)
SELECT p_online_report_id,
p_login_id,
p_user_id,
SYSDATE,
p_user_id,
SYSDATE,
pol.line_num,
pos.shipment_num,
pod.distribution_num,
p_sequence + ROWNUM,
po_core_s.GET_TRANSLATED_TEXT('PO_INVALID_OKE_CONTRACT_DLV_ID',
'LINE_NUM',pol.line_num,
'SHIP_NUM',pos.shipment_num,
'DIST_NUM',pod.distribution_num),
'PO_INVALID_OKE_CONTRACT_DLV_ID'
FROM po_distributions_gt pod,
po_line_locations_gt pos,
po_lines_gt pol
WHERE pod.po_line_id = pol.po_line_id
AND pod.line_location_id = pos.line_location_id
AND pod.oke_contract_deliverable_id IS NOT NULL
AND pod.oke_contract_deliverable_id NOT IN (SELECT deliverable_id
FROM oke_k_deliverables_b
WHERE k_line_id = pod.oke_contract_line_id
--
-- Look for contract deliverables in
-- oke_deliverable_actions also (DTS flow)
UNION
SELECT action_id
FROM oke_deliverable_actions
WHERE deliverable_id = pod.oke_contract_line_id);
INSERT INTO po_online_report_text_gt (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_name)
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
pol.line_num,
pll.shipment_num, 0,
p_sequence + ROWNUM,
substr(g_linemsg||g_delim||POL.line_num||g_delim||g_shipmsg||g_delim||
PLL.shipment_num||g_delim||l_textline,1,240),
'PO_SUB_PO_SHIP_INV_MATCH_NE_R'
FROM PO_HEADERS_GT POH,
PO_LINES_GT POL,
PO_LINE_LOCATIONS_GT PLL
WHERE POH.po_header_id = POL.po_header_id
AND POL.po_line_id = PLL.po_line_id
AND POH.po_header_id = p_document_id
AND Nvl(PLL.LCM_FLAG,'N') = 'Y'
AND Nvl(PLL.match_option,'P') <> 'R';
INSERT INTO po_online_report_text_gt (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_name)
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
pol.line_num,
pll.shipment_num,
pod.distribution_num,
p_sequence + ROWNUM,
substr(g_linemsg||g_delim||POL.line_num||g_delim||g_shipmsg||g_delim||PLL.shipment_num||g_delim
||g_distmsg||g_delim||POD.distribution_num||g_delim||l_textline,1,240),
'PO_SUB_PO_DIST_DEST_TYPE_NE_IN'
FROM PO_HEADERS_GT POH,
PO_LINES_GT POL,
PO_LINE_LOCATIONS_GT PLL,
PO_DISTRIBUTIONS_GT POD
WHERE POH.po_header_id = POD.po_header_id
AND POD.line_location_id = PLL.line_location_id
AND PLL.po_line_id = POL.po_line_id
AND POH.po_header_id = p_document_id
AND Nvl(POD.LCM_FLAG,'N') = 'Y'
AND POD.DESTINATION_TYPE_CODE <> 'INVENTORY';
* p_online_report_id: Id used to INSERT INTO online_report_text table
* p_user_id: User performing the action
* p_login_id: Last update login_id
* IN OUT PARAMETERS
* p_sequence: Sequence number of last reported error
* Modifies: Inserts error msgs in online_report_text_gt table, uses
* global_temp tables for processing
* Effects: This procedure runs the document submission checks for PLANNED POs
* and BLANKET PAs
* Returns:
* p_sequence: This parameter contains the current count of number of error
* messages inserted
*/
PROCEDURE check_planned_po_blanket_pa(p_document_id IN NUMBER,
p_online_report_id IN NUMBER,
p_user_id IN NUMBER,
p_login_id IN NUMBER,
p_sequence IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2) IS
l_textline po_online_report_text.text_line%TYPE := NULL;
INSERT INTO po_online_report_text_gt(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_name)
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
0,
0,
0,
p_sequence + ROWNUM,
substr(l_textline,1,240),
'PO_SUB_AGREED_GRT_LIMIT'
FROM PO_HEADERS_GT POH
WHERE POH.po_header_id = p_document_id
AND POH.blanket_total_amount is not null
AND POH.amount_limit is not null
AND POH.blanket_total_amount > POH.amount_limit;
INSERT INTO po_online_report_text_gt(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_name)
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
0,
0,
0,
p_sequence + ROWNUM,
substr(l_textline,1,240),
'PO_SUB_MINREL_GRT_LIMIT'
FROM PO_HEADERS_GT POH
WHERE POH.po_header_id = p_document_id
AND POH.min_release_amount is not null
AND POH.amount_limit is not null
AND POH.min_release_amount > POH.amount_limit;
INSERT INTO po_online_report_text_gt(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_name)
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
0,
0,
0,
p_sequence + ROWNUM,
substr(l_textline,1,240),
'PO_SUB_LIMIT_GRT_REL_AMT'
FROM PO_HEADERS_GT POH
WHERE POH.po_header_id = p_document_id
AND POH.amount_limit is not null
AND ( (NVL(POH.global_agreement_flag, 'N') = 'N'
AND
POH.amount_limit <
( SELECT --
sum ( decode ( PLL2.quantity
, NULL , ( PLL2.amount
- nvl(PLL2.amount_cancelled,0)
)
, ( ( PLL2.quantity
- nvl(PLL2.quantity_cancelled,0) )
* PLL2.price_override
)
)
)
FROM PO_LINE_LOCATIONS PLL2
WHERE PLL2.po_header_id = POH.po_header_id
AND PLL2.shipment_type in ('BLANKET', 'SCHEDULED')
)
)
OR
--bug2969379
--GA should have amount limit checked in a different way
( POH.global_agreement_flag = 'Y'
AND
POH.amount_limit <
( SELECT
sum ( decode ( PLL3.quantity
, NULL , ( PLL3.amount
- nvl(PLL3.amount_cancelled,0)
)
, ( ( PLL3.quantity
- nvl(PLL3.quantity_cancelled,0) )
* PLL3.price_override
)
)
)
FROM PO_LINE_LOCATIONS_ALL PLL3
WHERE PLL3.from_header_id = POH.po_header_id
)
)
);
INSERT into po_online_report_text_gt(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_name)
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
POL.line_num,
0,
0,
p_sequence + ROWNUM,
substr(g_linemsg||g_delim||POL.line_num
||g_delim||l_textline,1,240),
'PO_SVC_PRICE_LIMIT_LT_PRICE'
FROM PO_LINES_GT POL
WHERE POL.po_header_id = p_document_id
AND trunc(sysdate) <= trunc(nvl(POL.expiration_date, sysdate + 1)) -- bug 3449694
AND nvl(POL.cancel_flag,'N')= 'N'
AND nvl(POL.closed_code,'OPEN') <> 'FINALLY CLOSED'
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_online_report_id: Id used to INSERT INTO online_report_text table
* p_user_id: User performing the action
* p_login_id: Last update login_id
* IN OUT PARAMETERS
* p_sequence: Sequence number of last reported error
* Modifies: Inserts error msgs in online_report_text_gt table, uses
* global_temp tables for processing
* Effects: This procedure runs the document submission checks for BLANKETS
* Returns:
* p_sequence: This parameter contains the current count of number of error
* messages inserted
*/
PROCEDURE check_blanket_agreement(p_document_id IN NUMBER,
p_online_report_id IN NUMBER,
p_user_id IN NUMBER,
p_login_id IN NUMBER,
p_check_asl IN BOOLEAN, -- <2757450>
p_sequence IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2) IS
l_textline po_online_report_text.text_line%TYPE := NULL;
INSERT INTO po_online_report_text_gt(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_name)
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
0, 0, 0,
p_sequence + ROWNUM,
substr(l_textline,1,240),
'PO_SUB_HEADER_NO_LINES'
FROM PO_HEADERS_GT POH
WHERE POH.po_header_id = p_document_id AND
NOT EXISTS (SELECT 'Lines Exist'
FROM PO_LINES_GT POL
WHERE POL.po_header_id = POH.po_header_id);
INSERT INTO po_online_report_text_gt(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_name)
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
pl.line_num,
0,
0,
p_sequence + ROWNUM,
substr(g_linemsg||g_delim||pl.line_num||g_delim||l_textline,1,240),
'PO_ALL_NO_ITEM'
from po_headers_gt ph,po_lines_gt pl,mtl_system_items itm,financials_system_parameters fsp,po_line_types_b plt
where itm.inventory_item_id = pl.item_id
and pl.item_id is not null
and itm.organization_id = fsp.inventory_organization_id
and itm.purchasing_enabled_flag = 'N'
and ph.po_header_id = p_document_id
and pl.po_header_id = ph.po_header_id
and pl.line_type_id = plt.line_type_id
and nvl(plt.outside_operation_flag,'N') = nvl(itm.outside_operation_flag,'N')
and (pl.creation_date >= nvl(ph.approved_date ,pl.creation_date)) ;
INSERT INTO po_online_report_text_gt(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_name)
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
pl.line_num,
pll.shipment_num,
0,
p_sequence + ROWNUM,
CASE
WHEN pll.start_date < ph.start_date
THEN substr(g_linemsg||g_delim||pl.line_num||g_delim
||g_price_breakmsg||g_delim||pll.shipment_num||g_delim
||FND_MESSAGE.GET_STRING('PO', PO_MESSAGE_S.POX_EFFECTIVE_DATES1),1,240)
WHEN pll.end_date > ph.end_date
THEN substr(g_linemsg||g_delim||pl.line_num||g_delim
||g_price_breakmsg||g_delim||pll.shipment_num||g_delim
||FND_MESSAGE.GET_STRING('PO', PO_MESSAGE_S.POX_EFFECTIVE_DATES),1,240)
WHEN pll.start_date > ph.end_date
THEN substr(g_linemsg||g_delim||pl.line_num||g_delim
||g_price_breakmsg||g_delim||pll.shipment_num||g_delim
||FND_MESSAGE.GET_STRING('PO', PO_MESSAGE_S.POX_EFFECTIVE_DATES4),1,240)
WHEN pll.end_date < ph.start_date
THEN substr(g_linemsg||g_delim||pl.line_num||g_delim
||g_price_breakmsg||g_delim||pll.shipment_num||g_delim
||FND_MESSAGE.GET_STRING('PO', PO_MESSAGE_S.POX_EFFECTIVE_DATES5),1,240)
WHEN pll.start_date > pl.expiration_date
THEN substr(g_linemsg||g_delim||pl.line_num||g_delim
||g_price_breakmsg||g_delim||pll.shipment_num||g_delim
||FND_MESSAGE.GET_STRING('PO', PO_MESSAGE_S.POX_EFFECTIVE_DATES6),1,240)
WHEN pll.end_date > pl.expiration_date
THEN substr(g_linemsg||g_delim||pl.line_num||g_delim
||g_price_breakmsg||g_delim||pll.shipment_num||g_delim
||FND_MESSAGE.GET_STRING('PO', PO_MESSAGE_S.POX_EFFECTIVE_DATES2),1,240)
END
,
CASE
WHEN pll.start_date < ph.start_date
THEN PO_MESSAGE_S.POX_EFFECTIVE_DATES1
WHEN pll.end_date > ph.end_date
THEN PO_MESSAGE_S.POX_EFFECTIVE_DATES
WHEN pll.start_date > ph.end_date
THEN PO_MESSAGE_S.POX_EFFECTIVE_DATES4
WHEN pll.end_date < ph.start_date
THEN PO_MESSAGE_S.POX_EFFECTIVE_DATES5
WHEN pll.start_date > pl.expiration_date
THEN PO_MESSAGE_S.POX_EFFECTIVE_DATES6
WHEN pll.end_date > pl.expiration_date
THEN PO_MESSAGE_S.POX_EFFECTIVE_DATES2
END
FROM po_headers_gt ph, po_lines_gt pl, po_line_locations_gt pll
WHERE ph.po_header_id = p_document_id
AND pl.po_header_id = ph.po_header_id
AND pll.po_line_id = pl.po_line_id
AND pll.shipment_type = 'PRICE BREAK'
AND (pll.start_date < ph.start_date
or pll.end_date > ph.end_date
or pll.start_date > ph.end_date
or pll.end_date < ph.start_date
or pll.start_date > pl.expiration_date
or pll.end_date > pl.expiration_date);
INSERT INTO po_online_report_text_gt(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_name)
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
pl.line_num,
0,
0,
p_sequence + ROWNUM,
substr(g_linemsg||g_delim||pl.line_num||g_delim||l_textline,1,240),
PO_MESSAGE_S.POX_EXPIRATION_DATES
FROM po_headers_gt ph, po_lines_gt pl
WHERE ph.po_header_id = p_document_id
AND pl.po_header_id = ph.po_header_id
AND (pl.expiration_date < ph.start_date
or pl.expiration_date > ph.end_date);
* p_online_report_id: Id used to INSERT INTO online_report_text table
* p_user_id: User performing the action
* p_login_id: Last update login_id
* IN OUT PARAMETERS
* p_sequence: Sequence number of last reported error
* Modifies: Inserts error msgs in online_report_text_gt table, uses
* global_temp tables for processing
* Effects: This procedure runs the document submission checks for Standard
* POs including GLOBAL AGREEMENTS reference checks and Consigned
* Inventory checks
* Returns:
* p_sequence: This parameter contains the current count of number of error
* messages inserted
*/
PROCEDURE check_standard_po(p_document_id IN NUMBER,
p_online_report_id IN NUMBER,
p_user_id IN NUMBER,
p_login_id IN NUMBER,
p_sequence IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2) IS
l_is_ga_referenced VARCHAR2(1) := NULL;
SELECT 'Y'
FROM PO_HEADERS_GT POH, PO_LINES_GT POL, PO_HEADERS_ALL POHA
WHERE POH.po_header_id = p_document_id
AND POH.po_header_id = POL.po_header_id
AND POL.from_header_id = POHA.po_header_id
AND POHA.type_lookup_code = 'BLANKET'
AND POHA.global_agreement_flag = 'Y';
SELECT 'Y'
FROM po_lines_gt POL,
po_headers_all POHA
WHERE POL.po_header_id = p_doc_id
AND POL.contract_id = POHA.po_header_id
AND POHA.global_agreement_flag = 'Y';
* p_online_report_id: Id used to INSERT INTO online_report_text table
* p_user_id: User performing the action
* p_login_id: Last update login_id
* IN OUT PARAMETERS
* p_sequence: Sequence number of last reported error
* Modifies: Inserts error msgs in online_report_text_gt table, uses
* global_temp tables for processing
* Effects: This procedure performs checks for the Consigned from Supplier
* project in order to enforce the following:
* 1) Consigned status on any new or modified shipment that is not
* partially received or partially invoiced should match the
* consigned setting on the corresponding ASL entry.
* 2) Document references cannot exist for PO lines with one or
* more consigned shipments
* Returns:
* p_sequence: This parameter contains the current count of number of error
* messages inserted
* x_return_status: This parameter signifies whether the checks contained in
* this procedure completed successfully.
*/
PROCEDURE check_std_consigned_ref(p_document_id IN NUMBER,
p_online_report_id IN NUMBER,
p_user_id IN NUMBER,
p_login_id IN NUMBER,
p_sequence IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2) IS
l_api_name CONSTANT varchar2(40) := 'CHECK_STD_CONSIGNED_REF';
INSERT INTO
po_online_report_text_gt(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_name)
-- SQL What: Selects any PO Line with at least one consigned shipment
-- and a document reference or contract
-- SQL Why: Document references and consigned shipments cannot exist
-- for the same PO Line
-- SQL Join: po_line_id
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
pol.line_num,
pll.shipment_num,
0,
p_sequence + ROWNUM,
substr(g_linemsg||g_delim||POL.line_num||g_delim||g_shipmsg
||g_delim||PLL.shipment_num||':'||g_delim||l_textline,1,240),
'PO_DOC_REF_SUP_CONS_COEXIST'
FROM po_lines_gt pol,
po_line_locations_gt pll
WHERE pol.po_header_id = p_document_id
AND pol.po_line_id = pll.po_line_id
AND pll.shipment_type = 'STANDARD'
AND pll.consigned_flag = 'Y'
AND nvl(pol.cancel_flag,'N') = 'N'
AND nvl(pol.closed_code,'OPEN') <> 'FINALLY CLOSED'
AND (pol.oke_contract_header_id is not null or
pol.oke_contract_version_id is not null or
pol.from_header_id is not null or
pol.from_line_id is not null or
pol.contract_num is not null);
SELECT POH.vendor_id,
POH.vendor_site_id,
POL.item_id,
PLL.ship_to_organization_id,
PLL.consigned_flag,
POL.line_num,
PLL.shipment_num,
PLL.line_location_id
BULK COLLECT INTO
l_vendor_id,
l_vendor_site_id,
l_item_id,
l_ship_to_org_id,
l_consigned_flag,
l_line_num,
l_shipment_num,
l_line_location_id
FROM PO_HEADERS_GT POH,
PO_LINE_LOCATIONS_GT PLL,
PO_LINES_GT POL
WHERE POH.po_header_id = p_document_id AND
POH.po_header_id = POL.po_header_id AND
-- POH.po_header_id = PLL.po_header_id AND
POL.po_line_id = PLL.po_line_id AND
PLL.shipment_type = 'STANDARD' AND
nvl(PLL.cancel_flag,'N') = 'N' AND
nvl(PLL.closed_code,'OPEN') <> 'FINALLY CLOSED' AND
-- Bug fix for #2733398
-- nvl(PLL.approved_flag, 'N') IN ('N','R') AND
nvl(PLL.approved_flag, 'N') = 'N' AND
PLL.quantity_received <= 0 AND
PLL.quantity_billed <= 0
ORDER BY
POH.vendor_id,
POH.vendor_site_id,
POL.item_id,
PLL.ship_to_organization_id;
SELECT count('Y')
INTO l_count_expense_dist
FROM DUAL
WHERE EXISTS(SELECT 'Y'
FROM PO_DISTRIBUTIONS_GT
WHERE LINE_LOCATION_ID = l_line_location_id(i)
AND DESTINATION_TYPE_CODE = 'EXPENSE');
INSERT INTO
po_online_report_text_gt
(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_name)
VALUES
(p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
l_line_num(i),
l_shipment_num(i),
0,
p_sequence + i,
substr(g_linemsg||g_delim||l_line_num(i)||g_delim||g_shipmsg
||g_delim||l_shipment_num(i)||':'||g_delim||l_textline,1,240),
'PO_SUP_CONS_STATUS_MISMATCH');
* p_online_report_id: Id used to INSERT INTO online_report_text table
* p_user_id: User performing the action
* p_login_id: Last update login_id
* IN OUT PARAMETERS
* p_sequence: Sequence number of last reported error
* Modifies: Inserts error msgs in online_report_text_gt table, uses
* global_temp tables for processing
* Effects: This procedure runs the document submission checks for Standard
* POs which have GLOBAL AGREEMENTS reference
* Returns:
* p_sequence: This parameter contains the current count of number of error
* messages inserted
*/
PROCEDURE check_std_global_ref(p_document_id IN NUMBER,
p_online_report_id IN NUMBER,
p_user_id IN NUMBER,
p_login_id IN NUMBER,
p_sequence IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2) IS
l_textline po_online_report_text.text_line%TYPE := NULL;
INSERT into po_online_report_text_gt(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_name)
--
--SQL Querying for PO lines that reference GAs that are not enabled
--SQL for purchasing in this org, to report an error message.
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
POL.line_num,
0,
0,
p_sequence + ROWNUM,
substr(g_linemsg||g_delim||POL.line_num||g_delim
||l_textline,1,240),
'PO_SUB_STD_GA_DISABLED'
FROM PO_HEADERS_GT POH1, PO_LINES_GT POL, PO_HEADERS_ALL POH
WHERE POH1.po_header_id = p_document_id
AND POL.po_header_id = POH1.po_header_id
AND POL.from_header_id = POH.po_header_id --JOIN
AND POH.type_lookup_code = 'BLANKET'
AND POH.global_agreement_flag = 'Y'
--
AND NOT EXISTS --< Bug 3301427 Start >
(SELECT 'previously approved shipment'
FROM po_line_locations_gt pllg
WHERE pllg.po_line_id = pol.po_line_id
AND pllg.approved_date IS NOT NULL) --< Bug 3301427 End >
AND NOT EXISTS
--SQL Query enabled org assignments of this current purchasing org
(SELECT 'Enabled purchasing org'
FROM PO_GA_ORG_ASSIGNMENTS PGOA
WHERE PGOA.po_header_id = POH.po_header_id
AND PGOA.purchasing_org_id = POH1.org_id
AND PGOA.enabled_flag = 'Y');
INSERT into po_online_report_text_gt(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_name)
--SQL What: Querying for PO lines that reference GAs that are not approved.
--SQL Why: Add appropriate error message to po_online_report_text_gt
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
POL.line_num,
0,
0,
p_sequence + ROWNUM,
substr(g_linemsg||g_delim||POL.line_num||g_delim
||l_textline,1,240),
'PO_SUB_STD_GA_APPROVED'
FROM PO_HEADERS_GT POH1, PO_LINES_GT POL, PO_HEADERS_ALL POH2
WHERE POH1.po_header_id = p_document_id
AND POL.po_header_id = POH1.po_header_id --JOIN
AND POH2.po_header_id = POL.from_header_id --JOIN
AND POH2.type_lookup_code = 'BLANKET'
AND POH2.global_agreement_flag = 'Y'
AND NVL(POH2.approved_flag, 'N') <> 'Y'
AND NVL(POH2.user_hold_flag, 'N') <> 'Y' --< Bug 3422733 >
AND NOT EXISTS --< Bug 3301427 Start >
(SELECT 'previously approved shipment'
FROM po_line_locations_gt pllg
WHERE pllg.po_line_id = pol.po_line_id
AND pllg.approved_date IS NOT NULL); --< Bug 3301427 End >
INSERT into po_online_report_text_gt(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_name)
--SQL What: Querying for PO lines that reference GAs that are on hold
--SQL Why: Add appropriate error message to po_online_report_text_gt
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
POL.line_num,
0,
0,
p_sequence + ROWNUM,
substr(g_linemsg||g_delim||POL.line_num||g_delim
||l_textline,1,240),
'PO_SUB_STD_GA_ON_HOLD'
FROM PO_HEADERS_GT POH1, PO_LINES_GT POL, PO_HEADERS_ALL POH2
WHERE POH1.po_header_id = p_document_id
AND POL.po_header_id = POH1.po_header_id --JOIN
AND POH2.po_header_id = POL.from_header_id --JOIN
AND POH2.type_lookup_code = 'BLANKET'
AND POH2.global_agreement_flag = 'Y'
AND POH2.user_hold_flag = 'Y'
AND NOT EXISTS --< Bug 3301427 Start >
(SELECT 'previously approved shipment'
FROM po_line_locations_gt pllg
WHERE pllg.po_line_id = pol.po_line_id
AND pllg.approved_date IS NOT NULL); --< Bug 3301427 End >
INSERT into po_online_report_text_gt(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_name)
--SQL What: Querying for PO lines whose vendor does not
--SQL match the vendor of the referenced GA
--SQL Why: Add appropriate error message to po_online_report_text_gt
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
POL.line_num,
0,
0,
p_sequence + ROWNUM,
substr(g_linemsg||g_delim||POL.line_num||g_delim
||l_textline,1,240),
'PO_SUB_STD_GA_VENDOR_MISMATCH'
FROM PO_LINES_GT POL, PO_HEADERS_GT POH1, PO_HEADERS_ALL POH2
WHERE POL.po_header_id = p_document_id
AND POL.po_header_id = POH1.po_header_id --JOIN
AND POL.from_header_id = POH2.po_header_id --JOIN
AND POH2.type_lookup_code = 'BLANKET'
AND POH2.global_agreement_flag = 'Y'
AND POH1.vendor_id <> POH2.vendor_id;
INSERT into po_online_report_text_gt(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_name)
--SQL What: Querying for PO lines whose vendor_site_id does not
--SQL match a valid vendor_site_id in the GA's org assignments
--SQL Why: Add appropriate error message to po_online_report_text_gt
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
POL.line_num,
0,
0,
p_sequence + ROWNUM,
substr(g_linemsg||g_delim||POL.line_num||g_delim
||l_textline,1,240),
'PO_SUB_STD_GA_VDR_SITE_MISMT'
FROM PO_LINES_GT POL, PO_HEADERS_GT POH1, PO_HEADERS_ALL POH2
WHERE POL.po_header_id = p_document_id
AND POL.po_header_id = POH1.po_header_id --JOIN
AND POL.from_header_id = POH2.po_header_id --JOIN
AND POH2.type_lookup_code = 'BLANKET'
AND POH2.global_agreement_flag = 'Y'
--
AND NOT EXISTS --< Bug 3301427 Start >
(SELECT 'previously approved shipment'
FROM po_line_locations_gt pllg
WHERE pllg.po_line_id = pol.po_line_id
AND pllg.approved_date IS NOT NULL) --< Bug 3301427 End >
AND NOT EXISTS
(SELECT 'Enabled vendor site'
FROM PO_GA_ORG_ASSIGNMENTS pgoa
WHERE PGOA.po_header_id = POH2.po_header_id
AND PGOA.vendor_site_id = POH1.vendor_site_id
AND PGOA.enabled_flag = 'Y');
INSERT into po_online_report_text_gt(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_name)
--SQL What: Querying for PO lines that were not created within
--SQL the effective dates of the referenced GA
--SQL Why: Add appropriate error message to po_online_report_text_gt
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
POL.line_num,
PLL.shipment_num, -- Bug #5415428 As Need by date also forms part of the checks
0,
p_sequence + ROWNUM,
substr(g_linemsg||g_delim||POL.line_num||g_delim
||l_textline,1,240),
'PO_SUB_STD_AFTER_GA_DATE'
FROM PO_LINES_GT POL, PO_HEADERS_GT POH, PO_HEADERS_ALL POH2, PO_LINES_ALL POL2, PO_LINE_LOCATIONS_GT PLL
WHERE POL.po_header_id = p_document_id
AND POL.po_header_id = POH.po_header_id -- JOIN
AND PLL.po_line_id = POL.po_line_id --JOIN, Bug #5415428 - Get the Need by date
AND POL.from_header_id = POH2.po_header_id --JOIN
AND POL.from_line_id = POL2.po_line_id --JOIN
AND POH2.type_lookup_code = 'BLANKET'
AND POH2.global_agreement_flag = 'Y'
--Bug #2699630: Adding trunc on both sides of the check
--Bug #5415428: Start date and need by date also needs to be considered
AND (NVL(TRUNC(PLL.need_by_date),NVL(TRUNC(POL.start_date), TRUNC(POH.creation_date)))
> NVL ( TRUNC(POL2.expiration_date) , TRUNC(POH2.end_date)));
INSERT into po_online_report_text_gt(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_name)
--SQL What: Querying for PO lines that were not created within
--SQL the effective dates of the referenced GA
--SQL Why: Add appropriate error message to po_online_report_text_gt
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
POL.line_num,
PLL.shipment_num,
0,
p_sequence + ROWNUM,
substr(g_linemsg||g_delim||POL.line_num||g_delim||
g_shipmsg||g_delim||PLL.shipment_num||g_delim||l_textline,1,240),
'PO_SUB_STD_BEFORE_GA_DATE'
FROM PO_LINES_GT POL, PO_HEADERS_GT POH, PO_HEADERS_ALL POH2, PO_LINE_LOCATIONS_GT PLL
WHERE POL.po_header_id = p_document_id
AND POL.po_header_id = POH.po_header_id
AND PLL.po_line_id = POL.po_line_id --JOIN
AND POL.from_header_id = POH2.po_header_id --JOIN
AND POH2.type_lookup_code = 'BLANKET'
AND POH2.global_agreement_flag = 'Y'
--Bug #2699630: Adding trunc on both sides of the check
--Bug #5415428: Start date also needs to be considered
AND NVL(TRUNC(PLL.need_by_date), NVL(TRUNC(POL.start_date),TRUNC(POH.creation_date))) < TRUNC(POH2.start_date);
INSERT into po_online_report_text_gt(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_name)
--SQL What: Querying for PO lines whose currency code does not
--SQL match the currency code of the referenced GA
--SQL Why: Add appropriate error message to po_online_report_text_gt
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
POL.line_num,
0,
0,
p_sequence + ROWNUM,
substr(g_linemsg||g_delim||POL.line_num||g_delim
||l_textline,1,240),
'PO_SUB_STD_GA_CURR_MISMATCH'
FROM PO_LINES_GT POL, PO_HEADERS_GT POH1, PO_HEADERS_ALL POH2
WHERE POL.po_header_id = p_document_id
AND POL.po_header_id = POH1.po_header_id --JOIN
AND POL.from_header_id = POH2.po_header_id --JOIN
AND POH2.type_lookup_code = 'BLANKET'
AND POH2.global_agreement_flag = 'Y'
AND POH1.currency_code <> POH2.currency_code;
SELECT /*+ FULL(POL) ORDERED */ -- bug3413891
POL.line_num
, nvl ( decode ( POL.quantity --
, NULL , sum ( PLL1.amount
- nvl(PLL1.amount_cancelled,0) )
, sum ( ( PLL1.quantity
- nvl(PLL1.quantity_cancelled,0) )
* PLL1.price_override )
)
, 0 )
, POH1.amount_limit
BULK COLLECT INTO l_curr_doc_line_num,l_po_amount,l_amount_limit
FROM PO_LINES_GT POL, PO_HEADERS_ALL POH1, PO_LINE_LOCATIONS_GT PLL1
WHERE PLL1.from_header_id = POL.from_header_id
AND POL.po_header_id = p_document_id
AND POL.from_header_id = POH1.po_header_id
AND POH1.type_lookup_code = 'BLANKET'
AND POH1.global_agreement_flag = 'Y'
AND POH1.amount_limit IS NOT NULL
GROUP BY
POL.line_num
, POL.quantity --
, POH1.amount_limit;
SELECT /*+ FULL(POL) */
POL.line_num
, nvl ( decode ( POL.quantity --
, NULL , sum ( PLL2.amount
- nvl(PLL2.amount_cancelled,0) )
, sum ( ( PLL2.quantity
- nvl(PLL2.quantity_cancelled,0) )
* PLL2.price_override )
)
, 0 )
BULK COLLECT INTO l_prev_doc_line_num, l_prev_rel_amount
FROM PO_LINE_LOCATIONS_ALL PLL2, PO_HEADERS_ALL POH1,
PO_HEADERS_ALL POH2, PO_LINES_GT POL
WHERE POL.po_header_id = p_document_id
AND POL.from_header_id = POH1.po_header_id --JOIN
AND POH1.type_lookup_code = 'BLANKET'
AND POH1.global_agreement_flag = 'Y'
AND POH1.amount_limit IS NOT NULL
AND PLL2.from_header_id = POL.from_header_id --JOIN
AND POH2.po_header_id = PLL2.po_header_id --JOIN
AND nvl(POH2.approved_flag, 'N') = 'Y'
AND PLL2.po_header_id <> p_document_id
GROUP BY
POL.line_num
, POL.quantity; --
INSERT INTO po_online_report_text_gt(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_name)
VALUES(p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
l_curr_doc_line_num(l_curr_doc_line_index),
0,
0,
p_sequence+ l_rowcount,
substr(g_linemsg||g_delim||l_curr_doc_line_num(l_curr_doc_line_index)||g_delim
||l_textline,1,240),
'PO_SUB_STD_AMT_GRT_GA_AMT_LMT');
INSERT into po_online_report_text_gt(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_name)
--SQL What: Querying for PO line unit prices that exceed the
--SQL price tolerance on the GA line
--SQL Why: Add appropriate error message to po_online_report_text_gt
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
POL1.line_num,
0,
0,
p_sequence + ROWNUM,
decode ( POL1.order_type_lookup_code --
, 'FIXED PRICE' , PO_CORE_S.get_translated_text
( 'PO_SUB_PO_LINE_GT_GA_AMT_TOL'
, 'LINE_NUM', POL1.line_num
, 'LINE_AMT', POL1.amount
, 'AMT_TOL' , nvl ( POL2.not_to_exceed_price
, POL1.amount )
)
, PO_CORE_S.get_translated_text
( 'PO_SUB_PO_LINE_GT_GA_PRICE_TOL'
, 'LINE_NUM' , POL1.line_num
, 'LINE_PRICE', POL1.unit_price
, 'PRICE_TOL' , nvl ( POL2.not_to_exceed_price
, POL1.unit_price )
)
),
decode ( POL1.order_type_lookup_code --
, 'FIXED PRICE' , 'PO_SUB_PO_LINE_GT_GA_AMT_TOL'
, 'PO_SUB_PO_LINE_GT_GA_PRICE_TOL'
)
FROM PO_LINES_GT POL1, PO_LINES_ALL POL2, PO_HEADERS_ALL POH
WHERE POL1.po_header_id = p_document_id
AND POH.po_header_id = POL1.from_header_id --JOIN
AND POH.type_lookup_code = 'BLANKET'
AND POH.global_agreement_flag = 'Y'
AND POL1.from_line_id = POL2.po_line_id --JOIN
AND nvl(POL1.cancel_flag,'N')= 'N'
AND nvl(POL1.closed_code,'OPEN') <> 'FINALLY CLOSED'
AND POL2.allow_price_override_flag = 'Y' -- Bug 3177525
AND ( --
( ( POL1.order_type_lookup_code IN ('QUANTITY','AMOUNT','RATE') ) --
AND ( POL1.unit_price > nvl ( POL2.not_to_exceed_price
, POL1.unit_price )
)
)
OR
( ( POL1.order_type_lookup_code IN ('FIXED PRICE') ) --
AND ( POL1.amount > nvl( POL2.not_to_exceed_price, POL1.amount ) )
)
);
INSERT into po_online_report_text_gt(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_name)
--SQL What: Querying for PO shipment totals that fail to meet the
--SQL minimum release amount of the GA line
--SQL Why: Add appropriate error message to po_online_report_text_gt
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
POL1.line_num,
0,
0,
p_sequence + ROWNUM,
substr(g_linemsg||g_delim||POL1.line_num||g_delim
||l_textline,1,240),
'PO_SUB_STD_GA_LINE_LESS_MINREL' -- <2710030>
FROM PO_LINES_GT POL1, PO_LINES_ALL POL2, PO_HEADERS_ALL POH
WHERE POL1.po_header_id = p_document_id
AND POL1.from_header_id = POL2.po_header_id --JOIN
AND POL1.from_line_id = POL2.po_line_id --JOIN
AND POL1.from_header_id = POH.po_header_id --JOIN
AND POH.type_lookup_code = 'BLANKET'
AND POH.global_agreement_flag = 'Y'
AND POL2.min_release_amount IS NOT NULL
AND POL2.min_release_amount >
--SQL What: Querying PO_LINE_LOCATIONS for the total amount of the
--SQL shipments in this PO that reference the current GA line
--SQL Why: This sum determines whether the minimum release amount
--SQL for the GA line has been met
( SELECT --
decode ( POL1.quantity
, NULL , decode ( sum ( PLL.amount
- nvl(PLL.amount_cancelled,0) )
, 0 , POL2.min_release_amount
, sum ( PLL.amount
- nvl(PLL.amount_cancelled,0) )
)
, decode ( sum ( PLL.quantity
- nvl(PLL.quantity_cancelled,0) )
, 0 , POL2.min_release_amount
, sum ( ( PLL.quantity
- nvl(PLL.quantity_cancelled,0) )
* PLL.price_override )
)
)
--
--Changing the query to go to PO_LINE_LOCATIONS_GT instead of PO_LINE_LOCATIONS
FROM PO_LINE_LOCATIONS_GT PLL
WHERE PLL.po_header_id = p_document_id
AND PLL.from_line_id = POL2.po_line_id);
* p_online_report_id: Id used to INSERT INTO online_report_text table
* p_user_id: User performing the action
* p_login_id: Last update login_id
* IN OUT PARAMETERS
* x_sequence: Sequence number of last reported error
* Modifies: Inserts error msgs in online_report_text_gt table, uses
* global_temp tables for processing
* Effects: This procedure performs checks for the lines referencing global
* contracts to enforce the following:
* 1) Contract is still enabled for purchasing in current OU
* 2) Supplier Site is still enabled on the referenced GC
* 3) Amount released should be less than amount limit on GC
* Returns:
* x_sequence: This parameter contains the current count of number of error
* messages inserted
* x_return_status: This parameter signifies whether the checks contained in
* this procedure completed successfully.
*/
PROCEDURE check_std_gc_ref
( p_document_id IN NUMBER,
p_online_report_id IN NUMBER,
p_user_id IN NUMBER,
p_login_id IN NUMBER,
x_sequence IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2
) IS
l_textline PO_ONLINE_REPORT_TEXT.text_line%TYPE := NULL;
INSERT INTO po_online_report_text_gt (
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_name
)
SELECT
p_online_report_id,
p_login_id,
p_user_id,
SYSDATE,
p_user_id,
SYSDATE,
POL.line_num,
0,
0,
x_sequence + ROWNUM,
SUBSTR (g_linemsg || g_delim || POL.line_num || g_delim || l_textline,
1,
240),
'PA_SUB_STD_GC_NOT_EN_PUR'
FROM
po_headers_gt POH,
po_lines_gt POL,
po_headers_all POHA
WHERE
POH.po_header_id = p_document_id
AND POL.po_header_id = POH.po_header_id
AND POL.contract_id = POHA.po_header_id
AND POHA.global_agreement_flag = 'Y'
AND NOT EXISTS --< Bug 3301427 Start >
(SELECT 'previously approved shipment'
FROM po_line_locations_gt pllg
WHERE pllg.po_line_id = pol.po_line_id
AND pllg.approved_date IS NOT NULL) --< Bug 3301427 End >
AND NOT EXISTS (SELECT 1
FROM po_ga_org_assignments PGOA,
po_system_parameters PSP
WHERE PGOA.po_header_id = POHA.po_header_id
AND PGOA.purchasing_org_id = PSP.org_id
AND PGOA.enabled_flag = 'Y');
INSERT INTO po_online_report_text_gt (
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_name
)
SELECT
p_online_report_id,
p_login_id,
p_user_id,
SYSDATE,
p_user_id,
SYSDATE,
POL.line_num,
0,
0,
x_sequence + ROWNUM,
SUBSTR (g_linemsg || g_delim || POL.line_num || g_delim || l_textline,
1,
240),
'PA_SUB_STD_GC_INVALID_SITE'
FROM
po_headers_gt POH,
po_lines_gt POL,
po_headers_all POHA
WHERE
POH.po_header_id = p_document_id
AND POL.po_header_id = POH.po_header_id
AND POL.contract_id = POHA.po_header_id
AND POHA.global_agreement_flag = 'Y'
AND NOT EXISTS --< Bug 3301427 Start >
(SELECT 'previously approved shipment'
FROM po_line_locations_gt pllg
WHERE pllg.po_line_id = pol.po_line_id
AND pllg.approved_date IS NOT NULL) --< Bug 3301427 End >
AND NOT EXISTS (SELECT 1
FROM po_ga_org_assignments PGOA
WHERE PGOA.po_header_id = POHA.po_header_id
AND PGOA.vendor_site_id = Decode( Nvl ( poha.Enable_All_Sites,'N'),'N',POH.vendor_site_id,pgoa.Vendor_Site_Id)
AND PGOA.enabled_flag = 'Y');
SELECT /*+ FULL(POL) ORDERED */ -- bug3413891
POL.line_num,
sum(DECODE (POL1.order_type_lookup_code
, 'RATE', POL1.amount
, 'FIXED PRICE', POL1.amount
, POL1.quantity * POL1.unit_price)* NVL(POHA1.rate,1)),
POHA.amount_limit* NVL(POHA.rate,1)
BULK COLLECT INTO l_curr_doc_line_num,
l_po_amount,
l_amount_limit
FROM po_lines_gt POL, -- target_line
po_headers_all POHA, -- global contract
po_lines_gt POL1, -- all lines in current doc
-- having the same contract ref
po_headers_all POHA1 -- document
WHERE
POL.po_header_id = p_document_id
AND POL.po_header_id = POHA1.po_header_id
AND POL.contract_id = POHA.po_header_id
AND POHA.global_agreement_flag = 'Y'
AND POHA.amount_limit IS NOT NULL
AND POL.contract_id = POL1.contract_id
GROUP BY POL.line_num,POHA.amount_limit* NVL(POHA.rate,1);
SELECT /*+ FULL(POL) */ -- bug3413891
POL.line_num,
sum(DECODE (POL1.order_type_lookup_code
, 'RATE', POL1.amount
, 'FIXED PRICE', POL1.amount
, POL1.quantity * POL1.unit_price)*NVL(POH1.rate,1))
BULK COLLECT INTO l_prev_doc_line_num,
l_prev_rel_amount
FROM po_lines_gt POL, -- target line
po_lines_all POL1, -- all lines from other doc with
-- the same GC ref
po_headers_all POH1, -- headers of lines in POL1
po_headers_all POHA -- global contract
WHERE
POL.po_header_id = p_document_id
AND POL.contract_id = POHA.po_header_id
AND POHA.global_agreement_flag = 'Y'
AND POHA.amount_limit IS NOT NULL
AND POL1.contract_id = POL.contract_id
AND POL1.po_header_id <> POL.po_header_id
AND POH1.po_header_id = POL1.po_header_id
AND POH1.approved_flag = 'Y'
GROUP BY POL.line_num;
INSERT INTO po_online_report_text_gt (
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_name
) VALUES (
p_online_report_id,
p_login_id,
p_user_id,
SYSDATE,
p_user_id,
SYSDATE,
l_curr_doc_line_num(l_curr_doc_line_index),
0,
0,
x_sequence + l_rowcount,
SUBSTR (g_linemsg || g_delim ||
l_curr_doc_line_num(l_curr_doc_line_index) ||
g_delim || l_textline, 1, 240),
'PO_SUB_STD_CONTRACT_AMT_LIMIT'
);
* p_online_report_id: Id used to INSERT INTO online_report_text table
* p_user_id: User performing the action
* p_login_id: Last update login_id
* IN OUT PARAMETERS
* p_sequence: Sequence number of last reported error
* Modifies: Inserts error msgs in online_report_text_gt table, uses
* global_temp tables for processing
* Effects: This procedure runs the document submission checks for CONTRACT
* PAs
* Returns:
* p_sequence: This parameter contains the current count of number of error
* messages inserted
*/
PROCEDURE check_contract_agreement(p_document_id IN NUMBER,
p_online_report_id IN NUMBER,
p_user_id IN NUMBER,
p_login_id IN NUMBER,
p_sequence IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2) IS
l_textline po_online_report_text.text_line%TYPE := NULL;
INSERT INTO po_online_report_text_gt(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_name)
SELECT p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
0,
0,
0,
p_sequence + ROWNUM,
substr(l_textline,1,240),
'PO_SUB_CONTRACT_AMT_LIMIT'
--
-- For local contract, need to take care of currency conversion
-- since the std PO referencing a contract may be in a different
-- currency
--
--Added a sum function to the sql's select clause
-- bug5138959
-- Added a sum function to the second select clause
-- bug5153099
-- Removed group by clause in subquery.Also removed the checking for
-- global_agreement_flag and the corresponding OR case logic.
FROM PO_HEADERS_GT POH
WHERE POH.po_header_id = p_document_id
AND POH.type_lookup_code = 'CONTRACT'
AND POH.amount_limit IS NOT NULL
AND ((POH.amount_limit * NVL(POH.rate, 1)) -- amt limit in fn currency --
<
(SELECT SUM( --Bug#4619187
DECODE (POL1.order_type_lookup_code
, 'RATE', POL1.amount
, 'FIXED PRICE', POL1.amount
, POL1.quantity * POL1.unit_price)
* NVL(POH1.rate,1)
)
FROM po_headers POH1,
po_lines POL1
WHERE POL1.contract_id = POH.po_header_id
AND POL1.po_header_id = POH1.po_header_id
AND NVL(POL1.cancel_flag, 'N') = 'N'
) -- amt released in fn currency
);
* p_online_report_id: Id used to INSERT INTO online_report_text table
* p_user_id: User performing the action
* p_login_id: Last update login_id
* IN OUT PARAMETERS
* p_sequence: Sequence number of last reported error
* Modifies: Inserts error msgs in online_report_text_gt table, uses
* global_temp tables for processing
* Effects: This procedure runs the document submission checks for HEADER
* level CBC (FPI Project) validations for PO, REQ, REL
* Returns:
* p_sequence: This parameter contains the current count of number of error
* messages inserted
*/
PROCEDURE do_cbc_related_validations(p_document_type IN VARCHAR2,
p_document_subtype IN VARCHAR2,
p_document_id IN NUMBER,
p_online_report_id IN NUMBER,
p_user_id IN NUMBER,
p_login_id IN NUMBER,
p_sequence IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2) IS
l_msg_count NUMBER;
FND_MSG_PUB.Delete_Msg(p_msg_index => FND_MSG_PUB.G_LAST);
INSERT INTO PO_ONLINE_REPORT_TEXT_GT (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_name)
VALUES (p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
0,
0,
0,
p_sequence + 1,
substr(l_textline,1,240),
'IGC_MULT_FISCAL_YEARS');
DELETE FROM po_headers_gt;
DELETE FROM po_lines_gt;
DELETE FROM po_line_locations_gt;
DELETE FROM po_distributions_gt;
DELETE FROM po_releases_gt;
DELETE FROM po_req_headers_gt;
DELETE FROM po_req_lines_gt;
DELETE FROM po_req_distributions_gt;
DELETE FROM po_online_report_text_gt;
SELECT po_header_id
INTO l_blanket_header_id
FROM po_releases_gt
WHERE po_release_id = l_doc_id;
FOR ship_rec in (select line_location_id from po_line_locations_gt)
LOOP
set_lcm_flag(ship_rec.line_location_id,'BEFORE',l_return_status);
* Private Procedure: UPDATE_GLOBAL_TEMP_TABLES
* Requires:
* IN PARAMETERS:
* p_document_type: Type of submitted document
* p_document_subtype: Subtype of submitted document
* p_document_id: Id of submitted document
* p_requested_changes: This object contains all the requested changes to
* the document. It contains 5 objects. These objects
* are: 1.Header_Changes 2.Release_Changes 3.Line_
* Changes 4.Shipment_Changes 5.Distribution_Changes.
* In FPI, following change requests are allowed:
* 1. HEADER_CHANGES: None
* 2. RELEASE_CHANGES: None
* 3. LINE_CHANGES: unit_price, vendor_product_num
* 4. SHIPMENT_CHANGES: quantity, promised_date,
* price_override
* 5. DISTRIBUTION_CHANGES: quantity_ordered
* Modifies:
* Effects: Updates the global temp tables with the changes in object
* p_requested_changes
* Returns:
*/
PROCEDURE update_global_temp_tables(p_document_type IN VARCHAR2,
p_document_subtype IN VARCHAR2,
p_document_id IN NUMBER,
-- Renamed the type to PO_CHANGES_REC_TYPE:
p_requested_changes IN PO_CHANGES_REC_TYPE,
x_return_status OUT NOCOPY VARCHAR2) IS
l_api_name CONSTANT varchar2(40) := 'UPDATE_GLOBAL_TEMP_TABLES';
UPDATE po_lines_gt
SET unit_price = nvl(p_requested_changes.line_changes.unit_price(i),unit_price),
vendor_product_num = nvl(p_requested_changes.line_changes.vendor_product_num(i),
vendor_product_num),
--
-- Added several changeable fields:
quantity = nvl(p_requested_changes.line_changes.quantity(i),
quantity),
start_date = nvl(p_requested_changes.line_changes.start_date(i),
start_date),
expiration_date =
nvl(p_requested_changes.line_changes.expiration_date(i),
expiration_date),
amount = nvl(p_requested_changes.line_changes.amount(i),
amount)
--
WHERE po_line_id = p_requested_changes.line_changes.po_line_id(i);
UPDATE po_line_locations_gt
SET price_override = nvl(p_requested_changes.line_changes.unit_price(i),price_override)
WHERE po_line_id = p_requested_changes.line_changes.po_line_id(i)
AND nvl(payment_type, 'NULL') NOT IN ('MILESTONE', 'ADVANCE')
-- : do not carry line price down in Qty Milestone case
;
UPDATE po_line_locations_gt
SET quantity = nvl(p_requested_changes.shipment_changes.quantity(i),quantity),
promised_date = nvl(p_requested_changes.shipment_changes.promised_date(i),
promised_date),
price_override = nvl(p_requested_changes.shipment_changes.price_override(i),
price_override),
--
-- Added several changeable fields:
need_by_date =
nvl(p_requested_changes.shipment_changes.need_by_date(i),
need_by_date),
ship_to_location_id =
nvl(p_requested_changes.shipment_changes.ship_to_location_id(i),
ship_to_location_id),
amount = nvl(p_requested_changes.shipment_changes.amount(i),
amount),
--
--
payment_type = nvl(p_requested_changes.shipment_changes.payment_type(i),
payment_type),
description = nvl(p_requested_changes.shipment_changes.description(i),
description),
value_basis = DECODE(p_requested_changes.shipment_changes.payment_type(i)
, NULL, value_basis
, 'RATE', 'QUANTITY'
, 'LUMPSUM', 'FIXED PRICE'
, 'MILESTONE', 'FIXED PRICE'
)
-- Note: the value basis decode assumes Milestone Pay Items are Amount
-- Milestones, since payment type is not changeable on Qty-based lines
--
WHERE line_location_id = p_requested_changes.shipment_changes.po_line_location_id(i)
AND p_requested_changes.shipment_changes.parent_line_location_id(i) IS NULL;
INSERT INTO po_line_locations_gt(
LINE_LOCATION_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
PO_HEADER_ID ,
PO_LINE_ID ,
LAST_UPDATE_LOGIN ,
CREATION_DATE ,
CREATED_BY ,
QUANTITY ,
QUANTITY_RECEIVED ,
QUANTITY_ACCEPTED ,
QUANTITY_REJECTED ,
QUANTITY_BILLED ,
QUANTITY_CANCELLED ,
UNIT_MEAS_LOOKUP_CODE ,
PO_RELEASE_ID ,
SHIP_TO_LOCATION_ID ,
SHIP_VIA_LOOKUP_CODE ,
NEED_BY_DATE ,
PROMISED_DATE ,
LAST_ACCEPT_DATE ,
PRICE_OVERRIDE ,
ENCUMBERED_FLAG ,
ENCUMBERED_DATE ,
UNENCUMBERED_QUANTITY ,
FOB_LOOKUP_CODE ,
FREIGHT_TERMS_LOOKUP_CODE ,
TAXABLE_FLAG ,
ESTIMATED_TAX_AMOUNT ,
FROM_HEADER_ID ,
FROM_LINE_ID ,
FROM_LINE_LOCATION_ID ,
START_DATE ,
END_DATE ,
LEAD_TIME ,
LEAD_TIME_UNIT ,
PRICE_DISCOUNT ,
TERMS_ID ,
APPROVED_FLAG ,
APPROVED_DATE ,
CLOSED_FLAG ,
CANCEL_FLAG ,
CANCELLED_BY ,
CANCEL_DATE ,
CANCEL_REASON ,
FIRM_STATUS_LOOKUP_CODE ,
FIRM_DATE ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
UNIT_OF_MEASURE_CLASS ,
ENCUMBER_NOW ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
INSPECTION_REQUIRED_FLAG ,
RECEIPT_REQUIRED_FLAG ,
QTY_RCV_TOLERANCE ,
QTY_RCV_EXCEPTION_CODE ,
ENFORCE_SHIP_TO_LOCATION_CODE ,
ALLOW_SUBSTITUTE_RECEIPTS_FLAG ,
DAYS_EARLY_RECEIPT_ALLOWED ,
DAYS_LATE_RECEIPT_ALLOWED ,
RECEIPT_DAYS_EXCEPTION_CODE ,
INVOICE_CLOSE_TOLERANCE ,
RECEIVE_CLOSE_TOLERANCE ,
SHIP_TO_ORGANIZATION_ID ,
SHIPMENT_NUM ,
SOURCE_SHIPMENT_ID ,
SHIPMENT_TYPE ,
CLOSED_CODE ,
REQUEST_ID ,
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
PROGRAM_UPDATE_DATE ,
GOVERNMENT_CONTEXT ,
RECEIVING_ROUTING_ID ,
ACCRUE_ON_RECEIPT_FLAG ,
CLOSED_REASON ,
CLOSED_DATE ,
CLOSED_BY ,
ORG_ID ,
GLOBAL_ATTRIBUTE1 ,
GLOBAL_ATTRIBUTE2 ,
GLOBAL_ATTRIBUTE3 ,
GLOBAL_ATTRIBUTE4 ,
GLOBAL_ATTRIBUTE5 ,
GLOBAL_ATTRIBUTE6 ,
GLOBAL_ATTRIBUTE7 ,
GLOBAL_ATTRIBUTE8 ,
GLOBAL_ATTRIBUTE9 ,
GLOBAL_ATTRIBUTE10 ,
GLOBAL_ATTRIBUTE11 ,
GLOBAL_ATTRIBUTE12 ,
GLOBAL_ATTRIBUTE13 ,
GLOBAL_ATTRIBUTE14 ,
GLOBAL_ATTRIBUTE15 ,
GLOBAL_ATTRIBUTE16 ,
GLOBAL_ATTRIBUTE17 ,
GLOBAL_ATTRIBUTE18 ,
GLOBAL_ATTRIBUTE19 ,
GLOBAL_ATTRIBUTE20 ,
GLOBAL_ATTRIBUTE_CATEGORY ,
QUANTITY_SHIPPED ,
COUNTRY_OF_ORIGIN_CODE ,
TAX_USER_OVERRIDE_FLAG ,
MATCH_OPTION ,
TAX_CODE_ID ,
CALCULATE_TAX_FLAG ,
CHANGE_PROMISED_DATE_REASON ,
NOTE_TO_RECEIVER ,
SECONDARY_QUANTITY ,
SECONDARY_UNIT_OF_MEASURE ,
PREFERRED_GRADE ,
SECONDARY_QUANTITY_RECEIVED ,
SECONDARY_QUANTITY_ACCEPTED ,
SECONDARY_QUANTITY_REJECTED ,
SECONDARY_QUANTITY_CANCELLED ,
VMI_FLAG ,
CONSIGNED_FLAG ,
RETROACTIVE_DATE ,
AMOUNT , --
DESCRIPTION , --
PAYMENT_TYPE , --
VALUE_BASIS --
)
SELECT
-- bug3611217
-- Use a sequence number rather than FND_API.G_MISS_NUM
-- since we have added unique constraint on LINE_LOCATION_ID
PO_LINE_LOCATIONS_S.nextval,
poll.LAST_UPDATE_DATE ,
poll.LAST_UPDATED_BY ,
poll.PO_HEADER_ID ,
poll.PO_LINE_ID ,
poll.LAST_UPDATE_LOGIN ,
poll.CREATION_DATE ,
poll.CREATED_BY ,
nvl(p_requested_changes.shipment_changes.quantity(i),poll.quantity),
-- Bug 3322019 START
-- Quantity received, accepted, cancelled, etc. should be
-- NULL or 0 on the split shipment.
decode(poll.quantity_received,NULL,NULL,0),
decode(poll.quantity_accepted,NULL,NULL,0),
decode(poll.quantity_rejected,NULL,NULL,0),
decode(poll.quantity_billed,NULL,NULL,0),
decode(poll.quantity_cancelled,NULL,NULL,0),
-- Bug 3322019 END
poll.UNIT_MEAS_LOOKUP_CODE ,
poll.PO_RELEASE_ID ,
-- Added as a changeable field:
nvl(p_requested_changes.shipment_changes.ship_to_location_id(i),
poll.ship_to_location_id),
poll.SHIP_VIA_LOOKUP_CODE ,
-- Added as a changeable field:
nvl(p_requested_changes.shipment_changes.need_by_date(i),
poll.need_by_date),
nvl(p_requested_changes.shipment_changes.promised_date(i),
poll.promised_date),
poll.LAST_ACCEPT_DATE ,
nvl(p_requested_changes.shipment_changes.price_override(i),
poll.price_override),
-- Bug 3322019 START
NULL, -- ENCUMBERED_FLAG
NULL, -- ENCUMBERED_DATE
NULL, -- UNENCUMBERED_QUANTITY
-- Bug 3322019 END
poll.FOB_LOOKUP_CODE ,
poll.FREIGHT_TERMS_LOOKUP_CODE ,
poll.TAXABLE_FLAG ,
0, -- ESTIMATED_TAX_AMOUNT (Bug 3322019)
poll.FROM_HEADER_ID ,
poll.FROM_LINE_ID ,
poll.FROM_LINE_LOCATION_ID ,
poll.START_DATE ,
poll.END_DATE ,
poll.LEAD_TIME ,
poll.LEAD_TIME_UNIT ,
poll.PRICE_DISCOUNT ,
poll.TERMS_ID ,
-- Bug 3322019 START
'N', -- APPROVED_FLAG
NULL, -- APPROVED_DATE
NULL, -- CLOSED_FLAG
'N', -- CANCEL_FLAG
NULL, -- CANCELLED_BY
NULL, -- CANCEL_DATE
NULL, -- CANCEL_REASON
-- Bug 3322019 END
poll.FIRM_STATUS_LOOKUP_CODE ,
poll.FIRM_DATE ,
poll.ATTRIBUTE_CATEGORY ,
poll.ATTRIBUTE1 ,
poll.ATTRIBUTE2 ,
poll.ATTRIBUTE3 ,
poll.ATTRIBUTE4 ,
poll.ATTRIBUTE5 ,
poll.ATTRIBUTE6 ,
poll.ATTRIBUTE7 ,
poll.ATTRIBUTE8 ,
poll.ATTRIBUTE9 ,
poll.ATTRIBUTE10 ,
poll.UNIT_OF_MEASURE_CLASS ,
poll.ENCUMBER_NOW ,
poll.ATTRIBUTE11 ,
poll.ATTRIBUTE12 ,
poll.ATTRIBUTE13 ,
poll.ATTRIBUTE14 ,
poll.ATTRIBUTE15 ,
poll.INSPECTION_REQUIRED_FLAG ,
poll.RECEIPT_REQUIRED_FLAG ,
poll.QTY_RCV_TOLERANCE ,
poll.QTY_RCV_EXCEPTION_CODE ,
poll.ENFORCE_SHIP_TO_LOCATION_CODE ,
poll.ALLOW_SUBSTITUTE_RECEIPTS_FLAG ,
poll.DAYS_EARLY_RECEIPT_ALLOWED ,
poll.DAYS_LATE_RECEIPT_ALLOWED ,
poll.RECEIPT_DAYS_EXCEPTION_CODE ,
poll.INVOICE_CLOSE_TOLERANCE ,
poll.RECEIVE_CLOSE_TOLERANCE ,
poll.SHIP_TO_ORGANIZATION_ID ,
--
--iSP is passing shipment_num now
nvl(p_requested_changes.shipment_changes.split_shipment_num(i),
FND_API.G_MISS_NUM),
--SHIPMENT_NUM ,
--
poll.SOURCE_SHIPMENT_ID ,
poll.SHIPMENT_TYPE ,
-- Bug 3322019 START
'OPEN', -- CLOSED_CODE
NULL, -- REQUEST_ID
NULL, -- PROGRAM_APPLICATION_ID
NULL, -- PROGRAM_ID
NULL, -- PROGRAM_UPDATE_DATE
-- Bug 3322019 START
poll.GOVERNMENT_CONTEXT ,
poll.RECEIVING_ROUTING_ID ,
poll.ACCRUE_ON_RECEIPT_FLAG ,
-- Bug 3322019 START
NULL, -- CLOSED_REASON
NULL, -- CLOSED_DATE
NULL, -- CLOSED_BY
-- Bug 3322019 END
poll.ORG_ID ,
poll.GLOBAL_ATTRIBUTE1 ,
poll.GLOBAL_ATTRIBUTE2 ,
poll.GLOBAL_ATTRIBUTE3 ,
poll.GLOBAL_ATTRIBUTE4 ,
poll.GLOBAL_ATTRIBUTE5 ,
poll.GLOBAL_ATTRIBUTE6 ,
poll.GLOBAL_ATTRIBUTE7 ,
poll.GLOBAL_ATTRIBUTE8 ,
poll.GLOBAL_ATTRIBUTE9 ,
poll.GLOBAL_ATTRIBUTE10 ,
poll.GLOBAL_ATTRIBUTE11 ,
poll.GLOBAL_ATTRIBUTE12 ,
poll.GLOBAL_ATTRIBUTE13 ,
poll.GLOBAL_ATTRIBUTE14 ,
poll.GLOBAL_ATTRIBUTE15 ,
poll.GLOBAL_ATTRIBUTE16 ,
poll.GLOBAL_ATTRIBUTE17 ,
poll.GLOBAL_ATTRIBUTE18 ,
poll.GLOBAL_ATTRIBUTE19 ,
poll.GLOBAL_ATTRIBUTE20 ,
poll.GLOBAL_ATTRIBUTE_CATEGORY ,
decode(poll.quantity_shipped,NULL,NULL,0), -- Bug 3322019
poll.COUNTRY_OF_ORIGIN_CODE ,
poll.TAX_USER_OVERRIDE_FLAG ,
poll.MATCH_OPTION ,
poll.TAX_CODE_ID ,
poll.CALCULATE_TAX_FLAG ,
poll.CHANGE_PROMISED_DATE_REASON ,
poll.NOTE_TO_RECEIVER ,
decode(poll.secondary_quantity,NULL,NULL,0), -- Bug 3322019
poll.SECONDARY_UNIT_OF_MEASURE ,
poll.PREFERRED_GRADE ,
-- Bug 3322019 START
decode(poll.secondary_quantity_received,NULL,NULL,0),
decode(poll.secondary_quantity_accepted,NULL,NULL,0),
decode(poll.secondary_quantity_rejected,NULL,NULL,0),
decode(poll.secondary_quantity_cancelled,NULL,NULL,0),
-- Bug 3322019 END
poll.VMI_FLAG ,
poll.CONSIGNED_FLAG ,
poll.RETROACTIVE_DATE ,
-- Added a changeable field:
NVL(p_requested_changes.shipment_changes.amount(i), poll.amount),
--
--
poll.DESCRIPTION,
poll.PAYMENT_TYPE,
DECODE(p_requested_changes.shipment_changes.payment_type(i)
, 'RATE', 'QUANTITY'
, 'LUMPSUM', 'FIXED PRICE'
, POL.order_type_lookup_code
)
-- Note: the value basis decode assumes Milestone Pay Items are Amount
-- Milestones, since payment type is not changeable on Qty-based lines
--
FROM po_line_locations poll
, po_lines_all pol --
WHERE poll.line_location_id =
p_requested_changes.shipment_changes.parent_line_location_id(i)
AND p_requested_changes.shipment_changes.po_line_location_id(i) IS NULL
AND poll.po_line_id = pol.po_line_id; --
UPDATE po_distributions_gt
SET quantity_ordered = nvl(p_requested_changes.distribution_changes.quantity_ordered(i),
quantity_ordered),
--
-- Added amount_ordered as a changeable field:
amount_ordered =
nvl(p_requested_changes.distribution_changes.amount_ordered(i),
amount_ordered)
--
WHERE po_distribution_id = p_requested_changes.distribution_changes.po_distribution_id(i);
END UPDATE_GLOBAL_TEMP_TABLES;
INSERT INTO po_headers_gt(
PO_HEADER_ID,
AGENT_ID,
TYPE_LOOKUP_CODE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
SEGMENT1,
SUMMARY_FLAG,
ENABLED_FLAG,
SEGMENT2,
SEGMENT3,
SEGMENT4,
SEGMENT5,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
LAST_UPDATE_LOGIN,
CREATION_DATE ,
CREATED_BY,
VENDOR_ID,
VENDOR_SITE_ID,
VENDOR_CONTACT_ID,
SHIP_TO_LOCATION_ID,
BILL_TO_LOCATION_ID ,
TERMS_ID ,
SHIP_VIA_LOOKUP_CODE ,
FOB_LOOKUP_CODE ,
FREIGHT_TERMS_LOOKUP_CODE,
STATUS_LOOKUP_CODE,
CURRENCY_CODE,
RATE_TYPE,
RATE_DATE,
RATE,
FROM_HEADER_ID,
FROM_TYPE_LOOKUP_CODE,
START_DATE,
END_DATE,
BLANKET_TOTAL_AMOUNT,
AUTHORIZATION_STATUS,
REVISION_NUM,
REVISED_DATE,
APPROVED_FLAG,
APPROVED_DATE,
AMOUNT_LIMIT,
MIN_RELEASE_AMOUNT,
NOTE_TO_AUTHORIZER,
NOTE_TO_VENDOR,
NOTE_TO_RECEIVER,
PRINT_COUNT,
PRINTED_DATE,
VENDOR_ORDER_NUM,
CONFIRMING_ORDER_FLAG,
COMMENTS,
REPLY_DATE,
REPLY_METHOD_LOOKUP_CODE,
RFQ_CLOSE_DATE,
QUOTE_TYPE_LOOKUP_CODE,
QUOTATION_CLASS_CODE,
QUOTE_WARNING_DELAY_UNIT,
QUOTE_WARNING_DELAY,
QUOTE_VENDOR_QUOTE_NUMBER,
ACCEPTANCE_REQUIRED_FLAG,
ACCEPTANCE_DUE_DATE,
CLOSED_DATE,
USER_HOLD_FLAG,
APPROVAL_REQUIRED_FLAG,
CANCEL_FLAG,
FIRM_STATUS_LOOKUP_CODE,
FIRM_DATE,
FROZEN_FLAG,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
CLOSED_CODE,
GOVERNMENT_CONTEXT,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
ORG_ID,
SUPPLY_AGREEMENT_FLAG,
EDI_PROCESSED_FLAG,
EDI_PROCESSED_STATUS,
GLOBAL_ATTRIBUTE_CATEGORY,
GLOBAL_ATTRIBUTE1,
GLOBAL_ATTRIBUTE2,
GLOBAL_ATTRIBUTE3,
GLOBAL_ATTRIBUTE4,
GLOBAL_ATTRIBUTE5,
GLOBAL_ATTRIBUTE6,
GLOBAL_ATTRIBUTE7,
GLOBAL_ATTRIBUTE8,
GLOBAL_ATTRIBUTE9,
GLOBAL_ATTRIBUTE10,
GLOBAL_ATTRIBUTE11,
GLOBAL_ATTRIBUTE12,
GLOBAL_ATTRIBUTE13,
GLOBAL_ATTRIBUTE14,
GLOBAL_ATTRIBUTE15,
GLOBAL_ATTRIBUTE16,
GLOBAL_ATTRIBUTE17,
GLOBAL_ATTRIBUTE18,
GLOBAL_ATTRIBUTE19,
GLOBAL_ATTRIBUTE20,
INTERFACE_SOURCE_CODE,
REFERENCE_NUM,
WF_ITEM_TYPE,
WF_ITEM_KEY,
MRC_RATE_TYPE,
MRC_RATE_DATE,
MRC_RATE,
PCARD_ID,
PRICE_UPDATE_TOLERANCE,
PAY_ON_CODE,
XML_FLAG,
XML_SEND_DATE,
XML_CHANGE_SEND_DATE,
GLOBAL_AGREEMENT_FLAG,
CONSIGNED_CONSUMPTION_FLAG,
CBC_ACCOUNTING_DATE,
CONTERMS_EXIST_FLAG --
, encumbrance_required_flag --
,enable_all_sites --
)
SELECT
PO_HEADER_ID,
AGENT_ID,
TYPE_LOOKUP_CODE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
SEGMENT1,
SUMMARY_FLAG,
ENABLED_FLAG,
SEGMENT2,
SEGMENT3,
SEGMENT4,
SEGMENT5,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
LAST_UPDATE_LOGIN,
CREATION_DATE ,
CREATED_BY,
VENDOR_ID,
VENDOR_SITE_ID,
VENDOR_CONTACT_ID,
SHIP_TO_LOCATION_ID,
BILL_TO_LOCATION_ID ,
TERMS_ID ,
SHIP_VIA_LOOKUP_CODE ,
FOB_LOOKUP_CODE ,
FREIGHT_TERMS_LOOKUP_CODE,
STATUS_LOOKUP_CODE,
CURRENCY_CODE,
RATE_TYPE,
RATE_DATE,
RATE,
FROM_HEADER_ID,
FROM_TYPE_LOOKUP_CODE,
START_DATE,
END_DATE,
BLANKET_TOTAL_AMOUNT,
AUTHORIZATION_STATUS,
REVISION_NUM,
REVISED_DATE,
APPROVED_FLAG,
APPROVED_DATE,
AMOUNT_LIMIT,
MIN_RELEASE_AMOUNT,
NOTE_TO_AUTHORIZER,
NOTE_TO_VENDOR,
NOTE_TO_RECEIVER,
PRINT_COUNT,
PRINTED_DATE,
VENDOR_ORDER_NUM,
CONFIRMING_ORDER_FLAG,
COMMENTS,
REPLY_DATE,
REPLY_METHOD_LOOKUP_CODE,
RFQ_CLOSE_DATE,
QUOTE_TYPE_LOOKUP_CODE,
QUOTATION_CLASS_CODE,
QUOTE_WARNING_DELAY_UNIT,
QUOTE_WARNING_DELAY,
QUOTE_VENDOR_QUOTE_NUMBER,
ACCEPTANCE_REQUIRED_FLAG,
ACCEPTANCE_DUE_DATE,
CLOSED_DATE,
USER_HOLD_FLAG,
APPROVAL_REQUIRED_FLAG,
CANCEL_FLAG,
FIRM_STATUS_LOOKUP_CODE,
FIRM_DATE,
FROZEN_FLAG,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
CLOSED_CODE,
GOVERNMENT_CONTEXT,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
ORG_ID,
SUPPLY_AGREEMENT_FLAG,
EDI_PROCESSED_FLAG,
EDI_PROCESSED_STATUS,
GLOBAL_ATTRIBUTE_CATEGORY,
GLOBAL_ATTRIBUTE1,
GLOBAL_ATTRIBUTE2,
GLOBAL_ATTRIBUTE3,
GLOBAL_ATTRIBUTE4,
GLOBAL_ATTRIBUTE5,
GLOBAL_ATTRIBUTE6,
GLOBAL_ATTRIBUTE7,
GLOBAL_ATTRIBUTE8,
GLOBAL_ATTRIBUTE9,
GLOBAL_ATTRIBUTE10,
GLOBAL_ATTRIBUTE11,
GLOBAL_ATTRIBUTE12,
GLOBAL_ATTRIBUTE13,
GLOBAL_ATTRIBUTE14,
GLOBAL_ATTRIBUTE15,
GLOBAL_ATTRIBUTE16,
GLOBAL_ATTRIBUTE17,
GLOBAL_ATTRIBUTE18,
GLOBAL_ATTRIBUTE19,
GLOBAL_ATTRIBUTE20,
INTERFACE_SOURCE_CODE,
REFERENCE_NUM,
WF_ITEM_TYPE,
WF_ITEM_KEY,
MRC_RATE_TYPE,
MRC_RATE_DATE,
MRC_RATE,
PCARD_ID,
PRICE_UPDATE_TOLERANCE,
PAY_ON_CODE,
XML_FLAG,
XML_SEND_DATE,
XML_CHANGE_SEND_DATE,
GLOBAL_AGREEMENT_FLAG,
CONSIGNED_CONSUMPTION_FLAG,
CBC_ACCOUNTING_DATE,
CONTERMS_EXIST_FLAG --
, encumbrance_required_flag --
, enable_all_sites --
FROM po_headers
WHERE po_header_id = p_document_id;
SELECT po_header_id, segment1 into t_po_header_id, t_segment1
from po_headers_gt where po_header_id = p_document_id;
INSERT INTO po_lines_gt(
PO_LINE_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
PO_HEADER_ID ,
LINE_TYPE_ID ,
LINE_NUM ,
LAST_UPDATE_LOGIN ,
CREATION_DATE ,
CREATED_BY ,
ITEM_ID ,
ITEM_REVISION ,
CATEGORY_ID ,
ITEM_DESCRIPTION ,
UNIT_MEAS_LOOKUP_CODE ,
QUANTITY_COMMITTED ,
COMMITTED_AMOUNT ,
ALLOW_PRICE_OVERRIDE_FLAG ,
NOT_TO_EXCEED_PRICE ,
LIST_PRICE_PER_UNIT ,
UNIT_PRICE ,
QUANTITY ,
UN_NUMBER_ID ,
HAZARD_CLASS_ID ,
NOTE_TO_VENDOR ,
FROM_HEADER_ID ,
FROM_LINE_ID ,
MIN_ORDER_QUANTITY ,
MAX_ORDER_QUANTITY ,
QTY_RCV_TOLERANCE ,
OVER_TOLERANCE_ERROR_FLAG ,
MARKET_PRICE ,
UNORDERED_FLAG ,
CLOSED_FLAG ,
USER_HOLD_FLAG ,
CANCEL_FLAG ,
CANCELLED_BY ,
CANCEL_DATE ,
CANCEL_REASON ,
FIRM_STATUS_LOOKUP_CODE ,
FIRM_DATE ,
VENDOR_PRODUCT_NUM ,
CONTRACT_NUM ,
TAXABLE_FLAG ,
TAX_NAME ,
TYPE_1099 ,
CAPITAL_EXPENSE_FLAG ,
NEGOTIATED_BY_PREPARER_FLAG ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
REFERENCE_NUM ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
MIN_RELEASE_AMOUNT ,
PRICE_TYPE_LOOKUP_CODE ,
CLOSED_CODE ,
PRICE_BREAK_LOOKUP_CODE ,
GOVERNMENT_CONTEXT ,
REQUEST_ID ,
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
PROGRAM_UPDATE_DATE ,
CLOSED_DATE ,
CLOSED_REASON ,
CLOSED_BY ,
TRANSACTION_REASON_CODE ,
ORG_ID ,
QC_GRADE ,
BASE_UOM ,
BASE_QTY ,
SECONDARY_UOM ,
SECONDARY_QTY ,
GLOBAL_ATTRIBUTE_CATEGORY ,
GLOBAL_ATTRIBUTE1 ,
GLOBAL_ATTRIBUTE2 ,
GLOBAL_ATTRIBUTE3 ,
GLOBAL_ATTRIBUTE4 ,
GLOBAL_ATTRIBUTE5 ,
GLOBAL_ATTRIBUTE6 ,
GLOBAL_ATTRIBUTE7 ,
GLOBAL_ATTRIBUTE8 ,
GLOBAL_ATTRIBUTE9 ,
GLOBAL_ATTRIBUTE10 ,
GLOBAL_ATTRIBUTE11 ,
GLOBAL_ATTRIBUTE12 ,
GLOBAL_ATTRIBUTE13 ,
GLOBAL_ATTRIBUTE14 ,
GLOBAL_ATTRIBUTE15 ,
GLOBAL_ATTRIBUTE16 ,
GLOBAL_ATTRIBUTE17 ,
GLOBAL_ATTRIBUTE18 ,
GLOBAL_ATTRIBUTE19 ,
GLOBAL_ATTRIBUTE20 ,
LINE_REFERENCE_NUM ,
PROJECT_ID ,
TASK_ID ,
EXPIRATION_DATE ,
TAX_CODE_ID ,
OKE_CONTRACT_HEADER_ID ,
OKE_CONTRACT_VERSION_ID ,
SECONDARY_QUANTITY ,
SECONDARY_UNIT_OF_MEASURE ,
PREFERRED_GRADE ,
AUCTION_HEADER_ID ,
AUCTION_DISPLAY_NUMBER ,
AUCTION_LINE_NUMBER ,
BID_NUMBER ,
BID_LINE_NUMBER ,
RETROACTIVE_DATE ,
CONTRACT_ID , --
START_DATE , --
AMOUNT , --
ORDER_TYPE_LOOKUP_CODE , --
PURCHASE_BASIS , --
MATCHING_BASIS --
)
SELECT
PO_LINE_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
PO_HEADER_ID ,
LINE_TYPE_ID ,
LINE_NUM ,
LAST_UPDATE_LOGIN ,
CREATION_DATE ,
CREATED_BY ,
ITEM_ID ,
ITEM_REVISION ,
CATEGORY_ID ,
ITEM_DESCRIPTION ,
UNIT_MEAS_LOOKUP_CODE ,
QUANTITY_COMMITTED ,
COMMITTED_AMOUNT ,
ALLOW_PRICE_OVERRIDE_FLAG ,
NOT_TO_EXCEED_PRICE ,
LIST_PRICE_PER_UNIT ,
UNIT_PRICE ,
QUANTITY ,
UN_NUMBER_ID ,
HAZARD_CLASS_ID ,
NOTE_TO_VENDOR ,
FROM_HEADER_ID ,
FROM_LINE_ID ,
MIN_ORDER_QUANTITY ,
MAX_ORDER_QUANTITY ,
QTY_RCV_TOLERANCE ,
OVER_TOLERANCE_ERROR_FLAG ,
MARKET_PRICE ,
UNORDERED_FLAG ,
CLOSED_FLAG ,
USER_HOLD_FLAG ,
CANCEL_FLAG ,
CANCELLED_BY ,
CANCEL_DATE ,
CANCEL_REASON ,
FIRM_STATUS_LOOKUP_CODE ,
FIRM_DATE ,
VENDOR_PRODUCT_NUM ,
CONTRACT_NUM ,
TAXABLE_FLAG ,
TAX_NAME ,
TYPE_1099 ,
CAPITAL_EXPENSE_FLAG ,
NEGOTIATED_BY_PREPARER_FLAG ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
REFERENCE_NUM ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
MIN_RELEASE_AMOUNT ,
PRICE_TYPE_LOOKUP_CODE ,
CLOSED_CODE ,
PRICE_BREAK_LOOKUP_CODE ,
GOVERNMENT_CONTEXT ,
REQUEST_ID ,
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
PROGRAM_UPDATE_DATE ,
CLOSED_DATE ,
CLOSED_REASON ,
CLOSED_BY ,
TRANSACTION_REASON_CODE ,
ORG_ID ,
QC_GRADE ,
BASE_UOM ,
BASE_QTY ,
SECONDARY_UOM ,
SECONDARY_QTY ,
GLOBAL_ATTRIBUTE_CATEGORY ,
GLOBAL_ATTRIBUTE1 ,
GLOBAL_ATTRIBUTE2 ,
GLOBAL_ATTRIBUTE3 ,
GLOBAL_ATTRIBUTE4 ,
GLOBAL_ATTRIBUTE5 ,
GLOBAL_ATTRIBUTE6 ,
GLOBAL_ATTRIBUTE7 ,
GLOBAL_ATTRIBUTE8 ,
GLOBAL_ATTRIBUTE9 ,
GLOBAL_ATTRIBUTE10 ,
GLOBAL_ATTRIBUTE11 ,
GLOBAL_ATTRIBUTE12 ,
GLOBAL_ATTRIBUTE13 ,
GLOBAL_ATTRIBUTE14 ,
GLOBAL_ATTRIBUTE15 ,
GLOBAL_ATTRIBUTE16 ,
GLOBAL_ATTRIBUTE17 ,
GLOBAL_ATTRIBUTE18 ,
GLOBAL_ATTRIBUTE19 ,
GLOBAL_ATTRIBUTE20 ,
LINE_REFERENCE_NUM ,
PROJECT_ID ,
TASK_ID ,
EXPIRATION_DATE ,
TAX_CODE_ID ,
OKE_CONTRACT_HEADER_ID ,
OKE_CONTRACT_VERSION_ID ,
SECONDARY_QUANTITY ,
SECONDARY_UNIT_OF_MEASURE ,
PREFERRED_GRADE ,
AUCTION_HEADER_ID ,
AUCTION_DISPLAY_NUMBER ,
AUCTION_LINE_NUMBER ,
BID_NUMBER ,
BID_LINE_NUMBER ,
RETROACTIVE_DATE ,
CONTRACT_ID , --
START_DATE , --
AMOUNT , --
ORDER_TYPE_LOOKUP_CODE , --
PURCHASE_BASIS , --
MATCHING_BASIS --
FROM po_lines_all pol
WHERE pol.po_line_id = l_line_id_tbl(i)
;
INSERT INTO po_releases_gt(
PO_RELEASE_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
PO_HEADER_ID ,
RELEASE_NUM ,
AGENT_ID ,
RELEASE_DATE ,
LAST_UPDATE_LOGIN ,
CREATION_DATE ,
CREATED_BY ,
REVISION_NUM ,
REVISED_DATE ,
APPROVED_FLAG ,
APPROVED_DATE ,
PRINT_COUNT ,
PRINTED_DATE ,
ACCEPTANCE_REQUIRED_FLAG ,
ACCEPTANCE_DUE_DATE ,
HOLD_BY ,
HOLD_DATE ,
HOLD_REASON ,
HOLD_FLAG ,
CANCEL_FLAG ,
CANCELLED_BY ,
CANCEL_DATE ,
CANCEL_REASON ,
FIRM_STATUS_LOOKUP_CODE ,
FIRM_DATE ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
AUTHORIZATION_STATUS ,
GOVERNMENT_CONTEXT ,
REQUEST_ID ,
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
PROGRAM_UPDATE_DATE ,
CLOSED_CODE ,
FROZEN_FLAG ,
RELEASE_TYPE ,
NOTE_TO_VENDOR ,
ORG_ID ,
EDI_PROCESSED_FLAG ,
GLOBAL_ATTRIBUTE_CATEGORY ,
GLOBAL_ATTRIBUTE1 ,
GLOBAL_ATTRIBUTE2 ,
GLOBAL_ATTRIBUTE3 ,
GLOBAL_ATTRIBUTE4 ,
GLOBAL_ATTRIBUTE5 ,
GLOBAL_ATTRIBUTE6 ,
GLOBAL_ATTRIBUTE7 ,
GLOBAL_ATTRIBUTE8 ,
GLOBAL_ATTRIBUTE9 ,
GLOBAL_ATTRIBUTE10 ,
GLOBAL_ATTRIBUTE11 ,
GLOBAL_ATTRIBUTE12 ,
GLOBAL_ATTRIBUTE13 ,
GLOBAL_ATTRIBUTE14 ,
GLOBAL_ATTRIBUTE15 ,
GLOBAL_ATTRIBUTE16 ,
GLOBAL_ATTRIBUTE17 ,
GLOBAL_ATTRIBUTE18 ,
GLOBAL_ATTRIBUTE19 ,
GLOBAL_ATTRIBUTE20 ,
WF_ITEM_TYPE ,
WF_ITEM_KEY ,
PCARD_ID ,
PAY_ON_CODE ,
XML_FLAG ,
XML_SEND_DATE ,
XML_CHANGE_SEND_DATE ,
CONSIGNED_CONSUMPTION_FLAG ,
CBC_ACCOUNTING_DATE)
SELECT
PO_RELEASE_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
PO_HEADER_ID ,
RELEASE_NUM ,
AGENT_ID ,
RELEASE_DATE ,
LAST_UPDATE_LOGIN ,
CREATION_DATE ,
CREATED_BY ,
REVISION_NUM ,
REVISED_DATE ,
APPROVED_FLAG ,
APPROVED_DATE ,
PRINT_COUNT ,
PRINTED_DATE ,
ACCEPTANCE_REQUIRED_FLAG ,
ACCEPTANCE_DUE_DATE ,
HOLD_BY ,
HOLD_DATE ,
HOLD_REASON ,
HOLD_FLAG ,
CANCEL_FLAG ,
CANCELLED_BY ,
CANCEL_DATE ,
CANCEL_REASON ,
FIRM_STATUS_LOOKUP_CODE ,
FIRM_DATE ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
AUTHORIZATION_STATUS ,
GOVERNMENT_CONTEXT ,
REQUEST_ID ,
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
PROGRAM_UPDATE_DATE ,
CLOSED_CODE ,
FROZEN_FLAG ,
RELEASE_TYPE ,
NOTE_TO_VENDOR ,
ORG_ID ,
EDI_PROCESSED_FLAG ,
GLOBAL_ATTRIBUTE_CATEGORY ,
GLOBAL_ATTRIBUTE1 ,
GLOBAL_ATTRIBUTE2 ,
GLOBAL_ATTRIBUTE3 ,
GLOBAL_ATTRIBUTE4 ,
GLOBAL_ATTRIBUTE5 ,
GLOBAL_ATTRIBUTE6 ,
GLOBAL_ATTRIBUTE7 ,
GLOBAL_ATTRIBUTE8 ,
GLOBAL_ATTRIBUTE9 ,
GLOBAL_ATTRIBUTE10 ,
GLOBAL_ATTRIBUTE11 ,
GLOBAL_ATTRIBUTE12 ,
GLOBAL_ATTRIBUTE13 ,
GLOBAL_ATTRIBUTE14 ,
GLOBAL_ATTRIBUTE15 ,
GLOBAL_ATTRIBUTE16 ,
GLOBAL_ATTRIBUTE17 ,
GLOBAL_ATTRIBUTE18 ,
GLOBAL_ATTRIBUTE19 ,
GLOBAL_ATTRIBUTE20 ,
WF_ITEM_TYPE ,
WF_ITEM_KEY ,
PCARD_ID ,
PAY_ON_CODE ,
XML_FLAG ,
XML_SEND_DATE ,
XML_CHANGE_SEND_DATE ,
CONSIGNED_CONSUMPTION_FLAG ,
CBC_ACCOUNTING_DATE
FROM po_releases
WHERE po_release_id = p_document_id;
INSERT INTO po_req_headers_gt(
PROGRAM_UPDATE_DATE ,
INTERFACE_SOURCE_CODE ,
INTERFACE_SOURCE_LINE_ID ,
CLOSED_CODE ,
ORG_ID ,
DESCRIPTION ,
AUTHORIZATION_STATUS ,
NOTE_TO_AUTHORIZER ,
TYPE_LOOKUP_CODE ,
TRANSFERRED_TO_OE_FLAG ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ON_LINE_FLAG ,
PRELIMINARY_RESEARCH_FLAG ,
RESEARCH_COMPLETE_FLAG ,
PREPARER_FINISHED_FLAG ,
PREPARER_FINISHED_DATE ,
AGENT_RETURN_FLAG ,
AGENT_RETURN_NOTE ,
CANCEL_FLAG ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
GOVERNMENT_CONTEXT ,
REQUEST_ID ,
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
REQUISITION_HEADER_ID ,
PREPARER_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
SEGMENT1 ,
SUMMARY_FLAG ,
ENABLED_FLAG ,
SEGMENT2 ,
SEGMENT3 ,
SEGMENT4 ,
SEGMENT5 ,
START_DATE_ACTIVE ,
END_DATE_ACTIVE ,
LAST_UPDATE_LOGIN ,
CREATION_DATE ,
CREATED_BY ,
WF_ITEM_TYPE ,
WF_ITEM_KEY ,
EMERGENCY_PO_NUM ,
PCARD_ID ,
APPS_SOURCE_CODE ,
CBC_ACCOUNTING_DATE)
SELECT
PROGRAM_UPDATE_DATE ,
INTERFACE_SOURCE_CODE ,
INTERFACE_SOURCE_LINE_ID ,
CLOSED_CODE ,
ORG_ID ,
DESCRIPTION ,
AUTHORIZATION_STATUS ,
substr(NOTE_TO_AUTHORIZER,1,480) , -- Bug4443295(added substr)
TYPE_LOOKUP_CODE ,
TRANSFERRED_TO_OE_FLAG ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ON_LINE_FLAG ,
PRELIMINARY_RESEARCH_FLAG ,
RESEARCH_COMPLETE_FLAG ,
PREPARER_FINISHED_FLAG ,
PREPARER_FINISHED_DATE ,
AGENT_RETURN_FLAG ,
AGENT_RETURN_NOTE ,
CANCEL_FLAG ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
GOVERNMENT_CONTEXT ,
REQUEST_ID ,
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
REQUISITION_HEADER_ID ,
PREPARER_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
SEGMENT1 ,
SUMMARY_FLAG ,
ENABLED_FLAG ,
SEGMENT2 ,
SEGMENT3 ,
SEGMENT4 ,
SEGMENT5 ,
START_DATE_ACTIVE ,
END_DATE_ACTIVE ,
LAST_UPDATE_LOGIN ,
CREATION_DATE ,
CREATED_BY ,
WF_ITEM_TYPE ,
WF_ITEM_KEY ,
EMERGENCY_PO_NUM ,
PCARD_ID ,
APPS_SOURCE_CODE ,
CBC_ACCOUNTING_DATE
FROM po_requisition_headers
WHERE requisition_header_id = p_document_id;
INSERT INTO po_req_lines_gt(
REQUEST_ID ,
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
PROGRAM_UPDATE_DATE ,
GOVERNMENT_CONTEXT ,
CLOSED_REASON ,
CLOSED_DATE ,
TRANSACTION_REASON_CODE ,
QUANTITY_RECEIVED ,
SOURCE_REQ_LINE_ID ,
ORG_ID ,
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 ,
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 ,
BOM_RESOURCE_ID ,
PARENT_REQ_LINE_ID ,
JUSTIFICATION ,
NOTE_TO_AGENT ,
NOTE_TO_RECEIVER ,
PURCHASING_AGENT_ID ,
DOCUMENT_TYPE_CODE ,
BLANKET_PO_HEADER_ID ,
BLANKET_PO_LINE_NUM ,
CURRENCY_CODE ,
RATE_TYPE ,
RATE_DATE ,
RATE ,
CURRENCY_UNIT_PRICE ,
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 ,
REQUISITION_LINE_ID ,
REQUISITION_HEADER_ID ,
LINE_NUM ,
LINE_TYPE_ID ,
CATEGORY_ID ,
ITEM_DESCRIPTION ,
UNIT_MEAS_LOOKUP_CODE ,
UNIT_PRICE ,
QUANTITY ,
AMOUNT , --
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 ,
ENCUMBERED_FLAG ,
RFQ_REQUIRED_FLAG ,
NEED_BY_DATE ,
LINE_LOCATION_ID ,
MODIFIED_BY_AGENT_FLAG ,
KANBAN_CARD_ID ,
CATALOG_TYPE ,
CATALOG_SOURCE ,
MANUFACTURER_ID ,
MANUFACTURER_NAME ,
MANUFACTURER_PART_NUMBER ,
REQUESTER_EMAIL ,
REQUESTER_FAX ,
REQUESTER_PHONE ,
UNSPSC_CODE ,
OTHER_CATEGORY_CODE ,
SUPPLIER_DUNS ,
TAX_STATUS_INDICATOR ,
PCARD_FLAG ,
NEW_SUPPLIER_FLAG ,
AUTO_RECEIVE_FLAG ,
TAX_USER_OVERRIDE_FLAG ,
TAX_CODE_ID ,
NOTE_TO_VENDOR ,
OKE_CONTRACT_HEADER_ID ,
OKE_CONTRACT_VERSION_ID ,
ITEM_SOURCE_ID ,
SUPPLIER_REF_NUMBER ,
SECONDARY_UNIT_OF_MEASURE ,
SECONDARY_QUANTITY ,
PREFERRED_GRADE ,
SECONDARY_QUANTITY_RECEIVED ,
SECONDARY_QUANTITY_CANCELLED ,
AUCTION_HEADER_ID ,
AUCTION_DISPLAY_NUMBER ,
AUCTION_LINE_NUMBER ,
REQS_IN_POOL_FLAG ,
VMI_FLAG ,
BID_NUMBER ,
BID_LINE_NUMBER)
SELECT
REQUEST_ID ,
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
PROGRAM_UPDATE_DATE ,
GOVERNMENT_CONTEXT ,
CLOSED_REASON ,
CLOSED_DATE ,
TRANSACTION_REASON_CODE ,
QUANTITY_RECEIVED ,
SOURCE_REQ_LINE_ID ,
ORG_ID ,
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 ,
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 ,
BOM_RESOURCE_ID ,
PARENT_REQ_LINE_ID ,
JUSTIFICATION ,
NOTE_TO_AGENT ,
NOTE_TO_RECEIVER ,
PURCHASING_AGENT_ID ,
DOCUMENT_TYPE_CODE ,
BLANKET_PO_HEADER_ID ,
BLANKET_PO_LINE_NUM ,
CURRENCY_CODE ,
RATE_TYPE ,
RATE_DATE ,
RATE ,
CURRENCY_UNIT_PRICE ,
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 ,
REQUISITION_LINE_ID ,
REQUISITION_HEADER_ID ,
LINE_NUM ,
LINE_TYPE_ID ,
CATEGORY_ID ,
ITEM_DESCRIPTION ,
UNIT_MEAS_LOOKUP_CODE ,
UNIT_PRICE ,
QUANTITY ,
AMOUNT , --
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 ,
ENCUMBERED_FLAG ,
RFQ_REQUIRED_FLAG ,
NEED_BY_DATE ,
LINE_LOCATION_ID ,
MODIFIED_BY_AGENT_FLAG ,
KANBAN_CARD_ID ,
CATALOG_TYPE ,
CATALOG_SOURCE ,
MANUFACTURER_ID ,
MANUFACTURER_NAME ,
MANUFACTURER_PART_NUMBER ,
REQUESTER_EMAIL ,
REQUESTER_FAX ,
REQUESTER_PHONE ,
UNSPSC_CODE ,
OTHER_CATEGORY_CODE ,
SUPPLIER_DUNS ,
TAX_STATUS_INDICATOR ,
PCARD_FLAG ,
NEW_SUPPLIER_FLAG ,
AUTO_RECEIVE_FLAG ,
TAX_USER_OVERRIDE_FLAG ,
TAX_CODE_ID ,
NOTE_TO_VENDOR ,
OKE_CONTRACT_HEADER_ID ,
OKE_CONTRACT_VERSION_ID ,
ITEM_SOURCE_ID ,
SUPPLIER_REF_NUMBER ,
SECONDARY_UNIT_OF_MEASURE ,
SECONDARY_QUANTITY ,
PREFERRED_GRADE ,
SECONDARY_QUANTITY_RECEIVED ,
SECONDARY_QUANTITY_CANCELLED ,
AUCTION_HEADER_ID ,
AUCTION_DISPLAY_NUMBER ,
AUCTION_LINE_NUMBER ,
REQS_IN_POOL_FLAG ,
VMI_FLAG ,
BID_NUMBER ,
BID_LINE_NUMBER
FROM po_requisition_lines
WHERE requisition_header_id = p_document_id;
INSERT INTO po_req_distributions_gt(
DISTRIBUTION_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
REQUISITION_LINE_ID ,
SET_OF_BOOKS_ID ,
CODE_COMBINATION_ID ,
REQ_LINE_QUANTITY ,
REQ_LINE_AMOUNT , --
LAST_UPDATE_LOGIN ,
CREATION_DATE ,
CREATED_BY ,
ENCUMBERED_FLAG ,
GL_ENCUMBERED_DATE ,
GL_ENCUMBERED_PERIOD_NAME ,
GL_CANCELLED_DATE ,
FAILED_FUNDS_LOOKUP_CODE ,
ENCUMBERED_AMOUNT ,
BUDGET_ACCOUNT_ID ,
ACCRUAL_ACCOUNT_ID ,
ORG_ID ,
VARIANCE_ACCOUNT_ID ,
PREVENT_ENCUMBRANCE_FLAG ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
GOVERNMENT_CONTEXT ,
REQUEST_ID ,
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
PROGRAM_UPDATE_DATE ,
PROJECT_ID ,
TASK_ID ,
EXPENDITURE_TYPE ,
PROJECT_ACCOUNTING_CONTEXT ,
EXPENDITURE_ORGANIZATION_ID ,
GL_CLOSED_DATE ,
SOURCE_REQ_DISTRIBUTION_ID ,
DISTRIBUTION_NUM ,
PROJECT_RELATED_FLAG ,
EXPENDITURE_ITEM_DATE ,
ALLOCATION_TYPE ,
ALLOCATION_VALUE ,
AWARD_ID ,
END_ITEM_UNIT_NUMBER ,
RECOVERABLE_TAX ,
NONRECOVERABLE_TAX ,
RECOVERY_RATE ,
TAX_RECOVERY_OVERRIDE_FLAG ,
OKE_CONTRACT_LINE_ID ,
OKE_CONTRACT_DELIVERABLE_ID)
SELECT
PRD.DISTRIBUTION_ID ,
PRD.LAST_UPDATE_DATE ,
PRD.LAST_UPDATED_BY ,
PRD.REQUISITION_LINE_ID ,
PRD.SET_OF_BOOKS_ID ,
PRD.CODE_COMBINATION_ID ,
PRD.REQ_LINE_QUANTITY ,
PRD.REQ_LINE_AMOUNT , --
PRD.LAST_UPDATE_LOGIN ,
PRD.CREATION_DATE ,
PRD.CREATED_BY ,
PRD.ENCUMBERED_FLAG ,
PRD.GL_ENCUMBERED_DATE ,
PRD.GL_ENCUMBERED_PERIOD_NAME ,
PRD.GL_CANCELLED_DATE ,
PRD.FAILED_FUNDS_LOOKUP_CODE ,
PRD.ENCUMBERED_AMOUNT ,
PRD.BUDGET_ACCOUNT_ID ,
PRD.ACCRUAL_ACCOUNT_ID ,
PRD.ORG_ID ,
PRD.VARIANCE_ACCOUNT_ID ,
PRD.PREVENT_ENCUMBRANCE_FLAG ,
PRD.ATTRIBUTE_CATEGORY ,
PRD.ATTRIBUTE1 ,
PRD.ATTRIBUTE2 ,
PRD.ATTRIBUTE3 ,
PRD.ATTRIBUTE4 ,
PRD.ATTRIBUTE5 ,
PRD.ATTRIBUTE6 ,
PRD.ATTRIBUTE7 ,
PRD.ATTRIBUTE8 ,
PRD.ATTRIBUTE9 ,
PRD.ATTRIBUTE10 ,
PRD.ATTRIBUTE11 ,
PRD.ATTRIBUTE12 ,
PRD.ATTRIBUTE13 ,
PRD.ATTRIBUTE14 ,
PRD.ATTRIBUTE15 ,
PRD.GOVERNMENT_CONTEXT ,
PRD.REQUEST_ID ,
PRD.PROGRAM_APPLICATION_ID ,
PRD.PROGRAM_ID ,
PRD.PROGRAM_UPDATE_DATE ,
PRD.PROJECT_ID ,
PRD.TASK_ID ,
PRD.EXPENDITURE_TYPE ,
PRD.PROJECT_ACCOUNTING_CONTEXT ,
PRD.EXPENDITURE_ORGANIZATION_ID ,
PRD.GL_CLOSED_DATE ,
PRD.SOURCE_REQ_DISTRIBUTION_ID ,
PRD.DISTRIBUTION_NUM ,
PRD.PROJECT_RELATED_FLAG ,
PRD.EXPENDITURE_ITEM_DATE ,
PRD.ALLOCATION_TYPE ,
PRD.ALLOCATION_VALUE ,
PRD.AWARD_ID ,
PRD.END_ITEM_UNIT_NUMBER ,
PRD.RECOVERABLE_TAX ,
PRD.NONRECOVERABLE_TAX ,
PRD.RECOVERY_RATE ,
PRD.TAX_RECOVERY_OVERRIDE_FLAG ,
PRD.OKE_CONTRACT_LINE_ID ,
PRD.OKE_CONTRACT_DELIVERABLE_ID
FROM po_req_distributions PRD, po_requisition_lines PRL
WHERE PRD.requisition_line_id = PRL.requisition_line_id AND
PRL.requisition_header_id = p_document_id;
'EXISTS (SELECT ''Y'' FROM PO_ACTION_HISTORY POAH2 ' ||
'WHERE POAH2.employee_id = ' || p_employee_id ||
' AND POAH2.object_type_code = (DECODE(' || p_query_table || '.type_lookup_code, ''BLANKET'', ''PA'', ''STANDARD'', ''PO'' , ''PLANNED'' , ''PO'' , ''CONTRACT'' , ''PA'', ''RELEASE'' , ''RELEASE'' ) )' ||
' AND POAH2.object_id = ' || p_query_table || '.' || l_id_column || '))))';*/
'EXISTS (SELECT ''Y'' FROM PO_ACTION_HISTORY POAH2 ' ||
'WHERE POAH2.employee_id =' || p_employee_id ||
/* Bug 7229262/7239696
' AND POAH2.object_type_code = (DECODE(' || p_query_table || '.type_lookup_code, ''BLANKET'', ''PA'', ''STANDARD'', ''PO'' , ''PLANNED'' , ''PO'' , ''CONTRACT'' , ''PA'', ''RELEASE'' , ''RELEASE'' ) ) ' ||
*/
' AND POAH2.object_type_code = (DECODE(' || p_query_table || '.'||l_doctype_column||', ''BLANKET'', ''PA'', ''STANDARD'', ''PO'' , ''PLANNED'' , ''PO'' , ''CONTRACT'' , ''PA'', ''RELEASE'' , ''RELEASE'' ) )' ||
/* End Bug 7229262/7239696 */
' AND POAH2.object_id = ' || p_query_table || '.' || l_id_column || ')) OR (' ||
p_employee_id || ' IN (SELECT H.superior_id ' ||
' FROM PO_EMPLOYEE_HIERARCHIES H, PO_SYSTEM_PARAMETERS PSP WHERE H.employee_id = ' ||
p_query_table || '.' || p_owner_id_column ||
' AND H.position_structure_id = NVL(PSP.SECURITY_POSITION_STRUCTURE_ID,-1) '
||'AND PSP.ORG_ID = '||p_query_table || '.ORG_ID '
||'))))';
'EXISTS (SELECT ''Y'' FROM PO_ACTION_HISTORY POAH2 ' ||
'WHERE POAH2.employee_id =' || p_employee_id ||
/* Bug 7229262/7239696
' AND POAH2.object_type_code = (DECODE(' || p_query_table || '.type_lookup_code, ''BLANKET'', ''PA'', ''STANDARD'', ''PO'' , ''PLANNED'' , ''PO'' , ''CONTRACT'' , ''PA'', ''RELEASE'' , ''RELEASE'' ) ) ' ||
*/
' AND POAH2.object_type_code = (DECODE(' || p_query_table || '.'||l_doctype_column||', ''BLANKET'', ''PA'', ''STANDARD'', ''PO'' , ''PLANNED'' , ''PO'' , ''CONTRACT'' , ''PA'', ''RELEASE'' , ''RELEASE'' ) ) ' ||
/* End Bug 7229262/7239696 */
' AND POAH2.object_id = ' || p_query_table || '.' || l_id_column || ')) OR (' ||
'EXISTS(SELECT NULL FROM PO_AGENTS WHERE agent_id= ' ||
p_employee_id || ' AND SYSDATE BETWEEN NVL(start_date_active, ' ||
'SYSDATE) AND NVL(end_date_active, SYSDATE+1))))' ||')'
||')';
l_update_allowed VARCHAR2(1);
INSERT INTO PO_SESSION_GT
( key,
num1
)
SELECT l_procedure_id,
1 -- Dummy Value
FROM DUAL
WHERE (p_release_id(i) IS NOT NULL
--Case 1: No Release is specified, PO Header has to be specified
-- Through any of HeaderId, DocNum-and-SubType, or VendorOrderNum
OR EXISTS (select null from po_headers_all h
WHERE h.po_header_id = l_header_id(i)
AND (h.authorization_status is NULL
OR h.authorization_status NOT IN ('PRE-APPROVED', 'IN PROCESS'))
AND (h.cancel_flag is null or h.cancel_flag <> 'Y')
AND (h.closed_code is NULL or h.closed_code NOT IN ('FINALLY CLOSED'))
AND (h.frozen_flag is NULL or h.frozen_flag <> 'Y')))
AND (p_release_id(i) IS NULL
--Case 2: PO Release is specified
OR EXISTS (select null from po_releases_all h
WHERE h.po_release_id = p_release_id(i)
AND (h.authorization_status is NULL
OR h.authorization_status NOT IN ('PRE-APPROVED', 'IN PROCESS'))
AND (h.cancel_flag is null or h.cancel_flag <> 'Y')
AND (h.closed_code is NULL or h.closed_code NOT IN ('FINALLY CLOSED'))
AND (h.frozen_flag is NULL or h.frozen_flag <> 'Y')))
AND (p_line_id(i) IS NULL
--Case 3: Optionally, Line is specified
OR EXISTS (SELECT null from po_lines_all l
WHERE l.po_line_id = p_line_id(i)
AND (l.cancel_flag is null or l.cancel_flag <> 'Y')
AND (l.closed_code is NULL or l.closed_code NOT IN ('FINALLY CLOSED'))))
AND (p_line_location_id(i) IS NULL
--Case 4: Optionally, Line Location is specified
OR EXISTS (SELECT null from po_line_locations_all l
WHERE l.line_location_id = p_line_location_id(i)
AND (l.cancel_flag is null or l.cancel_flag <> 'Y')
AND (l.closed_code is NULL or l.closed_code NOT IN ('FINALLY CLOSED')))) ;
DELETE FROM po_session_gt
WHERE key = l_procedure_id;
PO_DRAFTS_PVT.update_permission_check
( p_calling_module => p_calling_module,
p_po_header_id => p_header_id(i),
p_role => l_role,
p_skip_cat_upload_chk => l_skip_cat_upload_chk,
x_update_allowed => l_update_allowed,
x_locking_applicable => l_locking_applicable,
x_unlock_required => l_unlock_required,
x_message => l_message
);
IF (l_update_allowed = FND_API.G_FALSE) THEN
x_po_status_rec.updatable_flag(i) := 'N';
INSERT INTO PO_SESSION_GT
( key,
num1
)
SELECT l_procedure_id,
1 -- Dummy Value
FROM DUAL
WHERE (p_release_id(i) IS NOT NULL
--Case 1: No Release is specified, PO Header has to be specified
-- Through any of HeaderId, DocNum-and-SubType, or VendorOrderNum
-- Bug 3407980: Modified closed_code condition so that it only discards 'FINALLY CLOSED'
OR EXISTS (select null from po_headers_all h
WHERE h.po_header_id = l_header_id(i)
AND (h.authorization_status is NULL
OR h.authorization_status NOT IN ('APPROVED'))
AND (h.closed_code is NULL or h.closed_code <> 'FINALLY CLOSED')
AND (h.frozen_flag is NULL or h.frozen_flag <> 'Y')
AND (h.user_hold_flag is NULL or h.user_hold_flag <> 'Y')))
AND (p_release_id(i) IS NULL
--Case 2: PO Release is specified
-- Bug 3407980: Modified closed_code condition so that it only discards 'FINALLY CLOSED'
OR EXISTS (select null from po_releases_all h
WHERE h.po_release_id = p_release_id(i)
AND (h.authorization_status is NULL
OR h.authorization_status NOT IN ('APPROVED'))
AND (h.closed_code is NULL or h.closed_code <> 'FINALLY CLOSED')
AND (h.frozen_flag is NULL or h.frozen_flag <> 'Y')
AND (h.hold_flag is NULL or h.hold_flag <> 'Y')))
AND (p_line_id(i) IS NULL
--Case 3: Optionally, Line is specified
-- Bug 3407980: Modified closed_code condition so that it only discards 'FINALLY CLOSED'
OR EXISTS (SELECT null from po_lines_all l
WHERE l.po_line_id = p_line_id(i)
AND (l.closed_code is NULL or l.closed_code <> 'FINALLY CLOSED')))
AND (p_line_location_id(i) IS NULL
--Case 4: Optionally, Line Location is specified
-- Bug 3407980: Modified closed_code condition so that it only discards 'FINALLY CLOSED'
OR EXISTS (SELECT null from po_line_locations_all l
WHERE l.line_location_id = p_line_location_id(i)
AND (l.closed_code is NULL or l.closed_code <> 'FINALLY CLOSED')))
;
DELETE FROM po_session_gt
WHERE key = l_procedure_id;
INSERT INTO PO_SESSION_GT
( key,
num1
)
SELECT l_procedure_id,
1 -- Dummy Value
FROM DUAL
WHERE (p_release_id(i) IS NOT NULL
--Case 1: No Release is specified, PO Header has to be specified
-- Through any of HeaderId, DocNum-and-SubType, or VendorOrderNum
-- Bug 3407980: Modified closed_code condition so that it only discards 'FINALLY CLOSED'
OR EXISTS (select null from po_headers_all h
WHERE h.po_header_id = l_header_id(i)
AND (h.closed_code is NULL or h.closed_code <> 'FINALLY CLOSED')
AND (h.frozen_flag is NULL or h.frozen_flag <> 'Y')
AND (h.user_hold_flag is NULL or h.user_hold_flag <> 'Y')))
AND (p_release_id(i) IS NULL
--Case 2: PO Release is specified
-- Bug 3407980: Modified closed_code condition so that it only discards 'FINALLY CLOSED'
OR EXISTS (select null from po_releases_all h
WHERE h.po_release_id = p_release_id(i)
AND (h.closed_code is NULL or h.closed_code <> 'FINALLY CLOSED')
AND (h.frozen_flag is NULL or h.frozen_flag <> 'Y')
AND (h.hold_flag is NULL or h.hold_flag <> 'Y')))
AND (p_line_id(i) IS NULL
--Case 3: Optionally, Line is specified
-- Bug 3407980: Modified closed_code condition so that it only discards 'FINALLY CLOSED'
OR EXISTS (SELECT null from po_lines_all l
WHERE l.po_line_id = p_line_id(i)
AND (l.closed_code is NULL or l.closed_code <> 'FINALLY CLOSED')))
AND (p_line_location_id(i) IS NULL
--Case 4: Optionally, Line Location is specified
-- Bug 3407980: Modified closed_code condition so that it only discards 'FINALLY CLOSED'
OR EXISTS (SELECT null from po_line_locations_all l
WHERE l.line_location_id = p_line_location_id(i)
AND (l.closed_code is NULL or l.closed_code <> 'FINALLY CLOSED')))
;
DELETE FROM po_session_gt
WHERE key = l_procedure_id;
delete from po_headers_gt;
INSERT
INTO po_headers_gt
( AGENT_ID, TYPE_LOOKUP_CODE, LAST_UPDATE_DATE, LAST_UPDATED_BY,
SEGMENT1, SUMMARY_FLAG, ENABLED_FLAG,
authorization_status, approved_flag,
closed_code, cancel_flag,
frozen_flag, user_hold_flag, PO_HEADER_ID)
SELECT
AGENT_ID, TYPE_LOOKUP_CODE, LAST_UPDATE_DATE, LAST_UPDATED_BY,
SEGMENT1, SUMMARY_FLAG, ENABLED_FLAG,
NVL(authorization_status, 'INCOMPLETE'), nvl(approved_flag, 'N'),
nvl(closed_code, 'OPEN'), NVL(cancel_flag, 'N'),
NVL(frozen_flag, 'N'), NVL(user_hold_flag, 'N'), l_sequence(i)
FROM po_headers_all h
WHERE p_release_id(i) is null
AND h.po_header_id = p_header_id(i)
;
INSERT
INTO po_headers_gt
( AGENT_ID, TYPE_LOOKUP_CODE, LAST_UPDATE_DATE, LAST_UPDATED_BY,
SEGMENT1, SUMMARY_FLAG, ENABLED_FLAG,
authorization_status, approved_flag,
closed_code, cancel_flag,
frozen_flag, user_hold_flag, PO_HEADER_ID)
SELECT
0 dummy, ' ' dummy, LAST_UPDATE_DATE, LAST_UPDATED_BY,
' ' dummy, ' ' dummy, ' ' dummy,
NVL(authorization_status, 'INCOMPLETE'), nvl(approved_flag, 'N'),
nvl(closed_code, 'OPEN'), NVL(cancel_flag, 'N'),
NVL(frozen_flag, 'N'), NVL(hold_flag, 'N'), l_sequence(i)
FROM po_releases_all h
WHERE h.po_release_id = p_release_id(i)
;
UPDATE po_headers_gt gt
SET (closed_code, cancel_flag, user_hold_flag)
=
(SELECT nvl(closed_code, 'OPEN'), NVL(cancel_flag, 'N'), NVL(user_hold_flag, 'N')
FROM po_lines_all s
WHERE s.po_line_id = p_line_id(i))
WHERE p_line_id(i) is not null and gt.po_header_id = l_sequence(i)
;
UPDATE po_headers_gt gt
SET (approved_flag, closed_code, cancel_flag)
=
(SELECT nvl(approved_flag, 'N'), nvl(closed_code, 'OPEN'), NVL(cancel_flag, 'N')
FROM po_line_locations_all s
WHERE s.line_location_id = p_line_location_id(i))
WHERE p_line_location_id(i) is not null and gt.po_header_id = l_sequence(i)
;
SELECT
authorization_status, approved_flag, closed_code, cancel_flag, frozen_flag, user_hold_flag
BULK COLLECT INTO
x_po_status_rec.authorization_status, x_po_status_rec.approval_flag, x_po_status_rec.closed_code,
x_po_status_rec.cancel_flag, x_po_status_rec.frozen_flag, x_po_status_rec.hold_flag
FROM po_headers_gt
ORDER BY PO_HEADER_ID;
IF INSTR(p_mode, G_CHECK_UPDATEABLE) > 0 THEN --{
check_updatable (
p_count => l_count,
p_header_id => p_header_id,
p_release_id => p_release_id,
p_document_type => p_document_type,
p_document_subtype => p_document_subtype,
p_document_num => p_document_num,
p_vendor_order_num => p_vendor_order_num,
p_line_id => p_line_id,
p_line_location_id => p_line_location_id,
p_distribution_id => p_distribution_id,
p_lock_flag => p_lock_flag,
p_calling_module => p_calling_module, -- PDOI Rewrite R12
p_role => p_role, -- PDOI Rewrite R12
p_skip_cat_upload_chk => p_skip_cat_upload_chk, -- PDOI Rewrite R12
x_po_status_rec => x_po_status_rec,
x_return_status => x_return_status);
END IF; --}END of IF INSTR(p_mode, G_CHECK_UPDATEABLE) > 0
INSERT INTO PO_ONLINE_REPORT_TEXT_GT
( 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_name
)
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
, decode ( POL.quantity --
, NULL , PO_CORE_S.get_translated_text
( 'PO_SUB_SHIP_BILL_GT_ORD_AMT'
, 'LINE_NUM' , POL.line_num
, 'SHIP_NUM' , POLL.shipment_num
, 'AMT_BILLED' , nvl(POLL.amount_billed, 0)
, 'AMT_ORDERED' , nvl(POLL.amount, 0)
)
, PO_CORE_S.get_translated_text
( 'PO_SUB_SHIP_BILL_GT_ORD_QTY'
, 'LINE_NUM' , POL.line_num
, 'SHIP_NUM' , POLL.shipment_num
, 'QTY_BILLED' , nvl(POLL.quantity_billed, 0)
, 'QTY_ORDERED' , nvl(POLL.quantity, 0)
)
)
, decode ( POL.quantity --
, NULL , 'PO_SUB_SHIP_BILL_GT_ORD_AMT'
, 'PO_SUB_SHIP_BILL_GT_ORD_QTY'
)
FROM
PO_LINE_LOCATIONS_GT POLL
, PO_LINES_ALL POL -- For releases, PO_LINES_GT table isn't populated.
WHERE POLL.po_line_id = POL.po_line_id
AND nvl(POLL.cancel_flag, 'N') = 'N'
AND nvl(POLL.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND ( --
--: Use POLL value basis
( ( POLL.value_basis IN ('QUANTITY', 'AMOUNT') )
AND (nvl(POLL.quantity_billed,0) > nvl(POLL.quantity,0) ) )
OR ( ( POLL.value_basis IN ('FIXED PRICE', 'RATE') )
AND (nvl(POLL.amount_billed,0) > nvl(POLL.amount,0) ) )
--
)
;
INSERT INTO PO_ONLINE_REPORT_TEXT_GT
( 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_name
)
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
, decode ( POL.quantity --
, NULL , PO_CORE_S.get_translated_text
( 'PO_SUB_DIST_BILL_GT_ORD_AMT'
, 'LINE_NUM' , POL.line_num
, 'SHIP_NUM' , POLL.shipment_num
, 'DIST_NUM' , POD.distribution_num
, 'AMT_BILLED' , nvl(POD.amount_billed, 0)
, 'AMT_ORDERED' , nvl(POD.amount_ordered, 0)
)
, PO_CORE_S.get_translated_text
( 'PO_SUB_DIST_BILL_GT_ORD_QTY'
, 'LINE_NUM' , POL.line_num
, 'SHIP_NUM' , POLL.shipment_num
, 'DIST_NUM' , POD.distribution_num
, 'QTY_BILLED' , nvl(POD.quantity_billed, 0)
, 'QTY_ORDERED' , nvl(POD.quantity_ordered, 0)
)
)
, decode ( POL.quantity --
, NULL , 'PO_SUB_DIST_BILL_GT_ORD_AMT'
, 'PO_SUB_DIST_BILL_GT_ORD_QTY'
)
FROM
PO_DISTRIBUTIONS_GT POD
, PO_LINE_LOCATIONS_GT POLL
, PO_LINES_ALL POL -- For releases, PO_LINES_GT table isn't populated.
WHERE POD.line_location_id = POLL.line_location_id
AND POL.po_line_id = POLL.po_line_id
AND nvl(POLL.cancel_flag, 'N') = 'N'
AND nvl(POLL.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND ( --
( ( POL.quantity IS NOT NULL )
AND ( nvl(POD.quantity_billed,0) > nvl(POD.quantity_ordered,0) ) )
OR ( ( POL.amount IS NOT NULL )
AND ( nvl(POD.amount_billed,0) > nvl(POD.amount_ordered,0) ) )
)
;
INSERT INTO PO_ONLINE_REPORT_TEXT_GT
( 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_name
)
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
, decode ( POL.quantity --
, NULL , PO_CORE_S.get_translated_text
( 'PO_SUB_DIST_DLVR_GT_ORD_AMT'
, 'LINE_NUM' , POL.line_num
, 'SHIP_NUM' , POLL.shipment_num
, 'DIST_NUM' , POD.distribution_num
, 'AMT_DELIVERED' , nvl(POD.amount_delivered, 0)
, 'AMT_ORDERED' , nvl(POD.amount_ordered, 0)
)
, PO_CORE_S.get_translated_text
( 'PO_SUB_DIST_DLVR_GT_ORD_QTY'
, 'LINE_NUM' , POL.line_num
, 'SHIP_NUM' , POLL.shipment_num
, 'DIST_NUM' , POD.distribution_num
, 'QTY_DELIVERED' , nvl(POD.quantity_delivered, 0)
, 'QTY_ORDERED' , nvl(POD.quantity_ordered, 0)
)
)
, decode ( POL.quantity --
, NULL , 'PO_SUB_DIST_DLVR_GT_ORD_AMT'
, 'PO_SUB_DIST_DLVR_GT_ORD_QTY'
)
FROM
PO_DISTRIBUTIONS_GT POD
, PO_LINE_LOCATIONS_GT POLL
, PO_LINES_ALL POL -- For releases, PO_LINES_GT table isn't populated.
WHERE POD.line_location_id = POLL.line_location_id
AND POL.po_line_id = POLL.po_line_id
AND nvl(POLL.cancel_flag,'N') = 'N'
AND nvl(POLL.closed_code,'OPEN') <> 'FINALLY CLOSED'
AND ( --
( ( POL.quantity IS NOT NULL )
AND ( nvl(POD.quantity_delivered,0) > nvl(POD.quantity_ordered,0) ) )
OR ( ( POL.amount IS NOT NULL )
AND ( nvl(POD.amount_delivered,0) > nvl(POD.amount_ordered,0) ) )
)
;
INSERT INTO PO_LINE_LOCATIONS_GT (
line_location_id ,
last_update_date ,
last_updated_by ,
po_header_id ,
po_line_id ,
last_update_login ,
creation_date ,
created_by ,
quantity ,
quantity_received ,
quantity_accepted ,
quantity_rejected ,
quantity_billed ,
quantity_cancelled ,
amount, --
amount_received, --
amount_accepted, --
amount_rejected, --
amount_billed, --
amount_cancelled, --
unit_meas_lookup_code ,
po_release_id ,
ship_to_location_id ,
ship_via_lookup_code ,
need_by_date ,
promised_date ,
last_accept_date ,
price_override ,
encumbered_flag ,
encumbered_date ,
unencumbered_quantity ,
fob_lookup_code ,
freight_terms_lookup_code ,
taxable_flag ,
estimated_tax_amount ,
from_header_id ,
from_line_id ,
from_line_location_id ,
start_date ,
end_date ,
lead_time ,
lead_time_unit ,
price_discount ,
terms_id ,
approved_flag ,
approved_date ,
closed_flag ,
cancel_flag ,
cancelled_by ,
cancel_date ,
cancel_reason ,
firm_status_lookup_code ,
firm_date ,
attribute_category ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
unit_of_measure_class ,
encumber_now ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15 ,
inspection_required_flag ,
receipt_required_flag ,
qty_rcv_tolerance ,
qty_rcv_exception_code ,
enforce_ship_to_location_code ,
allow_substitute_receipts_flag ,
days_early_receipt_allowed ,
days_late_receipt_allowed ,
receipt_days_exception_code ,
invoice_close_tolerance ,
receive_close_tolerance ,
ship_to_organization_id ,
shipment_num ,
source_shipment_id ,
shipment_type ,
closed_code ,
request_id ,
program_application_id ,
program_id ,
program_update_date ,
government_context ,
receiving_routing_id ,
accrue_on_receipt_flag ,
closed_reason ,
closed_date ,
closed_by ,
org_id ,
global_attribute1 ,
global_attribute2 ,
global_attribute3 ,
global_attribute4 ,
global_attribute5 ,
global_attribute6 ,
global_attribute7 ,
global_attribute8 ,
global_attribute9 ,
global_attribute10 ,
global_attribute11 ,
global_attribute12 ,
global_attribute13 ,
global_attribute14 ,
global_attribute15 ,
global_attribute16 ,
global_attribute17 ,
global_attribute18 ,
global_attribute19 ,
global_attribute20 ,
global_attribute_category ,
quantity_shipped ,
country_of_origin_code ,
tax_user_override_flag ,
match_option ,
tax_code_id ,
calculate_tax_flag ,
change_promised_date_reason ,
note_to_receiver ,
secondary_quantity ,
secondary_unit_of_measure ,
preferred_grade ,
secondary_quantity_received ,
secondary_quantity_accepted ,
secondary_quantity_rejected ,
secondary_quantity_cancelled ,
vmi_flag ,
consigned_flag ,
retroactive_date ,
payment_type , --
description , --
value_basis --
)
SELECT
line_location_id ,
last_update_date ,
last_updated_by ,
po_header_id ,
po_line_id ,
last_update_login ,
creation_date ,
created_by ,
quantity ,
quantity_received ,
quantity_accepted ,
quantity_rejected ,
quantity_billed ,
quantity_cancelled ,
amount, --
amount_received, --
amount_accepted, --
amount_rejected, --
amount_billed, --
amount_cancelled, --
unit_meas_lookup_code ,
po_release_id ,
ship_to_location_id ,
ship_via_lookup_code ,
need_by_date ,
promised_date ,
last_accept_date ,
price_override ,
encumbered_flag ,
encumbered_date ,
unencumbered_quantity ,
fob_lookup_code ,
freight_terms_lookup_code ,
taxable_flag ,
estimated_tax_amount ,
from_header_id ,
from_line_id ,
from_line_location_id ,
start_date ,
end_date ,
lead_time ,
lead_time_unit ,
price_discount ,
terms_id ,
approved_flag ,
approved_date ,
closed_flag ,
cancel_flag ,
cancelled_by ,
cancel_date ,
cancel_reason ,
firm_status_lookup_code ,
firm_date ,
attribute_category ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
unit_of_measure_class ,
encumber_now ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15 ,
inspection_required_flag ,
receipt_required_flag ,
qty_rcv_tolerance ,
qty_rcv_exception_code ,
enforce_ship_to_location_code ,
allow_substitute_receipts_flag ,
days_early_receipt_allowed ,
days_late_receipt_allowed ,
receipt_days_exception_code ,
invoice_close_tolerance ,
receive_close_tolerance ,
ship_to_organization_id ,
shipment_num ,
source_shipment_id ,
shipment_type ,
closed_code ,
request_id ,
program_application_id ,
program_id ,
program_update_date ,
government_context ,
receiving_routing_id ,
accrue_on_receipt_flag ,
closed_reason ,
closed_date ,
closed_by ,
org_id ,
global_attribute1 ,
global_attribute2 ,
global_attribute3 ,
global_attribute4 ,
global_attribute5 ,
global_attribute6 ,
global_attribute7 ,
global_attribute8 ,
global_attribute9 ,
global_attribute10 ,
global_attribute11 ,
global_attribute12 ,
global_attribute13 ,
global_attribute14 ,
global_attribute15 ,
global_attribute16 ,
global_attribute17 ,
global_attribute18 ,
global_attribute19 ,
global_attribute20 ,
global_attribute_category ,
quantity_shipped ,
country_of_origin_code ,
tax_user_override_flag ,
match_option ,
tax_code_id ,
calculate_tax_flag ,
change_promised_date_reason ,
note_to_receiver ,
secondary_quantity ,
secondary_unit_of_measure ,
preferred_grade ,
secondary_quantity_received ,
secondary_quantity_accepted ,
secondary_quantity_rejected ,
secondary_quantity_cancelled ,
vmi_flag ,
consigned_flag ,
retroactive_date ,
payment_type , --
description , --
value_basis --
FROM PO_LINE_LOCATIONS_ALL POLL
WHERE POLL.line_location_id = l_line_location_id_tbl(i)
;
INSERT INTO PO_DISTRIBUTIONS_GT
(
po_distribution_id ,
last_update_date ,
last_updated_by ,
po_header_id ,
po_line_id ,
line_location_id ,
set_of_books_id ,
code_combination_id ,
quantity_ordered ,
amount_ordered, --
last_update_login ,
creation_date ,
created_by ,
po_release_id ,
quantity_delivered ,
quantity_billed ,
quantity_cancelled ,
amount_delivered, --
amount_billed ,
amount_cancelled, --
req_header_reference_num ,
req_line_reference_num ,
req_distribution_id ,
deliver_to_location_id ,
deliver_to_person_id ,
rate_date ,
rate ,
accrued_flag ,
encumbered_flag ,
encumbered_amount ,
unencumbered_quantity ,
unencumbered_amount ,
failed_funds_lookup_code ,
gl_encumbered_date ,
gl_encumbered_period_name ,
gl_cancelled_date ,
destination_type_code ,
destination_organization_id ,
destination_subinventory ,
attribute_category ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15 ,
wip_entity_id ,
wip_operation_seq_num ,
wip_resource_seq_num ,
wip_repetitive_schedule_id ,
wip_line_id ,
bom_resource_id ,
budget_account_id ,
accrual_account_id ,
variance_account_id ,
prevent_encumbrance_flag ,
government_context ,
destination_context ,
distribution_num ,
source_distribution_id ,
request_id ,
program_application_id ,
program_id ,
program_update_date ,
project_id ,
task_id ,
expenditure_type ,
project_accounting_context ,
expenditure_organization_id ,
gl_closed_date ,
accrue_on_receipt_flag ,
expenditure_item_date ,
org_id ,
kanban_card_id ,
award_id ,
mrc_rate_date ,
mrc_rate ,
mrc_encumbered_amount ,
mrc_unencumbered_amount ,
end_item_unit_number ,
tax_recovery_override_flag ,
recoverable_tax ,
nonrecoverable_tax ,
recovery_rate ,
oke_contract_line_id ,
oke_contract_deliverable_id
, distribution_type
, amount_to_encumber
)
SELECT
po_distribution_id ,
last_update_date ,
last_updated_by ,
po_header_id ,
po_line_id ,
line_location_id ,
set_of_books_id ,
code_combination_id ,
quantity_ordered ,
amount_ordered, --
last_update_login ,
creation_date ,
created_by ,
po_release_id ,
quantity_delivered ,
quantity_billed ,
quantity_cancelled ,
amount_delivered, --
amount_billed ,
amount_cancelled, --
req_header_reference_num ,
req_line_reference_num ,
req_distribution_id ,
deliver_to_location_id ,
deliver_to_person_id ,
rate_date ,
rate ,
accrued_flag ,
encumbered_flag ,
encumbered_amount ,
unencumbered_quantity ,
unencumbered_amount ,
failed_funds_lookup_code ,
gl_encumbered_date ,
gl_encumbered_period_name ,
gl_cancelled_date ,
destination_type_code ,
destination_organization_id ,
destination_subinventory ,
attribute_category ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15 ,
wip_entity_id ,
wip_operation_seq_num ,
wip_resource_seq_num ,
wip_repetitive_schedule_id ,
wip_line_id ,
bom_resource_id ,
budget_account_id ,
accrual_account_id ,
variance_account_id ,
prevent_encumbrance_flag ,
government_context ,
destination_context ,
distribution_num ,
source_distribution_id ,
request_id ,
program_application_id ,
program_id ,
program_update_date ,
project_id ,
task_id ,
expenditure_type ,
project_accounting_context ,
expenditure_organization_id ,
gl_closed_date ,
accrue_on_receipt_flag ,
expenditure_item_date ,
org_id ,
kanban_card_id ,
award_id ,
mrc_rate_date ,
mrc_rate ,
mrc_encumbered_amount ,
mrc_unencumbered_amount ,
end_item_unit_number ,
tax_recovery_override_flag ,
recoverable_tax ,
nonrecoverable_tax ,
recovery_rate ,
oke_contract_line_id ,
oke_contract_deliverable_id
, distribution_type
, amount_to_encumber
FROM PO_DISTRIBUTIONS_ALL POD
WHERE POD.po_distribution_id = l_dist_id_tbl(i)
;
SELECT
PRD.gl_encumbered_date
, PRL.line_num
, NULL
, PRD.distribution_num
BULK COLLECT INTO
l_date_tbl
, l_line_num_tbl
, l_shipment_num_tbl
, l_distribution_num_tbl
FROM
PO_REQ_DISTRIBUTIONS_GT PRD
, PO_REQ_LINES_GT PRL
, PO_REQ_HEADERS_GT PRH
WHERE PRL.requisition_line_id = PRD.requisition_line_id --JOIN
AND PRH.requisition_header_id = PRL.requisition_header_id --JOIN
AND PRL.line_location_id IS NULL
AND
( NVL(PRH.transferred_to_oe_flag,'N') <> 'Y'
OR NVL(PRL.source_type_code,'VENDOR') <> 'INVENTORY'
)
AND NVL(PRD.encumbered_flag,'N') = 'N'
AND NVL(PRL.cancel_flag,'N') = 'N'
AND NVL(PRL.closed_code,'OPEN') <> 'FINALLY CLOSED'
AND Nvl(prl.modified_by_agent_flag,'N') = 'N' /*Bug 4882209*/
;
SELECT
POD.gl_encumbered_date
, POL.line_num
, POLL.shipment_num
, POD.distribution_num
BULK COLLECT INTO
l_date_tbl
, l_line_num_tbl
, l_shipment_num_tbl
, l_distribution_num_tbl
FROM
PO_DISTRIBUTIONS_GT POD
, PO_LINE_LOCATIONS_GT POLL
, PO_LINES_ALL POL -- For Releases, PO_LINES_GT doesn't get populated.
, PO_HEADERS_GT POH
WHERE POLL.line_location_id(+) = POD.line_location_id --JOIN
AND POL.po_line_id(+) = POD.po_line_id --JOIN
-- PA distributions don't have associated lines or shipments
AND POH.po_header_id = POD.po_header_id --JOIN
AND NVL(POD.encumbered_flag,'N') = 'N'
AND NVL(POLL.cancel_flag,'N') = 'N'
AND NVL(POLL.closed_code,'OPEN') <> 'FINALLY CLOSED'
AND NVL(POLL.approved_flag,'N') <> 'Y'
AND ( ( p_doc_type = g_document_type_PA
AND POH.encumbrance_required_flag = 'Y'
)
OR ( p_doc_type <> g_document_type_PA )
)
;
SELECT PO_SESSION_GT_S.nextval
INTO l_dates_key
FROM DUAL
;
INSERT INTO PO_SESSION_GT
( key
, num1 -- line_num
, num2 -- shipment_num
, num3 -- distribution_num
, char1 -- period_name
)
VALUES
( l_dates_key
, l_line_num_tbl(i)
, l_shipment_num_tbl(i)
, l_distribution_num_tbl(i)
, l_period_name_tbl(i)
)
;
INSERT INTO PO_ONLINE_REPORT_TEXT_GT
( 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_name
)
SELECT
p_online_report_id
, p_login_id
, p_user_id
, sysdate
, p_user_id
, sysdate
, NVL(DATES.num1,0) -- line_num
, NVL(DATES.num2,0) -- shipment_num
, NVL(DATES.num3,0) -- distribution_num
, p_sequence + rownum
, substr(
DECODE( DATES.num1 -- line_num
, NULL, ''
, g_linemsg||g_delim||TO_CHAR(DATES.num1)||g_delim
)
||
DECODE( DATES.num2 -- shipment_num
, NULL, ''
, g_shipmsg||g_delim||TO_CHAR(DATES.num2)||g_delim
)
||
DECODE( p_doc_type
, g_document_type_PA, ''
, g_distmsg||g_delim||TO_CHAR(DATES.num3)||g_delim
)
||l_textline
, 1
, 240
)
, l_msg_name
FROM PO_SESSION_GT DATES
WHERE DATES.key = l_dates_key
AND DATES.char1 IS NULL -- period_name not found
;
SELECT NVL(MIN(po_header_id), '-999')
INTO x_header_id(i)
FROM po_headers
WHERE segment1 = p_document_num(i)
AND type_lookup_code = p_type_lookup_code(i)
AND po_header_id = NVL(x_header_id(i), po_header_id);
SELECT NVL(MIN(po_header_id), '-999')
INTO x_header_id(i)
FROM po_headers_all
WHERE vendor_order_num = p_vendor_order_num(i)
AND po_header_id = NVL(x_header_id(i), po_header_id);
SELECT vendor_id, vendor_site_id
INTO l_vendor_id, l_vendor_site_id
FROM po_headers_all
WHERE po_header_id = p_document_id;
INSERT INTO po_online_report_text_gt
(online_report_id
,last_updated_by
,last_update_date
,created_by
,creation_date
,line_num
,shipment_num
,distribution_num
,sequence
,text_line
,message_name)
VALUES
(p_online_report_id
,p_login_id
,sysdate
,p_user_id
,sysdate
,0 ,0 ,0
,p_sequence+1
,substr(l_text_line,1,240)
,'PO_VENDOR_SITE_CCR_INVALID');
INSERT INTO PO_ONLINE_REPORT_TEXT_GT
( 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_name
)
SELECT
p_online_report_id
, p_login_id
, p_user_id
, SYSDATE
, p_user_id
, SYSDATE
, 0
, 0
, 0
, p_sequence + ROWNUM
, substr(l_textline, 1, 240)
, 'PO_CAN_POH_WITH_RCV_TRX'
FROM po_headers_gt poh
WHERE EXISTS
(
SELECT 'Eligible shipment'
FROM po_line_locations_gt poll
WHERE poll.po_header_id = poh.po_header_id
AND NVL(poll.cancel_flag, 'N') = 'N'
AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
)
AND EXISTS
(
SELECT 'Transaction to process'
FROM rcv_transactions_interface rti
WHERE rti.processing_status_code = 'PENDING'
AND rti.po_header_id = poh.po_header_id
);
INSERT INTO PO_ONLINE_REPORT_TEXT_GT
( 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_name
)
SELECT
p_online_report_id
, p_login_id
, p_user_id
, SYSDATE
, p_user_id
, SYSDATE
, 0
, 0
, 0
, p_sequence + ROWNUM
, substr(l_textline, 1, 240)
, 'PO_CAN_REL_WITH_RCV_TRX'
FROM po_releases_gt por
WHERE EXISTS
(
SELECT 'Eligible shipment'
FROM po_line_locations_gt poll
WHERE poll.po_release_id = por.po_release_id
AND NVL(poll.cancel_flag, 'N') = 'N'
AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
)
AND EXISTS
(
SELECT 'Transaction to process'
FROM rcv_transactions_interface rti
WHERE rti.processing_status_code = 'PENDING'
AND rti.po_release_id = por.po_release_id
);
INSERT INTO PO_ONLINE_REPORT_TEXT_GT
( 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_name
)
SELECT
p_online_report_id
, p_login_id
, p_user_id
, SYSDATE
, p_user_id
, SYSDATE
, pol.line_num
, 0
, 0
, p_sequence + ROWNUM
, substr(g_linemsg || g_delim || pol.line_num || g_delim || l_textline, 1, 240)
, 'PO_CAN_POL_WITH_RCV_TRX'
FROM po_lines_gt pol
WHERE EXISTS
(
SELECT 'Eligible shipment'
FROM po_line_locations_gt poll
WHERE poll.po_line_id = pol.po_line_id
AND NVL(poll.cancel_flag, 'N') = 'N'
AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
)
AND EXISTS
(
SELECT 'Transaction to process'
FROM rcv_transactions_interface rti
WHERE rti.processing_status_code = 'PENDING'
AND rti.po_line_id = pol.po_line_id
);
INSERT INTO PO_ONLINE_REPORT_TEXT_GT
( 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_name
)
SELECT
p_online_report_id
, p_login_id
, p_user_id
, SYSDATE
, p_user_id
, SYSDATE
, DECODE(p_document_type, g_document_type_RELEASE, 0, pol.line_num)
, poll.shipment_num
, 0
, p_sequence + ROWNUM
, DECODE(p_document_type,
g_document_type_RELEASE, substr(g_shipmsg || g_delim || poll.shipment_num
|| g_delim || l_textline, 1, 240),
substr(g_linemsg || g_delim || pol.line_num || g_delim || g_shipmsg || g_delim
|| poll.shipment_num || g_delim || l_textline, 1, 240)
)
, DECODE(p_document_type, g_document_type_RELEASE, 'PO_CAN_RELS_WITH_RCV_TRX',
'PO_CAN_POLL_WITH_RCV_TRX')
FROM po_lines_gt pol, po_line_locations_gt poll
WHERE pol.po_line_id = poll.po_line_id
AND NVL(poll.cancel_flag, 'N') = 'N'
AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND EXISTS
(
SELECT 'Transaction to process'
FROM rcv_transactions_interface rti
WHERE rti.processing_status_code = 'PENDING'
AND rti.po_line_location_id = poll.line_location_id
);
INSERT INTO PO_ONLINE_REPORT_TEXT_GT
( 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_name
)
SELECT
p_online_report_id
, p_login_id
, p_user_id
, SYSDATE
, p_user_id
, SYSDATE
, DECODE(p_document_type, g_document_type_RELEASE, 0, pol.line_num)
, poll.shipment_num
, 0
, p_sequence + ROWNUM
, DECODE(p_document_type, g_document_type_RELEASE,
substr(g_shipmsg || g_delim || poll.shipment_num || g_delim || l_text_normal_po, 1, 240),
substr(g_linemsg || g_delim || pol.line_num || g_delim || g_shipmsg || g_delim
|| poll.shipment_num || g_delim ||
DECODE(poll.payment_type , NULL, l_text_normal_po, l_text_complex_po), 1, 240))
, DECODE(poll.payment_type, NULL, 'PO_CAN_POLL_WITH_ASN', 'PO_CAN_POLL_WITH_PENDING_WCR')
FROM po_lines_gt pol, po_line_locations_gt poll
WHERE NVL(poll.cancel_flag, 'N') = 'N'
AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
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'
);
INSERT INTO PO_ONLINE_REPORT_TEXT_GT
( 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_name
)
SELECT
p_online_report_id
, p_login_id
, p_user_id
, SYSDATE
, p_user_id
, SYSDATE
, DECODE(p_document_type, g_document_type_RELEASE, 0, pol.line_num)
, poll.shipment_num
, 0
, p_sequence + ROWNUM
, substr(
DECODE(p_document_type, g_document_type_RELEASE,
g_shipmsg || g_delim || poll.shipment_num || g_delim
|| PO_CORE_S.get_translated_text('PO_CAN_POLL_REC_NOT_DEL'
, 'QTY2', NVL(poll.quantity_received, 0)
, 'QTY1', sum(NVL(pod.quantity_delivered, 0))
),
g_linemsg || g_delim || pol.line_num || g_delim || g_shipmsg || g_delim
|| poll.shipment_num || g_delim
|| PO_CORE_S.get_translated_text('PO_CAN_POLL_REC_NOT_DEL'
, 'QTY2', NVL(poll.quantity_received, 0)
, 'QTY1', sum(NVL(pod.quantity_delivered, 0))
)
), 1, 240)
, 'PO_CAN_POLL_REC_NOT_DEL'
FROM po_lines_gt pol, po_line_locations_gt poll, po_distributions_gt pod
WHERE pod.line_location_id = poll.line_location_id
AND pol.po_line_id = poll.po_line_id
AND pol.order_type_lookup_code NOT IN ('RATE', 'FIXED PRICE')
AND NVL(poll.cancel_flag, 'N') = 'N'
AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND NVL(poll.quantity_received, 0) >
(
SELECT sum(NVL(pod2.quantity_delivered, 0))
FROM po_distributions_gt pod2
WHERE pod2.line_location_id = poll.line_location_id
)
GROUP BY pol.line_num
, poll.shipment_num
, NVL(poll.quantity_received, 0)
, ROWNUM --
;
INSERT INTO PO_ONLINE_REPORT_TEXT_GT
( 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_name
)
SELECT
p_online_report_id
, p_login_id
, p_user_id
, SYSDATE
, p_user_id
, SYSDATE
, DECODE(p_document_type, g_document_type_RELEASE, 0, pol.line_num)
, poll.shipment_num
, 0
, p_sequence + ROWNUM
, substr(
DECODE(p_document_type, g_document_type_RELEASE,
g_shipmsg || g_delim || poll.shipment_num || g_delim
|| PO_CORE_S.get_translated_text('PO_CAN_POLL_AMT_REC_NOT_DEL'
, 'QTY2', NVL(poll.amount_received, 0)
, 'QTY1', sum(NVL(pod.amount_delivered, 0))
),
g_linemsg || g_delim || pol.line_num || g_delim || g_shipmsg || g_delim
|| poll.shipment_num || g_delim
|| PO_CORE_S.get_translated_text('PO_CAN_POLL_AMT_REC_NOT_DEL'
, 'QTY2', NVL(poll.amount_received, 0)
, 'QTY1', sum(NVL(pod.amount_delivered, 0))
)
), 1, 240)
, 'PO_CAN_POLL_AMT_REC_NOT_DEL'
FROM po_lines_gt pol, po_line_locations_gt poll, po_distributions_gt pod
WHERE pod.line_location_id = poll.line_location_id
AND pol.po_line_id = poll.po_line_id
AND pol.order_type_lookup_code IN ('RATE', 'FIXED PRICE')
AND NVL(poll.cancel_flag, 'N') = 'N'
AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND NVL(poll.amount_received, 0) >
(
SELECT sum(NVL(pod2.amount_delivered, 0))
FROM po_distributions_gt pod2
WHERE pod2.line_location_id = poll.line_location_id
)
GROUP BY pol.line_num
, poll.shipment_num
, NVL(poll.amount_received, 0)
, ROWNUM --
;
INSERT INTO PO_ONLINE_REPORT_TEXT_GT
( 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_name
)
SELECT
p_online_report_id
, p_login_id
, p_user_id
, SYSDATE
, p_user_id
, SYSDATE
, DECODE(p_document_type, g_document_type_RELEASE, 0, pol.line_num)
, poll.shipment_num
, pod.distribution_num
, p_sequence + ROWNUM
, substr(
DECODE(p_document_type, g_document_type_RELEASE,
g_shipmsg || g_delim || poll.shipment_num || g_delim || g_distmsg || g_delim || l_textline
, g_linemsg || g_delim || pol.line_num || g_delim || g_shipmsg || g_delim
|| poll.shipment_num || g_delim || g_distmsg || g_delim || l_textline
), 1, 240)
, 'PO_CAN_POLL_INVALID_ACCT_FLEX'
FROM po_lines_gt pol, po_line_locations_gt poll
, po_distributions_gt pod, gl_code_combinations gcc
WHERE 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 NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND ( NVL(poll.cancel_flag, 'N') = 'N' OR p_action_requested = g_action_FINAL_CLOSE_CHECK)
AND gcc.code_combination_id = pod.code_combination_id
AND 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));
INSERT INTO PO_ONLINE_REPORT_TEXT_GT
( 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_name
)
SELECT
p_online_report_id
, p_login_id
, p_user_id
, SYSDATE
, p_user_id
, SYSDATE
, pol.line_num
, 0
, 0
, p_sequence + ROWNUM
, substr(g_linemsg || g_delim || pol.line_num || g_delim || l_textline,1,240) --Bug5096900
, 'PO_CAN_POL_PLAN_WITH_OPEN_REL'
FROM po_lines_gt pol
WHERE EXISTS
(
SELECT 'Uncancelled Open Releases Exist'
FROM po_line_locations pll
WHERE pll.po_line_id = pol.po_line_id
AND pll.shipment_type = 'BLANKET'
AND NVL(pll.cancel_flag, 'N') = 'N'
AND NVL(pll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
);
INSERT INTO PO_ONLINE_REPORT_TEXT_GT
( 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_name
)
SELECT
p_online_report_id
, p_login_id
, p_user_id
, SYSDATE
, p_user_id
, SYSDATE
, pol.line_num
, 0
, 0
, p_sequence + ROWNUM
, substr(g_linemsg || g_delim || pol.line_num || g_delim || l_textline,1,240) --Bug5096900
, 'PO_CAN_GAL_WITH_OPEN_STD_REF'
FROM po_lines_gt pol
WHERE EXISTS
(
SELECT 'Uncancelled Std PO lines referencing this GA line exist'
FROM po_lines_all pol2
WHERE pol2.from_line_id = pol.po_line_id
AND NVL(pol2.cancel_flag, 'N') = 'N'
AND NVL(pol2.closed_code, 'OPEN') <> 'FINALLY CLOSED'
);
INSERT INTO PO_ONLINE_REPORT_TEXT_GT
( 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_name
)
SELECT
p_online_report_id
, p_login_id
, p_user_id
, SYSDATE
, p_user_id
, SYSDATE
, 0
, 0
, 0
, p_sequence + ROWNUM
, substr(l_textline,1,240) --Bug5096900
, 'PO_CAN_CGA_WITH_OPEN_STD_REF'
FROM po_headers_gt poh
WHERE EXISTS
(
SELECT 'Open Std PO lines referencing this contract exist'
FROM po_lines_all pol
WHERE pol.contract_id = poh.po_header_id
AND NVL(pol.cancel_flag, 'N') = 'N'
AND NVL(pol.closed_code, 'OPEN') <> 'FINALLY CLOSED'
);
INSERT INTO PO_ONLINE_REPORT_TEXT_GT
( 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_name
)
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
, substr(g_linemsg || g_delim || pol.line_num || g_delim || g_shipmsg
|| poll.shipment_num || g_delim || l_textline,1,240) --Bug5096900
, 'PO_CAN_POLL_PLAN_WITH_OPEN_REL'
FROM po_lines_gt pol, po_line_locations_gt poll
WHERE poll.po_line_id = pol.po_line_id
AND NVL(poll.cancel_flag, 'N') = 'N'
AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND EXISTS
(
SELECT 'Uncancelled Open Releases Exist'
FROM po_line_locations poll2
WHERE poll2.source_shipment_id = poll.line_location_id
AND poll2.shipment_type = 'SCHEDULED'
AND NVL(poll2.cancel_flag, 'N') = 'N'
AND NVL(poll2.closed_code, 'OPEN') <> 'FINALLY CLOSED'
);
SELECT
POL.line_num
, POL.quantity
, POL.amount --
, sum( PLL.quantity - nvl(PLL.quantity_cancelled,0) ) --
, sum( PLL.amount - nvl(PLL.amount_cancelled,0) ) --
BULK COLLECT INTO
l_line_num
, l_line_qty_tbl --
, l_line_amt_tbl --
, l_lineloc_qty_tbl --
, l_lineloc_amt_tbl --
FROM
PO_LINE_LOCATIONS_GT PLL
, PO_LINES_GT POL
WHERE
POL.po_line_id = PLL.po_line_id
AND PLL.shipment_type in ('STANDARD', 'PLANNED')
AND POL.po_header_id = p_document_id
AND nvl(POL.cancel_flag,'N') = 'N'
AND nvl(POL.closed_code,'OPEN') <> 'FINALLY CLOSED'
AND (
( ( POL.quantity IS NOT NULL ) --
AND ( round(POL.quantity, 10) <>
(SELECT round(sum(PLL2.quantity) -
sum(nvl(PLL2.quantity_cancelled, 0)), 10)
FROM PO_LINE_LOCATIONS_GT PLL2
WHERE PLL2.po_line_id = POL.po_line_id AND
PLL2.shipment_type in ('STANDARD', 'PLANNED') ) )
)
OR --
( ( POL.amount IS NOT NULL )
AND ( round(POL.amount, 10) <>
( SELECT round ( sum ( PLL3.amount
- nvl(PLL3.amount_cancelled, 0) )
, 10
)
FROM po_line_locations_gt PLL3
WHERE PLL3.po_line_id = POL.po_line_id
AND PLL3.shipment_type IN ('STANDARD','PLANNED')
)
)
)
)
GROUP BY
POL.line_num
, POL.quantity
, POL.amount; --
INSERT INTO po_online_report_text_gt(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_name)
VALUES(p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
l_line_num(i),
NULL, --
NULL, --
x_sequence+ l_rowCount(i),
decode ( l_line_qty_tbl(i) --
, NULL , PO_CORE_S.get_translated_text
( 'PO_SUB_PO_LINE_NE_SHIP_AMT'
, 'LINE_NUM', l_line_num(i)
, 'LINE_AMT', l_line_amt_tbl(i)
, 'SHIP_AMT', l_lineloc_amt_tbl(i)
)
, PO_CORE_S.get_translated_text
( 'PO_SUB_PO_LINE_NE_SHIP_QTY'
, 'LINE_NUM', l_line_num(i)
, 'LINE_QTY', l_line_qty_tbl(i)
, 'SHIP_QTY', l_lineloc_qty_tbl(i)
)
),
decode ( l_line_qty_tbl(i) --
, NULL , 'PO_SUB_PO_LINE_NE_SHIP_AMT'
, 'PO_SUB_PO_LINE_NE_SHIP_QTY'
)
);
SELECT poh.currency_code
INTO l_currency_code
FROM po_headers_all poh
WHERE poh.po_header_id = p_document_id;
SELECT
subtotal.line_num
, subtotal.line_amount
, subtotal.line_loc_amount
, subtotal.financing_advance_amount
BULK COLLECT INTO
l_line_num
, l_line_amt_tbl
, l_lineloc_amt_tbl
, l_fin_adv_amount
FROM
( SELECT
POL.line_num
, CASE
WHEN ( POL.order_type_lookup_code = 'FIXED PRICE'
OR POL.order_type_lookup_code = 'RATE')
THEN
POL.amount
ELSE
CASE
WHEN l_min_acct_unit IS NOT NULL THEN
-- Round to minimum accountable unit.
ROUND(
NVL(POL.quantity * POL.unit_price,0) / l_min_acct_unit
) * l_min_acct_unit
ELSE
-- Round to currency precision.
ROUND(NVL(POL.quantity * POL.unit_price,0), l_precision)
END
END line_amount
, SUM (CASE
WHEN ( PLL.value_basis = 'FIXED PRICE'
OR PLL.value_basis = 'RATE')
THEN
PLL.amount - NVL(PLL.amount_cancelled, 0)
ELSE
CASE
WHEN l_min_acct_unit IS NOT NULL THEN
-- Round to minimum accountable unit.
ROUND(
((PLL.quantity-NVL(PLL.quantity_cancelled,0)) * PLL.price_override) / l_min_acct_unit
) * l_min_acct_unit
ELSE
-- Round to currency precision.
ROUND((PLL.quantity-NVL(PLL.quantity_cancelled,0)) * PLL.price_override, l_precision)
END
END) line_loc_amount
, SUM (CASE
WHEN PLL.payment_type = 'ADVANCE' THEN
PLL.amount
END) financing_advance_amount
FROM
PO_LINE_LOCATIONS_GT PLL
, PO_LINES_GT POL
WHERE
POL.po_line_id = PLL.po_line_id
AND ( (l_is_financing_flag = 'N' AND PLL.shipment_type = 'STANDARD')
OR (l_is_financing_flag = 'Y' and PLL.shipment_type = 'PREPAYMENT'))
AND POL.po_header_id = p_document_id
AND nvl(POL.cancel_flag,'N') = 'N'
AND nvl(POL.closed_code,'OPEN') <> 'FINALLY CLOSED'
-- AND nvl(payment_type, 'NULL') <> 'ADVANCE' --Bug 5440038
GROUP BY POL.line_num, POL.order_type_lookup_code, POL.amount, POL.quantity, POL.unit_price
) subtotal
WHERE
( (l_is_financing_flag = 'Y' AND NOT (subtotal.line_amount >= subtotal.line_loc_amount))
OR
(l_is_financing_flag = 'N' AND NOT (subtotal.line_amount = subtotal.line_loc_amount))
);
INSERT INTO po_online_report_text_gt(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_name)
VALUES(p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
l_line_num(i),
NULL,
NULL,
x_sequence+ l_rowCount(i),
decode ( l_fin_adv_amount(i),
NULL,
PO_CORE_S.get_translated_text
( 'PO_SUB_PAY_ITEM_NE_LINE_AMT'
, 'LINE_NUM', l_line_num(i)
, 'PAY_ITEM_AMT', l_lineloc_amt_tbl(i)
, 'LINE_AMT', l_line_amt_tbl(i)
),
PO_CORE_S.get_translated_text
( 'PO_SUB_PRE_PAY_GE_LINE_AMT'
, 'LINE_NUM', l_line_num(i)
, 'PAY_ITEM_AMT', (l_lineloc_amt_tbl(i)-l_fin_adv_amount(i) )
, 'ADV_AMT', l_fin_adv_amount(i)
, 'LINE_AMT', l_line_amt_tbl(i)
)
),
decode ( l_fin_adv_amount(i),
NULL, 'PO_SUB_PAY_ITEM_NE_LINE_AMT'
, 'PO_SUB_PRE_PAY_GE_LINE_AMT'
)
);
SELECT
POL.line_num
, PLL.shipment_num
, PLL.quantity
, PLL.amount --
, sum( nvl(POD.quantity_ordered,0) - nvl(POD.quantity_cancelled,0) )
, sum( nvl(POD.amount_ordered,0) - nvl(POD.amount_cancelled,0) )
BULK COLLECT INTO
l_line_num
, l_shipment_num
, l_lineloc_qty_tbl
, l_lineloc_amt_tbl
, l_dist_qty_tbl
, l_dist_amt_tbl
FROM PO_DISTRIBUTIONS_GT POD,PO_LINE_LOCATIONS_GT PLL, PO_LINES_GT POL
WHERE PLL.po_line_id = POL.po_line_id
AND POD.line_location_id = PLL.line_location_id
AND PLL.po_header_id = p_document_id
AND nvl(PLL.cancel_flag,'N') = 'N'
AND nvl(PLL.closed_code,'OPEN') <> 'FINALLY CLOSED'
AND PLL.shipment_type in ('STANDARD', 'PLANNED', 'PREPAYMENT') --
GROUP BY
POL.line_num
, PLL.shipment_num
, PLL.quantity
, PLL.amount --
, PLL.amount_cancelled
, PLL.quantity_cancelled
, PLL.shipment_type --
HAVING
decode ( PLL.quantity --
, NULL , abs ( ( PLL.amount - nvl(PLL.amount_cancelled,0) )
- sum( POD.amount_ordered - nvl(POD.amount_cancelled,0) ) )
, abs ( ( PLL.quantity - nvl(PLL.quantity_cancelled,0) )
- sum( POD.quantity_ordered - nvl(POD.quantity_cancelled,0) ) )
) > .00001;
INSERT INTO po_online_report_text_gt (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_name)
VALUES(
p_online_report_id,
p_login_id,
p_user_id,
sysdate,
p_user_id,
sysdate,
l_line_num(i),
l_shipment_num(i),
NULL, --
x_sequence+l_rowCount(i),
decode ( l_lineloc_qty_tbl(i)
, NULL , PO_CORE_S.get_translated_text
( 'PO_SUB_PO_SHIP_NE_DIST_AMT'
, 'LINE_NUM', l_line_num(i)
, 'SHIP_NUM', l_shipment_num(i)
, 'SHIP_AMT', l_lineloc_amt_tbl(i)
, 'DIST_AMT', l_dist_amt_tbl(i)
)
, PO_CORE_S.get_translated_text
( 'PO_SUB_PO_SHIP_NE_DIST_QTY'
, 'LINE_NUM', l_line_num(i)
, 'SHIP_NUM', l_shipment_num(i)
, 'SHIP_QTY', l_lineloc_qty_tbl(i)
, 'DIST_QTY', l_dist_qty_tbl(i)
)
),
decode ( l_lineloc_qty_tbl(i)
, NULL , 'PO_SUB_PO_SHIP_NE_DIST_AMT'
, 'PO_SUB_PO_SHIP_NE_DIST_QTY'
)
);
FUNCTION is_pay_item_price_updateable (
p_line_location_id IN NUMBER
, p_add_reasons_to_msg_list IN VARCHAR2)
RETURN BOOLEAN
IS
d_module VARCHAR(70) :=
'po.plsql.PO_DOCUMENT_CHECKS_PVT.is_pay_item_price_updateable';
l_is_price_updateable BOOLEAN;
l_is_price_updateable := TRUE;
SELECT NVL(pll.quantity_received, 0)
, NVL(pll.quantity_billed, 0)
, NVL(pll.quantity_financed, 0)
INTO l_quantity_received
, l_quantity_billed
, l_quantity_financed
FROM po_line_locations_all pll
WHERE line_location_id = p_line_location_id;
l_is_price_updateable := FALSE;
PO_LOG.proc_return(d_module, l_is_price_updateable);
RETURN(l_is_price_updateable);
END is_pay_item_price_updateable;
INSERT INTO PO_ONLINE_REPORT_TEXT_GT
( 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_name
)
SELECT
p_online_report_id
, p_login_id
, p_user_id
, SYSDATE
, p_user_id
, SYSDATE
, null -- lines
, null -- shipments
, null -- distribution_num
, p_sequence + ROWNUM
, substr(l_textline, 1, 240)
, l_message_name
FROM po_headers_gt poh
WHERE poh.po_header_id = p_doc_level_id
AND chk_unv_invoices(l_invoice_type, poh.po_header_id, NULL, NULL,NULL, NULL, p_origin_doc_id, l_calling_sequence) = 1;
INSERT INTO PO_ONLINE_REPORT_TEXT_GT
( 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_name
)
SELECT
p_online_report_id
, p_login_id
, p_user_id
, SYSDATE
, p_user_id
, SYSDATE
, line_num -- lines
, null -- shipments
, null -- distribution_num
, p_sequence + ROWNUM
, substr(l_textline, 1, 240)
, l_message_name
FROM po_lines_gt pol
WHERE pol.po_line_id=p_doc_level_id
AND chk_unv_invoices(l_invoice_type, pol.po_header_id, NULL, pol.po_line_id,NULL, NULL, p_origin_doc_id, 'CHECK_PO_LINE_FINAL_CLOSE') = 1;
INSERT INTO PO_ONLINE_REPORT_TEXT_GT
( 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_name
)
SELECT
p_online_report_id
, p_login_id
, p_user_id
, SYSDATE
, p_user_id
, SYSDATE
, null -- lines
, null -- shipments
, null -- distribution_num
, p_sequence + ROWNUM
, substr(l_textline, 1, 240)
, l_message_name
FROM po_releases_gt por
WHERE por.po_release_id=p_doc_level_id
AND chk_unv_invoices(l_invoice_type, por.po_header_id, por.po_release_id, NULL,NULL, NULL, p_origin_doc_id, l_calling_sequence) = 1;
INSERT INTO PO_ONLINE_REPORT_TEXT_GT
( 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_name
)
SELECT
p_online_report_id
, p_login_id
, p_user_id
, SYSDATE
, p_user_id
, SYSDATE
, null -- lines
, shipment_num -- shipments
, null -- distribution_num
, p_sequence + ROWNUM
, substr(l_textline, 1, 240)
, l_message_name
FROM po_line_locations_gt poll
WHERE poll.line_location_id=p_doc_level_id
AND chk_unv_invoices(l_invoice_type, poll.po_header_id, poll.po_release_id, NULL,poll.line_location_id, NULL, p_origin_doc_id, l_calling_sequence) = 1;
INSERT INTO PO_ONLINE_REPORT_TEXT_GT
( 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_name
)
SELECT
p_online_report_id
, p_login_id
, p_user_id
, SYSDATE
, p_user_id
, SYSDATE
, null -- lines
, null -- shipments
, distribution_num
, p_sequence + ROWNUM
, substr(l_textline, 1, 240)
, l_message_name
FROM po_distributions_gt pod
WHERE pod.po_distribution_id=p_doc_level_id
AND chk_unv_invoices(l_invoice_type, pod.po_header_id, pod.po_release_id, NULL,NULL, pod.po_distribution_id, p_origin_doc_id, l_calling_sequence) = 1;
SELECT po_header_id
INTO l_document_id
FROM po_lines_gt
WHERE po_line_id=p_doc_level_id;
SELECT po_header_id
INTO l_document_id
FROM po_line_locations_gt
WHERE line_location_id=p_doc_level_id;
SELECT po_header_id
INTO l_document_id
FROM po_distributions_gt
WHERE po_distribution_id=p_doc_level_id;
SELECT poh.vendor_id,
poh.vendor_site_id,
pol.item_id,
poll.ship_to_organization_id,
poll.consigned_flag,
poll.outsourced_assembly
INTO l_vendor_id,
l_vendor_site_id,
l_inventory_item_id,
l_ship_to_organization_id,
l_consigned_flag,
l_outsourced_assembly
FROM po_line_locations_all poll,
po_lines_all pol,
po_headers_all poh
WHERE poh.po_header_id = pol.po_header_id
AND pol.po_line_id = poll.po_line_id
AND poll.line_location_id = p_line_location_id;
UPDATE po_line_locations_gt
SET lcm_flag = 'Y'
WHERE line_location_id = p_line_location_id
and lcm_flag is null;
UPDATE po_distributions_gt
SET lcm_flag = 'Y'
WHERE line_location_id = p_line_location_id
and lcm_flag is null;
UPDATE po_line_locations_all
SET lcm_flag = 'Y'
WHERE line_location_id = p_line_location_id;
UPDATE po_distributions_all
SET lcm_flag = 'Y'
WHERE line_location_id = p_line_location_id;
UPDATE po_line_locations_all
SET lcm_flag = null
WHERE line_location_id = p_line_location_id
AND lcm_flag = 'Y';
UPDATE po_distributions_all
SET lcm_flag = null
WHERE line_location_id = p_line_location_id
AND lcm_flag = 'Y';