function download

```
TAGGEG SO-DO
```

SELECT f_get_partner_code(C.partner_id) AS partner_code, f_get_partner_name(C.partner_id) AS partner_name,
       E.doc_no AS do_no, E.doc_date AS do_date, C.doc_no AS so_no, C.doc_date AS so_date,
       SUM(D.gross_sell_price) AS price_so, f_get_brand_name(H.brand_id) AS brand_name, SUM(H.gross_sell_price) AS master_price,
       SUM(D.discount_amount) AS discount_amount, SUM(D.nett_item_amount) AS total_amount,
       f_get_promo_code(A.promo_id) AS promo_code, f_get_promo_code(A.product_launching_id) AS product_launching,
       K.sub_promo_code, COALESCE(SUM(L.coin_promo), 0) AS coin_promo, COALESCE(SUM(L.coin_launching), 0) AS coin_launching,
       COALESCE(SUM(L.coin_sub_promo), 0) AS coin_sub_promo,
       COALESCE(SUM(L.coin_periodic_adjustment), 0) AS coin_periodic_adjustment, COALESCE(SUM(L.coin_total), 0) AS coin_total
FROM sl_invoice_balance_promo_coin A
INNER JOIN sl_invoice B ON A.invoice_id = B.invoice_id
INNER JOIN sl_so C ON B.ref_id = C.so_id AND B.ref_doc_type_id = C.doc_type_id
INNER JOIN sl_so_item D ON C.so_id = D.so_id
INNER JOIN sl_do E ON C.so_id = E.ref_id AND C.doc_type_id = E.ref_doc_type_id
INNER JOIN sl_do_item F ON E.do_id = F.do_id AND D.so_item_id = F.ref_id
INNER JOIN m_product G ON D.product_id = G.product_id
INNER JOIN m_sell_price_by_brand H ON G.brand_id = H.brand_id
INNER JOIN sl_coin_promo_balance L ON C.partner_id = L.partner_id AND A.promo_id = L.promo_id
INNER JOIN m_sub_promo K ON A.sub_promo_id = K.sub_promo_id
WHERE A.promo_id = 34
GROUP BY C.partner_id, E.doc_no, E.doc_date, C.doc_no, C.doc_date,
    H.brand_id, A.promo_id, A.product_launching_id,
    K.sub_promo_code;

```
NON TAGGED SO-DO
```
SELECT f_get_partner_code(C.partner_id) AS partner_code, f_get_partner_name(C.partner_id) AS partner_name,
       E.doc_no AS do_no, E.doc_date AS do_date, C.doc_no AS so_no, C.doc_date AS so_date,
       SUM(D.gross_sell_price) AS price_so, f_get_brand_name(H.brand_id) AS brand_name, SUM(H.gross_sell_price) AS master_price,
       SUM(D.discount_amount) AS discount_amount, SUM(D.nett_item_amount) AS total_amount,
       f_get_promo_code(A.promo_id) AS promo_code, f_get_promo_code(A.product_launching_id) AS product_launching,
       K.sub_promo_code, COALESCE(SUM(L.coin_promo), 0) AS coin_promo, COALESCE(SUM(L.coin_launching), 0) AS coin_launching,
       COALESCE(SUM(L.coin_sub_promo), 0) AS coin_sub_promo,
       COALESCE(SUM(L.coin_periodic_adjustment), 0) AS coin_periodic_adjustment, COALESCE(SUM(L.coin_total), 0) AS coin_total
