<dataTemplate name="AR_OPEN_INVOICE" defaultPackage="AR_REPORTS" Version="1.0">
<properties>
<property name="xml_tag_case" value="upper" />
</properties>
<parameters>
<parameter name="P_AS_OF_DATE" datatype="varchar2" />
<parameter name="P_SET_OF_BOOKS_ID" datatype="number" />
<parameter name="P_CUSTOMER_NAME_FROM" datatype="varchar2" />
<parameter name="P_CUSTOMER_NAME_TO" datatype="varchar2" />
<parameter name="P_CUSTOMER_NUMBER_LOW" datatype="varchar2" />
<parameter name="P_CUSTOMER_NUMBER_HIGH" datatype="varchar2" />
<parameter name="P_CURRENCY" datatype="varchar2" />
<parameter name="P_MIN_INVOICE_BALANCE" datatype="varchar2" />
<parameter name="P_MIN_CUSTOMER_BALANCE" datatype="varchar2" />
<parameter name="P_INCLUDE_ON_ACCOUNT_CREDITS" datatype="varchar2" />
<parameter name="P_INCLUDE_ON_ACCOUNT_RECEIPTS" datatype="varchar2" />
<parameter name="P_INCLUDE_UNAPPLIED_RECEIPTS" datatype="varchar2" />
<parameter name="P_INCLUDE_UNCLEARED_RECEIPTS" datatype="varchar2" />
<parameter name="P_REFERENCE_NUMBER" datatype="varchar2" />
<parameter name="P_OPERATING_UNIT" datatype="varchar2" />
<parameter name="P_DUE_DATE_FROM" datatype="varchar2" />
<parameter name="P_DUE_DATE_TO" datatype="varchar2" />
<parameter name="P_COLLECTOR_NAME_FROM" datatype="varchar2" />
<parameter name="P_COLLECTOR_NAME_TO" datatype="varchar2" />
<parameter name="P_COLL_ASSIGNED_FROM" datatype="varchar2" />
<parameter name="P_COLL_ASSIGNED_TO" datatype="varchar2" />
<parameter name="P_DEBUG_FLAG" datatype="varchar2" />
<parameter name="P_TRACE_FLAG" datatype="varchar2" />
</parameters>
<lexicals />
<dataQuery>
<sqlStatement name="Q_header">
<![CDATA[SELECT X.OPERATING_UNIT,
X.SET_OF_BOOKS_ID,
X.CUSTOMER_NUMBER,
X.CUSTOMER_NAME,
X.PARTY_SITE_NUMBER,
X.ADDRESS_LINE1,
X.ADDRESS_LINE2,
X.ADDRESS_LINE3,
X.ADDRESS_LINE4,
X.PROVINCE,
X.CITY,
X.STATE,
X.ZIP,
X.COUNTRY,
X.TRX_NUMBER,
X.ASSIGNED_COLLECTOR,
X.RELATED_SO,
X.SALESPERSON,
X.RELATED_PROJECT,
X.PURCHASE_ORDER,
X.INVOICE_TERMS,
to_char(X.TRANSACTION_DATE) "INVOICE_DATE",
to_char(X.DUE_DATE) "DUE_DATE",
trunc(X.AS_OF_DATE-X.DUE_DATE) "DAYS_PAST_DUE",
to_char(X.EXPECTED_PAYMENT_DATE) "EXPECTED_PAYMENT_DATE",
X.TRX_CURRENCY_CODE "CURRENCY",
X.UNAPPLIED_RECEIPTS "INVOICE_AMOUNT",
X.TRANS_AMOUNT "ORIGINAL_INVOICE_AMOUNT",
X.TRANS_AMOUNT_REMAINING "BALANCE_DUE",
X.DISPUTED_AMOUNT,
round(X.TRANS_AMOUNT * NVL(X.CONVERSION_RATE,1),2) "USD_ORIGINAL_INVOICE_AMOUNT",
round(X.TRANS_AMOUNT_REMAINING * NVL(X.CONVERSION_RATE,1),2) "USD_BALANCE_DUE",
round(X.DISPUTED_AMOUNT * NVL(X.CONVERSION_RATE,1),2) "USD_DISPUTED_AMOUNT",
CASE WHEN trunc(X.DUE_DATE) > trunc(X.AS_OF_DATE) THEN X.TRANS_AMOUNT_REMAINING END "NOT_DUE",
CASE WHEN trunc(X.DUE_DATE) = trunc(X.AS_OF_DATE) THEN X.TRANS_AMOUNT_REMAINING END "CURRENT",
CASE WHEN trunc(X.DUE_DATE) < trunc(X.AS_OF_DATE) THEN X.TRANS_AMOUNT_REMAINING END "PAST_DUE",
CASE WHEN trunc(X.AS_OF_DATE-X.DUE_DATE) BETWEEN 1 AND 30 THEN X.TRANS_AMOUNT_REMAINING END "PD_1_30",
CASE WHEN trunc(X.AS_OF_DATE-X.DUE_DATE) BETWEEN 31 AND 60 THEN X.TRANS_AMOUNT_REMAINING END "PD_31_60",
CASE WHEN trunc(X.AS_OF_DATE-X.DUE_DATE) BETWEEN 61 AND 90 THEN X.TRANS_AMOUNT_REMAINING END "PD_61_90",
CASE WHEN trunc(X.AS_OF_DATE-X.DUE_DATE) BETWEEN 91 AND 180 THEN X.TRANS_AMOUNT_REMAINING END "PD_91_180",
CASE WHEN trunc(X.AS_OF_DATE-X.DUE_DATE) BETWEEN 181 AND 360 THEN X.TRANS_AMOUNT_REMAINING END "PD_181_360",
CASE WHEN trunc(X.AS_OF_DATE-X.DUE_DATE) > 360 THEN X.TRANS_AMOUNT_REMAINING END "PD_360",
CASE WHEN trunc(X.DUE_DATE) > trunc(X.AS_OF_DATE) THEN round(X.TRANS_AMOUNT_REMAINING * NVL(X.CONVERSION_RATE,1),2) END "USD_NOT_DUE",
CASE WHEN trunc(X.DUE_DATE) = trunc(X.AS_OF_DATE) THEN round(X.TRANS_AMOUNT_REMAINING * NVL(X.CONVERSION_RATE,1),2) END "USD_CURRENT",
CASE WHEN trunc(X.DUE_DATE) < trunc(X.AS_OF_DATE) THEN round(X.TRANS_AMOUNT_REMAINING * NVL(X.CONVERSION_RATE,1),2) END "USD_PAST_DUE",
CASE WHEN trunc(X.AS_OF_DATE-X.DUE_DATE) BETWEEN 1 AND 30 THEN round(X.TRANS_AMOUNT_REMAINING * NVL(X.CONVERSION_RATE,1),2) END "USD_PD_1_30",
CASE WHEN trunc(X.AS_OF_DATE-X.DUE_DATE) BETWEEN 31 AND 60 THEN round(X.TRANS_AMOUNT_REMAINING * NVL(X.CONVERSION_RATE,1),2) END "USD_PD_31_60",
CASE WHEN trunc(X.AS_OF_DATE-X.DUE_DATE) BETWEEN 61 AND 90 THEN round(X.TRANS_AMOUNT_REMAINING * NVL(X.CONVERSION_RATE,1),2) END "USD_PD_61_90",
CASE WHEN trunc(X.AS_OF_DATE-X.DUE_DATE) BETWEEN 91 AND 180 THEN round(X.TRANS_AMOUNT_REMAINING * NVL(X.CONVERSION_RATE,1),2) END "USD_PD_91_180",
CASE WHEN trunc(X.AS_OF_DATE-X.DUE_DATE) BETWEEN 181 AND 360 THEN round(X.TRANS_AMOUNT_REMAINING * NVL(X.CONVERSION_RATE,1),2) END "USD_PD_181_360",
CASE WHEN trunc(X.AS_OF_DATE-X.DUE_DATE) > 360 THEN round(X.TRANS_AMOUNT_REMAINING * NVL(X.CONVERSION_RATE,1),2) END "USD_PD_360",
X.COLLECTOR_NAME,
to_char(X.LAST_CONTACT_DATE) LAST_CONTACT_DATE,
X.CUSTOMER_CONTACT,
X.STATUS,
to_char(X.COLLECTOR_FORECAST_DATE) COLLECTOR_FORECAST_DATE,
X.CUSTOMER_RESPONSE,
X.OUTCOME,
X.NOTES
FROM
AR_INV_REPORT x
where x.REQUEST_ID = (select fnd_global.conc_request_id request_id from dual)
]]>
</sqlStatement>
</dataQuery>
<dataTrigger name="beforeReportTrigger" source="AR_REPORTS.before_report(:P_AS_OF_DATE,
:P_SET_OF_BOOKS_ID,
:P_CUSTOMER_NAME_FROM,
:P_CUSTOMER_NAME_TO,
:P_CUSTOMER_NUMBER_LOW,
:P_CUSTOMER_NUMBER_HIGH,
:P_CURRENCY,
:P_MIN_INVOICE_BALANCE,
:P_MIN_CUSTOMER_BALANCE,
:P_INCLUDE_ON_ACCOUNT_CREDITS,
:P_INCLUDE_ON_ACCOUNT_RECEIPTS,
:P_INCLUDE_UNAPPLIED_RECEIPTS,
:P_INCLUDE_UNCLEARED_RECEIPTS,
:P_REFERENCE_NUMBER,
:P_OPERATING_UNIT,
:P_DUE_DATE_FROM,
:P_DUE_DATE_TO,
:P_COLLECTOR_NAME_FROM,
:P_COLLECTOR_NAME_TO,
:P_COLL_ASSIGNED_FROM,
:P_COLL_ASSIGNED_TO,
:P_DEBUG_FLAG,
:P_TRACE_FLAG)"/>
<dataStructure>
<group name="G_header" dataType="varchar2" source="Q_header">
<element name="ORG_ID" value="ORG_ID" />
<element name="OPERATING_UNIT" value="OPERATING_UNIT" />
<element name="SET_OF_BOOKS_ID" value="SET_OF_BOOKS_ID" />
<element name="CUSTOMER_NUMBER" value="CUSTOMER_NUMBER" />
<element name="CUSTOMER_NAME" value="CUSTOMER_NAME" />
<element name="PARTY_SITE_NUMBER" value="PARTY_SITE_NUMBER" />
<element name="ADDRESS_LINE1" value="ADDRESS_LINE1" />
<element name="ADDRESS_LINE2" value="ADDRESS_LINE2" />
<element name="ADDRESS_LINE3" value="ADDRESS_LINE3" />
<element name="ADDRESS_LINE4" value="ADDRESS_LINE4" />
<element name="CITY" value="CITY" />
<element name="STATE" value="STATE" />
<element name="PROVINCE" value="PROVINCE" />
<element name="ZIP" value="ZIP" />
<element name="COUNTRY" value="COUNTRY" />
<element name="TRX_NUMBER" value="TRX_NUMBER" />
<element name="ASSIGNED_COLLECTOR" value="ASSIGNED_COLLECTOR" />
<element name="RELATED_SO" value="RELATED_SO" />
<element name="SALESPERSON" value="SALESPERSON" />
<element name="RELATED_PROJECT" value="RELATED_PROJECT" />
<element name="PURCHASE_ORDER" value="PURCHASE_ORDER" />
<element name="INVOICE_TERMS" value="INVOICE_TERMS" />
<element name="INVOICE_DATE" value="INVOICE_DATE" />
<element name="DUE_DATE" value="DUE_DATE" />
<element name="DAYS_PAST_DUE" value="DAYS_PAST_DUE" />
<element name="EXPECTED_PAYMENT_DATE" value="EXPECTED_PAYMENT_DATE" />
<element name="COLLECTOR_NAME" value="COLLECTOR_NAME" />
<element name="CURRENCY" value="CURRENCY" />
<element name="INVOICE_AMOUNT" value="INVOICE_AMOUNT" />
<element name="ORIGINAL_INVOICE_AMOUNT" value="ORIGINAL_INVOICE_AMOUNT" />
<element name="BALANCE_DUE" value="BALANCE_DUE" />
<element name="DISPUTED_AMOUNT" value="DISPUTED_AMOUNT" />
<element name="USD_ORIGINAL_INVOICE_AMOUNT" value="USD_ORIGINAL_INVOICE_AMOUNT" />
<element name="USD_BALANCE_DUE" value="USD_BALANCE_DUE" />
<element name="USD_DISPUTED_AMOUNT" value="USD_DISPUTED_AMOUNT" />
<element name="NOT_DUE" value="NOT_DUE" />
<element name="CURRENT" value="CURRENT" />
<element name="PAST_DUE" value="PAST_DUE" />
<element name="PD_1_30" value="PD_1_30" />
<element name="PD_31_60" value="PD_31_60" />
<element name="PD_61_90" value="PD_61_90" />
<element name="PD_91_180" value="PD_91_180" />
<element name="PD_181_360" value="PD_181_360" />
<element name="PD_360" value="PD_360" />
<element name="USD_NOT_DUE" value="USD_NOT_DUE" />
<element name="USD_CURRENT" value="USD_CURRENT" />
<element name="USD_PAST_DUE" value="USD_PAST_DUE" />
<element name="USD_PD_1_30" value="USD_PD_1_30" />
<element name="USD_PD_31_60" value="USD_PD_31_60" />
<element name="USD_PD_61_90" value="USD_PD_61_90" />
<element name="USD_PD_91_180" value="USD_PD_91_180" />
<element name="USD_PD_181_360" value="USD_PD_181_360" />
<element name="USD_PD_360" value="USD_PD_360" />
<element name="LAST_CONTACT_DATE" value="LAST_CONTACT_DATE" />
<element name="CUSTOMER_CONTACT" value="CUSTOMER_CONTACT" />
<element name="STATUS" value="STATUS" />
<element name="COLLECTOR_FORECAST_DATE" value="COLLECTOR_FORECAST_DATE" />
<element name="CUSTOMER_RESPONSE" value="CUSTOMER_RESPONSE" />
<element name="OUTCOME" value="OUTCOME" />
<element name="NOTES" value="NOTES" />
</group>
</dataStructure>
<dataTrigger name="afterReportTrigger" source="AR_REPORTS.after_report"/>
</dataTemplate>
No comments:
Post a Comment