Thursday, November 1, 2012

How to Delete XML Publisher Definition and Template

How to Delete XML Publisher Definition and Template

 How often do you create a XML publisher definition with a wrong Codes (Template or Data Definition)? Or you want to change the Code so that it is more meaningful?

In the XML Publisher's OA Framework pages, both Template and Data Definition pages do not provide an option to delete anything. Moreover, the Template Code or Definition Code is not allowed to be updated.

The reason is that: concurrent program with XML output matches the Short Name with the template Code to find out which XML Publisher template to use for post processing. If you delete this template, the Post Processor cannot find the template, and then give errors.

You cannot change the Concurrent Program Short Name in the Form, and you cannot change the XML Template Code, and you cannot change the Data definition Code. If you make a typo in any one, disable it and create another one with the correct name. That's what Oracle suggests.

Come on...I WANT TO DELETE THEM, rather than recreating everything, and leave the wrong stuff in the system.

In another blog I show the way to delete concurrent program, and in here I will show you how to delete XML publisher template and the definition associated with this template. Change the parameters to fit your needs.

 PL/SQL CODE:

 


SET SERVEROUTPUT ON

DECLARE
   -- Change the following two parameters
   var_templateCode    VARCHAR2 (100) := 'SYMPLIK-TEST2';     -- Template Code
   boo_deleteDataDef   BOOLEAN := TRUE;     -- delete the associated Data Def.
BEGIN
   FOR RS
      IN (SELECT T1.APPLICATION_SHORT_NAME TEMPLATE_APP_NAME,
                 T1.DATA_SOURCE_CODE,
                 T2.APPLICATION_SHORT_NAME DEF_APP_NAME
            FROM XDO_TEMPLATES_B T1, XDO_DS_DEFINITIONS_B T2
           WHERE T1.TEMPLATE_CODE = var_templateCode
                 AND T1.DATA_SOURCE_CODE = T2.DATA_SOURCE_CODE)
   LOOP
      XDO_TEMPLATES_PKG.DELETE_ROW (RS.TEMPLATE_APP_NAME, var_templateCode);

      DELETE FROM XDO_LOBS
            WHERE     LOB_CODE = var_templateCode
                  AND APPLICATION_SHORT_NAME = RS.TEMPLATE_APP_NAME
                  AND LOB_TYPE IN ('TEMPLATE_SOURCE', 'TEMPLATE');

      DELETE FROM XDO_CONFIG_VALUES
            WHERE     APPLICATION_SHORT_NAME = RS.TEMPLATE_APP_NAME
                  AND TEMPLATE_CODE = var_templateCode
                  AND DATA_SOURCE_CODE = RS.DATA_SOURCE_CODE
                  AND CONFIG_LEVEL = 50;

      DBMS_OUTPUT.PUT_LINE ('Template ' || var_templateCode || ' deleted.');

      IF boo_deleteDataDef
      THEN
         XDO_DS_DEFINITIONS_PKG.DELETE_ROW (RS.DEF_APP_NAME,
                                            RS.DATA_SOURCE_CODE);

         DELETE FROM XDO_LOBS
               WHERE LOB_CODE = RS.DATA_SOURCE_CODE
                     AND APPLICATION_SHORT_NAME = RS.DEF_APP_NAME
                     AND LOB_TYPE IN
                            ('XML_SCHEMA',
                             'DATA_TEMPLATE',
                             'XML_SAMPLE',
                             'BURSTING_FILE');

         DELETE FROM XDO_CONFIG_VALUES
               WHERE     APPLICATION_SHORT_NAME = RS.DEF_APP_NAME
                     AND DATA_SOURCE_CODE = RS.DATA_SOURCE_CODE
                     AND CONFIG_LEVEL = 30;

         DBMS_OUTPUT.PUT_LINE (
            'Data Defintion ' || RS.DATA_SOURCE_CODE || ' deleted.');
      END IF;
   END LOOP;

   DBMS_OUTPUT.PUT_LINE (
      'Issue a COMMIT to make the changes or ROLLBACK to revert.');
EXCEPTION
   WHEN OTHERS
   THEN
      ROLLBACK;
      DBMS_OUTPUT.PUT_LINE (
         'Unable to delete XML Publisher Template ' || var_templateCode);
      DBMS_OUTPUT.PUT_LINE (SUBSTR (SQLERRM, 1, 200));
END;
/

Thursday, October 25, 2012

How to find first and last date of a month in SQL


How to find first and last date of a month in SQL

