Bug 3218 - json file for einvoicing
Summary: json file for einvoicing
Status: RESOLVED USER TO VERIFY
Alias: None
Product: IMIS SALES
Classification: 01.IMIS
Component: IMIS SALES FORMS AND REPORTS (show other bugs)
Version: unspecified
Hardware: All All
: Normal major
Deadline: 2020-03-05
Assignee: Mahender
URL:
: 2870 (view as bug list)
Depends on:
Blocks:
 
Reported: 2020-03-03 19:56 IST by kk
Modified: 2020-10-16 12:08 IST (History)
2 users (show)

See Also:
Database: ALL
Module ID:

Attachments
imis einv json schema (4.62 KB, application/json)
2020-03-03 19:56 IST, kk 		Details
imis json schema for e-invoice V2 (4.57 KB, application/json)
2020-03-03 22:59 IST, kk 		Details
json schema for einvoice (23.75 KB, application/json)
2020-03-04 14:09 IST, kk 		Details
einvoice JSON schema V7 (25.31 KB, application/json)
2020-03-06 16:24 IST, kk 		Details
imis einvoice schema v2 (4.39 KB, application/json)
2020-03-06 16:25 IST, kk 		Details
Show Obsolete (2) View All Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Comment 26 Mahender 2020-10-16 12:08:19 IST 
this is done
Comment 15 Mahender 2020-03-11 16:24:21 IST 
SET SERVEROUTPUT ON
set serveroutput on size 1000000;
DECLARE
  P_SI_RECID   		SALE_INVOICE.SI_RECID%TYPE := 20000005797050 ;
  SALE_INVOICE_ROW	VW_GST_SALE_INVOICES%ROWTYPE;
  CUSTOMER_ROW		CUSTOMERS%ROWTYPE;
  CUST_STATE_ROW	STATES%ROWTYPE;
  CO_STATE_ROW		STATES%ROWTYPE;
