SELECT *
FROM apps.RA_CUSTOMER_TRX_ALL
WHERE attribute11 = '9999'
AND TRUNC (CREATION_DATE) BETWEEN TO_DATE ('22-DEC-2022',
'DD-MON-YYYY')
AND TO_DATE ('22-DEC-2022')
ORDER BY CREATION_DATE DESC;
SELECT DISTINCT
gl.name LEDGER,
-- wn.item_key,
gjb.NAME batch,
gjh.JE_SOURCE,
gjb.RUNNING_TOTAL_DR TOTAL_DR,
gjb.RUNNING_TOTAL_CR TOTAL_CR,
gjb.RUNNING_TOTAL_ACCOUNTED_DR ACCOUNTED_DR,
gjb.RUNNING_TOTAL_ACCOUNTED_CR ACCOUNTED_CR,
default_period_name period,
wn.FROM_USER,
wn.recipient_role approver,
DECODE (gjb.approval_status_code,
'A', 'Approved',
'I', 'In Process',
'J', 'Rejected',
'R', 'Required',
'V', 'Validation Failed',
'Z', 'N/A') status,
wn.begin_date approval_start_date,
wn.end_date approval_end_date,
wn.due_date approval_due_date
FROM wf_notifications wn,
wf_items wi,
gl_je_batches gjb,
gl_je_headers gjh,
gl_ledgers gl
WHERE 1 = 1
AND gjb.JE_BATCH_ID = gjh.JE_BATCH_ID
AND gjh.ledger_id = gl.ledger_id
AND wn.CONTEXT LIKE
'GLBATCH%'
|| (SELECT item_key
FROM wf_items a
WHERE a.user_key = wi.user_key
AND a.item_type = 'GLBATCH'
AND ROWNUM = 1)
|| '%'
AND wi.item_type = wn.MESSAGE_TYPE
AND wi.user_key = gjb.NAME
-- AND gjb.name LIKE 'BatchName%';
AND default_period_name = 'MAR-21'
ORDER BY 1,
3,
8,
9,
10,
11;
SELECT DECODE (FA.APPLICATION_SHORT_NAME, 'SQLGL', 'GL') APPLICATION_NAME,
AGL.LEDGER_ID,
AGL.NAME,
--AGL.CURRENCY_CODE,
-- AGL.ATTRIBUTE1 "LEDGER_TYPE",
GPS.SHOW_STATUS,
GPS.PERIOD_YEAR,
GPS.PERIOD_NAME,
GPS.LAST_UPDATE_DATE,
fu.USER_NAME,
DECODE (gps.closing_status,
'O', 'Open',
'C', 'Closed',
'F', 'Future',
'N', 'Never') CLOSING_STAUTS
FROM APPS.GL_PERIOD_STATUSES_V GPS,
APPS.GL_LEDGERS AGL,
FND_APPLICATION FA,
FND_USER FU
WHERE 1 = 1
AND GPS.LEDGER_ID = AGL.LEDGER_ID
AND GPS.APPLICATION_ID = FA.APPLICATION_ID
AND FU.USER_ID = GPS.LAST_UPDATED_BY
AND FA.APPLICATION_SHORT_NAME = 'SQLGL'
AND GPS.PERIOD_YEAR = '2021'
-- AND AGL.LEDGER_ID IN ('2021', '2042')
AND agl.name NOT IN ('ALL LEDGERS',
'INTERNATIONAL',
'WESCO LEDGERS',
'BRUCKNER GROUP',
'TVC REGION')
ORDER BY 2, 3, 8 DESC;
SELECT
gl.name "LEDGER_NAME",
b.name "JOURNAL_BATCH_NAME",
h.name "JOURNAL_NAME",
s.user_je_source_name "JOURNAL_SOURCE",
c.user_je_category_name "JOURNAL_CATEGORY",
h.period_name,
h.currency_code,
l.je_line_num "JOURNAL_LINE_NUM",
gcc.concatenated_segments "ACCOUNT_CODE_COMBINATION",
gcc.segment1 "LEGAL_ENTITY",
gcc.segment2 "BRANCH",
gcc.segment3 "ACCOUNT",
gcc.segment4 "INTERCOMPANY",
gcc.segment5 "FUTURE1",
gcc.segment6 "FUTURE2",
gcc.segment7 "FUTURE3",
l.entered_dr,
l.entered_cr,
l.accounted_dr,
l.accounted_cr,
SUM(nvl(entered_dr, 0) - nvl(entered_cr, 0)) net_entered,
SUM(nvl(accounted_dr, 0) - nvl(accounted_cr, 0)) net_accounted
FROM
apps.gl_je_headers h,
apps.gl_je_lines l,
apps.gl_je_categories c,
apps.gl_je_sources s,
apps.gl_code_combinations_kfv gcc,
apps.gl_je_batches b,
apps.gl_ledgers gl
WHERE
l.je_header_id = h.je_header_id
AND b.je_batch_id = h.je_batch_id
AND h.je_category = c.je_category_name
AND h.je_source = s.je_source_name
AND gcc.code_combination_id = l.code_combination_id
AND h.ledger_id = gl.ledger_id
AND h.ledger_id = l.ledger_id
AND h.period_name IN ('DEC-19')
---AND gl.name IN ('USD WESCO')
GROUP BY
gl.name,
h.name,
b.name,
h.period_name,
l.je_line_num,
gcc.concatenated_segments,
h.currency_code,
gcc.segment1,
gcc.segment2,
gcc.segment3,
gcc.segment4,
gcc.segment5,
gcc.segment6,
gcc.segment7,
c.user_je_category_name,
s.user_je_source_name,
l.entered_dr,
l.entered_cr,
l.accounted_dr,
l.accounted_cr
ORDER BY
gl.name,
b.name,
h.name,
l.je_line_num;
Lost Discount Report
SELECT i.vendor_id "VENDOR_ID",
v1.vendor_name VENDOR_NAME,
v2.vendor_site_code "VENDOR_SITE_CODE",
segment1 "VENDOR_NUM",
invoice_num "INVOICE_NUM",
invoice_date "INVOICE_DATE",
i.gl_date "INVOICE_GL_DATE",
DUE_DATE,
DISCOUNT_DATE,
c.CHECK_DATE,
i.terms_date,
invoice_amount "INVOICE_AMOUNT",
i.AMOUNT_PAID,
c.check_number,
-- i.AMOUNT_APPLICABLE_TO_DISCOUNT,
p.DISCOUNT_TAKEN,
p.DISCOUNT_LOST,
at.name " PAY_TERM_NAME",
apsa.DISCOUNT_AMOUNT_AVAILABLE,
apsa.DISCOUNT_AMOUNT_REMAINING,
v2.ALWAYS_TAKE_DISC_FLAG,
i.description "INVOICE_DESCRIPTION",
i.source,
i.org_id
FROM apps.po_vendors v1,
apps.po_vendor_sites_all v2,
apps.ap_invoices_all i,
apps.ap_invoice_payments_all p,
apps.ap_checks_all c,
apps.ap_payment_schedules_All apsa,
apps.ap_terms at
WHERE v1.vendor_id = i.vendor_id
AND apsa.invoice_id = i.invoice_id
AND v2.vendor_site_id = i.vendor_site_id
AND i.invoice_id = p.invoice_id
AND at.term_id = i.terms_id
AND i.payment_status_flag != 'N'
AND i.invoice_amount != 0
AND i.amount_paid != 0
AND P.DISCOUNT_LOST != '0'
AND c.check_id = p.check_id
AND (c.check_date BETWEEN TO_DATE ('01-JAN-2020', 'DD-MON-YYYY')
AND TO_DATE ('31-AUG-2020', 'DD-MON-YYYY'))
-- AND i.vendor_id = 2619
-- AND APSA.INVOICE_ID IN ( 71629083,
-- 71674452)
-- and i.org_id = 121
AND c.void_date IS NULL;
SELECT hp.party_name
, hp.party_number
, hca.account_number
, hca.cust_account_id
, hp.party_id
, hps.party_site_id
, hps.location_id
, hl.address1
, hl.address2
, hl.address3
, hl.city
, hl.state
, hl.country
, hl.postal_code
, hcsu.site_use_code
, hcsu.site_use_id
, hcsa.bill_to_flag
FROM hz_parties hp
, hz_party_sites hps
, hz_locations hl
, hz_cust_accounts_all hca
, hz_cust_acct_sites_all hcsa
, hz_cust_site_uses_all hcsu
WHERE hp.party_id = hps.party_id
AND hps.location_id = hl.location_id
AND hp.party_id = hca.party_id
AND hcsa.party_site_id = hps.party_site_id
AND hcsu.cust_acct_site_id = hcsa.cust_acct_site_id
AND hca.cust_account_id = hcsa.cust_account_id
AND hca.account_number = :customer_number
SELECT fcpl.user_concurrent_program_name
, fcp.concurrent_program_name
, par.column_seq_num
, par.end_user_column_name
, par.form_left_prompt prompt
, par.enabled_flag
, par.required_flag
, par.display_flag
, par.flex_value_set_id
, ffvs.flex_value_set_name
, flv.meaning default_type
, par.DEFAULT_VALUE
FROM fnd_concurrent_programs fcp
, fnd_concurrent_programs_tl fcpl
, fnd_descr_flex_col_usage_vl par
, fnd_flex_value_sets ffvs
, fnd_lookup_values flv
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
AND ffvs.flex_value_set_id = par.flex_value_set_id
AND flv.lookup_type(+) = 'FLEX_DEFAULT_TYPE'
AND flv.lookup_code(+) = par.default_type
AND flv.LANGUAGE(+) = USERENV ('LANG')
ORDER BY par.column_seq_num
//
SELECT FCR.REQUEST_ID "REQUEST ID" ,
FCF.USER_CONCURRENT_PROGRAM_NAME "REQUEST NAME",
FCR.REQUESTED_BY "SUBMITTED BY" ,
FU.USER_NAME "USER_NAME" ,
FCR.ARGUMENT_TEXT "PARAMETERS" ,
TO_CHAR(FCR.REQUESTED_START_DATE, 'DD-MON-YY HH24:MI') "SUBMITTED DATE"
FROM apps.FND_CONCURRENT_REQUESTS FCR,
apps.FND_CONC_REQUESTS_FORM_V FCF ,
apps.FND_USER FU
WHERE
---FCR.PHASE_CODE='P'
--AND FCR.STATUS_CODE ='Q'
FCR.REQUEST_ID =FCF.REQUEST_ID
AND FU.USER_ID =FCR.REQUESTED_BY
AND TRUNC(SYSDATE) BETWEEN START_DATE AND NVL(END_DATE,SYSDATE+1);
SELECT ooha.order_number
, oola.line_number so_line_number
, oola.ordered_item
, oola.ordered_quantity * oola.unit_selling_price so_extended_price
, rcta.trx_number invoice_number
, rcta.trx_date
, rctla.line_number inv_line_number
, rctla.unit_selling_price inv_unit_selling_price
FROM oe_order_headers_all ooha
, oe_order_lines_all oola
, ra_customer_trx_all rcta
, ra_customer_trx_lines_all rctla
WHERE ooha.header_id = oola.header_id
AND rcta.customer_trx_id = rctla.customer_trx_id
AND rctla.interface_line_attribute6 = TO_CHAR (oola.line_id)
AND rctla.interface_line_attribute1 = TO_CHAR (ooha.order_number)
AND order_number = :p_order_number
. Query to get Customer Related information for a Sales Order
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.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
5. Item Import (Item Conversion)
The Item import Interface(IOI) reads data from following tables for importing items and item details. The MTL_SYSTEMS_ITEM_INTERFACE table is used for new item numbers
and all item attributes. This is the main item interface table, and can be
the only table used to import items. MTL_ITEM_REVISIONS_INTERFACE is used if Item revisions history is also loaded with items. Item categories can be imported using MTL_ITEM_CATEGORIES_INTERFACE.
The import error can be tracked using MTL_INTERFACE_ERRORS table. The transaction_id and request_id populated by the import program can be used to link interface table and error table.
Required columns in MTL_SYSTEM_ITEMS_INTERFACE
PROCESS_FLAG = 1 (The column is used to identify status of record)
TRANSACTION_TYPE = 'CREATE' or 'UPDATE'
SET_PROCESS_ID = any numeric value (This is not a required column but for performance it is advised to use this column and then run import program for the value entered here)
ORGANIZATION_ID/ORGANIZATION_CODE = Master/Child Org.
DESCRIPTION = 'Description of the item'
ITEM_NUMBER and/or SEGMENT(n) = If using item_number then each segment value should be entered concatenated by segment seperator. If Item revisions history is also being loaded then Item_number should be populated.
LIST_PRICE_PER_UNIT = If material cost is to be populated for an item along with item import.
Required columns in MTL_ITEM_REVISIONS_INTERFACE table. The table is only used if Item revision is to be loaded in the same run with IOI. If this table is not used then items are created with the default revision setup for an organization.
PROCESS_FLAG = 1
TRANSACTION_TYPE = 'CREATE'
SET_PROCESS_ID = any numeric value (Should be same for the item in MTL_SYSTEM_ITEMS_INTERFACE table)
ORGANIZATION_ID/ORGANIZATION_CODE = Master/Child Org.
REVISION
EFFECTIVITY_DATE
IMPLEMENTATION_DATE
ITEM_NUMBER = Same as item_number in mtl_system_items_interface table.
Each row in the mtl_item_revisions_interface table must have the REVISION and
EFFECTIVITY_DATE in alphabetical (ASCII sort) and chronological order.
Required columns for MTL_ITEM_CATEGORIES_INTERFACE table.
TRANSACTION_TYPE = 'CREATE'
SET_PROCESS_ID = any numeric value (Should be same for the item in MTL_SYSTEM_ITEMS_INTERFACE table)
ORGANIZATION_ID/ORGANIZATION_CODE = Master/Child Org
ITEM_NUMBER/INVENTORY_ITEM_ID or both
CATEGORY_SET_NAME or CATEGORY_SET_NAME or both
CATEGORY_ID or CATEGORY_NAME or both
For performance purpose, it is advised to batch set of records using set_process_id column and then run import program for that set_process_id. The item import (IOI) program can be run in parallel if separate set_process_ids are passed while submitting. The IOI automatically separates Master records from Child, and
processes Master records first. However, as one IOI process is not aware of
other IOI processes running in parallel, do not split a given item's separate
Organization records into two different SET_PROCESS_IDs that are being run in
parallel.
Item import program can be run in 2 modes INSERT & UPDATE.
The method to update Item attribute columns to NULL is to use the following values:
· for Numeric fields: insert -999999
· for Character fields: insert '!'
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
7. Link Purchase Order and Requisition
SELECT prh.segment1 req_number
,prh.authorization_status
,prl.line_num req_line_num
,prl.item_description req_item_description
,prl.unit_price req_unit_price
,prl.quantity req_quantity
,pd.req_header_reference_num
,pd.req_line_reference_num
,pl.line_num
,pl.item_description
,pl.quantity
,pl.amount
,ph.segment1 po_number
,prd.distribution_id
,pd.req_distribution_id
FROM po_requisition_headers_all prh
,po_requisition_lines_all prl
,po_req_distributions_all prd
,po_distributions_all pd
,po_line_locations_all pll
,po_lines_all pl
,po_headers_all ph
WHERE prh.requisition_header_id = prl.requisition_header_id
and prh.org_id = prl.org_id
and prl.requisition_line_id = prd.requisition_line_id
and prl.org_id = prd.org_id
and prd.distribution_id = pd.req_distribution_id(+)
and prd.org_id = pd.org_id(+)
and pd.line_location_id = pll.line_location_id(+)
and pd.org_id = pll.org_id(+)
and pll.po_line_id = pl.po_line_id(+)
and pll.org_id = pl.org_id(+)
and pl.po_header_id = ph.po_header_id(+)
and pl.org_id = ph.org_id(+)
8. Query to find locked objects in Oracle
SELECT c.owner
, c.object_name
, c.object_type
, b.SID
, b.serial#
, b.status
, b.osuser
, b.machine
, b.program
, b.module
, b.action
FROM v$locked_object a
, v$session b
, dba_objects c
WHERE b.SID = a.session_id
AND a.object_id = c.object_id
ORDER BY module
9. want to forcefully kill any session
alter system kill session 'sid,serial#'
e.g.
altery system kill session '123,5325'
10. Find Appln Id, Appl Name, Table Name and Column Name
SELECT fa.application_id
, fa.application_short_name
, fat.application_name
, table_name
, column_name
, ft.description table_description
, fc.description column_description
FROM fnd_tables ft
, fnd_columns fc
, fnd_application_tl fat
, fnd_application fa
WHERE ft.table_id = fc.table_id
AND fc.column_name = :column_name
AND fat.application_id = ft.application_id
AND fat.LANGUAGE = USERENV ('LANG')
AND fa.application_id = fat.application_id
11.Query to List all the responsibilities attached to a User
select fu.user_name, fr.responsibility_name, furg.START_DATE, furg.END_DATE
from fnd_user_resp_groups_direct furg, fnd_user fu, fnd_responsibility_tl fr
where fu.user_name = :user_name
and furg.user_id = fu.user_id
and furg.responsibility_id = fr.responsibility_id
and fr.language = userenv('LANG')
Below is an example of how to display dates into week Range in Oracle
For E.g. 22-Nov-08 is in the date range 17-Nov-08 to 23-Nov-08
Firstly lets see how to get week of the year. Following query can be used to get this
SELECT to_char(sysdate,'WW') FROM Dual;
Now lets get the week range
SELECT TO_CHAR (TRUNC (SYSDATE, 'IYYY') + ((TO_CHAR (SYSDATE, 'WW') - 1) * 7), 'DD-MON-RR')
|| ' to '
|| TO_CHAR (TRUNC (SYSDATE, 'IYYY') + ((TO_CHAR (SYSDATE, 'WW')) * 7)-1, 'DD-MON-RR')
FROM Dual;
The output of above query for date 29-Nov-2008 is 24-NOV-08 to 30-NOV-08
13. Sales Order Line Status Flow and Meaning
Below are some of the different statuses of Sales Order Line with brief explanation
OM = Order Management Sales order form
SE = Shipping Transactions or execution form
1) Entered (OM): Order is saved but not booked
2) Booked (OM): Order is Booked.
3) Awaiting Shipping (OM): Order is booked but lines are not yet picked.
Navigating to Shipping Execution, the delivery line status flow is:
4) Not Ready to Release (SE): A delivery line may be in this status when it is interfaced manually into Shipping, is not scheduled and has no reservations. When lines are imported automatically from Order Management this status is not used
5) Released to Warehouse (SE): Pick Release has started but not yet completed. One of the reason could be allocation have not been pick confirmed. The Pick Release process creates a Move Order Header & Mover Order Line in Inventory. This is a common status for users that perform a two-step pick release process. This status indicates that inventory allocation has occurred however pick conformation has not yet taken place.
6) Ready to Release (SE): Order Line is booked and passed to shipping execution. The line is now eligible to pick Release.
7) Backordered(SE): The status of Backorderd is assigned to a line under the following circumstances.
· The Pick Release process attempted to allocate inventory to the line and all or a partial quantity of the item was not available. In this case the system automatically backorders the discrepant quantity.
· At Ship confirm the user enters a shipped quantity for an item that is less than the original requested quantity.
· The user manually Backorders the entire delivery.
8) Shipped (SE): The delivery line is shipped confirmed.
9) Confirmed (SE): The delivery line is shipped or backordered and the trip stops are open.
10) Picked (OM): Pick release is complete, both allocations and pick confirm
11) Picked Partial (OM): This status occurs when a delivery line is not allocated the full quantity during Pick Release and Ship Confirm has not occurred
12) Interfaced (SE): The delivery line is shipped and Inventory interface concurrent process is complete.
13) Awaiting Fulfillment (OM): When fulfillment set is used, Not all shippable lines in a fulfillment set or a
configuration are fulfilled
14) Fulfilled (OM): All lines in a fulfillment set are fulfilled.
15) Interfaced to Receivables (OM): The order is linked with Receivables and the invoice is created.
16) Partially Interfaced to Receivables (OM): This status is used in a PTO flow and indicates that the particular PTO item is required for revenue.
17) Closed (OM): Closed indicates that the line is closed.
18) Canceled (OM): Indicates that the line has been completely canceled. No further processing will occur for this line.
14. Concurrent Program Name with Parameter, Value set
SELECT fcpl.user_concurrent_program_name
, fcp.concurrent_program_name
, par.column_seq_num
, par.end_user_column_name
, par.form_left_prompt prompt
, par.enabled_flag
, par.required_flag
, par.display_flag
, par.flex_value_set_id
, ffvs.flex_value_set_name
, flv.meaning default_type
, par.DEFAULT_VALUE
FROM fnd_concurrent_programs fcp
, fnd_concurrent_programs_tl fcpl
, fnd_descr_flex_col_usage_vl par
, fnd_flex_value_sets ffvs
, fnd_lookup_values flv
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
AND ffvs.flex_value_set_id = par.flex_value_set_id
AND flv.lookup_type(+) = 'FLEX_DEFAULT_TYPE'
AND flv.lookup_code(+) = par.default_type
AND flv.LANGUAGE(+) = USERENV ('LANG')
ORDER BY par.column_seq_num
15. Query to find out the shipper info
select
wnd.delivery_id delivery_id,
substrb(party.party_name,1,50) customer,
wpb.name batch_name,
wsh_util_core.get_location_description(
wnd.INITIAL_PICKUP_LOCATION_ID,
'NEW UI CODE') ship_from,
wsh_util_core.get_location_description(
wnd.ULTIMATE_DROPOFF_LOCATION_ID,
'NEW UI CODE') ship_to,
wnd.INITIAL_PICKUP_DATE pickup_date,
wnd.ULTIMATE_DROPOFF_DATE dropoff_date,
lv.meaning ship_method,
wnd.WAYBILL waybill,
wnd.GROSS_WEIGHT gross_weight,
wnd.WEIGHT_UOM_CODE uom,
wnd.status_code,
we.message
from wsh_new_deliveries wnd,
wsh_picking_batches wpb,
wsh_exceptions we,
fnd_lookup_values_vl lv,
hz_cust_accounts cust_acct,
hz_parties party
where wnd.delivery_id = 12814
and wpb.batch_id = wnd.batch_id
and we.delivery_id(+) = wnd.delivery_id
and we.exception_name(+) = 'WSH_BATCH_MESSAGE'
and lv.lookup_code(+) = wpb.ship_method_code
and lv.lookup_type(+) = 'SHIP_METHOD'
and lv.view_application_id(+) = 3
and cust_acct.cust_account_id (+)=wnd.customer_id
and party.party_id(+) = cust_acct.party_id
SELECT
wnd.delivery_id,
wnd.name delivery_name,
wdd.source_header_number
so_order_number,
oola.line_number so_line_number,
wdd.source_header_id so_header_id,
wdd.source_line_id so_line_id,
wdd.shipping_instructions,
wdd.inventory_item_id,
wdd.requested_quantity_uom,
msi.description item_description,
msi.revision_qty_control_code ,
wdd.ship_method_code carrier,
wdd.shipment_priority_code priority,
wdd.organization_id,
wnd.initial_pickup_location_id,
wdd.released_status,
wdd.source_code
FROM mtl_system_items_vl msi,
oe_order_lines_all oola,
wsh_delivery_details wdd,
wsh_delivery_assignments wda,
wsh_new_deliveries wnd
WHERE wnd.delivery_id =18910
AND wda.delivery_id = wnd.delivery_id(+)
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND wdd.inventory_item_id = msi.inventory_item_id(+)
AND wdd.organization_id = msi.organization_id(+)
AND wdd.source_line_id = oola.line_id
AND wdd.source_header_id = oola.header_id
17. Query to find out order and line hold information.
select distinct ohd.name
,ooh.hold_until_date
,ooh.hold_comment
,h.order_number
,l.item_identifier_type
,l.inventory_item_id
,l.ordered_item
,oh.header_id
,oh.line_id
,oh.order_hold_id
from oe_hold_definitions ohd,
oe_hold_sources_all ooh,
oe_order_headers_all h,
oe_order_lines_all l,
oe_order_holds_all oh
where ohd.hold_id = ooh.hold_id
and oh.hold_source_id = ooh.hold_source_id
and oh.header_id = h.header_id
and h.header_id=l.header_id
and l.open_flag='Y'
and h.open_flag='Y'
order by ohd.name,h.order_number;
18. Sample Code for oe_order_pub.Process_Order.
Got sample code to create Sales Order in Oracle Order Management .Please note that this is not my code , one of my friend has forwaded this .But I have tested it throughly and succesfully created Sales Orders.
create or replace procedure createsalesorder
(p_org_id NUMBER,
p_user_id NUMBER,
p_resp_id NUMBER,
p_appl_id NUMBER,
p_order_type_id NUMBER,
p_sold_to_org_id NUMBER,
p_ship_to_org_id NUMBER,
p_price_list_id NUMBER,
p_curr_code VARCHAR2,
p_flow_status_code VARCHAR2,
p_po_num VARCHAR2,
p_order_source_id NUMBER,
p_inventory_item_id NUMBER,
p_ordered_quantity NUMBER,
p_tax_code VARCHAR2)
IS
l_api_version_number NUMBER := 1;
l_return_status VARCHAR2(2000);
l_msg_count NUMBER;
l_msg_data VARCHAR2(2000);
/*****************PARAMETERS****************************************************/
l_debug_level number := 1; -- OM DEBUG LEVEL (MAX 5)
l_org number := p_org_id;--204; -- OPERATING UNIT
l_user number := p_user_id;--1318; -- USER
l_resp number := p_resp_id;--21623; -- RESPONSIBLILTY
l_appl number := p_appl_id;--660; -- ORDER MANAGEMENT
/***INPUT VARIABLES FOR PROCESS_ORDER API*************************/
l_header_rec oe_order_pub.header_rec_type;
l_line_tbl oe_order_pub.line_tbl_type;
l_action_request_tbl oe_order_pub.Request_Tbl_Type;
/***OUT VARIABLES FOR PROCESS_ORDER API***************************/
l_header_rec_out oe_order_pub.header_rec_type;
l_header_val_rec_out oe_order_pub.header_val_rec_type;
l_header_adj_tbl_out oe_order_pub.header_adj_tbl_type;
l_header_adj_val_tbl_out oe_order_pub.header_adj_val_tbl_type;
l_header_price_att_tbl_out oe_order_pub.header_price_att_tbl_type;
l_header_adj_att_tbl_out oe_order_pub.header_adj_att_tbl_type;
l_header_adj_assoc_tbl_out oe_order_pub.header_adj_assoc_tbl_type;
l_header_scredit_tbl_out oe_order_pub.header_scredit_tbl_type;
l_header_scredit_val_tbl_out oe_order_pub.header_scredit_val_tbl_type;
l_line_tbl_out oe_order_pub.line_tbl_type;
l_line_val_tbl_out oe_order_pub.line_val_tbl_type;
l_line_adj_tbl_out oe_order_pub.line_adj_tbl_type;
l_line_adj_val_tbl_out oe_order_pub.line_adj_val_tbl_type;
l_line_price_att_tbl_out oe_order_pub.line_price_att_tbl_type;
l_line_adj_att_tbl_out oe_order_pub.line_adj_att_tbl_type;
l_line_adj_assoc_tbl_out oe_order_pub.line_adj_assoc_tbl_type;
l_line_scredit_tbl_out oe_order_pub.line_scredit_tbl_type;
l_line_scredit_val_tbl_out oe_order_pub.line_scredit_val_tbl_type;
l_lot_serial_tbl_out oe_order_pub.lot_serial_tbl_type;
l_lot_serial_val_tbl_out oe_order_pub.lot_serial_val_tbl_type;
l_action_request_tbl_out oe_order_pub.request_tbl_type;
l_msg_index NUMBER;
l_data VARCHAR2(2000);
l_loop_count NUMBER;
l_debug_file VARCHAR2(200);
-- book API vars
b_return_status VARCHAR2(200);
b_msg_count NUMBER;
b_msg_data VARCHAR2(2000);
BEGIN
dbms_application_info.set_client_info(l_org);
/*****************INITIALIZE DEBUG INFO*************************************/
if (l_debug_level > 0) then
l_debug_file := OE_DEBUG_PUB.Set_Debug_Mode('FILE');
oe_debug_pub.initialize;
oe_debug_pub.setdebuglevel(l_debug_level);
Oe_Msg_Pub.initialize;
end if;
/*****************INITIALIZE ENVIRONMENT*************************************/
fnd_global.apps_initialize(l_user, l_resp, l_appl); -- pass in user_id, responsibility_id, and application_id
/*****************INITIALIZE HEADER RECORD******************************/
l_header_rec := oe_order_pub.G_MISS_HEADER_REC;
/***********POPULATE REQUIRED ATTRIBUTES **********************************/
l_header_rec.operation := OE_GLOBALS.G_OPR_CREATE;
l_header_rec.order_type_id := p_order_type_id;--1430;
l_header_rec.sold_to_org_id := p_sold_to_org_id;--1006;
l_header_rec.ship_to_org_id := p_ship_to_org_id;--1026;
l_header_rec.price_list_id := p_price_list_id;--1000;
l_header_rec.pricing_date := SYSDATE;
l_header_rec.transactional_curr_code := p_curr_code;--'USD';
l_header_rec.flow_status_code := p_flow_status_code;--'ENTERED';
l_header_rec.cust_po_number := p_po_num;--'06112009-08';
l_header_rec.order_source_id := p_order_source_id;--0 ;
--l_header_rec.attribute1 := 'ABC';
/*******INITIALIZE ACTION REQUEST RECORD*************************************/
l_action_request_tbl(1) := oe_order_pub.G_MISS_REQUEST_REC;
l_action_request_tbl(1).request_type := oe_globals.g_book_order;
l_action_request_tbl(1).entity_code := oe_globals.g_entity_header;
/*****************INITIALIZE LINE RECORD********************************/
l_line_tbl(1) := oe_order_pub.G_MISS_LINE_REC;
l_line_tbl(1).operation := OE_GLOBALS.G_OPR_CREATE;
l_line_tbl(1).inventory_item_id := p_inventory_item_id;--149 ;
l_line_tbl(1).ordered_quantity := p_ordered_quantity;--1;
l_line_tbl(1).ship_to_org_id := p_ship_to_org_id;--1026 ;
l_line_tbl(1).tax_code := p_tax_code;--'Location' ;
/*****************CALLTO PROCESS ORDER API*********************************/
dbms_output.put_line('Calling API');
oe_order_pub.Process_Order( p_api_version_number => l_api_version_number,
p_header_rec => l_header_rec,
p_line_tbl => l_line_tbl,
p_action_request_tbl => l_action_request_tbl,
--OUT variables
x_header_rec => l_header_rec_out,
x_header_val_rec => l_header_val_rec_out,
x_header_adj_tbl => l_header_adj_tbl_out,
x_header_adj_val_tbl => l_header_adj_val_tbl_out,
x_header_price_att_tbl => l_header_price_att_tbl_out,
x_header_adj_att_tbl => l_header_adj_att_tbl_out,
x_header_adj_assoc_tbl => l_header_adj_assoc_tbl_out,
x_header_scredit_tbl => l_header_scredit_tbl_out,
x_header_scredit_val_tbl => l_header_scredit_val_tbl_out,
x_line_tbl => l_line_tbl_out,
x_line_val_tbl => l_line_val_tbl_out,
x_line_adj_tbl => l_line_adj_tbl_out,
x_line_adj_val_tbl => l_line_adj_val_tbl_out,
x_line_price_att_tbl => l_line_price_att_tbl_out,
x_line_adj_att_tbl => l_line_adj_att_tbl_out,
x_line_adj_assoc_tbl => l_line_adj_assoc_tbl_out,
x_line_scredit_tbl => l_line_scredit_tbl_out,
x_line_scredit_val_tbl => l_line_scredit_val_tbl_out,
x_lot_serial_tbl => l_lot_serial_tbl_out,
x_lot_serial_val_tbl => l_lot_serial_val_tbl_out,
x_action_request_tbl => l_action_request_tbl_out,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
/*****************CHECK RETURN STATUS***********************************/
if l_return_status = FND_API.G_RET_STS_SUCCESS then
dbms_output.put_line('Return status is success ');
dbms_output.put_line('debug level '
l_debug_level);
if (l_debug_level > 0) then
dbms_output.put_line('success');
end if;
commit;
else
dbms_output.put_line('Return status failure ');
if (l_debug_level > 0) then
dbms_output.put_line('failure');
end if;
rollback;
end if;
/*****************DISPLAY RETURN STATUS FLAGS******************************/
if (l_debug_level > 0) then
DBMS_OUTPUT.PUT_LINE('process ORDER ret status IS: '
l_return_status);
DBMS_OUTPUT.PUT_LINE('process ORDER msg data IS: '
l_msg_data);
DBMS_OUTPUT.PUT_LINE('process ORDER msg COUNT IS: '
l_msg_count);
DBMS_OUTPUT.PUT_LINE('header.order_number IS: ' to_char(l_header_rec_out.order_number));
DBMS_OUTPUT.PUT_LINE('header.return_status IS: ' l_header_rec_out.return_status);
DBMS_OUTPUT.PUT_LINE('header.booked_flag IS: ' l_header_rec_out.booked_flag);
DBMS_OUTPUT.PUT_LINE('header.header_id IS: ' l_header_rec_out.header_id);
DBMS_OUTPUT.PUT_LINE('header.order_source_id IS: ' l_header_rec_out.order_source_id);
DBMS_OUTPUT.PUT_LINE('header.flow_status_code IS: ' l_header_rec_out.flow_status_code);
end if;
/*****************DISPLAY ERROR MSGS*************************************/
if (l_debug_level > 0) then
FOR i IN 1 .. l_msg_count LOOP
Oe_Msg_Pub.get(
p_msg_index => i
,p_encoded => Fnd_Api.G_FALSE
,p_data => l_data
,p_msg_index_out => l_msg_index);
DBMS_OUTPUT.PUT_LINE('message is: ' l_data);
DBMS_OUTPUT.PUT_LINE('message index is: ' l_msg_index);
END LOOP;
end if;
if (l_debug_level > 0) then
DBMS_OUTPUT.PUT_LINE('Debug = ' OE_DEBUG_PUB.G_DEBUG);
DBMS_OUTPUT.PUT_LINE('Debug Level = ' to_char(OE_DEBUG_PUB.G_DEBUG_LEVEL));
DBMS_OUTPUT.PUT_LINE('Debug File = ' OE_DEBUG_PUB.G_DIR'/'OE_DEBUG_PUB.G_FILE);
DBMS_OUTPUT.PUT_LINE('****************************************************');
OE_DEBUG_PUB.DEBUG_OFF;
end if;
--END;
end createsalesorder;
19. Query to find price discounts and surcharges on order lines
col list_line_type_code form a12
col arithmetic_operator form a12
Select h.order_number,
l.line_number,
pa.list_line_type_code,
pa.arithmetic_operator,
pa.operand,
DECODE(PA.MODIFIER_LEVEL_CODE,'ORDER',
L.UNIT_LIST_PRICE*L.ORDERED_QUANTITY *PA.OPERAND * SIGN(PA.ADJUSTED_AMOUNT)/100,
(PA.ADJUSTED_AMOUNT* NVL(L.ORDERED_QUANTITY,0) )) DISCOUNT_AMT
From
qp_list_headers_vl lh,
oe_price_adjustments pa,
oe_order_lines_all l,
oe_order_headers_all h
Where h.order_number = 14463
and h.header_id = l.header_id
and h.org_id = l.org_id
and h.header_id = pa.header_id
and l.line_id = pa.line_id(+)
and pa.list_header_id = lh.list_header_id
AND ( PA.LIST_LINE_TYPE_CODE = 'DIS'
OR PA.LIST_LINE_TYPE_CODE = 'SUR'
OR PA.LIST_LINE_TYPE_CODE = 'PBH' )
AND PA.APPLIED_FLAG='Y'
AND NOT EXISTS
(SELECT 'X'
FROM OE_PRICE_ADJ_ASSOCS PAS,
OE_PRICE_ADJUSTMENTS PA1
WHERE PAS.RLTD_PRICE_ADJ_ID =
PA.PRICE_ADJUSTMENT_ID
AND PA1.PRICE_ADJUSTMENT_ID=
PAS.PRICE_ADJUSTMENT_ID
AND PA1.LIST_LINE_TYPE_CODE ='PBH')
Order by l.line_id
/
a: Qp_list_headers_vl is view based on qp_list_headers_b and qp_list_headers_tl tables.
----------------------------------------------------------------------
20. Query to find freight charges on order lines
col charge_name form a24
col source_system_code form a12
select
HEADER_ID ,
LINE_ID ,
CHARGE_ID ,
CHARGE_NAME ,
CHARGE_AMOUNT ,
CURRENCY_CODE ,
INVOICED_FLAG ,
INTERCO_INVOICED_FLAG ,
ORG_ID ,
SOURCE_SYSTEM_CODE ,
ESTIMATED_FLAG ,
INVOICED_AMOUNT
from OE_CHARGE_LINES_V
where header_id=
(select header_id
from oe_order_headers_all
where order_number=14463)
order by line_id
/
a: The OE_CHARGE_LINES_V view is based on oe_price_adjustments, oe_order_headers_all and oe_order_lines_all for FREIGHT CHARGES.
21. Query to find out order and line hold information
col ordered_item form a32
col hold_name form a24
col hold_comment form a32
select ho.name hold_name,
hs.hold_until_date,
hs.hold_comment,
h.order_number,
oh.header_id,
oh.line_id,
oh.order_hold_id,
l.item_identifier_type,
l.inventory_item_id,
l.ordered_item
from oe_order_holds_all oh,
oe_order_lines_all l,
oe_order_headers_all h,
oe_hold_definitions ho,
oe_hold_sources_all hs
where h.order_number= 14463
and oh.header_id = h.header_id
and (h.cancelled_flag is null or h.cancelled_flag = 'N')
and h.open_flag='Y'
and oh.hold_source_id = hs.hold_source_id
and hs.hold_id = ho.hold_id
and h.header_id = l.header_id(+)
and l.open_flag = 'Y'
and l.line_id = nvl(oh.line_id,l.line_id)
and l.service_reference_line_id is null
and oh.hold_release_id is null
and nvl(h.org_id,0) = 204
and nvl(l.org_id,0) = nvl(h.org_id,0)
order by ho.name,h.order_number
22. Query to find freight related info of order viz: freight carrier, ship method and service level
col shipping_method_code form a32
col carrier_name form a24
select
h.order_number,
h.shipping_method_code,
wc.carrier_name,
wcsm.SERVICE_LEVEL ,
wcsm.freight_code
from
wsh_carrier_ship_methods_v wcsm,
wsh_carriers_v wc,
oe_order_headers_all h
where h.order_number= 14463
and h.org_id = 204
and h.shipping_method_code = wcsm.ship_method_code(+)
and nvl(wcsm.organization_id(+),0) = 204 --Master Organization
and wcsm.freight_code = wc.freight_code(+)
order by h.order_number
/
23. Query to find out the shipper info
select
wnd.delivery_id delivery_id,
substrb(party.party_name,1,50) customer,
wpb.name batch_name,
wsh_util_core.get_location_description(
wnd.INITIAL_PICKUP_LOCATION_ID,
'NEW UI CODE') ship_from,
wsh_util_core.get_location_description(
wnd.ULTIMATE_DROPOFF_LOCATION_ID,
'NEW UI CODE') ship_to,
wnd.INITIAL_PICKUP_DATE pickup_date,
wnd.ULTIMATE_DROPOFF_DATE dropoff_date,
lv.meaning ship_method,
wnd.WAYBILL waybill,
wnd.GROSS_WEIGHT gross_weight,
wnd.WEIGHT_UOM_CODE uom,
wnd.status_code,
we.message
from wsh_new_deliveries wnd,
wsh_picking_batches wpb,
wsh_exceptions we,
fnd_lookup_values_vl lv,
hz_cust_accounts cust_acct,
hz_parties party
where wnd.delivery_id = 12814
and wpb.batch_id = wnd.batch_id
and we.delivery_id(+) = wnd.delivery_id
and we.exception_name(+) = 'WSH_BATCH_MESSAGE'
and lv.lookup_code(+) = wpb.ship_method_code
and lv.lookup_type(+) = 'SHIP_METHOD'
and lv.view_application_id(+) = 3
and cust_acct.cust_account_id (+)=wnd.customer_id
and party.party_id(+) = cust_acct.party_id
24. Query to find out shipper detail info
SELECT
wnd.delivery_id,
wnd.name delivery_name,
wdd.source_header_number
so_order_number,
oola.line_number so_line_number,
wdd.source_header_id so_header_id,
wdd.source_line_id so_line_id,
wdd.shipping_instructions,
wdd.inventory_item_id,
wdd.requested_quantity_uom,
msi.description item_description,
msi.revision_qty_control_code ,
wdd.ship_method_code carrier,
wdd.shipment_priority_code priority,
wdd.organization_id,
wnd.initial_pickup_location_id,
wdd.released_status,
wdd.source_code
FROM mtl_system_items_vl msi,
oe_order_lines_all oola,
wsh_delivery_details wdd,
wsh_delivery_assignments wda,
wsh_new_deliveries wnd
WHERE wnd.delivery_id =18910
AND wda.delivery_id = wnd.delivery_id(+)
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND wdd.inventory_item_id = msi.inventory_item_id(+)
AND wdd.organization_id = msi.organization_id(+)
AND wdd.source_line_id = oola.line_id
AND wdd.source_header_id = oola.header_id
/
25. Query to find out Move order line details
SELECT
wnd.delivery_id,
wnd.name delivery_name,
wnd.initial_pickup_location_id,
mtrh.request_number mo_number,
mtrl.line_number mo_line_number,
mtrl.line_id mo_line_id,
mtrl.from_subinventory_code,
mtrl.to_subinventory_code,
mtrl.lot_number,
mtrl.serial_number_start,
mtrl.serial_number_end,
mtrl.uom_code,
mtrl.quantity,
mtrl.quantity_delivered,
mtrl.quantity_detailed,
wdd.source_header_number so_order_number,
oola.line_number so_line_number,
wdd.source_header_id so_header_id,
wdd.source_line_id so_line_id,
wdd.shipping_instructions,
wdd.inventory_item_id,
wdd.requested_quantity_uom,
msi.description item_description,
msi.revision_qty_control_code ,
wdd.ship_method_code carrier,
wdd.shipment_priority_code priority,
wdd.organization_id,
wdd.released_status,
wdd.source_code
FROM mtl_system_items_vl msi,
oe_order_lines_all oola,
mtl_txn_request_lines mtrl,
mtl_txn_request_headers mtrh,
wsh_delivery_details wdd,
wsh_delivery_assignments wda,
wsh_new_deliveries wnd
WHERE wnd.delivery_id =18910
AND wda.delivery_id = wnd.delivery_id(+)
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND wdd.move_order_line_id = mtrl.line_id
AND mtrl.header_id = mtrh.header_id
AND wdd.inventory_item_id = msi.inventory_item_id(+)
AND wdd.organization_id = msi.organization_id(+)
AND wdd.source_line_id = oola.line_id
AND wdd.source_header_id = oola.header_id
/
26. Query to find Bill of Lading info of the Delivery
select
wnd.delivery_id delivery_id,
wdi.sequence_number bol_number,
wdi.bol_notify_party,
wdi.port_of_loading,
wdi.port_of_discharge,
wnd.WAYBILL waybill,
wnd.GROSS_WEIGHT gross_weight,
wnd.WEIGHT_UOM_CODE uom,
wnd.status_code
from wsh_new_deliveries wnd,
wsh_delivery_legs wdl,
wsh_document_instances wdi
where wnd.delivery_id =12784
and wnd.delivery_id = wdl.delivery_id (+)
and wdi.entity_id (+) = wdl.delivery_leg_id
AND wdi.entity_name (+) = 'WSH_DELIVERY_LEGS'
AND wdi.document_type (+) = 'BOL'
AND wdi.status (+) <> 'CANCELLED'
/
27. Query to find delivery leg and pick up stop info
SELECT wt.trip_id,
wt.name,
wt.STATUS_CODE,
wt.VEHICLE_ITEM_ID,
wt.VEHICLE_NUMBER,
wt.CARRIER_ID,
wt.SHIP_METHOD_CODE,
wts.STOP_ID,
wts.STOP_LOCATION_ID,
wts.STATUS_CODE,
wts.STOP_SEQUENCE_NUMBER,
wts.PLANNED_ARRIVAL_DATE,
wts.PLANNED_DEPARTURE_DATE,
wts.ACTUAL_ARRIVAL_DATE,
wts.ACTUAL_DEPARTURE_DATE,
wts.DEPARTURE_NET_WEIGHT,
wts.WEIGHT_UOM_CODE,
wdl.DELIVERY_LEG_ID,
wdl.DELIVERY_ID,
wdl.PICK_UP_STOP_ID,
wdl.DROP_OFF_STOP_ID,
wdl.SEQUENCE_NUMBER,
wdl.LOADING_ORDER_FLAG,
wdl.SHIPPER_TITLE,
wdl.SHIPPER_PHONE
FROM wsh_trips wt
,wsh_trip_stops wts
,wsh_delivery_legs wdl
WHERE wdl.delivery_id =12814
AND wts.stop_id = wdl.pick_up_stop_id
AND wts.trip_id = wt.trip_id;
28. Query to find Requisition header info
set lines 150
set pages 150
execute fnd_client_info.set_org_context('204');
col Description form a40
col Req_type form a26
col type_lookup_code form a16
col PREPARER form a30
col APPROVER form a30
col NOTE_TO_APPROVER form a40
SELECT prh.segment1 Requisition
, psp.manual_req_num_type req_num_type
, ppf.full_name Preparer
, prh.creation_date Creation_Date
, prh.type_lookup_code
, ppf1.full_name Approver
, t.type_name Req_type
, prh.description Description
, pah.note Note_To_Approver
, prh.requisition_header_id Req_header
FROM po_requisition_headers prh
, per_people_f ppf1
, per_people_f ppf
, po_action_history pah
, po_system_parameters psp
, PO_DOCUMENT_TYPES_ALL_TL T
, PO_DOCUMENT_TYPES_ALL_B B
WHERE prh.REQUISITION_HEADER_ID=11675
and NVL(PRH.contractor_requisition_flag, 'N') <> 'Y'
AND prh.preparer_id = ppf.person_id
AND nvl(ppf.business_group_id, 0) = (select nvl(max(fsp.business_group_id), 0)
from financials_system_parameters fsp)
AND nvl(pah.action_code,'SUBMIT') in ('SUBMIT', 'FORWARD', 'REJECT', 'APPROVE',
'APPROVE AND RESERVE', 'RESERVE', 'ACCEPT','RETURN')
--AND prh.segment1 = P_req_num_from
AND EXISTS (SELECT null
FROM po_requisition_lines prl
WHERE prl.requisition_header_id = prh.requisition_header_id
AND nvl(prl.modified_by_agent_flag,'N') = 'N'
AND nvl(prl.closed_code,'OPEN') != 'FINALLY CLOSED')
AND pah.object_id = prh.requisition_header_id
AND pah.employee_id = ppf1.person_id
AND pah.object_type_code = 'REQUISITION'
AND pah.object_sub_type_code = prh.type_lookup_code
AND pah.sequence_num =
(SELECT max(sequence_num)
FROM po_action_history pah
WHERE pah.object_id = prh.requisition_header_id
AND pah.object_type_code = 'REQUISITION'
AND pah.object_sub_type_code = prh.type_lookup_code)
and B.DOCUMENT_TYPE_CODE = T.DOCUMENT_TYPE_CODE
AND B.DOCUMENT_SUBTYPE = T.DOCUMENT_SUBTYPE
AND b.document_type_code = 'REQUISITION'
AND b.document_subtype = prh.type_lookup_code
AND NVL(B.ORG_ID, -99) = NVL(T.ORG_ID, -99)
AND NVL(B.ORG_ID,NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1 ,1),' ',
NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99))
= NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ', NULL,
SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)
AND T.LANGUAGE = USERENV('LANG')
/
29. Query to find Requisition details info
set lines 150
set pages 150
execute fnd_client_info.set_org_context('204');
col Justification form a24
col Item_Description form a36
col Source form a56
col Source_Type form a12
col Requestor form a20
col Line_Type form a12
col Item form a16
SELECT prl.line_num Line
, plt.line_type Line_Type
, prl.item_id prl_item_id
, msi.segment1 Item
, prl.item_revision Rev
, prl.need_by_date Need_By_Date
, prl.unit_meas_lookup_code Unit
, round(prl.quantity,2) Quantity_Amount
, prl.unit_price Unit_Price
, DECODE (PRL.order_type_lookup_code, /* <SERVICES FPJ> */
'FIXED PRICE', PRL.amount,
'RATE', PRL.amount,
NVL(PRL.quantity, 1) * PRL.unit_price) C_AMOUNT
, ppf.full_name Requestor
, plc.displayed_field Source_Type
, decode(prl.source_type_code,'INVENTORY',ood.organization_name||' - '||prl.source_subinventory,'VENDOR',prh.segment1||' - '||prl.suggested_vendor_name||' - '||prl.suggested_vendor_location||' - '||prl.suggested_vendor_contact||' - '||prl.suggested_buyer_id,null) Source
, prl.item_description Item_Description
, prd.req_line_quantity Distributions
, prl.justification Justification
, prl.requisition_header_id
, prl.requisition_line_id
FROM po_requisition_headers prh
, po_requisition_lines prl
, po_req_distributions prd
, po_line_types plt
, per_people_f ppf
, org_organization_definitions ood
, po_lookup_codes plc
, mtl_system_items msi
, mtl_categories mca
, gl_code_combinations gcc, financials_system_parameters fsp
, po_system_parameters psp
WHERE prh.segment1 = '1713'
AND prl.requisition_line_id = prd.requisition_line_id
AND prl.requisition_header_id = prh.requisition_header_id
AND prl.line_type_id = plt.line_type_id
AND prl.to_person_id = ppf.person_id (+)
AND prl.source_organization_id = ood.organization_id(+)
AND plc.lookup_type = 'REQUISITION SOURCE TYPE'
AND plc.lookup_code = prl.source_type_code
AND nvl(ppf.business_group_id, 0) = (select nvl(max(fsp.business_group_id),0)
from financials_system_parameters fsp)
AND trunc(sysdate)
BETWEEN nvl(ppf.effective_start_date, trunc(sysdate))
AND nvl(ppf.effective_end_date, trunc(sysdate))
AND prl.item_id = msi.inventory_item_id(+)
AND msi.organization_id = 204
AND prl.category_id = mca.category_id
AND prd.code_combination_id = gcc.code_combination_id
AND nvl(prl.modified_by_agent_flag,'N') = 'N'
AND nvl(prl.cancel_flag,'N') != 'Y'
AND nvl(prl.closed_code,'OPEN') != 'FINALLY CLOSED'
ORDER BY prl.line_num
/
30. Query to find an PO details
set lines 150
set pages 150
execute fnd_client_info.set_org_context('204');
col PO_Number_Release form a16
col Vendor form a28
col Description form a24
col Unit_Price form a18
col unit form a8
SELECT
decode(por.release_num,NULL, poh.segment1, poh.segment1 ||'-'|| por.release_num) PO_Number_Release
, pol.line_num Line
, pov.vendor_name Vendor
, pol.item_revision Rev
, pol.item_description Description
, pll.shipment_num
, pod.distribution_num Distribution
, decode(plt.order_type_lookup_code, 'AMOUNT',NULL,pll.price_override) Unit_Price
, pll.promised_date Promised_Date
, pol.unit_meas_lookup_code Unit
, DECODE (POL.order_type_lookup_code,
'RATE', POD.amount_ordered,
'FIXED PRICE', POD.amount_ordered,
POD.quantity_ordered) Quantity_Amount_Ordered
, DECODE (POL.order_type_lookup_code,
'RATE', POD.amount_billed,
'FIXED PRICE', POD.amount_billed,
POD.quantity_billed) Quantity_Amount_Billed
, DECODE (POL.order_type_lookup_code,
'RATE', POD.amount_delivered,
'FIXED PRICE', POD.amount_delivered,
POD.quantity_delivered) Qty_Amount_Delivered
, DECODE (POL.order_type_lookup_code,
'RATE', (NVL(POD.amount_ordered, 0) - NVL(POD.amount_billed, 0)) /
DECODE (NVL(POD.amount_ordered, 0), 0, 1, POD.amount_ordered),
'FIXED PRICE', (NVL(POD.amount_ordered, 0) - NVL(POD.amount_billed, 0)) /
DECODE (NVL(POD.amount_ordered, 0), 0, 1, POD.amount_ordered),
(NVL(POD.quantity_ordered, 0) - NVL(POD.quantity_billed, 0)) /
DECODE (NVL(POD.quantity_ordered, 0), 0, 1, POD.quantity_ordered)) * 100 Percent_Unbilled
, DECODE (POL.order_type_lookup_code,
'RATE', POD.amount_ordered - NVL(POD.amount_cancelled, 0)- NVL(POD.amount_billed, 0),
'FIXED PRICE', POD.amount_ordered - NVL(POD.amount_cancelled, 0)- NVL(POD.amount_billed, 0),
(POD.quantity_ordered - NVL(POD.quantity_cancelled, 0)- NVL(POD.quantity_billed, 0)) * PLL.price_override) C_AMOUNT_OPEN_INV
, poh.po_header_id
, pol.po_line_id
, por.release_num
, poh.currency_code C_CURRENCY
, nvl(por.po_release_id,-1) release_id
FROM po_distributions pod
, mtl_system_items msi
, po_line_locations pll
, po_lines pol
, po_releases por
, po_headers poh
, po_vendors pov
, financials_system_parameters fsp
, po_line_types plt
WHERE poh.segment1='804'
AND poh.po_header_id = pol.po_header_id
AND pol.po_line_id = pll.po_line_id
AND pll.line_location_id = pod.line_location_id
AND pol.item_id = msi.inventory_item_id (+)
AND msi.organization_id = fsp.inventory_organization_id
AND poh.vendor_id = pov.vendor_id (+)
AND pll.po_release_id = por.po_release_id (+)
AND pol.line_type_id = plt.line_type_id
AND pll.shipment_type in ('STANDARD','BLANKET','SCHEDULED')
AND nvl(pol.closed_code,'OPEN') not in ('CLOSED','FINALLY CLOSED')
AND nvl(pll.closed_code,'OPEN') not in ('CLOSED','FINALLY CLOSED')
AND nvl(poh.closed_code,'OPEN') not in ('CLOSED','FINALLY CLOSED')
AND nvl(por.closed_code,'OPEN') not in ('CLOSED','FINALLY CLOSED')
AND nvl(poh.cancel_flag,'N') = 'N'
AND nvl(por.cancel_flag,'N') = 'N'
AND nvl(pol.cancel_flag,'N') = 'N'
AND nvl(pll.cancel_flag,'N') = 'N'
ORDER BY pll.line_location_id
/
31. Query to find receipts against a PO shipment line
set lines 150
set pages 150
execute fnd_client_info.set_org_context('204');
SELECT
pol.po_header_id,
pol.po_line_id,
pll.line_location_id,
pll.quantity,
rsh. shipment_header_id,
rsh. receipt_source_code,
rsh. vendor_id,
rsh. vendor_site_id,
rsh. organization_id,
rsh. shipment_num,
rsh. receipt_num,
rsh. ship_to_location_id,
rsh. bill_of_lading,
rsl.shipment_line_id,
rsl.QUANTITY_SHIPPED,
rsl.QUANTITY_RECEIVED ,
rct.transaction_type,
rct.transaction_id,
decode(pol.order_type_lookup_code,'RATE',nvl(rct.amount,0),'FIXED PRICE',nvl(rct.amount,0),
nvl(rct.source_doc_quantity,0) ) transaction_qty
from rcv_transactions rct
, rcv_shipment_headers rsh
, rcv_shipment_lines rsl
, po_lines pol
, po_line_locations pll
where rct.po_line_location_id = 28302
and rct.po_line_location_id = pll.line_location_id
and rct.po_line_id = pol.po_line_id
and nvl(pol.order_type_lookup_code,'QUANTITY') NOT IN ('RATE','FIXED PRICE')
and rct.shipment_line_id=rsl.shipment_line_id
and rsl.shipment_header_id=rsh.shipment_header_id
order by rct.transaction_id
set lines 150
set pages 150
execute fnd_client_info.set_org_context('204');
SELECT pol.po_header_id,pol.po_line_id,rct.po_line_location_id Line_location_id
, sum ( (nvl(rct.source_doc_quantity,0)) ) Qty_returned
from rcv_transactions rct
, po_lines pol
, po_line_locations pll
where rct.transaction_type = 'RETURN TO VENDOR'
and rct.po_line_location_id = pll.line_location_id
and rct.po_line_id = pol.po_line_id
and nvl(pol.order_type_lookup_code,'QUANTITY') NOT IN ('RATE','FIXED PRICE')
group by pol.po_header_id,pol.po_line_id,rct.po_line_location_id
union all
SELECT pol.po_header_id,pol.po_line_id,rct.po_line_location_id Line_location_id
, sum ( (nvl(rct.amount,0)) ) Qty_returned
from rcv_transactions rct
, po_lines pol
, po_line_locations pll
where rct.transaction_type = 'RETURN TO VENDOR'
and rct.po_line_location_id = pll.line_location_id
and rct.po_line_id = pol.po_line_id
and nvl(pol.order_type_lookup_code,'QUANTITY') IN ('RATE','FIXED PRICE')
group by pol.po_header_id,pol.po_line_id,rct.po_line_location_id
/
33. Query to find PO corrections
set lines 150
set pages 150
execute fnd_client_info.set_org_context('204');
SELECT pol.po_header_id,pol.po_line_id, rct.po_line_location_id Line_location_id
, sum (nvl(rct1.source_doc_quantity,0) ) Qty_corrected
from rcv_transactions rct
, rcv_transactions rct1
, po_lines pol
, po_line_locations pll
where rct.transaction_type in ( 'RECEIVE' ,'MATCH')
and rct.po_line_location_id = pll.line_location_id
and rct1.transaction_type = 'CORRECT'
and rct1.parent_transaction_id = rct.transaction_id
and rct1.po_line_location_id = pll.line_location_id
and rct.po_line_id = pol.po_line_id
and nvl(pol.order_type_lookup_code,'QUANTITY') NOT IN ('RATE','FIXED PRICE')
group by pol.po_header_id,pol.po_line_id,rct.po_line_location_id
union all
SELECT pol.po_header_id,pol.po_line_id,rct.po_line_location_id Line_location_id
, sum (nvl(rct1.amount,0) ) Qty_corrected
from rcv_transactions rct
, rcv_transactions rct1
, po_lines pol
, po_line_locations pll
where rct.transaction_type in ( 'RECEIVE' ,'MATCH')
and rct.po_line_location_id = pll.line_location_id
and rct1.transaction_type = 'CORRECT'
and rct1.parent_transaction_id = rct.transaction_id
and rct1.po_line_location_id = pll.line_location_id
and rct.po_line_id = pol.po_line_id
and nvl(pol.order_type_lookup_code,'QUANTITY') IN ('RATE','FIXED PRICE')
group by pol.po_header_id,pol.po_line_id,rct.po_line_location_id
34. Query to find first level components of an Assemby
set lines 150
set pages 150
col segment1 form a20
select
bom.assembly_item_id,
bic.component_sequence_id ,
bic.bill_sequence_id ,
--parent_bill_seq_id ,
bic.operation_seq_num ,
bic.component_item_id ,
msi.segment1,
--bic.bom_item_type ,
bic.item_num ,
bic.component_quantity
from
bom_inventory_components bic,
mtl_system_items msi,
bom_bill_of_materials bom
where bom.assembly_item_id=149
and bom.organization_id=207
and bom.bill_sequence_id=bic.bill_sequence_id
and bic.component_item_id=msi.inventory_item_id
and msi.organization_id=207
order by 1,2
/
35. Query to find all level components of an Assembly
set lines 150
set pages 150
col parent_item form a20
col child_item form a20
break on parent_item
select
(select msi.segment1 from mtl_system_items msi where msi.inventory_item_id=bom.assembly_item_id and msi.organization_id=207) parent_item,
bom.assembly_item_id,
lpad(' ',2*(level-1),' ')||
(select msi.segment1 from mtl_system_items msi where msi.inventory_item_id=bic.component_item_id and msi.organization_id=207) child_item,
bic.component_item_id child_item_id,
--bic.component_sequence_id ,
bic.bill_sequence_id ,
--parent_bill_seq_id ,
bic.operation_seq_num ,
--bic.bom_item_type ,
--bic.item_num ,
level,
bic.component_quantity
from
bom_inventory_components bic,
(select * from bom_bill_of_materials where organization_id=207) bom
where bom.bill_sequence_id=bic.bill_sequence_id
start with bom.assembly_item_id=149
connect by prior bic.component_item_id=bom.assembly_item_id
--order by level, bom.assembly_item_id
/
(Oracle uses BOM explosion package for this, which takes care of various conditions like Model and Option class etc)
36. Query to find latest execution of the Concurrent Program
select fcp.concurrent_program_name,
fcp.user_concurrent_program_name,
fcr.requested_by Executed_By,
count(fcr.concurrent_program_id) Exec_Count,
max(fcr.request_date) Last_Run_Date
from FND_CONCURRENT_REQUESTS fcr,
FND_CONCURRENT_PROGRAMS_VL FCP
where 1=1
AND upper(fcp.concurrent_program_name)='QL_INV_TOT_REV'
AND fcr.PROGRAM_APPLICATION_ID= fcp.application_id
and fcp.concurrent_program_id = fcr.concurrent_program_id
group by fcp.concurrent_program_name,fcp.user_concurrent_program_name,fcr.requested_by
37. Querying order_number and po_number through receipt_num
select rsh.receipt_num,
ooha.order_number,
pha.segment1 po_number
from oe_order_headers_all ooha
, oe_order_lines_all oola
, oe_drop_ship_sources odss
, po_headers_all pha
, po_lines_all pla
,rcv_shipment_headers rsh
,rcv_shipment_lines rsl
where oola.header_id =ooha.header_id
AND odss.header_id =ooha.header_id
AND odss.line_id =oola.line_id
AND odss.po_header_id =pha.po_header_id
AND odss.po_line_id =pla.po_line_id
AND rsl.po_header_id =pha.po_header_id
AND rsl.shipment_header_id=rsh.shipment_header_id
AND rsh.receipt_num='8000030674';
select ooha.order_number
, ooha.order_type_id
,ooha.created_by
, fnd.user_name
,oola.flow_status_code
,oola.ordered_quantity
,oola.shipped_quantity
,oola.fulfilled_quantity
,oola.shipping_quantity
, ottt.name order_type
,otta.sales_document_type_code
, prha.segment1 requisition_num
,prla.line_num requisition_line_num
,pha.creation_date "po date"
,pha.segment1 "po number"
,pha.type_lookup_code "po type"
,pda.quantity_ordered
, pda.quantity_delivered
, pla.line_num "po line num"
,pha.authorization_status po_status
, rsh.receipt_num
,rsl.line_num shipment_line_num
from oe_order_headers_all ooha
,oe_order_lines_all oola
, oe_drop_ship_sources odss
, po_requisition_headers_all prha
, po_requisition_lines_all prla
, po_headers_all pha
,po_lines_all pla
,po_distributions_all pda
, rcv_shipment_headers rsh
,rcv_shipment_lines rsl
,oe_transaction_types_tl ottt
, oe_transaction_types_all otta
,fnd_user fnd
where oola.header_id =ooha.header_id
AND ottt.transaction_type_id =otta.transaction_type_id
AND otta.transaction_type_id =ooha.order_type_id
AND fnd.user_id =ooha.created_by
AND odss.line_id =oola.line_id
AND odss.header_id =ooha.header_id
AND odss.requisition_line_id =prla.requisition_line_id
AND prla.requisition_header_id =prha.requisition_header_id
AND pla.po_header_id =pha.po_header_id
AND odss.po_line_id =pla.po_line_id
AND odss.po_header_id =pha.po_header_id
AND pla.po_line_id =pda.po_line_id
AND rsl.po_header_id =pha.po_header_id
AND rsl.shipment_header_id =rsh.shipment_header_id
AND ooha.order_number=100013652;
SeLECT ooha.order_number
,shp_hp.party_name customer_name
,shp_hca.account_number customer_number
,shp_hp.party_name ship_to_customer_name
,shp_hca.account_number ship_to_customer_number
,shp_hl.address1 shp_address
,shp_hl.address2
,shp_hl.address3
,shp_hl.city||' , '||shp_hl.state||' , '||shp_hl.postal_code||
' , '||shp_hl.country ship_to_address
,bll_hp.party_name bill_to_customer_name
,bll_hca.account_number bill_to_customer_number
,bll_hl.address1 billing_address
, bll_hl.address2
,bll_hl.address3
,bll_hl.city||' , '||bll_hl.state||
' , '||bll_hl.postal_code||
' , '||bll_hl.country bll_to_address
,oola.line_number
,msi.segment1 item
,msi.description
,oola.ordered_quantity
,wdd.unit_price
,wdd.unit_price * wdd.SHIPPED_QUANTITY line_amount
,wnd.delivery_id
,rct.trx_number
,rct.trx_date
,rctl.line_number
,rctl.quantity_invoiced
--,quantity_ordered
,rctl.unit_selling_price *
rctl.QUANTITY_INVOICED invoice_line_amount
FROM oe_order_headers_all ooha,
oe_order_lines_all oola,
hz_parties shp_hp,
hz_party_sites shp_hps,
hz_cust_accounts shp_hca,
hz_cust_acct_sites_all shp_hcasa,
hz_cust_site_uses_all shp_hcsua,
hz_locations shp_hl,
hz_parties bll_hp,
hz_party_sites bll_hps,
hz_cust_accounts bll_hca,
hz_cust_acct_sites_all bll_hcasa,
hz_cust_site_uses_all bll_hcsua,
hz_locations bll_hl,
mtl_system_items_b msi,
wsh_delivery_details wdd,
wsh_delivery_assignments wda,
wsh_new_deliveries wnd,
ra_customer_trx_all rct,
ra_customer_trx_lines_all rctl
WHERE oola.header_id=ooha.header_id
AND shp_hps.party_id=shp_hp.party_id
AND shp_hca.party_id=shp_hp.party_id
AND shp_hcasa.party_site_id=shp_hps.party_site_id
AND shp_hcasa.cust_account_id=shp_hca.cust_account_id
AND shp_hcsua.site_use_id=ooha.ship_to_org_id
AND shp_hcsua.cust_acct_site_id=shp_hcasa.cust_acct_site_id
AND shp_hl.location_id=shp_hps.location_id
AND bll_hps.party_id=bll_hp.party_id
AND bll_hca.party_id=bll_hp.party_id
AND bll_hcasa.party_site_id=bll_hps.party_site_id
AND bll_hcasa.cust_account_id=bll_hca.cust_account_id
AND bll_hcsua.cust_acct_site_id=bll_hcasa.cust_acct_site_id
AND bll_hcsua.site_use_id=ooha.invoice_to_org_id
AND bll_hl.location_id=bll_hps.location_id
AND msi.inventory_item_id=oola.inventory_item_id
AND msi.organization_id=oola.ship_from_org_id
AND wdd.source_line_id=oola.line_id
AND wda.delivery_detail_id=wdd.delivery_detail_id
AND wda.delivery_id=wnd.delivery_id
AND rctl.customer_trx_id=rct.customer_trx_id
AND rctl.interface_line_attribute6=oola.line_id
AND ooha.order_number=100013627
;
SELECT pha.segment1 po_number
,pv.vendor_id
,pv.vendor_name
,shp_hl.location_code ship_to
,pvs.vendor_site_code
,pla.line_num
,plt.line_type
,pla.item_description
,pla.quantity
,pla.unit_price
,pla.quantity * pla.unit_price line_amount
,pll.invoice_close_tolerance
,pll.match_option
,pll.receive_close_tolerance
,rsl.line_num shipment_line_num
,rsl.to_organization_id
,rsl.quantity_shipped
,pda.distribution_num
,pda.destination_type_code
,pda.destination_subinventory
,pda.quantity_delivered
,rsh.receipt_num
,aia.invoice_num
,aia.invoice_amount
,aia.invoice_date
FROM po_headers_all pha
, po_lines_all pla
, po_vendors pv
, po_vendor_sites_all pvs
, po_line_types plt
, po_line_locations_all pll
, hr_locations shp_hl
--, hr_locations bll_hl
,rcv_shipment_lines rsl
, po_distributions_all pda
, rcv_shipment_headers rsh
, rcv_transactions rt
, ap_invoices_all aia
, ap_invoice_distributions_all aid
WHERE pha.po_header_id =pla.po_header_id
AND pha.vendor_id =pv.vendor_id
AND pv.vendor_id =pvs.vendor_id
and pha.org_id = pvs.org_id
AND pla.line_type_id =plt.line_type_id
AND pla.po_header_id =pll.po_header_id
AND pla.po_line_id =pll.po_line_id
AND shp_hl.ship_to_location_id =pll.ship_to_location_id
AND pha.po_header_id =rsl.po_header_id
AND rsh.shipment_header_id =rsl.shipment_header_id
AND rsl.po_distribution_id =pda.po_distribution_id
AND aia.invoice_id =aid.invoice_id
--AND pda.po_distribution_id =aid.po_distribution_id
AND aid.rcv_transaction_id =rt.transaction_id
AND rsh.shipment_header_id =rt.shipment_header_id
AND pha.segment1 ='Testing001';
/*Download description flexfield*/
Run the below download command in home directory of Instance1, the ldt file descript_flex.ldt is created in the same directory.
FNDLOAD apps/<password> 0 Y DOWNLOAD @FND:patch/115/import/afffload.lct descript_flex.ldt desc_flex_application DESCRIPTIVE_FLEXFIELD_NAME=description_flexfield_name
desc_flex_application - is the shortname of the Application of the DFF.
description_flexfield_name - This is not title of the DFF.
To get description_flexfield_name:
Open the DFF in Application DeveloperàFlexfieldàDescriptiveàSegments
click help->diagnostics->examine->
block=table, field=DESCRIPTIVE_FLEXFIELD_NAME.
The value in the field “Value” is the description_flexfield_name.
For Example, The description_flexfield_name of the DFF “Additional Information” in the above screenshot is: CS_INCIDENTS_ALL_B_EXT.
Application is: Service.
Short name of Service is: CS
And the download command is:
FNDLOAD apps/<password> 0 Y DOWNLOAD @FND:patch/115/import/afffload.lct descript_flex.ldt CS DESCRIPTIVE_FLEXFIELD_NAME=CS_INCIDENTS_ALL_B_EXT
/*Upload description flexfield*/
Transfer the ldt file descript_flex.ldt from Instance1 to the home directory of the Instance2 and run the below upload command in the same directory.
FNDLOAD apps/apps 0 Y UPLOAD @FND:patch/115/import/afffload.lct descript_flex.ldt
/*Compiling description flexfield*/
After migration, run the below compilation command to compile the flexfield in the Instance2.
fdfcmp apps/<password> 0 Y D desc_flex_application description_flexfield_name
doskey = set ksh -o vi
SELECT c.owner,c.object_name,c.object_type,b.sid,
b.serial#,b.status,b.osuser,b.machine
FROM v$locked_object a ,v$session b,dba_objects c
WHERE b.sid = a.session_id
AND a.object_id = c.object_id;
alter system kill session 'sid,serial#';
ex:- alter system kill session '7,36';
SELECT msi.inventory_item_id,msi.segment1,mic.category_id,mcs.category_set_id,mc.segment20 lob
FROM mtl_system_items msi,mtl_item_categories mic,mtl_category_sets mcs,mtl_categories mc
WHERE msi.inventory_item_id = 52986
AND msi.organization_id = 5760
AND mic.inventory_item_id = msi.inventory_item_id
AND mic.organization_id = msi.organization_id
AND mic.category_set_id = mcs.category_set_id
AND mcs.category_set_name = 'PRODUCT_LINE'
AND mc.category_id = mic.category_id;
Delete from emp where ROWID not in ( select
max(rowid) from emp group by empno);
select * from dba_locks where blocking_others = 'Blocking';
select * from dba_ddl_locks where name like '%QL_RMA4_WARR_INHERIT_PKG%';
alter system kill session '28,6316';
FORMS60_PATH=$FORMS60_PATH:$AU_TOP/forms/US
export FORMS60_PATH
echo $FORMS60_PATH
(or)
. ./APPSORA.env
f60gen ABCDEFG.fmb username/password
f60gen Module=CUSTOM.pll Userid=apps/apps Module_Type=LIBRARY Output_File=/proj4/proj4appl/au/11.5.0/resource/CUSTOM.plx Compile_All=Yes
After compiling the CUSTOM.pll we have to relogin the Application session
Choose the environment: - (Wesco Test system applmgr password : -DevOracle32!)
[applmgr@hqscm-tst-app01 ~]$ ls *.env
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct STI_SERIAL_FORMAT_LOV.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="INV" LOOKUP_TYPE="STI_SERIAL_FORMAT_LOV"
FNDLOAD apps/apps1571 O Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct STI_SERIAL_FORMAT_LOV.ldt
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct serial_number_validation_profile_erp2.ldt PROFILE PROFILE_NAME='QL:SERIAL_VALIDATIONS_OVERRIDE_ERP2' APPLICATION_SHORT_NAME='QL'
FNDLOAD apps/apps1571 O Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct serial_number_validation_profile_erp2.ldt
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct xyz.ldt PROGRAM APPLICATION_SHORT_NAME="FND" CONCURRENT_PROGRAM_NAME="concurrent name"
FNDLOAD apps/apps1571 O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct xyz.ldt
FNDLOAD apps/simple4u 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct NAP_ACK_R1.ldt REQUEST_GROUP REQUEST_GROUP_UNIT UNIT_TYPE='P' UNIT_APP='XBOL' UNIT_NAME='NAPP_OEXOEACK' REQUEST_GROUP_NAME='NAPP OM Concurrent Programs' APPLICATION_SHORT_NAME='ONT'
FNDLOAD apps/simple4u 0 Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct NAP_ACK_R1.ldt
USERS.DAT --> $FND_TOP/bin -- ASCII Format
QL_PRINT --> $FND_TOP/bin -- ASCII Format
*.dcl --> /usr/odcs636/doc -- Binary Format
>> Should run the <<ln -s $FND_TOP/bin/fndcpesr "file name without extension">> command from "QL_TOP/bin" to create the softlink
>> EX:- "ln -s $FND_TOP/bin/fndcpesr LOAD_HYPERION " ( Actual file name LOAD_HYPERION.prog )
adident Header POXPORRA.rdf
For a concurrent program defined via “Define Concurrent Program” form but submitted via CONCSUB, the following parameters can be used by CONCSUB :
PRINTER=<printer name>
NUMBER_OF_COPIES=<number of reports to be printed>
PRINT_STYLE=<printer style to be used>
LANGUAGE=<language to be used>
Sample:
CONCSUB apps/apps SYSADMIN “System Administrator” SYSADMIN \
WAIT=Y CONCURRENT FND FNDSCARU LANGUAGE=FRENCH \
PRINTER=hplj4l NUMBER_OF_COPIES=1 \
PRINT_STYLE=LANDSCAPE
Note: the others parameters which can be used with CONCSUB
are described in the “System Administration User Guide” as follow :
$ CONCSUB <APPS username>/<APPS password> \
<responsibility application short name> \
<responsibility name> \
<username> \
[WAIT=N|Y|<n seconds>] \
CONCURRENT \
<program application short name> \
<program name> \
[PROGRAM_NAME=<description>] \
[REPEAT_TIME=<resubmission time>] \
[REPEAT_INTERVAL= <number>] \
[REPEAT_INTERVAL_UNIT=< resubmission unit>] \
[REPEAT_INTERVAL_TYPE=< resubmission type>] \
[REPEAT_END=<resubmission end date and time>] \
[START=<date>] \
[IMPLICIT=< type of concurrent request> \
[<parameter 1> ... <parameter n>]
For parameters that follow the CONCURRENT parameter and include
spaces, enclose the parameter argument in double quotes, then again in
single quotes.
chmod -R 777 $PO_TOP
COPY FROM apps/apps@proj10 TO apps/apps1574@ps4 create sam USING select * from sam;
SELECT application_short_name, frt.responsibility_id, frt.responsibility_name
FROM apps.fnd_responsibility_tl frt, fnd_application fa
WHERE fa.application_id = frt.application_id;
SELECT DISTINCT a.responsibility_name, c.user_menu_name
FROM apps.fnd_responsibility_tl a,
apps.fnd_responsibility b,
apps.fnd_menus_tl c,
apps.fnd_menus d,
apps.fnd_application_tl e,
apps.fnd_application f
WHERE a.responsibility_id(+) = b.responsibility_id
AND a.responsibility_id = &resp_id
AND b.menu_id = c.menu_id
AND b.menu_id = d.menu_id
AND e.application_id = f.application_id
AND f.application_id = b.application_id
AND a.LANGUAGE = 'US';
SELECT DISTINCT U.USER_ID,
U.USER_NAME USER_NAME,
R.RESPONSIBILITY_NAME RESPONSIBLITY,
A.APPLICATION_NAME APPLICATION
FROM FND_USER U,
FND_USER_RESP_GROUPS G,
FND_APPLICATION_TL A,
FND_RESPONSIBILITY_TL R
WHERE G.USER_ID(+) = U.USER_ID
AND G.RESPONSIBILITY_APPLICATION_ID = A.APPLICATION_ID
AND A.APPLICATION_ID = R.APPLICATION_ID
AND G.RESPONSIBILITY_ID = R.RESPONSIBILITY_ID
ORDER BY 1;
================================================================================
SELECT responsibility_name responsibility, request_group_name,
frg.description
FROM fnd_request_groups frg, fnd_responsibility_vl frv
WHERE frv.request_group_id = frg.request_group_id
ORDER BY responsibility_name
Personalization is feature available in 11.5.10.X. For More detail on form Personalization Use Following Tables (Rule_id) is reference key for these tables
applsys.fnd_form_custom_actions, applsys.fnd_form_custom_scopes
SELECT ffft.user_function_name "User Form Name", ffcr.SEQUENCE,
ffcr.description, ffcr.rule_type, ffcr.enabled, ffcr.trigger_event,
ffcr.trigger_object, ffcr.condition, ffcr.fire_in_enter_query
FROM fnd_form_custom_rules ffcr, fnd_form_functions_vl ffft
WHERE ffcr.ID = ffft.function_id
ORDER BY 1;
SELECT a.application_name,
DECODE (b.status, 'i', 'installed', 's', 'shared', 'n / a') status,
patch_level
FROM apps.fnd_application_vl a, apps.fnd_product_installations b
WHERE a.application_id = b.application_id;
SELECT responsibility_name, frg.request_group_name,
fcpv.user_concurrent_program_name, fcpv.description
FROM fnd_request_groups frg,
fnd_request_group_units frgu,
fnd_concurrent_programs_vl fcpv,
fnd_responsibility_vl frv
WHERE frgu.request_unit_type = 'p'
AND frgu.request_group_id = frg.request_group_id
AND frgu.request_unit_id = fcpv.concurrent_program_id
AND frv.request_group_id = frg.request_group_id
ORDER BY responsibility_name;
SELECT fa.application_short_name,
fcpv.user_concurrent_program_name,
description,
DECODE (fcpv.execution_method_code,
'B', 'Request Set Stage Function',
'Q', 'SQL*Plus',
'H', 'Host',
'L', 'SQL*Loader',
'A', 'Spawned',
'I', 'PL/SQL Stored Procedure',
'P', 'Oracle Reports',
'S', 'Immediate',
fcpv.execution_method_code
) exe_method,
output_file_type, program_type, printer_name,
minimum_width,
minimum_length, concurrent_program_name,
concurrent_program_id
FROM fnd_concurrent_programs_vl fcpv, fnd_application fa
WHERE fcpv.application_id = fa.application_id
ORDER BY description
SELECT f.request_id , pt.user_concurrent_program_name user_concurrent_program_name
, f.actual_start_date actual_start_date
, f.actual_completion_date actual_completion_date,
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)
|| ' HOURS ' ||
floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)
|| ' MINUTES ' ||
round((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600 -
(floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)*60) ))
|| ' SECS ' time_difference
,
DECODE(p.concurrent_program_name,'ALECDC',p.concurrent_program_name||'['||f.description||']',p.concurrent_program_name) concurrent_program_name
, decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase
, f.status_code
FROM apps.fnd_concurrent_programs p
, apps.fnd_concurrent_programs_tl pt
, apps.fnd_concurrent_requests f
WHERE f.concurrent_program_id = p.concurrent_program_id
and f.program_application_id = p.application_id
and f.concurrent_program_id = pt.concurrent_program_id
and f.program_application_id = pt.application_id
AND pt.language = USERENV('Lang')
and f.actual_start_date is not null
ORDER by f.actual_completion_date-f.ac
--- Concurrent Program and Appication short name
n Concurent Program Short Name
n Appication Short Name
begin
fnd_program.delete_program ('XXCMHOEXOHOHS','ONT');
End;
-- XML Templates deleting quires from Back end
Template code and Application short name
n Concurent Program Short Name
select * from xdo_lobs where LOB_code ='XXCMHOEXOHOHS'
delete from xdo_lobs where LOB_code ='XXCMHOEXOHOHS'
begin
xdo_templates_pkg.delete_row('ONT','XXCMHOEXOHOHS');
end;
BEGIN
xdo_ds_definitions_pkg.delete_row('ONT','XXCMHOEXOHOHS');
End;
SELECT
sesion.serial#,
sesion.username,
-- sesion.sql_id,
-- sesion.sql_child_number,
optimizer_mode,
hash_value,
address,
sql_text
from v$sqlarea sqlarea, v$session sesion
where sesion.sql_hash_value = sqlarea.hash_value
and sesion.sql_address = sqlarea.address
and sesion.username is not null
select 'ALTER SYSTEM KILL SESSION '''||b.sid||','||b.serial#||''' IMMEDIATE;',username,osuser,machine,LOGON_TIME
from v$access a, v$session b
where a.object = 'ARC_SNAPSHOT_TB' –-any object name like pkg or tableor view
and a.sid = b.sid
1. Select max(log_sequence) from apps.fnd_log_messages
2. Then turn on the following debug profile for your user at the user level only.
FND: Debug Log Enabled -- Yes
FND: Debug Log Level -- Statement
FND: Debug Log Module -- %
FND: Debug Log Filename for Middle-Tier -- /dclo1i/applcsf/log/DCLO1I_auohsclop80/Customer_Category_Log.txt
Log out and log back into applications. The application will be very slow. Reproduce the issue. Then send the output of the following to Oracle.
3. select * from apps.fnd_log_messages
where log_sequence > (value from above query)
and user_id = <your fnd_user id>
/* Formatted on 2008/03/18 20:29 (Formatter Plus v4.8.6) */
SELECT responsibility_name
FROM fnd_responsibility_tl
WHERE responsibility_id IN
(SELECT responsibility_id
FROM fnd_responsibility
WHERE request_group_id IN
(SELECT request_group_id
FROM fnd_request_group_units
WHERE request_unit_id IN
(SELECT concurrent_program_id
FROM fnd_concurrent_programs_tl
WHERE user_concurrent_program_name LIKE 'XXAT: Agilent Service Agreement - AX Bill in Arrears: Reassign Document Sequence'
AND LANGUAGE = 'US'
)
)
)
AND LANGUAGE = 'US'
=================================
List of scheduled concurrent jobs
=================================
SELECT FCR.REQUEST_ID "REQUEST ID",
FCF.USER_CONCURRENT_PROGRAM_NAME "REQUEST NAME",
FCR.REQUESTED_BY "SUBMITTED BY",
FU.USER_NAME "USER_NAME",
TO_CHAR (FCR.REQUESTED_START_DATE, 'DD-MON-YY HH24:MI')
"SUBMITTED DATE"
FROM apps.FND_CONCURRENT_REQUESTS FCR,
apps.FND_CONC_REQUESTS_FORM_V FCF,
apps.FND_USER FU
WHERE FCR.PHASE_CODE = 'P'
AND FCR.STATUS_CODE = 'Q'
AND FCR.REQUEST_ID = FCF.REQUEST_ID
AND FU.USER_ID = FCR.REQUESTED_BY
AND TRUNC (SYSDATE) BETWEEN START_DATE AND NVL (END_DATE, SYSDATE + 1);
==============================
Withd Distinct Porgrmas Query
=======================-======
SELECT DISTINCT FCF.USER_CONCURRENT_PROGRAM_NAME "REQUEST NAME"
FROM apps.FND_CONCURRENT_REQUESTS FCR,
apps.FND_CONC_REQUESTS_FORM_V FCF
WHERE FCR.PHASE_CODE='P'
AND FCR.STATUS_CODE ='Q'
AND FCR.REQUEST_ID =FCF.REQUEST_ID; -- 151
Steps to follow for moving files with the proper permissions and ownership
======================================================
Please have the user follow this procedure to xfer files to the MT
Assuming you have impdba powerbroker role on auohscmht04 do the
following to put files to the MT
1] use winscp to connect to the host as c_<username>
2] now login to putty using your c_<username>
once logged in as c_<username>
$cd ..
$pwd
/home
$chmod -R 777 c_<username>
Once you logged in the instance, run the below mentioned commands
pbrun impdba password-manager tcmhti (To get the Database passwords)
Now login as the aptcmhti user
pbrun impdba -u aptcmhti ( /tcmhti/applmgr/1200/xxcmh/12.0.0 ( for bin))
now you should be able to copy your jsps from /home/c_username to say
for eg $OA_HTML/ or any other tops
Keep in mind that you should only copy the files using aptcmhti user and
not using the c_<username>
or the files will not have the proper ownership.
=================
Form Compliation
======================
f60gen "Path .fmb file" USERNAME/PASSWORD output_file =PATH file fmx
f60gen /home/sadevel/srinivas/IS0MAST/IS0MAST.fmb apps/thistl3 output_file =/home/sadevel/srinivas/IS0MAST/IS0MAST.fmx
SELECT OOH.ORDER_NUMBER||','||HP.PARTY_NAME||','||HL.ADDRESS1||','||
HL.ADDRESS2||','||HL.ADDRESS3||','||HL.ADDRESS4||','||HL.CITY||','||
HL.STATE||','||HL.POSTAL_CODE "DESCRIPTION"
,FFV.FLEX_VALUE || ','||FFV.DESCRIPTION Flex_description
,HCA.ACCOUNT_NUMBER ||' '||HCSU.LOCATION SHIP_TO_LOCATION1
,HCA.ACCOUNT_NUMBER ||' '||HCSU1.LOCATION BILL_TO_LOCATION1
,OTT.NAME ORDER_TYPE
,JRS.NAME SALESPERSON
,'NO DATA' SCHEDULERR
,'NO DATA' USERSTATUS
,'NO DATA' SETTLEMENTRULE
,'NO DATA' APPLICANTNUMBER
,OOH.REQUEST_DATE PROJECT_START_DATE
,'NO DATA' PROJECT_END_DATE
,OOH.ORDER_NUMBER
,HCSU.site_use_id SHIP_TO_LOCATION2
,HCSU1.site_use_id BILL_TO_LOCATION2
,OOH.flow_status_code
FROM OE_ORDER_HEADERS_ALL OOH
,OE_TRANSACTION_TYPES_TL OTT
,HZ_CUST_ACCOUNTS_ALL HCA
,HZ_PARTIES HP
,HZ_CUST_ACCT_SITES_ALL HCAS
,HZ_CUST_SITE_USES_ALL HCSU
,HZ_LOCATIONS HL
,HZ_PARTY_SITES HPS
,FND_FLEX_VALUES_VL FFV
,HZ_CUST_SITE_USES_ALL HCSU1
,JTF_RS_SALESREPS JRS
WHERE OOH.ORG_ID = 109
AND OOH.ORDER_TYPE_ID = OTT.TRANSACTION_TYPE_ID
AND OTT.NAME IN ('CNO EAS Equipment Order',
'CNO EAC Equipment Order',
'CNO CCTV Equipment Order',
'CNO ILS Equipment Order',
'CNO BA Equipment Order',
'CNO BURG Equipment Order' )
AND OOH.SHIP_TO_ORG_ID = HCSU.SITE_USE_ID
AND HCSU.CUST_ACCT_SITE_ID = HCAS.CUST_ACCT_SITE_ID
AND HCAS.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
AND HCA.PARTY_ID = HP.PARTY_ID
AND HP.PARTY_ID = HPS.PARTY_ID
AND HPS.PARTY_SITE_ID = HCAS.PARTY_SITE_ID
AND HPS.LOCATION_ID = HL.LOCATION_ID
AND HCSU1.SITE_USE_ID=OOH.INVOICE_TO_ORG_ID
AND FFV.FLEX_VALUE_SET_ID = 1003374
AND FFV.FLEX_VALUE = OOH.ATTRIBUTE2
AND OOH.INVOICE_TO_ORG_ID=HCSU1.SITE_USE_ID
AND OOH.SALESREP_ID = JRS.SALESREP_ID
AND OOH.FLOW_STATUS_CODE <> 'CANCELLED'
AND NOT EXISTS(SELECT 'A' FROM RA_CUSTOMER_TRX_ALL WHERE INTERFACE_HEADER_ATTRIBUTE1=TO_CHAR(OOH.ORDER_NUMBER));
/* Formatted on 2008/05/01 15:06 (Formatter Plus v4.8.0) */
SELECT request_id, actual_start_date, actual_completion_date,
TRUNC (MOD ((actual_completion_date - actual_start_date) * 24, 24)
) hr,
TRUNC (MOD ((actual_completion_date - actual_start_date) * 24 * 60,
60
)
) MIN,
TRUNC (MOD ((actual_completion_date - actual_start_date) * 24 * 60
* 60,
60
)
) sec,
argument_text, u.user_name, r.responsibility_id
FROM apps.fnd_concurrent_requests r, apps.fnd_user u
WHERE u.user_id = r.requested_by
AND concurrent_program_id = :cuncurrent_program_id
AND request_id = :request_id
ORDER BY TRUNC (MOD ((actual_completion_date - actual_start_date) * 24, 24)) DESC,
TRUNC (MOD ((actual_completion_date - actual_start_date) * 24 * 60,
60
)
) DESC,
TRUNC (MOD ((actual_completion_date - actual_start_date) * 24 * 60
* 60,
60
)
) DESC
select * from APPS.FND_RESPONSIBILITY_TL WHERE RESPONSIBILITY_ID = 55512
DESCRIBE Xcbpc_Trip_Stop_Rpt_Pkg.POOIO
DECLARE
TYPE myarray
IS
TABLE OF VARCHAR2 (255);
l_str myarray := myarray (' thousand ', ' lakh ', ' crore ' );
l_num VARCHAR2 (50) DEFAULT TRUNC (:P_NUMBER);
l_return VARCHAR2 (4000);
l_hun VARCHAR2 (400);
BEGIN
IF l_num IS NOT NULL AND l_num>0 THEN
l_hun := TO_CHAR (TO_DATE (SUBSTR (l_num, LENGTH (l_num) - 2, 3), 'J'), 'Jsp' );
END IF;
l_num := SUBSTR(l_num,1,LENGTH (l_num) -3);
FOR i IN 1 .. l_str.COUNT
LOOP
EXIT
WHEN l_num IS NULL;
IF (SUBSTR (l_num, LENGTH (l_num) - 2, 2) <> 0) THEN
l_return := TO_CHAR (TO_DATE (SUBSTR (l_num, LENGTH (l_num) - 2, 2), 'J'), 'Jsp' ) || l_str (i) || l_return;
END IF;
l_num := SUBSTR (l_num, 1, LENGTH (l_num) - 2);
END LOOP;
l_num := TRUNC (:P_NUMBER);
IF l_num IS NOT NULL AND l_num > 0 THEN
DBMS_OUTPUT.PUT_LINE( l_return||' '||l_hun||' Rupees Only');
END IF;
END;
CANCEL REQUISITIONS
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
INTERNAL REQUISITIONS THAT DO NOT HAVE AN ASSOCIATED INTERNAL SALES ORDER
SELECT
RQH.SEGMENT1
,RQL.LINE_NUM
,RQL.REQUISITION_HEADER_ID
,RQL.REQUISITION_LINE_ID
,RQL.ITEM_ID
,RQL.UNIT_MEAS_LOOKUP_CODE
,RQL.UNIT_PRICE
,RQL.QUANTITY
,RQL.QUANTITY_CANCELLED
,RQL.QUANTITY_DELIVERED
,RQL.CANCEL_FLAG
,RQL.SOURCE_TYPE_CODE
,RQL.SOURCE_ORGANIZATION_ID
,RQL.DESTINATION_ORGANIZATION_ID
,RQH.TRANSFERRED_TO_OE_FLAG
FROM
PO_REQUISITION_LINES_ALL RQL
,PO_REQUISITION_HEADERS_ALL RQH
WHERE
RQL.REQUISITION_HEADER_ID = RQH.REQUISITION_HEADER_ID and RQL.SOURCE_TYPE_CODE = 'INVENTORY'
And RQL.SOURCE_ORGANIZATION_ID is not null
and not exists(select 'existing internal order']
from OE_ORDER_LINES_ALL LIN
where
LIN.SOURCE_DOCUMENT_LINE_ID = RQL.REQUISITION_LINE_ID and LIN.SOURCE_DOCUMENT_TYPE_ID = 10)
ORDER BY RQH.REQUISITION_HEADER_ID, RQL.LINE_NUM;
DISPLAY WHAT REQUISITION AND PO ARE LINKED
(Relation with Requisition and PO )
SELECT
r.segment1 "Req Num"
,p.segment1 "PO Num"
from
,po_headers_all p
,po_distributions_all d
,po_req_distributions_all rd
,po_requisition_lines_all rl
,po_requisition_headers_all r
WHERE
p.po_header_id = d.po_header_id
and d.req_distribution_id = rd.distribution_id
and rd.requisition_line_id = rl.requisition_line_id
and rl.requisition_header_id = r.requisition_header_id
PURCHASE REQUISITION WITHOUT PO THAT MEANS A PR HAS NOT BEEN AUTOCREATED TO PO
(Purchase Requisition without a Purchase Order)
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.pl_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
ALL PO’s WITH APPROVAL, INVOICE, & PAYMENT DETAILS
SELECT
,a.org_id "ORG ID"
,E.SEGMENT1 "VENDOR NUM"
,e.vendor_name "SUPPLIER NAME"
,UPPER(e.vendor_type_lookup_code) "VENDOR TYPE"
,f.vendor_site_code "VENDOR SITE CODE"
,f.ADDRESS_LINE1 "ADDRESS"
,f.city "CITY"
,f.country "COUNTRY"
,to_char(trunc(d.CREATION_DATE)) "PO Date"
,d.segment1 "PO NUM"
,d.type_lookup_code "PO Type"
,c.quantity_ordered "QTY ORDERED"
,c.quantity_cancelled "QTY CANCELLED"
,g.item_id "ITEM ID"
,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 Approved?"
, 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
,h.check_id
,h.invoice_payment_id "Payment Id"
,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
andb.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'
ALL OPEN PO'S
SELECT
,h.segment1 "PO NUM"
,h.authorization_status "STATUS"
,l.line_num "SEQ NUM"
,ll.line_location_id
,d.po_distribution_id
,h.type_lookup_code "TYPE"
FROM
,po.po_headers_all h
,po.po_lines_all l
,po.po_line_locations_all ll
,po.po_distributions_all d
WHERE
h.po_header_id = l.po_header_id
and ll.po_line_id = l.po_Line_id
and ll.line_location_id = d.line_location_id
and h.closed_date is NULL
and h.type_lookup_code not in ('QUOTATION')
Purchase Order Cycle
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
select *
from rcv_shipment_headers
where shipment_header_id in
(select shipment_header_id
from rcv_shipment_lineswhere 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 : Mostly 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
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_transactionswhere 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 =))
AOL Queries
To get list of responsibilities
SELECT (SELECT application_short_name
FROM fnd_application fa
WHERE
fa.application_id = frt.application_id) application,
frt.responsibility_id,
frt.responsibility_name
FROM apps.fnd_responsibility_tl frt;
To get Menus Associated with responsibility
SELECT DISTINCT
a.responsibility_name,
c.user_menu_name
FROM apps.fnd_responsibility_tl a,
apps.fnd_responsibility b,
apps.fnd_menus_tl c,
apps.fnd_menus d,
apps.fnd_application_tl e,
apps.fnd_application f
WHERE a.responsibility_id(+) = b.responsibility_id
AND a.responsibility_name p_responsbility_name
AND b.menu_id = c.menu_id
AND b.menu_id = d.menu_id
AND e.application_id = f.application_id
AND f.application_id = b.application_id
AND a.LANGUAGE = 'US';
p_responsbility_name is the name of the responsbility_name to be passed
To get submenus and Function attached to this Main menu
SELECT c.prompt,
c.description
FROM apps.fnd_menus_tl a,
fnd_menu_entries_tl c
WHERE a.menu_id = c.menu_id
AND a.user_menu_name = p_menu_name
To get assigned responsibility to a user.
SELECT distinct
u.user_id
, SUBSTR (u.user_name, 1, 30) user_name
,SUBS TR (r.responsibility_name, 1, 60) responsiblity
,SUBSTR (a.application_name, 1, 50) application
FROM fnd_user u,
fnd_user_resp_groups g,
fnd_application_tl a,
fnd_responsibility_tl r
WHERE g.user_id(+) = u.user_id
AND g.responsibility_application_id = a.application_id
AND a.application_id = r.application_id
AND g.responsibility_id = r.responsibility_id
ORDER BY SUBSTR (user_name, 1, 30),
SUBSTR (a.application_name, 1, 50),
SUBSTR (r.responsibility_name, 1, 60);
To get responsibility and attached request groups.
SELECT responsibility_name responsibility
, request_group_name
,frg.description
FROM fnd_request_groups frg,
fnd_responsibility_vl frv
WHERE frv.request_group_id = frg.request_group_id
ORDER BY responsibility_name
Request Attached To Responsibility Listing
SELECT responsibility_name
,frg.request_group_name
,fcpv.user_concurrent_program_name
,fcpv.description
FROM fnd_request_groups frg,
fnd_request_group_units frgu,
fnd_concurrent_programs_vl fcpv,
fnd_responsibility_vl frv
WHERE frgu.request_unit_type = 'P'
AND frgu.request_group_id = frg.request_group_id
AND frgu.request_unit_id = fcpv.concurrent_program_id
AND frv.request_group_id = frg.request_group_id
ORDER BY responsibility_name;
To get all request with application
SELECT fa.application_short_name,
fcpv.user_concurrent_program_name,
Description,
DECODE (fcpv.execution_method_code,
'B', 'Request Set Stage Function',
'Q', 'SQL*Plus',
'H', 'Host',
'L', 'SQL*Loader',
'A', 'Spawned',
'I', 'PL/SQL Stored Procedure',
'P', 'Oracle Reports',
'S', 'Immediate',
fcpv.execution_method_code)
exe_method,
output_file_type,
program_type,
printer_name,
minimum_width,
minimum_length,
concurrent_program_name,
concurrent_program_id
FROM fnd_concurrent_programs_vl fcpv, fnd_application fa
WHERE fcpv.application_id = fa.application_id
ORDER BY description
Request Associated Resp
SELECT DISTINCT FCPL.USER_CONCURRENT_PROGRAM_NAME,
FCP.CONCURRENT_PROGRAM_NAME,
FAPP.APPLICATION_NAME,
FRG.REQUEST_GROUP_NAME,
FNRTL.RESPONSIBILITY_NAME
FROM APPS.FND_REQUEST_GROUPS FRG,
APPS.FND_APPLICATION_TL FAPP,
APPS.FND_REQUEST_GROUP_UNITS FRGU,
APPS.FND_CONCURRENT_PROGRAMS FCP,
APPS.FND_CONCURRENT_PROGRAMS_TL FCPL,
APPS.FND_RESPONSIBILITY FNR,
APPS.FND_RESPONSIBILITY_TL FNRTL
WHERE FRG.APPLICATION_ID = FAPP.APPLICATION_ID
AND FRG.APPLICATION_ID = FRGU.APPLICATION_ID
AND FRG.REQUEST_GROUP_ID = FRGU.REQUEST_GROUP_ID
AND FRG.REQUEST_GROUP_ID = FNR.REQUEST_GROUP_ID
AND FRG.APPLICATION_ID = FNR.APPLICATION_ID
AND FNR.RESPONSIBILITY_ID = FNRTL.RESPONSIBILITY_ID
AND FRGU.REQUEST_UNIT_ID = FCP.CONCURRENT_PROGRAM_ID
AND FRGU.UNIT_APPLICATION_ID = FCP.APPLICATION_ID
AND FCP.CONCURRENT_PROGRAM_ID = FCPL.CONCURRENT_PROGRAM_ID
AND FCPL.USER_CONCURRENT_PROGRAM_NAME LIKE '%Shipments Report - CP%'
AND FNRTL.LANGUAGE = 'US'
AND FAPP.LANGUAGE = 'US';
Request Status Listing
SELECT f.request_id,
pt.user_concurrent_program_name user_concurrent_program_name,
f.actual_start_date actual_start_date,
f.actual_completion_date actual_completion_date,
FLOOR (
( (f.actual_completion_date - f.actual_start_date)
* 24
* 60
* 60)
/ 3600)
|| ' HOURS '
|| FLOOR (
( ( (f.actual_completion_date - f.actual_start_date)
* 24
* 60
* 60)
- FLOOR (
( (f.actual_completion_date - f.actual_start_date)
* 24
* 60
* 60)
/ 3600)
* 3600)
/ 60)
|| ' MINUTES '
|| ROUND (
( ( (f.actual_completion_date - f.actual_start_date)
* 24
* 60
* 60)
- FLOOR (
( (f.actual_completion_date - f.actual_start_date)
* 24
* 60
* 60)
/ 3600)
* 3600
- ( FLOOR (
( ( (f.actual_completion_date - f.actual_start_date)
* 24
* 60
* 60)
- FLOOR (
( ( f.actual_completion_date
- f.actual_start_date)
* 24
* 60
* 60)
/ 3600)
* 3600)
/ 60)
* 60)))
|| ' SECS '
time_difference,
DECODE (
p.concurrent_program_name,
'ALECDC', p.concurrent_program_name || '[' || f.description || ']',
p.concurrent_program_name)
concurrent_program_name,
DECODE (f.phase_code,
'R', 'Running',
'C', 'Complete',
f.phase_code)
Phase,
f.status_code
FROM apps.fnd_concurrent_programs p,
apps.fnd_concurrent_programs_tl pt,
apps.fnd_concurrent_requests f
WHERE f.concurrent_program_id = p.concurrent_program_id
AND f.program_application_id = p.application_id
AND f.concurrent_program_id = pt.concurrent_program_id
AND f.program_application_id = pt.application_id
AND pt.language = USERENV ('Lang')
AND f.actual_start_date IS NOT NULL
ORDER BY f.actual_completion_date - f.actual_start_date DESC;
ADDING RESP TO USER
Add Responsibility to your USER without having Application System Administrator Rights
By following procedure you can add responsibility to your USER with out having Application System Administrator Rights
begin
fnd_user_pkg.addresp(
‘&User_Name’, /*Application User Name */
‘&Responsablity_Application_Short_Name’, /*get from Query Below */
‘&Responsibility_Key’,/*get from Query Below */
‘&Security_Group’, /* Most of cases it is ‘STANDARD’ so you can hard code it */
‘&Description’, /* Any comments you want */
‘&Start_Date’, /* Sysdate From Today */
‘&End_Date’ ); /* Sysdate + 365 Rights for Next One Year*/
commit;
dbms_output.put_line(’Responsibility Added Successfully’);
exception
when others then
dbms_output.put_line(’ Responsibility is not added due to ‘ || SQLCODE || substr(SQLERRM, 1, 100));
Rollback;
end;
To get value for ‘Responsablity_Application_Short_Name’ and ‘Responsibility_Key’ Parameters you need to run following sql Statement by Using APPS User Name
SELECT FAV.APPLICATION_SHORT_NAME,
FAV.APPLICATION_NAME,
FRV.RESPONSIBILITY_KEY,
FRV.RESPONSIBILITY_NAME
FROM FND_APPLICATION_VL FAV, FND_RESPONSIBILITY_VL FRV
WHERE FRV.APPLICATION_ID = FAV.APPLICATION_ID
ORDER BY FRV.RESPONSIBILITY_NAME
@Advanced Supply Chain Planner
EXAMPLE CODE
To add Responsibility of @Advanced Supply Chain Planner to User cbyna
BEGIN
fnd_user_pkg.addresp (’cbyna’,
‘MSC’,
‘ADV_SUPPLY_CHAIN_PLANNING’,
‘STANDARD’,
‘Test By Script’,
SYSDATE,
SYSDATE + 100
);
COMMIT;
DBMS_OUTPUT.put_line (’Responsibility Added Successfully’);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ( ‘ Responsibility is not added due to ‘
|| SQLCODE
|| SUBSTR (SQLERRM, 1, 100)
);
ROLLBACK;
END;
Query that gives the Accounting Entries to be passed in GL
Select
ad.source_type, ara.postable, segment1||'.'||segment2||'.'||segment3||'.'||segment4||'.'||segment5
acc, sum(ad.acctd_amount_dr), sum(ad.acctd_amount_cr) from ar_distributions_all ad,
ar_receivable_applications_all ara, gl_code_combinations gc
where ara.payment_schedule_id in
(select crh.payment_schedule_id from ar_distributions_all ad, ar_receivable_applications_all crh
where ad.code_combination_id= p_code_combination_id
and crh.receivable_application_id=ad.source_id
and ad.source_type='REC' and crh.posting_control_id<0 and crh.gl_date between '&From_GL_date' and
'&To_Gl_Date' and crh.org_id=&Org_id) and gc.code_combination_id = ad.code_combination_id and
ara.receivable_application_id = ad.source_id group by ad.source_type,ara.postable,
segment1||'.'||segment2||'.'||segment3||'.'||segment4||'.'||segment5
To Get Detail of Locks with Object Locked
SELECT VLO.OS_USER_NAME “OS USERNAME”, VLO.ORACLE_USERNAME “DB USER”,
VP.SPID “SPID”, AO.OWNER “OWNER”, AO.OBJECT_NAME “OBJECT LOCKED”,AO.OBJECT_TYPE,
DECODE (VLO.LOCKED_MODE,
1, ‘NO LOCK’,
2, ‘ROW SHARE’,
3, ‘ROW EXCLUSIVE’,
4, ‘SHARE’,
5, ‘SHARE ROW EXCL’,
6, ‘EXCLUSIVE’,
NULL
) “MODE OF LOCK”,
VS.STATUS “CURRENT STATUS”
FROM V$LOCKED_OBJECT VLO, ALL_OBJECTS AO, V$SESSION VS, V$PROCESS VP
WHERE VLO.OBJECT_ID = AO.OBJECT_ID
AND VS.STATUS <> ‘KILLED’
AND VLO.SESSION_ID = VS.SID
AND VS.PADDR = VP.ADDR;
SELECT PARTY_NAME
, ACCOUNT_NUMBER
,ORDER_NUMBER
,ORDERED_DATE
,SUB_TOTAL
,TAX
,CHARGES
,(SUB_TOTAL + TAX + CHARGES ) TOTAL
FROM
(SELECT HP.PARTY_NAME
,HCA.ACCOUNT_NUMBER
,OOH.ORDER_NUMBER
,OOH.ORDERED_DATE
,(SELECT SUM (NVL(ORDERED_QUANTITY,0) * NVL(UNIT_SELLING_PRICE,0))
FROM OE_ORDER_LINES_ALL
WHERE HEADER_ID = OOH.HEADER_ID) SUB_TOTAL
,(SELECT SUM (NVL(TAX_VALUE,0))
FROM OE_ORDER_LINES_ALL
WHERE HEADER_ID = OOH.HEADER_ID) TAX
,( SELECT SUM(NVL(CHARGE_AMOUNT,0))
FROM OE_CHARGE_LINES_V
WHERE header_id = OOH.HEADER_ID) CHARGES
FROM OE_ORDER_HEADERS_ALL OOH
,HZ_CUST_ACCOUNTS HCA
,HZ_PARTIES HP
WHERE OOH.ORDERED_DATE > = TO_DATE('01-01-2009','DD-MM-YYYY')
AND OOH.ORDERED_DATE <= TO_DATE('31-12-2010','DD-MM-YYYY')
AND OOH.SOLD_TO_ORG_ID = HCA.CUST_ACCOUNT_ID
AND HCA.PARTY_ID = HP.PARTY_ID
AND (HCA.ACCOUNT_NUMBER = '14874' OR HCA.ACCOUNT_NUMBER = '168541')
AND OOH.HEADER_ID = 3538222
);
SELECT PARTY_NAME
, ACCOUNT_NUMBER
,ORDER_NUMBER
,ORDERED_DATE
,SUB_TOTAL
,TAX
,CHARGES
,(SUB_TOTAL + TAX + CHARGES ) TOTAL
FROM
(SELECT HP.PARTY_NAME
,HCA.ACCOUNT_NUMBER
,OOH.ORDER_NUMBER
,OOH.ORDERED_DATE
,(SELECT SUM (NVL(ORDERED_QUANTITY,0) * NVL(UNIT_SELLING_PRICE,0))
FROM OE_ORDER_LINES_ALL@CPDEV_CPPROD
WHERE HEADER_ID = OOH.HEADER_ID) SUB_TOTAL
,(SELECT SUM (NVL(TAX_VALUE,0))
FROM OE_ORDER_LINES_ALL@CPDEV_CPPROD
WHERE HEADER_ID = OOH.HEADER_ID) TAX
,( SELECT SUM(NVL(CHARGE_AMOUNT,0))
FROM OE_CHARGE_LINES_V@CPDEV_CPPROD
WHERE header_id = OOH.HEADER_ID) CHARGES
FROM OE_ORDER_HEADERS_ALL@CPDEV_CPPROD OOH
,HZ_CUST_ACCOUNTS@CPDEV_CPPROD HCA
,HZ_PARTIES@CPDEV_CPPROD HP
WHERE OOH.ORDERED_DATE > = TO_DATE('01-01-2009','DD-MM-YYYY')
AND OOH.ORDERED_DATE <= TO_DATE('31-12-2010','DD-MM-YYYY')
AND OOH.SOLD_TO_ORG_ID = HCA.CUST_ACCOUNT_ID
AND HCA.PARTY_ID = HP.PARTY_ID
AND (HCA.ACCOUNT_NUMBER = '14874' OR HCA.ACCOUNT_NUMBER = '168541')
-- AND OOH.HEADER_ID = 3538222
);
SELECT PARTY_NAME ,
ACCOUNT_NUMBER ,
ORDER_NUMBER ,
ORDERED_DATE ,
SUB_TOTAL ,
TAX ,
CHARGES ,
(SUB_TOTAL + TAX + CHARGES ) TOTAL
FROM
(SELECT HP.PARTY_NAME ,
HCA.ACCOUNT_NUMBER ,
OOH.ORDER_NUMBER ,
OOH.ORDERED_DATE ,
(SELECT SUM (NVL(ORDERED_QUANTITY,0) * NVL(UNIT_SELLING_PRICE,0))
FROM OE_ORDER_LINES_ALL
WHERE HEADER_ID = OOH.HEADER_ID) SUB_TOTAL ,
(SELECT SUM (NVL(TAX_VALUE,0))
FROM OE_ORDER_LINES_ALL
WHERE HEADER_ID = OOH.HEADER_ID) TAX ,
(SELECT SUM(DECODE(P.LINE_ID, NULL, DECODE(P.CREDIT_OR_CHARGE_FLAG,'C',(-1) * P.OPERAND,P.OPERAND), DECODE(P.CREDIT_OR_CHARGE_FLAG,'C', DECODE(P.ARITHMETIC_OPERATOR, 'LUMPSUM', (-1) * (P.OPERAND), (-1) * (L.ORDERED_QUANTITY*P.ADJUSTED_AMOUNT)), DECODE(P.ARITHMETIC_OPERATOR, 'LUMPSUM', P.OPERAND, (L.ORDERED_QUANTITY*P.ADJUSTED_AMOUNT)))))
FROM OE_PRICE_ADJUSTMENTS P ,
OE_ORDER_LINES_ALL L ,
OE_ORDER_HEADERS_ALL H
WHERE P.HEADER_ID = H.HEADER_ID
AND P.LINE_ID = L.LINE_ID(+)
AND P.LIST_LINE_TYPE_CODE = 'FREIGHT_CHARGE'
AND P.APPLIED_FLAG = 'Y'
AND h.header_id = OOH.HEADER_ID) CHARGES
FROM OE_ORDER_HEADERS_ALL OOH ,
HZ_CUST_ACCOUNTS HCA ,
HZ_PARTIES HP
WHERE OOH.SOLD_TO_ORG_ID = HCA.CUST_ACCOUNT_ID
AND HCA.PARTY_ID = HP.PARTY_ID
AND HCA.ACCOUNT_NUMBER IN ('14874','168541')
AND OOH.ordered_date BETWEEN '01-JAN-09' AND '31-DEC-10');
SELECT OOH.ORDER_NUMBER ORDER_NUM,OOH.header_id,
OOH.ORDERED_DATE ORDER_DATE,
--OOL.ORDERED_QUANTITY * OOL.UNIT_SELLING_PRICE TOTAL_AMOUNT
OE_OE_TOTALS_SUMMARY.PRT_ORDER_TOTAL (ooh.header_id) Total
,HP.PARTY_NAME
,HCA.ACCOUNT_NUMBER
FROM OE_ORDER_HEADERS_ALL OOH
,HZ_CUST_ACCOUNTS HCA
, HZ_PARTIES HP
WHERE OOH.SOLD_TO_ORG_ID = HCA.CUST_ACCOUNT_ID
AND HCA.PARTY_ID = HP.PARTY_ID
AND TRUNC(OOH.ordered_date) BETWEEN '01-JAN-09' AND '07-JAN-09'
AND (HCA.ACCOUNT_NUMBER = '14874' OR HCA.ACCOUNT_NUMBER = '168541');
SELECT DISTINCT apps.pv.vendor_name vendor_name,
pv.segment1 vendor_number,
pvs.vendor_site_code vendor_site_code,
aba.bank_account_name bank_account_name,
aba.bank_account_num bank_account_num,
aba.currency_code currency_code,
abau.primary_flag primary_flag,
abb.bank_name bank_name,
abb.bank_number bank_number,
abb.bank_branch_name bank_branch_name,
abb.bank_num bank_num
FROM apps.ap_bank_account_uses_all abau,
apps.ap_bank_accounts_all aba,
apps.ap_bank_branches abb,
apps.po_vendors pv,
apps.po_vendor_sites_all pvs
WHERE abau.external_bank_account_id = aba.bank_account_id
AND aba.bank_branch_id = abb.bank_branch_id
AND abau.vendor_id = pv.vendor_id
AND abau.vendor_id = pvs.vendor_id(+)
AND abau.vendor_site_id = pvs.vendor_site_id(+)
select pv.vendor_name, pv.segment1,pvs.vendor_site_code,aba.bank_account_num,aba.bank_account_name,abb.bank_branch_name,abb.bank_name from apps.po_vendors pv, apps.po_vendor_sites_all pvs, apps.ap_bank_accounts_all aba, apps.ap_bank_account_uses_all abau,
apps.ap_bank_branches abb
where 1=1
and (pv.vendor_id,pvs.vendor_site_id) in ( select vendor_id, vendor_site_id from apps.ap_bank_account_uses_all where org_id=7604
and primary_flag='Y'
group by vendor_id, vendor_site_id
having count(*)>1)
and aba.bank_account_id=abau.external_bank_account_id
and abau.vendor_site_id=pvs.vendor_site_id
and aba.bank_branch_id=abb.bank_branch_id
and 1=1
order by 1,3
Fixed Assets
SELECT A.EVENT_TYPE_CODE,
A.ACCOUNTING_DATE,
A.JE_CATEGORY_NAME,
B.ACCOUNTING_CLASS_CODE,
B.AE_LINE_NUM,
C.SEGMENT1
|| '-'
|| C.SEGMENT2
|| '-'
|| C.SEGMENT3
|| '-'
|| C.SEGMENT4
|| '-'
|| C.SEGMENT5
|| '-'
|| C.SEGMENT6
ACCOUNT,
B.DESCRIPTION,
B.CURRENCY_CODE,
B.ENTERED_DR,
B.ENTERED_CR,
B.ACCOUNTED_DR,
A.ACCOUNTING_ENTRY_STATUS_CODE,
B.ACCOUNTED_CR
FROM XLA_AE_HEADERS A, XLA_AE_LINES B, GL_CODE_COMBINATIONS C
WHERE A.PERIOD_NAME = 'MAY-13'
AND A.APPLICATION_ID = 140
AND A.LEDGER_ID = 1001
AND A.AE_HEADER_ID = B.AE_HEADER_ID
AND B.CODE_COMBINATION_ID = C.CODE_COMBINATION_ID
AND ACCOUNTING_ENTRY_STATUS_CODE IN ('D', 'F')
AND B.DESCRIPTION LIKE '%102104072%'
ORDER BY A.JE_CATEGORY_NAME,
A.EVENT_TYPE_CODE,
A.AE_HEADER_ID,
B.AE_LINE_NUM
Following aresome SQL queries to tun to pull Oracle eBTax (Oracle eBusiness Tax) information directly from the tables.
a. Tax Regimes: ZX_REGIMES_B
b. Taxes: ZX_TAXES_B
c. Tax Status: ZX_STATUS_B
d. Tax Rates: ZX_RATES_B
e. Tax Jurisdictions: ZX_JURISDICTIONS_B
f. Tax Rules: ZX_RULES_B
You will most likely need to refine your extracts based on the data you have, whetehr you have migrated data or multiple countries etc.
SELECT *
FROM zx_regimes_b
WHERE tax_regime_code = '&tax_regime_code';
SELECT *
FROM zx_taxes_b
WHERE DECODE('&tax_name',null,'xxx',tax) = nvl('&tax_name','xxx')
AND tax_regime_code = '&tax_regime_code';
SELECT *
FROM zx_status_b
WHERE tax = '&tax_name'
AND tax_regime_code = '&tax_regime_code';
SELECT *
FROM zx_rates_b
WHERE tax = '&tax_name'
AND tax_regime_code = '&tax_regime_code';
SELECT *
FROM zx_jurisdictions_b
WHERE DECODE('&tax_name',null,'xxx',tax) = nvl('&tax_name','xxx')
AND tax_regime_code = '&tax_regime_code';
SELECT *
FROM zx_rules_b
WHERE tax = '&tax_name'
AND tax_regime_code = '&tax_regime_code';
===================================================
Select
dftt.DET_FACTOR_TEMPL_NAME,
dft.DETERMINING_FACTOR_CLASS_CODE,
dft.DETERMINING_FACTOR_CQ_CODE,
dft.DETERMINING_FACTOR_CODE,
dft.REQUIRED_FLAG--,
from zx_det_factor_templ_dtl dft, zx_det_factor_templ_tl dftt
WHERE dft.DET_FACTOR_TEMPL_ID = dftt.DET_FACTOR_TEMPL_ID
===================================================
Select
zxc.CONDITION_GROUP_CODE,
zxcg.DET_FACTOR_TEMPL_CODE,
zxc.DETERMINING_FACTOR_CLASS_CODE,
zxc.DETERMINING_FACTOR_CODE,
zxc.DETERMINING_FACTOR_CQ_CODE,
zxc.OPERATOR_CODE,
zxc.value_low
from zx_conditions zxc, zx_condition_groups_b zxcg
where OPERATOR_CODE <> 'Y'
and zxc.CONDITION_GROUP_CODE = zxcg.CONDITION_GROUP_CODE
AND ZXC.IGNORE_FLAG = 'N'
order by zxcg.det_factor_templ_code, zxc.CONDITION_GROUP_CODE, zxc.condition_group_code, zxc.determining_factor_class_code
===================================================
EBTAX TRANSACTION TABLES
Following are the main E-Business tax tables that will contain the transaction information that will have the tax details after tax is calculated.
a. ZX_LINES: This table will have the tax lines for associated with PO/Release schedules.
TRX_ID: Transaction ID. This is linked to the
PO_HEADERS_ALL.PO_HEADER_ID
TRX_LINE_ID: Transaction Line ID. This is linked to the
PO_LINE_LOCATIONS_ALL.LINE_LOCATION_ID
b. ZX_REC_NREC_DIST: This table will have the tax distributions for associated with PO/Release distributions.
TRX_ID: Transaction ID. This is linked to the
PO_HEADERS_ALL.PO_HEADER_ID
TRX_LINE_ID: Transaction Line ID. This is linked to the
PO_LINE_LOCATIONS_ALL.LINE_LOCATION_ID
TRX_LINE_DIST_ID: Transaction Line Distribution ID. This is linked to the
PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID
RECOVERABLE_FLAG: Recoverable Flag. If the distribution is recoverable then the flag will be set to Y and there will be values in the RECOVERY_TYPE_CODE and RECOVERY_RATE_CODE.
c. PO_REQ_DISTRIBUTIONS_ALL: This table will have the tax distributions for associated with Requisition distribution.
RECOVERABLE_TAX: Recoverable tax amount
NONRECOVERABLE_TAX: Non Recoverable tax amount
d. ZX_LINES_DET_FACTORS: This table holds all the information of the tax line transaction for both the requisitions as well as the purchase orders/releases.
TRX_ID: Transaction ID. This is linked to the
PO_REQUISITION_HEADERS_ALL.REQUISITION_HEADER_ID /
PO_HEADERS_ALL.PO_HEADER_ID
TRX_LINE_ID: Transaction Line ID. This is linked to the
PO_REQUISITION_LINES_ALL.REQUISITION_LINE_ID /
PO_LINE_LOCATIONS_ALL.LINE_LOCATION_ID
=============================================================
SQL FOR PARTY FISCAL CLASSIFICATION CODE
SELECT HPP.PARTY_NAME,HP.PARTY_SITE_NAME ,HCA.*
FROM ZX_PARTY_TAX_PROFILE ZP
,HZ_CODE_ASSIGNMENTS HCA
,HZ_PARTY_SITES HP
,HZ_PARTIES HPP
WHERE ZP.PARTY_TAX_PROFILE_ID = HCA.OWNER_TABLE_ID
--AND ZP.PARTY_ID = :PARTY_ID
AND HCA.OWNER_TABLE_NAME = 'ZX_PARTY_TAX_PROFILE'
AND HP.PARTY_SITE_ID = ZP.PARTY_ID
AND HPP.PARTY_ID= HP.PARTY_ID
AND HCA.CLASS_CODE IS NOT NULL
ORDER BY ZP.LAST_UPDATE_DATE DESC
SELECT HP.PARTY_ID, HP.PARTY_NAME, HPS.PARTY_SITE_ID, HPS.PARTY_SITE_NAME, ZP.PARTY_TAX_PROFILE_ID
FROM ZX_PARTY_TAX_PROFILE ZP,
HZ_PARTY_SITES HPS,
HZ_PARTIES HP,
HZ_CUST_ACCOUNTS_ALL CA
WHERE HP.PARTY_ID = HPS.PARTY_ID
AND HP.PARTY_ID = CA.PARTY_ID
AND HPS.PARTY_SITE_ID = ZP.PARTY_ID
AND CA.CUSTOMER_CLASS_CODE = 'WEB CUSTOMER'
AND UPPER(HP.PARTY_NAME) LIKE 'CAROLE%FINCK%'
AND EXISTS (
SELECT 1
FROM HZ_CODE_ASSIGNMENTS HCA
WHERE HCA.OWNER_TABLE_ID = ZP.PARTY_TAX_PROFILE_ID
AND HCA.OWNER_TABLE_NAME = 'ZX_PARTY_TAX_PROFILE'
AND HCA.CLASS_CODE IS NOT NULL)
ORDER BY ZP.LAST_UPDATE_DATE DESC;
=================================================================
CUSTOMER ADDRESSES THAT DOESNT HAVE ANY GEOGRAPHY REFERENCE
SELECT HCA.ACCOUNT_NUMBER
,HCA.ACCOUNT_NAME
,HCS_SHIP.SITE_USE_CODE
,HL_SHIP.ADDRESS1 ADDRESS
,HL_SHIP.STATE STATE
,HL_SHIP.COUNTY COUNTY
,HL_SHIP.CITY CITY
,HL_SHIP.POSTAL_CODE
FROM HZ_CUST_SITE_USES_ALL HCS_SHIP
, HZ_CUST_ACCT_SITES_ALL HCA_SHIP
, HZ_CUST_ACCOUNTS HCA
, HZ_PARTY_SITES HPS_SHIP
, HZ_LOCATIONS HL_SHIP
WHERE HCA.CUST_ACCOUNT_ID=HCA_SHIP.CUST_ACCOUNT_ID(+)
AND HCS_SHIP.CUST_ACCT_SITE_ID(+) = HCA_SHIP.CUST_ACCT_SITE_ID
-- AND HCA.ACCOUNT_NUMBER='10001'
AND HCA_SHIP.PARTY_SITE_ID = HPS_SHIP.PARTY_SITE_ID
AND HPS_SHIP.LOCATION_ID = HL_SHIP.LOCATION_ID
AND HCA.STATUS='A'
AND HCS_SHIP.STATUS='A'
AND HCA_SHIP.STATUS='A'
AND HL_SHIP.COUNTRY='US'
AND NOT EXISTS (SELECT 1 FROM HZ_GEOGRAPHIES HG
WHERE HG.GEOGRAPHY_ELEMENT2_CODE=HL_SHIP.STATE
AND UPPER(HL_SHIP.COUNTY)=UPPER(HG.GEOGRAPHY_ELEMENT3_CODE)
AND UPPER(HL_SHIP.CITY)=UPPER(HG.GEOGRAPHY_ELEMENT4_CODE)
AND SYSDATE BETWEEN HG.START_DATE AND HG.END_DATE)
=================================================================
LIST OF JURISDICTIONS' FOR WHICH TAX RATES HAS BEEN DEFINED
SELECT TAX,
TAX_JURISDICTION_CODE,
GEOGRAPHY_ELEMENT2_CODE STATE_CODE,
GEOGRAPHY_ELEMENT3_CODE COUNTY_CODE,
GEOGRAPHY_ELEMENT4_CODE CITY_CODE
FROM ZX_JURISDICTIONS_B ZJ,
HZ_GEOGRAPHIES HG
WHERE
ZJ.TAX_REGIME_CODE='US_SALE_AND_USE_TAX'
AND SYSDATE BETWEEN ZJ.EFFECTIVE_FROM AND NVL(ZJ.EFFECTIVE_TO,'31-DEC-4999')
AND SYSDATE BETWEEN HG.START_DATE AND HG.END_DATE
AND ZJ.ZONE_GEOGRAPHY_ID=HG.GEOGRAPHY_ID
AND ZJ.TAX=HG.GEOGRAPHY_TYPE
AND NOT EXISTS (SELECT 1 FROM ZX_RATES_B ZR
WHERE
ZR.TAX_REGIME_CODE='US_SALE_AND_USE_TAX'
AND ZR.TAX_JURISDICTION_CODE=ZJ.TAX_JURISDICTION_CODE)
ORDER BY TAX,
TAX_JURISDICTION_CODE,
GEOGRAPHY_ELEMENT2_CODE ,
GEOGRAPHY_ELEMENT3_CODE,
GEOGRAPHY_ELEMENT4_CODE
========================================================================
LIST OF GEOGRAPHY'S WITHOUT JURISDICTIONS
SELECT * FROM
(SELECT GEOGRAPHY_TYPE,
GEOGRAPHY_ELEMENT2_CODE STATE_CODE,
GEOGRAPHY_ELEMENT3_CODE COUNTY_CODE,
GEOGRAPHY_ELEMENT4_CODE CITY_CODE
FROM
HZ_GEOGRAPHIES HG
WHERE HG.GEOGRAPHY_TYPE='STATE'
AND SYSDATE BETWEEN HG.START_DATE AND HG.END_DATE
AND GEOGRAPHY_ELEMENT1_CODE='US'
AND NOT EXISTS (SELECT 1 FROM ZX_JURISDICTIONS_B ZJ
WHERE ZJ.ZONE_GEOGRAPHY_ID=HG.GEOGRAPHY_ID
AND ZJ.TAX_REGIME_CODE='US_SALE_AND_USE_TAX'
AND SYSDATE BETWEEN ZJ.EFFECTIVE_FROM AND NVL(ZJ.EFFECTIVE_TO,'31-DEC-4999')
AND ZJ.TAX=HG.GEOGRAPHY_TYPE)
UNION
SELECT GEOGRAPHY_TYPE,
GEOGRAPHY_ELEMENT2_CODE STATE_CODE,
GEOGRAPHY_ELEMENT3_CODE COUNTY_CODE,
GEOGRAPHY_ELEMENT4_CODE CITY_CODE
FROM
HZ_GEOGRAPHIES HG
WHERE HG.GEOGRAPHY_TYPE='COUNTY'
AND SYSDATE BETWEEN HG.START_DATE AND HG.END_DATE
AND GEOGRAPHY_ELEMENT1_CODE='US'
AND NOT EXISTS (SELECT 1 FROM ZX_JURISDICTIONS_B ZJ
WHERE ZJ.ZONE_GEOGRAPHY_ID=HG.GEOGRAPHY_ID
AND ZJ.TAX_REGIME_CODE='US_SALE_AND_USE_TAX'
AND SYSDATE BETWEEN ZJ.EFFECTIVE_FROM AND NVL(ZJ.EFFECTIVE_TO,'31-DEC-4999')
AND ZJ.TAX=HG.GEOGRAPHY_TYPE)
UNION
SELECT GEOGRAPHY_TYPE,
GEOGRAPHY_ELEMENT2_CODE STATE_CODE,
GEOGRAPHY_ELEMENT3_CODE COUNTY_CODE,
GEOGRAPHY_ELEMENT4_CODE CITY_CODE
FROM
HZ_GEOGRAPHIES HG
WHERE HG.GEOGRAPHY_TYPE='CITY'
AND SYSDATE BETWEEN HG.START_DATE AND HG.END_DATE
AND GEOGRAPHY_ELEMENT1_CODE='US'
AND NOT EXISTS (SELECT 1 FROM ZX_JURISDICTIONS_B ZJ
WHERE ZJ.ZONE_GEOGRAPHY_ID=HG.GEOGRAPHY_ID
AND ZJ.TAX_REGIME_CODE='_US_SALE_AND_USE_TAX'
AND SYSDATE BETWEEN ZJ.EFFECTIVE_FROM AND NVL(ZJ.EFFECTIVE_TO,'31-DEC-4999')
AND ZJ.TAX=HG.GEOGRAPHY_TYPE))
ORDER BY GEOGRAPHY_TYPE,STATE_CODE,
COUNTY_CODE,
CITY_CODE
===================================================================
TAX RULES AND CONDITIONS
SELECT tax_regime_code
,tax
,DECODE(
rul.service_type_code
,'DET_TAX_STATUS'
,'Determine Tax Status'
,'DET_RECOVERY_RATE'
,'Determine Tax Rate'
,'DET_APPLICABLE_TAXES'
,'Determine Applicability'
,'DET_PLACE_OF_SUPPLY'
,'Determine Place of supply'
,'DET_TAX_RATE'
,'Determine Tax Rate'
)
rule
,rul.priority
,det_factor_templ_code factor_set
,res.priority
,condition_group_code
,alphanumeric_result
,NVL(ou.name, 'Global Configuration Owner') owner
FROM zx.zx_rules_b rul
,zx.zx_process_results res
,zx.zx_party_tax_profile pp
,hr_operating_units ou
WHERE rul.tax_rule_id = res.tax_rule_id
AND rul.content_owner_id = pp.party_tax_profile_id
AND pp.party_id = ou.organization_id(+)
ORDER BY rul.tax_regime_code
,rul.tax
,rul.service_type_code
,rul.priority
,res.priority
===================================================================
SUPPLIER TAX REGISTRATION CREATION
Use the below script to create Tax Registrations for suppliers - if you have defined any tax rule based on Tax Registrations
DECLARE X_RETURN_STATUS VARCHAR2(1);
BEGIN
ZX_REGISTRATIONS_PKG.INSERT_ROW ( P_REQUEST_ID => NULL
,P_ATTRIBUTE1 => NULL
,P_ATTRIBUTE2 => NULL
,P_ATTRIBUTE3 => NULL
,P_ATTRIBUTE4 => NULL
,P_ATTRIBUTE5 => NULL
,P_ATTRIBUTE6 => NULL
,P_VALIDATION_RULE => NULL
,P_ROUNDING_RULE_CODE => 'UP'
,P_TAX_JURISDICTION_CODE => NULL
,P_SELF_ASSESS_FLAG => 'Y'
,P_REGISTRATION_STATUS_CODE => 'REGISTERED'
,P_REGISTRATION_SOURCE_CODE => 'IMPLICIT'
,P_REGISTRATION_REASON_CODE => NULL
,P_TAX => NULL
,P_TAX_REGIME_CODE => 'DAR'
,P_INCLUSIVE_TAX_FLAG => 'N'
,P_EFFECTIVE_FROM => TO_DATE('01-DEC-2007','DD-MON-YYYY')
,P_EFFECTIVE_TO => NULL
,P_REP_PARTY_TAX_NAME => NULL
,P_DEFAULT_REGISTRATION_FLAG => 'N'
,P_BANK_ACCOUNT_NUM => NULL
,P_RECORD_TYPE_CODE => NULL
,P_LEGAL_LOCATION_ID => NULL
,P_TAX_AUTHORITY_ID => NULL
,P_REP_TAX_AUTHORITY_ID => NULL
,P_COLL_TAX_AUTHORITY_ID => NULL
,P_REGISTRATION_TYPE_CODE => NULL
,P_REGISTRATION_NUMBER => NULL
,P_PARTY_TAX_PROFILE_ID => 812988
,P_LEGAL_REGISTRATION_ID => NULL
,P_BANK_ID => NULL
,P_BANK_BRANCH_ID => NULL
,P_ACCOUNT_SITE_ID => NULL
,P_ATTRIBUTE14 => NULL
,P_ATTRIBUTE15 => NULL
,P_ATTRIBUTE_CATEGORY => NULL
,P_PROGRAM_LOGIN_ID => NULL
,P_ACCOUNT_ID => NULL
,P_TAX_CLASSIFICATION_CODE => NULL
,P_ATTRIBUTE7 => NULL
,P_ATTRIBUTE8 => NULL
,P_ATTRIBUTE9 => NULL
,P_ATTRIBUTE10 => NULL
,P_ATTRIBUTE11 => NULL
,P_ATTRIBUTE12 => NULL
,P_ATTRIBUTE13 => NULL
,X_RETURN_STATUS => X_RETURN_STATUS
);
DBMS_OUTPUT.PUT_LINE('RETURN STATUS :' ||X_RETURN_STATUS);
COMMIT;
END;
================================================================
EXCLUDE FREIGHT FROM DISCOUNT
SELECT APS.VENDOR_NAME,
APS.EXCLUDE_FREIGHT_FROM_DISCOUNT VEND_EXCD,
APSS.VENDOR_SITE_CODE,
APSS.EXCLUDE_FREIGHT_FROM_DISCOUNT SITE_EXCD
FROM APPS.AP_SUPPLIERS APS,
APPS.AP_SUPPLIER_SITES_ALL APSS
WHERE APS.VENDOR_ID = APSS.VENDOR_ID
AND APS.VENDOR_ID NOT IN (1, 2, 3)
AND APSS.EXCLUDE_FREIGHT_FROM_DISCOUNT IS NULL
AND APS.EXCLUDE_FREIGHT_FROM_DISCOUNT IS NULL
=================================================================
Tax rates and the accounts associated to them
SELECT rates.tax_regime_code regime
,rates.tax tax
,rates.tax_status_code status
,rates.tax_rate_code tax_rate
,rates.percentage_rate rate
,rates.default_rec_rate_code rec_rate
,rates.offset_tax_rate_code offset_rate
,ou.name org
,rate_acc.concatenated_segments ar_acc
,rec_acc.concatenated_segments ap_acc
FROM zx.zx_rates_b rates
,zx.zx_taxes_b tax
,zx.zx_accounts rate_zx_acc
,gl_code_combinations_kfv rate_acc
,zx.zx_rates_b rec
,zx.zx_accounts rec_zx_acc
,gl_code_combinations_kfv rec_acc
,hr_operating_units ou
WHERE 1 = 1
AND rates.tax = tax.tax
AND rates.default_rec_rate_code = rec.tax_rate_code
AND rates.rate_type_code = 'PERCENTAGE'
AND tax.tax_type_code <> 'OFFSET'
AND(rates.effective_to IS NULL
OR rates.effective_to >= TRUNC(SYSDATE))
AND rates.active_flag = 'Y'
AND rate_zx_acc.tax_account_entity_code(+) = 'RATES'
AND rate_zx_acc.tax_account_entity_id(+) = rates.tax_rate_id
AND rate_zx_acc.tax_account_ccid = rate_acc.code_combination_id(+)
AND rec_zx_acc.tax_account_entity_code = 'RATES'
AND rec_zx_acc.tax_account_entity_id = rec.tax_rate_id
AND rec_zx_acc.tax_account_ccid = rec_acc.code_combination_id
AND rate_zx_acc.internal_organization_id = ou.organization_id (+)
AND rec_zx_acc.internal_organization_id = NVL(rate_zx_acc.internal_organization_id, rec_zx_acc.internal_organization_id)
AND rates.tax <> 'DUMMY TAX'
UNION
SELECT rates.tax_regime_code regime
,rates.tax tax
,rates.tax_status_code status
,rates.tax_rate_code tax_rate
,rates.percentage_rate rate
,rates.default_rec_rate_code rec_rate
,rates.offset_tax_rate_code offset_rate
,ou.name org
,rate_acc.concatenated_segments ar_acc
,NULL ap_acc
FROM zx.zx_rates_b rates
,zx.zx_taxes_b tax
,zx.zx_accounts rate_zx_acc
,gl_code_combinations_kfv rate_acc
,hr_operating_units ou
WHERE 1 = 1
AND rates.tax = tax.tax
AND rates.rate_type_code = 'PERCENTAGE'
AND tax.tax_type_code <> 'OFFSET'
AND(rates.effective_to IS NULL
OR rates.effective_to >= TRUNC(SYSDATE))
AND rates.active_flag = 'Y'
AND rate_zx_acc.tax_account_entity_code(+) = 'RATES'
AND rate_zx_acc.tax_account_entity_id(+) = rates.tax_rate_id
AND rate_zx_acc.tax_account_ccid = rate_acc.code_combination_id(+)
AND rate_zx_acc.internal_organization_id = ou.organization_id (+)
AND rates.default_rec_rate_code IS NULL
AND rates.tax <> 'DUMMY TAX'
ORDER BY regime
,tax
,status
,tax_rate
begin
if fnd_user_pkg.changepassword('sgooda','password' ) then
null;
end if;
end;
BEGIN
FND_USER_PKG.ADDRESP(USERNAME => 'SGOODA'
,RESP_APP => 'SYSADMIN'
,RESP_KEY => 'SYSTEM_ADMINISTRATOR'
,SECURITY_GROUP => 'STANDARD'
,DESCRIPTION => 'Auto Assignment'
,START_DATE => SYSDATE - 1
,END_DATE => NULL);
END;
SELECT A.APP_SHORT_NAME, A.SUBDIR, A.FILENAME, B.VERSION
FROM APPS.AD_FILES A,
APPS.AD_FILE_VERSIONS B
WHERE A.FILENAME LIKE '%apetxutb.pls%' ----'%<file name>%'
AND A.FILE_ID = B.FILE_ID
AND A.APP_SHORT_NAME = 'SQLAP' --- e.g. : ASF, AMS....
AND B.CREATION_DATE = (SELECT MAX (CREATION_DATE)
FROM APPS.AD_FILE_VERSIONS VER
WHERE VER.FILE_ID = A.FILE_ID)
DECLARE
V_FROM VARCHAR2(80) := 'oracle@mycompany.com';
V_RECIPIENT VARCHAR2(80) := 'sgooda@wescodist.com';
V_SUBJECT VARCHAR2(80) := 'test subject';
V_MAIL_HOST VARCHAR2(30) := 'mail.mycompany.com';
V_MAIL_CONN UTL_SMTP.CONNECTION;
CRLF VARCHAR2(2) := CHR(13)||CHR(10);
BEGIN
V_MAIL_CONN := UTL_SMTP.OPEN_CONNECTION(V_MAIL_HOST, 25);
UTL_SMTP.HELO(V_MAIL_CONN, V_MAIL_HOST);
UTL_SMTP.MAIL(V_MAIL_CONN, V_FROM);
UTL_SMTP.RCPT(V_MAIL_CONN, V_RECIPIENT);
UTL_SMTP.DATA(V_MAIL_CONN,
'Date: ' || TO_CHAR(SYSDATE, 'Dy, DD Mon YYYY hh24:mi:ss') || CRLF ||
'From: ' || V_FROM || CRLF ||
'Subject: '|| V_SUBJECT || CRLF ||
'To: ' || V_RECIPIENT || CRLF ||
CRLF ||
'some message text'|| CRLF || -- Message body
'more message text'|| CRLF
);
UTL_SMTP.QUIT(V_MAIL_CONN);
EXCEPTION
WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN
RAISE_APPLICATION_ERROR(-20000, 'Unable to send mail: '||SQLERRM);
END;
SELECT U_DUMP.VALUE || '/' || INSTANCE.VALUE || '_ora_' ||
V$PROCESS.SPID|| NVL2(V$PROCESS.TRACEID, '_' ||
$PROCESS.TRACEID, NULL ) || '.trc'"Trace File"
FROM V$PARAMETER U_DUMP
CROSS JOIN V$PARAMETER INSTANCE
CROSS JOIN V$PROCESS
JOIN V$SESSION ON V$PROCESS.ADDR = V$SESSION.PADDR
WHERE U_DUMP.NAME = 'user_dump_dest'
AND INSTANCE.NAME = 'instance_name'
AND V$SESSION.AUDSID=SYS_CONTEXT('userenv','sessionid');
Instance Clone Script
SELECT TO_CHAR (resetlogs_time, 'YYYY-MM-DD, HH24:MI:SS') FROM V$DATABASE;
SELECT outfile_name,
requested_by,
progs.user_concurrent_program_name,
reqs.request_id,
reqs.parent_request_id,
responsibility_id,
completion_text,
argument_text,
24 * (actual_completion_date - actual_start_date) * 60
run_time_in_minutes,
requested_by,
pp_start_date,
pp_end_date,
actual_start_date,
actual_completion_date,
reqs.*
FROM apps.fnd_concurrent_requests reqs,
apps.fnd_concurrent_programs_tl progs
WHERE reqs.request_id = 6712666 ----Request Id---
AND progs.concurrent_program_id = reqs.concurrent_program_id
AND progs.language = 'US'
ORDER BY reqs.request_id DESC;
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 APPS.FND_PROFILE_OPTIONS_VL PO,
APPS.FND_PROFILE_OPTION_VALUES POV,
APPS.FND_USER USR,
APPS.FND_APPLICATION APP,
APPS.FND_RESPONSIBILITY RSP,
APPS.FND_NODES SVR,
APPS.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
AND PO.USER_PROFILE_OPTION_NAME =
'AR: Activate Multi Fund Accounting Extract'
ORDER BY 1, POV.LEVEL_ID, 5
Between
AND TRUNC (ACA.CHECK_DATE) BETWEEN TO_DATE ('06-OCT-2015',
'DD-MON-YYYY')
AND TO_DATE ('13-OCT-2015',
AP_HOLD_WORKFLOW_STATUS_QUERY
SELECT aia.invoice_num invoice_number,
aila.line_number invoice_line_number,
flvvilt.meaning invoice_line_type,
aha.hold_lookup_code hold_type,
wiv.item_type wf_item_type,
wiv.item_key wf_item_key,
aha.wf_status wf_status,
aha.ATTRIBUTE11 wf_approver_note,
aha.ATTRIBUTE12 wf_note,
wiv.begin_date wf_begin_date,
wiv.end_date wf_end_date
FROM apps.ap_holds_all aha,
apps.ap_invoices_all aia,
apps.ap_invoice_lines_all aila,
apps.fnd_lookup_values_vl flvvilt,
apps.wf_items_v wiv
WHERE 1 = 1
AND aia.invoice_num = :p_in_invoice_number
AND aha.invoice_id = aia.invoice_id
AND aila.invoice_id = aia.invoice_id
AND aila.po_line_location_id = aha.line_location_id
AND NVL (aila.discarded_flag, 'N') = 'N'
AND flvvilt.lookup_type = 'INVOICE LINE TYPE'
AND flvvilt.lookup_code = aila.line_type_lookup_code
AND wiv.item_type = 'APINVHDN'
AND wiv.item_key = aha.hold_id
ORDER BY aia.invoice_num, aila.line_number;
AP Invoices and Payment Details with Hold, Internal Bank Information_By Invoice GL Date
SELECT DECODE (aia.org_id, 121, 'WES US OU', 122, 'WES CA OU')
"OU_NAME",
aia.attribute2
"BRANCH#",
aia.attribute3
"WESNET_VENDOR#",
aps.segment1
"ORAC_VEND_NUM",
aps.VENDOR_NAME,
assa.VENDOR_SITE_CODE,
aia.INVOICE_NUM,
aia.INVOICE_DATE,
aia.GL_DATE,
aia.INVOICE_AMOUNT,
aia.AMOUNT_PAID,
aia.INVOICE_CURRENCY_CODE,
aia.PAYMENT_CURRENCY_CODE,
aia.amount_applicable_to_discount
"DISCOUNT_ELIGIBLE",
aia.DISCOUNT_AMOUNT_TAKEN,
at.name
"PAYMENT_TERM",
aca.CHECK_NUMBER,
ipa.CREATION_DATE
"PAYMENT_DATE",
apsa.DUE_DATE,
aia.PAY_GROUP_LOOKUP_CODE,
apsa.PAYMENT_METHOD_CODE,
aia.PAYMENT_STATUS_FLAG
"PAYMENT_STATUS",
aia.description
"INVOICE_DESCRIPTION",
assa.ADDRESS_LINE1,
assa.ADDRESS_LINE2,
assa.ADDRESS_LINE3,
assa.CITY,
assa.STATE,
assa.ZIP,
assa.PROVINCE,
assa.COUNTRY,
aia.DOC_CATEGORY_CODE,
aia.SOURCE,
aida.DISTRIBUTION_LINE_NUMBER,
aida.LINE_TYPE_LOOKUP_CODE,
aida.ACCOUNTING_DATE,
gcc.segment1
"LE",
gcc.segment2
"BRANCH",
gcc.segment3
"ACCOUNT",
gcc.segment4
"IC",
gcc.segment5
"F1",
gcc.segment6
"F2",
gcc.segment7
"F3",
aida.PERIOD_NAME,
aida.AMOUNT
"DIST_LINE_AMOUNT",
aida.DESCRIPTION
"DIST_LINE_DESCRIPTION",
ah.HOLD_LOOKUP_CODE,
iBpa.payee_name
"PAID_TO_NAME",
ibpa.int_bank_name
"PAYEE_BANK_NAME",
--ipa.int_bank_number "Payee Bank Number",
ibpa.int_bank_account_name
"PAYEE_BANK_ACT_NAME",
ibpa.int_bank_account_number
"PAYEE_BANK_ACT_NUM"
FROM apps.ap_invoices_all aia,
apps.ap_suppliers aps,
apps.ap_supplier_sites_all assa,
apps.ap_payment_schedules_all apsa,
apps.ap_checks_all aca,
apps.ap_terms at,
apps.ap_invoice_payments_all ipa,
apps.ap_invoice_distributions_all aida,
apps.gl_code_combinations gcc,
apps.ap_holds ah,
apps.iby_payments_all ibpa,
apps.iby_docs_payable_all idpa
WHERE 1 = 1
AND aia.vendor_id = aps.vendor_id
AND aia.vendor_site_id = assa.vendor_site_id
AND aia.vendor_id = assa.vendor_id
AND aps.vendor_id = assa.vendor_id
AND apsa.invoice_id(+) = aia.invoice_id
AND aia.terms_id = at.term_id
AND ipa.invoice_id(+) = aia.invoice_id
AND aca.check_id(+) = ipa.check_id
AND aida.invoice_id = aia.invoice_id
AND aida.dist_code_combination_id = gcc.code_combination_id
AND ah.invoice_id(+) = aia.invoice_id
AND ibpa.payee_supplier_number(+) = aps.segment1
AND ibpa.party_site_id(+) = assa.party_site_id
AND ibpa.supplier_site_id(+) = assa.vendor_site_id
AND ibpa.payment_id(+) = idpa.payment_id
AND idpa.calling_app_doc_unique_ref2(+) = aia.invoice_id
AND idpa.payee_party_id(+) = aps.party_id
AND ( aia.attribute2 IN ('002591', ------ attribute2 is Branch DFF
'002594',
'002590',
'002580',
'002592',
'002595')
OR aia.attribute2 IS NULL)
AND TRUNC (aia.gl_date) BETWEEN TO_DATE ('01-JAN-2021', 'DD-MON-YYYY')
AND TO_DATE ('31-JAN-2021', 'DD-MON-YYYY')
AND aia.INVOICE_AMOUNT <> '0'
--AND aca.CHECK_NUMBER IS NULL
ORDER BY 1,
2,
3,
4,
5,
6;
Open AP with Discount SQL (JFLORE)
SELECT pv.segment1 "VENDOR_NUM",
pv.vendor_name VENDOR_NAME,
pvsa.vendor_site_code "VENDOR_SITE_CODE",
aia.payment_status_flag,
DECODE(aia.payment_status_flag, 'N', 'UN-PAID', 'PAID') payment_status,
aia.invoice_type_lookup_code,
aia.invoice_num "INVOICE_NUM",
aia.invoice_date "INVOICE_DATE",
aia.gl_date "INVOICE_GL_DATE",
apsa.DUE_DATE,
apsa.DISCOUNT_DATE,
aia.terms_date,
aia.invoice_amount,
aia.amount_paid,
(SELECT sum(discount_taken) FROM ap_invoice_payments_all WHERE invoice_id = aia.invoice_id) DISCOUNT_TAKEN,
(SELECT sum(discount_lost) FROM ap_invoice_payments_all WHERE invoice_id = aia.invoice_id) DISCOUNT_LOST,
at.name "PAY_TERM_NAME",
apsa.DISCOUNT_AMOUNT_AVAILABLE,
apsa.DISCOUNT_AMOUNT_REMAINING,
pvsa.ALWAYS_TAKE_DISC_FLAG,
aia.description "INVOICE_DESCRIPTION",
aia.source,
aia.org_id,
decode(aia.org_id, 121, 'USD WESCO OU',
122, 'CAD WESCO OU',
146, 'M AND M WESCO OU') OPERATING_UNIT
-- aia.vendor_id ""VENDOR_ID"",
FROM apps.ap_invoices_all aia,
apps.po_vendors pv,
apps.po_vendor_sites_all pvsa,
apps.ap_payment_schedules_all apsa,
apps.ap_terms at
WHERE pv.vendor_id = aia.vendor_id
AND pvsa.vendor_site_id = aia.vendor_site_id
AND aia.invoice_amount != 0
AND aia.payment_status_flag = 'N'
AND aia.amount_paid <> aia.invoice_amount
AND apsa.invoice_id = aia.invoice_id
AND at.term_id = aia.terms_id
AND aia.org_id <> 146
-- and (aia.gl_date BETWEEN TO_DATE ('01-JAN-1951', 'DD-MON-YYYY')
-- AND TO_DATE ('30-NOV-2021', 'DD-MON-YYYY'))
--ORDER BY 2, 3, 8
ORDER BY 8