Working with dates is fun. In this post we are going to show a method to easily find the first and last date of the current monthprevious month, and next month.
Knowledge about this will come in handy if you i.e. want to do a year-over-year sales analysis, or maybe you would like to take the current month’s sales figures and compare them to what is budgeted for next month’s sale.
Depending on how your data is organized or partitioned, there are time you want to use the last date of a month in i.e. a BETWEEN statement in SQL.
Here is a query that selects the first and last date of  a month, for last monthcurrent month, and next month.

SELECT TRUNC (SYSDATE) today                                     -- Last Month,
       TO_DATE (TO_CHAR (ADD_MONTHS (SYSDATE, -1), 'YYYYMM'), 'YYYYMM')
          first_date_last_month,
       TO_DATE (TO_CHAR (ADD_MONTHS (SYSDATE, 0), 'YYYYMM'), 'YYYYMM') - 1
          last_date_last_month                                                   -- Current Month
                              ,
       TO_DATE (TO_CHAR (ADD_MONTHS (SYSDATE, 0), 'YYYYMM'), 'YYYYMM')
          first_date_this_month,
       TO_DATE (TO_CHAR (ADD_MONTHS (SYSDATE, +1), 'YYYYMM'), 'YYYYMM') - 1
          last_date_this_month                                                     -- Next Month
                              ,
       TO_DATE (TO_CHAR (ADD_MONTHS (SYSDATE, +1), 'YYYYMM'), 'YYYYMM')
          first_date_next_month,
       TO_DATE (TO_CHAR (ADD_MONTHS (:P_CURR_BEGIN_DATE, +2), 'YYYYMM'), 'YYYYMM') - 1
          last_date_next_month
  FROM DUAL;

Tuesday, October 9, 2012

Oracle Procedure to send emails to single or multiple receipients

Oracle Procedure to send emails to single or multiple recipients:

1. The below code is used to send email to single recipient using oracle Store Procedure:

 /********************************************************************************

           Function Name: sendmail_from_plsql
              Description: This function sends and Email through the system mail host
                           to the address in the "p_rcptaddr" input parameter.
             Input Params: p_sendaddr : Email address of the sender.
                           p_rcptaddr : Email address of the receipient.
                           p_ccaddr : Email address of the cc address.
                           p_subject: Subject title of the Email message.
                           p_body   : Body of the mail message.
           Output Params:  return boolean value, TRUE if sendmail successful else FALSE
               p_error_msg  - Error msg out, If there is error in sending mail
    ********************************************************************************/

FUNCTION sendmail_from_plsql (p_sendaddr    IN     VARCHAR2,
                                 p_rcptaddr    IN     VARCHAR2,
                                 p_ccaddr      IN     VARCHAR2,
                                 p_subject     IN     VARCHAR2,
                                 p_body        IN     VARCHAR2,
                                 p_error_msg      OUT VARCHAR2)
      RETURN BOOLEAN
   IS
   BEGIN
      mailhost := 'xxxxxxxxxx';-- give your mail host name over here
      mail_conn := UTL_SMTP.open_connection (mailhost, 25);
      UTL_SMTP.helo (mail_conn, mailhost);
      UTL_SMTP.mail (mail_conn, p_sendaddr);
      UTL_SMTP.rcpt (mail_conn, p_rcptaddr);

      IF p_ccaddr IS NOT NULL
      THEN
         UTL_SMTP.rcpt (mail_conn, p_ccaddr);
      END IF;

      v_mesg :=
            'Date: '
         || TO_CHAR (SYSDATE, 'dd Mon yy hh24:mi:ss')
         || v_crlf
         || 'From: '
         || p_sendaddr
         || v_crlf
         || 'To: '
         || p_rcptaddr
         || v_crlf
         || 'Cc: '
         || p_ccaddr
         || v_crlf
         || 'Subject: '
         || p_subject
         || v_crlf
         || p_body
         || v_crlf;
      UTL_SMTP.data (mail_conn, v_mesg);
      UTL_SMTP.quit (mail_conn);
      RETURN (TRUE);
   EXCEPTION
      WHEN OTHERS
      THEN
         p_error_msg := SQLERRM;
         RETURN (FALSE);
   END sendmail_from_plsql;


call the above procedure using the below code:

Here v_mail_sent must be a boolean datatype



DECLARE
   v_errmsg   VARCHAR2 (2000);
   v_true boolean;
