Usefull Links

Getting the difference between Dates
**************************************
http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551242712657900129

Interfaces and Conversions in Oracle Applications
***************************************************
http://www.erpschools.com/apps/oracle-applications/Articles/General/Interfaces-and-Conversions-in-Oracle-Applications/index.aspx

Bom Interface:
******************
http://www.erpstuff.com/forums/topic.asp?TOPIC_ID=1083

All PO Queries:
*****************
http://oracleerp4u.blogspot.com/2010_09_01_archive.html
***********************
http://oracleerp4u.blogspot.com/

Below Link is used for Training in all modules with screenshot & Apps Technical also.
*************************************************************************************
http://confluentminds.com/Trainings/

Debugging Concurrent Programs

Concurrent Programs

For concurrent programs you can place in Log file / output file / fnd_log_messages table

For Log File
FND_FILE.PUT_LINE(FND_FILE.LOG,'your log statement');

For Output File
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'your output statement');

For Tracing it in a table FND_LOG_MESSAGES

fnd_log.string(log_level => fnd_log.level_statement
,module => 'custom module'
,message => 'you log message here');

CUSTOM FORM DEVELOPMENT

On the CLIENT machine create a FOLDER as say: c:\custom_test

In custom_test folder creates 2 folders forms, resource

Copy TEMPLATE.fmb, APPSTAND.fmb, .pll files to CLIENT

• Copy TEMPLATE.fmb , APPSTAND.fmb from AU_TOP/forms/US to c:\custom_test\ forms directory copy all .pll files from /Applvis/visappl/au/11.5.0/resource to c:\custom_test\resource using ftp
• On windows go to command prompt
• Cd c:\custom_test\forms
• ftp cloneserver
• username: applmgr password: applmgr
• now you are at ftp prompt
• bin
• prompt
• cd visappl/au/11.5.0/forms/US ( here apparently cd $AU_TOP does not work)
• get TEMPLATE.fmb
• (file copied)
• get APPSTAND.fmb
• (file copied)
• lcd ./resource (check this. Basically you need to be in c:\custom_test\resource. You can go to that directory and then run ftp)
• cd visappl/au/11.5.0/resource
• mget *.pll
• ( now all .pll files are copied to c:custom_test/resource)

SET env variable FORMS60_PATH through regedit

• Regedit/HKEY_LOCAL_MACHINE/software/oracle
• Double click FORMS60_PATH
• At the end of existing value data add: ;c:\custom_test\froms;c:\custom_test\resource

Now open TEMPLATE.fmb and make the following changes

• DELETE BLOCKNAME &DETAIL BLOCK FROM DATABLOCK,DELETE BLOCKNAME FROM CANVAS AND WINDOW
• Create a window NEW_WIN, canvas NEW_CAN
• Create a new block based on the table you created in your custom schema
• In pre-form trigger: app_windows.set_window_position(‘NEW_WIN’)…….
• In program units open custom_package.AP_cutom_pacakge body
• Change : if (wind=’NEW_WIN’);
• Template name = TEST_FORM
• Save as TEST_FROM to c:\custom_test\forms
• (????????SET THE WINDOW NAME AS U HAVE CREATED NEW WINDOW IN PRE-FORM TRIGGER BY BLOCKNAME?????)

DEPLOY the FORM (upload it to AU_TOP/forms/US)

• Go to command prompt (on client)
• cd c:\cutom_test\forms
• ftp cloneserver
• cd visappl/au/11.5.0/froms/US
• bin
• prompt
• put TEST_FORM.fmb


Changing ORACLE_HOME (/Visdb/visdb/9.2.0 to /Applvis/visora/8.0.6)

• thru putty login as applmgr
• pwd: /Applvis
• echo $ORACLE_HOME: shows /Visdb/visdb/9.2.0
• cd visora
• cd 8.0.6
• . VIS_cloneserver.env (this changes ORACLE_HOME apparently based on pwd?)
• echo $ORACLE_HOME: shows /Applvis/visora/8.0.6
• now ORACLE_HOME is 8.0.6 (forms/reports home)
• pwd : gives /Applvis/visora/8.0.6

COMPILE and generate FMX

• (now you are in /Applvis/visora/8.0.6 directory and ORACLE_HOME is set to /Applvis/visora/8.0.6)
• pwd: gives /Applvis/visora/8.0.6
• f60gen module=$AU_TOP/forms/US/TEST_FORMS.fmb module_type=form user=apps output_file=$TEST_TOP/forms/US/TEST_FORM.fmx compile_all=special batch=no
• this generates TEST_FORM.fmx and puts in TEST_TOP/forms/US

