Wednesday 16 November 2011

PAYROLL-(CUSTOM)

--|  FILENAME                                 
--|    gljirun_cust.sql                               
--|                                    
--|  PURPOSE                                
--|    Submit Journal Import using standard report submission method for APIL payroll process.
--|
--|                                     
--|  NOTES                                
--|    The script expects 6 parameters
--|      1. Data Access Set Id    -----   Given in the script
--|      2. source                -----   Constant value (Payroll) is given in the concurrent program
--|      3. ledger id             -----   Given in the script
--|      4. group id              ------  Given in the concurrent program
--|      5. error_to_suspense flag  --------   Given in the concurrent program
--|      6. create_summary flag     --------    Given in the concurrent program
--|      7. import descriptive flexfield flag     --------    Given in the concurrent program
--|  
--|  The below script is developed and modified by MG on 20-05-2011
--+==========================================================================
SET VERIFY OFF
WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
WHENEVER OSERROR EXIT FAILURE ROLLBACK;
DECLARE
  conc_request_id    NUMBER;
  irun_id            NUMBER; 
  group_id           NUMBER;
  errbuf           VARCHAR2(2000);
  orig_req_id        NUMBER;
  num_copies       NUMBER;
  print_style      VARCHAR2(100);
  printer          VARCHAR2(100);
  save_output_flag VARCHAR2(10);
  save_output_bool BOOLEAN;
 
  p_set_of_book_id NUMBER(15);
  p_access_set_id  NUMBER(15);
 
  CURSOR c_ledger_id IS
  SELECT distinct ledger_id
    FROM gl_interface    
   WHERE user_je_category_name = 'Payroll'
     AND status = 'NEW'; 
         
BEGIN 
  fnd_profile.get('CONC_REQUEST_ID', errbuf);
  orig_req_id := to_number(errbuf);
 
  IF (fnd_concurrent.get_request_print_options(orig_req_id,
                                               num_copies,
                                               print_style,
                                               printer,
                                  save_output_flag)) THEN
    IF (save_output_flag = 'Y') THEN
      save_output_bool := TRUE;
    ELSE
      save_output_bool := FALSE;
    END IF;
    IF (NOT fnd_request.set_print_options(printer,
                                          print_style,
                                          num_copies,
                                          save_output_bool)) THEN
      fnd_file.put_line(FND_FILE.Log,'Failed to set print options');
    END IF;
  END IF; 
 
 FOR i in c_ledger_id LOOP

  SELECT gl_journal_import_s.NEXTVAL
  INTO irun_id
  FROM DUAL;      
 
  p_set_of_book_id := i.ledger_id;    
    
  INSERT INTO gl_interface_control
  (status, set_of_books_id, je_source_name, group_id, interface_run_id)
  VALUES
  ('S',p_set_of_book_id, '&2', to_number('&4'), irun_id);
 
SELECT access_set_id
  INTO p_access_set_id
  FROM gl_access_sets
 WHERE default_ledger_id=p_set_of_book_id;
  ---- Launch the concurrent request
  conc_request_id
    := fnd_request.submit_request(
         'SQLGL',
         'GLLEZL',
         '',
         '',
         FALSE,
         to_char(irun_id),     
         to_number(p_access_set_id),
         '&5',
         '',
         '',
         '&6',
         '&7',
         'Y',
     chr(0),
         '', '', '', '', '', '', '', '', '', '',
         '', '', '', '', '', '', '', '', '', '',
         '', '', '', '', '', '', '', '', '', '',
         '', '', '', '', '', '', '', '', '', '',
         '', '', '', '', '', '', '', '', '', '',
         '', '', '', '', '', '', '', '', '', '',
         '', '', '', '', '', '', '', '', '', '',
         '', '', '', '', '', '', '', '', '', '',
         '', '', '', '', '', '', '', '', '', '',
         '');
  IF (conc_request_id = 0) THEN
    errbuf := fnd_message.get;
    fnd_file.put_line(FND_FILE.Log, errbuf);
    gl_message.write_log('LEZS0001');
    IF (fnd_concurrent.set_completion_status('ERROR', errbuf)) THEN
      NULL;
    END IF;
  ELSE
    gl_message.write_log('LEZS0000', 1, 'REQ_ID', to_char(conc_request_id));
    IF (fnd_concurrent.set_completion_status('NORMAL', NULL)) THEN
      NULL;
    END IF;
  END IF;
 
END LOOP;    

END;
/
COMMIT;
EXIT;

No comments:

Post a Comment