[Home] [Help]
49: --| Jan-15-2004 vto 3364726. Check G_crmgmt_installed directly |
50: --| instead of = TRUE. Removed some unnec NVLs |
51: --| Jan-16-2004 tsimmond Bug fix #3377881. In get_transaction_amount |
52: --| for order level credit check in sql statmnts|
53: --| added join between ra_terms_b and oe_order_lines|
54: --| t.term_id = l.payment_term_id |
55: --| Jan-21-2004 vto 3388857. Current order should not be limited |
56: --| by shipping horizon. |
57: --| Feb-05-2004 vto 3426436: Fix 3377881 as it is complete. |
2398: , ln.ordered_quantity ordered_quantity
2399: , ln.unit_selling_price selling_price
2400: , ln.tax_value tax_value
2401: FROM
2402: OE_ORDER_LINES ln
2403: , mtl_item_categories ctg
2404: , ra_terms_b trm
2405: WHERE ln.header_id = p_header_id
2406: AND ln.invoice_to_org_id = NVL(l_site_use_id, ln.invoice_to_org_id )
4546: NVL(NVL(l.tax_line_value,l.tax_value),0), 0 ) --TaxER
4547: + ( l.unit_selling_price * l.ordered_quantity )
4548: )
4549: INTO l_order_value
4550: FROM oe_order_lines l,
4551: oe_order_headers_all h,
4552: ra_terms_b t,
4553: HZ_CUST_SITE_USES_ALL su,
4554: HZ_CUST_ACCT_SITES_ALL cas
4589: (DECODE(l.line_category_code,'RETURN',(-1)*l.ordered_quantity,
4590: l.ordered_quantity) )
4591: ))
4592: INTO l_order_value
4593: FROM oe_order_lines l,
4594: oe_order_headers_all h,
4595: ra_terms_b t,
4596: HZ_CUST_SITE_USES_ALL su,
4597: HZ_CUST_ACCT_SITES_ALL cas
4632: END IF;
4633:
4634: SELECT NVL(SUM(P.commitment_applied_amount), 0)
4635: INTO l_commitment
4636: FROM OE_PAYMENTS P, OE_ORDER_HEADERS_ALL H, OE_ORDER_LINES L ,
4637: HZ_CUST_SITE_USES_ALL su,
4638: HZ_CUST_ACCT_SITES_ALL cas,
4639: ra_terms_b t
4640: WHERE h.header_id = p_header_id
4698: + DECODE(p_credit_check_rule_rec.include_tax_flag ,'Y',DECODE(p.list_line_type_code,'TAX',p.adjusted_amount,0),0)
4699: --TaxER End
4700: )
4701: INTO l_freights
4702: FROM oe_order_lines l,
4703: oe_order_headers_all h,
4704: ra_terms_b t,
4705: HZ_CUST_SITE_USES_ALL su,
4706: HZ_CUST_ACCT_SITES_ALL cas,
4758: + DECODE(p_credit_check_rule_rec.include_tax_flag ,'Y',DECODE(p.list_line_type_code,'TAX',p.adjusted_amount,0),0)
4759: --TaxER End
4760: )
4761: INTO l_freights
4762: FROM oe_order_lines l,
4763: oe_order_headers_all h,
4764: ra_terms_b t,
4765: HZ_CUST_SITE_USES_ALL su,
4766: HZ_CUST_ACCT_SITES_ALL cas,
4821: NVL(NVL(l.tax_line_value,l.tax_value),0), 0 ) --TaxER
4822: + ( l.unit_selling_price * l.ordered_quantity )
4823: )
4824: INTO l_order_value
4825: FROM oe_order_lines l,
4826: oe_order_headers_all h,
4827: ra_terms_b t
4828: WHERE h.header_id = p_header_id
4829: AND h.org_id = l.org_id /* MOAC_SQL_CHANGE */
4859: + (l.unit_selling_price * DECODE(l.line_category_code,'RETURN',
4860: (-1)*l.ordered_quantity,l.ordered_quantity))
4861: )
4862: INTO l_order_value
4863: FROM oe_order_lines l,
4864: oe_order_headers_all h,
4865: ra_terms_b t
4866: WHERE h.header_id = p_header_id
4867: AND h.org_id = l.org_id /* MOAC_SQL_CHANGE */
4894: SELECT NVL(SUM(P.commitment_applied_amount), 0)
4895: INTO l_commitment
4896: FROM OE_PAYMENTS P,
4897: OE_ORDER_HEADERS_ALL H,
4898: OE_ORDER_LINES L,
4899: ra_terms_b t
4900: WHERE h.header_id = p_header_id
4901: AND h.org_id = l.org_id /* MOAC_SQL_CHANGE */
4902: AND h.header_id = l.header_id
4957: + DECODE(p_credit_check_rule_rec.include_tax_flag ,'Y',DECODE(p.list_line_type_code,'TAX',p.adjusted_amount,0),0)
4958: --TaxER End
4959: )
4960: INTO l_freights
4961: FROM oe_order_lines l,
4962: oe_order_headers_all h,
4963: ra_terms_b t,
4964: oe_price_adjustments p
4965: WHERE h.header_id = p_header_id
5014: + DECODE(p_credit_check_rule_rec.include_tax_flag ,'Y',DECODE(p.list_line_type_code,'TAX',p.adjusted_amount,0),0)
5015: --TaxER End
5016: )
5017: INTO l_freights
5018: FROM oe_order_lines l,
5019: oe_order_headers_all h,
5020: ra_terms_b t,
5021: oe_price_adjustments p
5022: WHERE h.header_id = p_header_id
5075: NVL(NVL(l.tax_line_value,l.tax_value),0), 0 ) --TaxER
5076: + ( l.unit_selling_price * l.ordered_quantity )
5077: )
5078: INTO l_order_value
5079: FROM oe_order_lines l,
5080: oe_order_headers_all h,
5081: ra_terms_b t
5082: WHERE h.header_id = p_header_id
5083: AND h.org_id = l.org_id /* MOAC_SQL_CHANGE */
5112: + (l.unit_selling_price * DECODE(l.line_category_code,'RETURN',
5113: (-1)*l.ordered_quantity,l.ordered_quantity))
5114: )
5115: INTO l_order_value
5116: FROM oe_order_lines l,
5117: oe_order_headers_all h,
5118: ra_terms_b t
5119: WHERE h.header_id = p_header_id
5120: AND h.org_id = l.org_id /* MOAC_SQL_CHANGE */
5151: END IF;
5152:
5153: SELECT NVL(SUM(P.commitment_applied_amount), 0)
5154: INTO l_commitment
5155: FROM oe_order_lines l,
5156: oe_order_headers_all h,
5157: ra_terms_b t,
5158: oe_payments p
5159: WHERE h.header_id = p_header_id
5235: --TaxER End
5236: )
5237: INTO l_freights_hdr_1
5238: FROM oe_price_adjustments p
5239: , oe_order_lines l
5240: , oe_order_headers_all h
5241: , ra_terms_b t
5242: WHERE h.header_id = p_header_id
5243: AND h.org_id = l.org_id /* MOAC_SQL_CHANGE */
5313: --TaxER End
5314: )
5315: INTO l_freights_hdr_1
5316: FROM oe_price_adjustments p
5317: , oe_order_lines l
5318: , oe_order_headers_all h
5319: , ra_terms_b t
5320: WHERE h.header_id = p_header_id
5321: AND h.org_id = l.org_id /* MOAC_SQL_CHANGE */
6031: ( l.ordered_quantity * l.unit_selling_price )
6032: + DECODE(l_include_tax_flag, 'Y',
6033: NVL(NVL(l.tax_line_value,l.tax_value),0), 0 ) --TaxER
6034: )
6035: FROM oe_order_lines_all l
6036: , oe_order_headers h
6037: , hz_cust_site_uses_all su
6038: , hz_cust_acct_sites_all cas
6039: WHERE h.invoice_to_org_id = su.site_use_id
6070: * l.unit_selling_price )
6071: + DECODE(l_include_tax_flag, 'Y',
6072: NVL(DECODE(l.line_category_code,'RETURN',(-1)*NVL(l.tax_line_value,l.tax_value),NVL(l.tax_line_value,l.tax_value)),0),0 --TaxER
6073: ))
6074: FROM oe_order_lines l
6075: , oe_order_headers_all h
6076: , hz_cust_site_uses_all su
6077: , hz_cust_acct_sites_all cas
6078: WHERE h.invoice_to_org_id = su.site_use_id
6105: ------------------------------------------------
6106:
6107: Cursor customer_commitment_total (p_curr_code IN VARCHAR2 default NULL) IS
6108: SELECT NVL(SUM(P.commitment_applied_amount), 0)
6109: FROM OE_PAYMENTS P, OE_ORDER_HEADERS_ALL H, OE_ORDER_LINES L
6110: , hz_cust_site_uses_all su
6111: , hz_cust_acct_sites_all cas
6112: WHERE H.invoice_to_org_id = su.site_use_id
6113: AND h.org_id = su.org_id
6136: OR
6137: (l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
6138: Cursor site_commitment_total (p_curr_code IN VARCHAR2 default NULL) IS
6139: SELECT NVL(SUM(P.commitment_applied_amount), 0)
6140: FROM OE_PAYMENTS P, OE_ORDER_HEADERS_ALL H, OE_ORDER_LINES L
6141: WHERE H.INVOICE_TO_ORG_ID = p_site_use_id
6142: AND H.TRANSACTIONAL_CURR_CODE = p_curr_code
6143: AND H.OPEN_FLAG = 'Y'
6144: AND H.BOOKED_FLAG = 'Y'
6171: (l.ordered_quantity * l.unit_selling_price )
6172: + DECODE(l_include_tax_flag, 'Y',
6173: NVL(NVL(l.tax_line_value,l.tax_value),0), 0 ) --TaxER
6174: )
6175: FROM oe_order_lines l
6176: , oe_order_headers_all h
6177: , hz_cust_site_uses_all su
6178: , hz_cust_acct_sites_all cas
6179: WHERE h.invoice_to_org_id = su.site_use_id
6214: * l.unit_selling_price )
6215: + DECODE(l_include_tax_flag, 'Y',
6216: NVL(DECODE(l.line_category_code,'RETURN',(-1)*NVL(l.tax_line_value,l.tax_value),NVL(l.tax_line_value,l.tax_value)),0),0 ) --TaxER
6217: )
6218: FROM oe_order_lines l
6219: , oe_order_headers_all h
6220: , hz_cust_site_uses_all su
6221: , hz_cust_acct_sites_all cas
6222: WHERE h.invoice_to_org_id = su.site_use_id
6253: (l.ordered_quantity * l.unit_selling_price )
6254: + DECODE(l_include_tax_flag, 'Y',
6255: NVL(NVL(l.tax_line_value,l.tax_value),0), 0 ) --TaxER
6256: )
6257: FROM oe_order_lines l
6258: , oe_order_headers_all h
6259: , hz_cust_site_uses_all su
6260: , hz_cust_acct_sites_all cas
6261: WHERE h.invoice_to_org_id = su.site_use_id
6296: * l.unit_selling_price )
6297: + DECODE(l_include_tax_flag, 'Y',
6298: NVL(DECODE(l.line_category_code,'RETURN',(-1)*NVL(l.tax_line_value,l.tax_value),NVL(l.tax_line_value,l.tax_value)),0),0 ) --TaxER
6299: )
6300: FROM oe_order_lines l
6301: , oe_order_headers_all h
6302: , hz_cust_site_uses_all su
6303: , hz_cust_acct_sites_all cas
6304: WHERE h.invoice_to_org_id = su.site_use_id
6344: + DECODE(l_include_tax_flag,'Y',DECODE(p.list_line_type_code,'TAX',p.adjusted_amount,0),0)
6345: --TaxER End
6346: )
6347: FROM oe_price_adjustments p
6348: , oe_order_lines l
6349: , oe_order_headers_all h
6350: , hz_cust_site_uses_all su
6351: , hz_cust_acct_sites_all cas
6352: WHERE h.invoice_to_org_id = su.site_use_id
6394: + DECODE(l_include_tax_flag,'Y',DECODE(p.list_line_type_code,'TAX',p.adjusted_amount,0),0)
6395: --TaxER End
6396: )
6397: FROM oe_price_adjustments p
6398: , oe_order_lines l
6399: , oe_order_headers_all h
6400: , hz_cust_site_uses_all su
6401: , hz_cust_acct_sites_all cas
6402: WHERE h.invoice_to_org_id = su.site_use_id
6463: AND h.header_id <> l_header_id
6464: AND EXISTS
6465: (SELECT NULL
6466: FROM oe_payment_types_all pt,
6467: oe_order_lines l
6468: WHERE pt.credit_check_flag = 'Y'
6469: AND l.header_id = h.header_id
6470: AND l.org_id = pt.org_id /* MOAC_SQL_CHANGE */
6471: AND NVL(pt.org_id, -99) = G_ORG_ID
6510: AND h.header_id <> l_header_id
6511: AND EXISTS
6512: (SELECT NULL
6513: FROM oe_payment_types_all pt,
6514: oe_order_lines l
6515: WHERE pt.credit_check_flag = 'Y'
6516: AND l.header_id = h.header_id
6517: AND l.org_id = pt.org_id /* MOAC_SQL_CHANGE */
6518: AND NVL(pt.org_id, -99) = G_ORG_ID
6535: + DECODE(l_include_tax_flag,'Y',DECODE(p.list_line_type_code,'TAX',p.adjusted_amount,0),0)
6536: --TaxER End
6537: )
6538: FROM oe_price_adjustments p
6539: , oe_order_lines l
6540: , oe_order_headers_all h
6541: , hz_cust_site_uses_all su
6542: , hz_cust_acct_sites_all cas
6543: WHERE h.invoice_to_org_id = su.site_use_id
6589: + DECODE(l_include_tax_flag,'Y',DECODE(p.list_line_type_code,'TAX',p.adjusted_amount,0),0)
6590: --TaxER End
6591: )
6592: FROM oe_price_adjustments p
6593: , oe_order_lines l
6594: , oe_order_headers_all h
6595: , hz_cust_site_uses_all su
6596: , hz_cust_acct_sites_all cas
6597: WHERE h.invoice_to_org_id = su.site_use_id
6642: + DECODE(l_include_tax_flag,'Y',DECODE(p.list_line_type_code,'TAX',p.adjusted_amount,0),0)
6643: --TaxER End
6644: )
6645: FROM oe_price_adjustments p
6646: , oe_order_lines l
6647: , oe_order_headers_all h
6648: , hz_cust_site_uses_all su
6649: , hz_cust_acct_sites_all cas
6650: WHERE h.invoice_to_org_id = su.site_use_id
6697: + DECODE(l_include_tax_flag,'Y',DECODE(p.list_line_type_code,'TAX',p.adjusted_amount,0),0)
6698: --TaxER End
6699: )
6700: FROM oe_price_adjustments p
6701: , oe_order_lines l
6702: , oe_order_headers_all h
6703: , hz_cust_site_uses_all su
6704: , hz_cust_acct_sites_all cas
6705: WHERE h.invoice_to_org_id = su.site_use_id
6774: AND oh.hold_release_id IS NULL )
6775: AND EXISTS
6776: (SELECT NULL
6777: FROM oe_payment_types_all pt,
6778: oe_order_lines_all l --Performance issue (SQL ID-14880869 FTS on OE_ORDER_HOLDS_ALL)
6779: WHERE pt.credit_check_flag = 'Y'
6780: AND l.header_id = h.header_id
6781: AND l.org_id = pt.org_id /* MOAC_SQL_CHANGE */
6782: AND NVL(pt.org_id, -99) = G_ORG_ID
6823: AND oh.hold_release_id IS NULL )
6824: AND EXISTS
6825: (SELECT NULL
6826: FROM oe_payment_types_all pt,
6827: oe_order_lines_all l --Performance issue (SQL ID-14880892 FTS on OE_ORDER_HOLDS_ALL)
6828: WHERE pt.credit_check_flag = 'Y'
6829: AND l.header_id = h.header_id
6830: AND l.org_id = pt.org_id /* MOAC_SQL_CHANGE */
6831: AND NVL(pt.org_id, -99) = G_ORG_ID
6871: AND oh.hold_release_id IS NULL )
6872: AND EXISTS
6873: (SELECT NULL
6874: FROM oe_payment_types_all pt,
6875: oe_order_lines_all l --Performance issue (SQL ID-14880915 FTS on OE_ORDER_HOLDS_ALL)
6876: WHERE pt.credit_check_flag = 'Y'
6877: AND l.header_id = h.header_id
6878: AND l.org_id = pt.org_id /* MOAC_SQL_CHANGE */
6879: AND NVL(pt.org_id, -99) = G_ORG_ID
6920: AND oh.hold_release_id IS NULL )
6921: AND EXISTS
6922: (SELECT NULL
6923: FROM oe_payment_types_all pt,
6924: oe_order_lines l
6925: WHERE pt.credit_check_flag = 'Y'
6926: AND l.header_id = h.header_id
6927: AND l.org_id = pt.org_id /* MOAC_SQL_CHANGE */
6928: AND NVL(pt.org_id, -99) = G_ORG_ID
7043: ( l.ordered_quantity * l.unit_selling_price )
7044: + DECODE(l_include_tax_flag, 'Y',
7045: NVL(NVL(l.tax_line_value,l.tax_value),0), 0 ) --TaxER
7046: )
7047: FROM oe_order_lines l
7048: , oe_order_headers_all h
7049: WHERE h.invoice_to_org_id = p_site_use_id
7050: AND h.header_id = l.header_id
7051: AND h.org_id = l.org_id /* MOAC_SQL_CHANGE */
7079: * l.unit_selling_price )
7080: + DECODE(l_include_tax_flag, 'Y',
7081: NVL(DECODE(l.line_category_code,'RETURN',(-1)*NVL(l.tax_line_value,l.tax_value),NVL(l.tax_line_value,l.tax_value)),0),0 ) --TaxER
7082: )
7083: FROM oe_order_lines l
7084: , oe_order_headers_all h
7085: WHERE h.invoice_to_org_id = p_site_use_id
7086: AND h.header_id = l.header_id
7087: AND h.org_id = l.org_id /* MOAC_SQL_CHANGE */
7111: + DECODE( l_include_tax_flag, 'Y',
7112: NVL(NVL(l.tax_line_value,l.tax_value),0), 0 --TaxER
7113: )
7114: )
7115: FROM oe_order_lines l
7116: , oe_order_headers_all h
7117: WHERE h.invoice_to_org_id = p_site_use_id
7118: AND h.header_id = l.header_id
7119: AND h.org_id = l.org_id /* MOAC_SQL_CHANGE */
7151: * l.unit_selling_price )
7152: + DECODE(l_include_tax_flag, 'Y',
7153: NVL(DECODE(l.line_category_code,'RETURN',(-1)*NVL(l.tax_line_value,l.tax_value),NVL(l.tax_line_value,l.tax_value)),0),0 ) --TaxER
7154: )
7155: FROM oe_order_lines l
7156: , oe_order_headers_all h
7157: WHERE h.invoice_to_org_id = p_site_use_id
7158: AND h.header_id = l.header_id
7159: AND h.org_id = l.org_id /* MOAC_SQL_CHANGE */
7185: + DECODE( l_include_tax_flag, 'Y',
7186: NVL(NVL(l.tax_line_value,l.tax_value),0), 0 --TaxER
7187: )
7188: )
7189: FROM oe_order_lines l
7190: , oe_order_headers_all h
7191: WHERE h.invoice_to_org_id = p_site_use_id
7192: AND h.header_id = l.header_id
7193: AND h.org_id = l.org_id /* MOAC_SQL_CHANGE */
7222: ( DECODE(l.line_category_code,'RETURN',(-1)*l.ordered_quantity,l.ordered_quantity) * l.unit_selling_price )
7223: + DECODE(l_include_tax_flag, 'Y',
7224: NVL(DECODE(l.line_category_code,'RETURN',(-1)*NVL(l.tax_line_value,l.tax_value),NVL(l.tax_line_value,l.tax_value)),0),0 ) --TaxER
7225: )
7226: FROM oe_order_lines l
7227: , oe_order_headers_all h
7228: WHERE h.invoice_to_org_id = p_site_use_id
7229: AND h.header_id = l.header_id
7230: AND h.org_id = l.org_id /* MOAC_SQL_CHANGE */
7261: + DECODE(l_include_tax_flag,'Y',DECODE(p.list_line_type_code,'TAX',p.adjusted_amount,0),0)
7262: --TaxER End
7263: )
7264: FROM oe_price_adjustments p
7265: , oe_order_lines l
7266: , oe_order_headers_all h
7267: WHERE h.invoice_to_org_id = p_site_use_id
7268: AND p.line_id = l.line_id
7269: AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,NVL(H.REQUEST_DATE,H.CREATION_DATE)))
7307: + DECODE(l_include_tax_flag,'Y',DECODE(p.list_line_type_code,'TAX',p.adjusted_amount,0),0)
7308: --TaxER End
7309: )
7310: FROM oe_price_adjustments p
7311: , oe_order_lines l
7312: , oe_order_headers_all h
7313: WHERE h.invoice_to_org_id = p_site_use_id
7314: AND p.line_id = l.line_id
7315: AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,NVL(H.REQUEST_DATE,H.CREATION_DATE)))
7363: AND h.header_id <> l_header_id
7364: AND EXISTS
7365: (SELECT NULL
7366: FROM oe_payment_types_all pt,
7367: oe_order_lines l
7368: WHERE pt.credit_check_flag = 'Y'
7369: AND l.header_id = h.header_id
7370: AND l.org_id = pt.org_id /* MOAC_SQL_CHANGE */
7371: AND NVL(pt.org_id, -99) = G_ORG_ID
7402: AND h.header_id <> l_header_id
7403: AND EXISTS
7404: (SELECT NULL
7405: FROM oe_payment_types_all pt,
7406: oe_order_lines l
7407: WHERE pt.credit_check_flag = 'Y'
7408: AND l.header_id = h.header_id
7409: AND l.org_id = pt.org_id /* MOAC_SQL_CHANGE */
7410: AND NVL(pt.org_id, -99) = G_ORG_ID
7426: + DECODE(l_include_tax_flag,'Y',DECODE(p.list_line_type_code,'TAX',p.adjusted_amount,0),0)
7427: --TaxER End
7428: )
7429: FROM oe_price_adjustments p
7430: , oe_order_lines l
7431: , oe_order_headers_all h
7432: WHERE h.invoice_to_org_id = p_site_use_id
7433: AND p.line_id = l.line_id
7434: AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,NVL(H.REQUEST_DATE,H.CREATION_DATE)))
7476: + DECODE(l_include_tax_flag,'Y',DECODE(p.list_line_type_code,'TAX',p.adjusted_amount,0),0)
7477: --TaxER End
7478: )
7479: FROM oe_price_adjustments p
7480: , oe_order_lines l
7481: , oe_order_headers_all h
7482: WHERE h.invoice_to_org_id = p_site_use_id
7483: AND p.line_id = l.line_id
7484: AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,NVL(H.REQUEST_DATE,H.CREATION_DATE)))
7523: + DECODE(l_include_tax_flag,'Y',DECODE(p.list_line_type_code,'TAX',p.adjusted_amount,0),0)
7524: --TaxER End
7525: )
7526: FROM oe_price_adjustments p
7527: , oe_order_lines l
7528: , oe_order_headers_all h
7529: WHERE h.invoice_to_org_id = p_site_use_id
7530: AND p.line_id = l.line_id
7531: AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,NVL(H.REQUEST_DATE,H.CREATION_DATE)))
7572: + DECODE(l_include_tax_flag,'Y',DECODE(p.list_line_type_code,'TAX',p.adjusted_amount,0),0)
7573: --TaxER End
7574: )
7575: FROM oe_price_adjustments p
7576: , oe_order_lines l
7577: , oe_order_headers_all h
7578: WHERE h.invoice_to_org_id = p_site_use_id
7579: AND p.line_id = l.line_id
7580: AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,NVL(H.REQUEST_DATE,H.CREATION_DATE)))
7600: AND oh.hold_release_id IS NULL )
7601: AND EXISTS
7602: (SELECT NULL
7603: FROM oe_payment_types_all pt,
7604: oe_order_lines l
7605: WHERE pt.credit_check_flag = 'Y'
7606: AND l.header_id = h.header_id
7607: AND l.org_id = pt.org_id /* MOAC_SQL_CHANGE */
7608: AND NVL(pt.org_id, -99) = G_ORG_ID
7644: AND oh.hold_release_id IS NULL )
7645: AND EXISTS
7646: (SELECT NULL
7647: FROM oe_payment_types_all pt,
7648: oe_order_lines l
7649: WHERE pt.credit_check_flag = 'Y'
7650: AND l.header_id = h.header_id
7651: AND l.org_id = pt.org_id /* MOAC_SQL_CHANGE */
7652: AND NVL(pt.org_id, -99) = G_ORG_ID
7689: AND oh.hold_release_id IS NULL )
7690: AND EXISTS
7691: (SELECT NULL
7692: FROM oe_payment_types_all pt,
7693: oe_order_lines l
7694: WHERE pt.credit_check_flag = 'Y'
7695: AND l.header_id = h.header_id
7696: AND l.org_id = pt.org_id /* MOAC_SQL_CHANGE */
7697: AND NVL(pt.org_id, -99) = G_ORG_ID
7733: AND oh.hold_release_id IS NULL )
7734: AND EXISTS
7735: (SELECT NULL
7736: FROM oe_payment_types_all pt,
7737: oe_order_lines l
7738: WHERE pt.credit_check_flag = 'Y'
7739: AND l.header_id = h.header_id
7740: AND l.org_id = pt.org_id /* MOAC_SQL_CHANGE */
7741: AND NVL(pt.org_id, -99) = G_ORG_ID
7777: AND oh.hold_release_id IS NULL )
7778: AND EXISTS
7779: (SELECT NULL
7780: FROM oe_payment_types_all pt,
7781: oe_order_lines l
7782: WHERE pt.credit_check_flag = 'Y'
7783: AND l.header_id = h.header_id
7784: AND l.org_id = pt.org_id /* MOAC_SQL_CHANGE */
7785: AND NVL(pt.org_id, -99) = G_ORG_ID
8018: ( l.ordered_quantity * l.unit_selling_price )
8019: + DECODE(l_include_tax_flag, 'Y',
8020: NVL(NVL(l.tax_line_value,l.tax_value),0), 0 ) --TaxER
8021: )
8022: FROM oe_order_lines_ALL l
8023: , oe_order_headers_ALL h
8024: , hz_cust_site_uses_ALL su
8025: , hz_cust_acct_sites_ALL cas
8026: WHERE h.invoice_to_org_id = su.site_use_id
8057: * l.unit_selling_price )
8058: + DECODE(l_include_tax_flag, 'Y',
8059: NVL(DECODE(l.line_category_code,'RETURN',(-1)*NVL(l.tax_line_value,l.tax_value),NVL(l.tax_line_value,l.tax_value)),0),0) --TaxER
8060: )
8061: FROM oe_order_lines_ALL l
8062: , oe_order_headers_ALL h
8063: , hz_cust_site_uses_ALL su
8064: , hz_cust_acct_sites_ALL cas
8065: WHERE h.invoice_to_org_id = su.site_use_id
8095: (p_curr_code IN VARCHAR2 default NULL) IS
8096: SELECT NVL(SUM(P.commitment_applied_amount), 0)
8097: FROM OE_PAYMENTS P
8098: , OE_ORDER_HEADERS_ALL H
8099: , OE_ORDER_LINES_ALL L
8100: , hz_cust_site_uses_ALL su
8101: , hz_cust_acct_sites_ALL cas
8102: WHERE H.invoice_to_org_id = su.site_use_id
8103: AND cas.cust_acct_site_id = su.cust_acct_site_id
8134: ( l.ordered_quantity * l.unit_selling_price )
8135: + DECODE(l_include_tax_flag, 'Y',
8136: NVL(NVL(l.tax_line_value,l.tax_value),0), 0 ) --TaxER
8137: )
8138: FROM oe_order_lines_ALL l
8139: , oe_order_headers_ALL h
8140: , hz_cust_site_uses_ALL su
8141: , hz_cust_acct_sites_ALL cas
8142: WHERE h.invoice_to_org_id = su.site_use_id
8177: * l.unit_selling_price )
8178: + DECODE(l_include_tax_flag, 'Y',
8179: NVL(DECODE(l.line_category_code,'RETURN',(-1)*NVL(l.tax_line_value,l.tax_value),NVL(l.tax_line_value,l.tax_value)),0),0 ) --TaxER
8180: )
8181: FROM oe_order_lines_ALL l
8182: , oe_order_headers_ALL h
8183: , hz_cust_site_uses_ALL su
8184: , hz_cust_acct_sites_ALL cas
8185: WHERE h.invoice_to_org_id = su.site_use_id
8216: ( l.ordered_quantity * l.unit_selling_price )
8217: + DECODE(l_include_tax_flag, 'Y',
8218: NVL(NVL(l.tax_line_value,l.tax_value),0), 0 ) --TaxER
8219: )
8220: FROM oe_order_lines_ALL l
8221: , oe_order_headers_ALL h
8222: , hz_cust_site_uses_ALL su
8223: , hz_cust_acct_sites_ALL cas
8224: WHERE h.invoice_to_org_id = su.site_use_id
8258: * l.unit_selling_price )
8259: + DECODE(l_include_tax_flag, 'Y',
8260: NVL(DECODE(l.line_category_code,'RETURN',(-1)*NVL(l.tax_line_value,l.tax_value),NVL(l.tax_line_value,l.tax_value)),0),0 ) --TaxER
8261: )
8262: FROM oe_order_lines_ALL l
8263: , oe_order_headers_ALL h
8264: , hz_cust_site_uses_ALL su
8265: , hz_cust_acct_sites_ALL cas
8266: WHERE h.invoice_to_org_id = su.site_use_id
8303: + DECODE(l_include_tax_flag,'Y',DECODE(p.list_line_type_code,'TAX',p.adjusted_amount,0),0)
8304: --TaxER End
8305: )
8306: FROM oe_price_adjustments p
8307: , oe_order_lines_ALL l
8308: , oe_order_headers_ALL h
8309: , hz_cust_site_uses_ALL su
8310: , hz_cust_acct_sites_ALL cas
8311: WHERE h.invoice_to_org_id = su.site_use_id
8353: + DECODE(l_include_tax_flag,'Y',DECODE(p.list_line_type_code,'TAX',p.adjusted_amount,0),0)
8354: --TaxER End
8355: )
8356: FROM oe_price_adjustments p
8357: , oe_order_lines_ALL l
8358: , oe_order_headers_ALL h
8359: , hz_cust_site_uses_ALL su
8360: , hz_cust_acct_sites_ALL cas
8361: WHERE h.invoice_to_org_id = su.site_use_id
8419: AND h.header_id <> l_header_id
8420: AND EXISTS
8421: ( SELECT NULL
8422: FROM oe_payment_types_all t,
8423: oe_order_lines_all l
8424: WHERE t.credit_check_flag = 'Y'
8425: AND NVL(t.org_id,-99) = NVL(h.org_id, -99)
8426: AND l.header_id = h.header_id
8427: AND t.payment_type_code =
8463: AND h.header_id <> l_header_id
8464: AND EXISTS
8465: ( SELECT NULL
8466: FROM oe_payment_types_all t,
8467: oe_order_lines_all l
8468: WHERE t.credit_check_flag = 'Y'
8469: AND NVL(t.org_id,-99) = NVL(h.org_id, -99)
8470: AND l.header_id = h.header_id
8471: AND t.payment_type_code =
8487: + DECODE(l_include_tax_flag,'Y',DECODE(p.list_line_type_code,'TAX',p.adjusted_amount,0),0)
8488: --TaxER End
8489: )
8490: FROM oe_price_adjustments p
8491: , oe_order_lines_ALL l
8492: , oe_order_headers_ALL h
8493: , hz_cust_site_uses_ALL su
8494: , hz_cust_acct_sites_ALL cas
8495: WHERE h.invoice_to_org_id = su.site_use_id
8541: + DECODE(l_include_tax_flag,'Y',DECODE(p.list_line_type_code,'TAX',p.adjusted_amount,0),0)
8542: --TaxER End
8543: )
8544: FROM oe_price_adjustments p
8545: , oe_order_lines_ALL l
8546: , oe_order_headers_ALL h
8547: , hz_cust_site_uses_ALL su
8548: , hz_cust_acct_sites_ALL cas
8549: WHERE h.invoice_to_org_id = su.site_use_id
8592: + DECODE(l_include_tax_flag,'Y',DECODE(p.list_line_type_code,'TAX',p.adjusted_amount,0),0)
8593: --TaxER End
8594: )
8595: FROM oe_price_adjustments p
8596: , oe_order_lines_ALL l
8597: , oe_order_headers_ALL h
8598: , hz_cust_site_uses_ALL su
8599: , hz_cust_acct_sites_ALL cas
8600: WHERE h.invoice_to_org_id = su.site_use_id
8646: + DECODE(l_include_tax_flag,'Y',DECODE(p.list_line_type_code,'TAX',p.adjusted_amount,0),0)
8647: --TaxER End
8648: )
8649: FROM oe_price_adjustments p
8650: , oe_order_lines_ALL l
8651: , oe_order_headers_ALL h
8652: , hz_cust_site_uses_ALL su
8653: , hz_cust_acct_sites_ALL cas
8654: WHERE h.invoice_to_org_id = su.site_use_id
8721: AND oh.hold_release_id IS NULL )
8722: AND EXISTS
8723: ( SELECT NULL
8724: FROM oe_payment_types_all t,
8725: oe_order_lines_all l
8726: WHERE t.credit_check_flag = 'Y'
8727: AND NVL(t.org_id,-99) = NVL(h.org_id, -99)
8728: AND l.header_id = h.header_id
8729: AND t.payment_type_code =
8770: AND oh.hold_release_id IS NULL )
8771: AND EXISTS
8772: ( SELECT NULL
8773: FROM oe_payment_types_all t,
8774: oe_order_lines_all l
8775: WHERE t.credit_check_flag = 'Y'
8776: AND NVL(t.org_id,-99) = NVL(h.org_id, -99)
8777: AND l.header_id = h.header_id
8778: AND t.payment_type_code =
8817: AND oh.hold_release_id IS NULL )
8818: AND EXISTS
8819: ( SELECT NULL
8820: FROM oe_payment_types_all t,
8821: oe_order_lines_all l
8822: WHERE t.credit_check_flag = 'Y'
8823: AND NVL(t.org_id,-99) = NVL(h.org_id, -99)
8824: AND l.header_id = h.header_id
8825: AND t.payment_type_code =
8865: AND oh.hold_release_id IS NULL )
8866: AND EXISTS
8867: ( SELECT NULL
8868: FROM oe_payment_types_all t,
8869: oe_order_lines_all l
8870: WHERE t.credit_check_flag = 'Y'
8871: AND NVL(t.org_id,-99) = NVL(h.org_id, -99)
8872: AND l.header_id = h.header_id
8873: AND t.payment_type_code =