APPS.PO_CLM_COLS_SUB_CHECK SQL Statements

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

Line: 33

        SELECT  FROM_HEADER_ID IDV_HEADER_ID, LINE_NUM,
                FROM_LINE_ID IDV_LINE_ID, PO_LINE_ID
        FROM    po_lines_gt
        WHERE   PO_HEADER_ID = p_document_id
        AND     CLM_INFO_FLAG <> 'Y';

Line: 48

            SELECT  TYPE_LOOKUP_CODE
            INTO    l_document_type_code
            FROM    po_headers_gt
            WHERE   PO_HEADER_ID = p_document_id;

Line: 83

                        SELECT  NVL(CLM_MAX_ORDER_AMOUNT, l_total_amt_ordered + 1),
                                NVL(CLM_MIN_ORDER_AMOUNT, l_total_amt_ordered - 1)
                        INTO    l_idv_max_ord_amt,
                                l_idv_min_ord_amt
                        FROM    po_lines_all
                        WHERE   PO_HEADER_ID = c_get_po_lines_rec.IDV_HEADER_ID
                        AND     PO_LINE_ID = c_get_po_lines_rec.IDV_LINE_ID;

Line: 168

        SELECT  LINE_NUM,
                PO_LINE_ID
        FROM    po_lines_gt
        WHERE   PO_HEADER_ID = p_document_id;

Line: 182

            SELECT  TYPE_LOOKUP_CODE
            INTO    l_document_type_code
            FROM    po_headers_gt
            WHERE   PO_HEADER_ID = p_document_id;

Line: 200

                SELECT  CLM_MAX_ORDER_AMOUNT, AMOUNT_LIMIT
                INTO    l_idv_max_ord_amt, l_amt_limit
                FROM    po_headers_gt
                WHERE   PO_HEADER_ID = p_document_id;

Line: 296

        SELECT  FROM_LINE_ID, LINE_NUM
        FROM    po_lines_gt
        WHERE   PO_HEADER_ID = p_document_id
        AND     CLM_INFO_FLAG <> 'Y';

Line: 321

            SELECT  TYPE_LOOKUP_CODE
            INTO    l_document_type_code
            FROM    po_headers_gt
            WHERE   PO_HEADER_ID = p_document_id;

Line: 344

                        SELECT  CLM_ORDER_START_DATE,
                                CLM_ORDER_END_DATE
                        INTO    l_ord_st_dt,
                                l_ord_end_dt
                        FROM    PO_LINES_ALL
                        WHERE   PO_LINE_ID = c_get_po_lines_rec.FROM_LINE_ID;

Line: 351

                        SELECT  CLM_EFFECTIVE_DATE
                        INTO    l_effective_date
                        FROM    po_headers_gt
                        WHERE   PO_HEADER_ID = p_document_id;

Line: 449

        SELECT  CLM_ORDER_START_DATE, CLM_ORDER_END_DATE, LINE_NUM
        FROM    po_lines_gt
        WHERE   PO_HEADER_ID = p_document_id;

Line: 462

            SELECT  TYPE_LOOKUP_CODE
            INTO    l_document_type_code
            FROM    po_headers_gt
            WHERE   PO_HEADER_ID = p_document_id;

Line: 481

                SELECT  START_DATE, END_DATE
                INTO    l_head_st_dt, l_head_end_dt
                FROM    po_headers_gt
                WHERE   PO_HEADER_ID = p_document_id;

Line: 571

              SELECT  TYPE_LOOKUP_CODE
              INTO    l_document_type_code
              FROM    po_headers_gt
              WHERE   PO_HEADER_ID = p_document_id;

Line: 593

          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,
              null,
              0,
          	  0,
              p_sequence,
              FND_MESSAGE.GET_STRING('PO',l_effect_dt_null_msg),
              'Effective date Invalid',
              'W'
            FROM    PO_HEADERS_GT POH
            WHERE   POH.PO_HEADER_ID = p_document_id
            AND POH.CLM_EFFECTIVE_DATE IS NULL ;

Line: 635

             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,
                FND_MESSAGE.GET_STRING('PO','PO_EFFECT_DT_INVALID') || ' for Line '||POL.LINE_NUM_DISPLAY,
                'Effective date Invalid',
                'E'
              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.draft_id = p_draft_id
              AND      POH.PO_HEADER_ID = p_document_id
              AND POH.CLM_EFFECTIVE_DATE  > Nvl(PLL.CLM_PERIOD_PERF_START_DATE,Nvl(PLL.NEED_BY_DATE, POH.CLM_EFFECTIVE_DATE + 1 ))
              ORDER BY POL.LINE_NUM;

