1) Calling a Package procedure in personalization:

Go To personalization, Type required event , go to actions, Action Type--> Builtin -->
Builtin Type--> Execute a Procedure

Syntax:

='BEGIN apps.xxcust_pkg_name.proc_name('''||${ITEM.DLVY.NAME.value}||''','''||${ITEM.DLVY.ATTRIBUTE1.value}||''','||${ITEM.DLVY.DELIVERY_ID.VALUE} ||','||'FND_PROFILE.VALUE('||'''ORG_ID'''||'),'||'NULL'||','||'''STDFRM'''||','||'NULL'||');  END'



use below sql query in plsql/toad  to insert actual fields:

SELECT
'BEGIN apps.xxcust_vat_exmp_pkg.XXTAJ_INSERT_TABLE1('||&Avalue||','||&Bvalue||','||&CVALUE||','||'FND_PROFILE.VALUE('||'''ORG_ID'''||'),'||'''  '''||');  END'
FROM DUAL

Once this show properly in toad, replace &value with syntax like : ${ITEM.DLVY.NAME.value}

To see personalization to enable on which event:

Go to Help--> Diagnostics--> custom Code--> show custom events

1) Table extending or going into footer in RTF / XML Publisher report:

Reason:  Go to table -> Table Properties --> Check preferred width of column and rows and cell, it should be unchecked. Because if there is no data, still it takes space.

Query to get Tax details from invoice in oracle apps r12

1) Below is the query to get tax details from invoice:


  SELECT  lines.TAX_AMT,lines.tax_rate   
         FROM zx_lines lines,ra_customer_trx_all rct,ra_customer_trx_lines_all rl,
 ZX_TAXES_B ZTB,GL_DAILY_CONVERSION_TYPES gc
  where rct.trx_number=:TRX_NUMBER  and rct.customer_trx_id=lines.trx_id
 and rct.customer_trx_id       = rl.customer_trx_id
  and ZTB.Exchange_Rate_Type=gc.conversion_type
  and ztb.tax_id=lines.tax_id
  AND RL.LINE_TYPE='LINE'
  and rct.org_id=:P_ORG_ID
  and lines.trx_line_id=rl.customer_trx_line_id
  and rl.customer_trx_line_id=:customer_trx_line_id;

2) For Standard and Blanket PO:

    ---for tax rate and amount
      SELECT tax_rate  , tax_amt 
(SELECT  lines.tax_rate  ,lines.tax_amt
      FROM   po_headers_all poh,
       po_lines_all pol , po_line_locations_all plla ,zx_lines Lines
--WHERE (poh.segment1 = :p_po_no OR :p_po_no IS NULL)
WHERE  poh.PO_HEADER_ID=:po_header_id1
AND POL.PO_LINE_ID=:L_PO_LINE_ID
AND    poh.org_id = :p_org_id
AND    pol.po_header_id = poh.po_header_id
AND    pol.org_id = poh.org_id
   and lines.trx_id=poh.po_header_id and lines.trx_line_id=plla.line_location_id
  and pol.po_line_id=plla.po_line_id 
--AND    poh.authorization_status = 'APPROVED'
AND    :p_report_type = 'STANDARD'
AND    pol.quantity > 0  --- version 115.2 added this condition to show only those lines which are open/not fully cancelled
UNION ALL
SELECT  lines.tax_rate  ,lines.tax_amt
      FROM   po_headers_all poh,
       po_lines_all pol,
       po_distributions_all pod,
       po_releases_all prl,po_line_locations_all  pll,
   zx_lines lines
--WHERE (poh.segment1 = :p_po_no OR :p_po_no IS NULL)
WHERE  poh.PO_HEADER_ID=:po_header_id1
AND    poh.org_id = :p_org_id
--AND    poh.authorization_status = 'APPROVED'
AND    pol.po_header_id = poh.po_header_id
AND    pol.org_id = poh.org_id
AND    pod.po_header_id = pol.po_header_id
AND    pod.po_line_id = pol.po_line_id
AND    pod.org_id = pol.org_id
AND    prl.po_release_id = pod.po_release_id
AND    prl.po_header_id = pod.po_header_id
AND    prl.org_id = pod.org_id
and lines.trx_id=prl.po_release_id
and lines.trx_line_id=pll.line_location_id
AND    :p_report_type = 'BLANKET'
AND    ( prl.po_release_id  =NVL( :po_release_id1,prl.po_release_id))
and pol.po_line_id =:L_PO_LINE_ID
AND PRL.RELEASE_NUM =NVL(:P_RELEASE_NO,PRL.RELEASE_NUM)
and pll.po_line_id=pol.PO_LINE_ID
and pll.PO_HEADER_ID=pol.PO_HEADER_ID
and pll.LINE_LOCATION_ID=pod.line_location_id
and NVL(pll.CANCEL_FLAG,'N')='N'
--and not exists (select 1 from PO_LINE_LOCATIONS_RELEASE_V pllr
--where pllr.PO_RELEASE_ID=prl.PO_RELEASE_ID
--and nvl(QUANTITY_CANCELLED,0)<>0)

) a;

OracleAppsKnowHows

XML Publisher RTF Report

1)  To replace Null value of XML Tag  with 'NA'  or If Else condition in RTF layout

  There are two ways to do it:

         a) using if condition, syntax: insert if and end if syntax in field

                <?if: xml_tag !=0?>  xmltag  <?end if?>

   

         b) use XDOFX  , in this we can use if, if else also, insert below syntax in XML Tag

                <?xdofx:if CP_TAX_AMT != '' then CP_TOTAL_AED+CP_TAX_AMT_AED else   'NA' end if?>