GST Reporting Types Interface

--HSN And SAC Code Migration

CREATE TABLE XXJG.JG_HSN_REPORTING_CODES
(
  REPORTING_CODE              VARCHAR2(30 BYTE),
  REPORTING_CODE_DESCRIPTION  VARCHAR2(1500 BYTE),
  EFFECTIVE_FROM              DATE,
  STATUS                      VARCHAR2(30 BYTE),
  ERROR_MSG                   VARCHAR2(240 BYTE)
)
/

CREATE OR REPLACE PUBLIC SYNONYM JG_HSN_REPORTING_CODES FOR XXJG.JG_HSN_REPORTING_CODES
/

CREATE TABLE XXJG.JG_SAC_REPORTING_CODES
(
  REPORTING_CODE              VARCHAR2(30 BYTE),
  REPORTING_CODE_DESCRIPTION  VARCHAR2(1500 BYTE),
  EFFECTIVE_FROM              DATE,
  STATUS                      VARCHAR2(30 BYTE),
  ERROR_MSG                   VARCHAR2(240 BYTE)
)
/

CREATE OR REPLACE PUBLIC SYNONYM JG_SAC_REPORTING_CODES FOR XXJG.JG_SAC_REPORTING_CODES
/





CREATE OR REPLACE PROCEDURE APPS.XXJG_REP_TYPE_HSN (ERRBUF              OUT VARCHAR2,
                                                     RETCODE             OUT NUMBER) AS
   L_REPORTING_TYPE_ID   NUMBER;
   L_EFFECTIVE_FROM      DATE := '01-JUL-2017';
   L_CREATED_BY          NUMBER;
   L_LAST_UPDATED_BY     NUMBER;
   L_LAST_UPDATE_LOGIN   NUMBER := 01072017;

   V_COUNT               NUMBER;

   CURSOR CUR_HSN
   IS
      SELECT ROWID,
             TRIM (REPLACE (REPORTING_CODE, CHR (13), NULL)) REPORTING_CODE,
             TRIM (REPLACE (REPORTING_CODE_DESCRIPTION, CHR (13), NULL))
                REPORTING_CODE_DESCRIPTION
        FROM JG_HSN_REPORTING_CODES HSN
       WHERE 1 = 1 ; -- AND REPORTING_CODE = '12131400';
BEGIN
   BEGIN
      L_CREATED_BY := NULL;
      L_LAST_UPDATED_BY := NULL;

      SELECT USER_ID, USER_ID
        INTO L_CREATED_BY, L_LAST_UPDATED_BY
        FROM APPS.FND_USER
       WHERE 1 = 1 AND UPPER (USER_NAME) = 'XYZAGM';
   END;

   BEGIN
      L_REPORTING_TYPE_ID := NULL;

      SELECT REPORTING_TYPE_ID
        INTO L_REPORTING_TYPE_ID
        FROM APPS.JAI_REPORTING_TYPES
       WHERE 1 = 1 AND REPORTING_TYPE_CODE = 'GST_HSN_CODE';
   END;

   FOR REC IN CUR_HSN
   LOOP
      V_COUNT := NULL;

      BEGIN
         SELECT COUNT (REPORTING_CODE)
           INTO V_COUNT
           FROM APPS.JAI_REPORTING_CODES
          WHERE REPORTING_CODE = REC.REPORTING_CODE
            AND REPORTING_TYPE_ID = L_REPORTING_TYPE_ID;
      END;

      IF V_COUNT = 0
      THEN
         INSERT INTO APPS.JAI_REPORTING_CODES (REPORTING_CODE_ID,
                                               REPORTING_TYPE_ID,
                                               REPORTING_CODE,
                                               REPORTING_CODE_DESCRIPTION,
                                               EFFECTIVE_FROM,
                                               EFFECTIVE_TO,
                                               CREATION_DATE,
                                               CREATED_BY,
                                               LAST_UPDATE_DATE,
                                               LAST_UPDATE_LOGIN,
                                               LAST_UPDATED_BY,
                                               RECORD_TYPE_CODE)
              VALUES (JAI_REPORTING_CODES_S.NEXTVAL,
                      L_REPORTING_TYPE_ID,
                      REC.REPORTING_CODE,
                      REC.REPORTING_CODE_DESCRIPTION,
                      L_EFFECTIVE_FROM,
                      NULL,
                      SYSDATE,
                      L_CREATED_BY,
                      SYSDATE,
                      L_LAST_UPDATE_LOGIN,
                      L_LAST_UPDATED_BY,
                      NULL);

         UPDATE JG_HSN_REPORTING_CODES
            SET STATUS = 'COMPLETE', ERROR_MSG = NULL
          WHERE ROWID = REC.ROWID;

         DBMS_OUTPUT.PUT_LINE ('SUCCESS');
      ELSE
         UPDATE JG_HSN_REPORTING_CODES
            SET STATUS = 'ERROR',
                ERROR_MSG =
                   'REPORTING CODE IS ALREADY EXISTS IN BASE TABLE APPS.JAI_REPORTING_CODES'
          WHERE ROWID = REC.ROWID;

         DBMS_OUTPUT.PUT_LINE ('FAILED');
      END IF;

      COMMIT;
   END LOOP;
