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

Bank Details for Suppliers

Bank at Supplier Site level
-------------------------------

SELECT HZP.PARTY_NAME "VENDOR NAME"
, APS.SEGMENT1 "VENDOR NUMBER"
, ASS.VENDOR_SITE_CODE "SITE CODE"
, IEB.BANK_ACCOUNT_NUM "ACCOUNT NUMBER"
, IEB.BANK_ACCOUNT_NAME "ACCOUNT NAME"
, IEB.BANK_ACCOUNT_TYPE "Bank Account Type" 
, HZPBANK.PARTY_NAME "BANK NAME"
, HOPBRANCH.BANK_OR_BRANCH_NUMBER "BANK NUMBER"
, HZPBRANCH.PARTY_NAME "BRANCH NAME"
, HOPBRANCH.BANK_OR_BRANCH_NUMBER "BRANCH NUMBER" 
, HZPBANK.address1 "Bank Address"
, HZPBANK.country "Bank Country"
, HZPBANK.city "Bank City"
, HZPBANK.Postal_Code
, NVL(HZPBANK.state, HZPBANK.province) "State/Province"
FROM apps.HZ_PARTIES HZP
, apps.AP_SUPPLIERS APS
, apps.HZ_PARTY_SITES SITE_SUPP
, apps.AP_SUPPLIER_SITES_ALL ASS
, apps.IBY_EXTERNAL_PAYEES_ALL IEP
, apps.IBY_PMT_INSTR_USES_ALL IPI
, apps.IBY_EXT_BANK_ACCOUNTS IEB
, apps.HZ_PARTIES HZPBANK
, apps.HZ_PARTIES HZPBRANCH
, apps.HZ_ORGANIZATION_PROFILES HOPBANK
, apps.HZ_ORGANIZATION_PROFILES HOPBRANCH
WHERE HZP.PARTY_ID = APS.PARTY_ID
AND HZP.PARTY_ID = SITE_SUPP.PARTY_ID
AND SITE_SUPP.PARTY_SITE_ID = ASS.PARTY_SITE_ID
AND ASS.VENDOR_ID = APS.VENDOR_ID
AND IEP.PAYEE_PARTY_ID = HZP.PARTY_ID
AND IEP.PARTY_SITE_ID = SITE_SUPP.PARTY_SITE_ID
AND IEP.SUPPLIER_SITE_ID = ASS.VENDOR_SITE_ID
AND IEP.EXT_PAYEE_ID = IPI.EXT_PMT_PARTY_ID
AND IPI.INSTRUMENT_ID = IEB.EXT_BANK_ACCOUNT_ID
AND IEB.BANK_ID = HZPBANK.PARTY_ID
AND IEB.BANK_ID = HZPBRANCH.PARTY_ID
AND HZPBRANCH.PARTY_ID = HOPBRANCH.PARTY_ID
AND HZPBANK.PARTY_ID = HOPBANK.PARTY_ID
ORDER BY 1,3


Bank at Supplier level
------------------------

SELECT HZP.PARTY_NAME "VENDOR NAME"
, APS.SEGMENT1 "VENDOR NUMBER"
, IEB.BANK_ACCOUNT_NUM "ACCOUNT NUMBER"
, IEB.BANK_ACCOUNT_NAME "ACCOUNT NAME"
, IEB.BANK_ACCOUNT_TYPE "Bank Account Type" 
, HZPBANK.PARTY_NAME "BANK NAME"
, HOPBRANCH.BANK_OR_BRANCH_NUMBER "BANK NUMBER"
, HZPBRANCH.PARTY_NAME "BRANCH NAME"
, HOPBRANCH.BANK_OR_BRANCH_NUMBER "BRANCH NUMBER" 
, HZPBANK.address1 "Bank Address"
, HZPBANK.country "Bank Country"
, HZPBANK.city "Bank City"
, HZPBANK.Postal_Code
, NVL(HZPBANK.state, HZPBANK.province) "State/Province"
FROM apps.HZ_PARTIES HZP
, apps.AP_SUPPLIERS APS
, apps.IBY_EXTERNAL_PAYEES_ALL IEP
, apps.IBY_PMT_INSTR_USES_ALL IPI
, apps.IBY_EXT_BANK_ACCOUNTS IEB
, apps.HZ_PARTIES HZPBANK
, apps.HZ_PARTIES HZPBRANCH
, apps.HZ_ORGANIZATION_PROFILES HOPBANK
, apps.HZ_ORGANIZATION_PROFILES HOPBRANCH
WHERE HZP.PARTY_ID = APS.PARTY_ID
AND IEP.PAYEE_PARTY_ID = HZP.PARTY_ID
AND IEP.EXT_PAYEE_ID = IPI.EXT_PMT_PARTY_ID
AND IPI.INSTRUMENT_ID = IEB.EXT_BANK_ACCOUNT_ID
AND IEB.BANK_ID = HZPBANK.PARTY_ID
AND IEB.BANK_ID = HZPBRANCH.PARTY_ID
AND HZPBRANCH.PARTY_ID = HOPBRANCH.PARTY_ID
AND HZPBANK.PARTY_ID = HOPBANK.PARTY_ID
and IEP.SUPPLIER_SITE_ID is null
and aps.vendor_id = 2