ORACLE ERP & APPS TECH

 Business requirements of custom pop-up error message containing values from DB When requirements demands pop-up error messages in form personalization to fetch values from database tables based on the user entered input in the form window. Form personalization can be achieved as shown below : Message text: ='Duplicate Number- ' || :BLOCK.DISPLAY_COL1 || '. Alert Duplicate value entered. Check Ref COL.' || (select character24   from qa_results qr, qa_plans qp where qr.plan_id =qp.plan_id and qp.name = :Q_RES_HEAD.NAME  and qr.character1= :Q_RES.DISPLAY1)

As part of performance and run time of a query or a function we might need to capture time in milli/micro/ nano seconds. Below snippets would help in such scenarios: By default  localtimestamp will give upto 6 decimal places of a sec. to get nano sec upto 9 decimal use  localtimestamp(9) declare l_starttime timestamp(9); l_endtime timestamp(9); l_count number:=0; l_Diff number:=0; begin select localtimestamp(9) into l_starttime from dual;  dbms_output.put_line('Start Time: ' || l_starttime);   select count(*) into l_count from PO_line_locations_all pll, po_headers_all PHA where pll.po_header_id = pha.po_header_id and exists (select 1 from po_requisition_lines_all prl,po_requisition_headers_all prh               where prl.requisition_header_id = prh.requisition_header_id                 and prl.blanket_po_header_id = pha.po_header_id) and pll.po_header_id = <enter header id>; select localtimestamp(9) into l_endtime from dual;  dbms_output.put_line('End Time: ' || l

unobtrusive  - Unremarkable, modest albeit - Even though, even if, although deepfake - images or videos generated using AI where they are hard to recognize the real vs AI generated images  

 Format to add barcodes to RTF templates XML Tag:  <?PART_NUMBER?> Barcode Tag:  <?format-barcode:PART_NUMBER;'code128a';'XMLPBarVendor'?> add below tag to the template as well:  <?register-barcode-vendor:’com.agilent.xdo.template.rtf.util.barcoder.BarcodeUtil’;'XMLPBarVendor'?> How to add new Barcode font to Oracle apps

 How I learnt Oracle Demantra Basics What it Does? Real Time sales & Operation Planning  Causal analysis, Seasonal & cyclic events, trend analysis Demantra tables  Staging Tables select * from t_src_item_tmpl; select * from t_src_sales_tmpl; select  * from t_src_loc_tmpl; Base tables select * from sales_data;--sales order historical data select * from mdp_matrix;--combination data select * from inputs;--time buckets select * from queries;--worksheets select * from items;--items Select * from t_ep_item; select * from location;--locations Integration interface select * from transfer_query; Patch select * from version_details_history; Workflow select * from wf_schemas; select * from wf_process_log; Select * from group_tables;

***ORA ERROR NUMBERS & FIX** Error: ORA-00917: missing comma for an INSERT statement. Fix: additional ( ' ) is present in the data which might interfere with the string being inserted. ex: SPANISH USER' S to be replaced with SPANISH USERS Error: ORA-06512: Indicates the line number at which the unhandled error occurred. Fix Use DBMS_UTILITY.format_error_backtrace ex: FND_FILE.PUT_LINE(FND_FILE.log, DBMS_UTILITY.format_error_backtrace); Error: ORA-02251: Sub-Query not allowed in here --(in triggers) Fix: Use CASE and EXISTS in select statement to capture value and validate with IF condition example: Error: ora-24345 in value set used in DFF Fix: increase the length of the column in value set definition Error: ORA-06502: PL/SQL: numeric or value error: character string buffer too small Fix: Occurs when string variable is passed in as Number variable (ex: while calling procedures etc.,) make sure to pass in correct data type API Error: ONTOE_INVALID_ATTRIBUTENATTRIBUTEShippi

Freight Carriers: Navigation: Shipping/OM Super User responsibilities -> Setup -> Shipping -> Freight Carrier, Cost Types -> Freight Carriers  Query to Fetch Freight Carriers: select  wc.carrier_name,  WC.FREIGHT_CODE, wcs.service_level,  wcs.mode_of_transport, ship_method_code, WOC.ORGANIZATION_CODE from  apps.WSH_CARRIERS_V WC, apps.WSH_CARRIER_SERVICES WCS, apps.WSH_ORG_CARRIER_SERVICES_V woc where wc.carrier_id = wcs.carrier_id   and wcs.carrier_service_id = woc.carrier_service_id   and WC.ACTIVE ='A'   and wc.carrier_name ='EUROPOST' order by wc.carrier_name,WOC.ORGANIZATION_CODE

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 Sales Order data ======================== select distinct ooh.CREATION_DATE , Ooh.Order_number  "Sales Order Number", VERSION_NUMBER  "Sales Order Revision Number  ", to_char(ORDERED_DATE,'DD-MM-RRRR')  "Entered Date", (select ORDER_CATEGORY_CODE from apps.oe_transaction_types_all where transaction_type_id = ooh.ORDER_TYPE_ID )  "Order Type",ooh.ORDER_TYPE_ID, Ooh.Quote_number  "Quote Number", ooh.TAX_EXEMPT_FLAG  "Order Taxable  ", (select name from apps.ra_terms where Term_id = ooh.PAYMENT_TERM_ID)  "Payment Terms", ooh.TAX_EXEMPT_NUMBER  "Tax Exempt Id Number", (select p.FIRST_NAME  from apps.fnd_user f,       apps.Per_all_people_f p where f.EMPLOYEE_ID = p.PERSON_ID   and f.user_id = ooh.LAST_UPDATED_BY )  "Last Maintenance - First Name", (select p.LAST_NAME  from apps.fnd_user f,       apps.Per_all_people_f p where f.EMPLOYEE_ID = p.PERSON_ID

Query to get Customer Invoice data =========================== SELECT distinct rct.CREATION_DATE, Rct.Trx_number   "Invoice Number", Rctl.Line_number "Invoice Line Number", Rct.Trx_date     "Invoice Date", rctl.SALES_ORDER  "Sales Order Number", --rctl.INTERFACE_LINE_ATTRIBUTE3 so_line_id,rctl.inventory_item_id,rctl.warehouse_id, rctl.SALES_ORDER_DATE  "Sales Order Date", rct.SHIP_DATE_ACTUAL  "Ship Date", Ool.Request_date  "Customer Required Date", Ool.Promise_date  "Promise Date", rct.PURCHASE_ORDER  "Customer Purchase Order Number", rctl.QUANTITY_INVOICED  "Quantity Sold", rctl.UOM_CODE  "Quantity Unit of Measure", rctl.UNIT_SELLING_PRICE  "Unit Price", DECODE (             Ool.Unit_list_price,             0,             0,             ROUND (                ( ( (Ool.Unit_list_price - Ool.Unit_selling_price)                   / Ool.Unit_lis

Process steps to Disable ATO items (uncheck ATO flag) Pre-Requisites : 1-> Cancel Schedule program -  AutoCreate Final Assembly Orders 2-> Close or Cancel Internal Orders 3-> Ship & complete Order to close for Awaiting Shipping & Awaiting Returns status order lines. 4-> Accept and close Pre_Billing Acceptance order lines. Action Steps : 1 a-> Cancel all Reservations against the item to un-check ATO flag. 1 b-> May require cancellation of WO (re-check with your functional folks) 2-> Cancel all Sales Order lines against the Items. 3-> Un-Check ATO Flag (REPLENISH_TO_ORDER_FLAG = 'N') 4-> Re-create cancelled Order lines if required.

Script to upload items for a given quote and vendor: --SET SERVEROUTPUT ON; DECLARE V_ITEM_ID           mtl_system_items_b.inventory_item_id%TYPE; V_PRICE             PO_LINES_ALL.UNIT_PRICE%TYPE; V_ITEM_DESC         mtl_system_items_b.DESCRIPTION%TYPE; V_line_num          PO_LINES_ALL.LINE_NUM%TYPE := 0; V_organization_id   mtl_system_items_b.organization_id%TYPE; CURSOR C1 IS select msib.inventory_item_id,msib.segment1 ITEM_NUM,        msib.DESCRIPTION,        msib.PRIMARY_UNIT_OF_MEASURE UOM       ,msib.organization_id,mic.category_id,        (select ITEM_COST    from cst_item_costs   where INVENTORY_ITEM_ID = msib.INVENTORY_ITEM_ID     and ORGANIZATION_ID = msib.ORGANIZATION_ID     and COST_TYPE_ID =1) price  from mtl_system_items_b msib,       mtl_item_categories mic where msib.INVENTORY_ITEM_ID = mic.INVENTORY_ITEM_ID and msib.ORGANIZATION_ID = mic.ORGANIZATION_ID and msib.organization_id = 12 and mic .CATEGORY_SET_ID = 1001 AND msib.segment1 in ('2

Query to Assign Items to Sourcing rule: --SET SERVEROUTPUT ON; DECLARE V_ASSIGNMENT_ID             MRP_SR_ASSIGNMENTS.ASSIGNMENT_ID%TYPE;  V_ASSIGNMENT_SET_ID    MRP_SR_ASSIGNMENTS.ASSIGNMENT_SET_ID%TYPE := 1; V_ASSIGNMENT_TYPE       MRP_SR_ASSIGNMENTS.ASSIGNMENT_TYPE%TYPE := 1; V_SOURCING_RULE_ID      MRP_SR_ASSIGNMENTS.SOURCING_RULE_ID%TYPE := 1001; V_SOURCING_RULE_TYPE   MRP_SR_ASSIGNMENTS.SOURCING_RULE_TYPE%TYPE :=1; V_organization_id                    MRP_SR_ASSIGNMENTS.organization_id%TYPE; CURSOR C1 IS select msib.inventory_item_id,msib.segment1 ITEM_NUM,msib.organization_id    from mtl_system_items_b msib where msib.organization_id = 12   AND msib.segment1 in ('2017');  BEGIN   FOR SR IN C1 LOOP     V_organization_id := SR.organization_id ;      IF V_organization_id = 12 THEN             BEGIN                           V_ASSIGNMENT_ID := MRP_SR_ASSIGNMENTS_S.NEXTVAL ;                                    INSERT INTO MRP_SR_ASSIGNMENTS MSRA

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_to_Address_Line_1",          apsite_rmt.ADDRESS_LINE2 "Supp_Remit_to_Address_Line_2",          apsite_rmt.ADDRESS_LINE3 "Supp_Remit_to_Address_Line_3",