ORACLE ERP & APPS TECH

Steps To create USER in oracle apps using API ================================ Base table: FND_USER API: FND_USER_PKG.CREATE_USER DECLARE   P_USER_NAME             VARCHAR2(200)       := '1111' ; P_OWNER                 VARCHAR2(200)       := NULL; P_UNENCRYPTED_PASSWORD VARCHAR2(200)       := 'oracle123'; P_SESSION_NUMBER        NUMBER               := userenv('sessionid'); P_START_DATE DATE   := SYSDATE; P_END_DATE DATE      ...

**Query to obtain PO/PR Select Distinct Pha.Segment1 Po#, Pha.Po_Header_Id, Pha.Creation_Date, Pha.Approved_Date, Pda.Po_Header_Id Pda_Po_Header_Id, Prha.Segment1 Pr# From Apps.Po_Headers_All Pha, Apps.Po_Distributions_All Pda, Apps.Po_Req_Distributions_All Prda, Apps.Po_Requisition_Lines_All Prla, Apps.Po_Requisition_Headers_All Prha Where Pha.Org_Id=123 And Pda.Po_Header_Id=Pha.Po_Header_Id And Prda.Distribution_Id=Pda.Req_Distribution_Id And Prla.Requisition_Line_Id=Prda.Requisition_Line_Id And Prha.Requisition_Header_Id=Prla.Requisition_Header_Id And Pha.Segment1 = '900000' And Prha.Segment1 = '10000' And --TO_CHAR(pha.approved_date,'DD-MON-YYYY') between('16-JAN-2007' AND '17-JAN_2007') AND To_Date(Pha.Approved_Date) Between '15-JAN-2007' And '16-JAN-2007' And Order By Pha.Creation_Date; **Query to Obtain PO/Receipt Select C.Segment1 Po_Number, C.Closed_Code, C.Authorization_Status, D.Line_Num...

**Query to get Executable name for given Program SELECT DISTINCT fcp.user_concurrent_program_name, fe.executable_name, fe.execution_file_name FROM fnd_request_group_units frgu, fnd_concurrent_programs_vl fcp, fnd_request_groups frg, fnd_executables fe WHERE frgu.request_unit_id = fcp.concurrent_program_id AND frgu.request_group_id = frg.request_group_id AND fe.executable_id = fcp.executable_id AND fe.execution_file_name LIKE 'GEPS_INV_ITEM_COPY_COST_PKG%'; AND fcp.user_concurrent_program_name ='<program_name>'; **Query for Concurrent Program Parameters Select B.User_Concurrent_Program_Name,A.Concurrent_Program_Name, B.Description,D.Meaning,F.End_User_Column_Name, F.Enabled_Flag,G.Flex_Value_Set_Name,G.Validation_Type From Apps.Fnd_Concurrent_Programs A, Apps.Fnd_Concurrent_Programs_Tl B, Apps.Fnd_Executables C, Apps.Fnd_Lookups D, Apps.Fnd_Application E, Apps.Fnd_Descr_Flex_Col_Usage_Vl F, Apps.Fnd_Flex_Value_Sets G Where B.User_Concu...

How to Terminate Apps Sessions from background ============== SELECT s.inst_id,        s.sid,        s.serial#,        p.spid,        s.username,        s.program FROM   gv$session s        JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id WHERE  s.type != 'BACKGROUND'; The basic syntax for killing a session is shown below. SQL> ALTER SYSTEM KILL SESSION 'sid,serial#'; In a RAC environment, you optionally specify the INST_ID, shown when querying the GV$SESSION view. This allows you to kill a session on different RAC node. ALTER SYSTEM KILL SESSION 'sid,serial#@inst_id'; In addition to the syntax described above, you can add the IMMEDIATE clause. ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

DECLARE TYPE array is TABLE OF t%ROWTYPE INDEX BY BINARY_INTEGER ; data array; errors NUMBER ; l_cnt NUMBER := 0; dml_errors EXCEPTION; PRAGMA EXCEPTION_INIT (dml_errors, -24381) ; CURSOR c is SELECT * FROM t; BEGIN OPEN c; LOOP FETCH c BULK COLLECT INTO data LIMIT 100; BEGIN FORALL i IN 1..data.COUNT SAVE EXCEPTIONS INSERT INTO t2 values data(i) ; EXCEPTION WHEN dml_errors THEN errors := SQL%BULK_EXCEPTIONS.COUNT ; l_cnt := l_cnt + 1 ; FOR i IN 1..errors LOOP dbms_output.put_line('Error occurred during iteration ' ||SQL%BULK_EXCEPTIONS(i).ERROR_INDEX ||'. Oracle error is ' || SQL%BULK_EXCEPTIONS i).ERROR_CODE ); END LOOP; END; EXIT WHEN c%NOTFOUND ; END LOOP; CLOSE c; dbms_output.put_line(l_cnt || ' Total Errors.'); END ;

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