END;
/








CREATE OR REPLACE PROCEDURE APPS.XXJG_REP_TYPE_SAC (ERRBUF              OUT VARCHAR2,
                                                     RETCODE             OUT NUMBER) AS
   L_REPORTING_TYPE_ID   NUMBER;
   L_EFFECTIVE_FROM      DATE := '01-JUL-2017';
   L_CREATED_BY          NUMBER;
   L_LAST_UPDATED_BY     NUMBER;
   L_LAST_UPDATE_LOGIN   NUMBER := 01072017;

   V_COUNT               NUMBER;

   CURSOR CUR_SAC
   IS
      SELECT ROWID,
             TRIM (REPLACE (REPORTING_CODE, CHR (13), NULL)) REPORTING_CODE,
             TRIM (REPLACE (REPORTING_CODE_DESCRIPTION, CHR (13), NULL))
                REPORTING_CODE_DESCRIPTION
        FROM JG_SAC_REPORTING_CODES SAC
       WHERE 1 = 1 ; -- AND REPORTING_CODE = '996633';
BEGIN
   BEGIN
      L_CREATED_BY := NULL;
      L_LAST_UPDATED_BY := NULL;

      SELECT USER_ID, USER_ID
        INTO L_CREATED_BY, L_LAST_UPDATED_BY
        FROM APPS.FND_USER
       WHERE 1 = 1 AND UPPER (USER_NAME) = 'XYZAGM';
   END;

   BEGIN
      L_REPORTING_TYPE_ID := NULL;

      SELECT REPORTING_TYPE_ID
        INTO L_REPORTING_TYPE_ID
        FROM APPS.JAI_REPORTING_TYPES
       WHERE 1 = 1 AND REPORTING_TYPE_CODE = 'GST_SAC_CODE_ITEM';
   END;

   FOR REC IN CUR_SAC
   LOOP
      V_COUNT := NULL;

      BEGIN
         SELECT COUNT (REPORTING_CODE)
           INTO V_COUNT
           FROM APPS.JAI_REPORTING_CODES
          WHERE REPORTING_CODE = REC.REPORTING_CODE
            AND REPORTING_TYPE_ID = L_REPORTING_TYPE_ID;
      END;

      IF V_COUNT = 0
      THEN
         INSERT INTO APPS.JAI_REPORTING_CODES (REPORTING_CODE_ID,
                                               REPORTING_TYPE_ID,
                                               REPORTING_CODE,
                                               REPORTING_CODE_DESCRIPTION,
                                               EFFECTIVE_FROM,
                                               EFFECTIVE_TO,
                                               CREATION_DATE,
                                               CREATED_BY,
                                               LAST_UPDATE_DATE,
                                               LAST_UPDATE_LOGIN,
                                               LAST_UPDATED_BY,
                                               RECORD_TYPE_CODE)
              VALUES (JAI_REPORTING_CODES_S.NEXTVAL,
                      L_REPORTING_TYPE_ID,
                      REC.REPORTING_CODE,
                      REC.REPORTING_CODE_DESCRIPTION,
                      L_EFFECTIVE_FROM,
                      NULL,
                      SYSDATE,
                      L_CREATED_BY,
                      SYSDATE,
                      L_LAST_UPDATE_LOGIN,
                      L_LAST_UPDATED_BY,
                      NULL);

         UPDATE JG_SAC_REPORTING_CODES
            SET STATUS = 'COMPLETE', ERROR_MSG = NULL
          WHERE ROWID = REC.ROWID;

         DBMS_OUTPUT.PUT_LINE ('SUCCESS');
      ELSE
         UPDATE JG_SAC_REPORTING_CODES
            SET STATUS = 'ERROR',
                ERROR_MSG =
                   'REPORTING CODE IS ALREADY EXISTS IN BASE TABLE APPS.JAI_REPORTING_CODES'
          WHERE ROWID = REC.ROWID;

         DBMS_OUTPUT.PUT_LINE ('FAILED');
      END IF;

      COMMIT;
   END LOOP;
