EBS Technical

AP Invoice posted register query

select aia.INVOICE_NUM ,aba.batch_name ,aia.INVOICE_AMOUNT ,aia.PAY_GROUP_LOOKUP_CODE INv_group ,aia.INVOICE_CURRENCY_CODE ,aia.CREATION_DATE ,To_char(aia.creation_date,’MONTH’) InvCreation_Month ,aia.INVOICE_DATE ,aia.SOURCE ,aia.INVOICE_TYPE_LOOKUP_CODE INVOICE_TYPE ,PAYMENT_METHOD_CODE INVOICE_PAY_METHOD ,aia.amount_paid amount_paid ,aia.description ,aia.doc_sequence_value VOUCHER_NUM ,(select max(aila.ACCOUNTING_DATE) from apps.ap_invoice_lines_all aila where aila.invoice_id = aia.invoice_id…

Read More

API to update Customer Bill to Role

set serveroutput on; DECLARE l_contact_point_rec HZ_CUST_ACCOUNT_ROLE_V2PUB.CUST_ACCOUNT_ROLE_REC_TYPE; l_role_id NUMBER := 0; l_cust_acct_id NUMBER := 0; l_obj_num NUMBER := 0; p_party_id HZ_PARTIES.party_id % TYPE; x_msg_count NUMBER; x_msg_data VARCHAR2(2000) := NULL; x_return_status VARCHAR2(1000)…

Read More

API to Update Customer Name

SET SERVEROUTPUT ON; SET DEFINE OFF; DECLARE l_organization_rec HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE; l_party_rec HZ_PARTY_V2PUB.PARTY_REC_TYPE; –l_party_obj_version NUMBER; x_profile_id NUMBER; x_return_status VARCHAR2(1); x_msg_count NUMBER; x_msg_data VARCHAR2(4000); CURSOR C1 IS SELECT ICNU.CUST_NUMBER, ICNU.CURR_NAME, ICNU.CORRECT_NAME, ICNU.STATUS, HCA.PARTY_ID,…

Read More

Account Receivable Collection Report

AIM: 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 More

Iexpence Report Query

AIM: 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 More

RA Customer transaction in Account receivable.

AIM: 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 More

AR Receipt in account receivable.

AIM: 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 More

TDS Depot Automation Report

This 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

Create Requisition interface against the blanket purchase agreement for different Locations

AIM: Component is used for Create the Requisition against the Blanket Purchase Agreement For different Locations. SCRIPT: CREATE OR REPLACE PACKAGE BODY APPS.XXTTK_BPO_PKG IS PROCEDURE XXTTK_REQ ( ERRORBUF     OUT       VARCHAR2…

Read More

SQL Query to get Catalog and Non Catalog Po’s in Oracle apps

Introduction: Using the below SQL script we can get the Catalog and Non Catalog Po’s details from country wise. Script: SELECT TO_CHAR(TO_DATE(p.creation_date,’DD-MON-RRRR’), ‘Q’)AS MY_QTR ,TO_CHAR(P.CREATION_DATE,’RRRR’) POYear ,P.SEGMENT1 “PO Number” ,PL.LINE_NUM…

Read More