Query To Get Debtor Ledger

Debtor Ledger Report  Query

SELECT DISTINCT b.gl_date, c.type type1, --d.CUSTOMER_CATEGORY_CODE , -- d.customer_name, party.party_name customer_name, -- c.NAME Vch_Type, decode(c.type, 'INV', 'Invoice', 'CM', 'Credit Memo', 'DM', 'Debit Memo') vch_type, a.trx_number doc_no, a.trx_date doc_dt, --c.DESCRIPTION particular, d.description particular, b.amount * nvl(a.exchange_rate, 1) amount, --a.customer_trx_id, -- a.INTERFACE_HEADER_ATTRIBUTE1 ref_no, --d.customer_id, cust.cust_account_id, NULL, NULL, NULL, NULL FROM ra_customer_trx_all a, ra_cust_trx_line_gl_dist_all b, ra_cust_trx_types_all c, hz_cust_accounts cust, hz_parties party, hz_cust_acct_sites_all hcasa, hz_cust_site_uses_all hcsua, ( SELECT DISTINCT gcc.code_combination_id, gcc.segment4, ffvl.description, lgd.customer_trx_id FROM ra_customer_trx_all a, ra_cust_trx_line_gl_dist_all lgd, gl_code_combinations gcc, fnd_flex_values_vl ffvl WHERE a.customer_trx_id = lgd.customer_trx_id AND lgd.code_combination_id = gcc.code_combination_id AND gcc.segment4 = ffvl.flex_value AND lgd.account_class = 'REV' ) d WHERE cust.party_id = party.party_id AND cust.cust_account_id = hcasa.cust_account_id AND a.customer_trx_id = d.customer_trx_id AND hcasa.cust_acct_site_id = hcsua.cust_acct_site_id AND a.customer_trx_id = b.customer_trx_id AND a.cust_trx_type_id = c.cust_trx_type_id AND a.bill_to_customer_id = cust.cust_account_id AND a.complete_flag = 'Y' AND b.org_id = nvl(:p_org_id, b.org_id) AND b.latest_rec_flag = 'Y' AND c.type IN ( 'INV', 'CM', 'DM' ) AND b.gl_date IS NOT NULL AND trunc(b.gl_date) BETWEEN nvl(:p_from_date, b.gl_date) AND nvl(:p_to_date, b.gl_date) AND cust.cust_account_id = nvl(:p_from_customer_id, cust.cust_account_id) UNION SELECT DISTINCT ( g.gl_date ), 'REC' type1, party.party_name customer_name, --e.TYPE vch_type, 'Receipt' vch_type, e.receipt_number doc_no, e.receipt_date doc_dt, abca.bank_account_name particular, e.amount amount, cust.cust_account_id, -- e.cash_receipt_id, NULL, NULL, NULL, NULL FROM ar_cash_receipts_all e, ar_cash_receipt_history_all g, ce_bank_accounts abca, ce_bank_acct_uses_all abcau, hz_cust_accounts cust, hz_parties party, ar_receivable_applications_all f, ar_payment_schedules_all d WHERE e.cash_receipt_id = g.cash_receipt_id AND abcau.bank_account_id = abca.bank_account_id AND e.remit_bank_acct_use_id = abcau.bank_acct_use_id AND e.pay_from_customer = cust.cust_account_id AND party.party_id = cust.party_id --and hcasa.CUST_ACCT_SITE_ID=hcsua.CUST_ACCT_SITE_ID --and cust.CUST_ACCOUNT_ID=hcasa.cust_account_id -- and cust.CUST_ACCOUNT_ID=hcasa.cust_account_id AND e.cash_receipt_id = f.receivable_application_id AND d.payment_schedule_id = f.payment_schedule_id -- AND g.status = 'CLEARED' AND g.gl_date BETWEEN nvl(:p_from_date, g.gl_date) AND nvl(:p_to_date, g.gl_date) AND cust.cust_account_id = nvl(:p_from_customer_id, cust.cust_account_id) AND g.org_id = nvl(:p_org_id, g.org_id) UNION SELECT DISTINCT ( g.gl_date ), 'REV' type1, --rc.CUSTOMER_CATEGORY_CODE, party.party_name customer_name, -- e.TYPE vch_type, 'Receipt' vch_type, e.receipt_number doc_no, e.receipt_date doc_dt, abca.bank_account_name particular, e.amount amount, -- null, --e.COMMENTS,--a.TRX_NUMBER||' '||ABCA.BANK_ACCOUNT_NAME ref_no , -- hp.PARTY_NAME, --e.cash_receipt_id, --e.REVERSAL_COMMENTS cust.cust_account_id, NULL, NULL, NULL, NULL FROM ar_payment_schedules_all d, ar_cash_receipts_all e, ar_receivable_applications_all f, ar_cash_receipt_history_all g, --RA_CUSTOMER_TRX_ALL A , hz_cust_accounts cust, hz_parties party, hz_cust_acct_sites_all hcasa, hz_cust_site_uses_all hcsua, ce_bank_accounts abca WHERE d.payment_schedule_id = f.payment_schedule_id AND f.cash_receipt_id = e.cash_receipt_id (+) -- AND f.AMOUNT_APPLIED IS NOT NULL AND f.cash_receipt_id = g.cash_receipt_id --and d.CUSTOMER_ID = nvl(:P_FROM_CUSTOMER_ID,d.customer_id) AND d.customer_id IN ( SELECT DISTINCT cust_account_id FROM hz_cust_acct_sites_all WHERE cust_acct_site_id IN ( SELECT DISTINCT cust_acct_site_id FROM hz_cust_site_uses_all --where GL_ID_REC=:P_CODE) ) ) AND g.gl_date BETWEEN nvl(:p_from_date, g.gl_date) AND nvl(:p_to_date, g.gl_date) AND g.first_posted_record_flag = 'N' AND g.org_id = nvl(:p_org_id, g.org_id) -- and rc.customer_id=d.CUSTOMER_ID -- and rc.customer_id=d.CUSTOMER_ID --and rc.CUSTOMER_ID=hcasa.CUST_ACCOUNT_ID AND cust.party_id = party.party_id -- and D.customer_id = hcasa.cust_account_id AND cust.cust_account_id = hcasa.cust_account_id AND d.customer_id = hcasa.cust_account_id AND hcasa.cust_acct_site_id = hcsua.cust_acct_site_id AND e.receipt_method_id NOT IN ( SELECT receipt_method_id FROM ar_receipt_methods WHERE upper(name) LIKE 'ADVANCE%' ) AND e.reversal_date IS NOT NULL AND e.remittance_bank_account_id = abca.bank_account_id --AND HCSUA.GL_ID_REC=:P_CODE AND e.status IN ( 'REV', 'NSF', 'STOP' ) -- and e.STATUS='REV' AND e.cash_receipt_id IN ( SELECT cash_receipt_id FROM ar_cash_receipt_history_all a WHERE a.status = 'REVERSED' ) AND g.reversal_gl_date IS NULL AND g.reversal_cash_receipt_hist_id IS NULL AND cust.cust_account_id = nvl(:p_from_customer_id, cust.cust_account_id) UNION SELECT DISTINCT h.gl_date, 'EXGL' type, --rc.CUSTOMER_CATEGORY_CODE, party.party_name customer_name, 'Receipt' vch_type, c.receipt_number doc_no,--trx number c.receipt_date doc_dt, abca.bank_account_name particular, --sum((c.EXCHANGE_RATE-t.EXCHANGE_RATE)*(AMOUNT_APPLIED)) SUM((nvl(c.exchange_rate, r.trans_to_receipt_rate) - t.exchange_rate) *(r.amount_applied)) exgainloss, --(c.EXCHANGE_RATE-t.EXCHANGE_RATE)*(AMOUNT_APPLIED) AMOUNT, --rc.CUSTOMER_ID , cust.cust_account_id, NULL, NULL, NULL, NULL FROM ra_customer_trx_all t, ar_receivable_applications_all r, ar_cash_receipts_all c, ar_cash_receipt_history_all h, ce_bank_accounts abca, hz_cust_accounts cust, hz_parties party WHERE r.applied_customer_trx_id = t.customer_trx_id AND c.cash_receipt_id = r.cash_receipt_id AND cust.party_id = party.party_id AND c.cash_receipt_id = h.cash_receipt_id AND c.pay_from_customer = cust.cust_account_id -- AND c.REMITTANCE_BANK_ACCOUNT_ID=ABCA.BANK_ACCOUNT_ID AND c.remit_bank_acct_use_id = abca.bank_account_id AND cust.party_id = party.party_id AND c.pay_from_customer = cust.cust_account_id --and r.CASH_RECEIPT_ID = h.CASH_RECEIPT_ID --and c.PAY_FROM_CUSTOMER = 1040 AND h.status = 'CLEARED' -- and h.STATUS = 'REMITTED' AND h.gl_date BETWEEN nvl(:p_from_date, h.gl_date) AND nvl(:p_to_date, h.gl_date) AND h.org_id = nvl(:p_org_id, h.org_id) AND cust.cust_account_id = nvl(:p_from_customer_id, cust.cust_account_id) --and c.CURRENCY_CODE <> 'INR' --AND RC.CUSTOMER_ID =:P_FROM_CUSTOMER_ID--new --and (nvl(c.exchange_rate,r.TRANS_TO_RECEIPT_RATE)-t.EXCHANGE_RATE)*(r.AMOUNT_APPLIED)<>0 GROUP BY h.gl_date, type, c.receipt_number, c.receipt_date, NULL, NULL, c.cash_receipt_id, c.comments, party.party_name, abca.bank_account_name, cust.cust_account_id UNION ALL SELECT DISTINCT b.gl_date, 'ADJ' type1, -- d.CUSTOMER_CATEGORY_CODE, party.party_name, c.name vch_type, a.trx_number, a.trx_date, -- c.DESCRIPTION particular, d.description particular, b.amount * nvl(a.exchange_rate, 1) amount, a.customer_trx_id, a.interface_header_attribute1 ref_no, -- d.customer_id, NULL, NULL, NULL FROM ra_customer_trx_all a, ar_adjustments_all b, ra_cust_trx_types_all c, --AR_CUSTOMERS d, hz_cust_accounts cust, hz_parties party, hz_cust_acct_sites_all hcasa, hz_cust_site_uses_all hcsua, ( SELECT DISTINCT gcc.code_combination_id, gcc.segment4, ffvl.description, lgd.customer_trx_id FROM ra_customer_trx_all a, ra_cust_trx_line_gl_dist_all lgd, gl_code_combinations gcc, fnd_flex_values_vl ffvl WHERE a.customer_trx_id = lgd.customer_trx_id AND lgd.code_combination_id = gcc.code_combination_id AND gcc.segment4 = ffvl.flex_value AND lgd.account_class = 'REV' ) d WHERE a.customer_trx_id = b.customer_trx_id AND a.cust_trx_type_id = c.cust_trx_type_id AND cust.party_id = party.party_id AND a.bill_to_customer_id = cust.cust_account_id AND cust.cust_account_id = hcasa.cust_account_id AND hcasa.cust_acct_site_id = hcsua.cust_acct_site_id AND a.customer_trx_id = d.customer_trx_id --and a.customer_trx_id <> '69735' --AND a.sold_to_customer_id =d.customer_id AND b.org_id = nvl(:p_org_id, b.org_id) --AND c.type in ('INV','CM','DM') AND b.gl_date IS NOT NULL --AND a.trx_number =:P_TRX AND trunc(b.gl_date) BETWEEN nvl(:p_from_date, b.gl_date) AND nvl(:p_to_date, b.gl_date) AND cust.cust_account_id = nvl(:p_from_customer_id, cust.cust_account_id) --AND HCSUA.GL_ID_REC=:P_CODE; ORDER BY 1, 3

