Wednesday, 18 October 2017

How to find GSTIN Number

First Party GSTIN  Number:

SELECT  'GSTIN : '||b.registration_number
        --INTO   :cp_org_gst
        FROM   ja.jai_party_regs a, ja.jai_party_reg_lines b
       WHERE       org_id = :p_org_id
               AND a.party_id= :P_INV_ORG_ID
               AND a.party_reg_id = b.party_reg_id
               AND a.reg_class_code = b.reg_class_code
               AND a.reg_class_code = 'FIRST_PARTY'
               AND a.party_class_name = 'Transaction Tax'
               AND a.PARTY_TYPE_CODE='IO'
               AND b.registration_type_name = 'GSTIN'
               AND a.party_site_id IS NOT NULL
               AND SYSDATE BETWEEN b.effective_from
                               AND  NVL (b.effective_to, SYSDATE);
 Third Party Supplier GSTIN  Number:

SELECT b.registration_number
FROM   ja.jai_party_regs a, ja.jai_party_reg_lines b
WHERE       a.org_id = aps.org_id
       AND a.party_id = :p_vendor_id
       AND a.party_site_id = :p_vendor_site_id
       AND a.party_reg_id = b.party_reg_id
   AND a.PARTY_TYPE_CODE = 'THIRD_PARTY_SITE'
   AND NVL(a.SUPPLIER_FLAG,'N') = 'Y' AND NVL(SITE_FLAG,'N') = 'Y'
   AND b.REGISTRATION_TYPE_CODE = 'GSTN'
   AND SYSDATE BETWEEN b.effective_from  AND  NVL (b.effective_to, SYSDATE) 

How To Find HSN Code

/***From Base Table***/

SELECT reporting_code
INTO v_hsn_code
FROM apps.jai_item_templ_hdr jith,
     apps.jai_reporting_associations jra,
     apps.jai_regimes jr
WHERE     jith.template_hdr_id = jra.entity_id
      AND jra.reporting_usage = 'LR'
      --and jra.EFFECTIVE_FROM='01-JAN-2017'
      AND jra.effective_to IS NULL
      --and jra.REGIME_CODE='GST'
      AND jr.regime_id = jra.regime_id
      AND jr.regime_code = 'GST'
      AND jith.inventory_item_id = :inventory_item_id
GROUP BY reporting_code

Friday, 6 October 2017

How to find all concurrent program in a query

SELECT   va.application_name,
         a.user_concurrent_program_name AS user_concurrent_program_name,
         va.execution_file_name short_name,
         va.executable_name executable_name,
         NVL ((SELECT                                             ---LOB_CODE,
                      file_name
                 FROM xdo_lobs
                WHERE application_short_name = 'XXCUS'
                  AND xdo_file_type = 'RTF'
                  AND UPPER (lob_code) = UPPER (va.executable_name)),
              'NOT RTF'
             ) rtf_name,
         a.concurrent_program_name AS concurrent_program_name,
         DECODE (a.execution_method_code,
                 'P', 'Oracel Report',
                 'I', 'PL/SQL Stored Procedure',
                 a.execution_method_code
                ) report_type,
         a.output_file_type, a.output_print_style,
         c.application_short_name AS application_short_name,
         b.column_seq_num AS column_seq_num, b.srw_param AS param_seq,
         d.flex_value_set_name AS values_set_name,
         'Select '
        ||CHR(10)||FVT.VALUE_COLUMN_NAME||' VALUE ' ||
         Decode (VALUE_COLUMN_TYPE, 'C', 'CHR', 'N', 'NUMBER', 'V', 'VARCHAR2', 'D', 'Date') ||
         '  Size  '||VALUE_COLUMN_SIZE ||' ,'
        ||CHR(10)||FVT.MEANING_COLUMN_NAME||' MEANING '||
        Decode (VALUE_COLUMN_TYPE, 'C', 'CHR', 'N', 'NUMBER', 'V', 'VARCHAR2', 'D', 'Date') ||
         '  Size  '||VALUE_COLUMN_SIZE ||' ,'
        ||CHR(10)||FVT.ID_COLUMN_NAME||' ID '||     
        Decode (VALUE_COLUMN_TYPE, 'C', 'CHR', 'N', 'NUMBER', 'V', 'VARCHAR2', 'D', 'Date') ||
         '  Size  '||VALUE_COLUMN_SIZE ||' '
        ||CHR(10)||' From '||FVT.APPLICATION_TABLE_NAME "SELECT_FROM" ,
         FVT.ADDITIONAL_WHERE_CLAUSE "Where",
         FVT.ADDITIONAL_QUICKPICK_COLUMNS ADDITIONAL_COLUMN,
         DECODE (b.default_type,
                 'S', 'SQL Statement',
                 'P', 'Profile'
                ) default_type,
         b.DEFAULT_VALUE,
         DECODE (b.required_flag, 'Y', 'YES', 'N', 'NO') required_type,
         DECODE (b.display_flag, 'Y', 'YES', 'N', 'NO') display_type,
         b.form_left_prompt AS prompt
    FROM fnd_concurrent_programs_vl a,
         fnd_descr_flex_col_usage_vl b,
         fnd_application c,
         fnd_flex_value_sets d,
         fnd_executables_form_v va, FND_FLEX_VALIDATION_TABLES FVT
   WHERE a.enabled_flag = 'Y'
     AND a.application_id = va.application_id
     AND TRIM (va.executable_name) = TRIM (a.concurrent_program_name)
     AND a.concurrent_program_name =
                                 SUBSTR (b.descriptive_flexfield_name, 7, 100)
     AND a.application_id = c.application_id
     AND b.enabled_flag = 'Y'
     and va.execution_file_name like '%XX%'
     AND b.flex_value_set_id = d.flex_value_set_id
     AND b.flex_value_set_id = fvt.flex_value_set_id (+)
     AND a.application_id = 20003                      --(Your Application Id)
ORDER BY a.concurrent_program_id, b.column_seq_num, 1;

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?> 

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

Serial Number : <xsl:value-of select="position()"/>

Summation column : <?sum(current-group()/LINE_AMT)?> 
 
<?end body?>
 SQL function:
<?xdofx:decode(C_AMT_DUE_REM_INV,0,'',C_AMT_DUE_REM_INV)?> 
 
Current date : 
<?xdoxslt:sysdate('DD-MON-YYYY HH24:MI')?>
<?xdoxslt:sysdate('Day Month DD,YYYY')?> 
 
Format Number: 
 <?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')?> 
 
 Sorting:
<?sort:SOURCE1;'ascending';data-type='text'?>
<?sort:SOURCE1;'descending';data-type='text'?>
<?sort:current-group()/ITEM_NAME;'ascending';data-type='text'?> 

 
Condition:
<?xdoxslt:ifelse ((APR) != 0, xdoxslt:div((APR1)*100,(APR)),0)?>

<?xdofx:to_number()?>

<?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?>



<?xdoxslt:toWordsAmt(CS_IGST_AMT)?>

<?template:code_n_acc_n?>

<?end template?>

 

<?template:code_y_acc_y?>

<?end template?>




HIDE the COLUMN from the RTF report in ORACLE

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?>
 

Displaying image dynamically in XML Publisher (BI Tools) in RTF Template :

Right click on company logo image and click “Size” then goto “AltText” tab 
and enter dynamic path in “Alternative Text” field.
e.g. url:{concat(‘${OA_MEDIA}’,’/JG_LOGO.jpg’)}.