FORM REGISTRATION

• Login to applications with application developer responsibility
• Application/form
• Enter the following details
o Form: the fmx name (TEST_FORM)
o Application: Oracle Receivables (as per Amer) –give appropriate name based the intended use of this form
o user form name: TEST_FORM_U (this will appear in LOV)
o SAVE

Attach the FORM to FUNCTION
(Create a new function)
Application/function
Enter the following details
o Function: TEST_FUNCT
o User function name: TEST FUNCTION
o Form: TEST_FORM (previously registered)
o SAVE

Attach FUNCTION to MENU
Application/menu
• Enter the following details
o Menu: test_menu
o User menu name: test menu
o Seq: 1
o Prompt: test form
o Function: TEST_FUNCT( previously deined)
o SAVE

Attach MENU to RESPONSIBILITY

Attach RESPONSIBILITY to USER

Login as the new USER

CTL File Example (PO Conversion)

options (skip=1)
LOAD DATA
INFILE '$XBOL_TOP/bin/xxalv_po_conv.csv'
BADFILE '$XBOL_TOP/bin/xxalv_po_conv.bad'
REPLACE
INTO TABLE bolinf.xxalv_po_legacy_data
FIELDS TERMINATED BY "," optionally enclosed by '"' trailing nullcols
(po_group "TRIM(:po_group)",
po_num "TRIM(:po_num)",
line_type "TRIM(:line_type)",
vendor_num "TRIM(:vendor_num)",
vendor_suffix "TRIM(:vendor_suffix)",
buyr "TRIM(:buyr)",
creation_date "TRIM(:creation_date)",
ctry "TRIM(:ctry)",
pp_cl "TRIM(:pp_cl)",
trms "TRIM(:trms)",
sh_to "TRIM(:sh_to)",
byr_note "TRIM(:byr_note)",
conf_no "TRIM(:conf_no)",
conf_name "TRIM(:conf_name)",
fobc "TRIM(:fobc)",
sh_via "TRIM(:sh_via)",
ctg "TRIM(:ctg)",
nu1 "TRIM(:nu1)",
sh_via_name "TRIM(:sh_via_name)",
note1 "TRIM(:note1)",
note2 "TRIM(:note2)",
note3 "TRIM(:note3)",
note4 "TRIM(:note4)",
hdr_sts "TRIM(:hdr_sts)",
sts_date "TRIM(:sts_date)",
co_rc "TRIM(:co_rc)",
co_no,
co_date "TRIM(:co_date)",
line_no,
order_qty,
unit_price,
part "TRIM(:part)",
lin_cmt "TRIM(:lin_cmt)",
nu2 "TRIM(:nu2)",
req_no "TRIM(:req_no)",
req_date "TRIM(:req_date)",
equipment_no "TRIM(:equipment_no)",
account "TRIM(:account)",
dept "TRIM(:dept)",
operation "TRIM(:operation)",
mfg_part "TRIM(:mfg_part)",
recd_qty,
recd_date "TRIM(:recd_date)",
line_sts "TRIM(:line_sts)",
line_sts_date "TRIM(:line_sts_date)",
co_ct "TRIM(:co_ct)",
deliver_to "TRIM(:deliver_to)",
invoice_qty,
invoice_date "TRIM(:invoice_date)",
txbl "TRIM(:txbl)",
invoice_amt,
ext_dsc "TRIM(:ext_dsc)",
multi "TRIM(:multi)",
comp "TRIM(:comp)",
wrk_ord "TRIM(:wrk_ord)",
uom_code "TRIM(:uom_code)",
opn_clo "TRIM(:opn_clo)",
opn_rcv "TRIM(:opn_rcv)",
opn_inv "TRIM(:opn_inv)",
opn_today "TRIM(:opn_today)",
rcv_today "TRIM(:rcv_today)",
nu3 "TRIM(:nu3)",
nu4 "TRIM(:nu4)",
nu5 "TRIM(:nu5)",
nu6 "TRIM(:nu6)",
nu7 "TRIM(:nu7)",
vendor_name "TRIM(:vendor_name)",
buyer_name "TRIM(:buyer_name)",
need_date "TRIM(:need_date)",
country_name "TRIM(:country_name)",
freight_term "TRIM(:freight_term)",
ship_to "TRIM(:ship_to)",
fob_name "TRIM(:fob_name)",
ship_via "TRIM(:ship_via)",
change_order "TRIM(:change_order)",
payment_term "TRIM(:payment_term)",
po_status "TRIM(:po_status)",
cap_proj_no "TRIM(:cap_proj_no)",
item_description1 "TRIM(:item_description1)",
item_description2 "TRIM(:item_description2)",
special_instruction1 "TRIM(:special_instruction1)",
special_instruction2 "TRIM(:special_instruction2)",
special_instruction3 "TRIM(:special_instruction3)",
special_instruction4 "TRIM(:special_instruction4)",
line_ext_desc1 "TRIM(:line_ext_desc1)",
line_ext_desc2 "TRIM(:line_ext_desc2)",
line_ext_desc3 "TRIM(:line_ext_desc3)",
line_ext_desc4 "TRIM(:line_ext_desc4)",
line_ext_desc5 "TRIM(:line_ext_desc5)",
line_ext_desc6 "TRIM(:line_ext_desc6)",
open_qty "TRIM (:open_qty)")

