APPS.PO_CLM_CAR_UTIL SQL Statements

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

Line: 46

	SELECT * FROM PO_CLM_CARS WHERE po_header_id = p_h_id  AND po_draft_id = p_d_id AND car_id <> c_id AND reporting_method = 'SINGLE_CAR';

Line: 63

			SELECT DISTINCT REPORTING_METHOD
			  INTO l_reporting_method
			  FROM PO_CLM_CARS
			 WHERE PO_HEADER_ID = p_clm_car_row.po_header_id
			   AND PO_DRAFT_ID = -1;

Line: 72

		SELECT ORG_ID
		  INTO l_org_id
		  FROM PO_HEADERS_MERGE_V
		 WHERE PO_HEADER_ID = p_clm_car_row.po_header_id
		   AND DRAFT_ID = l_po_draft_id;

Line: 90

			  WHEN NOT MATCHED THEN  INSERT (c.car_id,
											 c.po_header_id,
											 c.po_draft_id,
											 c.report_type,
											 c.award_idv_type,
											 c.car_status,
											 c.car_number,
											 c.car_description,
											 c.piid,
											 c.modification_segment,
											 c.reporting_method,
											 c.approved_without_reporting,
											 c.rel_without_rpt_reason,
											 c.exemption_reason,
											 c.transaction_number,
											 c.agency_id,
											 c.contracting_office_agency_id,
											 c.prepared_user,
											 c.date_signed,
											 c.car_xml,
											 c.last_update_date,
											 c.last_updated_by,
											 c.creation_date,
											 c.created_by,
											 c.last_update_login)
									 VALUES (l_car_id,
											 p_clm_car_row.po_header_id,
											 l_po_draft_id,
											 NVL(p_clm_car_row.report_type, get_report_type(p_po_header_id => p_clm_car_row.po_header_id)),
											 p_clm_car_row.award_idv_type,
											 NVL(p_clm_car_row.car_status, 'DRAFT'),
											 NVL(p_clm_car_row.car_number, get_car_number(p_po_header_id  => p_clm_car_row.po_header_id,
																						  p_draft_id	  => l_po_draft_id)),
											 p_clm_car_row.car_description,
											 NVL(p_clm_car_row.piid, get_PIID(p_po_header_id  => p_clm_car_row.po_header_id,
																			  p_draft_id	  => l_po_draft_id)),
											 NVL(p_clm_car_row.modification_segment, get_modification_segment(p_po_header_id  => p_clm_car_row.po_header_id,
																											  p_draft_id	  => l_po_draft_id)),
											 NVL(p_clm_car_row.reporting_method, l_reporting_method),
											 p_clm_car_row.approved_without_reporting,
											 p_clm_car_row.rel_without_rpt_reason,
											 p_clm_car_row.exemption_reason,
											 NVL(p_clm_car_row.transaction_number, 0),
											 NVL(p_clm_car_row.agency_id, get_agency_id(l_org_id)), --CLM Controls Project Changes
											 NVL(p_clm_car_row.contracting_office_agency_id, get_contr_office_agency_id(l_org_id)), --CLM Controls Project Changes
											 NVL(p_clm_car_row.prepared_user, get_prepared_user()),
											 p_clm_car_row.date_signed,
											 p_clm_car_row.car_xml,
											 sysdate,
											 fnd_global.user_id,
											 sysdate,
											 fnd_global.user_id,
											 fnd_global.login_id)
			  WHEN MATCHED THEN UPDATE SET   c.report_type = NVL(p_clm_car_row.report_type, c.report_type),
											 c.award_idv_type = NVL(p_clm_car_row.award_idv_type, c.award_idv_type),
											 c.car_status = NVL(p_clm_car_row.car_status, c.car_status),
											 c.car_number = NVL(p_clm_car_row.car_number,
																DECODE(c.car_status, 'NOT_REPORTED', get_car_number(p_po_header_id  => c.po_header_id,
																 												    p_draft_id	    => c.po_draft_id), c.car_number)), -- if car is not reported, we refresh car number
											 c.car_description = NVL(p_clm_car_row.car_description, c.car_description),
											 c.piid = NVL(p_clm_car_row.piid, DECODE(c.car_status, 'NOT_REPORTED', get_PIID(p_po_header_id  => c.po_header_id,
																															p_draft_id	  	=> c.po_draft_id), c.piid)),  -- if car is not reported, we refresh piid
											 c.modification_segment = NVL(p_clm_car_row.modification_segment,
																		  DECODE(c.car_status, 'NOT_REPORTED', get_modification_segment(p_po_header_id  => c.po_header_id,
																																		p_draft_id	  	=> c.po_draft_id), c.modification_segment)), -- if car is not reported, we refresh mod num
											 c.reporting_method = NVL(p_clm_car_row.reporting_method, 'SINGLE_CAR'),   --Always set SINGLE_CAR for reporting method
											 c.approved_without_reporting = NVL(p_clm_car_row.approved_without_reporting, c.approved_without_reporting),
											 c.rel_without_rpt_reason = NVL(p_clm_car_row.rel_without_rpt_reason, c.rel_without_rpt_reason),
											 c.exemption_reason = NVL(p_clm_car_row.exemption_reason, c.exemption_reason),
											 c.transaction_number = NVL(p_clm_car_row.transaction_number, c.transaction_number),
											 c.agency_id = NVL(p_clm_car_row.agency_id, c.agency_id),
											 c.contracting_office_agency_id = NVL(p_clm_car_row.contracting_office_agency_id, c.contracting_office_agency_id),
											 c.prepared_user = NVL(p_clm_car_row.prepared_user, c.prepared_user),
											 c.date_signed = NVL(p_clm_car_row.date_signed, c.date_signed),
											 c.car_xml = NVL(p_clm_car_row.car_xml, c.car_xml),
											 c.last_update_date = sysdate,
											 c.last_updated_by = fnd_global.user_id,
											 c.creation_date = sysdate,
											 c.created_by = fnd_global.user_id,
											 c.last_update_login = fnd_global.login_id;

