Oracle E-Business Suite: 2019

Wednesday, November 20, 2019

OAF Customization

package oracle.apps.eam.workorder.webui;

import java.sql.Timestamp;
import java.util.Calendar;
import oracle.apps.fnd.common.VersionInfo;
import oracle.apps.fnd.framework.OAApplicationModule;
import oracle.apps.fnd.framework.OAViewObject;
import oracle.apps.fnd.framework.webui.OAPageContext;
import oracle.apps.fnd.framework.webui.beans.OAWebBean;
import oracle.apps.fnd.framework.webui.beans.message.OAMessageChoiceBean;
import oracle.jbo.Row;
import oracle.jbo.ViewObject;

public class XXCreateUpdateWOCO
extends CreateUpdateWOCO
{
public static final String RCS_ID = "$Header: CreateUpdateWOCO.java 120.15.12010000.5 2008/12/31 08:26:12 dsingire ship $";
public static final boolean RCS_IDRECORDED = VersionInfo.recordClassVersion("$Header: CreateUpdateWOCO.java 120.15.12010000.5 2008/12/31 08:26:12 dsingire ship $", "oracle.apps.eam.workorder.webui");

public void processRequest(OAPageContext pageContext, OAWebBean webBean)
{
super.processRequest(pageContext, webBean);

OAMessageChoiceBean priority = (OAMessageChoiceBean)webBean.findChildRecursive("Priority");
pageContext.writeDiagnostics(pageContext, " priority " + priority, 4);
if (priority != null) {
priority.setFireActionForSubmit("prioritySelected", null, null, true);
}
OAApplicationModule rootAM = pageContext.getRootApplicationModule();
OAViewObject createUpdateVO = (OAViewObject)rootAM.findViewObject("WOCreateUpdateVO");
if ((createUpdateVO != null) && (createUpdateVO.getCurrentRow() != null))
{
Row row = createUpdateVO.getCurrentRow();
if ((row.getAttribute("RequestNumber") != null) && (row.getAttribute("InstanceNumber") != null))
{
String sqlQueryStatement = null;
if (row.getAttribute("AssetActivity") == null) {
sqlQueryStatement = "SELECT xxasas_updatewo_pkg.get_customer_name ( '" + row.getAttribute("InstanceNumber") + "' )" + " ,xxasas_updatewo_pkg.get_customer_number ( '" + row.getAttribute("InstanceNumber") + "' )" + " ,xxasas_updatewo_pkg.get_contract_type ( '" + row.getAttribute("InstanceNumber") + "')" + " ,xxasas_updatewo_pkg.get_contract_supplier ( '" + row.getAttribute("InstanceNumber") + "')" + " FROM DUAL ";
} else {
sqlQueryStatement = "SELECT xxasas_updatewo_pkg.get_customer_name ( '" + row.getAttribute("InstanceNumber") + "' )" + " ,xxasas_updatewo_pkg.get_customer_number ( '" + row.getAttribute("InstanceNumber") + "' )" + " ,xxasas_updatewo_pkg.get_contract_type ( '" + row.getAttribute("InstanceNumber") + "' ,'" + row.getAttribute("AssetActivity") + "')" + " ,xxasas_updatewo_pkg.get_contract_supplier ( '" + row.getAttribute("InstanceNumber") + "' ,'" + row.getAttribute("AssetActivity") + "')" + " FROM DUAL ";
}
if (rootAM.findViewObject("XXGetCustDtlsVO") != null) {
rootAM.findViewObject("XXGetCustDtlsVO").remove();
}
ViewObject getCustDtlsVO = rootAM.createViewObjectFromQueryStmt("XXGetCustDtlsVO", sqlQueryStatement);
getCustDtlsVO.executeQuery();

pageContext.writeDiagnostics(pageContext, " getRowCount " + getCustDtlsVO.getRowCount(), 4);
if (getCustDtlsVO.first() != null)
{
row.setAttribute("Attribute1", getCustDtlsVO.first().getAttribute(0));
row.setAttribute("Attribute2", getCustDtlsVO.first().getAttribute(1));
row.setAttribute("Attribute3", getCustDtlsVO.first().getAttribute(2));
row.setAttribute("Attribute4", getCustDtlsVO.first().getAttribute(3));
}
}
}
}

public void processFormRequest(OAPageContext pageContext, OAWebBean webBean)
{
super.processFormRequest(pageContext, webBean);
pageContext.writeDiagnostics(pageContext, " LovInputSourceId " + pageContext.getLovInputSourceId(), 4);
OAApplicationModule rootAM = pageContext.getRootApplicationModule();
OAViewObject createUpdateVO = (OAViewObject)rootAM.findViewObject("WOCreateUpdateVO");
if ("prioritySelected".equals(pageContext.getParameter("event")))
{
if ((createUpdateVO != null) && (createUpdateVO.getCurrentRow() != null))
{
oracle.jbo.domain.Date startDate = (oracle.jbo.domain.Date)createUpdateVO.getCurrentRow().getAttribute("ScheduledStartDate");
pageContext.writeDiagnostics(pageContext, " startDate " + startDate.timestampValue(), 4);
pageContext.writeDiagnostics(pageContext, " priority " + createUpdateVO.getCurrentRow().getAttribute("Priority"), 4);

Calendar cal = Calendar.getInstance();
cal.setTime(new java.util.Date(startDate.timestampValue().getTime()));
String sqlQueryStatement = "SELECT DESCRIPTION FROM fnd_lookup_values_vl WHERE lookup_code = " + createUpdateVO.getCurrentRow().getAttribute("Priority") + " AND lookup_type = 'WIP_EAM_ACTIVITY_PRIORITY' ";
if (rootAM.findViewObject("XXGetSCDforPriorityVO") != null) {
rootAM.findViewObject("XXGetSCDforPriorityVO").remove();
}
ViewObject getTimeVO = rootAM.createViewObjectFromQueryStmt("XXGetSCDforPriorityVO", sqlQueryStatement);
getTimeVO.executeQuery();

pageContext.writeDiagnostics(pageContext, " getRowCount " + getTimeVO.getRowCount(), 4);
if (getTimeVO.first() != null) {
cal.add(10, Integer.parseInt(getTimeVO.first().getAttribute(0) + ""));
}
pageContext.writeDiagnostics(pageContext, " endDate " + cal.getTime(), 4);

oracle.jbo.domain.Date newDate = new oracle.jbo.domain.Date(new Timestamp(cal.getTime().getTime()));
createUpdateVO.getCurrentRow().setAttribute("ScheduledCompletionDate", newDate);

pageContext.writeDiagnostics(pageContext, " newDate " + newDate, 4);

pageContext.writeDiagnostics(pageContext, " SCD " + createUpdateVO.getCurrentRow().getAttribute("ScheduledCompletionDate"), 4);
}
}
else if ((pageContext.isLovEvent()) && ("AssetNumber".equals(pageContext.getLovInputSourceId())))
{
if ((createUpdateVO != null) && (createUpdateVO.getCurrentRow() != null))
{
Row row = createUpdateVO.getCurrentRow();
pageContext.writeDiagnostics(pageContext, " InstanceNumber " + createUpdateVO.getCurrentRow().getAttribute("InstanceNumber"), 4);

String sqlQueryStatement = null;
if (row.getAttribute("AssetActivity") == null) {
sqlQueryStatement = "SELECT xxasas_updatewo_pkg.get_customer_name ( '" + row.getAttribute("InstanceNumber") + "' )" + " ,xxasas_updatewo_pkg.get_customer_number ( '" + row.getAttribute("InstanceNumber") + "' )" + " ,xxasas_updatewo_pkg.get_contract_type ( '" + row.getAttribute("InstanceNumber") + "')" + " ,xxasas_updatewo_pkg.get_contract_supplier ( '" + row.getAttribute("InstanceNumber") + "')" + " FROM DUAL ";
} else {
sqlQueryStatement = "SELECT xxasas_updatewo_pkg.get_customer_name ( '" + row.getAttribute("InstanceNumber") + "' )" + " ,xxasas_updatewo_pkg.get_customer_number ( '" + row.getAttribute("InstanceNumber") + "' )" + " ,xxasas_updatewo_pkg.get_contract_type ( '" + row.getAttribute("InstanceNumber") + "' ,'" + row.getAttribute("AssetActivity") + "')" + " ,xxasas_updatewo_pkg.get_contract_supplier ( '" + row.getAttribute("InstanceNumber") + "' ,'" + row.getAttribute("AssetActivity") + "')" + " FROM DUAL ";
}
if (rootAM.findViewObject("XXGetCustDtlsVO") != null) {
rootAM.findViewObject("XXGetCustDtlsVO").remove();
}
ViewObject getCustDtlsVO = rootAM.createViewObjectFromQueryStmt("XXGetCustDtlsVO", sqlQueryStatement);
getCustDtlsVO.executeQuery();

pageContext.writeDiagnostics(pageContext, " getRowCount " + getCustDtlsVO.getRowCount(), 4);
if (getCustDtlsVO.first() != null)
{
row.setAttribute("Attribute1", getCustDtlsVO.first().getAttribute(0));
row.setAttribute("Attribute2", getCustDtlsVO.first().getAttribute(1));
row.setAttribute("Attribute3", getCustDtlsVO.first().getAttribute(2));
row.setAttribute("Attribute4", getCustDtlsVO.first().getAttribute(3));
}
}
}
else if ((pageContext.isLovEvent()) && ("Activity".equals(pageContext.getLovInputSourceId()))) {
if ((createUpdateVO != null) && (createUpdateVO.getCurrentRow() != null))
{
Row row = createUpdateVO.getCurrentRow();
pageContext.writeDiagnostics(pageContext, " InstanceNumber " + createUpdateVO.getCurrentRow().getAttribute("InstanceNumber"), 4);
pageContext.writeDiagnostics(pageContext, " AssetActivity " + createUpdateVO.getCurrentRow().getAttribute("AssetActivity"), 4);

String sqlQueryStatement = null;
if (row.getAttribute("AssetActivity") != null) {
sqlQueryStatement = "SELECT xxasas_updatewo_pkg.get_contract_type ( '" + row.getAttribute("InstanceNumber") + "' ,'" + row.getAttribute("AssetActivity") + "')" + " ,xxasas_updatewo_pkg.get_contract_supplier ( '" + row.getAttribute("InstanceNumber") + "' ,'" + row.getAttribute("AssetActivity") + "')" + " FROM DUAL ";
} else {
sqlQueryStatement = "SELECT xxasas_updatewo_pkg.get_contract_type ( '" + row.getAttribute("InstanceNumber") + "' )" + " ,xxasas_updatewo_pkg.get_contract_supplier ( '" + row.getAttribute("InstanceNumber") + "' )" + " FROM DUAL ";
}
if (rootAM.findViewObject("XXGetCustDtlsVO") != null) {
rootAM.findViewObject("XXGetCustDtlsVO").remove();
}
ViewObject getCustDtlsVO = rootAM.createViewObjectFromQueryStmt("XXGetCustDtlsVO", sqlQueryStatement);
getCustDtlsVO.executeQuery();

pageContext.writeDiagnostics(pageContext, " getRowCount " + getCustDtlsVO.getRowCount(), 4);
if (getCustDtlsVO.first() != null)
{
row.setAttribute("Attribute3", getCustDtlsVO.first().getAttribute(0));
row.setAttribute("Attribute4", getCustDtlsVO.first().getAttribute(1));
}
}
}
}
}

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

