APPS.CS_CONTRACT_BILLING SQL Statements

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

Line: 135

		SELECT
			CON.CONTRACT_ID 			CONTRACT_ID,
			CON.CONTRACT_NUMBER			CONTRACT_NUMBER,
			SRV.CP_SERVICE_ID			CP_SERVICE_ID,
			SRV.EXTENDED_PRICE			EXTENDED_PRICE,
			SRV.DURATION_QUANTITY		DURATION,
			SRV.UNIT_OF_MEASURE_CODE		PERIOD,
			SRV.BILLING_FREQUENCY_PERIOD  BILLING_PERIOD,
			SRV.NEXT_BILL_DATE			NEXT_BILL_DATE,
			SRV.FIRST_BILL_DATE			FIRST_BILL_DATE,
			SRV.START_DATE_ACTIVE		START_DATE_ACTIVE,
			SRV.END_DATE_ACTIVE			END_DATE_ACTIVE,
			SRV.BILL_ON				BILL_ON,
			SRV.SERVICE_INVENTORY_ITEM_ID	INVENTORY_ITEM_ID
		FROM
			CS_CONTRACTS 				CON,
			CS_CP_SERVICES 			SRV,
			CS_CONTRACT_STATUSES 		STS
 		WHERE
			CON.CONTRACT_ID  = SRV.CONTRACT_ID AND
			CON.CONTRACT_NUMBER  =
				 NVL(p_contract_number,CON.CONTRACT_NUMBER) AND
			STS.ELIGIBLE_FOR_INVOICING = 'Y' AND
		     (
			 ( SRV.FIRST_BILL_DATE <= p_date_range AND
			   SRV.NEXT_BILL_DATE IS NULL
			  ) OR

		 	 (  SRV.NEXT_BILL_DATE <= p_date_range AND
			    SRV.NEXT_BILL_DATE <= SRV.END_DATE_ACTIVE
			  )
			 ) AND
			SRV.CONTRACT_LINE_STATUS_ID = STS.CONTRACT_STATUS_ID ;

Line: 168

		--FOR UPDATE OF CON.CONTRACT_ID NOWAIT;

Line: 171

		SELECT
			CON.CONTRACT_ID 			CONTRACT_ID,
			CON.CONTRACT_NUMBER			CONTRACT_NUMBER,
			SRV.CP_SERVICE_ID			CP_SERVICE_ID,
			SRV.EXTENDED_PRICE			EXTENDED_PRICE,
			SRV.DURATION_QUANTITY		DURATION,
			SRV.UNIT_OF_MEASURE_CODE		PERIOD,
			SRV.BILLING_FREQUENCY_PERIOD  BILLING_PERIOD,
			SRV.NEXT_BILL_DATE			NEXT_BILL_DATE,
			SRV.FIRST_BILL_DATE			FIRST_BILL_DATE,
			SRV.START_DATE_ACTIVE		START_DATE_ACTIVE,
			SRV.END_DATE_ACTIVE			END_DATE_ACTIVE,
			SRV.BILL_ON				BILL_ON,
			SRV.SERVICE_INVENTORY_ITEM_ID	INVENTORY_ITEM_ID,
			PROD.QUANTITY				QUANTITY
		FROM
			CS_CONTRACTS 				CON,
			CS_CP_SERVICES 			SRV,
			CS_CUSTOMER_PRODUCTS 		PROD,
			CS_CONTRACT_COVERAGE_LEVELS 	COVL,
			CS_COVERED_PRODUCTS   		COVP,
			CS_CONTRACT_STATUSES 		STS
 		WHERE
			CON.CONTRACT_ID  = SRV.CONTRACT_ID AND
			STS.ELIGIBLE_FOR_INVOICING = 'Y' AND
		     (
			 ( SRV.FIRST_BILL_DATE <= p_date_range AND
			   SRV.NEXT_BILL_DATE IS NULL
			  ) OR

		 	 (  SRV.NEXT_BILL_DATE <= p_date_range AND
			    SRV.NEXT_BILL_DATE <= SRV.END_DATE_ACTIVE
			  )
			 ) AND
			SRV.CONTRACT_LINE_STATUS_ID = STS.CONTRACT_STATUS_ID AND
			SRV.CP_SERVICE_ID   = COVL.CP_SERVICE_ID AND
			COVL.COVERAGE_LEVEL_ID = COVP.COVERAGE_LEVEL_ID AND
			PROD.CUSTOMER_PRODUCT_ID = COVP.CUSTOMER_PRODUCT_ID
		FOR UPDATE OF CON.CONTRACT_ID;