BEGIN

  APEX_JSON.initialize_clob_output;

  APEX_JSON.open_object; --{ main

  SELECT S.*
  INTO   SALE_INVOICE_ROW
  FROM   VW_GST_SALE_INVOICES S
  WHERE  S.DOCUMENT_RECID = P_SI_RECID;

  SELECT C.*
  INTO   CUSTOMER_ROW
  FROM   CUSTOMERS C
  WHERE  COMPANY		= SALE_INVOICE_ROW.COMPANY_CODE
  AND    CUSTOMER_CODE  = SALE_INVOICE_ROW.PARTY_CODE;

  SELECT S.*
  INTO CUST_STATE_ROW
  FROM STATES S
  WHERE  STATE_CODE		= CUSTOMER_ROW.STATE_CODE
  AND    COUNTRY_CODE	= CUSTOMER_ROW.COUNTRY_CODE;


  SELECT S.*
  INTO CO_STATE_ROW
  FROM STATES S
  WHERE  STATE_CODE		= PKG_CONTROLS.GET_COMPANY_INFO(SALE_INVOICE_ROW.COMPANY_CODE,'STATE_CODE')
  AND    COUNTRY_CODE	= PKG_CONTROLS.GET_COMPANY_INFO(SALE_INVOICE_ROW.COMPANY_CODE,'COUNTRY_CODE');

  APEX_JSON.write('$schema', 'http://json-schema.org/draft-07/schema#');
  APEX_JSON.write('Title', 'GST-India Invoice Document ');
  APEX_JSON.write('Description', 'GST Invoice format for IRN Generation in INDIA');
  APEX_JSON.write('TaxSch', 'GST');
  APEX_JSON.write('Version', '1.01');
  APEX_JSON.write('Irn', 'XXX-64-XXX');

  APEX_JSON.open_object('TranDtls'); --{
  APEX_JSON.write('TaxSch', 'GST');
  APEX_JSON.write('RegRev', 'Y');
  APEX_JSON.write('SupTyp', CASE WHEN SALE_INVOICE_ROW.CUSTOMER_TYPE='E' THEN 'DEXP' ELSE 'B2B' END);
  APEX_JSON.close_object; -- } TransDtls

  APEX_JSON.open_object('DocDtls'); --{
  APEX_JSON.write('Typ', 'INV');
  APEX_JSON.write('No',SALE_INVOICE_ROW.DOCUMENT_TYPE||'/'||SALE_INVOICE_ROW.DOCUMENT_SERIES||'/'||SALE_INVOICE_ROW.DOCUMENT_NUMBER);
  APEX_JSON.write('Dt', TO_CHAR(SALE_INVOICE_ROW.DOCUMENT_DATE,'DD/MM/YYYY'));
  APEX_JSON.close_object; --} DocDtls

  APEX_JSON.open_object('SellerDtls'); --{
  APEX_JSON.write('Gstin', PKG_CONTROLS.GET_REGN_NUMBER(SALE_INVOICE_ROW.COMPANY_CODE,'GST'));
  APEX_JSON.write('LglNm', PKG_REPORTS.GET_COMPANY_NAME(SALE_INVOICE_ROW.COMPANY_CODE,','));
  APEX_JSON.write('Addr1', PKG_REPORTS.GET_COMPANY_ADDRESS(SALE_INVOICE_ROW.COMPANY_CODE));
  APEX_JSON.write('Loc',   PKG_CONTROLS.GET_COMPANY_INFO(SALE_INVOICE_ROW.COMPANY_CODE,'LOCATION_NAME'));
  APEX_JSON.write('Dst',   PKG_CONTROLS.GET_COMPANY_INFO(SALE_INVOICE_ROW.COMPANY_CODE,'DISTRICT_CODE'));
  APEX_JSON.write('Pin',   to_number(PKG_CONTROLS.GET_COMPANY_INFO(SALE_INVOICE_ROW.COMPANY_CODE,'POSTAL_CODE')));
  APEX_JSON.write('State',  PKG_CONTROLS.GET_COMPANY_INFO(SALE_INVOICE_ROW.COMPANY_CODE,'STATE_NAME'));
  APEX_JSON.close_object; --} SellerDtls

  APEX_JSON.open_object('BuyerDtls');--{
  APEX_JSON.write('Gstin', PKG_AR.GET_REGN_NUMBER(SALE_INVOICE_ROW.COMPANY_CODE,SALE_INVOICE_ROW.PARTY_CODE,'GST'));
  APEX_JSON.write('LglNm', PKG_AR.GET_CUSTOMER_NAME(SALE_INVOICE_ROW.PARTY_CODE));
  APEX_JSON.write('Addr1', PKG_AR.GET_CUSTOMER_ADDRESS(SALE_INVOICE_ROW.PARTY_CODE,SALE_INVOICE_ROW.ADDRESS_CODE));
  APEX_JSON.write('Loc',   PKG_AR.GET_STATION_NAME(SALE_INVOICE_ROW.PARTY_CODE));
  APEX_JSON.write('Pin',   to_number(CUSTOMER_ROW.POSTAL_CODE));
  APEX_JSON.write('State',  PKG_AR.GET_STATE_NAME(SALE_INVOICE_ROW.PARTY_CODE));
  APEX_JSON.close_object; --} BuyerDtls

  APEX_JSON.open_object('ItemList'); --{
 	APEX_JSON.open_array('ITEM'); --[
  FOR cur_rec IN (SELECT * FROM VW_GST_INVOICE_ITEMS I WHERE I.PARENT_RECID = P_SI_RECID )
  LOOP
    APEX_JSON.open_object; --{
    APEX_JSON.write('SlNo', cur_rec.SEQ_NUMBER);
    APEX_JSON.write('PrdDesc', cur_rec.STOCK_NAME);
    APEX_JSON.write('HsnCd', cur_rec.EXCISE_TARIFF);
    APEX_JSON.write('Qty', cur_rec.NO_OF_PACKS);
    APEX_JSON.write('FreeQty', 0);
    APEX_JSON.write('Unit', /*cur_rec.IUOM_CODE*/ 'NOS');
    APEX_JSON.write('UnitPrice', cur_rec.NET_PRICE);
    APEX_JSON.write('TotAmt', cur_rec.ITEM_VALUE);
    APEX_JSON.write('Discount', NVL(cur_rec.NDIS_AMOUNT,0)+NVL(cur_rec.TDIS_AMOUNT,0)+NVL(cur_rec.SDIS_AMOUNT,0));
    APEX_JSON.write('OthChrg', 0);
    APEX_JSON.write('AssAmt', cur_rec.COST_AMOUNT);
    APEX_JSON.write('CgstRt', cur_rec.CGST_RATE);
    APEX_JSON.write('SgstRt', cur_rec.SGST_RATE);
    APEX_JSON.write('IgstRt', cur_rec.IGST_RATE);
    APEX_JSON.write('CesRt', cur_rec.CESS_RATE);
    APEX_JSON.write('CesNonAdval', cur_rec.CESS_ON_QTY);
    APEX_JSON.write('StateCes', 0);
    APEX_JSON.write('TotItemVal', cur_rec.ITEM_VALUE);

    APEX_JSON.close_object; --} ITEM
  END LOOP;
  APEX_JSON.close_array; --]
  APEX_JSON.close_object; --} ItemList

  APEX_JSON.open_object('ValDtls'); --{
  APEX_JSON.write('AssVal', 	SALE_INVOICE_ROW.COST_OF_GOODS);
  APEX_JSON.write('CgstVal', 	SALE_INVOICE_ROW.CGST_AMOUNT);
  APEX_JSON.write('SgstVal',   	SALE_INVOICE_ROW.SGST_AMOUNT);
  APEX_JSON.write('IgstVal',   	SALE_INVOICE_ROW.IGST_AMOUNT);
  APEX_JSON.write('CesVal',  	SALE_INVOICE_ROW.CESS_AMOUNT);
  APEX_JSON.write('StCesVal',  	0);
  APEX_JSON.write('CesNonAdVal',SALE_INVOICE_ROW.CESS_ON_QTY);
  APEX_JSON.write('TotInvVal',  SALE_INVOICE_ROW.VALUE_OF_INVOICE);

  APEX_JSON.close_object; --} ValDtls
  DELETE JSON_INVOICE_DOCUMENT;
  INSERT INTO JSON_INVOICE_DOCUMENT (INV_DOC) VALUES(APEX_JSON.get_clob_output);
  DBMS_OUTPUT.put_line(APEX_JSON.get_clob_output);
  APEX_JSON.close_object; --} main

  APEX_JSON.free_output;
END;
Comment 14 Mahender 2020-03-11 15:18:57 IST 
SET SERVEROUTPUT ON
set serveroutput on size 1000000;
DECLARE
  P_SI_RECID   		SALE_INVOICE.SI_RECID%TYPE := 20000005797050 ;
  SALE_INVOICE_ROW	VW_GST_SALE_INVOICES%ROWTYPE;
  CUSTOMER_ROW		CUSTOMERS%ROWTYPE;
  CUST_STATE_ROW	STATES%ROWTYPE;
  CO_STATE_ROW		STATES%ROWTYPE;
