VIEW: APPS.PO_PRICE_ADJUSTMENTS_V

Source

View Text - Preformatted

SELECT PPD.DRAFT_ID,PPD.CHANGE_ACCEPTED_FLAG,PPD.DELETE_FLAG,PPD.PRICE_ADJUSTMENT_ID,PPD.CREATION_DATE,PPD.CREATED_BY,PPD.LAST_UPDATE_DATE,PPD.LAST_UPDATED_BY,PPD.LAST_UPDATE_LOGIN,PPD.PROGRAM_APPLICATION_ID,PPD.PROGRAM_ID,PPD.PROGRAM_UPDATE_DATE,PPD.REQUEST_ID,PPD.PO_HEADER_ID,PPD.AUTOMATIC_FLAG,PPD.PO_LINE_ID,PPD.ADJ_LINE_NUM,PPD.CONTEXT,PPD.ATTRIBUTE1,PPD.ATTRIBUTE2,PPD.ATTRIBUTE3,PPD.ATTRIBUTE4,PPD.ATTRIBUTE5,PPD.ATTRIBUTE6,PPD.ATTRIBUTE7,PPD.ATTRIBUTE8,PPD.ATTRIBUTE9,PPD.ATTRIBUTE10,PPD.ATTRIBUTE11,PPD.ATTRIBUTE12,PPD.ATTRIBUTE13,PPD.ATTRIBUTE14,PPD.ATTRIBUTE15,PPD.ORIG_SYS_DISCOUNT_REF,PPD.CHANGE_SEQUENCE,PPD.LIST_HEADER_ID,PPD.LIST_TYPE_CODE,PPD.LIST_LINE_ID,PPD.LIST_LINE_TYPE_CODE,PPD.MODIFIED_FROM,PPD.MODIFIED_TO,PPD.UPDATE_ALLOWED,PPD.CHANGE_REASON_CODE,PPD.CHANGE_REASON_TEXT,PPD.UPDATED_FLAG,PPD.APPLIED_FLAG,PPD.OPERAND,PPD.ARITHMETIC_OPERATOR,PPD.COST_ID,PPD.TAX_CODE,PPD.TAX_EXEMPT_FLAG,PPD.TAX_EXEMPT_NUMBER,PPD.TAX_EXEMPT_REASON_CODE,PPD.PARENT_ADJUSTMENT_ID,PPD.LIST_LINE_TYPE_CODE,PPD.LIST_LINE_ID,PPD.INVOICED_FLAG,PPD.ESTIMATED_FLAG,PPD.INC_IN_SALES_PERFORMANCE,PPD.ADJUSTED_AMOUNT,PPD.PRICING_PHASE_ID,PPD.CHARGE_TYPE_CODE,PPD.CHARGE_SUBTYPE_CODE,PPD.RANGE_BREAK_QUANTITY,PPD.ACCRUAL_CONVERSION_RATE,PPD.PRICING_GROUP_SEQUENCE,PPD.ACCRUAL_FLAG,PPD.LIST_LINE_NO,PPD.SOURCE_SYSTEM_CODE,PPD.BENEFIT_QTY,PPD.BENEFIT_UOM_CODE,PPD.PRINT_ON_INVOICE_FLAG,PPD.EXPIRATION_DATE,PPD.REBATE_TRANSACTION_TYPE_CODE,PPD.REBATE_TRANSACTION_REFERENCE,PPD.REBATE_PAYMENT_SYSTEM_CODE,PPD.REDEEMED_DATE,PPD.REDEEMED_FLAG,PPD.MODIFIER_LEVEL_CODE,PPD.PRICE_BREAK_TYPE_CODE,PPD.SUBSTITUTION_ATTRIBUTE,PPD.PRORATION_TYPE_CODE,PPD.INCLUDE_ON_RETURNS_FLAG,PPD.CREDIT_OR_CHARGE_FLAG,PPD.AC_CONTEXT,PPD.AC_ATTRIBUTE1,PPD.AC_ATTRIBUTE2,PPD.AC_ATTRIBUTE3,PPD.AC_ATTRIBUTE4,PPD.AC_ATTRIBUTE5,PPD.AC_ATTRIBUTE6,PPD.AC_ATTRIBUTE7,PPD.AC_ATTRIBUTE8,PPD.AC_ATTRIBUTE9,PPD.AC_ATTRIBUTE10,PPD.AC_ATTRIBUTE11,PPD.AC_ATTRIBUTE12,PPD.AC_ATTRIBUTE13,PPD.AC_ATTRIBUTE14,PPD.AC_ATTRIBUTE15,PPD.OPERAND_PER_PQTY,PPD.ADJUSTED_AMOUNT_PER_PQTY,PPD.INTERCO_INVOICED_FLAG,PPD.INVOICED_AMOUNT,PPD.RETROBILL_REQUEST_ID,PPD.TAX_RATE_ID FROM PO_PRICE_ADJUSTMENTS_DRAFT PPD,PO_PRICE_ADJUSTMENTS_DRAFT RPPD,PO_DRAFTS DFT WHERE ppd.draft_id = dft.draft_id (+) AND NVL(ppd.delete_flag,'N') = 'N' AND ppd.parent_adjustment_id = rppd.price_adjustment_id (+) AND ppd.draft_id = rppd.draft_id (+) AND NVL(rppd.delete_flag (+),'N') = 'N' AND dft.owner_role (+) = po_global.ROLE AND dft.status (+) <> 'COMPLETED' UNION ALL SELECT TO_NUMBER(NULL), NULL, NULL, ppa.price_adjustment_id, ppa.creation_date, ppa.created_by, ppa.last_update_date, ppa.last_updated_by, ppa.last_update_login, ppa.program_application_id, ppa.program_id, ppa.program_update_date, ppa.request_id, ppa.po_header_id, ppa.automatic_flag, ppa.po_line_id, ppa.adj_line_num, ppa.context, ppa.attribute1, ppa.attribute2, ppa.attribute3, ppa.attribute4, ppa.attribute5, ppa.attribute6, ppa.attribute7, ppa.attribute8, ppa.attribute9, ppa.attribute10, ppa.attribute11, ppa.attribute12, ppa.attribute13, ppa.attribute14, ppa.attribute15, ppa.orig_sys_discount_ref, ppa.change_sequence, ppa.list_header_id, ppa.list_type_code, ppa.list_line_id, ppa.list_line_type_code, ppa.modified_from, ppa.modified_to, ppa.update_allowed, ppa.change_reason_code, ppa.change_reason_text, ppa.updated_flag, ppa.applied_flag, ppa.operand, ppa.arithmetic_operator, ppa.cost_id, ppa.tax_code, ppa.tax_exempt_flag, ppa.tax_exempt_number, ppa.tax_exempt_reason_code, ppa.parent_adjustment_id, rppa.list_line_type_code, rppa.list_line_id, ppa.invoiced_flag, ppa.estimated_flag, ppa.inc_in_sales_performance, ppa.adjusted_amount, ppa.pricing_phase_id, ppa.charge_type_code, ppa.charge_subtype_code, ppa.range_break_quantity, ppa.accrual_conversion_rate, ppa.pricing_group_sequence, ppa.accrual_flag, ppa.list_line_no, ppa.source_system_code, ppa.benefit_qty, ppa.benefit_uom_code, ppa.print_on_invoice_flag, ppa.expiration_date, ppa.rebate_transaction_type_code, ppa.rebate_transaction_reference, ppa.rebate_payment_system_code, ppa.redeemed_date, ppa.redeemed_flag, ppa.modifier_level_code, ppa.price_break_type_code, ppa.substitution_attribute, ppa.proration_type_code, ppa.include_on_returns_flag, ppa.credit_or_charge_flag, ppa.ac_context, ppa.ac_attribute1, ppa.ac_attribute2, ppa.ac_attribute3, ppa.ac_attribute4, ppa.ac_attribute5, ppa.ac_attribute6, ppa.ac_attribute7, ppa.ac_attribute8, ppa.ac_attribute9, ppa.ac_attribute10, ppa.ac_attribute11, ppa.ac_attribute12, ppa.ac_attribute13, ppa.ac_attribute14, ppa.ac_attribute15, ppa.operand_per_pqty, ppa.adjusted_amount_per_pqty, ppa.interco_invoiced_flag, ppa.invoiced_amount, ppa.retrobill_request_id, ppa.tax_rate_id FROM po_price_adjustments ppa, po_price_adjustments rppa WHERE ppa.parent_adjustment_id = rppa.price_adjustment_id (+) AND NOT EXISTS (SELECT 1 FROM po_price_adjustments_draft ppd1, po_drafts dft1 WHERE ppa.price_adjustment_id = ppd1.price_adjustment_id AND ppd1.draft_id = dft1.draft_id (+) AND dft1.owner_role (+) = po_global.ROLE AND dft1.status (+) <> 'COMPLETED') AND NOT EXISTS (SELECT 1 FROM po_price_adjustments_draft ppd2, po_drafts dft2 WHERE ppa.parent_adjustment_id = ppd2.price_adjustment_id AND ppd2.draft_id = dft2.draft_id (+) AND dft2.owner_role (+) = po_global.ROLE AND dft2.status (+) <> 'COMPLETED')

