Accounts Payable Trial Balance Report Query

 Report name: Accounts Payable Trial Balance

The standard summary report for AP Trial Balance is based on below query.

SELECT

summary_dat.definition_code,

summary_dat.ledger_id,

gl.name,

gl.short_name,

gl.currency_code,

''SUMMARY'',

summary_dat.source_application_id,

summary_dat.code_combination_id,

decode(gl.ledger_category_code,''PRIMARY'',summary_dat.SUM_acctd_unrounded_orig_amt ,0),

decode(gl.ledger_category_code,''PRIMARY'',summary_dat.SUM_acctd_rounded_orig_amt,0),

summary_dat.sum_entd_unrounded_rem_amount,

summary_dat.sum_entd_rounded_rem_amount,

summary_dat.sum_acctd_unrounded_rem_amount,

summary_dat.sum_acctd_rounded_rem_amount,

summary_dat.party_name,

summary_dat.party_id,

summary_dat.balancing_segment_value,

summary_dat.natural_account_segment_value,

summary_dat.cost_center_segment_value,

summary_dat.intercompany_segment_value,

summary_dat.management_segment_value,

gl.currency_code

FROM

    (

        SELECT

  tb.definition_code,

  tb.ledger_id,

  tb.source_application_id,

  tb.code_combination_id,

  SUM(tb.entered_unrounded_rem_amount) SUM_ENTD_UNROUNDED_REM_AMOUNT,

  SUM(tb.entered_rounded_rem_amount) SUM_entd_rounded_rem_amount ,

  SUM(tb.acctd_unrounded_rem_amount) SUM_acctd_unrounded_rem_amount ,

  SUM(tb.acctd_rounded_rem_amount) SUM_acctd_rounded_rem_amount ,

  SUM(nvl(tiv.base_amount,tiv.invoice_amount)) SUM_acctd_unrounded_orig_amt,

  SUM(nvl(tiv.base_amount,tiv.invoice_amount)) SUM_acctd_rounded_orig_amt,

  tiv.party_name,

  tb.party_id,

  tb.balancing_segment_value,

  tb.natural_account_segment_value,

  tb.cost_center_segment_value,

  tb.intercompany_segment_value,

  tb.management_segment_value

 FROM

  AP_SLA_INVOICES_TRANSACTION_V tiv,

  xla_transaction_entities xte,

  -- inline view

  ( SELECT /*+ parallel(xtb) leading(xtb) NO_MERGE */  --added hint bug#8409806 bug9133956

  xtb.definition_code,

  nvl(xtb.applied_to_entity_id,xtb.source_entity_id) entity_id,

  xtb.code_combination_id ,

  xtb.source_application_id,

  SUM (Nvl(xtb.entered_unrounded_cr,0)) -  SUM (Nvl(xtb.entered_unrounded_dr,0)) entered_unrounded_rem_amount,

  SUM (Nvl(xtb.entered_rounded_cr,0)) -  SUM (Nvl(xtb.entered_rounded_dr,0)) entered_rounded_rem_amount,

  SUM (Nvl(xtb.acctd_unrounded_cr,0)) -  SUM (Nvl(xtb.acctd_unrounded_dr,0)) acctd_unrounded_rem_amount,

  SUM (Nvl(xtb.acctd_rounded_cr,0)) -  SUM (Nvl(xtb.acctd_rounded_dr,0)) acctd_rounded_rem_amount,

  xtb.ledger_id,

  xtb.party_id,

  xtb.balancing_segment_value,

  xtb.natural_account_segment_value,

  xtb.cost_center_segment_value,

  xtb.intercompany_segment_value,

  xtb.management_segment_value

  FROM     xla_trial_balances xtb

  where    xtb.definition_code = :1

    and xtb.source_application_id=200

    and xtb.gl_date between :2 and :3

    AND NVL(xtb.party_id,-99)    = NVL(:4,NVL(xtb.party_id,-99))

      GROUP BY  xtb.definition_code,

    nvl(xtb.applied_to_entity_id,xtb.source_entity_id) ,

    xtb.code_combination_id ,

    xtb.source_application_id,

    xtb.ledger_id,

    xtb.party_id,

    xtb.balancing_segment_value,

    xtb.natural_account_segment_value,

    xtb.cost_center_segment_value,

    xtb.intercompany_segment_value,

    xtb.management_segment_value

     HAVING SUM (Nvl(xtb.acctd_rounded_cr,0)) <> SUM (Nvl(xtb.acctd_rounded_dr,0))

  ) tb

  --end of inline view

 WHERE tb.entity_id=xte.entity_id

 AND tb.source_application_id=200

 AND xte.entity_code=''AP_INVOICES''

 AND xte.application_id=tb.source_application_id

 AND nvl(xte.source_id_int_1,-99)=tiv.invoice_id

 GROUP BY

  tb.definition_code, tb.ledger_id, tb.source_application_id,

  tb.code_combination_id, tiv.party_name,tb.party_id,

  tb.balancing_segment_value, tb.natural_account_segment_value,

  tb.cost_center_segment_value, tb.intercompany_segment_value,

  tb.management_segment_value

    ) summary_dat ,

   gl_ledgers gl

 WHERE summary_dat.ledger_id=gl.ledger_id

Script to get the Password of user account

 You can use below script to get the existing password of Oracle EBS user account.

SELECT usertable.user_name ,

(SELECT get_pwd.decrypt (UPPER (

(SELECT

(SELECT get_pwd.decrypt (UPPER (

(SELECT UPPER (FND_WEB_SEC.GET_GUEST_USERNAME_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_WEB_SEC.GET_GUEST_USERNAME_PWD,1, INSTR (FND_WEB_SEC.GET_GUEST_USERNAME_PWD, '/')- 1)

FROM DUAL

))

)),usertable.encrypted_user_password)

FROM DUAL

) AS encrypted_user_password

FROM fnd_user usertable

WHERE usertable.user_name LIKE UPPER (<Insert username here>);

Script to reset Oracle Apps User Password

You can use below script to reset user password.

SET serveroutput ON;

DECLARE

  v_user_name    VARCHAR2(30):= UPPER(<Insert user name here>);

  v_new_password VARCHAR2(30):= 'Welcome1'; -- give preferred password here

  v_status       BOOLEAN;

BEGIN

  v_status   := fnd_user_pkg.ChangePassword ( username => v_user_name,

                                              newpassword => v_new_password

                                            );

  IF v_status =TRUE THEN

    dbms_output.put_line ('The password reset successfully for the User:'||v_user_name);

    COMMIT;

  ELSE

    DBMS_OUTPUT.put_line ('Unable to reset password due to'||SQLCODE||' '||SUBSTR(SQLERRM, 1, 100));

    ROLLBACK;

  END IF;

END;

Note:

This script do not work in case if user account is locked.