Query To Get Customer Address Details In Oracle Apps R12

Customer Address Details in Oracle apps?

The following Query will provide the customer address details in oracle applications.

SELECT  hca.account_number customer_number,
                hp.party_name customer_name,
                hps.party_site_number site_number, hl.address1 address1,
                hl.address2 address2, hl.address3 address3,
                hl.address4 address4, hl.city city,
                hl.postal_code postal_code, hl.state state,
                ftt.territory_short_name country,
                hcsua1.LOCATION bill_to_location,
                hcsua2.LOCATION ship_to_location
  FROM hz_parties hp,
                hz_party_sites hps,
                hz_cust_accounts hca,
                hz_cust_acct_sites_all hcasa1,
                hz_cust_site_uses_all hcsua1,
                hz_locations hl,
                fnd_territories_tl ftt,
                hz_cust_acct_sites_all hcasa2,
                hz_cust_site_uses_all hcsua2
  WHERE hp.party_id = hps.party_id(+)
       AND hp.party_id = hca.party_id(+)
       AND hcasa1.party_site_id(+) = hps.party_site_id
       AND hcasa2.party_site_id(+) = hps.party_site_id
       AND hcsua1.cust_acct_site_id(+) = hcasa1.cust_acct_site_id
       AND hcsua2.cust_acct_site_id(+) = hcasa2.cust_acct_site_id
       AND hcsua1.site_use_code(+) = 'bill_to'
       AND hcsua2.site_use_code(+) = 'ship_to'
       AND hcasa1.org_id(+) = fnd_profile.VALUE ('org_id')
       AND hcasa2.org_id(+) = fnd_profile.VALUE ('org_id')
       AND hps.location_id = hl.location_id
       AND hl.country = ftt.territory_code
       AND ftt.LANGUAGE = USERENV ('lang')
  ORDER BY customer_number;

