APPS.POS_CANCEL_ASN SQL Statements

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

Line: 54

     select  rsl.shipment_line_id
       from  RCV_SHIPMENT_LINES rsl,
             RCV_SHIPMENT_HEADERS rsh
      where  rsh.shipment_num = p_shipment_num
        and  rsh.shipment_header_id = rsl.shipment_header_id
	and  rsh.vendor_id = p_vendor_id
	and  rsh.vendor_site_id = p_vendor_site_id;

Line: 63

     select  invoice_id, org_id
       from  AP_INVOICES_ALL
      where  invoice_num = p_invoice_num
        and  vendor_id = p_vendor_id
	and  vendor_site_id = p_vendor_site_id;

Line: 99

   select count(*)
     into l_count
     from RCV_SHIPMENT_HEADERS
    where shipment_num = p_shipment_num
      and vendor_id = p_vendor_id
      and vendor_site_id = p_vendor_site_id;

Line: 113

   select count(*)
     into l_receipt_count
     from rcv_shipment_headers
    where shipment_num = p_shipment_num
      and vendor_id = p_vendor_id
      and vendor_site_id = p_vendor_site_id
      and receipt_num is not null;

Line: 202

     select  processing_status_code, transaction_type
       from  RCV_HEADERS_INTERFACE
      where  shipment_num = p_shipment_num
        and  vendor_id = p_vendor_id
        and  vendor_site_id = p_vendor_site_id;

Line: 246

       select count(*)
       into l_rsl_line_count
       from rcv_shipment_lines
       where shipment_header_id in
             (select shipment_header_id from rcv_shipment_headers
              where shipment_num = p_shipment_num
              and vendor_id = p_vendor_id and vendor_site_id = p_vendor_site_id);

Line: 287

     select header_interface_id
       from rcv_headers_interface
      where shipment_num = p_shipment_num
        and vendor_id = p_vendor_id
        and vendor_site_id = p_vendor_site_id;

Line: 301

     select distinct rti.asn_attach_id
       from rcv_transactions_interface rti,
            fnd_attached_documents fad
      where rti.header_interface_id = l_header_intf_id
        and rti.asn_attach_id is not null
        and to_char(rti.asn_attach_id) = fad.PK1_value
        and fad.entity_name = 'ASN_ATTACH';

Line: 320

        /* Delete ASN attachment if exists. */
        OPEN C_asn_attach (x_header_interface_id);

Line: 328

            RCV_ASN_ATTACHMENT_PKG.delete_line_attachment (
		p_api_version 	=> 1.0,
		p_init_msg_list => 'F',
		x_return_status	=> l_return_status,
		x_msg_count	=> l_msg_count,
		x_msg_data	=> l_msg_data,
		p_asn_attach_id => l_asn_attach_id );

Line: 341

        /* Delete ASN line from interface table. */
        delete
          from RCV_TRANSACTIONS_INTERFACE
         where header_interface_id = x_header_interface_id;

Line: 352

   delete
     from RCV_HEADERS_INTERFACE
    where shipment_num = p_shipment_num
      and vendor_id = p_vendor_id
      and vendor_site_id = p_vendor_site_id;

Line: 383

   select count(*)
     into x_total_lines
     from RCV_SHIPMENT_LINES rsl,
          RCV_SHIPMENT_HEADERS rsh
    where rsh.shipment_num = p_shipment_num
      and rsh.vendor_id = p_vendor_id
      and rsh.vendor_site_id = p_vendor_site_id
      and rsh.shipment_header_id = rsl.shipment_header_id;

Line: 397

   select count(*)
     into x_cancelled_lines
     from RCV_SHIPMENT_LINES rsl,
          RCV_SHIPMENT_HEADERS rsh
    where rsh.shipment_num = p_shipment_num
      and rsh.vendor_id = p_vendor_id
      and rsh.vendor_site_id = p_vendor_site_id
      and rsh.shipment_header_id = rsl.shipment_header_id
      and rsl.shipment_line_status_code = 'CANCELLED';

Line: 408

   select count(*)
     into x_pending_cancel
     from RCV_TRANSACTIONS_INTERFACE rti,
          RCV_SHIPMENT_HEADERS rsh
    where rti.transaction_type = 'CANCEL'
      and rti.shipment_header_id = rsh.shipment_header_id
      and rsh.shipment_num = p_shipment_num
      and rsh.vendor_id = p_vendor_id
      and rsh.vendor_site_id = p_vendor_site_id
      and rti.processing_status_code ='PENDING'; /*4946276 : Performance fix and */

Line: 460

     select  processing_status_code, transaction_type
       from  RCV_HEADERS_INTERFACE
      where  shipment_num = p_shipment_num
       and header_interface_id = p_header_id
        and  vendor_id = p_vendor_id
        and  vendor_site_id = p_vendor_site_id;

Line: 500

	select count(*)
	into l_rsl_line_count
	from rcv_shipment_lines
	where  shipment_header_id = p_header_id;

