[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* 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 (returns at least one error)
* 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.
*/
-- SUB_CHK_1
PROCEDURE po_submission_check(
p_api_version IN NUMBER
, p_action_requested IN VARCHAR2
, p_document_type IN VARCHAR2
, p_document_subtype IN VARCHAR2
, p_document_level IN VARCHAR2
, p_document_level_id IN NUMBER
, p_org_id IN NUMBER
, p_requested_changes IN PO_CHANGES_REC_TYPE
, p_check_asl IN BOOLEAN
, p_draft_id IN NUMBER := -1 -- CLM Aprvl
, p_req_chg_initiator IN VARCHAR2 := NULL -- bug4957243
, 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';
SELECT po_header_id,
NVL(consigned_consumption_flag, 'N')
INTO l_doc_id,
l_consigned_flag
FROM PO_HEADERS
WHERE po_header_id= l_document_id;
SELECT requisition_header_id
INTO l_doc_id
FROM PO_REQUISITION_HEADERS
WHERE requisition_header_id= l_document_id;
SELECT po_release_id,
NVL(consigned_consumption_flag, 'N')
INTO l_doc_id,
l_consigned_flag
FROM PO_RELEASES
WHERE po_release_id= l_document_id;
PROCEDURE check_std_po_price_updateable (
p_api_version IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
p_po_line_id IN PO_LINES_ALL.po_line_id%TYPE,
p_from_price_break IN VARCHAR2,
p_add_reasons_to_msg_list IN VARCHAR2,
x_price_updateable OUT NOCOPY VARCHAR2,
x_retroactive_price_change OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'CHECK_STD_PO_PRICE_UPDATEABLE';
x_price_updateable := PO_CORE_S.G_PARAMETER_YES;
SELECT pol.po_header_id
INTO l_po_header_id
FROM po_lines_all pol
WHERE pol.po_line_id = p_po_line_id;
SELECT NVL(REFH.global_agreement_flag, 'N'),
NVL(REFL.allow_price_override_flag, 'N')
INTO l_has_ga_ref,
l_allow_price_override
FROM po_lines_all POL,
po_headers_all REFH,
po_lines_all REFL
WHERE POL.po_line_id = p_po_line_id
AND REFH.po_header_id (+) = POL.from_header_id -- JOIN
AND REFL.po_line_id (+) = POL.from_line_id; -- JOIN
x_price_updateable := PO_CORE_S.G_PARAMETER_NO;
SELECT count(*)
INTO l_accrue_invoice_count
FROM po_line_locations_all
WHERE (po_line_id = p_po_line_id)
AND ((NVL(quantity_received,0) > 0 AND accrue_on_receipt_flag = 'Y')
OR (NVL(quantity_billed,0) > 0));
x_price_updateable := PO_CORE_S.G_PARAMETER_NO;
x_price_updateable := PO_CORE_S.G_PARAMETER_NO;
SELECT COUNT(*)
INTO l_accrue_invoice_count
FROM po_line_locations_all pll
WHERE po_line_id = p_po_line_id
AND pll.payment_type = 'DELIVERY'
AND ( ( NVL(quantity_received,0) > 0
AND accrue_on_receipt_flag = 'Y'
)
OR NVL(quantity_billed,0) > 0
);
x_price_updateable := PO_CORE_S.G_PARAMETER_NO;
SELECT count(*)
INTO l_pending_rcv_transactions
FROM dual
WHERE EXISTS
( SELECT 1
FROM rcv_transactions_interface RTI, po_line_locations_all PLL
WHERE PLL.po_line_id = p_po_line_id
AND RTI.po_line_location_id = PLL.line_location_id -- JOIN
AND RTI.transaction_status_code = 'PENDING' );
x_price_updateable := PO_CORE_S.G_PARAMETER_NO;
END check_std_po_price_updateable;
PROCEDURE check_rel_price_updateable (
p_api_version IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
p_line_location_id IN PO_LINE_LOCATIONS_ALL.line_location_id%TYPE,
p_from_price_break IN VARCHAR2,
p_add_reasons_to_msg_list IN VARCHAR2,
x_price_updateable OUT NOCOPY VARCHAR2,
x_retroactive_price_change OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'CHECK_REL_PRICE_UPDATEABLE';
x_price_updateable := PO_CORE_S.G_PARAMETER_YES;
SELECT NVL(PLL.quantity_received,0),
NVL(PLL.accrue_on_receipt_flag,'N'),
NVL(PLL.quantity_billed,0),
NVL(POL.allow_price_override_flag, 'N')
INTO l_qty_received,
l_accrue_flag,
l_qty_billed,
l_allow_price_override
FROM po_line_locations_all PLL,
po_lines_all POL
WHERE PLL.line_location_id = p_line_location_id
AND PLL.po_line_id = POL.po_line_id; -- JOIN
x_price_updateable := PO_CORE_S.G_PARAMETER_NO;
x_price_updateable := PO_CORE_S.G_PARAMETER_NO;
x_price_updateable := PO_CORE_S.G_PARAMETER_NO;
SELECT count(*)
INTO l_pending_rcv_transactions
FROM dual
WHERE EXISTS
( SELECT 1
FROM rcv_transactions_interface RTI
WHERE RTI.po_line_location_id = p_line_location_id
AND RTI.transaction_status_code = 'PENDING' );
x_price_updateable := PO_CORE_S.G_PARAMETER_NO;
END check_rel_price_updateable;
PROCEDURE check_payitem_price_updateable (
p_api_version IN NUMBER
, p_line_location_id IN NUMBER
, p_add_reasons_to_msg_list IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, x_price_updateable OUT NOCOPY VARCHAR2
) IS
d_module VARCHAR2(70) :=
'po.plsql.PO_DOCUMENT_CHECKS_GRP.check_payitem_price_updateable';
l_is_price_updateable BOOLEAN;
l_is_price_updateable := PO_DOCUMENT_CHECKS_PVT.is_pay_item_price_updateable(
p_line_location_id => p_line_location_id
, p_add_reasons_to_msg_list => p_add_reasons_to_msg_list);
IF (l_is_price_updateable) THEN
x_price_updateable := PO_CORE_S.g_parameter_yes;
x_price_updateable := PO_CORE_S.g_parameter_no;
PO_LOG.proc_end(d_module, 'x_price_updateable', x_price_updateable);
x_price_updateable := PO_CORE_S.g_parameter_no;
SELECT po_online_report_text_s.nextval
INTO x_new_report_id
FROM sys.dual;
INSERT INTO PO_ONLINE_REPORT_TEXT
(
online_report_id
,sequence
,last_updated_by
,last_update_date
,created_by
,creation_date
,last_update_login
,text_line
,line_num
,shipment_num
,distribution_num
,transaction_level
,quantity
,transaction_id
,transaction_date
,transaction_type
,transaction_uom
,transaction_location
,request_id
,program_application_id
,program_id
,program_update_date
,message_type
,show_in_psa_flag
,segment1
,distribution_type
)
SELECT
x_new_report_id
,sequence
,last_updated_by
,last_update_date
,created_by
,creation_date
,last_update_login
,text_line
,line_num
,shipment_num
,distribution_num
,transaction_level
,quantity
,transaction_id
,transaction_date
,transaction_type
,transaction_uom
,transaction_location
,request_id
,program_application_id
,program_id
,program_update_date
,message_type
,show_in_psa_flag
,segment1
,distribution_type
FROM PO_ONLINE_REPORT_TEXT
WHERE online_report_id = p_report_id1
ORDER BY sequence ASC;
PO_LOG.stmt(d_module,d_progress,'After inserted data of report 1 to the combined report, #rows='||SQL%ROWCOUNT);
SELECT MAX(sequence)
INTO l_id1_max_seq
FROM PO_ONLINE_REPORT_TEXT
WHERE online_report_id = p_report_id1;
SELECT sequence
BULK COLLECT INTO l_id2_seq_list
FROM PO_ONLINE_REPORT_TEXT
WHERE online_report_id = p_report_id2
ORDER BY sequence ASC;
INSERT INTO PO_ONLINE_REPORT_TEXT
(
online_report_id
,sequence
,last_updated_by
,last_update_date
,created_by
,creation_date
,last_update_login
,text_line
,line_num
,shipment_num
,distribution_num
,transaction_level
,quantity
,transaction_id
,transaction_date
,transaction_type
,transaction_uom
,transaction_location
,request_id
,program_application_id
,program_id
,program_update_date
,message_type
,show_in_psa_flag
,segment1
,distribution_type
)
SELECT
x_new_report_id
,l_id1_max_seq + i -- increment
,last_updated_by
,last_update_date
,created_by
,creation_date
,last_update_login
,text_line
,line_num
,shipment_num
,distribution_num
,transaction_level
,quantity
,transaction_id
,transaction_date
,transaction_type
,transaction_uom
,transaction_location
,request_id
,program_application_id
,program_id
,program_update_date
,message_type
,show_in_psa_flag
,segment1
,distribution_type
FROM PO_ONLINE_REPORT_TEXT
WHERE online_report_id = p_report_id2 and sequence = l_id2_seq_list(i);
PO_LOG.stmt(d_module,d_progress,'After inserted data of report 2 to the combined report');
* 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 (returns at least one error)
* 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 (including
* copydoc check and normal po 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.
*/
--End of Comments
-------------------------------------------------------------------------------
PROCEDURE po_combined_submission_check(
p_api_version IN NUMBER
, p_action_requested IN VARCHAR2
, p_document_type IN VARCHAR2
, p_document_subtype IN VARCHAR2
, p_document_level IN VARCHAR2
, p_document_level_id IN NUMBER
, p_draft_id IN NUMBER -- CLM Approval
, p_org_id IN NUMBER
, p_requested_changes IN PO_CHANGES_REC_TYPE
, p_check_asl IN BOOLEAN
, p_req_chg_initiator IN VARCHAR2 := NULL -- bug4957243
, p_origin_doc_id IN NUMBER := NULL -- Bug#5462677
-- parameters for combination
, p_from_header_id IN NUMBER
, p_from_type_lookup_code IN VARCHAR2
, p_po_header_id IN NUMBER
, p_online_report_id IN NUMBER
, p_sob_id IN NUMBER
, 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
d_module VARCHAR2(70) :=
'po.plsql.PO_DOCUMENT_CHECKS_GRP.po_combined_submission_check';
SELECT FSP.inventory_organization_id
INTO l_inv_org_id
FROM po_system_parameters_all PSP,
financials_system_params_all FSP,
gl_sets_of_books GLS,
fnd_id_flex_structures COAFS
WHERE FSP.org_id= PSP.org_id
AND FSP.set_of_books_id = GLS.set_of_books_id
AND COAFS.id_flex_num = GLS.chart_of_accounts_id
AND COAFS.application_id = 101 /** SQLGL **/
AND COAFS.id_flex_code = 'GL#'
AND fsp.org_id = p_org_id;
SELECT COUNT(*)
INTO l_report_id1_rownum
FROM PO_ONLINE_REPORT_TEXT
WHERE online_report_id = p_online_report_id;
SELECT COUNT(*)
INTO l_report_id2_rownum
FROM PO_ONLINE_REPORT_TEXT
WHERE online_report_id = l_report_id2;