AR Tables - Accounts Receivables In EBS R12

AR Tables - Accounts Receivables

Table Name & Description:

RA_CUST_TRX_TYPES_ALL Transaction type for invoices, commitments and credit memos

RA_CUSTOMER_TRX_ALL Header-level information about invoices, debit memos, chargebacks, commitments and credit memos

RA_CUSTOMER_TRX_LINES_ALL Invoice, debit memo, chargeback, credit memo and commitment lines

RA_CUST_TRX_LINE_GL_DIST_ALL Accounting records for revenue, unearned revenue and unbilled receivables

RA_CUST_TRX_LINE_SALESREPS_ALL Sales credit assignments for transactions

AR_ADJUSTMENTS_ALL Pending and approved invoice adjustments

RA_BATCHES_ALL

AR_CASH_RECEIPTS_ALL Detailed receipt information

AR_CASH_RECEIPT_HISTORY_ALL History of actions and status changes in the life cycle of a receipt

AR_PAYMENT_SCHEDULES_ALL All transactions except adjustments and miscellaneous cash receipts

AR_RECEIVABLE_APPLICATIONS_ALL Accounting information for cash and credit memo applications

AR_TRANSACTION_HISTORY_ALL Life cycle of a transaction

HZ_CUST_ACCOUNTS Stores information about customer accounts.

HZ_CUSTOMER_PROFILES Credit information for customer accounts and customer account sites

HZ_CUST_ACCT_SITES_ALL Stores all customer account sites across all operating units

HZ_CUST_ACCT_RELATE_ALL Relationships between customer accounts

HZ_CUST_CONTACT_POINTS This table is no longer used

HZ_CUST_PROF_CLASS_AMTS Customer profile class amount limits for each currency

HZ_CUST_SITE_USES_ALL Stores business purposes assigned to customer account sites.

HZ_LOCATIONS Physical addresses

HZ_ORG_CONTACTS People as contacts for parties

HZ_ORG_CONTACT_ROLES Roles played by organization contacts

HZ_PARTIES Information about parties such as organizations, people, and groups

HZ_PARTY_SITES Links party to physical locations

HZ_PARTY_SITE_USES The way that a party uses a particular site or address

HZ_RELATIONSHIPS Relationships between entities

HZ_RELATIONSHIP_TYPES Relationship types

Convert Amount In Number In To Words In EBS R12

How To Convert Amount In Number In To Words.

Create following Function to convert amount in number in to words.

