DBA Data[Home] [Help]

APPS.PSP_SUM_ADJ SQL Statements

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

Line: 189

   SELECT distinct ppc.batch_name
     FROM psp_payroll_controls ppc,
          psp_adjustment_control_table pact
    WHERE ppc.status_code = decode(g_run_type, 'N', 'N', 'R', 'I')
      AND ppc.source_type = 'A'
      AND ppc.batch_name = pact.adjustment_batch_name
      AND (g_run_type = 'N' OR (g_run_type = 'R' and
           ppc.adj_sum_batch_name = p_adj_sum_batch_name))
      AND (dist_dr_amount is not null or dist_cr_amount is not null)
      AND ppc.business_group_id = p_business_group_id
      AND ppc.set_of_books_id = p_set_of_books_id
      AND (pact.approver_id is not null and pact.approver_id <> -999);
Line: 207

   SELECT payroll_control_id,
          source_type,
          payroll_source_code,
          time_period_id,
          batch_name,
	  gms_phase
   FROM   psp_payroll_controls
   WHERE  source_type = 'A'
   AND    ((adj_sum_batch_name is null) OR
		(adj_sum_batch_name is not null and run_id is not null))
   AND    (dist_dr_amount IS NOT NULL OR dist_cr_amount IS NOT NULL)
   AND    batch_name = p_batch_name
   AND    business_group_id = p_business_group_id
   AND    set_of_books_id = p_set_of_books_id
   AND    status_code in ( 'N','I');   --- added 'I' for 2444657
Line: 225

   select decode(count(*), 0, 'N', 'R')
   from psp_payroll_controls
   where source_type = 'A'
     and adj_sum_batch_name = p_adj_sum_batch_name
     and status_code = 'I';
Line: 232

   SELECT	count(*)
   FROM		psp_payroll_controls
   WHERE	adj_sum_batch_name = p_adj_sum_batch_name
   AND          source_type = 'A'     -- Bug 2133056
   AND		g_run_type <> 'R';
Line: 248

   SELECT	psp_st_run_id_s.nextval
   INTO		g_run_id
   FROM		DUAL;
Line: 319

       UPDATE psp_payroll_controls
       SET status_code = 'I',
         adj_sum_batch_name = p_adj_sum_batch_name,
         run_id = g_run_id
       WHERE payroll_control_id = payroll_control_rec.payroll_control_id;
Line: 325

       UPDATE psp_payroll_controls
       SET run_id = g_run_id
       WHERE payroll_control_id = payroll_control_rec.payroll_control_id;
Line: 349

   SELECT distinct batch_name
     FROM psp_payroll_controls
    WHERE source_type = 'A'
      AND adj_sum_batch_name = p_adj_sum_batch_name
      AND (dist_dr_amount is not null or dist_cr_amount is not null)
--      AND (gl_phase = 'GL_Tie_Back' OR gms_phase = 'GMS_Tie_Back') : Bug 1776606 : Commented out
-- Bug 1776606 : Added the next two conditions..Phase =Null or GMS_TIE_BACK /GL_TIE_BACK
      AND (gms_phase is null or gms_phase = 'GMS_Tie_Back')
      AND (gl_phase is null or gl_phase = 'GL_Tie_Back')
      AND status_code = 'I'
      AND business_group_id = p_business_group_id
      AND set_of_books_id = p_set_of_books_id
      AND run_id = nvl(g_run_id, run_id);
Line: 366

   SELECT payroll_control_id,
          source_type,
          payroll_source_code,
          time_period_id,
          batch_name,
	  gl_phase,
	  gms_phase
   FROM   psp_payroll_controls
   WHERE  source_type = 'A'
   AND    batch_name = p_batch_name
   AND    (dist_dr_amount IS NOT NULL OR dist_cr_amount IS NOT NULL)
   AND    status_code = 'I'
   AND    run_id = g_run_id;
Line: 383

   SELECT count(*)
   FROM   psp_summary_lines
   WHERE  payroll_control_id = p_payroll_control_id
   AND    status_code <> 'A';
Line: 389

   SELECT count(*)
     FROM psp_adjustment_lines
    WHERE payroll_control_id = p_payroll_control_id
      AND gl_code_combination_id is not null
      AND status_code <> 'A';
Line: 442

   DELETE FROM pa_transaction_interface_all
--   WHERE batch_name = g_gms_batch_name
   WHERE batch_name IN (SELECT GMS_BATCH_NAME
                        FROM psp_summary_lines
                        WHERE  PAYROLL_CONTROL_ID IN(SELECT payroll_control_id
                                                    FROM    psp_payroll_controls
                                                    WHERE   adj_sum_batch_name =p_adj_sum_batch_name))
    ---AND transaction_status_code = 'A'   delete 'R' also, for  2445196
    AND transaction_source in ('OLD', 'GOLD');
Line: 453

   delete from gms_transaction_interface_all
--   where batch_name = g_gms_batch_name
   WHERE batch_name IN (SELECT GMS_BATCH_NAME
                        FROM psp_summary_lines
                        WHERE  PAYROLL_CONTROL_ID IN(SELECT payroll_control_id
                                                    FROM    psp_payroll_controls
                                                    WHERE   adj_sum_batch_name =p_adj_sum_batch_name))
     and transaction_source = 'GOLD';
Line: 491

   UPDATE psp_payroll_controls
   SET status_code = 'P'
   WHERE payroll_control_id = payroll_control_rec.payroll_control_id;
Line: 518

   SELECT distinct batch_name
     FROM psp_payroll_controls
    WHERE source_type = 'A'
      AND status_code = 'I'
      AND run_id = nvl(g_run_id, run_id)
      AND gl_phase is null
      AND adj_sum_batch_name = p_adj_sum_batch_name
      AND business_group_id = p_business_group_id
      AND set_of_books_id = p_set_of_books_id
      AND (dist_dr_amount is not null or dist_cr_amount is not null);
Line: 532

   SELECT payroll_control_id,
          source_type,
          payroll_source_code,
          time_period_id,
          batch_name,
	  gl_posting_override_date
   FROM   psp_payroll_controls
   WHERE  batch_name = p_batch_name
   AND    (dist_dr_amount IS NOT NULL OR dist_cr_amount IS NOT NULL)
   AND    source_type = 'A'
   AND    status_code = 'I'
   AND    run_id = nvl(g_run_id, run_id);
Line: 546

   SELECT pal.person_id,
          pal.assignment_id,
          pal.gl_code_combination_id gl_ccid,
          pal.dr_cr_flag,
          pal.effective_date,
          psl.accounting_date,   -- removed nvl on a/c date for 4734810
          psl.exchange_rate_type,
          pal.distribution_amount,
          pal.adjustment_line_id distribution_line_id,
          'A' tab_flag,
	DECODE(g_dff_grouping_option, 'Y', pal.attribute_category, NULL) attribute_category,	-- Introduced DFF columns for bug fix 2908859
	DECODE(g_dff_grouping_option, 'Y', pal.attribute1, NULL) attribute1,
	DECODE(g_dff_grouping_option, 'Y', pal.attribute2, NULL) attribute2,
	DECODE(g_dff_grouping_option, 'Y', pal.attribute3, NULL) attribute3,
	DECODE(g_dff_grouping_option, 'Y', pal.attribute4, NULL) attribute4,
	DECODE(g_dff_grouping_option, 'Y', pal.attribute5, NULL) attribute5,
	DECODE(g_dff_grouping_option, 'Y', pal.attribute6, NULL) attribute6,
	DECODE(g_dff_grouping_option, 'Y', pal.attribute7, NULL) attribute7,
	DECODE(g_dff_grouping_option, 'Y', pal.attribute8, NULL) attribute8,
	DECODE(g_dff_grouping_option, 'Y', pal.attribute9, NULL) attribute9,
	DECODE(g_dff_grouping_option, 'Y', pal.attribute10, NULL) attribute10
   FROM   psp_adjustment_lines       pal,
          psp_distribution_lines_history pdlh,
          psp_summary_lines psl
   WHERE  pal.status_code = 'N'
   AND    pal.gl_code_combination_id is not null
   AND    pal.payroll_control_id = p_payroll_control_id
   AND    pal.orig_source_type = 'D'
   AND    pal.orig_line_id = pdlh.distribution_line_id
   AND    pdlh.summary_line_id = psl.summary_line_id
   UNION    --- added union to get accounting dates -- 3108109
   SELECT pal.person_id,
          pal.assignment_id,
          pal.gl_code_combination_id gl_ccid,
          pal.dr_cr_flag,
          pal.effective_date,
          psl.accounting_date,  ---3108109
          psl.exchange_rate_type,
          pal.distribution_amount,
          pal.adjustment_line_id distribution_line_id,
          'A' tab_flag,
	DECODE(g_dff_grouping_option, 'Y', pal.attribute_category, NULL) attribute_category,	-- Introduced DFF columns for bug fix 2908859
	DECODE(g_dff_grouping_option, 'Y', pal.attribute1, NULL) attribute1,
	DECODE(g_dff_grouping_option, 'Y', pal.attribute2, NULL) attribute2,
	DECODE(g_dff_grouping_option, 'Y', pal.attribute3, NULL) attribute3,
	DECODE(g_dff_grouping_option, 'Y', pal.attribute4, NULL) attribute4,
	DECODE(g_dff_grouping_option, 'Y', pal.attribute5, NULL) attribute5,
	DECODE(g_dff_grouping_option, 'Y', pal.attribute6, NULL) attribute6,
	DECODE(g_dff_grouping_option, 'Y', pal.attribute7, NULL) attribute7,
	DECODE(g_dff_grouping_option, 'Y', pal.attribute8, NULL) attribute8,
	DECODE(g_dff_grouping_option, 'Y', pal.attribute9, NULL) attribute9,
	DECODE(g_dff_grouping_option, 'Y', pal.attribute10, NULL) attribute10
   FROM   psp_adjustment_lines       pal,
          psp_adjustment_lines_history palh,
          psp_summary_lines psl
   WHERE  pal.status_code = 'N'
   AND    pal.gl_code_combination_id is not null
   AND    pal.payroll_control_id = p_payroll_control_id
   AND    pal.orig_source_type = 'A'
   AND    pal.orig_line_id = palh.adjustment_line_id
   AND    palh.summary_line_id = psl.summary_line_id
   UNION
   SELECT pal.person_id,
          pal.assignment_id,
          pal.gl_code_combination_id gl_ccid,
          pal.dr_cr_flag,
          pal.effective_date,
          psl.accounting_date, ---3108109
          psl.exchange_rate_type,
          pal.distribution_amount,
          pal.adjustment_line_id distribution_line_id,
          'A' tab_flag,
	DECODE(g_dff_grouping_option, 'Y', pal.attribute_category, NULL) attribute_category,	-- Introduced DFF columns for bug fix 2908859
	DECODE(g_dff_grouping_option, 'Y', pal.attribute1, NULL) attribute1,
	DECODE(g_dff_grouping_option, 'Y', pal.attribute2, NULL) attribute2,
	DECODE(g_dff_grouping_option, 'Y', pal.attribute3, NULL) attribute3,
	DECODE(g_dff_grouping_option, 'Y', pal.attribute4, NULL) attribute4,
	DECODE(g_dff_grouping_option, 'Y', pal.attribute5, NULL) attribute5,
	DECODE(g_dff_grouping_option, 'Y', pal.attribute6, NULL) attribute6,
	DECODE(g_dff_grouping_option, 'Y', pal.attribute7, NULL) attribute7,
	DECODE(g_dff_grouping_option, 'Y', pal.attribute8, NULL) attribute8,
	DECODE(g_dff_grouping_option, 'Y', pal.attribute9, NULL) attribute9,
	DECODE(g_dff_grouping_option, 'Y', pal.attribute10, NULL) attribute10
   FROM   psp_adjustment_lines       pal,
          psp_pre_gen_dist_lines_history pglh,
          psp_summary_lines psl
   WHERE  pal.status_code = 'N'
   AND    pal.gl_code_combination_id is not null
   AND    pal.payroll_control_id = p_payroll_control_id
   AND    pal.orig_source_type = 'P'
   AND    pal.orig_line_id = pglh.pre_gen_dist_line_id
   AND    pglh.summary_line_id = psl.summary_line_id
   ORDER BY 1,2,3,4,6,7,11,12,13,14,15,16,17,18,19,20,21,5;
