Scheduled Concurrent Programs

SELECT r.request_id,

            p.user_concurrent_program_name

         || CASE

               WHEN p.user_concurrent_program_name = 'Report Set'

               THEN

                  (SELECT ' - ' || s.user_request_set_name

                     FROM apps.fnd_request_sets_tl s

                    WHERE     s.application_id = r.argument1

                          AND s.request_set_id = r.argument2

                          AND language = 'US')

               WHEN p.user_concurrent_program_name = 'Check Periodic Alert'

               THEN

                  (SELECT ' - ' || a.alert_name

                     FROM apps.alr_alerts a

                    WHERE     a.application_id = r.argument1

                          AND a.alert_id = r.argument2

                          AND language = 'US')

            END

            concurrent_program_name,

         DECODE (c.class_type,

                 'P', 'Periodic',

                 'S', 'On Specific Days',

                 'X', 'Advanced',

                 c.class_type)

            schedule_type,

         CASE

            WHEN c.class_type = 'P'

            THEN

                  'Repeat every '

               || SUBSTR (c.class_info, 1, INSTR (c.class_info, ':') - 1)

               || DECODE (SUBSTR (c.class_info,

                                    INSTR (c.class_info,

                                           ':',

                                           1,

                                           1)

                                  + 1,

                                  1),

                          'N', ' minutes',

                          'M', ' months',

                          'H', ' hours',

                          'D', ' days')

               || DECODE (SUBSTR (c.class_info,

                                    INSTR (c.class_info,

                                           ':',

                                           1,

                                           2)

                                  + 1,

                                  1),

                          'S', ' from the start of the prior run',

                          'C', ' from the completion of the prior run')

            WHEN c.class_type = 'S'

            THEN

                  NVL2 (dates.dates, 'Dates: ' || dates.dates || '. ', NULL)

               || DECODE (SUBSTR (c.class_info, 32, 1),

                          '1', 'Last day of month ')

               || DECODE (

                     SIGN (TO_NUMBER (SUBSTR (c.class_info, 33))),

                     '1',    'Days of week: '

                          || DECODE (SUBSTR (c.class_info, 33, 1), '1', 'Su ')

                          || DECODE (SUBSTR (c.class_info, 34, 1), '1', 'Mo ')

                          || DECODE (SUBSTR (c.class_info, 35, 1), '1', 'Tu ')

                          || DECODE (SUBSTR (c.class_info, 36, 1), '1', 'We ')

                          || DECODE (SUBSTR (c.class_info, 37, 1), '1', 'Th ')

                          || DECODE (SUBSTR (c.class_info, 38, 1), '1', 'Fr ')

                          || DECODE (SUBSTR (c.class_info, 39, 1), '1', 'Sa '))

         END

            schedule,

         r.requested_start_date next_run,

         CASE

            WHEN     p.user_concurrent_program_name != 'Report Set'

                 AND p.user_concurrent_program_name != 'Check Periodic Alert'

            THEN

               r.argument_text

         END

            argument_text,

         r.hold_flag on_hold,

         c.date1 start_date,

         c.date2 end_date,

         c.class_info,

         user_name

    FROM apps.fnd_concurrent_requests r,

         applsys.fnd_conc_release_classes c,

         apps.fnd_concurrent_programs_tl p,

         apps.fnd_user usr,

         (    SELECT release_class_id,

                     SUBSTR (MAX (SYS_CONNECT_BY_PATH (s, ' ')), 2) dates,

                     a

                FROM (SELECT release_class_id,

                             RANK ()

                                OVER (PARTITION BY release_class_id ORDER BY s)

                                a,

                             s

                        FROM (SELECT c.class_info,

                                     l,

                                     c.release_class_id,

                                     DECODE (SUBSTR (c.class_info, l, 1),

                                             '1', TO_CHAR (l))

                                        s

                                FROM (    SELECT LEVEL l

                                            FROM DUAL

                                      CONNECT BY LEVEL <= 31),

                                     apps.fnd_conc_release_classes c

                               WHERE c.class_type = 'S')

                       WHERE s IS NOT NULL)

          CONNECT BY PRIOR (a || release_class_id) =

                        (a - 1) || release_class_id

            GROUP BY release_class_id, a) dates

   WHERE     r.phase_code = 'P'

         AND c.application_id = r.release_class_app_id

         AND c.release_class_id = r.release_class_id

         AND NVL (c.date2, SYSDATE + 1) > SYSDATE

         AND c.class_type IS NOT NULL

         AND p.concurrent_program_id = r.concurrent_program_id

         AND p.application_id = r.program_application_id

         AND p.language = 'US'

         AND dates.release_class_id(+) = r.release_class_id

         AND usr.user_id = requested_by

        -- AND user_name = 'XXSDM'

         AND P.user_concurrent_program_name = 'Workflow Background Process'