BEGIN

  APEX_JSON.initialize_clob_output;

  APEX_JSON.open_object; --{ main

  SELECT S.*
  INTO   SALE_INVOICE_ROW
  FROM   VW_GST_SALE_INVOICES S
  WHERE  S.DOCUMENT_RECID = P_SI_RECID;

  SELECT C.*
  INTO   CUSTOMER_ROW
  FROM   CUSTOMERS C
  WHERE  COMPANY		= SALE_INVOICE_ROW.COMPANY_CODE
  AND    CUSTOMER_CODE  = SALE_INVOICE_ROW.PARTY_CODE;

  SELECT S.*
  INTO CUST_STATE_ROW
  FROM STATES S
  WHERE  STATE_CODE		= CUSTOMER_ROW.STATE_CODE
  AND    COUNTRY_CODE	= CUSTOMER_ROW.COUNTRY_CODE;


  SELECT S.*
  INTO CO_STATE_ROW
  FROM STATES S
  WHERE  STATE_CODE		= PKG_CONTROLS.GET_COMPANY_INFO(SALE_INVOICE_ROW.COMPANY_CODE,'STATE_CODE')
  AND    COUNTRY_CODE	= PKG_CONTROLS.GET_COMPANY_INFO(SALE_INVOICE_ROW.COMPANY_CODE,'COUNTRY_CODE');

  APEX_JSON.write('$schema', 'http://json-schema.org/draft-07/schema#');
  APEX_JSON.write('Title', 'GST-India Invoice Document ');
  APEX_JSON.write('Description', 'GST Invoice format for IRN Generation in INDIA');
  APEX_JSON.write('TaxSch', 'GST');
  APEX_JSON.write('Version', '1.01');
  APEX_JSON.write('Irn', 'XXX-64-XXX');

  APEX_JSON.open_object('TranDtls'); --{
  APEX_JSON.write('TaxSch', 'GST');
  APEX_JSON.write('RegRev', 'Y');
  APEX_JSON.write('SupTyp', CASE WHEN SALE_INVOICE_ROW.CUSTOMER_TYPE='E' THEN 'DEXP' ELSE 'B2B' END);
  APEX_JSON.close_object; -- } TransDtls

  APEX_JSON.open_object('DocDtls'); --{
  APEX_JSON.write('Typ', 'INV');
  APEX_JSON.write('No',SALE_INVOICE_ROW.DOCUMENT_TYPE||'/'||SALE_INVOICE_ROW.DOCUMENT_SERIES||'/'||SALE_INVOICE_ROW.DOCUMENT_NUMBER);
  APEX_JSON.write('Dt', TO_CHAR(SALE_INVOICE_ROW.DOCUMENT_DATE,'DD/MM/YYYY'));
  APEX_JSON.close_object; --} DocDtls

  APEX_JSON.open_object('SellerDtls'); --{
  APEX_JSON.write('Gstin', PKG_CONTROLS.GET_REGN_NUMBER(SALE_INVOICE_ROW.COMPANY_CODE,'GST'));
  APEX_JSON.write('LglNm', PKG_REPORTS.GET_COMPANY_NAME(SALE_INVOICE_ROW.COMPANY_CODE,','));
  APEX_JSON.write('Addr1', PKG_REPORTS.GET_COMPANY_ADDRESS(SALE_INVOICE_ROW.COMPANY_CODE));
  APEX_JSON.write('Loc',   PKG_CONTROLS.GET_COMPANY_INFO(SALE_INVOICE_ROW.COMPANY_CODE,'LOCATION_NAME'));
  APEX_JSON.write('Dst',   PKG_CONTROLS.GET_COMPANY_INFO(SALE_INVOICE_ROW.COMPANY_CODE,'DISTRICT_CODE'));
  APEX_JSON.write('Pin',   to_number(PKG_CONTROLS.GET_COMPANY_INFO(SALE_INVOICE_ROW.COMPANY_CODE,'POSTAL_CODE')));
  APEX_JSON.write('Stcd',  PKG_CONTROLS.GET_COMPANY_INFO(SALE_INVOICE_ROW.COMPANY_CODE,'STATE_NAME'));
  APEX_JSON.close_object; --} SellerDtls

  APEX_JSON.open_object('BuyerDtls');--{
  APEX_JSON.write('Gstin', PKG_AR.GET_REGN_NUMBER(SALE_INVOICE_ROW.COMPANY_CODE,SALE_INVOICE_ROW.PARTY_CODE,'GST'));
  APEX_JSON.write('LglNm', PKG_AR.GET_CUSTOMER_NAME(SALE_INVOICE_ROW.PARTY_CODE));
  APEX_JSON.write('Addr1', PKG_AR.GET_CUSTOMER_ADDRESS(SALE_INVOICE_ROW.PARTY_CODE,SALE_INVOICE_ROW.ADDRESS_CODE));
  APEX_JSON.write('Loc',   PKG_AR.GET_STATION_NAME(SALE_INVOICE_ROW.PARTY_CODE));
  APEX_JSON.write('Pin',   to_number(CUSTOMER_ROW.POSTAL_CODE));
  APEX_JSON.write('Stcd',  PKG_AR.GET_STATE_NAME(SALE_INVOICE_ROW.PARTY_CODE));
  APEX_JSON.close_object; --} BuyerDtls

  APEX_JSON.open_object('ItemList'); --{
 	APEX_JSON.open_array('ITEM'); --[
  FOR cur_rec IN (SELECT * FROM VW_GST_INVOICE_ITEMS I WHERE I.PARENT_RECID = P_SI_RECID )
  LOOP
    APEX_JSON.open_object; --{
    APEX_JSON.write('SlNo', cur_rec.SEQ_NUMBER);
    APEX_JSON.write('PrdDesc', cur_rec.STOCK_NAME);
    APEX_JSON.write('HsnCd', cur_rec.EXCISE_TARIFF);
    APEX_JSON.write('Qty', cur_rec.NO_OF_PACKS);
    APEX_JSON.write('FreeQty', 0);
    APEX_JSON.write('Unit', /*cur_rec.IUOM_CODE*/ 'NOS');
    APEX_JSON.write('UnitPrice', cur_rec.NET_PRICE);
    APEX_JSON.write('TotAmt', cur_rec.ITEM_VALUE);
    APEX_JSON.write('Discount', NVL(cur_rec.NDIS_AMOUNT,0)+NVL(cur_rec.TDIS_AMOUNT,0)+NVL(cur_rec.SDIS_AMOUNT,0));
    APEX_JSON.write('OthChrg', 0);
    APEX_JSON.write('AssAmt', cur_rec.COST_AMOUNT);
    APEX_JSON.write('CgstRt', cur_rec.CGST_RATE);
    APEX_JSON.write('SgstRt', cur_rec.SGST_RATE);
    APEX_JSON.write('IgstRt', cur_rec.IGST_RATE);
    APEX_JSON.write('CesRt', cur_rec.CESS_RATE);
    APEX_JSON.write('CesNonAdval', cur_rec.CESS_ON_QTY);
    APEX_JSON.write('StateCes', 0);
    APEX_JSON.write('TotItemVal', cur_rec.ITEM_VALUE);

    APEX_JSON.close_object; --} ITEM
  END LOOP;
  APEX_JSON.close_array; --]
  APEX_JSON.close_object; --} ItemList

  APEX_JSON.open_object('ValDtls'); --{
  APEX_JSON.write('AssVal', 	SALE_INVOICE_ROW.COST_OF_GOODS);
  APEX_JSON.write('CgstVal', 	SALE_INVOICE_ROW.CGST_AMOUNT);
  APEX_JSON.write('SgstVal',   	SALE_INVOICE_ROW.SGST_AMOUNT);
  APEX_JSON.write('IgstVal',   	SALE_INVOICE_ROW.IGST_AMOUNT);
  APEX_JSON.write('CesVal',  	SALE_INVOICE_ROW.CESS_AMOUNT);
  APEX_JSON.write('StCesVal',  	0);
  APEX_JSON.write('CesNonAdVal',SALE_INVOICE_ROW.CESS_ON_QTY);
  APEX_JSON.write('TotInvVal',  SALE_INVOICE_ROW.VALUE_OF_INVOICE);

  APEX_JSON.close_object; --} ValDtls
  DELETE JSON_INVOICE_DOCUMENT;
  INSERT INTO JSON_INVOICE_DOCUMENT (INV_DOC) VALUES(APEX_JSON.get_clob_output);
  DBMS_OUTPUT.put_line(APEX_JSON.get_clob_output);
  APEX_JSON.close_object; --} main

  APEX_JSON.free_output;