Line: 715

   	update psp_payroll_controls
      	   set gl_phase = 'Summarize_GL_Lines'
    	where payroll_control_id = payroll_control_rec.payroll_control_id;
Line: 766

        insert_into_summary_lines(
            	l_summary_line_id,
		l_person_id,
		l_assignment_id,
            	payroll_control_rec.time_period_id,
 		l_effective_date,
                l_accounting_date,    -- added for 3108109
                l_exchange_rate_type,
            	payroll_control_rec.source_type,
 		payroll_control_rec.payroll_source_code,
            	l_sob_id,
 		l_gl_ccid,
 		NULL,
 		NULL,
 		NULL,
 		NULL,
 		NULL,
 		l_summary_amount,
 		l_dr_cr_flag,
 		'N',
            	payroll_control_rec.batch_name,
            	payroll_control_rec.payroll_control_id,
		p_business_group_id,
		l_attribute_category,				-- Introduced DFF parameters for bug fix 2908859
		l_attribute1,
		l_attribute2,
		l_attribute3,
		l_attribute4,
		l_attribute5,
		l_attribute6,
		l_attribute7,
		l_attribute8,
		l_attribute9,
		l_attribute10,
            	l_return_status);
Line: 811

           UPDATE psp_adjustment_lines
              SET summary_line_id = l_summary_line_id
	    WHERE adjustment_line_id = l_dist_line_id;
Line: 820

       dist_line_id.delete;
Line: 852

     insert_into_summary_lines(
            	l_summary_line_id,
		l_person_id,
		l_assignment_id,
            	payroll_control_rec.time_period_id,
 		l_effective_date,
                l_accounting_date, --- added for 3108109
                l_exchange_rate_type,
            	payroll_control_rec.source_type,
 		payroll_control_rec.payroll_source_code,
            	l_sob_id,
 		l_gl_ccid,
 		NULL,
 		NULL,
 		NULL,
 		NULL,
 		NULL,
 		l_summary_amount,
 		l_dr_cr_flag,
 		'N',
            payroll_control_rec.batch_name,
            payroll_control_rec.payroll_control_id,
	    p_business_group_id,
		l_attribute_category,				-- Introduced DFF parameters for bug fix 2908859
		l_attribute1,
		l_attribute2,
		l_attribute3,
		l_attribute4,
		l_attribute5,
		l_attribute6,
		l_attribute7,
		l_attribute8,
		l_attribute9,
		l_attribute10,
            l_return_status);
Line: 897

         UPDATE psp_adjustment_lines
         SET summary_line_id = l_summary_line_id,
             status_code = 'N'
         WHERE adjustment_line_id = l_dist_line_id;
Line: 915

     dist_line_id.delete;
Line: 938

 PROCEDURE insert_into_summary_lines(
		P_SUMMARY_LINE_ID		OUT NOCOPY	NUMBER,
		P_PERSON_ID			IN	NUMBER,
		P_ASSIGNMENT_ID			IN	NUMBER,
		P_TIME_PERIOD_ID		IN	NUMBER,
 		P_EFFECTIVE_DATE		IN	DATE,
                P_ACCOUNTING_DATE               IN      DATE, ---added-> 3108109
                P_EXCHANGE_RATE_TYPE            IN      VARCHAR2,
            	P_SOURCE_TYPE			IN	VARCHAR2,
 		P_SOURCE_CODE			IN	VARCHAR2,
		P_SET_OF_BOOKS_ID		IN	NUMBER,
 		P_GL_CODE_COMBINATION_ID	IN	NUMBER,
 		P_PROJECT_ID			IN	NUMBER,
 		P_EXPENDITURE_ORGANIZATION_ID	IN	NUMBER,
 		P_EXPENDITURE_TYPE		IN	VARCHAR2,
 		P_TASK_ID			IN	NUMBER,
 		P_AWARD_ID			IN	NUMBER,
 		P_SUMMARY_AMOUNT		IN	NUMBER,
 		P_DR_CR_FLAG			IN	VARCHAR2,
 		P_STATUS_CODE			IN	VARCHAR2,
            	P_INTERFACE_BATCH_NAME		IN	VARCHAR2,
		P_PAYROLL_CONTROL_ID		IN	NUMBER,
		P_BUSINESS_GROUP_ID		IN	NUMBER,
		p_attribute_category		IN	VARCHAR2,		-- Introduced DFF parameters for bug fix 2908859
		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_RETURN_STATUS			OUT NOCOPY   	VARCHAR2,
		P_ORG_ID			IN  NUMBER DEFAULT NULL     -- R12 MOAc uptake
		) IS
	l_msg_id 	number(9);
Line: 988

    SELECT PSP_SUMMARY_LINES_S.NEXTVAL
    INTO P_SUMMARY_LINE_ID
    FROM DUAL;
Line: 992

    INSERT INTO PSP_SUMMARY_LINES(
		SUMMARY_LINE_ID,
		PERSON_ID,
		ASSIGNMENT_ID,
		TIME_PERIOD_ID,
 		EFFECTIVE_DATE,
                ACCOUNTING_DATE,
                EXCHANGE_RATE_TYPE,
 		GMS_POSTING_EFFECTIVE_DATE, /* New column added for Enhancement Employee Assignment with Zero Work Days */
            	SOURCE_TYPE,
 		SOURCE_CODE,
		SET_OF_BOOKS_ID,
 		GL_CODE_COMBINATION_ID,
 		PROJECT_ID,
 		EXPENDITURE_ORGANIZATION_ID,
 		EXPENDITURE_TYPE,
 		TASK_ID,
 		AWARD_ID,
 		SUMMARY_AMOUNT,
 		DR_CR_FLAG,
 		STATUS_CODE,
            	INTERFACE_BATCH_NAME,
            	PAYROLL_CONTROL_ID,
		BUSINESS_GROUP_ID,
		LAST_UPDATE_DATE,
		LAST_UPDATED_BY,
		LAST_UPDATE_LOGIN,
		CREATED_BY,
		CREATION_DATE,
		ACTUAL_SUMMARY_AMOUNT,    --For Bug 2496661
		attribute_category,			-- Introduced DFF columns for bug fix 2908859
		attribute1,
		attribute2,
		attribute3,
		attribute4,
		attribute5,
		attribute6,
		attribute7,
		attribute8,
		attribute9,
		attribute10,
		org_id              -- R12 MOAc uptake
		)
    VALUES(
            	P_SUMMARY_LINE_ID,
		P_PERSON_ID,
		P_ASSIGNMENT_ID,
		P_TIME_PERIOD_ID,
 		P_EFFECTIVE_DATE,
                P_ACCOUNTING_DATE,  --- 3108109
                P_EXCHANGE_RATE_TYPE,
 		L_GMS_POSTING_EFFECTIVE_DATE, /* New column added for Enhancement Employee Assignment with Zero Work Days */
            	P_SOURCE_TYPE,
 		P_SOURCE_CODE,
		P_SET_OF_BOOKS_ID,
 		P_GL_CODE_COMBINATION_ID,
 		P_PROJECT_ID,
 		P_EXPENDITURE_ORGANIZATION_ID,
 		P_EXPENDITURE_TYPE,
 		P_TASK_ID,
 		P_AWARD_ID,
 		P_SUMMARY_AMOUNT,
 		P_DR_CR_FLAG,
 		P_STATUS_CODE,
            	P_INTERFACE_BATCH_NAME,
            	P_PAYROLL_CONTROL_ID,
		P_BUSINESS_GROUP_ID,
		SYSDATE,
		FND_GLOBAL.USER_ID,
		FND_GLOBAL.LOGIN_ID,
		FND_GLOBAL.USER_ID,
		SYSDATE,
		DECODE(P_PROJECT_ID, NULL,P_SUMMARY_AMOUNT, DECODE(P_DR_CR_FLAG,'C',0 - P_SUMMARY_AMOUNT,P_SUMMARY_AMOUNT)), --For Bug 2496661
		p_attribute_category,			-- Introduced DFF columns for bug fix 2908859
		p_attribute1,
		p_attribute2,
		p_attribute3,
		p_attribute4,
		p_attribute5,
		p_attribute6,
		p_attribute7,
		p_attribute8,
		p_attribute9,
		p_attribute10,
		P_ORG_ID              -- R12 MOAc uptake
		);
Line: 1082

      g_error_api_path := 'INSERT_INTO_SUMMARY_LINES:'||g_error_api_path;
Line: 1083

      fnd_msg_pub.add_exc_msg('PSP_SUM_TRANS','INSERT_INTO_SUMMARY_LINES');
Line: 1095

   SELECT distinct batch_name
     FROM psp_payroll_controls
    WHERE adj_sum_batch_name = p_adj_sum_batch_name
      AND source_type = 'A'
      AND (dist_dr_amount IS NOT NULL OR dist_cr_amount IS NOT NULL)
      AND status_code = 'I'
      AND gl_phase = 'Summarize_GL_Lines'
      AND business_group_id = p_business_group_id
      AND set_of_books_id = p_set_of_books_id
      AND run_id = nvl(g_run_id, run_id);
Line: 1108

   SELECT payroll_control_id,
          source_type,
          payroll_source_code,
          time_period_id,
          batch_name,
--	Introduced for bug fix 2916848
	  currency_code
   FROM   psp_payroll_controls
   WHERE  batch_name = p_batch_name
   AND    source_type = 'A'
   AND    (dist_dr_amount IS NOT NULL OR dist_cr_amount IS NOT NULL)
   AND    status_code = 'I'
   AND    gl_phase = 'Summarize_GL_Lines'
   AND    run_id = g_run_id;
Line: 1125

   SELECT psl.summary_line_id,
          psl.source_code,
          psl.effective_date,
          psl.accounting_date,  --- added for 3108109
          psl.exchange_rate_type,
          psl.set_of_books_id,
          psl.gl_code_combination_id,
          psl.summary_amount,
          psl.dr_cr_flag,
          psl.attribute1,
          psl.attribute2,
          psl.attribute3,
          psl.attribute4,
          psl.attribute5,
          psl.attribute6,
          psl.attribute7,
          psl.attribute8,
          psl.attribute9,
          psl.attribute10,
          psl.attribute11,
          psl.attribute12,
          psl.attribute13,
          psl.attribute14,
          psl.attribute15,
          psl.attribute16,
          psl.attribute17,
          psl.attribute18,
          psl.attribute19,
          psl.attribute20,
          psl.attribute21,
          psl.attribute22,
          psl.attribute23,
          psl.attribute24,
          psl.attribute25,
          psl.attribute26,
          psl.attribute27,
          psl.attribute28,
          psl.attribute29,
          psl.attribute30,
          psl.person_id	--Included this column as part of bug fix 1828519
   FROM  psp_summary_lines  psl
   WHERE psl.status_code = 'N'
   AND   psl.gl_code_combination_id IS NOT NULL
   AND   psl.payroll_control_id = p_payroll_control_id;