CREATE OR REPLACE FUNCTION APPS.f_words (p_amount IN Number) RETURN Varchar2 IS ------------------------------------- -- Index by Tables to store word list ------------------------------------- TYPE typ_word_list IS TABLE OF Varchar2(200) INDEX BY BINARY_INTEGER; t_typ_word_list typ_word_list; TYPE typ_word_gap IS TABLE OF Varchar2(200) INDEX BY BINARY_INTEGER; t_typ_word_gap typ_word_gap; ------------------ -- Local Variables ------------------ v_amount Number := p_amount; v_amount_length Number; v_words Varchar2(10000); v_point_found Varchar2(1) := 'N'; v_point_value Number; BEGIN /*Getting value after point if found */ v_point_value := SUBSTR(v_amount,(INSTR(v_amount,'.',1) + 1),2); /*Checking whether amount has any scale value also */ v_point_found := CASE WHEN (INSTR(v_amount,'.',1)) = 0 THEN 'N' WHEN (INSTR(v_amount,'.',1)) > 0 THEN 'Y' END; /*Converting amount into pure numeric format */ v_amount := FLOOR(ABS(v_amount)); -- v_amount_length := LENGTH(v_amount); -- t_typ_word_gap(2) := 'and Paise'; t_typ_word_gap(3) := 'Hundred'; t_typ_word_gap(4) := 'Thousand'; t_typ_word_gap(6) := 'Lakh'; t_typ_word_gap(8) := 'Crore'; t_typ_word_gap(10) := 'Arab'; -- FOR i IN 1..99 LOOP t_typ_word_list(i) := To_Char(To_Date(i,'J'),'Jsp'); END LOOP; -- IF v_amount_length <= 2 THEN /* Conversion 1 to 99 digits */ v_words := t_typ_word_list(v_amount); ELSIF v_amount_length = 3 THEN /* Conversion for 3 digits till 999 */ v_words := t_typ_word_list(SUBSTR(v_amount,1,1))||' '||t_typ_word_gap(3); -- v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,2,2)); IF SUBSTR(v_amount,2,2) != 0 THEN v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,2,2)); END IF; ELSIF v_amount_length = 4 THEN /* Conversion for 4 digits till 9999 */ v_words := t_typ_word_list(SUBSTR(v_amount,1,1))||' '||t_typ_word_gap(4); IF SUBSTR(v_amount,2,1) != 0 THEN v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,2,1))||' '||t_typ_word_gap(3); END IF; IF SUBSTR(v_amount,3,2) != 0 THEN v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,3,2)); END IF; ELSIF v_amount_length = 5 THEN /* Conversion for 5 digits till 99999 */ v_words := t_typ_word_list(SUBSTR(v_amount,1,2))||' '||t_typ_word_gap(4); IF SUBSTR(v_amount,3,1) != 0 THEN v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,3,1))||' '||t_typ_word_gap(3); END IF; IF SUBSTR(v_amount,4,2) != 0 THEN v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,4,2)); END IF; ELSIF v_amount_length = 6 THEN /* Conversion for 6 digits till 999999 */ v_words := t_typ_word_list(SUBSTR(v_amount,1,1))||' '||t_typ_word_gap(6); IF SUBSTR(v_amount,2,2) != 0 THEN v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,2,2))||' '||t_typ_word_gap(4); END IF; IF SUBSTR(v_amount,4,1) != 0 THEN v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,4,1))||' '||t_typ_word_gap(3); END IF; IF SUBSTR(v_amount,5,2) != 0 THEN v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,5,2)); END IF; ELSIF v_amount_length = 7 THEN /* Conversion for 7 digits till 9999999 */ v_words := t_typ_word_list(SUBSTR(v_amount,1,2))||' '||t_typ_word_gap(6); IF SUBSTR(v_amount,3,2) != 0 THEN v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,3,2))||' '||t_typ_word_gap(4); END IF; IF SUBSTR(v_amount,5,1) != 0 THEN v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,5,1))||' '||t_typ_word_gap(3); END IF; IF SUBSTR(v_amount,6,2) != 0 THEN v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,6,2)); END IF; ELSIF v_amount_length = 8 THEN /* Conversion for 8 digits till 99999999 */ v_words := t_typ_word_list(SUBSTR(v_amount,1,1))||' '||t_typ_word_gap(8); IF SUBSTR(v_amount,2,2) != 0 THEN v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,2,2))||' '||t_typ_word_gap(6); END IF; IF SUBSTR(v_amount,4,2) != 0 THEN v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,4,2))||' '||t_typ_word_gap(4); END IF; IF SUBSTR(v_amount,6,1) != 0 THEN v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,6,1))||' '||t_typ_word_gap(3); END IF; IF SUBSTR(v_amount,7,2) != 0 THEN v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,7,2)); END IF; ELSIF v_amount_length = 9 THEN /* Conversion for 9 digits till 999999999 */ v_words := t_typ_word_list(SUBSTR(v_amount,1,2))||' '||t_typ_word_gap(8); IF SUBSTR(v_amount,3,2) != 0 THEN v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,3,2))||' '||t_typ_word_gap(6); END IF; IF SUBSTR(v_amount,5,2) != 0 THEN v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,5,2))||' '||t_typ_word_gap(4); END IF; IF SUBSTR(v_amount,7,1) != 0 THEN v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,7,1))||' '||t_typ_word_gap(3); END IF; IF SUBSTR(v_amount,8,2) != 0 THEN v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,8,2)); END IF; ELSIF v_amount_length = 10 THEN /* Conversion for 10 digits till 9999999999 */ v_words := t_typ_word_list(SUBSTR(v_amount,1,1))||' '||t_typ_word_gap(10); IF SUBSTR(v_amount,2,2) != 0 THEN v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,2,2))||' '||t_typ_word_gap(8); END IF; IF SUBSTR(v_amount,4,2) != 0 THEN v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,4,2))||' '||t_typ_word_gap(6); END IF; IF SUBSTR(v_amount,6,2) != 0 THEN v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,6,2))||' '||t_typ_word_gap(4); END IF; IF SUBSTR(v_amount,8,1) != 0 THEN v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,8,1))||' '||t_typ_word_gap(3); END IF; IF SUBSTR(v_amount,9,2) != 0 THEN v_words := v_words ||' '||t_typ_word_list(SUBSTR(v_amount,9,2)); END IF; END IF; -- IF v_point_found = 'Y' THEN IF v_point_value != 0 THEN v_words := v_words||' '||t_typ_word_gap(2)||' '||t_typ_word_list(CASE WHEN LENGTH(SUBSTR(p_amount,(INSTR(p_amount,'.',1) + 1),2)) = 1 THEN SUBSTR(p_amount,(INSTR(p_amount,'.',1) + 1),2)||'0' WHEN LENGTH(SUBSTR(p_amount,(INSTR(p_amount,'.',1) + 1),2)) = 2 THEN SUBSTR(p_amount,(INSTR(p_amount,'.',1) + 1),2) END); END IF; END IF; -- IF p_amount < 0 THEN v_words := 'Minus '||v_words; ELSIF p_amount = 0 THEN v_words := 'Zero'; END IF; IF LENGTH(v_amount) > 10 THEN v_words := 'Value larger than specified precision allowed to convert into words. Maximum 10 digits allowed for precision.'; END IF; RETURN (v_words); END f_words; / sho err select f_words(1548555) from dual --------------------------------------------------------------------------------- CREATE OR REPLACE FUNCTION APPS.spell_number (p_number IN NUMBER) RETURN VARCHAR2 AS TYPE myArray IS TABLE OF VARCHAR2 (255); l_str myArray := myArray ('', ' THOUSAND ',' LAKH ',' CRORE ',' ARAB ',' KHARAB ',' SHANKH '); l_num VARCHAR2 (50) DEFAULT TRUNC (p_number); l_return VARCHAR2 (4000); BEGIN FOR i IN 1 .. l_str.COUNT LOOP EXIT WHEN l_num IS NULL; IF (SUBSTR (l_num, LENGTH (l_num) - 2, 3) <> 0) THEN l_return := TO_CHAR (TO_DATE (SUBSTR (l_num, LENGTH (l_num) - 2, 3), 'J'), 'Jsp') || l_str (i) || l_return; END IF; l_num := SUBSTR (l_num, 1, LENGTH (l_num) - 3); END LOOP; RETURN l_return; END; /


