VIEW: APPS.QPR_SR_OFFADJ_HIER_V

Source

View Text - Preformatted

SELECT 'OM-'||pa.price_adjustment_id, nvl(pa.charge_subtype_code,pa.charge_type_code)||'-'||h.order_number|| '-'||l.line_number, null, null, null, null, null, decode(l.shipping_method_code, null, to_char(qpr_sr_util.get_null_pk), 'ShippingMethod-'||l.shipping_method_code), decode(l.shipping_method_code, null, qpr_sr_util.get_null_desc, sm.description), null, null, null, null, null, qpr_sr_util.get_oad_om_type_pk, qpr_sr_util.get_oad_om_type_desc, null, null, null, null, null, qpr_sr_util.get_oad_om_group_pk, qpr_sr_util.get_oad_om_group_desc, null, null, null, null, null, qpr_sr_util.get_all_oad_pk, qpr_sr_util.get_all_oad_desc, null, null, null, null, null, nvl(trunc(h.booked_date,'dd'),to_date('1000/01/01', 'yyyy/mm/dd')) FROM oe_order_lines_all l, oe_price_adjustments pa, oe_order_headers_all h, oe_ship_methods_v sm WHERE pa.line_id=l.line_id and l.header_id = h.header_id and h.booked_flag = 'Y' and l.booked_flag = 'Y' and h.order_category_code <> 'RETURN' and l.line_category_code <> 'RETURN' AND ((nvl(fnd_profile.value('QPR_INTERNAL_CUST'), 'N') = 'N' and h.sold_to_org_id not in ( select cust_account_id from hz_cust_accounts where nvl(customer_type, 'C') = 'I')) or nvl(fnd_profile.value('QPR_INTERNAL_CUST'), 'N') = 'Y') and nvl(h.source_document_type_id,0) <> 10 and l.retrobill_request_id is null and nvl(l.order_source_id,0) <> 27 and pa.list_line_type_code = 'COST' and l.shipping_method_code = sm.lookup_code(+) union all select 'CD-' || ral.customer_trx_line_id, rax.name || '-' || rah.trx_number || '-' || ral.line_number, null, null, null, null, null, decode(oel.payment_term_id, null, to_char(qpr_sr_util.get_null_pk), 'PaymentTerm-'||oel.payment_term_id), decode(oel.payment_term_id, null, qpr_sr_util.get_null_desc, rat.name), null, null, null, null, null, qpr_sr_util.get_oad_ar_cd_type_pk, qpr_sr_util.get_oad_ar_cd_type_desc, null, null, null, null, null, qpr_sr_util.get_oad_ar_group_pk, qpr_sr_util.get_oad_ar_group_desc, null, null, null, null, null, qpr_sr_util.get_all_oad_pk, qpr_sr_util.get_all_oad_desc, null, null, null, null, null, nvl(trunc(rec.apply_date,'dd'),to_date('1000/01/01', 'yyyy/mm/dd')) from ar_receivable_applications_all rec, RA_CUSTOMER_TRX_LINES_ALL ral , RA_CUSTOMER_TRX_all rah, ar_cash_receipt_history_all hist, ar_cash_receipts_all cr, RA_CUST_TRX_TYPES_ALL rax, oe_order_lines_all oel, ra_terms_vl rat where hist.status in ('CONFIRMED', 'CLEARED', 'REMITTED') and hist.current_record_flag = 'Y' and hist.cash_receipt_id = rec.cash_receipt_id and cr.cash_receipt_id = hist.cash_receipt_id and rec.status = 'APP' and rec.display = 'Y' and rec.application_type = 'CASH' and (nvl(rec.earned_discount_taken, 0) <> 0 or nvl(rec.unearned_discount_taken, 0) <> 0) and rec.applied_customer_trx_id = rah.customer_trx_id AND ((nvl(fnd_profile.value('QPR_INTERNAL_CUST'), 'N') = 'N' and rah.sold_to_customer_id not in ( select cust_account_id from hz_cust_accounts where nvl(customer_type, 'C') = 'I')) or nvl(fnd_profile.value('QPR_INTERNAL_CUST'), 'N') = 'Y') and nvl(rec.applied_customer_trx_line_id, ral.customer_trx_line_id) = ral.customer_trx_line_id and ral.customer_trx_id = rah.customer_trx_id and ral.line_type = 'LINE' and rah.CUST_TRX_TYPE_ID = rax.CUST_TRX_TYPE_ID and oel.line_id = ral.interface_line_attribute6 and oel.org_id = ral.org_id and oel.PAYMENT_TERM_ID = rat.TERM_ID(+)