package oracle.apps.eam.workrequest.webui;

import oracle.apps.fnd.common.VersionInfo;
import oracle.apps.fnd.framework.OAApplicationModule;
import oracle.apps.fnd.framework.OAViewObject;
import oracle.apps.fnd.framework.webui.OAPageContext;
import oracle.apps.fnd.framework.webui.beans.OAWebBean;
import oracle.jbo.Row;
import oracle.jbo.ViewObject;

public class XXWorkRequestDffCO
extends WorkRequestDffCO
{
public static final String RCS_ID = "$Header: WorkRequestDffCO.java 120.0.12010000.2 2009/05/27 13:18:56 somitra ship $";
public static final boolean RCS_ID_RECORDED = VersionInfo.recordClassVersion("$Header: WorkRequestDffCO.java 120.0.12010000.2 2009/05/27 13:18:56 somitra ship $", "oracle.apps.eam.workrequest.webui");

public void processRequest(OAPageContext pageContext, OAWebBean webBean)
{
super.processRequest(pageContext, webBean);
}

public void processFormRequest(OAPageContext pageContext, OAWebBean webBean)
{
super.processFormRequest(pageContext, webBean);
pageContext.writeDiagnostics(pageContext, " LovInputSourceId " + pageContext.getLovInputSourceId(), 4);
if ((pageContext.isLovEvent()) && ("AssetNumber".equals(pageContext.getLovInputSourceId())))
{
OAApplicationModule rootAM = pageContext.getRootApplicationModule();
OAViewObject requestDetailsVO = (OAViewObject)rootAM.findViewObject("RequestDetailsVO");
pageContext.writeDiagnostics(pageContext, " requestDetailsVO " + requestDetailsVO, 4);
if ((requestDetailsVO != null) && (requestDetailsVO.getCurrentRow() != null))
{
Row row = requestDetailsVO.getCurrentRow();
pageContext.writeDiagnostics(pageContext, " InstanceNumber " + requestDetailsVO.getCurrentRow().getAttribute("InstanceNumber"), 4);

String sqlQueryStatement = "SELECT xxasas_updatewo_pkg.get_customer_name ( '" + row.getAttribute("InstanceNumber") + "' )" + " ,xxasas_updatewo_pkg.get_customer_number ( '" + row.getAttribute("InstanceNumber") + "' )" + " ,xxasas_updatewo_pkg.get_contract_type ( '" + row.getAttribute("InstanceNumber") + "' )" + " ,xxasas_updatewo_pkg.get_contract_supplier ( '" + row.getAttribute("InstanceNumber") + "' )" + " FROM DUAL ";
if (rootAM.findViewObject("XXGetCustDtlsVO") != null) {
rootAM.findViewObject("XXGetCustDtlsVO").remove();
}
ViewObject getCustDtlsVO = rootAM.createViewObjectFromQueryStmt("XXGetCustDtlsVO", sqlQueryStatement);
getCustDtlsVO.executeQuery();

pageContext.writeDiagnostics(pageContext, " getRowCount " + getCustDtlsVO.getRowCount(), 4);
if (getCustDtlsVO.first() != null)
{
row.setAttribute("Attribute1", getCustDtlsVO.first().getAttribute(0));
row.setAttribute("Attribute2", getCustDtlsVO.first().getAttribute(1));
row.setAttribute("Attribute3", getCustDtlsVO.first().getAttribute(2));
row.setAttribute("Attribute4", getCustDtlsVO.first().getAttribute(3));
}
}
}
}
}