------------------------------------------------------------------------------
SELECT spell_number (5373455) FROM DUAL;
------------------------------------------------------------------------------

Query To Get Password For Application User In EBS R12

Query to get password for application user

Below above query works only in 11i since Oracle removed ‘Guest User Password’ profile option from Release 12 so please use below query for R12

Select Usertable.User_Name , ( Select XX_Get_User_Pwd.Decrypt (Upper ( ( Select ( Select Get_User_Pwd.Decrypt (Upper ( ( Select Upper (Fnd_Profile.Value ('Guest_User_Pwd')) From Dual)), Usertable.Encrypted_Foundation_Password) From Dual) As Apps_Password From Fnd_User Usertable Where Usertable.User_Name Like Upper ( ( Select Substr (Fnd_Profile.Value ('Guest_User_Pwd') , 1 , Instr (Fnd_Profile.Value ('Guest_User_Pwd'), '/') - 1 ) From Dual)))) ,Usertable.Encrypted_User_Password) From Dual) As Encrypted_User_Password From Fnd_User Usertable Where Usertable.User_Name Like Upper ('&Username');

=========================================================================

QUERY TO GET THE PASSWORD FOR THE FRONTEND USER LOGIN

====================================================================

ALTER SESSION SET current_schema = apps;

Select Usr.User_Name , Usr.Description, XX_Get_User_Pwd.Decrypt( ( Select ( Select Get_User_Pwd.Decrypt (Fnd_Web_Sec.Get_Guest_Username_Pwd, Usertable.Encrypted_Foundation_Password) From Dual) As Apps_Password From Fnd_User Usertable Where Usertable.User_Name = ( Select Substr(Fnd_Web_Sec.Get_Guest_Username_Pwd,1, Instr(Fnd_Web_Sec.Get_Guest_Username_Pwd,'/') - 1) From Dual)), Usr.Encrypted_User_Password) Password From Fnd_User Usr Where Usr.User_Name = '&User_Name';

Query To Get Apps User Password From Backend In EBS R12

Query To Get Apps User Password From Backend In EBS R12

ALTER SESSION SET current_schema = apps;

Select ( Select XX_ Get_User_Pwd.Decrypt (Fnd_Web_Sec.Get_Guest_Username_Pwd, Usertable.Encrypted_Foundation_Password) From Dual) As Apps_Password From Fnd_User Usertable Where Usertable.User_Name Like ( Select Substr (Fnd_Web_Sec.Get_Guest_Username_Pwd ,1, Instr (Fnd_Web_Sec.Get_Guest_Username_Pwd, '/') - 1) From Dual);

Method 2: Query to get password for apps user

 Query to get password for apps user

Below query works only in 11i since Oracle removed ‘Guest User Password’ profile option from Release 12 so please use below query for R12.

Select ( Select XX_Get_User_Pwd.Decrypt (Upper ( ( Select Upper (Fnd_Profile.Value ('Guest_User_Pwd')) From Dual)), Usertable.Encrypted_Foundation_Password) From Dual) As Apps_Password From Fnd_User Usertable Where Usertable.User_Name Like Upper ( ( Select Substr (Fnd_Profile.Value ('Guest_User_Pwd') ,1 , Instr (Fnd_Profile.Value ('Guest_User_Pwd'), '/') - 1 ) From Dual));

Method 1 : Query To Find Password Of A User In Oracle Apps(11i\R12)

 How to Find password of a User in Oracle Apps(11i\R12)?

Method 1: Create below package

CREATE OR REPLACE PACKAGE xx_get_user_pwd

AS

    FUNCTION decrypt ( KEY IN VARCHAR2 ,VALUE IN VARCHAR2 ) RETURN VARCHAR2;