Line: 1207

   SELECT	period_name, end_date
   FROM		per_time_periods
   WHERE	time_period_id = gl_batch_rec.time_period_id;
Line: 1213

   SELECT	currency_code
   FROM		gl_sets_of_books
   WHERE	set_of_books_id = l_sob_id;
Line: 1220

   SELECT	full_name
   FROM		per_people_f ppf
   WHERE	ppf.person_id = (SELECT	pal.person_id
				FROM	psp_adjustment_lines pal
                                WHERE pal.payroll_control_id = gl_batch_rec.payroll_control_id
                                  AND rownum = 1); --Bug 2133056 removed distinct and replace batch_name with payroll_control_id
Line: 1254

     SELECT	gl_interface_control_s.nextval
     INTO	l_group_id
     FROM	DUAL;
Line: 1269

     UPDATE psp_summary_lines
     SET group_id = l_group_id
     WHERE status_code = 'N'
     AND   gl_code_combination_id IS NOT NULL
     AND   payroll_control_id = gl_batch_rec.payroll_control_id;
Line: 1335

       insert_into_gl_interface(
		L_SOB_ID,GL_INTERFACE_REC.EFFECTIVE_DATE, gl_batch_rec.currency_code,
                L_USER_JE_CATEGORY_NAME,L_USER_JE_SOURCE_NAME,L_ENCUMBRANCE_TYPE_ID,
		GL_INTERFACE_REC.GL_CODE_COMBINATION_ID,L_ENTERED_DR,L_ENTERED_CR,
                L_GROUP_ID,L_REFERENCE1,L_REFERENCE1,L_REFERENCE4,
                GL_INTERFACE_REC.SUMMARY_LINE_ID,L_REFERENCE4,
                GL_INTERFACE_REC.ATTRIBUTE1,GL_INTERFACE_REC.ATTRIBUTE2,
                GL_INTERFACE_REC.ATTRIBUTE3,GL_INTERFACE_REC.ATTRIBUTE4,
                GL_INTERFACE_REC.ATTRIBUTE5,GL_INTERFACE_REC.ATTRIBUTE6,
		GL_INTERFACE_REC.ATTRIBUTE7,GL_INTERFACE_REC.ATTRIBUTE8,
                GL_INTERFACE_REC.ATTRIBUTE9,GL_INTERFACE_REC.ATTRIBUTE10,
                GL_INTERFACE_REC.ATTRIBUTE11,GL_INTERFACE_REC.ATTRIBUTE12,
                GL_INTERFACE_REC.ATTRIBUTE13,GL_INTERFACE_REC.ATTRIBUTE14,
                GL_INTERFACE_REC.ATTRIBUTE15,GL_INTERFACE_REC.ATTRIBUTE16,
		GL_INTERFACE_REC.ATTRIBUTE17,GL_INTERFACE_REC.ATTRIBUTE18,
                GL_INTERFACE_REC.ATTRIBUTE19,GL_INTERFACE_REC.ATTRIBUTE20,
                GL_INTERFACE_REC.ATTRIBUTE21,GL_INTERFACE_REC.ATTRIBUTE22,
                GL_INTERFACE_REC.ATTRIBUTE23,GL_INTERFACE_REC.ATTRIBUTE24,
                GL_INTERFACE_REC.ATTRIBUTE25,GL_INTERFACE_REC.ATTRIBUTE26,
		GL_INTERFACE_REC.ATTRIBUTE27,GL_INTERFACE_REC.ATTRIBUTE28,
                GL_INTERFACE_REC.ATTRIBUTE29,GL_INTERFACE_REC.ATTRIBUTE30,
		gl_interface_rec.exchange_rate_type,
                GL_INTERFACE_REC.accounting_date, --- added for 3108109
                L_RETURN_STATUS);
Line: 1386

     SELECT	GL_JOURNAL_IMPORT_S.NEXTVAL
     INTO	l_int_run_id
     FROM	DUAL;
Line: 1390

     insert into gl_interface_control(
         		je_source_name,
        		status,
      			interface_run_id,
        		group_id,
                  	set_of_books_id)
       		VALUES (
                  	l_user_je_source_name,
         		'S',
                  	l_int_run_id,
                  	l_group_id,
                  	l_sob_id
          	       );
Line: 1422

       update psp_payroll_controls
          set gl_phase = 'Submitted_Import_Request'
        --where payroll_control_id = gl_batch_rec.payroll_control_id;  Replaced this line for Bug 2133056
Line: 1462

   SELECT	user_je_source_name
   FROM		gl_je_sources
   WHERE	je_source_name = 'OLD';
Line: 1507

   SELECT user_je_category_name
   FROM   gl_je_categories
   WHERE  je_category_name = 'OLD';
Line: 1551

   SELECT distinct batch_name
     FROM psp_payroll_controls
    WHERE adj_sum_batch_name = p_adj_sum_batch_name
      AND (dist_dr_amount is not null and dist_cr_amount is not null)
      AND source_type = 'A'
      AND status_code = 'I'
      AND gl_phase = 'Submitted_Import_Request'
      AND business_group_id = p_business_group_id
      AND set_of_books_id = p_set_of_books_id
      AND run_id = nvl(g_run_id, run_id);
Line: 1566

   SELECT summary_line_id,
          dr_cr_flag,summary_amount
   FROM   psp_summary_lines
   WHERE  group_id = p_group_id
     AND  payroll_control_id = p_payroll_control_id;
Line: 1573

   SELECT reversal_entry_flag
     FROM psp_adjustment_lines
    WHERE summary_line_id = p_summary_line_id;
Line: 1578

   SELECT payroll_control_id,
	  source_type
     FROM psp_payroll_controls
    WHERE batch_name = p_batch_name
      AND (dist_dr_amount is not null and dist_cr_amount is not null)
      AND source_type = 'A'
      AND status_code = 'I'
      AND business_group_id = p_business_group_id
      AND set_of_books_id = p_set_of_books_id
      AND gl_phase = 'Submitted_Import_Request'
      AND run_id = nvl(g_run_id, run_id);
Line: 1640

   SELECT	PSL.group_id
   FROM		psp_summary_lines PSL,
                psp_payroll_controls PPC
   WHERE        PPC.payroll_control_id = PSL.payroll_control_id
-- WHERE	payroll_control_id = gl_tie_back_rec.payroll_control_id
     AND        PPC.batch_name =  pc_batch_rec.batch_name
     AND        PPC.source_type = 'A'
     AND	PSL.group_id IS NOT NULL
     AND        rownum =1;  -- Removed max function on group_id and introduced rownum=1 Bug 2133056
Line: 1651

   SELECT	count(*)
   FROM		gl_interface
   WHERE	group_id = l_group_id
   AND		set_of_books_id = p_set_of_books_id
   AND		user_je_source_name = 'OLD';
Line: 1659

   SELECT       count(*)
   FROM         gl_interface
   WHERE        group_id = l_group_id
   AND          user_je_source_name = 'OLD'
   AND          status = 'NEW';
Line: 1698

          update psp_payroll_controls
            set gl_phase = 'Summarize_GL_Lines'
          where gl_phase = 'Submitted_Import_Request'
            and batch_name =  pc_batch_rec.batch_name; --- 3184075
Line: 1704

          delete from gl_interface
           where group_id = l_group_id
             and user_je_source_name = 'OLD';
Line: 1708

          delete from gl_interface_control
           where group_id = l_group_id
             and je_source_name = 'OLD';
Line: 1728

   UPDATE psp_payroll_controls
      SET gl_phase = 'GL_Tie_Back'
    WHERE payroll_control_id = gl_tie_back_rec.payroll_control_id;
Line: 1774

       UPDATE psp_summary_lines
       SET status_code = 'A'
       WHERE summary_line_id = l_summary_line_id;
Line: 1784

         UPDATE psp_adjustment_lines
         SET status_code = 'A' WHERE summary_line_id = l_summary_line_id;
Line: 1788

         INSERT INTO psp_adjustment_lines_history
         (adjustment_line_id,person_id,assignment_id,element_type_id,
          distribution_date,effective_date,distribution_amount,
          dr_cr_flag,payroll_control_id,source_type,source_code,time_period_id,
          batch_name,status_code,set_of_books_id,gl_code_combination_id,project_id,
          expenditure_organization_id,expenditure_type,task_id,award_id,
          suspense_org_account_id,suspense_reason_code,effort_report_id,version_num,
          summary_line_id, reversal_entry_flag, original_line_flag, user_defined_field, percent,
 	  orig_source_type,
          orig_line_id,attribute_category,attribute1,attribute2,attribute3,attribute4,
          attribute5,attribute6,attribute7,attribute8,attribute9,attribute10,attribute11,
          attribute12,attribute13,attribute14,attribute15,last_update_date,
          last_updated_by,last_update_login,created_by,creation_date, business_group_id,
          adj_set_number, line_number)   ---   added cols 2634557 DA Multiple element Enh
         SELECT adjustment_line_id,person_id,assignment_id,element_type_id,
          distribution_date,effective_date,distribution_amount,
          dr_cr_flag,payroll_control_id,source_type,source_code,time_period_id,
          batch_name,status_code,set_of_books_id,gl_code_combination_id,project_id,
          expenditure_organization_id,expenditure_type,task_id,award_id,
          suspense_org_account_id,suspense_reason_code,effort_report_id,version_num,
          summary_line_id, reversal_entry_flag, original_line_flag, user_defined_field, percent,
 	  orig_source_type,
          orig_line_id,attribute_category,attribute1,attribute2,attribute3,attribute4,
          attribute5,attribute6,attribute7,attribute8,attribute9,attribute10,attribute11,
          attribute12,attribute13,attribute14,attribute15,SYSDATE,FND_GLOBAL.USER_ID,
          FND_GLOBAL.LOGIN_ID,FND_GLOBAL.USER_ID,SYSDATE, business_group_id,
          adj_set_number, line_number  ---   added cols 2634557 DA Multiple element Enh
         FROM psp_adjustment_lines
         WHERE status_code = 'A'
         AND summary_line_id = l_summary_line_id;
Line: 1819

         DELETE FROM psp_adjustment_lines
         WHERE status_code = 'A'
         AND summary_line_id = l_summary_line_id;
Line: 1825

       UPDATE psp_payroll_controls
       SET gl_dr_amount = nvl(gl_dr_amount,0) + l_dr_summary_amount,
           gl_cr_amount = nvl(gl_cr_amount,0) + l_cr_summary_amount
       WHERE payroll_control_id = gl_tie_back_rec.payroll_control_id;