ORDER BY user_concurrent_program_name --requested_by, on_hold, next_run;

How to Convert Single To Multi-Org?

There are 3 steps involved in it explained below.

Step- 1

Before running the Convert to Multi-Org process, make sure you do the following:

• Apply the AD Patch 2412184: ADADMIN Convert to Multi-Org Performance Improvement: Increase parallelization

• Define at least one Operating Unit, and set the profile option, "MO: Operating Unit" at Site level, to that Operating Unit's value
 
Step- 2
 
Run Convert to Multi-Org process available from the Database Objects menu of the ADADMIN utility. The Convert to Multi-Org process upgrades a standard product group into a Multi-Org product group. You can choose this option only if you do not already have Multi-Org set up.

The Convert to Multi-Org program does the following:

• Populates the ORG_ID column in the Multi-Org tables with the new operating unit value you defined at the site level profile option: MO: Operating Unit

• Sets the MULTI_ORG_FLAG in the FND_PRODUCT_GROUPS table to “Y”

• Runs the “Replicate Seed Data” program

If you define more than one Operating Unit, the replicate seed data process is run for all Operating Units.
 
Step- 3
 
After running the Convert to Multi-Org process, make sure you apply the following TCA patch as post conversion process:

• Patch 2451368: Migrate data from Customers to Site Uses

Multi-Org Modules

Financials

Oracle Payables- AP
Oracle Receivables- AR
Oracle Cash Management- CE

Regional Localizations- JG

Latin America Localizations- JL
European Localizations- JE
Asia/Pacific Localizations- JA
Oracle Property Manager- PN
Subledger Accounting- XLA
Trading Community Architecture- TCA
iReceivables
iExpenses
Oracle Collections
Global Accounting Engine- AX

Public Sector Financials

Oracle Grants Management- GMS
Oracle Grants- IGW
Oracle Federal Financials- FV

Procurement

Oracle Purchasing- PO
Oracle Sourcing- PON
iSourcing
iProcurement
iSupplier Poral- POS

Manufacturing

Oracle Order Management- ONT
Oracle Release Management- RLM
Oracle Release Management Integration Kit- RLA
Oracle Shipping- WSH
Oracle Pricing- QP

Projects

Oracle Projects- PA

CRM

CRM Administrator- JTF

Sales

Oracle Sales Compensation- CN
Oracle Sales- AS

Service

Oracle Service- CS

Other

Oracle Scheduler- CSR
Oracle Banking Center- FPT
IStore- IBE

PO-INV Link Query

SELECT
p.segment1 po_number,
pol.po_line_id,
pol.category_id,
pv.segment1 supplier_number,
pv.vendor_name supplier_name,
pol.line_type_id,
msi.segment1 Item_number,
pol.item_description,
trunc(pol.creation_date) PO_Date,
trunc(poll.need_by_date) Promised_date,
trunc(p.acceptance_due_date) Received_date,
(poll.quantity) Quantity_ordered,
(poll.quantity_received) Quantity_received,
(poll.quantity-poll.quantity_received) Quantity_remaining,
(poll.quantity_billed) Quantity_billed,
(poll.quantity_cancelled) Quantity_cancelled ,
(poll.quantity* pol.unit_price) Ordered_amount,
(poll.quantity_received*pol.unit_price) Received_amount,
(poll.quantity_billed*pol.unit_price) Billed_amount,
(poll.quantity_cancelled*pol.unit_price) Cancelled_amount
FROM
po_lines_all pol,
po_vendors pv,
po_line_locations_all poll,
po_headers p,
mtl_system_items msi
WHERE
pv.vendor_id = p.vendor_id and
p.po_header_id=pol.po_header_id and
poll.po_header_id = p.po_header_id and
poll.po_line_id=pol.po_line_id and
(msi.organization_id = poll.ship_to_organization_id or
msi.inventory_item_id is null) and
msi.inventory_item_id(+) = pol.item_id

GL_INTERFACE Reference Columns in 11i EBS

Reference columns are populated/updated in GL_INTERFACE, GL_JE_LINES and GL_IMPORT_REFERENCES tables only in 11i EBS and not in R12. In R12, there are different set of SLA tables which we need to refer to retrieve subledger data after transfer to GL.

Below is the detailed analysis of Reference columns in GL_INTERFACE

Reference 1 To 10