Tuesday, June 25, 2019

API TO UPDATE CUSTOMER CONTACTS ON A PROJECT - Projects (pa_customers_contacts_pub)

DECLARE
   p_api_version                 NUMBER;
   p_init_msg_list               VARCHAR2 (200);
   p_commit                      VARCHAR2 (200);
   p_validate_only               VARCHAR2 (200);
   p_validation_level            NUMBER;
   p_calling_module              VARCHAR2 (200);
   p_debug_mode                  VARCHAR2 (200);
   p_max_msg_count               NUMBER;
   p_project_id                  NUMBER;
   p_customer_id                 NUMBER;
   p_bill_ship_customer_id       NUMBER;
   p_contact_id                  NUMBER;
   p_contact_name                VARCHAR2 (200);
   p_project_contact_type_code   VARCHAR2 (200);
   p_project_contact_type_name   VARCHAR2 (200);
   x_return_status               VARCHAR2 (200);
   x_msg_count                   NUMBER;
   x_msg_data                    VARCHAR2 (200);
   CURSOR c1
   IS
      SELECT ppa.project_id, ppcust.customer_id, ppa.project_type, ppcust.bill_to_customer_id, acv.contact_id,
             acv.contact_name names
      FROM   pa_project_customers_v ppcust, pa_projects_all ppa, pa_customer_contact_names_v acv
       WHERE 1 = 1
         AND ppcust.project_id = ppa.project_id
         AND acv.customer_id = ppcust.customer_id
         AND (SELECT COUNT (contact_id)
                FROM pa_customer_contact_names_v ppcn
               WHERE ppcn.customer_id = ppcust.customer_id) = 1
         AND NOT EXISTS (SELECT ppco.contact_id
                       FROM pa_project_contacts ppco
                      WHERE ppco.project_id = ppa.project_id);
BEGIN
-- fnd_global.apps_initialize (0,0 ,0);
   mo_global.init ('AR');
   FOR i IN c1
   LOOP
      BEGIN
         p_api_version := 1.0;
         p_init_msg_list := NULL;
         p_commit := 'FND_API.G_FALSE';
         p_validate_only := 'FND_API.G_TRUE';
         p_validation_level := fnd_api.g_valid_level_full;
         p_calling_module := 'SELF_SERVICE';
         p_debug_mode := 'N';
         p_max_msg_count := fnd_api.g_miss_num;
         p_project_id := i.project_id;
         p_customer_id := i.customer_id;
         p_bill_ship_customer_id := i.bill_to_customer_id;
         p_contact_id := i.contact_id;
         p_contact_name := i.names;
         p_project_contact_type_code := 'BILLING';
         x_return_status := NULL;
         x_msg_count := NULL;
         x_msg_data := NULL;
         apps.pa_customers_contacts_pub.create_customer_contact (p_api_version,
                                                                 p_init_msg_list,
                                                                 p_commit,
                                                                 p_validate_only,
                                                                 p_validation_level,
                                                                 p_calling_module,
                                                                 p_debug_mode,
                                                                 p_max_msg_count,
                                                                 p_project_id,
                                                                 p_customer_id,
                                                                 p_bill_ship_customer_id,
                                                                 p_contact_id,
                                                                 p_contact_name,
                                                                 p_project_contact_type_code,
                                                                 p_project_contact_type_name,
                                                                 x_return_status,
                                                                 x_msg_count,
                                                                 x_msg_data
                                                                );
         DBMS_OUTPUT.put_line (   'Project ID :- '
                               || p_project_id
                               || '    x_return_status :- '
                               || x_return_status
                               || ' x_msg_count :- '
                               || x_msg_count
                               || '   x_msg_data :- '
                               || x_msg_data
                              );
         COMMIT;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line (   'Error : Project ID :- '
                                  || p_project_id
                                  || '    x_return_status :- '
                                  || x_return_status
                                  || ' x_msg_count :- '
                                  || x_msg_count
                                  || '   x_msg_data :- '
                                  || x_msg_data
                                 );
      END;
   END LOOP;
END;

Sunday, June 9, 2019

GL Breakup

GL - AR Breakup
================================

