ORACLE ERP & APPS TECH

Tables & Queries select * from hz_customer_profiles where cust_account_id =1054; select * from apps.hz_organizations_profiles; select hca.account_name,hca.account_number,hcsu.freight_term,hcsu.site_use_code from hz_cust_accounts hca, hz_cust_acct_sites_all hcas, hz_cust_site_uses_all hcsu where  hca.cust_account_id = hcas.cust_account_id and hcas.cust_acct_site_id =hcsu.cust_acct_site_id --and hca.cust_account_id =1054 and hcsu.site_use_code = 'BILL_TO' and hcsu.status ='A' order by 1; --TRX_num select * from apps.ra_customer_trx_all where customer_trx_id =3706; select * from apps.RA_CUSTOMER_TRX_LINES_all where customer_trx_id =3706; select * from apps.ar_adjustments_all where adjustment_number = 11002; select * from apps.AR_PAYMENT_SCHEDULES_all where payment_schedule_id =1693; select * from apps.RA_CUST_TRX_TYPES_all;

Some of the 11i table names that have changed in R12 11i R12 AP_ACCOUNTING_EVENTS_ALL  XLA_EVENTS  AP_AE_HEADERS_ALL  XLA_AE_HEADERS  AP_AE_LINES_ALL XLA_AE_LINES  AP_BANK_ACCOUNT_USES  CE_BANK_ACCT_USES_ALL AP_BANK_ACCOUNTS  CE_BANK_ACCOUNTS  && IBY_EXT_BANK_ACCOUNTS  AP_BANK_BRANCHES CEBV_BANK_BRANCHES  &&  CE_BANK_BRANCHES_V ap_inv_selection_criteria_all IBY_PAY_INSTRUCTIONS_ALL AP_CHECK_STOCKS_ALL  CE_PAYMENT_DOCUMENTS  AP_INVOICE_DISTS_MRC_V XLA_AE_LINES with APPLICATION_ID of AP Module AP_TAX_CODES_ALL  ZX_RATES_B AR_VAT_TAX_ALL_B ZX_RATES_B AX_AP_AE_HEADERS_ALL_V XLA_AE_HEADERS  AX_AP_AE_LINES_ALL_V XLA_AE_LINES AX_SLE_HEADERS XLA_AE_HEADERS  AX_SLE_LINES XLA_AE_LINES  CS_COUNTER_VALUES CSI_COUNTER_READINGS_BC_V ...

Data flow for Order-to-Cash cycle    1. Order Entry This is first stage, When the order is entered in the system, it creates a record in order headers and Order Lines table. Enter header details: Once you enter details on the order header and save it or move it to lines, record goes to one table oe_order_headers_all flow_status_code = ENTERED, booked _ flag = N), Primary key=HEADER_ID No record exist in any other table for this order till now. Enter Line details for this order: Enter different item numbers, quantity and other details in line tab. When the record gets saved, it goes to one table. Order header details will be linked with line details by order HEADER_ID. oe _ order _ lines _ all ( flow_status_code = ENTERED, booked _ flag = N, open _ flag = Y) Primary key= LINE_ID 2.Order Booking This is next stage, when Order is booked then the Flow status changed from Entered to Booked. At this stage, these below ...

DECLARE   v_item             VARCHAR2(240) := 'AS54888';    -- item to explode   v_org               VARCHAR2(4)      := 'A11';             -- org in which item is exploded   v_cnt                NUMBER        := 0;   v_err_msg       VARCHAR2(240);   v_err_code     NUMBER        := 0;   v_item_id        NUMBER        := 1050206;           -- set to inventory_item_id of item to explode   v_org_id          NUMBER        := 0;    -- set to organization_id of item to explode   v_order_by   ...

FNDLOAD ======== FNDLOAD is used to transfer various AOL Objects between instances. FNDLOAD executable can be located at $FND_TOP/bin- config files. Syntax ===== FNDLOAD [username/password] 0 Y [mode] [configuration file] [target data file] [object type key] [object Identifier] 0 Y - concurrent program flags.  [mode] -for download or upload . [configuration file] - The .lct file like afcpprog.lct  which varies based on the object to transfer. [target data file] - The .ldt file to be created for download or used for upload.  [object type key] -The object type. [object Identifier] - The object name, for ex: short name for CONCURRENT_PROGRAM_NAME='XXACMEPRT'. FORM Personalization ================  Download      FNDLOAD <userid>/<pass> 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct <filename.ldt>  FND_FORM_CUSTOM_RULES function_name=<function name>  Upload   ...

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

**Query to fetch the Nth highest value of a column from table select * from      (select Emp_name, Salary,  ROW_NUMBER() OVER (ORDER BY salary DESC) as Row_num from Employees_table  ) table1 where Row_num = n; **Query to check oracle applications version through back end    select * from product_component_version; select release_name from apps.fnd_product_groups;     SELECT * FROM apps.ad_bugs WHERE months_between (sysdate,CREATION_DATE)< 6 SELECT * FROM apps.ad_applied_patches WHERE MONTHS_BETWEEN(SYSDATE,CREATION_DATE)<6 select bug_number,creation_date from ad_bugs where bug_number='7579700'; select * from v$version where sid = '127'; select * from V$Container;  SELECT * FROM DBA_DML_LOCKS; **Query to get the table names and schema owner from the database SELECT table_name, owner FROM dba_tables WHERE table_name LIKE 'XX_XX%'Order by owner **Query to get the custom views ...