APPS.PA_AGREEMENT_CORE SQL Statements

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

Line: 20

     SELECT 'Y' into multi_flag
     FROM  PA_PROJECT_CUSTOMERS
     WHERE PROJECT_ID = p_project_id
     AND   CUSTOMER_BILL_SPLIT NOT IN (100, 0)
     HAVING COUNT(CUSTOMER_ID) > 1;

Line: 83

        select 1 into cust_exists from dual where exists (
                Select customer_name, customer_id, customer_number
                from pa_customers_v where status = 'A' and
                customer_id = p_customer_id);

Line: 111

        Select 1 into type_exists
        From Dual
        Where Exists (
                select  0
                from    pa_agreement_types atp, ra_terms rt
                where   atp.term_id = rt.term_id(+)
                and     trunc(sysdate) between trunc(atp.start_date_active)
                and     trunc(nvl(atp.end_date_active,sysdate))
    		and     atp.agreement_type = p_agreement_type);

Line: 153

          select 1 into invoice_exists
          from pa_draft_invoices_all
          where agreement_id = p_agreement_id
          and rownum=1;

Line: 181

        Select 1
	into Term_exists
	From Dual
	Where Exists (
                select 0
                from ra_terms
                where trunc(sysdate) between start_date_active
                and nvl(end_date_active, trunc(sysdate))
    		and term_id = p_term_id);

Line: 213

        select 1 into Person_Id_Exists
	from Dual Where Exists (
        select 0 from pa_employees
	where  person_id = p_owned_by_person_id);

Line: 246

        select 1 into not_unique
        from pa_agreements p
        where p.customer_id = p_customer_id
        and p.agreement_num = p_agreement_num
        and p.agreement_type = p_agreement_type;

Line: 282

SELECT 1
FROM PA_AGREEMENTS_ALL A
WHERE A.pm_agreement_reference = p_agreement_reference;

Line: 318

SELECT 1
FROM PA_AGREEMENTS_ALL A
WHERE A.agreement_id = p_agreement_id;

Line: 355

SELECT 1
FROM PA_PROJECT_FUNDINGS F
WHERE	F.pm_funding_reference = p_funding_reference
AND	F.agreement_id = p_agreement_id ;

Line: 393

SELECT 1
FROM PA_PROJECT_FUNDINGS F
WHERE	F.project_funding_id = p_funding_id
AND	F.agreement_id = p_agreement_id ;

Line: 434

                Select  nvl(sum(total_baselined_amount),0),
                        nvl(sum(total_unbaselined_amount),0)
                into    l_tot_baselined_amt,
                        l_tot_unbaselined_amt
                From    Pa_Summary_Project_Fundings
                where   Agreement_id = p_Agreement_id;

Line: 461

FUNCTION check_add_update
	( 	p_funding_id       		IN 	NUMBER
		,p_funding_reference		IN 	VARCHAR2
 	)
RETURN VARCHAR2
IS

CURSOR c1
IS
SELECT f.project_funding_id
FROM PA_PROJECT_FUNDINGS f
WHERE f.project_funding_id = p_funding_id;

Line: 476

SELECT f.project_funding_id
FROM PA_PROJECT_FUNDINGS f
WHERE f.pm_funding_reference = p_funding_reference;

Line: 504

END check_add_update;

Line: 527

SELECT f.agreement_id
FROM PA_PROJECT_FUNDINGS f
WHERE f.project_funding_id = p_funding_id;

Line: 533

SELECT f.agreement_id
FROM PA_PROJECT_FUNDINGS f
WHERE f.pm_funding_reference = p_funding_reference;

Line: 599

SELECT f.project_id
FROM PA_PROJECT_FUNDINGS f
WHERE f.project_funding_id = p_funding_id;

Line: 605

SELECT f.project_id
FROM PA_PROJECT_FUNDINGS f
WHERE f.pm_funding_reference = p_funding_reference;

Line: 660

SELECT f.task_id
FROM PA_PROJECT_FUNDINGS f
WHERE f.project_funding_id = p_funding_id;

Line: 666

SELECT f.task_id
FROM PA_PROJECT_FUNDINGS f
WHERE f.pm_funding_reference = p_funding_reference;

Line: 719

SELECT A.customer_id
FROM PA_AGREEMENTS_ALL A
WHERE A.agreement_id  = (SELECT f.agreement_id
			FROM PA_PROJECT_FUNDINGS f
			WHERE f.project_funding_id = p_funding_id);

Line: 727