Line: 307

				Process_And_Insert_Records
				(
					Billed_Until_Date     ,
					eligible_line.Next_Bill_date 		,
					eligible_line.bill_on,
					eligible_line.First_Bill_date 	,
					eligible_line.Start_Date_active 	,
					eligible_line.End_Date_active 	,
					eligible_line.Contract_Id 	,
					eligible_line.CP_Service_Id 	,
					invoice_amount,
					eligible_line.Extended_Price 		,
					eligible_line.duration	,
					eligible_line.inventory_Item_Id 	,
					eligible_line.period 	,
					eligible_line.Billing_Period
				);

Line: 376

	/* Select the sum of the invoice ammount for the service line and the
	maximum billed_until_date from CS_CONTRACTS_BILLING     */

     --FND_FILE.PUT_LINE(FND_FILE.LOG,'CONTRACT_ID ='||
	--	   					  to_char(p_contract_id));

Line: 384

	SELECT SUM(TRX_PRE_TAX_AMOUNT), MAX(BILLED_UNTIL_DATE)
	INTO   p_invoiced_service_amount, p_billed_until_date
	FROM   CS_CONTRACTS_BILLING
	WHERE  CONTRACT_ID = p_contract_Id
	AND    CP_SERVICE_ID  = p_cp_service_id;

Line: 413

PROCEDURE Process_And_Insert_Records
			    (
				P_BILLED_UNTIL_DATE     		IN OUT DATE,
				P_NEXT_BILL_DATE     		IN OUT DATE,
				P_BILL_ON 				IN OUT NUMBER,
				P_FIRST_BILL_DATE     		IN DATE,
				P_START_DATE_ACTIVE     		IN DATE,
				P_END_DATE_ACTIVE       		IN DATE,
				P_CONTRACT_ID	      		IN NUMBER,
				P_CP_SERVICE_ID     		IN NUMBER,
				P_INVOICE_AMOUNT      		IN NUMBER,
				P_EXTENDED_PRICE      		IN NUMBER,
				P_DURATION_QUANTITY    		IN NUMBER,
				P_SERVICE_INVENTORY_ITEM_ID 	IN NUMBER,
				P_UNIT_OF_MEASURE_CODE  		IN VARCHAR2,
				P_BILLING_FREQUENCY_PERIOD  	IN VARCHAR2
 			     ) IS
     v_retcode      			NUMBER ;

Line: 471

	    /* Insert a record in the interface table */
	    /*
         v_retcode := INSERT_CS_CONT_BILL_IFACE
		   			(
						billing_Amount     ,
						Billed_From_Date     ,
						p_Billed_Until_Date     ,
						Transaction_Date,
		    				eligible_line.contract_id,
		    				eligible_line.cp_service_id,
		    				eligible_line.quantity);

Line: 484

          v_retcode := INSERT_CS_CONT_BILL_IFACE
					   (
						billing_Amount     ,
						Billed_From_Date     ,
						p_Billed_Until_Date     ,
						Transaction_Date,
		    				p_contract_id,
		    				p_cp_service_id,
		    				NULL);

Line: 496

	    		/* update CS_CP_SERVICES set the transaction_availability_code*/
	    		/* To 'RESERVED' and update the column Next_bill_date with the*/
	    		/* new Next_Bill_date  */


              	v_retcode := UPDATE_CS_CP_SERVICES
				  		(
		    					p_contract_id,
		    					p_cp_service_id,
							p_Next_Bill_date
						);

Line: 517

 	       END IF;    /* After Update */