BEGIN
  v_true:= ctar_utility_pkg.sendmail_from_plsql (' sender email_id',
                        'To email_id',
                        'CC email_id',
                        'Hello , this is a test mail',
                        'Hello , this is a test mail',
                        v_errmsg);
END;



2. The below code is used to send email to multiple recipients:



 FUNCTION address_email (p_string IN VARCHAR2, p_recipients IN array)
      RETURN VARCHAR2
   IS
      l_recipients   LONG;
   BEGIN
      FOR i IN 1 .. p_recipients.COUNT
      LOOP
         UTL_SMTP.rcpt (mail_conn, p_recipients (i));

         IF (l_recipients IS NULL)
         THEN
            l_recipients := p_string || p_recipients (i);
         ELSE
            l_recipients := l_recipients || ', ' || p_recipients (i);
         END IF;
      END LOOP;

      RETURN l_recipients;
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line (
            ' address_email  error message is  ' || SQLERRM);
   END address_email;
  /*********************************************************************************
         Function Name: sendmail_to_multireceipents
            Description: This function sends and Email through the system mail host
                         to multiple address in the "p_rcptaddr" input parameter.
           Input Params: p_sendaddr : Email address of the sender.
                         p_to : Long List of Email address of the receipients.
                         p_cc :Long list of Email address of the cc addresses.
                         p_bcc :Long list of Email address of the bcc addresses.
                         p_subject: Subject title of the Email message.
                         p_body   : Body of the mail message.
  *******************************************************************************/


   PROCEDURE sendmail_to_multireceipents (
      p_sendaddr   IN VARCHAR2,
      p_to         IN array DEFAULT array (),
      p_cc         IN array DEFAULT array (),
      p_bcc        IN array DEFAULT array (),
      p_subject    IN VARCHAR2,
      p_body       IN LONG)
   AS
      l_to_list    LONG;
      l_cc_list    LONG;
      l_bcc_list   LONG;

      PROCEDURE writeData (p_text IN VARCHAR2)
      AS
      BEGIN
         IF (p_text IS NOT NULL)
         THEN
            UTL_SMTP.write_data (mail_conn, p_text || v_crlf);
         END IF;
      END;

   BEGIN
      mailhost := 'XXXXX';          --       mail host name
      mail_conn := UTL_SMTP.open_connection (mailhost, 25);

      UTL_SMTP.helo (mail_conn, mailhost);
      UTL_SMTP.mail (mail_conn, p_sendaddr);
      l_to_list := address_email ('To: ', p_to);
      l_cc_list := address_email ('Cc: ', p_cc);
      UTL_SMTP.open_data (mail_conn);
      writeData ('Date: ' || execution_date);
      writeData ('From: ' || p_sendaddr);
      writeData ('Subject: ' || NVL (p_subject, '(no subject)'));


      writeData (l_to_list);
      writeData (l_cc_list);

      UTL_SMTP.write_data (mail_conn, '' || v_crlf);
      UTL_SMTP.write_data (mail_conn, p_body);
      UTL_SMTP.close_data (mail_conn);
      UTL_SMTP.quit (mail_conn);
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line (
            ' sendmail_to_multireceipents error message is  ' || SQLERRM);
   END sendmail_to_multireceipents;


Code to call the above procedure is :


