select (SELECT hou.NAME FROM apps.hr_operating_units hou WHERE 1 = 1 AND hou.organization_id = aps.org_id) ou_name, (SELECT paym.payment_method_code FROM APPS.iby_ext_party_pmt_mthds paym WHERE IEPA.ext_payee_id = paym.ext_pmt_party_id and aps.vendor_site_id = iepa.supplier_site_id –AND ass.supplier_site_id…
Read Moreselect 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 Moreset 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 MoreSET 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 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 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 MoreAIM: 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