eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.PO_DOCUMENT_CHECKS_GRP SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 66


* 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_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';

Line: 230


         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;

Line: 246


         SELECT requisition_header_id
         INTO  l_doc_id
         FROM PO_REQUISITION_HEADERS
         WHERE requisition_header_id= l_document_id;

Line: 260


         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;

Line: 1295


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';

Line: 1337


  x_price_updateable := PO_CORE_S.G_PARAMETER_YES;

Line: 1343


  SELECT pol.po_header_id
  INTO   l_po_header_id
  FROM   po_lines_all pol
  WHERE  pol.po_line_id = p_po_line_id;

Line: 1357


    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

Line: 1405


        x_price_updateable := PO_CORE_S.G_PARAMETER_NO;

Line: 1417


      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));

Line: 1425


        x_price_updateable := PO_CORE_S.G_PARAMETER_NO;

Line: 1443


      x_price_updateable := PO_CORE_S.G_PARAMETER_NO;

Line: 1457


      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
          );

Line: 1469


        x_price_updateable := PO_CORE_S.G_PARAMETER_NO;

Line: 1488


  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' );

Line: 1499


    x_price_updateable := PO_CORE_S.G_PARAMETER_NO;

Line: 1526


END check_std_po_price_updateable;

Line: 1567


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';

Line: 1607


  x_price_updateable := PO_CORE_S.G_PARAMETER_YES;

Line: 1615


  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

Line: 1664


      x_price_updateable := PO_CORE_S.G_PARAMETER_NO;

Line: 1676


      x_price_updateable := PO_CORE_S.G_PARAMETER_NO;

Line: 1692


    x_price_updateable := PO_CORE_S.G_PARAMETER_NO;

Line: 1708


  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' );

Line: 1718


    x_price_updateable := PO_CORE_S.G_PARAMETER_NO;

Line: 1745


END check_rel_price_updateable;

Line: 1750


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';

Line: 1761


  l_is_price_updateable  BOOLEAN;

Line: 1775


  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);

Line: 1781


  IF (l_is_price_updateable) THEN
    x_price_updateable := PO_CORE_S.g_parameter_yes;

Line: 1784


    x_price_updateable := PO_CORE_S.g_parameter_no;

Line: 1791


    PO_LOG.proc_end(d_module, 'x_price_updateable', x_price_updateable);

Line: 1802


    x_price_updateable := PO_CORE_S.g_parameter_no;

Line: 1839


  SELECT po_online_report_text_s.nextval
  INTO x_new_report_id
  FROM sys.dual;

Line: 1852


  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;

Line: 1913


    PO_LOG.stmt(d_module,d_progress,'After inserted data of report 1 to the combined report, #rows='||SQL%ROWCOUNT);

Line: 1920


  SELECT MAX(sequence)
  INTO l_id1_max_seq
  FROM PO_ONLINE_REPORT_TEXT
  WHERE online_report_id = p_report_id1;

Line: 1933


  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;

Line: 1951


    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);

Line: 2012


    PO_LOG.stmt(d_module,d_progress,'After inserted data of report 2 to the combined report');

Line: 2087


* 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_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';

Line: 2171


    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;

Line: 2234


  SELECT COUNT(*)
  INTO l_report_id1_rownum
  FROM PO_ONLINE_REPORT_TEXT
  WHERE online_report_id = p_online_report_id;

Line: 2247


  SELECT COUNT(*)
  INTO l_report_id2_rownum
  FROM PO_ONLINE_REPORT_TEXT
  WHERE online_report_id = l_report_id2;