Rodrigo Maia [3:12 PM]
Hey Phil. Let me know when I may ask some questions for you.

Phil Romov [3:12 PM]
now is a good time

Rodrigo Maia [3:13 PM]
sweet :-)

[3:13]
So, I need some directions with Statement Reports.

[3:14]
Accord I understood, the report will represent a defined period, like a month.

[3:14]
is that?

Phil Romov [3:14 PM]
do we have ticket(s) for it?

Rodrigo Maia [3:15 PM]
Yes, I created some tickets in order to break the big one.

[3:15]
let me send the link

Phil Romov [3:15 PM]
thanks

Rodrigo Maia [3:15 PM]
https://sesac-tech.atlassian.net/browse/SR-18

Phil Romov [3:18 PM]
no, I think the report is by check number

[3:18]
and I think some checks can reprsent one period, or more than one period

Rodrigo Maia [3:19 PM]
I'll need some help to understand this :-S

Phil Romov [3:19 PM]
uploaded and commented on an image: Pasted image at 2016-08-24, 2:19 PM
1 Comment
for example, this is the current statement report

Phil Romov [3:20 PM]
and when I click the first “Check details” button (first row, all the way on the right, here’s what I get)
```LICENSEE    PUBLISHER    MFR PUBL. ID.    MFR TUNE#    SONG TITLE    RECORD NO.    SALE TYPE    UNITS    ROYALTY RATE    ROYALTY AMT    PERIOD END(MMDDYY)    LICENSEE PUB NAME    LICENSEE NAME    PUB NAME    MISC    ALBUM    WRITER(S)    HFA SONG    PERIOD END(YYYYMMDD)    LICENSE #    CHECK #    MISC-2    GROUP PAY CODE    PAYABLE AMT    HFA SPLIT    MFR SPLIT    MEDIA TYPE    3RD PARTY INFO    FOREIGN AMOUNT    EXCHANGE RATE    LABEL    CONFIG    PUB SONG ID    INFO    FILLER(MISC)    PERIOD CODE    ISRC    PLAY MINUTES    PLAY SECONDS    UPC    ISWC    SERVICE OFFERING    ARTIST NAME    MICRO PENNY PORTION    AMT W/MICRO PENNY    DEPT    PRODUCT TYPE    FRGN.TAX DETAIL    FRGN.TAX RATE    FOREIGN CURRENCY    COMMISSION RATE    COMMISSION AMOUNT    PAYABLE AMT NET    TRANSACTION DESC.    GROUPING CRITERIA    SALE TYPE DESCR.    ADDITIONAL MFR PUB#S    SONG FILE CARD HOLDER    ORIGINAL TRX #    HFA RATE CODE    AGREEMENT CODE    FLOOR INDICATION FLAG    DIR DISTRIBUTION FLAG    SONY UNITS    UNITS % FOR SONY
M17712    P61115            PORTRAIT                 2    0.091000    0.1800    13114        X5 MUSIC GROUP    MASONG MUSIC    010114 201401    TOP SMOOTH JAZZ    MASON, RITENOUR    P67035    20140131    1213188179    7493910    58244006*Amazon*1    568    0.1800    100.0000    100.0000    DIGITAL           MC    Amazon    0.0000    0.00000        DP                                                             *    010114    USGR19900181    2    35    7340070477030              LEE RITENOUR AND YELLOWJACKETS    0.0000    0.1800    MC    Digital    0.0000    0.00000           0.0000    0.0000    0.9000    STANDARD ROYALTY    1                3022245739    S                     0    0.0000
M17712    P61115            AMARETTO                 1    0.045500    0.0500    13116        X5 MUSIC GROUP    MASONG MUSIC    010116 201601    THE MOST RELAXING JAZZ SAXOPHO    MASON, LANG    A47020    20160131    1213187003    7493910    61292918*iTunes*1    500    0.0500    50.0000    50.0000    DIGITAL           MC    iTunes    0.0000    0.00000        DP                                                             *    010116    USGR10110408    4    20    7340070474190              TOM SCOTT    0.0000    0.0500    MC    Digital    0.0000    0.00000           0.0000    0.0000    1.7100    STANDARD ROYALTY    1                3049836215    S                     0    0.0000
M17712    P61115            PORTRAIT                 1    0.091000    0.0900    22814        X5 MUSIC GROUP    MASONG MUSIC    010214 201402    TOP SMOOTH JAZZ    MASON, RITENOUR    P67035    20140228    1213185099    7493910    58340213*Amazon*1    618    0.0900    100.0000    100.0000    DIGITAL           MC    Amazon    0.0000    0.00000        SP                                                             *    010214    USGR19900181    2    35    7340070477030              LEE RITENOUR AND YELLOWJACKETS    0.0000    0.0900    MC    Digital    0.0000    0.00000           0.0000    0.0000    0.6300    STANDARD ROYALTY    1                3022246032    S                     0    0.0000
```