Line: 1860

   SELECT person_id,
          assignment_id,
          to_number(null) element_type_id,
          dr_cr_flag,
          effective_date,
          accounting_date,
          exchange_rate_type,
          source_type,
          source_code,
          time_period_id,
          summary_amount reversal_dist_amount,
          interface_batch_name,
          summary_line_id
   FROM   psp_summary_lines
   WHERE  payroll_control_id = p_payroll_control_id
   AND    gl_code_combination_id IS NOT NULL
   AND    status_code = 'N'
   AND	  business_group_id = p_business_group_id
   AND	  set_of_books_id = p_set_of_books_id;
Line: 1887

   SELECT	reversing_gl_ccid
   FROM		psp_clearing_account
   WHERE	set_of_books_id = p_set_of_books_id
   AND		business_group_id = p_business_group_id
   AND		payroll_id = l_payroll_id;              -- Added for bug 5592964
Line: 1897

   select sum(nvl(distribution_amount,0))
   from   psp_adjustment_lines
   where dr_cr_flag = 'D'
    and gl_code_combination_id is not null
    and  payroll_control_id = p_payroll_control_id;
Line: 1904

   select sum(nvl(distribution_amount,0))
   from   psp_adjustment_lines
   where dr_cr_flag = 'C'
   and gl_code_combination_id is not null
   and  payroll_control_id = p_payroll_control_id;
Line: 1916

   select element_type_id
   from psp_adjustment_lines
   where summary_line_id = p_summary_line_id
   and rownum = 1;
Line: 1941

       SELECT payroll_id INTO l_payroll_id
       FROM  psp_payroll_controls
       WHERE payroll_control_id = p_payroll_control_id;
Line: 1958

       DELETE FROM psp_adjustment_lines
       WHERE reversal_entry_flag = 'Y'
       AND status_code = 'N'
       AND payroll_control_id = p_payroll_control_id;
Line: 1989

             insert_into_summary_lines(
                l_summary_line_id,
                ad_reversal_entry_rec.person_id,
                ad_reversal_entry_rec.assignment_id,
                ad_reversal_entry_rec.time_period_id,
                ad_reversal_entry_rec.effective_date,
                ad_reversal_entry_rec.accounting_date,
                ad_reversal_entry_rec.exchange_rate_type,
                'A',
                ad_reversal_entry_rec.source_code,
                p_set_of_books_id,
                l_clrg_account_glccid,
                NULL,
                NULL,
                NULL,
                NULL,
                NULL,
                ad_reversal_entry_rec.reversal_dist_amount,
                ad_reversal_entry_rec.dr_cr_flag,
                'N',
                ad_reversal_entry_rec.interface_batch_name,
                p_payroll_control_id,
                p_business_group_id,
--	Introduced NULL for DFF parameters as clearing a/c doesnt require DFF values as part bug fix 2908859
		NULL,
		NULL,
		NULL,
		NULL,
		NULL,
		NULL,
		NULL,
		NULL,
		NULL,
		NULL,
		NULL,
                l_return_status);
Line: 2031

         insert into psp_adjustment_lines
           (adjustment_line_id,person_id,assignment_id,element_type_id,
            distribution_date,effective_date,distribution_amount,dr_cr_flag,
            payroll_control_id,source_type,source_code,time_period_id,batch_name,
            status_code,gl_code_combination_id,reversal_entry_flag,last_update_date,last_updated_by,
            last_update_login,created_by,creation_date, business_group_id, set_of_books_id,
            summary_line_id)
         values
           (PSP_ADJUSTMENT_LINES_S.NEXTVAL,ad_reversal_entry_rec.person_id,
            ad_reversal_entry_rec.assignment_id,ad_reversal_entry_rec.element_type_id,
            ad_reversal_entry_rec.effective_date,ad_reversal_entry_rec.effective_date,
             ad_reversal_entry_rec.reversal_dist_amount, ----l_reversal_dist_amount, Bug 1976999
            ad_reversal_entry_rec.dr_cr_flag,
            p_payroll_control_id,ad_reversal_entry_rec.source_type,
            ad_reversal_entry_rec.source_code,ad_reversal_entry_rec.time_period_id,
            ad_reversal_entry_rec.interface_batch_name,'N',l_clrg_account_glccid,'Y',
	SYSDATE,FND_GLOBAL.USER_ID,FND_GLOBAL.LOGIN_ID,FND_GLOBAL.USER_ID,SYSDATE,
	p_business_group_id, p_set_of_books_id, l_summary_line_id);
Line: 2070

 PROCEDURE insert_into_gl_interface(
			P_SET_OF_BOOKS_ID 		IN	NUMBER,
			P_ACCOUNTING_DATE		IN	DATE,
			P_CURRENCY_CODE			IN	VARCHAR2,
			P_USER_JE_CATEGORY_NAME		IN	VARCHAR2,
			P_USER_JE_SOURCE_NAME		IN	VARCHAR2,
			P_ENCUMBRANCE_TYPE_ID		IN	NUMBER,
			P_CODE_COMBINATION_ID		IN	NUMBER,
			P_ENTERED_DR			IN	NUMBER,
			P_ENTERED_CR			IN	NUMBER,
			P_GROUP_ID			IN	NUMBER,
			P_REFERENCE1			IN	VARCHAR2,
			P_REFERENCE2			IN	VARCHAR2,
			P_REFERENCE4			IN	VARCHAR2,
			P_REFERENCE6			IN	VARCHAR2,
			P_REFERENCE10			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_ATTRIBUTE11			IN	VARCHAR2,
			P_ATTRIBUTE12			IN	VARCHAR2,
			P_ATTRIBUTE13			IN	VARCHAR2,
			P_ATTRIBUTE14			IN	VARCHAR2,
			P_ATTRIBUTE15			IN	VARCHAR2,
			P_ATTRIBUTE16			IN	VARCHAR2,
			P_ATTRIBUTE17			IN	VARCHAR2,
			P_ATTRIBUTE18			IN	VARCHAR2,
			P_ATTRIBUTE19			IN	VARCHAR2,
			P_ATTRIBUTE20			IN	VARCHAR2,
			P_ATTRIBUTE21			IN	VARCHAR2,
			P_ATTRIBUTE22			IN	VARCHAR2,
			P_ATTRIBUTE23			IN	VARCHAR2,
			P_ATTRIBUTE24			IN	VARCHAR2,
			P_ATTRIBUTE25			IN	VARCHAR2,
			P_ATTRIBUTE26			IN	VARCHAR2,
			P_ATTRIBUTE27			IN	VARCHAR2,
			P_ATTRIBUTE28			IN	VARCHAR2,
			P_ATTRIBUTE29			IN	VARCHAR2,
			P_ATTRIBUTE30			IN	VARCHAR2,
			P_CURRENCY_CONVERSION_TYPE	IN	VARCHAR2,	-- Introduced for bug fix 2916848
			P_CURRENCY_CONVERSION_DATE		IN	DATE,	-- Introduced for bug fix 2916848
			P_RETURN_STATUS			OUT NOCOPY	VARCHAR2) IS
	l_msg_id	number(9);
Line: 2121

   INSERT INTO GL_INTERFACE(
	STATUS,
	SET_OF_BOOKS_ID,
	ACCOUNTING_DATE,
	CURRENCY_CODE,
	DATE_CREATED,
	CREATED_BY,
	ACTUAL_FLAG,
	USER_JE_CATEGORY_NAME,
	USER_JE_SOURCE_NAME,
	ENCUMBRANCE_TYPE_ID,
	CODE_COMBINATION_ID,
	ENTERED_DR,
	ENTERED_CR,
	GROUP_ID,
	REFERENCE1,
	REFERENCE2,
	REFERENCE4,
	REFERENCE6,
	REFERENCE10,
	ATTRIBUTE1,
	ATTRIBUTE2,
	ATTRIBUTE3,
	ATTRIBUTE4,
	ATTRIBUTE5,
	ATTRIBUTE6,
	ATTRIBUTE7,
	ATTRIBUTE8,
	ATTRIBUTE9,
	ATTRIBUTE10,
	ATTRIBUTE11,
	ATTRIBUTE12,
	ATTRIBUTE13,
	ATTRIBUTE14,
	ATTRIBUTE15,
	ATTRIBUTE16,
	ATTRIBUTE17,
	ATTRIBUTE18,
	ATTRIBUTE19,
	ATTRIBUTE20,
	REFERENCE21,
	REFERENCE22,
	REFERENCE23,
	REFERENCE24,
	REFERENCE25,
	REFERENCE26,
	REFERENCE27,
	REFERENCE28,
	REFERENCE29,
	REFERENCE30,
--	Introduced teh following columns for bug fix 2916848
	USER_CURRENCY_CONVERSION_TYPE,
	CURRENCY_CONVERSION_DATE)
   VALUES(
	'NEW',
	P_SET_OF_BOOKS_ID,
	P_ACCOUNTING_DATE,
	P_CURRENCY_CODE,
	SYSDATE,
	FND_GLOBAL.USER_ID,
	'A',
	P_USER_JE_CATEGORY_NAME,
	P_USER_JE_SOURCE_NAME,
	P_ENCUMBRANCE_TYPE_ID,
	P_CODE_COMBINATION_ID,
	P_ENTERED_DR,
	P_ENTERED_CR,
	P_GROUP_ID,
	P_REFERENCE1,
	P_REFERENCE2,
	P_REFERENCE4,
	P_REFERENCE6,
	P_REFERENCE10,
	P_ATTRIBUTE1,
	P_ATTRIBUTE2,
	P_ATTRIBUTE3,
	P_ATTRIBUTE4,
	P_ATTRIBUTE5,
	P_ATTRIBUTE6,
	P_ATTRIBUTE7,
	P_ATTRIBUTE8,
	P_ATTRIBUTE9,
	P_ATTRIBUTE10,
	P_ATTRIBUTE11,
	P_ATTRIBUTE12,
	P_ATTRIBUTE13,
	P_ATTRIBUTE14,
	P_ATTRIBUTE15,
	P_ATTRIBUTE16,
	P_ATTRIBUTE17,
	P_ATTRIBUTE18,
	P_ATTRIBUTE19,
	P_ATTRIBUTE20,
	P_ATTRIBUTE21,
	P_ATTRIBUTE22,
	P_ATTRIBUTE23,
	P_ATTRIBUTE24,
	P_ATTRIBUTE25,
	P_ATTRIBUTE26,
	P_ATTRIBUTE27,
	P_ATTRIBUTE28,
	P_ATTRIBUTE29,
	P_ATTRIBUTE30,
--	Introduced the following columns for bug fix 2916848
	P_CURRENCY_CONVERSION_TYPE,
	DECODE(p_currency_conversion_type, NULL, NULL, P_CURRENCY_CONVERSION_DATE));
Line: 2231

      g_error_api_path := 'INSERT_INTO_GL_INTERFACE:'||g_error_api_path;
Line: 2232

      fnd_msg_pub.add_exc_msg('PSP_SUM_TRANS','INSERT_INTO_GL_INTERFACE');
Line: 2243

   SELECT distinct batch_name
   FROM   psp_payroll_controls
   WHERE  adj_sum_batch_name = p_adj_sum_batch_name
   AND    source_type = 'A'
   AND    (dist_dr_amount IS NOT NULL OR dist_cr_amount IS NOT NULL)
   AND    status_code = 'I'
   AND    run_id = nvl(g_run_id, run_id)
   AND	  business_group_id = p_business_group_id
   AND	  set_of_books_id = p_set_of_books_id
   AND     gms_phase is null;