END xx_get_user_pwd;
/

CREATE OR REPLACE PACKAGE BODY xx_get_user_pwd

AS

    FUNCTION decrypt ( KEY IN VARCHAR2 ,VALUE IN VARCHAR2 )

    RETURN VARCHAR2

AS         LANGUAGE JAVA NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt

(java.lang.String,java.lang.String) return java.lang.String';

END xx_get_user_pwd;
/

Query To Get Quarterly Start And End Date In EBS R12

 HOW TO GET QUARTRLY START AND END DATE ?

SELECT ADD_MONTHS (TRUNC (param.start_date, 'Q'), 3 * (LEVEL - 1) ) AS qstart, ADD_MONTHS (TRUNC (param.start_date, 'Q'), 3 * (LEVEL)) - 1 AS qend FROM ( SELECT TO_DATE ('&start_date') AS start_date, TO_DATE ('&end_date') AS end_date FROM DUAL) param CONNECT BY ADD_MONTHS (TRUNC (param.start_date, 'Q'), 3 * (LEVEL)) - 1 <= param.end_date

Query To Find On-Hand Inventory Information In EBS R12

How to find On-hand inventory information?

Execute below query to see the on-hand inventory information in oracle applications.


SELECT NVL(substr(org.organization_code, 1,3), ' ') orgcode , NVL(substr(msi.segment1, 1, 8), ' ') seg11 , NVL(substr(msi.segment1, 9, 8), ' ') seg12 , NVL(substr(msi.segment1, 17, 4), ' ') seg13 , NVL(moq.subinventory_code, ' ') sub_inv_code , NVL(to_char(round(sum(moq.transaction_quantity))), ' ') trans_qnty FROM mtL_system_items msi , org_organization_definitions org , mtl_onhand_quantities moq , hr_organization_units hou WHERE moq.inventory_iteM_id = msi.inventory_item_id AND moq.organizatioN_id = msi.organizatioN_id AND moq.organizatioN_id = org.organizatioN_id AND moq.organization_id = hou.organization_id GROUP BY org.organization_code , moq.subinventory_code , msi.segment1;

Query To Get PO Details With TAX Amount & Item Amount Details In EBS R12

 Query to Get PO detail with his tax amount and item amount details

Note:- Only Purchase Requisitions will be converted to the Purchase orders.

select pha.SEGMENT1 po_num , pha.AUTHORIZATION_STATUS po_status , pha.CLOSED_CODE close_status , trunc(pha.CREATION_DATE) po_date , pla.LINE_NUM po_line_number , gcc.SEGMENT2 cost_center , gl_flexfields_pkg.get_description_sql (0986, 2, gcc.segment2 ) cost_center_desc, pla.UNIT_PRICE , pla.QUANTITY , (pla.UNIT_PRICE*pla.QUANTITY) Base_amount , ( select nvl(sum(jpt.TAX_AMOUNT),0) from jai_po_taxes jpt, jai_cmn_taxes_all jcta where jpt.PO_HEADER_ID =pla.PO_HEADER_ID and jpt.PO_LINE_ID =pla.PO_LINE_ID and jcta.TAX_ID =jpt.TAX_ID and jcta.INCLUSIVE_TAX_FLAG='N' ) tax_amt, ((pla.UNIT_PRICE*pla.QUANTITY) + ( select nvl(sum(jpt.TAX_AMOUNT),0) from jai_po_taxes jpt, jai_cmn_taxes_all jcta where jpt.PO_HEADER_ID =pla.PO_HEADER_ID and jpt.PO_LINE_ID =pla.PO_LINE_ID and jcta.TAX_ID =jpt.TAX_ID and jcta.INCLUSIVE_TAX_FLAG='N' ) ) total_amt from po_headers_all pha , po_lines_all pla , po_distributions_all pda , po_line_locations_all plla , gl_code_combinations gcc where pha.PO_HEADER_ID =pla.PO_HEADER_ID and pla.PO_LINE_ID =pda.PO_LINE_ID and pda.PO_HEADER_ID =pha.PO_HEADER_ID and pda.LINE_LOCATION_ID =plla.LINE_LOCATION_ID and pda.CODE_COMBINATION_ID=gcc.CODE_COMBINATION_ID and pha.SEGMENT1 = :P_PO_NUM order by 1,5

Query To Get Cancelled Requisitions In EBS R12

 How to get the Cancelled Requisitions?

You can get all the Canceled Requisitions in the PO Module using the following Query.

SELECT prha.requisition_header_id "requisition_header_id" , prha.segment1 "Requisition Number" , prha.preparer_id "preparer_id" , TRUNC(prha.creation_date) "creation_date" , prha.description "description" , prha.note_to_authorizer "note_to_authorizer" FROM po_requisition_headers_all prha , po_action_history pah WHERE action_code ='CANCEL' AND pah.object_type_code=' REQUISITION' AND pah.object_id =prha.requisition_header_id

Query For Purchasing Summary Report

Purchasing Summary Report

Query to get purchaser order summer with invoice no amount tax amount 