View Text - HTML Formatted

SELECT PPD.DRAFT_ID
, PPD.CHANGE_ACCEPTED_FLAG
, PPD.DELETE_FLAG
, PPD.PRICE_ADJUSTMENT_ID
, PPD.CREATION_DATE
, PPD.CREATED_BY
, PPD.LAST_UPDATE_DATE
, PPD.LAST_UPDATED_BY
, PPD.LAST_UPDATE_LOGIN
, PPD.PROGRAM_APPLICATION_ID
, PPD.PROGRAM_ID
, PPD.PROGRAM_UPDATE_DATE
, PPD.REQUEST_ID
, PPD.PO_HEADER_ID
, PPD.AUTOMATIC_FLAG
, PPD.PO_LINE_ID
, PPD.ADJ_LINE_NUM
, PPD.CONTEXT
, PPD.ATTRIBUTE1
, PPD.ATTRIBUTE2
, PPD.ATTRIBUTE3
, PPD.ATTRIBUTE4
, PPD.ATTRIBUTE5
, PPD.ATTRIBUTE6
, PPD.ATTRIBUTE7
, PPD.ATTRIBUTE8
, PPD.ATTRIBUTE9
, PPD.ATTRIBUTE10
, PPD.ATTRIBUTE11
, PPD.ATTRIBUTE12
, PPD.ATTRIBUTE13
, PPD.ATTRIBUTE14
, PPD.ATTRIBUTE15
, PPD.ORIG_SYS_DISCOUNT_REF
, PPD.CHANGE_SEQUENCE
, PPD.LIST_HEADER_ID
, PPD.LIST_TYPE_CODE
, PPD.LIST_LINE_ID
, PPD.LIST_LINE_TYPE_CODE
, PPD.MODIFIED_FROM
, PPD.MODIFIED_TO
, PPD.UPDATE_ALLOWED
, PPD.CHANGE_REASON_CODE
, PPD.CHANGE_REASON_TEXT
, PPD.UPDATED_FLAG
, PPD.APPLIED_FLAG
, PPD.OPERAND
, PPD.ARITHMETIC_OPERATOR
, PPD.COST_ID
, PPD.TAX_CODE
, PPD.TAX_EXEMPT_FLAG
, PPD.TAX_EXEMPT_NUMBER
, PPD.TAX_EXEMPT_REASON_CODE
, PPD.PARENT_ADJUSTMENT_ID
, PPD.LIST_LINE_TYPE_CODE
, PPD.LIST_LINE_ID
, PPD.INVOICED_FLAG
, PPD.ESTIMATED_FLAG
, PPD.INC_IN_SALES_PERFORMANCE
, PPD.ADJUSTED_AMOUNT
, PPD.PRICING_PHASE_ID
, PPD.CHARGE_TYPE_CODE
, PPD.CHARGE_SUBTYPE_CODE
, PPD.RANGE_BREAK_QUANTITY
, PPD.ACCRUAL_CONVERSION_RATE
, PPD.PRICING_GROUP_SEQUENCE
, PPD.ACCRUAL_FLAG
, PPD.LIST_LINE_NO
, PPD.SOURCE_SYSTEM_CODE
, PPD.BENEFIT_QTY
, PPD.BENEFIT_UOM_CODE
, PPD.PRINT_ON_INVOICE_FLAG
, PPD.EXPIRATION_DATE
, PPD.REBATE_TRANSACTION_TYPE_CODE
, PPD.REBATE_TRANSACTION_REFERENCE
, PPD.REBATE_PAYMENT_SYSTEM_CODE
, PPD.REDEEMED_DATE
, PPD.REDEEMED_FLAG
, PPD.MODIFIER_LEVEL_CODE
, PPD.PRICE_BREAK_TYPE_CODE
, PPD.SUBSTITUTION_ATTRIBUTE
, PPD.PRORATION_TYPE_CODE
, PPD.INCLUDE_ON_RETURNS_FLAG
, PPD.CREDIT_OR_CHARGE_FLAG
, PPD.AC_CONTEXT
, PPD.AC_ATTRIBUTE1
, PPD.AC_ATTRIBUTE2
, PPD.AC_ATTRIBUTE3
, PPD.AC_ATTRIBUTE4
, PPD.AC_ATTRIBUTE5
, PPD.AC_ATTRIBUTE6
, PPD.AC_ATTRIBUTE7
, PPD.AC_ATTRIBUTE8
, PPD.AC_ATTRIBUTE9
, PPD.AC_ATTRIBUTE10
, PPD.AC_ATTRIBUTE11
, PPD.AC_ATTRIBUTE12
, PPD.AC_ATTRIBUTE13
, PPD.AC_ATTRIBUTE14
, PPD.AC_ATTRIBUTE15
, PPD.OPERAND_PER_PQTY
, PPD.ADJUSTED_AMOUNT_PER_PQTY
, PPD.INTERCO_INVOICED_FLAG
, PPD.INVOICED_AMOUNT
, PPD.RETROBILL_REQUEST_ID
, PPD.TAX_RATE_ID
FROM PO_PRICE_ADJUSTMENTS_DRAFT PPD
, PO_PRICE_ADJUSTMENTS_DRAFT RPPD
, PO_DRAFTS DFT
WHERE PPD.DRAFT_ID = DFT.DRAFT_ID (+)
AND NVL(PPD.DELETE_FLAG
, 'N') = 'N'
AND PPD.PARENT_ADJUSTMENT_ID = RPPD.PRICE_ADJUSTMENT_ID (+)
AND PPD.DRAFT_ID = RPPD.DRAFT_ID (+)
AND NVL(RPPD.DELETE_FLAG (+)
, 'N') = 'N'
AND DFT.OWNER_ROLE (+) = PO_GLOBAL.ROLE
AND DFT.STATUS (+) <> 'COMPLETED' UNION ALL SELECT TO_NUMBER(NULL)
, NULL
, NULL
, PPA.PRICE_ADJUSTMENT_ID
, PPA.CREATION_DATE
, PPA.CREATED_BY
, PPA.LAST_UPDATE_DATE
, PPA.LAST_UPDATED_BY
, PPA.LAST_UPDATE_LOGIN
, PPA.PROGRAM_APPLICATION_ID
, PPA.PROGRAM_ID
, PPA.PROGRAM_UPDATE_DATE
, PPA.REQUEST_ID
, PPA.PO_HEADER_ID
, PPA.AUTOMATIC_FLAG
, PPA.PO_LINE_ID
, PPA.ADJ_LINE_NUM
, PPA.CONTEXT
, PPA.ATTRIBUTE1
, PPA.ATTRIBUTE2
, PPA.ATTRIBUTE3
, PPA.ATTRIBUTE4
, PPA.ATTRIBUTE5
, PPA.ATTRIBUTE6
, PPA.ATTRIBUTE7
, PPA.ATTRIBUTE8
, PPA.ATTRIBUTE9
, PPA.ATTRIBUTE10
, PPA.ATTRIBUTE11
, PPA.ATTRIBUTE12
, PPA.ATTRIBUTE13
, PPA.ATTRIBUTE14
, PPA.ATTRIBUTE15
, PPA.ORIG_SYS_DISCOUNT_REF
, PPA.CHANGE_SEQUENCE
, PPA.LIST_HEADER_ID
, PPA.LIST_TYPE_CODE
, PPA.LIST_LINE_ID
, PPA.LIST_LINE_TYPE_CODE
, PPA.MODIFIED_FROM
, PPA.MODIFIED_TO
, PPA.UPDATE_ALLOWED
, PPA.CHANGE_REASON_CODE
, PPA.CHANGE_REASON_TEXT
, PPA.UPDATED_FLAG
, PPA.APPLIED_FLAG
, PPA.OPERAND
, PPA.ARITHMETIC_OPERATOR
, PPA.COST_ID
, PPA.TAX_CODE
, PPA.TAX_EXEMPT_FLAG
, PPA.TAX_EXEMPT_NUMBER
, PPA.TAX_EXEMPT_REASON_CODE
, PPA.PARENT_ADJUSTMENT_ID
, RPPA.LIST_LINE_TYPE_CODE
, RPPA.LIST_LINE_ID
, PPA.INVOICED_FLAG
, PPA.ESTIMATED_FLAG
, PPA.INC_IN_SALES_PERFORMANCE
, PPA.ADJUSTED_AMOUNT
, PPA.PRICING_PHASE_ID
, PPA.CHARGE_TYPE_CODE
, PPA.CHARGE_SUBTYPE_CODE
, PPA.RANGE_BREAK_QUANTITY
, PPA.ACCRUAL_CONVERSION_RATE
, PPA.PRICING_GROUP_SEQUENCE
, PPA.ACCRUAL_FLAG
, PPA.LIST_LINE_NO
, PPA.SOURCE_SYSTEM_CODE
, PPA.BENEFIT_QTY
, PPA.BENEFIT_UOM_CODE
, PPA.PRINT_ON_INVOICE_FLAG
, PPA.EXPIRATION_DATE
, PPA.REBATE_TRANSACTION_TYPE_CODE
, PPA.REBATE_TRANSACTION_REFERENCE
, PPA.REBATE_PAYMENT_SYSTEM_CODE
, PPA.REDEEMED_DATE
, PPA.REDEEMED_FLAG
, PPA.MODIFIER_LEVEL_CODE
, PPA.PRICE_BREAK_TYPE_CODE
, PPA.SUBSTITUTION_ATTRIBUTE
, PPA.PRORATION_TYPE_CODE
, PPA.INCLUDE_ON_RETURNS_FLAG
, PPA.CREDIT_OR_CHARGE_FLAG
, PPA.AC_CONTEXT
, PPA.AC_ATTRIBUTE1
, PPA.AC_ATTRIBUTE2
, PPA.AC_ATTRIBUTE3
, PPA.AC_ATTRIBUTE4
, PPA.AC_ATTRIBUTE5
, PPA.AC_ATTRIBUTE6
, PPA.AC_ATTRIBUTE7
, PPA.AC_ATTRIBUTE8
, PPA.AC_ATTRIBUTE9
, PPA.AC_ATTRIBUTE10
, PPA.AC_ATTRIBUTE11
, PPA.AC_ATTRIBUTE12
, PPA.AC_ATTRIBUTE13
, PPA.AC_ATTRIBUTE14
, PPA.AC_ATTRIBUTE15
, PPA.OPERAND_PER_PQTY
, PPA.ADJUSTED_AMOUNT_PER_PQTY
, PPA.INTERCO_INVOICED_FLAG
, PPA.INVOICED_AMOUNT
, PPA.RETROBILL_REQUEST_ID
, PPA.TAX_RATE_ID
FROM PO_PRICE_ADJUSTMENTS PPA
, PO_PRICE_ADJUSTMENTS RPPA
WHERE PPA.PARENT_ADJUSTMENT_ID = RPPA.PRICE_ADJUSTMENT_ID (+)
AND NOT EXISTS (SELECT 1
FROM PO_PRICE_ADJUSTMENTS_DRAFT PPD1
, PO_DRAFTS DFT1
WHERE PPA.PRICE_ADJUSTMENT_ID = PPD1.PRICE_ADJUSTMENT_ID
AND PPD1.DRAFT_ID = DFT1.DRAFT_ID (+)
AND DFT1.OWNER_ROLE (+) = PO_GLOBAL.ROLE
AND DFT1.STATUS (+) <> 'COMPLETED')
AND NOT EXISTS (SELECT 1
FROM PO_PRICE_ADJUSTMENTS_DRAFT PPD2
, PO_DRAFTS DFT2
WHERE PPA.PARENT_ADJUSTMENT_ID = PPD2.PRICE_ADJUSTMENT_ID
AND PPD2.DRAFT_ID = DFT2.DRAFT_ID (+)
AND DFT2.OWNER_ROLE (+) = PO_GLOBAL.ROLE
AND DFT2.STATUS (+) <> 'COMPLETED')