APPS.RCV_AP_PURGE_PVT SQL Statements

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

Line: 14

PROCEDURE delete_costing_data
( x_return_status   OUT NOCOPY  VARCHAR2,
  p_rt_ids_tbl      IN          PO_TBL_NUMBER
);

Line: 19

PROCEDURE delete_rcv_attachments
( x_return_status   OUT NOCOPY  VARCHAR2,
  p_rsh_ids_tbl     IN          PO_TBL_NUMBER,
  p_rti_ids_tbl     IN          PO_TBL_NUMBER
);

Line: 93

            asn_debug.put_line('Before insert into phr. low = ' || l_range_low || ' high = ' || l_range_high);

Line: 104

        INSERT INTO po_history_receipts
        ( receipt_num,
          shipment_num,
          transaction_date,
          vendor_id,
          receiver_name,
          item_description,
          purge_name
        )
        SELECT  NVL(RSH.receipt_num, -1),
                RSH.shipment_num,
                RSH.shipped_date,
                RSH.vendor_id,
                PAPF.full_name,
                RSL.item_description,
                p_purge_name
        FROM    per_all_people_f PAPF,
                rcv_shipment_lines RSL,
                rcv_shipment_headers RSH,
                po_purge_po_list PPL
        WHERE   PPL.double_check_flag = 'Y'
        AND     PPL.po_header_id = RSL.po_header_id
        AND     PPL.po_header_id BETWEEN l_range_low AND l_range_high
        AND     RSL.shipment_header_id = RSH.shipment_header_id
        AND     RSL.employee_id = PAPF.person_id (+)
        AND     TRUNC(SYSDATE) BETWEEN NVL(PAPF.effective_start_date,
                                           TRUNC(SYSDATE))
                               AND     NVL(PAPF.effective_end_date,
                                           TRUNC(SYSDATE));

Line: 184

PROCEDURE delete_receipts
( x_return_status       OUT NOCOPY  VARCHAR2,
  p_range_size          IN          NUMBER,
  p_po_lower_limit      IN          NUMBER,
  p_po_upper_limit      IN          NUMBER
) IS


TYPE num_tbltyp IS TABLE OF NUMBER;

Line: 196

l_api_name      VARCHAR2(50) := 'delete_receipts';

Line: 223

    SELECT  PPL2.po_header_id
    BULK COLLECT INTO l_ids_tbl
    FROM    (SELECT PPL.po_header_id po_header_id,
                    MOD(ROWNUM, p_range_size) mod_result
             FROM   po_purge_po_list PPL
             WHERE  PPL.double_check_flag = 'Y'
             ORDER BY PPL.po_header_id) PPL2
    WHERE   PPL2.mod_result = 0;

Line: 252

        DELETE
        FROM    rcv_shipment_lines RSL
        WHERE   EXISTS (
                    SELECT  NULL
                    FROM    po_purge_po_list PPL
                    WHERE   PPL.po_header_id = RSL.po_header_id
                    AND     PPL.double_check_flag = 'Y'
                    AND     PPL.po_header_id BETWEEN l_range_low
                                             AND     l_range_high);

Line: 270

        DELETE
        FROM    rcv_shipment_headers RSH
        WHERE   NOT EXISTS (
                        SELECT  NULL
                        FROM    rcv_shipment_lines RSL
                        WHERE   RSL.shipment_header_id =
                                RSH.shipment_header_id)
        AND     NOT EXISTS (
                        SELECT  NULL
                        FROM    rcv_transactions_interface RTI
                        WHERE   RTI.shipment_header_id = RSH.shipment_header_id
                        AND     RTI.processing_status_code <> 'COMPLETED')
        RETURNING RSH.shipment_header_id
        BULK COLLECT INTO   l_rsh_ids_tbl;

Line: 286

            asn_debug.put_line('Deleted ' || l_rsh_ids_tbl.COUNT || ' rsh records');

Line: 291

        SELECT  RT.transaction_id, RT.interface_transaction_id
        BULK COLLECT INTO   l_rt_ids_tbl, l_rti_ids_tbl
        FROM    rcv_transactions RT
        WHERE   EXISTS (
                    SELECT  NULL
                    FROM    po_purge_po_list PPL
                    WHERE   PPL.po_header_id = RT.po_header_id
                    AND     PPL.double_check_flag = 'Y'
                    AND     PPL.po_header_id BETWEEN l_range_low
                                             AND     l_range_high);

