SQL pokazuje jakie ZS wchodzi w limit kredytowy
SELECT SUM(NVL(l.ordered_quantity, 0) * NVL(l.unit_selling_price, 0)) - SUM(NVL(p.commitment_applied_amount, 0)) LIMIT, h.order_number, l.flow_status_code, h.invoice_to_org_id, s.cust_account_id, ca.party_id, h.org_id, h.transactional_curr_code FROM oe_order_lines_all l, oe_order_headers_all h, oe_payments p, hz_cust_site_uses_all su, hz_cust_acct_sites_all s, hz_cust_accounts ca WHERE h.header_id = l.header_id AND h.credit_card_approval_date IS NULL AND h.booked_flag = 'Y' AND h.open_flag = 'Y' AND l.open_flag = 'Y' AND l.line_category_code = 'ORDER' AND NVL(l.invoiced_quantity, 0) = 0 AND p.header_id(+) = l.header_id AND p.line_id(+) = l.line_id AND su.site_use_id = h.invoice_to_org_id AND su.cust_acct_site_id = s.cust_acct_site_id AND ca.cust_account_id = s.cust_account_id AND s.cust_account_id IN (SELECT t.customer_id FROM apps.ra_customers t WHERE UPPER(t.customer_name) LIKE UPPER(&klient)) GROUP BY h.order_number, h.invoice_to_org_id, h.transactional_curr_code, s.cust_account_id, l.flow_status_code, ca.party_id, h.org_id, TO_NUMBER(TO_CHAR(NVL(l.schedule_ship_date, NVL(l.request_date, h.request_date)), 'J')) UNION ALL SELECT SUM(NVL(l.tax_value, 0)), h.order_number, l.flow_status_code, h.invoice_to_org_id, s.cust_account_id, ca.party_id, h.org_id, h.transactional_curr_code FROM oe_order_lines_all l, oe_order_headers_all h, hz_cust_site_uses_all su, hz_cust_acct_sites_all s, hz_cust_accounts ca WHERE h.header_id = l.header_id AND h.credit_card_approval_date IS NULL AND h.booked_flag = 'Y' AND h.open_flag = 'Y' AND l.open_flag = 'Y' AND l.line_category_code = 'ORDER' AND NVL(l.invoiced_quantity, 0) = 0 AND su.site_use_id = h.invoice_to_org_id AND su.cust_acct_site_id = s.cust_acct_site_id AND ca.cust_account_id = s.cust_account_id AND s.cust_account_id IN (SELECT t.customer_id FROM apps.ra_customers t WHERE UPPER(t.customer_name) LIKE UPPER(&klient)) GROUP BY h.invoice_to_org_id, h.order_number, h.transactional_curr_code, l.flow_status_code, s.cust_account_id, ca.party_id, h.org_id, TO_NUMBER(TO_CHAR(NVL(l.schedule_ship_date, NVL(l.request_date, h.request_date)), 'J')) ORDER BY 2 |