Line: 518

     END IF; 	   /* After Insert */

Line: 525

					'Error in Process and Insert record' );

Line: 528

END Process_And_Insert_Records ;

Line: 888

FUNCTION Insert_cs_cont_bill_iface
			(
				P_BILLING_AMOUNT     	IN NUMBER,
				P_billed_from_date     	IN DATE,
				P_billed_until_date     	IN DATE,
				P_transaction_date     	IN DATE,
				P1_CONTRACT_ID     		IN NUMBER,
				P1_CP_SERVICE_ID    		IN NUMBER,
				P_quantity    		IN NUMBER
			)RETURN NUMBER IS
txn_id NUMBER;

Line: 908

     DBMS_TRANSACTION.SAVEPOINT('Insert_Interface');

Line: 911

     SELECT MAX(CP_SERVICE_TRANSACTION_ID)
	INTO   txn_id
	FROM   CS_CP_SERVICE_TRANSACTIONS
	WHERE  CP_SERVICE_ID = p1_cp_service_id
	AND    TRANSACTION_TYPE_CODE  NOT IN ('TERMINATE');

Line: 919

     CS_CONTINTF_PVT.Insert_Row
	(
	  p_api_version		=> 1.0,
	  p_init_msg_list		=> 'T',
	  p_validation_level	=> 100,
	  p_commit			=> 'F',
	  x_return_status        => v_return_status,
	  x_msg_count		     => v_msg_count,
	  x_msg_data			=> v_msg_data,
	  p_cp_service_transaction_id 	=> txn_id,
	  p_cp_service_id 				=> p1_cp_service_id,
	  p_contract_id 				=> p1_contract_id,
	  p_ar_trx_type 				=> 'INV',
	  p_trx_start_date 				=> p_billed_from_date,
	  p_trx_end_date 				=> p_billed_until_date,
	  p_trx_date 					=> p_transaction_date,
	  p_trx_amount 				=> round(p_billing_amount,2),
	  p_reason_code	 			=> 'CONTRACTS',
	  p_reason_comments	 			=> NULL,
	  p_cp_quantity	 			=> NULL,
	  p_concurrent_process_id 		=> NULL,
	  p_created_by 				=> user_id,
	  p_creation_date 				=> sysdate,
	  x_contracts_interface_id 		=> contracts_interface_id,
	  x_object_version_number 		=> object_version_number);

Line: 950

       	FND_FILE.PUT_LINE( FND_FILE.LOG, 'Error Inserting in Interface tbl' );

Line: 957

       	FND_FILE.PUT_LINE( FND_FILE.LOG, 'records Inserted in Interface tbl');

Line: 966

					'Error in Insert_CS_cont_bill_iface ' );

Line: 968

       		FND_FILE.PUT_LINE( FND_FILE.LOG, 'Err in UPdate CS_CP_SERVICES :'
			|| 	 v_msg_data);

Line: 976

FUNCTION Update_CS_CP_Services
			(
				P_CONTRACT_ID     		IN NUMBER,
				P_CP_SERVICE_ID    		IN NUMBER,
				P_Next_Bill_Date     	IN DATE
			) RETURN NUMBER IS

    v_retcode NUMBER := SUCCESS;

Line: 989

	-- This select is required only to lock the record.
     SELECT 'X'
	INTO  dummy
	FROM   CS_CP_SERVICES
	WHERE  contract_id = p_contract_id AND
		  cp_service_id = p_cp_service_id
	FOR UPDATE OF cp_service_id;

Line: 998

	UPDATE CS_CP_SERVICES
--	SET SERVICE_TXN_AVAILABILITY_CODE = 'RESERVED' ,
 	SET NEXT_BILL_DATE = p_Next_Bill_Date
     WHERE CONTRACT_ID = p_Contract_Id AND
		 CP_SERVICE_ID = p_cp_service_id;

Line: 1005

     FND_FILE.PUT_LINE( FND_FILE.LOG, 'CS_CP_SERVICES  Updated' );

Line: 1021

		DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('Insert_Interface');