SELECT gjh.name jv_name,
gjh.je_category jv_category,
gjh.je_source jv_source,
gsl.je_line_num,
gjl.period_name period,
TO_CHAR (gsl.accounting_date, 'DD-Mon-YYYY') accounting_date,
NVL (gjl.accounted_cr, 0) - NVL (gjl.accounted_dr, 0) gl_amount,
DECODE (gsl.gl_sl_link_id,
NULL, NVL (gjl.accounted_cr, 0) - NVL (gjl.accounted_dr, 0),
NVL (gsl.accounted_cr, 0) - NVL (gsl.accounted_dr, 0))
sla_amount,
rcta.interface_header_attribute2 plot_number,
rcta.trx_number invoice_number,
NULL adjustiment_number,
NULL receipt_number,
ac.customer_name,
ac.customer_number,
gsl.description
FROM (SELECT /*+ leading(ir,l,h) cardinality(ir,10) */
ir.je_header_id,
ir.je_line_num,
TO_NUMBER (te.source_id_int_1) transaction_id,
l.code_combination_id,
l.accounted_dr,
l.accounted_cr,
l.currency_code,
h.accounting_date,
l.gl_sl_link_id,
l.gl_sl_link_table,
h.period_name,
h.je_category_name,
h.application_id,
h.description
FROM xla.xla_ae_headers h,
xla.xla_ae_lines l,
xla.xla_events e,
xla.xla_transaction_entities te,
apps.gl_import_references ir
WHERE e.entity_id = te.entity_id
AND e.application_id = te.application_id
AND te.application_id = h.application_id
AND te.entity_id = h.entity_id
AND e.event_id = h.event_id
AND e.application_id = h.application_id
AND h.ae_header_id = l.ae_header_id
AND h.application_id = l.application_id
AND l.gl_sl_link_id = ir.gl_sl_link_id
AND l.gl_sl_link_table = ir.gl_sl_link_table) gsl,
apps.gl_je_headers gjh,
apps.gl_je_lines gjl,
apps.ra_customer_trx_all rcta,
apps.ar_customers ac
WHERE gsl.je_header_id(+) = gjl.je_header_id
AND gjl.je_header_id = gjh.je_header_id
AND gjl.je_line_num = gsl.je_line_num(+)
AND gjl.code_combination_id = :p_code_combination_id
AND gjl.period_name LIKE '%' || :p_year
AND gjh.status = 'P' --This will pick all posted journal entry
AND gjh.actual_flag = 'A' -- This is for Actual entry A-Actual, B-Budget
AND rcta.customer_trx_id(+) = transaction_id
AND ac.customer_id(+) = rcta.bill_to_customer_id
AND je_category = 'Sales Invoices'
UNION ALL
SELECT gjh.name jv_name,
gjh.je_category jv_category,
gjh.je_source jv_source,
gsl.je_line_num,
gjl.period_name period,
TO_CHAR (gsl.accounting_date, 'DD-Mon-YYYY') accounting_date,
NVL (gjl.accounted_cr, 0) - NVL (gjl.accounted_dr, 0) gl_amount,
DECODE (gsl.gl_sl_link_id,
NULL, NVL (gjl.accounted_cr, 0) - NVL (gjl.accounted_dr, 0),
NVL (gsl.accounted_cr, 0) - NVL (gsl.accounted_dr, 0))
sla_amount,
rcta.interface_header_attribute2 plot_number,
rcta.trx_number invoice_number,
NULL adjustiment_number,
NULL receipt_number,
ac.customer_name,
ac.customer_number,
gsl.description
FROM (SELECT /*+ leading(ir,l,h) cardinality(ir,10) */
ir.je_header_id,
ir.je_line_num,
TO_NUMBER (te.source_id_int_1) transaction_id,
l.code_combination_id,
l.accounted_dr,
l.accounted_cr,
l.currency_code,
h.accounting_date,
l.gl_sl_link_id,
l.gl_sl_link_table,
h.period_name,
h.je_category_name,
h.application_id,
h.description
FROM xla.xla_ae_headers h,
xla.xla_ae_lines l,
xla.xla_events e,
xla.xla_transaction_entities te,
apps.gl_import_references ir
WHERE e.entity_id = te.entity_id
AND e.application_id = te.application_id
AND te.application_id = h.application_id
AND te.entity_id = h.entity_id
AND e.event_id = h.event_id
AND e.application_id = h.application_id
AND h.ae_header_id = l.ae_header_id
AND h.application_id = l.application_id
AND l.gl_sl_link_id = ir.gl_sl_link_id
AND l.gl_sl_link_table = ir.gl_sl_link_table) gsl,
apps.gl_je_headers gjh,
apps.gl_je_lines gjl,
apps.ra_customer_trx_all rcta,
apps.ar_customers ac
WHERE gsl.je_header_id(+) = gjl.je_header_id
AND gjl.je_header_id = gjh.je_header_id
AND gjl.je_line_num = gsl.je_line_num(+)
AND gjl.code_combination_id = :p_code_combination_id
AND gjl.period_name LIKE '%' || :p_year
AND gjh.status = 'P' --This will pick all posted journal entry
AND gjh.actual_flag = 'A' -- This is for Actual entry A-Actual, B-Budget
AND rcta.customer_trx_id(+) = transaction_id
AND ac.customer_id(+) = rcta.bill_to_customer_id
AND je_category = 'Credit Memos'
UNION ALL
SELECT gjh.name jv_name,
gjh.je_category jv_category,
gjh.je_source jv_source,
gsl.je_line_num,
gjl.period_name period,
TO_CHAR (gsl.accounting_date, 'DD-Mon-YYYY') accounting_date,
NVL (gjl.accounted_cr, 0) - NVL (gjl.accounted_dr, 0) gl_amount,
DECODE (gsl.gl_sl_link_id,
NULL, NVL (gjl.accounted_cr, 0) - NVL (gjl.accounted_dr, 0),
NVL (gsl.accounted_cr, 0) - NVL (gsl.accounted_dr, 0))
sla_amount,
rcta.interface_header_attribute2 plot_number,
rcta.trx_number invoice_number,
NULL adjustiment_number,
NULL receipt_number,
ac.customer_name,
ac.customer_number,
gsl.description
FROM (SELECT /*+ leading(ir,l,h) cardinality(ir,10) */
ir.je_header_id,
ir.je_line_num,
TO_NUMBER (te.source_id_int_1) transaction_id,
l.code_combination_id,
l.accounted_dr,
l.accounted_cr,
l.currency_code,
h.accounting_date,
l.gl_sl_link_id,
l.gl_sl_link_table,
h.period_name,
h.je_category_name,
h.application_id,
h.description
FROM xla.xla_ae_headers h,
xla.xla_ae_lines l,
xla.xla_events e,
xla.xla_transaction_entities te,
apps.gl_import_references ir
WHERE e.entity_id = te.entity_id
AND e.application_id = te.application_id
AND te.application_id = h.application_id
AND te.entity_id = h.entity_id
AND e.event_id = h.event_id
AND e.application_id = h.application_id
AND h.ae_header_id = l.ae_header_id
AND h.application_id = l.application_id
AND l.gl_sl_link_id = ir.gl_sl_link_id
AND l.gl_sl_link_table = ir.gl_sl_link_table) gsl,
apps.gl_je_headers gjh,
apps.gl_je_lines gjl,
apps.ra_customer_trx_all rcta,
apps.ar_customers ac
WHERE gsl.je_header_id(+) = gjl.je_header_id
AND gjl.je_header_id = gjh.je_header_id
AND gjl.je_line_num = gsl.je_line_num(+)
AND gjl.code_combination_id = :p_code_combination_id
AND gjl.period_name LIKE '%' || :p_year
AND gjh.status = 'P' --This will pick all posted journal entry
AND gjh.actual_flag = 'A' -- This is for Actual entry A-Actual, B-Budget
AND rcta.customer_trx_id(+) = transaction_id
AND ac.customer_id(+) = rcta.bill_to_customer_id
AND je_category = 'Debit Memos'
UNION ALL
SELECT gjh.name jv_name,
gjh.je_category jv_category,
gjh.je_source jv_source,
gsl.je_line_num,
gjl.period_name period,
TO_CHAR (gsl.accounting_date, 'DD-Mon-YYYY') accounting_date,
NVL (gjl.accounted_cr, 0) - NVL (gjl.accounted_dr, 0) gl_amount,
DECODE (gsl.gl_sl_link_id,
NULL, NVL (gjl.accounted_cr, 0) - NVL (gjl.accounted_dr, 0),
NVL (gsl.accounted_cr, 0) - NVL (gsl.accounted_dr, 0))
sla_amount,
rcta.interface_header_attribute2 plot_number,
rcta.trx_number invoice_number,
ADJUSTMENT_NUMBER adjustiment_number,
NULL receipt_number,
ac.customer_name,
ac.customer_number,
gsl.description
FROM (SELECT /*+ leading(ir,l,h) cardinality(ir,10) */
ir.je_header_id,
ir.je_line_num,
TO_NUMBER (te.source_id_int_1) transaction_id,
l.code_combination_id,
l.accounted_dr,
l.accounted_cr,
l.currency_code,
h.accounting_date,
l.gl_sl_link_id,
l.gl_sl_link_table,
h.period_name,
h.je_category_name,
h.application_id,
h.description
FROM xla.xla_ae_headers h,
xla.xla_ae_lines l,
xla.xla_events e,
xla.xla_transaction_entities te,
apps.gl_import_references ir
WHERE e.entity_id = te.entity_id
AND e.application_id = te.application_id
AND te.application_id = h.application_id
AND te.entity_id = h.entity_id
AND e.event_id = h.event_id
AND e.application_id = h.application_id
AND h.ae_header_id = l.ae_header_id
AND h.application_id = l.application_id
AND l.gl_sl_link_id = ir.gl_sl_link_id
AND l.gl_sl_link_table = ir.gl_sl_link_table) gsl,
apps.gl_je_headers gjh,
apps.gl_je_lines gjl,
apps.ar_adjustments_all aaa,
apps.ra_customer_trx_all rcta,
apps.ar_customers ac
WHERE gsl.je_header_id(+) = gjl.je_header_id
AND gjl.je_header_id = gjh.je_header_id
AND gjl.je_line_num = gsl.je_line_num(+)
AND gjl.code_combination_id = :p_code_combination_id
AND gjl.period_name LIKE '%' || :p_year
AND gjh.status = 'P' --This will pick all posted journal entry
AND gjh.actual_flag = 'A' -- This is for Actual entry A-Actual, B-Budget
AND aaa.adjustment_id(+) = transaction_id
AND rcta.customer_trx_id(+) = aaa.customer_trx_id
AND ac.customer_id(+) = rcta.bill_to_customer_id
AND je_category = 'Adjustment'
UNION ALL
SELECT gjh.name jv_name,
gjh.je_category jv_category,
gjh.je_source jv_source,
gsl.je_line_num,
gjl.period_name period,
TO_CHAR (gsl.accounting_date, 'DD-Mon-YYYY') accounting_date,
NVL (gjl.accounted_cr, 0) - NVL (gjl.accounted_dr, 0) gl_amount,
DECODE (gsl.gl_sl_link_id,
NULL, NVL (gjl.accounted_cr, 0) - NVL (gjl.accounted_dr, 0),
NVL (gsl.accounted_cr, 0) - NVL (gsl.accounted_dr, 0))
sla_amount,
(SELECT location_code
FROM apps.pn_locations_all
WHERE TO_CHAR (location_id) = rcta.attribute2)
plot_number,
NULL invoice_number,
NULL adjustiment_number,
rcta.receipt_number receipt_number,
ac.customer_name,
ac.customer_number,
gsl.description
FROM (SELECT /*+ leading(ir,l,h) cardinality(ir,10) */
ir.je_header_id,
ir.je_line_num,
TO_NUMBER (te.source_id_int_1) transaction_id,
l.code_combination_id,
l.accounted_dr,
l.accounted_cr,
l.currency_code,
h.accounting_date,
l.gl_sl_link_id,
l.gl_sl_link_table,
h.period_name,
h.je_category_name,
h.application_id,
h.description
FROM xla.xla_ae_headers h,
xla.xla_ae_lines l,
xla.xla_events e,
xla.xla_transaction_entities te,
apps.gl_import_references ir
WHERE e.entity_id = te.entity_id
AND e.application_id = te.application_id
AND te.application_id = h.application_id
AND te.entity_id = h.entity_id
AND e.event_id = h.event_id
AND e.application_id = h.application_id
AND h.ae_header_id = l.ae_header_id
AND h.application_id = l.application_id
AND l.gl_sl_link_id = ir.gl_sl_link_id
AND l.gl_sl_link_table = ir.gl_sl_link_table) gsl,
apps.gl_je_headers gjh,
apps.gl_je_lines gjl,
apps.ar_cash_receipts_all rcta,
apps.ar_customers ac
WHERE gsl.je_header_id(+) = gjl.je_header_id
AND gjl.je_header_id = gjh.je_header_id
AND gjl.je_line_num = gsl.je_line_num(+)
AND gjl.code_combination_id = :p_code_combination_id
AND gjl.period_name LIKE '%' || :p_year
AND gjh.status = 'P' --This will pick all posted journal entry
AND gjh.actual_flag = 'A' -- This is for Actual entry A-Actual, B-Budget
AND rcta.cash_receipt_id(+) = transaction_id
AND ac.customer_id(+) = rcta.pay_from_customer
AND je_category = 'Receipts'
UNION ALL
SELECT gjh.name jv_name,
gjh.je_category jv_category,
gjh.je_source jv_source,
gsl.je_line_num,
gjl.period_name period,
TO_CHAR (gsl.accounting_date, 'DD-Mon-YYYY') accounting_date,
NVL (gjl.accounted_cr, 0) - NVL (gjl.accounted_dr, 0) gl_amount,
DECODE (gsl.gl_sl_link_id,
NULL, NVL (gjl.accounted_cr, 0) - NVL (gjl.accounted_dr, 0),
NVL (gsl.accounted_cr, 0) - NVL (gsl.accounted_dr, 0))
sla_amount,
NULL plot_number,
NULL invoice_number,
NULL adjustiment_number,
NULL receipt_number,
NULL customer_name,
NULL customer_number,
NULL description
FROM (SELECT /*+ leading(ir,l,h) cardinality(ir,10) */
ir.je_header_id,
ir.je_line_num,
TO_NUMBER (te.source_id_int_1) transaction_id,
l.code_combination_id,
l.accounted_dr,
l.accounted_cr,
l.currency_code,
h.accounting_date,
l.gl_sl_link_id,
l.gl_sl_link_table,
h.period_name,
h.je_category_name,
h.application_id,
h.description
FROM xla.xla_ae_headers h,
xla.xla_ae_lines l,
xla.xla_events e,
xla.xla_transaction_entities te,
apps.gl_import_references ir
WHERE e.entity_id = te.entity_id
AND e.application_id = te.application_id
AND te.application_id = h.application_id
AND te.entity_id = h.entity_id
AND e.event_id = h.event_id
AND e.application_id = h.application_id
AND h.ae_header_id = l.ae_header_id
AND h.application_id = l.application_id
AND l.gl_sl_link_id = ir.gl_sl_link_id
AND l.gl_sl_link_table = ir.gl_sl_link_table) gsl,
apps.gl_je_headers gjh,
apps.gl_je_lines gjl,
apps.ar_cash_receipts_all rcta,
apps.ar_customers ac
WHERE gsl.je_header_id(+) = gjl.je_header_id
AND gjl.je_header_id = gjh.je_header_id
AND gjl.je_line_num = gsl.je_line_num(+)
AND gjl.code_combination_id = :p_code_combination_id
AND gjl.period_name LIKE '%' || :p_year
AND gjh.status = 'P' --This will pick all posted journal entry
AND gjh.actual_flag = 'A' -- This is for Actual entry A-Actual, B-Budget
AND rcta.cash_receipt_id(+) = transaction_id
AND ac.customer_id(+) = rcta.pay_from_customer
AND je_category = '210'

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

