Difference between CHR(10) Line Feed (\n) and CHR(13) Carriage Return (\r)

 CHR(13) is carriage return. CHR(10) is line feed.

They are different. Most of the editors will treat them as new line, but not all

The new line is actually based on your OS - If I am not wrong for Unix it is CHR(10).
For MAc it is CHR(13). For Windows it is combination of both.

So it all depends on, what editor, what OS - how they will interpret these characters..

This difference is viewable during you do file transfer between different OS.

Example :---

SQL> select
       replace('a,b,c,d,e',',',CHR(10)) x1,
       replace('a,b,c,d,e',',',CHR(13)) x2,
       replace('a,b,c,d,e',',',CHR(13)||CHR(10)) x3,
       replace('a,b,c,d,e',',',CHR(10)||CHR(13)) x4
   from
   dual;

X1        X2        X3            X4                                                                                                        
--------- --------- ------------- -------------                                                                                             
a         a b
c
d
e a             a                                                                                                         
b                   b             
b                                                                                                        
c                   c             
c                                                                                                        
d                   d             
d                                                                                                        
e                   e              e       

Use of CHR(0) String

Chr(0) is one of the ascii characters Where as NULL is undefined or unknown value.

SELECT ascii(chr(0))  Value FROM DUAL;

Value
-------------
0



Chr(0) is different from NULL.

SELECT DECODE(CHR(0), NULL, 'NULL', 'NOT NULL') Value from dual;

Value
--------
NOT NULL




SELECT length(chr(0)) FROM dual;

LENGTH(CHR(0))
--------------
1

SELECT length(NULL) FROM dual;

LENGTH(NULL)


You can use chr(0) to diplay a blank line with DBMS_OUTPUT.PUT_LINE.

Example

SQL> begin
dbms_output.put_line('This is first line');
dbms_output.put_line( Chr(0));
dbms_output.put_line('This is second line');
end;
/
This is first line

This is second line

PL/SQL procedure successfully completed.

Calculate Sales Order Tax and Lines / Order Total

Standard Package to show Sales order Line Basic/ Tax Value and Total Value (Basic+Tax)

Sometime we Need to show Line wise or Complete Order Taxes / Tax+Basic in few reports, for this , oracle has provided a standard Package to Calculate these values, based on parameter passed.



Package Name: oe_totals_grp.get_order_total

Below are the Illustration of this package with example.

For Example: 1 Order have 3 lines, and each one have taxes attached on it.


  Header ID: 23096

  Line ID: 36492----Line Value=1,045.00----Tax on this Line ----87.52----TOTAL VALUE---1132.52
  Line ID: 36494----Line Value=505.00-----Tax on This Line ----42.3-----T0TAL VALUE---547.3
  Line ID: 36495----Line Value=1,750.00---Tax on this Line ----146.56---T0TAL VALUE---1896.56



  Calculate Line wise Tax

    select nvl(oe_totals_grp.get_order_total (HEADER_ID, LINE_ID, 'TAXES'),0) from dual;
 
 For Ex:
 
    select nvl(oe_totals_grp.get_order_total (23096, 36492, 'TAXES'),0) LINE_Tax from dual;
   
    Output : LINE_Tax=87.52
 
 
  Calculate Order Taxes (All Lines)


    select nvl(oe_totals_grp.get_order_total (HEADER_ID, NULL, 'TAXES'),0) from dual;
 
 
   Ex:
 
   select nvl(oe_totals_grp.get_order_total (23096, null, 'TAXES'),0) Order_Tax from dual;
 
 
   Output : Order_Tax: 276.38
 
 
 
 
 
    Calculate Line wise Value (Without Tax)

    select nvl(oe_totals_grp.get_order_total (HEADER_ID, LINE_ID, 'LINES'),0) from dual;
 
 
    Ex:
 
      select nvl(oe_totals_grp.get_order_total (23096, 36492, 'LINES'),0)  LINE_BASIC from dual;
   
   
      Output : LINE_BASIC=1045
   
 
  Calculate All Lines Total (Without Tax)

    select nvl(oe_totals_grp.get_order_total (HEADER_ID, NULL, 'LINES'),0) from dual;
 
 
     Ex:
 
   select nvl(oe_totals_grp.get_order_total (23096, null, 'LINES'),0) Order_Basic from dual;
 
 
   Output : Order_Basic: 3300
 
 
 
 
   Calculate Line wise Value ( With Tax)

    select nvl(oe_totals_grp.get_order_total (HEADER_ID, LINE_ID, 'ALL'),0) from dual;
 
 
     Ex:
 
      select nvl(oe_totals_grp.get_order_total (23096, 36492, 'ALL'),0)  LINE_TOTAL from dual;
   
   
      Output : LINE_TOTAL=1132.52
 


    
  Calculate Order Total Value (With Tax)

    select nvl(oe_totals_grp.get_order_total (HEADER_ID, NULL, 'ALL'),0) from dual;
 
     Ex:
 
   select nvl(oe_totals_grp.get_order_total (23096, null, 'ALL'),0) Order_Total from dual;
 
 
   Output : Order_Total: 3576.38