Line: 203

CURSOR c_car IS SELECT *
				  FROM PO_CLM_CARS
				 WHERE PO_HEADER_ID = p_po_header_id
				   AND PO_DRAFT_ID = NVL(p_po_draft_id, -1) FOR UPDATE;

Line: 213

			UPDATE PO_CLM_CARS
			   SET CAR_STATUS = NVL(p_car_status, l_car_rec.car_status),
			       CAR_DESCRIPTION = NVL(p_car_description, l_car_rec.car_description)
			 WHERE CURRENT OF c_car;

Line: 250

PROCEDURE delete_clm_car
(
	p_api_version		IN			NUMBER,
	p_car_id			IN			NUMBER,
	p_po_header_id		IN			NUMBER,
	p_po_draft_id		IN			NUMBER,
	p_commit			IN			VARCHAR2,
	p_validation_level	IN			VARCHAR2,
	x_return_status		OUT NOCOPY	VARCHAR2,
	x_error_msg			OUT NOCOPY	VARCHAR2

) IS

BEGIN
	-- delete the car
	DELETE FROM PO_CLM_CARS
	      WHERE car_id = p_car_id
		    AND po_header_id = p_po_header_id
			AND po_draft_id = NVL(p_po_draft_id, -1);

Line: 270

	--delete the logs of the car
	DELETE FROM PO_CLM_CAR_LOGS
	      WHERE car_id = p_car_id
		    AND po_header_id = p_po_header_id
			AND po_draft_id = NVL(p_po_draft_id, -1);

Line: 284

		x_error_msg := 'Cannot delete CAR: ' || dbms_utility.format_error_backtrace;