Encumbrance Query

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

/* Formatted on 18/09/2017 09:21:20 (QP5 v5.256.13226.35510) */
SELECT 'Requisitions' "GL Entry Type",
prha.creation_date,
prha.segment1 "REQ Number",
TO_CHAR (prha.creation_date, 'DD-Mon-YYYY') "REQ Creation Date",
gsl.description "JV REQ Number",
SUM (
DECODE (
gsl.gl_sl_link_id,
NULL, NVL (gjl.accounted_cr, 0) - NVL (gjl.accounted_dr, 0),
NVL (gsl.accounted_cr, 0) - NVL (gsl.accounted_dr, 0))
* -1)
"Amount"
FROM (SELECT /*+ leading(ir,l,h) cardinality(ir,10) */
ir.je_header_id,
ir.je_line_num,
TO_NUMBER (te.source_id_int_1) transaction_id,
l.code_combination_id,
l.accounted_dr,
l.accounted_cr,
l.currency_code,
h.accounting_date,
l.gl_sl_link_id,
l.gl_sl_link_table,
h.period_name,
h.je_category_name,
h.application_id,
h.description,
e.event_id,
h.ae_header_id,
l.ae_line_num
FROM xla.xla_ae_headers h,
xla.xla_ae_lines l,
xla.xla_events e,
xla.xla_transaction_entities te,
apps.gl_import_references ir
WHERE e.entity_id = te.entity_id
AND e.application_id = te.application_id
AND te.application_id = h.application_id
AND te.entity_id = h.entity_id
AND e.event_id = h.event_id
AND e.application_id = h.application_id
AND h.ae_header_id = l.ae_header_id
AND h.application_id = l.application_id
AND l.gl_sl_link_id = ir.gl_sl_link_id
AND l.gl_sl_link_table = ir.gl_sl_link_table) gsl,
apps.gl_je_headers gjh,
apps.gl_je_lines gjl,
apps.po_requisition_headers_all prha
WHERE gsl.je_header_id(+) = gjl.je_header_id
AND gjl.je_header_id = gjh.je_header_id
AND gjl.je_line_num = gsl.je_line_num(+)
AND gjl.code_combination_id = :p_code_combination_id
AND gjl.period_name LIKE '%' || :p_year
AND gjh.status = 'P' --This will pick all posted journal entry
AND gjh.actual_flag = 'E' -- This is for Actual entry A-Actual, B-Budget
AND je_category = 'Requisitions'
AND prha.requisition_header_id(+) = gsl.transaction_id
GROUP BY prha.segment1, gsl.description, prha.creation_date
HAVING SUM (
DECODE (
gsl.gl_sl_link_id,
NULL, NVL (gjl.accounted_cr, 0) - NVL (gjl.accounted_dr, 0),
NVL (gsl.accounted_cr, 0) - NVL (gsl.accounted_dr, 0))
* -1) <> 0
UNION ALL
SELECT 'Purchases',
prha.creation_date,
prha.segment1 "PO Number",
TO_CHAR (prha.creation_date, 'DD-Mon-YYYY') "PO Creation Date",
gsl.description "JV PO Number",
SUM (
DECODE (
gsl.gl_sl_link_id,
NULL, NVL (gjl.accounted_cr, 0) - NVL (gjl.accounted_dr, 0),
NVL (gsl.accounted_cr, 0) - NVL (gsl.accounted_dr, 0))
* -1)
"Amount"
FROM (SELECT /*+ leading(ir,l,h) cardinality(ir,10) */
ir.je_header_id,
ir.je_line_num,
TO_NUMBER (te.source_id_int_1) transaction_id,
l.code_combination_id,
l.accounted_dr,
l.accounted_cr,
l.currency_code,
h.accounting_date,
l.gl_sl_link_id,
l.gl_sl_link_table,
h.period_name,
h.je_category_name,
h.application_id,
h.description,
e.event_id,
h.ae_header_id,
l.ae_line_num
FROM xla.xla_ae_headers h,
xla.xla_ae_lines l,
xla.xla_events e,
xla.xla_transaction_entities te,
apps.gl_import_references ir
WHERE e.entity_id = te.entity_id
AND e.application_id = te.application_id
AND te.application_id = h.application_id
AND te.entity_id = h.entity_id
AND e.event_id = h.event_id
AND e.application_id = h.application_id
AND h.ae_header_id = l.ae_header_id
AND h.application_id = l.application_id
AND l.gl_sl_link_id = ir.gl_sl_link_id
AND l.gl_sl_link_table = ir.gl_sl_link_table) gsl,
apps.gl_je_headers gjh,
apps.gl_je_lines gjl,
apps.po_headers_all prha
WHERE gsl.je_header_id(+) = gjl.je_header_id
AND gjl.je_header_id = gjh.je_header_id
AND gjl.je_line_num = gsl.je_line_num(+)
AND gjl.code_combination_id = :p_code_combination_id
AND gjl.period_name LIKE '%' || :p_year
AND gjh.status = 'P' --This will pick all posted journal entry
AND gjh.actual_flag = 'E' -- This is for Actual entry A-Actual, B-Budget
AND je_category = 'Purchases'
AND prha.po_header_id(+) = gsl.transaction_id
GROUP BY prha.segment1, gsl.description, prha.creation_date
HAVING SUM (
DECODE (
gsl.gl_sl_link_id,
NULL, NVL (gjl.accounted_cr, 0) - NVL (gjl.accounted_dr, 0),
NVL (gsl.accounted_cr, 0) - NVL (gsl.accounted_dr, 0))
* -1) <> 0
UNION ALL
SELECT 'Receiving',
pha.creation_date,
pha.segment1 "PO Number",
TO_CHAR (pha.creation_date, 'DD-Mon-YYYY') "PO Creation Date",
gsl.description "JV PO Number",
SUM (
DECODE (
gsl.gl_sl_link_id,
NULL, NVL (gjl.accounted_cr, 0) - NVL (gjl.accounted_dr, 0),
NVL (gsl.accounted_cr, 0) - NVL (gsl.accounted_dr, 0))
* -1)
"Amount"
FROM (SELECT /*+ leading(ir,l,h) cardinality(ir,10) */
ir.je_header_id,
ir.je_line_num,
TO_NUMBER (te.source_id_int_1) transaction_id,
l.code_combination_id,
l.accounted_dr,
l.accounted_cr,
l.currency_code,
h.accounting_date,
l.gl_sl_link_id,
l.gl_sl_link_table,
h.period_name,
h.je_category_name,
h.application_id,
h.description,
e.event_id,
h.ae_header_id,
l.ae_line_num,
prha.source_distribution_id_num_1,
TO_NUMBER (rrsl.reference3) po_dist_id
FROM xla.xla_ae_headers h,
xla.xla_ae_lines l,
xla.xla_events e,
xla.xla_transaction_entities te,
apps.gl_import_references ir,
apps.xla_distribution_links prha,
apps.rcv_receiving_sub_ledger rrsl
WHERE e.entity_id = te.entity_id
AND e.application_id = te.application_id
AND te.application_id = h.application_id
AND te.entity_id = h.entity_id
AND e.event_id = h.event_id
AND e.application_id = h.application_id
AND h.ae_header_id = l.ae_header_id
AND h.application_id = l.application_id
AND l.gl_sl_link_id = ir.gl_sl_link_id
AND l.gl_sl_link_table = ir.gl_sl_link_table
AND prha.source_distribution_type = 'RCV_RECEIVING_SUB_LEDGER'
AND prha.ae_header_id = h.ae_header_id
AND prha.ae_line_num = l.ae_line_num
AND rrsl.rcv_sub_ledger_id = prha.source_distribution_id_num_1)
gsl,
apps.gl_je_headers gjh,
apps.gl_je_lines gjl,
apps.po_distributions_all pda,
apps.po_headers_all pha
WHERE gsl.je_header_id(+) = gjl.je_header_id
AND gjl.je_header_id = gjh.je_header_id
AND gjl.je_line_num = gsl.je_line_num(+)
AND gjl.code_combination_id = :p_code_combination_id
AND gjl.period_name LIKE '%' || :p_year
AND gjh.status = 'P' --This will pick all posted journal entry
AND gjh.actual_flag = 'E' -- This is for Actual entry A-Actual, B-Budget
AND je_category = 'Receiving'
AND gsl.po_dist_id = pda.po_distribution_id(+)
AND pda.po_header_id = pha.po_header_id(+)
GROUP BY pha.segment1, gsl.description, pha.creation_date
HAVING SUM (
DECODE (
gsl.gl_sl_link_id,
NULL, NVL (gjl.accounted_cr, 0) - NVL (gjl.accounted_dr, 0),
NVL (gsl.accounted_cr, 0) - NVL (gsl.accounted_dr, 0))
* -1) <> 0
UNION ALL
SELECT 'Purchase Invoices',
prha.creation_date,
prha.invoice_num "Invoice Number",
TO_CHAR (prha.creation_date, 'DD-Mon-YYYY') "Invoice Creation Date",
gsl.description "JV INV Number",
SUM (
DECODE (
gsl.gl_sl_link_id,
NULL, NVL (gjl.accounted_cr, 0) - NVL (gjl.accounted_dr, 0),
NVL (gsl.accounted_cr, 0) - NVL (gsl.accounted_dr, 0))
* -1)
"Amount"
FROM (SELECT /*+ leading(ir,l,h) cardinality(ir,10) */
ir.je_header_id,
ir.je_line_num,
TO_NUMBER (te.source_id_int_1) transaction_id,
l.code_combination_id,
l.accounted_dr,
l.accounted_cr,
l.currency_code,
h.accounting_date,
l.gl_sl_link_id,
l.gl_sl_link_table,
h.period_name,
h.je_category_name,
h.application_id,
h.description,
e.event_id,
h.ae_header_id,
l.ae_line_num
FROM xla.xla_ae_headers h,
xla.xla_ae_lines l,
xla.xla_events e,
xla.xla_transaction_entities te,
apps.gl_import_references ir
WHERE e.entity_id = te.entity_id
AND e.application_id = te.application_id
AND te.application_id = h.application_id
AND te.entity_id = h.entity_id
AND e.event_id = h.event_id
AND e.application_id = h.application_id
AND h.ae_header_id = l.ae_header_id
AND h.application_id = l.application_id
AND l.gl_sl_link_id = ir.gl_sl_link_id
AND l.gl_sl_link_table = ir.gl_sl_link_table) gsl,
apps.gl_je_headers gjh,
apps.gl_je_lines gjl,
apps.ap_invoices_all prha
WHERE gsl.je_header_id(+) = gjl.je_header_id
AND gjl.je_header_id = gjh.je_header_id
AND gjl.je_line_num = gsl.je_line_num(+)
AND gjl.code_combination_id = :p_code_combination_id
AND gjl.period_name LIKE '%' || :p_year
AND gjh.status = 'P' --This will pick all posted journal entry
AND gjh.actual_flag = 'E' -- This is for Actual entry A-Actual, B-Budget
AND je_category = 'Purchase Invoices'
AND prha.invoice_id(+) = gsl.transaction_id
GROUP BY prha.invoice_num, gsl.description, prha.creation_date
HAVING SUM (
DECODE (
gsl.gl_sl_link_id,
NULL, NVL (gjl.accounted_cr, 0) - NVL (gjl.accounted_dr, 0),
NVL (gsl.accounted_cr, 0) - NVL (gsl.accounted_dr, 0))
* -1) <> 0
ORDER BY 1, 2

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

