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

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

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

Query to get comma separated values into each row SELECT TRIM(regexp_substr(:l_comma_code,'[^,]+', 1, level)) Comma_col_value                            FROM dual                      CONNECT BY regexp_substr(:l_comma_code,'[^,]+', 1, level) is not null; =>Query to get all dates in a month for a given date:     SELECT  TRUNC(TO_DATE('2024-01-01', 'YYYY-MM-DD'), 'MM') + LEVEL - 1 AS day_in_month     FROM  dual CONNECT BY      TRUNC(TO_DATE('2024-01-01', 'YYYY-MM-DD'), 'MM') + LEVEL - 1 <= LAST_DAY(TO_DATE('2024-01-01', 'YYYY-MM-DD')); =>SQL to get ISO week and month for date: select TO_CHAR(SYSDATE, 'IW') AS wk_Bucket, TO_CHAR(SYSDATE, 'MM') AS MN_Bucket  FROM DUAL; =>SQL query to get last ISO week for a given month and year SELECT TO_CHAR(LAST_DAY(TO_DATE(:month_num || '-' || :year_num, 'MM-YYYY')), 'I...