APPS.PA_IC_INV_UTILS SQL Statements

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

Line: 24

        P_LAST_UPDATE_LOGIN      NUMBER,
        P_REQUEST_ID             NUMBER,
        P_PROGRAM_APPLICATION_ID NUMBER,
        P_PROGRAM_ID             NUMBER,
        P_LAST_UPDATED_BY        NUMBER,
        P_CREATED_BY             NUMBER,
        P_DEBUG_MODE             VARCHAR2,
        P_SOB                    NUMBER,
        P_ORG                    NUMBER,
        P_FUNC_CURR              VARCHAR2
) IS


/* R12 :  Ledger Architecture Changes : The table gl_mc_reporting_options will  obsoleted, replace with
   new table gl_alc_ledger_rships_v and corresponding columns
   Also remove the date validation, If we add application id and relationship_enabled_flag check then no need to
   check the date. */

  /* cursor c_reporting_sob(p_sob_id in number,p_org_id in number) is
         select reporting_set_of_books_id ,
                reporting_currency_code
           from gl_mc_reporting_options
           where primary_set_of_books_id = p_sob_id
            and org_id = p_org_id
            and application_id = 275
            and nvl(enabled_flag,'N')='Y'
            and TRUNC(sysdate) between
                TRUNC(start_date) and TRUNC(nvl(end_date,sysdate)); */    /* BUG# 3118592 */

Line: 55

         select ledger_id  reporting_set_of_books_id ,
                currency_code reporting_currency_code
           from gl_alc_ledger_rships_v
          where source_ledger_id  = p_sob_id
            and (org_id = -99 OR org_id = p_org_id)
            and application_id = 275
            and relationship_enabled_flag ='Y';

Line: 67

G_LAST_UPDATE_LOGIN := P_LAST_UPDATE_LOGIN;

Line: 71

G_LAST_UPDATED_BY := P_LAST_UPDATED_BY;

Line: 115

SELECT  nvl(MAX(draft_invoice_num),0) + 1
FROM    pa_draft_invoices DI
WHERE   DI.project_id = P_project_id;

Line: 120

SELECT  DI.draft_invoice_num
FROM    pa_draft_invoices DI
WHERE   DI.Project_ID = P_project_id
AND     DI.draft_invoice_num
        =
        ( select min(draft_invoice_num)
            from pa_draft_invoices
           where project_id = P_project_id
           AND     request_id = P_request_id
           AND     generation_error_flag = 'Y'
        );

Line: 182

    l_user_id := pa_ic_inv_utils.g_last_update_login;

Line: 195

	  Update pa_draft_invoices
             set draft_invoice_num = l_new_invoice_num,
		 last_update_date  = SYSDATE,
		 last_update_login = l_user_id
	   where project_id = P_Project_Id
	     and draft_invoice_num = l_err_invoice_num;

Line: 202

	  Update pa_draft_invoice_items
             set draft_invoice_num = l_new_invoice_num,
		 last_update_date  = SYSDATE,
		 last_update_login = l_user_id
	   where project_id = P_Project_Id
	     and draft_invoice_num = l_err_invoice_num;

Line: 209

	  Update pa_draft_invoice_details
             set draft_invoice_num = l_new_invoice_num,
		 last_update_date  = SYSDATE,
		 last_update_login = l_user_id
	   where project_id = P_Project_Id
	     and draft_invoice_num = l_err_invoice_num;

Line: 216

	  Update pa_distribution_warnings
             set draft_invoice_num = l_new_invoice_num,
		 last_update_date  = SYSDATE,
		 last_update_login = l_user_id
	   where project_id = P_Project_Id
	     and draft_invoice_num = l_err_invoice_num;

Line: 267

PROCEDURE  Update_SPF
	   ( P_DRAFT_INVOICE_NUM IN NUMBER ,
	     P_AGREEMENT_ID      IN NUMBER,
             P_PROJECT_ID        IN NUMBER,
             P_INVOICE_MODE      IN VARCHAR2) AS

   l_total_billed_amount  NUMBER := 0;

Line: 281

    l_user_id := pa_ic_inv_utils.g_last_update_login;

