@AbapCatalog.sqlViewName : 'PPPMGDOCTOTAMT'
@AbapCatalog.compiler.compareFilter : true
@AbapCatalog.preserveKey : true
@ClientHandling.algorithm : #SESSION_VARIABLE
@AccessControl.authorizationCheck : #NOT_REQUIRED
@VDM.private : true
@VDM.viewType : #COMPOSITE
@ObjectModel.usageType.sizeCategory : #L
@ObjectModel.usageType.serviceQuality : #A
@ObjectModel.usageType.dataClass : #TRANSACTIONAL
@Metadata.ignorePropagatedAnnotations : true
--@EndUserText.label: 'Prepayment Document Total Amount'
------------------------------------------------------------------------------------
-- This UNION view will get the Total price for Applicable documents in Work Center
-- Based on Pricing Option configuration, one of below 3 options will be used for
-- Total Amount derrivations:
-- VLOGP: Using CMM_VLOGP data
-- CPE: Using CPE tables data
-- Net Price: Net Price from the document
--
-- Union Views:
-- View 1: Trading Contracts
-- View 2: Purchasing Documents
-- View 3: Sales Documents
-- Only 1 view data should be retrieved, based on value for BO
------------------------------------------------------------------------------------
define view P_PrepaymentRefDocTotalAmount
with parameters
P_DocCat : ppmgag_e_documentcategory,
P_BusObj : ppmgac_e_link_bus
--------------------------------------------------------------------------------------
-- View 1: TRADING CONTRACTS
--------------------------------------------------------------------------------------
-- Valid Doc Types
as select from I_PrepayWrkCtrAllowedDocTypes as DocTypes
-- Document Header
inner join I_PreAgrmtTradingContract as Document on Document.TradingContractType = DocTypes.PrepaymentAgrmtRefDocumentType
-- Document Item
inner join I_PreAgrmtTradingContractItem as DocumentItem on DocumentItem.TradingContract = Document.TradingContract
-- Material and Commodity
inner join I_PrepaymentMaterial as Material on DocumentItem.Material = Material.Material
-- Pricing Options config
left outer to one join I_PrepaymentWrkCtrLinkConfign as WCPrcConfig on WCPrcConfig.PrepaymentAgrmtWorkCenterType = DocTypes.PrepaymentAgrmtWorkCenterType
-- CMM Price
association [0..1] to P_PrepaymentDocVersLogPrcgVal as CMMPrice on WCPrcConfig.PrepaymentWrkCtrDocPricingOptn = '01'
and Material.Commodity is not initial
and CMMPrice.PrepaymentAgrmtRefBusObject = :P_BusObj
and CMMPrice.PrepaymentDocumentNumber = Document.TradingContract
and CMMPrice.PrepaymentDocumentItemNumber = DocumentItem.TradingContractItem
-- CPE Price
association [0..1] to I_PrepaymentDocumentMarketVal as CPEPrice on WCPrcConfig.PrepaymentWrkCtrDocPricingOptn = '02'
and Material.Commodity is not initial
and CPEPrice.PrepaymentAgrmtRefBusObject = :P_BusObj
and CPEPrice.PrepaymentRefDocCondition = Document.PrepaymentRefDocCondition
and CPEPrice.PrepaymentRefDocConditionItem = DocumentItem.TradingContractItem
{
key DocTypes.PrepaymentAgrmtWorkCenterType as PrepaymentAgrmtWorkCenterType,
key DocTypes.PrepaymentAgrmtRefDocumentCat as PrepaymentAgrmtRefDocumentCat,
key :P_BusObj as PrepaymentAgrmtRefBusObject,
key Document.TradingContract as DocNumber,
key DocumentItem.TradingContractItem as DocumentItemNumber,
cast('00000' as abap.numc(5)) as PurchasingDocumentItem,
lpad( Document.TradingContract, 25, '0' ) as PrepaymentAgrmtRefDocument,
Document.CompanyCode as CompanyCode,
Document.TradingContractType as DocumentType,
@Semantics.amount.currencyCode: 'PrepaymentTotalAmtCrcy'
cast(
case when WCPrcConfig.PrepaymentWrkCtrDocPricingOptn = '01' -- CMM Pricing
and Material.Commodity is not initial
then CMMPrice.PrepaymentTotalAmt
when WCPrcConfig.PrepaymentWrkCtrDocPricingOptn = '02' -- CPE Pricing
and Material.Commodity is not initial
then CPEPrice.PrepaymentTotalAmt
when WCPrcConfig.PrepaymentWrkCtrDocPricingOptn = '03' -- Item Net Amount
or WCPrcConfig.PrepaymentWrkCtrDocPricingOptn is null
or Material.Commodity is initial
then DocumentItem.TrdgContrTotalNetAmount
else 0 end as abap.curr(23,2) ) as PrepaymentTotalAmt,
@Semantics.currencyCode: true
DocumentItem.TrdgContrTotalNetAmountCrcy as PrepaymentTotalAmtCrcy,
case when WCPrcConfig.PrepaymentWrkCtrDocPricingOptn = '01' -- CMM Pricing
and CMMPrice.PrepaymentDocCmmdtyMarketQty is initial
and Material.Commodity is not initial
then 'X'
when WCPrcConfig.PrepaymentWrkCtrDocPricingOptn = '02' -- CPE Pricing
and Material.Commodity is not initial
then CPEPrice.PrepaymentTotalAmtIsFixedPrice
when WCPrcConfig.PrepaymentWrkCtrDocPricingOptn = '03' -- Item Net Amount
or WCPrcConfig.PrepaymentWrkCtrDocPricingOptn is null
or Material.Commodity is initial
then 'X'
else '' end as PrepaymentTotalAmtIsFixedPrice
}
where
DocTypes.PrepaymentAgrmtRefDocumentCat = 'TC'
and DocTypes.PrepaymentAgrmtRefDocumentCat = :P_DocCat
union
--------------------------------------------------------------------------------------
-- View 2: PURCHASING DOCUMENTS
--------------------------------------------------------------------------------------
-- Valid Doc Types
select from I_PrepayWrkCtrAllowedDocTypes as DocTypes
-- Document Header
inner join I_PurchasingDocument as Document on Document.PurchasingDocumentType = DocTypes.PrepaymentAgrmtRefDocumentType
-- Document Item
inner join I_PurchasingDocumentItem as DocumentItem on DocumentItem.PurchasingDocument = Document.PurchasingDocument
-- Material and Commodity
inner join I_PrepaymentMaterial as Material on DocumentItem.Material = Material.Material
-- Pricing Options config
left outer to one join I_PrepaymentWrkCtrLinkConfign as WCPrcConfig on WCPrcConfig.PrepaymentAgrmtWorkCenterType = DocTypes.PrepaymentAgrmtWorkCenterType
-- CMM Prices
association [0..1] to P_PrepaymentDocVersLogPrcgVal as CMMPrice on WCPrcConfig.PrepaymentWrkCtrDocPricingOptn = '01'
and Material.Commodity is not initial
and CMMPrice.PrepaymentAgrmtRefBusObject = :P_BusObj
and CMMPrice.PrepaymentDocumentNumber = Document.PurchasingDocument
and CMMPrice.PurchasingDocumentItem = DocumentItem.PurchasingDocumentItem
-- CPE Price
association [0..1] to I_PrepaymentDocumentMarketVal as CPEPrice on WCPrcConfig.PrepaymentWrkCtrDocPricingOptn = '02'
and Material.Commodity is not initial
and CPEPrice.PrepaymentAgrmtRefBusObject = :P_BusObj
and CPEPrice.PrepaymentRefDocCondition = Document.PurchasingDocumentCondition
and CPEPrice.PurchasingDocumentItem = DocumentItem.PurchasingDocumentItem
{
key DocTypes.PrepaymentAgrmtWorkCenterType as PrepaymentAgrmtWorkCenterType,
key DocTypes.PrepaymentAgrmtRefDocumentCat as PrepaymentAgrmtRefDocumentCat,
key :P_BusObj as PrepaymentAgrmtRefBusObject,
key Document.PurchasingDocument as DocNumber,
key cast( DocumentItem.PurchasingDocumentItem as abap.char(6) ) as DocumentItemNumber,
DocumentItem.PurchasingDocumentItem as PurchasingDocumentITem,
lpad( Document.PurchasingDocument, 25, '0' ) as PrepaymentAgrmtRefDocument,
Document.CompanyCode as CompanyCode,
Document.PurchasingDocumentType as DocumentType,
@Semantics.amount.currencyCode: 'PrepaymentTotalAmtCrcy'
cast(
case when WCPrcConfig.PrepaymentWrkCtrDocPricingOptn = '01' -- CMM Pricing
and Material.Commodity is not initial
then CMMPrice.PrepaymentTotalAmt
when WCPrcConfig.PrepaymentWrkCtrDocPricingOptn = '02' -- CPE Pricing
and Material.Commodity is not initial
then CPEPrice.PrepaymentTotalAmt
when WCPrcConfig.PrepaymentWrkCtrDocPricingOptn = '03' -- Item Net Amount
or WCPrcConfig.PrepaymentWrkCtrDocPricingOptn is null
or Material.Commodity is initial
then DocumentItem.NetAmount
else 0 end as abap.curr(23,2) ) as PrepaymentTotalAmt,
@Semantics.currencyCode: true
Document.DocumentCurrency as PrepaymentTotalAmtCrcy,
case when WCPrcConfig.PrepaymentWrkCtrDocPricingOptn = '01' -- CMM Pricing
and CMMPrice.PrepaymentDocCmmdtyMarketQty is initial
and Material.Commodity is not initial
then 'X'
when WCPrcConfig.PrepaymentWrkCtrDocPricingOptn = '02' -- CPE Pricing
and Material.Commodity is not initial
then CPEPrice.PrepaymentTotalAmtIsFixedPrice
when WCPrcConfig.PrepaymentWrkCtrDocPricingOptn = '03' -- Item Net Amount
or WCPrcConfig.PrepaymentWrkCtrDocPricingOptn is null
or Material.Commodity is initial
then 'X'
else '' end as PrepaymentTotalAmtIsFixedPrice
}
where
DocTypes.PrepaymentAgrmtRefDocumentCat = 'PO'
and DocTypes.PrepaymentAgrmtRefDocumentCat = :P_DocCat
union
--------------------------------------------------------------------------------------
-- View 3: SALES DOCUMENTS
--------------------------------------------------------------------------------------
-- Valid Doc Types
select from I_PrepayWrkCtrAllowedDocTypes as DocTypes
-- Document Header
inner join I_SalesDocument as Document on Document.SalesDocumentType = DocTypes.PrepaymentAgrmtRefDocumentType
-- Document Item
inner join I_SalesDocumentItem as DocumentItem on DocumentItem.SalesDocument = Document.SalesDocument
-- Material and Commodity
inner join I_PrepaymentMaterial as Material on DocumentItem.Material = Material.Material
{
key DocTypes.PrepaymentAgrmtWorkCenterType as PrepaymentAgrmtWorkCenterType,
key DocTypes.PrepaymentAgrmtRefDocumentCat as PrepaymentAgrmtRefDocumentCat,
key :P_BusObj as PrepaymentAgrmtRefBusObject,
key Document.SalesDocument as DocNumber,
key cast( DocumentItem.SalesDocumentItem as abap.char(6) ) as DocumentItemNumber,
cast('00000' as abap.numc(5)) as PurchasingDocumentItem,
lpad( Document.SalesDocument, 25, '0' ) as PrepaymentAgrmtRefDocument,
Document.BillingCompanyCode as CompanyCode,
Document.SalesDocumentType as DocumentType,
@Semantics.amount.currencyCode: 'PrepaymentTotalAmtCrcy'
cast(DocumentItem.NetAmount as abap.curr(23,2)) as PrepaymentTotalAmt,
@Semantics.currencyCode: true
Document.TransactionCurrency as PrepaymentTotalAmtCrcy,
cast('X' as boole_d) as PrepaymentTotalAmtIsFixedPrice
}
where
DocTypes.PrepaymentAgrmtRefDocumentCat = 'SO'
and DocTypes.PrepaymentAgrmtRefDocumentCat = :P_DocCat
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_PREAGRMTTRADINGCONTRACT",
"I_PREAGRMTTRADINGCONTRACTITEM",
"I_PREPAYMENTDOCUMENTMARKETVAL",
"I_PREPAYMENTMATERIAL",
"I_PREPAYMENTWRKCTRLINKCONFIGN",
"I_PREPAYWRKCTRALLOWEDDOCTYPES",
"I_PURCHASINGDOCUMENT",
"I_PURCHASINGDOCUMENTITEM",
"I_SALESDOCUMENT",
"I_SALESDOCUMENTITEM",
"P_PREPAYMENTDOCVERSLOGPRCGVAL"
],
"ASSOCIATED":
[
"I_PREPAYMENTDOCUMENTMARKETVAL",
"P_PREPAYMENTDOCVERSLOGPRCGVAL"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/