SELECT A.customer_id
FROM PA_AGREEMENTS_ALL A
WHERE A.agreement_id  = (SELECT f.agreement_id
			FROM PA_PROJECT_FUNDINGS f
			WHERE f.pm_funding_reference = p_funding_reference);

Line: 767

| Description       : This procedure will insert one row in to PA_AGREEMENTS_ALL
| History           :
|    15-MAY-2000     Created         Nikhil Mishra.
|    07-SEP-2001     Modified        Srividya
|      Added all new columns used in MCB2
+============================================================================*/
 PROCEDURE create_agreement(
	   p_Rowid                   IN OUT NOCOPY VARCHAR2,/*File.sql.39*/
           p_Agreement_Id                   IN OUT NOCOPY NUMBER,/*File.sql.39*/
           p_Customer_Id                    IN NUMBER,
           p_Agreement_Num                  IN VARCHAR2,
           p_Agreement_Type                 IN VARCHAR2,
           p_Last_Update_Date               IN DATE,
           p_Last_Updated_By                IN NUMBER,
           p_Creation_Date                  IN DATE,
           p_Created_By                     IN NUMBER,
           p_Last_Update_Login              IN NUMBER,
           p_Owned_By_Person_Id             IN NUMBER,
           p_Term_Id                        IN NUMBER,
           p_Revenue_Limit_Flag             IN VARCHAR2,
           p_Amount                         IN NUMBER,
           p_Description                    IN VARCHAR2,
           p_Expiration_Date                IN DATE,
           p_Attribute_Category             IN VARCHAR2,
           p_Attribute1                     IN VARCHAR2,
           p_Attribute2                     IN VARCHAR2,
           p_Attribute3                     IN VARCHAR2,
           p_Attribute4                     IN VARCHAR2,
           p_Attribute5                     IN VARCHAR2,
           p_Attribute6                     IN VARCHAR2,
           p_Attribute7                     IN VARCHAR2,
           p_Attribute8                     IN VARCHAR2,
           p_Attribute9                     IN VARCHAR2,
           p_Attribute10                    IN VARCHAR2,
           p_Template_Flag                  IN VARCHAR2,
           p_pm_agreement_reference         IN VARCHAR2,
           p_pm_product_code         	    IN VARCHAR2,
	   p_agreement_currency_code	    IN VARCHAR2 DEFAULT NULL,
	   p_owning_organization_id	    IN NUMBER	DEFAULT NULL,
	   p_invoice_limit_flag		    IN VARCHAR2 DEFAULT NULL,
/*Federal*/
           p_customer_order_number          IN VARCHAR2 DEFAULT NULL,
           p_advance_required               IN VARCHAR2 DEFAULT NULL,
           p_start_date                     IN DATE     DEFAULT NULL,
           p_billing_sequence               IN NUMBER   DEFAULT NULL,
           p_line_of_account                IN VARCHAR2 DEFAULT NULL,
           p_Attribute11                    IN VARCHAR2 DEFAULT NULL,
           p_Attribute12                    IN VARCHAR2 DEFAULT NULL,
           p_Attribute13                    IN VARCHAR2 DEFAULT NULL,
           p_Attribute14                    IN VARCHAR2 DEFAULT NULL,
           p_Attribute15                    IN VARCHAR2 DEFAULT NULL,
           p_Attribute16                    IN VARCHAR2 DEFAULT NULL,
           p_Attribute17                    IN VARCHAR2 DEFAULT NULL,
           p_Attribute18                    IN VARCHAR2 DEFAULT NULL,
           p_Attribute19                    IN VARCHAR2 DEFAULT NULL,
           p_Attribute20                    IN VARCHAR2 DEFAULT NULL,
           p_Attribute21                    IN VARCHAR2 DEFAULT NULL,
           p_Attribute22                    IN VARCHAR2 DEFAULT NULL,
           p_Attribute23                    IN VARCHAR2 DEFAULT NULL,
           p_Attribute24                    IN VARCHAR2 DEFAULT NULL,
           p_Attribute25                    IN VARCHAR2 DEFAULT NULL
                      )
  is

	l_agreement_currency_code	VARCHAR2(30);