Line: 305

		DELETE FROM PO_CLM_CAR_LOGS
			  WHERE car_id = p_car_id
				AND po_header_id = p_po_header_id
				AND po_draft_id = NVL(p_po_draft_id, -1);

Line: 314

			INSERT INTO PO_CLM_CAR_LOGS ( car_id,
										  po_header_id,
										  po_draft_id,
										  source_transaction,
										  module,
										  message_level,
										  message_text,
										  last_update_date,
										  last_updated_by,
										  creation_date,
										  created_by,
										  last_update_login,
										  message_code
										)
										VALUES( p_clm_car_log_tab(i).car_id,
												p_clm_car_log_tab(i).po_header_id,
												NVL(p_clm_car_log_tab(i).po_draft_id, -1),
												p_clm_car_log_tab(i).source_transaction,
												p_clm_car_log_tab(i).module,
												p_clm_car_log_tab(i).message_level,
												p_clm_car_log_tab(i).message_text,
												sysdate,
												fnd_global.user_id,
												sysdate,
												fnd_global.user_id,
												fnd_global.login_id,
												p_clm_car_log_tab(i).message_code);

Line: 382

		SELECT PIID, MODIFICATION_SEGMENT
		  INTO l_piid_from_tab, l_mod_num_from_tab
		  FROM PO_CLM_CARS
		 WHERE CAR_ID = p_car_id
		   AND PO_HEADER_ID = p_po_header_id
		   AND PO_DRAFT_ID = NVL(p_po_draft_id, -1);

Line: 389

		SELECT PIID, MODIFICATION_SEGMENT
		  INTO l_piid_from_tab, l_mod_num_from_tab
		  FROM PO_CLM_CARS
		 WHERE PO_HEADER_ID = p_po_header_id
		   AND PO_DRAFT_ID = NVL(p_po_draft_id, -1);

Line: 439

	SELECT CLM_SOURCE_DOCUMENT_ID
	  INTO l_ref_idv_id
	  FROM PO_HEADERS_ALL
	 WHERE PO_HEADER_ID = p_po_header_id;

Line: 446

			SELECT AGENCY_ID
			  INTO x_ref_idv_agency_id
			  FROM PO_CLM_CARS
			 WHERE PO_HEADER_ID = l_ref_idv_id
			   AND PO_DRAFT_ID = -1;

Line: 460

  	SELECT  CLM_EXTERNAL_IDV
	  INTO x_ref_idv_piid
	  FROM PO_HEADERS_ALL
	  WHERE PO_HEADER_ID = p_po_header_id;

Line: 483

	SELECT CAR_STATUS
	  INTO l_car_status
	  FROM PO_CLM_CARS
	 WHERE CAR_ID = p_car_id
	   AND PO_HEADER_ID = p_po_header_id
	   AND PO_DRAFT_ID = p_po_draft_id;

Line: 504

  SELECT TYPE_LOOKUP_CODE
  INTO l_type_lookup_code
  FROM PO_HEADERS_ALL
  WHERE PO_HEADER_ID = p_po_header_id;

Line: 533

  SELECT DECODE(POH.draft_id, -1, POH.clm_document_number, POD.modification_number)
  INTO l_car_number
  FROM PO_DRAFTS POD, PO_HEADERS_MERGE_V POH
  WHERE POH.DRAFT_ID = POD.DRAFT_ID(+)
  AND POH.po_header_id = p_po_header_id
  AND poh.draft_id = NVL(p_draft_id, -1);

Line: 550

  SELECT replace(CLM_DOCUMENT_NUMBER, PO_DOC_NUMBERING_PKG.GET_DELIMITER_FOR_DOC (po_header_id, draft_id), '')
  INTO l_piid
  from PO_HEADERS_MERGE_V
  WHERE po_header_id = p_po_header_id
  AND draft_id = NVL(p_draft_id, -1);

