Dilli ERP Notes

Thursday, 20 February 2014

FA Details Query

/* Formatted on 20-02-2014 04:08:22 PM (QP5 v5.163.1008.3004) */
SELECT fp.period_name,
adt.asset_number,
adt.asset_type,
adt.current_units,
adt.tag_number,
-- LTRIM (RTRIM (cat.segment1))
-- || '-'
-- || LTRIM (RTRIM (cat.segment2))
-- || '-'
-- || LTRIM (RTRIM (cat.segment3))
-- category,
LTRIM (RTRIM (cat.segment1)) major,
LTRIM (RTRIM (cat.segment2)) miner,
LTRIM (RTRIM (cat.segment3)) sub_category,
/* (SELECT x2.description
FROM fnd_flex_values_vl x2, fa_categories_b y2
WHERE x2.flex_value_set_id = 1015080
AND x2.flex_value = y2.segment3
AND y2.category_id = cat.category_id)
Asset_Category_Description,*/
xx_fa_desc (cat.segment3) Asset_Category_Description,
bks.date_placed_in_service,
bks.life_in_months,
bks.original_cost,
adt.description,
dh.location_id,
fl.segment1,
adt.attribute1 property_tax_code,
bks.deprn_method_code,
ROUND (
SUM (
DECODE (bks.period_counter_fully_retired, '', bks.cost, 0)
* dh.units_assigned
/ ah.units),
2)
COST,
bks.original_cost,
ROUND (SUM (NVL (dn.deprn_amount, 0) * dh.units_assigned / ah.units),
2)
deprn,
ROUND (SUM (NVL (dn.deprn_reserve, 0) * dh.units_assigned / ah.units),
2)
deprn_reserve,
ROUND (SUM (NVL (dn.ytd_deprn, 0) * dh.units_assigned / ah.units), 2)
YTD_DEP,
ROUND (
SUM (
DECODE (bks.period_counter_fully_retired,
'', (bks.cost - dn.deprn_reserve),
0)
* dh.units_assigned
/ ah.units),
2)
c_nbv,
-- ROUND (SUM (NVL (dn.deprn_amount, 0) * dh.units_assigned / ah.units),
-- 2)
-- - (NVL (dn.deprn_adjustment_amount, 0)
-- - NVL (dn.bonus_deprn_adjustment_amount, 0))
-- - NVL (dn.bonus_deprn_amount, 0) ptd,
ROUND (
SUM (
dn.deprn_amount
- (NVL (dn.deprn_adjustment_amount, 0)
- NVL (dn.bonus_deprn_adjustment_amount, 0))
- NVL (dn.bonus_deprn_amount, 0)),
2)
ptd,
bks.date_placed_in_service,
--fp.period_name,
fcb.asset_cost_acct asset_cost_acct,
fcb.deprn_expense_acct deprn_acct,
fcb.deprn_reserve_acct acc_deprn_acct,
fbc.attribute1,
hou.name,
dhcc.segment4,
apps.gl_flexfields_pkg.get_description_sql (dhcc.chart_of_accounts_id,
4,
dhcc.segment4)
segment3_des,
NULL loc_seg2_desc,
NULL loc_seg3_desc
FROM fa_distribution_history dh,
fa_asset_history ah,
fa_additions adt,
fa_categories_b cat,
fa_category_books fcb,
fa_books bks,
gl_code_combinations dhcc,
fa_deprn_summary dn,
fa_deprn_periods fp,
fa_book_controls fbc,
fa_locations fl,
hr_operating_units hou
WHERE fp.book_type_code = 'PIUDCL_NAG_FAB'
AND dn.book_type_code = 'PIUDCL_NAG_FAB'
AND dn.period_counter =
(SELECT dp.period_counter
FROM fa_deprn_periods dp
WHERE dp.book_type_code = 'PIUDCL_NAG_FAB'
AND dp.period_counter =
(SELECT MAX (dpz.period_counter)
FROM fa_deprn_summary dsz,
fa_deprn_periods dpz
WHERE dpz.book_type_code = 'PIUDCL_NAG_FAB'
AND dpz.period_counter <=
fp.period_counter
AND dsz.book_type_code =
'PIUDCL_NAG_FAB'
AND dsz.period_counter =
dpz.period_counter
AND dsz.asset_id = dn.asset_id))
AND bks.book_type_code = 'PIUDCL_NAG_FAB'
AND bks.asset_id = dn.asset_id
AND NVL (bks.date_ineffective, SYSDATE) >=
TO_DATE (
TO_CHAR (NVL (fp.period_close_date, SYSDATE),
'DD-MM-YYYY HH24:MI:SS'),
'DD-MM-YYYY HH24:MI:SS')
AND bks.date_effective <
TO_DATE (
TO_CHAR (NVL (fp.period_close_date, SYSDATE),
'DD-MM-YYYY HH24:MI:SS'),
'DD-MM-YYYY HH24:MI:SS')
AND NVL (bks.period_counter_fully_retired, fp.period_counter) IN
(SELECT dpy.period_counter
FROM fa_deprn_periods dpy
WHERE dpy.book_type_code = 'PIUDCL_NAG_FAB'
AND dpy.fiscal_year = fp.fiscal_year)
AND adt.asset_id = dn.asset_id
AND adt.ASSET_CATEGORY_ID = cat.category_id
AND cat.category_id = fcb.category_id
AND adt.asset_id = dh.asset_id
AND fcb.book_type_code = 'PIUDCL_NAG_FAB'
AND dh.book_type_code = 'PIUDCL_NAG_FAB'
--AND fp.period_name = :from_period
AND dh.location_id = fl.location_id
--and fl.segment1 not in('None')
AND bks.book_type_code = fbc.book_type_code
AND fbc.book_type_code = 'PIUDCL_NAG_FAB'
--and :to_period
AND NVL (dh.date_ineffective, SYSDATE) >=
TO_DATE (
TO_CHAR (NVL (fp.period_close_date, SYSDATE),
'DD-MM-YYYY HH24:MI:SS'),
'DD-MM-YYYY HH24:MI:SS')
AND dh.date_effective <
TO_DATE (
TO_CHAR (NVL (fp.period_close_date, SYSDATE), 'DD-MM-YYYY
HH24:MI:SS'),
'DD-MM-YYYY HH24:MI:SS')
AND dhcc.code_combination_id(+) = dh.code_combination_id
AND ah.asset_id = adt.asset_id
AND NVL (ah.date_ineffective, SYSDATE) >=
TO_DATE (
TO_CHAR (NVL (fp.period_close_date, SYSDATE), 'DD-MM-YYYY
HH24:MI:SS'),
'DD-MM-YYYY HH24:MI:SS')
AND ah.date_effective <
TO_DATE (
TO_CHAR (NVL (fp.period_close_date, SYSDATE), 'DD-MM-YYYY
HH24:MI:SS'),
'DD-MM-YYYY HH24:MI:SS')
AND fbc.attribute1 = hou.name
AND dh.date_ineffective IS NULL
AND bks.date_ineffective IS NULL
--and hou.name=:p_operating_name
-- and bks.book_type_code=:p_book_type_code
--AND bks.date_placed_in_service between nvl(:p_from_date,(bks.date_placed_in_service)) and nvl(:p_to_date,(bks.date_placed_in_service))
GROUP BY fp.period_name,
dhcc.segment2,
dhcc.segment4,
dhcc.segment3,
adt.asset_number,
adt.tag_number,
LTRIM (RTRIM (cat.segment1))
|| '-'
|| LTRIM (RTRIM (cat.segment2))
|| '-'
|| LTRIM (RTRIM (cat.segment3)),
adt.description,
bks.date_placed_in_service,
bks.original_cost,
dh.location_id,
adt.context,
adt.attribute1,
fcb.asset_cost_acct,
fcb.deprn_reserve_acct,
fbc.attribute1,
fl.segment1,
dn.deprn_adjustment_amount,
dn.bonus_deprn_adjustment_amount,
dn.bonus_deprn_amount,
bks.deprn_method_code,
cat.category_id,
dn.deprn_amount,
dhcc.chart_of_accounts_id,
fcb.deprn_expense_acct,
adt.asset_type,
adt.current_units,
cat.segment1,
cat.segment2,
cat.segment3,
bks.original_cost,
bks.life_in_months,
hou.name
--fp.period_name
ORDER BY asset_number;

Function Query
================

CREATE OR REPLACE FUNCTION APPS.xx_fa_desc (
p_segment3 varchar2
)
RETURN varchar2
IS
v_desc varchar2(150);
BEGIN

dbms_output.put_line (p_segment3 || ' SEG3');

BEGIN

SELECT RTRIM
(XMLAGG (XMLELEMENT (E, DESCRIPTION || ',')).EXTRACT
('//text()'),
','
)DESCRIPTION into v_desc
FROM (
SELECT distinct x2.description
FROM fnd_flex_values_vl x2 , fa_categories_b y2
WHERE x2.flex_value_set_id = 1015080
and x2.flex_value = y2.segment3
and x2.flex_value = p_segment3);

EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_desc := null;
END;

RETURN v_desc;
END;
/

Thursday, 13 February 2014

Export/Import data from/to Oracle/Excel table using toad

Export/Import data from/to Oracle/Excel table using toad

Export data from oracle table to xl using toad
1. Fire up Toad and connect to database
2. Execute command in editor & press export dataset (green button) eg
select * from schema.table;
3. Press ok generates xl report of this table (multiple export formats available)

Import data from xl to oracle table using toad
1. Database-->Import-->Table Data
2. Select the proper schema and Table name where data should be inserted
3. Click "Execute Wizard" and specify the type of file to be imported
4. Select "Excel File (.xls)" and click next
5. Give the path of the file, and again click next
6. Finally click execute.

Wednesday, 5 February 2014

FA Depreciation Query

FA Depreciation Query

SELECT fp.period_name,
dhcc.segment2 business_unit,
dhcc.segment3 account,
dhcc.segment4 dept,
adt.asset_number,
adt.tag_number,
LTRIM (RTRIM (cat.segment1))
|| '-'
|| LTRIM (RTRIM (cat.segment2))
|| '-'
|| LTRIM (RTRIM (cat.segment3))
category,
bks.date_placed_in_service acquistion_date,
bks.original_cost,
adt.description,
dh.location_id,
adt.context subject_to_property_tax,
adt.attribute1 property_tax_code,
ROUND (
SUM (
DECODE (bks.period_counter_fully_retired, '', bks.cost, 0)
* dh.units_assigned
/ ah.units),
2)
COST,
ROUND (SUM (NVL (dn.deprn_amount, 0) * dh.units_assigned / ah.units),
2)
deprn,
ROUND (SUM (NVL (dn.deprn_reserve, 0) * dh.units_assigned / ah.units),
2)
deprn_reserve,
ROUND (SUM (NVL (dn.ytd_deprn, 0) * dh.units_assigned / ah.units), 2)
YTD_DEP,
ROUND (
SUM (
DECODE (bks.period_counter_fully_retired,
'', (bks.cost - dn.deprn_reserve),
0)
* dh.units_assigned
/ ah.units),
2)
c_nbv
FROM fa_distribution_history dh,
fa_asset_history ah,
fa_additions adt,
fa_categories_b cat,
fa_books bks,
gl_code_combinations dhcc,
fa_deprn_summary dn,
fa_deprn_periods fp
WHERE fp.book_type_code = 'PIUDCL_NAG_FAB' AND dn.book_type_code = 'PIUDCL_NAG_FAB'
AND dn.period_counter =
(SELECT dp.period_counter
FROM fa_deprn_periods dp
WHERE dp.book_type_code = 'PIUDCL_NAG_FAB'
AND dp.period_counter =
(SELECT MAX (dpz.period_counter)
FROM fa_deprn_summary dsz,
fa_deprn_periods dpz
WHERE dpz.book_type_code = 'PIUDCL_NAG_FAB'
AND dpz.period_counter <=
fp.period_counter
AND dsz.book_type_code = 'PIUDCL_NAG_FAB'
AND dsz.period_counter =
dpz.period_counter
AND dsz.asset_id = dn.asset_id))
AND bks.book_type_code = 'PIUDCL_NAG_FAB'
AND bks.asset_id = dn.asset_id
AND NVL (bks.date_ineffective, SYSDATE) >=
TO_DATE (
TO_CHAR (NVL (fp.period_close_date, SYSDATE),
'DD-MM-YYYY HH24:MI:SS'),
'DD-MM-YYYY HH24:MI:SS')
AND bks.date_effective <=
TO_DATE (
TO_CHAR (NVL (fp.period_close_date, SYSDATE),
'DD-MM-YYYY HH24:MI:SS'),
'DD-MM-YYYY HH24:MI:SS')
AND NVL (bks.period_counter_fully_retired, fp.period_counter) IN
(SELECT dpy.period_counter
FROM fa_deprn_periods dpy
WHERE dpy.book_type_code = 'PIUDCL_NAG_FAB'
AND dpy.fiscal_year = fp.fiscal_year)
AND adt.asset_id = dn.asset_id
AND adt.ASSET_CATEGORY_ID = cat.category_id
AND adt.asset_id = dh.asset_id
AND dh.book_type_code = 'PIUDCL_NAG_FAB'
AND NVL (dh.date_ineffective, SYSDATE) >=
TO_DATE (
TO_CHAR (NVL (fp.period_close_date, SYSDATE),
'DD-MM-YYYY HH24:MI:SS'),
'DD-MM-YYYY HH24:MI:SS')
AND dh.date_effective <=
TO_DATE (
TO_CHAR (NVL (fp.period_close_date, SYSDATE), 'DD-MM-YYYY
HH24:MI:SS'),
'DD-MM-YYYY HH24:MI:SS')
AND dhcc.code_combination_id(+) = dh.code_combination_id
AND ah.asset_id = adt.asset_id
AND NVL (ah.date_ineffective, SYSDATE) >=
TO_DATE (
TO_CHAR (NVL (fp.period_close_date, SYSDATE), 'DD-MM-YYYY
HH24:MI:SS'),
'DD-MM-YYYY HH24:MI:SS')
AND ah.date_effective <=
TO_DATE (
TO_CHAR (NVL (fp.period_close_date, SYSDATE), 'DD-MM-YYYY
HH24:MI:SS'),
'DD-MM-YYYY HH24:MI:SS')
GROUP BY fp.period_name,
dhcc.segment2,
dhcc.segment4,
dhcc.segment3,
adt.asset_number,
adt.tag_number,
LTRIM (RTRIM (cat.segment1))
|| '-'
|| LTRIM (RTRIM (cat.segment2))
|| '-'
|| LTRIM (RTRIM (cat.segment3)),
adt.description,
bks.date_placed_in_service,
bks.original_cost,
dh.location_id,
adt.context,
adt.attribute1;

Saturday, 25 January 2014

CONVERT MONTH-YEAR FORM TO DATE FORMAT

CONVERT MONTH-YEAR FORM TO DATE FORMAT

select trunc(trunc(to_date(:p_from_month,'Mon-YYYY'),'MM'),'MM') ,last_day(to_date(:p_to_month,'Mon-YYYY')) from dual

FNDLOAD Scripts

FNDLOAD Scripts

Code and AOL Migration Scripts

Messages:

—–DOWNLOAD—–
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct
XX_MESSAGE_NAME.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME=’ICX’ MESSAGE_NAME=’XX_MESSAGE_NAME’

Or can download all the messages within an application

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct
XX_MESSAGE_NAME.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME=’ICX’

—–UPLOAD——-
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afmdmsg.lct XX_MESSAGE_NAME.ldt

Lookups:
—–DOWNLOAD—–
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct XX_LKP_NAME.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME=’ICX’ LOOKUP_TYPE=’XX_LKP_NAME’

—–UPLOAD——-
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct XX_LKP_NAME.ldt

Value Set:
—–DOWNLOAD—–
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XX_VALUE_SET_NAME.ldt VALUE_SET FLEX_VALUE_SET_NAME=’XX_VALUE_SET_NAME’

If value set has values then,

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XX_VALUE_SET_NAME.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME=’XX_VALUE_SET_NAME’

—–UPLOAD——-
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct XX_VALUE_SET_NAME.ldt

Form Function:
—–DOWNLOAD—–
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XX_FORM_FUNCTION_NAME.ldt FUNCTION FUNCTION_NAME=’XX_FORM_FUNCTION_NAME’

—–UPLOAD——-
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct XX_FORM_FUNCTION_NAME.ldt

Menu:
—–DOWNLOAD—–
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XX_MENU_NAME.ldt MENU MENU_NAME=’XX_MENU_NAME’

—–UPLOAD——-
FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afsload.lct XX_MENU_NAME.ldt

Responsibility:
—–DOWNLOAD—–
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct XX_RESP_KEY.ldt FND_RESPONSIBILITY RESP_KEY=’XX_RESP_KEY’

—–UPLOAD——-
FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct XX_RESP_KEY.ldt

Profile Option Definition:
—–DOWNLOAD—–
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct XX_PROFILE_NAME.ldt PROFILE PROFILE_NAME=’XX_PROFILE_NAME’ APPLICATION_SHORT_NAME=’ICX’

—–UPLOAD——-
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct XX_PROFILE_NAME.ldt

Request Group:
—–DOWNLOAD—–
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct XX_REPORT_GROUP_NAME.ldt REQUEST_GROUP REQUEST_GROUP_NAME=’XX_REPORT_GROUP_NAME’ APPLICATION_SHORT_NAME=’PO’

—–UPLOAD——-
FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct XX_REPORT_GROUP_NAME.ldt

Concurrent program:
—–DOWNLOAD—–
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_CONC_PRG_NAME.ldt PROGRAM APPLICATION_SHORT_NAME=’PO’ CONCURRENT_PROGRAM_NAME=’XX_CONC_PRG_NAME’

—–UPLOAD——-
FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_CONC_PRG_NAME.ldt

Request Sets:
—–DOWNLOAD—–
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XX_REQ_SET_NAME.ldt REQ_SET REQUEST_SET_NAME=’XX_REQ_SET_NAME’

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XX_REQ_SET_NAME_LINK.ldt REQ_SET_LINKS REQUEST_SET_NAME=’XX_REQ_SET_NAME’

—–UPLOAD——-
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct XX_REQ_SET_NAME.ldt

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

Alert:
—–DOWNLOAD—–
FNDLOAD apps/apps 0 Y DOWNLOAD $ALR_TOP/patch/115/import/alr.lct XX_ALERT_NAME.ldt ALR_ALERTS APPLICATION_SHORT_NAME=’PO’ ALERT_NAME=’XX_ALERT_NAME’

—–UPLOAD——-
FNDLOAD apps/apps 0 Y UPLOAD $ALR_TOP/patch/115/import/alr.lct XX_ALERT_NAME.ldt CUSTOM_MODE=FORCE

Folders:
—–DOWNLOAD—–
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/fndfold.lct XX_FOLDER_NAME.ldt FND_FOLDERS NAME=’XX_FOLDER_NAME’

Or can download all the folders

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/fndfold.lct XX_ALL_FOLDER.ldt FND_FOLDERS

—–UPLOAD——-
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/fndfold.lct XX_FOLDER_NAME.ldt CUSTOM_MODE=FORCE

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

Examples
===========

uploaded

cd $XXESSEL_TOP

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct ESSELACL.ldt

data definition uploaded

cd $XXESSEL_TOP

FNDLOAD apps/apps 0 Y UPLOAD $XDO_TOP/patch/115/import/xdotmpl.lct ESSELACL_TMP.ldt

download concurrent :

cd $XXESSEL_TOP

FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct ESSELACL.ldt PROGRAM APPLICATION_SHORT_NAME="XXESSEL" CONCURRENT_PROGRAM_NAME="ESSELACL"

datadefinition download

cd $XXESSEL_TOP

FNDLOAD apps/apps 0 Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lct ESSELACL_TMP.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME=XXESSEL DATA_SOURCE_CODE=ESSELACL

Friday, 20 December 2013

How to stop the PO Receipt Traveler report for specific organization using Form personalization

How to stop the PO Receipt Traveler report for specific organization using Form personalization:

Stop Receipt Traveler – FLG Form Personalization

FORM NAME: RCVRCERC

Trigger Event : WHEN-NEW-BLOCK-INSTANCE

Condition : fnd_profile.value(‘MFG_ORGANIZATION_ID’)=306

Target object: PO_STARTUP_VALUES.PRINT_TRAVELLER_VALUE

Property Name: Value

Value: N

Note : Here if flag value is reset to 'N' then Receipt Traveler Report program never call .