BEGIN
   ctar_utility_pkg.sendmail_to_multireceipents (
      p_sendaddr   => 'Sender email id',
      p_to             => ctar_utility_pkg.array ('To email id's'),
      p_cc             =>  ctar_utility_pkg.array ('CC email id'),
      p_bcc            =>  NULL,
      p_subject        => 'Test Mail',
      p_body           => 'Hello , this is a test mail');
END;
/



Thursday, September 13, 2012

Creating FSG with beginning, debits, credits, and ending balance report


Creating FSG with beginning, debits, credits, and ending balance report

I have managed to get FSG debit and credit equal to trial balance - summary 1 . I need to calculate debit and credit on FSG first. Note that I was trying to make YTD report and this was done on column set.
Beginning Balance = BAL-Actual (FY Start) amount type.
For FSG Debit Column, I need to create 2 other column first. One for YTD-actual amount type and DR activity on account assignment, say A column. One for BAL-Actual (FY Start) amount type and DR activity on account assingment. This will be B column. Both are not shown. Then the real and shown debit column = A - B.
The same applies to Credit Column with CR activity.
Ending Balance will equal to Beginning Balance + Debit Column - Credit Column.

On the case of PTD report, I can use DR and CR activity directly. But I have to use YTD (-1 offset) on beginning balance and YTD on ending balance. There is no calculation on PTD report.

Friday, September 7, 2012

How to set APPS environment for newly created user in UNIX


How to set APPS environment for newly created user in UNIX


When your UNIX box user is created for Oracle apps instance. And if you are unable to change standard tops using environemnt variables such as $AU_TOP, $FND_TOP. You just follow the below steps to set the environment variables.
1. Login to UNIX box using your id
2. cd <application top path>
if you dont know the path, you can login to Oracle EBS application and get using these steps, open any form, Help->Diagnostics->Examine-> Choose Block "$ENVIRONMENT$", Field "FND_TOP" copy the value which is the path of FND_TOP
3. once you get the path, cd <path you copied>
4. change directory to reach $APPL_TOP path
5. Here you find the environemt file as below format
APPS<SID>_<hostname>.env
now, get the current path using pwd
copy the current path and environment file
ex: /opt/oracle/pub11demoappl/APPSPUBDEMO11i_mycomp.env
6. cd $HOME , you reach your home directory
7. vi .profile
8.reach the end of file
9. create a line as . environemt file with full path
ex:
. /opt/oracle/pub11demoappl/APPSPUBDEMO11i_mycomp.env   
dot(.) must be prefixed with a space to execute the file.
now save your .profile file using
:wq!
once your file is saved, exit and login to UNIX box using your user credentials
Once you login, you should be able to change directories such as $AU_TOP
try cd $AU_TOP and do pwd to see whether your directory is changed...

Setting up Forms60_Path in Registry


Setting up Forms60_Path in Registry

Setting up Forms60_Path in Registry

Copy all these PL/SQL libraries and the Template form in one folder.
Now we have to set the form60 path in the registry.
Go to Start -->Run -->Regedit -->HKEY_LOCAL_MACHINE -->Software -->Oracle
Here we can find the oracle homes. In these homes check for FORMS60_PATH.

Append the path of your folder where all the libraries and the form is present, prefixed with a semicolon(;).

E.g.: Before Appending “C:\forms\TOOLS\OPEN60\PLSQLLIB”
After Appending “C:\forms\TOOLS\OPEN60\PLSQLLIB;c:\forms\libraries”


Oracle has well-defined guidelines that must be followed when creating custom
forms in E-Business Suite. By following these guidelines, your custom forms will
look and feel exactly the same as standard forms, and your custom development
will remain upgrade safe. Effectively, when creating custom forms, the guidelines
and procedures followed by you are the same as those followed by Oracle’s product
development team. All the forms in Oracle E-Business Suite are based on
TEMPLATE.fmb. As with other forms in E-Business Suite, the TEMPLATE.fmb file can
be found in the $AU_TOP/forms/US directory.
The TEMPLATE form includes an example window with sample blocks, as well
as many referenced objects such as windows, canvases, blocks, Lists of Values
(LOVs), and other objects. It inherits the object groups from the APPSTAND form,
which is a platform-specific form. By starting out with the TEMPLATE form when
developing custom forms in E-Business Suite, developers ensure that they will get
the same look and properties of the standard product forms. Examples of such
properties specific to Oracle Applications are the toolbar, the menu, the calendar,
applications property classes, required Form Level triggers, required procedures,
LOVs, parameters, and many others.
Open TEMPLATE.fmb
In order to begin development, you must base your custom form development on
the TEMPLATE form (TEMPLATE.fmb). This ensures that your custom form will
inherit the shared core form components and libraries. The TEMPLATE form has
attached several libraries such as FNDSQF, APPCORE, APPDAYPF, and others that
contain many of the Application Object Library utilities.
Let us assume that you want to store all your FMB and PLL files in the folder C:\
code\forms on your Windows desktop. Transfer TEMPLATE.fmb from $AU_TOP/
forms/US from the middle-tier machine to the C:\code\forms directory on your
desktop. Now in Oracle Forms try to open TEMPLATE.fmb. You will get a message
“FRM-18108: Failed to load the following objects”. If you click on OK, you will get
another error listing the missing libraries (FRM-10102 errors). At this stage, you must
not save TEMPLATE.fmb once it is opened.
The reason we see those errors is because the TEMPLATE form itself depends on
other forms and libraries. Before you begin to transfer all the dependent objects from
the server, you must set the FORMS_PATH environment variable on your computer.
In order to do so, right-click My Computer, click on Properties, and in the Advanced
setting, click on the button labeled Environment Variable. For R12, name of the
environment variable will be FORMS_PATH and its value set to C:\code\forms.
For Release 11i, you can change the value of FORMS60_PATH in the registry to
include your directory, which is C:\code\forms in our example. After changing the
environment variable, you must restart the forms builder for this change to take effect.
In order to open TEMPLATE.fmb successfully, we have two options. The first option
is to transfer everything from the $AU_TOP/forms/US and $AU_TOP/resource
directories from the middle-tier machine to the C:\code\forms directory on the
desktop. However, in R12 instance, this will equate to transferring files over 3GB to
your desktop. Therefore, the second option is to be selective for the files being
transferred from the server. From $AU_TOP/forms/US, transfer the APP*.fmb and
FND*.fmb forms to folder C:\code\forms. Similarly, from $AU_TOP/resource, transfer
the APP*.pll, FND*.pll, VERT*.pll, PS*.pll, HR*.pll, GMS*.pll, FV*.pll, IGI*.pll, GLOBE.
pll, JA.pll, JE.pll, JL.pll, VERT.pll, GHR.pll, PQH_GEN.pll, PSAC.pll, CUSTOM.pll.

and OPM.pll libraries. If you still receive message for some other missing libraries,
then transfer those as well. Sometimes the error message for a missing library can be
misleading. For example, you might get the error, “Cannot find APPDAYPK.pll,”
despite the file being present in the C:\code\forms directory. In such cases, try to
open APPDAYPK.pll itself to see the dependent libraries that remain to be transferred
to your desktop.
Once all the required objects have been transferred to C:\code\forms, you will
be able to open TEMPLATE.fmb without any errors and at that point you can start
developing the custom form.
Steps for Developing Custom
Forms in E-Business Suite
Follow these steps to create custom forms in E-Business Suite:
1. Create TEMPLATE form Make a copy of TEMPLATE.fmb and rename
it to your custom form name. Your form name will begin with XX. For
developing HRMS-related screens, use HRTEMPLT.fmb.
2. Develop form Develop your form as per programming guidelines.
3. Generate runtime file Transfer the FMB file to midtier and compile it to
generate an FMX compiled file.
4. Register Register the form with Custom Application.
5. Create form function Create a form function attached to the form.
6. Attach to menu Attach the form function to the menu.
7. Assign to responsibility Assign the menu to the responsibility.

Monday, August 20, 2012

Useful queries/code/scripts in Oracle Apps Order Management



Useful queries/code/scripts in Oracle Apps Order Management


Following are some of the most frequently used queries in oracle apps order management:


Order header info
Select * from oe_order_headers_all ooh
where order_number= :p_order_number

Operating unit info
select * from hr_operating_units
where organization_id=oe_order_headers_all.org_id

Order type info
select * from apps.oe_transaction_types_tl
where transaction_type_id=oe_order_headers_all.order_type_id

Price list info
select * from apps.qp_list_headers_tl
where list_header_id=oe_order_headers_all.price_list_id

select * from apps.qp_list_lines
where list_header_id=oe_order_headers_all.price_list_id

Find customer info
select * from hz_cust_accounts hca
where cust_account_id=oe_order_headers_all.sold_to_org_id

select * from apps.hz_parties
where party_id=hz_cust_accounts.party_id

Find Ship to location info
select * from hz_cust_site_uses_all
where site_use_id=oe_order_headers_all.ship_to_org_id

select * from apps.hz_cust_acct_sites_all
where cust_acct_site_id=hz_cust_site_uses_all.cust_acct_site_id

select * from hz_party_sites
where party_site_id=hz_cust_acct_sites_all.party_site_id

Find Bill to location
select * from hz_cust_site_uses_all
where site_use_id=oe_order_headers_all.invoice_to_org_id
         
select * from hz_cust_acct_sites_all
where cust_acct_site_id=hz_cust_site_uses_all.cust_acct_site_id
         
select * from hz_party_sites
where party_site_id=hz_cust_acct_sites_all.party_site_id

actual address
select * from hz_locations 
where location_id=hz_party_sites.location_id

Sales rep id
select name from apps.ra_salesreps_all salerep  where
salesrep_id = oe_order_headers_all.salesrep_id  and rownum =1

Payment terms
select name from apps.ra_terms_tl
where term_id =oe_order_headers_all.payment_term_id
and language = 'US'

Order source
select name from apps.oe_order_sources
where order_source_id= oe_order_headers_all.order_source_id
and enabled_flag= 'Y'

Order Source Reference
select orig_sys_document_ref from oe_order_headers_all ooh
where order_number='&oracle order number'

FOB Point Code
select lookup_code from ar_lookups
where lookup_type = 'FOB' and enabled_flag = 'Y'
and upper(meaning) = upper(oe_order_headers_all.fob_point_code)

Freight terms
select lookup_code from apps.oe_lookups
where upper (lookup_type) = 'FREIGHT_TERMS'  and enabled_flag = 'Y'
and upper (lookup_code) = upper (oe_order_headers_all.freight_terms_code)

For sales channel code validation
select lookup_code from apps.oe_lookups
where lookup_type = 'SALES_CHANNEL' and enabled_flag = 'Y'
upper(lookup_code) = upper(oe_order_headers_all.sales_channel_code)

Ship method
select ship_method_code from wsh.wsh_carrier_services
where ship_method_code = oe_order_headers_all.shipping_method_code

Warehouse Info
select * from org_organization_definitions
where organization_id = oe_order_headers_all.ship_from_org_id

Sales order Lines Details
select * from apps.oe_order_lines_all
where header_id=oe_order_headers_all.header_id

Transactional currency code
select ota.price_list_id, qhb.currency_code
from ont.oe_transaction_types_all ota, qp.qp_list_headers_b qhb
where ota.transaction_type_id = oe_order_headers_all.order_type_id
and ota.price_list_id = qhb.list_header_id(+)
and NVL(qhb.list_type_code, 'PRL') = 'PRL'
and qhb.currency_code =oe_order_headers_all.transactional_curr_code

Item info
select * from apps.mtl_system_items_b
where segment1 like oe_order_lines_all.ordered_item
and organization_id=oe_order_lines_all.ship_from_org_id

UOM
select uom_code from inv.mtl_units_of_measure_tl
where upper(uom_code)= upper(oe_order_lines_all.order_quantity_uom)
and language= 'US' and nvl(disable_date, (sysdate + 1)) > sysdate

Item type code validation
select lookup_code from apps.oe_lookups
where upper(lookup_type) = 'ITEM_TYPE'
and enabled_flag = 'Y'
and upper(lookup_code)= oe_order_lines_all.item_type_code

On hand quantities
select * from apps.mtl_onhand_quantities
where inventory_item_id=oe_order_lines_all.inventory_item_id
and organization_id=oe_order_lines_all.ship_from_org_id

Shipping
select * from wsh_delivery_details
where source_header_id=oe_order_headers_all.header_id

select * from wsh_delivery_assignments
where delivery_detail_id=wsh_delivery_details.delivery_detail_id

select * from wsh_new_deliveries
where delivery_id=wsh_delivery_assignments.delivery_id

select * from wsh_delivery_legs
where delivery_id=wsh_new_deliveries.delivery_id

select * from wsh_trip_stops wts
where stop_id=wsh_delivery_legs.pick_up_stop_id

select * from wsh_trips wt
where trip_id=wsh_trip_stops.trip_id

select * from org_organization_definitions
where organization_id = wsh_new_deliveries.organization_id

Material transactions
select * from mtl_material_transactions
where inventory_item_id=oe_order_lines_all.inventory_item_id
and organization_id=oe_order_lines_all.ship_from_org_id

select * from mtl_transaction_types
where transaction_type_id = mmt.transaction_type_id

select * from apps.mtl_txn_source_types
where transaction_source_type_id= mmt.transaction_source_type_id
mmt = mtl_material_transactions


Join between OMWSHAR Tables
SELECT ooh.order_number
              ,ool.line_id
              ,ool.ordered_quantity
              ,ool.shipped_quantity
              ,ool.invoiced_quantity
              ,wdd.delivery_detail_id
              ,wnd.delivery_id
              ,rctl.interface_line_attribute1
              ,rctl.interface_line_attribute3
              ,rctl.interface_line_attribute6
              ,rct.org_id
              ,rct.creation_date
              ,trx_number
              ,rctl.quantity_ordered
              ,rct.interface_header_context
  FROM oe_order_headers_all ooh
             ,oe_order_lines_all ool
             ,wsh_delivery_details wdd
             ,wsh_new_deliveries wnd
             ,wsh_delivery_assignments wda
             ,ra_customer_trx_all rct
             ,ra_customer_trx_lines_all rctl
 WHERE ooh.header_Id=ool.header_id
      AND wdd.source_header_id=ooh.header_id
      AND wdd.delivery_detail_Id=wda.delivery_detail_id
      AND wda.delivery_id=wnd.delivery_id
      AND rctl.interface_line_attribute1=to_char(ooh.order_number)
      AND rctl.interface_line_attribute6=to_char(ool.line_id)
      AND rctl.interface_line_attribute3=to_char(wnd.delivery_id)
      AND rctl.customer_trx_id=rct.customer_trx_id
      AND rct.interface_header_context='ORDER ENTRY'



Purchase release concurrent program will transfer the details from OM to PO requisitions interface. The following query will verify the same:
SELECT interface_source_code,

           interface_source_line_id,
           quantity,
           destination_type_code,
           transaction_id,
           process_flag,
           request_id,
           TRUNC (creation_date)
  FROM po_requisitions_interface_all
 WHERE interface_source_code = 'ORDER ENTRY'
   AND interface_source_line_id IN (SELECT drop_ship_source_id
                                      FROM oe_drop_ship_sources
                                     WHERE header_id = &order_hdr_id
                                       AND line_id = &order_line_id);

The following sql is used to review the requisition, sales order, and receipt number. It shows the joins between various tables in Internal Sales order (ISO)

SELECT porh.segment1,
           porl.line_num,
           pord.distribution_num,
           ooh.order_number
           sales_order,
           ool.line_number so_line_num,
           rsh.receipt_num,
           rcv.transaction_type
  FROM oe_order_headers_all ooh,
          po_requisition_headers_all porh,
          po_requisition_lines_all porl,
          po_req_distributions_all pord,
          oe_order_lines_all ool,
          po_system_parameters_all posp,
          rcv_shipment_headers rsh,
          rcv_transactions rcv
 WHERE ooh.order_source_id = posp.order_source_id
   AND porh.org_id = posp.org_id
   AND porh.requisition_header_id = ool.source_document_id
   AND porl.requisition_line_id = ool.source_document_line_id
   AND porh.requisition_header_id = porl.requisition_header_id
   AND porl.requisition_line_id = pord.requisition_line_id
   AND porl.requisition_line_id = rcv.requisition_line_id
   AND pord.distribution_id = rcv.req_distribution_id
   AND rcv.shipment_header_id = rsh.shipment_header_id

Friday, August 10, 2012

REP-0613: Value does not match mask 'DD-MON-RR'


REP-0613: Value does not match mask 'DD-MON-RR'


Oracle Reports Date Handling When you register the report in Oracle apps. 

Use the value set FND_STANDARD_DATE Value set in the Oracle apps front end.
and in the user parameters defined in the report use the following as input mask against the date field.for the parameter as  RRRR/MM/DD HH24:MI:SS.

Then the Input to the code will be in the format of 'DD-MON-RR' and it is a date data type.


v_date_where    VARCHAR2(2000) := NULL;
begin
  IF :P_FROM_DT IS NOT NULL THEN
-- 06/30/2004 NPRASAD Commented the following 
--   v_date_where := ' AND trunc(ctb.creation_date)  >= to_date('||''''||to_char(:P_FROM_DT, 'DD-MON-RR')||''''||','|| '''DD-MON-RR'||''')-14 ';
  v_date_where := v_date_where || ' AND trunc(ctri.creation_date) >= to_date('||''''||to_char(:P_FROM_DT, 'DD-MON-RR')||''''||','|| '''DD-MON-RR'||''') ';
  END IF;
  IF :P_TO_DT IS NOT NULL THEN
  v_date_where := v_date_where || ' AND trunc(ctri.creation_date) <= to_date('||''''||to_char(:P_TO_DT, 'DD-MON-RR')||''''||','|| '''DD-MON-RR'||''') ';
  END IF;  
  srw.message(100,'Date where:'|| v_date_where);  
  return (v_date_where);
