Tuesday, 18 July 2017

Oracle STATE CODE Function

function CF_STATE_CODEFormula return Char is
V_LOOKUP_CODE  Varchar2(30);
begin
  Select MEANING
  Into V_LOOKUP_CODE
from apps.FND_LOOKUP_VALUES_VL 
Where LOOKUP_TYPE = 'JA_IN_STATE_CODE'
and upper(DESCRIPTION) = upper(:state);
Return V_LOOKUP_CODE;
Exception
    when others then
    Return NULL;
end;

Monday, 17 July 2017

Order To Bill_to and Ship_to Customer Address

----------------------Bill To Customer-------------------------------
SELECT hp.party_name,
       hp.party_number,
       hca.account_number,
       hca.cust_account_id,
       hp.party_id,
       hps.party_site_id,
       hcsu.cust_acct_site_id,
       hps.location_id,
       hl.address1,
       hl.address2,
       hl.address3,
       hl.city,
       hl.state,
       ter.nls_territory,
       hl.postal_code,
       hl.province,
       hcsu.site_use_code,
       hcsu.site_use_id,
       hcsa.bill_to_flag
FROM hz_parties hp,
     hz_party_sites hps,
     hz_locations hl,
     hz_cust_accounts_all hca,
     hz_cust_acct_sites_all hcsa,
     hz_cust_site_uses_all hcsu,
     fnd_territories ter
WHERE     hp.party_id = hps.party_id
      AND hps.location_id = hl.location_id
      AND hp.party_id = hca.party_id
      AND hcsa.party_site_id = hps.party_site_id
      AND hcsu.cust_acct_site_id = hcsa.cust_acct_site_id
      AND hca.cust_account_id = hcsa.cust_account_id
      AND hl.country = ter.territory_code
      AND hcsu.site_use_code = 'BILL_TO'
      AND hca.cust_account_id=:SOLD_TO_ORG_ID --Select SOLD_TO_ORG_ID  From oe_order_headers_all
      and hcsu.site_use_id=:INVOICE_TO_ORG_ID --225009  Select INVOICE_TO_ORG_ID From oe_order_headers_all 

----------------------Ship To Customer-------------------------------
/* Formatted on 7/17/2017 3:05:46 PM (QP5 v5.115.810.9015) */
SELECT hp.party_name,
       hp.party_number,
       hca.account_number,
       hca.cust_account_id,
       hp.party_id,
       hps.party_site_id,
       hcsu.cust_acct_site_id,
       hps.location_id,
       hl.address1,
       hl.address2,
       hl.address3,
       hl.city,
       hl.state,
       ter.nls_territory,
       hl.postal_code,
       hl.province,
       hcsu.site_use_code,
       hcsu.site_use_id,
       hcsa.bill_to_flag
FROM hz_parties hp,
     hz_party_sites hps,
     hz_locations hl,
     hz_cust_accounts_all hca,
     hz_cust_acct_sites_all hcsa,
     hz_cust_site_uses_all hcsu,
     fnd_territories ter
WHERE     hp.party_id = hps.party_id
      AND hps.location_id = hl.location_id
      AND hp.party_id = hca.party_id
      AND hcsa.party_site_id = hps.party_site_id
      AND hcsu.cust_acct_site_id = hcsa.cust_acct_site_id
      AND hca.cust_account_id = hcsa.cust_account_id
      AND hl.country = ter.territory_code
      AND hcsu.site_use_code = 'SHIP_TO'
      AND hca.cust_account_id=:SOLD_TO_ORG_ID --Select SOLD_TO_ORG_ID From oe_order_headers_all
      and hcsu.site_use_id=:SHIP_TO_ORG_ID --Select SHIP_TO_ORG_ID  From oe_order_headers_all 

Thursday, 4 May 2017

Oracle XML Tags Example

<?for-each:G_1?>

<?end for-each?> 

<?split-by-page-break:?>

<xsl:value-of select="position()"/>
<?sum(current-group()/LINE_AMT)?> 

 

<?xdoxslt:sysdate('DD-MON-YYYY HH24:MI')?>

 
<?format-number:field_name;'99G999G999G990D99'?>
 
<?xdoxslt:set_variable($_XDOCTX,'V_OP',CS_OP_VAL_NEW)?>
<?xdoxslt:set_variable($_XDOCTX,'V_CLOS',CS_CL_VAL_NEW)?>
<?xdoxslt:get_variable($_XDOCTX,'V_OP') + RECEIPT_VAL1 - xdoxslt:get_variable($_XDOCTX,'V_CLOS')?>
 
<?sort:current-group()/ITEM_NAME;'ascending';data-type='text'?> 
 
<?xdoxslt:ifelse ((APR) != 0, xdoxslt:div((APR1)*100,(APR)),0)?>
 
<?xdofx:if ../../../../CF_DOCUMENT ='STANDARD' or SECURITY_POR_PO_NUM!=''?> 
<?if: ../../../../CF_DOCUMENT ='STANDARD' or SECURITY_POR_PO_NUM!=''?>


<?if: CF_ACC_CODE='Y'  ?> <?call-template: code_y_acc_y?> <?end if?>
<?if: CF_ACC_CODE='N' ?><?call-template: code_n_acc_n?> <?end if?>



<?template:code_n_acc_n?>
<?end template?>
 
<?template:code_y_acc_y?>
<?end template?>



How to HIDE the COLUMN from the RTF report in ORACLE?
ANS: I am HIDE the Open Value column in rtf.
Goto the Column Heading and the type the Code like:
In the if field we type the code like:
<?if@column:/items/@type="PRIVATE"?>
In the end if field we type the code like:
<?end if?>
Then go to the row of open value field.
In the if field we type the code like:
<?if@column:/items/@type="PRIVATE"?>
In the 20.00 field we type the code like:
<?if@column:/items/@type="PRIVATE"?>  <?CF_OPENING_VAL?>  <?end  if?> 
In the end if field we type the code like:
<?end if?>
Then go to the SUM total of the field and type the code like :
In the if field we type the code like:
<?if@column:/items/@type="PRIVATE"?>
In the end if field we type the code like:
<?end if?>