reference1 => BATCH NAME
reference2 => BATCH Description
reference4 => Header NAME
reference5 => Header Description.
reference7 => JE Reversal FLAG
reference8 => JE Reversal PERIOD
reference10 => JOURNAL LINE DESCRIPTION(GE LINE description field )

Reference 11 To 20

Not Used

Reference 21 To 30

In Summary mode, gl_interface.reference21 - reference30 maps to gl_je_lines.reference1 - reference10.

In Detail mode, gl_interface.reference21 - reference30 maps to gl_import_reference.reference1 - reference10.

Below is the mapping for Reference 21-30 Columns with Subledgers (AP/ AR/ FA)

AP (Reference 21 To 30)

reference21 = vendor_name
reference22 = invoice_id
reference23 = distribution_line_number or check_id
reference24 = check_number
reference25 = invoice_num
reference26 = 'AP INV. JUST INSERTED' or  'AP PAYMT. JUST INSERTED'
reference27 = set_of_books_id
reference28 = invoice_distribution_id or distribution_line_number
reference29 = invoice_payment_id

For more details, Refer to Metalink Note ID- 1011799.6

AR (Reference 21 To 30)

Adjustments
-------------
REFERENCE21 = posting_control_id
REFERENCE22 = adjustment_id
REFERENCE23 = line_id
REFERENCE24 = trx_number
REFERENCE25 = adjustment_number
REFERENCE26 = cust_trx_type
RERENCE27 = bill_to_customer_id
REFERENCE28 = ADJ
REFERENCE29 = source_type prefixed by �ADJ_�
REFERENCE30 = AR_ADJUSTMENTS

Transactions
-------------
REFERENCE21 = posting_control_id
REFERENCE22 = customer_trx_id
REFERENCE23 = cust_trx_line_gl_dist_id
REFERENCE24 = trx_number
REFERENCE25 = cust.account_number
REFERENCE26 = CUSTOMER
REFERENCE27 = bill_to_customer_id
REFERENCE28 = type(CM/DM/CB/INV)
REFERENCE29 = type account_class
REFERENCE30 = RA_CUST_TRX_LINE_GL_DIST

Receipts
---------
REFERENCE21 = posting_control_id
REFERENCE22 = cash_receipt_history_id or cash_receipt_id for MISC
REFERENCE23 = line_id
REFERENCE24 = receipt_number
REFERENCE25 = null for CASH / cash_receipt_history_id for MISC
REFERENCE26 = null
REFERENCE27 = pay_from_customer
REFERENCE28 = MISC / TRADE
REFERENCE29 = MISC_sourcetype or TRADE_source_type
REFERENCE30 = AR_CASH_RECEIPT_HISTORY

Applications
--------------
REFERENCE21 = posting_control_id
REFERENCE22 = cash_receipt_id (receivable_application_id for CASH / receivable_application_id for CM)
REFERENCE23 = line_id
REFERENCE24 = receipt_number for CASH / trx_number for CM
REFERENCE25 = trx_number if status = APP/ NULL for unapplied records
REFERENCE26 = cust_trx_type
REFERENCE27 = pay_from_customer for CASH / bill_to_customer_id for CM
REFERENCE28 = application_type (TRADE or CCURR for CASH / CMAPP for CM)
REFERENCE29 = application_type source_type
REFERENCE30 = AR_RECEIVABLE_APPLICATIONS

Bills Receivable
----------------
REFERENCE21 = posting_control_id
REFERENCE22 = transaction_history_id
REFERENCE23 = line_id
REFERENCE24 = trx_number
REFERENCE25 = customer_Trx_id
REFERENCE26 = cust_trx_type
REFERENCE27 = drawee_id
REFERENCE28 = cust_trx_type
REFERENCE29 = BR_source_type
REFERENCE30 = AR_TRANSACTION_HISTORY

FA (Reference 21 To 30)

reference21 = transaction_header_id
reference22 = asset_id
reference23 = distribution_id
reference24 = adjustment_line_id
reference25 = book_type_code
reference26 = period_counter
reference27 = FA_TRANSFER_TO_GL
reference28 = adjustment_type / lookup_code
reference29 = cje_id

Transfer Recon References in R12

Navigation: Setup > Financials > Subledger Accounting > Accounting Methods Builder > Journal Entry Setups > Journal Line Types

1) Query a seeded Journal Line Type and copy it by clicking on the Copy button at the bottom left of the form

2) Give your Journal Line Type code, name, and description a meaningful name.

3) Click on the Accounting Attribute Assignments button at the bottom right of the form

4) Pick a source for the Reconciliation Reference

5) Save.

Navigation: Setup > Financials > Subledger Accounting > Accounting Methods Builder > Methods and Definitions > Journal Line Definitions