Line: 855

    pa_agreements_pkg.insert_row(
	x_rowid				=>	 p_rowid,
	x_agreement_id			=>	 p_agreement_id,
	x_customer_id			=>	 p_customer_id,
	x_agreement_num			=>	 p_agreement_num,
	x_agreement_type		=>	 p_agreement_type,
	x_last_update_date		=>	 p_last_update_date,
	x_last_updated_by		=>	 p_last_updated_by,
	x_creation_date			=>	 p_creation_date,
	x_created_by			=>	 p_created_by,
	x_last_update_login		=>	 p_last_update_login,
	x_owned_by_person_id		=>	 p_owned_by_person_id,
	x_term_id			=>	 p_term_id,
	x_revenue_limit_flag		=>	 p_revenue_limit_flag,
	x_amount			=>	 p_amount,
	x_description			=>	 p_description,
	x_expiration_date		=>	 p_expiration_date,
	x_attribute_category		=>	 p_attribute_category,
	x_attribute1			=>	 p_attribute1,
	x_attribute2			=>	 p_attribute2,
	x_attribute3			=>	 p_attribute3,
	x_attribute4			=>	 p_attribute4,
	x_attribute5			=>	 p_attribute5,
	x_attribute6			=>	 p_attribute6,
	x_attribute7			=>	 p_attribute7,
	x_attribute8			=>	 p_attribute8,
	x_attribute9			=>	 p_attribute9,
	x_attribute10			=>	 p_attribute10,
	x_template_flag			=>	 p_template_flag,
	x_pm_agreement_reference	=>	 p_pm_agreement_reference,
	x_pm_product_code		=>	 p_pm_product_code,
	x_owning_organization_id	=>	 p_owning_organization_id,
	x_agreement_currency_code	=>	 l_agreement_currency_code,
	x_invoice_limit_flag		=>	 l_invoice_limit_flag,
	x_org_id                        =>       l_org_id,
/*Federal*/
        x_customer_order_number         =>       p_customer_order_number,
        x_advance_required              =>       p_advance_required,
        x_start_date                    =>       p_start_date,
        x_billing_sequence              =>       p_billing_sequence,
        x_line_of_account               =>       p_line_of_account,
        x_attribute11                   =>       p_attribute11,
        x_attribute12                   =>       p_attribute12,
        x_attribute13                   =>       p_attribute13,
        x_attribute14                   =>       p_attribute14,
        x_attribute15                   =>       p_attribute15,
        x_attribute16                   =>       p_attribute16,
        x_attribute17                   =>       p_attribute17,
        x_attribute18                   =>       p_attribute18,
        x_attribute19                   =>       p_attribute19,
        x_attribute20                   =>       p_attribute20,
        x_attribute21                   =>       p_attribute21,
        x_attribute22                   =>       p_attribute22,
        x_attribute23                   =>       p_attribute23,
        x_attribute24                   =>       p_attribute24,
        x_attribute25                   =>       p_attribute25);/* Shared Services*/

Line: 920

| Name              : update_agreement
| Type              : PROCEDURE
| Description       : This procedure will update one row in to PA_AGREEMENTS_ALL
| History           :
|    15-MAY-2000     Created         Nikhil Mishra.
|    07-SEP-2001     Modified        Srividya
|      Added all new columns used in MCB2
+============================================================================*/
  PROCEDURE update_agreement(
           p_Agreement_Id		IN 	NUMBER,
           p_Customer_Id		IN	NUMBER,
           p_Agreement_Num		IN	VARCHAR2,
           p_Agreement_Type		IN	VARCHAR2,
           p_Last_Update_Date		IN	DATE,
           p_Last_Updated_By		IN	NUMBER,
           p_Last_Update_Login		IN	NUMBER,
           p_Owned_By_Person_Id		IN	NUMBER,
           p_Term_Id			IN	NUMBER,
           p_Revenue_Limit_Flag		IN	VARCHAR2,
           p_Amount			IN	NUMBER,
           p_Description		IN	VARCHAR2,
           p_Expiration_Date		IN	DATE,
           p_Attribute_Category		IN	VARCHAR2,
           p_Attribute1			IN	VARCHAR2,
           p_Attribute2			IN	VARCHAR2,
           p_Attribute3			IN	VARCHAR2,
           p_Attribute4			IN	VARCHAR2,
           p_Attribute5			IN	VARCHAR2,
           p_Attribute6			IN	VARCHAR2,
           p_Attribute7			IN	VARCHAR2,
           p_Attribute8			IN	VARCHAR2,
           p_Attribute9			IN	VARCHAR2,
           p_Attribute10		IN	VARCHAR2,
           p_Template_Flag		IN	VARCHAR2,
           p_pm_agreement_reference	IN	VARCHAR2,
           p_pm_product_code		IN	VARCHAR2,
	   p_agreement_currency_code	IN	VARCHAR2 DEFAULT NULL,
	   p_owning_organization_id	IN	NUMBER	DEFAULT NULL,
	   p_invoice_limit_flag		IN	VARCHAR2 DEFAULT NULL,
/*Federal*/
           p_customer_order_number          IN VARCHAR2 DEFAULT NULL,
           p_advance_required               IN VARCHAR2 DEFAULT NULL,
           p_start_date                     IN DATE     DEFAULT NULL,
           p_billing_sequence               IN NUMBER   DEFAULT NULL,
           p_line_of_account                IN VARCHAR2 DEFAULT NULL,
           p_Attribute11                    IN VARCHAR2 DEFAULT NULL,
           p_Attribute12                    IN VARCHAR2 DEFAULT NULL,
           p_Attribute13                    IN VARCHAR2 DEFAULT NULL,
           p_Attribute14                    IN VARCHAR2 DEFAULT NULL,
           p_Attribute15                    IN VARCHAR2 DEFAULT NULL,
           p_Attribute16                    IN VARCHAR2 DEFAULT NULL,
           p_Attribute17                    IN VARCHAR2 DEFAULT NULL,
           p_Attribute18                    IN VARCHAR2 DEFAULT NULL,
           p_Attribute19                    IN VARCHAR2 DEFAULT NULL,
           p_Attribute20                    IN VARCHAR2 DEFAULT NULL,
           p_Attribute21                    IN VARCHAR2 DEFAULT NULL,
           p_Attribute22                    IN VARCHAR2 DEFAULT NULL,
           p_Attribute23                    IN VARCHAR2 DEFAULT NULL,
           p_Attribute24                    IN VARCHAR2 DEFAULT NULL,
           p_Attribute25                    IN VARCHAR2 DEFAULT NULL)
  is
     CURSOR C IS
      SELECT rowid,agreement_currency_code,
	     owning_organization_id, invoice_limit_flag
      FROM PA_AGREEMENTS_ALL
      WHERE agreement_id = p_agreement_id;

