Posts by Doyensys

Oracle API Script for SHIP CONFIRMATION Using (WSH…

Oracle API Script for SHIP CONFIRMATION Using (WSH_DELIVERIES_PUB.Delivery_Action) —Ship Confirm Delivery using WSH_DELIVERIES_PUB.Delivery_Action—-   Declare   –Standard Parameters.     p_api_version                NUMBER;  …

Read More

Oracle API Script for Pick Release for Sales order…

Oracle API Script for Pick Release for Sales order( wsh_deliveries_pub.delivery_action) —sample script for pick release—  Declare    x_return_status        VARCHAR2 (2);    x_msg_count           …

Read More

OE_ORDER_PUB.PROCESS_ORDER ( Sample Script Crea…

OE_ORDER_PUB.PROCESS_ORDER ( Sample Script  Create and Book for Sales Order ) Declare l_count NUMBER; l_api_version_number NUMBER := 1; l_return_status VARCHAR2 (2000); l_msg_count NUMBER; l_msg_data VARCHAR2 (2000); l_msg_index NUMBER; API_ERROR EXCEPTION;…

Read More

Query to retrive Internal Requisition and Internal Sales Order Details

SELECT prh.segment1 ir_number,        prh.creation_date ir_date,        ppf.full_name requestor,        hl.location_code,        ood.organization_code destination_org_code,        ood.organization_name destination_org_name,      …

Read More

Auto Apply given receipts to Invoices

CREATE OR REPLACE PACKAGE BODY APPS.XX_RECEIPT_APPLICATION_PKG IS PROCEDURE MAIN(       errbuf                OUT      VARCHAR2,       retcode     …

Read More

Auto Apply given Credit memos to the Invoices

CREATE OR REPLACE PACKAGE BODY APPS.XX_CN_CM_APPLICATION_PKG IS PROCEDURE MAIN(errbuf OUT VARCHAR2,retcode OUT NUMBER,PR_GL_DATE IN VARCHAR2) IS v_count   number(20):=0; v_date           date :=fnd_date.canonical_to_date(PR_GL_DATE); v_end_date       date; v_period_status …

Read More

How to Delete Stuckked/corrupted Lock Box Transmission format

select * from APPS.AR_TRANSMISSIONS_ALL where TRANSMISSION_NAME=’IMCC161107′; delete from apps.ar_transmissions_all where transmission_name=’IMCC161107′ select * from apps.ar_payments_interface_all where ORG_ID=82 and TRANSMISSION_ID=12172459; Thanks, Nagaraju M

Read More

Inactive Customer Open Balances

select * from apps.ap_expense_report_headers_all where report_header_id = ”; select * from hz_cust_accounts where account_number like ‘07724.0M978%’; select hca.account_number ,count(rct.trx_number) ,substr(to_char(rct.trx_Date,’DD/MM/YYYY’),4,2) ,sum(rgl.amount) ,sum(amount_due_remaining) OPEN_BL ,sum(rgl.amount)-sum(amount_due_remaining) CLOSED_BL ,rct.invoice_currency_code ,rct.org_id  from apps.ra_customer_trx_all rct,…

Read More

How to Extract of all OU Wise Internal bank Accounts

SELECT cba.bank_account_name ,   cba.bank_account_id,   cba.bank_account_name_alt,   cba.bank_account_num ,   ou.name “OU NAME”,   cba.multi_currency_allowed_flag ,   cba.zero_amount_allowed ,   cba.account_classification ,   bb.bank_name ,   cba.bank_id,   bb.bank_number,…

Read More

How to Extract All the Asset Categories Book Wise with all Code Combinations.

gl3.segment1||’-‘||gl3.SEGMENT2||’-‘||gl3.SEGMENT3||’-‘||gl3.SEGMENT4||’-‘||gl3.SEGMENT5 DEPRN_RESERVE_ACCOUNT, deprn_method, life_in_months, (life_in_months/12) lIFE, prorate_convention_code FROM apps.fa_categories a, apps.FA_CATEGORY_BOOK_DEFAULTS b, apps.fa_category_books c, apps.gl_code_combinations gl1, apps.gl_code_combinations gl2, apps.gl_code_combinations gl3 WHERE a.category_id = b.category_id AND c.category_id = b.category_id AND c.category_id…

Read More