APPS.PA_IC_INV_DEL SQL Statements

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

Line: 12

PROCEDURE delete_invoices
	     (P_PROJECT_ID   IN  NUMBER,
	    p_mass_delete  in varchar2 DEFAULT 'N',
	    p_unapproved_inv_only IN varchar2 DEFAULT 'N') IS /*New variable added for bug 7026205*/
	     /*new parameter p_unapproved_inv_only for bug 7172117 */

/**
Delete the pending and unreleased Invoices for a project
* Select the pending / unreleased invoices for a project
* If the invoice is a canceled invoice or a credit memo
* then the invoice should be in error
**/

-- This procedure will delete the Invoice and its foreign key references

-- Cursor to select the unreleased draft invoices for a project

/*Added new select variable (DI.APPROVED_DATE) in the below cursor : bug 7172117 */

CURSOR C_DEL_UNAPPROVED_INV IS
        SELECT   DI.DRAFT_INVOICE_NUM,
                 DI.AGREEMENT_ID,
		 DI.APPROVED_DATE
        FROM     PA_DRAFT_INVOICES  DI
        WHERE    DI.PROJECT_ID = P_project_id
        AND      DI.RELEASED_BY_PERSON_ID IS NULL
        AND
          (
           nvl(DI.CANCEL_CREDIT_MEMO_FLAG, 'N') = 'N'
           or
           (
           nvl(DI.CANCEL_CREDIT_MEMO_FLAG, 'N') = 'Y'
           AND DI.generation_error_flag = 'Y'
           )
          )
        FOR UPDATE of DI.Draft_Invoice_Num
        ORDER BY DI.Draft_Invoice_Num  DESC;

Line: 64

   	pa_ic_inv_utils.log_message(' Entering delete_invoices procedure ');

Line: 67

    l_user_id := pa_ic_inv_utils.g_last_update_login;

Line: 77

             Update summary project fundings reduce invoiced_amount by the
             deleting invoice amount;

Line: 79

             Delete from pa_distribution_warnings  ;

Line: 80

             Delete from pa_draft_invoice_items
             Delete from pa_draft_invoices
      // remove the reference to the Invoice from Invoice Details
         End Loop;

Line: 100

        pa_ic_inv_utils.update_spf (l_draft_inv_num,
                                    l_agreement_id,
                                    p_project_id,
                                    'DELETE' );

Line: 105

        delete from pa_distribution_warnings
         where project_id = P_PROJECT_ID
           and draft_invoice_num = l_draft_inv_num;

Line: 110

   	pa_ic_inv_utils.log_message('pa_distribution_warnings rows deleted = ' || SQL%rowcount);

Line: 113

	   if p_mass_delete = 'Y' then  /*Added for bug 7026205*/
		insert_dist_warnings(p_project_id,
		                     l_draft_inv_num);

Line: 118

        delete from pa_draft_invoice_items
         where project_id = P_PROJECT_ID
           and draft_invoice_num = l_draft_inv_num;

Line: 123

     pa_ic_inv_utils.log_message('pa_draft_invoice_items rows deleted = ' || SQL%rowcount);

Line: 126

        delete from pa_draft_invoices
         where project_id = P_PROJECT_ID
           and draft_invoice_num = l_draft_inv_num;

Line: 131

       pa_ic_inv_utils.log_message('pa_draft_invoices rows deleted = ' || SQL%rowcount);

Line: 136

        Update pa_expenditure_items_all
           set cc_ic_processed_code = 'N',
	     denom_transfer_price   = NULL,
	     denom_tp_currency_code = NULL,
         /* Added following column updates to NULL to clear these columns when
            deleting the invoice for bug 6132313. acct_tp_rate_date = NULL added for bug 5276946*/
             acct_transfer_price = NULL,
             tp_base_amount = NULL,
	     tp_ind_compiled_set_id = NULL,
	     tp_bill_rate = NULL,
	     tp_bill_markup_percentage = NULL,
	     tp_schedule_line_percentage = NULL,
	     tp_rule_percentage = NULL,
	     tp_job_id = NULL,
             projfunc_transfer_price = NULL,
             project_transfer_price = NULL,
             projacct_transfer_price = NULL,
             Acct_tp_rate_type = NULL,
             Acct_tp_rate_date = NULL,
             Acct_tp_exchange_rate = NULL,
             project_tp_rate_type = NULL,
             project_tp_rate_date = NULL,
             project_tp_exchange_rate = NULL,
             projfunc_tp_rate_type = NULL,
             projfunc_tp_rate_date = NULL,
             projfunc_tp_exchange_rate = NULL
         where expenditure_item_id in
             ( select expenditure_item_id
                 from PA_DRAFT_INVOICE_DETAILS
                 Where Project_id = P_project_id
                 and Draft_Invoice_Num = L_draft_inv_num
             )
	   and (adjusted_expenditure_item_id is null /*Clause added for Bug 6899120*/
	        or (adjusted_expenditure_item_id is not null and
		    adjusted_expenditure_item_id in
        ( select expenditure_item_id
                 from PA_DRAFT_INVOICE_DETAILS
                 Where Project_id = P_project_id
                 and Draft_Invoice_Num = L_draft_inv_num
                   )
		    )
             );

