INFO: Oracle Workflow APIs

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_NOTIFICATIONS

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 = c.flex_value_set_id
and a.flex_value_id = b.flex_value_id
and he.set_of_books_id=bl.set_of_books_id
and a.language = 'US'
and c.flex_value_set_name like 'Operations Account'
and b.flex_value= gl.segment12;

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_id

INFO: AIM Documents for Techies & Techno Functional

OCA/OCP Oracle Database 11g All-in-One Exam Guide with CD-ROM: Exams 1Z0-051, 1Z0-052, 1Z0-053 (Osborne ORACLE Press Series)MD050 - Module Design - By Functional Consultants
MD070 - Technical Document Design - By Technical Consultants
MD020 - Testing Document Design - By Functional Consultants
MD0120 - Migration/ User Training - By Technical Consultants
CV040 - Conversion of Functional Document - By Functional Consultants
CV060 - Conversion of Technical Document - By Technical Consultants

Query : Asset migration

SELECT
Special Edition Using Oracle 11iFA.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 FAI,
FA_DISTRIBUTION_HISTORY,FA_DEPRN_SUMMARY FDS
WHERE
FA.ASSET_ID = FB. ASSET_ID AND
FA.ASSET_ID = FB ASSET_ID AND
FA.ASSET_ID =FAI.ASSET_ID AND
FA.ASSET_ID = FDH. ASSET_ID AND
FB.BOOK_TYPE_CODE = FAI. BOOK_TYPE_CODE AND
FB.BOOK_TYPE_CODE = FDH. BOOK_TYPE_CODE

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 asset_id
from fa.fa_retirements
where to_char(date_retired,'mon-yy') = 'dec-03')
and (retirement_id is not null and date_ineffective is not null)
group by asset_id
) b
where a.asset_id = b.asset_id(+)
) ad,
apps.fa_books bk,
apps.fa_categories_b cat,
fa.FA_BOOK_CONTROLS fbc,
fa.fa_locations loc,
hr.per_all_people_f hr,
(Select ds2.asset_id, fdh.UNITS_ASSIGNED, cc.SEGMENT2,cc.SEGMENT3,
fdh.LOCATION_ID,fdh.ASSIGNED_TO,
ds2.deprn_reserve as deprn_reserve,
decode(dp2.fiscal_year,TO_NUMBER(TO_CHAR(to_date(upper('JAN-07'),'MON-RR'),'YYYY')),
ds2.ytd_deprn,Null) as ytd_deprn,
decode(dp2.period_name,upper('JAN-07'),ds2.deprn_amount,Null) as deprn_amount
from fa.fa_deprn_periods dp2,
fa.fa_deprn_summary ds2,
fa.FA_BOOK_CONTROLS fbc,
fa.FA_DISTRIBUTION_HISTORY fdh,
gl.GL_CODE_COMBINATIONS cc
where fbc.BOOK_CLASS = 'CORPORATE'
and dp2.book_type_code = fbc.book_type_code
and ds2.book_type_code = fbc.book_type_code
and fdh.book_type_code = fbc.book_type_code
and dp2.period_counter = ds2.period_counter
and ds2.asset_id = fdh.asset_id
and cc.CODE_COMBINATION_ID=fdh.CODE_COMBINATION_ID
and nvl(fdh.DATE_INEFFECTIVE,sysdate+1) > sysdate
and ds2.period_counter = ( Select Max(dss.period_counter)
from fa.fa_deprn_summary dss
where dss.book_type_code = ds2.book_type_code
and dss.asset_id = ds2.asset_id )
and dp2.book_type_code = fbc.BOOK_TYPE_CODE ) ds
where ad.ASSET_ID = bk.ASSET_ID
and fbc.BOOK_CLASS = 'CORPORATE'
and bk.book_type_code = fbc.book_type_code
and nvl(bk.DATE_INEFFECTIVE, sysdate+1)>sysdate
and ds.asset_id(+) = ad.asset_id
and ad.asset_category_id = cat.category_id
and ds.LOCATION_ID = loc.location_id
and loc.enabled_flag = 'Y'
and ds.assigned_to = hr.person_id(+)
and nvl(hr.EFFECTIVE_END_DATE,sysdate+1)>sysdate

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 pv.VENDOR_ID = ai.VENDOR_ID
AND aid.line_type_lookup_code = 'ITEM'
AND ai.invoice_type_lookup_code = 'PREPAYMENT'
AND ai.INVOICE_ID = :P_INVOICE_ID
AND NVL (reversal_flag, 'N') <> 'Y'
GROUP BY pv.vendor_name, ai.invoice_num
HAVING NVL (
DECODE (
SIGN (SUM (amount - NVL (prepay_amount_remaining, amount))),
1,
DECODE (SUM (prepay_amount_remaining), 0, 'Y', NULL),
NULL
),
'N'
) <> 'Y';

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 table.

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') ytd_depreciation
,mvl_discoverer_fin_support.get_depreciation_dtls(fab.asset_id
,fbv.book_type_code
,fdh.distribution_id
,fdp.period_counter
,'RUN') dep_this_run
,gcc.segment2 department_no
,(SELECT ppx.full_name FROM per_people_x ppx WHERE ppx.person_id = fdh.assigned_to AND ROWNUM = 1) custodian
,(SELECT ppx.employee_number FROM per_people_x ppx WHERE ppx.person_id = fdh.assigned_to AND ROWNUM = 1) employee_number
,(SELECT po_number FROM fa_invoice_details_v WHERE asset_id = fbv.asset_id AND ROWNUM = 1) po_number
,(SELECT invoice_number FROM fa_invoice_details_v WHERE asset_id = fbv.asset_id AND ROWNUM = 1) invoice_number
,(SELECT vendor_number FROM fa_invoice_details_v WHERE asset_id = fbv.asset_id AND ROWNUM = 1) vendor_number
,(SELECT vendor_name FROM fa_invoice_details_v WHERE asset_id = fbv.asset_id AND ROWNUM = 1) vendor_name
,fab.serial_number
,fl.segment3||'.'||fl.segment4 location_flexfield
,fab.attribute1 tax_major_category
,fab.attribute2 tax_minor_category
,(SELECT retirement_type_code FROM fa_retirements WHERE retirement_id = fdh.retirement_id AND ROWNUM = 1) retirement_type
,(SELECT segment1||'.'||segment2 FROM fa_asset_keywords WHERE code_combination_id = fab.asset_key_ccid AND ROWNUM = 1) asset_key
,fdp.period_name
,fab.attribute3 acquisition_date
,fab.manufacturer_name
,fbv.retirement_pending_flag
FROM apps.fa_additions_v fab
,apps.fa_books_v fbv
,apps.fa_categories_b fcb
,apps.fa_deprn_periods fdp
,apps.fa_distribution_history fdh
,apps.gl_code_combinations gcc
,apps.fa_locations fl
WHERE 1=1
AND fab.asset_id = fbv.asset_id
AND fcb.category_id = fab.asset_category_id
AND fbv.transaction_header_id_out IS NULL
AND fdp.book_type_code = fbv.book_type_code
AND fdh.asset_id = fbv.asset_id
AND fdh.code_combination_id = gcc.code_combination_id
AND fdh.location_id=fl.location_id
AND fbv.transaction_header_id_out IS NULL
AND fdh.transaction_header_id_out IS NULL

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_org;

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_id

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 = ctt.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_date,
pse.amount_due_original,
pse.amount_due_remaining

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 b,
ar.ra_customer_trx_all c
WHERE c.set_of_books_id = '&sob'
AND a.customer_trx_id = c.customer_trx_id
AND a.set_of_books_id = '&sob'
AND a.set_of_books_id = b.set_of_books_id
AND a.customer_trx_line_id = b.link_to_cust_trx_line_id
AND a.revenue_amount <> b.taxable_amount
AND EXISTS (
SELECT 'T'
FROM ar.ra_cust_trx_types_all rctt
WHERE rctt.post_to_gl = 'Y'
AND rctt.set_of_books_id = c.set_of_books_id
AND rctt.cust_trx_type_id = c.cust_trx_type_id
AND rctt.end_date IS NULL)
AND EXISTS (
SELECT 'T'
FROM ar.ra_cust_trx_line_gl_dist_all rctlg
WHERE rctlg.set_of_books_id = c.set_of_books_id
AND rctlg.customer_trx_id = c.customer_trx_id
AND rctlg.gl_date >= SYSDATE - 31)

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 journal entry.