SELECT AI.ATTRIBUTE1 LOCATION , AI.ATTRIBUTE13 STATE , PV.VENDOR_NAME , PVS.VENDOR_SITE_CODE , ai.INVOICE_NUM , AI.INVOICE_DATE , ROUND(SUM(AID.AMOUNT)) Inv_Amount -- jiatd.TAX_AMOUNT , JITC.TAX_RATE , jitc.TAX_NAME , SUM(jiatd.TAX_AMOUNT) T_TAX , AI.INVOICE_AMOUNT FROM po_vendors pv , po_vendor_sites_all pvs , ja_in_ap_tax_distributions jiatd, ja_in_tax_codes jitc , ap_invoices_all ai , ap_invoice_distributions_all aid WHERE pv.vendor_id =pvs.vendor_id AND ai.vendor_id =pvs.vendor_id AND ai.vendor_site_id =pvs.vendor_site_id AND jitc.tax_id = jiatd.tax_id AND ai.invoice_id = jiatd.invoice_id AND ai.invoice_id = aid.invoice_id AND JIATD.INVOICE_ID = AID.INVOICE_ID AND JIATD.PO_DISTRIBUTION_ID = AID.PO_DISTRIBUTION_ID AND JIATD.PARENT_INVOICE_DISTRIBUTION_ID = AID.INVOICE_DISTRIBUTION_ID AND aid.LINE_TYPE_LOOKUP_CODE LIKE 'ITEM' AND ai.CANCELLED_DATE IS NULL --AND ai.ATTRIBUTE1 =NVL(:P_Location,ai.ATTRIBUTE1) AND ai.set_of_books_id = :P_SET_OF_BOOKS_ID --AND AI.INVOICE_NUM LIKE '09806%' AND ai.ATTRIBUTE1=NVL(:P_LOCATION,ai.ATTRIBUTE1) AND ( ai.ATTRIBUTE13)=nvl(:P_STATE,ai.ATTRIBUTE13) AND ai.set_of_books_id = :P_SET_OF_BOOKS_ID AND ai.INVOICE_DATE BETWEEN NVL(:p_from_date,ai.INVOICE_DATE) AND nvl(:p_to_date,ai.INVOICE_DATE) AND pvs.vendor_id =nvl(:p_vendor_id,pvs.vendor_id) AND pvs.vendor_site_id = nvl(:p_vendor_site_id,pvs.vendor_site_id) AND pv.VENDOR_TYPE_LOOKUP_CODE = NVL(:p_vndor_class,pv.vendor_type_lookup_code) AND jitc.TAX_NAME = NVL(:p_tax_type,jitc.tax_name) AND PVS.VENDOR_SITE_CODE =NVL( :p_site,pvs.vendor_site_code) GROUP BY AI.INVOICE_NUM ,--jiatd.TAX_AMOUNT, jitc.TAX_NAME , JITC.TAX_RATE , ai.INVOICE_NUM , JIATD.INVOICE_ID , JIATD.TASK_ID , AI.INVOICE_AMOUNT , AI.INVOICE_DATE , AI.ATTRIBUTE1 , AI.ATTRIBUTE13 , PV.VENDOR_NAME , PVS.VENDOR_SITE_CODE

Query For Debit Rate Report In EBS 12

 SPCL - DEBIT RATE REPORT

In this Report we get all detail PROJECT for which item is purchased for project and how many time items purchased and its total purchase rate and total tax amount on that item on using this info we calculate AVERAGE purchase amount for particular ITEM.



SELECT pvs.vendor_site_code dff_project, pol.item_number item_code, a.unit_meas_lookup_code unit, -- nvl(pvs.vendor_site_code_alt,pvs.vendor_site_code) project, SUM(round(a.quantity - nvl(a.quantity_cancelled, 0))) quantity, SUM(pol.unit_price) unit_price_sum, SUM(tax_amt) tax_amt, COUNT(pol.item_number) purchase_time FROM po_headers_all poh, -- jai_po_taxes b, po_line_locations_inq_v a, po_lines_v pol, -- hr_org_units_no_join hrou, po_vendor_sites_all pvs, po_vendors pov, ( SELECT SUM(b.tax_amount) tax_amt, b.po_line_id po_line_id FROM jai_po_taxes b GROUP BY b.po_header_id, b.po_line_id ) c WHERE poh.org_id = a.owning_org_id AND poh.vendor_site_id = a.vendor_site_id AND a.po_line_id = c.po_line_id (+)--------------------------------------------- --AND pvs.vendor_site_code_alt=:project_name AND pvs.vendor_site_code = :p_vendor_site AND round(a.quantity - nvl(a.quantity_cancelled, 0)) <> 0 AND pol.item_number IS NOT NULL --AND pol.item_number IN ( '0986' ) -- and b.PO_LINE_ID(+)=a.PO_LINE_ID --------------------------------------------- AND a.po_num = poh.segment1 -- AND poh.attribute1 IS NOT NULL -- AND a.currency_code = :p_curr AND poh.po_header_id = pol.po_header_id AND pol.po_line_id = a.po_line_id -- AND hrou.organization_id = a.ship_to_organization_id AND pvs.vendor_site_id = poh.vendor_site_id AND pov.vendor_id = poh.vendor_id--------------------------------------------- GROUP BY pvs.vendor_site_code, --a.item_id, pol.item_number, a.unit_meas_lookup_code -----------------------------------------------------------------------------------------------

PO Detail Report Query

PO DETAIL REPORT( DFF)- Excel:-

