Prepared Data Query for JSON

 

SELECT  'SALE INVOICE' AS invoiceType,'þ' as inv_sts,

INV_DATE AS invoiceDate,

DECODE(INV_DATE,NULL,'p','þ')DATE_STS,

'3970637' AS sellerNTNCNIC,'þ' AS  SLR_NTN_STS,

'S.M FOOD MAKERS' AS sellerBusinessName ,'þ' AS SLR_BUSINESS_STS,

'PUMJAB' AS sellerProvince,'þ' AS SLR_PROVIENCE,

'ADDRESS' AS sellerAddress,'þ' AS SLR_ADDRESS,

(SELECT CASE WHEN REG_TYPE ='U' then 'Unregistered' else 'Registered' end  from account where code = cus_id)buyerRegistrationType ,

DECODE((SELECT CASE WHEN REG_TYPE ='U' then 'Unregistered' else 'Registered' end  from account where code = cus_id),NULL,'p','þ')BUYER_TYPE_STS,

(SELECT CNIC_NTN_FBR FROM ACCOUNT WHERE CODE = CUS_ID)buyerNTNCNIC,

CASE 

    WHEN LENGTH((SELECT CNIC_NTN_FBR FROM ACCOUNT WHERE CODE = CUS_ID)) < 13 OR INSTR(((SELECT CNIC_NTN_FBR FROM ACCOUNT WHERE CODE = CUS_ID)), '-') > 7  THEN 'p'

WHEN ((SELECT CNIC_NTN_FBR FROM ACCOUNT WHERE CODE = CUS_ID)) IS NULL THEN 'p'

ELSE

'þ'

  END AS result_NTN,

(SELECT NAME FROM ACCOUNT WHERE CODE = CUS_ID)buyerBusinessName ,

DECODE((SELECT NAME FROM ACCOUNT WHERE CODE = CUS_ID),NULL,'p','þ')BRB_STS,

'PUNJAB' as buyerProvince ,

(SELECT ADDR1 FROM ACCOUNT WHERE CODE = CUS_ID) as buyerAddress,

DECODE((SELECT ADDR1 FROM ACCOUNT WHERE CODE = CUS_ID),NULL,'p','þ') AS BRB_ADDRESS_STS,

null as invoiceRefNo,

SCENARIO_CODE  as scenarioId  ,

DECODE(SCENARIO_CODE,NULL,'p','þ')SCNARIO_STS,

(SELECT HS_CODE FROM ITEM WHERE CODE = ITEM_CODE)hsCode,

CASE 

    WHEN INSTR((SELECT HS_CODE FROM ITEM WHERE CODE = ITEM_CODE), '.') > 0 THEN '1'

WHEN (SELECT HS_CODE FROM ITEM WHERE CODE = ITEM_CODE) IS NULL THEN NULL

    ELSE '0'

  END AS result,

(SELECT NAME FROM ITEM WHERE CODE = ITEM_CODE)AS productDescription ,                 

B.ST_RATE||'%' as rate ,                               

(SELECT TYPE FROM ITEM WHERE CODE = B.ITEM_CODE)AS uoM,                                

B.qty as quantity ,                                  

round(B.G_VALUE+((B.g_value*B.st_rate)/100),2) as totals,                               

round(B.G_VALUE,2)SalesExcludingST,                     

0 as fixedNotifiedOrRetailPrice ,           

round((B.g_value*B.st_rate)/100,2) as salesTaxApplicable,                        

0 as salesTaxWithheldAtSource ,            

0 as extraTax,                            

0 as furtherTax,                          

null as sroScheduleNo,                      

0 as fedPayable,                          

0 as discount,                                    

(SELECT SCENARIO_NAME FROM RIFFAT.FBR_SCENARIO  WHERE SCENARIO_CODE = (SELECT SCENARIO_CODE FROM SALM WHERE INV_NO = :P_INV) ) AS saleType,

null as sroItemSerialNo

from salm A, SALD B 

WHERE A.GRN_NO = B.GRN_NO

AND B.GRN_NO = (SELECT GRN_NO FROM SALM WHERE INV_NO = :P_INV)

AND A.INV_NO = :P_INV