SET SERVEROUTPUT ON  SIZE 100000
SET TERMOUT ON
SET VERIFY OFF
WHENEVER SQLERROR EXIT FAILURE ROLLBACK; 

DECLARE

	ABORT_ERROR			exception;		/* Temporary variables used exclusively within */
	error_msg			varchar2(200);		/* this program.			       */
	gl_date				date;
	gl_date_source			varchar2(30);
	format_has_lb			varchar2(1);
	check_unique_t_name		number;
	check_report_format		varchar2(1);
	temp				number;
	spawn_flag			char(1) := 'Y';
	last_request_id			number;
	bool				boolean;
	phase				varchar2(30);
	status				varchar2(30);
	dev_phase			varchar2(30);
	dev_status			varchar2(30);
	message				varchar2(30);
	
        t_request_id                    number ;		/* Temporary variables used to store incoming */
	t_transmission_request_id	number ;		/* and outgoing parameters.		      */
	t_transmission_name		varchar2(50);
	t_code_net_trans_yn		varchar2(1) ;
	t_net_transmission_name		varchar2(50);
	t_transmission_id		number ;
	t_code_run_import_yn		varchar2(1) ;
	t_file_name			varchar2(50) ;
	t_control_file			varchar2(50) ;
	t_transmission_format_id	number ;
	t_code_run_validation_yn	varchar2(1) ;
	t_code_pay_unrelated_inv_yn     varchar2(1) ;
	t_lockbox_id			number ;
	t_gl_date			Date ;
	t_code_report_format		varchar2(30);
	t_code_complete_batches_yn	varchar2(1) ;
	t_code_run_appl_yn		varchar2(1) ;

	CURSOR cur_trans IS
	  SELECT transmission_id,
                 transmission_request_id,
		 requested_gl_date,
		 latest_request_id
          FROM ar_transmissions
          WHERE transmission_name = t_transmission_name;
 