SELECT

    ppf.first_name

    || '  '

    || ppf.last_name                                                                              buyer,

    a.authorization_status,

    a.match_option                                                                                invoice_match,

    a.closed_code                                                                                 closet_status,

    a.bill_to_location,

    mca.segment1

    || '.'

    || mca.segment2                                                                               catgory,

    a.promised_date,

    pvs.vendor_site_code                                                                          dff_project,

    a.ship_to_organization                                                                        shipto_org,

    nvl(SUM(b.tax_amount), 0)                                                                   totaltax,

    trunc((a.price_override + nvl((SUM(b.tax_amount) / a.quantity), 0)), 2)                rate,

    trunc((a.price_override + nvl((SUM(b.tax_amount) / a.quantity), 0)) *(a.quantity - nvl(a.quantity_cancelled, 0)),

          2)                                                                                     amount,

        --Billed Amount--g-

    trunc(decode(a.quantity_billed,

                 0,

                 0,

                 NULL,

                 0,

                 (a.price_override + nvl((SUM(b.tax_amount) / a.quantity), 0)) * a.quantity_billed),

          2)                                                                                     billed_amt,

    trunc(decode(a.quantity_received,

                 0,

                 0,

                 NULL,

                 0,

                 (a.price_override + nvl((SUM(b.tax_amount) / a.quantity), 0)) * a.quantity_received),

          2)                                                                                     received_amt,

    a.price_override,

    a.po_num,

    pvs.vendor_site_code_alt                                                                      project,

    to_date(a.order_date)                                                                        po_date,

    a.ship_to_location                                                                            shtoloc,

    a.vendor_name                                                                                 vendor_name,

    COUNT(b.tax_line_no),

    mca.segment1,

    pol.item_number                                                                               item_code,

    a.item_description,

    round(a.quantity - nvl(a.quantity_cancelled, 0))                                            quantity,

    a.quantity_cancelled,

    round(a.quantity_received)                                                                   quantity_received,

    round(a.quantity_billed)                                                                     quantity_billed,

    a.item_id,

    a.revision_num,

    a.unit_meas_lookup_code                                                                       unit,

    a.currency_code,

    a.agent_id,

    a.line_location_id,

    a.ship_to_organization_id,

    a.po_header_id,

    poh.attribute14                                                                               contract_type

FROM

    jai_po_taxes             b,

    po_headers_all           poh,

    po_line_locations_inq_v  a,

    po_lines_v               pol,

    hr_org_units_no_join     hrou,

    per_people_f             ppf,

    po_vendor_sites_all      pvs,

    po_vendors               pov,

    mtl_categories           mca

WHERE

        poh.org_id = a.owning_org_id

    AND b.line_location_id (+) = a.line_location_id

    AND poh.vendor_site_id = a.vendor_site_id

    AND a.po_num = poh.segment1

    --AND poh.attribute1 IS NOT NULL

    AND a.currency_code = 'INR'--:p_curr

    AND poh.po_header_id = pol.po_header_id

    AND b.po_header_id (+) = a.po_header_id

    AND b.po_line_id (+) = a.po_line_id

    AND pol.po_line_id = a.po_line_id

    AND hrou.organization_id = a.ship_to_organization_id

    AND ppf.person_id (+) = a.agent_id

     --AND ppf.employee_number IS NOT NULL

    AND trunc(sysdate) BETWEEN ppf.effective_start_date AND ppf.effective_end_date

    AND pvs.vendor_site_id = poh.vendor_site_id

    AND pov.vendor_id = poh.vendor_id

    AND mca.category_id = a.category_id

    AND nvl(poh.attribute14, 1) = nvl(:contract_type, nvl(poh.attribute14, 1)) 

        -- AND poh.attribute1 BETWEEN NVL (:from_site, poh.attribute1)

        -- AND NVL (:to_site, poh.attribute1)

    AND pvs.vendor_site_code BETWEEN nvl(:from_site, pvs.vendor_site_code) AND nvl(:to_site, pvs.vendor_site_code)

    AND a.order_date BETWEEN nvl(:from_date, a.order_date) AND nvl(:p_to_date, sysdate)

    AND a.vendor_name BETWEEN nvl(:from_vendor, a.vendor_name) AND nvl(:to_vendor, a.vendor_name)

    AND ( :from_vendor IS NULL

          OR pov.vendor_name >= :from_vendor )

    AND ( :to_vendor IS NULL

          OR pov.vendor_name <= :to_vendor )  

    AND ( decode(nvl(:p_item_flag, 'Y'), 'N', 'N') = 'N'

          AND pol.item_number IS NULL

          OR decode(nvl(:p_item_flag, 'Y'), 'Y', 'Y') = 'Y'

          AND pol.item_number BETWEEN nvl(:from_item, pol.item_number) AND nvl(:to_item, pol.item_number) )  

    AND poh.attribute2 = decode(nvl(:p_item_flag, 'Y'), 'Y', 'POL-Local', 'Work Order')

    AND mca.segment1 BETWEEN nvl(:p_category_from, mca.segment1) AND nvl(:p_category_to, mca.segment1)

    AND mca.segment2 BETWEEN nvl(:p_subcategory_from, mca.segment2) AND nvl(:p_subcategory_to, mca.segment2)

    AND a.ship_to_organization = nvl(:p_shpto_org, a.ship_to_organization)

    AND a.ship_to_location_id = nvl(:p_shp_loc, a.ship_to_location_id)

    AND ppf.person_id = nvl(:p_buyer, ppf.person_id)    

GROUP BY

    a.item_id,

    a.price_override,

    a.quantity,

    a.po_num,

    a.order_date,

    a.ship_to_location,

    a.vendor_name,

    pvs.vendor_site_code,

    mca.segment1,

    pol.item_number,

    a.item_description,

    a.item_id,

    a.unit_meas_lookup_code,

    a.quantity,

    a.quantity_cancelled,

    a.currency_code,

    pvs.vendor_site_code_alt,

    a.agent_id,

    a.line_location_id,

    a.ship_to_organization_id,

    a.po_header_id,

    a.quantity_received,

    a.quantity_billed,

    a.revision_num,

    ppf.first_name,

    ppf.last_name,

    a.authorization_status,

    a.match_option,

    a.closed_code,

    a.bill_to_location,

    mca.segment1,

    mca.segment2,

    a.promised_date,

    poh.attribute1,

    a.ship_to_organization,

    poh.attribute14

ORDER BY

    a.po_num