[Home] [Help]
186:
187: SELECT su.site_use_id
188: INTO l_site_use_id
189: FROM hz_cust_acct_sites cas
190: , hz_cust_site_uses_all su
191: WHERE cas.cust_acct_site_id = su.cust_acct_site_id
192: AND su.cust_acct_site_id = l_cust_acct_site_id
193: AND su.site_use_code = 'DRAWEE' ; /* MOAC_SQL_CHANGE */
194:
3145: CURSOR trx_curr_csr_customer IS
3146: SELECT soh.transactional_curr_code user_code
3147: FROM oe_order_headers soh
3148: , hz_cust_acct_sites_all cas
3149: , hz_cust_site_uses_all su
3150: WHERE soh.org_id = su.org_id
3151: AND soh.invoice_to_org_id = su.site_use_id
3152: AND cas.cust_acct_site_id = su.cust_acct_site_id
3153: AND cas.cust_account_id = p_entity_id
3155: UNION
3156: SELECT pay.invoice_currency_code user_code
3157: FROM ar_payment_schedules pay
3158: , hz_cust_acct_sites_all cas
3159: , hz_cust_site_uses_all su
3160: WHERE pay.org_id = su.org_id
3161: AND pay.customer_site_use_id = su.site_use_id
3162: AND cas.cust_acct_site_id = su.cust_acct_site_id
3163: AND cas.cust_account_id = p_entity_id
3174: CURSOR trx_curr_csr_customer_global IS
3175: SELECT soh.transactional_curr_code user_code
3176: FROM oe_order_headers_ALL soh
3177: , hz_cust_acct_sites_ALL cas
3178: , hz_cust_site_uses_ALL su
3179: WHERE soh.invoice_to_org_id = su.site_use_id
3180: AND cas.cust_acct_site_id = su.cust_acct_site_id
3181: AND cas.cust_account_id = p_entity_id
3182: GROUP BY transactional_curr_code
3183: UNION
3184: SELECT pay.invoice_currency_code user_code
3185: FROM ar_payment_schedules_ALL pay
3186: , hz_cust_acct_sites_ALL cas
3187: , hz_cust_site_uses_ALL su
3188: WHERE pay.customer_site_use_id = su.site_use_id
3189: AND cas.cust_acct_site_id = su.cust_acct_site_id
3190: AND cas.cust_account_id = p_entity_id
3191: GROUP BY invoice_currency_code
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
4399: WHERE h.header_id = p_header_id
4400: AND h.org_id = l.org_id /* MOAC_SQL_CHANGE */
4401: AND h.header_id = l.header_id
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
4441: WHERE h.header_id = p_header_id
4442: AND h.org_id = l.org_id /* MOAC_SQL_CHANGE */
4443: AND h.header_id = l.header_id
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
4483: AND h.org_id = l.org_id /* MOAC_SQL_CHANGE */
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,
4545: oe_price_adjustments p
4546: WHERE h.header_id = p_header_id
4547: AND h.org_id = l.org_id /* MOAC_SQL_CHANGE */
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,
4600: oe_price_adjustments p
4601: WHERE h.header_id = p_header_id
4602: AND h.org_id = l.org_id /* MOAC_SQL_CHANGE */
5702:
5703: CURSOR cust_ar_balance (p_curr_code IN VARCHAR2 default NULL) IS
5704: SELECT SUM(NVL(ps.amount_due_remaining,0))
5705: FROM ar_payment_schedules ps
5706: , hz_cust_site_uses_all su
5707: , hz_cust_acct_sites_all cas
5708: WHERE ps.CUSTOMER_SITE_USE_ID = su.site_use_id
5709: AND ps.org_id = su.org_id /* MOAC_SQL_CHANGE */
5710: AND ps.status = 'OP'
5723:
5724: CURSOR cust_BR_balance (p_curr_code IN VARCHAR2 default NULL) IS
5725: SELECT SUM(NVL(ps.amount_due_remaining,0))
5726: FROM ar_payment_schedules ps
5727: , hz_cust_site_uses_all su
5728: , hz_cust_acct_sites_all cas
5729: WHERE ps.CUSTOMER_SITE_USE_ID = su.site_use_id
5730: AND ps.org_id = su.org_id /* MOAC_SQL_CHANGE */
5731: AND ps.status = 'OP'
5742: CURSOR cust_pay_risk (p_curr_code IN VARCHAR2 default NULL) IS
5743: SELECT SUM(NVL(crh.amount,0))
5744: FROM ar_cash_receipt_history crh
5745: , ar_cash_receipts_all cr
5746: , hz_cust_site_uses_all su
5747: , hz_cust_acct_sites_all cas
5748: WHERE crh.cash_receipt_id = cr.cash_receipt_id
5749: AND crh.org_id = cr.org_id /* MOAC_SQL_CHANGE */
5750: AND NVL(cr.confirmed_flag,'Y') = 'Y'
5783: CURSOR cust_br_pay_risk (p_curr_code IN VARCHAR2 default NULL) IS
5784: SELECT SUM(NVL(crh.amount,0))
5785: FROM ar_cash_receipt_history crh
5786: , ar_cash_receipts_all cr
5787: , hz_cust_site_uses_all su
5788: , hz_cust_acct_sites_all cas
5789: WHERE crh.cash_receipt_id = cr.cash_receipt_id
5790: AND crh.org_id = cr.org_id /* MOAC_SQL_CHANGE */
5791: AND NVL(cr.confirmed_flag,'Y') = '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
5832: AND h.org_id = su.org_id
5833: AND su.cust_acct_site_id = cas.cust_acct_site_id
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
5871: AND h.org_id = su.org_id
5872: AND su.cust_acct_site_id = cas.cust_acct_site_id
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
5906: AND su.cust_acct_site_id = cas.cust_acct_site_id
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
5972: AND h.org_id = su.org_id
5973: AND su.cust_acct_site_id = cas.cust_acct_site_id
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
6015: AND h.org_id = su.org_id
6016: AND su.cust_acct_site_id = cas.cust_acct_site_id
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
6054: AND h.org_id = su.org_id
6055: AND su.cust_acct_site_id = cas.cust_acct_site_id
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
6097: AND h.org_id = su.org_id
6098: AND su.cust_acct_site_id = cas.cust_acct_site_id
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
6142: AND h.org_id = su.org_id
6143: AND su.cust_acct_site_id = cas.cust_acct_site_id
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
6188: AND h.org_id = su.org_id
6189: AND su.cust_acct_site_id = cas.cust_acct_site_id
6223: SUM( DECODE( P.CREDIT_OR_CHARGE_FLAG, 'C', (-1), (+1) ) * P.OPERAND )
6224: FROM
6225: oe_price_adjustments p
6226: , oe_order_headers h
6227: , hz_cust_site_uses_all su
6228: , hz_cust_acct_sites_all cas
6229: WHERE h.invoice_to_org_id = su.site_use_id
6230: AND h.org_id = su.org_id /* MOAC_SQL_CHANGE */
6231: AND su.cust_acct_site_id = cas.cust_acct_site_id
6266: SUM( DECODE( P.CREDIT_OR_CHARGE_FLAG, 'C', (-1), (+1) ) * P.OPERAND )
6267: FROM
6268: oe_price_adjustments p
6269: , oe_order_headers_all h
6270: , hz_cust_site_uses_all su
6271: , hz_cust_acct_sites_all cas
6272: WHERE h.invoice_to_org_id = su.site_use_id
6273: AND h.org_id = su.org_id /* MOAC_SQL_CHANGE */
6274: AND su.cust_acct_site_id = cas.cust_acct_site_id
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
6317: AND h.org_id = su.org_id
6318: AND su.cust_acct_site_id = cas.cust_acct_site_id
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
6367: AND h.org_id = su.org_id
6368: AND su.cust_acct_site_id = cas.cust_acct_site_id
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
6416: AND h.org_id = su.org_id
6417: AND su.cust_acct_site_id = cas.cust_acct_site_id
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
6467: AND h.org_id = su.org_id
6468: AND su.cust_acct_site_id = cas.cust_acct_site_id
6505: SUM( DECODE( P.CREDIT_OR_CHARGE_FLAG, 'C', (-1), (+1) ) * P.OPERAND )
6506: FROM
6507: oe_price_adjustments p
6508: , oe_order_headers h
6509: , hz_cust_site_uses_all su
6510: , hz_cust_acct_sites_all cas
6511: WHERE h.invoice_to_org_id = su.site_use_id
6512: AND h.org_id = su.org_id
6513: AND su.cust_acct_site_id = cas.cust_acct_site_id
6550: SUM( DECODE( P.CREDIT_OR_CHARGE_FLAG, 'C', (-1), (+1) ) * P.OPERAND )
6551: FROM
6552: oe_price_adjustments p
6553: , oe_order_headers h
6554: , hz_cust_site_uses_all su
6555: , hz_cust_acct_sites_all cas
6556: WHERE h.invoice_to_org_id = su.site_use_id
6557: AND h.org_id = su.org_id
6558: AND su.cust_acct_site_id = cas.cust_acct_site_id
6594: SUM( DECODE( P.CREDIT_OR_CHARGE_FLAG, 'C', (-1), (+1) ) * P.OPERAND )
6595: FROM
6596: oe_price_adjustments p
6597: , oe_order_headers h
6598: , hz_cust_site_uses_all su
6599: , hz_cust_acct_sites_all cas
6600: WHERE h.invoice_to_org_id = su.site_use_id
6601: AND h.org_id = su.org_id
6602: AND su.cust_acct_site_id = cas.cust_acct_site_id
6639: SUM( DECODE( P.CREDIT_OR_CHARGE_FLAG, 'C', (-1), (+1) ) * P.OPERAND )
6640: FROM
6641: oe_price_adjustments p
6642: , oe_order_headers h
6643: , hz_cust_site_uses_all su
6644: , hz_cust_acct_sites_all cas
6645: WHERE h.invoice_to_org_id = su.site_use_id
6646: AND h.org_id = su.org_id
6647: AND su.cust_acct_site_id = cas.cust_acct_site_id
6707: CURSOR site_pay_risk (p_curr_code IN VARCHAR2 default NULL) IS
6708: SELECT SUM(NVL(crh.amount,0))
6709: FROM ar_cash_receipt_history crh
6710: , ar_cash_receipts_all cr
6711: , hz_cust_site_uses_all s
6712: , hz_cust_acct_sites_all a
6713: WHERE crh.cash_receipt_id = cr.cash_receipt_id
6714: AND crh.org_id = cr.org_id
6715: AND crh.current_record_flag = 'Y'
7501: /*CURSOR cust_ar_balance_global
7502: (p_curr_code IN VARCHAR2 default NULL) IS
7503: SELECT SUM(NVL(ps.amount_due_remaining,0))
7504: FROM ar_payment_schedules_ALL ps
7505: , hz_cust_site_uses_ALL su
7506: , hz_cust_acct_sites_ALL cas
7507: WHERE ps.customer_site_use_id = su.site_use_id
7508: AND ps.status = 'OP'
7509: AND cas.cust_acct_site_id = su.cust_acct_site_id
7520: (p_curr_code IN VARCHAR2 default NULL) IS
7521: SELECT SUM(NVL(amount_due_remaining,0)) FROM (
7522: SELECT NVL(ps.amount_due_remaining,0) amount_due_remaining,ps.PAYMENT_SCHEDULE_ID
7523: FROM ar_payment_schedules_ALL ps
7524: , hz_cust_site_uses_ALL su
7525: , hz_cust_acct_sites_ALL cas
7526: WHERE ps.customer_site_use_id = su.site_use_id
7527: AND ps.status = 'OP'
7528: AND cas.cust_acct_site_id = su.cust_acct_site_id
7554: (p_curr_code IN VARCHAR2 default NULL) IS
7555: SELECT SUM(NVL(crh.amount,0))
7556: FROM ar_cash_receipt_history_ALL crh
7557: , ar_cash_receipts_ALL cr
7558: , hz_cust_site_uses_ALL su
7559: , hz_cust_acct_sites_ALL cas
7560: WHERE crh.cash_receipt_id = cr.cash_receipt_id
7561: AND (cr.confirmed_flag IS NULL OR cr.confirmed_flag = 'Y')
7562: AND crh.current_record_flag = 'Y'
7592: SELECT SUM(NVL(amount,0)) FROM (
7593: SELECT NVL(crh.amount,0) amount,cr.pay_from_customer,cr.cash_receipt_id
7594: FROM ar_cash_receipt_history_ALL crh
7595: , ar_cash_receipts_ALL cr
7596: , hz_cust_site_uses_ALL su
7597: , hz_cust_acct_sites_ALL cas
7598: WHERE crh.cash_receipt_id = cr.cash_receipt_id
7599: AND (cr.confirmed_flag IS NULL OR cr.confirmed_flag = 'Y')
7600: AND crh.current_record_flag = 'Y'
7660: CURSOR cust_br_balance_global
7661: (p_curr_code IN VARCHAR2 default NULL) IS
7662: SELECT SUM(NVL(ps.amount_due_remaining,0))
7663: FROM ar_payment_schedules_ALL ps
7664: , hz_cust_site_uses_ALL su
7665: , hz_cust_acct_sites_ALL cas
7666: WHERE ps.customer_site_use_id = su.site_use_id
7667: AND ps.status = 'OP'
7668: AND cas.cust_acct_site_id = su.cust_acct_site_id
7676: (p_curr_code IN VARCHAR2 default NULL) IS
7677: SELECT SUM(NVL(crh.amount,0))
7678: FROM ar_cash_receipt_history_ALL crh
7679: , ar_cash_receipts_ALL cr
7680: , hz_cust_site_uses_ALL su
7681: , hz_cust_acct_sites_ALL cas
7682: WHERE crh.cash_receipt_id = cr.cash_receipt_id
7683: AND (cr.confirmed_flag IS NULL OR cr.confirmed_flag = 'Y')
7684: AND crh.current_record_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
7722: AND cas.cust_acct_site_id = su.cust_acct_site_id
7723: AND cas.cust_account_id = p_customer_id
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
7761: AND cas.cust_acct_site_id = su.cust_acct_site_id
7762: AND cas.cust_account_id = p_customer_id
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
7799: AND cas.cust_account_id = p_customer_id
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
7838: AND cas.cust_acct_site_id = su.cust_acct_site_id
7839: AND cas.cust_account_id = p_customer_id
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
7881: AND cas.cust_acct_site_id = su.cust_acct_site_id
7882: AND cas.cust_account_id = p_customer_id
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
7920: AND cas.cust_acct_site_id = su.cust_acct_site_id
7921: AND cas.cust_account_id = p_customer_id
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
7962: AND cas.cust_acct_site_id = su.cust_acct_site_id
7963: AND cas.cust_account_id = p_customer_id
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
8004: AND cas.cust_acct_site_id = su.cust_acct_site_id
8005: AND cas.cust_account_id = p_customer_id
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
8050: AND cas.cust_acct_site_id = su.cust_acct_site_id
8051: AND cas.cust_account_id = p_customer_id
8083: SUM( DECODE( P.CREDIT_OR_CHARGE_FLAG, 'C', (-1), (+1) ) * P.OPERAND )
8084: FROM
8085: oe_price_adjustments p
8086: , oe_order_headers_ALL h
8087: , hz_cust_site_uses_ALL su
8088: , hz_cust_acct_sites_ALL cas
8089: WHERE h.invoice_to_org_id = su.site_use_id
8090: AND cas.cust_acct_site_id = su.cust_acct_site_id
8091: AND cas.cust_account_id = p_customer_id
8123: SUM( DECODE( P.CREDIT_OR_CHARGE_FLAG, 'C', (-1), (+1) ) * P.OPERAND )
8124: FROM
8125: oe_price_adjustments p
8126: , oe_order_headers_ALL h
8127: , hz_cust_site_uses_ALL su
8128: , hz_cust_acct_sites_ALL cas
8129: WHERE h.invoice_to_org_id = su.site_use_id
8130: AND cas.cust_acct_site_id = su.cust_acct_site_id
8131: AND cas.cust_account_id = p_customer_id
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
8172: AND cas.cust_acct_site_id = su.cust_acct_site_id
8173: AND cas.cust_account_id = p_customer_id
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
8222: AND cas.cust_acct_site_id = su.cust_acct_site_id
8223: AND cas.cust_account_id = p_customer_id
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
8269: AND cas.cust_acct_site_id = su.cust_acct_site_id
8270: AND cas.cust_account_id = p_customer_id
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
8319: AND cas.cust_acct_site_id = su.cust_acct_site_id
8320: AND cas.cust_account_id = p_customer_id
8356: SUM( DECODE( P.CREDIT_OR_CHARGE_FLAG, 'C', (-1), (+1) ) * P.OPERAND )
8357: FROM
8358: oe_price_adjustments p
8359: , oe_order_headers_ALL h
8360: , hz_cust_site_uses_ALL su
8361: , hz_cust_acct_sites_ALL cas
8362: WHERE h.invoice_to_org_id = su.site_use_id
8363: AND cas.cust_acct_site_id = su.cust_acct_site_id
8364: AND cas.cust_account_id = p_customer_id
8401: SUM( DECODE( P.CREDIT_OR_CHARGE_FLAG, 'C', (-1), (+1) ) * P.OPERAND )
8402: FROM
8403: oe_price_adjustments p
8404: , oe_order_headers_ALL h
8405: , hz_cust_site_uses_ALL su
8406: , hz_cust_acct_sites_ALL cas
8407: WHERE h.invoice_to_org_id = su.site_use_id
8408: AND cas.cust_acct_site_id = su.cust_acct_site_id
8409: AND cas.cust_account_id = p_customer_id
8444: SUM( DECODE( P.CREDIT_OR_CHARGE_FLAG, 'C', (-1), (+1) ) * P.OPERAND )
8445: FROM
8446: oe_price_adjustments p
8447: , oe_order_headers_ALL h
8448: , hz_cust_site_uses_ALL su
8449: , hz_cust_acct_sites_ALL cas
8450: WHERE h.invoice_to_org_id = su.site_use_id
8451: AND cas.cust_acct_site_id = su.cust_acct_site_id
8452: AND cas.cust_account_id = p_customer_id
8488: SUM( DECODE( P.CREDIT_OR_CHARGE_FLAG, 'C', (-1), (+1) ) * P.OPERAND )
8489: FROM
8490: oe_price_adjustments p
8491: , oe_order_headers_ALL h
8492: , hz_cust_site_uses_ALL su
8493: , hz_cust_acct_sites_ALL cas
8494: WHERE h.invoice_to_org_id = su.site_use_id
8495: AND cas.cust_acct_site_id = su.cust_acct_site_id
8496: AND cas.cust_account_id = p_customer_id