BEGIN 

	/* Populate variables from the SRS report submission sreen */

	t_transmission_name		:= '&&1'  ;
        t_code_net_trans_yn             := '&&2'  ; /*  */
	t_net_transmission_name		:= '&&3'  ;	
	t_file_name       		:= '&&4'  ;
	t_control_file 			:= '&&5'  ;
	t_transmission_format_id    	:= '&&6'  ;
        t_code_run_import_yn            := '&&7'  ;
	t_code_pay_unrelated_inv_yn     := '&&8'  ;
	t_lockbox_id			:= '&&9'  ;
	t_gl_date                       := '&&10' ;
	t_code_report_format            := '&&11' ;
	t_code_complete_batches_yn      := '&&12' ;
        t_code_run_validation_yn        := '&&13' ;
	t_code_run_appl_yn		:= '&&14' ;

        DBMS_OUTPUT.PUT_LINE('=============SUBMIT PROCESS LOCKBOX LOG ==================');
	DBMS_OUTPUT.PUT_LINE('');
	DBMS_OUTPUT.PUT_LINE('Verifying transmission name....');

	IF t_code_net_trans_yn = 'Y'
   	  THEN SELECT count(*)
               INTO check_unique_t_name
               FROM AR_TRANSMISSIONS
               WHERE transmission_name = t_net_transmission_name;

               IF check_unique_t_name != 0 or t_net_transmission_name is NULL
	 	 THEN SELECT 'LOCKBOX' || to_char(t_gl_date,'DDMONRR') || AR_TRANSMISSIONS_S.nextval 
		      INTO t_net_transmission_name
		      FROM dual;

		      /* Check again to make sure newly created name is indeed unique. */
		      SELECT count(*)
               	      INTO check_unique_t_name
               	      FROM AR_TRANSMISSIONS
               	      WHERE transmission_name = t_net_transmission_name;

		      /* If it's still not unique, then abort program. */
               	      IF check_unique_t_name != 0
			THEN error_msg := 'Problems creating New Transmission Name.  Cannot continue.';
                      	     RAISE ABORT_ERROR;
			ELSE DBMS_OUTPUT.PUT_LINE('Created New Transmission Name:  ' || t_net_transmission_name);
               	      END IF;
	       END IF;

	       t_transmission_name := t_net_transmission_name;

	  ELSE 
		select transmission_name into t_transmission_name 
		from ar_transmissions 
		where transmission_id = (Select max(Transmission_id) from ar_transmissions);

		OPEN cur_trans;
	       FETCH cur_trans INTO t_transmission_id, t_transmission_request_id, gl_date, last_request_id;
	       IF cur_trans%NOTFOUND 
		 THEN error_msg := 'Cannot retrieve transmission information.'; /* This condition can occur */
		      RAISE ABORT_ERROR;					/* when an old transmission */
	       END IF;								/* is, somehow, no longer   */
	       CLOSE cur_trans;  						/* in the system.           */
	END IF;

	DBMS_OUTPUT.PUT_LINE('Verifying Transmission Format...');

	IF t_code_net_trans_yn = 'Y' and t_code_run_import_yn = 'N'	
	  THEN IF t_transmission_format_id is not NULL
		 THEN DBMS_OUTPUT.PUT_LINE('* Warning:  Transmission Format will not be used. *');
		      t_transmission_format_id := '';
	       END IF;
	  ELSE IF (t_code_net_trans_yn = 'N' and t_code_run_import_yn = 'N') 
		 THEN DECLARE
			temp_format_id number;
		      BEGIN
			SELECT t.requested_trans_format_id
			INTO temp_format_id
			FROM ar_transmissions t, ar_transmission_formats tf
			WHERE t.transmission_request_id = t_transmission_request_id
			AND t.requested_trans_format_id = tf.transmission_format_id
			AND tf.status_lookup_code = 'A';
	
			IF temp_format_id != nvl(t_transmission_format_id,temp_format_id)
			  THEN DBMS_OUTPUT.PUT_LINE('* Warning:  Specified Transmission Format ' ||
						    'will be ignored.  Program will use ' ||
						    'Transmission Format that correspond to ' ||
						    'specified Old Transmission Name.');
			END IF;

			t_transmission_format_id := temp_format_id;
		      EXCEPTION
			WHEN NO_DATA_FOUND 
			  THEN error_msg := 'Specified Transmission is no longer active.';
			       RAISE ABORT_ERROR;
		      END;

		 ELSE IF t_code_run_import_yn = 'Y'
			THEN DECLARE 
			       temp varchar2(15);
		 	     BEGIN
			       SELECT 'ACTIVE'
			       INTO temp
			       FROM ar_transmission_formats
			       WHERE transmission_format_id = t_transmission_format_id
			       AND status_lookup_code = 'A';
			     EXCEPTION
			       WHEN NO_DATA_FOUND 
				 THEN error_msg := 'Specified Transmission is no longer active.';
				      RAISE ABORT_ERROR;
			     END;
		       END IF;
	       END IF;
        END IF;

	DBMS_OUTPUT.PUT_LINE('Verifying Submit Import and Data/Control File parameters....');

        IF t_transmission_format_id is NULL				/* If it's not an old transmission, */
          THEN format_has_lb := 'N';					/* this implies a locbox does not   */
          ELSE SELECT count(*)						/* already exists. 		    */
               INTO temp						/* If it is an old transmission,    */
               FROM AR_TRANS_FIELD_FORMATS FLD,				/* still need to check that it has  */
                    AR_TRANS_RECORD_FORMATS REC				/* a lockbox specified already.     */
               WHERE  REC.TRANSMISSION_FORMAT_ID = t_transmission_format_id
               AND    REC.RECORD_TYPE_LOOKUP_CODE IN ( 'LB HDR', 'LB TRL' )
               AND    REC.RECORD_FORMAT_ID = FLD.RECORD_FORMAT_ID
               AND    FLD.FIELD_TYPE_LOOKUP_CODE = 'LB NUM';

               IF temp = 0
                 THEN format_has_lb := 'N';
                 ELSE format_has_lb := 'Y';
               END IF;
        END IF;

	/* If validation is Yes and lockbox does not exists for the specified transmission, then the lockbox */
	/* parameter must be entered or else program will abort.					     */
	IF t_code_run_validation_yn = 'Y' 
	  THEN IF format_has_lb = 'N'
		 THEN IF t_lockbox_id is NULL
			THEN error_msg := 'Lockbox must be specified.  Please re-submit.'; 
			     RAISE ABORT_ERROR;
		      END IF;

		      BEGIN
		        SELECT gl_date_source			/* Check that lockbox is active. */
		        INTO gl_date_source			/* Also, get GL date source.     */
		        FROM ar_lockboxes
		        WHERE lockbox_id = t_lockbox_id
		        AND status = 'A';
			IF gl_date_source != 'DEP'		/* If GL date source is 'DEP',   */
			  THEN check_report_format := 'N';      /* then make sure transmission   */
			  ELSE SELECT count(*)			/* has a deposit date associated */
			       INTO temp			/* with it.			 */
			       FROM ar_trans_field_formats
			       WHERE transmission_format_id = t_transmission_format_id
			       AND field_type_lookup_code = 'DEPOSIT DATE'; 
			       IF temp = 0
				 THEN error_msg := 'Specified lockbox requires a deposit date in the ' ||
						   'transmission format.';
				      RAISE ABORT_ERROR;
			       END IF;
			END IF;
		      EXCEPTION
			WHEN NO_DATA_FOUND THEN
			  error_msg := 'Specified lockbox is not active.  Please re-submit with an ' ||
				       'active lockbox.';
			  RAISE ABORT_ERROR;
		      END;

	       END IF;

	/* Check report format is not null when Submit Validation is Yes. */
	       IF nvl(check_report_format,'Y') = 'Y'
		 THEN DBMS_OUTPUT.PUT_LINE('Verifying Report Format parameter....');
	       	      IF t_code_report_format is NULL
	                THEN error_msg := 'Must specify Report Format when Submit Validation is set to Yes.';
	              	     RAISE ABORT_ERROR;
		      END IF;
	       END IF;

	/* Correctly sets the Allow Payment of Unrelated Invoices parameter according to installations. */
               DBMS_OUTPUT.PUT_LINE('Verifying Allow Payment of Unrelated Invoices parameter....');
	       SELECT count(*) 
	       INTO temp 
  	       FROM AR_SYSTEM_PARAMETERS
	       WHERE pay_unrelated_invoices_flag = 'Y';
	       IF temp = 0 and nvl(t_code_pay_unrelated_inv_yn,'Y') != 'N'     
 		 THEN DBMS_OUTPUT.PUT_LINE('* Warning:  Allow Payment of Unrelated Invoices parameter will ' ||
					   'be set to No according to installation setups.');
		      t_code_pay_unrelated_inv_yn := 'N';
	       END IF;

	/* Verify that if GL date is required, GL date parameter should not be blank. */
	       IF t_code_net_trans_yn = 'N' and t_gl_date is NULL
		 THEN t_gl_date := gl_date;
	       END IF;
               DBMS_OUTPUT.PUT_LINE('Verifying GL Date parameter....'); 
	       IF gl_date_source = 'CON' AND t_gl_date is NULL
		 THEN error_msg := 'A GL date must be entered for this transmission.  Please re-submit.';
		      RAISE ABORT_ERROR;
	       END IF; 

	/* Check that GL date is not in a closed period. */
	       IF t_gl_date is NOT NULL
		 THEN SELECT count(*)
		      INTO temp
		      FROM ar_system_parameters s, gl_period_statuses p
		      WHERE p.closing_status in ('O','F')
		      AND trunc(t_gl_date) between trunc(p.start_date) and trunc(p.end_date)
		      AND p.application_id = 222
		      AND p.set_of_books_id = s.set_of_books_id;
		      IF temp = 0
			THEN error_msg := 'Please enter a GL date that is in an open or future enterable ' ||
					  'period.';
			     RAISE ABORT_ERROR;
		      END IF;
	       END IF;

	/* If validation is no, then warn user that some parameters might not be used because they are not */
	/* applicable.  Reset those parameters to NULL.							   */
	  ELSE IF t_gl_date is not NULL
		 THEN t_gl_date := '';
	       END IF;
	       IF t_code_pay_unrelated_inv_yn is not NULL
                 THEN DBMS_OUTPUT.PUT_LINE('* Warning:  Allow Payment of Unrelated Invoices parameter is ' ||
					   'not applicable here.  Will not be used.');
                      t_code_pay_unrelated_inv_yn := '';
               END IF;
	       IF t_code_complete_batches_yn is not NULL
                 THEN t_code_complete_batches_yn := '';
               END IF;
	       IF t_lockbox_id is not NULL
                 THEN DBMS_OUTPUT.PUT_LINE('* Warning:  Lockbox is not applicable here.  Will not be used.');
                      t_lockbox_id := '';
               END IF;
	       IF t_code_report_format is not NULL
                 THEN DBMS_OUTPUT.PUT_LINE('* Warning:  Report Format is not applicable here.  Will not be used.');
		      t_code_report_format := '';
               END IF;
	END IF;

	IF t_code_run_validation_yn = 'Y' and t_code_run_import_yn = 'N' and t_code_net_trans_yn = 'N'
	  THEN DECLARE
		 parent_id number;
	       BEGIN
		 SELECT c2.parent_request_id				/* Get parent id to check if report set */
		 INTO parent_id						/* is still running.  If not, then      */
		 FROM fnd_concurrent_requests c2			/* don't need to proceed with check.    */
		 WHERE c2.request_id = (SELECT c1.parent_request_id
				        FROM fnd_concurrent_requests c1
				        WHERE c1.request_id = t_transmission_request_id);

		 IF parent_id is NOT NULL
		   THEN bool := FND_CONCURRENT.GET_REQUEST_STATUS(parent_id,
                                                         	  '',
                                                         	  '',
                                                         	  phase,
                                                         	  status,
                                                         	  dev_phase,
                                                         	  dev_status,
                                                         	  message);
			IF phase != 'Completed' and bool = TRUE
			  THEN SELECT count(*)
			       INTO temp 
			       FROM AR_PAYMENTS_INTERFACE
			       WHERE transmission_request_id = t_transmission_request_id;

			       IF temp = 0			/* Make sure that records exist in */
				 THEN spawn_flag := 'N';	/* AR_PAYMENTS_INTERFACE.  If not, */
			       END IF;				/* then set spawn flag to No.	   */
			END IF;
		 END IF;
	       EXCEPTION
		 WHEN NO_DATA_FOUND THEN NULL;
	       END;
	END IF;

	END IF;


EXCEPTION
	WHEN ABORT_ERROR THEN DBMS_OUTPUT.PUT_LINE('Program aborted.');
			      RAISE_APPLICATION_ERROR(-20000,error_msg);
End;
/