6) Link the Journal Line Type just created to the Journal Line Definition. Copy a seeded one and create your own if needed.

7) Assuming that the JLD is already linked to the Application Accounting Definition, validate the Application Accounting Definition either in the form or via the concurrent program 'Validate Application Accounting Definitions'.

Assuming also that the Application Accounting Definition is already linked to the Subledger Accounting Method which in turn is already linked with the ledger, Create Accounting can now be run.

8) Once Create Accounting, Journal Import and the Post program in GL have completed, one can reconcile either manually or run Automatic reconciliation in GL.

To enable or disable reconciliation for accounting code combinations:

1) Navigate to the GL Accounts window (General Ledger: Setup > Accounts > Combinations).

2) Query the accounts that you want.

3) Navigate to the descriptive flexfield for the first account. The Regional Reconciliation Combinations window displays the default setting for this account.

4) Enter Yes or No to enable or disable reconciliation for this code combination.

5) Press the OK button.

6) Repeat steps 3 to 5 for each account.

7) Save your work.

Notification Email in HTML Format

OPTION-1
========
Using sqlplus as the apps user:

1) Execute the below update statements:

update wf_local_roles
set notification_preference = 'MAILHTML'
where user_flag='Y'

2)
update fnd_user_preferences
set preference_value = 'MAILHTML'
where module_name = 'WF'
and preference_name = 'MAILTYPE'

3) Commit

OPTION-2
========

1) Login to application with "Workflow Administrator Web Applications" responsibility.

2) Navigate as per below:
---> Administrator Workflow
------------>Administration
---------------> Workflow Configuration

3) Scroll to section "Global Preferences"

4) Change "Notification Style" to your desired value to have the preferences set as default values for all new users

INV Open Periods

SELECT
opu.name as operating_unit
, per.organization_id as inv_org_id
, par.organization_code as inv_org_code
, per.period_name
, per.period_year
, per.period_num
, flv.meaning as status
, per.period_start_date
, per.schedule_close_date
FROM
org_acct_periods per
, fnd_lookup_values flv
, mtl_parameters par
, hr_all_organization_units org1
, hr_all_organization_units_tl otl
, hr_organization_information org2
, hr_organization_information org3
, hr_operating_units opu
WHERE 1=1
AND flv.lookup_type(+) = 'MTL_ACCT_PERIOD_STATUS'
AND flv.enabled_flag(+) = 'Y'
AND per.organization_id = par.organization_id
AND flv.lookup_code(+) = decode(nvl(per.period_close_date,sysdate), per.period_close_date, decode(per.open_flag, 'N', decode(summarized_flag,'N',65,66), 'Y', 4, 'P', 2, 4), 3 )
AND flv.language = 'US'
AND upper(flv.meaning) != 'CLOSED'
AND per.organization_id = org1.organization_id
AND org1.organization_id = otl.organization_id
AND org1.organization_id = org2.organization_id
AND org1.organization_id = org3.organization_id
AND org2.org_information_context = 'Accounting Information'
AND org3.org_information_context = 'CLASS'
AND org3.org_information1 = 'INV'
AND org3.org_information2 = 'Y'
AND org2.org_information3 = opu.organization_id
AND otl.language = 'US'
ORDER BY opu.name, per.organization_id, per.period_year desc, per.period_num desc

User Creation Thru API (FND_USER_PKG)

Below is the code to create multiple users and adding responsibilities to them through API
Other way around, you can load users in table and fetch cursor table data in Loop to create users.

declare
v_user_name := 'euser' ;
v_session_id := sid;
/* Pass here sid from this query- SELECT username, sid, serial#, status FROM v$session WHERE username = 'APPS'; */
BEGIN
for i in 1..1000 loop
fnd_user_pkg.createuser
(x_user_name => v_user_name||i
,x_owner => ''
,x_unencrypted_password => 'welcome1'
,x_session_number => v_session_id
,x_start_date => SYSDATE - 10
,x_end_date => SYSDATE + 100
,x_last_logon_date => SYSDATE - 10
,x_description => 'EBS user creation'
,x_password_date => SYSDATE - 10
,x_password_accesses_left => 10000
,x_password_lifespan_accesses => 10000
,x_password_lifespan_days => 10000
,x_email_address => 'user@myemail.com'
,x_fax => ''
,x_customer_id => ''
,x_supplier_id => '');

fnd_user_pkg.addresp
(username => v_user_name||i
,resp_app => 'SYSADMIN'
,resp_key => 'SYSTEM_ADMINISTRATOR'
,security_group => 'STANDARD'
,description => 'Auto Assignment'
,start_date => SYSDATE - 10
,end_date => SYSDATE + 1000);
end loop;
END;
/

