undefine v_headerinfo
Define   v_headerinfo =  '$Header: DATAFIX_58239C.sql 1.0  01-AUG-2015 support $'
undefine v_testlongname
Define   v_testlongname = 'AR Date Update Script for Case 58239'

REM   =========================================================================
REM   Created By: William Green
REM   =========================================================================
REM   PURPOSE:           Change the GL_DATE filed of the credit memos to 08/01/2015
REM   PRODUCT:           Accounts Receivable (AR)
REM   PRODUCT VERSIONS:   11.5.10.2
REM   PLATFORM:          Generic
REM   PARAMETERS:        None 
REM   =========================================================================


REM   =========================================================================
REM   USAGE: 	sqlplus apps/password @DATAFIX_58239C.sql
REM   EXAMPLE: 
REM   OUTPUT:   The script will create an output file in text format named 
REM		  		DATAFIX_58239C.txt. Open the file in a text editor and 
REM		  		review the output
REM   =========================================================================


REM   =========================================================================
REM   CHANGE HISTORY:
REM   02-AUG-2015   William Green  Created
REM     
REM   =========================================================================

REM  ================SQL PLUS Environment setup================================
set serveroutput on size 1000000
set verify off
set feedback off
set linesize 132
set head on
set pagesize 100

REM ============== Define SQL Variables for input parameters ==================
clear columns

REM ============= Accept other Input Parameters ===============================

set serveroutput on verify off linesize 132 

-- Setup system

set serveroutput on verify off linesize 120 head off
set null 'Not Defined'

-- Undefine Statements

--undefine p_variable1

--setting variables

--prompt
--accept p_variable1 prompt 'Enter the variable1 value: '
--prompt

--getting vendor sites 
prompt Receipts to Be Corrected:
set heading on
COLUMN cash_receipt_id FORMAT A15
COLUMN receipt_number FORMAT A15
SELECT a.cash_receipt_id, b.receipt_number
FROM AR.AR_RECEIVABLE_APPLICATIONS_ALL a, AR.AR_CASH_RECEIPTS_ALL b
WHERE a.cash_receipt_id = b.cash_receipt_id
	AND a.posting_control_id = - 3
	AND a.gl_date BETWEEN to_date('01-JUL-2015', 'DD-MON-YYYY')
		AND to_date('31-JUL-2015', 'DD-MON-YYYY')
	AND NVL(a.postable, 'Y') = 'Y'
	AND NVL(a.org_id, - 99) = 1
	AND ROWNUM <= 3
ORDER BY a.cash_receipt_id;
set heading off

REM ============ Spooling the output file======================================
Define v_spoolfilename  = 'DATAFIX_58239C._OUTPUT.txt'

PROMPT
PROMPT Running.....
PROMPT 
spool  &v_spoolfilename

REM =================Run the Pl/SQL api file ==================================
--@@CoreApiTxt.sql

DECLARE 

v_cash_receipt_id  		NUMBER;
v_unapp_acct			NUMBER;
v_rec_acct				NUMBER;
v_group_id				NUMBER;
v_amount				NUMBER;
v_conf_src_id			NUMBER;
v_remit_src_id			NUMBER;
v_rec_src_id			NUMBER;
v_third_party_sub_id	NUMBER;
v_third_party_id		NUMBER;
v_dr_line_id			NUMBER;
v_cr_line_id			NUMBER;
v_user_id				NUMBER := -999;
v_date					DATE := sysdate;

l_sql_debug				varchar2(1000);
l_block_debug			varchar2(1000) := 'Declaration';
l_glint_cnt				number := 0;
l_restore_cnt			number := 0;
l_dist_cnt				number := 0;
l_crh_cnt				number := 0;
l_recapp_cnt			number := 0;
l_tot_cnt               number := 0;

CURSOR getReceipts
  IS	 
	SELECT cash_receipt_id
	FROM AR.AR_RECEIVABLE_APPLICATIONS_ALL
	WHERE posting_control_id = - 3
		AND gl_date BETWEEN to_date('01-JUL-2015', 'DD-MON-YYYY')
			AND to_date('31-JUL-2015', 'DD-MON-YYYY')
		AND NVL(postable, 'Y') = 'Y'
		AND NVL(org_id, - 99) = 1
		AND ROWNUM <= 3
	ORDER BY cash_receipt_id