Line: 2257

   SELECT payroll_control_id,
          source_type,
          payroll_source_code,
          time_period_id,
          batch_name
   FROM   psp_payroll_controls
   WHERE  batch_name = p_batch_name
   AND    source_type = 'A'
   AND    (dist_dr_amount IS NOT NULL OR dist_cr_amount IS NOT NULL)
   AND    status_code = 'I'
   AND    run_id = nvl(g_run_id, run_id);
Line: 2270

   SELECT pal.person_id,
          pal.assignment_id,
	  pal.project_id project_id,
	  pal.expenditure_organization_id,
	  pal.expenditure_type,
	  pal.task_id,
	  pal.award_id,
          pal.dr_cr_flag,
          pal.effective_date,
          psl.accounting_date,  -- new column 3108109, removed nvl for 4734810
          psl.exchange_rate_type,
	  pal.distribution_amount,
          pal.adjustment_line_id distribution_line_id,
          'A' tab_flag,
	DECODE(g_dff_grouping_option, 'Y', pal.attribute_category, NULL) attribute_category,	-- Introduced DFF columns for bug fix 2908859
	DECODE(g_dff_grouping_option, 'Y', pal.attribute1, NULL) attribute1,
	DECODE(g_dff_grouping_option, 'Y', pal.attribute2, NULL) attribute2,
	DECODE(g_dff_grouping_option, 'Y', pal.attribute3, NULL) attribute3,
	DECODE(g_dff_grouping_option, 'Y', pal.attribute4, NULL) attribute4,
	DECODE(g_dff_grouping_option, 'Y', pal.attribute5, NULL) attribute5,
	DECODE(g_dff_grouping_option, 'Y', pal.attribute6, NULL) attribute6,
	DECODE(g_dff_grouping_option, 'Y', pal.attribute7, NULL) attribute7,
	DECODE(g_dff_grouping_option, 'Y', pal.attribute8, NULL) attribute8,
	DECODE(g_dff_grouping_option, 'Y', pal.attribute9, NULL) attribute9,
	DECODE(g_dff_grouping_option, 'Y', pal.attribute10, NULL) attribute10
   FROM   psp_adjustment_lines       pal,
          psp_distribution_lines_history pdh,
          psp_summary_lines psl
   WHERE  pal.status_code = 'N'
   AND    pal.gl_code_combination_id IS NULL
   AND    pal.payroll_control_id = p_payroll_control_id
   AND    pdh.distribution_line_id = pal.orig_line_id
   AND    pal.orig_source_type = 'D'
   AND    psl.summary_line_id = pdh.summary_line_id
   UNION
   SELECT pal.person_id,
          pal.assignment_id,
	  pal.project_id project_id,
	  pal.expenditure_organization_id,
	  pal.expenditure_type,
	  pal.task_id,
	  pal.award_id,
          pal.dr_cr_flag,
          pal.effective_date,
          psl.accounting_date,  -- new column 3108109
          psl.exchange_rate_type,
	  pal.distribution_amount,
          pal.adjustment_line_id distribution_line_id,
          'A' tab_flag,
	DECODE(g_dff_grouping_option, 'Y', pal.attribute_category, NULL) attribute_category,	-- Introduced DFF columns for bug fix 2908859
	DECODE(g_dff_grouping_option, 'Y', pal.attribute1, NULL) attribute1,
	DECODE(g_dff_grouping_option, 'Y', pal.attribute2, NULL) attribute2,
	DECODE(g_dff_grouping_option, 'Y', pal.attribute3, NULL) attribute3,
	DECODE(g_dff_grouping_option, 'Y', pal.attribute4, NULL) attribute4,
	DECODE(g_dff_grouping_option, 'Y', pal.attribute5, NULL) attribute5,
	DECODE(g_dff_grouping_option, 'Y', pal.attribute6, NULL) attribute6,
	DECODE(g_dff_grouping_option, 'Y', pal.attribute7, NULL) attribute7,
	DECODE(g_dff_grouping_option, 'Y', pal.attribute8, NULL) attribute8,
	DECODE(g_dff_grouping_option, 'Y', pal.attribute9, NULL) attribute9,
	DECODE(g_dff_grouping_option, 'Y', pal.attribute10, NULL) attribute10
   FROM   psp_adjustment_lines       pal,
          psp_adjustment_lines_history pal2,
          psp_summary_lines psl
   WHERE  pal.status_code = 'N'
   AND    pal.gl_code_combination_id IS NULL
   AND    pal.payroll_control_id = p_payroll_control_id
   AND    pal2.adjustment_line_id = pal.orig_line_id
   AND    pal.orig_source_type = 'A'
   AND    psl.summary_line_id = pal2.summary_line_id
   union
   SELECT pal.person_id,
          pal.assignment_id,
	  pal.project_id project_id,
	  pal.expenditure_organization_id,
	  pal.expenditure_type,
	  pal.task_id,
	  pal.award_id,
          pal.dr_cr_flag,
          pal.effective_date,
          psl.accounting_date,   -- new column 3108109
          psl.exchange_rate_type,
	  pal.distribution_amount,
          pal.adjustment_line_id distribution_line_id,
          'A' tab_flag,
	DECODE(g_dff_grouping_option, 'Y', pal.attribute_category, NULL) attribute_category,	-- Introduced DFF columns for bug fix 2908859
	DECODE(g_dff_grouping_option, 'Y', pal.attribute1, NULL) attribute1,
	DECODE(g_dff_grouping_option, 'Y', pal.attribute2, NULL) attribute2,
	DECODE(g_dff_grouping_option, 'Y', pal.attribute3, NULL) attribute3,
	DECODE(g_dff_grouping_option, 'Y', pal.attribute4, NULL) attribute4,
	DECODE(g_dff_grouping_option, 'Y', pal.attribute5, NULL) attribute5,
	DECODE(g_dff_grouping_option, 'Y', pal.attribute6, NULL) attribute6,
	DECODE(g_dff_grouping_option, 'Y', pal.attribute7, NULL) attribute7,
	DECODE(g_dff_grouping_option, 'Y', pal.attribute8, NULL) attribute8,
	DECODE(g_dff_grouping_option, 'Y', pal.attribute9, NULL) attribute9,
	DECODE(g_dff_grouping_option, 'Y', pal.attribute10, NULL) attribute10
   FROM   psp_adjustment_lines       pal,
          psp_pre_gen_dist_lines_history pgh,
          psp_summary_lines psl
   WHERE  pal.status_code = 'N'
   AND    pal.gl_code_combination_id IS NULL
   AND    pal.payroll_control_id = p_payroll_control_id
   AND    pgh.pre_gen_dist_line_id = pal.orig_line_id
   AND    pal.orig_source_type = 'P'
   AND    psl.summary_line_id = pgh.summary_line_id
   ORDER BY 1,2,3,4,5,6,7,8,10,11,15,16,17,18,19,20,21,22,23,24,25,9;
Line: 2445

          UPDATE psp_payroll_controls
             SET gms_phase = 'Summarize_GMS_Lines'
           WHERE payroll_control_id = payroll_control_rec.payroll_control_id;
Line: 2509

	-- insert into summary lines
        insert_into_summary_lines(
            	l_summary_line_id,
		l_person_id,
		l_assignment_id,
            	payroll_control_rec.time_period_id,
 		l_effective_date,
                l_accounting_date,   --- 3108109
                l_exchange_rate_type,
            	payroll_control_rec.source_type,
 		payroll_control_rec.payroll_source_code,
            	p_set_of_books_id,
            	NULL,
 		l_project_id,
 		l_expenditure_organization_id,
 		l_expenditure_type,
 		l_task_id,
 		l_award_id,
 		l_summary_amount,
 		l_dr_cr_flag,
 		'N',
            	payroll_control_rec.batch_name,
            	payroll_control_rec.payroll_control_id,
		p_business_group_id,
		l_attribute_category,				-- Introduced DFF parameters for bug fix 2908859
		l_attribute1,
		l_attribute2,
		l_attribute3,
		l_attribute4,
		l_attribute5,
		l_attribute6,
		l_attribute7,
		l_attribute8,
		l_attribute9,
		l_attribute10,
        l_return_status,
		l_org_id			-- R12 MOAC Uptake
		);
Line: 2555

           UPDATE psp_adjustment_lines
           SET summary_line_id = l_summary_line_id WHERE adjustment_line_id = l_dist_line_id;
Line: 2562

       dist_line_id.delete;
Line: 2603

	 -- insert into summary lines
     insert_into_summary_lines(
            	l_summary_line_id,
		l_person_id,
		l_assignment_id,
            	payroll_control_rec.time_period_id,
 		l_effective_date,
                l_accounting_date, --- added for 3108109
                l_exchange_rate_type,
            	payroll_control_rec.source_type,
 		payroll_control_rec.payroll_source_code,
            	p_set_of_books_id,
 		NULL,
 		l_project_id,
 		l_expenditure_organization_id,
 		l_expenditure_type,
 		l_task_id,
 		l_award_id,
 		l_summary_amount,
 		l_dr_cr_flag,
 		'N',
            	payroll_control_rec.batch_name,
            	payroll_control_rec.payroll_control_id,
		p_business_group_id,
		l_attribute_category,				-- Introduced DFF parameters for bug fix 2908859
		l_attribute1,
		l_attribute2,
		l_attribute3,
		l_attribute4,
		l_attribute5,
		l_attribute6,
		l_attribute7,
		l_attribute8,
		l_attribute9,
		l_attribute10,
        l_return_status,
		l_org_id 		-- R12 MOAC Uptake
		);
Line: 2650

         UPDATE psp_adjustment_lines
         SET summary_line_id = l_summary_line_id,
             status_code = 'N'
         WHERE adjustment_line_id = l_dist_line_id;
Line: 2656

     dist_line_id.delete;
Line: 2685

   SELECT distinct batch_name
   FROM   psp_payroll_controls
   WHERE  adj_sum_batch_name = p_adj_sum_batch_name
   AND    source_type = 'A'
   AND    (dist_dr_amount IS NOT NULL OR dist_cr_amount IS NOT NULL)
   AND    gms_phase = 'Summarize_GMS_Lines'
   AND    status_code = 'I'
   AND	  business_group_id = p_business_group_id
   AND	  set_of_books_id = p_set_of_books_id
   AND    run_id = nvl(g_run_id, run_id);
Line: 2699

   SELECT payroll_control_id,
          source_type,
          payroll_source_code,
          time_period_id,
          batch_name,
--	Introduced the following for bug fix 2916848
	  currency_code
   FROM   psp_payroll_controls
   WHERE  batch_name = p_batch_name
   AND    source_type = 'A'
   AND    (dist_dr_amount IS NOT NULL OR dist_cr_amount IS NOT NULL)
   AND    gms_phase = 'Summarize_GMS_Lines'
   AND    status_code = 'I'
   AND    run_id = g_run_id;
Line: 2716

   SELECT psl.summary_line_id,
          psl.source_code,
          psl.person_id,
          psl.assignment_id,
          NVL(psl.gms_posting_effective_date,psl.effective_date) effective_date, /* Bug: 1994421 Column modified for Enhancement Employee Assignment with Zero Work Days */
          psl.accounting_date, --- 3108109
          psl.exchange_rate_type,
          psl.project_id,
          psl.expenditure_organization_id,
          psl.expenditure_type,
          psl.task_id,
          psl.award_id,
          psl.summary_amount,
          psl.dr_cr_flag,
          psl.attribute1,			-- Introduced attributes 1, 4 * 5 for bug fix 29098859
          psl.attribute2,
          psl.attribute3,
          psl.attribute4,
          psl.attribute5,
          psl.attribute6,
          psl.attribute7,
          psl.attribute8,
          psl.attribute9,
          psl.attribute10,
		  org_id			-- R12 MOAC uptake
   FROM  psp_summary_lines  psl
   WHERE psl.status_code = 'N'
   AND   psl.gl_code_combination_id IS NULL
   AND   psl.payroll_control_id = p_payroll_control_id;