How to find version of file on server?

strings -a $INV_TOP/forms/US/INVMWBIV.fmx | grep '$Header'

$Header: INVMWBIV.fmb 120.101.12000000.30 2008/07/17 07:07 ckrishna noship

OR

grep '$Header' $INV_TOP/patch/115/sql/INVTXGGB.pls

/* $Header: INVTXGGB.pls 120.55.12000000.15 2007/10/25 11:02:58 ksaripal ship $ */

Registering Disco Workbook in Apps R12

1) Create the workbook

2) Open the workbook in the Discoverer Desktop or Plus edition and go to
'File->Manage Workbooks->Properties' look for the value for 'Identifier'. Save this value.

3) Create a form function. The form function definition includes the properties listed in these tabs:

3.1 Description tab:
3.1.1 Function Name: XX_[FUNCTION_NAME] (it is accepted practice to identify customizations with an XX prefix)
3.1.2 User Function Name: This is the name that will show in the menu
3.1.3 Description: Add a description of the function if you want.

3.2 Properties tab:
3.2.1 Type : SSWA jsp function
3.2.2 Maintenance Mode Support: Leave as "None"
3.2.3 Context Dependence: Leave as "Responsibility"

3.3 Form tab:
3.3.1 Form: Leave the field blank.
3.3.2 Application: Leave the field blank.
3.3.3 Parameters: mode=DISCO&workbook=(workbook identifier from step2)&parameters=(Disco parameters name/values)

3.4 Web HTML tab:
3.4.1 HTML call : OracleOasis.jsp

3.5 Web Host tab:
3.5.1 Leave all fields blank.

3.6 Region tab:
3.6.1 Leave all fields blank.

3.7 Save the form.

4) Open the menu form as sysadmin.

4.1 Search for the main menu under which you want the link to appear.
4.2 Add the information you need such as prompt, submenu, description etc.
4.3 Enter into the Function field the name of the function you created in step 3.
4.4 Save the menu form.

A message will appear saying that a concurrent program will run to regenerate the menus.

5) Set below Profile options, if your End User Layer Name = EUL_US
ICX: Discoverer Default End User Layer Schema Prefix = EUL
ICX: Discoverer EDW End User Layer Schema Prefix = US

6) Bounce Apache and Forms.

How to find OPP log file?

To investigate on XML issues or other publishing problems, often the OPP logfile is needed.
OPP stands for Output Post Processor. Below are the steps to find log file

1. Login to the application as SYSADMIN

2. Responsibility: System Administrator

3. Function: Concurrent --> Manager --> Administration

4. Select the Output Post Processor

5. Click on the Processes button

6. Select the Concurrent Process which was active during the time that the request ran

7. Click on the Manager Log button to open the Output Post Processor log file

How to Apply a Patch?

1) Stop all application services (keep the database and the database listener up and running)

2) Download the patch from Metalink

3) Copy the patch file to any directory (i.e. /u04/patches)

4) As 'applmgr' do the following:

- Source the environment file
- Issue "adadmin" and Select (5. Change Maintenance Mode), then (1. Enable Maintenance Mode)
- $ cd /u04/patches
- Type 'unzip (patch_number).zip'
- cd (patch_number)
- Type 'adpatch'
- When it prompts you to enter the patch driver, type 'u.drv'

5) Apply the patch on the database tier first then on the application tier.

6) Once you apply the patch successfully, 'Disable Maintenance Mode' from adadmin.

Note- And, always follow the steps in the patch README file.
 

CUSTOM.pll Code for Tabs/Buttons

Hiding Tab

my_tab_page_id TAB_PAGE;
begin
if (event_name = 'WHEN-NEW-FORM-INSTANCE') then
if (form_name = 'OEXOEORD') then
my_tab_page_id := FIND_TAB_PAGE('ORDER_REGIONS.LINE_ITEMS');
SET_TAB_PAGE_PROPERTY(my_tab_page_id,VISIBLE,property_FALSE);
end if;
end if;

Renaming Tab

my_tab_page_id TAB_PAGE;
begin
if (event_name = 'WHEN-NEW-FORM-INSTANCE') then
if (form_name = 'OEXOEORD') then
my_tab_page_id := FIND_TAB_PAGE('ORDER_REGIONS.LINE_ITEMS');
SET_TAB_PAGE_PROPERTY(my_tab_page_id,LABEL,'XX Items');
end if;
end if;

Hiding Button

if (event_name = 'WHEN-NEW-FORM-INSTANCE') then
if (form_name = 'OEXOEORD') then
app_item_property2.set_property('ORDER_CONTROL.BOOK_ORDER',displayed, PROPERTY_false);
end if;
end if;