Amortized Adjustment

For amortized adjustments, Oracle Assets spreads the adjustment amount over the remaining life or remaining capacity of the asset. For flat–rate methods, Oracle Assets starts depreciating the asset using the new information. You can set up your amortized adjustments to have a retroactive start date by changing the default amortization start date (usually the system date) to a date in a previous period. Any adjustment amount missed since the amortization start date is taken in the current period.

If you amortize an adjustment for an asset, you cannot expense any future adjustments for that asset in that book.You can allow an amortized adjustment for the book in the Book Controls window.

• Method Adjustments: For amortized method changes, Oracle Assets does not recalculate accumulated depreciation, but uses the new information for the remaining time the asset is in service.

– For table and calculated methods, Oracle Assets depreciates the cost less the accumulated depreciation over the remaining life of the asset.

– For diminishing value methods, Oracle Assets calculates depreciation based on the recoverable net book value of the asset as of the period you make the change.

– If, instead, your depreciation method multiplies the flat–rate by the cost, Oracle Assets begins using the new information to calculate depreciation.

• Bonus Adjustments:

– For assets with a cost–based depreciation basis, the bonus rate is applied to the cost.

– For assets with a net book value depreciation method basis, the bonus rate is applied to the cost less total reserve (accumulated depreciation and bonus reserve).

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 = '&sob'

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
AND b.org_id = '&org_id'
AND b.org_id = a.org_id
AND b.receipt_number LIKE '%&receipt_number%')
AND ad.code_combination_id = gcc.code_combination_id
UNION ALL
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 = 'RA'
AND EXISTS (
SELECT 'T'
FROM ar.ar_receivable_applications_all a,
ar.ar_cash_receipts_all b
WHERE a.cash_receipt_id = b.cash_receipt_id
AND a.receivable_application_id = source_id
AND b.org_id = '&org_id'
AND b.org_id = a.org_id
AND b.receipt_number LIKE '%&receipt_number%')
AND ad.code_combination_id = gcc.code_combination_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',
'N', 'Never',
closing_status
) gl_status,
set_of_books_id
FROM gl.gl_period_statuses
WHERE application_id = 101
AND UPPER (period_name) = UPPER ('&period_name')
AND set_of_books_id ='&sob' ) a,
(SELECT period_name,
DECODE (closing_status,
'O', 'Open',
'C', 'Closed',
'F', 'Future',
'N', 'Never',
closing_status
) po_status,set_of_books_id
FROM gl.gl_period_statuses
WHERE application_id = 201
AND UPPER (period_name) = UPPER ('&period_name')
AND set_of_books_id ='&sob' ) b,
(SELECT period_name,
DECODE (closing_status,
'O', 'Open',
'C', 'Closed',
'F', 'Future',
'N', 'Never',
closing_status
) ap_status,set_of_books_id
FROM gl.gl_period_statuses
WHERE application_id = 200
AND UPPER (period_name) = UPPER ('&period_name')
AND set_of_books_id ='&sob' ) c,
(SELECT period_name,
DECODE (closing_status,
'O', 'Open',
'C', 'Closed',
'F', 'Future',
'N', 'Never',
closing_status
) ar_status,set_of_books_id
FROM gl.gl_period_statuses
WHERE application_id = 222
AND UPPER (period_name) = UPPER ('&period_name')
AND set_of_books_id ='&sob') d,
(SELECT fdp.period_name,
DECODE (fdp.period_close_date,
NULL, 'Open',
'Closed'
) fa_status,fbc.set_of_books_id
FROM fa.fa_book_controls fbc, fa.fa_deprn_periods fdp
WHERE fbc.set_of_books_id ='&sob'
AND fbc.book_type_code = fdp.book_type_code
AND UPPER (fdp.period_name) = UPPER ('&period_name')) e
WHERE a.period_name = b.period_name(+)
AND a.period_name = c.period_name(+)
AND a.period_name = d.period_name(+)
AND a.period_name = e.period_name(+)
AND a.set_of_books_id=b.set_of_books_id(+)
and a.set_of_books_id=c.set_of_books_id(+)
and a.set_of_books_id=d.set_of_books_id(+)
and a.set_of_books_id=e.set_of_books_id(+)
ORDER BY 1

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 exist;

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,
ONT.OE_HOLD_RELEASES HR,
APPS.FND_LOOKUP_VALUES FLV1
WHERE HS.ORG_ID = '&ORG_ID' --- xxx
AND HS.HOLD_ID = HD.HOLD_ID
AND OH.HOLD_SOURCE_ID = HS.HOLD_SOURCE_ID
AND OH.HEADER_ID = OHA.HEADER_ID
AND FLV.LOOKUP_TYPE = 'HOLD_ENTITY_DESC'
AND FLV.VIEW_APPLICATION_ID = 660
AND FLV.LOOKUP_CODE = HS.HOLD_ENTITY_CODE
AND OH.HOLD_RELEASE_ID = HR.HOLD_RELEASE_ID
AND FLV1.LOOKUP_TYPE = 'RELEASE_REASON'
AND FLV1.VIEW_APPLICATION_ID = 660
AND FLV1.LOOKUP_CODE = HR.RELEASE_REASON_CODE

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) seg3,
gl_flexfields_pkg.get_description_sql(&char_of_account_id,
4,
gcc.segment4) seg4,
gl_flexfields_pkg.get_description_sql(&char_of_account_id,
5,
gcc.segment5) seg5
FROM gl_code_combinations gcc
WHERE gcc.segment4 = '25130' --gcc.code_combination_id = &code_combination_id;

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,
NULL)) Entered_Dr,
DECODE(SIGN(mta.transaction_value),
-1,
(-1 * mta.transaction_value),
0,
0,
NULL,
DECODE(SIGN(mta.base_transaction_value),
-1,
(-1 * mta.base_transaction_value))) Entered_Cr,
DECODE(SIGN(mta.base_transaction_value),
1,
mta.base_transaction_value,
0,
0,
NULL) Accounted_Dr,
DECODE(SIGN(mta.base_transaction_value),
-1,
(-1 * mta.base_transaction_value),
0,
0,
NULL) Accounted_Cr,
gh.currency_code,
mtt.transaction_type_name,
decode(mta.gl_batch_id, -1, 'N', 'Y') "Transfered_Flag",
mta.gl_batch_id,
gh.je_header_id
from inv.mtl_material_transactions mmt,
inv.mtl_transaction_types mtt,
inv.mtl_system_items_b msi,
inv.mtl_transaction_accounts mta,
gl.gl_code_combinations gcc,
gl.gl_je_batches gb,
gl.gl_je_headers gh,
gl.gl_je_lines gl,
gl.gl_import_references gr
where mmt.organization_id = msi.organization_id
and msi.inventory_item_id = mmt.inventory_item_id
and mmt.transaction_id = mta.transaction_id
and gcc.code_combination_id = mta.reference_account
and mtt.transaction_type_id = mmt.transaction_type_id
and gb.je_batch_id = gh.je_batch_id
and gh.je_header_id = gl.je_header_id
and gl.code_combination_id = mta.reference_account
and mta.gl_batch_id =
to_number(substr(gb.name, 1, instr(gb.name, ' ') - 1))
and gh.je_Category = 'MTL'
and gh.je_source = 'Inventory'
and gh.name = 'XXX' ---REPLACE XXX WITH NAME
and gl.je_line_num = gr.je_line_num
and gr.je_header_id = gl.je_header_id
and gr.je_line_num = gl.je_line_num
and mta.gl_batch_id = gr.reference_1
and gh.period_name = '&period_name' -- ENTER THE PERIOD
and upper(gb.name) like upper('%&gl_batch_name%')
order by 1

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_references

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 "CHARGE ACCOUNT"
FROM apps.GL_JE_JOURNAL_LINES_V gjjlv
, gl_je_lines gjl
, ap_ae_lines_all aala
, gl_je_headers gjh
, gl_je_batches gjb
, ap_invoices_all aia
, apps.gl_code_combinations_KFV glcc
, po_vendors pv
WHERE gjl.gl_sl_link_table = 'APECL'
AND gjl.period_name ='NOV-2008'--period
--and gjjlv.currency_code = --currency code
AND gjb.je_batch_id = gjh.je_batch_id
AND gjh.je_header_id = gjl.je_header_id
AND gjh.period_name = gjl.period_name
AND gjh.set_of_books_id = gjl.set_of_books_id
AND glcc.code_combination_id = gjl.code_combination_id
AND gjjlv.je_batch_id = gjh.je_batch_id
AND gjjlv.je_header_id = gjh.je_header_id
AND gjjlv.line_je_line_num = gjl.je_line_num
AND gjh.period_name = gjjlv.period_name
AND gjh.set_of_books_id = gjjlv.set_of_books_id
AND glcc.code_combination_id = gjjlv.line_code_combination_id
AND aala.code_combination_id = gjl.code_combination_id
AND aala.gl_sl_link_id = gjl.gl_sl_link_id
AND aala.reference5 = aia.invoice_num
AND gjh.set_of_books_id = aia.set_of_books_id
AND pv.vendor_id = aia.vendor_id
AND gjjlv.line_reference_1 = pv.vendor_name
ORDER BY aia.invoice

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 apps.GL_JE_JOURNAL_LINES_V gjjlv
, gl_je_lines gje
, apps.fa_ael_gl_v fasv
, gl_je_headers gjh
, gl_je_batches gjb
, apps.gl_code_combinations_kfv glcc
WHERE gjh.period_name BETWEEN 'SEP-2008' AND 'OCT-2008'
AND glcc.code_combination_id = gje.code_combination_id
AND glcc.code_combination_id = fasv.code_combination_id
AND gjh.JE_BATCH_ID = gjb.JE_BATCH_ID
AND gjh.JE_HEADER_ID = gje.JE_HEADER_ID
AND gjh.period_name = gjb.default_period_name
AND gjh.period_name = gje.period_name
AND gjjlv.period_name = gjh.period_name
AND gjjlv.je_batch_id = gjh.je_batch_id
AND gjjlv.je_header_id = gjh.je_header_id
AND gjjlv.LINE_JE_LINE_NUM = gje.je_line_num
AND gjjlv.je_header_id = fasv.je_header_id
AND glcc.segment1='22'

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 table.

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 je_header_created_by,
h.description je_header_description,
h.running_total_accounted_dr je_header_total_acctd_dr,
h.running_total_accounted_cr je_header_total_acctd_cr,
l.je_line_num je_lines_line_number,
l.ledger_id je_lines_ledger_id,
glcc.concatenated_segments je_lines_ACCOUNT,
l.entered_dr je_lines_entered_dr,
l.entered_cr je_lines_entered_cr,
l.accounted_dr je_lines_accounted_dr,
l.accounted_cr je_lines_accounted_cr,
l.description je_lines_description,
glcc1.concatenated_segments xla_lines_account,
xlal.accounting_class_code xla_lines_acct_class_code,
xlal.accounted_dr xla_lines_accounted_dr,
xlal.accounted_cr xla_lines_accounted_cr,
xlal.description xla_lines_description,
xlal.accounting_date xla_lines_accounting_date,
xlate.entity_code xla_trx_entity_code,
xlate.source_id_int_1 xla_trx_source_id_int_1,
xlate.source_id_int_2 xla_trx_source_id_int_2,
xlate.source_id_int_3 xla_trx_source_id_int_3,
xlate.security_id_int_1 xla_trx_security_id_int_1,
xlate.security_id_int_2 xla_trx_security_id_int_2,
xlate.transaction_number xla_trx_transaction_number,
rcvt.transaction_type rcv_trx_transaction_type,
rcvt.transaction_date rcv_trx_transaction_date,
rcvt.quantity rcv_trx_quantity,
rcvt.shipment_header_id rcv_trx_shipment_header_id,
rcvt.shipment_line_id rcv_trx_shipment_line_id,
rcvt.destination_type_code rcv_trx_destination_type_code,
rcvt.po_header_id rcv_trx_po_header_id,
rcvt.po_line_id rcv_trx_po_line_id,
rcvt.po_line_location_id rcv_trx_po_line_location_id,
rcvt.po_distribution_id rcv_trx_po_distribution_id,
rcvt.vendor_id rcv_trx_vendor_id,
rcvt.vendor_site_id rcv_trx_vendor_site_id
FROM
gl_je_batches b,
gl_je_headers h,
gl_je_lines l,
fnd_user u,
fnd_user u1,
gl_code_combinations_kfv glcc,
gl_code_combinations_kfv glcc1,
gl_import_references gir,
xla_ae_lines xlal,
xla_ae_headers xlah,
xla_events xlae,
xla.xla_transaction_entities xlate,
rcv_transactions rcvt
WHERE
b.created_by = u.user_id
AND h.created_by = u1.user_id
AND b.je_batch_id = h.je_batch_id
AND h.je_header_id = l.je_header_id
AND l.code_combination_id = glcc.code_combination_id
AND l.je_header_id = gir.je_header_id
AND l.je_line_num = gir.je_line_num
AND gir.gl_sl_link_table = xlal.gl_sl_link_table
AND gir.gl_sl_link_id = xlal.gl_sl_link_id
AND xlal.application_id = xlah.application_id
AND xlal.ae_header_id = xlah.ae_header_id
AND xlal.code_combination_id = glcc1.code_combination_id
AND xlah.application_id = xlae.application_id
AND xlah.event_id = xlae.event_id
AND xlae.application_id = xlate.application_id
AND xlae.entity_id = xlate.entity_id
AND xlate.source_id_int_1 = rcvt.transaction_id
AND h.je_category = 'Receiving'
AND b.default_period_name = '01-JUN-2010'
ORDER BY h.je_category;