exception
  when others then
  srw.message (101,'Error! CF_DATE_WHERE failed '||sqlerrm);
  return (NULL);

Monday, July 30, 2012

Register, Attach, Submit, Delete Concurrent program using API


Register, Attach, Submit Concurrent program using API


The scripts in this blog can be used to:
1)    Register the executable and Program
2)    Attach Concurrent program to a Request Group
3)    Submit Concurrent program

1)    Registering the Executable from back end
          Usually we create executable in the front-end, but this can be done from the database tier i.e. back-end too.
          Below is the PL/SQL code to create an executable from back-end.
         BEGIN
              FND_PROGRAM.executable('XXMZ_EMPLOYEE' -- executable
                                                              , 'XXMZ Custom' -- application
                                                              , 'XXMZ_EMPLOYEE' -- short_name
                                                              , 'Executable for Employee INFORMATION' -- description
                                                              , 'PL/SQL Stored Procedure' -- execution_method
                                                              , 'XXMZ_EMPLOYEE' -- execution_file_name
                                                              , '' -- subroutine_name
                                                              , '' -- Execution File Path
                                                              , 'US' -- language_code
                                                              , '');
             COMMIT;
         END;
       Query in the front-end to see whether your executable is created.


2)    Registering the Concurrent program from back end
            Usually we create Concurrent program in the front-end, but this can be done from the database tier too.
            Below is the program to create a Concurrent program from back-end.
            BEGIN
                     FND_PROGRAM.register('Concurrent program for Employee Information' -- program
                                                                , 'XXMZ Custom' -- application
                                                                , 'Y' -- enabled
                                                                , 'XXMZ_EMPLOYEE' -- short_name
                                                                , ‘ Employee Information' -- description
                                                                , 'XXMZ_EMPLOYEE' -- executable_short_name
                                                                , 'XXMZ Custom' -- executable_application
                                                                , '' -- execution_options
                                                                , '' -- priority
                                                                , 'Y' -- save_output
                                                                , 'Y' -- print
                                                                , '' -- cols
                                                                , '' -- rows
                                                                , '' -- style
                                                                , 'N' -- style_required
                                                                , '' -- printer
                                                                , '' -- request_type
                                                                , '' -- request_type_application
                                                                , 'Y' -- use_in_srs
                                                                , 'N' -- allow_disabled_values
                                                                , 'N' -- run_alone
                                                                , 'TEXT' – output_type
                                                                , 'N' -- enable_trace
                                                                , 'Y' -- restart
                                                                , 'Y' -- nls_compliant
                                                                , '' -- icon_name
                                                                , 'US'); -- language_code
                        COMMIT;
            END;

 Query in the front-end to see whether your Concurrent program is created .

