Bugzilla – Attachment 2986 Details for
Bug 9260
New Report
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
Against_call_id_9260_NOIDA.csv
Against_call_id_9260.GWF (text/plain), 75.91 KB, created by
Sharad
on 2022-08-24 17:29:05 IST
(
hide
)
Description:
Against_call_id_9260_NOIDA.csv
Filename:
MIME Type:
Creator:
Sharad
Created:
2022-08-24 17:29:05 IST
Size:
75.91 KB
patch
obsolete
>"REM WORKSPACETAB0",Query1,,171 >SELECT * FROM ( >SELECT A.DATA_SOURCE > ,A.COMP_CODE > ,A.BILL_NO > ,A.INVOICE_DATE > ,A.SUPP_CODE > ,A.SUPP_BILL > ,A.SUPP_DATE > ,A.DOCUMENT_TYPE > ,NVL(CASE WHEN DATA_SOURCE IN ('SLR','CEV') THEN PKG_AR.GET_CUSTOMER_NAME(SUPP_CODE) ELSE PKG_AP.GET_SUPPLIER_NAME(SUPP_CODE) END,PKG_REPORTS.GET_COMPANY_NAME(SUPP_CODE)) SUPP_NAME > ,A.DATA_TYPE > ,A.DATA_DESC > ,A.COMMODITY_TYPE > --,A.COMMODITY_CODE > ,A.TAXABLE_AMT > ,A.IGST_RATE > ,A.CGST_RATE > ,A.SGST_RATE > ,A.CESS_RATE > ,A.IGST_AMOUNT > ,A.CGST_AMOUNT > ,A.SGST_AMOUNT > ,A.CESS_AMOUNT > ,A.CESS_ON_QTY > ,A.TOTAL_GST > ,A.BILL_AMNT > ,A.GST_R_FLAG > ,A.GST_CREDIT_FLAG > ,A.STATUS > ,NVL(CASE WHEN A.DOCUMENT_TYPE IN ('W7', 'DC') AND A.STATUS != 'Z' THEN PKG_CONTROLS.GET_CONTROL_ACCOUNT ( REF_COMPANY, 'FG_TRANSFER_DR' ) > WHEN A.DOCUMENT_TYPE IN ('W5', '49') AND A.STATUS != 'Z' THEN PKG_CONTROLS.GET_CONTROL_ACCOUNT ( REF_COMPANY, 'FG_RECEIVED_DR' ) > WHEN A.DOCUMENT_TYPE IN ('AP' ) AND A.STATUS != 'Z' THEN AP_DR_ACCOUNT_CODE > ELSE NULL > END,GRN_DR_ACCOUNT_CODE) DR_ACCOUNT_CODE > , ACNT_MODEL > , STORE_TYPE >FROM ( > SELECT > A.DATA_SOURCE DATA_SOURCE > ,A.DOCUMENT_DATE INVOICE_DATE > ,A.DOCUMENT_STRING BILL_NO > ,A.DOCUMENT_TYPE DOCUMENT_TYPE > ,A.AREF_NUMBER SUPP_BILL > ,A.AREF_DATE SUPP_DATE > ,A.COMPANY_CODE COMP_CODE > ,A.SUPPLIER_CODE SUPP_CODE > ,0 DATA_TYPE > ,'PB' DATA_DESC > ,CASE WHEN ALLOW_GST_CREDIT = 'Y' THEN > CASE WHEN GST_REVERSE_FLAG = 'N' THEN NVL(B.COMMODITY_TYPE, '$') ELSE DECODE (NVL(B.COMMODITY_TYPE,'#'), '001', '#1', '002', '#3') END > ELSE > DECODE (NVL(B.COMMODITY_TYPE,'#'), '001', '#2', '002', '#4') END > COMMODITY_TYPE > --,A.COMMODITY_CODE COMMODITY_CODE > ,SUM(A.TAXABLE_AMT) TAXABLE_AMT > ,A.IGST_RATE > ,A.CGST_RATE > ,A.SGST_RATE > ,A.CESS_RATE > ,SUM(A.IGST_AMOUNT) IGST_AMOUNT > ,SUM(A.CGST_AMOUNT) CGST_AMOUNT > ,SUM(A.SGST_AMOUNT) SGST_AMOUNT > ,SUM(A.CESS_AMOUNT) CESS_AMOUNT > ,SUM(A.CESS_ON_QTY) CESS_ON_QTY > ,SUM(NVL(IGST_AMOUNT, 0) + NVL(CGST_AMOUNT, 0) + NVL(SGST_AMOUNT, 0) + NVL(CESS_AMOUNT, 0) + NVL(CESS_ON_QTY, 0)) TOTAL_GST > -- Comment by ss on dt. 24/03/2018 > --,ROUND(SUM(A.BILL_AMOUNT)) BILL_AMNT > ,ROUND(SUM(A.BILL_AMOUNT)- SUM(NVL(OTHR_AMOUNT,0))- SUM(NVL(IGST_OTHERS,0))- SUM(NVL(CGST_OTHERS,0))- SUM(NVL(SGST_OTHERS,0))) BILL_AMNT > ,A.GST_REVERSE_FLAG GST_R_FLAG > ,A.ALLOW_GST_CREDIT GST_CREDIT_FLAG > ,A.STATUS > ,(SELECT COMPANY_CODE FROM VW_COMPANY_DETAIL WHERE SUPPLIER_CODE = A.SUPPLIER_CODE) REF_COMPANY > ,(SELECT ACNT_MODEL FROM ITEM_CLASSES WHERE ITEM_CLASS = A.ITEM_CLASS AND COMPANY_CODE = A.COMPANY_CODE) ACNT_MODEL > ,(SELECT STORE_TYPE FROM VW_STORES WHERE COMPANY_CODE = A.COMPANY_CODE AND STORE_CODE = A.STORE_CODE ) STORE_TYPE > ,CASE WHEN A.DOCUMENT_TYPE IN ('44' ) AND A.STATUS != 'Z' AND NVL((SELECT STORE_TYPE FROM VW_STORES WHERE COMPANY_CODE = A.COMPANY_CODE AND STORE_CODE = A.STORE_CODE ), 'I') IN ('I', 'E') AND (SELECT ACNT_MODEL FROM ITEM_CLASSES WHERE ITEM_CLASS = A.ITEM_CLASS AND COMPANY_CODE = A.COMPANY_CODE) = 'STKS' THEN PKG_IC.GET_POSTING_ACCOUNT(A.COMPANY_CODE, 'STKS', A.STORE_CODE, A.ITEM_CODE) > ELSE > PKG_IC.GET_POSTING_ACCOUNT (A.COMPANY_CODE, 'MEMO', A.STORE_CODE, A.ITEM_CODE) > END GRN_DR_ACCOUNT_CODE > ,CASE WHEN A.DOCUMENT_TYPE IN ('AP' ) AND A.STATUS != 'Z' THEN ( SELECT GL_CODE||GL_SUB_CODE FROM EXPENSE_CODES WHERE COMPANY = A.COMPANY_CODE AND EXPENSE_CODE = A.ITEM_CODE ) > ELSE > NULL > END AP_DR_ACCOUNT_CODE > FROM VW_PURCHASE_LEDGER A, COMMODITIES B > WHERE PKG_CONTROLS.GET_REGN_NUMBER(A.COMPANY_CODE, 'GST') = '07AAACD1952B1Z8' > /* > -- Parameter add supplier gst number > -- By ss on dt. 10/08/2018 > */ > --AND NVL(GST_NUMBER, '$' ) LIKE NVL(:P_SUPP_GST_NUMBER,'%') > -- Add new parameter by ss on dt. 24/03/2018 > --AND ALLOW_GST_CREDIT LIKE NVL(:P_ALLOW_GST_CREDIT,'%') > AND (( 'E' ='E' AND A.DOCUMENT_DATE BETWEEN '01/04/2022' AND '30/04/2022' ) OR ( 'E' ='B' AND A.AREF_DATE BETWEEN '01/04/2022' AND '30/04/2022' )) > AND A.COMMODITY_CODE = B.COMMODITY_CODE (+) > GROUP BY A.DATA_SOURCE,A.DOCUMENT_DATE,A.DOCUMENT_STRING,A.DOCUMENT_TYPE,A.AREF_NUMBER,A.AREF_DATE, A.COMPANY_CODE,A.SUPPLIER_CODE,A. IGST_RATE, A.CGST_RATE, A.SGST_RATE, A.CESS_RATE,B.COMMODITY_TYPE, A.COMMODITY_CODE ,A.GST_REVERSE_FLAG ,A.ALLOW_GST_CREDIT ,A.STATUS, A.ITEM_CLASS, A.STORE_CODE,A.ITEM_CODE > > UNION ALL > SELECT > A.DATA_SOURCE DATA_SOURCE > ,A.LINK_REF_DATE INVOICE_DATE > ,A.LINK_REF_STRING BILL_NO > ,A.LINK_REF_TYPE DOCUMENT_TYPE > ,A.AREF_NUMBER SUPP_BILL > ,A.AREF_DATE SUPP_DATE > ,A.COMPANY_CODE COMP_CODE > ,A.SUPPLIER_CODE SUPP_CODE > ,1 DATA_TYPE > ,'OH' DATA_DESC > ,CASE WHEN ALLOW_GST_CREDIT = 'Y' THEN > CASE WHEN GST_REVERSE_FLAG = 'N' THEN NVL(B.COMMODITY_TYPE, '$') ELSE DECODE (NVL(B.COMMODITY_TYPE,'#'), '001', '#1', '002', '#3') END > ELSE > DECODE (NVL(B.COMMODITY_TYPE,'#'), '001', '#2', '002', '#4') END > COMMODITY_TYPE > --,A.COMMODITY_CODE COMMODITY_CODE > ,SUM(A.TAXABLE_AMT) TAXABLE_AMT > ,A.IGST_RATE > ,A.CGST_RATE > ,A.SGST_RATE > ,NULL CESS_RATE > ,SUM(A.IGST_OTHERS) IGST_AMOUNT > ,SUM(A.CGST_OTHERS) CGST_AMOUNT > ,SUM(A.SGST_OTHERS) SGST_AMOUNT > ,NULL CESS_AMOUNT > ,NULL CESS_ON_QTY > ,SUM(NVL(IGST_OTHERS, 0) + NVL(CGST_OTHERS, 0) + NVL(SGST_OTHERS, 0)) TOTAL_GST > ,ROUND(SUM(NVL(IGST_OTHERS, 0) + NVL(CGST_OTHERS, 0) + NVL(SGST_OTHERS, 0) + NVL(TAXABLE_AMT,0))) BILL_AMNT > ,A.GST_REVERSE_FLAG GST_R_FLAG > ,A.ALLOW_GST_CREDIT GST_CREDIT_FLAG > ,A.STATUS STATUS > ,(SELECT COMPANY_CODE FROM VW_COMPANY_DETAIL WHERE SUPPLIER_CODE = A.SUPPLIER_CODE) REF_COMPANY > ,(SELECT ACNT_MODEL FROM ITEM_CLASSES WHERE ITEM_CLASS = PKG_IC.GET_ITEM_CLASS(A.ITEM_CODE) AND COMPANY_CODE = A.COMPANY_CODE) ACNT_MODEL > ,(SELECT STORE_TYPE FROM VW_STORES WHERE COMPANY_CODE = A.COMPANY_CODE AND STORE_CODE = A.STORE_CODE ) STORE_TYPE > ,CASE WHEN A.VOUCHER_TYPE IN ('44' ) AND A.STATUS != 'Z' AND NVL((SELECT STORE_TYPE FROM VW_STORES WHERE COMPANY_CODE = A.COMPANY_CODE AND STORE_CODE = A.STORE_CODE ), 'I') IN ('I', 'E') AND (SELECT ACNT_MODEL FROM ITEM_CLASSES WHERE ITEM_CLASS = PKG_IC.GET_ITEM_CLASS(A.ITEM_CODE) AND COMPANY_CODE = A.COMPANY_CODE) = 'STKS' THEN PKG_IC.GET_POSTING_ACCOUNT(A.COMPANY_CODE, 'STKS', A.STORE_CODE, A.ITEM_CODE) > ELSE > PKG_IC.GET_POSTING_ACCOUNT (A.COMPANY_CODE, 'MEMO', A.STORE_CODE, A.ITEM_CODE) > END GRN_DR_ACCOUNT_CODE > ,CASE WHEN A.VOUCHER_TYPE IN ('AP' ) AND A.STATUS != 'Z' THEN ( SELECT GL_CODE||GL_SUB_CODE FROM EXPENSE_CODES WHERE COMPANY = A.COMPANY_CODE AND EXPENSE_CODE = A.ITEM_CODE ) > ELSE > NULL > END AP_DR_ACCOUNT_CODE > FROM VW_OVERHEAD_LEDGER A ,COMMODITIES B > WHERE PKG_CONTROLS.GET_REGN_NUMBER(A.COMPANY_CODE, 'GST') = '07AAACD1952B1Z8' > /* > -- Parameter add supplier gst number > -- By ss on dt. 10/08/2018 > */ > --AND NVL(GST_NUMBER, '$') LIKE NVL(:P_SUPP_GST_NUMBER,'%') > -- Add new parameter by ss on dt. 24/03/2018 > --AND ALLOW_GST_CREDIT LIKE NVL(:P_ALLOW_GST_CREDIT,'%') > /* > -- Bgn -- > -- Against call id - 8839 > --Communication gap by both user. So revert back all change. > -- Bgn -- > -- Against call id - 8839 > AND A.LINK_SPV_TYPE IS NOT NULL > -- End -- > -- End -- > */ > AND (( 'E' ='E' AND A.LINK_REF_DATE BETWEEN '01/04/2022' AND '30/04/2022' ) OR ( 'E' ='B' AND A.AREF_DATE BETWEEN '01/04/2022' AND '30/04/2022' )) > AND A.COMMODITY_CODE = B.COMMODITY_CODE (+) > GROUP BY A.DATA_SOURCE,A.LINK_REF_DATE,A.AREF_DATE, A.LINK_REF_STRING,A.LINK_REF_TYPE,A.AREF_NUMBER,A.AREF_DATE, A.COMPANY_CODE,A.SUPPLIER_CODE,A. IGST_RATE, A.CGST_RATE, A.SGST_RATE,B.COMMODITY_TYPE/*,A.COMMODITY_CODE*/,A.GST_REVERSE_FLAG,A.ALLOW_GST_CREDIT,A.STATUS,PKG_IC.GET_ITEM_CLASS(A.ITEM_CODE), A.STORE_CODE,A.VOUCHER_TYPE,A.ITEM_CODE >) A--, GL_POSTING_DETAILS B >--WHERE A.COMP_CODE = B.COMPANY >--AND A.BILL_NO = B.VOUCHER_TYPE||'/'||B.VOUCHER_SERIES||'/'||RTRIM(LTRIM(TO_CHAR(B.VOUCHER_NUMBER,'000000'))) >--AND B.AMOUNT < 0 >--WHERE DATA_SOURCE IN ('FGP')--NOT IN ('FGR') >ORDER BY COMMODITY_TYPE, INVOICE_DATE, BILL_NO, DATA_TYPE >) >WHERE DR_ACCOUNT_CODE IS NULL >--WHERE DATA_SOURCE = 'APV' >ORDER BY 1, 3 >"REM WORKSPACETAB1","MAKE QUERY",,259 >SELECT > A.DATA_SOURCE > , A.COMP_CODE > , A.BILL_NO > , A.INVOICE_DATE > , A.SUPP_CODE > , A.SUPP_BILL > , A.SUPP_DATE > , A.DOCUMENT_TYPE > , NVL(CASE WHEN DATA_SOURCE IN ('SLR','CEV') THEN PKG_AR.GET_CUSTOMER_NAME(SUPP_CODE) ELSE PKG_AP.GET_SUPPLIER_NAME(SUPP_CODE) END,PKG_REPORTS.GET_COMPANY_NAME(SUPP_CODE)) SUPP_NAME > , A.DATA_TYPE > , A.DATA_DESC > , A.COMMODITY_TYPE > , SUM(A.TAXABLE_AMT) TAXABLE_AMT > , A.IGST_RATE > , A.CGST_RATE > , A.SGST_RATE > , A.CESS_RATE > , SUM(A.IGST_AMOUNT) IGST_AMOUNT > , SUM(A.CGST_AMOUNT) CGST_AMOUNT > , SUM(A.SGST_AMOUNT) SGST_AMOUNT > , SUM(A.CESS_AMOUNT) CESS_AMOUNT > , SUM(A.CESS_ON_QTY) CESS_ON_QTY > , SUM(A.TOTAL_GST ) TOTAL_GST > , ROUND(SUM(A.BILL_AMNT)) BILL_AMNT > , A.GST_R_FLAG > , A.GST_CREDIT_FLAG > , A.STATUS > , NVL( > CASE > WHEN A.DOCUMENT_TYPE IN ('W7', 'DC', 'W5', '49') THEN DR_ACCOUNT_CODE > WHEN A.DOCUMENT_TYPE IN ('AP' ) THEN AP_DR_ACCOUNT_CODE > ELSE > NULL > END > , GRN_DR_ACCOUNT_CODE > ) DR_ACCOUNT_CODE >FROM ( > SELECT > A.DATA_SOURCE DATA_SOURCE > , A.DOCUMENT_DATE INVOICE_DATE > , A.DOCUMENT_STRING BILL_NO > , A.DOCUMENT_TYPE DOCUMENT_TYPE > , A.AREF_NUMBER SUPP_BILL > , A.AREF_DATE SUPP_DATE > , A.COMPANY_CODE COMP_CODE > , A.SUPPLIER_CODE SUPP_CODE > , 0 DATA_TYPE > , 'PB' DATA_DESC > , CASE > WHEN ALLOW_GST_CREDIT = 'Y' THEN > CASE > WHEN GST_REVERSE_FLAG = 'N' THEN NVL(B.COMMODITY_TYPE, '$') > ELSE > DECODE (NVL(B.COMMODITY_TYPE,'#'), '001', '#1', '002', '#3') > END > ELSE > DECODE (NVL(B.COMMODITY_TYPE,'#'), '001', '#2', '002', '#4') > END COMMODITY_TYPE > , A.TAXABLE_AMT TAXABLE_AMT > , A.IGST_RATE > , A.CGST_RATE > , A.SGST_RATE > , A.CESS_RATE > , A.IGST_AMOUNT IGST_AMOUNT > , A.CGST_AMOUNT CGST_AMOUNT > , A.SGST_AMOUNT SGST_AMOUNT > , A.CESS_AMOUNT CESS_AMOUNT > , A.CESS_ON_QTY CESS_ON_QTY > , NVL(IGST_AMOUNT, 0) > + NVL(CGST_AMOUNT, 0) > + NVL(SGST_AMOUNT, 0) > + NVL(CESS_AMOUNT, 0) > + NVL(CESS_ON_QTY, 0) TOTAL_GST > ,( > A.BILL_AMOUNT > - NVL(OTHR_AMOUNT,0) > - NVL(IGST_OTHERS,0) > - NVL(CGST_OTHERS,0) > - NVL(SGST_OTHERS,0) > ) BILL_AMNT > , A.GST_REVERSE_FLAG GST_R_FLAG > , A.ALLOW_GST_CREDIT GST_CREDIT_FLAG > , A.STATUS > , ( > SELECT COMPANY_CODE > FROM VW_COMPANY_DETAIL > WHERE SUPPLIER_CODE = A.SUPPLIER_CODE > ) REF_COMPANY > , ( > SELECT ACNT_MODEL > FROM ITEM_CLASSES > WHERE ITEM_CLASS = A.ITEM_CLASS > AND COMPANY_CODE = A.COMPANY_CODE > ) ACNT_MODEL > , ( > SELECT STORE_TYPE > FROM VW_STORES > WHERE COMPANY_CODE = A.COMPANY_CODE > AND STORE_CODE = A.STORE_CODE > ) STORE_TYPE > , > CASE > WHEN A.DOCUMENT_TYPE IN ('W7', 'DC') AND A.STATUS != 'Z' THEN PKG_CONTROLS.GET_CONTROL_ACCOUNT (( > SELECT COMPANY_CODE > FROM VW_COMPANY_DETAIL > WHERE SUPPLIER_CODE = A.SUPPLIER_CODE > ), 'FG_TRANSFER_DR' ) > WHEN A.DOCUMENT_TYPE IN ('W5', '49') AND A.STATUS != 'Z' THEN PKG_CONTROLS.GET_CONTROL_ACCOUNT (( > SELECT COMPANY_CODE > FROM VW_COMPANY_DETAIL > WHERE SUPPLIER_CODE = A.SUPPLIER_CODE > ), 'FG_RECEIVED_DR' ) > ELSE > NULL > END DR_ACCOUNT_CODE > , CASE > WHEN A.DOCUMENT_TYPE IN ('44') AND A.STATUS != 'Z' AND NVL((SELECT STORE_TYPE FROM VW_STORES WHERE COMPANY_CODE = A.COMPANY_CODE AND STORE_CODE = A.STORE_CODE ), 'I') IN ('I', 'E') AND (SELECT ACNT_MODEL FROM ITEM_CLASSES WHERE ITEM_CLASS = A.ITEM_CLASS AND COMPANY_CODE = A.COMPANY_CODE) = 'STKS' THEN > PKG_IC.GET_POSTING_ACCOUNT(A.COMPANY_CODE, 'STKS', A.STORE_CODE, A.ITEM_CODE) > ELSE > PKG_IC.GET_POSTING_ACCOUNT(A.COMPANY_CODE, 'MEMO', A.STORE_CODE, A.ITEM_CODE) > END GRN_DR_ACCOUNT_CODE > , CASE > WHEN A.DOCUMENT_TYPE IN ('AP') AND A.STATUS != 'Z' THEN > ( SELECT GL_CODE||GL_SUB_CODE FROM EXPENSE_CODES WHERE COMPANY = A.COMPANY_CODE AND EXPENSE_CODE = A.ITEM_CODE ) > ELSE > NULL > END AP_DR_ACCOUNT_CODE > FROM VW_PURCHASE_LEDGER A, COMMODITIES B > WHERE PKG_CONTROLS.GET_REGN_NUMBER(A.COMPANY_CODE, 'GST') = '09AAACD1952B1Z4' > /* > -- Parameter add supplier gst number > -- By ss on dt. 10/08/2018 > */ > --AND NVL(GST_NUMBER, '$' ) LIKE NVL(:P_SUPP_GST_NUMBER,'%') > -- Add new parameter by ss on dt. 24/03/2018 > --AND ALLOW_GST_CREDIT LIKE NVL(:P_ALLOW_GST_CREDIT,'%') > AND (( 'E' ='E' AND A.DOCUMENT_DATE BETWEEN '01/06/2022' AND '30/06/2022' ) OR ( 'E' ='B' AND A.AREF_DATE BETWEEN '01/06/2022' AND '30/06/2022' )) > AND A.COMMODITY_CODE = B.COMMODITY_CODE (+) > --GROUP BY A.DATA_SOURCE,A.DOCUMENT_DATE,A.DOCUMENT_STRING,A.DOCUMENT_TYPE,A.AREF_NUMBER,A.AREF_DATE, A.COMPANY_CODE,A.SUPPLIER_CODE,A. IGST_RATE, A.CGST_RATE, A.SGST_RATE, A.CESS_RATE,B.COMMODITY_TYPE, A.COMMODITY_CODE ,A.GST_REVERSE_FLAG ,A.ALLOW_GST_CREDIT ,A.STATUS, A.ITEM_CLASS, A.STORE_CODE,A.ITEM_CODE > > UNION ALL > > SELECT > A.DATA_SOURCE DATA_SOURCE > , A.LINK_REF_DATE INVOICE_DATE > , A.LINK_REF_STRING BILL_NO > , A.LINK_REF_TYPE DOCUMENT_TYPE > , A.AREF_NUMBER SUPP_BILL > , A.AREF_DATE SUPP_DATE > , A.COMPANY_CODE COMP_CODE > , A.SUPPLIER_CODE SUPP_CODE > , 1 DATA_TYPE > , 'OH' DATA_DESC > , CASE > WHEN ALLOW_GST_CREDIT = 'Y' THEN > CASE > WHEN GST_REVERSE_FLAG = 'N' THEN > NVL(B.COMMODITY_TYPE, '$') > ELSE > DECODE (NVL(B.COMMODITY_TYPE,'#'), '001', '#1', '002', '#3') > END > ELSE > DECODE (NVL(B.COMMODITY_TYPE,'#'), '001', '#2', '002', '#4') > END COMMODITY_TYPE > , A.TAXABLE_AMT TAXABLE_AMT > , A.IGST_RATE > , A.CGST_RATE > , A.SGST_RATE > , NULL CESS_RATE > , A.IGST_OTHERS IGST_AMOUNT > , A.CGST_OTHERS CGST_AMOUNT > , A.SGST_OTHERS SGST_AMOUNT > , NULL CESS_AMOUNT > , NULL CESS_ON_QTY > ,( > NVL(IGST_OTHERS, 0) > + NVL(CGST_OTHERS, 0) > + NVL(SGST_OTHERS, 0) > ) TOTAL_GST > ,( > NVL(IGST_OTHERS, 0) > + NVL(CGST_OTHERS, 0) > + NVL(SGST_OTHERS, 0) > + NVL(TAXABLE_AMT, 0) > ) BILL_AMNT > , A.GST_REVERSE_FLAG GST_R_FLAG > , A.ALLOW_GST_CREDIT GST_CREDIT_FLAG > , A.STATUS STATUS > , ( > SELECT COMPANY_CODE > FROM VW_COMPANY_DETAIL > WHERE SUPPLIER_CODE = A.SUPPLIER_CODE > ) REF_COMPANY > , ( > SELECT ACNT_MODEL > FROM ITEM_CLASSES > WHERE ITEM_CLASS = PKG_IC.GET_ITEM_CLASS(A.ITEM_CODE) > AND COMPANY_CODE = A.COMPANY_CODE > ) ACNT_MODEL > , ( > SELECT STORE_TYPE > FROM VW_STORES > WHERE COMPANY_CODE = A.COMPANY_CODE > AND STORE_CODE = A.STORE_CODE > ) STORE_TYPE > , > CASE > WHEN A.VOUCHER_TYPE IN ('W7', 'DC') AND A.STATUS != 'Z' THEN PKG_CONTROLS.GET_CONTROL_ACCOUNT (( > SELECT COMPANY_CODE > FROM VW_COMPANY_DETAIL > WHERE SUPPLIER_CODE = A.SUPPLIER_CODE > ), 'FG_TRANSFER_DR' ) > WHEN A.VOUCHER_TYPE IN ('W5', '49') AND A.STATUS != 'Z' THEN PKG_CONTROLS.GET_CONTROL_ACCOUNT (( > SELECT COMPANY_CODE > FROM VW_COMPANY_DETAIL > WHERE SUPPLIER_CODE = A.SUPPLIER_CODE > ), 'FG_RECEIVED_DR' ) > ELSE > NULL > END DR_ACCOUNT_CODE > , CASE > WHEN A.VOUCHER_TYPE IN ('44') AND A.STATUS != 'Z' AND NVL((SELECT STORE_TYPE FROM VW_STORES WHERE COMPANY_CODE = A.COMPANY_CODE AND STORE_CODE = A.STORE_CODE ), 'I') IN ('I', 'E') AND (SELECT ACNT_MODEL FROM ITEM_CLASSES WHERE ITEM_CLASS = PKG_IC.GET_ITEM_CLASS(A.ITEM_CODE) AND COMPANY_CODE = A.COMPANY_CODE) = 'STKS' THEN > PKG_IC.GET_POSTING_ACCOUNT(A.COMPANY_CODE, 'STKS', A.STORE_CODE, A.ITEM_CODE) > ELSE > PKG_IC.GET_POSTING_ACCOUNT (A.COMPANY_CODE, 'MEMO', A.STORE_CODE, A.ITEM_CODE) > END GRN_DR_ACCOUNT_CODE > , CASE > WHEN A.VOUCHER_TYPE IN ('AP') AND A.STATUS != 'Z' THEN > ( SELECT GL_CODE||GL_SUB_CODE FROM EXPENSE_CODES WHERE COMPANY = A.COMPANY_CODE AND EXPENSE_CODE = A.ITEM_CODE ) > ELSE > NULL > END AP_DR_ACCOUNT_CODE > FROM VW_OVERHEAD_LEDGER A ,COMMODITIES B > WHERE PKG_CONTROLS.GET_REGN_NUMBER(A.COMPANY_CODE, 'GST') = '09AAACD1952B1Z4' > /* > -- Parameter add supplier gst number > -- By ss on dt. 10/08/2018 > */ > --AND NVL(GST_NUMBER, '$') LIKE NVL(:P_SUPP_GST_NUMBER,'%') > -- Add new parameter by ss on dt. 24/03/2018 > --AND ALLOW_GST_CREDIT LIKE NVL(:P_ALLOW_GST_CREDIT,'%') > /* > -- Bgn -- > -- Against call id - 8839 > --Communication gap by both user. So revert back all change. > -- Bgn -- > -- Against call id - 8839 > AND A.LINK_SPV_TYPE IS NOT NULL > -- End -- > -- End -- > */ > AND (( 'E' ='E' AND A.LINK_REF_DATE BETWEEN '01/06/2022' AND '30/06/2022' ) OR ( 'E' ='B' AND A.AREF_DATE BETWEEN '01/06/2022' AND '30/06/2022' )) > AND A.COMMODITY_CODE = B.COMMODITY_CODE (+) >-- GROUP BY A.DATA_SOURCE,A.LINK_REF_DATE,A.AREF_DATE, A.LINK_REF_STRING,A.LINK_REF_TYPE,A.AREF_NUMBER,A.AREF_DATE, A.COMPANY_CODE,A.SUPPLIER_CODE,A. IGST_RATE, A.CGST_RATE, A.SGST_RATE,B.COMMODITY_TYPE/*,A.COMMODITY_CODE*/,A.GST_REVERSE_FLAG,A.ALLOW_GST_CREDIT,A.STATUS,PKG_IC.GET_ITEM_CLASS(A.ITEM_CODE), A.STORE_CODE,A.VOUCHER_TYPE,A.ITEM_CODE > ) A >GROUP BY A.DATA_SOURCE, A.COMP_CODE, A.BILL_NO, A.INVOICE_DATE, A.SUPP_CODE, A.SUPP_BILL, A.SUPP_DATE, A.DOCUMENT_TYPE, A.DATA_TYPE, A.DATA_DESC, A.COMMODITY_TYPE, A.IGST_RATE, A.CGST_RATE, A.SGST_RATE, A.CESS_RATE, A.GST_R_FLAG, A.GST_CREDIT_FLAG, A.STATUS, DR_ACCOUNT_CODE, AP_DR_ACCOUNT_CODE, GRN_DR_ACCOUNT_CODE >ORDER BY COMMODITY_TYPE, INVOICE_DATE, BILL_NO, DATA_TYPE > >"REM WORKSPACETAB2","FINAL QUERY FOR PROCEDURE",,317 >INSERT INTO TT$INV_WISE_PURCHASE_DTL >( > COMMODITY_TYPE > , COMMODITY_DESC > , COMP_CODE > , SHORT_NAME > , DOCUMENT_STRING > , DOCUMENT_DATE > , SUPP_BILL_NUMBER > , SUPP_BILL_DATE > , SUPPLIER_CODE > , SUPPLIER_NAME > , SUPPLIER_GST_NUMBER > , DATA_DESC > , ACCOUNT_CODE > , ACCOUNT_NAME > , SUB_ACCOUNT_CODE > , SUB_ACCOUNT_NAME > , TAXABLE_AMOUNT > , IGST_RATE > , IGST_AMOUNT > , CGST_RATE > , CGST_AMOUNT > , SGST_RATE > , SGST_AMOUNT > , CESS_RATE > , CESS_AMOUNT > , CESS_ON_QTY > , TOTAL_GST > , BILL_AMOUNT >) >SELECT > COMMODITY_TYPE > , COMMODITY_DESC > , COMP_CODE > , SHORT_NAME > , BILL_NO > , INVOICE_DATE > , SUPP_BILL > , SUPP_DATE > , SUPP_CODE > , SUPP_NAME > , GST_NUMBER > , DATA_DESC > , SUBSTR(DR_ACCOUNT_CODE,0,5) ACC_CODE > , PKG_GL.GET_ACCOUNT_NAME(SUBSTR(DR_ACCOUNT_CODE,0,5)) ACC_DESC > , SUBSTR(DR_ACCOUNT_CODE,6,9) SUB_ACC_CODE > , PKG_GL.GET_ACCOUNT_NAME(DR_ACCOUNT_CODE) SUB_ACC_DESC > , TAXABLE_AMT > , IGST_RATE > , IGST_AMOUNT > , CGST_RATE > , CGST_AMOUNT > , SGST_RATE > , SGST_AMOUNT > , CESS_RATE > , CESS_AMOUNT > , CESS_ON_QTY > , TOTAL_GST > , BILL_AMNT >FROM > ( > > SELECT > A.DATA_SOURCE > , A.COMP_CODE > , PKG_CONTROLS.GET_COMPANY_INFO(A.COMP_CODE, 'SHORT_NAME') SHORT_NAME > , A.BILL_NO > , A.INVOICE_DATE > , A.SUPP_CODE > , (SELECT GST_NUMBER FROM VW_SUPPLIERS WHERE COMPANY_CODE = A.COMP_CODE AND SUPPLIER_CODE = A.SUPP_CODE) GST_NUMBER > , A.SUPP_BILL > , A.SUPP_DATE > , A.DOCUMENT_TYPE > , NVL(CASE WHEN DATA_SOURCE IN ('SLR','CEV') THEN PKG_AR.GET_CUSTOMER_NAME(SUPP_CODE) ELSE PKG_AP.GET_SUPPLIER_NAME(SUPP_CODE) END,PKG_REPORTS.GET_COMPANY_NAME(SUPP_CODE)) SUPP_NAME > , A.DATA_TYPE > , A.DATA_DESC > , A.COMMODITY_TYPE > , CASE > WHEN A.COMMODITY_TYPE = '#1' THEN > 'PURCHASE OF GOODS UNDER REVERSE CHARGE' > WHEN A.COMMODITY_TYPE = '#2' THEN > 'PURCHASE OF GOODS UNDER GST CREDIT NOT ALLOWED' > WHEN A.COMMODITY_TYPE = '#3' THEN > 'PURCHASE OF SERVICES UNDER REVERSE CHARGE' > WHEN A.COMMODITY_TYPE = '#4' THEN > 'PURCHASE OF SERVICES UNDER GST NOT ALLOWED' > WHEN A.COMMODITY_TYPE = '$' THEN > 'PURCHASES WITH MISSING HSN' > ELSE > NVL(PKG_POPLISTS.POPDATA_DESCRIPTION('CMDTTYPE',A.COMMODITY_TYPE),A.COMMODITY_TYPE) > END COMMODITY_DESC > , SUM(A.TAXABLE_AMT) TAXABLE_AMT > , A.IGST_RATE > , A.CGST_RATE > , A.SGST_RATE > , A.CESS_RATE > , SUM(A.IGST_AMOUNT) IGST_AMOUNT > , SUM(A.CGST_AMOUNT) CGST_AMOUNT > , SUM(A.SGST_AMOUNT) SGST_AMOUNT > , SUM(A.CESS_AMOUNT) CESS_AMOUNT > , SUM(A.CESS_ON_QTY) CESS_ON_QTY > , SUM(A.TOTAL_GST ) TOTAL_GST > , ROUND(SUM(A.BILL_AMNT)) BILL_AMNT > , A.GST_R_FLAG > , A.GST_CREDIT_FLAG > , A.STATUS > , NVL( > CASE > WHEN A.DOCUMENT_TYPE IN ('W7', 'DC', 'W5', '49') THEN DR_ACCOUNT_CODE > WHEN A.DOCUMENT_TYPE IN ('AP' ) THEN AP_DR_ACCOUNT_CODE > ELSE > NULL > END > , GRN_DR_ACCOUNT_CODE > ) DR_ACCOUNT_CODE > FROM ( > SELECT > A.DATA_SOURCE DATA_SOURCE > , A.DOCUMENT_DATE INVOICE_DATE > , A.DOCUMENT_STRING BILL_NO > , A.DOCUMENT_TYPE DOCUMENT_TYPE > , A.AREF_NUMBER SUPP_BILL > , A.AREF_DATE SUPP_DATE > , A.COMPANY_CODE COMP_CODE > , A.SUPPLIER_CODE SUPP_CODE > , 0 DATA_TYPE > , 'PB' DATA_DESC > , CASE > WHEN ALLOW_GST_CREDIT = 'Y' THEN > CASE > WHEN GST_REVERSE_FLAG = 'N' THEN NVL(B.COMMODITY_TYPE, '$') > ELSE > DECODE (NVL(B.COMMODITY_TYPE,'#'), '001', '#1', '002', '#3') > END > ELSE > DECODE (NVL(B.COMMODITY_TYPE,'#'), '001', '#2', '002', '#4') > END COMMODITY_TYPE > , A.TAXABLE_AMT TAXABLE_AMT > , A.IGST_RATE > , A.CGST_RATE > , A.SGST_RATE > , A.CESS_RATE > , A.IGST_AMOUNT IGST_AMOUNT > , A.CGST_AMOUNT CGST_AMOUNT > , A.SGST_AMOUNT SGST_AMOUNT > , A.CESS_AMOUNT CESS_AMOUNT > , A.CESS_ON_QTY CESS_ON_QTY > , NVL(IGST_AMOUNT, 0) > + NVL(CGST_AMOUNT, 0) > + NVL(SGST_AMOUNT, 0) > + NVL(CESS_AMOUNT, 0) > + NVL(CESS_ON_QTY, 0) TOTAL_GST > ,( > A.BILL_AMOUNT > - NVL(OTHR_AMOUNT,0) > - NVL(IGST_OTHERS,0) > - NVL(CGST_OTHERS,0) > - NVL(SGST_OTHERS,0) > ) BILL_AMNT > , A.GST_REVERSE_FLAG GST_R_FLAG > , A.ALLOW_GST_CREDIT GST_CREDIT_FLAG > , A.STATUS > , ( > SELECT COMPANY_CODE > FROM VW_COMPANY_DETAIL > WHERE SUPPLIER_CODE = A.SUPPLIER_CODE > ) REF_COMPANY > , ( > SELECT ACNT_MODEL > FROM ITEM_CLASSES > WHERE ITEM_CLASS = A.ITEM_CLASS > AND COMPANY_CODE = A.COMPANY_CODE > ) ACNT_MODEL > , ( > SELECT STORE_TYPE > FROM VW_STORES > WHERE COMPANY_CODE = A.COMPANY_CODE > AND STORE_CODE = A.STORE_CODE > ) STORE_TYPE > , > CASE > WHEN A.DOCUMENT_TYPE IN ('W7', 'DC') AND A.STATUS != 'Z' THEN PKG_CONTROLS.GET_CONTROL_ACCOUNT (( > SELECT COMPANY_CODE > FROM VW_COMPANY_DETAIL > WHERE SUPPLIER_CODE = A.SUPPLIER_CODE > ), 'FG_TRANSFER_DR' ) > WHEN A.DOCUMENT_TYPE IN ('W5', '49') AND A.STATUS != 'Z' THEN PKG_CONTROLS.GET_CONTROL_ACCOUNT (( > SELECT COMPANY_CODE > FROM VW_COMPANY_DETAIL > WHERE SUPPLIER_CODE = A.SUPPLIER_CODE > ), 'FG_RECEIVED_DR' ) > ELSE > NULL > END DR_ACCOUNT_CODE > , CASE > WHEN A.DOCUMENT_TYPE IN ('44') AND A.STATUS != 'Z' AND NVL((SELECT STORE_TYPE FROM VW_STORES WHERE COMPANY_CODE = A.COMPANY_CODE AND STORE_CODE = A.STORE_CODE ), 'I') IN ('I', 'E') AND (SELECT ACNT_MODEL FROM ITEM_CLASSES WHERE ITEM_CLASS = A.ITEM_CLASS AND COMPANY_CODE = A.COMPANY_CODE) = 'STKS' THEN > PKG_IC.GET_POSTING_ACCOUNT(A.COMPANY_CODE, 'STKS', A.STORE_CODE, A.ITEM_CODE) > ELSE > PKG_IC.GET_POSTING_ACCOUNT(A.COMPANY_CODE, 'MEMO', A.STORE_CODE, A.ITEM_CODE) > END GRN_DR_ACCOUNT_CODE > , CASE > WHEN A.DOCUMENT_TYPE IN ('AP') AND A.STATUS != 'Z' THEN > ( SELECT GL_CODE||GL_SUB_CODE FROM EXPENSE_CODES WHERE COMPANY = A.COMPANY_CODE AND EXPENSE_CODE = A.ITEM_CODE ) > ELSE > NULL > END AP_DR_ACCOUNT_CODE > FROM VW_PURCHASE_LEDGER A, COMMODITIES B > WHERE PKG_CONTROLS.GET_REGN_NUMBER(A.COMPANY_CODE, 'GST') = :P_GST_NUMBER > AND NVL(GST_NUMBER, '$' ) LIKE NVL(:P_SUPP_GST_NUMBER,'%') > AND ALLOW_GST_CREDIT LIKE NVL(:P_ALLOW_GST_CREDIT,'%') > AND (( :P_RCPT_BILL_DATE ='E' AND A.DOCUMENT_DATE BETWEEN :P_QRY_BGN_DATE AND :P_QRY_END_DATE ) OR ( :P_RCPT_BILL_DATE ='B' AND A.AREF_DATE BETWEEN :P_QRY_BGN_DATE AND :P_QRY_END_DATE )) > AND A.COMMODITY_CODE = B.COMMODITY_CODE (+) > > UNION ALL > > SELECT > A.DATA_SOURCE DATA_SOURCE > , A.LINK_REF_DATE INVOICE_DATE > , A.LINK_REF_STRING BILL_NO > , A.LINK_REF_TYPE DOCUMENT_TYPE > , A.AREF_NUMBER SUPP_BILL > , A.AREF_DATE SUPP_DATE > , A.COMPANY_CODE COMP_CODE > , A.SUPPLIER_CODE SUPP_CODE > , 1 DATA_TYPE > , 'OH' DATA_DESC > , CASE > WHEN ALLOW_GST_CREDIT = 'Y' THEN > CASE > WHEN GST_REVERSE_FLAG = 'N' THEN > NVL(B.COMMODITY_TYPE, '$') > ELSE > DECODE (NVL(B.COMMODITY_TYPE,'#'), '001', '#1', '002', '#3') > END > ELSE > DECODE (NVL(B.COMMODITY_TYPE,'#'), '001', '#2', '002', '#4') > END COMMODITY_TYPE > , A.TAXABLE_AMT TAXABLE_AMT > , A.IGST_RATE > , A.CGST_RATE > , A.SGST_RATE > , NULL CESS_RATE > , A.IGST_OTHERS IGST_AMOUNT > , A.CGST_OTHERS CGST_AMOUNT > , A.SGST_OTHERS SGST_AMOUNT > , NULL CESS_AMOUNT > , NULL CESS_ON_QTY > ,( > NVL(IGST_OTHERS, 0) > + NVL(CGST_OTHERS, 0) > + NVL(SGST_OTHERS, 0) > ) TOTAL_GST > ,( > NVL(IGST_OTHERS, 0) > + NVL(CGST_OTHERS, 0) > + NVL(SGST_OTHERS, 0) > + NVL(TAXABLE_AMT, 0) > ) BILL_AMNT > , A.GST_REVERSE_FLAG GST_R_FLAG > , A.ALLOW_GST_CREDIT GST_CREDIT_FLAG > , A.STATUS STATUS > , ( > SELECT COMPANY_CODE > FROM VW_COMPANY_DETAIL > WHERE SUPPLIER_CODE = A.SUPPLIER_CODE > ) REF_COMPANY > , ( > SELECT ACNT_MODEL > FROM ITEM_CLASSES > WHERE ITEM_CLASS = PKG_IC.GET_ITEM_CLASS(A.ITEM_CODE) > AND COMPANY_CODE = A.COMPANY_CODE > ) ACNT_MODEL > , ( > SELECT STORE_TYPE > FROM VW_STORES > WHERE COMPANY_CODE = A.COMPANY_CODE > AND STORE_CODE = A.STORE_CODE > ) STORE_TYPE > , > CASE > WHEN A.VOUCHER_TYPE IN ('W7', 'DC') AND A.STATUS != 'Z' THEN PKG_CONTROLS.GET_CONTROL_ACCOUNT (( > SELECT COMPANY_CODE > FROM VW_COMPANY_DETAIL > WHERE SUPPLIER_CODE = A.SUPPLIER_CODE > ), 'FG_TRANSFER_DR' ) > WHEN A.VOUCHER_TYPE IN ('W5', '49') AND A.STATUS != 'Z' THEN PKG_CONTROLS.GET_CONTROL_ACCOUNT (( > SELECT COMPANY_CODE > FROM VW_COMPANY_DETAIL > WHERE SUPPLIER_CODE = A.SUPPLIER_CODE > ), 'FG_RECEIVED_DR' ) > ELSE > NULL > END DR_ACCOUNT_CODE > , CASE > WHEN A.VOUCHER_TYPE IN ('44') AND A.STATUS != 'Z' AND NVL((SELECT STORE_TYPE FROM VW_STORES WHERE COMPANY_CODE = A.COMPANY_CODE AND STORE_CODE = A.STORE_CODE ), 'I') IN ('I', 'E') AND (SELECT ACNT_MODEL FROM ITEM_CLASSES WHERE ITEM_CLASS = PKG_IC.GET_ITEM_CLASS(A.ITEM_CODE) AND COMPANY_CODE = A.COMPANY_CODE) = 'STKS' THEN > PKG_IC.GET_POSTING_ACCOUNT(A.COMPANY_CODE, 'STKS', A.STORE_CODE, A.ITEM_CODE) > ELSE > PKG_IC.GET_POSTING_ACCOUNT (A.COMPANY_CODE, 'MEMO', A.STORE_CODE, A.ITEM_CODE) > END GRN_DR_ACCOUNT_CODE > , CASE > WHEN A.VOUCHER_TYPE IN ('AP') AND A.STATUS != 'Z' THEN > ( SELECT GL_CODE||GL_SUB_CODE FROM EXPENSE_CODES WHERE COMPANY = A.COMPANY_CODE AND EXPENSE_CODE = A.ITEM_CODE ) > ELSE > NULL > END AP_DR_ACCOUNT_CODE > FROM VW_OVERHEAD_LEDGER A ,COMMODITIES B > FROM VW_OVERHEAD_LEDGER A ,COMMODITIES B > WHERE PKG_CONTROLS.GET_REGN_NUMBER(A.COMPANY_CODE, 'GST') = :P_GST_NUMBER > AND NVL(GST_NUMBER, '$') LIKE NVL(:P_SUPP_GST_NUMBER,'%') > AND ALLOW_GST_CREDIT LIKE NVL(:P_ALLOW_GST_CREDIT,'%') > AND (( :P_RCPT_BILL_DATE ='E' AND A.LINK_REF_DATE BETWEEN :P_QRY_BGN_DATE AND :P_QRY_END_DATE ) OR ( :P_RCPT_BILL_DATE ='B' AND A.AREF_DATE BETWEEN :P_QRY_BGN_DATE AND :P_QRY_END_DATE )) > AND A.COMMODITY_CODE = B.COMMODITY_CODE (+) > ) A > GROUP BY A.DATA_SOURCE, A.COMP_CODE, A.BILL_NO, A.INVOICE_DATE, A.SUPP_CODE, A.SUPP_BILL, A.SUPP_DATE, A.DOCUMENT_TYPE, A.DATA_TYPE, A.DATA_DESC, A.COMMODITY_TYPE, A.IGST_RATE, A.CGST_RATE, A.SGST_RATE, A.CESS_RATE, A.GST_R_FLAG, A.GST_CREDIT_FLAG, A.STATUS, DR_ACCOUNT_CODE, AP_DR_ACCOUNT_CODE, GRN_DR_ACCOUNT_CODE > ) > ORDER BY COMMODITY_TYPE, INVOICE_DATE, BILL_NO, DATA_TYPE >"REM WORKSPACETAB3",Query43,,1 >EXEC PKG_PR.INV_WISE_PURCH_DTL('09AAACD1952B1Z4', NULL, NULL, 'E', '01/06/2022', '30/06/2022') >"REM WORKSPACETAB4",Query44,,4 >SELECT * FROM TT$INV_WISE_PURCHASE_DTL >WHERE ACCOUNT_CODE IS NULL >AND STATUS !='Z' >ORDER BY COMMODITY_TYPE, DOCUMENT_DATE, DOCUMENT_STRING, DATA_TYPE >"REM WORKSPACETAB5",Query41,,339 >/* >DELETE FROM TT$INV_WISE_PURCHASE_DTL; >INSERT INTO TT$INV_WISE_PURCHASE_DTL >( > COMMODITY_TYPE > , COMMODITY_DESC > , COMP_CODE > , SHORT_NAME > , DOCUMENT_STRING > , DOCUMENT_DATE > , SUPP_BILL_NUMBER > , SUPP_BILL_DATE > , SUPPLIER_CODE > , SUPPLIER_NAME > , SUPPLIER_GST_NUMBER > , DATA_DESC > , ACCOUNT_CODE > , ACCOUNT_NAME > , SUB_ACCOUNT_CODE > , SUB_ACCOUNT_NAME > , TAXABLE_AMOUNT > , IGST_RATE > , IGST_AMOUNT > , CGST_RATE > , CGST_AMOUNT > , SGST_RATE > , SGST_AMOUNT > , CESS_RATE > , CESS_AMOUNT > , CESS_ON_QTY > , TOTAL_GST > , BILL_AMOUNT >) >*/ >SELECT >--/* > > COMMODITY_TYPE > , COMMODITY_DESC > , COMP_CODE > , SHORT_NAME > , BILL_NO > , INVOICE_DATE > , SUPP_BILL > , SUPP_DATE > , SUPP_CODE > , SUPP_NAME > , GST_NUMBER > , DATA_DESC > , SUBSTR(DR_ACCOUNT_CODE,0,5) ACC_CODE > , PKG_GL.GET_ACCOUNT_NAME(SUBSTR(DR_ACCOUNT_CODE,0,5)) ACC_DESC > , SUBSTR(DR_ACCOUNT_CODE,6,9) SUB_ACC_CODE > , PKG_GL.GET_ACCOUNT_NAME(DR_ACCOUNT_CODE) SUB_ACC_DESC > , TAXABLE_AMT > , IGST_RATE > , IGST_AMOUNT > , CGST_RATE > , CGST_AMOUNT > , SGST_RATE > , SGST_AMOUNT > , CESS_RATE > , CESS_AMOUNT > , CESS_ON_QTY > , TOTAL_GST > , BILL_AMNT > , STATUS >--*/ >/* > --DOCUMENT_TYPE > SUM(TAXABLE_AMT) > , SUM(IGST_AMOUNT) > , SUM(CGST_AMOUNT) > , SUM(SGST_AMOUNT) > , SUM(CESS_AMOUNT) > , SUM(CESS_ON_QTY) > , SUM(TOTAL_GST) > , ROUND(SUM(BILL_AMNT)) >*/ >FROM > ( > > SELECT > A.DATA_SOURCE > , A.COMP_CODE > , PKG_CONTROLS.GET_COMPANY_INFO(A.COMP_CODE, 'SHORT_NAME') SHORT_NAME > , A.BILL_NO > , A.INVOICE_DATE > , A.SUPP_CODE > , (SELECT GST_NUMBER FROM VW_SUPPLIERS WHERE COMPANY_CODE = A.COMP_CODE AND SUPPLIER_CODE = A.SUPP_CODE) GST_NUMBER > , A.SUPP_BILL > , A.SUPP_DATE > , A.DOCUMENT_TYPE > , NVL(CASE WHEN DATA_SOURCE IN ('SLR','CEV') THEN PKG_AR.GET_CUSTOMER_NAME(SUPP_CODE) ELSE PKG_AP.GET_SUPPLIER_NAME(SUPP_CODE) END,PKG_REPORTS.GET_COMPANY_NAME(SUPP_CODE)) SUPP_NAME > , A.DATA_TYPE > , A.DATA_DESC > , A.COMMODITY_TYPE > , CASE > WHEN A.COMMODITY_TYPE = '#1' THEN > 'PURCHASE OF GOODS UNDER REVERSE CHARGE' > WHEN A.COMMODITY_TYPE = '#2' THEN > 'PURCHASE OF GOODS UNDER GST CREDIT NOT ALLOWED' > WHEN A.COMMODITY_TYPE = '#3' THEN > 'PURCHASE OF SERVICES UNDER REVERSE CHARGE' > WHEN A.COMMODITY_TYPE = '#4' THEN > 'PURCHASE OF SERVICES UNDER GST NOT ALLOWED' > WHEN A.COMMODITY_TYPE = '$' THEN > 'PURCHASES WITH MISSING HSN' > ELSE > NVL(PKG_POPLISTS.POPDATA_DESCRIPTION('CMDTTYPE',A.COMMODITY_TYPE),A.COMMODITY_TYPE) > END COMMODITY_DESC > , SUM(A.TAXABLE_AMT) TAXABLE_AMT > , A.IGST_RATE > , A.CGST_RATE > , A.SGST_RATE > , A.CESS_RATE > , SUM(A.IGST_AMOUNT) IGST_AMOUNT > , SUM(A.CGST_AMOUNT) CGST_AMOUNT > , SUM(A.SGST_AMOUNT) SGST_AMOUNT > , SUM(A.CESS_AMOUNT) CESS_AMOUNT > , SUM(A.CESS_ON_QTY) CESS_ON_QTY > , SUM(A.TOTAL_GST ) TOTAL_GST > , SUM(A.BILL_AMNT) BILL_AMNT > , A.GST_R_FLAG > , A.GST_CREDIT_FLAG > , A.STATUS > , NVL( > CASE > WHEN A.DOCUMENT_TYPE IN ('W7', 'DC', 'W5', '49') THEN DR_ACCOUNT_CODE > WHEN A.DOCUMENT_TYPE IN ('AP' ) THEN AP_DR_ACCOUNT_CODE > ELSE > NULL > END > , GRN_DR_ACCOUNT_CODE > ) DR_ACCOUNT_CODE > FROM ( > SELECT > A.DATA_SOURCE DATA_SOURCE > , A.DOCUMENT_DATE INVOICE_DATE > , A.DOCUMENT_STRING BILL_NO > , A.DOCUMENT_TYPE DOCUMENT_TYPE > , A.AREF_NUMBER SUPP_BILL > , A.AREF_DATE SUPP_DATE > , A.COMPANY_CODE COMP_CODE > , A.SUPPLIER_CODE SUPP_CODE > , 0 DATA_TYPE > , 'PB' DATA_DESC > , CASE > WHEN ALLOW_GST_CREDIT = 'Y' THEN > CASE > WHEN GST_REVERSE_FLAG = 'N' THEN NVL(B.COMMODITY_TYPE, '$') > ELSE > DECODE (NVL(B.COMMODITY_TYPE,'#'), '001', '#1', '002', '#3') > END > ELSE > DECODE (NVL(B.COMMODITY_TYPE,'#'), '001', '#2', '002', '#4') > END COMMODITY_TYPE > , A.TAXABLE_AMT TAXABLE_AMT > , A.IGST_RATE > , A.CGST_RATE > , A.SGST_RATE > , A.CESS_RATE > , A.IGST_AMOUNT IGST_AMOUNT > , A.CGST_AMOUNT CGST_AMOUNT > , A.SGST_AMOUNT SGST_AMOUNT > , A.CESS_AMOUNT CESS_AMOUNT > , A.CESS_ON_QTY CESS_ON_QTY > , NVL(IGST_AMOUNT, 0) > + NVL(CGST_AMOUNT, 0) > + NVL(SGST_AMOUNT, 0) > + NVL(CESS_AMOUNT, 0) > + NVL(CESS_ON_QTY, 0) TOTAL_GST > ,( > A.BILL_AMOUNT > - NVL(OTHR_AMOUNT,0) > - NVL(IGST_OTHERS,0) > - NVL(CGST_OTHERS,0) > - NVL(SGST_OTHERS,0) > ) BILL_AMNT > , A.GST_REVERSE_FLAG GST_R_FLAG > , A.ALLOW_GST_CREDIT GST_CREDIT_FLAG > , A.STATUS > , ( > SELECT COMPANY_CODE > FROM VW_COMPANY_DETAIL > WHERE SUPPLIER_CODE = A.SUPPLIER_CODE > ) REF_COMPANY > , ( > SELECT ACNT_MODEL > FROM ITEM_CLASSES > WHERE ITEM_CLASS = A.ITEM_CLASS > AND COMPANY_CODE = A.COMPANY_CODE > ) ACNT_MODEL > , ( > SELECT STORE_TYPE > FROM VW_STORES > WHERE COMPANY_CODE = A.COMPANY_CODE > AND STORE_CODE = A.STORE_CODE > ) STORE_TYPE > , > CASE > WHEN A.DOCUMENT_TYPE IN ('W7', 'DC') AND A.STATUS != 'Z' THEN PKG_CONTROLS.GET_CONTROL_ACCOUNT (( > SELECT COMPANY_CODE > FROM VW_COMPANY_DETAIL > WHERE SUPPLIER_CODE = A.SUPPLIER_CODE > ), 'FG_TRANSFER_DR' ) > WHEN A.DOCUMENT_TYPE IN ('W5', '49') AND A.STATUS != 'Z' THEN PKG_CONTROLS.GET_CONTROL_ACCOUNT (( > SELECT COMPANY_CODE > FROM VW_COMPANY_DETAIL > WHERE SUPPLIER_CODE = A.SUPPLIER_CODE > ), 'FG_RECEIVED_DR' ) > ELSE > NULL > END DR_ACCOUNT_CODE > , CASE > WHEN A.DOCUMENT_TYPE IN ('44') AND A.STATUS != 'Z' AND NVL((SELECT STORE_TYPE FROM VW_STORES WHERE COMPANY_CODE = A.COMPANY_CODE AND STORE_CODE = A.STORE_CODE ), 'I') IN ('I', 'E') AND (SELECT ACNT_MODEL FROM ITEM_CLASSES WHERE ITEM_CLASS = A.ITEM_CLASS AND COMPANY_CODE = A.COMPANY_CODE) = 'STKS' THEN > PKG_IC.GET_POSTING_ACCOUNT(A.COMPANY_CODE, 'STKS', A.STORE_CODE, A.ITEM_CODE) > ELSE > PKG_IC.GET_POSTING_ACCOUNT(A.COMPANY_CODE, 'MEMO', A.STORE_CODE, A.ITEM_CODE) > END GRN_DR_ACCOUNT_CODE > , CASE > WHEN A.DOCUMENT_TYPE IN ('AP') AND A.STATUS != 'Z' THEN > ( SELECT GL_CODE||GL_SUB_CODE FROM EXPENSE_CODES WHERE COMPANY = A.COMPANY_CODE AND EXPENSE_CODE = A.ITEM_CODE ) > ELSE > NULL > END AP_DR_ACCOUNT_CODE > FROM VW_PURCHASE_LEDGER A, COMMODITIES B > WHERE PKG_CONTROLS.GET_REGN_NUMBER(A.COMPANY_CODE, 'GST') = '09AAACD1952B1Z4' > --AND NVL(GST_NUMBER, '$' ) LIKE NVL(:P_SUPP_GST_NUMBER,'%') > --AND ALLOW_GST_CREDIT LIKE NVL(:P_ALLOW_GST_CREDIT,'%') > AND (( 'E' ='E' AND A.DOCUMENT_DATE BETWEEN '01/06/2022' AND '30/06/2022' ) OR ( 'E' ='B' AND A.AREF_DATE BETWEEN '01/06/2022' AND '30/06/2022' )) > AND A.COMMODITY_CODE = B.COMMODITY_CODE (+) > > UNION ALL > > SELECT > A.DATA_SOURCE DATA_SOURCE > , A.LINK_REF_DATE INVOICE_DATE > , A.LINK_REF_STRING BILL_NO > , A.LINK_REF_TYPE DOCUMENT_TYPE > , A.AREF_NUMBER SUPP_BILL > , A.AREF_DATE SUPP_DATE > , A.COMPANY_CODE COMP_CODE > , A.SUPPLIER_CODE SUPP_CODE > , 1 DATA_TYPE > , 'OH' DATA_DESC > , CASE > WHEN ALLOW_GST_CREDIT = 'Y' THEN > CASE > WHEN GST_REVERSE_FLAG = 'N' THEN > NVL(B.COMMODITY_TYPE, '$') > ELSE > DECODE (NVL(B.COMMODITY_TYPE,'#'), '001', '#1', '002', '#3') > END > ELSE > DECODE (NVL(B.COMMODITY_TYPE,'#'), '001', '#2', '002', '#4') > END COMMODITY_TYPE > , A.TAXABLE_AMT TAXABLE_AMT > , A.IGST_RATE > , A.CGST_RATE > , A.SGST_RATE > , NULL CESS_RATE > , A.IGST_OTHERS IGST_AMOUNT > , A.CGST_OTHERS CGST_AMOUNT > , A.SGST_OTHERS SGST_AMOUNT > , NULL CESS_AMOUNT > , NULL CESS_ON_QTY > ,( > NVL(IGST_OTHERS, 0) > + NVL(CGST_OTHERS, 0) > + NVL(SGST_OTHERS, 0) > ) TOTAL_GST > ,( > NVL(IGST_OTHERS, 0) > + NVL(CGST_OTHERS, 0) > + NVL(SGST_OTHERS, 0) > + NVL(TAXABLE_AMT, 0) > ) BILL_AMNT > , A.GST_REVERSE_FLAG GST_R_FLAG > , A.ALLOW_GST_CREDIT GST_CREDIT_FLAG > , A.STATUS STATUS > , ( > SELECT COMPANY_CODE > FROM VW_COMPANY_DETAIL > WHERE SUPPLIER_CODE = A.SUPPLIER_CODE > ) REF_COMPANY > , ( > SELECT ACNT_MODEL > FROM ITEM_CLASSES > WHERE ITEM_CLASS = PKG_IC.GET_ITEM_CLASS(A.ITEM_CODE) > AND COMPANY_CODE = A.COMPANY_CODE > ) ACNT_MODEL > , ( > SELECT STORE_TYPE > FROM VW_STORES > WHERE COMPANY_CODE = A.COMPANY_CODE > AND STORE_CODE = A.STORE_CODE > ) STORE_TYPE > , > CASE > WHEN A.VOUCHER_TYPE IN ('W7', 'DC') AND A.STATUS != 'Z' THEN PKG_CONTROLS.GET_CONTROL_ACCOUNT (( > SELECT COMPANY_CODE > FROM VW_COMPANY_DETAIL > WHERE SUPPLIER_CODE = A.SUPPLIER_CODE > ), 'FG_TRANSFER_DR' ) > WHEN A.VOUCHER_TYPE IN ('W5', '49') AND A.STATUS != 'Z' THEN PKG_CONTROLS.GET_CONTROL_ACCOUNT (( > SELECT COMPANY_CODE > FROM VW_COMPANY_DETAIL > WHERE SUPPLIER_CODE = A.SUPPLIER_CODE > ), 'FG_RECEIVED_DR' ) > ELSE > NULL > END DR_ACCOUNT_CODE > , CASE > WHEN A.VOUCHER_TYPE IN ('44') AND A.STATUS != 'Z' AND NVL((SELECT STORE_TYPE FROM VW_STORES WHERE COMPANY_CODE = A.COMPANY_CODE AND STORE_CODE = A.STORE_CODE ), 'I') IN ('I', 'E') AND (SELECT ACNT_MODEL FROM ITEM_CLASSES WHERE ITEM_CLASS = PKG_IC.GET_ITEM_CLASS(A.ITEM_CODE) AND COMPANY_CODE = A.COMPANY_CODE) = 'STKS' THEN > PKG_IC.GET_POSTING_ACCOUNT(A.COMPANY_CODE, 'STKS', A.STORE_CODE, A.ITEM_CODE) > ELSE > PKG_IC.GET_POSTING_ACCOUNT (A.COMPANY_CODE, 'MEMO', A.STORE_CODE, A.ITEM_CODE) > END GRN_DR_ACCOUNT_CODE > , CASE > WHEN A.VOUCHER_TYPE IN ('AP') AND A.STATUS != 'Z' THEN > ( SELECT GL_CODE||GL_SUB_CODE FROM EXPENSE_CODES WHERE COMPANY = A.COMPANY_CODE AND EXPENSE_CODE = A.ITEM_CODE ) > ELSE > NULL > END AP_DR_ACCOUNT_CODE > FROM VW_OVERHEAD_LEDGER A ,COMMODITIES B > WHERE PKG_CONTROLS.GET_REGN_NUMBER(A.COMPANY_CODE, 'GST') = '09AAACD1952B1Z4' > --AND NVL(GST_NUMBER, '$') LIKE NVL(:P_SUPP_GST_NUMBER,'%') > --AND ALLOW_GST_CREDIT LIKE NVL(:P_ALLOW_GST_CREDIT,'%') > AND (( 'E' ='E' AND A.LINK_REF_DATE BETWEEN '01/06/2022' AND '30/06/2022' ) OR ( 'E' ='B' AND A.AREF_DATE BETWEEN '01/06/2022' AND '30/06/2022' )) > AND A.COMMODITY_CODE = B.COMMODITY_CODE (+) > ) A > GROUP BY A.DATA_SOURCE, A.COMP_CODE, A.BILL_NO, A.INVOICE_DATE, A.SUPP_CODE, A.SUPP_BILL, A.SUPP_DATE, A.DOCUMENT_TYPE, A.DATA_TYPE, A.DATA_DESC, A.COMMODITY_TYPE, A.IGST_RATE, A.CGST_RATE, A.SGST_RATE, A.CESS_RATE, A.GST_R_FLAG, A.GST_CREDIT_FLAG, A.STATUS, DR_ACCOUNT_CODE, AP_DR_ACCOUNT_CODE, GRN_DR_ACCOUNT_CODE > ) > WHERE DR_ACCOUNT_CODE IS NULL > AND STATUS != 'Z' > --WHERE DOCUMENT_TYPE IN ('45', '44', 'W5') > --GROUP BY DOCUMENT_TYPE > ORDER BY COMMODITY_TYPE, INVOICE_DATE, BILL_NO, DATA_TYPE > >"REM WORKSPACETAB6",Query42,,149 >SELECT > /* > DATA_SOURCE > ,COMP_CODE > ,BILL_NO > ,INVOICE_DATE > ,SUPP_CODE > ,SUPP_BILL > ,SUPP_DATE > ,DOCUMENT_TYPE > ,NVL(CASE WHEN DATA_SOURCE IN ('SLR','CEV') THEN PKG_AR.GET_CUSTOMER_NAME(SUPP_CODE) ELSE PKG_AP.GET_SUPPLIER_NAME(SUPP_CODE) END,PKG_REPORTS.GET_COMPANY_NAME(SUPP_CODE)) SUPP_NAME > ,DATA_TYPE > ,DATA_DESC > ,COMMODITY_TYPE > ,COMMODITY_CODE > ,TAXABLE_AMT > ,IGST_RATE > ,CGST_RATE > ,SGST_RATE > ,CESS_RATE > ,IGST_AMOUNT > ,CGST_AMOUNT > ,SGST_AMOUNT > ,CESS_AMOUNT > ,CESS_ON_QTY > ,TOTAL_GST > ,BILL_AMNT > ,GST_R_FLAG > ,GST_CREDIT_FLAG > ,STATUS > */ > DOCUMENT_TYPE > , SUM(TAXABLE_AMT) > , SUM(IGST_AMOUNT) > , SUM(CGST_AMOUNT) > , SUM(SGST_AMOUNT) > , SUM(CESS_AMOUNT) > , SUM(CESS_ON_QTY) > , SUM(TOTAL_GST) > , SUM(BILL_AMNT) >FROM ( > SELECT > A.DATA_SOURCE DATA_SOURCE > ,A.DOCUMENT_DATE INVOICE_DATE > ,A.DOCUMENT_STRING BILL_NO > ,A.DOCUMENT_TYPE DOCUMENT_TYPE > ,A.AREF_NUMBER SUPP_BILL > ,A.AREF_DATE SUPP_DATE > ,A.COMPANY_CODE COMP_CODE > ,A.SUPPLIER_CODE SUPP_CODE > ,0 DATA_TYPE > ,'PB' DATA_DESC > ,CASE WHEN ALLOW_GST_CREDIT = 'Y' THEN > CASE WHEN GST_REVERSE_FLAG = 'N' THEN NVL(B.COMMODITY_TYPE, '$') ELSE DECODE (NVL(B.COMMODITY_TYPE,'#'), '001', '#1', '002', '#3') END > ELSE > DECODE (NVL(B.COMMODITY_TYPE,'#'), '001', '#2', '002', '#4') END > COMMODITY_TYPE > ,A.COMMODITY_CODE COMMODITY_CODE > ,SUM(A.TAXABLE_AMT) TAXABLE_AMT > ,A.IGST_RATE > ,A.CGST_RATE > ,A.SGST_RATE > ,A.CESS_RATE > ,SUM(A.IGST_AMOUNT) IGST_AMOUNT > ,SUM(A.CGST_AMOUNT) CGST_AMOUNT > ,SUM(A.SGST_AMOUNT) SGST_AMOUNT > ,SUM(A.CESS_AMOUNT) CESS_AMOUNT > ,SUM(A.CESS_ON_QTY) CESS_ON_QTY > ,SUM(NVL(IGST_AMOUNT, 0) + NVL(CGST_AMOUNT, 0) + NVL(SGST_AMOUNT, 0) + NVL(CESS_AMOUNT, 0) + NVL(CESS_ON_QTY, 0)) TOTAL_GST > -- Comment by ss on dt. 24/03/2018 > --,ROUND(SUM(A.BILL_AMOUNT)) BILL_AMNT > ,ROUND(SUM(A.BILL_AMOUNT)- SUM(NVL(OTHR_AMOUNT,0))- SUM(NVL(IGST_OTHERS,0))- SUM(NVL(CGST_OTHERS,0))- SUM(NVL(SGST_OTHERS,0))) BILL_AMNT > ,A.GST_REVERSE_FLAG GST_R_FLAG > ,A.ALLOW_GST_CREDIT GST_CREDIT_FLAG > ,A.STATUS > FROM VW_PURCHASE_LEDGER A, COMMODITIES B > WHERE PKG_CONTROLS.GET_REGN_NUMBER(A.COMPANY_CODE, 'GST') = '09AAACD1952B1Z4' > /* > -- Parameter add supplier gst number > -- By ss on dt. 10/08/2018 > */ > --AND NVL(GST_NUMBER, '$' ) LIKE NVL(:P_SUPP_GST_NUMBER,'%') > -- Add new parameter by ss on dt. 24/03/2018 > --AND ALLOW_GST_CREDIT LIKE NVL(:P_ALLOW_GST_CREDIT,'%') > AND (( 'E' ='E' AND A.DOCUMENT_DATE BETWEEN '01/06/2022' AND '30/06/2022' ) OR ( 'E' ='B' AND A.AREF_DATE BETWEEN '01/06/2022' AND '30/06/2022' )) > AND A.COMMODITY_CODE = B.COMMODITY_CODE (+) > GROUP BY A.DATA_SOURCE,A.DOCUMENT_DATE,A.DOCUMENT_STRING,A.DOCUMENT_TYPE,A.AREF_NUMBER,A.AREF_DATE, A.COMPANY_CODE,A.SUPPLIER_CODE,A. IGST_RATE, A.CGST_RATE, A.SGST_RATE, A.CESS_RATE,B.COMMODITY_TYPE, A.COMMODITY_CODE ,A.GST_REVERSE_FLAG ,A.ALLOW_GST_CREDIT ,A.STATUS > > UNION ALL > SELECT > A.DATA_SOURCE DATA_SOURCE > ,A.LINK_REF_DATE INVOICE_DATE > ,A.LINK_REF_STRING BILL_NO > ,A.LINK_REF_TYPE DOCUMENT_TYPE > ,A.AREF_NUMBER SUPP_BILL > ,A.AREF_DATE SUPP_DATE > ,A.COMPANY_CODE COMP_CODE > ,A.SUPPLIER_CODE SUPP_CODE > ,1 DATA_TYPE > ,'OH' DATA_DESC > ,CASE WHEN ALLOW_GST_CREDIT = 'Y' THEN > CASE WHEN GST_REVERSE_FLAG = 'N' THEN NVL(B.COMMODITY_TYPE, '$') ELSE DECODE (NVL(B.COMMODITY_TYPE,'#'), '001', '#1', '002', '#3') END > ELSE > DECODE (NVL(B.COMMODITY_TYPE,'#'), '001', '#2', '002', '#4') END > COMMODITY_TYPE > ,A.COMMODITY_CODE COMMODITY_CODE > ,SUM(A.TAXABLE_AMT) TAXABLE_AMT > ,A.IGST_RATE > ,A.CGST_RATE > ,A.SGST_RATE > ,NULL CESS_RATE > ,SUM(A.IGST_OTHERS) IGST_AMOUNT > ,SUM(A.CGST_OTHERS) CGST_AMOUNT > ,SUM(A.SGST_OTHERS) SGST_AMOUNT > ,NULL CESS_AMOUNT > ,NULL CESS_ON_QTY > ,SUM(NVL(IGST_OTHERS, 0) + NVL(CGST_OTHERS, 0) + NVL(SGST_OTHERS, 0)) TOTAL_GST > ,ROUND(SUM(NVL(IGST_OTHERS, 0) + NVL(CGST_OTHERS, 0) + NVL(SGST_OTHERS, 0) + NVL(TAXABLE_AMT,0))) BILL_AMNT > ,A.GST_REVERSE_FLAG GST_R_FLAG > ,A.ALLOW_GST_CREDIT GST_CREDIT_FLAG > ,A.STATUS STATUS > FROM VW_OVERHEAD_LEDGER A ,COMMODITIES B > WHERE PKG_CONTROLS.GET_REGN_NUMBER(A.COMPANY_CODE, 'GST') = '09AAACD1952B1Z4' > /* > -- Parameter add supplier gst number > -- By ss on dt. 10/08/2018 > */ > --AND NVL(GST_NUMBER, '$') LIKE NVL(:P_SUPP_GST_NUMBER,'%') > -- Add new parameter by ss on dt. 24/03/2018 > --AND ALLOW_GST_CREDIT LIKE NVL(:P_ALLOW_GST_CREDIT,'%') > /* > -- Bgn -- > -- Against call id - 8839 > --Communication gap by both user. So revert back all change. > -- Bgn -- > -- Against call id - 8839 > AND A.LINK_SPV_TYPE IS NOT NULL > -- End -- > -- End -- > */ > AND (( 'E' ='E' AND A.LINK_REF_DATE BETWEEN '01/06/2022' AND '30/06/2022' ) OR ( 'E' ='B' AND A.AREF_DATE BETWEEN '01/06/2022' AND '30/06/2022' )) > AND A.COMMODITY_CODE = B.COMMODITY_CODE (+) > GROUP BY A.DATA_SOURCE,A.LINK_REF_DATE,A.AREF_DATE, A.LINK_REF_STRING,A.LINK_REF_TYPE,A.AREF_NUMBER,A.AREF_DATE, A.COMPANY_CODE,A.SUPPLIER_CODE,A. IGST_RATE, A.CGST_RATE, A.SGST_RATE,B.COMMODITY_TYPE,A.COMMODITY_CODE >,A.GST_REVERSE_FLAG,A.ALLOW_GST_CREDIT,A.STATUS ) > > WHERE DOCUMENT_TYPE IN ('45', '44', 'W5') >GROUP BY DOCUMENT_TYPE >--ORDER BY COMMODITY_TYPE, INVOICE_DATE, BILL_NO, DATA_TYPE > >"REM WORKSPACETAB7",Query40,,1 >SELECT * FROM TT$INV_WISE_PURCHASE_DTL >"REM WORKSPACETAB8",Query38,,1 >SELECT * FROM TT$INV_WISE_PURCHASE_DTL >"REM WORKSPACETAB9",Query39,,1 >DESC TT$PURCHASE_GSTR_2A >"REM WORKSPACETAB10",Query34,,1 >desc xyz >"REM WORKSPACETAB11","ALTER SCRIPT",,35 >DROP TABLE TT$INV_WISE_PURCHASE_DTL; >CREATE GLOBAL TEMPORARY TABLE TT$INV_WISE_PURCHASE_DTL >( > COMMODITY_TYPE VARCHAR2(10), > COMMODITY_DESC VARCHAR2(100), > COMP_CODE VARCHAR2(3), > SHORT_NAME VARCHAR2(100), > DOCUMENT_STRING VARCHAR2(30), > DOCUMENT_DATE DATE, > SUPP_BILL_NUMBER VARCHAR2(30), > SUPP_BILL_DATE DATE, > SUPPLIER_CODE VARCHAR2(10), > SUPPLIER_NAME VARCHAR2(100), > SUPPLIER_GST_NUMBER VARCHAR2(50), > DATA_TYPE NUMBER(1), > DATA_DESC VARCHAR2(2), > ACCOUNT_CODE VARCHAR2(5), > ACCOUNT_NAME VARCHAR2(255), > SUB_ACCOUNT_CODE VARCHAR2(4), > SUB_ACCOUNT_NAME VARCHAR2(255), > TAXABLE_AMOUNT NUMBER, > IGST_RATE NUMBER, > IGST_AMOUNT NUMBER, > CGST_RATE NUMBER, > CGST_AMOUNT NUMBER, > SGST_RATE NUMBER, > SGST_AMOUNT NUMBER, > CESS_RATE NUMBER, > CESS_AMOUNT NUMBER, > CESS_ON_QTY NUMBER, > TOTAL_GST NUMBER, > BILL_AMOUNT NUMBER, > STATUS VARCHAR2(1) >) >ON COMMIT PRESERVE ROWS; >"REM WORKSPACETAB12",Query36,,1 >DESC ALL_SUPPLIERS >"REM WORKSPACETAB13",Query31,,1 >SELECT * FROM VW_SUPPLIERS >"REM WORKSPACETAB14",Query27,,3 >SELECT * FROM ALL_TAB_COLUMNS >WHERE COLUMN_NAME LIKE '%IGST%' >AND OWNER = 'IMISMAIN' >"REM WORKSPACETAB15",Query37,,1 >DESC TT$PURCHASE_GSTR_2A >"REM WORKSPACETAB16",Query33,,1 >select * from TT$RELATED_PARTIES_DETAIL >"REM WORKSPACETAB17",Query29,,1 >DESC TT$GST_RETURNS_DATA >"REM WORKSPACETAB18",Query32,,0 >"REM WORKSPACETAB19",Query28,,1 >DESC TT$SALE_BILL_ITEMS >"REM WORKSPACETAB20","FINAL REPORT QUERY",,237 >SELECT > A.DATA_SOURCE > , A.COMP_CODE > , A.BILL_NO > , A.INVOICE_DATE > , A.SUPP_CODE > , A.SUPP_BILL > , A.SUPP_DATE > , A.DOCUMENT_TYPE > , NVL(CASE WHEN DATA_SOURCE IN ('SLR','CEV') THEN PKG_AR.GET_CUSTOMER_NAME(SUPP_CODE) ELSE PKG_AP.GET_SUPPLIER_NAME(SUPP_CODE) END,PKG_REPORTS.GET_COMPANY_NAME(SUPP_CODE)) SUPP_NAME > , A.DATA_TYPE > , A.DATA_DESC > , A.COMMODITY_TYPE > , SUM(A.TAXABLE_AMT) TAXABLE_AMT > , A.IGST_RATE > , A.CGST_RATE > , A.SGST_RATE > , A.CESS_RATE > , SUM(A.IGST_AMOUNT) IGST_AMOUNT > , SUM(A.CGST_AMOUNT) CGST_AMOUNT > , SUM(A.SGST_AMOUNT) SGST_AMOUNT > , SUM(A.CESS_AMOUNT) CESS_AMOUNT > , SUM(A.CESS_ON_QTY) CESS_ON_QTY > , SUM(A.TOTAL_GST ) TOTAL_GST > , SUM(A.BILL_AMNT ) BILL_AMNT > , A.GST_R_FLAG > , A.GST_CREDIT_FLAG > , A.STATUS > , NVL( > CASE > WHEN A.DOCUMENT_TYPE IN ('W7', 'DC', 'W5', '49') THEN DR_ACCOUNT_CODE > WHEN A.DOCUMENT_TYPE IN ('AP' ) THEN AP_DR_ACCOUNT_CODE > ELSE > NULL > END > , GRN_DR_ACCOUNT_CODE > ) DR_ACCOUNT_CODE >FROM ( > SELECT > A.DATA_SOURCE DATA_SOURCE > , A.DOCUMENT_DATE INVOICE_DATE > , A.DOCUMENT_STRING BILL_NO > , A.DOCUMENT_TYPE DOCUMENT_TYPE > , A.AREF_NUMBER SUPP_BILL > , A.AREF_DATE SUPP_DATE > , A.COMPANY_CODE COMP_CODE > , A.SUPPLIER_CODE SUPP_CODE > , 0 DATA_TYPE > , 'PB' DATA_DESC > , CASE > WHEN ALLOW_GST_CREDIT = 'Y' THEN > CASE > WHEN GST_REVERSE_FLAG = 'N' THEN NVL(B.COMMODITY_TYPE, '$') > ELSE > DECODE (NVL(B.COMMODITY_TYPE,'#'), '001', '#1', '002', '#3') > END > ELSE > DECODE (NVL(B.COMMODITY_TYPE,'#'), '001', '#2', '002', '#4') > END COMMODITY_TYPE > , A.TAXABLE_AMT TAXABLE_AMT > , A.IGST_RATE > , A.CGST_RATE > , A.SGST_RATE > , A.CESS_RATE > , A.IGST_AMOUNT IGST_AMOUNT > , A.CGST_AMOUNT CGST_AMOUNT > , A.SGST_AMOUNT SGST_AMOUNT > , A.CESS_AMOUNT CESS_AMOUNT > , A.CESS_ON_QTY CESS_ON_QTY > , NVL(IGST_AMOUNT, 0) > + NVL(CGST_AMOUNT, 0) > + NVL(SGST_AMOUNT, 0) > + NVL(CESS_AMOUNT, 0) > + NVL(CESS_ON_QTY, 0) TOTAL_GST > ,( > A.BILL_AMOUNT > - NVL(OTHR_AMOUNT,0) > - NVL(IGST_OTHERS,0) > - NVL(CGST_OTHERS,0) > - NVL(SGST_OTHERS,0) > ) BILL_AMNT > , A.GST_REVERSE_FLAG GST_R_FLAG > , A.ALLOW_GST_CREDIT GST_CREDIT_FLAG > , A.STATUS > , ( > SELECT COMPANY_CODE > FROM VW_COMPANY_DETAIL > WHERE SUPPLIER_CODE = A.SUPPLIER_CODE > ) REF_COMPANY > , ( > SELECT ACNT_MODEL > FROM ITEM_CLASSES > WHERE ITEM_CLASS = A.ITEM_CLASS > AND COMPANY_CODE = A.COMPANY_CODE > ) ACNT_MODEL > , ( > SELECT STORE_TYPE > FROM VW_STORES > WHERE COMPANY_CODE = A.COMPANY_CODE > AND STORE_CODE = A.STORE_CODE > ) STORE_TYPE > , > CASE > WHEN A.DOCUMENT_TYPE IN ('W7', 'DC') AND A.STATUS != 'Z' THEN PKG_CONTROLS.GET_CONTROL_ACCOUNT (( > SELECT COMPANY_CODE > FROM VW_COMPANY_DETAIL > WHERE SUPPLIER_CODE = A.SUPPLIER_CODE > ), 'FG_TRANSFER_DR' ) > WHEN A.DOCUMENT_TYPE IN ('W5', '49') AND A.STATUS != 'Z' THEN PKG_CONTROLS.GET_CONTROL_ACCOUNT (( > SELECT COMPANY_CODE > FROM VW_COMPANY_DETAIL > WHERE SUPPLIER_CODE = A.SUPPLIER_CODE > ), 'FG_RECEIVED_DR' ) > ELSE > NULL > END DR_ACCOUNT_CODE > , CASE > WHEN A.DOCUMENT_TYPE IN ('44') AND A.STATUS != 'Z' AND NVL((SELECT STORE_TYPE FROM VW_STORES WHERE COMPANY_CODE = A.COMPANY_CODE AND STORE_CODE = A.STORE_CODE ), 'I') IN ('I', 'E') AND (SELECT ACNT_MODEL FROM ITEM_CLASSES WHERE ITEM_CLASS = A.ITEM_CLASS AND COMPANY_CODE = A.COMPANY_CODE) = 'STKS' THEN > PKG_IC.GET_POSTING_ACCOUNT(A.COMPANY_CODE, 'STKS', A.STORE_CODE, A.ITEM_CODE) > ELSE > PKG_IC.GET_POSTING_ACCOUNT(A.COMPANY_CODE, 'MEMO', A.STORE_CODE, A.ITEM_CODE) > END GRN_DR_ACCOUNT_CODE > , CASE > WHEN A.DOCUMENT_TYPE IN ('AP') AND A.STATUS != 'Z' THEN > ( SELECT GL_CODE||GL_SUB_CODE FROM EXPENSE_CODES WHERE COMPANY = A.COMPANY_CODE AND EXPENSE_CODE = A.ITEM_CODE ) > ELSE > NULL > END AP_DR_ACCOUNT_CODE > FROM VW_PURCHASE_LEDGER A, COMMODITIES B > WHERE PKG_CONTROLS.GET_REGN_NUMBER(A.COMPANY_CODE, 'GST') = :P_GST_NUMBER > AND NVL(GST_NUMBER, '$' ) LIKE NVL(:P_SUPP_GST_NUMBER,'%') > AND ALLOW_GST_CREDIT LIKE NVL(:P_ALLOW_GST_CREDIT,'%') > AND (( :P_RCPT_BILL_DATE ='E' AND A.DOCUMENT_DATE BETWEEN :P_QRY_BGN_DATE AND :P_QRY_END_DATE ) OR ( :P_RCPT_BILL_DATE ='B' AND A.AREF_DATE BETWEEN :P_QRY_BGN_DATE AND :P_QRY_END_DATE )) > AND A.COMMODITY_CODE = B.COMMODITY_CODE (+) > > UNION ALL > > SELECT > A.DATA_SOURCE DATA_SOURCE > , A.LINK_REF_DATE INVOICE_DATE > , A.LINK_REF_STRING BILL_NO > , A.LINK_REF_TYPE DOCUMENT_TYPE > , A.AREF_NUMBER SUPP_BILL > , A.AREF_DATE SUPP_DATE > , A.COMPANY_CODE COMP_CODE > , A.SUPPLIER_CODE SUPP_CODE > , 1 DATA_TYPE > , 'OH' DATA_DESC > , CASE > WHEN ALLOW_GST_CREDIT = 'Y' THEN > CASE > WHEN GST_REVERSE_FLAG = 'N' THEN > NVL(B.COMMODITY_TYPE, '$') > ELSE > DECODE (NVL(B.COMMODITY_TYPE,'#'), '001', '#1', '002', '#3') > END > ELSE > DECODE (NVL(B.COMMODITY_TYPE,'#'), '001', '#2', '002', '#4') > END COMMODITY_TYPE > , A.TAXABLE_AMT TAXABLE_AMT > , A.IGST_RATE > , A.CGST_RATE > , A.SGST_RATE > , NULL CESS_RATE > , A.IGST_OTHERS IGST_AMOUNT > , A.CGST_OTHERS CGST_AMOUNT > , A.SGST_OTHERS SGST_AMOUNT > , NULL CESS_AMOUNT > , NULL CESS_ON_QTY > ,( > NVL(IGST_OTHERS, 0) > + NVL(CGST_OTHERS, 0) > + NVL(SGST_OTHERS, 0) > ) TOTAL_GST > ,( > NVL(IGST_OTHERS, 0) > + NVL(CGST_OTHERS, 0) > + NVL(SGST_OTHERS, 0) > + NVL(TAXABLE_AMT, 0) > ) BILL_AMNT > , A.GST_REVERSE_FLAG GST_R_FLAG > , A.ALLOW_GST_CREDIT GST_CREDIT_FLAG > , A.STATUS STATUS > , ( > SELECT COMPANY_CODE > FROM VW_COMPANY_DETAIL > WHERE SUPPLIER_CODE = A.SUPPLIER_CODE > ) REF_COMPANY > , ( > SELECT ACNT_MODEL > FROM ITEM_CLASSES > WHERE ITEM_CLASS = PKG_IC.GET_ITEM_CLASS(A.ITEM_CODE) > AND COMPANY_CODE = A.COMPANY_CODE > ) ACNT_MODEL > , ( > SELECT STORE_TYPE > FROM VW_STORES > WHERE COMPANY_CODE = A.COMPANY_CODE > AND STORE_CODE = A.STORE_CODE > ) STORE_TYPE > , > CASE > WHEN A.VOUCHER_TYPE IN ('W7', 'DC') AND A.STATUS != 'Z' THEN PKG_CONTROLS.GET_CONTROL_ACCOUNT (( > SELECT COMPANY_CODE > FROM VW_COMPANY_DETAIL > WHERE SUPPLIER_CODE = A.SUPPLIER_CODE > ), 'FG_TRANSFER_DR' ) > WHEN A.VOUCHER_TYPE IN ('W5', '49') AND A.STATUS != 'Z' THEN PKG_CONTROLS.GET_CONTROL_ACCOUNT (( > SELECT COMPANY_CODE > FROM VW_COMPANY_DETAIL > WHERE SUPPLIER_CODE = A.SUPPLIER_CODE > ), 'FG_RECEIVED_DR' ) > ELSE > NULL > END DR_ACCOUNT_CODE > , CASE > WHEN A.VOUCHER_TYPE IN ('44') AND A.STATUS != 'Z' AND NVL((SELECT STORE_TYPE FROM VW_STORES WHERE COMPANY_CODE = A.COMPANY_CODE AND STORE_CODE = A.STORE_CODE ), 'I') IN ('I', 'E') AND (SELECT ACNT_MODEL FROM ITEM_CLASSES WHERE ITEM_CLASS = PKG_IC.GET_ITEM_CLASS(A.ITEM_CODE) AND COMPANY_CODE = A.COMPANY_CODE) = 'STKS' THEN > PKG_IC.GET_POSTING_ACCOUNT(A.COMPANY_CODE, 'STKS', A.STORE_CODE, A.ITEM_CODE) > ELSE > PKG_IC.GET_POSTING_ACCOUNT (A.COMPANY_CODE, 'MEMO', A.STORE_CODE, A.ITEM_CODE) > END GRN_DR_ACCOUNT_CODE > , CASE > WHEN A.VOUCHER_TYPE IN ('AP') AND A.STATUS != 'Z' THEN > ( SELECT GL_CODE||GL_SUB_CODE FROM EXPENSE_CODES WHERE COMPANY = A.COMPANY_CODE AND EXPENSE_CODE = A.ITEM_CODE ) > ELSE > NULL > END AP_DR_ACCOUNT_CODE > FROM VW_OVERHEAD_LEDGER A ,COMMODITIES B > WHERE PKG_CONTROLS.GET_REGN_NUMBER(A.COMPANY_CODE, 'GST') = :P_GST_NUMBER > AND NVL(GST_NUMBER, '$') LIKE NVL(:P_SUPP_GST_NUMBER,'%') > AND ALLOW_GST_CREDIT LIKE NVL(:P_ALLOW_GST_CREDIT,'%') > AND (( :P_RCPT_BILL_DATE ='E' AND A.LINK_REF_DATE BETWEEN :P_QRY_BGN_DATE AND :P_QRY_END_DATE ) OR ( :P_RCPT_BILL_DATE ='B' AND A.AREF_DATE BETWEEN :P_QRY_BGN_DATE AND :P_QRY_END_DATE )) > AND A.COMMODITY_CODE = B.COMMODITY_CODE (+) > ) A >GROUP BY A.DATA_SOURCE, A.COMP_CODE, A.BILL_NO, A.INVOICE_DATE, A.SUPP_CODE, A.SUPP_BILL, A.SUPP_DATE, A.DOCUMENT_TYPE, A.DATA_TYPE, A.DATA_DESC, A.COMMODITY_TYPE, A.IGST_RATE, A.CGST_RATE, A.SGST_RATE, A.CESS_RATE, A.GST_R_FLAG, A.GST_CREDIT_FLAG, A.STATUS, DR_ACCOUNT_CODE, AP_DR_ACCOUNT_CODE, GRN_DR_ACCOUNT_CODE >ORDER BY COMMODITY_TYPE, INVOICE_DATE, BILL_NO, DATA_TYPE > >"REM WORKSPACETAB21",Query27,,3 >SELECT * FROM ITEMS >WHERE ITEM_CODE = 'GN-OTH-SWL' >ORDER BY COMPANY >"REM WORKSPACETAB22",Query29,,135 >SELECT DATA_SOURCE > ,COMP_CODE > ,BILL_NO > ,INVOICE_DATE > ,SUPP_CODE > ,SUPP_BILL > ,SUPP_DATE > ,DOCUMENT_TYPE > ,NVL(CASE WHEN DATA_SOURCE IN ('SLR','CEV') THEN PKG_AR.GET_CUSTOMER_NAME(SUPP_CODE) ELSE PKG_AP.GET_SUPPLIER_NAME(SUPP_CODE) END,PKG_REPORTS.GET_COMPANY_NAME(SUPP_CODE)) SUPP_NAME > ,DATA_TYPE > ,DATA_DESC > ,COMMODITY_TYPE > ,COMMODITY_CODE > ,TAXABLE_AMT > ,IGST_RATE > ,CGST_RATE > ,SGST_RATE > ,CESS_RATE > ,IGST_AMOUNT > ,CGST_AMOUNT > ,SGST_AMOUNT > ,CESS_AMOUNT > ,CESS_ON_QTY > ,TOTAL_GST > ,BILL_AMNT > ,GST_R_FLAG > ,GST_CREDIT_FLAG > ,STATUS >FROM ( > SELECT > A.DATA_SOURCE DATA_SOURCE > ,A.DOCUMENT_DATE INVOICE_DATE > ,A.DOCUMENT_STRING BILL_NO > ,A.DOCUMENT_TYPE DOCUMENT_TYPE > ,A.AREF_NUMBER SUPP_BILL > ,A.AREF_DATE SUPP_DATE > ,A.COMPANY_CODE COMP_CODE > ,A.SUPPLIER_CODE SUPP_CODE > ,0 DATA_TYPE > ,'PB' DATA_DESC > ,CASE WHEN ALLOW_GST_CREDIT = 'Y' THEN > CASE WHEN GST_REVERSE_FLAG = 'N' THEN NVL(B.COMMODITY_TYPE, '$') ELSE DECODE (NVL(B.COMMODITY_TYPE,'#'), '001', '#1', '002', '#3') END > ELSE > DECODE (NVL(B.COMMODITY_TYPE,'#'), '001', '#2', '002', '#4') END > COMMODITY_TYPE > ,A.COMMODITY_CODE COMMODITY_CODE > ,SUM(A.TAXABLE_AMT) TAXABLE_AMT > ,A.IGST_RATE > ,A.CGST_RATE > ,A.SGST_RATE > ,A.CESS_RATE > ,SUM(A.IGST_AMOUNT) IGST_AMOUNT > ,SUM(A.CGST_AMOUNT) CGST_AMOUNT > ,SUM(A.SGST_AMOUNT) SGST_AMOUNT > ,SUM(A.CESS_AMOUNT) CESS_AMOUNT > ,SUM(A.CESS_ON_QTY) CESS_ON_QTY > ,SUM(NVL(IGST_AMOUNT, 0) + NVL(CGST_AMOUNT, 0) + NVL(SGST_AMOUNT, 0) + NVL(CESS_AMOUNT, 0) + NVL(CESS_ON_QTY, 0)) TOTAL_GST > -- Comment by ss on dt. 24/03/2018 > --,ROUND(SUM(A.BILL_AMOUNT)) BILL_AMNT > ,ROUND(SUM(A.BILL_AMOUNT)- SUM(NVL(OTHR_AMOUNT,0))- SUM(NVL(IGST_OTHERS,0))- SUM(NVL(CGST_OTHERS,0))- SUM(NVL(SGST_OTHERS,0))) BILL_AMNT > ,A.GST_REVERSE_FLAG GST_R_FLAG > ,A.ALLOW_GST_CREDIT GST_CREDIT_FLAG > ,A.STATUS > FROM VW_PURCHASE_LEDGER A, COMMODITIES B > WHERE PKG_CONTROLS.GET_REGN_NUMBER(A.COMPANY_CODE, 'GST') = :P_GST_NUMBER > /* > -- Parameter add supplier gst number > -- By ss on dt. 10/08/2018 > */ > AND NVL(GST_NUMBER, '$' ) LIKE NVL(:P_SUPP_GST_NUMBER,'%') > -- Add new parameter by ss on dt. 24/03/2018 > AND ALLOW_GST_CREDIT LIKE NVL(:P_ALLOW_GST_CREDIT,'%') > AND (( :P_RCPT_BILL_DATE ='E' AND A.DOCUMENT_DATE BETWEEN :P_QRY_BGN_DATE AND :P_QRY_END_DATE ) OR ( :P_RCPT_BILL_DATE ='B' AND A.AREF_DATE BETWEEN :P_QRY_BGN_DATE AND :P_QRY_END_DATE )) > AND A.COMMODITY_CODE = B.COMMODITY_CODE (+) > GROUP BY A.DATA_SOURCE,A.DOCUMENT_DATE,A.DOCUMENT_STRING,A.DOCUMENT_TYPE,A.AREF_NUMBER,A.AREF_DATE, A.COMPANY_CODE,A.SUPPLIER_CODE,A. IGST_RATE, A.CGST_RATE, A.SGST_RATE, A.CESS_RATE,B.COMMODITY_TYPE, A.COMMODITY_CODE ,A.GST_REVERSE_FLAG ,A.ALLOW_GST_CREDIT ,A.STATUS > > UNION ALL > SELECT > A.DATA_SOURCE DATA_SOURCE > ,A.LINK_REF_DATE INVOICE_DATE > ,A.LINK_REF_STRING BILL_NO > ,A.LINK_REF_TYPE DOCUMENT_TYPE > ,A.AREF_NUMBER SUPP_BILL > ,A.AREF_DATE SUPP_DATE > ,A.COMPANY_CODE COMP_CODE > ,A.SUPPLIER_CODE SUPP_CODE > ,1 DATA_TYPE > ,'OH' DATA_DESC > ,CASE WHEN ALLOW_GST_CREDIT = 'Y' THEN > CASE WHEN GST_REVERSE_FLAG = 'N' THEN NVL(B.COMMODITY_TYPE, '$') ELSE DECODE (NVL(B.COMMODITY_TYPE,'#'), '001', '#1', '002', '#3') END > ELSE > DECODE (NVL(B.COMMODITY_TYPE,'#'), '001', '#2', '002', '#4') END > COMMODITY_TYPE > ,A.COMMODITY_CODE COMMODITY_CODE > ,SUM(A.TAXABLE_AMT) TAXABLE_AMT > ,A.IGST_RATE > ,A.CGST_RATE > ,A.SGST_RATE > ,NULL CESS_RATE > ,SUM(A.IGST_OTHERS) IGST_AMOUNT > ,SUM(A.CGST_OTHERS) CGST_AMOUNT > ,SUM(A.SGST_OTHERS) SGST_AMOUNT > ,NULL CESS_AMOUNT > ,NULL CESS_ON_QTY > ,SUM(NVL(IGST_OTHERS, 0) + NVL(CGST_OTHERS, 0) + NVL(SGST_OTHERS, 0)) TOTAL_GST > ,ROUND(SUM(NVL(IGST_OTHERS, 0) + NVL(CGST_OTHERS, 0) + NVL(SGST_OTHERS, 0) + NVL(TAXABLE_AMT,0))) BILL_AMNT > ,A.GST_REVERSE_FLAG GST_R_FLAG > ,A.ALLOW_GST_CREDIT GST_CREDIT_FLAG > ,A.STATUS STATUS > FROM VW_OVERHEAD_LEDGER A ,COMMODITIES B > WHERE PKG_CONTROLS.GET_REGN_NUMBER(A.COMPANY_CODE, 'GST') = :P_GST_NUMBER > /* > -- Parameter add supplier gst number > -- By ss on dt. 10/08/2018 > */ > AND NVL(GST_NUMBER, '$') LIKE NVL(:P_SUPP_GST_NUMBER,'%') > -- Add new parameter by ss on dt. 24/03/2018 > AND ALLOW_GST_CREDIT LIKE NVL(:P_ALLOW_GST_CREDIT,'%') > /* > -- Bgn -- > -- Against call id - 8839 > --Communication gap by both user. So revert back all change. > -- Bgn -- > -- Against call id - 8839 > AND A.LINK_SPV_TYPE IS NOT NULL > -- End -- > -- End -- > */ > AND (( :P_RCPT_BILL_DATE ='E' AND A.LINK_REF_DATE BETWEEN :P_QRY_BGN_DATE AND :P_QRY_END_DATE ) OR ( :P_RCPT_BILL_DATE ='B' AND A.AREF_DATE BETWEEN :P_QRY_BGN_DATE AND :P_QRY_END_DATE )) > AND A.COMMODITY_CODE = B.COMMODITY_CODE (+) > GROUP BY A.DATA_SOURCE,A.LINK_REF_DATE,A.AREF_DATE, A.LINK_REF_STRING,A.LINK_REF_TYPE,A.AREF_NUMBER,A.AREF_DATE, A.COMPANY_CODE,A.SUPPLIER_CODE,A. IGST_RATE, A.CGST_RATE, A.SGST_RATE,B.COMMODITY_TYPE,A.COMMODITY_CODE >,A.GST_REVERSE_FLAG,A.ALLOW_GST_CREDIT,A.STATUS ) > >ORDER BY COMMODITY_TYPE, INVOICE_DATE, BILL_NO, DATA_TYPE > >"REM WORKSPACETAB23",Query28,,2 >SELECT * FROM USER_IDS >WHERE USER_ID = 'E20661' >"REM WORKSPACETAB24",Query16,,4 >GRN >FGR >FGP >APV >"REM WORKSPACETAB25",Query20,,1 >DESC SERVICE_RECEIVED_NOTES >"REM WORKSPACETAB26",Query9,,6 >SELECT *--DISTINCT DATA_SOURCE, DOCUMENT_TYPE >FROM VW_PURCHASE_LEDGER A >WHERE PKG_CONTROLS.GET_REGN_NUMBER(A.COMPANY_CODE, 'GST') = '07AAACD1952B1Z8' >AND A.DOCUMENT_DATE BETWEEN '01/04/2022' AND '30/04/2022' >AND A.DATA_SOURCE IN ('APV') >order by DOCUMENT_NUMBER >"REM WORKSPACETAB27",Query18,,5 >SELECT *--DISTINCT DATA_SOURCE, DOCUMENT_TYPE >FROM VW_OVERHEAD_LEDGER A >WHERE PKG_CONTROLS.GET_REGN_NUMBER(A.COMPANY_CODE, 'GST') = '07AAACD1952B1Z8' >AND A.VOUCHER_DATE BETWEEN '01/04/2022' AND '30/04/2022' >AND A.DATA_SOURCE IN ('GRN') >"REM WORKSPACETAB28",Query19,,3 >SELECT * FROM VW_STORES >WHERE COMPANY_CODE = '01' >AND STORE_CODE = '022' >"REM WORKSPACETAB29",Query17,,3 >SELECT * FROM ITEM_CLASSES >WHERE ITEM_CLASS = 0 >AND COMPANY_CODE = '01' >"REM WORKSPACETAB30","MISC AP (AP)",,8 >SELECT * FROM GL_POSTING_DETAILS >WHERE COMPANY = '01' >AND VOUCHER_TYPE = 'AP' >AND VOUCHER_SERIES = '2204' >AND VOUCHER_NUMBER = 1 >AND AMOUNT < 0 > >-- AP/2204/000001 >"REM WORKSPACETAB31",Query24,,5 >select * from ACCOUNTS_PAYABLE_VOUCHERS >WHERE COMPANY = '01' >AND VOUCHER_TYPE = 'AP' >AND VOUCHER_SERIES = '2204' >AND VOUCHER_NUMBER = 000005 >"REM WORKSPACETAB32",Query25,,5 >select * from AP_VOUCHER_MISC_DETAILS >WHERE COMPANY = '01' >AND VOUCHER_TYPE = 'AP' >AND VOUCHER_SERIES = '2204' >AND VOUCHER_NUMBER = 000005 >"REM WORKSPACETAB33",Query23,,5 > select GL_CODE,GL_SUB_CODE > > from EXPENSE_CODES > where company = '01' > and expense_code = 'INSURNCE-MOTOR_CAR'; >"REM WORKSPACETAB34",Query22,,6 > select account_code, sub_account_code > from ap_controls a, suppliers s > where a.company = '01' > and s.company = a.company > and s.type_of_supplier = a.type_of_supplier > and s.supplier_code = '3006326' >"REM WORKSPACETAB35","FG STOCK RECEIPT FROM TRANSFER (W5)",,8 >SELECT * FROM GL_POSTING_DETAILS >WHERE COMPANY = '01' >AND VOUCHER_TYPE = 'W5' >AND VOUCHER_SERIES = '2022' >AND VOUCHER_NUMBER = 85 >ORDER BY GL_POSTING_SEQ_NUMBER > >-- W5/2022/000085 >"REM WORKSPACETAB36",Query8,,1 >SELECT PKG_GL.GET_ACCOUNT_NAME('10597'), PKG_GL.GET_ACCOUNT_NAME('105970003') FROM DUAL >"REM WORKSPACETAB37",Query7,,9 >SELECT /*TRANSACTION_ITEM_CODE, TRANSACTION_STOCK_TYPE,*/ SUM(TRANSACTION_ITEM_QTY), SUM(TRANSACTION_ITEM_VALUE) >FROM WAREHOUSE_TRANSACTION_ITEMS >WHERE COMPANY = '01' >AND TRANSACTION_TYPE = 'W5' >AND TRANSACTION_SERIES = '2022' >AND TRANSACTION_NO = 000001 >--AND AMOUNT < 0 >--ORDER BY GL_POSTING_SEQ_NUMBER >--GROUP BY TRANSACTION_ITEM_CODE, TRANSACTION_STOCK_TYPE >"REM WORKSPACETAB38","FG STOCK RECEIPT AGAINST FG GRN (49)",,10 >SELECT * FROM GL_POSTING_DETAILS >WHERE COMPANY = '04' >AND VOUCHER_TYPE = '49' >AND VOUCHER_SERIES = '2204' >AND VOUCHER_NUMBER = 3 >AND AMOUNT < 0 >AND UPPER(NARRATION) LIKE '%PURCHASE OF GOODS%' >ORDER BY GL_POSTING_SEQ_NUMBER > >--449/2204/000003 >"REM WORKSPACETAB39",Query11,,9 >SELECT * FROM FG_RECEIVED_NOTES >WHERE COMPANY_CODE = '01' >AND DOCUMENT_TYPE = '49' >AND DOCUMENT_SERIES = '2204' >AND DOCUMENT_NUMBER = 2 >--AND AMOUNT < 0 >--AND UPPER(NARRATION) LIKE '%PURCHASE OF GOODS%' >--ORDER BY GL_POSTING_SEQ_NUMBER >-- >"REM WORKSPACETAB40",Query15,,4 >SELECT *--COMPANY_CODE >FROM VW_COMPANY_DETAIL >WHERE SUPPLIER_CODE = '3001297' > >"REM WORKSPACETAB41","FG STOVK RECEIPT AGAINT GRN (44)",,7 >SELECT * FROM GL_POSTING_DETAILS >WHERE COMPANY = '01' >AND VOUCHER_TYPE = '44' >AND VOUCHER_SERIES = 'W224' >AND VOUCHER_NUMBER = 000001 >AND AMOUNT < 0 >ORDER BY GL_POSTING_SEQ_NUMBER >"REM WORKSPACETAB42",Query16,,6 >SELECT * FROM GOODS_RECEIVED_NOTES >WHERE COMPANY = '01' >AND GRN_TYPE = '44' >AND GRN_SERIES = 'G224' >AND GRN_NO = 1 > >"REM WORKSPACETAB43",Query21,,5 >SELECT * FROM GOODS_RECEIVED_NOTE_ITEMS >WHERE COMPANY = '01' >AND GRN_TYPE = '44' >AND GRN_SERIES = 'G224' >AND GRN_NO = 1
You cannot view the attachment while viewing its details because your browser does not support IFRAMEs.
View the attachment on a separate page
.
View Attachment As Raw
Actions:
View
Attachments on
bug 9260
:
2935
|
2983
| 2986 |
2987
|
2992
|
2994