[3:20]
so just more detail, by check number

[3:20]
does that make sense?

Rodrigo Maia [3:20 PM]
not yet :-(

[3:21]
so, who access this report?

[3:21]
I mean, what is the group level?

[3:21]
all artists from a company

[3:21]
all sound_recordings from a catalog?

Phil Romov [3:23 PM]
I think publishers access this report...

[3:23]
the group level is a select distinct

[3:23]
so its “select distinct check_number, net_amount, payee, payer, check cut date"

[3:24]
and then when you download statement detail for a check, you get “select * where check_num = this check num"

Rodrigo Maia [3:25 PM]
My fault, my terrible english :-S

[3:25]
when I said Group Level, I mean what is the entity over all data used on report

[3:26]
I mean, if is a publisher, all data on report will be related with this publisher for a period.

Phil Romov [3:26 PM]
oh sorry

[3:26]
there’s a where clause

[3:26]
so the user’s account is the entity

[3:26]
if it begins with “H” (e.g. H00001 or HFA account) then all data is shown

[3:27]
otherwise, (e.g. P12345 or any publisher P#) then you select “where payee = this user’s P#"

[3:27]
another global entity is: check_cut_date != 0 and check_number != 0

Rodrigo Maia [3:30 PM]
It's obscured for a while.

[3:30]
Where do I get these infos?

Phil Romov [3:31 PM]
it should be on oracle but I haven’t connected yet

[3:32]
I got this connection information from Todd, maybe you can try and see if it works for you
```Your application's credentials are:
username:  apcts_rs
password:  apctsrs400

CTSDEV=
  (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST=actsdadl.sesac.com)
      (PORT=1527)
    )
    (CONNECT_DATA=
      (SERVICE_NAME=CTSDEV.sesac.com)
    )
  )

```

Rodrigo Maia [3:32 PM]
So, when some data is on Oracle what we used to do?

Phil Romov [3:32 PM]
we connect to it and query the report :slightly_smiling_face:

Rodrigo Maia [3:32 PM]
sweet

Phil Romov [3:32 PM]
table names should be: `mc_statement_details` and `mc_payment_details`

[3:33]
let me know if you have success connecting to that from ruby

[3:33]
and any questions remaining

Rodrigo Maia [3:44 PM]
Thanks :-)

[3:44]
another question, I want create a table in order to insert reports generated

[3:45]
I was thinking about `link.statement_reports`

[3:46]
save the entity (user, publisher, artist), the period (as you said, the check) and the status (ordered, builded, archived etc.)

[3:47]
if a user ask for the same report twice, we can return the generated report or avoid generate twice the same report.

Phil Romov [3:48 PM]
hmm, I don’t know if caching it in db is a good idea

[3:48]
because your report is generated from a select anyway

Rodrigo Maia [3:48 PM]
no, no. I don't want save the entire report, just the request to create it.

Phil Romov [3:49 PM]
oh yeah

Rodrigo Maia [3:49 PM]
The final report, I was considering save on AWS S3

[3:49]
The goal of this table is: If one user requires to generate one report, we save on it.

[3:49]
if another user request the same report, we respond with the link of s3 instead generate it again.

Phil Romov [3:50 PM]
sounds good

[3:50]
are you also using a queue to schedule report downloads?

Rodrigo Maia [3:50 PM]
It's planned to use.

[3:50]
I did't reach this step yet :-P

Phil Romov [3:50 PM]
i was thinking, maybe instead of saving request to `link.statement_reports` you can save it to the queue

[3:50]
i’m not sure what you save by also storing it in `link.statement_reports`

Rodrigo Maia [3:51 PM]
the worker will get the message from queue and check if the report already exists.

[3:51]
if exists, return the link, else generate it.

Phil Romov [3:52 PM]
I guess that works

Rodrigo Maia [3:52 PM]
Looks like I can check on s3 if already exists.

Phil Romov [3:52 PM]
yeah that’s what I was thinking

[3:52]
might not need `link.statement_reports` then, keep it simpler

Rodrigo Maia [3:52 PM]
I'll try on this way, checking if it already exists.

[3:53]
If I have some trouble, I'll back to this topic.

[3:54]
thanks for the infos :-)

[3:54]
I have enought to don't disturb you for today and tomorrow :-P