Line: 993

 	-- dbms_output.put_line('Calling: pa_agreement_core.update_agreement');

Line: 1008

        pa_agreements_pkg.update_row(
	x_rowid				=>	 agr_rec.rowid,
	x_agreement_id			=>	 p_agreement_id,
	x_customer_id			=>	 p_customer_id,
	x_agreement_num			=>	 p_agreement_num,
	x_agreement_type		=>	 p_agreement_type,
	x_last_update_date		=>	 p_last_update_date,
	x_last_updated_by		=>	 p_last_updated_by,
	x_last_update_login		=>	 p_last_update_login,
	x_owned_by_person_id		=>	 p_owned_by_person_id,
	x_term_id			=>	 p_term_id,
	x_revenue_limit_flag		=>	 p_revenue_limit_flag,
	x_amount			=>	 p_amount,
	x_description			=>	 p_description,
	x_expiration_date		=>	 p_expiration_date,
	x_attribute_category		=>	 p_attribute_category,
	x_attribute1			=>	 p_attribute1,
	x_attribute2			=>	 p_attribute2,
	x_attribute3			=>	 p_attribute3,
	x_attribute4			=>	 p_attribute4,
	x_attribute5			=>	 p_attribute5,
	x_attribute6			=>	 p_attribute6,
	x_attribute7			=>	 p_attribute7,
	x_attribute8			=>	 p_attribute8,
	x_attribute9			=>	 p_attribute9,
	x_attribute10			=>	 p_attribute10,
	x_template_flag			=>	 p_template_flag,
	x_pm_agreement_reference	=>	 p_pm_agreement_reference,
	x_pm_product_code		=>	 p_pm_product_code,
	x_owning_organization_id	=>	 p_owning_organization_id,
	x_agreement_currency_code	=>	 l_agreement_currency_code,
	x_invoice_limit_flag		=>	 l_invoice_limit_flag,
/*Federal*/
        x_customer_order_number         =>       p_customer_order_number,
        x_advance_required              =>       p_advance_required,
        x_start_date                    =>       p_start_date,
        x_billing_sequence              =>       p_billing_sequence,
        x_line_of_account               =>       p_line_of_account,
        x_attribute11                   =>       p_attribute11,
        x_attribute12                   =>       p_attribute12,
        x_attribute13                   =>       p_attribute13,
        x_attribute14                   =>       p_attribute14,
        x_attribute15                   =>       p_attribute15,
        x_attribute16                   =>       p_attribute16,
        x_attribute17                   =>       p_attribute17,
        x_attribute18                   =>       p_attribute18,
        x_attribute19                   =>       p_attribute19,
        x_attribute20                   =>       p_attribute20,
        x_attribute21                   =>       p_attribute21,
        x_attribute22                   =>       p_attribute22,
        x_attribute23                   =>       p_attribute23,
        x_attribute24                   =>       p_attribute24,
        x_attribute25                   =>       p_attribute25);