Ref :http://oracle.anilrpatil.com/2009/12/12/drilldown-from-gl-to-receiving-transactions-2/

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_id

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 l
WHERE a.asset_id = b.asset_id
AND a.asset_id = h.asset_id
AND a.asset_category_id = c.category_id
AND h.code_combination_id = g.code_combination_id
AND h.location_id = l.location_id
AND a.asset_key_ccid = d.CODE_COMBINATION_ID
AND b.book_type_code LIKE ' CORP BOOK'
AND H.TRANSACTION_HEADER_ID_OUT IS NULL

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, 1, 50),
SUBSTR (r.responsibility_name, 1, 60)

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 = fa.application_id
ORDER BY description

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, 60);

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 BY msi.inventory_item_id, msi.organization_id) Q1
JOIN
oe_order_lines_all ool ON (q1.inventory_item_id = ool.inventory_item_id AND q1.organization_id = ool.ship_from_org_id)
JOIN oe_order_headers_all ooh ON (ool.header_id = ooh.header_id)
JOIN mtl_system_items_b msi ON (ool.flow_status_code = 'AWAITING_SHIPPING' AND ool.inventory_item_id = msi.inventory_item_id AND ool.ship_from_org_id = msi.organization_id)
GROUP BY msi.inventory_item_id, msi.segment1, msi.description
ORDER BY msi.segment1

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 wish to use. You can also set a context for multiple operating units by setting the security profile. This is done by executing the following statement:

