Details for some of Oracle Workflow APIs are...
1 WF_ENGINE
2 WF_CORE
3 WF_PURGE
4 WF_DIRECTORY
5 WF_PREF
6 WF_MONITOR
7 Oracle Workflow Views
8 WF_QUEUE
9 FND_DOCUMENT_MANAGEMENT
10 WF_NOTIFICATI...
Query : To get the Statement of expenses incurred in particular gl entry account
select gl.chart_of_accounts_id,
gl.account_type,
gl.segment12 account_dep,
gl.segment14 company,
gl.segment16 department,
bl.currency_code,
bl.period_net_dr ,
bl.period_net_cr ,
substr(a.description,1,50) descr,
he.period_name
from gl_code_combinations gl ,
gl_balances bl,
fnd_flex_value_sets c ,
fnd_flex_values b ,
fnd_flex_values_tl a,
gl_je_headers he
where gl.code_combination_id =bl.code_combination_id
and gl.chart_of_accounts_id=xxx -- add coaid
and b.flex_value_set_id...
Query : INTRANSIT REPORT
select t.invoiced_flag,
t.actual_cost ,
t.transaction_date ,
t.transaction_quantity,
t.inventory_item_id ,
t.subinventory_code
from mtl_material_transactions t,
mtl_onhand_quantities o
where t.subinventory_code = 'INTRANSIT'
and t.inventory_item_id not in o.inventory_item...
INFO: AIM Documents for Techies & Techno Functional
MD050 - Module Design - By Functional ConsultantsMD070 - Technical Document Design - By Technical ConsultantsMD020 - Testing Document Design - By Functional ConsultantsMD0120 - Migration/ User Training - By Technical ConsultantsCV040 - Conversion of Functional Document - By Functional ConsultantsCV060 - Conversion of Technical Document - By Technical Consulta...
Query : Asset migration
SELECTFA.ASSET_NUMBER,FA.TAG_NUMBER,FA.DESCRIPTION,FA.SERIAL_NUMBER,FA.MODEL_NUMBER,FB.BOOK_TYPE_CODE,FB.DATE_PLACED_IN_SERVICE,
FAI.FIXED_ASSETS_COST,
FA.CURRENT_UNITS,
FA.CURRENT_UNITS,
FAI.PAYABLES_CODE_COMBINATION_ID
FDH.LOCATION_ID,
FDH.ASSIGNED_TO,
'MIGRATION',
FAI.INVOICE_NUMBER,
FAI.PO_VENDOR_ID,
FAI.PO_NUMBER,
FA.DEPRECIATE_FLAG,
FA.ASSET_TYPE,
FDS.DEPRN_RESERVE,
FDS.YTD_DEPRN,
FB.SALVAGE_VALUE
FROM
FA_ADDITIONS FA, FA_BOOKS FB, FA_ASSET_INVOICES...
Query :
select SUM( NVL(ds.deprn_reserve,0) * ds.UNITS_ASSIGNED/ad.CURRENT_UNITS) as deprn_reserve
From (select a.asset_id,
a.tag_number,
a.current_units+nvl(b.units,0) as current_units,
a.asset_category_id
from apps.fa_additions a,
(select asset_id,sum(abs(transaction_units)) as units
from fa.fa_distribution_history
where asset_id in (select...
Query : To get Prepayment Invoice Status
SELECT pv.VENDOR_NAME,
ai.invoice_num,
NVL (
DECODE (
SIGN (SUM (amount - NVL (prepay_amount_remaining, amount))),
1,
DECODE (SUM (prepay_amount_remaining), 0, 'Y', NULL),
NULL
),
'N'
)
AS PP_F -- Y is Fully Applied, N is Partially or Not Applied
FROM ap_invoice_distributions_all aid, ap_invoices_all ai, po_vendors pv
WHERE aid.invoice_id = ai.INVOICE_ID
AND...
INFO: Accounting lines in FA via SLA
Accounting lines are now stored in the SLA Tables:
xla_events
xla_ae_headers
xla_ae_lines
xla_distribution_links
by linking to the event id in the FA_Transaction_Headers and
FA_Deprn_Summary tables. CCID no longer stored in the
FA_Adjustments tab...
Query : To get YTD Depreciation
SELECT fbv.book_type_code
,fab.asset_number,fab.asset_id
,fab.tag_number
,fbv.date_placed_in_service
,fab.description
,fcb.segment1||'.'||fcb.segment2 asset_category
,fcb.segment1||'.'||fcb.segment2 asset_category1
,fbv.cost asset_cost
,fbv.original_cost original_asset_cost
,fbv.life_in_months asset_life
,mvl_discoverer_fin_support.get_depreciation_dtls(fab.asset_id
,fbv.book_type_code
,fdh.distribution_id
,fdp.period_counter
,'ACCUM') accum_depreciation
,mvl_discoverer_fin_support.get_depreciation_dtls(fab.asset_id
,fbv.book_type_code
,fdh.distribution_id
,fdp.period_counter
,'YTD')...
Query : Get the Inventory org Address
select * from hr_locations_v hlv,
hr_all_organization_units hou
WHERE hlv.inventory_organization_id = hou.organization_id
AND hlv.location_id = hou.location_id
AND hou.organization_id = :p_mfg_o...
Query : To get the list of Custom Alerts
SELECT aa.alert_name,
aa.creation_date,
aa.last_update_date,
aa.alert_condition_type,
aa.enabled_flag,
aa.start_date_active,
aa.end_date_active,
aa.description,
aa.date_last_checked,
aa.maintain_history_days,
aa.table_name,
aa.sql_statement_text,
fa.application_name
FROM alr_alerts aa, fnd_application_tl fa
WHERE aa.alert_name LIKE 'XXA%'
AND aa.application_id = fa.application...
Query : Retrieve Incomplete Invoices before month end
SELECT /*+RULE */
hca.account_number acct_nbr
, ctx.trx_number trx_nbr
, TO_CHAR(ctx.trx_date,’DD-MM-YYYY’) trx_date
, ctt.NAME tran_type
, ctx.customer_trx_id
, ctx.bill_to_customer_id
, ctx.cust_trx_type_id
FROM apps.ra_cust_trx_types_all ctt
, apps.hz_cust_accounts hca
, apps.ra_customer_trx_all ctx
WHERE NVL(ctx.complete_flag,’N') = ‘N’
AND ctx.bill_to_customer_id = hca.cust_account_id
AND ctx.cust_trx_type_id...
Query : To retrieve transactions with a certain amount open (dunning charges amount)
SELECT pse.customer_id, hza.account_number, hza.account_name,
pse.trx_number, pse.trx_date, pse.amount_due_original,
pse.amount_due_remaining
FROM apps.ar_payment_schedules_all pse, apps.hz_cust_accounts hza
WHERE 1 = 1
AND pse.customer_id = hza.cust_account_id
to_char(xmv.customer_id) als type
and hza.CUST_ACCOUNT_ID > -1
AND pse.status = 'OP'
AND pse.CLASS IN ('INV', 'CM')
HAVING SUM (pse.amount_due_remaining) =4.50 --- this must be your condition
GROUP BY pse.customer_id,
hza.account_number,
hza.account_name,
pse.trx_number,
pse.trx_dat...
Query : To validate whether a transaction's REC is equal to its REV plus TAX or not
SELECT c.trx_number, c.complete_flag, c.customer_trx_id,
a.customer_trx_line_id,
(SELECT payment_schedule_id
FROM ar.ar_payment_schedules_all d
WHERE a.customer_trx_id = d.customer_trx_id) payment_schedule_id,
(SELECT receivable_application_id
FROM ar.ar_receivable_applications_all e
WHERE a.customer_trx_id =
e.customer_trx_id)
receivable_application_id
FROM ar.ra_customer_trx_lines_all a,
ar.ra_customer_trx_lines_all...
How : Amortized and Expensed Adjustments
Amortized and Expensed Adjustments
In the period you add an asset or for CIP assets, changing financial information does not adjust depreciation, since no depreciation has been taken. If you change financial information after you have run depreciation, you must choose whether to expense or amortize the adjustment:
Expensed Adjustment
For expensed adjustments, Oracle Assets recalculates depreciation using the new information and expenses the entire adjustment amount in the current period. Expensing the adjustment results in a one–time adjusting...
Query : To query Receipt Accounting entries -II
SELECT acr.receipt_number, ada.amount_dr, ada.amount_cr,
ada.acctd_amount_dr,ada.acctd_amount_cr
FROM ar.ar_distributions_all ada,
ar.ar_misc_cash_distributions_all mcd,
ar.ar_cash_receipts_all acr
WHERE ada.source_table = 'MCD'
AND ada.source_id = mcd.misc_cash_distribution_id
AND mcd.cash_receipt_id = acr.cash_receipt_id
AND mcd.set_of_books_id = '&s...
Query : To query Receipt Accounting entries -I
SELECT amount_dr, amount_cr, acctd_amount_dr, acctd_amount_cr,
gcc.segment1
|| '.'
|| gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5 ACCOUNT
FROM ar.ar_distributions_all ad, gl.gl_code_combinations gcc
WHERE source_table = 'CRH'
AND EXISTS (
SELECT 'T'
FROM ar.ar_cash_receipt_history_all a, ar.ar_cash_receipts_all b
WHERE a.cash_receipt_id = b.cash_receipt_id
AND source_id = cash_receipt_history_id
...
Query : To Check whether Periods of AP/AR/GL/FA/PO is closed?
SELECT (SELECT sob.NAME
FROM gl.gl_sets_of_books sob
WHERE sob.set_of_books_id = a.set_of_books_id) "SOB_Name",
a.period_name "Period_Name", a.period_num "Period_Num",
a.gl_status "GL_Status", b.po_status "PO_Status",
c.ap_status "AP_Status", d.ar_status "AR_Status",
e.fa_status "FA_Status"
FROM (SELECT period_name, period_num,
DECODE (closing_status,
'O', 'Open',
'C', 'Closed',
'F', 'Future',
...
Query : Verify that Multi Org is Installed
select multi_org_flag,release_name,
applications_system_name
from apps.fnd_product_groups;
If the value returned is 'Y' then a Multi Org environment exists;
If the value returned is 'N' then a Multi Org environment does not exi...
Query : Sales Order Hold & Release
SELECT OHA.ORDER_NUMBER,
HS.HOLD_ENTITY_CODE CREDIT_CHECK_CRITERIA_CODE,
FLV.MEANING CREDIT_CHECK_CRITERIA,
HD.NAME HOLD_NAME,
HD.TYPE_CODE HOLD_TYPE,
HR.RELEASE_REASON_CODE,
FLV1.MEANING RELEASE_REASON,
HS.RELEASED_FLAG,
HS.HOLD_ENTITY_ID,
OH.HOLD_RELEASE_ID,
OH.HEADER_ID,
HS.HOLD_SOURCE_ID
FROM ONT.OE_HOLD_SOURCES_ALL HS,
ONT.OE_HOLD_DEFINITIONS HD,
ONT.OE_ORDER_HOLDS_ALL OH,
ONT.OE_ORDER_HEADERS_ALL OHA,
APPS.FND_LOOKUP_VALUES FLV,
...
Query : Get the Account Description
SELECT apps.gl_flexfields_pkg.get_description_sql(&char_of_account_id,
1,
gcc.segment1) seg1,
gl_flexfields_pkg.get_description_sql(&char_of_account_id,
2,
gcc.segment2) seg2,
gl_flexfields_pkg.get_description_sql(&char_of_account_id,
3,
gcc.segment3)...
Query : Get Inventory Accounting Entries linked to GL
select mta.transaction_id,
mmt.organization_id,
msi.segment1,
mta.transaction_date,
mta.primary_quantity,
gcc.segment1 || '.' || gcc.segment2 || '.' || gcc.segment3 || '.' ||
gcc.segment4 || '.' || gcc.segment5 account,
DECODE(SIGN(mta.transaction_value),
1,
mta.transaction_value,
0,
0,
NULL,
DECODE(SIGN(mta.base_transaction_value),
1,
mta.base_transaction_value,
...
How : General Ledger and Accounts Payabels connected via SLA module
All SLA transactions queries is linked using XLA_TRANSACTION_ENTITIES table.
TRANSACTIONS, RECEIPTS, ADJUSTMENTS, PURCHASE_ORDER, AP_INVOICES,AP_PAYMENTS. This demonstrates this is a common table for linking Subledger Transaction such as AR Transactions through to their accounting entries.
All tables used in this activity
xla.xla_transaction_entities , xla.xla_events , xla_ae_headers , xla_ae_lines , gl_import_referen...
Query : AP to GL data transfer
SELECT glcc.segment1 "Company"
, gjjlv.period_name "PERIOD"
, gjb.name "JOURNAL BATCH NAME"
, gjjlv.header_name "JOURNAL SOURCE"
, gjjlv.line_reference_1 "SUPPLIER NAME"
, gjjlv.currency_code "CURRENCY"
, invoice_type_lookup_code "TRANSACTION TYPE"
, gjjlv.line_reference_5 "TRANSACTION NUMBER"
, aia.invoice_date "TRANSACTION DATE"
-- , gjjlv.je_source "SOURCE"
, gjjlv.line_entered_dr "ENTERED DEBIT"
, gjjlv.line_entered_cr "ENTERED CREDIT"
, gjjlv.line_accounted_dr "ACCOUNTED_DEBIT"
, gjjlv.line_accounted_cr "ACCOUNTED_CREDIT"
,glcc.concatenated_segments...
Query : Subledger Transfer to GL
This query gives details of different journals transferred to GL
SELECT gjjlv.period_name period_name
, gjb.name batch_name
, gjjlv.header_name Journal_Entry
, gjjlv.je_source Source
, gjjlv.line_entered_dr Entered_Debit
, gjjlv.line_entered_cr Entered_credit
, gjjlv.line_accounted_dr Accounted_Debit
, gjjlv.line_accounted_cr Accounted_Credit
, gjjlv.currency_code Currency
, fasv.TRX_TYPE_NAME Trans_Type
, fasv.TRX_NUMBER_DISPLAYED Transaction_Number
, fasv.TRX_DATE Transaction_Date
, fasv.ASSET_NUMBER Reference
, glcc.CONCATENATED_SEGMENTS
,gjjlv.created_by
FROM...
How : What are the tables involved in the Transfer to GL and GL posting?
What are the tables involved in the Transfer to GL and GL posting?
Subledger Tables
XLA_AE_HEADERS
XLA_AE_LINES
XLA_DISTRIBUTION_LINKS
Transfer Journal Entries to GL (XLAGLTRN) process takes the subledger journals and inserts records into the Interface Tables
Interface Tables
GL_INTERFACE / XLA_GLT_
Journal Import (GLLEZL) then reads from the interface table and creates records in the GL Tables
GL Tables
GL_JE_BATCHES
GL_JE_HEADERS
GL_JE_LINES
GL_IMPORT_REFERENCES
GL Posting process then posts to the GL_BALANCES tabl...
Query : Drilldown from GL to Receiving Transactions
SELECT b.NAME je_batch_name,
b.description je_batch_description,
b.running_total_accounted_dr je_batch_total_dr,
b.running_total_accounted_cr je_batch_total_cr,
b.status je_batch_status,
b.default_effective_date je_batch_effective_date,
b.default_period_name je_batch_period_name,
b.creation_date je_batch_creation_date,
u.user_name je_batch_created_by,
h.je_category je_header_category,
h.je_source je_header_source,
h.period_name je_header_period_name,
h.NAME je_header_journal_name,
h.status je_header_journal_status,
h.creation_date je_header_created_date,
u1.user_name...
Query : Lists all the organization units in the system as well as their addresses
select org.organization_id,
org.name,
loc.address_line_1,
loc.address_line_2,
loc.address_line_3,
loc.town_or_city,
loc.country,
loc.postal_code
from hr_all_organization_units org,
hr_locations_all loc
where org.location_id = loc.location...
Query : To get the asset detail information
SELECT DISTINCT a.asset_number,
a.description,
a.asset_type,
d.segment1 asset_key,
c.segment1 major_category,
c.segment2 minor_category,
b.deprn_method_code,
b.life_in_months/12 life,
b.book_type_code,
b.date_placed_in_service,
b.depreciate_flag,
b.cost,
h.units_assigned UNITS,
g.segment1 COMPANY,
g.segment2 DEPARTMENT,
g.segment3 ACCOUNT,
l.segment1 country,
l.segment2 state,
l.segment3 city,
L.SEGMENT4 BUILDING
FROM fa_additions a,
fa_books_v b,
fa_categories c,
fa_asset_keywords d,
gl_code_combinations g,
fa_distribution_history h,
fa_locations...
Query : Check responsibility assigned to a specific USER
SELECT UNIQUE u.user_id, SUBSTR (u.user_name, 1, 30) user_name,
SUBSTR (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
and a.application_name = 'Purchasing' --- You can filter this
ORDER BY SUBSTR (user_name, 1, 30),
SUBSTR (a.application_name,...
Query : 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...
Query : To get assigned responsibility to a user
SELECT UNIQUE u.user_id, SUBSTR (u.user_name, 1, 30) user_name,
SUBSTR (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, 6...
Query : Get the Hierarchial Information
Query for Hierarchy
SELECT a.employee_id, a.first_name, a.last_name, a.manager_id,
b.first_name mgr_first_name, b.last_name mgr_last_name
FROM
(
select employee_id, first_name, last_name, manager_id
from employees
start with employee_id = 100 -- Replace with starting number
connect by prior employee_id = manager_id
) a,
employees b
WHERE a.manager_id = b.employee_id...
Query : INVENTORY & ORDER MANAGEMENT
Just replace xxx in the query with your organization_id
SELECT
msi.segment1 as Item_Number
, msi.description as Item_Description
, SUM(ool.ordered_quantity) AS SO_quantity
, AVG(oh_qty) as on_hand
FROM
(SELECT
msi.inventory_item_id, msi.organization_id, SUM(ohd.primary_transaction_quantity) AS oh_qty
FROM
mtl_system_items_b msi
JOIN mtl_onhand_quantities_detail ohd ON
(msi.inventory_item_id = ohd.inventory_item_id AND msi.organization_id = ohd.organization_id)
WHERE ohd.subinventory_code = 'Warehouse' AND msi.organization_id = xxx GROUP...
Setting Organization Context in Oracle R12
For those of you who are trying to set the organization context within your SQL sessions or custom programs, there’s a new API you must call in R12.
In 11i the way of setting organization context in 11i was to execute the following statement:
dbms_application_info.set_client_info(ORG_ID);
However, in R12, the new way of setting the organization context is as follows:
mo_global.init(’AR’);
mo_global.set_policy_context(’S',ORG_ID);
The 'S' parameter indicates a single organization context. Replace ORG_ID with the ID of the operating unit you...
Oracle Price Lists
Oracle Price Lists
http://www.oracle.com/corporate/pricing/pricelists.html
Software Investment Guide
http://www.oracle.com/corporate/pricing/sig.h...
EAM
To implement Enterprise Asset Management, you must have the following required products installed:
•Oracle Inventory
•Oracle Bills of Material
•Oracle Human Resources
•Oracle Cost Management
•Oracle Manufacturing Scheduling
•Oracle Quality
•Oracle Work In Process
To implement Enterprise Asset Management, the following products are optional ;
however, they are useful in the overall robust eAM solution:
•Oracle Master Scheduling/MRP
•Oracle Property Management
•Oracle Financials
•Oracle Fixed Assets
•Oracle iProcurement
•Oracle Projects
•Oracle...
How : Item Import
First You must import items into the Item Master organization before you import items into additional organizations. This can be achieved by specifying only your Item Master organization on a first run of the Item Interface and once this has completed, you can run the Item Interface again, this time specifying an additional or all organizations.
Interface table get inserted into ..
MTL_SYSTEM_ITEM_INTERFACE
MTL_ITEMS_REVISIONS_INTERFACE
MTL_ITEM_CATEGORIES_INTERFACE
You set the TRANSACTION_TYPE column to CREATE, to create an item record (true...
Query to find subinventories for an inv organization
select
secondary_inventory_name subinventory,
description,
subinventory_type,
organization_id,
asset_inventory,
quantity_tracked,
inventory_atp_code,
availability_type,
reservable_type,
locator_type,
picking_order,
dropping_order,
location_id,
status_id
from mtl_secondary_inventories
where organization_id=
order by subinvent...
Query to find organization parameters of an inv org
select
ood.organization_code ORG_CODE,
a.master_organization_id MASTER_ORG_ID,
o1.organization_code MASTER_ORG_CD,
o1.organization_name MASTER_ORG_NM,
a.cost_organization_id COST_ORG_ID,
o2.organization_code COST_ORG_CD,
o2.organization_name COST_ORG_NM,
a.source_organization_id SOURCE_ORG_ID,
o3.organization_code SOURCE_ORG_CD,
o3.organization_name SOURCE_ORG_NM,
mfg1.meaning PRIMARY_COST_METHOD,
...
Query to find Supplier sites
select
pov.vendor_name Supplier,
povs.vendor_site_id,
povs.vendor_site_code Site,
povs.address_line1 A1ddress,
povs.address_line2 A2ddress,
povs.address_line3 A3ddress,
povs.city||', '||
povs.state||' '||
povs.zip A4ddress,
povs.ship_to_location_id,
povs.bill_to_location_id,
povs.ship_via_lookup_code,
povs.freight_terms_lookup_code,
povs.fob_lookup_code
from po_vendors pov,
po_vendor_sites povs
where pov.vendor_id=601
and pov.vendor_id=povs.vendor_id
order by...
Query to find supplier info
select
pov.vendor_id,
pov.vendor_name supplier,
pov.vendor_type_lookup_code,
sl.location_code shipto_location,
bl.location_code billto_location,
pov.customer_num,
pov.ship_via_lookup_code,
pov.fob_lookup_code,
rt.name terms,
pov.set_of_books_id,
pov.credit_status_lookup_code,
pov.credit_limit
from ra_terms rt,
hr_locations bl,
hr_locations sl,
po_vendors pov
where pov.vendor_name like 'AVIVA%'
and pov.ship_to_location_id=sl.location_id(+)
and pov.bill_to_location_id=bl.location_id(+)
and...
Shipping APIs
Auto create Delivery WSH_DELIVERY_DETAILS_PUB. AUTOCREATE_DELIVERIES
Pick Release Delivery WSH_DELIVERIES_PUB. DELIVERY_ACTION PICK-RELEASE ; This is used for update actual shipped
quantities WSH_DELIVERY_DETAILS_PUB. UPDATE_SHIPPING_ATTRIBUTES UPDATE
Ship confirm delivery WSH_DELIVERIES_PUB. DELIVERY_ACTION CONFIRM...
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,
...
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 (+)...
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,
...
Query : AR to GL data transfer
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, ...
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...
Query : To find PO returns
execute fnd_client_info.set_org_context('org_id');
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...
API: ORACLE SERVICE CONTRACT API’S
Below mentioned is the core API’s that team uses to convert Oracle Service Contracts.
OKS_CONTRACTS_PUB.Create_Contract_Header:
This API is used to create contract header information. The API is called using P_K_Header_Rec,
P_Header_Contacts_Tbl, P_Header_Sales_Crd_Tbl and P_Header_Articles_Tbl. Depending on your conversion
needs and the available information in the legacy application you would be required to populate each of these
in-parameters. However if no information is available for sales credit or contract articles you could leave the
P_Header_Sales_Crd_Tbl...
API : User Creation
DECLARE
ln_user_id NUMBER;
ln_resp_id NUMBER;
ln_app_id NUMBER;
BEGIN
fnd_user_pkg.createUser( 'TEST1','','WELCOME123');
SELECT user_id
INTO ln_user_id
FROM fnd_user WHERE user_name = 'TEST1';
SELECT responsibility_id,
application_id
INTO ln_resp_id,
ln_app_id
FROM fnd_responsibility
WHERE responsibility_key = 'SYSTEM_ADMINISTRATOR';
FND_USER_RESP_GROUPS_API.insert_assignment( ln_user_id
,ln_resp_id
,ln_app_id
,NULL
,SYSDATE
,NULL
,NULL );
COMMIT;
E...
Query : 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...
Query : Customer Listing with Bill To and Ship To Addresses
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...
Query : To find an PO details
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
,...
HOW: Contract Line Created in OKS
The Contract line details are stored in
1. OKC_K_LINES_B
2. OKC_K_LINES_TL
3. OKE_K_LINES
And the view holding the details of the Contract Line Form is OKE_K_LINE...
Query : Retrieve ex employees for last 3 months
select
from per_all_people_f papf,
per_periods_of_service ppos
where papf.person_id = ppos.person_id
and ppos.actual_termination_date BETWEEN ADD_MONTHS(sysdate,-3) AND sysdate
and ppos.actual_terminate_date BETWEEN papf.effective_start_date AND papf.effective_end_d...
Query : Finding Subordinates and their subordinates
SELECT per.EMPLOYEE_NUMBER,per.FULL_NAME
FROM hr.per_all_people_f per
,hr.per_person_type_usages_f usg
,hr.per_person_types ppt
,hr.per_all_assignments_f paf
WHERE per.person_id = paf.person_id
AND paf.primary_flag = 'Y'
AND per.person_id = usg.person_id
AND usg.person_type_id = ppt.person_type_id
AND ppt.user_person_type = 'Employee'
AND TRUNC(SYSDATE) BETWEEN per.effective_start_date AND per.effective_end_date
AND TRUNC(SYSDATE) BETWEEN paf.effective_start_date AND paf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN usg.effective_start_date AND...
Query : Find employee count
select ppg.group_name, count(*)
from per_all_people_f ppl, per_all_assignments_f paf, pay_people_groups ppg
where ppl.person_id = paf.person_id
and ppl.current_employee_flag = 'Y'
and paf.assignment_type = 'E'
and paf.people_group_id = ppg.people_group_id (+)
and ppl.effective_start_date <= TO_DATE('31/12/2009','DD/MM/YYYY')
and ppl.effective_end_date >= TO_DATE('01/01/2009','DD/MM/YYYY')
and paf.effective_start_date <= TO_DATE('31/12/2009','DD/MM/YYYY')
and paf.effective_end_date >= TO_DATE('01/01/2009','DD/MM/YYYY')
group...
Data Mapping from Payroll to General Ledger when information
Payroll populates the following reference columns in GL_INTERFACE:
reference21 with payroll_action_id of Payroll Transfer to GL process
reference22 with cost_allocation_keyflex_id of Payroll cost flexfield for this entry
reference23 with effective date of the Payroll Run this entry is derived from
reference24 with date_earned of the Payroll Run this entry is derived from
reference25 with payroll_action_id of Payroll Run this entry is derived from *
* is only in 11i
So, these are then placed in reference1, reference2,...
Sample Code: For warranty contract creation for Install base Item
set serverout on
DECLARE
l_K_header_rec OKS_CONTRACTS_PUB.header_rec_type;
l_header_contacts_tbl OKS_CONTRACTS_PUB.contact_tbl;
hcon INTEGER := 1;
l_header_sales_crd_tbl OKS_CONTRACTS_PUB.SalesCredit_tbl;
l_header_articles_tbl OKS_CONTRACTS_PUB.obj_articles_tbl;
l_K_line_rec OKS_CONTRACTS_PUB.line_rec_type;
l_line_contacts_tbl OKS_CONTRACTS_PUB.contact_tbl;
l_line_sales_crd_tbl OKS_CONTRACTS_PUB.SalesCredit_tbl;
l_K_Support_rec OKS_CONTRACTS_PUB.line_rec_type;
l_Support_contacts_tbl OKS_CONTRACTS_PUB.contact_tbl;
l_Support_sales_crd_tbl OKS_CONTRACTS_PUB.SalesCredit_tbl;
l_K_covd_rec...
How : Payroll is moved to GL
This is simple 10 steps process:
1) record get inserted in the following parameters in PAY_ACTION_PARAMETERS
insert into PAY_ACTION_PARAMETERS values ('TGL_DATE_USED','E');
insert into PAY_ACTION_PARAMETERS values ('TGL_REVB_ACC_DATE','Y');
insert into PAY_ACTION_PARAMETERS values ('TGL_GROUP_ID','Y');
2) Setup your costing flexfield qualifiers.
3) Create an element.
4) Link the element.
Check the costing radio button.
Check the transfer to GL checkbox.
Enter the values for the costing/balancing field.
5) Run the quickpay/pre-payments/costing...
Query : Relation between Order Number & Contract Number
Relation between Order Number (From Order Management) and Contract Number (From Service Contracts module).
The relationship is maintained in the table okc_k_rel_objs. following is the sql that will give you the contract number when provided order number.
Release 12 query:
select hdr.contract_number,hdr.contract_number_modifier
from oe_order_headers_all oe,
okc_k_rel_objs rel,
okc_k_headers_all_b hdr
where rel.chr_id = hdr.id
and rel.jtot_object1_code = 'OKX_ORDERHEAD'
and rel.object1_id1 = oe.header_id
and oe.order_number = ''
Release 11i...
API : Contracts Migration API
Contract migration does not happen as the same traditional way of copying data from legacy systems to Interface tables and from interface tables to base tables because oracle does not provide any interface tables for Contracts, but of course provided some other methodology of importing data through the API.
Contracts constitutes the following data,
Contracts headers
OKE_IMPORT_CONTRACT_PUB.create_contract_header
Contract Party
OKC_CONTRACT_PARTY_PUB.create_k_party_role
Contract Contact
OKC_CONTRACT_PARTY_PUB.create_contact
Contract Terms
OKE_TERMS_PUB.create_term...
Service contracts API
How to create a service contract using API
You can call API "oks_contracts_pub" to create service cotract header,line, billing schedule etc.
okc_contract_pub
Creating contracts from API is creating it from back-end through program whereas using responsibility it is using Oracle provided standard form.
If your requirement is just to create service contract then use form otherwise you can use API within your program as follows:
Creation of contract header
oks_contracts_pub.create_contract_header(p_k_header_rec, p_header_contacts_tbl, p_header_sales_crd_tbl,...
Salary History Conversion
You can use following APIS for creation of Salary proposals and its components :
1. HR_MAINTAIN_PROPOSAL_API.INSERT_SALARY_PROPOSAL
2. HR_MAINTAIN_PROPOSAL_API.INSERT_PROPOSAL_COMPONENT
3. HR_MAINTAIN_PROPOSAL_API.APPROVE_SALARY_PROPOSAL
If you are not creating Employee History then the Salary APIs will give you problems. When employee record does not exist and you try to create salary and its components, it wont be able to do so and return errors. To create employee salary, you need payroll, pay basis and an element. When you try to create employee...
Subscribe to:
Posts (Atom)