Line: 566

  SELECT REPLACE(MODIFICATION_NUMBER, CLM_DOCUMENT_NUMBER||PO_DOC_NUMBERING_PKG.GET_DELIMITER_FOR_DOC (POH.po_header_id, POH.draft_id), '')
  INTO l_modification_segment
  FROM PO_DRAFTS POD, PO_HEADERS_MERGE_V POH
  WHERE POH.DRAFT_ID = POD.DRAFT_ID(+)
  AND POH.po_header_id = p_po_header_id
  AND poh.draft_id = NVL(p_draft_id, -1);

Line: 653

  UPDATE PO_CLM_CARS
  SET CAR_STATUS = 'DRAFT'
  WHERE PO_HEADER_ID = p_po_header_id
  AND PO_DRAFT_ID = p_po_draft_id
  AND CAR_STATUS = 'AUTHENTICATED';

Line: 660

PROCEDURE insert_mod_default_car
(
    p_po_header_id  IN  NUMBER,
    p_draft_id   IN  NUMBER,
    p_invoked_from  IN  VARCHAR2 DEFAULT 'CREATE_MOD_UI',
    p_rel_without_rpt_reason IN VARCHAR2 DEFAULT NULL
) IS
l_car_id NUMBER;

Line: 680

       SELECT ORG_ID
	 INTO l_org_id
	 FROM PO_HEADERS_MERGE_V
	WHERE PO_HEADER_ID = p_po_header_id
	  AND DRAFT_ID = p_draft_id;

Line: 692

        SELECT exemption_reason INTO l_exemption_reason
        FROM po_clm_cars
        WHERE po_header_id = p_po_header_id
        AND po_draft_id = -1
        AND reporting_method = 'EXEMPT';

Line: 706

      SELECT car_id INTO l_car_id
      FROM po_clm_cars
      WHERE po_header_id = p_po_header_id
      AND po_draft_id = p_draft_id;

Line: 713

      select ATM.FPDS_AWARD_TYPE INTO l_award_idv_type
      from
      PO_CLM_TO_FPDS_AWARD_TYP_MAP ATM,
      PO_HEADERS_MERGE_V POH
      WHERE
      POH.PO_HEADER_ID =  p_po_header_id AND
      POH.DRAFT_ID = p_draft_id AND
      ATM.REPORT_TYPE(+) = l_report_type AND
      ATM.CLM_AWARD_TYPE(+) = POH.CLM_AWARD_TYPE;

Line: 723

      INSERT INTO po_clm_cars(
        car_id,
        po_header_id,
        po_draft_id,
        report_type,
        award_idv_type,
        car_status,
        car_number,
        car_description,
        modification_segment,
        reporting_method,
        approved_without_reporting,
        rel_without_rpt_reason,
        contracting_office_agency_id,
        exemption_reason,
        transaction_number,
        agency_id,
        prepared_user,
        LAST_UPDATE_DATE,
        LAST_UPDATED_BY,
        CREATION_DATE,
        CREATED_BY,
        LAST_UPDATE_LOGIN,
        PIID)
        VALUES(
            PO_CLM_CARS_S.NEXTVAL,
            p_po_header_id,
            p_draft_id,
            l_report_type,
            l_award_idv_type,
            'NOT_REPORTED',
            po_clm_car_util.get_car_number(p_po_header_id, p_draft_id),
            NULL,
            po_clm_car_util.get_modification_segment(p_po_header_id, p_draft_id),
            Decode(p_invoked_from,'MULTI_MOD','NONE','EXEMPT'),
            Decode(p_invoked_from,'MULTI_MOD',1,NULL),
            Decode(p_invoked_from,'MULTI_MOD',p_rel_without_rpt_reason,NULL),
            po_clm_car_util.get_contr_office_agency_id(l_org_id), --CLM Controls Project Changes
            l_exemption_reason,
            '0',
            po_clm_car_util.get_agency_id(l_org_id), --CLM Controls Project Changes
            po_clm_car_util.get_prepared_user(),
            SYSDATE,
            FND_GLOBAL.user_id,
            SYSDATE,
            FND_GLOBAL.user_id,
            FND_GLOBAL.login_id,
            po_clm_car_util.get_piid(p_po_header_id, p_draft_id));