Line: 710

        SELECT  CLM_MIN_ORDER_QUANTITY,
                CLM_MAX_ORDER_QUANTITY,
                CLM_MAX_TOTAL_QUANTITY,
                CLM_MIN_TOTAL_QUANTITY,
                QUANTITY QUANTITY,
                MATCHING_BASIS, LINE_NUM
        FROM    po_lines_gt
        WHERE   PO_HEADER_ID = p_document_id
        AND     CLM_INFO_FLAG <> 'Y';

Line: 735

            SELECT  TYPE_LOOKUP_CODE
            INTO    l_document_type_code
            FROM    po_headers_gt
            WHERE   PO_HEADER_ID = p_document_id;

Line: 821

        SELECT  CLM_MIN_ORDER_AMOUNT,
                CLM_MAX_ORDER_AMOUNT,
                CLM_MAX_TOTAL_AMOUNT,
                CLM_MIN_TOTAL_AMOUNT,
                MATCHING_BASIS,
                AMOUNT AMOUNT,
                QUANTITY QUANTITY,
                NVL(UNIT_PRICE, 0) UNIT_PRICE, LINE_NUM
        FROM    po_lines_gt
        WHERE   PO_HEADER_ID = p_document_id
        AND     CLM_INFO_FLAG <> 'Y';

Line: 849

            SELECT  TYPE_LOOKUP_CODE
            INTO    l_document_type_code
            FROM    po_headers_gt
            WHERE   PO_HEADER_ID = p_document_id;

Line: 971

        SELECT  NVL(SUM(QUANTITY), 0) TOTAL_QTY,
                FROM_HEADER_ID IDV_HEADER_ID,
                FROM_LINE_ID IDV_LINE_ID,
                MATCHING_BASIS
        FROM    po_lines_gt
        WHERE   PO_HEADER_ID = p_document_id
        AND     CLM_INFO_FLAG <> 'Y'
        AND     MATCHING_BASIS = 'QUANTITY'
        GROUP BY FROM_HEADER_ID, FROM_LINE_ID, MATCHING_BASIS;

Line: 990

            SELECT  TYPE_LOOKUP_CODE
            INTO    l_document_type_code
            FROM    po_headers_gt
            WHERE   PO_HEADER_ID = p_document_id;

Line: 1011

                        SELECT  NVL(CLM_MIN_ORDER_QUANTITY, c_get_po_lines_rec.TOTAL_QTY - 1), NVL(CLM_MAX_ORDER_QUANTITY, c_get_po_lines_rec.TOTAL_QTY + 1)
                        INTO    l_idv_min_ord_qty, l_idv_max_ord_qty
                        FROM    PO_LINES_ALL
                        WHERE   PO_HEADER_ID = c_get_po_lines_rec.IDV_HEADER_ID
                        AND     PO_LINE_ID = c_get_po_lines_rec.IDV_LINE_ID;

Line: 1038

                        SELECT  MIN(LINE_NUM)
                        INTO    l_line_num
                        FROM    po_lines_gt
                        WHERE   PO_HEADER_ID = p_document_id
                        AND     CLM_INFO_FLAG <> 'Y'
                        AND     MATCHING_BASIS = 'QUANTITY'
                        AND     FROM_HEADER_ID = c_get_po_lines_rec.IDV_HEADER_ID
                        AND     FROM_LINE_ID = c_get_po_lines_rec.IDV_LINE_ID;

Line: 1107

        SELECT  NVL(SUM(QUANTITY), 0) TOTAL_QTY,
                FROM_HEADER_ID IDV_HEADER_ID,
                FROM_LINE_ID IDV_LINE_ID,
                MATCHING_BASIS
        FROM    po_lines_gt
        WHERE   PO_HEADER_ID = p_document_id
        AND     CLM_INFO_FLAG <> 'Y'
        AND     MATCHING_BASIS = 'QUANTITY'
        GROUP BY FROM_HEADER_ID, FROM_LINE_ID, MATCHING_BASIS;

Line: 1126

            SELECT  TYPE_LOOKUP_CODE
            INTO    l_document_type_code
            FROM    po_headers_gt
            WHERE   PO_HEADER_ID = p_document_id;

Line: 1148

                        SELECT  CLM_MAX_TOTAL_QUANTITY, CLM_TOTAL_QUANTITY_ORDERED
                        INTO    l_idv_max_qty, l_tot_qty_ordered
                        FROM    PO_LINES_ALL
                        WHERE   PO_HEADER_ID = c_get_po_lines_rec.IDV_HEADER_ID
                        AND     PO_LINE_ID = c_get_po_lines_rec.IDV_LINE_ID;

Line: 1166

                           SELECT SUM(Nvl(QUANTITY,0))
                             INTO l_line_base_qty
                             FROM PO_LINES_MERGE_V
                            WHERE PO_HEADER_ID = p_document_id
                              AND FROM_HEADER_ID = c_get_po_lines_rec.IDV_HEADER_ID
                              AND FROM_LINE_ID = c_get_po_lines_rec.IDV_LINE_ID
                              AND DRAFT_ID = -1
                            GROUP BY FROM_HEADER_ID, FROM_LINE_ID, MATCHING_BASIS;

