SELECT
t.contract AS "Depot Code"
, NVL(ifsapp.company_site_api.get_description(t.contract),'Total') AS "Depot name"
, t.current_month_supply_tp "Current Month Supply (TP) Performance" -- dom amount
, t.current_month_supply_tp_vat "Current Month Supply (TP+Vat)" --dom amount -discount- vat (DOM + VAT)
, t.collection_against_current_month_supply "Collection against current month supply" --this month invoice collection (cash + credit)
, t.collection_up_to_previous_month_credit "Collection upto Previous Month Credit" --
, t.total_collection "Total Collection" --
, t.today_total_collection "Today Total Collection" -- total collection
, t.today_advance_coll_amount "Today Advance coll Balance"
FROM
(
SELECT
cs.contract
, SUM(NVL(sa_value.total_tp_minus_discount,0)) current_month_supply_tp -- dom amount
, SUM(NVL(sa_value.total_tp_minus_discount,0)) + SUM(NVL(sa_value.vat_dom_amount,0)) current_month_supply_tp_vat --dom amount -discount- vat (DOM + VAT)
, SUM(NVL(cur_month_coll.cur_month_coll_amount,0)) collection_against_current_month_supply --this month invoice collection (cash + credit)
, SUM(NVL(pre_month_coll.coll_upto_pre_mont_amount,0)) collection_up_to_previous_month_credit --
, SUM(NVL(cur_month_coll.cur_month_coll_amount,0)) + SUM(NVL(pre_month_coll.coll_upto_pre_mont_amount,0)) total_collection
, SUM(NVL(cur_month_coll.today_coll_amount,0)) today_total_collection -- total collection
, SUM(NVL(cur_month_coll.today_adv_coll_amount,0)) today_advance_coll_amount
FROM ifsapp.company_site cs LEFT JOIN
(
select coiij.contract,
sum(nvl (coiij.vat_dom_amount, 0)) as vat_dom_amount,
-- sum(nvl (coiij.sale_unit_price, 0) * nvl (coiij.invoiced_qty, 0) tp,
-- suM(nvl (coiij.sale_unit_price, 0) * nvl (coiij.invoiced_qty, 0) * nvl (coiij.discount,0)/100 ) Discount_amount,
sum(nvl (coiij.sale_unit_price, 0) * nvl (coiij.invoiced_qty, 0) -
nvl (coiij.sale_unit_price, 0) * nvl (coiij.invoiced_qty, 0) * nvl (coiij.discount,0)/100 ) total_tp_minus_discount
from customer_order_inv_item_join coiij
where trunc(coiij.invoice_date) between to_date('&From_Date','dd/mm/yyyy') and to_date('&To_Date','dd/mm/yyyy')
AND coiij.contract BETWEEN '1003' AND '1029'
and ifsapp.customer_order_api.get_order_id (coiij.order_no) != 'BE'
-- and coiij.contract = '1015'
group by coiij.contract
) sa_value ON cs.contract = sa_value.contract
LEFT JOIN
(
-- current month
SELECT cpa.contract,
SUM (cpa.pay_amount) AS cur_month_coll_amount,
SUM (cpa.totay_pay_amount) AS today_coll_amount,
SUM (cpa.cupoa_pay_amount) AS today_adv_coll_amount
FROM (SELECT ledger_item_id,
ledger_item_series_id,
SUBSTR (ifsapp.payment_per_currency_api.get_short_name ( ltcq.company, ltcq.series_id, ltcq.payment_id,ltcq.currency), 1, 4) contract,
CASE WHEN ltcq.ledger_item_series_id IN ('CD', 'EX') THEN
NVL(DECODE ( is_new_ledger_item, 'FALSE', paid_amount - NVL (boe_discount_fee, 0), paid_amount), 0)
END pay_amount,
CASE WHEN ltcq.ledger_item_series_id IN ('CD', 'EX') AND TRUNC (ltcq.pay_date) = TO_DATE ('&To_Date', 'dd/mm/yyyy') THEN
NVL(DECODE ( is_new_ledger_item, 'FALSE', paid_amount - NVL (boe_discount_fee, 0), paid_amount), 0)
END TOTAY_PAY_AMOUNT,
CASE WHEN ltcq.ledger_item_series_id IN ('CUPOA') AND TRUNC (ltcq.pay_date) = TO_DATE ('&To_Date', 'dd/mm/yyyy') THEN
NVL ( DECODE ( is_new_ledger_item, 'FALSE', paid_amount - NVL (boe_discount_fee, 0), paid_amount), 0) -
(ifsapp.x_egp_customer_offset (ledger_item_series_id, ledger_item_id, TO_DATE ('&To_Date', 'dd/mm/yyyy')))
END cupoa_pay_amount
FROM ifsapp.ledger_transaction_cu_qry ltcq
WHERE ltcq.party_type_db = 'CUSTOMER'
AND ltcq.payment_type_code_db = 'CUSTPAY'
AND ltcq.rolledback = 'FALSE'
AND ltcq.correction = 'FALSE'
AND ltcq.ledger_item_series_id IN ('CD', 'EX', 'CUPOA')
AND TRUNC (ltcq.pay_date) BETWEEN TO_DATE ('&From_Date', 'dd/mm/yyyy') AND TO_DATE ('&To_Date', 'dd/mm/yyyy')
-- AND ifsapp.payment_per_currency_api.get_short_name (
-- ltcq.company,
-- ltcq.series_id,
-- ltcq.payment_id,
-- ltcq.currency) = '101402'
) cpa
LEFT JOIN
(SELECT coihua.series_id,
coihua.invoice_no,
coihua.invoice_date,
coihua.invoice_id,
coihua.contract
FROM ifsapp.cust_order_inv_head_uiv_all coihua
WHERE coihua.party_type = 'CUSTOMER'
AND TRUNC (coihua.invoice_date) BETWEEN TO_DATE ('&From_Date', 'dd/mm/yyyy') AND TO_DATE ('&To_Date', 'dd/mm/yyyy')
-- AND coihua.contract = '1014'
UNION ALL
SELECT mci.series_id,
mci.invoice_no,
mci.invoice_date,
mci.invoice_id,
ifsapp.cust_ord_customer_api.get_c_default_site (mci.identity) contract
FROM man_cust_invoice mci
WHERE mci.party_type_db = 'CUSTOMER'
AND mci.series_id = 'EX'
-- AND ifsapp.cust_ord_customer_api.get_c_default_site (
-- mci.identity) = '1014'
AND TRUNC (mci.invoice_date) BETWEEN TO_DATE ('&From_Date', 'dd/mm/yyyy') AND TO_DATE ('&To_Date', 'dd/mm/yyyy')
) inv_sale
ON inv_sale.invoice_no = cpa.ledger_item_id
AND inv_sale.series_id = cpa.ledger_item_series_id
GROUP BY cpa.contract
) cur_month_coll ON cs.contract = cur_month_coll.contract
LEFT JOIN
(
-- previous month
SELECT cpa.contract,
SUM (cpa.pay_amount) AS coll_upto_pre_mont_amount
FROM (SELECT ledger_item_id,
ledger_item_series_id,
SUBSTR (ifsapp.payment_per_currency_api.get_short_name ( ltcq.company, ltcq.series_id, ltcq.payment_id,ltcq.currency), 1, 4) contract,
CASE WHEN ltcq.ledger_item_series_id IN ('CD', 'EX') THEN
NVL(DECODE ( is_new_ledger_item, 'FALSE', paid_amount - NVL (boe_discount_fee, 0), paid_amount), 0)
END pay_amount
FROM ifsapp.ledger_transaction_cu_qry ltcq
WHERE ltcq.party_type_db = 'CUSTOMER'
AND ltcq.payment_type_code_db = 'CUSTPAY'
AND ltcq.rolledback = 'FALSE'
AND ltcq.correction = 'FALSE'
AND ltcq.ledger_item_series_id IN ('CD', 'EX')
AND TRUNC (ltcq.pay_date) BETWEEN TO_DATE ('&From_Date', 'dd/mm/yyyy') AND TO_DATE ('&To_Date', 'dd/mm/yyyy')
-- AND ifsapp.payment_per_currency_api.get_short_name (
-- ltcq.company,
-- ltcq.series_id,
-- ltcq.payment_id,
-- ltcq.currency) = '101402'
) cpa
LEFT JOIN
(SELECT coihua.series_id,
coihua.invoice_no,
coihua.invoice_date,
coihua.invoice_id,
coihua.contract
FROM ifsapp.cust_order_inv_head_uiv_all coihua
WHERE coihua.party_type = 'CUSTOMER'
AND TRUNC (coihua.invoice_date) <= LAST_DAY (ADD_MONTHS (to_date('&From_Date','dd/mm/yyyy'), -1))
-- AND coihua.contract = '1014'
UNION ALL
SELECT mci.series_id,
mci.invoice_no,
mci.invoice_date,
mci.invoice_id,
ifsapp.cust_ord_customer_api.get_c_default_site (mci.identity) contract
FROM man_cust_invoice mci
WHERE mci.party_type_db = 'CUSTOMER'
AND mci.series_id = 'EX'
-- AND ifsapp.cust_ord_customer_api.get_c_default_site (
-- mci.identity) = '1014'
AND TRUNC (mci.invoice_date) <= LAST_DAY (ADD_MONTHS (to_date('&From_Date','dd/mm/yyyy'), -1))
) inv_sale
ON inv_sale.invoice_no = cpa.ledger_item_id
AND inv_sale.series_id = cpa.ledger_item_series_id
GROUP BY cpa.contract
) pre_month_coll ON cs.contract = pre_month_coll.contract
WHERE cs.contract BETWEEN '1003' AND '1029'
GROUP BY ROLLUP(cs.contract)
) t
No comments:
Post a Comment