AR Invoice PDF email (Alternative of BPA Process)

<?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>

  • February 25, 2019 | 19 views
  • Comments