Line: 1184

                              SELECT SUM(Nvl(QUANTITY,0))
                                INTO l_line_mod_qty
                                FROM PO_LINES_MERGE_V
                               WHERE PO_HEADER_ID = p_document_id
                                 AND FROM_HEADER_ID = c_get_po_lines_rec.IDV_HEADER_ID
                                 AND FROM_LINE_ID = c_get_po_lines_rec.IDV_LINE_ID
                                 AND DRAFT_ID = p_draft_id
                               GROUP BY FROM_HEADER_ID, FROM_LINE_ID, MATCHING_BASIS;

Line: 1215

                        SELECT  MIN(LINE_NUM)
                        INTO    l_line_num
                        FROM    po_lines_gt
                        WHERE   PO_HEADER_ID = p_document_id
                        AND     CLM_INFO_FLAG <> 'Y'
                        AND     MATCHING_BASIS = 'QUANTITY'
                        AND     FROM_HEADER_ID = c_get_po_lines_rec.IDV_HEADER_ID
                        AND     FROM_LINE_ID = c_get_po_lines_rec.IDV_LINE_ID;

Line: 1282

        SELECT  FROM_HEADER_ID IDV_HEADER_ID, PO_LINE_ID ,
                FROM_LINE_ID IDV_LINE_ID, LINE_NUM
        FROM    po_lines_gt
        WHERE   PO_HEADER_ID = p_document_id
        AND     CLM_INFO_FLAG <> 'Y';

Line: 1301

            SELECT  TYPE_LOOKUP_CODE
            INTO    l_document_type_code
            FROM    po_headers_gt
            WHERE   PO_HEADER_ID = p_document_id;

Line: 1323

                        SELECT  CLM_MAX_TOTAL_AMOUNT, NVL(CLM_TOTAL_AMOUNT_ORDERED, 0)
                        INTO    l_idv_max_amt, l_tot_amt_ordered
                        FROM    PO_LINES_ALL
                        WHERE   PO_HEADER_ID = c_get_po_lines_rec.IDV_HEADER_ID
                        AND     PO_LINE_ID = c_get_po_lines_rec.IDV_LINE_ID;

Line: 1438

            SELECT  TYPE_LOOKUP_CODE, CLM_SOURCE_DOCUMENT_ID
            INTO    l_document_type_code, l_idv_header_id
            FROM    po_headers_gt
            WHERE   PO_HEADER_ID = p_document_id;

Line: 1456

            SELECT  NVL(clm_amount_released, 0), amount_limit
            INTO    l_clm_amt_released, l_amount_limit
            FROM    PO_HEADERS_ALL
            WHERE   PO_HEADER_ID = l_idv_header_id;

Line: 1552

            SELECT  TYPE_LOOKUP_CODE, CLM_SOURCE_DOCUMENT_ID
            INTO    l_document_type_code, l_idv_header_id
            FROM    po_headers_gt
            WHERE   PO_HEADER_ID = p_document_id;

Line: 1576

            SELECT  clm_max_order_amount, clm_min_order_amount
            INTO    l_max_ord_amt, l_min_ord_amt
            FROM    PO_HEADERS_ALL
            WHERE   PO_HEADER_ID = l_idv_header_id;

Line: 1687

            SELECT  TYPE_LOOKUP_CODE, CLM_SOURCE_DOCUMENT_ID
            INTO    l_document_type_code, l_source_id
            FROM    po_headers_gt
            WHERE   PO_HEADER_ID = p_document_id;

Line: 1709

                            SELECT  TYPE_LOOKUP_CODE
                            INTO    l_doc_type
                            FROM    PO_HEADERS_ALL
                            WHERE   PO_HEADER_ID = l_source_id;

Line: 1717

                                SELECT  1
                                INTO    l_count
                                FROM    po_headers_gt h, po_lines_gt l
                                WHERE   h.PO_HEADER_ID = p_document_id
                                AND     h.CLM_AWARD_TYPE IS NOT NULL
                                AND     h.PO_HEADER_ID = l.PO_HEADER_ID
                                AND     (l.FROM_HEADER_ID IS NOT NULL OR h.CLM_SOURCE_DOCUMENT_ID IS NOT NULL)
                                AND     Nvl(l.FROM_HEADER_ID, -999) <> Nvl(h.CLM_SOURCE_DOCUMENT_ID, -999)
                                AND     ROWNUM = 1;

