[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 )
4390: NVL(l.tax_value,0), 0 )
4391: + ( l.unit_selling_price * l.ordered_quantity )
4392: )
4393: INTO l_order_value
4394: FROM oe_order_lines l,
4395: oe_order_headers_all h,
4396: ra_terms_b t,
4397: HZ_CUST_SITE_USES_ALL su,
4398: HZ_CUST_ACCT_SITES_ALL cas
4432: (DECODE(l.line_category_code,'RETURN',(-1)*l.ordered_quantity,
4433: l.ordered_quantity) )
4434: ))
4435: INTO l_order_value
4436: FROM oe_order_lines l,
4437: oe_order_headers_all h,
4438: ra_terms_b t,
4439: HZ_CUST_SITE_USES_ALL su,
4440: HZ_CUST_ACCT_SITES_ALL cas
4474: END IF;
4475:
4476: SELECT NVL(SUM(P.commitment_applied_amount), 0)
4477: INTO l_commitment
4478: FROM OE_PAYMENTS P, OE_ORDER_HEADERS_ALL H, OE_ORDER_LINES L ,
4479: HZ_CUST_SITE_USES_ALL su,
4480: HZ_CUST_ACCT_SITES_ALL cas,
4481: ra_terms_b t
4482: WHERE h.header_id = p_header_id
4536: * DECODE( p.arithmetic_operator, 'LUMPSUM', --bug 4295299
4537: p.operand, (l.ordered_quantity * p.adjusted_amount))
4538: )
4539: INTO l_freights
4540: FROM oe_order_lines l,
4541: oe_order_headers_all h,
4542: ra_terms_b t,
4543: HZ_CUST_SITE_USES_ALL su,
4544: HZ_CUST_ACCT_SITES_ALL cas,
4591: * DECODE( p.arithmetic_operator, 'LUMPSUM', --bug 4295299
4592: p.operand, (l.ordered_quantity * p.adjusted_amount))
4593: )
4594: INTO l_freights
4595: FROM oe_order_lines l,
4596: oe_order_headers_all h,
4597: ra_terms_b t,
4598: HZ_CUST_SITE_USES_ALL su,
4599: HZ_CUST_ACCT_SITES_ALL cas,
4652: NVL(l.tax_value,0), 0 )
4653: + ( l.unit_selling_price * l.ordered_quantity )
4654: )
4655: INTO l_order_value
4656: FROM oe_order_lines l,
4657: oe_order_headers_all h,
4658: ra_terms_b t
4659: WHERE h.header_id = p_header_id
4660: AND h.org_id = l.org_id /* MOAC_SQL_CHANGE */
4689: + (l.unit_selling_price * DECODE(l.line_category_code,'RETURN',
4690: (-1)*l.ordered_quantity,l.ordered_quantity))
4691: )
4692: INTO l_order_value
4693: FROM oe_order_lines l,
4694: oe_order_headers_all h,
4695: ra_terms_b t
4696: WHERE h.header_id = p_header_id
4697: AND h.org_id = l.org_id /* MOAC_SQL_CHANGE */
4723: SELECT NVL(SUM(P.commitment_applied_amount), 0)
4724: INTO l_commitment
4725: FROM OE_PAYMENTS P,
4726: OE_ORDER_HEADERS_ALL H,
4727: OE_ORDER_LINES L,
4728: ra_terms_b t
4729: WHERE h.header_id = p_header_id
4730: AND h.org_id = l.org_id /* MOAC_SQL_CHANGE */
4731: AND h.header_id = l.header_id
4782: * DECODE( p.arithmetic_operator, 'LUMPSUM', --bug 4295299
4783: p.operand, (l.ordered_quantity * p.adjusted_amount))
4784: )
4785: INTO l_freights
4786: FROM oe_order_lines l,
4787: oe_order_headers_all h,
4788: ra_terms_b t,
4789: oe_price_adjustments p
4790: WHERE h.header_id = p_header_id
4834: * DECODE( p.arithmetic_operator, 'LUMPSUM', --bug 4295299
4835: p.operand, (l.ordered_quantity * p.adjusted_amount))
4836: )
4837: INTO l_freights
4838: FROM oe_order_lines l,
4839: oe_order_headers_all h,
4840: ra_terms_b t,
4841: oe_price_adjustments p
4842: WHERE h.header_id = p_header_id
4893: NVL(l.tax_value,0), 0 )
4894: + ( l.unit_selling_price * l.ordered_quantity )
4895: )
4896: INTO l_order_value
4897: FROM oe_order_lines l,
4898: oe_order_headers_all h,
4899: ra_terms_b t
4900: WHERE h.header_id = p_header_id
4901: AND h.org_id = l.org_id /* MOAC_SQL_CHANGE */
4929: + (l.unit_selling_price * DECODE(l.line_category_code,'RETURN',
4930: (-1)*l.ordered_quantity,l.ordered_quantity))
4931: )
4932: INTO l_order_value
4933: FROM oe_order_lines l,
4934: oe_order_headers_all h,
4935: ra_terms_b t
4936: WHERE h.header_id = p_header_id
4937: AND h.org_id = l.org_id /* MOAC_SQL_CHANGE */
4967: END IF;
4968:
4969: SELECT NVL(SUM(P.commitment_applied_amount), 0)
4970: INTO l_commitment
4971: FROM oe_order_lines l,
4972: oe_order_headers_all h,
4973: ra_terms_b t,
4974: oe_payments p
4975: WHERE h.header_id = p_header_id
5047: p.operand, (l.ordered_quantity * p.adjusted_amount))
5048: )
5049: INTO l_freights_hdr_1
5050: FROM oe_price_adjustments p
5051: , oe_order_lines l
5052: , oe_order_headers_all h
5053: , ra_terms_b t
5054: WHERE h.header_id = p_header_id
5055: AND h.org_id = l.org_id /* MOAC_SQL_CHANGE */
5116: p.operand, (l.ordered_quantity * p.adjusted_amount))
5117: )
5118: INTO l_freights_hdr_1
5119: FROM oe_price_adjustments p
5120: , oe_order_lines l
5121: , oe_order_headers_all h
5122: , ra_terms_b t
5123: WHERE h.header_id = p_header_id
5124: AND h.org_id = l.org_id /* MOAC_SQL_CHANGE */
5823: ( l.ordered_quantity * l.unit_selling_price )
5824: + DECODE(l_include_tax_flag, 'Y',
5825: NVL(l.tax_value,0), 0 )
5826: )
5827: FROM oe_order_lines_all l
5828: , oe_order_headers h
5829: , hz_cust_site_uses_all su
5830: , hz_cust_acct_sites_all cas
5831: WHERE h.invoice_to_org_id = su.site_use_id
5862: * l.unit_selling_price )
5863: + DECODE(l_include_tax_flag, 'Y',
5864: NVL(DECODE(l.line_category_code,'RETURN',(-1)*l.tax_value,l.tax_value),0),0
5865: ))
5866: FROM oe_order_lines l
5867: , oe_order_headers_all h
5868: , hz_cust_site_uses_all su
5869: , hz_cust_acct_sites_all cas
5870: WHERE h.invoice_to_org_id = su.site_use_id
5897: ------------------------------------------------
5898:
5899: Cursor customer_commitment_total (p_curr_code IN VARCHAR2 default NULL) IS
5900: SELECT NVL(SUM(P.commitment_applied_amount), 0)
5901: FROM OE_PAYMENTS P, OE_ORDER_HEADERS_ALL H, OE_ORDER_LINES L
5902: , hz_cust_site_uses_all su
5903: , hz_cust_acct_sites_all cas
5904: WHERE H.invoice_to_org_id = su.site_use_id
5905: AND h.org_id = su.org_id
5928: OR
5929: (l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
5930: Cursor site_commitment_total (p_curr_code IN VARCHAR2 default NULL) IS
5931: SELECT NVL(SUM(P.commitment_applied_amount), 0)
5932: FROM OE_PAYMENTS P, OE_ORDER_HEADERS_ALL H, OE_ORDER_LINES L
5933: WHERE H.INVOICE_TO_ORG_ID = p_site_use_id
5934: AND H.TRANSACTIONAL_CURR_CODE = p_curr_code
5935: AND H.OPEN_FLAG = 'Y'
5936: AND H.BOOKED_FLAG = 'Y'
5963: (l.ordered_quantity * l.unit_selling_price )
5964: + DECODE(l_include_tax_flag, 'Y',
5965: NVL(l.tax_value,0), 0 )
5966: )
5967: FROM oe_order_lines l
5968: , oe_order_headers_all h
5969: , hz_cust_site_uses_all su
5970: , hz_cust_acct_sites_all cas
5971: WHERE h.invoice_to_org_id = su.site_use_id
6006: * l.unit_selling_price )
6007: + DECODE(l_include_tax_flag, 'Y',
6008: NVL(DECODE(l.line_category_code,'RETURN',(-1)*l.tax_value,l.tax_value),0),0 )
6009: )
6010: FROM oe_order_lines l
6011: , oe_order_headers_all h
6012: , hz_cust_site_uses_all su
6013: , hz_cust_acct_sites_all cas
6014: WHERE h.invoice_to_org_id = su.site_use_id
6045: (l.ordered_quantity * l.unit_selling_price )
6046: + DECODE(l_include_tax_flag, 'Y',
6047: NVL(l.tax_value,0), 0 )
6048: )
6049: FROM oe_order_lines l
6050: , oe_order_headers_all h
6051: , hz_cust_site_uses_all su
6052: , hz_cust_acct_sites_all cas
6053: WHERE h.invoice_to_org_id = su.site_use_id
6088: * l.unit_selling_price )
6089: + DECODE(l_include_tax_flag, 'Y',
6090: NVL(DECODE(l.line_category_code,'RETURN',(-1)*l.tax_value,l.tax_value),0),0 )
6091: )
6092: FROM oe_order_lines l
6093: , oe_order_headers_all h
6094: , hz_cust_site_uses_all su
6095: , hz_cust_acct_sites_all cas
6096: WHERE h.invoice_to_org_id = su.site_use_id
6133: * DECODE( p.arithmetic_operator, 'LUMPSUM', --bug 4295299
6134: p.operand, (l.ordered_quantity * p.adjusted_amount))
6135: )
6136: FROM oe_price_adjustments p
6137: , oe_order_lines l
6138: , oe_order_headers_all h
6139: , hz_cust_site_uses_all su
6140: , hz_cust_acct_sites_all cas
6141: WHERE h.invoice_to_org_id = su.site_use_id
6179: * DECODE( p.arithmetic_operator, 'LUMPSUM', --bug 4295299
6180: p.operand, (l.ordered_quantity * p.adjusted_amount))
6181: )
6182: FROM oe_price_adjustments p
6183: , oe_order_lines l
6184: , oe_order_headers_all h
6185: , hz_cust_site_uses_all su
6186: , hz_cust_acct_sites_all cas
6187: WHERE h.invoice_to_org_id = su.site_use_id
6243: AND h.header_id <> l_header_id
6244: AND EXISTS
6245: (SELECT NULL
6246: FROM oe_payment_types_all pt,
6247: oe_order_lines l
6248: WHERE pt.credit_check_flag = 'Y'
6249: AND l.header_id = h.header_id
6250: AND l.org_id = pt.org_id /* MOAC_SQL_CHANGE */
6251: AND NVL(pt.org_id, -99) = G_ORG_ID
6286: AND h.header_id <> l_header_id
6287: AND EXISTS
6288: (SELECT NULL
6289: FROM oe_payment_types_all pt,
6290: oe_order_lines l
6291: WHERE pt.credit_check_flag = 'Y'
6292: AND l.header_id = h.header_id
6293: AND l.org_id = pt.org_id /* MOAC_SQL_CHANGE */
6294: AND NVL(pt.org_id, -99) = G_ORG_ID
6308: * DECODE( p.arithmetic_operator, 'LUMPSUM', --bug 4295299
6309: p.operand, (l.ordered_quantity * p.adjusted_amount))
6310: )
6311: FROM oe_price_adjustments p
6312: , oe_order_lines l
6313: , oe_order_headers_all h
6314: , hz_cust_site_uses_all su
6315: , hz_cust_acct_sites_all cas
6316: WHERE h.invoice_to_org_id = su.site_use_id
6358: * DECODE( p.arithmetic_operator, 'LUMPSUM', --bug 4295299
6359: p.operand, (l.ordered_quantity * p.adjusted_amount))
6360: )
6361: FROM oe_price_adjustments p
6362: , oe_order_lines l
6363: , oe_order_headers_all h
6364: , hz_cust_site_uses_all su
6365: , hz_cust_acct_sites_all cas
6366: WHERE h.invoice_to_org_id = su.site_use_id
6407: * DECODE( p.arithmetic_operator, 'LUMPSUM', --bug 4295299
6408: p.operand, (l.ordered_quantity * p.adjusted_amount))
6409: )
6410: FROM oe_price_adjustments p
6411: , oe_order_lines l
6412: , oe_order_headers_all h
6413: , hz_cust_site_uses_all su
6414: , hz_cust_acct_sites_all cas
6415: WHERE h.invoice_to_org_id = su.site_use_id
6458: * DECODE( p.arithmetic_operator, 'LUMPSUM', --bug 4295299
6459: p.operand, (l.ordered_quantity * p.adjusted_amount))
6460: )
6461: FROM oe_price_adjustments p
6462: , oe_order_lines l
6463: , oe_order_headers_all h
6464: , hz_cust_site_uses_all su
6465: , hz_cust_acct_sites_all cas
6466: WHERE h.invoice_to_org_id = su.site_use_id
6530: AND oh.hold_release_id IS NULL )
6531: AND EXISTS
6532: (SELECT NULL
6533: FROM oe_payment_types_all pt,
6534: oe_order_lines_all l --Performance issue (SQL ID-14880869 FTS on OE_ORDER_HOLDS_ALL)
6535: WHERE pt.credit_check_flag = 'Y'
6536: AND l.header_id = h.header_id
6537: AND l.org_id = pt.org_id /* MOAC_SQL_CHANGE */
6538: AND NVL(pt.org_id, -99) = G_ORG_ID
6575: AND oh.hold_release_id IS NULL )
6576: AND EXISTS
6577: (SELECT NULL
6578: FROM oe_payment_types_all pt,
6579: oe_order_lines_all l --Performance issue (SQL ID-14880892 FTS on OE_ORDER_HOLDS_ALL)
6580: WHERE pt.credit_check_flag = 'Y'
6581: AND l.header_id = h.header_id
6582: AND l.org_id = pt.org_id /* MOAC_SQL_CHANGE */
6583: AND NVL(pt.org_id, -99) = G_ORG_ID
6619: AND oh.hold_release_id IS NULL )
6620: AND EXISTS
6621: (SELECT NULL
6622: FROM oe_payment_types_all pt,
6623: oe_order_lines_all l --Performance issue (SQL ID-14880915 FTS on OE_ORDER_HOLDS_ALL)
6624: WHERE pt.credit_check_flag = 'Y'
6625: AND l.header_id = h.header_id
6626: AND l.org_id = pt.org_id /* MOAC_SQL_CHANGE */
6627: AND NVL(pt.org_id, -99) = G_ORG_ID
6664: AND oh.hold_release_id IS NULL )
6665: AND EXISTS
6666: (SELECT NULL
6667: FROM oe_payment_types_all pt,
6668: oe_order_lines l
6669: WHERE pt.credit_check_flag = 'Y'
6670: AND l.header_id = h.header_id
6671: AND l.org_id = pt.org_id /* MOAC_SQL_CHANGE */
6672: AND NVL(pt.org_id, -99) = G_ORG_ID
6787: ( l.ordered_quantity * l.unit_selling_price )
6788: + DECODE(l_include_tax_flag, 'Y',
6789: NVL(l.tax_value,0), 0 )
6790: )
6791: FROM oe_order_lines l
6792: , oe_order_headers_all h
6793: WHERE h.invoice_to_org_id = p_site_use_id
6794: AND h.header_id = l.header_id
6795: AND h.org_id = l.org_id /* MOAC_SQL_CHANGE */
6823: * l.unit_selling_price )
6824: + DECODE(l_include_tax_flag, 'Y',
6825: NVL(DECODE(l.line_category_code,'RETURN',(-1)*l.tax_value,l.tax_value),0),0 )
6826: )
6827: FROM oe_order_lines l
6828: , oe_order_headers_all h
6829: WHERE h.invoice_to_org_id = p_site_use_id
6830: AND h.header_id = l.header_id
6831: AND h.org_id = l.org_id /* MOAC_SQL_CHANGE */
6855: + DECODE( l_include_tax_flag, 'Y',
6856: NVL(l.tax_value,0), 0
6857: )
6858: )
6859: FROM oe_order_lines l
6860: , oe_order_headers_all h
6861: WHERE h.invoice_to_org_id = p_site_use_id
6862: AND h.header_id = l.header_id
6863: AND h.org_id = l.org_id /* MOAC_SQL_CHANGE */
6895: * l.unit_selling_price )
6896: + DECODE(l_include_tax_flag, 'Y',
6897: NVL(DECODE(l.line_category_code,'RETURN',(-1)*l.tax_value,l.tax_value),0),0 )
6898: )
6899: FROM oe_order_lines l
6900: , oe_order_headers_all h
6901: WHERE h.invoice_to_org_id = p_site_use_id
6902: AND h.header_id = l.header_id
6903: AND h.org_id = l.org_id /* MOAC_SQL_CHANGE */
6929: + DECODE( l_include_tax_flag, 'Y',
6930: NVL(l.tax_value,0), 0
6931: )
6932: )
6933: FROM oe_order_lines l
6934: , oe_order_headers_all h
6935: WHERE h.invoice_to_org_id = p_site_use_id
6936: AND h.header_id = l.header_id
6937: AND h.org_id = l.org_id /* MOAC_SQL_CHANGE */
6966: ( DECODE(l.line_category_code,'RETURN',(-1)*l.ordered_quantity,l.ordered_quantity) * l.unit_selling_price )
6967: + DECODE(l_include_tax_flag, 'Y',
6968: NVL(DECODE(l.line_category_code,'RETURN',(-1)*l.tax_value,l.tax_value),0),0 )
6969: )
6970: FROM oe_order_lines l
6971: , oe_order_headers_all h
6972: WHERE h.invoice_to_org_id = p_site_use_id
6973: AND h.header_id = l.header_id
6974: AND h.org_id = l.org_id /* MOAC_SQL_CHANGE */
7002: * DECODE( p.arithmetic_operator, 'LUMPSUM', --bug 4295299
7003: p.operand, (l.ordered_quantity * p.adjusted_amount))
7004: )
7005: FROM oe_price_adjustments p
7006: , oe_order_lines l
7007: , oe_order_headers_all h
7008: WHERE h.invoice_to_org_id = p_site_use_id
7009: AND p.line_id = l.line_id
7010: AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,NVL(H.REQUEST_DATE,H.CREATION_DATE)))
7044: * DECODE( p.arithmetic_operator, 'LUMPSUM', --bug 4295299
7045: p.operand, (l.ordered_quantity * p.adjusted_amount))
7046: )
7047: FROM oe_price_adjustments p
7048: , oe_order_lines l
7049: , oe_order_headers_all h
7050: WHERE h.invoice_to_org_id = p_site_use_id
7051: AND p.line_id = l.line_id
7052: AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,NVL(H.REQUEST_DATE,H.CREATION_DATE)))
7095: AND h.header_id <> l_header_id
7096: AND EXISTS
7097: (SELECT NULL
7098: FROM oe_payment_types_all pt,
7099: oe_order_lines l
7100: WHERE pt.credit_check_flag = 'Y'
7101: AND l.header_id = h.header_id
7102: AND l.org_id = pt.org_id /* MOAC_SQL_CHANGE */
7103: AND NVL(pt.org_id, -99) = G_ORG_ID
7130: AND h.header_id <> l_header_id
7131: AND EXISTS
7132: (SELECT NULL
7133: FROM oe_payment_types_all pt,
7134: oe_order_lines l
7135: WHERE pt.credit_check_flag = 'Y'
7136: AND l.header_id = h.header_id
7137: AND l.org_id = pt.org_id /* MOAC_SQL_CHANGE */
7138: AND NVL(pt.org_id, -99) = G_ORG_ID
7151: * DECODE( p.arithmetic_operator, 'LUMPSUM', --bug 4295299
7152: p.operand, (l.ordered_quantity * p.adjusted_amount))
7153: )
7154: FROM oe_price_adjustments p
7155: , oe_order_lines l
7156: , oe_order_headers_all h
7157: WHERE h.invoice_to_org_id = p_site_use_id
7158: AND p.line_id = l.line_id
7159: AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,NVL(H.REQUEST_DATE,H.CREATION_DATE)))
7197: * DECODE( p.arithmetic_operator, 'LUMPSUM', --bug 4295299
7198: p.operand, (l.ordered_quantity * p.adjusted_amount))
7199: )
7200: FROM oe_price_adjustments p
7201: , oe_order_lines l
7202: , oe_order_headers_all h
7203: WHERE h.invoice_to_org_id = p_site_use_id
7204: AND p.line_id = l.line_id
7205: AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,NVL(H.REQUEST_DATE,H.CREATION_DATE)))
7240: * DECODE( p.arithmetic_operator, 'LUMPSUM', --bug 4295299
7241: p.operand, (l.ordered_quantity * p.adjusted_amount))
7242: )
7243: FROM oe_price_adjustments p
7244: , oe_order_lines l
7245: , oe_order_headers_all h
7246: WHERE h.invoice_to_org_id = p_site_use_id
7247: AND p.line_id = l.line_id
7248: AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,NVL(H.REQUEST_DATE,H.CREATION_DATE)))
7285: * DECODE( p.arithmetic_operator, 'LUMPSUM', --bug 4295299
7286: p.operand, (l.ordered_quantity * p.adjusted_amount))
7287: )
7288: FROM oe_price_adjustments p
7289: , oe_order_lines l
7290: , oe_order_headers_all h
7291: WHERE h.invoice_to_org_id = p_site_use_id
7292: AND p.line_id = l.line_id
7293: AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,NVL(H.REQUEST_DATE,H.CREATION_DATE)))
7312: AND oh.hold_release_id IS NULL )
7313: AND EXISTS
7314: (SELECT NULL
7315: FROM oe_payment_types_all pt,
7316: oe_order_lines l
7317: WHERE pt.credit_check_flag = 'Y'
7318: AND l.header_id = h.header_id
7319: AND l.org_id = pt.org_id /* MOAC_SQL_CHANGE */
7320: AND NVL(pt.org_id, -99) = G_ORG_ID
7352: AND oh.hold_release_id IS NULL )
7353: AND EXISTS
7354: (SELECT NULL
7355: FROM oe_payment_types_all pt,
7356: oe_order_lines l
7357: WHERE pt.credit_check_flag = 'Y'
7358: AND l.header_id = h.header_id
7359: AND l.org_id = pt.org_id /* MOAC_SQL_CHANGE */
7360: AND NVL(pt.org_id, -99) = G_ORG_ID
7393: AND oh.hold_release_id IS NULL )
7394: AND EXISTS
7395: (SELECT NULL
7396: FROM oe_payment_types_all pt,
7397: oe_order_lines l
7398: WHERE pt.credit_check_flag = 'Y'
7399: AND l.header_id = h.header_id
7400: AND l.org_id = pt.org_id /* MOAC_SQL_CHANGE */
7401: AND NVL(pt.org_id, -99) = G_ORG_ID
7433: AND oh.hold_release_id IS NULL )
7434: AND EXISTS
7435: (SELECT NULL
7436: FROM oe_payment_types_all pt,
7437: oe_order_lines l
7438: WHERE pt.credit_check_flag = 'Y'
7439: AND l.header_id = h.header_id
7440: AND l.org_id = pt.org_id /* MOAC_SQL_CHANGE */
7441: AND NVL(pt.org_id, -99) = G_ORG_ID
7473: AND oh.hold_release_id IS NULL )
7474: AND EXISTS
7475: (SELECT NULL
7476: FROM oe_payment_types_all pt,
7477: oe_order_lines l
7478: WHERE pt.credit_check_flag = 'Y'
7479: AND l.header_id = h.header_id
7480: AND l.org_id = pt.org_id /* MOAC_SQL_CHANGE */
7481: AND NVL(pt.org_id, -99) = G_ORG_ID
7713: ( l.ordered_quantity * l.unit_selling_price )
7714: + DECODE(l_include_tax_flag, 'Y',
7715: NVL(l.tax_value,0), 0 )
7716: )
7717: FROM oe_order_lines_ALL l
7718: , oe_order_headers_ALL h
7719: , hz_cust_site_uses_ALL su
7720: , hz_cust_acct_sites_ALL cas
7721: WHERE h.invoice_to_org_id = su.site_use_id
7752: * l.unit_selling_price )
7753: + DECODE(l_include_tax_flag, 'Y',
7754: NVL(DECODE(l.line_category_code,'RETURN',(-1)*l.tax_value,l.tax_value),0),0 )
7755: )
7756: FROM oe_order_lines_ALL l
7757: , oe_order_headers_ALL h
7758: , hz_cust_site_uses_ALL su
7759: , hz_cust_acct_sites_ALL cas
7760: WHERE h.invoice_to_org_id = su.site_use_id
7790: (p_curr_code IN VARCHAR2 default NULL) IS
7791: SELECT NVL(SUM(P.commitment_applied_amount), 0)
7792: FROM OE_PAYMENTS P
7793: , OE_ORDER_HEADERS_ALL H
7794: , OE_ORDER_LINES_ALL L
7795: , hz_cust_site_uses_ALL su
7796: , hz_cust_acct_sites_ALL cas
7797: WHERE H.invoice_to_org_id = su.site_use_id
7798: AND cas.cust_acct_site_id = su.cust_acct_site_id
7829: ( l.ordered_quantity * l.unit_selling_price )
7830: + DECODE(l_include_tax_flag, 'Y',
7831: NVL(l.tax_value,0), 0 )
7832: )
7833: FROM oe_order_lines_ALL l
7834: , oe_order_headers_ALL h
7835: , hz_cust_site_uses_ALL su
7836: , hz_cust_acct_sites_ALL cas
7837: WHERE h.invoice_to_org_id = su.site_use_id
7872: * l.unit_selling_price )
7873: + DECODE(l_include_tax_flag, 'Y',
7874: NVL(DECODE(l.line_category_code,'RETURN',(-1)*l.tax_value,l.tax_value),0),0 )
7875: )
7876: FROM oe_order_lines_ALL l
7877: , oe_order_headers_ALL h
7878: , hz_cust_site_uses_ALL su
7879: , hz_cust_acct_sites_ALL cas
7880: WHERE h.invoice_to_org_id = su.site_use_id
7911: ( l.ordered_quantity * l.unit_selling_price )
7912: + DECODE(l_include_tax_flag, 'Y',
7913: NVL(l.tax_value,0), 0 )
7914: )
7915: FROM oe_order_lines_ALL l
7916: , oe_order_headers_ALL h
7917: , hz_cust_site_uses_ALL su
7918: , hz_cust_acct_sites_ALL cas
7919: WHERE h.invoice_to_org_id = su.site_use_id
7953: * l.unit_selling_price )
7954: + DECODE(l_include_tax_flag, 'Y',
7955: NVL(DECODE(l.line_category_code,'RETURN',(-1)*l.tax_value,l.tax_value),0),0 )
7956: )
7957: FROM oe_order_lines_ALL l
7958: , oe_order_headers_ALL h
7959: , hz_cust_site_uses_ALL su
7960: , hz_cust_acct_sites_ALL cas
7961: WHERE h.invoice_to_org_id = su.site_use_id
7995: * DECODE( p.arithmetic_operator, 'LUMPSUM', --bug 4295299
7996: p.operand, (l.ordered_quantity * p.adjusted_amount))
7997: )
7998: FROM oe_price_adjustments p
7999: , oe_order_lines_ALL l
8000: , oe_order_headers_ALL h
8001: , hz_cust_site_uses_ALL su
8002: , hz_cust_acct_sites_ALL cas
8003: WHERE h.invoice_to_org_id = su.site_use_id
8041: * DECODE( p.arithmetic_operator, 'LUMPSUM', --bug 4295299
8042: p.operand, (l.ordered_quantity * p.adjusted_amount))
8043: )
8044: FROM oe_price_adjustments p
8045: , oe_order_lines_ALL l
8046: , oe_order_headers_ALL h
8047: , hz_cust_site_uses_ALL su
8048: , hz_cust_acct_sites_ALL cas
8049: WHERE h.invoice_to_org_id = su.site_use_id
8102: AND h.header_id <> l_header_id
8103: AND EXISTS
8104: ( SELECT NULL
8105: FROM oe_payment_types_all t,
8106: oe_order_lines_all l
8107: WHERE t.credit_check_flag = 'Y'
8108: AND NVL(t.org_id,-99) = NVL(h.org_id, -99)
8109: AND l.header_id = h.header_id
8110: AND t.payment_type_code =
8142: AND h.header_id <> l_header_id
8143: AND EXISTS
8144: ( SELECT NULL
8145: FROM oe_payment_types_all t,
8146: oe_order_lines_all l
8147: WHERE t.credit_check_flag = 'Y'
8148: AND NVL(t.org_id,-99) = NVL(h.org_id, -99)
8149: AND l.header_id = h.header_id
8150: AND t.payment_type_code =
8163: * DECODE( p.arithmetic_operator, 'LUMPSUM', --bug 4295299
8164: p.operand, (l.ordered_quantity * p.adjusted_amount))
8165: )
8166: FROM oe_price_adjustments p
8167: , oe_order_lines_ALL l
8168: , oe_order_headers_ALL h
8169: , hz_cust_site_uses_ALL su
8170: , hz_cust_acct_sites_ALL cas
8171: WHERE h.invoice_to_org_id = su.site_use_id
8213: * DECODE( p.arithmetic_operator, 'LUMPSUM', --bug 4295299
8214: p.operand, (l.ordered_quantity * p.adjusted_amount))
8215: )
8216: FROM oe_price_adjustments p
8217: , oe_order_lines_ALL l
8218: , oe_order_headers_ALL h
8219: , hz_cust_site_uses_ALL su
8220: , hz_cust_acct_sites_ALL cas
8221: WHERE h.invoice_to_org_id = su.site_use_id
8260: * DECODE( p.arithmetic_operator, 'LUMPSUM', --bug 4295299
8261: p.operand, (l.ordered_quantity * p.adjusted_amount))
8262: )
8263: FROM oe_price_adjustments p
8264: , oe_order_lines_ALL l
8265: , oe_order_headers_ALL h
8266: , hz_cust_site_uses_ALL su
8267: , hz_cust_acct_sites_ALL cas
8268: WHERE h.invoice_to_org_id = su.site_use_id
8310: * DECODE( p.arithmetic_operator, 'LUMPSUM', --bug 4295299
8311: p.operand, (l.ordered_quantity * p.adjusted_amount))
8312: )
8313: FROM oe_price_adjustments p
8314: , oe_order_lines_ALL l
8315: , oe_order_headers_ALL h
8316: , hz_cust_site_uses_ALL su
8317: , hz_cust_acct_sites_ALL cas
8318: WHERE h.invoice_to_org_id = su.site_use_id
8380: AND oh.hold_release_id IS NULL )
8381: AND EXISTS
8382: ( SELECT NULL
8383: FROM oe_payment_types_all t,
8384: oe_order_lines_all l
8385: WHERE t.credit_check_flag = 'Y'
8386: AND NVL(t.org_id,-99) = NVL(h.org_id, -99)
8387: AND l.header_id = h.header_id
8388: AND t.payment_type_code =
8425: AND oh.hold_release_id IS NULL )
8426: AND EXISTS
8427: ( SELECT NULL
8428: FROM oe_payment_types_all t,
8429: oe_order_lines_all l
8430: WHERE t.credit_check_flag = 'Y'
8431: AND NVL(t.org_id,-99) = NVL(h.org_id, -99)
8432: AND l.header_id = h.header_id
8433: AND t.payment_type_code =
8468: AND oh.hold_release_id IS NULL )
8469: AND EXISTS
8470: ( SELECT NULL
8471: FROM oe_payment_types_all t,
8472: oe_order_lines_all l
8473: WHERE t.credit_check_flag = 'Y'
8474: AND NVL(t.org_id,-99) = NVL(h.org_id, -99)
8475: AND l.header_id = h.header_id
8476: AND t.payment_type_code =
8512: AND oh.hold_release_id IS NULL )
8513: AND EXISTS
8514: ( SELECT NULL
8515: FROM oe_payment_types_all t,
8516: oe_order_lines_all l
8517: WHERE t.credit_check_flag = 'Y'
8518: AND NVL(t.org_id,-99) = NVL(h.org_id, -99)
8519: AND l.header_id = h.header_id
8520: AND t.payment_type_code =