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 in_summary_monthly_cogs D ON C.product_id = D.product_id AND C.date_year_month = D.date_year_month
- 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) <= TO_CHAR(TO_DATE(C.date_year_month,'YYYYMM') - INTERVAL '1 Month','YYYYMM')
- WHERE C.qty <> 0
- AND C.gl_amount = 0
- AND C.doc_type_id = 311
- AND D.qty_total = 0
- AND C.date_year_month >= '201608'
- ORDER BY C.date_year_month, C.product_id, B.doc_date DESC
- )
- SELECT A.tenant_id, A.ou_bu_id, A.date_year_month,
- A.product_id, A.curr_code, A.nett_price_po, 0, '20180213152301', -1,
- '20180213152301', -1
- 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