mo_global.set_org_access(NULL,SECURITY_PROFILE_ID,’ASO’);

Replace SECURITY_PROFILE_ID with the ID of the appropriate security profile. ‘ASO’ represents the application short name associated with the responsibility you’ll be using.

Oracle Price Lists

Oracle Price Lists
http://www.oracle.com/corporate/pricing/pricelists.html

Software Investment Guide
http://www.oracle.com/corporate/pricing/sig.html

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 Project Manufacturing
•Oracle Purchasing
•Oracle Order Management
•Oracle Time and Labor .

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 when both importing a new item and assigning an already existing item to another organization). This is the only value currently supported by the Item Interface.Set the PROCESS_FLAG to 1 (Pending), so that the Item Interface can pick up the row and process it.The program sets the PROCESS_FLAG to 7 (Import succeeded) or 4 (Import failed) or 3 (validation failed).

A row is inserted into the MTL_INTERFACE_ERRORS table for all failed rows.When the Item Interface imports an item, it also assigns the item to the mandatory category sets based on the item defining attributes. The default category for each category set is used. The Item Interface also allows you to assign items to other category sets and categories, when there is data for item category assignments in the MTL_ITEM_CATEGORIES_INTERFACE table.


Runbing Import Items Report :
you need to indicate whether to run the interface for all organizations in the item interface table. If you choose No, the interface runs only for the current organization and interface table rows for other organizations are ignored.Parameter Create or Update Items..
1 Create new items.
2 Update existing items