Short text attachments

SELECT st.short_text INTO l_short_text
FROM fnd_attachment_functions fndattfn,
fnd_doc_category_usages fndcatusg,
fnd_documents_vl fnddoc,
fnd_attached_documents fndattdoc,
fnd_documents_short_text st,
fnd_document_categories_tl fdtl
--fnd_attached_docs_form_vl fadf
WHERE fndattfn.attachment_function_id = fndcatusg.attachment_function_id
AND fndcatusg.category_id = fnddoc.category_id
AND fnddoc.document_id = fndattdoc.document_id
AND fndattfn.function_name = 'OEXOETEL'
--AND fndattdoc.entity_name = 'OE_ORDER_HEADERS'
AND fnddoc.media_id = st.media_id(+)
AND fdtl.category_id = fndcatusg.category_id
--AND fdtl.user_name IN ('Sales Order Acknowledgement - Header')
AND fdtl.user_name = :P_FOOTER_ATTACH
AND fndattdoc.pk1_value = :OE_ORDER_HEADERS_ALL.HEADER_ID
/*AND fadf.pk1_value = fndattdoc.pk1_value
AND fndattfn.function_name = fadf.function_name
AND fadf.document_id = fnddoc.document_id
AND fndcatusg.format = 'F'
AND fadf.function_type = 'R'*/
AND fdtl.LANGUAGE = USERENV ('LANG');
*****************************************************************
Below query is Tuned
*****************************************************************

select
-- pk1_value ftr_header_id,
pk1_value actheadnote_id,
fdst.short_text actheadnote,
fdlt.long_text actheadnote_long,
fdc.user_name ftr_headuse_ord,
TO_NUMBER ( fdc.attribute1 ) ftr_headuseseq_ord,
datatype_id ftr_note_datatype_id,
fd.datatype_id actheadnote_datatype_id
from fnd_attachment_functions faf,
fnd_document_categories_vl fdc,
fnd_doc_category_usages fdcu,
fnd_documents_vl fd,
fnd_attached_documents fad,
fnd_documents_long_text fdlt,
fnd_documents_short_text fdst
where faf.attachment_function_id = fdcu.attachment_function_id
and fdcu.category_id = fd.category_id
and fdc.category_id = fd.category_id
and fd.document_id = fad.document_id
and faf.function_name = :P_FUNCTION_NAME
--and faf.function_name = 'OEXOEORD'
and fad.entity_name = 'OE_ORDER_HEADERS'
and fd.datatype_id in (1,2)
and fd.media_id=fdst.media_id(+)
and fd.media_id=fdlt.media_id(+)
and fdc.user_name <> 'AU Header Comments' ---- to hide attachments URL from XMl reports
and fdc.user_name <> 'Internal'
and faf.function_type='R'
AND FDCU.format = 'F'
order by seq_num

To giving URL link for the Attachments

function CF_attach return Char is
l_gfm_id NUMBER;
gfm_agent VARCHAR2 (255);
l_url VARCHAR2 (1000);
MED_ID number;
begin

SELECT DISTINCT DT.MEDIA_ID INTO MED_ID
FROM FND_ATTACHED_DOCUMENTS AD,
FND_DOCUMENTS_TL DT
WHERE((entity_name = 'PO_HEAD' and pk1_value = :POH_PO_HEADER_ID and
pk2_value = '1') OR
(entity_name = 'PO_HEADERS' and pk1_value = :POH_PO_HEADER_ID) OR
(entity_name = 'PO_VENDORS' and pk1_value = :POH_VENDOR_ID))
AND AD.DOCUMENT_ID=DT.DOCUMENT_ID
AND DT.LANGUAGE = USERENV('LANG')
AND ROWNUM=1;