Line: 2748

   SELECT	transaction_source
   FROM		pa_transaction_sources
   WHERE	transaction_source = 'OLD';
Line: 2753

   SELECT	transaction_source
   FROM		pa_transaction_sources
   WHERE	transaction_source = 'GOLD';
Line: 2797

   SELECT	period_name, end_date
   FROM		per_time_periods
   WHERE	time_period_id = gms_batch_rec.time_period_id;
Line: 2802

   SELECT	employee_number
   FROM		per_people_f
   WHERE	person_id = gms_interface_rec.person_id
   AND		gms_interface_rec.effective_date BETWEEN effective_start_date AND effective_end_date;
Line: 2808

   SELECT	task_number
   FROM		pa_tasks
   WHERE	task_id = gms_interface_rec.task_id;
Line: 2813

   SELECT	name					--	Bug 2447912: Removed SUBSTR function
   FROM		hr_all_organization_units hou
   WHERE	organization_id = gms_interface_rec.expenditure_organization_id;
Line: 2818

   SELECT	segment1, org_id
   FROM		pa_projects_all
   WHERE 	project_id = gms_interface_rec.project_Id;
Line: 2824

   SELECT	full_name
   FROM		per_people_f ppf
   WHERE	ppf.person_id = (SELECT	pal.person_id
				FROM	psp_adjustment_lines pal
				WHERE	pal.payroll_control_id = gms_batch_rec.payroll_control_id
                                 AND    rownum = 1); -- Bug 2133056 Replaced batch_name with payroll_control_id and removed DISTINCT
Line: 2838

SELECT	DISTINCT payroll_control_id
FROM	psp_summary_lines
WHERE	gms_batch_name = l_gms_batch_name;
Line: 2858

   SELECT distinct org_id
   FROM   psp_payroll_controls ppc,
   psp_summary_lines  psl
   WHERE  ppc.payroll_control_id = psl.payroll_control_id
   AND    ppc.adj_sum_batch_name = p_adj_sum_batch_name
   AND    ppc.source_type = 'A'
   AND    (ppc.dist_dr_amount IS NOT NULL OR ppc.dist_cr_amount IS NOT NULL)
   AND    ppc.gms_phase = 'Summarize_GMS_Lines'
   AND    ppc.status_code = 'I'
   AND	  ppc.business_group_id = p_business_group_id
   AND	  ppc.set_of_books_id = p_set_of_books_id
   AND    run_id = nvl(g_run_id, run_id)
   AND	  psl.status_code = 'N'
   AND    psl.gl_code_combination_id IS NULL;
Line: 2918

		SELECT 	to_char(psp_gms_batch_name_s.nextval)
		INTO 	gms_batch_name_tab(i)
		FROM 	dual;
Line: 2924

   SELECT	to_char(psp_gms_batch_name_s.nextval)
   INTO		l_gms_batch_name
   FROM		DUAL;
Line: 2949

		 UPDATE psp_summary_lines
		 SET gms_batch_name = gms_batch_name_tab(i)                   --  l_gms_batch_name
		 WHERE payroll_control_id = gms_batch_rec.payroll_control_id
		 AND   status_code = 'N'
		 AND   gl_code_combination_id IS NULL
		 AND    org_id = org_id_tab(i);			-- R12 MOAC uptake
Line: 2959

		 -- update psp_summary_lines with gms batch name
		 UPDATE psp_summary_lines
		 SET gms_batch_name = gms_batch_name_tab(i)                   --  l_gms_batch_name
		 WHERE payroll_control_id = gms_batch_rec.payroll_control_id
		 AND   status_code = 'N'
		 AND   gl_code_combination_id IS NULL
		 AND    org_id = org_id_tab(i);			-- R12 MOAC uptake
Line: 3143

     select pa_txn_interface_s.nextval
       into l_txn_interface_id
       from dual;
Line: 3177

       insert_into_pa_interface(l_txn_interface_id,
	L_TXN_SOURCE,L_GMS_BATCH_NAME,L_EXPENDITURE_ENDING_DATE,
	L_EMPLOYEE_NUMBER,L_ORG_NAME,GMS_INTERFACE_REC.EFFECTIVE_DATE,
	L_SEGMENT1,L_TASK_NUMBER,GMS_INTERFACE_REC.EXPENDITURE_TYPE,
	1,GMS_INTERFACE_REC.SUMMARY_AMOUNT,L_EXPENDITURE_COMMENT,
	'P',GMS_INTERFACE_REC.SUMMARY_LINE_ID, GMS_INTERFACE_REC.ORG_ID,
--	GMS_INTERFACE_REC.AWARD_ID, GMS_INTERFACE_REC.ATTRIBUTE2,
--	gms_attr are sent to gms_interface
	gms_batch_rec.currency_code, GMS_INTERFACE_REC.SUMMARY_AMOUNT,
	gms_interface_rec.attribute1, gms_interface_rec.attribute2, GMS_INTERFACE_REC.ATTRIBUTE3,
	gms_interface_rec.attribute4, GMS_INTERFACE_REC.ATTRIBUTE5,
	GMS_INTERFACE_REC.ATTRIBUTE6,GMS_INTERFACE_REC.ATTRIBUTE7,
	GMS_INTERFACE_REC.ATTRIBUTE8,GMS_INTERFACE_REC.ATTRIBUTE9,
	GMS_INTERFACE_REC.ATTRIBUTE10, gms_interface_rec.exchange_rate_type,
        GMS_INTERFACE_REC.ACCOUNTING_DATE, --- 3108109
	p_business_group_id, -- Introduced for the Bug fix 2935850
	L_RETURN_STATUS);
Line: 3200

		insert_into_pa_interface(l_txn_interface_id,
			L_TXN_SOURCE,L_GMS_BATCH_NAME,L_EXPENDITURE_ENDING_DATE,
			L_EMPLOYEE_NUMBER,L_ORG_NAME,GMS_INTERFACE_REC.EFFECTIVE_DATE,
			L_SEGMENT1,L_TASK_NUMBER,GMS_INTERFACE_REC.EXPENDITURE_TYPE,
			GMS_INTERFACE_REC.SUMMARY_AMOUNT, 1, L_EXPENDITURE_COMMENT,
			'P',GMS_INTERFACE_REC.SUMMARY_LINE_ID, GMS_INTERFACE_REC.ORG_ID,
			gms_batch_rec.currency_code, GMS_INTERFACE_REC.SUMMARY_AMOUNT,
			gms_interface_rec.attribute1, gms_interface_rec.attribute2,
			GMS_INTERFACE_REC.ATTRIBUTE3, gms_interface_rec.attribute4,
			GMS_INTERFACE_REC.ATTRIBUTE5, GMS_INTERFACE_REC.ATTRIBUTE6,
			GMS_INTERFACE_REC.ATTRIBUTE7, GMS_INTERFACE_REC.ATTRIBUTE8,
			GMS_INTERFACE_REC.ATTRIBUTE9, GMS_INTERFACE_REC.ATTRIBUTE10,
			gms_interface_rec.exchange_rate_type,
			GMS_INTERFACE_REC.ACCOUNTING_DATE,
			p_business_group_id,
			L_RETURN_STATUS);
Line: 3286

		update psp_payroll_controls
		   set gms_phase = 'Submitted_Import_Request'
	--	 where gms_phase = 'Transfer_GMS_Lines'
		   where payroll_control_id in
			(select distinct payroll_control_id
			   from psp_summary_lines
			  where gms_batch_name = l_gms_batch_name);
Line: 3305

		UPDATE	psp_payroll_controls
		SET	gms_phase = 'Submitted_Import_Request'
		WHERE	payroll_control_id = r_payroll_controls.payroll_control_id(I);
Line: 3310

		r_payroll_controls.payroll_control_id.DELETE;
Line: 3358

   SELECT distinct batch_name
   FROM   psp_payroll_controls
   WHERE  adj_sum_batch_name = p_adj_sum_batch_name
   AND    (dist_dr_amount is not null and dist_cr_amount is not null)
   AND    source_type = 'A'
   AND    status_code = 'I'
   AND    gms_phase = 'Submitted_Import_Request'
   AND	  business_group_id = p_business_group_id
   AND	  set_of_books_id = p_set_of_books_id
   AND    run_id = nvl(g_run_id, run_id);
Line: 3372

   SELECT payroll_control_id,
          source_type,
          payroll_source_code,
          time_period_id,
          batch_name
   FROM   psp_payroll_controls
   WHERE  batch_name = p_batch_name
   AND    (dist_dr_amount is not null and dist_cr_amount is not null)
   AND    source_type = 'A'
   AND    status_code = 'I'
   AND    gms_phase = 'Submitted_Import_Request'
   AND    run_id = nvl(g_run_id, run_id);
Line: 3388

   SELECT summary_line_id,
          dr_cr_flag,summary_amount
   FROM   psp_summary_lines
   WHERE  gms_batch_name = p_gms_batch_name
   AND	  payroll_control_id = p_payroll_control_id;
Line: 3396

   SELECT nvl(transaction_rejection_code,'P'),
          orig_transaction_reference,
          transaction_status_code
   FROM   pa_transaction_interface_all
   WHERE  batch_name = to_char(p_gms_batch_name)
   AND	  transaction_status_code in ('R', 'PO', 'PI', 'PR')
   AND	  transaction_source = p_txn_src
   AND	  orig_transaction_reference in
	 (select to_char(summary_line_id)
	    from psp_summary_lines
	   where payroll_control_id = p_payroll_control_id
	     and gms_batch_name = p_gms_batch_name);
Line: 3448

   x_update_count		number(9);
Line: 3449

   x_insert_count		number(9);
Line: 3450

   x_delete_count		number(9);
Line: 3475

   SELECT	DISTINCT gms_batch_name
   FROM		psp_summary_lines
   WHERE	payroll_control_id = gms_tie_back_rec.payroll_control_id
   AND		gms_batch_name IS NOT NULL;
Line: 3481

   SELECT	transaction_source
   FROM		pa_transaction_interface_all
   WHERE	batch_name = TO_CHAR(l_gms_batch_name);
Line: 3486

   SELECT	count(*)
   FROM		pa_transaction_interface_all
   WHERE	batch_name = TO_CHAR(l_gms_batch_name)
   AND		transaction_source = l_txn_source
   AND		transaction_status_code IN ('R', 'PO', 'PI', 'PR')
   AND		orig_transaction_reference IN	(SELECT	TO_CHAR(summary_line_id)
						FROM	psp_summary_lines
						WHERE	payroll_control_id = gms_tie_back_rec.payroll_control_id
						AND	gms_batch_name = l_gms_batch_name);
Line: 3498

   select count(*), transaction_status_code
     from pa_transaction_interface_all
    where batch_name = to_char(l_gms_batch_name)
      and transaction_source = l_txn_source
      and transaction_status_code in ('P', 'I')
    group by transaction_status_code  ;