Customer Item Interfaces
Extract customer item and customer item cross reference information from your source system and insert it into the MTL_CI_INTERFACE and MTL_CI_XREFS_INTERFACE tables. You run the Customer Item and Customer Item Cross Reference Interfaces to import the data.

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 subinventory

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,
mfg2.meaning NEGATIVE_BALANCE,
mfg11.meaning GL_UPDATE_CODE,
a.calendar_code CALENDAR_CODE,
a.default_demand_class DEFAULT_DEMAND_CLASS,
mfg12.meaning ENCUMBRANCE_REVERSAL_FLAG,
mfg3.meaning LOCATOR_CONTROL,
mfg4.meaning INTERORG_TRANSFER_CODE,
DECODE(a.maintain_fifo_qty_stack_type, NULL,'',mfg6.meaning)
MAINTAIN_FIFO_COST,
mfg7.meaning SERIAL_NUMBER_TYPE,
mfg8.meaning LOT_NUMBER_UNIQUENESS,
mfg9.meaning LOT_NUMBER_GENERATION,
DECODE(a.LOT_NUMBER_ZERO_PADDING, NULL, '' ,
mfg10.meaning)
LOT_NUMBER_ZERO_PADDING,
b.rule_name ATP_RULE_NAME,
c.picking_rule_name PICKING_RULE_NAME,
a.default_locator_order_value,
a.default_subinv_order_value,
a.interorg_trnsfr_charge_percent intorg_charge_percent,
a.auto_serial_alpha_prefix,
a.start_auto_serial_number,
a.auto_lot_alpha_prefix,
a.lot_number_length,
mfg13.meaning SERIAL_GENERATION,
mfg14.meaning SOURCE_TYPE,
a.source_subinventory SOURCE_SUBINV
from
mtl_parameters a,
mtl_atp_rules b,
mtl_picking_rules c,
org_organization_definitions ood,
org_organization_definitions o1,
org_organization_definitions o2,
org_organization_definitions o3,
mfg_lookups mfg1,
mfg_lookups mfg2,
mfg_lookups mfg3,
mfg_lookups mfg4,
mfg_lookups mfg6,
mfg_lookups mfg7,
mfg_lookups mfg8,
mfg_lookups mfg9,
mfg_lookups mfg10,
mfg_lookups mfg11,
mfg_lookups mfg12,
mfg_lookups mfg13,
mfg_lookups mfg14
where a.organization_id = 207
and a.master_organization_id = o1.organization_id (+)
and a.cost_organization_id = o2.organization_id (+)
and a.source_organization_id = o3.organization_id (+)
and a.organization_id = ood.organization_id (+)
and a.default_atp_rule_id = b.rule_id(+)
and a.default_picking_rule_id = c.picking_rule_id(+)
and mfg1.lookup_type (+) = 'MTL_PRIMARY_COST'
and a.primary_cost_method = mfg1.lookup_code(+)
and mfg2.lookup_type (+) = 'SYS_YES_NO'
and a.negative_inv_receipt_code = mfg2.lookup_code(+)
and mfg3.lookup_type (+) = 'MTL_LOCATION_CONTROL'
and a.stock_locator_control_code = mfg3.lookup_code(+)
and mfg4.lookup_type (+) = 'MTL_INTER_INV_TRANSFER'
and a.matl_interorg_transfer_code = mfg4.lookup_code(+)
and mfg6.lookup_type (+) = 'SYS_YES_NO'
and a.maintain_fifo_qty_stack_type = mfg6.lookup_code(+)
and mfg7.lookup_type (+) = 'MTL_SERIAL_NUMBER_TYPE'
and a.serial_number_type = mfg7.lookup_code(+)
and mfg8.lookup_type (+) = 'MTL_LOT_UNIQUENESS'
and a.lot_number_uniqueness = mfg8.lookup_code(+)
and mfg9.lookup_type (+) = 'MTL_LOT_GENERATION'
and a.lot_number_generation = mfg9.lookup_code(+)
and mfg10.lookup_type (+) = 'SYS_YES_NO'
and a.lot_number_zero_padding = mfg10.lookup_code(+)
and mfg11.lookup_type (+) = 'SYS_YES_NO'
and a.general_ledger_update_code = mfg11.lookup_code(+)
and mfg12.lookup_type (+) = 'SYS_YES_NO'
and a.encumbrance_reversal_flag = mfg12.lookup_code(+)
and mfg13.lookup_type (+) = 'MTL_SERIAL_GENERATION'
and a.serial_number_generation = mfg13.lookup_code(+)
and mfg14.lookup_type (+) = 'MTL_SOURCE_TYPES'
and a.source_type = mfg14.lookup_code (+)

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 1

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 pov.terms_id=rt.term_id(+)
order by 1

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,
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;

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'

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

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,
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

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

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 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

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 and P_Header_Articles_Tbl NULL. However it is a must that you populate the
P_K_Header_Rec with all the values. At least 1 contact information for the vendor should be created in
P_Header_Contacts_Tbl. The value for the contact_object_code would be ‘OKX_SALESPERS’. If you need to
create additional parties on the contract header depending on your billing requirements and other entity
relationships you may have to call Okc_Contract_Party_Pub.Create_K_Party_Role. Als o you would need to
determine in advance how do you wish to bring these contracts into the Application. These contracts can be
brought in as ‘ENTERED’, which would mean the process owners would have to submit the contract for approval
using Contracts Approval Workflow.

OKS_CONTRACTS_PUB.Create_Service_Line

This API is used to create Contract Service Line for each contract header. The API is called using P_K_Line_Rec,
P_Contact_Tbl and P_Line_Sales_Crd_Tbl. Again depending on your needs and available information you would
populate each of these parameters. However if no information were available for the P_Contact_Tbl and
P_Line_Sales_Crd_Tbl, it would be okay to let them be NULL from conversion perspective. The key aspect of
service line API is to understand the relationships between the shipping entity and the billing entity. Especially
if the item is serviceable with a usage component and has been leased out using a third party company. Also
important is to attach this item with the appropriate Install Base record if Install Base application is in use.

OKS_CONTRACTS_PUB.Create_Covered_Line
This API is used to create Covered Lines for each service line you create. Before you call this API you are
required to set values for P_K_Covered_Rec and P_Price_Attribs_In. Depending on your pricing needs values for
P_Price_Attribs_In will have to be set. If there is no complex pricing requirements it is okay to leave the value
for this record type null. Values of certain columns of P_K_Covered_Rec should be same as P_K_Line_Rec e.g.
value for PERIOD in the record should be same as the value for the P_K_Line_Rec.Usage_Type or value for
Line_Renewal_Type should be either FUL/DNR/KEP.

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;
END;

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 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(+);

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 = 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 = ;

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
, 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=
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;

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_LINES_V

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_date

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 usg.effective_end_date
AND ( per.person_id IN
(SELECT v.person_id FROM (SELECT person_id, supervisor_id
FROM hr.per_all_assignments_f s
WHERE TRUNC(SYSDATE) BETWEEN s.effective_start_date AND s.effective_end_date
AND s.primary_flag = 'Y'
AND s.assignment_type = 'E') v
CONNECT BY v.supervisor_id = PRIOR v.person_id
START WITH v.person_id = )
or PAF.PERSON_ID = )

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 by ppg.group_name

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, reference3, reference4 and
reference5 in GL_IMPORT_REFERENCES respectively.

 
Using these 5 values, the customer will be able to run sql to derive which
elements, assignment etc (pay run results), the entries in GL were
derived from.