Line: 286

   pa_debug.g_err_stage := ' In Update_SPF ';

Line: 288

   	pa_ic_inv_utils.log_message('Update_SPF: ' || pa_debug.g_err_stage);

Line: 291

    SELECT SUM(amount)
    INTO   l_total_billed_amount
    FROM   pa_draft_invoice_items
    WHERE  draft_invoice_num = p_draft_invoice_num
    AND    project_id = p_project_id ;

Line: 297

    Update pa_summary_project_fundings
       set total_accrued_amount = NVL(total_accrued_amount,0) +
    decode(P_INVOICE_MODE,'DELETE',(-1)*l_total_billed_amount,
                                    l_total_billed_amount),
           total_billed_amount = NVL(total_billed_amount,0) +
    decode(P_INVOICE_MODE,'DELETE',(-1)*l_total_billed_amount,
                                    l_total_billed_amount),
           last_update_date = SYSDATE,
	   last_update_login = l_user_id,
	   request_id        = l_request_id,
           program_application_id = l_program_application_id,
	   program_id        = l_program_id
      where project_id = P_project_id
	and agreement_id = P_agreement_id;

Line: 313

        pa_ic_inv_utils.log_message ('Updated SPF rows = '||SQL%rowcount);

Line: 314

   	pa_ic_inv_utils.log_message ('Update_SPF: ' || 'Updating SPF with amount =  ' ||
                                    l_total_billed_amount);

Line: 322

END Update_SPF;

Line: 352

    l_user_id := pa_ic_inv_utils.g_last_update_login;

Line: 359

		SELECT	COUNT(*)
		INTO	l_cnt
		FROM	pa_distribution_warnings
		WHERE	project_id = p_project_id
		AND	draft_invoice_num = P_DRAFT_INVOICE_NUM;

Line: 368

		/*pa_ic_inv_del.delete_invoices(p_project_id);*/

Line: 370

		delete pa_draft_invoices_all
		where project_id = p_project_id
		AND	draft_invoice_num = P_DRAFT_INVOICE_NUM;

Line: 383

		     select meaning
		     into l_rejection_reason
		     from pa_lookups
		     where lookup_type = P_REJN_LOOKUP_TYPE
		     and lookup_code = P_REJN_LOOKUP_CODE;

Line: 396

		     Update pa_draft_invoices
		       set generation_error_flag = 'Y',
			  last_update_date = SYSDATE,
			  last_update_login = l_user_id,
			  request_id        = l_request_id,
			  transfer_rejection_reason = l_rejection_reason
		     where project_id = P_PROJECT_ID
		     and draft_invoice_num = P_DRAFT_INVOICE_NUM;

Line: 406

		     pa_ic_inv_utils.log_message('Rows updated in DI = '||SQL%rowcount);

Line: 409

		     INSERT INTO PA_DISTRIBUTION_WARNINGS
		     (
			DRAFT_INVOICE_NUM, PROJECT_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,
			CREATION_DATE, CREATED_BY, REQUEST_ID, PROGRAM_APPLICATION_ID,
			PROGRAM_ID, PROGRAM_UPDATE_DATE, WARNING_MESSAGE
		     )
		     VALUES
			(
			 P_draft_invoice_num, P_project_id, SYSDATE, l_user_id,
			 SYSDATE, l_user_id, l_request_id, l_program_application_id,
			 l_program_id, SYSDATE, l_rejection_reason
			);

Line: 423

		     pa_ic_inv_utils.log_message('Rows Inserted in pa_distribution_warnings = '||SQL%rowcount);