END;
/






-----------------------------------------------------------------------------------------------------------
Manish Kumar T

Oracle GST Tax for AR Receipts Query

SELECT   ara.receivable_application_id, ara.cash_receipt_id,
         jtl.tax_rate_id, jtl.tax_regime_id
         ,jtdf.det_factor_id det_factor_id
         ,jtl.tax_line_id tax_line_id
         ,(Select trx_number from ra_customer_trx_all where customer_trx_id=ara.applied_customer_trx_id)invoice_number
         ,trim(acr.receipt_number)receipt_number
         ,acr.RECEIPT_DATE
         ,ara.gl_date
         ,ara.line_applied line_applied_amt
         ,jtdf.line_amt trx_amount
         ,jtl.rounded_tax_amt_trx_curr tax_amt_trx_curr
         ,jtl.rounded_tax_amt_fun_curr tax_amt_fun_curr
         ,xal.*
    FROM ar_receivable_applications_all ara,
         ar_cash_receipts_all acr,
         jai_tax_det_factors jtdf,
         jai_tax_lines jtl
         ,xla.xla_transaction_entities te
         ,xla_ae_headers xah
         ,xla_ae_lines xal
WHERE    trim(acr.receipt_number) = NVL(:ar_receipt_number,trim(acr.receipt_number))
     AND ara.cash_receipt_id = acr.cash_receipt_id
     AND ara.status = 'APP'
     AND ara.display = 'Y'
     AND ara.set_of_books_id = :p_ledger_id
     AND ara.org_id = :p_org_id
     AND ara.gl_date BETWEEN :p_start_date AND :p_end_date
     AND ara.cash_receipt_id = jtdf.trx_id
     AND jtdf.application_id = 222
     AND jtdf.entity_code = 'RECEIPTS'
     AND jtdf.event_class_code = 'RECEIPT'
     AND jtdf.det_factor_id = jtl.det_factor_id
     and nvl (te.source_id_int_1, -99) = acr.cash_receipt_id
   and te.application_id = 222
   and te.entity_code = 'RECEIPTS'
   and xah.entity_id = te.entity_id
   and xah.application_id = te.application_id
   and xah.event_type_code ='RECP_CREATE'-- 'MANUAL'
   and xah.ae_header_id = xal.ae_header_id
   and xal.accounting_class_code='RECEIVABLE'

How to find Supplier 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 = :p_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 = 'GSTIN' --Depen on Setup
   AND SYSDATE BETWEEN b.effective_from  AND  NVL (b.effective_to, SYSDATE) 

How To Find HSN Code

/***Query to Item 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' ---Depen on setup

      AND jith.organization_id = :inv_org_id
      AND jith.inventory_item_id = :inventory_item_id
GROUP BY reporting_code;

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;

Oracle EBS R12 STATE CODE Function

--Oracle EBS R12 STATE CODE Lookup

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;