PAY_TRGL_PKG - Procedure to transfer pay costs to General Ledger (pytrangl.pkb)

 

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 OKS_CONTRACTS_PUB.Covered_level_Rec_Type;
l_price_attribs_in OKS_CONTRACTS_PUB.pricing_attributes_type;
l_merge_rule Varchar2(100);
l_usage_instantiate Varchar2(100);
l_ib_creation Varchar2(100);
l_Strm_hdr_rec OKS_BILL_SCH.StreamHdr_type;
l_strm_level_tbl OKS_BILL_SCH.StreamLvl_tbl;
lx_chrid Number;
lx_service_line_id Number;
lx_cp_line_id Number;
lx_return_status Varchar2(1);
lx_msg_count Number;
lx_msg_data Varchar2(2000);
lx_msg_tbl okc_qa_check_pub.msg_tbl_type;
l_descr okc_k_headers_tl.short_description%TYPE;
l_contract_number okc_k_headers_b.contract_number%TYPE;
l_qa_error BOOLEAN;

exc_failed EXCEPTION;

BEGIN

dbms_output.enable(1000000);

-- fnd_global.apps_initialize(1701 -- CBS_CONVERSION
-- ,21707 -- OKC_MANAGER
-- ,510); -- OKC

xxcb_c00_common_pkg.init_job('C04','DK',100);
xxcb_c00_common_pkg.g_debug_on := TRUE;

okc_context.set_okc_org_context;

okc_api.init_msg_list(OKC_API.G_TRUE);

savepoint start_contract;

l_descr := 'CBS API Test '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS');

l_K_header_rec.contract_number := l_contract_number;
l_K_header_rec.start_date := TRUNC(sysdate);
l_K_header_rec.end_date := TRUNC(sysdate)+365;
l_K_header_rec.sts_code := 'ENTERED';
l_K_header_rec.scs_code := 'SERVICE';
l_K_header_rec.authoring_org_id := 85; -- CANON DENMARK
l_K_header_rec.short_description := l_descr;
l_K_header_rec.chr_group := 2; -- CBS DK Service Contracts
l_K_header_rec.pdf_id := 3; -- Approval Workflow OKCAUKAP
l_K_header_rec.party_id := 1108; -- Lego Systems
l_K_header_rec.bill_to_id := 1039; -- Lego Systems 1039
l_K_header_rec.ship_to_id := 1041; -- Lego Systems 1041
l_K_header_rec.price_list_id := 6112; -- CBS DK Standard Pricelist
l_K_header_rec.agreement_id := 44; -- Lego Systems Agreement
l_K_header_rec.currency := 'DKK'; -- Danish Kroner
l_K_header_rec.accounting_rule_type:= 1002; -- 12 MONTHS ADVANCE
l_K_header_rec.invoice_rule_type := -2; -- Advance Invoice
l_K_header_rec.payment_term_id := 1024; -- 1ST OF MONTH
l_K_header_rec.contact_id := 1131; -- Peter Parker
l_K_header_rec.merge_type := 'NEW';
l_K_header_rec.merge_object_id := NULL;
l_K_header_rec.Ar_interface_yn := 'Y';
l_K_header_rec.transaction_type := 1089; -- SERV-Invoice
l_K_header_rec.Summary_invoice_yn := 'N';
l_K_header_rec.qcl_id := 1;



l_header_contacts_tbl(hcon).party_role := 'CUSTOMER';
l_header_contacts_tbl(hcon).contact_role := 'USER';
l_header_contacts_tbl(hcon).contact_object_code := 'OKX_PCONTACT';
l_header_contacts_tbl(hcon).contact_id := 1131; -- Peter Parker

hcon := hcon + 1;

l_header_contacts_tbl(hcon).party_role := 'VENDOR';
l_header_contacts_tbl(hcon).contact_role := 'SALESPERSON';
l_header_contacts_tbl(hcon).contact_object_code := 'OKX_SALEPERS';
l_header_contacts_tbl(hcon).contact_id := 100000000; -- Lange, MRS. Dagmar



l_K_line_rec.k_line_number := '1';
l_K_line_rec.line_sts_code := 'ACTIVE';
l_K_line_rec.cust_account := '1021';
l_K_line_rec.org_id := 85; -- CANON DENMARK
l_K_line_rec.organization_id := 90; -- CANON DENMARK
l_K_line_rec.bill_to_id := 1039; -- Lego Systems 1039
l_K_line_rec.ship_to_id := 1041; -- Lego Systems 1041
l_K_line_rec.accounting_rule_type := 1002; -- 12 MONTHS ADVANCE
l_K_line_rec.invoicing_rule_type := -2; -- Advance Invoice
l_K_line_rec.line_type := 'S'; -- Service ---E,U.W,S,SB,SU
l_K_line_rec.currency := 'DKK'; -- Danish Kroner
l_K_line_rec.list_price := 24;
l_K_line_rec.negotiated_amount := 24;
l_K_line_rec.customer_product_id := 10884; -- From Install Base
l_K_line_rec.customer_id := 1108; -- Lego
l_K_line_rec.start_date_active := TRUNC(sysdate);
l_K_line_rec.end_date_active := TRUNC(sysdate)+365;
l_K_line_rec.quantity := 1;
l_K_line_rec.net_amount := 24;
l_K_line_rec.srv_id := 926; -- Monthly Warranty
l_K_line_rec.srv_sdt := TRUNC(sysdate);
l_K_line_rec.srv_edt := TRUNC(sysdate)+365;


l_line_sales_crd_tbl(1).ctc_id := 100000000; -- Lange, MRS. Dagmar
l_line_sales_crd_tbl(1).sales_credit_type_id := 1; -- Quota Sales Credit
l_line_sales_crd_tbl(1).percent := 100;



l_K_covd_rec.line_number := '1.1';
l_K_covd_rec.product_sts_code := 'ACTIVE';
l_K_covd_rec.Customer_Product_Id := 10884;
l_K_covd_rec.Product_Desc := '7655A002AA;TEST11;10884';
l_K_covd_rec.Product_Start_Date := TRUNC(sysdate);
l_K_covd_rec.Product_End_Date := TRUNC(sysdate)+365;
l_K_covd_rec.Quantity := 1;
l_K_covd_rec.settlement_flag := 'N';
l_K_covd_rec.average_bill_flag := 'N';
l_K_covd_rec.Uom_Code := 'YR';
l_K_covd_rec.list_price := 24;
l_K_covd_rec.negotiated_amount := 24;
l_K_covd_rec.currency_code := 'DKK';
l_K_covd_rec.period := 'Year';



l_Strm_hdr_rec.Rule_Information1 := 'P'; -- Bill Type T E P T = Top Level
l_Strm_hdr_rec.Rule_Information_Category := 'SLH';