Line: 453

   select  expenditure_item_id expenditure_item_id,
           denom_currency_code denom_tp_currency_code,
           denom_bill_amount   denom_transfer_price,
	   acct_rate_type      acct_tp_rate_type,
	   acct_rate_date      acct_tp_rate_date,
	   acct_exchange_rate  acct_tp_exchange_rate,
	   bill_amount         acct_transfer_price,
           markup_calc_base_code cc_markup_base_code,
	   base_amount         tp_base_amount,
           ind_compiled_set_id tp_ind_compiled_set_id,
           bill_rate           tp_bill_rate,
           bill_markup_percentage tp_bill_markup_percentage,
	   schedule_line_percentage tp_schedule_line_percentage
     from  pa_draft_invoice_details did
     where did.project_id = P_PROJECT_ID
       and did.request_id = P_REQUEST_ID
       and did.draft_invoice_num = P_DRAFT_INVOICE_NUM
       and did.line_num = ( select max(did1.line_num)
                              from pa_draft_invoice_details did1
			      where did1.expenditure_item_id =
                                      did.expenditure_item_id)
   ;

Line: 481

    l_user_id := pa_ic_inv_utils.g_last_update_login;

Line: 497

    Update pa_expenditure_items_all ei
       set ei.cc_ic_processed_code = decode(ei.cc_cross_charge_code,'I','Y','X')
          ,ei.last_update_date = SYSDATE
          ,ei.last_update_login = l_user_id
          ,ei.request_id        = l_request_id
    where ei.expenditure_item_id = c_rec.expenditure_item_id
      and ei.cc_ic_processed_code = 'B';

Line: 513

	 pa_ic_inv_utils.log_message('Updated rows = '||SQL%rowcount);*/

Line: 516

		 pa_ic_inv_utils.log_message('Updated rows = '||l_rowcount);

Line: 593

 SELECT 'x'
 FROM  PA_DRAFT_INVOICE_DETAILS DID
 WHERE DID.PROJECT_ID = P_PROJECT_ID
 AND DID.INVOICED_FLAG = 'N'
 AND NOT EXISTS
     ( SELECT 'X'
       FROM  PA_DRAFT_INVOICES DI
       WHERE DI.PROJECT_ID = P_PROJECT_ID
       AND  DI.RELEASED_BY_PERSON_ID IS NULL
       AND  DI.CC_PROJECT_ID = DID.CC_PROJECT_ID )
      ;

Line: 606

SELECT 'X'
FROM  PA_DRAFT_INVOICES DI
WHERE DI.PROJECT_ID = P_PROJECT_ID
     AND  DI.RELEASED_BY_PERSON_ID IS NULL
     ;

Line: 667

PROCEDURE Update_DI_for_MRC
	   ( P_DRAFT_INVOICE_NUM IN NUMBER ,
	     P_REQUEST_ID	 IN NUMBER ,
             P_PROJECT_ID        IN NUMBER) AS
begin

-- Update pa_draft_invoices with creation date so that the
-- trigger on this table is fired and creates MRC rows
   pa_debug.g_err_stage := ' In Update_DI_for_MRC ';

Line: 677

   	pa_ic_inv_utils.log_message('Update_DI_for_MRC: ' || pa_debug.g_err_stage);

Line: 680

    update pa_draft_invoices
       set creation_date = creation_date
       where project_id = P_PROJECT_ID
         and request_id = P_request_id
         and draft_invoice_num = P_draft_invoice_num;

Line: 687

        pa_ic_inv_utils.log_message('Rows Updated = '||SQL%rowcount);

Line: 688

    	pa_ic_inv_utils.log_message('Done Update_DI_for_MRC');

Line: 691

end Update_DI_for_MRC;

Line: 701

  SELECT CREDIT_HOLD
  INTO   L_CREDIT_HOLD
/*  FROM   AR_CUSTOMER_PROFILES  Commented for TCA changes */
  FROM   HZ_CUSTOMER_PROFILES
  WHERE  SITE_USE_ID   = P_SITE_USE_ID ;

Line: 722

 SELECT STATUS,
        SITE_USE_ID
 INTO   P_SITE_STATUS,
        P_SITE_USE_ID
/*  FROM   RA_SITE_USES  Commented for TCA changes. */
 FROM  HZ_CUST_SITE_USES
 WHERE  CUST_ACCT_SITE_ID     = P_ADDRESS_ID   /* The column address_id has been replaced with CUST_ACCT_SITE_ID for tca change */
 AND    SITE_USE_CODE         = P_SITE_USE_CODE
 AND    STATUS                = 'A';