Renaming Button
 
if (event_name = 'WHEN-NEW-FORM-INSTANCE') then
if (form_name = 'OEXOEORD') then
app_item_property2.set_property('ORDER_CONTROL.BOOK_ORDER',Label,'Please confirm');
end if;
end if;

CUSTOM.pll Code for Fields

Mandatory Field

if (event_name = 'WHEN-NEW-FORM-INSTANCE') then
if (form_name = 'OEXOEORD') then
app_item_property2.set_property('ORDER.CUST_PO_NUMBER',REQUIRED, PROPERTY_ON);
end if;
end if;

Restricting Field Case

if (event_name = 'WHEN-NEW-FORM-INSTANCE') then
if (form_name = 'OEXOEORD') then
app_item_property2.set_property('ORDER.CUST_PO_NUMBER',CASE_RESTRICTION, UPPERCASE);
end if;
end if;

Changing Field Background Color

if (event_name = 'WHEN-NEW-FORM-INSTANCE') then
if (form_name = 'OEXOEORD') then
app_item_property2.set_property('ORDER.TERMS',BACKGROUND_COLOR,'r255g0b255');
end if;
end if;


Note- Colors can be derived from http://www.geodyssey.com/galileo/namedcolors.html
 
Changing Field Prompt
 
if (event_name = 'WHEN-NEW-FORM-INSTANCE') then
if (form_name = 'WSHFRREL' AND block_name = 'RELEASE') then
app_item_property2.set_property ('RELEASE.ORDER_NUMBER', prompt_text, 'Sales Order Number');
end if;
end if;
 
Disabling Field
 
if (event_name = 'WHEN-NEW-ITEM-INSTANCE') then
if (form_name = 'OEXOEORD' AND block_name = 'ORDER') then
COPY ('Business World', 'ORDER.SOLD_TO');
VALIDATE (item_scope);
app_item_property2.set_property ('ORDER.SOLD_TO', enabled, property_false);
end if;
end if;

Adding Special Menu Item in Form

a menuitem;

Begin
a := FIND_MENU_ITEM ('SPECIAL.SPECIAL15');
if (event_name = 'WHEN-NEW-BLOCK-INSTANCE') then
if (form_name = 'OEXOEORD' AND block_name = 'LINE') then
app_special2.instantiate ('SPECIAL15', 'Query Form');
SET_MENU_ITEM_PROPERTY (a, displayed, property_true);
SET_MENU_ITEM_PROPERTY (a, enabled, property_true);
end if;
end if;

if (event_name = 'SPECIAL15') then
if (form_name = 'INVIDITM') then
fnd_function.EXECUTE (function_name => 'INV_INVMATWB',
open_flag => 'Y',
session_flag => 'Y' );
end if;
end if;

Profile Option Values

Profile Option Details

SELECT
po.profile_option_name as name
,po.user_profile_option_name
,decode(to_char(pov.level_id),'10001','SITE','10002','APP','10003','RESP','10005','SERVER','10006','ORG','10004','USER', '???') as "LEVEL"
, decode(to_char(pov.level_id),'10001','','10002', app.application_short_name,'10003', rsp.responsibility_key,'10005', svr.node_name,'10006', org.name,'10004', usr.user_name,'???') as context
, pov.profile_option_value as value
FROM
fnd_profile_options_vl po
, fnd_profile_option_values pov
, fnd_user usr
, fnd_application app
, fnd_responsibility rsp
, fnd_nodes svr
, hr_operating_units org
WHERE 1=1
AND pov.application_id = po.application_id
AND pov.profile_option_id = po.profile_option_id
AND usr.user_id = pov.level_value
AND rsp.application_id = pov.level_value_application_id
AND rsp.responsibility_id = pov.level_value
AND app.application_id = pov.level_value
AND svr.node_id = pov.level_value
AND org.organization_id = pov.level_value
ORDER BY 1, pov.level_id, 5

Note- Initialize your SQL session before running above query


Profile option with Modification Date and User

SELECT t.user_profile_option_name, profile_option_value,
v.creation_date,
v.last_update_date,
v.creation_date - v.last_update_date "Change Date",
(SELECT UNIQUE user_name
FROM fnd_user
WHERE user_id = v.created_by) "Created By",
(SELECT user_name
FROM fnd_user
WHERE user_id = v.last_updated_by) "Last Update By"
FROM fnd_profile_options o,
fnd_profile_option_values v,
fnd_profile_options_tl t
WHERE o.profile_option_id = v.profile_option_id
AND o.application_id = v.application_id
AND start_date_active <= SYSDATE
AND NVL (end_date_active, SYSDATE) >= SYSDATE
AND o.profile_option_name = t.profile_option_name
AND level_id = 10001
AND t.LANGUAGE IN (SELECT language_code
FROM fnd_languages
WHERE installed_flag = 'B'
UNION
SELECT nls_language
FROM fnd_languages
WHERE installed_flag = 'B')
ORDER BY user_profile_option_name;