l_strm_level_tbl(1).Rule_Id := -2; -- Advance Invoice
l_strm_level_tbl(1).Rule_Information1 := '10'; -- Seq No
l_strm_level_tbl(1).Rule_Information2 := '25-MAR-2003'; -- Start Date
l_strm_level_tbl(1).Rule_Information3 := '12'; -- Periods
l_strm_level_tbl(1).Rule_Information4 := '1'; -- UOM/Period
l_strm_level_tbl(1).Rule_Information6 := 2; -- Amount i.e. 24 / 12
l_strm_level_tbl(1).Rule_Information_Category := 'SLL';
l_strm_level_tbl(1).Object1_Id1 := 'MTH'; -- UOM
l_strm_level_tbl(1).Jtot_Object1_Code := 'OKS_TUOM';



OKS_CONTRACTS_PUB.create_contract
(p_K_header_rec => l_K_header_rec
,p_header_contacts_tbl => l_header_contacts_tbl
,p_header_sales_crd_tbl => l_header_sales_crd_tbl
,p_header_articles_tbl => l_header_articles_tbl
,p_K_line_rec => l_K_line_rec
,p_line_contacts_tbl => l_line_contacts_tbl
,p_line_sales_crd_tbl => l_line_sales_crd_tbl
,p_K_Support_rec => l_K_Support_rec
,p_Support_contacts_tbl => l_Support_contacts_tbl
,p_Support_sales_crd_tbl => l_Support_sales_crd_tbl
,p_K_covd_rec => l_K_covd_rec
,p_price_attribs_in => l_price_attribs_in
,p_merge_rule => l_merge_rule
,p_usage_instantiate => l_usage_instantiate
,p_ib_creation => l_ib_creation
,p_Strm_hdr_rec => l_Strm_hdr_rec
,p_strm_level_tbl => l_strm_level_tbl
,x_chrid => lx_chrid
,x_return_status => lx_return_status
,x_msg_count => lx_msg_count
,x_msg_data => lx_msg_data
);

dbms_output.put_line('Contract Return Status is:'||lx_return_status);
dbms_output.put_line('Message Count is:'||to_char(lx_msg_count));

IF lx_return_status != 'S' THEN
IF NVL(lx_msg_count,0) > 0 THEN
FOR i IN 1..lx_msg_count LOOP
dbms_output.put_line('OKS API:'||fnd_msg_pub.get(i, 'F'));
END LOOP;
END IF;
RAISE exc_failed;
END IF;


l_K_line_rec.k_hdr_id := lx_chrid;
l_K_line_rec.k_line_number := '2';
l_K_line_rec.line_sts_code := 'ACTIVE';
l_K_line_rec.cust_account := '1021';
l_K_line_rec.org_id := 85; -- CANON DENMARK
l_K_line_rec.organization_id := 90; -- CANON DENMARK
l_K_line_rec.bill_to_id := 1039; -- Lego Systems 1039
l_K_line_rec.ship_to_id := 1041; -- Lego Systems 1041
l_K_line_rec.accounting_rule_type := 1002; -- 12 MONTHS ADVANCE
l_K_line_rec.invoicing_rule_type := -2; -- Advance Invoice
l_K_line_rec.line_type := 'U'; -- Usage ---E,U.W,S,SB,SU
l_K_line_rec.currency := 'DKK'; -- Danish Kroner
l_K_line_rec.list_price := 24;
l_K_line_rec.negotiated_amount := 24;
l_K_line_rec.customer_product_id := 10884; -- From Install Base
l_K_line_rec.customer_id := 1108; -- Lego
l_K_line_rec.start_date_active := TRUNC(sysdate);
l_K_line_rec.end_date_active := TRUNC(sysdate)+365;
l_K_line_rec.quantity := 1;
l_K_line_rec.net_amount := 0;
l_K_line_rec.srv_id := 374; -- BK Usage
l_K_line_rec.srv_sdt := TRUNC(sysdate);
l_K_line_rec.srv_edt := TRUNC(sysdate)+365;
l_K_line_rec.usage_type := 'FRT';
l_K_line_rec.usage_period := 'YR';


l_line_sales_crd_tbl(1).ctc_id := 100000000; -- Lange, MRS. Dagmar
l_line_sales_crd_tbl(1).sales_credit_type_id := 1; -- Quota Sales Credit
l_line_sales_crd_tbl(1).percent := 100;


OKS_CONTRACTS_PUB.Create_Service_Line
(p_k_line_rec => l_k_line_rec
,p_Contact_tbl => l_line_contacts_tbl
,p_line_sales_crd_tbl => l_line_sales_crd_tbl
,x_service_line_id => lx_service_line_id
,x_return_status => lx_return_status
,x_msg_count => lx_msg_count
,x_msg_data => lx_msg_data
);


dbms_output.put_line('Service Return Status is:'||lx_return_status);
dbms_output.put_line('Message Count is:'||to_char(lx_msg_count));

IF lx_return_status != 'S' THEN
IF NVL(lx_msg_count,0) > 0 THEN
FOR i IN 1..lx_msg_count LOOP
dbms_output.put_line('OKS API:'||fnd_msg_pub.get(i, 'F'));
END LOOP;
END IF;
RAISE exc_failed;
END IF;

l_K_covd_rec.k_id := lx_chrid;
l_K_covd_rec.Attach_2_Line_id := lx_service_line_id;
l_K_covd_rec.line_number := '2.1';
l_K_covd_rec.product_sts_code := 'ACTIVE';
l_K_covd_rec.Customer_Product_Id := 283; -- Counter Id
l_K_covd_rec.Product_Desc := '7655A002AA';
l_K_covd_rec.Product_Start_Date := TRUNC(sysdate);
l_K_covd_rec.Product_End_Date := TRUNC(sysdate)+365;
l_K_covd_rec.Quantity := 1;
l_K_covd_rec.settlement_flag := 'N';
l_K_covd_rec.average_bill_flag := 'N';
l_K_covd_rec.Uom_Code := 'YR';
l_K_covd_rec.list_price := 0;
l_K_covd_rec.negotiated_amount := 0;
l_K_covd_rec.currency_code := 'DKK';
l_K_covd_rec.period := 'Year';
l_K_covd_rec.fixed_qty := '1';



OKS_CONTRACTS_PUB.Create_Covered_Line
(p_k_covd_rec => l_k_covd_rec
,p_price_attribs => l_price_attribs_in
,x_cp_line_id => lx_cp_line_id
,x_return_status => lx_return_status
,x_msg_count => lx_msg_count
,x_msg_data => lx_msg_data
);

dbms_output.put_line('Covered Return Status is:'||lx_return_status);

IF lx_return_status != 'S' THEN
IF NVL(lx_msg_count,0) > 0 THEN
FOR i IN 1..lx_msg_count LOOP
dbms_output.put_line('OKS API:'||fnd_msg_pub.get(i, 'F'));
END LOOP;
END IF;
RAISE exc_failed;
END IF;


