Update PO Inquiry
unknown
pgsql
3 years ago
6.3 kB
2
Indexable
Never
WITH ctedata AS ( SELECT DISTINCT co_1.ad_client_id, co_1.c_order_id, co_1.c_doctype_id, co_1.documentno, co_1.docstatus, co_1.dateordered, co_1.datepromised, co_1.user1_id, co_1.ad_org_id, co_1.ad_orgtrx_id, co_1.salesrep_id, co_1.c_bpartner_id, co_1.c_bpartner_location_id, co_1.c_bpartnersr_id, co_1.paymentrule, co_1.c_paymentterm_id, co_1.priorityrule, co_1.description, co_1.note, co_1.c_currency_id, co_1.grandtotal, mr.documentno AS mrno, mr.m_requisition_id, mr.c_doctype_id AS pr_c_doctype_id, cp.c_prroute_id, mr.budgetuse, CASE WHEN mr.c_doctype_id = 1000050::numeric THEN 1000000::numeric ELSE mr.c_prroute_id END AS department, mp.mp_requisition_id, mp.documentno AS mpno, ce.c_elementvalue_id, ce.value AS akunno, ce.name AS akunname, mpc.value AS glclass, po_line.line, po_line.m_product_id, prod.value AS item_no, prod.sku, prod.name AS item_name, po_line.description AS item_description, po_line.qtyentered, po_line.qtydelivered, COALESCE(po_line.qtyentered, 0::numeric) - COALESCE(po_line.qtydelivered, 0::numeric) AS os_delivery, po_line.qtyinvoiced, po_line.c_uom_id, po_line.priceactual, po_line.priceentered, po_line.discount, po_line.linenetamt, po_line.c_tax_id, prod.lco_withholdingcategory_id, prod.producttype, po_line.c_orderline_id, co_1.created, co_1.createdby, co_1.updated, co_1.updatedby FROM c_order co_1 JOIN c_orderline po_line ON co_1.c_order_id = po_line.c_order_id LEFT JOIN m_product prod ON prod.m_product_id = po_line.m_product_id LEFT JOIN m_product_category mpc ON mpc.m_product_category_id = prod.m_product_category_id LEFT JOIN m_product_acct mpa ON mpa.m_product_id = prod.m_product_id LEFT JOIN c_validcombination cv ON cv.c_validcombination_id = mpa.p_expense_acct LEFT JOIN c_elementvalue ce ON ce.c_elementvalue_id = cv.account_id LEFT JOIN m_requisitionline mrl ON mrl.m_requisitionline_id = po_line.m_requisitionline_id LEFT JOIN m_requisition mr ON mr.m_requisition_id = mrl.m_requisition_id LEFT JOIN c_prroute cp ON cp.c_prroute_id = mr.c_prroute_id LEFT JOIN mp_requisitionline mpr ON mpr.mp_requisitionline_id = mrl.mp_requisitionline_id LEFT JOIN mp_requisition mp ON mp.mp_requisition_id = mpr.mp_requisition_id WHERE co_1.docstatus <> 'VO'::bpchar ), ctemaxreceipt AS ( SELECT col.c_orderline_id, max(mi_1.m_inout_id) AS m_inout_id, (array_agg(mi_1.documentno ORDER BY mi_1.m_inout_id DESC))[1] AS receiptno FROM m_inout mi_1 JOIN m_inoutline mil ON mil.m_inout_id = mi_1.m_inout_id JOIN c_orderline col ON col.c_orderline_id = mil.c_orderline_id WHERE mi_1.docstatus <> 'VO'::bpchar GROUP BY col.c_orderline_id ), ctemaxcm AS ( SELECT col.c_orderline_id, max(mi_1.m_cargomanifest_id) AS m_cargomanifest_id, (array_agg(mi_1.documentno ORDER BY mi_1.m_cargomanifest_id DESC))[1] AS cmno FROM m_cargomanifest mi_1 JOIN m_cargomanifest_receipt mil ON mil.m_cargomanifest_id = mi_1.m_cargomanifest_id JOIN ctemaxreceipt col ON col.m_inout_id = mil.m_inout_id WHERE mi_1.docstatus <> 'VO'::bpchar GROUP BY col.c_orderline_id ) SELECT co.ad_client_id, co.c_doctype_id, co.documentno, co.docstatus, co.dateordered, co.datepromised, co.user1_id, co.ad_org_id, co.ad_orgtrx_id, co.salesrep_id, co.c_bpartner_id, co.c_bpartner_location_id, co.c_bpartnersr_id, co.paymentrule, co.c_paymentterm_id, co.priorityrule, co.description, co.note, co.c_currency_id, co.grandtotal, co.mrno, co.m_requisition_id, co.pr_c_doctype_id, co.c_prroute_id, co.budgetuse, co.department, co.mp_requisition_id, co.mpno, co.c_elementvalue_id, co.akunno, co.akunname, co.glclass, co.line, co.m_product_id, co.item_no, co.sku, co.item_name, co.item_description, co.qtyentered, co.qtydelivered, co.qtyinvoiced, co.os_delivery, co.c_uom_id, co.priceactual, co.discount, co.linenetamt, co.c_tax_id, co.lco_withholdingcategory_id, co.producttype, co.c_orderline_id, cmr.m_inout_id, cmr.receiptno, mi.docstatus AS receiptdocstatus, mi.c_doctype_id AS receiptdoctype, co.created, co.createdby, co.updated, co.updatedby, cmc.m_cargomanifest_id, cmc.cmno, mc.docstatus AS cmdocstatus, CASE WHEN mi.docstatus = ANY (ARRAY['CO'::bpchar, 'CL'::bpchar]) THEN mi.updated ELSE NULL::timestamp without time zone END AS receipt_date, CASE WHEN mi.docstatus = ANY (ARRAY['CO'::bpchar, 'CL'::bpchar]) THEN mi.updatedby ELSE NULL::numeric(10,0) END AS receiptby, co.c_order_id, co.priceentered, mil.qtyentered as qtygr FROM ctedata co LEFT JOIN ctemaxreceipt cmr ON cmr.c_orderline_id = co.c_orderline_id LEFT JOIN m_inout mi ON mi.m_inout_id = cmr.m_inout_id left join m_inoutline mil on mil.c_orderline_id = co.c_orderline_id LEFT JOIN ctemaxcm cmc ON cmc.c_orderline_id = co.c_orderline_id LEFT JOIN m_cargomanifest mc ON mc.m_cargomanifest_id = cmc.m_cargomanifest_id;