What is PTD, QTD and YTD in GL?

(period-to-date) is the period activity for the concerned period
Period_net_dr – period_net_cr

QTD (quarter-to-date) is
(Quarter_to_date_dr – Quarter_to_date_cr) + (period_net_dr- period_net_cr)

YTD (year-to-date) is
 (Begin-balance_dr- begin_balance_cr) + (period_net_dr- period_net_cr)

All below columns are stored in GL_BALANCES table with CCID and Period Name

period_net_cr
period_net_dr
quarter_to_date_dr
quarter_to_date_cr
begin_balance_dr
beging_balance_cr

PA Detail Query

SELECT DISTINCT
hou.NAME org,
hou.organization_id org_id,
ppc.class_category CATEGORY,
ppc.class_code code,
ppa.project_id,
NULL expenditure_type,
ppa.segment1 proj_num,
ppa.NAME proj_name,
ppa.project_type project_type,
pcd.pa_date,
pcd.pa_period_name,
pet.expenditure_category expenditure_category,
(SELECT xx.description
FROM fnd_flex_values_vl xx
WHERE xx.flex_value_set_id = a.flex_value_set_id
AND a.parent_flex_value = xx.flex_value
AND xx.summary_flag = 'Y'
AND flex_value <> 'T')
account_type,
FROM apps.pa_projects_all ppa,
apps.pa_tasks ppt,
apps.hr_all_organization_units hou,
apps.pa_project_classes ppc,
apps.pa_cost_distribution_lines_all pcd,
apps.pa_expenditures_all e,
apps.pa_expenditure_items_all pei,
apps.pa_expenditure_types pet,
fnd_flex_value_children_v a,
fnd_id_flex_segments j
WHERE ppa.carrying_out_organization_id = hou.organization_id
AND ppa.project_id = ppt.project_id
AND ppa.project_id = pei.project_id
AND pei.task_id = ppt.task_id
AND e.expenditure_id = pei.expenditure_id
AND ppa.project_id = ppc.project_id
AND pei.expenditure_item_id = pcd.expenditure_item_id
AND pet.expenditure_type = pei.expenditure_type
AND a.flex_value_set_id = j.flex_value_set_id
AND UPPER (j.segment_name) = 'ACCOUNT'
AND j.id_flex_code = 'GLLE'
AND ppt.attribute1 = a.flex_value

Sending Email Attachment Thru PL/SQL

DECLARE

v_file_handle UTL_FILE.FILE_TYPE;
v_email_server VARCHAR2 (100) := 'sgcmm232.apac.corp.eds.com';
v_conn UTL_SMTP.CONNECTION;
v_port NUMBER := 25;
v_reply UTL_SMTP.REPLY;
v_msg VARCHAR2 (32767);
v_line VARCHAR2 (1000);
v_message VARCHAR2 (1000);
b_connected BOOLEAN := FALSE;
v_sender VARCHAR2 (50) := 'adchintawar@gmail.com';
CRLF VARCHAR2 (2) := CHR (13) || CHR (10);
RECPT VARCHAR2 (255) := 'adchintawar@gmail.com';
SLP PLS_INTEGER := 300;
pdirpath varchar2 (50) := 'C:\AMIT_DOC';
pfilename varchar2 (50) := 'test.txt';
p_Stat number;

BEGIN

p_stat := 0;

/***** Check if the file exists ****/

BEGIN

v_file_handle := UTL_FILE.FOPEN (pDirPath, pFileName, 'R');

EXCEPTION
WHEN UTL_FILE.INVALID_PATH
THEN
p_stat := 99;
RETURN;

WHEN OTHERS
THEN
p_stat := 99;
RETURN;
END;

/***** Try to connect for three times, do sleep in between for 5 minutes *****/

FOR i IN 1 .. 3
LOOP
BEGIN

--open the connection with the smtp server and
--do the handshake

v_conn := UTL_SMTP.OPEN_CONNECTION (v_email_server, v_port);
v_reply := UTL_SMTP.HELO (v_conn, v_email_server);

IF 250 = v_reply.code
THEN
b_connected := TRUE;
EXIT;
END IF;

EXCEPTION
WHEN OTHERS
THEN
DBMS_LOCK.SLEEP (SLP);
END;

