Bug 3218

Summary:	json file for einvoicing
Product:	[01.IMIS] IMIS SALES	Reporter:	kk <kapurk>
Component:	IMIS SALES FORMS AND REPORTS	Assignee:	Mahender <mahender.singh>
Status:	RESOLVED USER TO VERIFY
Severity:	major	CC:	pn.srivastava, sharad.sharma
Priority:	Normal
Version:	unspecified
Hardware:	All
OS:	All
Database:	ALL	Module ID:
Deadline:	2020-03-05
Attachments:	imis einv json schema imis json schema for e-invoice V2 json schema for einvoice einvoice JSON schema V7 imis einvoice schema v2

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.