END;
Comment 12 kk 2020-03-11 13:34:39 IST 
Validations on Items:

    Sl.no of the item is verified for duplicate values.
    Each item needs to have valid HSN code with at least 4 digits. That is, items of goods type should be 4 or 6 or 8 digits and items of service type should be 4 or 5 or 6 digits. HSN Code should be valid as per the GST master.
    If Is_Service is selected, then the HSN codes must belong to services.
    Each item should have valid Unit Quantity Code (UQC) as per the master codes, in case of goods.
    Quantity and Unit Quantity Code are mandatory for Goods and optional for Services.
    Tax rates are being validated. Only the allowed tax rates will be accepted.
    In case of intra-state transaction, the sum of SGST and CGST tax rates should be entered as GST Rate.
    In case of inter-state transaction, the IGST tax rate and value has to be passed.
    In case of export transaction, IGST tax rate and value has to be passed.
    In case the buyer is SEZ unit, then IGST tax rate and value has to be passed irrespective of state of the buyer.
    Maximum number of items in each invoice should not exceed more than 1000 items.

Calculation of Values:

    The following summation validations are to be done for items
        Gross Amount of Item = Quantity X Selling Unit Price
        Taxable Value of Item = Gross Amount of Item ¿ Discount
        SGST Value of Item = Taxable Value of Item X GST Rate / 2, if intra-state
        CGST Value of Item = Taxable Value of Item X GST Rate / 2, if intra-state
        IGST Value of Item = Taxable Value of Item X GST Rate, if inter-state
        Cess Value of Item = Taxable Value of Item X Cess Rate
        State Cess Value of Item = Taxable Value of Item X State Cess Rate
        Total Value of Item = Taxable Value of Item + SGST Value of Item + CGST Value of Item + IGST Value of Item + Cess Value of Item + State Cess Value of Item + Non-Advol Cess Value of Item + State Cess Non-advol value of Item + Other charges.
    The following summation validations are to be done on Invoice total
        Total Taxable Value = Taxable Value of all Items
        Total SGST Value = SGST Value of all Items
        Total CGST Value = CGST Value of all Items
        Total IGST Value = IGST Value of all Items
        Total Cess Value = Cess Value of all Items + Non-Advol Cess Value of all Items
        Total State Cess Value = State Cess Value of all Items + State Cess Nonadvol Value of all Items
        Total Invoice Value = Total Taxable Value + Total SGST Value + Total CGST Value + Total IGST Value + Total Cess Value + Total State Cess Value + Total Non-Advol Cess Value
    The round-off value can be + or -