FROM sl_invoice_balance_promo_coin A
INNER JOIN sl_invoice B ON A.invoice_id = B.invoice_id
INNER JOIN sl_so C ON B.ref_id = C.so_id AND B.ref_doc_type_id = C.doc_type_id
INNER JOIN sl_so_item D ON C.so_id = D.so_id
INNER JOIN sl_do E ON C.so_id = E.ref_id AND C.doc_type_id = E.ref_doc_type_id
INNER JOIN sl_do_item F ON E.do_id = F.do_id AND D.so_item_id = F.ref_id
INNER JOIN m_product G ON D.product_id = G.product_id
INNER JOIN m_sell_price_by_brand H ON G.brand_id = H.brand_id
LEFT OUTER JOIN sl_coin_promo_balance L ON C.partner_id = L.partner_id AND A.promo_id = L.promo_id
LEFT OUTER JOIN m_sub_promo K ON A.sub_promo_id = K.sub_promo_id
WHERE A.promo_id = -99 AND B.doc_date BETWEEN '20170826' AND '20171001'
GROUP BY C.partner_id, E.doc_no, E.doc_date, C.doc_no, C.doc_date,
    H.brand_id, A.promo_id, A.product_launching_id,
    K.sub_promo_code;


```
DO R - TAGGED
```
SELECT f_get_partner_code(C.partner_id) AS partner_code, f_get_partner_name(C.partner_id) AS partner_name,
       M.doc_no AS do_no, M.doc_date AS do_date, C.doc_no AS so_no, C.doc_date AS so_date,
       SUM(D.gross_sell_price) AS price_so, f_get_brand_name(H.brand_id) AS brand_name, SUM(H.gross_sell_price) AS master_price,
       SUM(D.discount_amount) AS discount_amount, SUM(D.nett_item_amount) AS total_amount,
       f_get_promo_code(A.promo_id) AS promo_code, f_get_promo_code(A.product_launching_id) AS product_launching,
       K.sub_promo_code, COALESCE(SUM(L.coin_promo), 0) AS coin_promo, COALESCE(SUM(L.coin_launching), 0) AS coin_launching,
       COALESCE(SUM(L.coin_sub_promo), 0) AS coin_sub_promo,
       COALESCE(SUM(L.coin_periodic_adjustment), 0) AS coin_periodic_adjustment, COALESCE(SUM(L.coin_total), 0) AS coin_total
FROM sl_invoice_balance_promo_coin A
INNER JOIN sl_invoice B ON A.invoice_id = B.invoice_id
INNER JOIN sl_so C ON B.ref_id = C.so_id AND B.ref_doc_type_id = C.doc_type_id
INNER JOIN sl_so_item D ON C.so_id = D.so_id
INNER JOIN sl_do E ON C.so_id = E.ref_id AND C.doc_type_id = E.ref_doc_type_id
INNER JOIN sl_do_item F ON E.do_id = F.do_id AND D.so_item_id = F.ref_id
INNER JOIN in_do_receipt M ON M.ref_id = E.do_id AND M.ref_doc_type_id = E.doc_type_id
INNER JOIN m_product G ON D.product_id = G.product_id
INNER JOIN m_sell_price_by_brand H ON G.brand_id = H.brand_id
INNER JOIN sl_coin_promo_balance L ON C.partner_id = L.partner_id AND A.promo_id = L.promo_id
INNER JOIN m_sub_promo K ON A.sub_promo_id = K.sub_promo_id
WHERE A.promo_id = 34
GROUP BY C.partner_id, M.doc_no, M.doc_date, C.doc_no, C.doc_date,
    H.brand_id, A.promo_id, A.product_launching_id,
    K.sub_promo_code;

```
DOR NO - TAGGED
```
SELECT f_get_partner_code(C.partner_id) AS partner_code, f_get_partner_name(C.partner_id) AS partner_name,
       M.doc_no AS do_no, M.doc_date AS do_date, C.doc_no AS so_no, C.doc_date AS so_date,
       SUM(D.gross_sell_price) AS price_so, f_get_brand_name(H.brand_id) AS brand_name, SUM(H.gross_sell_price) AS master_price,
       SUM(D.discount_amount) AS discount_amount, SUM(D.nett_item_amount) AS total_amount,
       f_get_promo_code(A.promo_id) AS promo_code, f_get_promo_code(A.product_launching_id) AS product_launching,
       K.sub_promo_code, COALESCE(SUM(L.coin_promo), 0) AS coin_promo, COALESCE(SUM(L.coin_launching), 0) AS coin_launching,
       COALESCE(SUM(L.coin_sub_promo), 0) AS coin_sub_promo,
       COALESCE(SUM(L.coin_periodic_adjustment), 0) AS coin_periodic_adjustment, COALESCE(SUM(L.coin_total), 0) AS coin_total
