Taiwan Services » DP Client

Amendment on item (5) TRAN_AMT:

TRAN_AMT = dp_earned (retrieval) * EXCHG_RATE

Note: dp_earned is in USD currency value. Take the absolute value via Math.abs method.

planning program flow soon

Add in:

item 11) TRAN_ID, set it to 'PROFITmmyyyy', where mmyyyy is the profit month.

For the batch file format, please refer to DP Batch File Format, for the latest version.

removed all old attachments. refer to the DP Batch File Format for the latest documents.

Changes:
Do not need to retrieve records from MEMBER_STAT, as BO earned DP profit has already been converted into cash pay out via eAccount, therefore BO will not able to utilize DP during purchase.
Only need to retrieve data from VIP_STAT.

means the the SQL to retrive VIP_STAT and MEMBER_STAT table did not need to execute? ty

or remove the UNION SELECT MEMBER_STAT?

Only remove the UNION SELECT MEMBER_STAT from SQL query.

refer to the issue description for the latest data retrievin SQL

ok ty

Please send the sample generated file to Benny Kuok Chan Weng [CWKuok@cosway.com.my] by today, do CC me and chooi-mey. If there's any issue, do update here. Thanks.

to chin yeh, the sample file is gen-ed.
u wan to hav a look first or i direct email to the receipient?

work with Benny to make sure the system can process the generated file.

do update the issue to reflect the current progress.

• TRAN_ID column - the data month will be the previous month of the report generation date
  • e.g. if report generation month is August 2011, then the value of the column would be PROFIT072011
• able to accept input parameter to generate batch file for different month, for example, 092011, 102011

Need to apply the logic (same as bank file generation checking) that check whether is ready to generate not.
Refer to GenProfitPaymentForUkBank, method "getUserInputOrSystemParam" logic checking, to check whether is the publish flag (MEND_PROC_IND) is set to 'Y' in table MTH_END_CALC, based on pass in month and year param/sys date.

to chooi mey, is it if date not null then READY_TO_GENERATE set to true value in hashtable?

sample file sent to benny and waiting for feedback

• The Profit from member of country Taiwan which belongs to Foreigner.
• The Profit from member of country Taiwan which not belongs to Foreigner.
• The Profit from member of country apart from Taiwan.

Remember to refer to the latest batch file format, DP Batch File Format

• populate the data to a new table, vip_stat_dummy and point your batch program to the new table
• below is the sample SQL you could use to populate part of the table's columns:
  

  select a.sacntry, b.shlogid from shaddr a,shopper b 
      where b.shrfnbr=a.sashnbr
       and b.shshtyp = 'S' and b.shopper_grp = '9' 
       and b.status ='A' and a.saadrflg='P' 
       and a.sacntry in ('036')
  
  

  • replace the sacntry with the country code you want to query
• populate the dp_earned column with dummy values
• populate the table with at least 5-7 country codes, each country code has 15-20 member ID
• try to only populate the columns that needed by batch file, e.g. dp_earned
• assume all Taiwan ID is not foreigner

• output filename convention is DP_PROFITMMyyyy.txt
  • MMyyyy - profit month, e.g. 092011 for September 2011
  • Example: DP_PROFIT092011.txt, DP_PROFIT102011.txt
• output folder is

/usr/ecosway/offlinefiles/etransfer

• no need to specify any value for the IS_FORE column. Just leave it blank

ok

• how to deploy the batch program
• how to execute the batch program
• how to configure the application properties if any

It would be great if the guides are written in wiki so everyone can view or edit it when necessary.

•how to deploy the batch program
•how to execute the batch program
•how to configure the application properties if any

for these purposes
is it different from other batch program as need to be planned?

Just prepare for the profit batch program first.

• change the PRO_CODE for profit to Profit, originally is Bonus
  • refer to the latest DP Batch File Format

neeed to gen and send sample file again?

yes, please generate again and send to Benny.

Bugs:
1) Those VIP residing in China, Macau and Hong Kong, should mapped into HKD currency (Currency_To), instead of RMB/MC.

the macau and china residing country is mapped to hong kong residing country in program and file generated.
only data inside database not changed.

The Currency_to should mapped to HKD as well, besides the residing country. Same to exchange rate.
It means when system detect the actual residing country of the VIP is 156/446, when generate the profit batch file, system need to treat them based at HK, instead of their originated country. This is because those VIP is actually earned profit/utilized DP balance in HKD currency, instead of RMB/MC.

ok i get wat u mean. now the file regenerated

For DP Amount in profit file, the value need to be in Local currency of the mapped country.
The retrieval value from VIP_STAT is defaulted in USD value. So need to perform currency conversion based on the latest exchange rate (from MEND_CURRCONV table) and its mapped country/currency.
DP AMOUNT = dp_earned * exchange rate (based on its mapped currency)
For example, for China VIP, the dp_earned need to be converted into HKD currency as the DP amount in profit file.

Add requirements:
1) Finance Report
- Filename Format: DP_PROFIT_FINANCE_REFERENCE_MMyyyy.csv
- File Encoding: ISO-8859-1
- Comma Delimited
- Header: MEMBER ID,COUNTRY CODE,MAPPED COUNTRY CODE,DP EARNED (USD),DP EARNED,EXCHANGE RATE
- Details: Example
VP123456,156,344,2.00(USD),15.60(HKD),7.80
VP123457,826,826,-1.00(USD),-0.62(GBP),0.62

