Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Wednesday, 12 December 2018

Query to get AR Invoice and Receipt Details in Oracle Apps R12

0 comments
SELECT DISTINCT
  PARTY_NAME PAYER_NAME,
  HZP.PARTY_ID PAYER_ID,
  ACCOUNT_NUMBER,
  (
    SELECT
      MAX(APPLY_DATE)
    FROM
      AR_RECEIVABLE_APPLICATIONS_ALL ARAA
    WHERE
      ARAA.APPLIED_CUSTOMER_TRX_ID =CT.CUSTOMER_TRX_ID
  )
  PAID_DATE,
  (
    SELECT
      SUM(AMOUNT_APPLIED)
    FROM
      AR_RECEIVABLE_APPLICATIONS_ALL ARAA
    WHERE
      ARAA.APPLIED_CUSTOMER_TRX_ID =CT.CUSTOMER_TRX_ID
  )
  PAYMENT_AMOUNT,
  CT.TRX_NUMBER INVOICE_NUMBER,
  CT.TRX_DATE INVOICE_DATE,
  (
    SELECT
      SUM (EXTENDED_AMOUNT)
    FROM
      RA_CUSTOMER_TRX_LINES_ALL
    WHERE
      CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID
    AND LINE_TYPE     = 'TAX'
  )
  TAX_AMOUNT,
  (
    SELECT
      SUM (EXTENDED_AMOUNT)
    FROM
      RA_CUSTOMER_TRX_LINES_ALL
    WHERE
      CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID
    AND LINE_TYPE     = 'LINE'
  )
  INVOICE_AMOUNT,
  PS_INV.AMOUNT_DUE_ORIGINAL,
  ACTLA.LINE_NUMBER,
  ACTLA.DESCRIPTION PRODUCT_PROGRAM_TYPE ,
  ACTLA.QUANTITY_INVOICED INVOICE_QUANTITY ,
  (
    SELECT DISTINCT
      PRIMARY_UNIT_OF_MEASURE
    FROM
      MTL_SYSTEM_ITEMS_B
    WHERE
      PRIMARY_UOM_CODE=ACTLA.UOM_CODE
  )
  UNIT_OF_MEASURE ,
  ACTLA.EXTENDED_AMOUNT CHARGED_LINE_AMOUNT ,
  (
    SELECT
      SUM(AMOUNT)
    FROM
      AR_ACTIVITY_DETAILS AAD
    WHERE
      AAD.CUSTOMER_TRX_LINE_ID=ACTLA.CUSTOMER_TRX_LINE_ID
  )
  PAID_LINE_AMOUNT
FROM
  RA_CUSTOMER_TRX_ALL CT,
  RA_CUSTOMER_TRX_LINES_ALL ACTLA,
  AR_PAYMENT_SCHEDULES_ALL PS_INV,
  HZ_PARTIES HZP,
  HZ_CUST_ACCOUNTS HCA
WHERE
  CT.CUSTOMER_TRX_ID        =ACTLA.CUSTOMER_TRX_ID
AND CT.CUSTOMER_TRX_ID      = PS_INV.CUSTOMER_TRX_ID(+)
AND HZP.PARTY_ID            = HCA.PARTY_ID
AND HCA.CUST_ACCOUNT_ID     = CT.BILL_TO_CUSTOMER_ID
AND ACTLA.LINE_TYPE         = 'LINE'
ORDER BY
  CT.TRX_NUMBER,

  ACTLA.LINE_NUMBER;
Continue reading >>

Friday, 12 October 2018

Oracle PLSQL Dynamic SQL

0 comments



Binding In PL\SQL
Oracle needs values for any variables listed in the statement

Example:
select empno,ename from emp where id=emp_id;

Oracle needs a value for emp_id. The process of obtaining these values is called binding variables.
Compiler assigns storage addresses to program variables that will hold Oracle data so that Oracle can look up the addresses at run time.

Static Binding
Binding at compile time, called static or early binding.
Increases efficiency

Dynamic Binding
Binding at run time, called dynamic or late binding.
Increases flexibility

Issue Using Directly DDL in PLSQL

For Example:

CREATE PROCEDURE create_table AS
BEGIN
CREATE TABLE dept (deptno NUMBER(2), ...); -- illegal Table is undefined until the procedure is executed at run time.
END;
CREATE PROCEDURE drop_table (table_name IN VARCHAR2) AS
BEGIN
DROP TABLE table_name; -- illegal Table name is unknown until the procedure is executed.
...
END;

DDL Can’t Use in PL\SQL Directly



Data definition language (DDL) statements, such as CREATE, DROP, GRANT, and REVOKE.

Session control language (SCL) statements, such as ALTER SESSION and SET ROLE.



BEGIN
CREATE TABLE dept_temp (deptno NUMBER(2), dname VARCHAR2(30),loc VARCHAR2(30));
END;

IF you use DDL directly you will get following error

ORA-06550: line 2, column 1:
PLS-00103: Encountered the symbol "CREATE" when expecting one of the following



Using DDL in PL/SQL by using Dynamic SQL


BEGIN

Execute immediate 'CREATE TABLE dept_temp (deptno NUMBER(2), dname VARCHAR2(30),loc VARCHAR2(30))';
END;


 




Continue reading >>