Line: 3508

         select expenditure_group
                from pa_expenditures_all
                where expenditure_id in
                     (select expenditure_id
                      from pa_transaction_interface_all
                      where transaction_source in ('OLD', 'GOLD') and
                            transaction_rejection_code is null and
                            batch_name = l_gms_batch_name )
               and expenditure_group is not null;
Line: 3541

     delete from pa_transaction_interface_all
      where batch_name = to_char(l_gms_batch_name)
	and transaction_source = l_txn_source;
Line: 3546

     delete from gms_transaction_interface_all
      where batch_name = to_char(l_gms_batch_name)
	and transaction_source = 'GOLD';
Line: 3552

     delete from psp_summary_lines
      where gms_batch_name = l_gms_batch_name
	and payroll_control_id = gms_tie_back_rec.payroll_control_id;
Line: 3557

     update psp_payroll_controls
       set gms_phase = 'Summarize_GMS_Lines'
     where gms_phase is not null
      and  adj_sum_batch_name = p_adj_sum_batch_name;
Line: 3572

    /* Added the delete statements for EXP, EXP items, EXP groups etc for bug 2133056 */
     OPEN group_name_cur;
Line: 3579

       delete gms_award_distributions
         where  document_type = 'EXP'
           and  expenditure_item_id in
            ( select expenditure_item_id
              from pa_expenditure_items_all
              where transaction_source = 'GOLD'
                and expenditure_id in
               (select expenditure_id
                from  pa_expenditures_all
                where expenditure_group = l_exp_group_name));
Line: 3590

        delete pa_expenditure_items_all
         where transaction_source in ('OLD','GOLD')
           and expenditure_id in
              (select expenditure_id
               from pa_expenditures_all
               where expenditure_group = l_exp_group_name);
Line: 3597

        delete pa_expenditures_all
               where expenditure_group = l_exp_group_name;
Line: 3600

        delete pa_expenditure_Groups_all
         where expenditure_group = l_exp_group_name;
Line: 3605

         delete gms_transaction_interface_all
           where transaction_source in ('GOLD') and
                 batch_name = l_gms_batch_name;
Line: 3610

         delete pa_transaction_interface_all
          where transaction_source in ('GOLD','OLD') and
                 batch_name = l_gms_batch_name;
Line: 3614

         delete psp_summary_lines
         where payroll_control_id in
         (select payroll_control_id
          from psp_payroll_controls
          where source_type = 'A'
            and adj_sum_batch_name = p_adj_sum_batch_name);
Line: 3621

       update psp_payroll_controls
       set gms_phase = null
       where gms_phase is not null
        and source_type = 'A'
        and  adj_sum_batch_name = p_adj_sum_batch_name;
Line: 3739

        /* 1685685  added update statement...to clean pa tables in cleanup_batch_details..Venkat.*/
        update psp_payroll_controls
          set gms_phase = 'GMS_Tie_Back'
        where payroll_control_id = gms_tie_back_rec.payroll_control_id;
Line: 3753

      UPDATE psp_summary_lines  PSL
       SET (PSL.status_code, PSL.expenditure_ending_date,PSL.expenditure_id,
               PSL.interface_id,PSL.expenditure_item_id,PSL.txn_interface_id)  =
            (select 'A', PTXN.expenditure_ending_date,PTXN.expenditure_id,
               PTXN.interface_id,PTXN.expenditure_item_id,PTXN.txn_interface_id
             from pa_transaction_interface_all PTXN
             where PTXN.transaction_source = l_txn_source
               and PTXN.orig_transaction_reference= to_char(PSL.summary_line_id)
               and PTXN.batch_name = l_gms_batch_name1)
       WHERE PSL.gms_batch_name = l_gms_batch_name1;   --- changed g_gms_batch_name to l_gms_batch_name for 2444657
Line: 3779

   	  UPDATE psp_payroll_controls
      	     SET gms_phase = 'GMS_Tie_Back'
           WHERE payroll_control_id = l_control_id;
Line: 3795

         UPDATE psp_adjustment_lines
         SET status_code = 'A' WHERE summary_line_id = l_summary_line_id;
Line: 3799

         INSERT INTO psp_adjustment_lines_history
         (adjustment_line_id,person_id,assignment_id,element_type_id,
          distribution_date,effective_date,distribution_amount,
          dr_cr_flag,payroll_control_id,source_type,source_code,time_period_id,
          batch_name,status_code,set_of_books_id,gl_code_combination_id,project_id,
          expenditure_organization_id,expenditure_type,task_id,award_id,
          suspense_org_account_id,suspense_reason_code,effort_report_id,version_num,
          summary_line_id, reversal_entry_flag, original_line_flag, user_defined_field, percent,
	  orig_source_type,
          orig_line_id,attribute_category,attribute1,attribute2,attribute3,attribute4,
          attribute5,attribute6,attribute7,attribute8,attribute9,attribute10,attribute11,
          attribute12,attribute13,attribute14,attribute15,last_update_date,
          last_updated_by,last_update_login,created_by,creation_date, business_group_id,
          adj_set_number, line_number)  ---   added cols 2634557 DA Multiple element Enh
         SELECT adjustment_line_id,person_id,assignment_id,element_type_id,
          distribution_date,effective_date,distribution_amount,
          dr_cr_flag,payroll_control_id,source_type,source_code,time_period_id,
          batch_name,status_code,set_of_books_id,gl_code_combination_id,project_id,
          expenditure_organization_id,expenditure_type,task_id,award_id,
          suspense_org_account_id,suspense_reason_code,effort_report_id,version_num,
          summary_line_id, reversal_entry_flag, original_line_flag, user_defined_field, percent,
 	  orig_source_type,
          orig_line_id,attribute_category,attribute1,attribute2,attribute3,attribute4,
          attribute5,attribute6,attribute7,attribute8,attribute9,attribute10,attribute11,
          attribute12,attribute13,attribute14,attribute15,SYSDATE,FND_GLOBAL.USER_ID,
          FND_GLOBAL.LOGIN_ID,FND_GLOBAL.USER_ID,SYSDATE, business_group_id,
          adj_set_number, line_number  ---   added cols 2634557 DA Multiple element Enh
         FROM psp_adjustment_lines
         WHERE status_code = 'A'
         AND summary_line_id = l_summary_line_id
	 AND payroll_control_id = gms_tie_back_rec.payroll_control_id;
Line: 3831

         DELETE FROM psp_adjustment_lines
         WHERE status_code = 'A'
         AND summary_line_id = l_summary_line_id
	 AND payroll_control_id = gms_tie_back_rec.payroll_control_id;
Line: 3837

          DELETE FROM pa_transaction_interface_all
	  WHERE orig_transaction_reference = to_char(l_summary_line_id)
	    AND transaction_status_code = 'A'
	    AND transaction_source = l_txn_source; */
Line: 3844

     UPDATE psp_payroll_controls
     SET ogm_dr_amount = nvl(ogm_dr_amount,0) + l_dr_summary_amount,
         ogm_cr_amount = nvl(ogm_cr_amount,0) + l_cr_summary_amount
     WHERE payroll_control_id = gms_tie_back_rec.payroll_control_id;
Line: 3876

 PROCEDURE insert_into_pa_interface(
	P_INTERFACE_ID			IN	NUMBER,
	P_TRANSACTION_SOURCE		IN	VARCHAR2,
	P_BATCH_NAME			IN	VARCHAR2,
	P_EXPENDITURE_ENDING_DATE	IN	DATE,
	P_EMPLOYEE_NUMBER		IN	VARCHAR2,
	P_ORGANIZATION_NAME		IN	VARCHAR2,
	P_EXPENDITURE_ITEM_DATE		IN	DATE,
	P_PROJECT_NUMBER		IN	VARCHAR2,
	P_TASK_NUMBER			IN	VARCHAR2,
	P_EXPENDITURE_TYPE		IN	VARCHAR2,
	P_QUANTITY			IN	NUMBER,
	P_RAW_COST			IN	NUMBER,
	P_EXPENDITURE_COMMENT		IN	VARCHAR2,
	P_TRANSACTION_STATUS_CODE	IN	VARCHAR2,
	P_ORIG_TRANSACTION_REFERENCE	IN	VARCHAR2,
	P_ORG_ID			IN	NUMBER,
	P_DENOM_CURRENCY_CODE		IN	VARCHAR2,
	P_DENOM_RAW_COST		IN	NUMBER,
	P_ATTRIBUTE1			IN	VARCHAR2,
	P_ATTRIBUTE2			IN	VARCHAR2,
	P_ATTRIBUTE3			IN	VARCHAR2,
	P_ATTRIBUTE4			IN	VARCHAR2,	-- Introduced attributes 4,5 for bug fix 2908859
	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_ACCT_RATE_TYPE		IN	VARCHAR2,	-- Introduced for bug fix 2916848
	P_ACCT_RATE_DATE		IN	DATE,		-- Introduced for bug fix 2916848
	P_PERSON_BUSINESS_GROUP_ID	IN	NUMBER,		-- Introduced for Bug fix 2935850
	P_RETURN_STATUS			OUT NOCOPY	VARCHAR2) IS
	l_msg_id	number(9);
Line: 3920

   INSERT INTO PA_TRANSACTION_INTERFACE_ALL(
	TXN_INTERFACE_ID,
	TRANSACTION_SOURCE,
	BATCH_NAME,
	EXPENDITURE_ENDING_DATE,
	EMPLOYEE_NUMBER,
	ORGANIZATION_NAME,
	EXPENDITURE_ITEM_DATE,
	PROJECT_NUMBER,
	TASK_NUMBER,
	EXPENDITURE_TYPE,
	QUANTITY,
	RAW_COST,
	EXPENDITURE_COMMENT,
	TRANSACTION_STATUS_CODE,
	ORIG_TRANSACTION_REFERENCE,
	ORG_ID,
	DENOM_CURRENCY_CODE,
	DENOM_RAW_COST,
	ATTRIBUTE1,
	ATTRIBUTE2,
	ATTRIBUTE3,
	ATTRIBUTE4,			-- Introduced attributes 4,5 for bug fix 2908859
	ATTRIBUTE5,
	ATTRIBUTE6,
	ATTRIBUTE7,
	ATTRIBUTE8,
	ATTRIBUTE9,
	ATTRIBUTE10,
	PERSON_BUSINESS_GROUP_ID,
--	Introduced the following columns for bug fix 2916848
	ACCT_RATE_TYPE,
	ACCT_RATE_DATE,
	UNMATCHED_NEGATIVE_TXN_FLAG)
   VALUES(
	P_INTERFACE_ID,
	P_TRANSACTION_SOURCE,
	P_BATCH_NAME,
	P_EXPENDITURE_ENDING_DATE,
	P_EMPLOYEE_NUMBER,
	P_ORGANIZATION_NAME,
	P_EXPENDITURE_ITEM_DATE,
	P_PROJECT_NUMBER,
	P_TASK_NUMBER,
	P_EXPENDITURE_TYPE,
	P_QUANTITY,
	P_RAW_COST,
	P_EXPENDITURE_COMMENT,
	P_TRANSACTION_STATUS_CODE,
	P_ORIG_TRANSACTION_REFERENCE,
	P_ORG_ID,
	P_DENOM_CURRENCY_CODE,
	P_DENOM_RAW_COST,
	P_ATTRIBUTE1,
	P_ATTRIBUTE2,
	P_ATTRIBUTE3,
	P_ATTRIBUTE4,			-- Introduced attributes 4,5 for bug fix 2908859
	P_ATTRIBUTE5,
	P_ATTRIBUTE6,
	P_ATTRIBUTE7,
	P_ATTRIBUTE8,
	P_ATTRIBUTE9,
	P_ATTRIBUTE10,
	P_PERSON_BUSINESS_GROUP_ID,
--	Introduced the following columns for bug fix 2916848
	P_ACCT_RATE_TYPE,
        DECODE(p_acct_rate_type, NULL, NULL, P_ACCT_RATE_DATE),
	l_unmatched_nve_txn_flag);