Remove Next Line from Sql
=============================

regexp_replace(acr.comments ,'[[:space:]]',' ')

KEY: 3-28634-01281-67446-26577
MSG: ARABIAN DANISH PAINT COMPANY
KEY: 1-49900-63081-22632-88752
MSG: DUBAI WORLD

Print Negitive amount in Bracket - select to_char(-133133,'9999999999D99PR') from dual

PDC
======================

SELECT ac.customer_name,
ac.customer_number,
acr.receipt_number,
acr.receipt_date,
aps.due_date,
acr.amount,
SUM (NVL (ad.amount_dr, 0) - NVL (ad.amount_cr, 0))
FROM apps.ar_cash_receipts_all acr,
apps.ar_receipt_method_accounts_all arma,
apps.gl_code_combinations_kfv gc,
apps.ar_payment_schedules_all aps,
apps.ar_customers ac,
apps.ar_cash_receipt_history_all crh,
apps.ar_distributions_all ad,
apps.gl_code_combinations_kfv gc2
WHERE acr.org_id = 2965
AND ac.customer_id = acr.pay_from_customer
AND arma.receipt_method_id = acr.receipt_method_id
AND arma.remit_bank_acct_use_id = acr.remit_bank_acct_use_id
AND arma.cash_ccid = gc.code_combination_id
AND aps.cash_receipt_id = acr.cash_receipt_id
AND crh.cash_receipt_id = acr.cash_receipt_id
AND gc.segment1 = '0811'
AND ad.source_table = 'CRH'
AND ad.source_id = crh.cash_receipt_history_id
AND gc2.code_combination_id = ad.code_combination_id
AND gc2.concatenated_segments =
-- AND crh.gl_date <= '30-JUN-2016'
AND crh.gl_posted_date between '01-SEP-2016' AND '30-SEP-2016'
GROUP BY ac.customer_name,
ac.customer_number,
acr.receipt_number,
acr.receipt_date,
aps.due_date,
acr.amount
HAVING SUM (NVL (ad.amount_dr, 0) - NVL (ad.amount_cr, 0)) <> 0

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

