GL PERIOD STATUS CODE QUERY IN ORACLE APPS

 SELECT sob.name "Set of Books" ,ps.application_id,

fnd.product_code "Product Code" ,

ps.PERIOD_NAME "Period Name" ,

ps.START_DATE "Period Start Date" ,

ps.END_DATE "Period End Date" ,

DECODE(ps.closing_status, 'O','O - Open' ,

                          'N','N - Never Opened' ,

                          'F','F - Future Enterable' ,

                          'C','C - Closed' ,'Unknown') "Period Status"

FROM apps.gl_period_statuses ps ,

apps.GL_SETS_OF_BOOKS sob ,

apps.fnd_application fnd

WHERE 1=1--ps.application_id   =101 -- GL 

AND sob.SET_OF_BOOKS_ID       = ps.SET_OF_BOOKS_ID

AND fnd.application_id        = ps.application_id

AND ps.adjustment_period_flag = 'N'

AND (TRUNC(SYSDATE) -- Comment line if a a date other than SYSDATE is being tested.

  BETWEEN TRUNC(ps.start_date) AND TRUNC (ps.end_date))

ORDER BY ps.SET_OF_BOOKS_ID,

  fnd.product_code,

  ps.start_date;

HOW TO SEND A MAIL FROM UTL_SMTP OR FROM BACK END

HOW TO SEND A MAIL FROM UTL_SMTP OR FROM BACK END :



create or replace FUNCTION xxx_popmd_upd_email_notif (p_po_number         VARCHAR2,
                                                      p_po_line           NUMBER,
                                                      p_po_shipment_num   NUMBER,
                                                      p_po_release_num    NUMBER,
                                                      p_po_promissed_date VARCHAR2,
                                                      p_justification     VARCHAR2)
   RETURN VARCHAR2
AS

   v_mail_conn       utl_smtp.connection;
   v_mail_host       VARCHAR2 (30);
   v_port_no         NUMBER;
   v_to_recipients   apps.alr_distribution_lists.to_recipients%TYPE;
   v_cc_recipients   apps.alr_distribution_lists.cc_recipients%TYPE;
   v_mail_subject    VARCHAR2 (3000);
   v_from            VARCHAR2 (80) := 'OracleMailerTest@xxx.com';
   crlf              VARCHAR2 (2) := CHR (13) ||CHR (10);
   v_employee_name   apps.fnd_user.user_name%TYPE;
   v_cp_date         VARCHAR2 (30);
   v_user_id         NUMBER := fnd_profile.value ('USER_ID');
   v_req_id          NUMBER := fnd_global.conc_request_id;
   l_cc              VARCHAR2 (3000);
   v_cc              VARCHAR2 (3000);
   l_to              VARCHAR2 (2000);
   l_tmp             VARCHAR2 (3000);
   n                 NUMBER;