Line: 3994

      g_error_api_path := 'INSERT_INTO_PA_INTERFACE:'||g_error_api_path;
Line: 3995

      fnd_msg_pub.add_exc_msg('PSP_SUM_ADJ','INSERT_INTO_PA_INTERFACE');
Line: 4010

select orig_source_type,
       orig_line_id
from psp_adjustment_lines pal,
     psp_payroll_controls ppc
WHERE   ppc.source_type          ='A'
AND     ppc.batch_name           = p_batch_name
AND     pal.payroll_control_id   = ppc.payroll_control_id
AND     pal.reversal_entry_flag IS NULL
UNION ALL -- added hist table for 2133056
SELECT palh.orig_source_type,
       palh.orig_line_id
FROM   psp_adjustment_lines_history palh,
       psp_payroll_controls  ppc
WHERE  palh.reversal_entry_flag IS NULL
AND    ppc.source_type          ='A'
AND    ppc.batch_name           = p_batch_name
AND    palh.payroll_control_id  = ppc.payroll_control_id
AND  palh.reversal_entry_flag IS NULL;
Line: 4033

   SELECT	gl_phase, gms_phase, batch_name
   FROM		psp_payroll_controls
   WHERE	payroll_control_id = p_payroll_control_id;
Line: 4039

         select expenditure_group
                from pa_expenditures_all
                where expenditure_id in
                     (select expenditure_id
                      from pa_transaction_interface_all
                      where transaction_source in ('OLD', 'GOLD') and
                            transaction_status_code = 'A' and
--                            batch_name = g_gms_batch_name and
			      batch_name IN (SELECT gms_batch_name FROM psp_summary_lines WHERE  payroll_control_id = p_payroll_control_id) and
                            rownum = 1);
Line: 4070

         delete gms_award_distributions
         where  document_type = 'EXP'
           and  expenditure_item_id in
            (select expenditure_item_id
            from pa_expenditure_items_all
            where transaction_source = 'GOLD'
               and orig_transaction_reference in
             (select to_char(summary_line_id)
                   from psp_summary_lines
                    where payroll_control_id in
                     (select payroll_control_id
                      from psp_payroll_controls
                       where batch_name = l_batch_name
                        and source_type='A')));  ----- Added this delete for Bug 2133056
Line: 4086

	delete from pa_expenditure_items_all
	 where transaction_source in ('OLD','GOLD')
	   and orig_transaction_reference in
        	(select to_char(summary_line_id)
		   from psp_summary_lines
 		    where payroll_control_id in
		     (select payroll_control_id
		      from psp_payroll_controls
		       where batch_name = l_batch_name
		        and source_type='A'));    --Added for bug 1685685
Line: 4099

  delete gms_transaction_interface_all
    where transaction_source in ('GOLD') and
          batch_name = g_gms_batch_name and
          orig_transaction_reference in
                (select to_char(summary_line_id)
                   from psp_summary_lines
                  where payroll_control_id in
                        (select payroll_control_id
                           from psp_payroll_controls
                          where batch_name = l_batch_name
                             and source_type = 'A'));
Line: 4113

 delete pa_expenditures_all EXP
    where EXP.expenditure_id in
        (select XFACE.expenditure_id
         from pa_transaction_interface_all XFACE
          where XFACE.transaction_source in('OLD','GOLD')
          and XFACE.orig_transaction_reference in
          (select to_char(PSL.summary_line_id)
           from psp_summary_lines PSL
              where PSL.payroll_control_id in
                (select PPC.payroll_control_id
                  from psp_payroll_controls PPC
                   where PPC.batch_name=l_batch_name
                    and PPC.source_type='A')))
       and 0 = (select count(*)
                from pa_expenditure_items_all ITEMS
                where ITEMS.expenditure_id = EXP.expenditure_id);
Line: 4132

  delete pa_transaction_interface_all
       where transaction_source in ('OLD','GOLD') and
             batch_name = g_gms_batch_name and
            orig_transaction_reference in
                (select to_char(summary_line_id)
                   from psp_summary_lines
                  where payroll_control_id in
                        (select payroll_control_id
                           from psp_payroll_controls
                          where batch_name = l_batch_name
                           and source_type = 'A'));
Line: 4145

    delete pa_expenditure_groups_all
    where transaction_source in('OLD','GOLD')
     and (0) = (select count(*) from pa_expenditures_all where expenditure_group=l_exp_group_name)
     and expenditure_group = l_exp_group_name;
Line: 4163

	delete from gl_interface
	 where user_je_source_name = l_user_je_source_name
	   and group_id  = p_group_id ;
Line: 4168

        delete from gl_interface_control
        where je_source_name = l_user_je_source_name
          and group_id = p_group_id;
Line: 4182

	 update psp_distribution_lines_history
	    set adjustment_batch_name = NULL
	  where distribution_line_id = adj_batch_rec.orig_line_id;
Line: 4186

	 update psp_pre_gen_dist_lines_history
	    set adjustment_batch_name = NULL
	  where pre_gen_dist_line_id = adj_batch_rec.orig_line_id;
Line: 4190

	 update psp_adjustment_lines_history
	    set adjustment_batch_name = NULL
	  where adjustment_line_id = adj_batch_rec.orig_line_id;
Line: 4196

   delete from psp_adjustment_lines_history
    where payroll_control_id in        --- Corrected this statment 2133056
           ( SELECT ppc.payroll_control_id
          FROM   psp_payroll_controls ppc
          WHERE  ppc.batch_name = l_batch_name
          AND    ppc.source_type = 'A');
Line: 4204

   delete from psp_adjustment_lines
    where batch_name = l_batch_name;
Line: 4207

   delete from psp_summary_lines
    where payroll_control_id in (select payroll_control_id
				  from psp_payroll_controls
				 where batch_name = l_batch_name
                                   and source_type = 'A'); --- added this condn 2133056
Line: 4213

   delete from psp_payroll_controls
    where batch_name = l_batch_name and
          source_type = 'A';       --- added this condn 2133056
Line: 4217

   delete from psp_adjustment_control_table
    where adjustment_batch_name = l_batch_name;
Line: 4229

SELECT distinct batch_name
  FROM psp_payroll_controls
 WHERE source_type = 'A'
   AND adj_sum_batch_name = p_adj_sum_batch_name
   AND status_code = 'I';
Line: 4236

SELECT payroll_control_id
  FROM psp_payroll_controls
 WHERE batch_name = p_batch_name
   AND status_code = 'I'
   AND decode(p_target_name, 'GL', gl_phase, gms_phase) = 'Submitted_Import_Request'
   AND source_type = 'A';
Line: 4255

   SELECT	MAX(group_id)
   FROM		psp_summary_lines
   WHERE	payroll_control_id = payroll_control_rec.payroll_control_id
   AND		group_id IS NOT NULL;
Line: 4261

   SELECT	count(*)
   FROM		gl_interface
   WHERE	group_id = l_group_id
   AND		user_je_source_name = 'OLD'
   AND		status = 'NEW';
Line: 4268

   SELECT	MAX(gms_batch_name)
   FROM		psp_summary_lines
   WHERE	payroll_control_id = payroll_control_rec.payroll_control_id
   AND		gms_batch_name IS NOT NULL;
Line: 4274

   SELECT	count(*)
   FROM		pa_transaction_interface
   WHERE	batch_name = TO_CHAR(l_gms_batch_name)
   AND		transaction_status_code = 'P'
   AND          transaction_source in ('OLD','GOLD');   --- Added condn for Bug 2133056
Line: 4281

   SELECT	count(*)
   FROM		pa_transaction_interface_all
   WHERE	batch_name = TO_CHAR(l_gms_batch_name)
   AND          transaction_source in ('OLD','GOLD')
   AND		transaction_status_code = 'I';          --- Added condn for Bug 2133056
Line: 4325

	 update psp_payroll_controls
            set gl_phase = 'Summarize_GL_Lines'
	  where gl_phase = 'Submitted_Import_Request'
	    and payroll_control_id = payroll_control_rec.payroll_control_id;
Line: 4330

          delete from gl_interface
           where group_id = l_group_id
             and user_je_source_name = 'OLD';
Line: 4334

          delete from gl_interface_control
           where group_id = l_group_id
             and je_source_name = 'OLD';
Line: 4364

	    update psp_payroll_controls
		 set gms_phase = NULL
	     where payroll_control_id = payroll_control_rec.payroll_control_id
	       and gms_phase = 'Submitted_Import_Request';
Line: 4369

	   delete from pa_transaction_interface_all
	    where batch_name = to_char(l_gms_batch_name)
	      and transaction_source in ('GOLD', 'OLD');
Line: 4373

	   delete from gms_transaction_interface_all
	    where batch_name = to_char(l_gms_batch_name)
	      and transaction_source = 'GOLD';
Line: 4377

	   delete from psp_summary_lines
	    where payroll_control_id = payroll_control_rec.payroll_control_id
	      and gms_batch_name = l_gms_batch_name;
Line: 4435

   SELECT	adjustment_batch_name, person_id, assignment_id, element_type_id,
		distribution_start_date, distribution_end_date,
		currency_code	-- Introduced for bug fix 2916848
   FROM		psp_adjustment_control_table
   WHERE	adjustment_batch_name = p_batch_name;
Line: 4442

   SELECT	substr(full_name, 1, 80)
   FROM		per_people_f ppf1
   WHERE	person_id = l_person_id
   AND		trunc(sysdate) BETWEEN effective_start_date and effective_end_date ;
Line: 4449

   SELECT	assignment_number
   FROM		per_all_assignments_f paf1
   WHERE	assignment_id = l_assignment_id
   AND		effective_start_date =	(SELECT	MAX(effective_start_date)
					FROM	per_all_assignments_f paf2
					WHERE	paf2.assignment_id = l_assignment_id
					AND	paf2.effective_start_date < trunc(sysdate));
Line: 4458

   SELECT	element_name
   FROM		pay_element_types_f pet1
   WHERE	element_type_id = l_element_type_id
   AND		effective_start_date =	(SELECT	MAX(effective_start_date)
					FROM	pay_element_types_f pet2
					WHERE	pet2.element_type_id = l_element_type_id
					AND	pet2.effective_start_date < trunc(sysdate));
Line: 4556

 PROCEDURE insert_into_psp_stout(
	P_MSG			IN	VARCHAR2) IS
	l_msg_id	number(9);
Line: 4560

   SELECT PSP_STOUT_S.NEXTVAL
    INTO l_msg_id
    FROM DUAL;
Line: 4564

   INSERT INTO PSP_STOUT(
	MSG_ID,
	MSG)
   VALUES(
	l_msg_id,
	P_MSG);