Line: 1063

  END  update_agreement;

Line: 1066

| Name              : delete_agreement
| Type              : PROCEDURE
| Description       : This procedure will delete one row from PA_AGREEMENTS_ALL
| History           :
|    15-MAY-2000     Created         Nikhil Mishra.
+============================================================================*/
  procedure delete_agreement(p_agreement_id	IN NUMBER)
  is
      CURSOR C IS
      SELECT rowid
      FROM PA_AGREEMENTS
      WHERE agreement_id = p_agreement_id;

Line: 1083

          pa_agreements_pkg.delete_row(agr_row_id);

Line: 1086

  END  delete_agreement;

Line: 1102

      SELECT  rowid,
	agreement_id,
	customer_id,
	agreement_num,
	agreement_type,
	owned_by_person_id,
	term_id,
	revenue_limit_flag ,
	amount,
	description,
	expiration_date,
	attribute_category,
	attribute1,
	attribute2,
	attribute3,
	attribute4,
	attribute5,
	attribute6,
	attribute7,
	attribute8 ,
	attribute9,
	attribute10,
	template_flag,
	pm_agreement_reference,
	pm_product_code,
	owning_organization_id,
	agreement_currency_code,
	invoice_limit_flag,
/*Federal*/
        customer_order_number,
        advance_required,
        start_date,
        billing_sequence,
        line_of_account,
	attribute11,
	attribute12,
	attribute13,
	attribute14,
	attribute15,
	attribute16,
	attribute17,
	attribute18 ,
	attribute19,
	attribute20,
	attribute21,
	attribute22,
	attribute23,
	attribute24,
	attribute25
      FROM PA_AGREEMENTS
      WHERE agreement_id = p_agreement_id;

Line: 1218

|      changed the select to check only for accrued amount
+============================================================================*/

FUNCTION check_revenue_limit
	( p_agreement_id        		IN 	NUMBER
 	)
RETURN VARCHAR2
IS
	l_check_limit NUMBER;

Line: 1230

	SELECT MIN(SIGN((f1.total_baselined_amount+f1.total_unbaselined_amount)
               -GREATEST(NVL(f1.total_accrued_amount,0), NVL(f1.total_billed_amount, 0))))
	INTO	check_limit
	FROM	pa_summary_project_fundings f1
	WHERE	f1.agreement_id = p_agreement_id;

Line: 1237

	SELECT MIN(SIGN((f1.total_baselined_amount+f1.total_unbaselined_amount)
               - NVL(f1.total_accrued_amount, 0)))
        */
	SELECT MIN(SIGN((f1.projfunc_baselined_amount+f1.projfunc_unbaselined_amount)
               - NVL(f1.projfunc_accrued_amount, 0)))
	INTO	l_check_limit
	FROM	pa_summary_project_fundings f1
	WHERE	f1.agreement_id = p_agreement_id;

Line: 1280

	SELECT  budget_type_code
	INTO	budget_type_code
	FROM	pa_project_fundings f1
	WHERE	f1.project_funding_id = p_funding_id;

Line: 1306

	SELECT agreement_currency_code INTO l_currency_code
	FROM pa_agreements_all
	WHERE agreement_id = p_agreement_id;

Line: 1328

	SELECT 'Y' INTO l_valid_flag
	FROM pa_organizations_project_v
	WHERE organization_id = p_owning_organization_id
	AND SYSDATE BETWEEN DECODE (date_from, NULL, SYSDATE, date_from)
		    AND DECODE(date_to, NULL, SYSDATE,date_to);

Line: 1368

	SELECT 'Y' INTO l_valid_flag
	FROM fnd_currencies
	WHERE currency_code = p_agreement_currency_code
	AND SYSDATE BETWEEN DECODE (start_date_active, NULL, SYSDATE,
                                    start_date_active)
		    AND DECODE(end_date_active, NULL, SYSDATE,end_date_active);

Line: 1420

	SELECT MIN(SIGN((f1.total_baselined_amount+f1.total_unbaselined_amount)
               - NVL(f1.total_billed_amount, 0)))
        */
	SELECT MIN(SIGN((f1.invproc_baselined_amount+f1.invproc_unbaselined_amount)
               - NVL(f1.invproc_billed_amount, 0)))
	INTO	l_check_limit
	FROM	pa_summary_project_fundings f1
	WHERE	f1.agreement_id = p_agreement_id;