BEGIN
   SELECT to_recipients,
          cc_recipients
     INTO v_to_recipients,
          v_cc_recipients
     FROM apps.alr_distribution_lists
    WHERE name = 'ORIGINAL_PROMISE_DATE_UPDATE';

   dbms_output.put_line ('Email Distribution List: ' || v_to_recipients);

   SELECT DISTINCT NVL (pf.full_name, fu.user_name)
     INTO v_employee_name
     FROM per_all_people_f pf,
          fnd_user fu
    WHERE fu.employee_id = pf.person_id(+)
      AND user_id = v_user_id;

   dbms_output.put_line ('Update by User Name: ' || v_employee_name);

    SELECT NVL (TO_CHAR (actual_start_date, 'DD-MON-YYYY HH24:MI:SS'), sysdate)
      INTO v_cp_date
      FROM fnd_concurrent_requests fcr
     WHERE request_id = v_req_id;

   dbms_output.put_line ('Update by date: ' || v_cp_date);

   SELECT SUBSTR (v.profile_option_value, 1, instr (v.profile_option_value, ':') - 1),
          to_number (SUBSTR (v.profile_option_value, 20, 2))
     INTO v_mail_host,
          v_port_no
     FROM fnd_profile_option_values v,
          fnd_profile_options o
    WHERE v.profile_option_id = o.profile_option_id
      AND o.profile_option_name = 'XX_CUS_TOP_EMAIL_SMTP';

   l_cc := v_to_recipients;

   LOOP
      EXIT WHEN l_cc IS NULL;

      v_mail_subject := 'Original Promise Date Update - ' || p_po_number || ' by ' || v_employee_name;
      v_mail_conn := utl_smtp.open_connection (v_mail_host, v_port_no);

      utl_smtp.helo (v_mail_conn, v_mail_host);
      utl_smtp.mail (v_mail_conn, v_from);

      n := instr (l_cc, ',');

      --IF n =0 THEN exit; end if;
      l_tmp := SUBSTR (l_cc, 1, n - 1);

      IF n = 0 THEN
         l_tmp := l_cc;
         l_cc := NULL;
      END IF;

      dbms_output.put_line ('Output1: ' || l_tmp);
      dbms_output.put_line ('Output2: ' || l_cc);

      --utl_smtp.rcpt( l_tmp );
      utl_smtp.rcpt (v_mail_conn, l_tmp);
      utl_smtp.data (v_mail_conn,  'Date: '                            || TO_CHAR (sysdate, 'Dy, DD Mon YYYY hh24:mi:ss')
                        || crlf || 'From: '                            || v_from
                        || crlf || 'Subject: '                         || v_mail_subject
                        || crlf || 'To: '                              || v_to_recipients
                        || crlf || 'Cc: '                              || v_cc_recipients
                        || crlf || 'PO Number: '                       || p_po_number
                        || crlf || 'Line Number: '                     || p_po_line
                        || crlf || 'Shipment Number: '                 || p_po_shipment_num
                        || crlf || 'Release Number: '                  || p_po_release_num
                        || crlf || 'New Original Promised Date: '      || p_po_promissed_date
                        || crlf || 'Justification: '                   || p_justification|| crlf
                        || crlf || 'Updated By    :' ||v_employee_name
                        || crlf || 'Date Updated  :' ||v_cp_date|| crlf);

      l_cc := SUBSTR (l_cc, n + 1);

      dbms_output.put_line ('Output3: ' || l_cc);

      utl_smtp.quit (v_mail_conn);
   END LOOP;

   v_cc:=v_cc_recipients;
    LOOP
      EXIT WHEN v_cc IS NULL;

      v_mail_subject := 'Original Promise Date Update - ' || p_po_number || ' by ' || v_employee_name;
      v_mail_conn := utl_smtp.open_connection (v_mail_host, v_port_no);

      utl_smtp.helo (v_mail_conn, v_mail_host);
      utl_smtp.mail (v_mail_conn, v_from);

      n := instr (v_cc, ',');

      --IF n =0 THEN exit; end if;
      l_tmp := SUBSTR (v_cc, 1, n - 1);

      IF n = 0 THEN
         l_tmp := v_cc;
         v_cc := NULL;
      END IF;

      dbms_output.put_line ('Output1: ' || l_tmp);
      dbms_output.put_line ('Output2: ' || v_cc);

      --utl_smtp.rcpt( l_tmp );
      utl_smtp.rcpt (v_mail_conn, l_tmp);
      utl_smtp.data (v_mail_conn,  'Date: '                            || TO_CHAR (sysdate, 'Dy, DD Mon YYYY hh24:mi:ss')
                        || crlf || 'From: '                            || v_from
                        || crlf || 'Subject: '                         || v_mail_subject
                        || crlf || 'To: '                              || v_to_recipients
                        || crlf || 'Cc: '                              || v_cc_recipients
                        || crlf || crlf || 'PO Number:  '              || p_po_number
                        || crlf || 'Line Number: '                     || p_po_line
                        || crlf || 'Shipment Number: '                 || p_po_shipment_num
                        || crlf || 'Release Number: '                  || p_po_release_num
                        || crlf || 'New Original Promised Date: '      || p_po_promissed_date
                        || crlf || 'Justification: '                   || p_justification|| crlf
                        || crlf || 'Updated By    :' ||v_employee_name
                        || crlf || 'Date Updated  :' ||v_cp_date|| crlf);

      v_cc := SUBSTR (v_cc, n + 1);

      dbms_output.put_line ('Output3: ' || v_cc);

      utl_smtp.quit (v_mail_conn);
   END LOOP;

   RETURN ('Notification Sent');
EXCEPTION
   WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
      raise_application_error ( - 20000, 'Unable to Send Mail', true);
   WHEN OTHERS THEN
      raise_application_error ( - 20001, 'Other Errors: '||sqlerrm||';'||SQLCODE);
END xxx_popmd_upd_email_notif;

passing multiple values for a single parameter in oracle apps Reports

passing multiple values for a single parameter in oracle apps Reports:

Here Comma delimited Multiple values parameter considering as a single sting .

Example : If passing parameter value as 'ABC,ABCD' This entire sting consider as a single string but i want to use this as a comma delimited like in ('ABC','ABCD')