BEGIN

dbms_output.put_line(chr(9));
l_block_debug := 'Beginning of the AR Credit Memo Date Update Script C';
dbms_output.put_line('Beginning of the AR Credit Memo Date Update Script C');
dbms_output.put_line(chr(9));
dbms_output.put_line(chr(9));

l_block_debug := 'Starting loop through receipts';
dbms_output.put_line('Starting loop through receipts');
dbms_output.put_line(chr(9));

--main attachments loop 
For rec in getReceipts loop 	
	v_group_id := null;
	l_block_debug := 'Processing cash recipt ID: '||rec.cash_receipt_id;
	dbms_output.put_line('Processing cash recipt ID: '||rec.cash_receipt_id);
	dbms_output.put_line(chr(9));
	
	l_block_debug := 'Updating AR_CASH_RECEIPT_HISTORY_ALL';
	dbms_output.put_line('Updating AR_CASH_RECEIPT_HISTORY_ALL');
	
	update AR.AR_CASH_RECEIPT_HISTORY_ALL set 
		gl_date = TO_DATE('08/01/2015','MM/DD/YYYY'),
		last_updated_by = v_user_id,
		last_update_date = v_date
	where cash_receipt_id = rec.cash_receipt_id
	and posting_control_id = -3;
	
	l_block_debug := 'Updating AR_DISTRIBUTIONS_ALL';
	dbms_output.put_line('Updating AR_DISTRIBUTIONS_ALL');
	
	update AR.AR_DISTRIBUTIONS_ALL set 
		gl_date = TO_DATE('08/01/2015','MM/DD/YYYY'),
		last_updated_by = v_user_id,
		last_update_date = v_date
	where posting_control_id = -3
	and source_id in (
		select cash_receipt_history_id
		from AR.AR_CASH_RECEIPT_HISTORY_ALL
		where cash_receipt_id = rec.cash_receipt_id
	);	
	
	l_block_debug := 'Updating AR_RECEIVABLE_APPLICATIONS_ALL';
	dbms_output.put_line('Updating AR_RECEIVABLE_APPLICATIONS_ALL');
	
	update AR.AR_RECEIVABLE_APPLICATIONS_ALL set 
		gl_date = TO_DATE('08/01/2015','MM/DD/YYYY'),
		last_updated_by = v_user_id,
		last_update_date = v_date
	where cash_receipt_id = rec.cash_receipt_id
	and posting_control_id = -3;	
	
	l_tot_cnt := l_tot_cnt + 1;

end loop;

dbms_output.put_line(chr(9));
l_block_debug := 'Completed loop through receipts';
dbms_output.put_line('Completed loop through receipts');
dbms_output.put_line(chr(9));

dbms_output.put_line(chr(9));
l_block_debug := 'Printing totals';
dbms_output.put_line('Total Records updated or restored: '||l_tot_cnt);
dbms_output.put_line(chr(9));

dbms_output.put_line(chr(9));
l_block_debug := 'End of the AR Credit Memo Date Update Script C';
dbms_output.put_line('End of the AR Credit Memo Date Update Script C');
dbms_output.put_line(chr(9));

commit;

EXCEPTION 

  When others then
      dbms_output.put_line(chr(9));
      dbms_output.put_line(sqlerrm ||' error occurred in Block: '||l_block_debug||'<br>for SQL: '||l_sql_debug);
      dbms_output.put_line('Please report the above error to Support');
      dbms_output.put_line(chr(9));	

END;
/

REM  ==============SQL PLUS Environment setup===================
Spool off
set term on

PROMPT
PROMPT	************      NOTE: THE PROCEDURE IS NOT COMPETE      *************
PROMPT  =======================================================================
PROMPT  Please review the output file:  &v_spoolfilename
PROMPT	All inserted records are identified by LAST_UPDATED_BY and CREATED_BY
PROMPT  set to -999 and CREATION_DATE and LAST_UPDATE_DATE fields set to SYSDATE
PROMPT  =======================================================================
PROMPT