Hi ,
firstly glad to be here in this Oracle forum , amazed by the knowledge shared and helping from fellow community members.
I need help on building SQL logic from below
Data to be present in the table :
Insert into TEMP (DOC_ID,TRAN_CODE,AMOUNT,VAT_RATE,LINE_NUMBER_TYPE) values (894769179,'TAX',21932.9712,16,'2');
Insert into TEMP (DOC_ID,TRAN_CODE,AMOUNT,VAT_RATE,LINE_NUMBER_TYPE) values (894769179,'TAX',0,0,'1');
Insert into TEMP (DOC_ID,TRAN_CODE,AMOUNT,VAT_RATE,LINE_NUMBER_TYPE) values (894769179,'VCT',0,0,null);
Insert into TEMP (DOC_ID,TRAN_CODE,AMOUNT,VAT_RATE,LINE_NUMBER_TYPE) values (894769179,'REASON',0,0,null);
Insert into TEMP (DOC_ID,TRAN_CODE,AMOUNT,VAT_RATE,LINE_NUMBER_TYPE) values (894769179,'UNR',37317.36,0,'2');
Insert into TEMP (DOC_ID,TRAN_CODE,AMOUNT,VAT_RATE,LINE_NUMBER_TYPE) values (894769179,'REASON',-105.1152,16,null);
Insert into TEMP (DOC_ID,TRAN_CODE,AMOUNT,VAT_RATE,LINE_NUMBER_TYPE) values (894769179,'UNR',12297.6,0,'1');
Table looks like below
SQL/PLSQL logic to build from below logic
a) Row number (1,5) and row number (2,7) are related as they have same LINE_NUMBER_TYPE values
b) Row number 1,6 are related with VAT_RATE value , but here 1, 5 already related with LINE_NUMBER_TYPE value ,so here 6,5 rows are indirecly related
c) I want to calculate (row_no_5.amount-row_no_6.amount) * row_no_6.vat_rate , after the calculation I need to update result back to row_no_1.amount value
Appreciate your help on this
Thank you