View Text - HTML Formatted

SELECT 'OM-'||PA.PRICE_ADJUSTMENT_ID
, NVL(PA.CHARGE_SUBTYPE_CODE
, PA.CHARGE_TYPE_CODE)||'-'||H.ORDER_NUMBER|| '-'||L.LINE_NUMBER
, NULL
, NULL
, NULL
, NULL
, NULL
, DECODE(L.SHIPPING_METHOD_CODE
, NULL
, TO_CHAR(QPR_SR_UTIL.GET_NULL_PK)
, 'SHIPPINGMETHOD-'||L.SHIPPING_METHOD_CODE)
, DECODE(L.SHIPPING_METHOD_CODE
, NULL
, QPR_SR_UTIL.GET_NULL_DESC
, SM.DESCRIPTION)
, NULL
, NULL
, NULL
, NULL
, NULL
, QPR_SR_UTIL.GET_OAD_OM_TYPE_PK
, QPR_SR_UTIL.GET_OAD_OM_TYPE_DESC
, NULL
, NULL
, NULL
, NULL
, NULL
, QPR_SR_UTIL.GET_OAD_OM_GROUP_PK
, QPR_SR_UTIL.GET_OAD_OM_GROUP_DESC
, NULL
, NULL
, NULL
, NULL
, NULL
, QPR_SR_UTIL.GET_ALL_OAD_PK
, QPR_SR_UTIL.GET_ALL_OAD_DESC
, NULL
, NULL
, NULL
, NULL
, NULL
, NVL(TRUNC(H.BOOKED_DATE
, 'DD')
, TO_DATE('1000/01/01'
, 'YYYY/MM/DD'))
FROM OE_ORDER_LINES_ALL L
, OE_PRICE_ADJUSTMENTS PA
, OE_ORDER_HEADERS_ALL H
, OE_SHIP_METHODS_V SM
WHERE PA.LINE_ID=L.LINE_ID
AND L.HEADER_ID = H.HEADER_ID
AND H.BOOKED_FLAG = 'Y'
AND L.BOOKED_FLAG = 'Y'
AND H.ORDER_CATEGORY_CODE <> 'RETURN'
AND L.LINE_CATEGORY_CODE <> 'RETURN'
AND ((NVL(FND_PROFILE.VALUE('QPR_INTERNAL_CUST')
, 'N') = 'N'
AND H.SOLD_TO_ORG_ID NOT IN ( SELECT CUST_ACCOUNT_ID
FROM HZ_CUST_ACCOUNTS
WHERE NVL(CUSTOMER_TYPE
, 'C') = 'I')) OR NVL(FND_PROFILE.VALUE('QPR_INTERNAL_CUST')
, 'N') = 'Y')
AND NVL(H.SOURCE_DOCUMENT_TYPE_ID
, 0) <> 10
AND L.RETROBILL_REQUEST_ID IS NULL
AND NVL(L.ORDER_SOURCE_ID
, 0) <> 27
AND PA.LIST_LINE_TYPE_CODE = 'COST'
AND L.SHIPPING_METHOD_CODE = SM.LOOKUP_CODE(+) UNION ALL SELECT 'CD-' || RAL.CUSTOMER_TRX_LINE_ID
, RAX.NAME || '-' || RAH.TRX_NUMBER || '-' || RAL.LINE_NUMBER
, NULL
, NULL
, NULL
, NULL
, NULL
, DECODE(OEL.PAYMENT_TERM_ID
, NULL
, TO_CHAR(QPR_SR_UTIL.GET_NULL_PK)
, 'PAYMENTTERM-'||OEL.PAYMENT_TERM_ID)
, DECODE(OEL.PAYMENT_TERM_ID
, NULL
, QPR_SR_UTIL.GET_NULL_DESC
, RAT.NAME)
, NULL
, NULL
, NULL
, NULL
, NULL
, QPR_SR_UTIL.GET_OAD_AR_CD_TYPE_PK
, QPR_SR_UTIL.GET_OAD_AR_CD_TYPE_DESC
, NULL
, NULL
, NULL
, NULL
, NULL
, QPR_SR_UTIL.GET_OAD_AR_GROUP_PK
, QPR_SR_UTIL.GET_OAD_AR_GROUP_DESC
, NULL
, NULL
, NULL
, NULL
, NULL
, QPR_SR_UTIL.GET_ALL_OAD_PK
, QPR_SR_UTIL.GET_ALL_OAD_DESC
, NULL
, NULL
, NULL
, NULL
, NULL
, NVL(TRUNC(REC.APPLY_DATE
, 'DD')
, TO_DATE('1000/01/01'
, 'YYYY/MM/DD'))
FROM AR_RECEIVABLE_APPLICATIONS_ALL REC
, RA_CUSTOMER_TRX_LINES_ALL RAL
, RA_CUSTOMER_TRX_ALL RAH
, AR_CASH_RECEIPT_HISTORY_ALL HIST
, AR_CASH_RECEIPTS_ALL CR
, RA_CUST_TRX_TYPES_ALL RAX
, OE_ORDER_LINES_ALL OEL
, RA_TERMS_VL RAT
WHERE HIST.STATUS IN ('CONFIRMED'
, 'CLEARED'
, 'REMITTED')
AND HIST.CURRENT_RECORD_FLAG = 'Y'
AND HIST.CASH_RECEIPT_ID = REC.CASH_RECEIPT_ID
AND CR.CASH_RECEIPT_ID = HIST.CASH_RECEIPT_ID
AND REC.STATUS = 'APP'
AND REC.DISPLAY = 'Y'
AND REC.APPLICATION_TYPE = 'CASH'
AND (NVL(REC.EARNED_DISCOUNT_TAKEN
, 0) <> 0 OR NVL(REC.UNEARNED_DISCOUNT_TAKEN
, 0) <> 0)
AND REC.APPLIED_CUSTOMER_TRX_ID = RAH.CUSTOMER_TRX_ID
AND ((NVL(FND_PROFILE.VALUE('QPR_INTERNAL_CUST')
, 'N') = 'N'
AND RAH.SOLD_TO_CUSTOMER_ID NOT IN ( SELECT CUST_ACCOUNT_ID
FROM HZ_CUST_ACCOUNTS
WHERE NVL(CUSTOMER_TYPE
, 'C') = 'I')) OR NVL(FND_PROFILE.VALUE('QPR_INTERNAL_CUST')
, 'N') = 'Y')
AND NVL(REC.APPLIED_CUSTOMER_TRX_LINE_ID
, RAL.CUSTOMER_TRX_LINE_ID) = RAL.CUSTOMER_TRX_LINE_ID
AND RAL.CUSTOMER_TRX_ID = RAH.CUSTOMER_TRX_ID
AND RAL.LINE_TYPE = 'LINE'
AND RAH.CUST_TRX_TYPE_ID = RAX.CUST_TRX_TYPE_ID
AND OEL.LINE_ID = RAL.INTERFACE_LINE_ATTRIBUTE6
AND OEL.ORG_ID = RAL.ORG_ID
AND OEL.PAYMENT_TERM_ID = RAT.TERM_ID(+)