gfm_agent := fnd_web_config.gfm_agent;
--DBMS_OUTPUT.put_line (gfm_agent);
l_gfm_id := MED_ID; --media id
--DBMS_OUTPUT.put_line (l_gfm_id); -- MEDIA_ID
l_url := fnd_gfm.construct_download_url (gfm_agent, l_gfm_id, FALSE);
--DBMS_OUTPUT.put_line (l_url);

return l_url;

EXCEPTION WHEN OTHERS THEN
RETURN(NULL);
end;

Print Bill To Customer address from Order Management

SELECT hl.address1, hl.address2, hl.address3,
hl.address4, hl.city, hl.postal_code
FROM hz_locations hl,
hz_party_sites hps,
hz_parties hp,
hz_cust_accounts hca,
hz_cust_acct_sites_all has,
hz_cust_site_uses_all hcs
WHERE hcs.site_use_code = 'BILL_TO'
AND has.cust_acct_site_id = hcs.cust_acct_site_id
AND has.party_site_id = hps.party_site_id
AND hca.cust_account_id = has.cust_account_id
AND hp.party_id = hca.party_id
AND hps.party_id = hp.party_id
AND hl.location_id = hps.location_id
AND hca.cust_account_id = oe_order_headers_all.sold_to_org_id
AND hcs.site_use_id = oe_order_headers_all.invoice_to_org_id;

Print Ship To Customer Address from Order Management

SELECT hl.address1, hl.address2, hl.address3,
hl.address4, hl.city, hl.postal_code,hl.STATE,hp.party_name
FROM hz_locations hl,
hz_party_sites hps,
hz_parties hp,
hz_cust_accounts hca,
hz_cust_acct_sites_all has,
hz_cust_site_uses_all hcs
WHERE hcs.site_use_code = 'SHIP_TO'
AND has.cust_acct_site_id = hcs.cust_acct_site_id
AND has.party_site_id = hps.party_site_id
AND hca.cust_account_id = has.cust_account_id
AND hp.party_id = hca.party_id
AND hps.party_id = hp.party_id
AND hl.location_id = hps.location_id
AND hca.cust_account_id = oe_order_headers_all.sold_to_org_id
AND hcs.site_use_id = wsh_delivery_details.ship_from_location_id;-->Take this coloumn from the table (wsh_delivery_details)

or use the below query

SELECT hp.party_name,hl.address1, hl.address2, hl.address3,
hl.address4,hl.PROVINCE, hl.city,hl.STATE, hl.postal_code,hl.COUNTRY,hcs.site_use_code
FROM hz_locations hl,
hz_party_sites hps,
hz_parties hp,
hz_cust_accounts hca,
hz_cust_acct_sites_all has,
hz_cust_site_uses_all hcs
WHERE 1=1--hcs.site_use_code = 'BILL_TO'
AND has.cust_acct_site_id = hcs.cust_acct_site_id
AND has.party_site_id = hps.party_site_id
AND hca.cust_account_id = has.cust_account_id
AND hp.party_id = hca.party_id
AND hps.party_id = hp.party_id
AND hl.location_id = hps.location_id
--AND hca.cust_account_id = 2231634 --oe_order_headers_all.sold_to_org_id
AND hcs.site_use_id = (SELECT (ship_to_org_id) or (invoice_to_org_id) FROM OE_ORDER_HEADERS_ALL WHERE HEADER_ID=
(select DISTINCT SOURCE_HEADER_ID from WSH_DELIVERABLES_V WHERE DELIVERY_ID=:P_DELIVERY_ID))-->use either (ship_to_org_id) or (invoice_to_org_id)

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

************************************************

select hla.description,
hla.address_line_1||hla.address_line_2 address1,
hla.town_or_city||hla.region_2||' '||hla.postal_code address2,
ft.ISO_territory_code address3,
'(Tel) + '||hla.telephone_number_1 Tel,
' (Fax) + '|| hla.telephone_number_2 Fax
from hr_locations_all hla, fnd_territories ft
where hla.inventory_organization_id=:P_MFG_ORG
and ft.territory_code=hla.country
and hla.inactive_date is null;