END LOOP;

IF b_connected = FALSE
THEN
p_stat := 99;
RETURN;
END IF;

v_reply := UTL_SMTP.MAIL (v_conn, v_sender);

IF 250 != v_reply.code
THEN
p_stat := 99;
RETURN;
END IF;

v_reply := UTL_SMTP.RCPT (v_conn, RECPT);

IF 250 != v_reply.code
THEN
p_stat := 99;
RETURN;
END IF;

UTL_SMTP.OPEN_DATA (v_conn);

v_message :=
'Sample Email This is an auto generated mail. Please do not reply to this mail.'
|| CHR (10);

v_msg :=
'Date: '
|| TO_CHAR (SYSDATE, 'Mon DD yy hh24:mi:ss')
|| CRLF
|| 'From: '
|| v_sender
|| CRLF
|| 'Subject: '
|| 'Sample file'
|| CRLF
|| 'To: '
|| RECPT
|| CRLF
|| 'Mime-Version: 1.0'
|| CRLF
|| 'Content-Type: multipart/mixed;
boundary="DMW.Boundary.605592468"'
|| CRLF
|| ''
|| CRLF
|| v_message
|| CRLF
|| ''
|| CRLF
|| '--DMW.Boundary.605592468'
|| CRLF
|| 'Content-Type: text/plain;
name="v_message.txt"; charset=US-ASCII'
|| CRLF
|| 'Content-Disposition: inline; filename="v_message.txt"'
|| CRLF
|| 'Content-Transfer-Encoding: 7bit'
|| CRLF
|| ''
|| CRLF
|| v_message
|| CRLF
|| CRLF
|| CRLF;

UTL_SMTP.WRITE_DATA (v_conn, v_msg);

/***** Prepare the attachment to be sent *****/

v_Msg :=
CRLF
|| '--DMW.Boundary.605592468'
|| CRLF
|| 'Content-Type:
|| application/octet-stream; name="'
|| pFileName
|| '"'
|| CRLF
|| 'Content-Disposition: attachment; filename="'
|| pFileName
|| '"'
|| CRLF
|| 'Content-Transfer-Encoding: 7bit'
|| CRLF
|| CRLF;

UTL_SMTP.WRITE_DATA (v_conn, v_msg);

LOOP

BEGIN

UTL_FILE.GET_LINE (v_file_handle, v_line);

EXCEPTION
WHEN NO_DATA_FOUND
THEN
EXIT;

END;

v_msg := '*** truncated ***'
|| CRLF;

v_msg := v_line
|| CRLF;

UTL_SMTP.WRITE_DATA (v_conn, v_msg);

END LOOP;

UTL_FILE.FCLOSE (v_file_handle);

v_msg := CRLF;

UTL_SMTP.WRITE_DATA (v_conn, v_msg);

v_msg := CRLF
|| '--DMW.Boundary.605592468--'
|| CRLF;

UTL_SMTP.WRITE_DATA (v_conn, v_msg);
UTL_SMTP.CLOSE_DATA (v_conn);
UTL_SMTP.QUIT (v_conn);

EXCEPTION
WHEN OTHERS
THEN
p_stat := 99;

END;

PO-Faxstar Integration

Faxstar is a product from the company called Sepe, Inc. It has the ability to send PO Document via email and Fax. I tried to jot down few commands which can be used in After report trigger

DECLARE
f text_io.file_type;
BEGIN
--Command to open the text file
f := text_io.fopen ('/tmp/'
|| :p_conc_request_id
|| 'e.txt', 'W');

-- Command to display Supplier Name in Console Log
text_io.put_line (f, '**(REF) '
|| v_supplier_name
|| ' **END');

-- Command to send Mail with Subject and Attachment
text_io.put (
f,
|| '**(MAIL) '
|| :p_email_address
|| ' **(SUBJECT)'
|| 'PO-NUMBER:'
|| :P_PO_NUM_FROM
|| ' **(PMATTACH) '
|| :p_conc_request_id
|| 'e.pdf'
|| ' **END'
);

-- Command to send Fax to given Fax Number
text_io.put (
f,
|| '**(FAX) '
|| :p_fax_num
|| ' **(PATTACHPDF) '
|| :p_conc_request_id
|| 'f.pdf'
|| ' **END'
);

-- Email Confirmation in case of fax failure
text_io.put_line (
f,
|| '**(MFAIL) '
|| 'adchintawar@gmail.com'
|| '**(REF)'
|| 'PO-NUM-'
|| :P_PO_NUM_FROM
|| '**END'
);

--Command to Close the text file
text_io.fclose (f);
END;