Enbaling the ARchivelog

1. Fire up your SQL *PLUS and log in with SYSDBA priveledges.

START > RUN > sqlplus

SQL > CONNECT sys/passTheAdmin AS SYSDBA

2. Issue a Shutdown command

SQL > SHUTDOWN IMMEDIATE;

 
3. Mount yourDatabase

SQL > STARTUP MOUNT;

 
4. Set your database in ARCHIVELOG mode

SQL > ALTER DATABASE ARCHIVELOG;



5. Open your database

SQL > ALTER DATABASE OPEN;

Displaying the table



Result Based:Simple  search
-- Query Region>> construction Mode:result based serach
-- RC on QueryRN>>New Region using Wizard,region style table
-- Change the properites of attributes to search allowed true


Autocustomization Search
--Query region>>Coustion mode :Auto customization
-- RC on QueryRN>>New Region using Wizard,region style table
--RC on QueryRN,select simple serach panel
  RC on Region1 select messageTextimput
--RC on QueryRN select new simple search mapping


Inlinve LOV:
-- RC on PAgelayout for New item>>id,style:MessageLOVinput,prompt
--RC on region1 select new table using wizard select the attributes
--Set search allowed property for the attributes
--set LOVMap1 properties:Number


Extenal LOV

--Create a region in webui
--Create a page and new item in page
  item properties:
   style:LOV
   External LOV : is from Region in webui
   Expand LOv and select the attribute

Pop list
--Vo
--AM
--PG--pageLayoutRN
--New Item style:Message Choice
  select the Attributes

Order to Cash Cycle

Order to cash flow is the process of entering an order ,delivery of the the goods, then producing the invoice for the goods.

Creating the Order:

1)Navigation: Order Management Super user,Vision Operations(USA) >>Orders, Returns > Sales Orders

Enter the Customer Name ,Order type in the Order Information Form

2)Go to the Line items tab

Enter the ordered item,Quantity

3) Click on the Book Order button

4)Run the sql query

select order_number, header_id, flow_status_code from oe_order_headers_all
where order_number = 66555;

This is related to the Header information

select flow_Status_code from oe_order_lines_all where header_id = 195864;

Here the line status is Awaiting Shipping

Pick Release:

1)Navigation: Shipping>>Transaction>>

In the Query Manager screen, enter the order number in the From Order Number field and tab out. Then, click on the Find button.

Click on the Actions and select Launch Pick Release for the LOV then Go

The following Message will be displayed

Now Check for the status in the same form

Ship Confirm:

1)Move on to the Delivery tab and select the Ship confirm button then ok then the confirmation message is displayed.

2)Run this query

select delivery_detail_id, released_status, oe_interfaced_flag, inv_interfaced_flag
from wsh_delivery_details
where source_code = 'OE' and source_line_id = 393680;

Released_Status is C (Shipped) becomes shipped after the ship confirm

OE_Interfaced_Flag is Y –Interface Trip Stop has interfaced shipping data to OM

Inv_Interfaced_flag = Y -- Interface Trip Stop has interfaced shipping data to INV

Invoice:

1)Checking the line status

Move on to sales order form and query the sale order, move onto the line item tab and check the status of the line as”SHIPPED”

Go to the Tools>>Workflow Status

2)Verify that the order line workflow is now at the Fulfill - Deferred activity with a status of Deferred. This means that the next steps is to run the Workflow Background Process.

Navigate to sale order form and view>>Request

Run the workflow background program

Check whether this program is successfully completed. In addition to this notice that the AR concurrent requests Autoinvoice and Autoinvoice Import Program will run automatically to import the invoices into AR.

3)The workflow status for the order line (go to Tools > Workflow Status) should now show the following progression.

4)Run this sql Query

select line_id, open_flag, flow_status_code, invoice_interface_status_code
from oe_order_lines_all
where line_id = 393680;

5)Move on to the sales order Form and Line Items then Click on the Action button select Additional Information from the LOV.

Move onto the Deliveries tab here the status is Closed and pick status is Shipped

Check for the line status Closed.

This Status (Closed) indicates that the O2C cycle has completed successfully.

Cursor using the for loop
Example:
declare
p number(6);
cursor c is
select * from emp for update;
begin
for i in c
loop
select avg(sal) into p from emp where empno=i.empno group by deptno;
dbms_output.put_line(i.empno);
end loop;
end;

WHILE LOOP:
declare
cursor c is
select * from emp;
x emp%rowtype;
begin
open c;
fetch c into x;
while (c%found) loop
dbms_output.put_line(x.ename);
fetch c into x;
end loop;
end;


so, better to use the for loop

*******Fields present in the Requisition Form*****************

select distinct prha.segment1 Requisition_Number
,hou.name operating_unit
,prha.description
,prha.authorization_status Status
,prla.item_description
,prla.unit_meas_lookup_code UOM
,prla.unit_price
,prla.quantity
,prla.need_by_date
,prla.purchase_basis
,prla.destination_type_code
,papf.full_name
,msib.segment1 item
,mcb.segment1||'.'||mcb.segment2 as Categories
,gcc.code_combination_id
,gcc.segment1||'-'||gcc.segment2||'-'||gcc.segment3||'-'||gcc.segment4 as Charge_Account
,okhb.CONTRACT_NUMBER
from
hr_operating_units hou
,po_requisition_headers_all prha
,po_requisition_lines_all prla
,per_all_people_f papf
,mtl_system_items_b msib
,mtl_categories_b mcb
,gl_code_combinations gcc
,PO_REQ_DISTRIBUTIONS_ALL prda
,okc_k_headers_b okhb
where 1=1
and prha.org_id=hou.organization_id
AND prla.creation_date BETWEEN papf.effective_start_date AND papf.effective_end_date
and prla.requisition_header_id=prha.requisition_header_id
and prha.preparer_id =papf.person_id
and msib.inventory_item_id =prla.item_id
and prda.code_combination_id=gcc.code_combination_id
and prda.requisition_line_id =prla.requisition_line_id
and prla.category_id =mcb.category_id
and prha.segment1 = :Requisition
and prla.OKE_CONTRACT_HEADER_ID=okhb.id;

Query to build the  list the supervisors for a given employee in a single column

the format is as

Employee_1 -> Employee_2 -> Employee_3 -> Employee_4
where Employee_2 is the supervisor of Employee_1 and so on.

SELECT
 DISTINCT SYS_CONNECT_BY_PATH(FULL_NAME, '->') "Path",
LEVEL LVL
FROM
  PER_ALL_PEOPLE_F PPFS,PER_ALL_ASSIGNMENTS_F PAFE
WHERE 1 = 1
and PPFS.PERSON_ID =  PAFE.person_ID
AND TRUNC (SYSDATE) BETWEEN PAFE.EFFECTIVE_START_DATE AND PAFE.EFFECTIVE_END_DATE
AND TRUNC (SYSDATE) BETWEEN PPFS.EFFECTIVE_START_DATE AND PPFS.EFFECTIVE_END_DATE
and level = 3
  CONNECT BY Ppfs.PERSON_ID = PRIOR Pafe.SUPERVISOR_ID  -- PPFS,PAFE
 START WITH Ppfs.PERSON_ID = &Numbe ;

Nth highest Salary.........................!  :P



select top 1 from
(select distnict top n from emp  --retrving the nth highest salary
order by sal desc) result
order by sal;



with result as
(select sal,dense_rank() over (order by salary desc) as denserank
from emp)

select salary 

from emp

where result.denserank=n;