l_Strm_hdr_rec.Chr_Id := lx_chrid;
l_Strm_hdr_rec.Cle_Id := lx_service_line_id;
l_Strm_hdr_rec.Rule_Information1 := 'P'; -- Bill Type T E P T = Top Level
l_Strm_hdr_rec.Rule_Information_Category := 'SLH'; -- was SLL


l_strm_level_tbl(1).Rule_Id := -2; -- Advance Invoice
l_strm_level_tbl(1).Rule_Information1 := '10'; -- Seq No
l_strm_level_tbl(1).Rule_Information2 := '25-MAR-2003'; -- Start Date
l_strm_level_tbl(1).Rule_Information3 := '12'; -- Periods
l_strm_level_tbl(1).Rule_Information4 := '1'; -- UOM/Period
l_strm_level_tbl(1).Rule_Information6 := 0; -- Amount i.e. 12 / 12
l_strm_level_tbl(1).Rule_Information_Category := 'SLL'; -- Was IRE
l_strm_level_tbl(1).Object1_Id1 := 'MTH'; -- UOM
l_strm_level_tbl(1).Jtot_Object1_Code := 'OKS_TUOM';


OKS_CONTRACTS_PUB.Create_Bill_Schedule
(p_Strm_hdr_rec => l_Strm_hdr_rec
,p_strm_level_tbl => l_strm_level_tbl
,p_invoice_rule_id => -2 -- Advance Invoice
,x_return_status => lx_return_status
);

dbms_output.put_line('Bill Schedule Return Status is:'||lx_return_status);

IF lx_return_status != 'S' THEN
RAISE exc_failed;
END IF;


okc_qa_check_pub.execute_qa_check_list
(p_api_version => 1.0
,p_init_msg_list => OKC_API.G_TRUE
,x_return_status => lx_return_status
,x_msg_count => lx_msg_count
,x_msg_data => lx_msg_data
,p_qcl_id => 1 -- DEFAULT QA CHECK LIST
,p_chr_id => lx_chrid
,p_override_flag => 'N'
,x_msg_tbl => lx_msg_tbl
);

IF lx_return_status != 'S' THEN
IF NVL(lx_msg_count,0) > 0 THEN
FOR i IN 1..lx_msg_count
LOOP
dbms_output.put_line('QA API:'||fnd_msg_pub.get(i, 'F'));
END LOOP;
END IF;
ELSE
l_qa_error := FALSE;

FOR i in NVL(lx_msg_tbl.FIRST,0)..NVL(lx_msg_tbl.LAST,-1)
LOOP
IF lx_msg_tbl(i).error_status = 'E' THEN -- Only interested in Errors (E)
l_qa_error := TRUE;
dbms_output.put_line('QA Check Error Message: '||TO_CHAR(i));
dbms_output.put_line('Name: '||lx_msg_tbl(i).name);
dbms_output.put_line('Data: '||lx_msg_tbl(i).data);
dbms_output.put_line(' ');
END IF;
END LOOP;

IF l_qa_error THEN
dbms_output.put_line('Failed QA');
RAISE exc_failed;
END IF;
END IF;


okc_contract_approval_pub.k_approved
(p_contract_id => lx_chrid
,p_date_approved => sysdate
,x_return_status => lx_return_status
);
IF lx_return_status != 'S' THEN
dbms_output.put_line('Unable to Approve');
RAISE exc_failed;
END IF;

okc_contract_approval_pub.k_signed
(p_contract_id => lx_chrid
,p_date_signed => sysdate
,x_return_status => lx_return_status
);
IF lx_return_status != 'S' THEN
dbms_output.put_line('Unable to Sign');
RAISE exc_failed;
END IF;

dbms_output.put_line('Contract Approved and Signed');
commit;

SELECT contract_number
INTO l_contract_number
FROM okc_k_headers_b
WHERE id = lx_chrid;

dbms_output.put_line('Contract No:'||l_contract_number);

xxcb_c00_common_pkg.row_status('LOADED');

xxcb_c00_common_pkg.show_stats;

EXCEPTION
WHEN exc_failed THEN
dbms_output.put_line('Rolled back to savepoint');
rollback to savepoint start_contract;
xxcb_c00_common_pkg.row_status('REJECTED');

WHEN OTHERS THEN
dbms_output.put_line('When Others:'||SQLERRM);
END;

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 process

6) Track the ASSIGNMENT_ACTION_ID and PAYROLL_ACTION_ID navigating to
View-->Payroll Process Results/Assignment Process Results

7) Verify that the costing details are created in PAY_COSTS table.
SELECT * FROM PAY_COSTS WHERE ASSIGNMENT_ACTION_ID = &assignment_action_id;
Also Refer Note:294553.1

8) Run the Payroll transfer to GL process

9) Verifity that the process has created data in gl_interfaces
select * from gl_interface
where set_of_books_id = &set_of_books_id
and group_id =&enter_group_id

10) Import the same in GL by navigating to
Journal-->Import.

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 query:

select hdr.contract_number,hdr.contract_number_modifier
from oe_order_headers_all oe,
okc_k_rel_objs rel,
okc_k_headers_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 = ''

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

Contracts Lines

OKE_IMPORT_CONTRACT_PUB.create_contract_line

Contract Deliverables
OKE_IMPORT_CONTRACT_PUB.create_deliverable

Contract Billing events
OKE_DELIVERABLE_BILLING_PVT.create_billing_event

Contract Funding and Fund Allocations
OKE_FUNDING_PUB.create_funding and OKE_ALLOCATION_PVT.add_allocation

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, p_header_articles_tbl, x_chrid, l_return_status, l_msg_count, l_msg_data);

This API can be used to pass in various header variables like the contract number,short description,start date, end date of the contract, the customer id,bill to and ship to locations of he customer, the status of the contract('Entered','Active'), price list used, invoicing rule type,accounting rule type,contract renewal type etc..

Creation of contract lines

oks_contracts_pub.create_service_line(l_k_line_rec, p_line_contact_tbl, p_line_sales_crd_tbl, x_service_line_id, l_return_status, l_msg_count, l_msg_data);

Creation of contract sub-lines

oks_contracts_pub.create_covered_line(p_k_covd_rec, p_price_attribs, x_cp_line_id, l_return_status, l_msg_count, l_msg_data);

Creation of contract billing schedule

oks_contracts_pub.create_bill_schedule(l_billing_sch, l_strm_level_tbl, l_invoice_rule_id, l_return_status);

Good luck.

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 salary in past when you dont have employee record, it can not find the employee data and these mandatory columns as well. So you need to have same cutoff date for employee history and salary history. And you need to make sure that the employee payroll and other mandatory parameters are available while creating salary.