Line: 839

	delete_clm_car(	p_api_version	=> 1.0,
					p_car_id		=> 10076,
					p_po_header_id	=> 106440,
					p_po_draft_id	=> -1,
					p_commit		=> 'T',
					p_validation_level	=> NULL,
					x_return_status		=> status,
					x_error_msg		=> error_msg);*/

Line: 915

l_update_car VARCHAr2(1);

Line: 943

    SELECT org_id
    INTO l_org_id
    FROM po_headers_merge_v
    WHERE po_header_id = p_po_header_id
          AND NVL(draft_id, -1) = p_draft_id;

Line: 952

      SELECT car_id
      INTO l_car_id
      FROM po_clm_cars
      WHERE po_header_id = p_po_header_id
            AND NVL(po_draft_id, -1) = p_draft_id;

Line: 970

      UPDATE po_clm_cars
      SET reporting_method =  l_fpds_reporting_method,
          approved_without_reporting = DECODE( l_fpds_reporting_method,
                                              'NONE', 1,
                                               2),
          rel_without_rpt_reason = l_fpds_reason,
		  exemption_reason = l_exemptiom_reason,
          last_update_date = sysdate,
          last_updated_by = FND_GLOBAL.user_id,
          last_update_login = FND_GLOBAL.login_id
      WHERE car_id = l_car_id
            AND reporting_method <> l_fpds_reporting_method
            AND rel_without_rpt_reason <> l_fpds_reason;

Line: 986

        PO_LOG.stmt(d_module, d_progress, 'Updated car ' || SQL%ROWCOUNT);

Line: 993

        PO_LOG.stmt(d_module, d_progress, 'Inserting new car');

Line: 996

      SELECT ATM.FPDS_AWARD_TYPE
      INTO l_award_idv_type
      FROM PO_CLM_TO_FPDS_AWARD_TYP_MAP ATM,
           PO_HEADERS_MERGE_V POH
      WHERE POH.PO_HEADER_ID =  p_po_header_id
	  AND NVL(POH.DRAFT_ID,-1) = p_draft_id
	  AND ATM.REPORT_TYPE(+) = l_report_type
      AND ATM.CLM_AWARD_TYPE(+) = POH.CLM_AWARD_TYPE;

Line: 1009

      INSERT INTO po_clm_cars(
        car_id,
        po_header_id,
        po_draft_id,
        report_type,
        award_idv_type,
        car_status,
        car_number,
        car_description,
        modification_segment,
        reporting_method,
        approved_without_reporting,
        rel_without_rpt_reason,
        contracting_office_agency_id,
        exemption_reason,
        transaction_number,
        agency_id,
        prepared_user,
        last_update_date,
        last_updated_by,
        creation_date,
        created_by,
        last_update_login,
        piid)
        VALUES(
            PO_CLM_CARS_S.NEXTVAL,
            p_po_header_id,
            p_draft_id,
            l_report_type,
            l_award_idv_type,
            'NOT_REPORTED',
            l_car_number,
            NULL,
            l_modification_segment,
            l_fpds_reporting_method,
            DECODE( l_fpds_reporting_method,
                    'NONE', 1,
                    2),
            l_fpds_reason,
            po_clm_car_util.get_contr_office_agency_id(l_org_id), --CLM Controls Project Changes
            l_exemptiom_reason,
            '0',
            po_clm_car_util.get_agency_id(l_org_id), --CLM Controls Project Changes
            po_clm_car_util.get_prepared_user(),
            SYSDATE,
            FND_GLOBAL.user_id,
            SYSDATE,
            FND_GLOBAL.user_id,
            FND_GLOBAL.login_id,
            l_PIID);