AR Aging

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

SELECT a.*,
       CASE WHEN (TRUNC (SYSDATE) - due_date + 1) <= 0 THEN tot ELSE 0 END
          future_payment,
       CASE
          WHEN (TRUNC (SYSDATE) - due_date + 1) BETWEEN 1 AND 30 THEN tot
          ELSE 0
       END
          day_1_30,
       CASE
          WHEN (TRUNC (SYSDATE) - due_date + 1) BETWEEN 31 AND 60 THEN tot
          ELSE 0
       END
          day_31_60,
       CASE
          WHEN (TRUNC (SYSDATE) - due_date + 1) BETWEEN 61 AND 90 THEN tot
          ELSE 0
       END
          day_61_90,
       CASE
          WHEN (TRUNC (SYSDATE) - due_date + 1) BETWEEN 91 AND 180 THEN tot
          ELSE 0
       END
          day_91_180,
       CASE
          WHEN (TRUNC (SYSDATE) - due_date + 1) BETWEEN 181 AND 365 THEN tot
          ELSE 0
       END
          day_181_365,
       CASE WHEN (TRUNC (SYSDATE) - due_date + 1) > 365 THEN tot ELSE 0 END
          day_above_365
FROM ( SELECT ac.customer_name,
                 ac.customer_number,
                 rct.trx_number,
                 rct.trx_date,
                 rct.ct_reference,
                 aps.due_date,
                 aps.amount_due_original,
                 SUM (
                      (aps.amount_due_original)
                    + (NVL (
                          (SELECT SUM (amount)
                             FROM apps.ar_adjustments_all
                            WHERE     customer_trx_id = rct.customer_trx_id --           AND apply_date   < = :p_date
                                  AND gl_date <= TO_DATE ( :p_date)),
                          0))
                    - (NVL (
                          (SELECT SUM (acctd_amount_applied_from)
                             FROM apps.ar_receivable_applications_all
                            WHERE     1 = 1
                                  --                                             apply_date                 < =to_date(:p_date)
                                  AND gl_date <= TO_DATE ( :p_date)
                                  AND application_type = 'CASH'
                                  AND applied_payment_schedule_id =
                                         aps.payment_schedule_id),
                          0))
                    + (NVL (
                          (SELECT SUM (acctd_amount_applied_from)
                             FROM apps.ar_receivable_applications_all
                            WHERE     1 = 1
                                  --                                             apply_date                 < =to_date(:p_date)
                                  AND gl_date <= TO_DATE ( :p_date)
                                  AND application_type = 'CM'
                                  AND customer_trx_id = aps.customer_trx_id),
                          0))
                    - (NVL (
                          (SELECT SUM (acctd_amount_applied_from)
                             FROM apps.ar_receivable_applications_all
                            WHERE     1 = 1
                                  --                                             apply_date                 < =to_date(:p_date)
                                  AND gl_date <= TO_DATE ( :p_date)
                                  AND application_type = 'CM'
                                  AND applied_customer_trx_id =
                                         aps.customer_trx_id),
                          0))                               --             ,0)
                             )
                    tot
            FROM apps.ra_customer_trx_all rct,
                 apps.ra_cust_trx_line_gl_dist_all rctgl,
                 apps.gl_code_combinations_kfv gc,
                 apps.ar_payment_schedules_all aps,
                 apps.ar_customers ac
           WHERE     rct.customer_trx_id = rctgl.customer_trx_id
                 AND ac.customer_id = rct.bill_to_customer_id
                 AND 'Y' =
                        DECODE (rct.invoicing_rule_id,
                                NULL, 'Y',
                                rctgl.account_set_flag)
                 AND rctgl.code_combination_id = gc.code_combination_id
                 AND gc.segment1 = '0811'
                 AND gc.segment3 = '121100'
                 AND rctgl.account_class = 'REC'
                 AND aps.customer_trx_id = rct.customer_trx_id
                 AND rct.org_id = 2965
                 AND aps.gl_date <= :p_date
        GROUP BY ac.customer_name,
                 rct.trx_number,
                 rct.trx_date,
                 rct.ct_reference,
                     ac.customer_number,
                 aps.due_date,
                 aps.amount_due_original
          HAVING SUM (
                      (aps.amount_due_original)
                    + (NVL (
                          (SELECT SUM (amount)
                             FROM apps.ar_adjustments_all
                            WHERE     customer_trx_id = rct.customer_trx_id --           AND apply_date   < = :p_date
                                  AND gl_date <= TO_DATE ( :p_date)),
                          0))
                    - (NVL (
                          (SELECT SUM (acctd_amount_applied_from)
                             FROM apps.ar_receivable_applications_all
                            WHERE     1 = 1
                                  --                                             apply_date                 < =to_date(:p_date)
                                  AND gl_date <= TO_DATE ( :p_date)
                                  AND application_type = 'CASH'
                                  AND applied_payment_schedule_id =
                                         aps.payment_schedule_id),
                          0))
                    + (NVL (
                          (SELECT SUM (acctd_amount_applied_from)
                             FROM apps.ar_receivable_applications_all
                            WHERE     1 = 1
                                  --                                             apply_date                 < =to_date(:p_date)
                                  AND gl_date <= TO_DATE ( :p_date)
                                  AND application_type = 'CM'
                                  AND customer_trx_id = aps.customer_trx_id),
                          0))
                    - (NVL (
                          (SELECT SUM (acctd_amount_applied_from)
                             FROM apps.ar_receivable_applications_all
                            WHERE     1 = 1
                                  --                                             apply_date                 < =to_date(:p_date)
                                  AND gl_date <= TO_DATE ( :p_date)
                                  AND application_type = 'CM'
                                  AND applied_customer_trx_id =
                                         aps.customer_trx_id),
                          0))                               --             ,0)
                             ) <> 0) a

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

