cancel
Showing results for 
Search instead for 
Did you mean: 

BP Related Related Journal Entries.

0 Kudos

Dear Experts,

i have a following Scenario,

I am Preparing MIS report , For over all Expenses For the Clint's . My report Is based On journal Entry , when i was picked Journal Entry , the all Vendor is credited we need all the Debit .

When a Particular BP( Vendor) is Involved In Credit , we Need All The Debit For Expenses.

case 1: BP ( All Vendor ) => Credit

expense => Debit

Case 2 : BP ( Particular Vendor ) => Credit

Expenses => Debit

Above two cases, we need the Only we need the Debit For Different case Conditions , How i do proceed segregate two type of expenses. please me

SELECT year(dateadd(month, -3, T0.RefDate)) as [Years],DATENAME(month, T0.RefDate) as [Months] , CAST(MONTH(T0.RefDate) AS VARCHAR(MAX)) AS[Monthnum], t1.ShortName as 'Account code',t1.transId,t0.RefDate, case when t2.AcctName in ('Entertainment - domestic','Entertainment - international','Entertainment - Staff T&E','Property Rates','Professional Fees','Legal Fees','Audit Fees','Accountancy Fees','Consultancy Fees','Consultancy Charges', 'Consultancy Charges - Others','Donation - Contract','Gift Articles - Contract','Miscellaneous Expenses - Contract','Pooja Expenses - Contract','Customer Entertainment Charges','Legal & Professional Charges - Contract','Commission & Brokerage', 'Rates Duties and Taxes','Labour Welfare Fund','Legal & Professional Charges - Contract','Rates And Taxes','Property Tax - Ambattur Office','Penalty Charges','Professional Fees','Registration Charges','Round Off','Seminar Expenses','Brokerage Charges', 'Rates Duties & Taxes','Professional Tax','Business Promotion Expenses','Fees & Licence & Taxes','Legal Expenses','Legal Expenses - Contract',' Legal & Professional Charges','Membership Fees','Pooja Expenses','Tender Fee','COURT STAMP PAPER FEES', 'Miscellaneous Expenses','Financial Expenses','Bank Interest CC','Bank Charges Paid','Credit Charges','Bad Debt Expense','Bank Charges - Contract') then 'MISC EXPENSES' when t2.AcctName in ('Maintenance & Repairs','Machinery & Equipment Maintenance','Repairs & Renewals','Gardening','Consumables & Spares','Consumables & Spares - Contract','Consumable & Spares Telangana','Consumable & Spares','Consumables & Spares AP', 'Labour Charges - Contract','Service Charges Technical & Consultant','Testing Charges A/C','Labour Charges - Others','Service Charges Tech & Conslt - Import','Repairs & Maintenance','Service Charges - Others','Repairs & Maintenance - Machinery') then 'PLANT MAINTENANCE' when t2.AcctName in ('Travel & Entertainment','Fares','Fares - Staff T&E','Charges For Ticket Booking','Travel Charges - Air Booking',' Travel Charges - Air Booking - Contract','Travel Charges - Bus Booking','Travel Charges - Contract','Travel Charges - Train Booking', 'Travel Charges - Train Booking - Contract','Leave Travel Concession') then 'TRAVEL EXPENSES' when t2.AcctName in ('Building Maintenance','Education & Training','Adminstration','Other Administrative Expenses','Audit Fees','General','Computer Maintenance','Internal Audit Fees','Office Maintenance') then 'OFFICE MAINTENANCE' when t2.AcctName in ('Utilities','Gas','Cooking Gas','Cooking Gas - Cauvery') then 'MESS' when t2.AcctName in ('Employers National Insurance Contributions','Employee Insurance Contributions','Insurance (WC / GPA)','Employers National Insurance Contributions',' Employers Liability Insurance','Private Healthcare Expenses','Employee Insurance Contributions','Employer Insurance Contributions','Insurance','Premises Insurance','Marine Insurance','Overseas Insurance', 'Insurance','Group Insurance','Building Insurance') then 'INSURANCE' when t2.AcctName in ('Water','Employee Bonuses','Safety Item Purchase - Contract',' Staffwelfare - Contract','Over Time Charges','Staff Welfare','Incentive') then 'STAFF WELFARE' when t2.AcctName in ('Telephone','Postage & Courier-Contract','Telephone Charges - Contract','Internet Charges','Telephone Charges','Postage & Courier') then 'COMMUNICATUION' when t2.AcctName in ('Property Rent','Electricity Charges - Cont','Rent - Contract','Electricity Charges','Advertising Costs') then 'ESTABLISHMENT' when t2.AcctName in ('Cleaning & Laundry','Cable Tv Rent - Contract','Guest House Maintenance','Cleaning Expenses','Books & Periodicals') then 'GUEST HOUSE MAINTENANCE' when t2.AcctName in ('Accommodation - domestic','Accommodation - international','Accommodation - Staff T&E','Stay Charges - Contract') then 'STAY CHARGES' when t2.AcctName in ('Travel Costs - Other','Motor Vehicle Expenses','Vehicle Hire','Vehicle Repairs & Servicing','Vehicle Fuel & Oil','Vehicle Miscellaneous Expenses','Equipment - Hire Charges','Fuel Charges - Contract','Local Conveyance - Contract','Vehicle Hire Charges','Vehicle Fuel Expenses','Vehicle Insurance',' Vehicle Maintenance','Local Conveyance') then 'LOCAL CONVEYANCE' when t2.AcctName in ('Salary & Benefits','Salaries & Wages - overtime','Redundancy Payments','Directors Salaries','Directors Remuneration','Salary') then 'STAFF SALARY' when t2.AcctName in ('Freight Charges','Material Handling Charges',' Transportation','Shipment Charges','Freight & Carriage','Inbound Freight & Carriage','Outbound Freight & Carriage','Freight Expense') then 'TRANSPORT EXPENSE' when t2.AcctName in ('Printing & Stationery','Printing & Stationary - Contract') then 'PRINTING & STATIONERY' else t2.AcctName end as 'Expense Name', (T1.DEBIT) AS [INVOICE AMOUNT] FROM OJDT T0 INNER JOIN JDT1 T1 ON T0.TransId=T1.TRANSID INNER JOIN OACT T2 ON T2.AcctCode=T1.Account WHERE t1.project ='CISL / OM092' and T1.DEBIT<>'0' and t1.ShortName <> '201000' and t1.account<>t1.ShortName and t0.TransType='30' GROUP BY year(dateadd(month, -3, T0.RefDate)),DATENAME(month, T0.RefDate),MONTH(T0.RefDate),t1.Account,t1.ShortName,t2.AcctName,t1.TransId,t0.RefDate,T1.DEBIT union all SELECT year(dateadd(month, -3, T0.RefDate)) as [Years],DATENAME(month, T0.RefDate) as [Months] , CAST(MONTH(T0.RefDate) AS VARCHAR(MAX)) AS[Monthnum], t1.ShortName as 'Account code',t1.transId,t0.RefDate, case when t2.AcctName in ('Entertainment - domestic','Entertainment - international','Entertainment - Staff T&E','Property Rates','Professional Fees','Legal Fees','Audit Fees','Accountancy Fees','Consultancy Fees','Consultancy Charges', 'Consultancy Charges - Others','Donation - Contract','Gift Articles - Contract','Miscellaneous Expenses - Contract','Pooja Expenses - Contract','Customer Entertainment Charges','Legal & Professional Charges - Contract','Commission & Brokerage', 'Rates Duties and Taxes','Labour Welfare Fund','Legal & Professional Charges - Contract','Rates And Taxes','Property Tax - Ambattur Office','Penalty Charges','Professional Fees','Registration Charges','Round Off','Seminar Expenses','Brokerage Charges', 'Rates Duties & Taxes','Professional Tax','Business Promotion Expenses','Fees & Licence & Taxes','Legal Expenses','Legal Expenses - Contract',' Legal & Professional Charges','Membership Fees','Pooja Expenses','Tender Fee','COURT STAMP PAPER FEES', 'Miscellaneous Expenses','Financial Expenses','Bank Interest CC','Bank Charges Paid','Credit Charges','Bad Debt Expense','Bank Charges - Contract') then 'MISC EXPENSES' when t2.AcctName in ('Maintenance & Repairs','Machinery & Equipment Maintenance','Repairs & Renewals','Gardening','Consumables & Spares','Consumables & Spares - Contract','Consumable & Spares Telangana','Consumable & Spares','Consumables & Spares AP', 'Labour Charges - Contract','Service Charges Technical & Consultant','Testing Charges A/C','Labour Charges - Others','Service Charges Tech & Conslt - Import','Repairs & Maintenance','Service Charges - Others','Repairs & Maintenance - Machinery') then 'PLANT MAINTENANCE' when t2.AcctName in ('Travel & Entertainment','Fares','Fares - Staff T&E','Charges For Ticket Booking','Travel Charges - Air Booking',' Travel Charges - Air Booking - Contract','Travel Charges - Bus Booking','Travel Charges - Contract','Travel Charges - Train Booking', 'Travel Charges - Train Booking - Contract','Leave Travel Concession') then 'TRAVEL EXPENSES' when t2.AcctName in ('Building Maintenance','Education & Training','Adminstration','Other Administrative Expenses','Audit Fees','General','Computer Maintenance','Internal Audit Fees','Office Maintenance') then 'OFFICE MAINTENANCE' when t2.AcctName in ('Utilities','Gas','Cooking Gas','Cooking Gas - Cauvery') then 'MESS' when t2.AcctName in ('Employers National Insurance Contributions','Employee Insurance Contributions','Insurance (WC / GPA)','Employers National Insurance Contributions',' Employers Liability Insurance','Private Healthcare Expenses','Employee Insurance Contributions','Employer Insurance Contributions','Insurance','Premises Insurance','Marine Insurance','Overseas Insurance', 'Insurance','Group Insurance','Building Insurance') then 'INSURANCE' when t2.AcctName in ('Water','Employee Bonuses','Safety Item Purchase - Contract',' Staffwelfare - Contract','Over Time Charges','Staff Welfare','Incentive') then 'STAFF WELFARE' when t2.AcctName in ('Telephone','Postage & Courier-Contract','Telephone Charges - Contract','Internet Charges','Telephone Charges','Postage & Courier') then 'COMMUNICATUION' when t2.AcctName in ('Property Rent','Electricity Charges - Cont','Rent - Contract','Electricity Charges','Advertising Costs') then 'ESTABLISHMENT' when t2.AcctName in ('Cleaning & Laundry','Cable Tv Rent - Contract','Guest House Maintenance','Cleaning Expenses','Books & Periodicals') then 'GUEST HOUSE MAINTENANCE' when t2.AcctName in ('Accommodation - domestic','Accommodation - international','Accommodation - Staff T&E','Stay Charges - Contract') then 'STAY CHARGES' when t2.AcctName in ('Travel Costs - Other','Motor Vehicle Expenses','Vehicle Hire','Vehicle Repairs & Servicing','Vehicle Fuel & Oil','Vehicle Miscellaneous Expenses','Equipment - Hire Charges','Fuel Charges - Contract','Local Conveyance - Contract','Vehicle Hire Charges','Vehicle Fuel Expenses','Vehicle Insurance',' Vehicle Maintenance','Local Conveyance') then 'LOCAL CONVEYANCE' when t2.AcctName in ('Salary & Benefits','Salaries & Wages - overtime','Redundancy Payments','Directors Salaries','Directors Remuneration','Salary') then 'STAFF SALARY' when t2.AcctName in ('Freight Charges','Material Handling Charges',' Transportation','Shipment Charges','Freight & Carriage','Inbound Freight & Carriage','Outbound Freight & Carriage','Freight Expense') then 'TRANSPORT EXPENSE' when t2.AcctName in ('Printing & Stationery','Printing & Stationary - Contract') then 'PRINTING & STATIONERY' else t2.AcctName end as 'Expense Name', (T1.DEBIT) AS [INVOICE AMOUNT] FROM OJDT T0 INNER JOIN JDT1 T1 ON T0.TransId=T1.TRANSID INNER JOIN OACT T2 ON T2.AcctCode=T1.Account WHERE t1.project ='CISL / OM092' and T1.DEBIT<>'0' and t1.ShortName <> '201000' and T1.ShortName='SI00004' and t0.TransType='18' GROUP BY year(dateadd(month, -3, T0.RefDate)),DATENAME(month, T0.RefDate),MONTH(T0.RefDate),t1.Account,t1.ShortName,t2.AcctName,t1.TransId,t0.RefDate,T1.DEBIT

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi balajibala003

Can you please elaborate it more or explain it more to understand.

Because i am still confuse to understand your requirement.

Regards,

Brij

0 Kudos

what i am saying is when i am posting manual journal entry following should be booked

case 1 :

credit => site imprest - cauvery ( BP Master )-5000

Debit => Travel Booking (Gl Account) -5000

case 2:

Credit => Alpa Enterprises (Bp Master or Any BP Except Site Imprest )-5000

Debit => Travel Booking (GL Account) -5000

i want get report based on Above two case conditions , separately i want report .

Above is my Query how i get report for different case condition

0 Kudos

when credit was Site Imprest -cauvery , i wan related debit Amount for separately.

when credit was Any other Except Site Imprest -cauvery, i want related Debit Amount separately.