Line: 306

        delete_costing_data
        ( x_return_status   => l_return_status,
          p_rt_ids_tbl      => l_rt_ids_tbl
        );

Line: 313

        DELETE
        FROM    rcv_transactions RT
        WHERE   transaction_id = l_rt_ids_tbl(i);

Line: 318

            asn_debug.put_line('Deleted ' || l_rt_ids_tbl.COUNT || ' rt records');

Line: 324

        DELETE
        FROM    rcv_lots_supply RLS
        WHERE   (RLS.shipment_line_id > 0
                 AND
                 NOT EXISTS (
                        SELECT  NULL
                        FROM    rcv_shipment_lines RSL
                        WHERE   RSL.shipment_line_id = RLS.shipment_line_id))
        OR      (RLS.transaction_id > 0
                 AND
                 NOT EXISTS (
                        SELECT  NULL
                        FROM    rcv_transactions RT
                        WHERE   RT.transaction_id = RLS.transaction_id));

Line: 342

        DELETE
        FROM    rcv_serials_supply RSS
        WHERE   (RSS.shipment_line_id > 0
                 AND
                 NOT EXISTS (
                        SELECT  NULL
                        FROM    rcv_shipment_lines RSL
                        WHERE   RSL.shipment_line_id = RSS.shipment_line_id))
        OR      (RSS.transaction_id > 0
                 AND
                 NOT EXISTS (
                        SELECT  NULL
                        FROM    rcv_transactions RT
                        WHERE   RT.transaction_id = RSS.transaction_id));

Line: 360

        DELETE
        FROM    rcv_lot_transactions RLT
        WHERE   (RLT.shipment_line_id > 0
                 AND
                 NOT EXISTS (
                        SELECT  NULL
                        FROM    rcv_shipment_lines RSL
                        WHERE   RSL.shipment_line_id = RLT.shipment_line_id))
        OR
                (RLT.transaction_id > 0
                 AND
                 NOT EXISTS (
                        SELECT  NULL
                        FROM    rcv_transactions RT
                        WHERE   RT.transaction_id = RLT.transaction_id));

Line: 379

        DELETE
        FROM    rcv_serial_transactions RST
        WHERE   (RST.shipment_line_id > 0
                 AND
                 NOT EXISTS (
                        SELECT  NULL
                        FROM    rcv_shipment_lines RSL
                        WHERE   RSL.shipment_line_id = RST.shipment_line_id))
        OR
                (RST.transaction_id > 0
                 AND
                 NOT EXISTS (
                        SELECT  NULL
                        FROM    rcv_transactions RT
                        WHERE   RT.transaction_id = RST.transaction_id));

Line: 397

        delete_rcv_attachments
        ( x_return_status   => l_return_status,
          p_rsh_ids_tbl     => l_rsh_ids_tbl,
          p_rti_ids_tbl     => l_rti_ids_tbl
        );

Line: 421

END delete_receipts;

Line: 453

PROCEDURE delete_costing_data
( x_return_status   OUT NOCOPY  VARCHAR2,
  p_rt_ids_tbl      IN          PO_TBL_NUMBER
) IS

l_api_name      VARCHAR2(50) := 'delete_costing_data';

Line: 505

END delete_costing_data;

Line: 533

PROCEDURE delete_rcv_attachments
( x_return_status   OUT NOCOPY  VARCHAR2,
  p_rsh_ids_tbl     IN          PO_TBL_NUMBER,
  p_rti_ids_tbl     IN          PO_TBL_NUMBER
) IS

l_api_name      VARCHAR2(50) := 'delete_rcv_attachments';

Line: 555

        FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments
        ( x_entity_name             => 'RCV_HEADERS',
          x_pk1_value               => p_rsh_ids_tbl(i),
          x_delete_document_flag    => 'Y'
        );

Line: 568

        FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments
        ( x_entity_name             => 'RCV_TRANSACTIONS_INTERFACE',
          x_pk1_value               => p_rti_ids_tbl(i),
          x_delete_document_flag    => 'Y'
        );

Line: 584

END delete_rcv_attachments;