<?xml version="1.0"?>
<dataTemplate name="XX__AR_INVPRINT" description="TW Invoice Printout for United States" defaultPackage="XX_AR_INVPRINT_PKG" version="1.0">
<parameters>
<parameter name="P_Report_Level" dataType = "character"></parameter>
<parameter name="P_Report_Context" dataType = "character"></parameter>
<parameter name="P_TRANSACTION_SOURCE" dataType = "character"></parameter>
<parameter name="P_TRANSACTION_TYPE" dataType = "number"></parameter>
<parameter name="P_TRANSACTION_CLASS" dataType = "character"></parameter>
<parameter name="P_DATE_FROM" dataType = "character"></parameter>
<parameter name="P_DATE_TO" dataType = "character"></parameter>
<parameter name="P_TRANS_NUMBER_LOW" dataType = "character"></parameter>
<parameter name="P_TRANS_NUMBER_HIGH" dataType = "character"></parameter>
<parameter name="P_CUST_NAME" dataType = "character"></parameter>
<parameter name="P_PERIOD" dataType = "character"></parameter>
</parameters>
<dataQuery>
<sqlStatement name="Q_HEADER">
<![CDATA[
SELECT DISTINCT TRANSACTION_ID,
NVL (fnd_profile.VALUE ('IMDC:BPA_INVOICE_WEB_LINK'), 'For Online Payments : www.xx.com') PAYMENT_WEB_ADDRESS,
bill_to_customer_name CUSTOMER_NAME,
PURCHASE_ORDER_NUMBER,
case when XX_CUST_EMAIL_FUNC(bill_to_contact_id,'PRI') is null then
bill_to_customer_number||' - '||bill_to_customer_name||' - '||transaction_number||' - '||ATTRIBUTE11||' - '||' Please Print and Send '
else
bill_to_customer_number||' - '||bill_to_customer_name||' - '||transaction_number||' - '||ATTRIBUTE11
end subject,
FORMATTED_BILL_TO_ADDRESS,
FORMATTED_REMIT_TO_ADDRESS,
NVL (fnd_profile.VALUE ('IMDC:BPA_REMIT_EMAIL'), 'Email : xx@xx.com') REMIT_EMAIL_ADDRESS,
NVL (fnd_profile.VALUE ('IMDC:BPA_REMIT_PHONE'), ' ') REMIT_PHONE_NUMBER,
TRANSACTION_NUMBER,
TRANSACTION_DATE,
ATTRIBUTE11 PERIOD,
ACCOUNT_NUMBER,
BILL_TO_LOCATION,
to_char(fnd_date.canonical_to_date(attribute10),'DD-MON-YYYY') COMMENCEMENT_DATE,
INVOICE_CURRENCY_CODE,
TERM_NAME,
term_due_date_from_ps TERM_DATE,
CASE WHEN :P_TRANS_NUMBER_LOW IS NOT NULL AND :P_TRANS_NUMBER_HIGH IS NOT NULL THEN
'xx@xx.com'
ELSE
NVL(XX_CUST_EMAIL_FUNC(bill_to_contact_id,'PRI'),'xx@xx.com') END AS EMAIL,
XX_CUST_EMAIL_FUNC(bill_to_contact_id,'PRI') CUSTEMAIL,
CASE WHEN :P_TRANS_NUMBER_LOW IS NULL AND :P_TRANS_NUMBER_HIGH IS NULL THEN
XX_CUST_EMAIL_FUNC(bill_to_contact_id,'SEC') ELSE NULL END AS SEC_EMAIL,
CUSTOMER_CONTACT_NAME ,
CUSTOMER_PHONE,
LINE_AMOUNT,
TAX_AMOUNT,
FREIGHT_AMOUNT,
TOTAL_AMOUNT,
PAYMENTS_AND_CREDITS,
FINANCE_CHARGES,
TO_CHAR (OUTSTANDING_BALANCE, fnd_currency.get_format_mask ( invoice_currency_code, 40 )) OUTSTANDING_BALANCE,
CURRENT_DATE,
CT_REFERENCE
FROM XXAR_CUSTOMER_TRX_HEADER
WHERE org_id = 11
AND CUST_TRX_TYPE_ID IN (123)
AND TRANSACTION_ID IN (SELECT CUSTOMER_TRX_ID FROM RA_CUSTOMER_TRX_ALL
WHERE org_id = 11
AND CUST_TRX_TYPE_ID IN (123)
AND TRX_NUMBER BETWEEN :P_TRANS_NUMBER_LOW
AND :P_TRANS_NUMBER_HIGH)
AND :P_TRANS_NUMBER_LOW IS NOT NULL
AND :P_TRANS_NUMBER_HIGH IS NOT NULL
AND :P_PERIOD IS NULL
UNION
SELECT DISTINCT TRANSACTION_ID,
NVL (fnd_profile.VALUE ('IMDC:BPA_INVOICE_WEB_LINK'), 'For Online Payments : www.xx.com') PAYMENT_WEB_ADDRESS,
bill_to_customer_name CUSTOMER_NAME,
PURCHASE_ORDER_NUMBER,
case when XX_CUST_EMAIL_FUNC(bill_to_contact_id,'PRI') is null then
bill_to_customer_number||' - '||bill_to_customer_name||' - '||transaction_number||' - '||ATTRIBUTE11||' - '||' Please Print and Send '
else
bill_to_customer_number||' - '||bill_to_customer_name||' - '||transaction_number||' - '||ATTRIBUTE11
end subject,
FORMATTED_BILL_TO_ADDRESS,
FORMATTED_REMIT_TO_ADDRESS,
NVL (fnd_profile.VALUE ('IMDC:BPA_REMIT_EMAIL'), 'Email : xx@xx.com') REMIT_EMAIL_ADDRESS,
NVL (fnd_profile.VALUE ('IMDC:BPA_REMIT_PHONE'), ' ') REMIT_PHONE_NUMBER,
TRANSACTION_NUMBER,
TRANSACTION_DATE,
ATTRIBUTE11 PERIOD,
ACCOUNT_NUMBER,
BILL_TO_LOCATION,
to_char(fnd_date.canonical_to_date(attribute10),'DD-MON-YYYY') COMMENCEMENT_DATE,
INVOICE_CURRENCY_CODE,
TERM_NAME,
term_due_date_from_ps TERM_DATE,
CASE WHEN :P_TRANS_NUMBER_LOW IS NOT NULL AND :P_TRANS_NUMBER_HIGH IS NOT NULL THEN
'xx@xx.com'
ELSE
NVL(XX_CUST_EMAIL_FUNC(bill_to_contact_id,'PRI'),'xx@xx.com') END AS EMAIL,
XX_CUST_EMAIL_FUNC(bill_to_contact_id,'PRI') CUSTEMAIL,
CASE WHEN :P_TRANS_NUMBER_LOW IS NULL AND :P_TRANS_NUMBER_HIGH IS NULL THEN
XX_CUST_EMAIL_FUNC(bill_to_contact_id,'SEC') ELSE NULL END AS SEC_EMAIL,
CUSTOMER_CONTACT_NAME ,
CUSTOMER_PHONE,
LINE_AMOUNT,
TAX_AMOUNT,
FREIGHT_AMOUNT,
TOTAL_AMOUNT,
PAYMENTS_AND_CREDITS,
FINANCE_CHARGES,
TO_CHAR (OUTSTANDING_BALANCE, fnd_currency.get_format_mask ( invoice_currency_code, 40 )) OUTSTANDING_BALANCE,
CURRENT_DATE,
CT_REFERENCE
FROM XXAR_CUSTOMER_TRX_HEADER , gl_periods gp
WHERE org_id = 11
AND CUST_TRX_TYPE_ID IN (123)
and GP.PERIOD_SET_NAME = 'XX_CALENDAR'
AND GP.PERIOD_NAME = :P_PERIOD
AND TRX_DATE BETWEEN GP.START_DATE AND GP.END_DATE
AND PRINTING_LAST_PRINTED IS NULL
AND :P_PERIOD IS NOT NULL
AND :P_TRANS_NUMBER_LOW IS NULL
AND :P_TRANS_NUMBER_HIGH IS NULL
UNION
SELECT DISTINCT TRANSACTION_ID,
NVL (fnd_profile.VALUE ('IMDC:BPA_INVOICE_WEB_LINK'), 'For Online Payments : www.xx.com') PAYMENT_WEB_ADDRESS,
bill_to_customer_name CUSTOMER_NAME,
PURCHASE_ORDER_NUMBER,
case when XX_CUST_EMAIL_FUNC(bill_to_contact_id,'PRI') is null then
bill_to_customer_number||' - '||bill_to_customer_name||' - '||transaction_number||' - '||ATTRIBUTE11||' - '||' Please Print and Send '
else
bill_to_customer_number||' - '||bill_to_customer_name||' - '||transaction_number||' - '||ATTRIBUTE11
end subject,
FORMATTED_BILL_TO_ADDRESS,
FORMATTED_REMIT_TO_ADDRESS,
NVL (fnd_profile.VALUE ('IMDC:BPA_REMIT_EMAIL'), 'Email : xx@xx.com') REMIT_EMAIL_ADDRESS,
NVL (fnd_profile.VALUE ('IMDC:BPA_REMIT_PHONE'), ' ') REMIT_PHONE_NUMBER,
TRANSACTION_NUMBER,
TRANSACTION_DATE,
ATTRIBUTE11 PERIOD,
ACCOUNT_NUMBER,
BILL_TO_LOCATION,
to_char(fnd_date.canonical_to_date(attribute10),'DD-MON-YYYY') COMMENCEMENT_DATE,
INVOICE_CURRENCY_CODE,
TERM_NAME,
term_due_date_from_ps TERM_DATE,
CASE WHEN :P_TRANS_NUMBER_LOW IS NOT NULL AND :P_TRANS_NUMBER_HIGH IS NOT NULL THEN
'xx@xx.com'
ELSE
NVL(XX_CUST_EMAIL_FUNC(bill_to_contact_id,'PRI'),'xx@xx.com') END AS EMAIL,
XX_CUST_EMAIL_FUNC(bill_to_contact_id,'PRI') CUSTEMAIL,
CASE WHEN :P_TRANS_NUMBER_LOW IS NULL AND :P_TRANS_NUMBER_HIGH IS NULL THEN
XX_CUST_EMAIL_FUNC(bill_to_contact_id,'SEC') ELSE NULL END AS SEC_EMAIL,
CUSTOMER_CONTACT_NAME ,
CUSTOMER_PHONE,
LINE_AMOUNT,
TAX_AMOUNT,
FREIGHT_AMOUNT,
TOTAL_AMOUNT,
PAYMENTS_AND_CREDITS,
FINANCE_CHARGES,
TO_CHAR (OUTSTANDING_BALANCE, fnd_currency.get_format_mask ( invoice_currency_code, 40 )) OUTSTANDING_BALANCE,
CURRENT_DATE,
CT_REFERENCE
FROM XXAR_CUSTOMER_TRX_HEADER , gl_periods gp
WHERE org_id = 11
AND CUST_TRX_TYPE_ID IN (123)
AND TRANSACTION_ID IN (SELECT CUSTOMER_TRX_ID FROM RA_CUSTOMER_TRX_ALL
WHERE org_id = 11
AND CUST_TRX_TYPE_ID IN (123)
AND TRX_NUMBER BETWEEN :P_TRANS_NUMBER_LOW
AND :P_TRANS_NUMBER_HIGH)
and GP.PERIOD_SET_NAME = 'XX_CALENDAR'
AND GP.PERIOD_NAME = :P_PERIOD
AND TRX_DATE BETWEEN GP.START_DATE AND GP.END_DATE
AND :P_PERIOD IS NOT NULL
AND :P_TRANS_NUMBER_LOW IS NOT NULL
AND :P_TRANS_NUMBER_HIGH IS NOT NULL
]]>
</sqlStatement>
<sqlStatement name="Q_TRX_LINES">
<![CDATA[
SELECT DESCRIPTION, QUANTITY,UNIT_OF_MEASURE_NAME,START_DATE,UNIT_PRICE,EXTENDED_AMOUNT,SALES_ORDER,
UOM_CODE,TRX_NUMBER,DATA_CENTER
FROM XXAR_CUSTOMER_TRX_LINE
WHERE CUSTOMER_TRX_ID = :TRANSACTION_ID
]]>
</sqlStatement>
</dataQuery>
<dataStructure>
<group name="G_HEADER" source="Q_HEADER">
<element name="PAYMENT_WEB_ADDRESS" value="PAYMENT_WEB_ADDRESS"/>
<element name="CUSTOMER_NAME" value="CUSTOMER_NAME"/>
<element name="PURCHASE_ORDER_NUMBER" value="PURCHASE_ORDER_NUMBER"/>
<element name="SUBJECT" value="SUBJECT"/>
<element name="FORMATTED_BILL_TO_ADDRESS" value="FORMATTED_BILL_TO_ADDRESS"/>
<element name="FORMATTED_REMIT_TO_ADDRESS" value="FORMATTED_REMIT_TO_ADDRESS"/>
<element name="REMIT_EMAIL_ADDRESS" value="REMIT_EMAIL_ADDRESS"/>
<element name="REMIT_PHONE_NUMBER" value="REMIT_PHONE_NUMBER"/>
<element name="TRANSACTION_NUMBER" value="TRANSACTION_NUMBER"/>
<element name="TRANSACTION_DATE" value="TRANSACTION_DATE"/>
<element name="PERIOD" value="PERIOD"/>
<element name="ACCOUNT_NUMBER" value="ACCOUNT_NUMBER"/>
<element name="BILL_TO_LOCATION" value="BILL_TO_LOCATION"/>
<element name="COMMENCEMENT_DATE" value="COMMENCEMENT_DATE"/>
<element name="INVOICE_CURRENCY_CODE" value="INVOICE_CURRENCY_CODE"/>
<element name="TERM_NAME" value="TERM_NAME"/>
<element name="TERM_DATE" value="TERM_DATE"/>
<element name="EMAIL" value="EMAIL"/>
<element name="CUSTEMAIL" value="CUSTEMAIL"/>
<element name="SEC_EMAIL" value="SEC_EMAIL"/>
<element name="CUSTOMER_CONTACT_NAME" value="CUSTOMER_CONTACT_NAME"/>
<element name="CUSTOMER_PHONE" value="CUSTOMER_PHONE"/>
<element name="LINE_AMOUNT" value="LINE_AMOUNT"/>
<element name="TAX_AMOUNT" value="TAX_AMOUNT"/>
<element name="FREIGHT_AMOUNT" value="FREIGHT_AMOUNT"/>
<element name="TOTAL_AMOUNT" value="TOTAL_AMOUNT"/>
<element name="PAYMENTS_AND_CREDITS" value="PAYMENTS_AND_CREDITS"/>
<element name="FINANCE_CHARGES" value="FINANCE_CHARGES"/>
<element name="OUTSTANDING_BALANCE" value="OUTSTANDING_BALANCE"/>
<element name="CURRENT_DATE" value="CURRENT_DATE"/>
<element name="CT_REFERENCE" value="CT_REFERENCE"/>
<group name="G_LINES" source="Q_TRX_LINES">
<element name="DESCRIPTION" value="DESCRIPTION"/>
<element name="QUANTITY" value="QUANTITY"/>
<element name="UNIT_OF_MEASURE_NAME" value="UNIT_OF_MEASURE_NAME"/>
<element name="START_DATE" value="START_DATE"/>
<element name="UNIT_PRICE" value="UNIT_PRICE"/>
<element name="EXTENDED_AMOUNT" value="EXTENDED_AMOUNT"/>
<element name="DATA_CENTER" value="DATA_CENTER"/>
</group>
</group>
</dataStructure>
<dataTrigger name="afterReportTrigger" source="XX_AR_INVPRINT_PKG.AfterReport"/>
</dataTemplate>
"AR Invoice PDF Email: BPA's Alternative" is an ingenious solution! Your exploration of this alternative method highlights efficiency and adaptability. Security Tips Cybersecurity Your insights into streamlining AR processes through automation offer a game-changing perspective, providing businesses with options to enhance customer experience and operational effectiveness.
ReplyDelete