Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- WITH tt_standard_price AS (
- SELECT DISTINCT ON (C.date_year_month, C.product_id) B.doc_date, f_get_product_code(C.product_id) AS product_code,
- f_get_product_name(C.product_id) AS product_name,
- C.product_id, C.date_year_month,
- A.curr_code, A.nett_price_po,
- C.tenant_id, C.ou_bu_id
- FROM in_summary_monthly_amount C
- INNER JOIN pu_po_item A ON A.product_id = C.product_id AND A.tenant_id = C.tenant_id
- INNER JOIN pu_po B ON A.po_id = B.po_id AND C.ou_bu_id = B.ou_id AND SUBSTR(B.doc_date, 1, 6) <= C.date_year_month
- WHERE C.qty <> 0
- AND C.gl_amount = 0
- AND C.doc_type_id = 311
- and not exists (
- select 1 from in_summary_monthly_cogs B
- where C.product_id = B.product_id
- and C.date_year_month = B.date_year_month
- and B.qty_total <> 0)
- ORDER BY C.date_year_month, C.product_id, B.doc_date DESC
- )
- SELECT A.date_year_month,
- f_get_product_code(A.product_id), f_get_product_name(A.product_id), A.curr_code, A.nett_price_po
- FROM tt_standard_price A
- WHERE NOT EXISTS(
- SELECT 1
- FROM in_product_standard_cogs B
- WHERE B.tenant_id = A.tenant_id
- AND B.ou_id = A.ou_bu_id
- AND B.year_month_date = A.date_year_month
- AND B.product_id = A.product_id
- )
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement