APPS.PO_REQIMP_PKG SQL Statements

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

Line: 16

l_no_rows_updated       EXCEPTION;

Line: 18

l_update_failure        EXCEPTION;

Line: 44

    IS SELECT NVL(t.conversion_rate,0),
              t.uom_class,
              NVL(f.conversion_rate,0),
              f.uom_class
         FROM mtl_uom_conversions t,
              mtl_uom_conversions f
        WHERE t.inventory_item_id IN (x_item_id,0)
          AND t.unit_of_measure = x_unit_of_purchase
          AND f.inventory_item_id IN (x_item_id,0)
          AND f.unit_of_measure = x_unit_of_measure
     ORDER BY t.inventory_item_id desc, f.inventory_item_id desc ;

Line: 101

         SELECT round(x_unit_price,2)
           INTO x_unit_price
           FROM dual ;

Line: 127

    SELECT nvl(conversion_rate,0)
      INTO x_class_rate
      FROM mtl_uom_class_conversions
     WHERE inventory_item_id = x_item_id
       AND to_uom_class = x_item_uom_class
       AND from_uom_class = x_from_class ;

Line: 244

    SELECT ceil(l_quantity)
      INTO l_quantity
      FROM dual ;

Line: 283

    IS SELECT poll.price_override
         FROM po_line_locations poll
        WHERE price_override is not null
          AND sysdate between nvl(start_date,sysdate-1)
                          and nvl(end_date,sysdate+1)
          AND (poll.quantity is null
               OR
               poll.quantity <= x_quantity)
          AND unit_meas_lookup_code = x_unit_of_purchase
          AND po_line_id = x_po_line_id
          -- Bug : 4236157
          AND poll.shipment_type <> 'PREPAYMENT'
     ORDER BY price_override asc ;

Line: 334

SELECT pad.document_header_id,
       pol.line_num,
       pol.po_line_id,
       pol.vendor_product_num,
       poh.vendor_id,
       poh.vendor_site_id ,
       poh.vendor_contact_id,
       pov.vendor_name,
       povs.vendor_site_code,
       nvl(pol.unit_price,0),
       pol.unit_meas_lookup_code
  FROM po_autosource_documents pad,
       po_headers poh,
       po_lines pol,
       po_vendors pov,
       po_vendor_sites povs,
       po_autosource_vendors ven,
       po_autosource_rules rul
 WHERE pad.autosource_rule_id = rul.autosource_rule_id
   AND pad.vendor_id = ven.vendor_id
   AND ven.autosource_rule_id = rul.autosource_rule_id
   AND ven.autosource_rule_id = pad.autosource_rule_id
   AND pad.document_header_id = poh.po_header_id
   AND pad.document_line_id   = pol.po_line_id
   AND  ((    poh.type_lookup_code = 'BLANKET'
              AND poh.approved_flag    = 'Y'
              AND nvl(poh.frozen_flag,'N') = 'N'
              AND nvl(poh.cancel_flag,'N') = 'N'
              AND nvl(pol.cancel_flag,'N') = 'N')
              OR
          (   poh.type_lookup_code = 'QUOTATION'
              AND poh.status_lookup_code = 'A'))
   AND poh.vendor_id = pov.vendor_id
   AND poh.vendor_site_id = povs.vendor_site_id(+)
   AND poh.vendor_id  = povs.vendor_id(+)
   AND SYSDATE between nvl(poh.start_date, SYSDATE)
   AND nvl(poh.end_date, SYSDATE+1)
   AND rul.item_id = x_item_id
   AND sysdate between  nvl(rul.start_date, sysdate)
   AND nvl(rul.end_date, sysdate+1)
ORDER BY vendor_rank asc, sequence_num ;

Line: 379

   SELECT paa.purchasing_unit_of_measure,
          pl.UNIT_MEAS_LOOKUP_CODE,
          pl.unit_price,
          ph.po_header_id,
          pl.line_num,
          pl.po_line_id,
          pl.unit_price
   FROM   po_approved_supplier_list pasl,
          po_vendors pv,
	      po_vendor_sites_all pvs,
	      po_asl_attributes paa,
	      po_asl_documents pad,
	      po_headers_all ph,
 	      po_lines_all pl,
          mrp_sr_source_org msso
   WHERE  pvs.vendor_site_id = pasl.vendor_site_id
   AND    pv.vendor_id = pasl.vendor_id
   AND    pasl.item_id = x_item_id
   AND    pasl.asl_id = paa.asl_id
   AND    pasl.asl_id = pad.asl_id
   AND    ph.po_header_id = pl.po_header_id
   AND  ((    ph.type_lookup_code = 'BLANKET'
	       AND ph.approved_flag    = 'Y'
               AND nvl(ph.frozen_flag,'N') = 'N'
	       AND nvl(ph.cancel_flag,'N') = 'N'
	       AND nvl(pl.cancel_flag,'N') = 'N')
	   OR
	  (   ph.type_lookup_code = 'QUOTATION'
	      AND ph.status_lookup_code = 'A'))
   AND    ph.po_header_id = pad.document_header_id
   AND SYSDATE between nvl(ph.start_date, SYSDATE) AND nvl(ph.end_date, SYSDATE+1)
   AND pl.item_id = x_item_id
   AND msso.vendor_id = pv.vendor_id
   AND (pasl.disable_flag IS NULL OR pasl.disable_flag = 'N')
   ORDER BY msso.allocation_percent desc, msso.rank, pad.sequence_num;   -- Added allocation_percent

Line: 420

   SELECT paa.purchasing_unit_of_measure,
          pl.UNIT_MEAS_LOOKUP_CODE,
          pl.unit_price,
          ph.po_header_id,
          pl.line_num,
          pl.po_line_id,
          pl.unit_price
   FROM   po_approved_supplier_list pasl,
          po_vendors pv,
	      po_vendor_sites_all pvs,
	      po_asl_attributes paa,
	      po_asl_documents pad,
	      po_headers_all ph,
 	      po_lines_all pl,
          mrp_sr_source_org msso
   WHERE  pvs.vendor_site_id = pasl.vendor_site_id
   AND    pv.vendor_id = pasl.vendor_id
   AND    pasl.item_id = x_item_id
   AND    pasl.asl_id = paa.asl_id
   AND    pasl.asl_id = pad.asl_id
   AND    ph.po_header_id = pl.po_header_id
   AND  ((    ph.type_lookup_code = 'BLANKET'
	       AND ph.approved_flag    = 'Y'
               AND nvl(ph.frozen_flag,'N') = 'N'
	       AND nvl(ph.cancel_flag,'N') = 'N'
	       AND nvl(pl.cancel_flag,'N') = 'N')
	   OR
	  (   ph.type_lookup_code = 'QUOTATION'
	      AND ph.status_lookup_code = 'A'))
   AND    ph.po_header_id = pad.document_header_id
   AND SYSDATE between nvl(ph.start_date, SYSDATE) AND nvl(ph.end_date, SYSDATE+1)
   AND pl.item_id = x_item_id
   AND msso.vendor_id = pv.vendor_id
   AND (pasl.disable_flag IS NULL OR pasl.disable_flag = 'N')
   AND pv.vendor_id = x_suggested_vendor_id
   AND pvs.vendor_site_id = x_suggested_vendor_site_id
   ORDER BY msso.allocation_percent desc, msso.rank, pad.sequence_num;   -- Added allocation_percent

Line: 566

       SELECT unit_of_measure
         INTO l_unit_of_measure
         FROM mtl_units_of_measure
        WHERE uom_code = x_uom_code ;

Line: 578

    SELECT count(*) INTO g_count FROM PO_APPROVED_SUPPLIER_LIST;

Line: 583

            select unit_meas_lookup_code
              into l_uom_direct
              from po_lines_all
             where po_header_id = x_autosource_doc_header_id
               and line_num = x_autosource_doc_line_num;

Line: 613

       SELECT uom_code
         INTO l_uom_code
         FROM mtl_units_of_measure
        WHERE unit_of_measure = l_unit_of_purchase ;

Line: 629

       SELECT list_price_per_unit
         INTO l_unit_price
         FROM mtl_system_items
        WHERE inventory_item_id = x_item_id
          AND organization_id = x_organization_id ;

Line: 635

       UPDATE po_requisitions_interface
          SET unit_price = l_unit_price
        WHERE rowid = x_rowid ;

Line: 662

         UPDATE po_requisitions_interface
         SET quantity = x_quantity
             -- uom_code = l_uom_code,
             -- unit_of_measure = l_unit_of_purchase
         WHERE rowid = x_rowid;

Line: 668

         UPDATE po_requisitions_interface
            SET unit_of_measure             = l_unit_of_purchase,
                uom_code                    = l_uom_code,
                unit_price                  = l_unit_price,
                quantity                    = x_quantity,
                autosource_doc_header_id    = l_document_header_id,
                autosource_doc_line_num     = l_document_line_num,
                suggested_vendor_name       = l_vendor_name,
                suggested_vendor_id         = l_vendor_id,
                suggested_vendor_site       = l_vendor_site_code,
                suggested_vendor_site_id    = l_vendor_site_id,
                suggested_vendor_contact_id = l_vendor_contact_id,
                suggested_vendor_contact    = null,
                suggested_vendor_phone      = null,
                suggested_vendor_item_num   = l_vendor_product_number
          WHERE rowid = x_rowid ;

Line: 727

    SELECT nvl(unit_of_issue,primary_unit_of_measure)
      INTO l_unit_of_issue
      FROM mtl_system_items
     WHERE inventory_item_id = x_item_id
       AND organization_id = x_source_organization_id ;

Line: 747

            SELECT uom_code
              INTO l_uoi_code
              FROM mtl_units_of_measure
             WHERE unit_of_measure = l_unit_of_issue ;

Line: 752

            UPDATE po_requisitions_interface
               SET unit_of_measure = l_unit_of_issue,
                   uom_code = l_uoi_code,
                   quantity = l_quantity,
                   unit_price = l_unit_price
             WHERE rowid = x_rowid  ;

Line: 774

PROCEDURE JA_AU_UPDATE_ERRORS
          (x_rowid      	IN
		varchar2,
           x_transaction_id	IN
		po_requisitions_interface.transaction_id%TYPE)
IS
BEGIN

     UPDATE po_requisitions_interface
        SET charge_account_id = 0,
	    request_id = NULL,
            process_flag = 'ERROR'
      WHERE rowid = x_rowid;

Line: 789

          RAISE l_update_failure;

Line: 792

     INSERT INTO po_interface_errors
	(
	 interface_type,
	 interface_transaction_id,
	 error_message,
	 processing_date,
	 creation_date,
	 created_by,
 	 last_update_date,
	 last_updated_by
	)
     VALUES
	(
	 'REQIMPORT',
	 x_transaction_id,
	 l_error_msg,
	 sysdate,
	 sysdate,
	 -1,
	 sysdate,
	 -1
	);

Line: 816

     WHEN l_update_failure THEN
--          DBMS_OUTPUT.NEW_LINE;

Line: 830

END JA_AU_UPDATE_ERRORS;

Line: 851

     SELECT chart_of_accounts_id, set_of_books_id
     INTO x_chart_of_accts_id, x_set_of_books_id
     FROM org_organization_definitions
     WHERE organization_id = x_org_id
     AND nvl(disable_date, sysdate+1) > sysdate ;

Line: 861

          JA_AU_update_errors(x_rowid,x_transaction_id);

Line: 897

     SELECT nvl(expense_account, -1)
     INTO x_subinv_ccid
     FROM mtl_secondary_inventories
     WHERE organization_id = x_org_id
     AND secondary_inventory_name = x_subinv ;

Line: 910

     SELECT nvl(expense_account, -1)
     INTO x_item_ccid
     FROM mtl_system_items
     WHERE organization_id = x_org_id
     AND inventory_item_id = x_item_id ;

Line: 925

          JA_AU_update_errors(x_rowid,x_transaction_id);

Line: 928

          JA_AU_update_errors(x_rowid,x_transaction_id);

Line: 961

          SELECT nvl(segment30,'!@')
            INTO l_value
            FROM gl_code_combinations
           WHERE code_combination_id = x_ccid ;

Line: 966

          SELECT nvl(segment29,'!@')
            INTO l_value
            FROM gl_code_combinations
           WHERE code_combination_id = x_ccid ;

Line: 971

          SELECT nvl(segment28,'!@')
            INTO l_value
            FROM gl_code_combinations
           WHERE code_combination_id = x_ccid ;

Line: 976

          SELECT nvl(segment27,'!@')
            INTO l_value
            FROM gl_code_combinations
           WHERE code_combination_id = x_ccid ;

Line: 981

          SELECT nvl(segment26,'!@')
            INTO l_value
            FROM gl_code_combinations
           WHERE code_combination_id = x_ccid ;

Line: 986

          SELECT nvl(segment25,'!@')
            INTO l_value
            FROM gl_code_combinations
           WHERE code_combination_id = x_ccid ;

Line: 991

          SELECT nvl(segment24,'!@')
            INTO l_value
            FROM gl_code_combinations
           WHERE code_combination_id = x_ccid ;

Line: 996

          SELECT nvl(segment23,'!@')
            INTO l_value
            FROM gl_code_combinations
           WHERE code_combination_id = x_ccid ;

Line: 1001

          SELECT nvl(segment22,'!@')
            INTO l_value
            FROM gl_code_combinations
           WHERE code_combination_id = x_ccid ;

Line: 1006

          SELECT nvl(segment21,'!@')
            INTO l_value
            FROM gl_code_combinations
           WHERE code_combination_id = x_ccid ;

Line: 1011

          SELECT nvl(segment20,'!@')
            INTO l_value
            FROM gl_code_combinations
           WHERE code_combination_id = x_ccid ;

Line: 1016

          SELECT nvl(segment19,'!@')
            INTO l_value
            FROM gl_code_combinations
           WHERE code_combination_id = x_ccid ;

Line: 1021

          SELECT nvl(segment18,'!@')
            INTO l_value
            FROM gl_code_combinations
           WHERE code_combination_id = x_ccid ;

Line: 1026

          SELECT nvl(segment17,'!@')
            INTO l_value
            FROM gl_code_combinations
           WHERE code_combination_id = x_ccid ;

Line: 1031

          SELECT nvl(segment16,'!@')
            INTO l_value
            FROM gl_code_combinations
           WHERE code_combination_id = x_ccid ;

Line: 1036

          SELECT nvl(segment15,'!@')
            INTO l_value
            FROM gl_code_combinations
           WHERE code_combination_id = x_ccid ;

Line: 1041

          SELECT nvl(segment14,'!@')
            INTO l_value
            FROM gl_code_combinations
           WHERE code_combination_id = x_ccid ;

Line: 1046

          SELECT nvl(segment13,'!@')
            INTO l_value
            FROM gl_code_combinations
           WHERE code_combination_id = x_ccid ;

Line: 1051

          SELECT nvl(segment12,'!@')
            INTO l_value
            FROM gl_code_combinations
           WHERE code_combination_id = x_ccid ;

Line: 1056

          SELECT nvl(segment11,'!@')
            INTO l_value
            FROM gl_code_combinations
           WHERE code_combination_id = x_ccid ;

Line: 1061

          SELECT nvl(segment10,'!@')
            INTO l_value
            FROM gl_code_combinations
           WHERE code_combination_id = x_ccid ;

Line: 1066

          SELECT nvl(segment9,'!@')
            INTO l_value
            FROM gl_code_combinations
           WHERE code_combination_id = x_ccid ;

Line: 1071

          SELECT nvl(segment8,'!@')
            INTO l_value
            FROM gl_code_combinations
           WHERE code_combination_id = x_ccid ;