FROM sl_invoice_balance_promo_coin A
INNER JOIN sl_invoice B ON A.invoice_id = B.invoice_id
INNER JOIN sl_so C ON B.ref_id = C.so_id AND B.ref_doc_type_id = C.doc_type_id
INNER JOIN sl_so_item D ON C.so_id = D.so_id
INNER JOIN sl_do E ON C.so_id = E.ref_id AND C.doc_type_id = E.ref_doc_type_id
INNER JOIN sl_do_item F ON E.do_id = F.do_id AND D.so_item_id = F.ref_id
INNER JOIN in_do_receipt M ON M.ref_id = E.do_id AND M.ref_doc_type_id = E.doc_type_id
INNER JOIN m_product G ON D.product_id = G.product_id
INNER JOIN m_sell_price_by_brand H ON G.brand_id = H.brand_id
LEFT OUTER JOIN sl_coin_promo_balance L ON C.partner_id = L.partner_id AND A.promo_id = L.promo_id
LEFT OUTER JOIN m_sub_promo K ON A.sub_promo_id = K.sub_promo_id
WHERE A.promo_id = -99 AND B.doc_date BETWEEN '20170826' AND '20171001'
GROUP BY C.partner_id, M.doc_no, M.doc_date, C.doc_no, C.doc_date,
    H.brand_id, A.promo_id, A.product_launching_id,
    K.sub_promo_code;

```
RRSI TAGGING
```

SELECT *
FROM sl_invoice_balance_promo_coin A
INNER JOIN sl_invoice B ON A.invoice_id = B.invoice_id
INNER JOIN sl_request_return_sales C ON B.ref_doc_type_id = C.ref_doc_type_id AND B.ref_id = C.ref_id
INNER JOIN sl_request_return_sales_brand_item D ON C.request_return_sales_id = D.request_return_sales_id
INNER JOIN m_sell_price_by_brand H ON D.brand_id = H.brand_id
INNER JOIN sl_coin_promo_balance L ON C.partner_id = L.partner_id AND A.promo_id = L.promo_id
INNER JOIN m_sub_promo K ON A.sub_promo_id = K.sub_promo_id
WHERE A.promo_id = 34;

```
RRSI NON TAGGING
```
SELECT *
FROM sl_invoice_balance_promo_coin A
INNER JOIN sl_invoice B ON A.invoice_id = B.invoice_id
INNER JOIN sl_request_return_sales C ON B.ref_doc_type_id = C.ref_doc_type_id AND B.ref_id = C.ref_id
INNER JOIN sl_request_return_sales_brand_item D ON C.request_return_sales_id = D.request_return_sales_id
INNER JOIN m_sell_price_by_brand H ON D.brand_id = H.brand_id
INNER JOIN sl_coin_promo_balance L ON C.partner_id = L.partner_id AND A.promo_id = L.promo_id
INNER JOIN m_sub_promo K ON A.sub_promo_id = K.sub_promo_id
WHERE A.promo_id = -99 AND B.doc_date BETWEEN '20170826' AND '20171001';


```
ADJ
```
SELECT f_get_partner_code(B.partner_id) AS partner_code, f_get_partner_name(B.partner_id) AS partner_name,
    A.doc_no, A.doc_date, '', '', '', '', '', '', '', '', '', '', '', COALESCE(SUM(adjustment_coin), 0) AS coin_promo,
    '', '', '', '', ''
FROM sl_adjustment_coin A
INNER JOIN sl_adjustment_coin_customer B ON A.adjustment_coin_id = B.adjustment_coin_id
GROUP BY B.partner_id, A.doc_no, A.doc_date;