2) Exception Report
- Filename Format: DP_PROFIT_EXCEPTION_MMyyyy.csv
- File Encoding: ISO-8859-1
- Comma Delimited
- Header: MEMBER ID,DP EARNED (USD),REASON
- Details: Example
VP123456,2.00,EXCHANGE RATE OF 344 NOT FOUND
VP123457,-1.00,EXCHANGE RATE OF 826 NOT FOUND
- Exception is catch when hashExchangeRateValue is null (due to fail get the match record from MEND_CURRCONV by country code), and skipped the record to be written into profit file.

• Refer to the format2Decimal method in my.com.eCosway.util.MyUtil class in usa-mall repository about how to round the monetary value such as DP amount. This is the common method we use to round the value to 2 decimal places.

The file encoding of the profit batch file will be UTF-8.

FYI, the file encoding for all of the VP/DP batch files will be UTF-8 as well.

format dp earned to 2 decimal is after currency conversion? for calculation purpose?

Use the util to format all monetary value to 2 decimal places after calculation

ok i get it

wat is the formula or how to calculate the dp that need to convert 2 decimal?

I've imported the live VIP_STAT & mend_currconv tables into development STORE db. Can you generate a copy and pass it to Ting-Ting for verification asap?

As per discussion, this batch program will be deployed to production by tomorrow morning. After deployed, we will generate the batch file and pass it to Benny for final verification.

i just gen the file and send to ting ting?

As discussed, do provide the profit batch file, which generated in live environment, to Ting-Ting & Benny for final verification.

After that, try to fix all of the bug(s) you have encountered by today.

production generated file sent to ting ting and benny.
bug fixed

Changes:

1) Add in USD_DP_AMT field at last column (position 531-543), with length 13N (13,2). Pass the DP amount in USD currency (before convert) into the field.

2) For INV_NO field (position 140-159), put in value "BONUSyyyyMM" to show the profit month remarks. For example: BONUS201107.

This changes will only take effect for next month profit run. The updated batch file format will be uploaded later.

adding

INV_NO field refers to current date time?

INV_NO field refer to profit month, for example, if you are generating for May 2011 profit, then it will be BONUS201105. Not the generated date time. Same as the filename MMyyyy logic.

finding test data to enable verification

Use the Apache POI to generate the excel file.

• memory usage on writing 1 million records to excel file
• memory usage on writing 100,000 records to excel file
• memory usage on writing 5 millions records (5 mapped country code in total, each contains 1 million) to excel file
• memory usage on writing 500,000 records (5 mapped country code in total, each contains 100,000) to excel file

Also, do update the Due date when this changes are ready to be deployed to production environment for final testing.

is the excel API (APACHE POI) already exist in our eclipse or need to download?

get the latest stable version from the official website

• prior office 2007 - 65,536 rows by 256 columns
• office 2007 or above - 1,048,576 rows by 16,384 columns

Click here to see the more details of the excel specification.

Because of this, your program must be able to split out the record to different sheets when reach the limit. However, the size of the workbook is limited by the available memory resource on the client machine.

At the moment, we will use xls format to have better compatibility.

you could use this code snippet to capture the memory usage:

long used = Runtime.getRuntime().totalMemory() - Runtime.getRuntime().freeMemory();

is the Runtime under java.lang? if is i try it now

Just a reminder. The total amount in the sheet's footer is referring to total amount (USD & local currency) on the particular sheet, not mapped country code.

Besides that, do update the Due Date.

load test still in progress
latest code commited to CVS

• memory usage on writing 100,000 records to excel file - 144212200 bytes (144MB)

• memory usage on writing 500,000 records (5 mapped country code in total, each contains 100,000) to excel file - 759753320 bytes (759MB)

• memory usage on writing 1 million records to excel file - 1385801560 bytes (1.39GB)

the following is settings for JVM arg
100 k and 500 k use default (no need to set)
for 1m record with arg -Xmx1536m

Attach the latest shell scripts and the required libraries here

This batch program has already been scheduled to run daily since yesterday.

Since this is a new program, you are required to enter the details of the program into the runHK.txt sheet of DailyBatchNew.xls, the file are located at:

<<src shared folder>>/schedule batch

i take a look

direct profit details added into into the runHK.txt sheet of DailyBatchNew.xls

Pls provide guideline to Chee Wei on how to manual run the DP Profit program.

i've added in the param needed in the force run column

Please fill up the related info. for Date Generated and Data Date columns in the excel file.

Date Generated and Date Date is refer to date of?

Date Generated and Data Date filled

[Changes]
1) Change the Finance Reference Report Excel sheet(tab) from country code to country name (with max length 20 chars).
2) Add new param argument to allow pre-run without checking on the publish flag.

Both items (country name & force run) tested.

ok

do update the DailyBatchNew.xls if there's any changes made.

DailyBatchNew.xls updated

[Changes]
- Remove the 2 columns: Country Code and Mapped Country Code, from displaying on sheet.
Note: Comment out the code with remarks, for future enable back if need to.

changes deployed to live and code commited into CVS

Chooi-Mey wrote:

[Changes/Bug]

1. In DP_Profit_Finance_Reference, show the value of the amount, include the negative sign if < 0.
2. Total is wrongly calculated due to negative value of the amount is not being take into calculation. Total is sum of amount (with 2 decimal point).

calculation corrected and deployed to production env