ORACLE ERP & APPS TECH

 Declare     -- 1. Use the standard Oracle API collection types        l_item_tbl      EGO_ITEM_PUB.Item_Tbl_Type;        x_item_tbl      EGO_ITEM_PUB.Item_Tbl_Type;        l_error_tbl     Error_Handler.Error_Tbl_Type;        --l_item_rec      EGO_ITEM_PUB.Item_Rec_Type;        -- Standard API tracking variables        x_return_status VARCHAR2(1);        x_msg_count     NUMBER;   TYPE l_input_stg is TABLE OF XXKT_ALT_ITEMINFO_STG%rowtype;      l_input_stg_row     l_input_stg:=l_input_stg();                l_error_msg         VARCHAR2(4000);        l_idx               NUMBER := 0; -- Counter for loop init...

 Step1: Enable tools Menu when new form instance Action Tab: Type: Menu Menu Entry: Special 4 Menu Name: Enter user friendly name seq1: to initialize the global variable to be passed to Concurrent program seq2: to capture the form value to global variable to be passed to Concurrent program Next seq condition to call concurrent program from special menu Action tab: seq1:Assign the form value to the global variable seq2:Call the concurrent program from the tools special menu Navigate to concurrent program definition:  assign default value type as SQL Statement and default value for the parameter required:  select :GLOBAL.XX_G_JOB from dual Output: Opens up the report with the default value of job from the oracle form to concurrent program parameter.

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

 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>; se...

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 to set run time check variables in RTF templates: step1: to set the variable "Chck" to 'N' :-      <?xdoxslt:set_variable($_XDOCTX, 'Chck', 'N')?>                           step2: if condition to check the value and perform action:-      <?if: xdoxslt:get_variable($_XDOCTX,'Chck')='Y'?>                              

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