Line: 1730

                                SELECT  1
                                INTO    l_count
                                FROM    po_headers_gt h, po_lines_gt l
                                WHERE   h.PO_HEADER_ID = p_document_id
                                AND     h.CLM_AWARD_TYPE IS NOT NULL
                                AND     h.PO_HEADER_ID = l.PO_HEADER_ID
                                AND     (l.CONTRACT_ID IS NOT NULL OR h.CLM_SOURCE_DOCUMENT_ID IS NOT NULL)
                                AND     Nvl(l.CONTRACT_ID, -999) <> Nvl(h.CLM_SOURCE_DOCUMENT_ID, -999)
                                AND     ROWNUM = 1;

Line: 1809

        SELECT  LINE_NUM
        FROM    po_lines_gt l
        WHERE   l.PO_HEADER_ID = p_document_id
        AND     l.FROM_LINE_ID IS NULL
        and     nvl(l.CLM_INFO_FLAG,'N') = 'N' ; --Bug 12669019

Line: 1828

            SELECT  TYPE_LOOKUP_CODE, CLM_SOURCE_DOCUMENT_ID
            INTO    l_document_type_code, l_source_id
            FROM    po_headers_gt
            WHERE   PO_HEADER_ID = p_document_id;

Line: 1850

                            SELECT  TYPE_LOOKUP_CODE
                            INTO    l_doc_type
                            FROM    PO_HEADERS_ALL
                            WHERE   PO_HEADER_ID = l_source_id;

Line: 2288

        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,
             p_line_num,
             0,
             0,
             p_sequence,
             l_ret_clm_err_msg_txt,
             p_return_message
        );

Line: 2367

            SELECT pol.po_header_id
            INTO   l_header_id
            FROM   po_lines_all pol
            WHERE  pol.po_line_id = p_doc_level_id;

Line: 2379

            SELECT  poh.currency_code, poh.org_id
            INTO    l_po_currency, l_org_id
            FROM    po_headers_gt poh
            WHERE   poh.po_header_id = l_header_id;

Line: 2406

                    SELECT  SUM
                            (
                                DECODE
                                (
                                     POL.matching_basis
                                    ,'AMOUNT', pol.amount
                                    ,nvl2
                                    (
                                         l_mau
                                        ,round(pol.quantity * pol.unit_price / l_mau) * l_mau
                                        ,round((pol.quantity * pol.unit_price), l_precision)
                                    )
                                )
                            )
                    INTO    l_return_val
                    FROM    po_lines_gt pol
                    WHERE   pol.po_header_id = p_doc_level_id
                    AND     (
                              (pol.from_header_id = p_idv_header_id AND pol.FROM_LINE_ID = NVL(p_idv_line_id, pol.FROM_LINE_ID))
                              OR
                              (pol.contract_id = p_idv_header_id)
                             );

Line: 2429

                    SELECT  DECODE
                            (
                               POL.matching_basis
                              ,'AMOUNT', pol.amount
                              ,nvl2
                              (
                                 l_mau
                                ,round(pol.quantity * pol.unit_price / l_mau) * l_mau
                                ,round((pol.quantity * pol.unit_price), l_precision)
                              )
                            )
                    INTO    l_return_val
                    FROM    po_lines_gt pol
                    WHERE   pol.po_line_id = p_doc_level_id
                    AND     (
                              (pol.from_header_id = p_idv_header_id AND pol.FROM_LINE_ID = p_idv_line_id)
                              OR
                              (pol.contract_id = p_idv_header_id)
                             );

Line: 2517

        SELECT  poh.currency_code, poh.org_id
            INTO    l_po_currency, l_org_id
            FROM    po_headers_gt poh
            WHERE   poh.po_header_id = p_header_id;

Line: 2540

                    SELECT Sum(
                            DECODE
                            (
                               POL.matching_basis
                              ,'AMOUNT', pol.amount
                              ,nvl2
                              (
                                 l_mau
                                ,round(pol.quantity * pol.unit_price / l_mau) * l_mau
                                ,round((pol.quantity * pol.unit_price), l_precision)
                              )
                             )
                            )
                    INTO    l_return_val
                    FROM    po_lines_merge_v pol
                    WHERE   pol.po_header_id = p_header_id
                    AND     pol.draft_id   = p_draft_id
                    AND     (pol.from_header_id = p_idv_header_id OR
                              pol.contract_id = p_idv_header_id
                             );

Line: 2563

                    SELECT  Sum(
		                        DECODE
                            (
                               POL.matching_basis
                              ,'AMOUNT', pol.amount
                              ,nvl2
                              (
                                 l_mau
                                ,round(pol.quantity * pol.unit_price / l_mau) * l_mau
                                ,round((pol.quantity * pol.unit_price), l_precision)
                              )
                            )
			                      )
                    INTO    l_return_val
                    FROM    po_lines_merge_v pol
                    WHERE   pol.po_header_id = p_header_id
		                  AND     pol.draft_id   = p_draft_id
                    AND     pol.from_header_id = p_idv_header_id
	                   AND     pol.FROM_LINE_ID = p_idv_line_id;