Line: 533

       select count(*)
       into l_rti_line_count
       from rcv_transactions_interface
       where HEADER_INTERFACE_ID = p_header_id;

Line: 538

       select 'Y'  into l_rti_error
       from dual
       where exists
         (select 1 from rcv_transactions_interface
          where HEADER_INTERFACE_ID = p_header_id
          and (processing_status_code = 'ERROR' or
               transaction_status_code = 'ERROR'));

Line: 573

      select fnd_message_cache.get_string('POS', 'POS_RUNNING')
      into x_processing_dsp
      from dual;

Line: 579

      select fnd_message_cache.get_string('POS', 'POS_PENDING')
      into x_processing_dsp
      from dual;

Line: 585

      select fnd_message_cache.get_string('POS', 'POS_ERROR')
      into x_processing_dsp
      from dual;

Line: 591

      select fnd_message_cache.get_string('POS', 'POS_MULTIPLE')
      into x_processing_dsp
      from dual;

Line: 604

   select count(*)
     into l_total_lines
     from RCV_SHIPMENT_LINES rsl,
          RCV_SHIPMENT_HEADERS rsh
    where rsh.shipment_num = p_shipment_num
      and rsh.vendor_id = p_vendor_id
      and rsh.vendor_site_id = p_vendor_site_id
      and rsh.shipment_header_id = rsl.shipment_header_id;

Line: 620

     select count(*)
     into l_cancelled_lines
     from RCV_SHIPMENT_LINES rsl,
          RCV_SHIPMENT_HEADERS rsh
      where rsh.shipment_num = p_shipment_num
      and rsh.vendor_id = p_vendor_id
      and rsh.vendor_site_id = p_vendor_site_id
      and rsh.shipment_header_id = rsl.shipment_header_id
      and rsl.shipment_line_status_code = 'CANCELLED';

Line: 631

     select count(*)
     into l_pending_cancel
     from RCV_TRANSACTIONS_INTERFACE rti,
          RCV_SHIPMENT_HEADERS rsh
      where rti.transaction_type = 'CANCEL'
      and rti.shipment_header_id = rsh.shipment_header_id
      and rsh.shipment_num = p_shipment_num
      and rsh.vendor_id = p_vendor_id
      and rsh.vendor_site_id = p_vendor_site_id;

Line: 662

      select fnd_message_cache.get_string('POS', 'POS_CANCELLED')
      into x_cancellation_dsp
      from dual;

Line: 668

      select fnd_message_cache.get_string('POS', 'POS_ASN_PENDING_CANCEL')
      into x_cancellation_dsp
      from dual;

Line: 674

      select fnd_message_cache.get_string('POS', 'POS_PARTIALLY_CANCELLED')
      into x_cancellation_dsp
      from dual;

Line: 711

     select  processing_status_code, transaction_type
       from  RCV_HEADERS_INTERFACE
      where  shipment_num = p_shipment_num
        and  vendor_id = p_vendor_id
        and  vendor_site_id = p_vendor_site_id;

Line: 766

       select count(*)
       into l_rsl_line_count
       from rcv_shipment_lines
       where shipment_header_id in
             (select shipment_header_id from rcv_shipment_headers
              where shipment_num = p_shipment_num
              and vendor_id = p_vendor_id and vendor_site_id = p_vendor_site_id);

Line: 799

      select fnd_message_cache.get_string('POS', 'POS_RUNNING')
      into x_processing_dsp
      from dual;

Line: 805

      select fnd_message_cache.get_string('POS', 'POS_PENDING')
      into x_processing_dsp
      from dual;

Line: 811

      select fnd_message_cache.get_string('POS', 'POS_ERROR')
      into x_processing_dsp
      from dual;

Line: 817

      select fnd_message_cache.get_string('POS', 'POS_MULTIPLE')
      into x_processing_dsp
      from dual;

Line: 830

   select count(*)
     into l_total_lines
     from RCV_SHIPMENT_LINES rsl,
          RCV_SHIPMENT_HEADERS rsh
    where rsh.shipment_num = p_shipment_num
      and rsh.vendor_id = p_vendor_id
      and rsh.vendor_site_id = p_vendor_site_id
      and rsh.shipment_header_id = rsl.shipment_header_id;

Line: 846

     select count(*)
     into l_cancelled_lines
     from RCV_SHIPMENT_LINES rsl,
          RCV_SHIPMENT_HEADERS rsh
      where rsh.shipment_num = p_shipment_num
      and rsh.vendor_id = p_vendor_id
      and rsh.vendor_site_id = p_vendor_site_id
      and rsh.shipment_header_id = rsl.shipment_header_id
      and rsl.shipment_line_status_code = 'CANCELLED';

Line: 857

     select count(*)
     into l_pending_cancel
     from RCV_TRANSACTIONS_INTERFACE rti,
          RCV_SHIPMENT_HEADERS rsh
      where rti.transaction_type = 'CANCEL'
      and rti.shipment_header_id = rsh.shipment_header_id
      and rsh.shipment_num = p_shipment_num
      and rsh.vendor_id = p_vendor_id
      and rsh.vendor_site_id = p_vendor_site_id;

