Interfaces and Conversions in Oracle Applications

Note : For More Information About This Article Please Download the Below Link

http://www.ziddu.com/download/15527264/Interfaces_and_Conversions_in_Oracle_Applications.pdf.html

Thanks,
Raju

How To Determine If A Package/Procedure/Function Currently Is In Use

Subject: How To Determine If A Package/Procedure/Function Currently Is In Use

goal: How to determine if a package or stored procedure/function currently is in use
goal: How to identify the user who is currently executing a specific
package or stored procedure/function
fact: Oracle Server - Enterprise Edition
fact: Oracle Server - Standard Edition

fix:
Prior to recompiling a package or stored procedure/function, or to modifying
underlying objects, it can be useful to find out if the object is currently
being executed.

The following query is an example of how to obtain this information. Run the
query in SQL*Plus connected as a user with DBA privileges.

&OBJECT_NAME is to be replaced by the name of the package or stored
procedure/function.

-------------------------------------------

COLUMN TO_NAME FORMAT A14 heading "Object name"
COLUMN USERNAME FORMAT A14 heading "User running"
COLUMN TO_OWNER FORMAT A14 heading "Object owner"

SELECT DISTINCT
o.to_name,
v.username,
o.to_owner,
s.users_executing "Number"
FROM v$object_dependency o,
v$sql s,
v$session v
WHERE o.to_name='&OBJECT_NAME'
AND o.from_address=s.address
AND o.from_hash=s.hash_value
AND o.from_address=v.sql_address
AND o.from_hash=v.sql_hash_value;

RELEASE 12 UPGRADE

Functional Upgrade Impacts Document for Oracle Payments (FINANCIALS)

Note : For More Information please download the below link

http://www.ziddu.com/download/15527101/RELEASE12UPGRADE.pdf.html

Thanks,
Raju

Oracle Alerts - For the beginners only

Contents

• What is Oracle Alert?
• What are the difference types of Alert?
• How to set up an Alert?
(Different components of Alert)
• How Alert Works?
• Limitations of Oracle Alert?

What is Oracle Alert?
• Complete exception control solution
• Easy electronic emailing
• Execution of process
• Schedule in regular interval
• Replacement of Database Trigger
• Take predefined actions
• Take actions depends on user response.

