{"id":261,"date":"2008-01-25T14:39:25","date_gmt":"2008-01-25T12:39:25","guid":{"rendered":"http:\/\/oracle.malin.pl\/?p=261"},"modified":"2012-03-12T16:04:54","modified_gmt":"2012-03-12T14:04:54","slug":"sql-limity-kredytowe","status":"publish","type":"post","link":"http:\/\/oracle.malin.pl\/?p=261","title":{"rendered":"SQL &#8211; Limity Kredytowe"},"content":{"rendered":"<p>SQL pokazuje jakie ZS wchodzi w limit kredytowy<\/p>\n<p><!--more--><\/p>\n<pre lang=\"sql\">SELECT SUM(NVL(l.ordered_quantity, 0) * NVL(l.unit_selling_price, 0)) -\r\n\r\nSUM(NVL(p.commitment_applied_amount, 0)) Limit,\r\n\r\nh.order_number,\r\n\r\nl.flow_status_code,\r\n\r\nh.invoice_to_org_id,\r\n\r\ns.cust_account_id,\r\n\r\nca.party_id,\r\n\r\nh.org_id,\r\n\r\nh.transactional_curr_code\r\n\r\nFROM oe_order_lines_all \u00a0 \u00a0 \u00a0 \u00a0 l,\r\n\r\noe_order_headers_all \u00a0 \u00a0 h,\r\n\r\noe_payments \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 p,\r\n\r\nhz_cust_site_uses_all \u00a0 su,\r\n\r\nhz_cust_acct_sites_all s,\r\n\r\nhz_cust_accounts \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ca\r\n\r\nWHERE h.header_id = l.header_id\r\n\r\nAND h.credit_card_approval_date IS NULL\r\n\r\nAND h.booked_flag = 'Y'\r\n\r\nAND h.open_flag = 'Y'\r\n\r\nAND l.open_flag = 'Y'\r\n\r\nAND l.line_category_code = 'ORDER'\r\n\r\nAND NVL(l.invoiced_quantity, 0) = 0\r\n\r\nAND p.header_id(+) = l.header_id\r\n\r\nAND p.line_id(+) = l.line_id\r\n\r\nAND su.site_use_id = h.invoice_to_org_id\r\n\r\nAND su.cust_acct_site_id = s.cust_acct_site_id\r\n\r\nAND ca.cust_account_id = s.cust_account_id\r\n\r\nand s.cust_account_id in\r\n\r\n(select t.customer_id\r\n\r\nfrom apps.ra_customers t\r\n\r\nwhere upper(t.customer_name) like upper(&amp;klient))\r\n\r\nGROUP BY h.order_number,\r\n\r\nh.invoice_to_org_id,\r\n\r\nh.transactional_curr_code,\r\n\r\ns.cust_account_id,\r\n\r\nl.flow_status_code,\r\n\r\nca.party_id,\r\n\r\nh.org_id,\r\n\r\nTO_NUMBER(TO_CHAR(NVL(l.schedule_ship_date,\r\n\r\nNVL(l.request_date, h.request_date)),\r\n\r\n'J'))\r\n\r\nunion all\r\n\r\nSELECT SUM(NVL(l.tax_value, 0)),\r\n\r\nh.order_number,\r\n\r\nl.flow_status_code,\r\n\r\nh.invoice_to_org_id,\r\n\r\ns.cust_account_id,\r\n\r\nca.party_id,\r\n\r\nh.org_id,\r\n\r\nh.transactional_curr_code\r\n\r\nFROM oe_order_lines_all \u00a0 \u00a0 \u00a0 \u00a0 l,\r\n\r\noe_order_headers_all \u00a0 \u00a0 h,\r\n\r\nhz_cust_site_uses_all \u00a0 su,\r\n\r\nhz_cust_acct_sites_all s,\r\n\r\nhz_cust_accounts \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ca\r\n\r\nWHERE h.header_id = l.header_id\r\n\r\nAND h.credit_card_approval_date IS NULL\r\n\r\nAND h.booked_flag = 'Y'\r\n\r\nAND h.open_flag = 'Y'\r\n\r\nAND l.open_flag = 'Y'\r\n\r\nAND l.line_category_code = 'ORDER'\r\n\r\nAND NVL(l.invoiced_quantity, 0) = 0\r\n\r\nAND su.site_use_id = h.invoice_to_org_id\r\n\r\nAND su.cust_acct_site_id = s.cust_acct_site_id\r\n\r\nAND ca.cust_account_id = s.cust_account_id\r\n\r\nand s.cust_account_id in\r\n\r\n(select t.customer_id\r\n\r\nfrom apps.ra_customers t\r\n\r\nwhere upper(t.customer_name) like upper(&amp;klient))\r\n\r\nGROUP BY h.invoice_to_org_id,\r\n\r\nh.order_number,\r\n\r\nh.transactional_curr_code,\r\n\r\nl.flow_status_code,\r\n\r\ns.cust_account_id,\r\n\r\nca.party_id,\r\n\r\nh.org_id,\r\n\r\nTO_NUMBER(TO_CHAR(NVL(l.schedule_ship_date,\r\n\r\nNVL(l.request_date, h.request_date)),\r\n\r\n'J'))\r\n\r\norder by 2<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>SQL pokazuje jakie ZS wchodzi w limit kredytowy<\/p>\n","protected":false},"author":37,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[5,3],"tags":[225,226],"_links":{"self":[{"href":"http:\/\/oracle.malin.pl\/index.php?rest_route=\/wp\/v2\/posts\/261"}],"collection":[{"href":"http:\/\/oracle.malin.pl\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/oracle.malin.pl\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/oracle.malin.pl\/index.php?rest_route=\/wp\/v2\/users\/37"}],"replies":[{"embeddable":true,"href":"http:\/\/oracle.malin.pl\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=261"}],"version-history":[{"count":3,"href":"http:\/\/oracle.malin.pl\/index.php?rest_route=\/wp\/v2\/posts\/261\/revisions"}],"predecessor-version":[{"id":654,"href":"http:\/\/oracle.malin.pl\/index.php?rest_route=\/wp\/v2\/posts\/261\/revisions\/654"}],"wp:attachment":[{"href":"http:\/\/oracle.malin.pl\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=261"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/oracle.malin.pl\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=261"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/oracle.malin.pl\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=261"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}