Validations on e-waybill:

    E-waybill can be generated only if E-way Bill related details are passed.
    E-way Bill is not generated for document types of Debit Note and Credit Note and Services.
    E-way Bill cannot be generated for cancelled IRN.
    E Way Bill can be generated provided at least HSN of one item belongs to goods.
    If only Transporter ¿Id is provided, then only Part-A is generated.
    If mode of transportation in `Road¿, then the Vehicle number and vehicle type should be passed.
    If mode of transportation in Ship. Air, Rail, then the transport document number and date should be passed.
    The Vehicle no. should match with specified format and exist in Vahan database.
    E-Waybill will not be generated if the seller or buyer GSTIN is blocked due to non-filing of Returns.
    PIN-PIN distance is validated.
Comment 11 kk 2020-03-06 16:25:21 IST 
Created attachment 836 [details]
imis einvoice schema v2

imis einvoice schema as per einvoice JSON schema V7
Comment 10 kk 2020-03-06 16:24:03 IST 
Created attachment 835 [details]
einvoice JSON schema V7

New version of einvoice schema released by GST deptt
Comment 9 Mahender 2020-03-06 15:21:22 IST 
SET SERVEROUTPUT ON
set serveroutput on size 1000000;
DECLARE
  P_SI_RECID   		SALE_INVOICE.SI_RECID%TYPE := 20000005797050 ;
  SALE_INVOICE_ROW	VW_GST_SALE_INVOICES%ROWTYPE;
  CUSTOMER_ROW		CUSTOMERS%ROWTYPE;
  CUST_STATE_ROW	STATES%ROWTYPE;
  CO_STATE_ROW		STATES%ROWTYPE;
BEGIN

  APEX_JSON.initialize_clob_output;

  APEX_JSON.open_object; --{ main

  SELECT S.*
  INTO   SALE_INVOICE_ROW
  FROM   VW_GST_SALE_INVOICES S
  WHERE  S.DOCUMENT_RECID = P_SI_RECID;

  SELECT C.*
  INTO   CUSTOMER_ROW
  FROM   CUSTOMERS C
  WHERE  COMPANY		= SALE_INVOICE_ROW.COMPANY_CODE
  AND    CUSTOMER_CODE  = SALE_INVOICE_ROW.PARTY_CODE;

  SELECT S.*
  INTO CUST_STATE_ROW
  FROM STATES S
  WHERE  STATE_CODE		= CUSTOMER_ROW.STATE_CODE
  AND    COUNTRY_CODE	= CUSTOMER_ROW.COUNTRY_CODE;


  SELECT S.*
  INTO CO_STATE_ROW
  FROM STATES S
  WHERE  STATE_CODE		= PKG_CONTROLS.GET_COMPANY_INFO(SALE_INVOICE_ROW.COMPANY_CODE,'STATE_CODE')
  AND    COUNTRY_CODE	= PKG_CONTROLS.GET_COMPANY_INFO(SALE_INVOICE_ROW.COMPANY_CODE,'COUNTRY_CODE');

  APEX_JSON.write('$schema', 'http://json-schema.org/draft-04/schema#');
  APEX_JSON.write('Title', 'GST-India Invoice Document');
  APEX_JSON.write('Description', 'GST Invoice format for IRN Generation in INDIA');
  APEX_JSON.write('TaxSch', 'GST');
  APEX_JSON.write('Version', '1.00');
  APEX_JSON.write('Irn', 'XXX-64-XXX');

  APEX_JSON.open_object('TranDtls'); --{
  APEX_JSON.write('Catg', CASE WHEN SALE_INVOICE_ROW.CUSTOMER_TYPE='E' THEN 'EXP' ELSE 'B2B' END);
  APEX_JSON.write('RegRev', 'RG');
  APEX_JSON.write('Typ', 'CMB');
  APEX_JSON.close_object; -- } TransDtls

  APEX_JSON.open_object('DocDtls'); --{
  APEX_JSON.write('type', 'INV');
  APEX_JSON.write('No',SALE_INVOICE_ROW.DOCUMENT_STRING);
  APEX_JSON.write('Dt', TO_CHAR(SALE_INVOICE_ROW.DOCUMENT_DATE,'YYYY-MM-DD'));
  APEX_JSON.close_object; --} DocDtls

  APEX_JSON.open_object('SellerDtls'); --{
  APEX_JSON.write('Gstin', PKG_CONTROLS.GET_REGN_NUMBER(SALE_INVOICE_ROW.COMPANY_CODE,'GST'));
  APEX_JSON.write('TrdNm', PKG_REPORTS.GET_COMPANY_NAME(SALE_INVOICE_ROW.COMPANY_CODE,','));
  APEX_JSON.write('Loc',   PKG_CONTROLS.GET_COMPANY_INFO(SALE_INVOICE_ROW.COMPANY_CODE,'LOCATION_NAME'));
  APEX_JSON.write('Dst',   PKG_CONTROLS.GET_COMPANY_INFO(SALE_INVOICE_ROW.COMPANY_CODE,'DISTRICT_CODE'));
  APEX_JSON.write('Pin',   to_number(PKG_CONTROLS.GET_COMPANY_INFO(SALE_INVOICE_ROW.COMPANY_CODE,'POSTAL_CODE')));
  APEX_JSON.write('Stcd',  CO_STATE_ROW.STATE_ID);
  APEX_JSON.close_object; --} SellerDtls

  APEX_JSON.open_object('BuyerDtls');--{
  APEX_JSON.write('Gstin', PKG_AR.GET_REGN_NUMBER(SALE_INVOICE_ROW.COMPANY_CODE,SALE_INVOICE_ROW.PARTY_CODE,'GST'));
  APEX_JSON.write('TrdNm', PKG_AR.GET_CUSTOMER_NAME(SALE_INVOICE_ROW.PARTY_CODE));
  APEX_JSON.write('Loc',   PKG_AR.GET_STATION_NAME(SALE_INVOICE_ROW.PARTY_CODE));
  APEX_JSON.write('Pin',   to_number(CUSTOMER_ROW.POSTAL_CODE));
  APEX_JSON.write('Stcd',  CUST_STATE_ROW.STATE_ID);
  APEX_JSON.close_object; --} BuyerDtls

  APEX_JSON.open_object('ItemList'); --{
 	APEX_JSON.open_array('ITEM'); --[
  FOR cur_rec IN (SELECT * FROM VW_GST_INVOICE_ITEMS I WHERE I.PARENT_RECID = P_SI_RECID )
  LOOP
    APEX_JSON.open_object; --{
    APEX_JSON.write('PrdNm', cur_rec.ITEM_CODE||'/'||cur_rec.STOCK_TYPE);
    APEX_JSON.write('PrdDesc', cur_rec.STOCK_NAME);
    APEX_JSON.write('HsnCd', cur_rec.EXCISE_TARIFF);
    APEX_JSON.write('Qty', cur_rec.NO_OF_PACKS);
    APEX_JSON.write('FreeQty', 0);
    APEX_JSON.write('Unit', cur_rec.IUOM_CODE);
    APEX_JSON.write('UnitPrice', cur_rec.NET_PRICE);
    APEX_JSON.write('TotAmt', cur_rec.ITEM_VALUE);
    APEX_JSON.write('Discount', NVL(cur_rec.NDIS_AMOUNT,0)+NVL(cur_rec.TDIS_AMOUNT,0)+NVL(cur_rec.SDIS_AMOUNT,0));
    APEX_JSON.write('OthChrg', 0);
    APEX_JSON.write('AssAmt', cur_rec.COST_AMOUNT);
    APEX_JSON.write('CgstRt', cur_rec.CGST_RATE);
    APEX_JSON.write('SgstRt', cur_rec.SGST_RATE);
    APEX_JSON.write('IgstRt', cur_rec.IGST_RATE);
    APEX_JSON.write('CesRt', cur_rec.CESS_RATE);
    APEX_JSON.write('CesNonAdval', cur_rec.CESS_ON_QTY);
    APEX_JSON.write('StateCes', 0);
    APEX_JSON.write('TotItemVal', cur_rec.ITEM_VALUE);

    APEX_JSON.close_object; --} ITEM
  END LOOP;
  APEX_JSON.close_array; --]
  APEX_JSON.close_object; --} ItemList

  APEX_JSON.open_object('ValDtls'); --{
  APEX_JSON.write('AssVal', 	SALE_INVOICE_ROW.COST_OF_GOODS);
  APEX_JSON.write('CgstVal', 	SALE_INVOICE_ROW.CGST_AMOUNT);
  APEX_JSON.write('SgstVal',   	SALE_INVOICE_ROW.SGST_AMOUNT);
  APEX_JSON.write('IgstVal',   	SALE_INVOICE_ROW.IGST_AMOUNT);
  APEX_JSON.write('CesVal',  	SALE_INVOICE_ROW.CESS_AMOUNT);
  APEX_JSON.write('StCesVal',  	0);
  APEX_JSON.write('CesNonAdVal',SALE_INVOICE_ROW.CESS_ON_QTY);
  APEX_JSON.write('TotInvVal',  SALE_INVOICE_ROW.VALUE_OF_INVOICE);

  APEX_JSON.close_object; --} ValDtls
  INSERT INTO JSON_INVOICE_DOCUMENT (INV_DOC) VALUES(APEX_JSON.get_clob_output);
  DBMS_OUTPUT.put_line(APEX_JSON.get_clob_output);
  APEX_JSON.close_object; --} main

  APEX_JSON.free_output;
END;
Comment 8 Mahender 2020-03-06 12:32:48 IST 
*** Bug 2870 has been marked as a duplicate of this bug. ***
Comment 7 kk 2020-03-06 01:12:38 IST 
- working code for invoice output in json
SET SERVEROUTPUT ON
set serveroutput on size 1000000;
DECLARE
  P_SI_RECID   		SALE_INVOICE.SI_RECID%TYPE := 20000005797050 ;
  SALE_INVOICE_ROW	VW_GST_SALE_INVOICES%ROWTYPE;
  CUSTOMER_ROW		CUSTOMERS%ROWTYPE;
BEGIN

  APEX_JSON.initialize_clob_output;

  APEX_JSON.open_object; --{ main

  SELECT S.*
  INTO   SALE_INVOICE_ROW
  FROM   VW_GST_SALE_INVOICES S
  WHERE  S.DOCUMENT_RECID = P_SI_RECID;

  SELECT C.*
  INTO   CUSTOMER_ROW
  FROM   CUSTOMERS C
  WHERE  COMPANY		= SALE_INVOICE_ROW.COMPANY_CODE
  AND    CUSTOMER_CODE  = SALE_INVOICE_ROW.PARTY_CODE;

  APEX_JSON.write('$schema', 'http://json-schema.org/draft-04/schema#');
  APEX_JSON.write('Title', 'GST-India Invoice Document');
  APEX_JSON.write('Description', 'GST Invoice format for IRN Generation in INDIA');
  APEX_JSON.write('TaxSch', 'GST');
  APEX_JSON.write('Version', '1.00');
  APEX_JSON.write('Irn', 'XXX-64-XXX');

  APEX_JSON.open_object('TranDtls'); --{
  APEX_JSON.write('Catg', CASE WHEN SALE_INVOICE_ROW.CUSTOMER_TYPE='E' THEN 'EXP' ELSE 'B2B' END);
  APEX_JSON.write('RegRev', 'RG');
  APEX_JSON.write('Typ', 'CMB');
  APEX_JSON.close_object; -- } TransDtls

  APEX_JSON.open_object('DocDtls'); --{
  APEX_JSON.write('type', 'INV');
  APEX_JSON.write('No',SALE_INVOICE_ROW.DOCUMENT_STRING);
  APEX_JSON.write('Dt', TO_CHAR(SALE_INVOICE_ROW.DOCUMENT_DATE,'YYYY-MM-DD'));
  APEX_JSON.close_object; --} DocDtls
  
  APEX_JSON.open_object('SellerDtls'); --{
  APEX_JSON.write('Gstin', PKG_CONTROLS.GET_REGN_NUMBER(SALE_INVOICE_ROW.COMPANY_CODE,'GST'));
  APEX_JSON.write('TrdNm', PKG_REPORTS.GET_COMPANY_NAME(SALE_INVOICE_ROW.COMPANY_CODE,','));
  APEX_JSON.write('Loc',   PKG_CONTROLS.GET_COMPANY_INFO(SALE_INVOICE_ROW.COMPANY_CODE,'LOCATION_NAME'));
  APEX_JSON.write('Dst',   PKG_CONTROLS.GET_COMPANY_INFO(SALE_INVOICE_ROW.COMPANY_CODE,'DISTRICT_CODE'));
  APEX_JSON.write('Pin',   to_number(PKG_CONTROLS.GET_COMPANY_INFO(SALE_INVOICE_ROW.COMPANY_CODE,'POSTAL_CODE')));
--  APEX_JSON.write('Stcd',  PKG_CONTROLS.GET_COMPANY_INFO(SALE_INVOICE_ROW.COMPANY_CODE,'STATE_NAME'));
  APEX_JSON.write('Stcd',  07);
  APEX_JSON.close_object; --} SellerDtls
  
  APEX_JSON.open_object('BuyerDtls');--{
  APEX_JSON.write('Gstin', PKG_AR.GET_REGN_NUMBER(SALE_INVOICE_ROW.COMPANY_CODE,SALE_INVOICE_ROW.PARTY_CODE,'GST'));
  APEX_JSON.write('TrdNm', PKG_AR.GET_CUSTOMER_NAME(SALE_INVOICE_ROW.PARTY_CODE));
  APEX_JSON.write('Loc',   PKG_AR.GET_STATION_NAME(SALE_INVOICE_ROW.PARTY_CODE));
  APEX_JSON.write('Pin',   to_number(CUSTOMER_ROW.POSTAL_CODE));
--  APEX_JSON.write('Stcd',  PKG_AR.GET_STATE_NAME(SALE_INVOICE_ROW.PARTY_CODE));
  APEX_JSON.write('Stcd',  09);
  APEX_JSON.close_object; --} BuyerDtls
  
  APEX_JSON.open_object('ItemList'); --{
 	APEX_JSON.open_array('ITEM'); --[
  FOR cur_rec IN (SELECT * FROM VW_GST_INVOICE_ITEMS I WHERE I.PARENT_RECID = P_SI_RECID )
  LOOP
    APEX_JSON.open_object; --{
    APEX_JSON.write('PrdNm', cur_rec.ITEM_CODE||'/'||cur_rec.STOCK_TYPE);
    APEX_JSON.write('PrdDesc', cur_rec.STOCK_NAME);
    APEX_JSON.write('HsnCd', cur_rec.EXCISE_TARIFF);
    APEX_JSON.write('Qty', cur_rec.NO_OF_PACKS);
    APEX_JSON.write('FreeQty', 0);
    APEX_JSON.write('Unit', cur_rec.IUOM_CODE);
    APEX_JSON.write('UnitPrice', cur_rec.NET_PRICE);
    APEX_JSON.write('TotAmt', cur_rec.ITEM_VALUE);
    APEX_JSON.write('Discount', NVL(cur_rec.NDIS_AMOUNT,0)+NVL(cur_rec.TDIS_AMOUNT,0)+NVL(cur_rec.SDIS_AMOUNT,0));
    APEX_JSON.write('OthChrg', 0);
    APEX_JSON.write('AssAmt', cur_rec.COST_AMOUNT);
    APEX_JSON.write('CgstRt', cur_rec.CGST_RATE);
    APEX_JSON.write('SgstRt', cur_rec.SGST_RATE);
    APEX_JSON.write('IgstRt', cur_rec.IGST_RATE);
    APEX_JSON.write('CesRt', cur_rec.CESS_RATE);
    APEX_JSON.write('CesNonAdval', cur_rec.CESS_ON_QTY);
    APEX_JSON.write('StateCes', 0);
    APEX_JSON.write('TotItemVal', cur_rec.ITEM_VALUE);

    APEX_JSON.close_object; --} ITEM
  END LOOP;
  APEX_JSON.close_array; --]
  APEX_JSON.close_object; --} ItemList 
  
  APEX_JSON.open_object('ValDtls'); --{
  APEX_JSON.write('AssVal', 	SALE_INVOICE_ROW.COST_OF_GOODS);
  APEX_JSON.write('CgstVal', 	SALE_INVOICE_ROW.CGST_AMOUNT);
  APEX_JSON.write('SgstVal',   	SALE_INVOICE_ROW.SGST_AMOUNT);
  APEX_JSON.write('IgstVal',   	SALE_INVOICE_ROW.IGST_AMOUNT);
  APEX_JSON.write('CesVal',  	SALE_INVOICE_ROW.CESS_AMOUNT);
  APEX_JSON.write('StCesVal',  	0);
  APEX_JSON.write('CesNonAdVal',SALE_INVOICE_ROW.CESS_ON_QTY);
  APEX_JSON.write('TotInvVal',  SALE_INVOICE_ROW.VALUE_OF_INVOICE);

  APEX_JSON.close_object; --} ValDtls
 /* INSERT INTO JSON_INVOICE_DOCUMENT (INV_DOC) VALUES(APEX_JSON.get_clob_output); */
  DBMS_OUTPUT.put_line(APEX_JSON.get_clob_output);
  APEX_JSON.close_object; --} main

  APEX_JSON.free_output;
END;
Comment 6 kk 2020-03-05 23:16:14 IST 
the output is still not correct. open_object should be used only at the beginning. I think you should use open_array for itemlist instead of open_object .

see this example which is very similar to our requirement:
https://oracle-base.com/articles/misc/apex_json-package-generate-and-parse-json-documents-in-oracle
Comment 5 Mahender 2020-03-04 17:32:55 IST 
SET SERVEROUTPUT ON
set serveroutput on size 10000000;
DECLARE
  P_SI_RECID   		SALE_INVOICE.SI_RECID%TYPE := 20000005797050 ;
  SALE_INVOICE_ROW	VW_GST_SALE_INVOICES%ROWTYPE;
  CUSTOMER_ROW		CUSTOMERS%ROWTYPE;
BEGIN

  APEX_JSON.initialize_clob_output;

  APEX_JSON.open_object;

  SELECT S.*
  INTO   SALE_INVOICE_ROW
  FROM   VW_GST_SALE_INVOICES S
  WHERE  S.DOCUMENT_RECID = P_SI_RECID;

  SELECT C.*
  INTO   CUSTOMER_ROW
  FROM   CUSTOMERS C
  WHERE  COMPANY		= SALE_INVOICE_ROW.COMPANY_CODE
  AND    CUSTOMER_CODE  = SALE_INVOICE_ROW.PARTY_CODE;

  APEX_JSON.write('$schema', 'http://json-schema.org/draft-04/schema#');
  APEX_JSON.write('Title', 'GST-India Invoice Document');
  APEX_JSON.write('Description', 'GST Invoice format for IRN Generation in INDIA');
  APEX_JSON.write('TaxSch', 'GST');
  APEX_JSON.write('Version', '1.00');
  APEX_JSON.write('Irn', 'XXX-64-XXX');

  APEX_JSON.open_object('TranDtls');
  APEX_JSON.write('Catg', CASE WHEN SALE_INVOICE_ROW.CUSTOMER_TYPE='E' THEN 'EXP' ELSE 'B2B' END);
  APEX_JSON.write('RegRev', 'RG');
  APEX_JSON.write('Typ', 'CMB');

  APEX_JSON.open_object('DocDtls');
  APEX_JSON.write('type', 'INV');
  APEX_JSON.write('No',SALE_INVOICE_ROW.DOCUMENT_STRING);
  APEX_JSON.write('Dt', TO_CHAR(SALE_INVOICE_ROW.DOCUMENT_DATE,'YYYY-MM-DD'));

  APEX_JSON.open_object('SellerDtls');
  APEX_JSON.write('Gstin', PKG_CONTROLS.GET_REGN_NUMBER(SALE_INVOICE_ROW.COMPANY_CODE,'GST'));
  APEX_JSON.write('TrdNm', PKG_REPORTS.GET_COMPANY_NAME(SALE_INVOICE_ROW.COMPANY_CODE,','));
  APEX_JSON.write('Loc',   PKG_CONTROLS.GET_COMPANY_INFO(SALE_INVOICE_ROW.COMPANY_CODE,'LOCATION_NAME'));
  APEX_JSON.write('Dst',   PKG_CONTROLS.GET_COMPANY_INFO(SALE_INVOICE_ROW.COMPANY_CODE,'DISTRICT_CODE'));
  APEX_JSON.write('Pin',   PKG_CONTROLS.GET_COMPANY_INFO(SALE_INVOICE_ROW.COMPANY_CODE,'POSTAL_CODE'));
  APEX_JSON.write('Stcd',  PKG_CONTROLS.GET_COMPANY_INFO(SALE_INVOICE_ROW.COMPANY_CODE,'STATE_NAME'));

  APEX_JSON.open_object('BuyerDtls');
  APEX_JSON.write('Gstin', PKG_AR.GET_REGN_NUMBER(SALE_INVOICE_ROW.COMPANY_CODE,SALE_INVOICE_ROW.PARTY_CODE,'GST'));
  APEX_JSON.write('TrdNm', PKG_AR.GET_CUSTOMER_NAME(SALE_INVOICE_ROW.PARTY_CODE));
  APEX_JSON.write('Loc',   PKG_AR.GET_STATION_NAME(SALE_INVOICE_ROW.PARTY_CODE));
  APEX_JSON.write('Pin',   CUSTOMER_ROW.POSTAL_CODE);
  APEX_JSON.write('Stcd',  PKG_AR.GET_STATE_NAME(SALE_INVOICE_ROW.PARTY_CODE));

  APEX_JSON.open_object('ItemList');
  FOR cur_rec IN (SELECT * FROM VW_GST_INVOICE_ITEMS I WHERE I.PARENT_RECID = P_SI_RECID /*AND ROWNUM<5*/)
  LOOP
  	APEX_JSON.open_array('Item');
    APEX_JSON.write('PrdNm', cur_rec.ITEM_CODE||'/'||cur_rec.STOCK_TYPE);
    APEX_JSON.write('PrdDesc', cur_rec.STOCK_NAME);
    APEX_JSON.write('HsnCd', cur_rec.EXCISE_TARIFF);
    APEX_JSON.write('Qty', cur_rec.NO_OF_PACKS);
    APEX_JSON.write('FreeQty', '');
    APEX_JSON.write('Unit', cur_rec.IUOM_CODE);
    APEX_JSON.write('UnitPrice', cur_rec.NET_PRICE);
    APEX_JSON.write('TotAmt', cur_rec.ITEM_VALUE);
    APEX_JSON.write('Discount', NVL(cur_rec.NDIS_AMOUNT,0)+NVL(cur_rec.TDIS_AMOUNT,0)+NVL(cur_rec.SDIS_AMOUNT,0));
    APEX_JSON.write('OthChrg', '');
    APEX_JSON.write('AssAmt', cur_rec.COST_AMOUNT);
    APEX_JSON.write('CgstRt', cur_rec.CGST_RATE);
    APEX_JSON.write('SgstRt', cur_rec.SGST_RATE);
    APEX_JSON.write('IgstRt', cur_rec.IGST_RATE);
    APEX_JSON.write('CesRt', cur_rec.CESS_RATE);
    APEX_JSON.write('CesNonAdval', cur_rec.CESS_ON_QTY);
    APEX_JSON.write('StateCes', '');
    APEX_JSON.write('TotItemVal', cur_rec.ITEM_VALUE);
  APEX_JSON.close_array;

  END LOOP;

  APEX_JSON.open_object('ValDtls');
  APEX_JSON.write('AssVal', 	SALE_INVOICE_ROW.COST_OF_GOODS);
  APEX_JSON.write('CgstVal', 	SALE_INVOICE_ROW.CGST_AMOUNT);
  APEX_JSON.write('SgstVal',   	SALE_INVOICE_ROW.SGST_AMOUNT);
  APEX_JSON.write('IgstVal',   	SALE_INVOICE_ROW.IGST_AMOUNT);
  APEX_JSON.write('CesVal',  	SALE_INVOICE_ROW.CESS_AMOUNT);
  APEX_JSON.write('CesNonAdVal',SALE_INVOICE_ROW.CESS_ON_QTY);
  APEX_JSON.write('TotInvVal',  SALE_INVOICE_ROW.VALUE_OF_INVOICE);

  APEX_JSON.close_object;
  INSERT INTO JSON_INVOICE_DOCUMENT (INV_DOC) VALUES(APEX_JSON.get_clob_output);
  DBMS_OUTPUT.put_line(APEX_JSON.get_clob_output);
  APEX_JSON.free_output;
END;
Comment 4 kk 2020-03-04 14:09:17 IST 
Created attachment 825 [details]
json schema for einvoice

Many of the mandatory field values are missing. e.g. in SellerDtls TrdNm, Loc, Dst, etc. Please see the notes at the end of the schema file. Also see the full schema detail in the attached file json-schema.json.
Comment 1 kk 2020-03-03 22:59:35 IST 
Created attachment 823 [details]
imis json schema for e-invoice V2

correction of some errors in the schema
Description kk 2020-03-03 19:56:52 IST 
Created attachment 822 [details]
imis einv json schema

Dear MS,

pls make a procedure to generate a json file as per the attached details.
the procedure should accept a RECID and create a json file for the corresponding invoice.