By using below Example it could be Rectified.


 SELECT * FROM XX_TABLE WHERE XX_COLUMN in (SELECT REGEXP_SUBSTR (value,'[^, ]+',1,LEVEL) value
        FROM (SELECT 'ABC,ABCD' value FROM dual)
                  connect BY REGEXP_SUBSTR (value,'[^, ]+',1,LEVEL) IS NOT NULL) --ABC,ABCD

In XML Report Date format zero's are missing (How to get zero values in XML Report)

By using this Below tag in RTF Template we will disply the zero starting and ending dates and number valued tags.


Here Tag is Creation_date.

Normally if the value for this tag is 03-JAN-2010,when you run the XML report by using Excel output the value will come like 3-jan-10.

by using below one overriding tag we will get expected output like 03-JAN-2010.

<fo:bidi-override direction="ltr" unicode-bidi="bidi-override"><?CREATION_DATE?></fo:bidi-override>

How to attach RTF Layout to the Concurrent Program From Back End:

Using submit_request will only submits the program and will not attach any layout or print option.
Code below will help to set XML publisher template/layout along with print option.

NOTE: Add_layout and Add_printer procedures are optional in calling submit_request. Use only if you need to set them.

Layout is submitted to a concurrent request using below procedure

fnd_request.add_layout (
                    template_appl_name   => 'Template Application',
                    template_code        => 'Template Code',
                    template_language    => 'en', --Use language from template definition
                    template_territory   => 'US', --Use territory from template definition
                    output_format        => 'PDF' --Use output format from template definition
                     );


Setting printer while submitting concurrent program

fnd_submit.set_print_options (printer      => lc_printer_name
                                   ,style        => 'PDF Publisher'
                                   ,copies       => 1
                                   );

fnd_request.add_printer (
                    printer => printer_name,
                    copies  => 1);



DECLARE
   lc_boolean        BOOLEAN;
   ln_request_id     NUMBER;
   lc_printer_name   VARCHAR2 (100);
   lc_boolean1       BOOLEAN;
   lc_boolean2       BOOLEAN;
BEGIN

      -- Initialize Apps
      fnd_global.apps_initialize (>USER_ID<
                                 ,>RESP_ID<
                                 ,>RESP_APPL_ID<
                                 );
   -- Set printer options
   lc_boolean :=
      fnd_submit.set_print_options (printer      => lc_printer_name
                                   ,style        => 'PDF Publisher'
                                   ,copies       => 1
                                   );
   --Add printer
   lc_boolean1 :=
                fnd_request.add_printer (printer      => lc_printer_name
                                         ,copies       => 1);
  --Set Layout
  lc_boolean2 :=
               fnd_request.add_layout (
                            template_appl_name   => 'Template Application',
                            template_code        => 'Template Code',
                            template_language    => 'en', --Use language from template definition
                            template_territory   => 'US', --Use territory from template definition
                            output_format        => 'PDF' --Use output format from template definition
                                    );
   ln_request_id :=
      fnd_request.submit_request ('FND',                -- application
                                  'COCN_PGM_SHORT_NAME',-- program short name
                                  '',                   -- description
                                  '',                   -- start time
                                  FALSE,                -- sub request
                                  'Argument1',          -- argument1
                                  'Argument2',          -- argument2
                                  'N',                  -- argument3
                                  NULL,                 -- argument4
                                  NULL,                 -- argument5
                                  'Argument6',          -- argument6
                                  CHR (0)               -- represents end of arguments
                                 );
   COMMIT;

   IF ln_request_id = 0
   THEN
      dbms.output.put_line ('Concurrent request failed to submit');
   END IF;
END;

Coma Delimiter sub string Program example for Reports

DECLARE
L_STRING VARCHAR2(400):='1,12,123,1234,12345,0,12';
begin
IF L_STRING IS NOT NULL THEN

SELECT LENGTH(L_STRING) into l_length FROM DUAL;

select substr(L_STRING,2,l_length-2) into L_STRING from dual;
srw.message(4,'P_SOURCE_LIN :'||L_STRING);

select (length(L_STRING) - length(replace(L_STRING, ',')))  INTO L_COUNT from dual;
srw.message(5,'l_count :'||l_count);
L_COUNT:=L_COUNT+1;
srw.message(6,'l_count :'||l_count);
FOR i IN 1..L_COUNT
loop

srw.message(8,'i  :'||i);
l_source:=null;
l_f:=0;
l_last:=0;
l_fir:=0;
l_sec:=0;
l_ccc:=0;
l_order_number:=0;
l_line_number:=0;

