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.RCV_RECEIPT_CONFIRMATION SQL Statements

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

Line: 33


  SELECT distinct rsh.shipment_header_id
  FROM rcv_shipment_headers rsh,
       rcv_shipment_lines rsl,
       rcv_transactions rt
  WHERE rsh.ship_to_org_id = p_org_id
  AND wms_deploy.get_client_code(rsl.item_id) =  p_client_code
  AND rt.transaction_date BETWEEN nvl(l_trx_date_from,rt.transaction_date) AND nvl(l_trx_date_to, rt.transaction_date)
  AND rsh.shipment_header_id BETWEEN nvl(p_shid_from,rsh.shipment_header_id) AND nvl(p_shid_to, rsh.shipment_header_id)
  AND rsh.shipment_header_id = rt.shipment_header_id
  AND rt.shipment_line_id = rsl.shipment_line_id
  AND Nvl(rt.xml_document_id,-99) = nvl(p_xml_doc_id,-99)
  AND Nvl(rt.receipt_confirmation_extracted,'N') not in ('Y','P')
  AND (rt.transaction_type IN ('DELIVER') OR
       (rt.TRANSACTION_TYPE IN ('CORRECT', 'RETURN TO RECEIVING')
                AND EXISTS (SELECT '1' FROM rcv_transactions rt2
                            WHERE rt.parent_transaction_id = rt2.transaction_id
                            AND rt2.transaction_type = 'DELIVER')
       ) OR
       (rt.TRANSACTION_TYPE IN ('CORRECT')
                AND EXISTS (SELECT '1' FROM rcv_transactions rt3
                            WHERE rt.parent_transaction_id = rt3.transaction_id
                            AND rt3.transaction_type = 'RETURN TO RECEIVING')
       )
    ) order by shipment_header_id asc;

Line: 59


  SELECT distinct rsh.shipment_header_id
  FROM rcv_shipment_headers rsh,
       rcv_shipment_lines rsl,
       rcv_transactions rt
  WHERE rsh.ship_to_org_id = p_org_id
  AND wms_deploy.get_client_code(rsl.item_id) =  p_client_code
  AND rt.transaction_date BETWEEN nvl(l_trx_date_from,rt.transaction_date) AND nvl(l_trx_date_to, rt.transaction_date)
  AND rsh.shipment_header_id BETWEEN nvl(p_shid_from,rsh.shipment_header_id) AND nvl(p_shid_to, rsh.shipment_header_id)
  AND rt.xml_document_id = p_xml_doc_id
  AND rsh.shipment_header_id = rt.shipment_header_id
  AND rt.shipment_line_id = rsl.shipment_line_id
  AND Nvl(rt.receipt_confirmation_extracted,'N') in ('Y')
  AND (rt.transaction_type IN ('DELIVER') OR
       (rt.TRANSACTION_TYPE IN ('CORRECT', 'RETURN TO RECEIVING')
                AND EXISTS (SELECT '1' FROM rcv_transactions rt2
                            WHERE rt.parent_transaction_id = rt2.transaction_id
                            AND rt2.transaction_type = 'DELIVER')
       ) OR
       (rt.TRANSACTION_TYPE IN ('CORRECT')
                AND EXISTS (SELECT '1' FROM rcv_transactions rt3
                            WHERE rt.parent_transaction_id = rt3.transaction_id
                            AND rt3.transaction_type = 'RETURN TO RECEIVING')
       )
    ) order by shipment_header_id asc;

Line: 85


  SELECT distinct rsh.shipment_header_id
  FROM rcv_shipment_headers rsh,
       rcv_transactions rt
  WHERE rsh.ship_to_org_id = p_org_id
  AND rt.transaction_date BETWEEN nvl(l_trx_date_from,rt.transaction_date) AND nvl(l_trx_date_to, rt.transaction_date)
  AND rsh.shipment_header_id BETWEEN nvl(p_shid_from,rsh.shipment_header_id) AND nvl(p_shid_to, rsh.shipment_header_id)
  AND rsh.shipment_header_id = rt.shipment_header_id
  AND Nvl(rt.xml_document_id,-99) = nvl(p_xml_doc_id,-99)
  AND Nvl(rt.receipt_confirmation_extracted,'N') not in ('Y','P')
  AND (rt.transaction_type IN ('DELIVER') OR
       (rt.TRANSACTION_TYPE IN ('CORRECT', 'RETURN TO RECEIVING')
                AND EXISTS (SELECT '1' FROM rcv_transactions rt2
                            WHERE rt.parent_transaction_id = rt2.transaction_id
                            AND rt2.transaction_type = 'DELIVER')
       ) OR
       (rt.TRANSACTION_TYPE IN ('CORRECT')
                AND EXISTS (SELECT '1' FROM rcv_transactions rt3
                            WHERE rt.parent_transaction_id = rt3.transaction_id
                            AND rt3.transaction_type = 'RETURN TO RECEIVING')
       )
    ) order by shipment_header_id asc;