Printing Address in standard Format in Formatted reports ,

Printing Address in standard Format , specified in  Oracle Package.


Create a Formula in Report, and pass the below values from Query.



function CF_SHIP_ADDRESSFormula return Char is
lv_address varchar2(2000);
begin

lv_address:= TRIM(arp_addr_pkg.format_address(NULL
                                   ,:ACCOUNT_NAME
                                   ,:SHIP_TO_ADDRESS1
                                   ,:SHIP_TO_ADDRESS2
                                    ,:SHIP_TO_ADDRESS3
                                  ,:SHIP_LOC_CITY||'|'||:SHIP_LOC_STATE||'|'||:SHIP_LOC_POSTAL_CODE
                                       ,NULL
                                       ,null
                                       ,NULL
                                       ,null
                                       ,null
                                       )
           );
return REPLACE(REPLACE(lv_address,', ',','||chr(10)),'|',',');
exception
when others then
return null;

end;

Print NO Data Found in XML Reports

Mark the Database Column based on what counting should be done.

For Example if, Report is related with Purchase order print , then PO_Header_id or Segment1 can be treated as Base Database Column from SQL Query.
or if report belongs to Order Management , and records are fetched based on Order numbers then HEADER_ID from oe_order_headers_all.


then on RTF template Use if/end if condition


<?if:count(//PO_HEADER_ID)=0?>
or
<?if:count(//HEADER_ID)=0?>

Calculation of Schedule Arrival date in Oracle Orders

If the order date type is "Ship", Schedule Arrival Date cannot be manually change , If the order date type is "Arrival", Schedule Ship Date cannot be manually changed. (Defaulting rule setup)

Schedule Arrival Date = Schedule Ship Date + Delivery Lead Time

Delivery Lead Time needs to be calculated in order to calculate the Schedule Arrival Date. Delivery lead Time is the Transit time taken.

Request Date Type - Possible values are arrival and ship. If the value is arrival then the request date and promise date will be considered arrival dates by the system; if the value is ship then it will be considered ship dates. The request date type can be defaulted from the customer information to the order, and the user can change it on the order if required.

If you set the Order Date Type = Arrival in the header, and you will be able to update the Schedule Arrival Date ,

but  when you try to update the Schedule Ship Date  with the Availability , will get the below error.

“The order date type is "Arrival", Schedule ship date can't be manually changed. Schedule ship date can be recalculated if you change request date or schedule arrival date.”

Query to Find out RTV (Return to Vendor ) Transactions

Select
org.organization_code,
poh.segment1 po_number,
trunc(poh.creation_date) po_date,
rsh.receipt_num,
(rsh.creation_date) receipt_date,
mtl.segment1||'-'||mtl.segment2 item,
mtl.description,
rcv.quantity,
pol.unit_price
from rcv_transactions rcv
, po_lines_all pol,
po_headers_all poh,
 po_line_locations_all pll,
rcv_shipment_headers rsh,
rcv_shipment_lines rsl,
mtl_system_items mtl,
org_organization_definitions org
where rcv.transaction_type = 'RETURN TO VENDOR'
and rcv.po_line_location_id = pll.line_location_id
and rcv.po_line_id = pol.po_line_id
and pll.SHIP_TO_ORGANIZATION_ID=org.ORGANIZATION_ID
and rcv.shipment_line_id=rsl.shipment_line_id
and org.ORGANIZATION_ID=mtl.ORGANIZATION_ID
and rcv.shipment_header_id = rsh.shipment_header_id
and mtl.inventory_item_id=pol.item_id
and pol.po_header_id=poh.po_header_id
and rsl.shipment_header_id=rsh.shipment_header_id
and org.operating_unit=1214
order by 1,2,4