SELECT INSTR(L_STRING,',', 1,i) into l_f FROM DUAL;
srw.message(9,'l_f  :'||l_f);
if i=1 and l_f =0 then
select L_STRING into l_source FROM DUAL;
elsif i=1 then
SELECT substr(L_STRING, 1,l_f-1) into l_source FROM DUAL;
srw.message(10,'l_source  :'||l_source);

elsif i=L_COUNT and i<>1 then
SELECT INSTR(L_STRING, ',', 1,i-1) into l_last FROM DUAL;
select substr(L_STRING,l_last+1) into l_source FROM DUAL;
else
SELECT INSTR(L_STRING, ',', 1,i-1) into l_fir FROM DUAL;
SELECT INSTR(L_STRING, ',',1, i) into l_sec FROM DUAL;

SELECT substr(L_STRING, l_fir+1,l_sec-l_fir-1) into l_source FROM DUAL;
end if;
srw.message(7,'l_chr :'||l_source);


srw.message(12,'l_ccc :'||l_ccc);
end if;

end loop;

END IF;
END;

Concurrent Request Status Codes and Meaning Of Status Code's

Concurrent Request Status Codes and Meaning Of Status Code's:

Use below query to cancel all scheduled concurrent programs.

===================================================

UPDATE fnd_concurrent_requests

SET phase_code = 'C', status_code = 'X'

WHERE status_code IN ('Q','I')

AND requested_start_date > SYSDATE

AND hold_flag = 'N';

COMMIT;

=====================================================

TO cancel all running concurrent programs.

UPDATE fnd_concurrent_requests

SET phase_code = 'C', status_code = 'X'

WHERE status_code IN ('R','I');

Commit;

=====================================================

Concurrent Request Phase Codes:-

SELECT LOOKUP_CODE, MEANING

  FROM FND_LOOKUP_VALUES

 WHERE LOOKUP_TYPE = 'CP_PHASE_CODE' AND LANGUAGE = 'US'

       AND ENABLED_FLAG = 'Y';

To Cancel Specific request:

=====================================================

update fnd_concurrent_requests

set status_code='D', phase_code='C'

where request_id=:request_id;

LOOKUP_CODE MEANING

C             Completed

I               Inactive

P             Pending

R             Running

Concurrent Request Status Codes:-

SELECT LOOKUP_CODE, MEANING

  FROM FND_LOOKUP_VALUES

 WHERE LOOKUP_TYPE = 'CP_STATUS_CODE' AND LANGUAGE = 'US'

       AND ENABLED_FLAG = 'Y';

LOOKUP_CODE	MEANING
R	Normal
I	Normal
Z	Waiting
D	Cancelled
U	Disabled
E	Error
M	No Manager
C	Normal
H	On Hold
W	Paused
B	Resuming
P	Scheduled
Q	Standby
S	Suspended
X	Terminated
T	Terminating
A	Waiting
G	Warning

Normally a concurrent request proceeds through three, possibly four, life cycle stages or phases,

Phase Code	Meaning with Description
Pending	Request is waiting to be run
Running	Request is running
Completed	Request has finished
Inactive	Request cannot be run

                                               

                               

Within each phase, a request's condition or status may change. Below appears a listing of each phase and the various states that a concurrent request can go through.

The status and the description of each meaning given below:

PENDING	Normal	Request is waiting for the next available manager.
	Standby	Program to run request is incompatible with other program(s) currently running.
	Scheduled	Request is scheduled to start at a future time or date.
	Waiting	A child request is waiting for its Parent request to mark it ready to run. For example, a report in a report set that runs sequentially must wait for a prior report to complete.
RUNNING	Normal	Request is running normally.
	Paused	Parent request pauses for all its child requests to complete. For example, a report set pauses for all reports in the set to complete.
	Resuming	All requests submitted by the same parent request have completed running. The Parent request is waiting to be restarted.
	Terminating	Running request is terminated, by selecting Terminate in the Status field of   the Request Details zone.
COMPLETED	Normal	Request completes normally.
	Error	Request failed to complete successfully.
	Warning	Request completes with warnings. For example, a report is generated successfully but fails to print.
	Cancelled	Pending or Inactive request is cancelled, by selecting Cancel in the Status field of the Request Details zone.
	Terminated	Running request is terminated, by selecting Terminate in the Status field of   the Request Details zone.
INACTIVE	Disabled	Program to run request is not enabled. Contact your system administrator.
	On Hold	Pending request is placed on hold, by selecting Hold in the Status field of the Request Details zone.
	No Manager	No manager is defined to run the request. Check with your system administrator.