Line: 888

      select fnd_message_cache.get_string('POS', 'POS_CANCELLED')
      into x_cancellation_dsp
      from dual;

Line: 894

      select fnd_message_cache.get_string('POS', 'POS_ASN_PENDING_CANCEL')
      into x_cancellation_dsp
      from dual;

Line: 900

      select fnd_message_cache.get_string('POS', 'POS_PARTIALLY_CANCELLED')
      into x_cancellation_dsp
      from dual;

Line: 928

     Select nvl(shipment_line_status_code, '')
       From RCV_SHIPMENT_LINES
      Where shipment_line_id = p_shipment_line_id;

Line: 933

     select nvl(transaction_type, '')
       from RCV_TRANSACTIONS_INTERFACE
      where shipment_line_id = p_shipment_line_id;

Line: 1002

   SELECT rcv_interface_groups_s.nextval
   INTO   x_group_id
   FROM   dual;

Line: 1006

   select
	RSL.PO_HEADER_ID,
	RSL.PO_RELEASE_ID,
	RSL.PO_LINE_ID,
	RSL.SHIPMENT_HEADER_ID,
	RSL.PO_LINE_LOCATION_ID,
	RSL.DELIVER_TO_LOCATION_ID,
	RSL.TO_ORGANIZATION_ID,
	RSL.ITEM_ID,
	RSL.QUANTITY_SHIPPED,
	RSL.SOURCE_DOCUMENT_CODE,
	RSL.CATEGORY_ID,
	RSL.UNIT_OF_MEASURE,
	RSL.ITEM_DESCRIPTION,
	RSL.EMPLOYEE_ID,
	RSL.DESTINATION_TYPE_CODE,
	RSL.DESTINATION_CONTEXT,
	RSL.TO_SUBINVENTORY,
	RSL.ROUTING_HEADER_ID,
	RSL.PRIMARY_UNIT_OF_MEASURE,
	RSL.SHIP_TO_LOCATION_ID,
        POHA.ORG_ID
     into
	X_po_header_id,
   	X_po_release_id,
   	X_po_line_id,
   	X_shipment_header_id,
   	X_po_line_location_id,
   	X_deliver_to_location_id,
   	X_to_organization_id,
   	X_item_id,
   	X_quantity_shipped,
   	X_source_document_code,
   	X_category_id,
   	X_unit_of_measure,
   	X_item_description,
   	X_employee_id,
   	X_destination_type_code,
   	X_destination_context,
   	X_subinventory,
   	X_routing_header_id,
   	X_primary_unit_of_measure,
   	X_ship_to_location_id,
        X_operating_unit_id
     from
	RCV_SHIPMENT_LINES  RSL,
        PO_HEADERS_ALL POHA
    where
	rsl.shipment_line_id = p_shipment_line_id
        and rsl.po_header_id = poha.po_header_id;

Line: 1057

   RCV_INSERT_RTI_SV.insert_into_rti(
                                x_group_id,
                                'CANCEL',
                                sysdate,
                                'PENDING',
                      	        'BATCH',
                                'PENDING',
                               	SYSDATE,
                               	1,
                               	1,
				'RCV',
                                SYSDATE,
                                1,
				'CANCEL',
				'VENDOR',
				X_po_header_id,
				X_po_release_id,
				X_po_line_id,
				p_shipment_line_id,
				X_shipment_header_id,
				X_po_line_location_id,
				X_deliver_to_location_id,
				X_to_organization_id,
				X_item_id,
				X_quantity_shipped,
				X_source_document_code,
				X_category_id,
				X_unit_of_measure,
				X_item_description,
				X_employee_id,
				X_destination_type_code,
				X_destination_context,
				X_subinventory,
				X_routing_header_id,
				X_primary_unit_of_measure,
				X_ship_to_location_id,
				p_vendor_id,
                                X_operating_unit_id);

Line: 1124

   l_last_update_date 		AP_INVOICES.last_update_date%TYPE;

Line: 1155

		OUT 	l_last_update_date,
		OUT 	l_dummy_amount,
		OUT 	l_pay_curr_invoice_amount,
		OUT 	l_token;

Line: 1182

		OUT 	l_last_update_date,
		OUT 	l_dummy_amount;

Line: 1219

   l_last_update_date 		AP_INVOICES.last_update_date%TYPE;

Line: 1253

		OUT 	l_last_update_date,
		OUT 	l_dummy_amount;

Line: 1276

        OUT     l_last_update_date,
        OUT     l_dummy_amount,
        OUT     l_pay_curr_invoice_amount,
        OUT     l_token;

Line: 1309

   select set_of_books_id, gl_date
     into l_set_of_books_id, l_gl_date
     from ap_invoices_all
    where invoice_id = p_invoice_id;