Alerts
• Periodic Alert • Event Alert
– Variable frequency – All possible events
• Customized Alert Frequency
• Customized Alert Actions
(Details / Summary action, No exception, History, Duplicate Checking
• Action Escalation
• Response Processing

Creating a Periodic Alert
• Set Frequency (On Demand, On day of …)
• Set time / interval
• Enter / import select statement
• Verify / Run select statement

Note : For More Information please download the below link

http://www.ziddu.com/download/15525611/OracleAlerts2.pdf.html

Thanks,
Raju

Oradcle Alerts

Oracle Alerts

Contents

Chapter 1 : Overview of Oracle Alerts

Chapter 2 : Defining Alerts

Chapter 3 : Type of Alerts

Chapter 4 : Check the Alerts

OverView of Oracle Alerts:

• Keep you informed of critical activity in your database

• Deliver key information from your applications, in the format you choose

• Provide you with regular reports on your database information

• Automate system maintenance, and routine online tasks

Overview:

Oracle Alert is your complete exception control solution.

Oracle Alert facilitates the flow of information within your organization by letting you create entities called alerts to monitor your business information and to notify you of the information you want. You can define one of two types of alerts: an event alert or a periodic alert.

An event alert immediately notifies you of activity in your database as it occurs. When you create an event alert, you specify the following:

• A database event that you want to monitor, that is, an insert and/or an update to a specific database table.

• A SQL Select statement that retrieves specific database information as a result of the database event.

• Actions that you want Oracle Alert to perform as a result of the database event. An action can entail sending someone an electronic mail message, running a concurrent program, running an operating script, or running a SQL statement script. You include all the actions you want Oracle Alert to perform, in an action set.

A periodic alert, on the other hand, checks the database for information according to a schedule you define. When you create a periodic alert, you specify the following:

• A SQL Select statement that retrieves specific database information.

• The frequency that you want the periodic alert to run the SQL statement.

• Actions that you want Oracle Alert to perform once it runs the SQL statement. An action can entail sending the retrieved information to someone in an electronic mail message, running a concurrent program, running an operating script, or running a

• SQL statement script.
  

Note : For More Information please download the below link

http://www.ziddu.com/download/15525590/OracleAlerts1.pdf.html

Thanks,
Raju

Single Query For P To P Cycle

SELECT a.org_id "ORG ID",
e.vendor_name "VENDOR NAME",
UPPER (e.vendor_type_lookup_code) "VENDOR TYPE",
f.vendor_site_code "VENDOR SITE",
f.address_line1 "ADDRESS",
f.city "CITY",
f.country "COUNTRY",
TO_CHAR (TRUNC (d.creation_date)) "PO DATE",
d.segment1 "PO NUMBER",
d.type_lookup_code "PO TYPE",
c.quantity_ordered "QTY ORDERED",
c.quantity_cancelled "QTY CANCALLED",
g.item_description "ITEM DESCRIPTION",
g.unit_price "UNIT PRICE",
(NVL (c.quantity_ordered, 0) - NVL (c.quantity_cancelled, 0)
) *
NVL (g.unit_price, 0) "PO Line Amount",
(SELECT DECODE (ph.approved_flag, 'Y', 'Approved')
FROM po.po_headers_all ph
WHERE ph.po_header_id = d.po_header_id) "PO STATUS",
a.invoice_type_lookup_code "INVOICE TYPE",
a.invoice_amount "INVOICE AMOUNT",
TO_CHAR (TRUNC (a.invoice_date)) "INVOICE DATE",
a.invoice_num "INVOICE NUMBER",
(SELECT DECODE (x.match_status_flag, 'A', 'Approved')
FROM ap.ap_invoice_distributions_all x
WHERE x.invoice_distribution_id = b.invoice_distribution_id)
"Invoice Approved?",
a.amount_paid,
h.amount,
i.check_number "CHEQUE NUMBER",
TO_CHAR (TRUNC (i.check_date)) "PAYMENT DATE"

FROM ap.ap_invoices_all a,
ap.ap_invoice_distributions_all b,
po.po_distributions_all c,
po.po_headers_all d,
po.po_vendors e,
po.po_vendor_sites_all f,
po.po_lines_all g,
ap.ap_invoice_payments_all h,
ap.ap_checks_all i

WHERE a.invoice_id = b.invoice_id
AND b.po_distribution_id = c.po_distribution_id(+)
AND c.po_header_id = d.po_header_id(+)
AND e.vendor_id(+) = d.vendor_id
AND f.vendor_site_id(+) = d.vendor_site_id
AND d.po_header_id = g.po_header_id
AND c.po_line_id = g.po_line_id
AND a.invoice_id = h.invoice_id
AND h.check_id = i.check_id
AND f.vendor_site_id = i.vendor_site_id
AND c.po_header_id IS NOT NULL
AND a.payment_status_flag = 'Y'
AND d.type_lookup_code != 'BLANKET';

Which User is Locking Table Query

SELECT c.owner,
c.object_name,
c.object_type,
fu.user_name locking_fnd_user_name,
fl.start_time locking_fnd_user_login_time,
vs.module,
vs.machine,
vs.osuser,
vlocked.oracle_username,
vs.SID,
vp.pid,
vp.spid AS os_process,
vs.serial#,
vs.status,
vs.saddr,
vs.audsid,
vs.process

FROM fnd_logins fl,
fnd_user fu,
v$locked_object vlocked,
v$process vp,
v$session vs,
dba_objects c

WHERE vs.SID = vlocked.session_id
AND vlocked.object_id = c.object_id
AND vs.paddr = vp.addr
AND vp.spid = fl.process_spid(+)
AND vp.pid = fl.pid(+)
AND fl.user_id = fu.user_id(+)
AND c.object_name LIKE '%' || UPPER ('&tabname_blank4all') || '%'
AND NVL (vs.status, 'XX') != 'KILLED';

To Get Concurrent Program Prameters Query

SELECT
fcpl.user_concurrent_program_name,
fcp.concurrent_program_name,
par.end_user_column_name,
par.form_left_prompt prompt,
par.enabled_flag,
par.required_flag,
par.display_flag

FROM fnd_concurrent_programs fcp,
fnd_concurrent_programs_tl fcpl,
fnd_descr_flex_col_usage_vl par

WHERE fcp.concurrent_program_id = fcpl.concurrent_program_id
AND fcpl.user_concurrent_program_name = :conc_prg_name
AND fcpl.LANGUAGE = 'US'
AND par.descriptive_flexfield_name = '$SRS$.' || fcp.concurrent_program_name;

Submitting Job Query

DECLARE
l_job number;
BEGIN
DBMS_JOB.SUBMIT(l_job,'DBMS_MVIEW.REFRESH(''VIEW_LICENSE_TS_MAIN_IB_NEW_MV'');');
DBMS_OUTPUT.PUT_LINE('JOB : '||l_job);
COMMIT;
END;

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

SELECT * FROM USER_JOBS WHERE JOB= 63362

=====================
Job locks
====================
SELECT SID, TYPE, ID1, ID2
FROM V$LOCK
WHERE TYPE = 'JQ';

Setting Org ID for the Current Session Query From Backend

BEGIN
dbms_application_info.set_client_info(539);
END;

begin
fnd_client_info.set_org_context('543');
end;

Purchase Requisition to Purchase Order Query

SELECT DISTINCT u.description "Requestor"
,porh.segment1 AS "Req Number"
,TRUNC (porh.creation_date) "Created On"
,pord.last_updated_by
,porh.authorization_status "Status"
,porh.description "Description"
,poh.segment1 "PO Number"
,TRUNC (poh.creation_date) "PO Creation Date"
,poh.authorization_status "PO Status"
,TRUNC (poh.approved_date) "Approved Date"
FROM apps.po_headers_all poh
,apps.po_distributions_all pod
,apps.po_req_distributions_all pord
,apps.po_requisition_lines_all porl
,apps.po_requisition_headers_all porh
,apps.fnd_user u
WHERE porh.requisition_header_id = porl.requisition_header_id
AND porl.requisition_line_id = pord.requisition_line_id
AND pord.distribution_id = pod.req_distribution_id(+)
AND pod.po_header_id = poh.po_header_id(+)
AND porh.created_by = u.user_id
AND poh.segment1 = 'I-41174'
ORDER BY 2

PO's Without Requesition Query

SELECT prh.segment1 "PR NUM"
,TRUNC (prh.creation_date) "CREATED ON"
,TRUNC (prl.creation_date) "Line Creation Date"
,prl.line_num "Seq #"
,msi.segment1 "Item Num"
,prl.item_description "Description"
,prl.quantity "Qty"
,TRUNC (prl.need_by_date) "Required By"
,ppf1.full_name "REQUESTOR"
,ppf2.agent_name "BUYER"

FROM po.po_requisition_headers_all prh
,po.po_requisition_lines_all prl
,apps.per_people_f ppf1
, (SELECT DISTINCT agent_id
,agent_name
FROM apps.po_agents_v) ppf2
,po.po_req_distributions_all prd
,inv.mtl_system_items_b msi
,po.po_line_locations_all pll
,po.po_lines_all pl
,po.po_headers_all ph

WHERE prh.requisition_header_id = prl.requisition_header_id
AND prl.requisition_line_id = prd.requisition_line_id
AND ppf1.person_id = prh.preparer_id
AND prh.creation_date BETWEEN ppf1.effective_start_date
AND ppf1.effective_end_date
AND ppf2.agent_id(+) = msi.buyer_id
AND msi.inventory_item_id = prl.item_id
AND msi.organization_id = prl.destination_organization_id
AND pll.line_location_id(+) = prl.line_location_id
AND pll.po_header_id = ph.po_header_id(+)
AND pll.po_line_id = pl.po_line_id(+)
AND prh.authorization_status = 'APPROVED'
AND pll.line_location_id IS NULL
AND prl.closed_code IS NULL
AND NVL (prl.cancel_flag, 'N') <> 'Y'
ORDER BY 1, 2

PO, Supplier and Invoice Query

SELECT a.org_id "ORG ID"
,e.vendor_name "VENDOR NAME"
,UPPER (e.vendor_type_lookup_code) "VENDOR TYPE"
,f.vendor_site_code "VENDOR SITE"
,f.address_line1 "ADDRESS"
,f.city "CITY"
,f.country "COUNTRY"
,TO_CHAR (TRUNC (d.creation_date)) "PO DATE"
,d.segment1 "PO NUMBER"
,d.type_lookup_code "PO TYPE"
,c.quantity_ordered "QTY ORDERED"
,c.quantity_cancelled "QTY CANCALLED"
,g.item_description "ITEM DESCRIPTION"
,g.unit_price "UNIT PRICE"
, (NVL (c.quantity_ordered, 0) - NVL (c.quantity_cancelled, 0))
* NVL (g.unit_price, 0) "PO Line Amount"
, (SELECT DECODE (ph.approved_flag, 'Y', 'Approved')
FROM po.po_headers_all ph
WHERE ph.po_header_id = d.po_header_id) "PO STATUS"
,a.invoice_type_lookup_code "INVOICE TYPE"
,a.invoice_amount "INVOICE AMOUNT"
,TO_CHAR (TRUNC (a.invoice_date)) "INVOICE DATE"
,a.invoice_num "INVOICE NUMBER"
, (SELECT DECODE (x.match_status_flag, 'A', 'Approved')
FROM ap.ap_invoice_distributions_all x
WHERE x.invoice_distribution_id = b.invoice_distribution_id)
"Invoice Approved?"
,a.amount_paid
,h.amount
,i.check_number "CHEQUE NUMBER"
,TO_CHAR (TRUNC (i.check_date)) "PAYMENT DATE"
FROM ap.ap_invoices_all a
,ap.ap_invoice_distributions_all b
,po.po_distributions_all c
,po.po_headers_all d
,po.po_vendors e
,po.po_vendor_sites_all f
,po.po_lines_all g
,ap.ap_invoice_payments_all h
,ap.ap_checks_all i
WHERE a.invoice_id = b.invoice_id
AND b.po_distribution_id = c.po_distribution_id(+)
AND c.po_header_id = d.po_header_id(+)
AND e.vendor_id(+) = d.vendor_id
AND f.vendor_site_id(+) = d.vendor_site_id
AND d.po_header_id = g.po_header_id
AND c.po_line_id = g.po_line_id
AND a.invoice_id = h.invoice_id
AND h.check_id = i.check_id
AND f.vendor_site_id = i.vendor_site_id
AND c.po_header_id IS NOT NULL
AND a.payment_status_flag = 'Y'
AND d.type_lookup_code != 'BLANKET'

On Hand Quantity on any historical date Query

SELECT SUM (target_qty),
item_id
FROM (SELECT moqv.subinventory_code subinv,
moqv.inventory_item_id item_id,
SUM (transaction_quantity) target_qty
FROM mtl_onhand_qty_cost_v moqv
WHERE moqv.organization_id = :org_id
AND moqv.inventory_item_id = :item_id
GROUP BY moqv.subinventory_code,
moqv.inventory_item_id,
moqv.item_cost
UNION
SELECT mmt.subinventory_code subinv,
mmt.inventory_item_id item_id,
-SUM (primary_quantity) target_qty
FROM mtl_material_transactions mmt,
mtl_txn_source_types mtst
WHERE mmt.organization_id = :org_id
AND transaction_date >= TO_DATE (:hist_date) + 1
AND mmt.transaction_source_type_id =
mtst.transaction_source_type_id
AND mmt.inventory_item_id = :item_id
GROUP BY mmt.subinventory_code,
mmt.inventory_item_id) oq
GROUP BY oq.item_id

Kill the Job and Session Query

select * from all_objects where object_name ='VIEW_LICENSE_TS_MAIN_IB_NEW_MV';

select * from all_objects where object_id = 1443040;

select * from v$locked_object where object_id=1443040;

SELECT * --SID, TYPE, ID1, ID2
FROM V$LOCK
where type ='JI'; -- JI stands for MView is running

select * from dba_jobs_running
--where rownum =1
where job=57609
AND SID =268;

select sid, serial#, command, taddr from v$session where sid=268 and serial# =20279;

alter system kill session '268,20279';

select * from v$locked_object where object_id=1612369;

select used_ublk from v$transaction where ADDR=;

BEGIN
DBMS_JOB.REMOVE(57609);
COMMIT;
END;

Getting Customer Address of a Sales Order Query

SELECT ooh.order_number,
hp_bill.party_name,
hl_ship.address1 ||
DECODE (hl_ship.address2, NULL, '', CHR (10)) ||
hl_ship.address2 ||
DECODE (hl_ship.address3, NULL, '', CHR (10)) ||
hl_ship.address3 ||
DECODE (hl_ship.address4, NULL, '', CHR (10)) ||
hl_ship.address4 ||
DECODE (hl_ship.city, NULL, '', CHR (10)) ||
hl_ship.city ||
DECODE (hl_ship.state, NULL, '', ',') ||
hl_ship.state ||
DECODE (hl_ship.postal_code, '', ',') ||
hl_ship.postal_code ship_to_address,
hl_bill.address1 ||
DECODE (hl_bill.address2, NULL, '', CHR (10)) ||
hl_bill.address2 ||
DECODE (hl_bill.address3, NULL, '', CHR (10)) ||
hl_bill.address3 ||
DECODE (hl_bill.address4, NULL, '', CHR (10)) ||
hl_bill.address4 ||
DECODE (hl_bill.city, NULL, '', CHR (10)) ||
hl_bill.city ||
DECODE (hl_bill.state, NULL, '', ',') ||
hl_bill.state ||
DECODE (hl_bill.postal_code, '', ',') ||
hl_bill.postal_code bill_to_address,
ooh.transactional_curr_code currency_code,
mp.organization_code,
ooh.fob_point_code,
ooh.freight_terms_code,
ooh.cust_po_number

FROM oe_order_headers_all ooh,
hz_cust_site_uses_all hcs_ship,
hz_cust_acct_sites_all hca_ship,
hz_party_sites hps_ship,
hz_parties hp_ship,
hz_locations hl_ship,
hz_cust_site_uses_all hcs_bill,
hz_cust_acct_sites_all hca_bill,
hz_party_sites hps_bill,
hz_parties hp_bill,
hz_locations hl_bill,
mtl_parameters mp

WHERE 1 = 1
--AND header_id = :p_header_id
AND ooh.order_number =:p_order_number
AND ooh.ship_to_org_id = hcs_ship.site_use_id
AND hcs_ship.cust_acct_site_id = hca_ship.cust_acct_site_id
AND hca_ship.party_site_id = hps_ship.party_site_id
AND hps_ship.party_id = hp_ship.party_id
AND hps_ship.location_id = hl_ship.location_id
AND ooh.invoice_to_org_id = hcs_bill.site_use_id
AND hcs_bill.cust_acct_site_id = hca_bill.cust_acct_site_id
AND hca_bill.party_site_id = hps_bill.party_site_id
AND hps_bill.party_id = hp_bill.party_id
AND hps_bill.location_id = hl_bill.location_id
AND mp.organization_id(+) = ooh.ship_from_org_id;

Oracle Forms Compilation

==================
porting form
===================

cp XXXXSHIPCONTACT.fmb XXXXSHIPCONTACT_BKP_20APR10.fmb

cp XXXXSHIPCONTACT.fmb /u01/oracle/tst2appl/au/11.5.0/forms/US/XXXXSHIPCONTACT.fmb

cp XXXXSHIPCONTACT.fmb /u01/oracle/tst2appl/xxssft/forms/US/XXXXSHIPCONTACT.fmb

cp XXXXSHIPCONTACT.fmb $XXSSFT_TOP/forms/US/XXXXSHIPCONTACT.fmb

cd $AU_TOP/forms/US

cd $XXSSFT_TOP/forms/US

ls -lrt XXXXSHIPCONTACT*

ls -lrt $XXSSFT_TOP/forms/US/XXXXSHIPCONTACT*

ls -lrt /u01/oracle/tst2appl/xxssft/forms/US/XXXXSHIPCONTACT.fmx

FORMS60_PATH=$FORMS_60_PATH:$AU_TOP/forms/US

f60gen module =XXXX.fmb userid =apps/apps output_file=XXXXSHIPCONTACT.fmx module_type=form batch=no compile_all=special

f60gen module=XXXX.fmb userid=apps/apps output_file=$XXSSFT_TOP/forms/US/XXXX.fmx module_type=form batch=no compile_all=special

f60gen module =XXXX.fmb userid =apps/apps output_file=XXXX.fmx module_type=form batch=no compile_all=special

f60gen module =XXXX.fmb userid =apps/apps output_file=$XXSSFT_TOP/forms/US/XXXXOICPAF.fmx module_type=form batch=no compile_all=special

OE_ORDERS.Ship_To_Contact('WHEN-NEW-ITEM-INSTANCE');
OE_ORDERS.Ship_To_Contact('KEY-LISTVAL');
OE_ORDERS.Ship_To_Contact('WHEN-VALIDATE-ITEM');
OE_ORDERS.Ship_To_Contact('POST-TEXT-ITEM');

====================
Porting Custom.pll
====================

cd $AU_TOP/resource /u01/oracle/tst2appl/au/11.5.0/resource

cp CUSTOM.pll CUSTOM_11_May_10.pll

cp CUSTOM.plx CUSTOM_11_May_10.plx

ls -lrt CUSTOM*

put CUSTOM.pll

f60gen module=CUSTOM.pll userid=apps/apps output_file=CUSTOM.plx module_type=library batch=no compile_all=special

f60gen module=D2kwutil.pll userid=apps/apps output_file=D2kwutil.plx module_type=library batch=no compile_all=special

Finding Oracle Version Query

SELECT product
,VERSION
,status
FROM product_component_version;

Finding Oracle Application Product Version Query

SELECT SUBSTR (a.application_name, 1, 60) application_name,
SUBSTR (i.product_version, 1, 4) VERSION,
i.patch_level,
i.application_id,
i.last_update_date
FROM apps.fnd_product_installations i,
apps.fnd_application_all_view a
WHERE i.application_id = a.application_id
ORDER BY a.application_name

Displaying column values in Row Query

(SELECT SUBSTR (MAX (SYS_CONNECT_BY_PATH (instance_number, ',')), 2)
FROM (SELECT ool.line_id, instance_number,
ROW_NUMBER () OVER (PARTITION BY line_id ORDER BY instance_number) rn
FROM csi_item_instances csi,
oe_order_lines_all ool
WHERE ool.line_id = oola.line_id
AND ool.line_id = csi.last_oe_order_line_id
AND ool.inventory_item_id = csi.inventory_item_id
AND TRUNC (SYSDATE) BETWEEN TRUNC (active_start_date)
AND TRUNC
(NVL (active_end_date,
SYSDATE
)
))
START WITH rn = 1
CONNECT BY PRIOR rn = rn - 1 AND PRIOR line_id = line_id) "IB Instance #",

Debugging PO Query

Stage 1: PO Creation :

PO_HEADERS_ALL

select po_header_id from po_headers_all where segment1 =;

select * from po_headers_all where po_header_id =;

po_lines_all

select * from po_lines_all where po_header_id =;

po_line_locations_all

select * from po_line_locations_all where po_header_id =;

po_distributions_all

select * from po_distributions_all where po_header_id =;

po_releases_all

SELECT * FROM po_releases_all WHERE po_header_id =;

Stage 2: Once PO is received data is moved to respective receving tables and inventory tables

RCV_SHIPMENT_HEADERS

select * from rcv_shipment_headers where shipment_header_id in
(select shipment_header_id from rcv_shipment_lines
where po_header_id =);

RCV_SHIPMENT_LINES

select * from rcv_shipment_lines where po_header_id =;

RCV_TRANSACTIONS

select * from rcv_transactions where po_header_id =;

RCV_ACCOUNTING_EVENTS

SELECT * FROM rcv_Accounting_Events WHERE rcv_transaction_id IN
(select transaction_id from rcv_transactions
where po_header_id =);

RCV_RECEIVING_SUB_LEDGER

select * from rcv_receiving_sub_ledger where rcv_transaction_id in (select transaction_id from rcv_transactions where po_header_id =);

RCV_SUB_LEDGER_DETAILS

select * from rcv_sub_ledger_details
where rcv_transaction_id in (select transaction_id from rcv_transactions where po_header_id =);

MTL_MATERIAL_TRANSACTIONS

select * from mtl_material_transactions where transaction_source_id =;

MTL_TRANSACTION_ACCOUNTS

select * from mtl_transaction_accounts where transaction_id in ( select transaction_id from mtl_material_transactions where transaction_source_id = =);

Stage 3: Invoicing details

AP_INVOICE_DISTRIBUTIONS_ALL

select * from ap_invoice_distributions_all where po_distribution_id in ( select po_distribution_id from po_distributions_all where po_header_id =);

AP_INVOICES_ALL

select * from ap_invoices_all where invoice_id in
(select invoice_id from ap_invoice_distributions_all where po_distribution_id in
( select po_distribution_id from po_distributions_all where po_header_id =));

Stage 4 : Many Time there is tie up with Project related PO

PA_EXPENDITURE_ITEMS_ALL

select * from pa_expenditure_items_all peia where peia.orig_transaction_reference in
( select to_char(transaction_id) from mtl_material_transactions
where transaction_source_id = );

Stage 5 : General Ledger

Prompt 17. GL_BC_PACKETS ..This is for encumbrances

SELECT * FROM gl_bc_packets WHERE reference2 IN ('');

GL_INTERFACE

SELECT *
FROM GL_INTERFACE GLI
WHERE user_je_source_name ='Purchasing'
AND gl_sl_link_table ='RSL'
AND reference21='PO'
AND EXISTS
( SELECT 1
FROM rcv_receiving_sub_ledger RRSL
WHERE GLI.reference22 =RRSL.reference2
AND GLI.reference23 =RRSL.reference3
AND GLI.reference24 =RRSL.reference4
AND RRSL.rcv_transaction_id in
(select transaction_id from rcv_transactions
where po_header_id ));

GL_IMPORT_REFERENCES

SELECT *
FROM gl_import_references GLIR
WHERE reference_1='PO'
AND gl_sl_link_table ='RSL'
AND EXISTS
( SELECT 1
FROM rcv_receiving_sub_ledger RRSL
WHERE GLIR.reference_2 =RRSL.reference2
AND GLIR.reference_3 =RRSL.reference3
AND GLIR.reference_4 =RRSL.reference4
AND RRSL.rcv_transaction_id in
(select transaction_id from rcv_transactions
where po_header_id =))

Converting amount in numbers to words Query

SELECT UPPER (ap_amount_utilities_pkg.ap_convert_number (111234234324)) AS amt_in_words FROM DUAL; -- For Upper Case Letters

SELECT INITCAP (ap_amount_utilities_pkg.ap_convert_number (111234234324)) AS amt_in_words FROM DUAL; -- For Mixed Case Letters

SELECT LOWER (ap_amount_utilities_pkg.ap_convert_number (111234234324)) AS amt_in_words FROM DUAL -- For Lower Case Letters

Canceled Requisitions Query

SELECT prh.requisition_header_id
,prh.preparer_id
,prh.segment1 "REQ NUM"
,TRUNC (prh.creation_date)
,prh.description
,prh.note_to_authorizer
FROM apps.po_requisition_headers_all prh, apps.po_action_history pah
WHERE action_code = 'CANCEL'
AND pah.object_type_code = 'REQUISITION'
AND pah.object_id = prh.requisition_header_id

SO PO Requesition Query

SELECT oh.order_number
,oh.header_id
,ol.line_id
,prh.requisition_header_id
,prh.segment1 req_number
,prl.requisition_line_id
,pl.po_line_id
,poh.po_header_id
,poh.segment1 po_number
FROM oe_order_headers_all oh
,oe_order_lines_all ol
,po_requisition_headers_all prh
,po_requisition_lines_all prl
,po_line_locations_all pol
,po_headers_all poh
,po_lines_all pl
WHERE ol.header_id = oh.header_id
AND prh.interface_source_line_id = ol.line_id
AND prh.interface_source_code = 'CTO'
AND prh.requisition_header_id = prl.requisition_header_id
AND prl.line_location_id = pol.line_location_id
AND pol.po_header_id = poh.po_header_id
AND pol.po_line_id = pl.po_line_id
AND ol.line_id = &sales_order_line_id;

Script to Add #WFM_CC Attribute to the orders

-- Cursor for already booked orders
DECLARE
CURSOR cur_err_orders
IS
SELECT wias.item_key
FROM wf_item_activity_statuses wias, wf_process_activities wpa
WHERE 1 = 1
AND wias.item_type = 'OEOH'
AND wias.activity_status = 'ERROR'
AND instance_label = 'XXDP_ORD_BOOK_ACK_SALES_REP_FU'
--'XXDP_ORD_BOOK_ACK_FUNC'
AND wias.process_activity = wpa.instance_id;

--AND wias.item_key = '1094113';

-- Cursor for Entered Orders
CURSOR cur_entered_ord
IS
SELECT DISTINCT h.header_id
FROM oe_order_headers_all h, oe_workflow_assignments w
WHERE TRUNC (h.creation_date) < '05-AUG-2010'
AND flow_status_code = 'ENTERED'
--and trunc(booked_date)>='05-AUG-2010'
AND h.order_type_id = w.order_type_id
AND w.end_date_active IS NULL
AND process_name IN (
SELECT DISTINCT p.process_name
FROM wf_process_activities p,
wf_process_activities a
WHERE p.instance_label = a.process_name
AND a.instance_label =
'XXDP_ORD_BOOK_ACK_SALES_REP_FU');
--AND h.header_id = 1129493;
BEGIN
FOR rec_err_orders IN cur_err_orders
LOOP
wf_engine.additemattr ('OEOH',
rec_err_orders.item_key,
'#WFM_CC',
NULL,
NULL,
NULL
);
COMMIT;
END LOOP;

FOR rec_entered_ord IN cur_entered_ord
LOOP
wf_engine.additemattr ('OEOH',
rec_entered_ord.header_id,
'#WFM_CC',
NULL,
NULL,
NULL
);
COMMIT;
END LOOP;
END;
/

Script to extract expense invoices for a Company for given period Query

SELECT
sob.NAME set_of_books
,flv.meaning TYPE
,ven.vendor_name supplier
,ven.segment1 supplier_num
,pvs.vendor_site_code site
,inv.invoice_date
,inv.terms_date receipt_date
,inv.invoice_num
,inv.invoice_currency_code
,inv.invoice_amount
,inv.base_amount functional_amount
,inv.doc_sequence_value voucher_number
,NVL (inv.tax_amount, 0) tax_amount
,DECODE (inv.auto_tax_calc_flag
,'Y', 'Header Level'
,'L', 'Line Level'
,'N', 'None'
,'T', 'Tax Code Level'
,NULL
) tax_calculation_level
,flv2.meaning payment_method
,inv.gl_date
,att.NAME terms
,inv.pay_group_lookup_code
,inv.exclusive_payment_flag pay_alone
, (SELECT (SUM (NVL (amount, 0)))
FROM ap_invoice_distributions_all
WHERE invoice_id = inv.invoice_id
AND line_type_lookup_code = 'AWT') withheld_amount
, (SELECT (SUM (NVL (amount, 0)))
FROM ap_invoice_distributions_all
WHERE invoice_id = inv.invoice_id
AND ( line_type_lookup_code = 'PREPAY'
OR line_type_lookup_code = 'TAX'
AND prepay_tax_parent_id IS NOT NULL
)) prepaid_amount
, gcc.segment1
|| '.'
|| gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment5
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment6 liability_account
,inv.payment_currency_code
,inv.payment_cross_rate_date
,inv.description

FROM ap_invoices_all inv
,fnd_lookup_values flv
,fnd_lookup_values flv2
,po_vendors ven
,po_vendor_sites_all pvs
,gl_sets_of_books sob
,ap_terms_tl att
,gl_code_combinations gcc

WHERE 1 = 1
/*AND inv.invoice_num IN
('91000795'
,'5084'
,'966601109151 - CN'
,'199545'
,'199547'
,'INV0538'
,'46249'
,'2098'
)*/
--AND inv.org_id IN (543, 718)
AND flv.lookup_type = 'INVOICE TYPE'
AND flv.view_application_id = 200
AND flv.lookup_code = inv.invoice_type_lookup_code
AND inv.invoice_type_lookup_code <> 'DEBIT'
AND flv2.lookup_type = 'PAYMENT METHOD'
AND flv2.view_application_id = 200
AND flv2.lookup_code = inv.payment_method_lookup_code
AND ven.vendor_id = inv.vendor_id
AND pvs.vendor_id = inv.vendor_id
AND pvs.vendor_site_id = inv.vendor_site_id
AND sob.set_of_books_id = inv.set_of_books_id
AND att.term_id = inv.terms_id
AND att.LANGUAGE = 'US'
AND gcc.code_combination_id = inv.accts_pay_code_combination_id
AND gcc.segment1 = '2'
AND inv.invoice_date BETWEEN '01-JAN-2009' AND '31-MAR-2010'
ORDER BY gl_date, invoice_num

Responsibilities assigned to User Query

SELECT fu.user_name
,fu.user_id
,rv.responsibility_name
FROM apps.fnd_user_resp_groups_direct ur
,fnd_responsibility_vl rv
,fnd_user fu
WHERE fu.user_id = ur.user_id
AND ur.responsibility_id = rv.responsibility_id
--AND ur.user_id = 19228
--AND rv.responsibility_name like 'NUAN US MLB%'
AND fu.user_name = 'AAOFFSHORE'
;

Reset the Oracle Apps user PWD Query

BEGIN
IF fnd_user_pkg.changepassword ('&uname', '&pswd')
THEN
DBMS_OUTPUT.put_line ('Password changed successfully');
ELSE
DBMS_OUTPUT.put_line ('Failed changing password');
END IF;
commit;
END;

List Salesrep Salary Query

SELECT
p.person_id
,p.full_name
,p.employee_number
,p.business_group_id
, (pp.proposed_salary_n * b.pay_annualization_factor) salary
,e.aei_information3 VARIABLE

FROM per_all_people_f p
,per_all_assignments_f a
,per_pay_proposals pp
,per_pay_bases b
,per_assignment_extra_info e

WHERE
NVL (p.effective_start_date, SYSDATE) <= SYSDATE
AND p.person_id = 19637
AND NVL (p.effective_end_date, SYSDATE) >= SYSDATE
AND p.person_id = a.person_id
AND NVL (a.effective_end_date, SYSDATE) >= SYSDATE
AND pp.assignment_id = a.assignment_id
AND ( NVL (pp.date_to, SYSDATE) >= SYSDATE
OR pp.date_to = (SELECT MAX (pp_sub.date_to)
FROM per_pay_proposals pp_sub
WHERE pp_sub.assignment_id = pp.assignment_id)
)
AND b.pay_basis_id = a.pay_basis_id
AND e.assignment_id = a.assignment_id
AND e.information_type = 'SSFT_COMMISSION_PLAN'
AND e.aei_information4 IN
('Commission Direct Selling'
,'Commission Sales Support'
,'Commission Sales Engineers')
AND NVL (TO_DATE (e.aei_information1, 'DD-MON-YYYY'), SYSDATE) <= SYSDATE
AND NVL (TO_DATE (e.aei_information2, 'DD-MON-YYYY'), SYSDATE) >= SYSDATE;

Menus Responsibilities Query

SELECT *

FROM fnd_menus_tl

WHERE menu_id IN
(
SELECT DISTINCT menu_id
FROM fnd_responsibility_vl frv
,fnd_responsibility_tl ftr
WHERE frv.responsibility_id = frt.responsibility_id
AND frt.responsibility_name LIKE 'NUAN%Order%'
AND frt.responsibility_name NOT LIKE '%Setup Only'
AND frt.responsibility_name NOT LIKE '%Inquiry'
)

Function Assigned to Responsibilities Query

SELECT
fff.function_name
,frv.responsibility_name
,frv.description

FROM fnd_responsibility_vl frv,
fnd_form_functions fff

WHERE 1=1
AND fff.function_name = 'PA_AF_CREATE_AGREEMENT'
AND frv.menu_id NOT IN (
SELECT frf.action_id
FROM fnd_resp_functions frf
WHERE frf.action_id = frv.menu_id
AND frf.rule_type = 'M')
AND frv.menu_id IN (SELECT me.menu_id
FROM fnd_menu_entries me
START WITH me.function_id = fff.function_id
CONNECT BY PRIOR me.menu_id = me.sub_menu_id)
AND fff.function_id NOT IN (
SELECT frf.action_id
FROM fnd_resp_functions frf
WHERE frf.action_id = fff.function_id
AND frf.rule_type = 'F')
AND frv.responsibility_name LIKE 'NUAN US%'
;

Designation of Employee Query In Oracle

SELECT
papf.person_id
,papf.employee_number
,papf.first_name
,papf.full_name
,papf.sex
,papf.date_of_birth
,papf.effective_start_date papf_start_date_active
,papf.effective_end_date papf_end_date_active
,mgr.full_name manager
,mgr.employee_number mgr_emp_no
,mgr.person_id mgr_prson_id
,paaf.assignment_number
,paaf.effective_start_date assingment_start_date
,pj.job_id
,pj.NAME
,usr.user_id
,usr.user_name
,usr.last_logon_date
,jrs.salesrep_id
,jrs.resource_id
,jrs.salesrep_number
,jrs.name salesrep_name
,jrs.org_id sales_org_id
,jrs.start_date_active rep_start_date_active
,jrs.end_date_active rep_end_date_active

FROM
per_all_people_f papf
,per_all_people_f mgr
,per_all_assignments_f paaf
,per_jobs pj
,fnd_user usr
,jtf_rs_salesreps jrs

WHERE 1 = 1
AND papf.person_id = paaf.person_id (+)
AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date AND NVL (papf.effective_end_date, TRUNC (SYSDATE))
AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date (+) AND NVL (paaf.effective_end_date (+), TRUNC (SYSDATE))
AND paaf.job_id = pj.job_id (+)
AND paaf.business_group_id = pj.business_group_id (+)
AND papf.party_id = usr.person_party_id (+)
AND TRUNC (SYSDATE) BETWEEN usr.start_date (+) AND NVL (usr.end_date (+) , TRUNC (SYSDATE))
AND paaf.supervisor_id = mgr.person_id (+)
AND TRUNC (SYSDATE) BETWEEN mgr.effective_start_date (+) AND NVL (mgr.effective_end_date (+), TRUNC (SYSDATE))
AND paaf.assignment_type (+) = 'E'
AND papf.person_id = jrs.person_id (+)
AND papf.full_name LIKE '%Behan, Linda%'
--AND papf.employee_number = '30880'
--AND papf.person_id = 36557
--AND mgr.employee_number = '141968'
--AND usr.user_name = 'AAOFFSHORE'
--AND usr.user_ID = 1234
--AND jrs.salesrep_id = 100009572
--AND jrs.resource_id = 100010609
--AND jrs.salesrep_number = 'XXSR100'
--AND jrs.name like 'Bashaar%'
;

Document on Creation of LDT Files in Oracle

This document explains how to create LDT file for following things:

1. Profile Options
2. Forms
3. Functions
4. Menus
5. Responsibilities
6. Request Groups
7. Request Sets
8. Lookups
9. Value Sets
10. Descriptive flex-fields
11. Key flex-fields
12. Concurrent programs
13. Form personalization
14. FND Users
15. Alerts

1.Profile Options:

Source:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct XXPRNAME.ldt
PROFILE PROFILE_NAME="XXPRNAME" APPLICATION_SHORT_NAME="PN"

Target:
FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct XXPRNAME.ldt

FNDLOAD apps/apps 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/afscprof.lct XXPRNAME.ldt PROFILE PROFILE_NAME=" XXPRNAME" APPLICATION_SHORT_NAME="PN"

2.Forms:

Source:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXFRMNAME.ldt FORM APPLICATION_SHORT_NAME="PN" FORM_NAME="XXFRMNAME"

Target:
FNDLOAD apps/apps 0 Y UPLOAD @FND:patch/115/import/afsload.lct XXFRMNAME.ldt

3.Functions:

Source:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXFUNNAME.ldt FUNCTION FUNC_APP_SHORT_NAME="PN" FUNCTION_NAME="XXFUNNAME"

Target:
FNDLOAD apps/apps O Y UPLOAD @FND:patch/115/import/afsload.lct XXFUNNAME.ldt

4.Menus:

Source:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXMNNAME.ldt MENU MENU_NAME="XXMNNAME"

Target:
FNDLOAD apps/apps 0 Y UPLOAD @FND:patch/115/import/afsload.lct XXMNNAME.ldt

5.Responsibilities:

Source:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct XXRESNAME.ldt FND_RESPONSIBILITY RESP_KEY="XXRESNAME"

Target:
1. FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct XXRESNAME.ldt

2.FNDLOAD apps/apps 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/afscursp.lct XXRESNAME.ldt FND_RESPONSIBILITY RESP_KEY="XXRESNAME" APPLICATION_SHORT_NAME="PN"

6.Request Groups:

Source:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct XXRQGNAME.ldt REQUEST_GROUP REQUEST_GROUP_NAME="XXRQGNAME" APPLICATION_SHORT_NAME="PN"

Target:
1.FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct XXRQGNAME.ldt

2.FNDLOAD apps/apps 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/afcpreqg.lct XXRQGNAME.ldt REQUEST_GROUP REQUEST_GROUP_NAME="XXRQGNAME" APPLICATION_SHORT_NAME="PN"

7.Request Sets:

Source:
1.FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XXRQSNAME.ldt REQ_SET REQUEST_SET_NAME="XXRQSNAME"

2.FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XXRQSLNAME.ldt REQ_SET_LINKS REQUEST_SET_NAME="XXRQSNAME"

Target:
1.FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct XXRQSNAME.ldt

2.FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct XXRQSLNAME.ldt

8.Lookups:

Source:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct XXLKPNAME.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="PN" LOOKUP_TYPE="XXLKPNAME"

Target:
1.FNDLOAD apps/apps 0 Y UPLOAD aflvmlu.lct XXLKPNAME.ldt

2.FNDLOAD apps/apps 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/aflvmlu.lct XXLKPNAME.ldt FND_LOOKUP_TYPE LOOKUP_TYPE="XXLKPNAME" APPLICATION_SHORT_NAME="PN"

9.Value sets:

Source:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XXVALSNAME.ldt VALUE_SET FLEX_VALUE_SET_NAME="XXVALSNAME"

Target:
1.FNDLOAD apps/apps 0 Y UPLOAD afffload.lct XXVALSNAME.ldt

2.FNDLOAD apps/apps 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/afffload.lct XXVALSNAME.ldt VALUE_SET FLEX_VALUE_SET_NAME="XXVALSNAME" APPLICATION_SHORT_NAME="PN"

10.Descriptive Flex-fields:

Source:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XXDFFNAME.ldt DESC_FLEX P_LEVEL='COL_ALL:REF_ALL:CTX_ONE:SEG_ALL' APPLICATION_SHORT_NAME="PN"

DESCRIPTIVE_FLEXFIELD_NAME="PN_LEASE_DETAILS" P_CONTEXT_CODE="Global Data Elements"

Target:
FNDLOAD apps/apps 0 Y UPLOAD @FND:patch/115/import/afffload.lct XXDFFNAME.ldt

11.Key Flex-fields:

Source:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XXKFFNAME.ldt KEY_FLEX P_LEVEL=’COL_ALL:FQL_ALL:SQL_ALL:STR_ONE:WFP_ALL:SHA_ALL:CVR_ALL:SEG_ALL’

APPLICATION_SHORT_NAME="FND" ID_FLEX_CODE="key flex code" P_STRUCTURE_CODE="structure name”

Target:
FNDLOAD apps/apps 0 Y UPLOAD @FND:patch/115/import/afffload.lct XXKFFNAME.ldt

12.Concurrent Programs:

Source:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XXCPNAME.ldt PROGRAM APPLICATION_SHORT_NAME="PN" CONCURRENT_PROGRAM_NAME="XXCPNAME"

Target:
1.FNDLOAD apps/apps 0 Y UPLOAD @FND:patch/115/import/afcpprog.lct XXCPNAME.ldt

2.FNDLOAD apps/apps 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/afcpprog.lct XXCPNAME.ldt PROGRAM CONCURRENT_PROGRAM_NAME="XXCPNAME" APPLICATION_SHORT_NAME="PN"

13.Form Personalization:

Source:
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct XXFPNAME.ldt FND_FORM_CUSTOM_RULES function_name="XXFPNAME"

Target:
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct XXFPNAME.ldt

14.FND Users:

Source:
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct ./XXUSERNAME.ldt FND_USER USER_NAME='XXUSERNAME'

Target:
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct ./ XXUSERNAME.ldt

15.Alerts:

Source:
FNDLOAD apps/apps 0 Y DOWNLOAD $ALR_TOP/patch/115/import/alr.lct XXALERTNAME.ldt ALR_ALERTS APPLICATION_SHORT_NAME=PER Alert_name="XXALERTNAME"

Target:
FNDLOAD apps/apps 0 Y UPLOAD $ALR_TOP/patch/115/import/alr.lct XXALERTNAME.ldt ALR_ALERTS APPLICATION_SHORT_NAME=PER Alert_name="XXALERTNAME"

Expense report Extract Queries

-- To extract Expense reports for Companies that are based on France and Italy Operating Units
SELECT hou.NAME organization_name
,ven.vendor_name
,ven.vendor_type_lookup_code vendor_type
,inv.doc_sequence_value voucher_number
,inv.gl_date gl_date
,inv.invoice_num invoice_number
,inv.invoice_currency_code currency
,inv.invoice_amount
,invd.amount distribution_amount
,invd.description distribution_description
,gcc.code_combination_id
,gcc.segment1 company
,gcc.segment2 department
,gcc.segment3 ACCOUNT
,gcc.segment5 product
,gcc.segment4 region
,gcc.segment6 future
,DECODE (aip.invoice_payment_type
,'PREPAY', inv2.invoice_num
,ac.check_number
) document_number
,invd.period_name
,jh.je_source
,jh.NAME journal_entry
,jl.description line_description
,jl.accounted_dr
,jl.accounted_cr
,gps.period_year gl_date_year
,gps.quarter_num gl_date_quarter
,TO_CHAR (inv.gl_date, 'MON') gl_date_month
,TO_CHAR (inv.gl_date, 'DD') gl_date_day

FROM ap_invoices_all inv
,ap_invoice_distributions_all invd
,hr_organization_units hou
,po_vendors ven
,gl_code_combinations gcc
,ap_invoices_all inv2
,ap_invoice_payments_all aip
,ap_checks_all ac
,ax_events ae
,ax_sle_headers ash
,ax_sle_lines asl
,gl_je_lines jl
,gl_je_headers jh
,hr_operating_units ou
,gl_period_statuses gps

WHERE inv.invoice_id = invd.invoice_id
AND hou.organization_id = invd.org_id
AND ven.vendor_id = inv.vendor_id
--AND inv.invoice_num IN ( 'TEST_197525' , '215245')
AND inv.invoice_type_lookup_code = 'EXPENSE REPORT'
AND gcc.code_combination_id = invd.dist_code_combination_id
AND gcc.segment1 = '38'
AND inv.gl_date BETWEEN '01-JAN-2009' AND '31-DEC-2009'
AND inv.invoice_id = aip.invoice_id(+)
AND aip.other_invoice_id = inv2.invoice_id(+)
AND ac.check_id(+) = aip.check_id
AND ae.event_id = invd.accounting_event_id
AND asl.source_id = invd.invoice_distribution_id
AND ash.event_id = ae.event_id
AND asl.sle_header_id = ash.sle_header_id
AND jl.reference_10 = asl.reference_10
AND jl.reference_9 = asl.reference_9
AND jl.subledger_doc_sequence_value = asl.sle_header_id
AND jl.set_of_books_id = asl.set_of_books_id
AND jh.je_header_id = jl.je_header_id
AND ou.organization_id = hou.organization_id
AND (inv.gl_date BETWEEN gps.start_date AND gps.end_date)
AND gps.set_of_books_id = ou.set_of_books_id
AND gps.application_id = 200

UNION ALL

SELECT hou.NAME organization_name
,ven.vendor_name
,ven.vendor_type_lookup_code vendor_type
,inv.doc_sequence_value voucher_number
,inv.gl_date gl_date
,inv.invoice_num invoice_number
,inv.invoice_currency_code currency
,inv.invoice_amount
,NULL distribution_amount
,NULL distribution_description
,gcc.code_combination_id
,gcc.segment1 company
,gcc.segment2 department
,gcc.segment3 ACCOUNT
,gcc.segment5 product
,gcc.segment4 region
,gcc.segment6 future
,DECODE (aip.invoice_payment_type
,'PREPAY', inv2.invoice_num
,ac.check_number
) document_number
,gps.period_name
,jh.je_source
,jh.NAME journal_entry
,jl.description line_description
,jl.accounted_dr
,jl.accounted_cr
,gps.period_year gl_date_year
,gps.quarter_num gl_date_quarter
,TO_CHAR (inv.gl_date, 'MON') gl_date_month
,TO_CHAR (inv.gl_date, 'DD') gl_date_day

FROM ap_invoices_all inv
,hr_organization_units hou
,po_vendors ven
,gl_code_combinations gcc
,ap_invoices_all inv2
,ap_invoice_payments_all aip
,ap_checks_all ac
,ax_sle_lines asl
,gl_je_lines jl
,gl_je_headers jh
,hr_operating_units ou
,gl_period_statuses gps

WHERE 1 = 1
AND hou.organization_id = inv.org_id
AND ven.vendor_id = inv.vendor_id
-- AND inv.invoice_num IN ( 'TEST_197525' , '215245')
AND inv.invoice_type_lookup_code = 'EXPENSE REPORT'
AND gcc.code_combination_id = asl.code_combination_id
AND gcc.segment1 = '38'
AND inv.gl_date BETWEEN '01-JAN-2009' AND '31-DEC-2009'
AND inv.invoice_id = aip.invoice_id(+)
AND aip.other_invoice_id = inv2.invoice_id(+)
AND ac.check_id(+) = aip.check_id
AND asl.source_table = 'AP_INVOICES'
AND asl.source_id = inv.invoice_id
AND jl.reference_10 = asl.reference_10
AND jl.reference_9 = asl.reference_9
AND jl.subledger_doc_sequence_value = asl.sle_header_id
AND jl.set_of_books_id = asl.set_of_books_id
AND jh.je_header_id = jl.je_header_id
AND ou.organization_id = hou.organization_id
AND (inv.gl_date BETWEEN gps.start_date AND gps.end_date)
AND gps.set_of_books_id = ou.set_of_books_id
AND gps.application_id = 200
ORDER BY 5, 6, 9

=========================================================
-- To extract Expense reports for all Companies (Except France and Italy Operating Units) and Date Range
SELECT hou.NAME organization_name
,ven.vendor_name
,ven.vendor_type_lookup_code vendor_type
,inv.doc_sequence_value voucher_number
,inv.gl_date gl_date
,inv.invoice_num invoice_number
,inv.invoice_currency_code currency
,inv.invoice_amount
,invd.amount distribution_amount
,invd.description distribution_description
,gcc.code_combination_id
,gcc.segment1 company
,gcc.segment2 department
,gcc.segment3 ACCOUNT
,gcc.segment5 product
,gcc.segment4 region
,gcc.segment6 future
,DECODE (aip.invoice_payment_type
,'PREPAY', inv2.invoice_num
,ac.check_number
) document_number
,invd.period_name
,jh.je_source
,jh.NAME journal_entry
,jl.description line_description
,jl.accounted_dr
,jl.accounted_cr
,gps.period_year gl_date_year
,gps.quarter_num gl_date_quarter
,TO_CHAR (inv.gl_date, 'MON') gl_date_month
,TO_CHAR (inv.gl_date, 'DD') gl_date_day

FROM ap_invoices_all inv
,ap_invoice_distributions_all invd
,hr_organization_units hou
,po_vendors ven
,gl_code_combinations gcc
,ap_invoices_all inv2
,ap_invoice_payments_all aip
,ap_checks_all ac
,ap_accounting_events_all ae
,ap_ae_headers_all aeh
,ap_ae_lines_all ael
,gl_je_lines jl
,gl_je_headers jh
,hr_operating_units ou
,gl_period_statuses gps

WHERE inv.invoice_id = invd.invoice_id
AND hou.organization_id = invd.org_id
AND ven.vendor_id = inv.vendor_id
--AND inv.invoice_num in ( '226685','227482','227650')
AND inv.invoice_type_lookup_code = 'EXPENSE REPORT'
AND gcc.code_combination_id = invd.dist_code_combination_id
AND gcc.segment1 = '37'
AND inv.gl_date BETWEEN '01-APR-2009' AND '31-MAR-2010'
AND inv.invoice_id = aip.invoice_id(+)
AND aip.other_invoice_id = inv2.invoice_id(+)
AND ac.check_id(+) = aip.check_id
AND ae.accounting_event_id = invd.accounting_event_id
AND aeh.accounting_event_id = ae.accounting_event_id
AND ael.ae_header_id = aeh.ae_header_id
AND ael.source_id = invd.invoice_distribution_id
AND jl.gl_sl_link_id = ael.gl_sl_link_id
AND jh.je_header_id = jl.je_header_id
AND ou.organization_id = hou.organization_id
AND (inv.gl_date BETWEEN gps.start_date AND gps.end_date)
AND gps.set_of_books_id = ou.set_of_books_id
AND gps.application_id = 200

UNION ALL

SELECT hou.NAME organization_name
,ven.vendor_name
,ven.vendor_type_lookup_code vendor_type
,inv.doc_sequence_value voucher_number
,inv.gl_date gl_date
,inv.invoice_num invoice_number
,inv.invoice_currency_code currency
,inv.invoice_amount
,NULL distribution_amount
,NULL distribution_description
,gcc.code_combination_id
,gcc.segment1 company
,gcc.segment2 department
,gcc.segment3 ACCOUNT
,gcc.segment5 product
,gcc.segment4 region
,gcc.segment6 future
,DECODE (aip.invoice_payment_type
,'PREPAY', inv2.invoice_num
,ac.check_number
) document_number
,gps.period_name period_name
,jh.je_source
,jh.NAME journal_entry
,jl.description line_description
,jl.accounted_dr
,jl.accounted_cr
,gps.period_year gl_date_year
,gps.quarter_num gl_date_quarter
,TO_CHAR (inv.gl_date, 'MON') gl_date_month
,TO_CHAR (inv.gl_date, 'DD') gl_date_day

FROM ap_invoices_all inv
,hr_organization_units hou
,po_vendors ven
,gl_code_combinations gcc
,ap_invoices_all inv2
,ap_invoice_payments_all aip
,ap_checks_all ac
,ap_ae_lines_all ael
,gl_je_lines jl
,gl_je_headers jh
,hr_operating_units ou
,gl_period_statuses gps

WHERE 1 = 1
AND hou.organization_id = inv.org_id
AND ven.vendor_id = inv.vendor_id
--AND inv.invoice_num in ( '226685','227482','227650')
AND inv.invoice_type_lookup_code = 'EXPENSE REPORT'
AND gcc.code_combination_id = ael.code_combination_id
AND gcc.segment1 = '37'
AND inv.gl_date BETWEEN '01-APR-2009' AND '31-MAR-2010'
AND inv.invoice_id = aip.invoice_id(+)
AND aip.other_invoice_id = inv2.invoice_id(+)
AND ac.check_id(+) = aip.check_id
AND ael.source_table = 'AP_INVOICES'
AND ael.source_id = inv.invoice_id
AND jl.gl_sl_link_id = ael.gl_sl_link_id
AND jh.je_header_id = jl.je_header_id
AND ou.organization_id = hou.organization_id
AND (inv.gl_date BETWEEN gps.start_date AND gps.end_date)
AND gps.set_of_books_id = ou.set_of_books_id
AND gps.application_id = 200
ORDER BY 5, 6, 9