Line: 1076

          SELECT nvl(segment7,'!@')
            INTO l_value
            FROM gl_code_combinations
           WHERE code_combination_id = x_ccid ;

Line: 1081

          SELECT nvl(segment6,'!@')
            INTO l_value
            FROM gl_code_combinations
           WHERE code_combination_id = x_ccid ;

Line: 1086

          SELECT nvl(segment5,'!@')
            INTO l_value
            FROM gl_code_combinations
           WHERE code_combination_id = x_ccid ;

Line: 1091

          SELECT nvl(segment4,'!@')
            INTO l_value
            FROM gl_code_combinations
           WHERE code_combination_id = x_ccid ;

Line: 1096

          SELECT nvl(segment3,'!@')
            INTO l_value
            FROM gl_code_combinations
           WHERE code_combination_id = x_ccid ;

Line: 1101

          SELECT nvl(segment2,'!@')
            INTO l_value
            FROM gl_code_combinations
           WHERE code_combination_id = x_ccid ;

Line: 1106

          SELECT nvl(segment1,'!@')
            INTO l_value
            FROM gl_code_combinations
           WHERE code_combination_id = x_ccid ;

Line: 1123

          JA_AU_update_errors(x_rowid,x_transaction_id);

Line: 1126

          JA_AU_update_errors(x_rowid,x_transaction_id);

Line: 1129

          JA_AU_update_errors(x_rowid,x_transaction_id);

Line: 1190

          JA_AU_update_errors(x_rowid,x_transaction_id);

Line: 1208

PROCEDURE JA_AU_UPDATE_REQINTERFACE
          (x_rowid      IN
           varchar2,
           x_transaction_id     IN
           po_requisitions_interface.transaction_id%TYPE,
           x_ccid       IN
           gl_code_combinations.code_combination_id%TYPE)
IS
BEGIN

     UPDATE po_requisitions_interface
        SET charge_account_id = x_ccid
      WHERE rowid = x_rowid;

Line: 1223

          RAISE l_no_rows_updated;

Line: 1229

     WHEN l_no_rows_updated THEN
--          DBMS_OUTPUT.NEW_LINE;

Line: 1235

          l_error_msg := 'AUTOGL ERROR - Update of charge_account_id in po_requisitions_interface failed.';

Line: 1236

          JA_AU_update_errors(x_rowid,x_transaction_id);

Line: 1244

END JA_AU_UPDATE_REQINTERFACE;

Line: 1303

SELECT nvl(upper(s.table_name), '!~') TABLE_NAME,
       nvl(s.constant, '!~') CONSTANT,
       s.segment
  FROM JA_AU_ACCT_DEFAULT_SEGS s, ja_au_account_defaults d
 WHERE s.gl_default_id = d.gl_default_id
   AND d.set_of_books_id = l_set_of_books_id
ORDER BY d.type,s.segment_num ;

Line: 1411

           JA_AU_update_errors(x_rowid,l_transaction_id);

Line: 1416

     JA_AU_update_reqinterface(x_rowid,
			      l_transaction_id,
                              l_ccid);

Line: 1451

    IS SELECT pri.source_type_code,
              pri.requisition_header_id,
              pri.requisition_line_id,
              pri.req_distribution_id,
              pri.requisition_type,
              pri.unit_price,
              pri.autosource_flag,
              pri.item_id,
              pri.charge_account_id,
              pri.unit_of_measure,
              pri.uom_code,
              pri.source_organization_id,
              pri.destination_organization_id,
              pri.source_subinventory,
              pri.destination_organization_id,
              pri.destination_subinventory,
	      pri.destination_type_code,
              pri.deliver_to_location_id,
              pri.quantity,
              pri.transaction_id,
              pri.rowid
              ,pri.autosource_doc_header_id
              ,pri.autosource_doc_line_num
              ,pri.suggested_vendor_id
              ,pri.suggested_vendor_site_id
         FROM po_requisitions_interface pri
        WHERE request_id = x_request_id ;