AR Revenue

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

SELECT gc.segment1 bu,
         gc.concatenated_segments,
         --gc.segment3 Account,
         rct.trx_number,
         ac.customer_name,
         ac.customer_number,
         pl.lease_num,
         plu.location_code,
         space_type plot_type,
         (SELECT ptta.name
            FROM apps.pn_term_templates_all ptta
           WHERE ptta.term_template_id(+) = ppt.term_template_id)
            payment_term_type,
         gc.segment2,
         gc.segment3 gl_account,
         TO_CHAR ( (rctg.gl_date)) gl_date,
         SUM (rctg.amount) gl_amount
    FROM apps.ra_customer_trx_all rct,
         apps.ra_cust_trx_line_gl_dist_all rctg,
         apps.gl_code_combinations_kfv gc,
         apps.ra_customer_trx_lines_all rctl,
         apps.pn_leases_all pl,
         apps.pn_payment_items_all ppi,
         apps.pn_payment_terms_all ppt,
         apps.xxfin_pn_locations_pub_v plu,
         apps.ar_customers ac
   WHERE     rct.batch_source_id = 24
         AND ac.customer_id = pl.customer_id
         AND ppt.location_id = plu.location_id
         AND rct.customer_trx_id = rctg.customer_trx_id
         AND rctl.customer_trx_id = rct.customer_trx_id
         AND rctl.customer_trx_line_id = rctg.customer_trx_line_id
         AND gc.code_combination_id = rctg.code_combination_id
         AND NVL (rctg.account_set_flag, 'N') <> 'Y'
         AND rctg.account_class = 'REV'
         AND rctl.interface_line_attribute10 = pl.lease_num
         AND ppt.lease_id = pl.lease_id
         AND rct.org_id = 2965
         AND gc.segment3 = '612530'
         AND rctg.org_id = rct.org_id
         --and NVL(fnd_profile.value('XXFIN_PN_SALE_ACCESS'),'N') = decode(tenure_code,'L','N','Y')
         AND ppi.payment_term_id = ppt.payment_term_id
         AND rctl.interface_line_attribute1 =
                pl.lease_num || ' - ' || ppi.payment_item_id
         --and gc.segment3 = nvl(:p_seg3,gc.segment3)
         AND rctg.gl_date >= NVL ( :p_from_date, rctg.gl_date)
         AND rctg.gl_date <= NVL ( :p_to_date, rctg.gl_date)
GROUP BY gc.segment1,
         rct.trx_number,
         ac.customer_name,
         gc.concatenated_segments,
         ac.customer_number,
         pl.lease_num,
         plu.location_code,
         space_type,
         gc.segment2,
         gc.segment3,
         rctg.gl_date,
         ppt.term_template_id
UNION ALL
SELECT gc.segment1 bu,
         gc.concatenated_segments,
         --gc.segment3 Account,
         rct.trx_number,
         ac.customer_name,
         ac.customer_number,
         NULL lease_num,
         NULL location_code,
         NULL plot_type,
         NULL payment_term_type,
         gc.segment2,
         gc.segment3 gl_account,
         TO_CHAR ( (rctg.gl_date)) gl_date,
         SUM (rctg.amount) gl_amount
    FROM apps.ra_customer_trx_all rct,
         apps.ra_cust_trx_line_gl_dist_all rctg,
         apps.gl_code_combinations_kfv gc,
         apps.ra_customer_trx_lines_all rctl,
         --         apps.pn_leases_all pl,
         --         apps.pn_payment_items_all ppi,
         --         apps.pn_payment_terms_all ppt,
         --         apps.xxfin_pn_locations_pub_v plu,
         apps.ar_customers ac
   WHERE    1=1
--   AND rct.batch_source_id = 24
         AND ac.customer_id = rct.bill_to_customer_id
         --         AND ppt.location_id = plu.location_id
         AND rct.customer_trx_id = rctg.customer_trx_id
         AND rctl.customer_trx_id = rct.customer_trx_id
         AND rctl.customer_trx_line_id = rctg.customer_trx_line_id
         AND gc.code_combination_id = rctg.code_combination_id
         AND NVL (rctg.account_set_flag, 'N') <> 'Y'
         AND rctg.account_class = 'REV'
         AND rctl.interface_line_attribute10 IS NULL
         --         AND ppt.lease_id = pl.lease_id
         AND rct.org_id = 2965
         AND gc.segment3 = '612530'
         AND rctg.org_id = rct.org_id
         --and NVL(fnd_profile.value('XXFIN_PN_SALE_ACCESS'),'N') = decode(tenure_code,'L','N','Y')
         --         AND ppi.payment_term_id = ppt.payment_term_id
         AND rctl.interface_line_attribute1 IS NULL
         --and gc.segment3 = nvl(:p_seg3,gc.segment3)
         AND rctg.gl_date >= NVL ( :p_from_date, rctg.gl_date)
         AND rctg.gl_date <= NVL ( :p_to_date, rctg.gl_date)
GROUP BY gc.segment1,
         rct.trx_number,
         ac.customer_name,
         gc.concatenated_segments,
         ac.customer_number,
         --         pl.lease_num,
         --         plu.location_code,
         --         space_type,
         gc.segment2,
         gc.segment3,
         rctg.gl_date
--         ppt.term_template_id

Oracle E-Business Suite