3)    Attaching the concurrent program to the request group
               Usually we Attach Concurrent program to the request group in the front-end, but this can be done from database tier too.
               Below is the program to Attach Concurrent program to the request group from back-end.
               BEGIN
                        FND_PROGRAM.add_to_group('XXMZ_EMPLOYEE' -- program_short_name
                                                                             , 'XXMZ Custom' -- application
                                                                             , 'xxmz Request Group' -- Report Group Name
                                                                             , 'XXMZ'); -- Report Group Application
                        COMMIT;
              END;


  Query in the front-end to see whether your Concurrent program is Attached to Request Group.

4)    Submitting Concurrent Program from Back-end
We first need to initialize oracle applications session using
                        fnd_global.apps_initialize(user_id,responsibility_id,application_responsibility_id) and then run fnd_request.submit_request
                         DECLARE
                               l_request_id NUMBER(30);
                         begin
                                  FND_GLOBAL.APPS_INITIALIZE (user_id => 1318, resp_id => 59966, resp_appl_id => 20064);
                                  l_request_id:= FND_REQUEST.SUBMIT_REQUEST ('XXMZ' --Application Short name,
'VENDOR_FORM'-- Concurrent Program Short Name );
                                  DBMS_OUTPUT.PUT_LINE(l_request_id);
                                  commit;
                         end;
                

Once the concurrent program is submitted from back-end, status of the concurrent program can be checked using below query.
                           SELECT * FROM FND_CONCURRENT_REQUESTS WHERE   REQUEST_ID= l_request_id;

You can use following code to wait for the request. It will return Boolean value.
FND_CONCURRENT.WAIT_FOR_REQUEST
                                   (request_id IN number default NULL,
                                    interval IN number default 60,
                                    max_wait IN number default 0,
                                    phase OUT varchar2,
                                    status OUT varchar2,
                                    dev_phase OUT varchar2,
                                    dev_status OUT varchar2,
                                    message OUT varchar2);

5) Delete a concurrent program from back-end

BEGIN   apps.fnd_program.remove_from_group (      program_short_name    => 'CTAR_AR_TRX_LINE_CONVERSION',      program_application   => 'CTAR',      request_group         => 'Receivables All',      group_application     => 'Receivables');   fnd_program.delete_program ('CTAR_AR_ACCTD_AMT_DUE_RMNG', 'CTAR');   fnd_program.delete_executable ('CTAR_AR_ACCTD_AMT_DUE_RMNG', 'CTAR');   COMMIT;END;