Below is the query to get the GST applied for Invoices, Credit memos and Debit memos WITH Parameter AS (SELECT to_date(:P_Transaction_Start_Date,’YYYY/MM/DD HH24:MI:SS’) AS BV_Transaction_Start_Date , to_date(:P_Transaction_End_Date,’YYYY/MM/DD HH24:MI:SS’) AS BV_Transaction_End_Date, to_date(:P_GL_Start_Date,’YYYY/MM/DD…
Read MoreSELECT glcc.concatenated_segments segments, gjl.period_name, gjh.name journal_name, gjb.name batch_name, gjh.je_source journal_source, gjh.je_category journal_category, glcc.segment1 entity_segment, glcc.segment2 project_segment, ffv.attribute1 project_vertical_dff, glcc.segment3, glcc.segment4, glcc.segment5, glcc.segment6, glcc.segment7, TO_CHAR(gjh.doc_sequence_value) gl_doc_no, TO_CHAR(gjh.default_effective_date,’DD-MON-YYYY’) gl_date, TO_CHAR(gjh.posted_date,’DD-MON-YYYY’) posted_date, xah.event_type_code…
Read MoreSELECT POV.VENDOR_NAME , AIA.INVOICE_TYPE_LOOKUP_CODE AS INVOICE_TYPE , AIA.INVOICE_NUM , AIA.INVOICE_DATE , AID.ACCOUNTING_DATE AS GL_DATE , AIA.INVOICE_CURRENCY_CODE , PHA.SEGMENT1 AS PO_NUMBER , RSH.RECEIPT_NUM , NVL(AIA.EXCHANGE_RATE,1) AS EXCHANGE_RATE , GCC.CONCATENATED_SEGMENTS , AID.AMOUNT…
Read MoreSELECT customer, account_number, invoice_no, line_no, accounting_rule_name, functional_amount FROM ( SELECT replace(hp.party_name,’,’,’ ‘) customer, hca.account_number, rcl.customer_trx_line_id, aps.creation_date creation_date, rct.trx_date invoice_date, rct.invoicing_rule_id invoice_rule, rct.trx_number invoice_no, rcl.line_number line_no, replace(rcl.line_type,’,’,’ ‘) line_type, replace(rcl.description,’,’,’ ‘)…
Read MoreAIM: Bellow query is used for getting the data of AR Collection report for last four weeks in account receivable. Script: select ( SELECT TERRITORY_SHORT_NAME FROM fnd_territories_vl WHERE TERRITORY_CODE=COUNTRY.COUNTRY)COUNTRY, CURRENCY_CODE,…
Read MoreAIM: This query is used for Iexpence Report query. Script: select APERH.EMPLOYEE_ID ,APERH.INVOICE_NUM EXPENSE_NUMBER ,DECODE(APERH.WORKFLOW_APPROVED_FLAG,’S’,’SAVED’,’I’,’IMPLICIT SAVE’,’R’,’MANAGER REJECTED’,’M’,’MANAGER APPROVED’,’P’ ,’PAYABLES APPROVED’,’A’,’AUTO APPROVED’,’W’,’WITHDRAWN’,’Y’,’APPROVED’,’IN PROGRESS’) INVOICE_STATUS ,aperh.expense_status_code ,APERH.CREATION_DATE ,OVERRIDE_APPROVER_NAME APPROVER_NAME , CASE WHEN…
Read MoreAIM: This query is used for RA Customer transaction in Account receivable. Script : SELECT hp.party_name CUSTOMER_NAME, rcta.TRX_NUMBER INVOICE_NUMBER, rcta.trx_date INVOICE_DATE, (SELECT SUM (extended_amount) FROM ra_customer_trx_lines_all ct WHERE ct.customer_trx_id =…
Read MoreSELECT “Transaction Type Name”, party_name, party_site_number, “Payment Terms”, gl_date, due_date, total_days, CASE WHEN total_days > 0 THEN amt_due_remaining END “Not Due Amount”, CASE WHEN total_days BETWEEN 1 AND 30 THEN…
Read MoreAIM: This query is used for getting the data of AR Receipt in account receivable. Script: SELECT ABA.NAME Receipt_Batch ,DECODE(ACRA.STATUS,’APP’,’APPLIED’,’UNID’,’UNIDENTIFIED’,’UNAPP’,’UNAPPLIED’,’REV’,’REVERSED’,ACRA.STATUS) STATE ,acra.RECEIPT_NUMBER ,ACRA.COMMENTS ,sum(unapp.amount_applied) Unapplied_Amount ,ACRA.TYPE ,ACRA.AMOUNT RECEIPT_AMOUNT ,ARM.NAME RECEIPT_MENTHOD…
Read MoreThis Query is used for getting the AP invoice for TDS during the current financial Year. Script: SELECT SEGMENT1 SUPPLIER_NUMBER ,VENDOR_NAME SUPPLIER_NAME ,ACTUAL_SECTION_CODE SECTION_CODE ,aia.invoice_num ,aia.invoice_date , jt.TAX_AMOUNT tax_amount ,SUM(JT.AMOUNT) …
Read More