Line: 108


  SELECT distinct rsh.shipment_header_id
  FROM rcv_shipment_headers rsh,
       rcv_transactions rt
  WHERE rsh.ship_to_org_id = p_org_id
  AND rt.transaction_date BETWEEN nvl(l_trx_date_from,rt.transaction_date) AND nvl(l_trx_date_to, rt.transaction_date)
  AND rsh.shipment_header_id BETWEEN nvl(p_shid_from,rsh.shipment_header_id) AND nvl(p_shid_to, rsh.shipment_header_id)
  AND rsh.shipment_header_id = rt.shipment_header_id
  AND rt.xml_document_id = p_xml_doc_id
  AND Nvl(rt.receipt_confirmation_extracted,'N') in ('Y')
  AND (rt.transaction_type IN ('DELIVER') OR
       (rt.TRANSACTION_TYPE IN ('CORRECT', 'RETURN TO RECEIVING')
                AND EXISTS (SELECT '1' FROM rcv_transactions rt2
                            WHERE rt.parent_transaction_id = rt2.transaction_id
                            AND rt2.transaction_type = 'DELIVER')
       ) OR
       (rt.TRANSACTION_TYPE IN ('CORRECT')
                AND EXISTS (SELECT '1' FROM rcv_transactions rt3
                            WHERE rt.parent_transaction_id = rt3.transaction_id
                            AND rt3.transaction_type = 'RETURN TO RECEIVING')
       )
    ) order by shipment_header_id asc;

Line: 322


            UPDATE rcv_transactions rt
            SET rt.receipt_confirmation_extracted  = 'P'
            WHERE rt.shipment_header_id = grh.shipment_header_id
            AND nvl(rt.receipt_confirmation_extracted, 'N') = 'N'
            AND (rt.transaction_type IN ('DELIVER') OR
                 (rt.TRANSACTION_TYPE IN ('CORRECT', 'RETURN TO RECEIVING')
                            AND EXISTS (SELECT '1' FROM rcv_transactions rt2
                                        WHERE rt.parent_transaction_id = rt2.transaction_id
                                        AND rt2.transaction_type = 'DELIVER')
                 ) OR
                 (rt.TRANSACTION_TYPE IN ('CORRECT')
                            AND EXISTS (SELECT '1' FROM rcv_transactions rt3
                                        WHERE rt.parent_transaction_id = rt3.transaction_id
                                        AND rt3.transaction_type = 'RETURN TO RECEIVING')
                 )
                )
            AND EXISTS (SELECT '1' FROM rcv_shipment_lines rsl
                        WHERE rsl.shipment_line_id = rt.shipment_line_id
                        AND wms_deploy.get_client_code(rsl.item_id) =  p_client_code);

Line: 350


            UPDATE rcv_transactions rt
            SET rt.receipt_confirmation_extracted  = 'P'
            WHERE rt.shipment_header_id = grh.shipment_header_id
            AND nvl(rt.receipt_confirmation_extracted, 'N') = 'N'
            AND (rt.transaction_type IN ('DELIVER') OR
                 (rt.TRANSACTION_TYPE IN ('CORRECT', 'RETURN TO RECEIVING')
                            AND EXISTS (SELECT '1' FROM rcv_transactions rt2
                                        WHERE rt.parent_transaction_id = rt2.transaction_id
                                        AND rt2.transaction_type = 'DELIVER')
                 ) OR
                 (rt.TRANSACTION_TYPE IN ('CORRECT')
                            AND EXISTS (SELECT '1' FROM rcv_transactions rt3
                                        WHERE rt.parent_transaction_id = rt3.transaction_id
                                        AND rt3.transaction_type = 'RETURN TO RECEIVING')
                 )
                );

Line: 399


            UPDATE rcv_transactions rt
            SET rt.receipt_confirmation_extracted  = null,
                rt.xml_document_id = null
            WHERE rt.shipment_header_id = grh.shipment_header_id
            AND nvl(rt.receipt_confirmation_extracted, 'N') = 'P'
            AND (rt.transaction_type IN ('DELIVER') OR
                 (rt.TRANSACTION_TYPE IN ('CORRECT', 'RETURN TO RECEIVING')
                            AND EXISTS (SELECT '1' FROM rcv_transactions rt2
                                        WHERE rt.parent_transaction_id = rt2.transaction_id
                                        AND rt2.transaction_type = 'DELIVER')
                 ) OR
                 (rt.TRANSACTION_TYPE IN ('CORRECT')
                            AND EXISTS (SELECT '1' FROM rcv_transactions rt3
                                        WHERE rt.parent_transaction_id = rt3.transaction_id
                                        AND rt3.transaction_type = 'RETURN TO RECEIVING')
                 )
                )
            AND EXISTS (SELECT '1' FROM rcv_shipment_lines rsl
                        WHERE rsl.shipment_line_id = rt.shipment_line_id
                        AND wms_deploy.get_client_code(rsl.item_id) =  p_client_code);

Line: 427


            UPDATE rcv_transactions rt
            SET rt.receipt_confirmation_extracted  = null,
                rt.xml_document_id = null
            WHERE rt.shipment_header_id = grh.shipment_header_id
            AND nvl(rt.receipt_confirmation_extracted, 'N') = 'P'
            AND (rt.transaction_type IN ('DELIVER') OR
                 (rt.TRANSACTION_TYPE IN ('CORRECT', 'RETURN TO RECEIVING')
                            AND EXISTS (SELECT '1' FROM rcv_transactions rt2
                                        WHERE rt.parent_transaction_id = rt2.transaction_id
                                        AND rt2.transaction_type = 'DELIVER')
                 ) OR
                 (rt.TRANSACTION_TYPE IN ('CORRECT')
                            AND EXISTS (SELECT '1' FROM rcv_transactions rt3
                                        WHERE rt.parent_transaction_id = rt3.transaction_id
                                        AND rt3.transaction_type = 'RETURN TO RECEIVING')
                 )
                );

Line: 504


    SELECT organization_name
    INTO l_ou_name
    FROM org_organization_definitions
    WHERE organization_id = p_org_id;