ORACLE ERP & APPS TECH

 Script to enable and disable DB Scheduler in Oracle Apps -> To enable DB scheduler BEGIN dbms_scheduler.enable('APPS.REQIMPORT_SCHEDULE'); END; -> To disable DB Scheduler BEGIN dbms_scheduler.disable('APPS.REQIMPORT_SCHEDULE'); END; -> To check status  select owner, job_name, job_action, enabled,state from dba_scheduler_jobs where job_name ='REQIMPORT_SCHEDULE';

 API's to update Purchase Order in R12 For all these API context needs to be set. =====API to Update Requestor               PO_Mass_Update_PO_GRP.Update_Persons                                     ('DELIVER TO'    ,      --p_update_person                                      G_OLD_PERSON_ID,       --p_old_personid     ,                                      L_NEW_PERSON_ID,       --p_new_personid     ,                                      NULL,            ...

Add New Font to Oracle Apps Business Case  T here could be a business scenario where we might need to add new font like BARCODES or specific font to match the companies LOGO and BRAND. In Order to achieve new fonts being added to Oracle Apps so it can used by XML Publisher in reporting, following steps need to be done to bring in the new font: Steps: Place the font .ttf file in any server path like erpapp/customtop/fonts                sample file name: New40font.ttf The new font needs to be added to the XDO.cfg config file to be able to use by XML publisher                 code snippet to be added to config file: <font family="New40font" style="normal" weight="normal" > <truetype path="/erpapp/customtop/fonts/New40font.ttf"/> </font>

Query to get Supplier data from Oracle R12 SELECT   aps.segment1 "SupplierNum",          assa.vendor_site_code "Address Book Identifier ",          aps.vendor_name "Supplier Company Name ",          --Hou.name "Operating Unit Name",          Assa.address_line1 "Supplier Address Line1",          Assa.address_line2 "Supplier Address Line2",          assa.address_line3 "Supplier Address Line3",          assa.address_line4 "Supplier Address Line4",          assa.city "Supplier city",          assa.state || assa.PROVINCE "Supplier State/Province",          assa.country "Supp_country_Code",          assa.zip "Supp_postal_Code",          apsite_rmt.ADDRESS_LINE1 "Supp_Remit_...

Query to get Tree structure of the indented BOM along with Vendor for the Purchased items and Item type SELECT DISTINCT LPAD (' ', LEVEL * 2) || LEVEL ORDER_LEVEL               , LPAD (' ', LEVEL * 1) || msib.segment1 ASSEMBLY_ITEM               , LPAD (' ', LEVEL * 4) || msib2.segment1 AS COMPONENT_ITEM               , msib2.description COMPONENT_ITEM_DESCRIPTION               , bic.bill_sequence_id c, bom.bill_sequence_id b                           , msib.description ASSEMBLY_DESCRIPTION               , msib.inventory_item_status_code ASSEMBLY_ITEM_STATUS                 , decode (msib2.PLANNING_MAKE_BUY_CODE, 1, 'MAKE'                       ...

To update a data base column to a NULL value, you must enter the following values: Column Data Type        Required value to create NULL column For a null character, assign the constant FND_API.G_MISS_CHAR For a null number,    assign the constant FND_API.G_MISS_NUM For a null date,        assign the constant FND_API.G_MISS_DATE For Char columns            char (0) For Date columns            TO_DATE (’1’,’j’) For Number columns       9.99E125 Ex: To update number column type to NULL Update po_headers set agent_id = 9.99E125 where agent_id = 101 and org_id = 100; Reference: How to Enter a Null Value (Clear a Value) For Any Column in Any BOM Interface Table [ID 264198.1]

## Profile Set Up To Trigger Receipt Traveler Report automatically upon creation of new Receipt. Navigation: Sysadmin-> Profile-> System-> Search for :  “ RCV: Print Receipt Traveler ” and set the value to Yes Enabling this profile will trigger  Receipt Traveler report as concurrent request after creation of new receipt. ## Profile option  Viewer: Text set to BROWSER at user level Enabling this profile option at user level will fix issue that occurs specific to user like unable to save transaction after clicking SAVE button.

Forms ========== f60gen $XX_TOP/forms/US/XX.fmb apps/$apps_pw module_type=FORM output_file=$XX_TOP/forms/US/XX.fmx f60gen XX.fmb  apps/oracle123 module_type=FORM PLL ==== f60gen $CUSTOM_TOP/resource/CUSTOM.pll  username/pass(of oracle) module_type=LIBRARY output_file=$CUSTOM_TOP/CUSTOM.plx *********** Step1 -> Connect to Unix Box Step2 -> Set Environment variables sudo -u xxerp -i . /xxerp/erpapp/appl/xxerp_xxerp.env . /xxerp/erpapp/comn/conf/xxerp_xxerpp/8.0.6/xxerp_xxerp.env Step3 -> FORMS60_PATH=:FORMS60_PATH:$AU_TOP/resource:$AU_TOP/forms/US; Step4 -> export FORMS60_PATH; cd $XX_TOP/forms/US f60gen XX.fmb  apps/oracle123 Unix Commands ============ 1-Convert PDF/image to ps (Postscript) to send docs to printer pdftops $PDF_FILE_NAME > $FILE_NAME.ps imagetops -gray $DEST_PATH$FILE_NAME > $FILE_NAME.ps 2-print command lp -d $PRINTER_NAME -o media=A4 -o fitplot -o sides=two-sided-short-edge $FILE_NAME 3-find file name to captur...

-- Picking line status from WSH_DELIVERY_DETAILS table SELECT FND.LOOKUP_CODE,        FND.MEANING   FROM FND_LOOKUP_VALUES FND  WHERE FND.LOOKUP_TYPE='PICK_STATUS'    AND LANGUAGE ='US'  ORDER BY lookup_code; --  Delivery FIRM Status SELECT FLV.LOOKUP_CODE,        FLV.MEANING   FROM FND_LOOKUP_VALUES FLV  WHERE FLV.LANGUAGE = 'US'    AND FLV.LOOKUP_TYPE = 'DELIVERY_PLANNED_FLAG'  ORDER BY lookup_code; -- Delivery Status SELECT FLV.LOOKUP_CODE,        FLV.MEANING   FROM FND_LOOKUP_VALUES FLV  WHERE FLV.LANGUAGE = 'US'    and flv.lookup_type = 'DELIVERY_STATUS'  ORDER BY lookup_code;   -- WMS TASK status SELECT *   FROM MFG_LOOKUPS  WHERE LOOKUP_TYPE = 'WMS_TASK_STATUS'  ORDER BY lookup_code; -- WMS Task Type SELECT *   FROM MFG_LOOKUPS  WHERE LOOKUP_TYPE = 'WMS_TASK_TYPES'  O...