Line: 180

      pa_ic_inv_utils.log_message('pa_expenditure_items_all rows updated = ' || SQL%rowcount);

Line: 184

   /* Added following code to update related item when deleting invoice associated
   with the original transaction. Bug 6651747. */

        Update pa_expenditure_items_all
           set cc_ic_processed_code = 'N',
	     denom_transfer_price   = NULL,
	     denom_tp_currency_code = NULl,
             acct_transfer_price = NULL,
             tp_base_amount = NULL,
	     tp_ind_compiled_set_id = NULL,
	     tp_bill_rate = NULL,
	     tp_bill_markup_percentage = NULL,
	     tp_schedule_line_percentage = NULL,
	     tp_rule_percentage = NULL,
	     tp_job_id = NULL,
             projfunc_transfer_price = NULL,
             project_transfer_price = NULL,
             projacct_transfer_price = NULL,
             Acct_tp_rate_type = NULL,
             Acct_tp_rate_date = NULL,
             Acct_tp_exchange_rate = NULL,
             project_tp_rate_type = NULL,
             project_tp_rate_date = NULL,
             project_tp_exchange_rate = NULL,
             projfunc_tp_rate_type = NULL,
             projfunc_tp_rate_date = NULL,
             projfunc_tp_exchange_rate = NULL
         where source_expenditure_item_id in
             ( select expenditure_item_id
                 from PA_DRAFT_INVOICE_DETAILS
                 Where Project_id = P_project_id
                 and Draft_Invoice_Num = L_draft_inv_num
             );

Line: 219

      pa_ic_inv_utils.log_message('Related Items: pa_expenditure_items_all rows updated = ' || SQL%rowcount);

Line: 224

        Update  PA_DRAFT_INVOICE_DETAILS
           Set  draft_invoice_num = NULL,
                draft_invoice_line_num = NULL,
                last_update_date  = SYSDATE,
                last_update_login = l_user_id,
                request_id = l_request_id,
                Invoiced_Flag = 'N'
           Where Project_id = P_project_id
             and Draft_Invoice_Num = L_draft_inv_num;

Line: 235

      pa_ic_inv_utils.log_message('PA_DRAFT_INVOICE_DETAILS rows updated = ' || SQL%rowcount);

Line: 247

   	pa_ic_inv_utils.log_message(' Leaving delete_invoices procedure ');

Line: 254

END delete_invoices;

Line: 257

 PROCEDURE insert_dist_warnings
           (P_PROJECT_ID IN NUMBER,
	    P_DRAFT_INVOICE_NUM IN NUMBER)

 IS

  BEGIN

    INSERT INTO PA_DISTRIBUTION_WARNINGS
	(
	   PROJECT_ID,
	   DRAFT_INVOICE_NUM,
	   LAST_UPDATE_DATE,
	   LAST_UPDATED_BY,
	   CREATION_DATE,
	   CREATED_BY,
	   REQUEST_ID,
	   PROGRAM_APPLICATION_ID,
	   PROGRAM_ID,
	   PROGRAM_UPDATE_DATE,
	   AGREEMENT_ID,
	   WARNING_MESSAGE_CODE,
	   WARNING_MESSAGE
	)
	(
	   Select dia.project_id,
		  DRAFT_INVOICE_NUM,
		  sysdate,
		  PA_IC_INV_UTILS.G_LAST_UPDATED_BY,
		  sysdate,
		  PA_IC_INV_UTILS.G_CREATED_BY,
		  PA_IC_INV_UTILS.G_REQUEST_ID,
		  PA_IC_INV_UTILS.G_PROGRAM_APPLICATION_ID,
		  PA_IC_INV_UTILS.G_PROGRAM_ID,
		  dia.invoice_Date,
		  dia.agreement_id,
		  dia.INV_CURRENCY_CODE,
		  (SELECT SUM(INV_AMOUNT)
		   FROM PA_DRAFT_INVOICE_ITEMS
		   WHERE PROJECT_ID = P_PROJECT_ID
		   AND DRAFT_INVOICE_NUM = P_DRAFT_INVOICE_NUM)
	   FROM PA_DRAFT_INVOICES DIA
	   WHERE PROJECT_ID = P_PROJECT_ID
	   AND   DRAFT_INVOICE_NUM = P_DRAFT_INVOICE_NUM
	 );

Line: 307

END insert_dist_warnings;